comphead opened a new issue, #7675:
URL: https://github.com/apache/datafusion-comet/issues/7675

   ### Describe the bug
   
   Under ANSI mode, a native `IN` can raise an error from a candidate that 
Spark never evaluates. Spark's `In` returns NULL without evaluating any 
candidate when the value is NULL, and otherwise stops at the first candidate 
that matches 
([`In.eval`](https://github.com/apache/spark/blob/v4.1.1/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala#L508-L534),
 the same in 3.4.3, 3.5.8 and 4.0.1).
   
   #7326 made `IN` lazy: 
[`lazy_in_list`](https://github.com/apache/datafusion-comet/blob/0fec45f232eac64aea65971c97956a64811af1dd/native/spark-expr/src/conditional_funcs/lazy_logical.rs#L111-L117)
 rewrites `v IN (c1, c2, ...)` as `v IN (c1) OR v = c2 OR ...` over 
`LazyLogical`. Two cases still evaluate a candidate that Spark skips:
   
   1. **The value is NULL.** For `OR`, `LazyLogical` evaluates the right 
operand on every row where the left one is not true, NULL included 
([`pending`](https://github.com/apache/datafusion-comet/blob/0fec45f232eac64aea65971c97956a64811af1dd/native/spark-expr/src/conditional_funcs/lazy_logical.rs#L233-L238)).
 On a row whose value is NULL, `v IN (c1)` is NULL, so `v = c2` runs and 
evaluates `c2`.
   2. **The value can fail.** `lazy_in_list` returns `None` unless 
`is_infallible(value)` holds, because the chain evaluates the value once per 
term. `spark_in_list` then falls through to DataFusion's `in_list` 
([L671](https://github.com/apache/datafusion-comet/blob/0fec45f232eac64aea65971c97956a64811af1dd/native/spark-expr/src/array_funcs/nested_comparison.rs#L671)),
 which evaluates every candidate over the whole batch.
   
   ### Steps to reproduce
   
   With `spark.sql.ansi.enabled=true`:
   
   ```sql
   CREATE TABLE lazy_in(v int, s string) USING parquet;
   INSERT INTO lazy_in SELECT /*+ REPARTITION(1) */ * FROM VALUES
     (0, 'bad'), (NULL, 'bad'), (2, '2') AS t(v, s);
   
   -- 1. The value is NULL on the second row
   SELECT v, v IN (0, CAST(s AS INT)) FROM lazy_in;
   
   -- 2. The value can fail, so the lazy rewrite does not apply
   SELECT v, (v + 1) IN (1, CAST(s AS INT)) FROM lazy_in;
   ```
   
   | Query | Spark | Comet |
   | --- | --- | --- |
   | 1 | `(0, true), (NULL, NULL), (2, true)` | `CAST_INVALID_INPUT` |
   | 2 | `(0, true), (NULL, NULL), (2, false)` | `CAST_INVALID_INPUT` |
   
   The Comet column is what the code at 0fec45f232 does by my reading. I have 
not run these queries. `logical_lazy_ansi.sql` from #7326 has the first shape 
with `WHERE v IS NOT NULL`, which leaves out the NULL row.
   
   ### Expected behavior
   
   The same results as Spark: no candidate is evaluated on a row whose value is 
NULL, and none after the first match.
   
   ### Additional context
   
   #7246 lists `IN` among the operators that evaluate operands Spark skips. Its 
`IN` case, with a value that is never NULL and cannot fail, is fixed by #7326. 
#7468 is the same shape for comparisons whose left operand is NULL. This came 
up in review of #7322, whose `shortCircuitSafe` keeps lambdas that use `IN` on 
the dispatcher.
   
   A possible direction: evaluate the value once and build the chain over that 
column, so that a value that can fail no longer disables the rewrite, and skip 
the candidates that can fail on rows where the value is NULL, where Spark's 
`In` returns NULL.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to