This is an automated email from the ASF dual-hosted git repository.
wenchen 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 b3182e5521b9 [SPARK-50744][SQL] Add a test case for view/CTE name
resolution precedence
b3182e5521b9 is described below
commit b3182e5521b9aca70e9dc029fcb47e1185602629
Author: Vladimir Golubev <[email protected]>
AuthorDate: Tue Jan 7 12:08:36 2025 +0800
[SPARK-50744][SQL] Add a test case for view/CTE name resolution precedence
### What changes were proposed in this pull request?
Add an important test case for CTE resolution:
```
CREATE VIEW v1 AS SELECT 1;
CREATE VIEW v2 AS SELECT * FROM v1;
– The result is 1.
– The `v2` body will be inlined in the main query tree during the analysis,
but upper `v1`
– CTE definition won't take precedence over the lower `v1` view.
WITH v1 AS (
SELECT 2
)
SELECT * FROM v2;
```
This is an exception to the usual "CTE name takes precedence over the
table/view name".
### Why are the changes needed?
To harden Spark testing.
### Does this PR introduce _any_ user-facing change?
No.
### How was this patch tested?
New test case.
### Was this patch authored or co-authored using generative AI tooling?
No.
Closes #49378 from vladimirg-db/vladimirg-db/add-cte-vs-view-test-case.
Authored-by: Vladimir Golubev <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../sql-tests/analyzer-results/cte.sql.out | 40 ++++++++++++++++++++++
.../src/test/resources/sql-tests/inputs/cte.sql | 5 +++
.../test/resources/sql-tests/results/cte.sql.out | 26 ++++++++++++++
3 files changed, 71 insertions(+)
diff --git a/sql/core/src/test/resources/sql-tests/analyzer-results/cte.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/cte.sql.out
index ea09573db51a..cdd3698ce9af 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/cte.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/cte.sql.out
@@ -17,6 +17,19 @@ CreateViewCommand `t2`, select * from values 0, 1 as t(id),
false, false, LocalT
+- LocalRelation [id#x]
+-- !query
+create temporary view t3 as select * from t
+-- !query analysis
+CreateViewCommand `t3`, select * from t, false, false, LocalTempView,
UNSUPPORTED, true
+ +- Project [id#x]
+ +- SubqueryAlias t
+ +- View (`t`, [id#x])
+ +- Project [cast(id#x as int) AS id#x]
+ +- Project [id#x]
+ +- SubqueryAlias t
+ +- LocalRelation [id#x]
+
+
-- !query
WITH s AS (SELECT 1 FROM s) SELECT * FROM s
-- !query analysis
@@ -76,6 +89,27 @@ WithCTE
+- CTERelationRef xxxx, true, [1#x], false, false
+-- !query
+WITH t AS (SELECT 1) SELECT * FROM t3
+-- !query analysis
+WithCTE
+:- CTERelationDef xxxx, false
+: +- SubqueryAlias t
+: +- Project [1 AS 1#x]
+: +- OneRowRelation
++- Project [id#x]
+ +- SubqueryAlias t3
+ +- View (`t3`, [id#x])
+ +- Project [cast(id#x as int) AS id#x]
+ +- Project [id#x]
+ +- SubqueryAlias t
+ +- View (`t`, [id#x])
+ +- Project [cast(id#x as int) AS id#x]
+ +- Project [id#x]
+ +- SubqueryAlias t
+ +- LocalRelation [id#x]
+
+
-- !query
WITH s1 AS (SELECT 1 FROM s2), s2 AS (SELECT 1 FROM s1) SELECT * FROM s1, s2
-- !query analysis
@@ -778,3 +812,9 @@ DropTempViewCommand t
DROP VIEW IF EXISTS t2
-- !query analysis
DropTempViewCommand t2
+
+
+-- !query
+DROP VIEW IF EXISTS t3
+-- !query analysis
+DropTempViewCommand t3
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 67a94ce61617..1e17529d545b 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/cte.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/cte.sql
@@ -1,5 +1,6 @@
create temporary view t as select * from values 0, 1, 2 as t(id);
create temporary view t2 as select * from values 0, 1 as t(id);
+create temporary view t3 as select * from t;
-- WITH clause should not fall into infinite loop by referencing self
WITH s AS (SELECT 1 FROM s) SELECT * FROM s;
@@ -10,6 +11,9 @@ SELECT * FROM r;
-- WITH clause should reference the base table
WITH t AS (SELECT 1 FROM t) SELECT * FROM t;
+-- Table `t` referenced by a view should take precedence over the top CTE `t`
+WITH t AS (SELECT 1) SELECT * FROM t3;
+
-- WITH clause should not allow cross reference
WITH s1 AS (SELECT 1 FROM s2), s2 AS (SELECT 1 FROM s1) SELECT * FROM s1, s2;
@@ -175,3 +179,4 @@ with cte as (select * from cte) select * from cte;
-- Clean up
DROP VIEW IF EXISTS t;
DROP VIEW IF EXISTS t2;
+DROP VIEW IF EXISTS t3;
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 754a8832ef6c..4367ae1d5f2d 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
@@ -15,6 +15,14 @@ struct<>
+-- !query
+create temporary view t3 as select * from t
+-- !query schema
+struct<>
+-- !query output
+
+
+
-- !query
WITH s AS (SELECT 1 FROM s) SELECT * FROM s
-- !query schema
@@ -70,6 +78,16 @@ struct<1:int>
1
+-- !query
+WITH t AS (SELECT 1) SELECT * FROM t3
+-- !query schema
+struct<id:int>
+-- !query output
+0
+1
+2
+
+
-- !query
WITH s1 AS (SELECT 1 FROM s2), s2 AS (SELECT 1 FROM s1) SELECT * FROM s1, s2
-- !query schema
@@ -580,3 +598,11 @@ DROP VIEW IF EXISTS t2
struct<>
-- !query output
+
+
+-- !query
+DROP VIEW IF EXISTS t3
+-- !query schema
+struct<>
+-- !query output
+
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
