You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
ANSI: native IN evaluates candidates that Spark skips when the value is NULL or can fail #7675
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, the same in 3.4.3, 3.5.8 and 4.0.1).
#7326 made IN lazy: lazy_in_list rewrites v IN (c1, c2, ...) as v IN (c1) OR v = c2 OR ... over LazyLogical. Two cases still evaluate a candidate that Spark skips:
The value is NULL. For OR, LazyLogical evaluates the right operand on every row where the left one is not true, NULL included (pending). On a row whose value is NULL, v IN (c1) is NULL, so v = c2 runs and evaluates c2.
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), which evaluates every candidate over the whole batch.
Steps to reproduce
With spark.sql.ansi.enabled=true:
CREATETABLElazy_in(v int, s string) USING parquet;
INSERT INTO lazy_in SELECT/*+ REPARTITION(1) */*FROMVALUES
(0, 'bad'), (NULL, 'bad'), (2, '2') AS t(v, s);
-- 1. The value is NULL on the second rowSELECT v, v IN (0, CAST(s ASINT)) FROM lazy_in;
-- 2. The value can fail, so the lazy rewrite does not applySELECT v, (v +1) IN (1, CAST(s ASINT)) 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 0fec45f 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.
Activity