ES|QL NULL check in CASE clause breaks later WHERE

[tomhe]

Elasticsearch Version

8.15.1

Installed Plugins

No response

Java Version

bundled

OS Version

Docker

Problem Description

Using a WHERE statement after a CASE clause gives very strange and unexpected behavior. It's somewhat hard to explain, but the steps to reproduce should explain the situation well.

Steps to Reproduce

1. Bring up a fresh Docker cluster from scratch, running 8.15.1
2. Ingest a few documents:

   POST _bulk
   {"index":{"_index":"test"}}
   {"val": 1}
   {"index":{"_index":"test"}}
   {"val": 2}
   {"index":{"_index":"test"}}
   {"val": 3}
   {"index":{"_index":"test"}}
   {"val": 4}
   {"index":{"_index":"test"}}
   {} // This doc has no value for val
   

3. Run a query that includes a NULL check in the CASE clause:

   POST /_query?format=txt
   {
   "query": """
       FROM test
       | KEEP val
       | EVAL eval = CASE(
           val == 0, null,
           val % 2 == 0, "val is even",
           val % 2 != 0, "val is odd",
           val IS NULL, "val is null")
       | SORT val
   """
   }
   

4. Observe that the output includes all documents and that the eval column has a value for all rows but the first:

   #! No limit defined, adding default limit of [1000]
       val      |     eval
   ---------------+---------------
   0              |null
   1              |val is odd
   2              |val is even
   3              |val is odd
   4              |val is even
   null           |val is null
   

5. Add a WHERE clause that operates on the eval column to only include rows where eval is not null:

   POST /_query?format=txt
   {
   "query": """
       FROM test
       | KEEP val
       | EVAL eval = CASE(
           val == 0, null,
           val % 2 == 0, "val is even",
           val % 2 != 0, "val is odd",
           val IS NULL, "val is null")
       | SORT val
       | WHERE eval IS NOT NULL
   """
   }
   

6. Observe that the row where val is 0 and eval is null is correctly excluded, but that the row where val is null and eval is val is null is (incorrectly) missing:

   #! No limit defined, adding default limit of [1000]
       val      |     eval
   ---------------+---------------
   1              |val is odd
   2              |val is even
   3              |val is odd
   4              |val is even
   

7. Change the WHERE clause that operates on the eval column to use LIKE:

   POST /_query?format=txt
   {
   "query": """
       FROM test
       | KEEP val
       | EVAL eval = CASE(
           val == 0, null,
           val % 2 == 0, "val is even",
           val % 2 != 0, "val is odd",
           val IS NULL, "val is null")
       | SORT val
       | WHERE eval LIKE "*"
   """
   }
   

8. Observe that the row for eval with the value val is null is still (incorrectly) missing:

   #! No limit defined, adding default limit of [1000]
       val      |     eval
   ---------------+---------------
   1              |val is odd
   2              |val is even
   3              |val is odd
   4              |val is even
   

9. Change the WHERE clause that operates on the eval column to use a different LIKE pattern:

   POST /_query?format=txt
   {
   "query": """
       FROM test
       | KEEP val
       | EVAL eval = CASE(
           val == 0, null,
           val % 2 == 0, "val is even",
           val % 2 != 0, "val is odd",
           val IS NULL, "val is null")
       | SORT val
       | WHERE eval LIKE "value*"
   """
   }
   

10. Observe that the row for eval with the value val is null now shows correctly:

    #! No limit defined, adding default limit of [1000]
        val      |     eval
    ---------------+---------------
    1              |val is odd
    2              |val is even
    3              |val is odd
    4              |val is even
    null           |val is null
    

11. Revert back to the query used in step 5, but add a LIMIT statement somewhere before the WHERE statement:

    POST /_query?format=txt
    {
    "query": """
        FROM test
        | KEEP val
        | EVAL eval = CASE(
            val == 0, null,
            val % 2 == 0, "val is even",
            val % 2 != 0, "val is odd",
            val IS NULL, "val is null")
        | SORT val
        | LIMIT 10
        | WHERE eval IS NOT NULL
    """
    }
    

