MongDB can't get group by to work.

[ichanjasper]

I have mongodb setup with 8.6 Teiid embedded version successfully.  When I ran a sql statement like [select "name", "state" from "mongoDBDS"."grades"] and it works perfectly.  However, if i ran a statement with group by like [select "state" from "mongoDBDS"."grades" group by "state" order by "state"] and it didn't return any value.  Do you guys have any clue?

Thanks a lot,

Ivan C.

[rareddy]

Can you try with a aggregate function like avg or sum in your select query? In the absence of aggregate it should work as DISTINCT per http://stackoverflow.com/questions/7343702/use-of-group-by-in-sql-only-with-aggregates

Also can you turn on DEBUG logging on "org.teiid.CONNECTOR" context and post the source query  that is being executed as part of this? You can also post the query plan for this.

Thanks

Ramesh..

[ichanjasper]

Hi, I tried aggregate function with group by, but I still couldn't get it to work.

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}

And it didn't work without aggregate function neither:

Original SQL:

select "grade" from "mongoDBDS"."grades" group by "grade" order by "grade"

Transform query:

SELECT grades.grade AS c_0 FROM grades GROUP BY grades.grade ORDER BY c_0

$group:{ "_id" : "$grade"}

$project:{ "c_0" : "$grade"}

$sort:{ "c_0" : 1}

Do you guys have any idea?

Thanks,

Ivan C.

[rareddy]

Do you see a exception? or get wrong results etc? what did not work?

Since you see the expressions that are being sent to MongoDB, if you execute then in MongoDB console do you get results, or you think the MongoDB expression generated by Teiid is in-correct?

[ichanjasper]

It got incorrect result. 

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          320

B          150

C          50 

Actual Result:   (WRONG)

grade   score

           520

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

[rareddy]

Ivan,

Can you log a JIRA with VDB and some sample data.

Thanks

Ramesh..

[ichanjasper]

How do I print out the VDB from VDBMetaData?

[rareddy]

Take look at TestVDBMetadata