[SPARK-28297] Handling outer links in CTE subquery expressions

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Peter Toth
• Created: 08/Jul/19 10:07
• Updated: 29/Mar/20 02:24

Description

This query does work in Spark SQL:

select (with cte(x) as (select 1) select x from cte) from test10

But the following query can't handle the outer reference to the c column of test10 table:

select (with cte(x) as (select c) select x from cte) from test10

org.apache.spark.sql.AnalysisException: Expressions referencing the outer query are not supported outside of WHERE/HAVING clauses:
Project [outer(c#41)]
+- OneRowRelation
;;
Project [scalar-subquery#167 [] AS scalarsubquery()#169]
:  +- Project [x#168]
:     +- SubqueryAlias `cte`
:        +- Project [c#41 AS x#168]
:           +- Project [outer(c#41)]
:              +- OneRowRelation
+- SubqueryAlias `default`.`test10`
   +- Relation[c#41] parquet

where test10:

CREATE TABLE test10 (c INTEGER);
INSERT INTO test10 SELECT generate_sequence(1, 10);