SQL: Suggesting a different approach to error messages

[astefan]

At the moment, a limitation in the functionality of ES SQL due to how Elasticserach is able to support such functionality, is presented to the user as an error message that says "Cannot ......, expected....." which loosely implies that the query is maybe wrong (which, in this case, is not wrong from SQL world point-of-view).

Example: SELECT SPACE("languages") s, COUNT(*) count FROM "test_emp" WHERE "languages" IS NOT NULL GROUP BY SPACE("languages") ORDER BY COUNT(*);

This one prints the following error message:

Bad request [Found 1 problem(s)
line 1:178: Cannot order by non-grouped column [COUNT(1)], expected [SPACE(languages)]]

But, ordering by count is not possible because the composite aggregation used behind the scene cannot sort by the size of the buckets (which is the count itself from sql). Maybe the error message could be clearer about this, mentioning somehow that this thing is unsupported: Ordering by non-grouped columns [COUNT(1)] is unsupported, expected [SPACE()]. This makes it less confusing by stating that this specific query configuration is un-supported (which is in fact true).

[elasticmachine]

Pinging @elastic/es-search-aggs

[matriv]

Closing this as the functionality was implemented with #38042 and there is no error thrown since.