SQL: IN does not behave as expected

[paulcarey]

Using ES 6.5.4 I would expect queries of the form WHERE foo = 'a' OR foo = 'b' to return exactly the same results as WHERE foo IN ('a', 'b'). However, this is not what I'm observing.

Running my queries through /translate shows a material difference in how these are generated.

Using IN:

  "query": {
    "terms": {
      "foo": [
        "a",
        "b"
      ],
      "boost": 1
    }
  }

Using repeated OR clauses:

  "query": {
    "bool": {
      "should": [
        {
          "term": {
            "foo.keyword": {
              "value": "a",
              "boost": 1
            }
          }
        },
        {
          "term": {
            "foo.keyword": {
              "value": "b",
              "boost": 1
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },

What really seems to matter here though is that using OR causes ES to refer to the .keyword instance of a field that contains the following mapping:

          "foo": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },

If I modify my IN query to WHERE foo.keyword IN ('a', 'b') then the results are as expected.

[elasticmachine]

Pinging @elastic/es-search

[costin]

Thanks for the report. We'll look into it however it is unlikely the fix will be backported all the way to the 6.5.x line.

[paulcarey]

That's fine, thanks. I have my eyes set on 6.7 and the support for combining group by and order by!