[
https://issues.apache.org/jira/browse/SPARK-60501?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-60501:
-----------------------------------
Labels: pull-request-available (was: )
> [SQL] PullupCorrelatedPredicates returns 0 instead of 1 for COUNT(*) over a
> correlated scalar aggregate
> --------------------------------------------------------------------------------------------------------
>
> Key: SPARK-60501
> URL: https://issues.apache.org/jira/browse/SPARK-60501
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 4.0.0
> Environment: macOS / Linux, PySpark / Spark 4.0.0 and master branch
> Reporter: Cheng Wang
> Priority: Major
> Labels: pull-request-available
>
> h3. What happened?
> PullupCorrelatedPredicates.rewriteSubQueries uses mayHaveCountBugAgg
> (sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala:488-513)
> to determine if a correlated scalar aggregate needs count-bug domain join
> handling:
> {code:scala}
> def mayHaveCountBugAgg(a: Aggregate): Boolean = {
> a.groupingExpressions.isEmpty && a.aggregateExpressions.exists(_.exists {
> case a: AggregateExpression => a.aggregateFunction.defaultResult.isDefined
> case _ => false
> })
> }
> {code}
> defaultResult.isDefined is true only for COUNT-like functions. However, ANY
> ungrouped aggregate (including MAX, MIN, SUM, AVG) always produces exactly
> one row on empty input (evaluating to NULL).
> When mayHaveCountBugAgg returns false, the inner aggregate is decorrelated
> without the LeftOuter domain join, so non-matching outer keys produce zero
> rows instead of one row (NULL). An enclosing COUNT(*) then counts 0 instead
> of 1.
> h3. To Reproduce (PySpark 4.0.0 / master)
> {code:sql}
> CREATE OR REPLACE TEMP VIEW a AS SELECT * FROM VALUES (1, 10), (2, 20) AS
> a(k, x);
> CREATE OR REPLACE TEMP VIEW b AS SELECT * FROM VALUES (1, 100), (1, 200) AS
> b(k, v);
> SELECT a.k, (
> SELECT COUNT(*)
> FROM (SELECT MAX(b.v) AS m FROM b WHERE b.k = a.k) sub
> ) AS cnt
> FROM a
> ORDER BY a.k;
> {code}
> h3. Expected vs Actual
> For k = 2, table b has no matching rows. The scalar subquery SELECT MAX(b.v)
> FROM b WHERE b.k = 2 produces 1 row: (NULL). The outer COUNT(*) over this 1
> row must return 1.
> * Expected:
> {noformat}
> +---+---+
> | k|cnt|
> +---+---+
> | 1| 1|
> | 2| 1|
> +---+---+
> {noformat}
> * Actual in Spark 4.0.0 / master:
> {noformat}
> +---+---+
> | k|cnt|
> +---+---+
> | 1| 1|
> | 2| 0| <-- Silent Wrong Result
> +---+---+
> {noformat}
> h3. Root Cause
> mayHaveCountBugAgg checks aggregateFunction.defaultResult.isDefined instead
> of checking whether the aggregate is ungrouped. An ungrouped aggregate always
> evaluates to 1 row regardless of whether the function returns NULL or a
> default value.
> h3. Suggested Fix
> Treat every ungrouped subquery aggregate as vulnerable, or check whether the
> parent expression is sensitive to row existence (such as COUNT(*)).
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]