Repository navigation
Native OR, AND, IN, concat, NULL operands and later top-K sort keys evaluate operands Spark skips, raising ANSI errors #7246
Description
Describe the bug
With ANSI mode on, several native expressions evaluate an operand on rows where Spark never evaluates it. A query that succeeds in Spark then fails in Comet with CAST_INVALID_INPUT or ARITHMETIC_OVERFLOW.
Spark evaluates these operands per row, and only when it needs them. It evaluates the right side of OR and AND only when the left side doesn't decide the result, and later IN candidates only until one matches. The right operand of + is evaluated only when the left is not NULL, and the numerator of DIV only when the divisor is not NULL. With Spark's default generated code, later concat arguments are evaluated only until one is NULL. Later sort keys of a top-K are evaluated only when the earlier keys tie. Comet evaluates all of them over the whole batch.
This is a focused follow-up under #6006. #7147 covers the related case of rows skipped by a LIMIT.
Steps to reproduce
spark.conf.set("spark.sql.ansi.enabled", "true")
spark.conf.set("spark.sql.adaptive.enabled", "false")
Seq((1, "bad"), (2, "2")).toDF("id", "txt").repartition(1).write.parquet("/tmp/items")
Seq[(Int, Option[Int], String)]((1, None, "bad"), (2, None, "2"))
.toDF("id", "n", "txt").repartition(1).write.parquet("/tmp/items2")
Seq((1, Int.MaxValue), (2, 5), (3, Int.MaxValue), (4, 7), (5, 9))
.toDF("a", "b").repartition(1).write.parquet("/tmp/topk")
Seq("items", "items2", "topk").foreach(t =>
spark.read.parquet(s"/tmp/$t").createOrReplaceTempView(t))Each table is a single Parquet file, so its rows arrive in one batch.
| Query | Spark | Comet |
|---|---|---|
SELECT id, id = 1 OR CAST(txt AS INT) = 2 FROM items |
(1, true), (2, true) |
CAST_INVALID_INPUT |
SELECT id, id <> 1 AND CAST(txt AS INT) = 2 FROM items |
(1, false), (2, true) |
CAST_INVALID_INPUT |
SELECT id, id IN (1, CAST(txt AS INT)) FROM items |
(1, true), (2, true) |
CAST_INVALID_INPUT |
SELECT id, n + CAST(txt AS INT) FROM items2 |
(1, NULL), (2, NULL) |
CAST_INVALID_INPUT |
SELECT id, CAST(txt AS INT) DIV n FROM items2 |
(1, NULL), (2, NULL) |
CAST_INVALID_INPUT |
SELECT id, concat(IF(id = 1, CAST(NULL AS STRING), CAST(id AS STRING)), CAST(CAST(txt AS INT) AS STRING)) FROM items |
(1, NULL), (2, '22') |
CAST_INVALID_INPUT |
SELECT a, b FROM topk ORDER BY a, b + 1 LIMIT 2 |
(1, 2147483647), (2, 5) |
ARITHMETIC_OVERFLOW |
The plans are fully native (CometProject or CometTakeOrderedAndProject over CometNativeScan). The same failures happen when a literal is replaced by a plain scalar subquery, for example id = (SELECT max(k) FROM nums) OR CAST(txt AS INT) = 2.
Reproduced on main at c4a0b1f with the default Spark 4.1 profile.
Expected behavior
The same rows as Spark, or a fallback to Spark for these shapes.
Additional context
#5889 makes these shapes fall back when the subquery is a merged struct subquery, which protects only that form. A fix here would cover columns, literals and plain subqueries too.
Activity
tlfua commentedon Nov 3, 2026
take
tlfua commentedon Nov 3, 2026
I re-ran the reproductions on current
main(644a870, default Spark 4.1 profile, ANSI on, AQE off). Each query was its ownCometSqlFileTestSuitefixture, so every row arrives in one batch, as in the original report.mainid = 1 OR CAST(txt AS INT) = 2CometProjectid <> 1 AND CAST(txt AS INT) = 2CometProjectid IN (1, CAST(txt AS INT))CometProjectid = (SELECT max(k) FROM nums) OR CAST(txt AS INT) = 2CometProjectn + CAST(txt AS INT)(nNULL)CAST_INVALID_INPUTCometProjectCAST(txt AS INT) DIV n(nNULL)CAST_INVALID_INPUTCometProjectconcat(IF(id = 1, NULL, ...), CAST(CAST(txt AS INT) AS STRING))CAST_INVALID_INPUTCometProjectORDER BY a, b + 1 LIMIT 2ARITHMETIC_OVERFLOWCometTakeOrderedAndProjectExecAll plans are fully native over
CometNativeScan.The
OR,ANDandINcases, including the scalar subquery form, are fixed by #7326 (LazyLogical/lazy_in_list), which landed after thec4a0b1f2bcthe issue was reproduced on.What remains:
+,DIV, and presumably-,*,/,%) andconcat. Spark skips the later operand on rows where an earlier one is NULL. The existingNullShortCircuit/withNullShortCircuit/canSkipEvaluationmachinery seems to fit, gated on the skipped operand being able to fail.I plan to take these as two PRs, (1) first. For top-K, would you prefer a lazy native evaluation of the later keys, or a fallback to Spark when ANSI is on and a later sort key can fail?