Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-2725

Group by doesn't work with MongoDB

    XMLWordPrintable

    Details

    • Steps to Reproduce:
      Hide

      Create database"test" and collection "grades" and here is the sample data:

      { "_id":

      { "$oid" : "525cec2972913d2ed302a03c" }

      ,
      "student_id": 0,
      "state": "CA",
      "name": "Doug",
      "score": 97,
      "grade": "A"}

      { "_id":

      { "$oid" : "525cec2972913d2ed302a03d" }

      ,
      "student_id": 1,
      "state": "FL",
      "name": "Sam",
      "score": 90,
      "grade": "A"}

      { "_id":

      { "$oid" : "525cec2972913d2ed302a03e" }

      ,
      "student_id": 2,
      "state": "NY",
      "name": "Alex",
      "score": 55,
      "grade": "F"}

      Create VDB with mongoDB connector:

      schemaText = "CREATE FOREIGN TABLE grades(state varchar(25), name varchar(25), score integer, grade varchar(5)) OPTIONS(UPDATABLE 'TRUE');"
      database ="test" />

      Run the following SQL with Teiid:

      select "grade", sum("score") as "sum_score" from "mongoDBDS"."grades" group by "grade" order by "grade"

      Expected Result:
      grade score
      A 187
      F 55

      Actual Result: (INCORRECT)
      grade score
      242

      Show
      Create database"test" and collection "grades" and here is the sample data: { "_id": { "$oid" : "525cec2972913d2ed302a03c" } , "student_id": 0, "state": "CA", "name": "Doug", "score": 97, "grade": "A"} { "_id": { "$oid" : "525cec2972913d2ed302a03d" } , "student_id": 1, "state": "FL", "name": "Sam", "score": 90, "grade": "A"} { "_id": { "$oid" : "525cec2972913d2ed302a03e" } , "student_id": 2, "state": "NY", "name": "Alex", "score": 55, "grade": "F"} Create VDB with mongoDB connector: schemaText = "CREATE FOREIGN TABLE grades(state varchar(25), name varchar(25), score integer, grade varchar(5)) OPTIONS(UPDATABLE 'TRUE');" database ="test" /> Run the following SQL with Teiid: select "grade", sum("score") as "sum_score" from "mongoDBDS"."grades" group by "grade" order by "grade" Expected Result: grade score A 187 F 55 Actual Result: (INCORRECT) grade score 242

      Description

      Group by SQL with/ without aggregate function returns incorrect values:

      For example:

      Original SQL:

      select "grade", sum("score") as "sum_score" from "mongoDBDS"."grades" group by "grade" order by "grade"

      Transform query:

      SELECT grades.grade AS c_0, SUM(grades.score) AS c_1 FROM grades GROUP BY grades.grade ORDER BY c_0

      $group:{ "_id" : "$grade" , "c_1" : { "$sum" : "$score"}}

      $project:

      { "c_0" : "$grade" , "c_1" : 1}

      $sort:

      { "c_0" : 1}

      Expected Result:
      grade score
      A 187
      F 55

      Actual Result: (INCORRECT)
      grade score
      242

      It only returns SUM(score) and group by fields are ignore.

        Attachments

          Activity

            People

            Assignee:
            rareddy@jboss.org Ramesh Reddy
            Reporter:
            mchantibco Ivan Chan (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: