SQL: IS NULL for a nested field doesn't work

[astefan]

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.

[elasticmachine]

Pinging @elastic/es-search-aggs