12. Observe that the row for eval with the value val is null now shows correctly (where it did not show in step 6):

        val      |     eval
    ---------------+---------------
    1              |val is odd
    2              |val is even
    3              |val is odd
    4              |val is even
    null           |val is null
    

Conclusion:

The best workaround seems to be to use a LIKE statement with some pattern other than "*" that matches all expected values (which is not always possible). Using the LIMIT fix will not work for large datasets.

Logs (if relevant)

No response

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[nik9000]

I can reproduce this with:

curl -uelastic:password -HContent-Type:application/json -XDELETE localhost:9200/test?pretty
curl -uelastic:password -HContent-Type:application/json -XPOST 'localhost:9200/test/_bulk?pretty&refresh' -d'
{"index":{"_index":"test"}}
{"val": 0}
{"index":{"_index":"test"}}
{"val": 1}
{"index":{"_index":"test"}}
{"val": 2}
{"index":{"_index":"test"}}
{"val": 3}
{"index":{"_index":"test"}}
{"val": 4}
{"index":{"_index":"test"}}
{}
'

echo '{
    "profile": true,
    "query": "FROM test 
    | KEEP val
    | EVAL eval = CASE(
        val == 0, null,
        val % 2 == 0, \"val is even\",
        val % 2 != 0, \"val is odd\",
        val IS NULL, \"val is null\")
    | SORT val
    | WHERE eval IS NOT NULL"
}' | tr '\n' ' ' | curl -s -uelastic:password -HContent-Type:application/json -XPOST localhost:9200/_query?pretty -d@-

That indeed skips the document where val is null even though we're trying to skip the documents where val is 0.

The profile yields this:

            "operator" : "LuceneSourceOperator[maxPageSize = 4228, remainingDocs = 2147483642]",
            "status" : {
              "processed_slices" : 1,
              "processed_queries" : [
                "FieldExistsQuery [field=val]"
              ],

We're extracting, incorrectly, that val has to exists from the CASE. I don't believe we should extract anything from the CASE at the moment. The best we could possible do is val != 0, but even that's quite tricky.

[nik9000]

InferIsNotNull is incorrectly deducing that, because CASE is referencing val that CASE is null if val is null. There's a blocklist of functions that have "funny" nullability rules that looks like:

    private static boolean skipExpression(Expression e) {
        return e instanceof Coalesce;

git-blame is giving me trouble because @alex-spies recently moved this file. But I'll bet @costin will remember more.

For what it's worth, if I add CASE to the blocklist like so:

    private static boolean skipExpression(Expression e) {
        return e instanceof Coalesce || e instanceof Case;
    }

Then the bug seems to vanish. We no longer try to push through the CASE and, instead, correctly give up on pushing the IS NOT NULL condition down to the index.

@costin, is that the right solution here?

[luigidellaquila]

Nullability was inherited from QL, where functions/operators semantics are quite different.
We had discussions about this in the past, see #105023.

TBH I don't like relying on instanceof, maybe in this case the solution could be

    private static boolean skipExpression(Expression e) {
        return e.nullable() == Nullability.UNKNOWN;
    }

Not that I like it either: IMHO Nullability should be a normal boolean.

Nullability.UNKNOWN is confusing, it took me quite some time to understand what it does, and I'm still not completely sure of all its implications (spoiler: Nullability.TRUE seems to be the safe option, but it's not true for some rules, eg. FoldNull; and the default nullability for functions still relies on children nullability, that in most cases is incorrect ).

Probably it's time to review all the nullability logic and make it more coherent with how ES|QL works.

[costin]

@nik9000 +1 on your solution; the conditional nature of Case trips the rule and should be excluded just like Coalesce.