[SPARK-39198] Cannot refer to nested CTE within a nested CTE in a subquery.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.2.1, 3.3.0
• Component/s: SQL

Environment

Tested on

• Databricks runtime 10.4
• Spark 3.2.1 from https://spark.apache.org/downloads.html
• GitHub apache/spark 'master' commit 17b85ff9

• Assignee: Unassigned
• Reporter: Jarno Rajala
• Created: 16/May/22 17:39
• Updated: 22/Feb/23 10:24
• Resolved: 22/Feb/23 10:24

Description

The following query fails with Table or view not found: cte1;

set spark.sql.legacy.ctePrecedencePolicy=CORRECTED;
with
cte1 as (select 1)
select * from (
  with
    cte2 as (select * from cte1)
    select * from cte2
); 

Or Spark 3.1.1 it returns 1 as expected.

This is related to SPARK-38404, but different, since the query fails with Spark built from 'master' (commit 17b85ff9). The PR #36146 therefore does not fix this issue.