SQL: GROUP BY constant with ORDER BY aggregate crashes the server

[luigidellaquila]

The following query

SELECT 1 as c, MAX(salary) FROM person GROUP BY c ORDER BY MAX(salary) desc

crashes the server with

»  Caused by: java.lang.ClassCastException: class org.elasticsearch.xpack.sql.session.SingletonRowSet cannot be cast to class org.elasticsearch.xpack.sql.execution.search.ResultRowSet (org.elasticsearch.xpack.sql.session.SingletonRowSet and org.elasticsearch.xpack.sql.execution.search.ResultRowSet are in unnamed module of loader java.net.FactoryURLClassLoader @34e07e65)
»       at org.elasticsearch.xpack.sql.execution.search.Querier$LocalAggregationSorterListener.consumeRowSet(Querier.java:321) ~[?:?]
»       at org.elasticsearch.xpack.sql.execution.search.Querier$LocalAggregationSorterListener.onResponse(Querier.java:302) ~[?:?]
»       at org.elasticsearch.xpack.sql.execution.search.Querier$LocalAggregationSorterListener.onResponse(Querier.java:259) ~[?:?]
»       at org.elasticsearch.xpack.sql.execution.search.Querier$ImplicitGroupActionListener.handleBuckets(Querier.java:420) ~[?:?]
»       at org.elasticsearch.xpack.sql.execution.search.Querier$ImplicitGroupActionListener.handleResponse(Querier.java:398) ~[?:?]
»       at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.onResponse(Querier.java:673) ~[?:?]
»       at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.onResponse(Querier.java:657) ~[?:?]
»       at org.elasticsearch.client.internal.node.NodeClient.lambda$executeLocally$0(NodeClient.java:115) ~[elasticsearch-8.2.0-SNAPSHOT.jar:8.2.0-SNAPSHOT]
»       ... 25 more

The following queries work as expected

SELECT 1 as c, MAX(salary) FROM person GROUP BY c;

SELECT 1 as c, MAX(salary) FROM person ORDER BY MAX(salary) desc;

SELECT 1 as c, MAX(salary) FROM person GROUP BY c ORDER BY c desc

SELECT name, 1 as c, MAX(salary) FROM person GROUP BY name, c order by MAX(salary) desc

so the problem seems to be due to the mix of GROUP BY constant and ORDER BY aggregation with no further grouping.

The query is uncommon, the ORDER BY is meaningless in this context, since it always returns only one result. Still, the outcome is a server crash, so the problem deserves some attention.

Steps to reproduce

POST localhost:9200/_bulk

{"index": {"_index":"person"}}
{"name" : "foo", "surname":"bar", "salary": 1000}
{"index": {"_index":"person"}}
{"name" : "foo", "surname":"baz", "salary": 2000}

GET localhost:9200/_sql

{
 "query":"SELECT 1 as c, MAX(salary) FROM person GROUP BY c ORDER BY MAX(salary) desc"
}

Further references

This issue could be related to #84490 and #74064, probably related to query optimizations. #84574 seems to fix it

[elasticmachine]

Pinging @elastic/es-ql (Team:QL)

[jonghak96]

Hello, @luigidellaquila. It's been about 9 months since you wrote an issue. Do you have any additional comments on the issue?

If you want to find the maximum value for all documents How about querying only one top value with "LIMIT 1", after executing a SELECT statement with c and specific fields in the GROUP BY clause?

ex)

SELECT 1 as c, MAX(salary) FROM person GROUP BY c, salary ORDER BY MAX(salary) desc LIMIT 1

If the issue has already been resolved, please leave a comment. Thank you.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)