[CALCITE-4868] Elasticsearch adapter fails if GROUP BY is followed by ORDER BY

• Type: Bug
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.28.0
• Component/s: elasticsearch-adapter
• Labels: pull-request-available
• Assignee: ZheHu
• Reporter: ZheHu
• Created: 28/Oct/21 01:20
• Updated: 22/Dec/21 12:55

Description

The Elasticsearch adapter doesn't support sort aggregation result. When I run the following SQL in ES Adapter module (AggregationTest.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");
  }

I get such exception:

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)

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):

{
  "aggs": {
    "cat5_val1": {
      "terms": {
        "field": "cat5",
        "order": [
          {
            "_key": "desc"
          },
          {
            "MAX_VAL1": "desc"
          }
        ]
      },
      "aggs": {
        "MAX_VAL1": {
          "max": {
            "field": "val1"
          }
        }
      }
    }
  }
}

Issue Links

• links to: GitHub Pull Request #2599