Xpack/sql Does not appear to be able to handle "IN"

Elastic Stack Elasticsearch
1

Example SQL ->

SELECT visit.app.appVersion FROM index WHERE visit.app.appName IN ('appA', 'appB')

returns

{
  "error": {
    "root_cause": [
      {
        "type": "sql_illegal_argument_exception",
        "reason": "Don't know how to translate In In[visit.app.appName{f}#15620,[appA, appB]]"
      }
    ],
    "type": "sql_illegal_argument_exception",
    "reason": "Don't know how to translate In In[visit.app.appName{f}#15620,[appA, appB]]"
  },
  "status": 500
}

Seems like xpack/sql should know how to handle a terms query. Can anyone explain how to do this without breaking the original SQL query into multiples?

2

https://www.elastic.co/guide/en/elasticsearch/reference/6.4/sql-syntax-select.html and associated pages show all the available options.

If you'd like something that's not listed then it would be great if you would raise a feature request.

3

This seems like it should be pretty basic functionality

WHERE visit.app.appName IN ('appA', 'appB')

should translate into something like:

terms: {visit.app.appName: [appA, appB]}

I should NOT have to do something like:

WHERE visit.app.appName = appA OR visit.app.appName = appB

I am fairly sure that something like this would have been (should have been) implemented, especially since the ES_DSL supports checking for multiple terms in one field.
I did look through the page you referred me to, I however do not see anything related to checking for one of many values inside of a field. This is part of the condition not necessarily part of the select. I do not see a page on supported condition operators. Thus the reason I asked the original question.

4

SQL Access | Elasticsearch Guide [6.4] | Elastic also mentions;

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

If you can create a feature request then it'll help us improve things! :slight_smile:

5

We are aware of this missing functionality and this feature has already been registered as a request here.

6

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