This is an automated email from the ASF dual-hosted git repository. rxin pushed a commit to branch branch-3.3 in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.3 by this push: new aa39b06462a [MINOR][TEST][SQL] Add a CTE subquery scope test case aa39b06462a is described below commit aa39b06462a98f37be59e239d12edd9f09a25b88 Author: Reynold Xin <r...@databricks.com> AuthorDate: Fri Dec 23 14:55:14 2022 -0800 [MINOR][TEST][SQL] Add a CTE subquery scope test case ### What changes were proposed in this pull request? I noticed we were missing a test case for this in SQL tests, so I added one. ### Why are the changes needed? To ensure we scope CTEs properly in subqueries. ### Does this PR introduce _any_ user-facing change? No. ### How was this patch tested? This is a test case change. Closes #39189 from rxin/cte_test. Authored-by: Reynold Xin <r...@databricks.com> Signed-off-by: Reynold Xin <r...@databricks.com> (cherry picked from commit 24edf8ecb5e47af294f89552dfd9957a2d9f193b) Signed-off-by: Reynold Xin <r...@databricks.com> --- .../test/resources/sql-tests/inputs/cte-nested.sql | 10 ++++++++ .../resources/sql-tests/results/cte-legacy.sql.out | 28 ++++++++++++++++++++++ .../resources/sql-tests/results/cte-nested.sql.out | 28 ++++++++++++++++++++++ .../sql-tests/results/cte-nonlegacy.sql.out | 28 ++++++++++++++++++++++ 4 files changed, 94 insertions(+) diff --git a/sql/core/src/test/resources/sql-tests/inputs/cte-nested.sql b/sql/core/src/test/resources/sql-tests/inputs/cte-nested.sql index 5f12388b9cb..e5ef2443417 100644 --- a/sql/core/src/test/resources/sql-tests/inputs/cte-nested.sql +++ b/sql/core/src/test/resources/sql-tests/inputs/cte-nested.sql @@ -17,6 +17,16 @@ SELECT ( SELECT * FROM t ); +-- Make sure CTE in subquery is scoped to that subquery rather than global +-- the 2nd half of the union should fail because the cte is scoped to the first half +SELECT * FROM + ( + WITH cte AS (SELECT * FROM range(10)) + SELECT * FROM cte WHERE id = 8 + ) a +UNION +SELECT * FROM cte; + -- CTE in CTE definition shadows outer WITH t AS (SELECT 1), diff --git a/sql/core/src/test/resources/sql-tests/results/cte-legacy.sql.out b/sql/core/src/test/resources/sql-tests/results/cte-legacy.sql.out index 264b64ffe96..ebdd64c3ac8 100644 --- a/sql/core/src/test/resources/sql-tests/results/cte-legacy.sql.out +++ b/sql/core/src/test/resources/sql-tests/results/cte-legacy.sql.out @@ -36,6 +36,34 @@ struct<scalarsubquery():int> 1 +-- !query +SELECT * FROM + ( + WITH cte AS (SELECT * FROM range(10)) + SELECT * FROM cte WHERE id = 8 + ) a +UNION +SELECT * FROM cte +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "TABLE_OR_VIEW_NOT_FOUND", + "sqlState" : "42000", + "messageParameters" : { + "relationName" : "`cte`" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 120, + "stopIndex" : 122, + "fragment" : "cte" + } ] +} + + -- !query WITH t AS (SELECT 1), diff --git a/sql/core/src/test/resources/sql-tests/results/cte-nested.sql.out b/sql/core/src/test/resources/sql-tests/results/cte-nested.sql.out index 2c622de3f36..b6e1793f7d7 100644 --- a/sql/core/src/test/resources/sql-tests/results/cte-nested.sql.out +++ b/sql/core/src/test/resources/sql-tests/results/cte-nested.sql.out @@ -36,6 +36,34 @@ struct<scalarsubquery():int> 1 +-- !query +SELECT * FROM + ( + WITH cte AS (SELECT * FROM range(10)) + SELECT * FROM cte WHERE id = 8 + ) a +UNION +SELECT * FROM cte +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "TABLE_OR_VIEW_NOT_FOUND", + "sqlState" : "42000", + "messageParameters" : { + "relationName" : "`cte`" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 120, + "stopIndex" : 122, + "fragment" : "cte" + } ] +} + + -- !query WITH t AS (SELECT 1), diff --git a/sql/core/src/test/resources/sql-tests/results/cte-nonlegacy.sql.out b/sql/core/src/test/resources/sql-tests/results/cte-nonlegacy.sql.out index 283f5a54a42..546ab7ecb95 100644 --- a/sql/core/src/test/resources/sql-tests/results/cte-nonlegacy.sql.out +++ b/sql/core/src/test/resources/sql-tests/results/cte-nonlegacy.sql.out @@ -36,6 +36,34 @@ struct<scalarsubquery():int> 1 +-- !query +SELECT * FROM + ( + WITH cte AS (SELECT * FROM range(10)) + SELECT * FROM cte WHERE id = 8 + ) a +UNION +SELECT * FROM cte +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "TABLE_OR_VIEW_NOT_FOUND", + "sqlState" : "42000", + "messageParameters" : { + "relationName" : "`cte`" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 120, + "stopIndex" : 122, + "fragment" : "cte" + } ] +} + + -- !query WITH t AS (SELECT 1), --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org