[I] Wrong results from `IN` when a list item is a `CASE` that reads a column [datafusion]

["ranflarion (via GitHub)" <gi...@apache.org>]

ranflarion opened a new issue, #26082:
URL: https://github.com/apache/datafusion/issues/26082

   ### Describe the bug
   
   `InListExpr::try_new` treats a list as constant when every item evaluates to a scalar on an empty batch (`try_evaluate_constant_list` in `datafusion/physical-expr/src/expressions/in_list.rs`), and from then on compares every row against those precomputed values through the static filter. A scalar on zero rows does not make an item constant: `CaseExpr::expr_or_expr` returns its THEN branch unbroadcast whenever the WHEN mask has no false and no null, which a zero-length mask satisfies. So `CASE WHEN d > 5 THEN 1 ELSE d END * 5` evaluates to the scalar `5` on the empty batch and is frozen into the static filter, whatever `d` holds on each row.
   
   ### To Reproduce
   
   ```sql
   CREATE TABLE t (n INT, d INT) AS VALUES (2, 0), (10, 2), (5, 2), (7, 1);
   
   SELECT n, n IN (2, 100, 101, CASE WHEN d > 5 THEN 1 ELSE d END * 5) AS r FROM t ORDER BY n;
   
   SELECT n FROM t WHERE n IN (2, 100, 101, CASE WHEN d > 5 THEN 1 ELSE d END * 5) ORDER BY n;
   ```
   
   The list has four items because `ShortenInListSimplifier` rewrites a list of three or fewer over a plain column into `OR`ed equalities, which never reach `InListExpr`.
   
   On main (264ee3dc6) the projection returns `(2, true), (5, true), (7, false), (10, false)` and the filter returns `2, 5`: every row is compared against `(2, 100, 101, 5)`.
   
   ### Expected behavior
   
   The CASE item is 0, 10, 10 and 5 on the four rows, so the projection should return `(2, true), (5, false), (7, false), (10, true)` and the filter `2, 10`.
   
   ### Additional context
   
   Found running DataFusion under DataFusion Comet, which plans Spark's `if` and `nullif` as `CaseExpr`, so Spark queries such as `n IN (2, nullif(d, 0) * 5)` returned wrong results. The same detection and fast path are in 54.1.0 and 55.1.0.
   
   Any item whose leaves read the input can be frozen this way, not only through a `Column` (a `LambdaVariable` reads it too), so I'd propose building the static filter only when every leaf of every item is a `Literal`. PR to follow.
   


-- 
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: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Wrong results from `IN` when a list item is a `CASE` that reads a column [datafusion]

["alamb (via GitHub)" <gi...@apache.org>]

alamb closed issue #26082: Wrong results from `IN` when a list item is a `CASE` that reads a column
URL: https://github.com/apache/datafusion/issues/26082


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org