[
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]