[
https://issues.apache.org/jira/browse/SPARK-60315?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Max Gekk updated SPARK-60315:
-----------------------------
Description:
The same query raises an error with whole-stage codegen off and returns rows
with it on. It happens when a filter's condition is a conjunction, an earlier
conjunct is NULL on a row, and a later conjunct raises on that row.
h3. Reproducer
Default (ANSI) mode, a Parquet table, local mode:
{code:sql}
CREATE TABLE t USING parquet AS
SELECT * FROM VALUES (NULL, 1, 2147483647), (0, 1, 0) AS v(a, b, y);
SET spark.sql.codegen.wholeStage=true;
SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0;
-- 0
SET spark.sql.codegen.wholeStage=false;
SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0;
-- [ARITHMETIC_OVERFLOW] overflow. Use 'try_add' to tolerate overflow and
return NULL instead. ... SQLSTATE: 22003
{code}
The same happens in legacy mode with year-month interval arithmetic, which
raises on overflow in every mode:
{code:sql}
SET spark.sql.ansi.enabled=false;
CREATE TABLE t2 USING parquet AS
SELECT * FROM VALUES (NULL, 1, 178956970), (0, 1, 0) AS v(a, b, y);
SELECT a FROM t2 WHERE NOT (a > 0 AND b > 0) AND make_ym_interval(y, 0) +
INTERVAL '1' YEAR > INTERVAL '0' YEAR;
-- codegen on: 0
-- codegen off: [INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION] Integer overflow
while operating with intervals. ... SQLSTATE: 22015
{code}
On the first row, {{NOT (a > 0 AND b > 0)}} is {{NOT (NULL AND TRUE)}}, which
is NULL, and the second conjunct overflows. The second row keeps the Parquet
row group from being skipped by its statistics; it passes both conjuncts on
both paths. {{NOT (a > 0 AND b > 0)}} is written so that the optimizer infers
no {{isnotnull}} filter that would drop the first row earlier on both paths.
Checked on master at 5d17442dec4 (8 October 2026) with {{bin/spark-sql --master
local[1]}}.
h3. Cause
The two paths evaluate the conjunction differently:
* *Whole-stage codegen* ({{FilterExec.generatePredicateCode}}) generates each
top-level conjunct separately and skips the row as soon as one is false *or
null* ({{if (isNull || !value) continue;}}). A filter keeps only rows whose
condition is true, so the remaining conjuncts are never evaluated on that row.
* *Interpreted* ({{FilterExec.doExecute}} without codegen, via
{{Predicate.create}} over the whole condition) evaluates the {{And}} tree with
{{And.eval}}, which short-circuits only on {{false}}. A null left side still
evaluates the right side, because {{NULL AND FALSE}} is false and {{NULL AND
TRUE}} is null, and that evaluation raises.
Both are valid evaluations of {{AND}} in isolation. Inside a filter, though,
false and null both drop the row, so the distinction the interpreted path
computes the right side for is never used.
h3. Expected
The query should behave the same with whole-stage codegen on and off. The SQL
standard's text points to the interpreted path's result, the error:
* *WHERE* (ISO/IEC 9075-2, 7.11 <where clause>, General Rule 2): "The <search
condition> is effectively evaluated for each row of T. The result of the <where
clause> is a table of those rows of T for which the result of the <search
condition> is True."
* *AND* (9075-2, 6.35 <boolean value expression>, Table 11): Unknown AND True
is Unknown, and Unknown AND False is False. With the first conjunct Unknown,
the value of the search condition depends on the second conjunct.
* *Overflow* (9075-2, 6.27 <numeric value expression>, General Rule 5a): an
exact numeric result that is not exactly representable raises "data exception —
numeric value out of range" (SQLSTATE 22003); interval arithmetic raises "data
exception — interval field overflow" (22015, 6.33).
* *Which parts may be skipped* (9075-1, 6.3.3.3 "Rule evaluation order"; SQL-92
3.3.4.4): "If the result of an expression or search condition can be determined
without completely evaluating all parts of the expression or search condition,
then the parts of the expression or search condition whose evaluation is not
necessary are called the inessential parts. [...] If evaluation of the
inessential parts would cause an exception condition to be raised, then it is
implementation-dependent whether or not that exception condition is raised."
The same subclause makes the order of evaluation implementation-dependent,
"particularly when operands or operators might cause conditions to be raised".
The second conjunct is not an inessential part of the search condition: without
it the result could be False or Unknown. So the standard has it evaluated, and
its exception raised. Skipping it, as whole-stage codegen does, relies on the
fact that a WHERE clause drops both False and Unknown rows, but the standard
defines inessential parts by the result of the search condition, not by the
effect of the clause.
Read this way, the codegen path should keep evaluating the remaining conjuncts
after a NULL one when they can raise, and stop early only on FALSE. The
alternative, the interpreted path stopping at the first NULL conjunct as
codegen does, keeps today's default behaviour but departs from the standard's
text. Either way the two paths should agree.
(Sources: 9075-2 as the 2013 committee draft of the 2016 edition; the 9075-1
rule as worded in SQL-92, 3.3.4.4, since Part 1's current text was not to hand.)
Found by differential testing of random queries against Spark's own row engine.
was:
The same query raises an error with whole-stage codegen off and returns rows
with it on. It happens when a filter's condition is a conjunction, an earlier
conjunct is NULL on a row, and a later conjunct raises on that row.
h3. Reproducer
Default (ANSI) mode, a Parquet table, local mode:
{code:sql}
CREATE TABLE t USING parquet AS
SELECT * FROM VALUES (NULL, 1, 2147483647), (0, 1, 0) AS v(a, b, y);
SET spark.sql.codegen.wholeStage=true;
SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0;
-- 0
SET spark.sql.codegen.wholeStage=false;
SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0;
-- [ARITHMETIC_OVERFLOW] overflow. Use 'try_add' to tolerate overflow and
return NULL instead. ... SQLSTATE: 22003
{code}
The same happens in legacy mode with year-month interval arithmetic, which
raises on overflow in every mode:
{code:sql}
SET spark.sql.ansi.enabled=false;
CREATE TABLE t2 USING parquet AS
SELECT * FROM VALUES (NULL, 1, 178956970), (0, 1, 0) AS v(a, b, y);
SELECT a FROM t2 WHERE NOT (a > 0 AND b > 0) AND make_ym_interval(y, 0) +
INTERVAL '1' YEAR > INTERVAL '0' YEAR;
-- codegen on: 0
-- codegen off: [INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION] Integer overflow
while operating with intervals. ... SQLSTATE: 22015
{code}
On the first row, {{NOT (a > 0 AND b > 0)}} is {{NOT (NULL AND TRUE)}}, which
is NULL, and the second conjunct overflows. The second row keeps the Parquet
row group from being skipped by its statistics; it passes both conjuncts on
both paths. {{NOT (a > 0 AND b > 0)}} is written so that the optimizer infers
no {{isnotnull}} filter that would drop the first row earlier on both paths.
Checked on master at 5d17442dec4 (8 October 2026) with {{bin/spark-sql --master
local[1]}}.
h3. Cause
The two paths evaluate the conjunction differently:
* *Whole-stage codegen* ({{FilterExec.generatePredicateCode}}) generates each
top-level conjunct separately and skips the row as soon as one is false *or
null* ({{if (isNull || !value) continue;}}). A filter keeps only rows whose
condition is true, so the remaining conjuncts are never evaluated on that row.
* *Interpreted* ({{FilterExec.doExecute}} without codegen, via
{{Predicate.create}} over the whole condition) evaluates the {{And}} tree with
{{And.eval}}, which short-circuits only on {{false}}. A null left side still
evaluates the right side, because {{NULL AND FALSE}} is false and {{NULL AND
TRUE}} is null, and that evaluation raises.
Both are valid evaluations of {{AND}} in isolation. Inside a filter, though,
false and null both drop the row, so the distinction the interpreted path
computes the right side for is never used.
h3. Expected
The query should behave the same with whole-stage codegen on and off.
Whole-stage codegen is the default, and its behaviour (no error) is the one
users see, so the interpreted filter should match it: evaluate the top-level
conjuncts in the same order and stop at the first one that is false or null.
The same applies to other interpreted evaluations of a filter condition that
codegen splits into conjuncts.
Found by differential testing of random queries against Spark's own row engine.
> Filter raises an error with whole-stage codegen off but not on when an
> earlier conjunct is null
> -----------------------------------------------------------------------------------------------
>
> Key: SPARK-60315
> URL: https://issues.apache.org/jira/browse/SPARK-60315
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 3.5.8, 4.2.0, 5.0.0, 4.1.3, 4.0.4
> Reporter: Max Gekk
> Priority: Major
>
> The same query raises an error with whole-stage codegen off and returns rows
> with it on. It happens when a filter's condition is a conjunction, an earlier
> conjunct is NULL on a row, and a later conjunct raises on that row.
> h3. Reproducer
> Default (ANSI) mode, a Parquet table, local mode:
> {code:sql}
> CREATE TABLE t USING parquet AS
> SELECT * FROM VALUES (NULL, 1, 2147483647), (0, 1, 0) AS v(a, b, y);
> SET spark.sql.codegen.wholeStage=true;
> SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0;
> -- 0
> SET spark.sql.codegen.wholeStage=false;
> SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0;
> -- [ARITHMETIC_OVERFLOW] overflow. Use 'try_add' to tolerate overflow and
> return NULL instead. ... SQLSTATE: 22003
> {code}
> The same happens in legacy mode with year-month interval arithmetic, which
> raises on overflow in every mode:
> {code:sql}
> SET spark.sql.ansi.enabled=false;
> CREATE TABLE t2 USING parquet AS
> SELECT * FROM VALUES (NULL, 1, 178956970), (0, 1, 0) AS v(a, b, y);
> SELECT a FROM t2 WHERE NOT (a > 0 AND b > 0) AND make_ym_interval(y, 0) +
> INTERVAL '1' YEAR > INTERVAL '0' YEAR;
> -- codegen on: 0
> -- codegen off: [INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION] Integer
> overflow while operating with intervals. ... SQLSTATE: 22015
> {code}
> On the first row, {{NOT (a > 0 AND b > 0)}} is {{NOT (NULL AND TRUE)}}, which
> is NULL, and the second conjunct overflows. The second row keeps the Parquet
> row group from being skipped by its statistics; it passes both conjuncts on
> both paths. {{NOT (a > 0 AND b > 0)}} is written so that the optimizer infers
> no {{isnotnull}} filter that would drop the first row earlier on both paths.
> Checked on master at 5d17442dec4 (8 October 2026) with {{bin/spark-sql
> --master local[1]}}.
> h3. Cause
> The two paths evaluate the conjunction differently:
> * *Whole-stage codegen* ({{FilterExec.generatePredicateCode}}) generates each
> top-level conjunct separately and skips the row as soon as one is false *or
> null* ({{if (isNull || !value) continue;}}). A filter keeps only rows whose
> condition is true, so the remaining conjuncts are never evaluated on that row.
> * *Interpreted* ({{FilterExec.doExecute}} without codegen, via
> {{Predicate.create}} over the whole condition) evaluates the {{And}} tree
> with {{And.eval}}, which short-circuits only on {{false}}. A null left side
> still evaluates the right side, because {{NULL AND FALSE}} is false and
> {{NULL AND TRUE}} is null, and that evaluation raises.
> Both are valid evaluations of {{AND}} in isolation. Inside a filter, though,
> false and null both drop the row, so the distinction the interpreted path
> computes the right side for is never used.
> h3. Expected
> The query should behave the same with whole-stage codegen on and off. The SQL
> standard's text points to the interpreted path's result, the error:
> * *WHERE* (ISO/IEC 9075-2, 7.11 <where clause>, General Rule 2): "The <search
> condition> is effectively evaluated for each row of T. The result of the
> <where clause> is a table of those rows of T for which the result of the
> <search condition> is True."
> * *AND* (9075-2, 6.35 <boolean value expression>, Table 11): Unknown AND True
> is Unknown, and Unknown AND False is False. With the first conjunct Unknown,
> the value of the search condition depends on the second conjunct.
> * *Overflow* (9075-2, 6.27 <numeric value expression>, General Rule 5a): an
> exact numeric result that is not exactly representable raises "data exception
> — numeric value out of range" (SQLSTATE 22003); interval arithmetic raises
> "data exception — interval field overflow" (22015, 6.33).
> * *Which parts may be skipped* (9075-1, 6.3.3.3 "Rule evaluation order";
> SQL-92 3.3.4.4): "If the result of an expression or search condition can be
> determined without completely evaluating all parts of the expression or
> search condition, then the parts of the expression or search condition whose
> evaluation is not necessary are called the inessential parts. [...] If
> evaluation of the inessential parts would cause an exception condition to be
> raised, then it is implementation-dependent whether or not that exception
> condition is raised." The same subclause makes the order of evaluation
> implementation-dependent, "particularly when operands or operators might
> cause conditions to be raised".
> The second conjunct is not an inessential part of the search condition:
> without it the result could be False or Unknown. So the standard has it
> evaluated, and its exception raised. Skipping it, as whole-stage codegen
> does, relies on the fact that a WHERE clause drops both False and Unknown
> rows, but the standard defines inessential parts by the result of the search
> condition, not by the effect of the clause.
> Read this way, the codegen path should keep evaluating the remaining
> conjuncts after a NULL one when they can raise, and stop early only on FALSE.
> The alternative, the interpreted path stopping at the first NULL conjunct as
> codegen does, keeps today's default behaviour but departs from the standard's
> text. Either way the two paths should agree.
> (Sources: 9075-2 as the 2013 committee draft of the 2016 edition; the 9075-1
> rule as worded in SQL-92, 3.3.4.4, since Part 1's current text was not to
> hand.)
> Found by differential testing of random queries against Spark's own row
> engine.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
