Josh Rosen created SPARK-58429:
----------------------------------
Summary: Uncorrelated IN-subquery selected with a global aggregate
returns false/NULL instead of true when the input is empty
Key: SPARK-58429
URL: https://issues.apache.org/jira/browse/SPARK-58429
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.0.0
Reporter: Josh Rosen
This is a report of a silent wrong-results bug present in Spark 4.0.0 and later
(introduced by SPARK-47070), under default configuration.
When an uncorrelated {{{}IN{}}}/{{{}EXISTS{}}} subquery predicate is selected
alongside a global aggregate, {{RewritePredicateSubquery}} plans an
{{ExistenceJoin}} *below* the {{Aggregate}} and wraps the resulting {{exists}}
attribute in {{{}first(){}}}, on the
[rationale|https://github.com/apache/spark/blob/8df89f20fc5c800ef4935c1b49a7525ec9df8921/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala#L356-L357]
that "the value of `exists` is functionally determined by grouping
expressions, so applying any aggregate function is semantically safe."
That rationale is vacuous for a {*}global aggregate over empty input{*}: it
produces its single output row from zero input rows, so there is no row for
{{first()}} to take a value from, and {{first()}} returns its initial (null)
buffer.
h3. Repro (default configuration):
{code:java}
SELECT count(*), 1 IN (SELECT id FROM range(1, 2)) FROM range(0);
-- outputs: 0, false WRONG (expected: 0, true) {code}
* {{range(1, 2)}} is the one-element set {{{}{1}{}}},
* {{SELECT 1 IN (SELECT id FROM range(1, 2))}} returns {{true}} on its own;
* {{range(0)}} is empty, so {{count(*)}} is 0 and the predicate's value does
not depend on the outer input at all.
* PostgreSQL and DuckDB return {{true}} here. The same query over a non-empty
outer input returns {{true}} correctly, so it is specifically the empty case
that is buggy.
Here is the incorrectly-optimized plan for this query:
{code:java}
Aggregate [count(1) AS count(1), first(exists#, false) AS (1 IN (listquery()))]
+- Join ExistenceJoin(exists#)
:- Range (0, 0, step=1)
+- Project
+- Filter (1 = id#)
+- Range (1, 2, step=1) {code}
Potential fix: for a global aggregate (empty {{{}groupingExpressions{}}}),
rewrite the {{ExistenceJoin}} _above_ the {{Aggregate}} so {{exists}} is
evaluated on the aggregate's single output row.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
