SQL: date and numeric comparison are translated to separate range queries #30017
Description
Original comment by @astefan:
For a query like:
POST /_xpack/sql/translate { "query":"SELECT name.keyword FROM library WHERE name!='NULL' AND release_date >= '2011-06-02' AND release_date <= '2011-06-02' AND match(author,'dan')" } or (with numerics):
POST /_xpack/sql/translate { "query":"SELECT name.keyword FROM library WHERE name!='NULL' AND price >= 10 AND price <= 200 AND match(author,'dan')" } The translated query uses individual range queries for the lower and upper limits even though the field being used in the query is the same and an optimization like {"range":{"price":{"from":10,"to":200,"include_lower":true,"include_upper":true,"boost":1}}} can be used instead. For the reference, this is the translated query at the moment (irrelevant parts not provided):
... "query": { "bool": { "filter": [ { "bool": { "filter": [ { "bool": { "filter": [ { "bool": { "must_not": [ { "term": { "name.keyword": { "value": "NULL", "boost": 1 } } } ], "adjust_pure_negative": true, "boost": 1 } }, { "range": { "price": { "from": 10, "to": null, "include_lower": true, "include_upper": false, "boost": 1 } } } ], "adjust_pure_negative": true, "boost": 1 } }, { "range": { "price": { "from": null, "to": 200, "include_lower": false, "include_upper": true, "boost": 1 } } } ], "adjust_pure_negative": true, "boost": 1 } }, ...