From 248922fd4fb7c11a40304431e8cc667a8911a906 Mon Sep 17 00:00:00 2001 From: Liang-Chi Hsieh Date: Wed, 21 Sep 2016 06:53:42 -0700 Subject: [SPARK-17590][SQL] Analyze CTE definitions at once and allow CTE subquery to define CTE ## What changes were proposed in this pull request? We substitute logical plan with CTE definitions in the analyzer rule CTESubstitution. A CTE definition can be used in the logical plan for multiple times, and its analyzed logical plan should be the same. We should not analyze CTE definitions multiple times when they are reused in the query. By analyzing CTE definitions before substitution, we can support defining CTE in subquery. ## How was this patch tested? Jenkins tests. Author: Liang-Chi Hsieh Closes #15146 from viirya/cte-analysis-once. --- .../scala/org/apache/spark/sql/SubquerySuite.scala | 25 ++++++++++++++++++++++ 1 file changed, 25 insertions(+) (limited to 'sql/core/src/test/scala') diff --git a/sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala index 52387b4b72..eab45050f7 100644 --- a/sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala +++ b/sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala @@ -76,6 +76,31 @@ class SubquerySuite extends QueryTest with SharedSQLContext { ) } + test("define CTE in CTE subquery") { + checkAnswer( + sql( + """ + | with t2 as (with t1 as (select 1 as b, 2 as c) select b, c from t1) + | select a from (select 1 as a union all select 2 as a) t + | where a = (select max(b) from t2) + """.stripMargin), + Array(Row(1)) + ) + checkAnswer( + sql( + """ + | with t2 as (with t1 as (select 1 as b, 2 as c) select b, c from t1), + | t3 as ( + | with t4 as (select 1 as d, 3 as e) + | select * from t4 cross join t2 where t2.b = t4.d + | ) + | select a from (select 1 as a union all select 2 as a) + | where a = (select max(d) from t3) + """.stripMargin), + Array(Row(1)) + ) + } + test("uncorrelated scalar subquery in CTE") { checkAnswer( sql("with t2 as (select 1 as b, 2 as c) " + -- cgit v1.2.3