[ 
https://issues.apache.org/jira/browse/SPARK-61484?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated SPARK-61484:
-----------------------------------
    Labels: pull-request-available  (was: )

> Correlated subqueries over a ROLLUP/CUBE/GROUPING SETS grouping key keep 
> referencing the pre-Expand attribute, returning wrong results in 
> super-aggregate rows and failing in HAVING
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: SPARK-61484
>                 URL: https://issues.apache.org/jira/browse/SPARK-61484
>             Project: Spark
>          Issue Type: Bug
>          Components: Optimizer, SQL
>    Affects Versions: 3.5.0, 5.0.0
>            Reporter: Avery Qi
>            Priority: Major
>              Labels: pull-request-available
>
> ResolveGroupingAnalytics lowers a grouping-analytics Aggregate into 
> Aggregate(Expand(...)). The Expand mints a fresh, nullable attribute for 
> every grouping key (col1#0 -> col1#3), and the aggregate list is rewritten to 
> the new attributes. A correlated subquery in the aggregate list or in HAVING 
> is not rewritten: its OuterReference nodes and the subquery's outerAttrs keep 
> pointing at the pre-Expand attribute col1#0, which the Expand passes through 
> unchanged and which is never NULL in a super-aggregate row.
> 1. Wrong result: correlated EXISTS in the SELECT list
> ```
> SELECT col1, SUM(col2) AS s,
>        EXISTS (SELECT 1 FROM VALUES (1), (2) AS v.x WHERE v.x = col1) AS 
> has_match
> FROM VALUES (1, 10), (1, 20), (2, 30) AS t(col1, col2)
> GROUP BY ROLLUP(col1)
> ORDER BY col1 NULLS LAST;
> ```
> Expected: (1, 30, true), (2, 30, true), (NULL, 60, false). The grand-total 
> row must evaluate the subquery with col1 = NULL.
> Analyzed plan today (the subquery still references outer(col1#0), the 
> pre-Expand input, instead of the grouping attribute col1#3 the aggregate 
> groups by):
> ```
> Sort [col1#3 ASC NULLS LAST|#3 ASC NULLS LAST], true
> +- Aggregate [col1#3, spark_grouping_id#2L|#3, spark_grouping_id#2L], 
> [col1#3, sum(col2#1) AS s#4L, exists#5 [col1#0] AS has_match#6|#3, 
> sum(col2#1) AS s#4L, exists#5 [col1#0] AS has_match#6]
>    :  +- Project [1 AS 1#7|#7]
>    :     +- Filter (x#8 = outer(col1#0))
>    :        +- LocalRelation [x#8|#8]
>    +- Expand [[col1#0, col2#1, col1#1, 0|#0, col2#1, col1#1, 0], [col1#0, 
> col2#1, null, 1|#0, col2#1, null, 1]], [col1#0, col2#1, col1#3, 
> spark_grouping_id#2L|#0, col2#1, col1#3, spark_grouping_id#2L]
>       +- Project [col1#0, col2#1, col1#0 AS col1#1|#0, col2#1, col1#0 AS 
> col1#1]
>          +- LocalRelation [col1#0, col2#1|#0, col2#1]
> ```
> PullupCorrelatedPredicates then finds col1#0 referenced by the subquery but 
> not among the grouping expressions and appends it to them, so the grand-total 
> row is evaluated against the never-null pre-Expand value (and no longer 
> aggregates over all rows). With spark.sql.planChangeValidation on (the 
> default in tests) the same query fails instead with 
> PLAN_VALIDATION_FAILED_RULE_IN_BATCH ... [MISSING_AGGREGATION] The 
> non-aggregating expression "x" ..., which is what the group-analytics.sql 
> golden file currently records for this exact query (added by SPARK-57143).
> Expected analyzed plan:
> ```
> Sort [col1#3 ASC NULLS LAST|#3 ASC NULLS LAST], true
> +- Aggregate [col1#3, spark_grouping_id#2L|#3, spark_grouping_id#2L], 
> [col1#3, sum(col2#1) AS s#4L, exists#5 [col1#3] AS has_match#6|#3, 
> sum(col2#1) AS s#4L, exists#5 [col1#3] AS has_match#6]
>    :  +- Project [1 AS 1#7|#7]
>    :     +- Filter (x#8 = outer(col1#3))
>    :        +- LocalRelation [x#8|#8]
>    +- Expand [[col1#0, col2#1, col1#1, 0|#0, col2#1, col1#1, 0], [col1#0, 
> col2#1, null, 1|#0, col2#1, null, 1]], [col1#0, col2#1, col1#3, 
> spark_grouping_id#2L|#0, col2#1, col1#3, spark_grouping_id#2L]
>       +- Project [col1#0, col2#1, col1#0 AS col1#1|#0, col2#1, col1#0 AS 
> col1#1]
>          +- LocalRelation [col1#0, col2#1|#0, col2#1]
> ```
> 2. Wrong result: a scalar subquery that is itself the grouping key
> ```
> SELECT (SELECT max(x) FROM VALUES (7) AS cfg(x)) AS v, count(*) AS c
> FROM VALUES (1), (2), (3) AS t(id)
> GROUP BY ROLLUP((SELECT max(x) FROM VALUES (7) AS cfg(x)));
> ```
> Expected (7, 3), (NULL, 3). The grand-total row shows (7, 3): the subquery in 
> the aggregate list is not recognised as the grouping key, so it is kept as-is 
> instead of being replaced by the nullable grouping attribute like any other 
> grouping expression.
> 3. Failure: correlated subquery in HAVING
> ```
> SELECT col1, count(*) AS c
> FROM VALUES (1, 10), (1, 20), (2, 30) AS t(col1, col2)
> GROUP BY ROLLUP(col1)
> HAVING NOT EXISTS (SELECT 1 FROM VALUES (1) AS v(x) WHERE v.x = col1);
> ```
> Expected (2, 1), (NULL, 3). tryResolveHavingCondition resolves the HAVING 
> condition against the pre-Expand aggregate and then rebuilds the aggregate 
> over Expand; it remaps plain attribute references in the condition through 
> the new output but not the outer references inside the subquery, so 
> outer(col1#0) points at an attribute the rebuilt aggregate no longer produces 
> and the query fails during subquery decorrelation. The same happens when the 
> key is selected under an alias (SELECT col1 AS k ... HAVING NOT EXISTS (... = 
> col1)) and when the key is not in the SELECT list (SELECT col2 ... GROUP BY 
> ROLLUP(col1, col2) HAVING ... = col1). The single-pass resolver has the 
> aliased variant of the same problem: it registers the aggregate-list aliases 
> before the aggregate is rebuilt over Expand, so a post-Expand outer reference 
> never matches the alias.
> Validation gap
> Independently of grouping analytics, ExprUtils.assertValidAggregation rejects 
> any correlated EXISTS/IN in an aggregate's SELECT list once 
> PullupCorrelatedPredicates has run, because the subquery's joinConds 
> reference the subquery plan's own attributes, which are never grouping 
> expressions. That makes even the plain GROUP BY col1 version of query 1 fail 
> with MISSING_AGGREGATION under spark.sql.planChangeValidation. The check 
> should look only at the subquery's outerAttrs.



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to