# SQL Translate does not work well

**URL:** <https://discuss.elastic.co/t/sql-translate-does-not-work-well/220813>\
**Category:** Elasticsearch\
**Created:** [February 25, 2020, 10:02am UTC](https://discuss.elastic.co/t/sql-translate-does-not-work-well/220813 "2020-02-25T10:02:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![SingingFisher](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@SingingFisher](https://discuss.elastic.co/u/SingingFisher)\
**Post date:** [February 25, 2020, 10:02am UTC](https://discuss.elastic.co/t/sql-translate-does-not-work-well/220813/1 "2020-02-25T10:02:00Z")

</div>

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

```auto
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:

```nohighlight
     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

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

```

got:

```json
{
    "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

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

```

got:

```json
{
    "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`?

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [February 26, 2020, 10:22am UTC](https://discuss.elastic.co/t/sql-translate-does-not-work-well/220813/2 "2020-02-26T10:22:20Z")

</div>

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](https://github.com/elastic/elasticsearch/issues/41144#issuecomment-483150764).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 25, 2020, 10:22am UTC](https://discuss.elastic.co/t/sql-translate-does-not-work-well/220813/3 "2020-03-25T10:22:36Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.