[
https://issues.apache.org/jira/browse/SPARK-60874?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihailo Aleksic updated SPARK-60874:
------------------------------------
Description:
The single-pass resolver validates a subquery expression
(ValidateSubqueryExpression) before CTEs are inlined, while the fixed-point
analyzer validates only after InlineCTE in CheckAnalysis. A \{{CTERelationRef}}
inside the subquery is treated as an opaque leaf,
so the validation does not see the aggregate or the correlated predicates in
the CTE body.
As a result, the single-pass resolver disagrees with the fixed-point analyzer
in both directions.
It rejects a legal correlated scalar subquery with
\{{UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.MUST_AGGREGATE_CORRELATED_SCALAR_SUBQUERY}},
which fixed point accepts:
{code:sql}
SELECT *, (WITH cte AS (SELECT y1, count(*) AS cnt FROM y WHERE y1 = x1 GROUP
BY y1)
SELECT cnt FROM cte) FROM x;
{code}
It accepts an illegal correlation, which fixed point rejects with
\{{UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED}}
(the correlated predicate is on the right side of a left join):
{code:sql}
SELECT *, (WITH cte AS (SELECT * FROM y WHERE y1 = x1)
SELECT count(*) FROM z LEFT JOIN cte ON z1 = y1) FROM x;
{code}
In tests, where both analyzers run, these cases fail with
\{{HYBRID_ANALYZER_EXCEPTION.SINGLE_PASS_FAILED_FIXED_POINT_SUCCEEDED}} and
\{{HYBRID_ANALYZER_EXCEPTION.FIXED_POINT_FAILED_SINGLE_PASS_SUCCEEDED}}.
Expected: ValidateSubqueryExpression follows CTE references into the
subquery's own CTE definitions, so the single-pass resolver accepts and rejects
the same queries as the fixed-point analyzer.
> Look through CTE references when validating correlated scalar subqueries in
> the single-pass resolver
> ----------------------------------------------------------------------------------------------------
>
> Key: SPARK-60874
> URL: https://issues.apache.org/jira/browse/SPARK-60874
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 4.2.0
> Reporter: Mihailo Aleksic
> Priority: Major
>
> The single-pass resolver validates a subquery expression
> (ValidateSubqueryExpression) before CTEs are inlined, while the fixed-point
> analyzer validates only after InlineCTE in CheckAnalysis. A
> \{{CTERelationRef}} inside the subquery is treated as an opaque leaf,
> so the validation does not see the aggregate or the correlated predicates
> in the CTE body.
>
> As a result, the single-pass resolver disagrees with the fixed-point
> analyzer in both directions.
>
> It rejects a legal correlated scalar subquery with
> \{{UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.MUST_AGGREGATE_CORRELATED_SCALAR_SUBQUERY}},
> which fixed point accepts:
> {code:sql}
> SELECT *, (WITH cte AS (SELECT y1, count(*) AS cnt FROM y WHERE y1 = x1
> GROUP BY y1)
> SELECT cnt FROM cte) FROM x;
> {code}
>
> It accepts an illegal correlation, which fixed point rejects with
> \{{UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED}}
> (the correlated predicate is on the right side of a left join):
> {code:sql}
> SELECT *, (WITH cte AS (SELECT * FROM y WHERE y1 = x1)
> SELECT count(*) FROM z LEFT JOIN cte ON z1 = y1) FROM x;
> {code}
>
> In tests, where both analyzers run, these cases fail with
> \{{HYBRID_ANALYZER_EXCEPTION.SINGLE_PASS_FAILED_FIXED_POINT_SUCCEEDED}} and
> \{{HYBRID_ANALYZER_EXCEPTION.FIXED_POINT_FAILED_SINGLE_PASS_SUCCEEDED}}.
>
> Expected: ValidateSubqueryExpression follows CTE references into the
> subquery's own CTE definitions, so the single-pass resolver accepts and
> rejects the same queries as the fixed-point analyzer.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
