SQL: ORDER BY a function doesn't actually order nor complains about not being able to

[elasticmachine]

Original comment by @astefan:

Example:

POST /_xpack/sql?format=txt
{
  "query": "SELECT author,COUNT(name) FROM library WHERE match(author,'dan') GROUP BY author HAVING COUNT(name)>0 ORDER BY COUNT(name) DESC"
}

Result:

    author     |  COUNT(name)  
---------------+---------------
Dan Andrei     |1              
Dan Bla        |3              
Dan Simmons    |2              

The query being created it's using composite aggregation which doesn't actually allow for "custom" sorting of buckets, only by their values. Accepting an ORDER BY COUNT should, at least, reject the query as not being supported.

[elasticmachine]

Pinging @elastic/es-search-aggs