SQL: IN does not behave as expected #38424
Description
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.
Metadata
Metadata
Assignees
Labels
Type
Fields
Give feedbackNo fields configured for issues without a type.