aggregation , sort with apache calcite

[asma zgolli <zg...@gmail.com>]

hello ,

i m using apache calcite mongodb adapter but aggregations , group by and
sort are not working with mongodb adapter.

select count(distinct _MAP['job']) from \"test\".\"banking\""

select * from \"test\".\"banking\" order by _MAP['job']"

select * from \"test\".\"banking\" group by _MAP['job']"

didn't work for me.


Exception in thread "main" java.sql.SQLException: Error while executing SQL
"select count(distinct _MAP['job']) from "test"."banking" ": While running
MongoDB query [{$project: {'$f0': '$job'}},

{$group: {_id: '$_0'}},

{$project: {_0: '$_id'}},

{$group: {_id: {}, 'EXPR$0': {$sum: {$cond: [ {$eq: ['_0', null]}, 0,
1]}}}}]

at org.apache.calcite.avatica.Helper.createException(Helper.java:56)

at org.apache.calcite.avatica.Helper.createException(Helper.java:41)

at org.apache.calcite.avatica.AvaticaStatement.executeInternal(
AvaticaStatement.java:163)

at org.apache.calcite.avatica.AvaticaStatement.executeQuery(
AvaticaStatement.java:227)

at

at test.applications.calcite.test.test1querycalcite$delayedInit$body.apply(
test1querycalcite.scala:29)

at scala.Function0$class.apply$mcV$sp(Function0.scala:34)

at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)

at scala.App$$anonfun$main$1.apply(App.scala:76)

at scala.App$$anonfun$main$1.apply(App.scala:76)

at scala.collection.immutable.List.foreach(List.scala:381)

at scala.collection.generic.TraversableForwarder$class.foreach(
TraversableForwarder.scala:35)

at scala.App$class.main(App.scala:76)



am i missing something?

yours sincerely,
asma

Re: aggregation , sort with apache calcite

[Stamatis Zampetakis <za...@gmail.com>]

Hi Asma,

There is some support for sort and aggregation in MongoDB but for sure
there are small bugs here and there that need to be fixed.
Your example seems similar to MongoAdapterTest#testDistinctCount [1] and
indeed there seems to be a problem (CALCITE-2115 [2]) but nobody seems to
be working on this at the moment.
Check the remaining tests in MongoAdapterTest [3] to see queries that
should work and if you find something that needs to be fixed please log a
JIRA case.

Best,
Stamatis

[1]
https://github.com/apache/calcite/blob/b49693d31964657bf5058bd9387e505992cebd51/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java#L557
[2] https://issues.apache.org/jira/browse/CALCITE-2115
[3]
https://github.com/apache/calcite/blob/master/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java

On Mon, Mar 1, 2021 at 11:01 PM asma zgolli <zg...@gmail.com> wrote:

> hello ,
>
> i m using apache calcite mongodb adapter but aggregations , group by and
> sort are not working with mongodb adapter.
>
> select count(distinct _MAP['job']) from \"test\".\"banking\""
>
> select * from \"test\".\"banking\" order by _MAP['job']"
>
> select * from \"test\".\"banking\" group by _MAP['job']"
>
> didn't work for me.
>
>
> Exception in thread "main" java.sql.SQLException: Error while executing SQL
> "select count(distinct _MAP['job']) from "test"."banking" ": While running
> MongoDB query [{$project: {'$f0': '$job'}},
>
> {$group: {_id: '$_0'}},
>
> {$project: {_0: '$_id'}},
>
> {$group: {_id: {}, 'EXPR$0': {$sum: {$cond: [ {$eq: ['_0', null]}, 0,
> 1]}}}}]
>
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
>
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
>
> at org.apache.calcite.avatica.AvaticaStatement.executeInternal(
> AvaticaStatement.java:163)
>
> at org.apache.calcite.avatica.AvaticaStatement.executeQuery(
> AvaticaStatement.java:227)
>
> at
>
> at test.applications.calcite.test.test1querycalcite$delayedInit$body.apply(
> test1querycalcite.scala:29)
>
> at scala.Function0$class.apply$mcV$sp(Function0.scala:34)
>
> at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
>
> at scala.App$$anonfun$main$1.apply(App.scala:76)
>
> at scala.App$$anonfun$main$1.apply(App.scala:76)
>
> at scala.collection.immutable.List.foreach(List.scala:381)
>
> at scala.collection.generic.TraversableForwarder$class.foreach(
> TraversableForwarder.scala:35)
>
> at scala.App$class.main(App.scala:76)
>
>
>
> am i missing something?
>
> yours sincerely,
> asma
>