# SQL translates to wrong DSL query

**URL:** <https://discuss.elastic.co/t/sql-translates-to-wrong-dsl-query/278211>\
**Category:** Elasticsearch\
**Created:** [July 8, 2021, 5:55pm UTC](https://discuss.elastic.co/t/sql-translates-to-wrong-dsl-query/278211 "2021-07-08T17:55:49Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![shivmgs](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@shivmgs](https://discuss.elastic.co/u/shivmgs)\
**Post date:** [July 8, 2021, 5:55pm UTC](https://discuss.elastic.co/t/sql-translates-to-wrong-dsl-query/278211/1 "2021-07-08T17:55:49Z")

</div>

We have a field company\_name of type text. For the same field, we have additional .raw field - company\_name.raw which is a keyword field.

For the following SQL query, search on company\_name

```json
{
    "query": "SELECT \"company_name\" AS \"column3\",COUNT( \"cursor\" ) AS \"column2\"FROM \"jobspikr_v3_data\" AS \"440b2537c24d29841e7443fdcdf254\"WHERE ( ( (\"company_name\" LIKE '%info%'))) GROUP BY \"company_name\" ORDER BY \"column3\" ASC LIMIT 101",
    "fetch_size": 10
}

```

Its being translated to the following DSL, where search is applied on `.raw` field.

```json
{
    "size": 0,
    "query": {
        "wildcard": {
            "company_name.raw": {
                "wildcard": "*info*",
                "boost": 1.0
            }
        }
    },
    "_source": false,
    "stored_fields": "_none_",
    "aggregations": {
        "groupby": {
            "composite": {
                "size": 10,
                "sources": [
                    {
                        "136613": {
                            "terms": {
                                "field": "company_name.raw",
                                "missing_bucket": true,
                                "order": "asc"
                            }
                        }
                    }
                ]
            },
            "aggregations": {
                "136626": {
                    "filter": {
                        "exists": {
                            "field": "cursor",
                            "boost": 1.0
                        }
                    }
                }
            }
        }
    }
}

```

Is there a way to restrict the search on the company\_name field(not .raw) field?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [July 8, 2021, 6:52pm UTC](https://discuss.elastic.co/t/sql-translates-to-wrong-dsl-query/278211/2 "2021-07-08T18:52:16Z")

</div>

Welcome!

A guess would be to change the type of the `company` field to be a `keyword` field.

But what is wrong with the current behavior?

---

<div class="post-metadata">

**Author:** ![shivmgs](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@shivmgs](https://discuss.elastic.co/u/shivmgs)\
**Post date:** [July 9, 2021, 4:24am UTC](https://discuss.elastic.co/t/sql-translates-to-wrong-dsl-query/278211/3 "2021-07-09T04:24:00Z")

</div>

HI David,

The company\_name.raw field is not a searchable field. Its used only for aggregation and sorting. company\_name field is a text field, which is used for all the searching. Here in the SQL we are trying to use the company\_name field only, but it translates to company\_name.raw automatically. Here we are getting responses like these

```auto
"type": "query_shard_exception",
"reason": "failed to create query: {\n \"wildcard\" : {\n \"company_name.raw\" : {\n \"wildcard\" : \"*info*\",\n \"boost\" : 1.0\n }\n }\n}",

```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [July 9, 2021, 6:24am UTC](https://discuss.elastic.co/t/sql-translates-to-wrong-dsl-query/278211/4 "2021-07-09T06:24:21Z")

</div>

Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script is something anyone can **copy and paste in Kibana dev console** , click on the run button to reproduce your use case. It will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

Also share the full error please.

---

<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:** [July 16, 2021, 8:39am UTC](https://discuss.elastic.co/t/sql-translates-to-wrong-dsl-query/278211/5 "2021-07-16T08:39:57Z")

</div>

This is documented [here](https://www.elastic.co/guide/en/elasticsearch/reference/7.x/sql-like-rlike-operators.html#sql-like-rlike-operators).

---

<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:** [August 13, 2021, 8:40am UTC](https://discuss.elastic.co/t/sql-translates-to-wrong-dsl-query/278211/6 "2021-08-13T08:40:33Z")

</div>

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