[ https://issues.apache.org/jira/browse/CALCITE-4868?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17439233#comment-17439233 ]
ZheHu commented on CALCITE-4868: -------------------------------- PR at :https://github.com/apache/calcite/pull/2599 > Elasticsearch adapter fails if GROUP BY is followed by ORDER BY > --------------------------------------------------------------- > > Key: CALCITE-4868 > URL: https://issues.apache.org/jira/browse/CALCITE-4868 > Project: Calcite > Issue Type: Bug > Components: elasticsearch-adapter > Affects Versions: 1.28.0 > Reporter: ZheHu > Assignee: ZheHu > Priority: Major > > The Elasticsearch adapter doesn't support sort aggregation result. When I run > the following SQL in ES Adapter module (AggregationTest.java): > {code:java} > @Test void testSortAggregation() { > CalciteAssert.that() > .with(newConnectionFactory()) > .query("select cat5, max(val1) as MAX_VAL1 from view group by cat5 > order by MAX_VAL1 desc, cat5 desc") > .returns("cat5=2; val1=7\n"); > } > {code} > I get such exception: > {code:java} > java.lang.IllegalArgumentException: Field MAX_VAL1 not defined for aggs > at > org.apache.calcite.adapter.elasticsearch.ElasticsearchMapping.missingValueFor(ElasticsearchMapping.java:85) > at > org.apache.calcite.adapter.elasticsearch.ElasticsearchTable.aggregate(ElasticsearchTable.java:226) > at > org.apache.calcite.adapter.elasticsearch.ElasticsearchTable.find(ElasticsearchTable.java:120) > at > org.apache.calcite.adapter.elasticsearch.ElasticsearchTable.access$000(ElasticsearchTable.java:61) > at > org.apache.calcite.adapter.elasticsearch.ElasticsearchTable$ElasticsearchQueryable.find(ElasticsearchTable.java:376) > at > org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:363) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:338) > at > org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:578) > at > org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:569) > at > org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184) > at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64) > at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43) > at > org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:667) > at > org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:638) > at > org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675) > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156) > {code} > AS a result of the ES mapping doesn't contain field "MAX_VAL1", such > exception occurred. > According to the SQL, generated ES scripts should be(I have run it in ES > 7.3.2): > {code:java} > { > "aggs": { > "cat5_val1": { > "terms": { > "field": "cat5", > "order": [ > { > "_key": "desc" > }, > { > "MAX_VAL1": "desc" > } > ] > }, > "aggs": { > "MAX_VAL1": { > "max": { > "field": "val1" > } > } > } > } > } > } > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)