Cheng Wang created SPARK-60502:
----------------------------------
Summary: [SQL] PullupCorrelatedPredicates ignores non-null
empty-input default of COALESCE(SUM(...), k)
Key: SPARK-60502
URL: https://issues.apache.org/jira/browse/SPARK-60502
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
h3. What happened?
mayHaveCountBugAgg (subquery.scala:488-513) inspects only
AggregateExpression.aggregateFunction.defaultResult.
For SUM, defaultResult is None, but the enclosing expression COALESCE(SUM(b.v),
10) evaluates to non-null 10 on empty input.
Because mayHaveCountBugAgg returns false, the domain join is skipped and outer
rows with no match produce zero rows, causing an enclosing SUM to return NULL
instead of 10.
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 SUM(s)
FROM (SELECT COALESCE(SUM(b.v), 10) AS s FROM b WHERE b.k = a.k) sub
) AS res
FROM a
ORDER BY a.k;
{code}
h3. Expected vs Actual
For k = 2:
* Expected: res = 10 (empty input -> SUM is NULL -> COALESCE evaluates to 10 ->
outer SUM is 10)
* Actual in Spark 4.0.0 / master: res = NULL (silent wrong result)
h3. Suggested Fix
Inspect projections above the Aggregate for non-null defaults on null inputs
(e.g., COALESCE, IFNULL, CASE WHEN IS NULL), or reuse
RewriteCorrelatedScalarSubquery.evalAggregateOnZeroTups.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
