Avery Qi created SPARK-61484:
--------------------------------
Summary: 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
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.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]