[teiid-issues] [JBoss JIRA] (TEIID-2725) Group by doesn't work with MongoDB

[Ramesh Reddy (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2725?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12849547#comment-12849547 ] 

Ramesh Reddy edited comment on TEIID-2725 at 11/4/13 9:17 AM:
--------------------------------------------------------------

The MongoDB expression needs to be 

{code}
$group:{ "_id" : "$grade" , "c_1" : { "$sum" : "$score"}}
$project: { "c_0" : "$_id" , "c_1" : 1}
$sort: { "c_0" : 1}
{code}

Correct the translator code to produce above query. Added a unit test
                
      was (Author: rareddy):
    The MongDB expression needs to be 

{code}
$group:{ "_id" : "$grade" , "c_1" : { "$sum" : "$score"}}
$project: { "c_0" : "$_id" , "c_1" : 1}
$sort: { "c_0" : 1}
{code}

Correct the translator code to produce above query. Added a unit test
                  
> Group by doesn't work with MongoDB
> ----------------------------------
>
>                 Key: TEIID-2725
>                 URL: https://issues.jboss.org/browse/TEIID-2725
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.6
>         Environment: Teiid with MongoDB
>            Reporter: Ivan Chan
>            Assignee: Ramesh Reddy
>              Labels: Beta1
>             Fix For: 8.6
>
>
> 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. 

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira