SQL Translate does not work well

[SingingFisher]

Hi gays:
I am using elasticsearch 7.3.
I am learning elasticsearch SQL translate but got a problem.

Here is my operation step:

• Step 1:search documents by sql

POST /_sql?format=txt
{
	"query":"SELECT author, name, count(0) as cnt FROM library group by author, name order by cnt desc"
}

response seem to be correct:

     author     |     name      |      cnt      
----------------+---------------+---------------
Frank Herbert   |Dune           |2              
Dan Simmons     |Hyperion       |1              
James S.A. Corey|Leviathan Wakes|1  

• Step 2:generate query DSL by sql translate

POST /_sql/translate
{
	"query":"SELECT author, name, count(0) as cnt FROM library group by author, name order by cnt desc"
}

got:

{
    "size": 0,
    "_source": false,
    "stored_fields": "_none_",
    "aggregations": {
        "groupby": {
            "composite": {
                "size": 1000,
                "sources": [
                    {
                        "448": {
                            "terms": {
                                "field": "author.keyword",
                                "missing_bucket": true,
                                "order": "asc"
                            }
                        }
                    },
                    {
                        "450": {
                            "terms": {
                                "field": "name.keyword",
                                "missing_bucket": true,
                                "order": "asc"
                            }
                        }
                    }
                ]
            }
        }
    }
}

• Step 3

POST /library/book/_search
query dsl from step 2

got:

{
    "took": 3,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 4,
            "relation": "eq"
        },
        "max_score": null,
        "hits": []
    },
    "aggregations": {
        "groupby": {
            "after_key": {
                "448": "James S.A. Corey",
                "450": "Leviathan Wakes"
            },
            "buckets": [
                {
                    "key": {
                        "448": "Dan Simmons",
                        "450": "Hyperion"
                    },
                    "doc_count": 1
                },
                {
                    "key": {
                        "448": "Frank Herbert",
                        "450": "Dune"
                    },
                    "doc_count": 2
                },
                {
                    "key": {
                        "448": "James S.A. Corey",
                        "450": "Leviathan Wakes"
                    },
                    "doc_count": 1
                }
            ]
        }
    }
}

Question:

One:

POST /_sql?format=txt works well, but query DSL generated by SQL translate seems to be inaccurate(order by C)

Two

what's the correct dsl for sql like select A, B, count(*) as C from XX group by A,B order by C?

[Andrei_Stefan]

That's by design, @SingingFisher.
Not all queries in ES SQL have a direct correspondent in Elasticsearch query DSL. In this specific case, sorting by COUNT() is done client-side, and not server-side. The query _translate api gives you is the server-side query.

You can find more details about this behavior here in this github issue.