This is an automated email from the ASF dual-hosted git repository.
rxin 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 24edf8ecb5e [MINOR][TEST][SQL] Add a CTE subquery scope test case
24edf8ecb5e is described below
commit 24edf8ecb5e47af294f89552dfd9957a2d9f193b
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>
---
.../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 013c5f27b50..65000471c75 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
@@ -33,6 +33,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 ed6d69b233e..2c67f2db56a 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
@@ -33,6 +33,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 6a48e1bec43..154ebd20223 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
@@ -33,6 +33,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
