SQL: IS NULL for a nested field doesn't work #35171
Description
sql> select *, user.* from test; groupName | user.first | user.last ---------------+---------------+--------------- fans |John |null fans |Alice |White fans |Roger |Mack pets |Beni |The cat pets |Fido |the Dog pets |Maia |the Cat players |John |Smith players |Alice |null players |Tom |null In the sample above user is a nested field in the test index. There are null values for user.last in the documents.
For the following query, there are no results:
sql> select *, user.* from test where "user.last" is null; groupName | user.first | user.last ---------------+---------------+--------------- sql> The problem lies in the way the ES query is constructed. When something is null (or doesn't exist) in ES terminology, the "negated" exists query is used. At the moment, ES SQL translates the query above in the following ES query (some bits were removed as not relevant and to increase readability):
"query": { "bool": { "must_not": [ { "nested": { "query": { "exists": { "field": "user.last", "boost": 1 } }, "path": "user" } } ] } } While the query above is correct syntactically, it is incorrect for the IS NULL scenario. The correct query is:
"query": { "nested": { "query": { "bool": { "must_not": [ { "exists": { "field": "user.last", "boost": 1 } } ] }}, "path": "user", "inner_hits":{} } } The relevant bit is that the nested query should include the bool must_not, while the current version of ES SQL puts the nested query inside the bool must_not.