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]

Reply via email to