[
https://issues.apache.org/jira/browse/SPARK-58429?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-58429:
-----------------------------------
Labels: correctness pull-request-available (was: correctness)
> 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
> Priority: Major
> Labels: correctness, pull-request-available
>
> 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]
