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]
