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

Reply via email to