[
https://issues.apache.org/jira/browse/SPARK-61484?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Avery Qi updated SPARK-61484:
-----------------------------
Description:
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.
was:
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], true
+- Aggregate [col1#3, spark_grouping_id#2L], [col1#3, sum(col2#1) AS s#4L,
exists#5 [col1#0] AS has_match#6]
: +- Project [1 AS 1#7]
: +- Filter (x#8 = outer(col1#0))
: +- LocalRelation [x#8]
+- Expand [[col1#0, col2#1, col1#1, 0], [col1#0, col2#1, null, 1]], [col1#0,
col2#1, col1#3, spark_grouping_id#2L]
+- Project [col1#0, col2#1, col1#0 AS col1#1]
+- LocalRelation [col1#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], true
+- Aggregate [col1#3, spark_grouping_id#2L], [col1#3, sum(col2#1) AS s#4L,
exists#5 [col1#3] AS has_match#6]
: +- Project [1 AS 1#7]
: +- Filter (x#8 = outer(col1#3))
: +- LocalRelation [x#8]
+- Expand [[col1#0, col2#1, col1#1, 0], [col1#0, col2#1, null, 1]], [col1#0,
col2#1, col1#3, spark_grouping_id#2L]
+- Project [col1#0, col2#1, col1#0 AS col1#1]
+- LocalRelation [col1#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.
> 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
>
> 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]