This is an automated email from the ASF dual-hosted git repository. dongjoon pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push: new 9e6666b [SPARK-28002][SQL] Support WITH clause column aliases 9e6666b is described below commit 9e6666bde1d0385761d06be39d18b7b351b0213c Author: Peter Toth <peter.t...@gmail.com> AuthorDate: Fri Jun 14 20:47:11 2019 -0700 [SPARK-28002][SQL] Support WITH clause column aliases ## What changes were proposed in this pull request? This PR adds support of column aliasing in a CTE so this query becomes valid: ``` WITH t(x) AS (SELECT 1) SELECT * FROM t WHERE x = 1 ``` ## How was this patch tested? Added new UTs. Closes #24842 from peter-toth/SPARK-28002. Authored-by: Peter Toth <peter.t...@gmail.com> Signed-off-by: Dongjoon Hyun <dh...@apple.com> --- .../apache/spark/sql/catalyst/parser/SqlBase.g4 | 2 +- .../spark/sql/catalyst/parser/AstBuilder.scala | 6 ++++- .../sql/catalyst/analysis/AnalysisSuite.scala | 12 ++++++++++ .../sql/catalyst/parser/PlanParserSuite.scala | 28 +++++++++++++++------- .../src/test/resources/sql-tests/inputs/cte.sql | 4 ++++ .../test/resources/sql-tests/results/cte.sql.out | 19 +++++++++++---- 6 files changed, 56 insertions(+), 15 deletions(-) diff --git a/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 b/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 index d58fb7c..ea06bc0 100644 --- a/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 +++ b/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 @@ -319,7 +319,7 @@ ctes ; namedQuery - : name=identifier AS? '(' query ')' + : name=identifier (columnAliases=identifierList)? AS? '(' query ')' ; tableProvider diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala index ec40e57..74c3a1b 100644 --- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala +++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala @@ -185,7 +185,11 @@ class AstBuilder(conf: SQLConf) extends SqlBaseBaseVisitor[AnyRef] with Logging * This is only used for Common Table Expressions. */ override def visitNamedQuery(ctx: NamedQueryContext): SubqueryAlias = withOrigin(ctx) { - SubqueryAlias(ctx.name.getText, plan(ctx.query)) + val subQuery: LogicalPlan = plan(ctx.query).optionalMap(ctx.columnAliases)( + (columnAliases, plan) => + UnresolvedSubqueryColumnAliases(visitIdentifierList(columnAliases), plan) + ) + SubqueryAlias(ctx.name.getText, subQuery) } /** diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnalysisSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnalysisSuite.scala index 8038733..b69f25b8 100644 --- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnalysisSuite.scala +++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnalysisSuite.scala @@ -29,6 +29,7 @@ import org.apache.spark.sql.catalyst.catalog.{CatalogStorageFormat, CatalogTable import org.apache.spark.sql.catalyst.dsl.expressions._ import org.apache.spark.sql.catalyst.dsl.plans._ import org.apache.spark.sql.catalyst.expressions._ +import org.apache.spark.sql.catalyst.parser.CatalystSqlParser.parsePlan import org.apache.spark.sql.catalyst.plans.{Cross, Inner} import org.apache.spark.sql.catalyst.plans.logical._ import org.apache.spark.sql.catalyst.plans.physical.{HashPartitioning, Partitioning, @@ -633,4 +634,15 @@ class AnalysisSuite extends AnalysisTest with Matchers { val res = ViewAnalyzer.execute(view) comparePlans(res, expected) } + + test("CTE with non-existing column alias") { + assertAnalysisError(parsePlan("WITH t(x) AS (SELECT 1) SELECT * FROM t WHERE y = 1"), + Seq("cannot resolve '`y`' given input columns: [x]")) + } + + test("CTE with non-matching column alias") { + assertAnalysisError(parsePlan("WITH t(x, y) AS (SELECT 1) SELECT * FROM t WHERE x = 1"), + Seq("Number of column aliases does not match number of columns. Number of column aliases: " + + "2; number of columns: 1.")) + } } diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala index fba2a28..1d63f1b 100644 --- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala +++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala @@ -42,10 +42,15 @@ class PlanParserSuite extends AnalysisTest { private def intercept(sqlCommand: String, messages: String*): Unit = interceptParseException(parsePlan)(sqlCommand, messages: _*) - private def cte(plan: LogicalPlan, namedPlans: (String, LogicalPlan)*): With = { + private def cte(plan: LogicalPlan, namedPlans: (String, (LogicalPlan, Seq[String]))*): With = { val ctes = namedPlans.map { - case (name, cte) => - name -> SubqueryAlias(name, cte) + case (name, (cte, columnAliases)) => + val subquery = if (columnAliases.isEmpty) { + cte + } else { + UnresolvedSubqueryColumnAliases(columnAliases, cte) + } + name -> SubqueryAlias(name, subquery) } With(plan, ctes) } @@ -84,15 +89,15 @@ class PlanParserSuite extends AnalysisTest { test("common table expressions") { assertEqual( "with cte1 as (select * from a) select * from cte1", - cte(table("cte1").select(star()), "cte1" -> table("a").select(star()))) + cte(table("cte1").select(star()), "cte1" -> ((table("a").select(star()), Seq.empty)))) assertEqual( "with cte1 (select 1) select * from cte1", - cte(table("cte1").select(star()), "cte1" -> OneRowRelation().select(1))) + cte(table("cte1").select(star()), "cte1" -> ((OneRowRelation().select(1), Seq.empty)))) assertEqual( "with cte1 (select 1), cte2 as (select * from cte1) select * from cte2", cte(table("cte2").select(star()), - "cte1" -> OneRowRelation().select(1), - "cte2" -> table("cte1").select(star()))) + "cte1" -> ((OneRowRelation().select(1), Seq.empty)), + "cte2" -> ((table("cte1").select(star()), Seq.empty)))) intercept( "with cte1 (select 1), cte1 as (select 1 from cte1) select * from cte1", "Found duplicate keys 'cte1'") @@ -812,10 +817,17 @@ class PlanParserSuite extends AnalysisTest { |WITH cte1 AS (SELECT * FROM testcat.db.tab) |SELECT * FROM cte1 """.stripMargin, - cte(table("cte1").select(star()), "cte1" -> table("testcat", "db", "tab").select(star()))) + cte(table("cte1").select(star()), + "cte1" -> ((table("testcat", "db", "tab").select(star()), Seq.empty)))) assertEqual( "SELECT /*+ BROADCAST(tab) */ * FROM testcat.db.tab", table("testcat", "db", "tab").select(star()).hint("BROADCAST", $"tab")) } + + test("CTE with column alias") { + assertEqual( + "WITH t(x) AS (SELECT c FROM a) SELECT * FROM t", + cte(table("t").select(star()), "t" -> ((table("a").select('c), Seq("x"))))) + } } diff --git a/sql/core/src/test/resources/sql-tests/inputs/cte.sql b/sql/core/src/test/resources/sql-tests/inputs/cte.sql index d34d89f..822c5c4 100644 --- a/sql/core/src/test/resources/sql-tests/inputs/cte.sql +++ b/sql/core/src/test/resources/sql-tests/inputs/cte.sql @@ -24,6 +24,10 @@ SELECT t1.id AS c1, FROM CTE1 t1 CROSS JOIN CTE1 t2; +-- CTE with column alias +WITH t(x) AS (SELECT 1) +SELECT * FROM t WHERE x = 1; + -- Clean up DROP VIEW IF EXISTS t; DROP VIEW IF EXISTS t2; diff --git a/sql/core/src/test/resources/sql-tests/results/cte.sql.out b/sql/core/src/test/resources/sql-tests/results/cte.sql.out index a446c2c..f8ccecb 100644 --- a/sql/core/src/test/resources/sql-tests/results/cte.sql.out +++ b/sql/core/src/test/resources/sql-tests/results/cte.sql.out @@ -1,5 +1,5 @@ -- Automatically generated by SQLQueryTestSuite --- Number of queries: 9 +-- Number of queries: 10 -- !query 0 @@ -89,16 +89,25 @@ struct<c1:int,c2:int> -- !query 7 -DROP VIEW IF EXISTS t +WITH t(x) AS (SELECT 1) +SELECT * FROM t WHERE x = 1 -- !query 7 schema -struct<> +struct<x:int> -- !query 7 output - +1 -- !query 8 -DROP VIEW IF EXISTS t2 +DROP VIEW IF EXISTS t -- !query 8 schema struct<> -- !query 8 output + + +-- !query 9 +DROP VIEW IF EXISTS t2 +-- !query 9 schema +struct<> +-- !query 9 output + --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org