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 65b466c241a6 [SPARK-54031][SQL] Add new golden file tests for analysis
edge-cases
65b466c241a6 is described below
commit 65b466c241a65f62533bc579bffd406825518429
Author: Vladimir Golubev <[email protected]>
AuthorDate: Tue Oct 28 23:28:12 2025 +0800
[SPARK-54031][SQL] Add new golden file tests for analysis edge-cases
### What changes were proposed in this pull request?
Add new golden file tests for analysis edge-cases discovered during
Analyzer support and development.
### Why are the changes needed?
Harden Spark testing coverage.
### Does this PR introduce _any_ user-facing change?
Test-only change.
### How was this patch tested?
Adding new golden files.
### Was this patch authored or co-authored using generative AI tooling?
No.
Closes #52734 from
vladimirg-db/vladimir-golubev_data/new-golden-files-for-analyzer-edge-cases-2.
Authored-by: Vladimir Golubev <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../alias-resolution-edge-cases.sql.out | 90 +++++++++
.../analyzer-results/collations-basic.sql.out | 8 +
.../sql-tests/analyzer-results/cte.sql.out | 101 ++++++++++
.../extract-value-resolution-edge-cases.sql.out | 40 ++++
.../analyzer-results/group-by-alias.sql.out | 21 ++
.../sql-tests/analyzer-results/group-by.sql.out | 103 ++++++++++
.../sql-tests/analyzer-results/having.sql.out | 124 ++++++++++++
.../join-resolution-edge-cases.sql.out | 224 +++++++++++++++++++++
.../sql-tests/analyzer-results/order-by.sql.out | 52 +++++
.../runtime-replaceable-edge-cases.sql.out | 22 ++
.../session-variable-precedence.sql.out | 74 +++++++
.../subquery/resolution-edge-cases.sql.out | 223 ++++++++++++++++++++
.../sql-tests/analyzer-results/timezone.sql.out | 21 ++
.../type-coercion-edge-cases.sql.out | 41 ++++
.../union-resolution-edge-cases.sql.out | 159 +++++++++++++++
.../sql-tests/analyzer-results/using-join.sql.out | 19 ++
.../inputs/alias-resolution-edge-cases.sql | 17 ++
.../sql-tests/inputs/collations-basic.sql | 3 +
.../src/test/resources/sql-tests/inputs/cte.sql | 19 ++
.../inputs/extract-value-resolution-edge-cases.sql | 10 +
.../resources/sql-tests/inputs/group-by-alias.sql | 3 +
.../test/resources/sql-tests/inputs/group-by.sql | 13 +-
.../src/test/resources/sql-tests/inputs/having.sql | 20 +-
.../inputs/join-resolution-edge-cases.sql | 26 +++
.../test/resources/sql-tests/inputs/order-by.sql | 11 +-
.../inputs/runtime-replaceable-edge-cases.sql | 2 +
.../inputs/session-variable-precedence.sql | 17 ++
.../inputs/subquery/resolution-edge-cases.sql | 70 +++++++
.../test/resources/sql-tests/inputs/timezone.sql | 7 +
.../sql-tests/inputs/type-coercion-edge-cases.sql | 12 ++
.../inputs/union-resolution-edge-cases.sql | 30 +++
.../test/resources/sql-tests/inputs/using-join.sql | 6 +
.../results/alias-resolution-edge-cases.sql.out | 90 +++++++++
.../sql-tests/results/collations-basic.sql.out | 22 ++
.../test/resources/sql-tests/results/cte.sql.out | 56 ++++++
.../extract-value-resolution-edge-cases.sql.out | 39 ++++
.../sql-tests/results/group-by-alias.sql.out | 23 +++
.../resources/sql-tests/results/group-by.sql.out | 106 ++++++++++
.../resources/sql-tests/results/having.sql.out | 92 +++++++++
.../results/join-resolution-edge-cases.sql.out | 168 ++++++++++++++++
.../resources/sql-tests/results/order-by.sql.out | 48 +++++
.../results/runtime-replaceable-edge-cases.sql.out | 24 +++
.../results/session-variable-precedence.sql.out | 79 ++++++++
.../results/subquery/resolution-edge-cases.sql.out | 179 ++++++++++++++++
.../resources/sql-tests/results/timezone.sql.out | 24 +++
.../results/type-coercion-edge-cases.sql.out | 63 ++++++
.../results/union-resolution-edge-cases.sql.out | 119 +++++++++++
.../resources/sql-tests/results/using-join.sql.out | 11 +
48 files changed, 2728 insertions(+), 3 deletions(-)
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/alias-resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/alias-resolution-edge-cases.sql.out
new file mode 100644
index 000000000000..76d47f35ecad
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/alias-resolution-edge-cases.sql.out
@@ -0,0 +1,90 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 INT, col2 STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t1`, false
+
+
+-- !query
+CREATE TABLE t2(col1 STRUCT<a: STRING>, a STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t2`, false
+
+
+-- !query
+SELECT LEN(LOWER('X')) AS a, 1 AS b, b AS c GROUP BY LOWER('X') ORDER BY
LOWER('X')
+-- !query analysis
+Project [a#x, b#x, c#x]
++- Sort [lower(X)#x ASC NULLS FIRST], true
+ +- Project [len(lower(X)#x) AS a#x, b#x, b#x AS c#x, lower(X)#x AS
lower(X)#x]
+ +- Project [lower(X)#x, 1 AS b#x]
+ +- Aggregate [lower(X)], [lower(X) AS lower(X)#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT LEN(LOWER('X')) AS a, 1 AS b, b AS c GROUP BY LOWER('X') HAVING
LOWER('X') = 'x'
+-- !query analysis
+Project [a#x, b#x, c#x]
++- Filter (lower(X)#x = x)
+ +- Project [len(lower(X)#x) AS a#x, b#x, b#x AS c#x, lower(X)#x AS
lower(X)#x]
+ +- Project [lower(X)#x, 1 AS b#x]
+ +- Aggregate [lower(X)], [lower(X) AS lower(X)#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT col1.field, field FROM VALUES(named_struct('field', 1))
+-- !query analysis
+Project [field#x, field#x]
++- Project [col1#x, col1#x.field AS field#x]
+ +- LocalRelation [col1#x]
+
+
+-- !query
+SELECT col1.field, field FROM VALUES(map('field', 1))
+-- !query analysis
+Project [field#x, field#x]
++- Project [col1#x, col1#x[field] AS field#x]
+ +- LocalRelation [col1#x]
+
+
+-- !query
+SELECT COUNT(col1) as alias, SUM(col1) + alias FROM t1 GROUP BY ALL
+-- !query analysis
+Project [alias#xL, (sum(col1)#xL + alias#xL) AS (sum(col1) +
lateralAliasReference(alias))#xL]
++- Project [count(col1)#xL, sum(col1)#xL, count(col1)#xL AS alias#xL]
+ +- Aggregate [count(col1#x) AS count(col1)#xL, sum(col1#x) AS sum(col1)#xL]
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT COUNT(col1) as alias, SUM(col1) + alias, SUM(col1) + col1 FROM t1 GROUP
BY ALL
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNRESOLVED_ALL_IN_GROUP_BY",
+ "sqlState" : "42803",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 74,
+ "stopIndex" : 85,
+ "fragment" : "GROUP BY ALL"
+ } ]
+}
+
+
+-- !query
+DROP TABLE t1
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t1
+
+
+-- !query
+DROP TABLE t2
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t2
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/collations-basic.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/collations-basic.sql.out
index 03409b001c15..74320fa4dfc3 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/collations-basic.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/collations-basic.sql.out
@@ -1136,6 +1136,14 @@ Project [try_validate_utf8(collate(utf8_binary#x,
utf8_lcase_rtrim)) AS try_vali
+- Relation spark_catalog.default.t1[s#x,utf8_binary#x,utf8_lcase#x] parquet
+-- !query
+SELECT CASE WHEN utf8_lcase = 'XX' THEN 'XX' ELSE utf8_lcase END FROM t1
+-- !query analysis
+Project [CASE WHEN (utf8_lcase#x = XX) THEN XX ELSE utf8_lcase#x END AS CASE
WHEN (utf8_lcase = 'XX' collate UTF8_LCASE) THEN 'XX' collate UTF8_LCASE ELSE
utf8_lcase END#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[s#x,utf8_binary#x,utf8_lcase#x] parquet
+
+
-- !query
drop table t1
-- !query analysis
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 8d8d978cb0cb..8f195185556f 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
@@ -30,6 +30,12 @@ CreateViewCommand `t3`, select * from t, false, false,
LocalTempView, UNSUPPORTE
+- LocalRelation [id#x]
+-- !query
+create table t4(col1 TIMESTAMP)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t4`, false
+
+
-- !query
WITH s AS (SELECT 1 FROM s) SELECT * FROM s
-- !query analysis
@@ -1031,6 +1037,94 @@ WithCTE
+- CTERelationRef xxxx, true, [1#x], false, false, 1
+-- !query
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT t4.col1 FROM t4 JOIN cte1 USING (col1)
+)
+-- !query analysis
+Project [col1#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- WithCTE
+ :- CTERelationDef xxxx, false
+ : +- SubqueryAlias cte1
+ : +- Project [col1#x]
+ : +- SubqueryAlias spark_catalog.default.t4
+ : +- Relation spark_catalog.default.t4[col1#x] parquet
+ +- Project [col1#x]
+ +- Project [col1#x]
+ +- Join Inner, (col1#x = col1#x)
+ :- SubqueryAlias spark_catalog.default.t4
+ : +- Relation spark_catalog.default.t4[col1#x] parquet
+ +- SubqueryAlias cte1
+ +- CTERelationRef xxxx, true, [col1#x], false, false
+
+
+-- !query
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT cte1.col1 FROM t4 JOIN cte1 USING
(col1)
+)
+-- !query analysis
+Project [col1#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- WithCTE
+ :- CTERelationDef xxxx, false
+ : +- SubqueryAlias cte1
+ : +- Project [col1#x]
+ : +- SubqueryAlias spark_catalog.default.t4
+ : +- Relation spark_catalog.default.t4[col1#x] parquet
+ +- Project [col1#x]
+ +- Project [col1#x, col1#x]
+ +- Join Inner, (col1#x = col1#x)
+ :- SubqueryAlias spark_catalog.default.t4
+ : +- Relation spark_catalog.default.t4[col1#x] parquet
+ +- SubqueryAlias cte1
+ +- CTERelationRef xxxx, true, [col1#x], false, false
+
+
+-- !query
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT t4.col1 FROM cte1 JOIN t4 USING (col1)
+)
+-- !query analysis
+Project [col1#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- WithCTE
+ :- CTERelationDef xxxx, false
+ : +- SubqueryAlias cte1
+ : +- Project [col1#x]
+ : +- SubqueryAlias spark_catalog.default.t4
+ : +- Relation spark_catalog.default.t4[col1#x] parquet
+ +- Project [col1#x]
+ +- Project [col1#x, col1#x]
+ +- Join Inner, (col1#x = col1#x)
+ :- SubqueryAlias cte1
+ : +- CTERelationRef xxxx, true, [col1#x], false, false
+ +- SubqueryAlias spark_catalog.default.t4
+ +- Relation spark_catalog.default.t4[col1#x] parquet
+
+
+-- !query
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT cte1.col1 FROM cte1 JOIN t4 USING
(col1)
+)
+-- !query analysis
+Project [col1#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- WithCTE
+ :- CTERelationDef xxxx, false
+ : +- SubqueryAlias cte1
+ : +- Project [col1#x]
+ : +- SubqueryAlias spark_catalog.default.t4
+ : +- Relation spark_catalog.default.t4[col1#x] parquet
+ +- Project [col1#x]
+ +- Project [col1#x]
+ +- Join Inner, (col1#x = col1#x)
+ :- SubqueryAlias cte1
+ : +- CTERelationRef xxxx, true, [col1#x], false, false
+ +- SubqueryAlias spark_catalog.default.t4
+ +- Relation spark_catalog.default.t4[col1#x] parquet
+
+
-- !query
DROP VIEW IF EXISTS t
-- !query analysis
@@ -1047,3 +1141,10 @@ DropTempViewCommand t2
DROP VIEW IF EXISTS t3
-- !query analysis
DropTempViewCommand t3
+
+
+-- !query
+DROP TABLE IF EXISTS t4
+-- !query analysis
+DropTable true, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t4
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/extract-value-resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/extract-value-resolution-edge-cases.sql.out
new file mode 100644
index 000000000000..9f34e1a6e4ea
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/extract-value-resolution-edge-cases.sql.out
@@ -0,0 +1,40 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 STRUCT<a: STRING>, a STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t1`, false
+
+
+-- !query
+SELECT col1.a, a FROM t1 ORDER BY a
+-- !query analysis
+Sort [a#x ASC NULLS FIRST], true
++- Project [col1#x.a AS a#x, a#x]
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,a#x] parquet
+
+
+-- !query
+SELECT col1.a, a FROM t1 ORDER BY col1.a
+-- !query analysis
+Project [a#x, a#x]
++- Sort [col1#x.a ASC NULLS FIRST], true
+ +- Project [col1#x.a AS a#x, a#x, col1#x]
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,a#x] parquet
+
+
+-- !query
+SELECT split(col1, '-')[1] AS a FROM VALUES('a-b') ORDER BY split(col1, '-')[1]
+-- !query analysis
+Project [a#x]
++- Sort [split(col1#x, -, -1)[1] ASC NULLS FIRST], true
+ +- Project [split(col1#x, -, -1)[1] AS a#x, col1#x]
+ +- LocalRelation [col1#x]
+
+
+-- !query
+DROP TABLE t1
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t1
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/group-by-alias.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/group-by-alias.sql.out
index a3fe0bedfa24..c67c6afbcf3d 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/group-by-alias.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/group-by-alias.sql.out
@@ -543,3 +543,24 @@ Aggregate [a#x], [1 AS a#x]
+- Project [a#x, b#x]
+- SubqueryAlias testData
+- LocalRelation [a#x, b#x]
+
+
+-- !query
+SELECT col1 AS a FROM VALUES (NAMED_STRUCT('f1', 1)) GROUP BY a.f1
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNRESOLVED_COLUMN.WITH_SUGGESTION",
+ "sqlState" : "42703",
+ "messageParameters" : {
+ "objectName" : "`a`.`f1`",
+ "proposal" : "`col1`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 63,
+ "stopIndex" : 66,
+ "fragment" : "a.f1"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/group-by.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/group-by.sql.out
index e9fce46ec794..a2d3f4cdb016 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/group-by.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/group-by.sql.out
@@ -12,6 +12,12 @@ AS testData(a, b), false, true, LocalTempView, UNSUPPORTED,
true
+- LocalRelation [a#x, b#x]
+-- !query
+CREATE TABLE t1(col1 TIMESTAMP, col2 STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t1`, false
+
+
-- !query
SELECT a, COUNT(b) FROM testData
-- !query analysis
@@ -1225,3 +1231,100 @@ GROUP BY a
Aggregate [a#x], [count(1) AS count(1)#xL]
+- SubqueryAlias t
+- LocalRelation [a#x]
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY ALL HAVING first(col2) = 'a'
+-- !query analysis
+Project [col1#x]
++- Filter (first(col2)#x = a)
+ +- Aggregate [col1#x], [col1#x, first(col2#x, false) AS first(col2)#x]
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY col1 HAVING EXISTS (SELECT first(t1.col2) == 0)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.CORRELATED_REFERENCE",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExprs" : "\"(first(col2) = 0) AS
`(first(outer(spark_catalog.default.t1.col2)) = 0)`\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 50,
+ "stopIndex" : 75,
+ "fragment" : "SELECT first(t1.col2) == 0"
+ } ]
+}
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY ALL HAVING last(col2) = 'a'
+-- !query analysis
+Project [col1#x]
++- Filter (last(col2)#x = a)
+ +- Aggregate [col1#x], [col1#x, last(col2#x, false) AS last(col2)#x]
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY col1 HAVING EXISTS (SELECT last(t1.col2) == 0)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.CORRELATED_REFERENCE",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExprs" : "\"(last(col2) = 0) AS
`(last(outer(spark_catalog.default.t1.col2)) = 0)`\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 50,
+ "stopIndex" : 74,
+ "fragment" : "SELECT last(t1.col2) == 0"
+ } ]
+}
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY ALL HAVING any_value(col2) = 'a'
+-- !query analysis
+Project [col1#x]
++- Filter (any_value(col2)#x = a)
+ +- Aggregate [col1#x], [col1#x, any_value(col2#x, false) AS
any_value(col2)#x]
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY col1 HAVING EXISTS (SELECT any_value(t1.col2) ==
0)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.CORRELATED_REFERENCE",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExprs" : "\"(any_value(col2) = 0) AS
`(any_value(outer(spark_catalog.default.t1.col2)) = 0)`\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 50,
+ "stopIndex" : 79,
+ "fragment" : "SELECT any_value(t1.col2) == 0"
+ } ]
+}
+
+
+-- !query
+DROP TABLE t1
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t1
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/having.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/having.sql.out
index dab6b87e8073..3e26ae2a2d06 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/having.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/having.sql.out
@@ -506,3 +506,127 @@ Project [a#x, b#x]
+- Project [col1#x, col2#x, col1#x AS a#x]
+- Aggregate [col1#x, col2#x], [col1#x, col2#x]
+- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+SELECT col1, col1 AS a FROM VALUES(1) GROUP BY col1 HAVING col1 > 0
+-- !query analysis
+Filter (col1#x > 0)
++- Aggregate [col1#x], [col1#x, col1#x AS a#x]
+ +- LocalRelation [col1#x]
+
+
+-- !query
+SELECT col1 AS a, col1 FROM VALUES(1) GROUP BY col1 HAVING col1 > 0
+-- !query analysis
+Filter (col1#x > 0)
++- Aggregate [col1#x], [col1#x AS a#x, col1#x]
+ +- LocalRelation [col1#x]
+
+
+-- !query
+SELECT make_date(col1, col2, col3) AS a, a AS b FROM VALUES(1,2,3) GROUP BY
make_date(col1, col2, col3) HAVING make_date(col1, col2, col3) > '2025-01-01'
+-- !query analysis
+Filter (a#x > cast(2025-01-01 as date))
++- Project [a#x, a#x AS b#x]
+ +- Project [make_date(col1, col2, col3)#x, make_date(col1, col2, col3)#x AS
a#x]
+ +- Aggregate [make_date(col1#x, col2#x, col3#x, true)],
[make_date(col1#x, col2#x, col3#x, true) AS make_date(col1, col2, col3)#x]
+ +- LocalRelation [col1#x, col2#x, col3#x]
+
+
+-- !query
+SELECT 1 AS a, 1 / a AS b, ZEROIFNULL(SUM(col1)) FROM VALUES(1) GROUP BY 1
HAVING ZEROIFNULL(SUM(col1)) > 0
+-- !query analysis
+Filter (zeroifnull(sum(col1))#xL > cast(0 as bigint))
++- Project [a#x, (cast(1#x as double) / cast(a#x as double)) AS b#x,
zeroifnull(sum(col1#x)) AS zeroifnull(sum(col1))#xL]
+ +- Project [1#x, sum(col1)#xL, 1#x AS a#x]
+ +- Aggregate [1], [1 AS 1#x, sum(col1#x) AS sum(col1)#xL]
+ +- LocalRelation [col1#x]
+
+
+-- !query
+SELECT col1 AS a, SUM(col2) AS b, CASE WHEN col1 = 1 THEN 1 END AS c FROM
VALUES(1,2) GROUP BY col1 HAVING CASE WHEN col1 = 1 THEN 1 END = 1
+-- !query analysis
+Filter (c#x = 1)
++- Aggregate [col1#x], [col1#x AS a#x, sum(col2#x) AS b#xL, CASE WHEN (col1#x
= 1) THEN 1 END AS c#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING MAX(col2) == (SELECT 1 WHERE
MAX(col2) = 1)
+-- !query analysis
+Project [col1#x]
++- Filter (max(col2)#x = scalar-subquery#x [max(col2)#x])
+ : +- Project [1 AS 1#x]
+ : +- Filter (outer(max(col2)#x) = 1)
+ : +- OneRowRelation
+ +- Aggregate [col1#x], [col1#x, max(col2#x) AS max(col2)#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING (SELECT 1 WHERE MAX(col2) =
1) == MAX(col2)
+-- !query analysis
+Project [col1#x]
++- Filter (scalar-subquery#x [max(col2)#x] = max(col2)#x)
+ : +- Project [1 AS 1#x]
+ : +- Filter (outer(max(col2)#x) = 1)
+ : +- OneRowRelation
+ +- Aggregate [col1#x], [col1#x, max(col2#x) AS max(col2)#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING (SELECT 1 WHERE MAX(col2) =
1) == (SELECT 1 WHERE MAX(col2) = 1)
+-- !query analysis
+Project [col1#x]
++- Filter (scalar-subquery#x [max(col2)#x] = scalar-subquery#x [max(col2)#x])
+ : :- Project [1 AS 1#x]
+ : : +- Filter (outer(max(col2)#x) = 1)
+ : : +- OneRowRelation
+ : +- Project [1 AS 1#x]
+ : +- Filter (outer(max(col2)#x) = 1)
+ : +- OneRowRelation
+ +- Aggregate [col1#x], [col1#x, max(col2#x) AS max(col2)#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING bool_or(col2 = 1) AND
bool_or(col2 = 1)
+-- !query analysis
+Project [col1#x]
++- Filter (bool_or((col2 = 1))#x AND bool_or((col2 = 1))#x)
+ +- Aggregate [col1#x], [col1#x, bool_or((col2#x = 1)) AS bool_or((col2 =
1))#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+SELECT 1 GROUP BY COALESCE(1, 1) HAVING COALESCE(1, 1) = 1 OR COALESCE(1, 1)
IS NOT NULL
+-- !query analysis
+Project [1#x]
++- Filter ((coalesce(1, 1)#x = 1#x) OR isnotnull(coalesce(1, 1)#x))
+ +- Aggregate [coalesce(1, 1)], [1 AS 1#x, coalesce(1, 1) AS coalesce(1,
1)#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT col1 FROM VALUES (1) t1 GROUP BY col1 HAVING (
+ SELECT MAX(t2.col1) FROM VALUES (1) t2 WHERE t2.col1 == MAX(t1.col1) GROUP
BY t2.col1 HAVING (
+ SELECT t3.col1 FROM VALUES (1) t3 WHERE t3.col1 == MAX(t2.col1)
+ )
+)
+-- !query analysis
+Project [col1#x]
++- Filter cast(scalar-subquery#x [max(col1)#x] as boolean)
+ : +- Filter cast(scalar-subquery#x [max(col1)#x] as boolean)
+ : : +- Project [col1#x]
+ : : +- Filter (col1#x = outer(max(col1)#x))
+ : : +- SubqueryAlias t3
+ : : +- LocalRelation [col1#x]
+ : +- Aggregate [col1#x], [max(col1#x) AS max(col1)#x]
+ : +- Filter (col1#x = outer(max(col1)#x))
+ : +- SubqueryAlias t2
+ : +- LocalRelation [col1#x]
+ +- Aggregate [col1#x], [col1#x, max(col1#x) AS max(col1)#x]
+ +- SubqueryAlias t1
+ +- LocalRelation [col1#x]
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/join-resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/join-resolution-edge-cases.sql.out
new file mode 100644
index 000000000000..014ed15b0231
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/join-resolution-edge-cases.sql.out
@@ -0,0 +1,224 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 INT, col2 STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t1`, false
+
+
+-- !query
+CREATE TABLE t2(col1 INT, col2 STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t2`, false
+
+
+-- !query
+SELECT * FROM t2 as t2_1 LEFT JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3
+-- !query analysis
+Project [col1#x, col2#x, col1#x, col2#x]
++- Project [col1#x, col2#x, col1#x, col2#x]
+ +- Join Inner, ((col1#x = col1#x) AND (col2#x = col2#x))
+ :- Join LeftOuter, (col1#x = col1#x)
+ : :- SubqueryAlias t2_1
+ : : +- SubqueryAlias spark_catalog.default.t2
+ : : +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+ : +- SubqueryAlias t2_2
+ : +- SubqueryAlias spark_catalog.default.t2
+ : +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+ +- SubqueryAlias t2_3
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT * FROM t2 as t2_1 RIGHT JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3
+-- !query analysis
+Project [col1#x, col2#x, col1#x, col2#x]
++- Project [col1#x, col2#x, col1#x, col2#x]
+ +- Join Inner, ((col1#x = col1#x) AND (col2#x = col2#x))
+ :- Join RightOuter, (col1#x = col1#x)
+ : :- SubqueryAlias t2_1
+ : : +- SubqueryAlias spark_catalog.default.t2
+ : : +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+ : +- SubqueryAlias t2_2
+ : +- SubqueryAlias spark_catalog.default.t2
+ : +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+ +- SubqueryAlias t2_3
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT * FROM t2 as t2_1 CROSS JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3
+-- !query analysis
+Project [col1#x, col2#x, col1#x, col2#x]
++- Project [col1#x, col2#x, col1#x, col2#x]
+ +- Join Inner, ((col1#x = col1#x) AND (col2#x = col2#x))
+ :- Join Cross, (col1#x = col1#x)
+ : :- SubqueryAlias t2_1
+ : : +- SubqueryAlias spark_catalog.default.t2
+ : : +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+ : +- SubqueryAlias t2_2
+ : +- SubqueryAlias spark_catalog.default.t2
+ : +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+ +- SubqueryAlias t2_3
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT * FROM t2 as t2_1 INNER JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3
+-- !query analysis
+Project [col1#x, col2#x, col1#x, col2#x]
++- Project [col1#x, col2#x, col1#x, col2#x]
+ +- Join Inner, ((col1#x = col1#x) AND (col2#x = col2#x))
+ :- Join Inner, (col1#x = col1#x)
+ : :- SubqueryAlias t2_1
+ : : +- SubqueryAlias spark_catalog.default.t2
+ : : +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+ : +- SubqueryAlias t2_2
+ : +- SubqueryAlias spark_catalog.default.t2
+ : +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+ +- SubqueryAlias t2_3
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col2 AS alias FROM t1 as t1_1 LEFT ANTI JOIN t1 as t1_2 ON t1_1.col1 =
t1_2.col1 ORDER BY col2
+-- !query analysis
+Project [alias#x]
++- Sort [col2#x ASC NULLS FIRST], true
+ +- Project [col2#x AS alias#x, col2#x]
+ +- Join LeftAnti, (col1#x = col1#x)
+ :- SubqueryAlias t1_1
+ : +- SubqueryAlias spark_catalog.default.t1
+ : +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+ +- SubqueryAlias t1_2
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col2 AS alias FROM t1 as t1_1 LEFT SEMI JOIN t1 as t1_2 ON t1_1.col1 =
t1_2.col1 ORDER BY col2
+-- !query analysis
+Project [alias#x]
++- Sort [col2#x ASC NULLS FIRST], true
+ +- Project [col2#x AS alias#x, col2#x]
+ +- Join LeftSemi, (col1#x = col1#x)
+ :- SubqueryAlias t1_1
+ : +- SubqueryAlias spark_catalog.default.t1
+ : +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+ +- SubqueryAlias t1_2
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1 FROM t1 as t1_1 LEFT SEMI JOIN t1 as t1_2 ORDER BY col2
+-- !query analysis
+Project [col1#x]
++- Sort [col2#x ASC NULLS FIRST], true
+ +- Project [col1#x, col2#x]
+ +- Join LeftSemi
+ :- SubqueryAlias t1_1
+ : +- SubqueryAlias spark_catalog.default.t1
+ : +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+ +- SubqueryAlias t1_2
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1 FROM t1 as t1_1 LEFT SEMI JOIN t1 as t1_2 GROUP BY ALL HAVING
MIN(col2) > 1
+-- !query analysis
+Project [col1#x]
++- Filter (cast(min(col2)#x as bigint) > cast(1 as bigint))
+ +- Aggregate [col1#x], [col1#x, min(col2#x) AS min(col2)#x]
+ +- Join LeftSemi
+ :- SubqueryAlias t1_1
+ : +- SubqueryAlias spark_catalog.default.t1
+ : +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+ +- SubqueryAlias t1_2
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT 1 FROM t2 NATURAL JOIN t1 JOIN t2 ON t2.col1 = t1.col1 WHERE t2.col2 = 1
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "AMBIGUOUS_REFERENCE",
+ "sqlState" : "42704",
+ "messageParameters" : {
+ "name" : "`t2`.`col1`",
+ "referenceNames" : "[`spark_catalog`.`default`.`t2`.`col1`,
`spark_catalog`.`default`.`t2`.`col1`]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 45,
+ "stopIndex" : 51,
+ "fragment" : "t2.col1"
+ } ]
+}
+
+
+-- !query
+SELECT 1 FROM t2 NATURAL JOIN t1 JOIN t2 ON t2.col1 = t1.col1 GROUP BY t2.col2
HAVING t2.col2 = 1
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "AMBIGUOUS_REFERENCE",
+ "sqlState" : "42704",
+ "messageParameters" : {
+ "name" : "`t2`.`col1`",
+ "referenceNames" : "[`spark_catalog`.`default`.`t2`.`col1`,
`spark_catalog`.`default`.`t2`.`col1`]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 45,
+ "stopIndex" : 51,
+ "fragment" : "t2.col1"
+ } ]
+}
+
+
+-- !query
+SELECT 1 FROM t2 NATURAL JOIN t1 JOIN t2 ON t2.col1 = t1.col1 ORDER BY t2.col2
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "AMBIGUOUS_REFERENCE",
+ "sqlState" : "42704",
+ "messageParameters" : {
+ "name" : "`t2`.`col1`",
+ "referenceNames" : "[`spark_catalog`.`default`.`t2`.`col1`,
`spark_catalog`.`default`.`t2`.`col1`]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 45,
+ "stopIndex" : 51,
+ "fragment" : "t2.col1"
+ } ]
+}
+
+
+-- !query
+DROP TABLE t1
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t1
+
+
+-- !query
+DROP TABLE t2
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t2
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/order-by.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/order-by.sql.out
index 920e16ea8687..ab15b63f5b61 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/order-by.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/order-by.sql.out
@@ -474,6 +474,58 @@ Project [a#x, b#x]
+- LocalRelation [col1#x, col2#x]
+-- !query
+SELECT col1, col1 AS a FROM VALUES(1) GROUP BY col1 ORDER BY col1 ASC
+-- !query analysis
+Sort [col1#x ASC NULLS FIRST], true
++- Aggregate [col1#x], [col1#x, col1#x AS a#x]
+ +- LocalRelation [col1#x]
+
+
+-- !query
+SELECT col1 AS a, col1 FROM VALUES(1) GROUP BY col1 ORDER BY col1 ASC
+-- !query analysis
+Sort [col1#x ASC NULLS FIRST], true
++- Aggregate [col1#x], [col1#x AS a#x, col1#x]
+ +- LocalRelation [col1#x]
+
+
+-- !query
+SELECT make_date(col1, col2, col3) AS a, a AS b FROM VALUES(1,2,3) GROUP BY
make_date(col1, col2, col3) ORDER BY make_date(col1, col2, col3)
+-- !query analysis
+Sort [a#x ASC NULLS FIRST], true
++- Project [a#x, a#x AS b#x]
+ +- Project [make_date(col1, col2, col3)#x, make_date(col1, col2, col3)#x AS
a#x]
+ +- Aggregate [make_date(col1#x, col2#x, col3#x, true)],
[make_date(col1#x, col2#x, col3#x, true) AS make_date(col1, col2, col3)#x]
+ +- LocalRelation [col1#x, col2#x, col3#x]
+
+
+-- !query
+SELECT 1 AS a, 1 / a AS b, ZEROIFNULL(SUM(col1)) FROM VALUES(1) GROUP BY 1
ORDER BY ZEROIFNULL(SUM(col1))
+-- !query analysis
+Sort [zeroifnull(sum(col1))#xL ASC NULLS FIRST], true
++- Project [a#x, (cast(1#x as double) / cast(a#x as double)) AS b#x,
zeroifnull(sum(col1#x)) AS zeroifnull(sum(col1))#xL]
+ +- Project [1#x, sum(col1)#xL, 1#x AS a#x]
+ +- Aggregate [1], [1 AS 1#x, sum(col1#x) AS sum(col1)#xL]
+ +- LocalRelation [col1#x]
+
+
+-- !query
+SELECT col1 AS a, SUM(col2) AS b, CASE WHEN col1 = 1 THEN 1 END AS c FROM
VALUES(1,2) GROUP BY col1 ORDER BY CASE WHEN col1 = 1 THEN 1 END ASC
+-- !query analysis
+Sort [c#x ASC NULLS FIRST], true
++- Aggregate [col1#x], [col1#x AS a#x, sum(col2#x) AS b#xL, CASE WHEN (col1#x
= 1) THEN 1 END AS c#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+SELECT col1+1 AS ltrl2, col1+1 AS ltrl1 FROM VALUES(1) GROUP BY col1+1 ORDER
BY col1+1
+-- !query analysis
+Sort [ltrl2#x ASC NULLS FIRST], true
++- Aggregate [(col1#x + 1)], [(col1#x + 1) AS ltrl2#x, (col1#x + 1) AS ltrl1#x]
+ +- LocalRelation [col1#x]
+
+
-- !query
DROP VIEW IF EXISTS testData
-- !query analysis
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/runtime-replaceable-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/runtime-replaceable-edge-cases.sql.out
new file mode 100644
index 000000000000..ebb342b67b64
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/runtime-replaceable-edge-cases.sql.out
@@ -0,0 +1,22 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+SELECT xpath_string('abc', date_format('X', 'unknown'))
+-- !query analysis
+org.apache.spark.SparkDateTimeException
+{
+ "errorClass" : "CAST_INVALID_INPUT",
+ "sqlState" : "22018",
+ "messageParameters" : {
+ "ansiConfig" : "\"spark.sql.ansi.enabled\"",
+ "expression" : "'X'",
+ "sourceType" : "\"STRING\"",
+ "targetType" : "\"TIMESTAMP\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 28,
+ "stopIndex" : 54,
+ "fragment" : "date_format('X', 'unknown')"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/session-variable-precedence.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/session-variable-precedence.sql.out
new file mode 100644
index 000000000000..5a4f4722037a
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/session-variable-precedence.sql.out
@@ -0,0 +1,74 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 INT, col2 STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t1`, false
+
+
+-- !query
+DECLARE all = 1
+-- !query analysis
+CreateVariable defaultvalueexpression(1, 1), false
++- ResolvedIdentifier
org.apache.spark.sql.catalyst.analysis.FakeSystemCatalog$@xxxxxxxx, session.all
+
+
+-- !query
+DECLARE a = 1
+-- !query analysis
+CreateVariable defaultvalueexpression(1, 1), false
++- ResolvedIdentifier
org.apache.spark.sql.catalyst.analysis.FakeSystemCatalog$@xxxxxxxx, session.a
+
+
+-- !query
+SELECT col1, SUM(col2) FROM t1 GROUP BY ALL
+-- !query analysis
+Aggregate [col1#x], [col1#x, sum(cast(col2#x as double)) AS sum(col2)#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1, SUM(col2) FROM t1 GROUP BY ALL ORDER BY ALL
+-- !query analysis
+Sort [col1#x ASC NULLS FIRST, sum(col2)#x ASC NULLS FIRST], true
++- Aggregate [col1#x], [col1#x, sum(cast(col2#x as double)) AS sum(col2)#x]
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1, col2 FROM t1 ORDER BY ALL
+-- !query analysis
+Sort [col1#x ASC NULLS FIRST, col2#x ASC NULLS FIRST], true
++- Project [col1#x, col2#x]
+ +- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1 AS a, SUM(col2) FROM t1 GROUP BY a
+-- !query analysis
+Aggregate [col1#x], [col1#x AS a#x, sum(cast(col2#x as double)) AS sum(col2)#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+DROP TEMP VARIABLE a
+-- !query analysis
+DropVariable false
++- ResolvedIdentifier
org.apache.spark.sql.catalyst.analysis.FakeSystemCatalog$@xxxxxxxx, session.a
+
+
+-- !query
+DROP TEMP VARIABLE all
+-- !query analysis
+DropVariable false
++- ResolvedIdentifier
org.apache.spark.sql.catalyst.analysis.FakeSystemCatalog$@xxxxxxxx, session.all
+
+
+-- !query
+DROP TABLE t1
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t1
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/resolution-edge-cases.sql.out
new file mode 100644
index 000000000000..9d96d14aea53
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/resolution-edge-cases.sql.out
@@ -0,0 +1,223 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 INT, col2 STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t1`, false
+
+
+-- !query
+CREATE TABLE t2(col1 INT)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t2`, false
+
+
+-- !query
+CREATE TABLE t3(col1 INT, col2 STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t3`, false
+
+
+-- !query
+SELECT *
+FROM (
+ SELECT (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+ FROM t3
+ GROUP BY (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+)
+-- !query analysis
+Project [scalarsubquery(col1)#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- Aggregate [scalar-subquery#x [col1#x]], [scalar-subquery#x [col1#x] AS
scalarsubquery(col1)#x]
+ : :- GlobalLimit 1
+ : : +- LocalLimit 1
+ : : +- Project [col1#x]
+ : : +- Filter (cast(outer(col1#x) as bigint) = cast(col2#x as
bigint))
+ : : +- SubqueryAlias spark_catalog.default.t1
+ : : +- Relation spark_catalog.default.t1[col1#x,col2#x]
parquet
+ : +- GlobalLimit 1
+ : +- LocalLimit 1
+ : +- Project [col1#x]
+ : +- Filter (cast(outer(col1#x) as bigint) = cast(col2#x as
bigint))
+ : +- SubqueryAlias spark_catalog.default.t1
+ : +- Relation spark_catalog.default.t1[col1#x,col2#x]
parquet
+ +- SubqueryAlias spark_catalog.default.t3
+ +- Relation spark_catalog.default.t3[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT *
+FROM (
+ SELECT 1 IN (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+ FROM t3
+ GROUP BY 1 IN (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+)
+-- !query analysis
+Project [(1 IN (listquery(col1)))#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- Aggregate [1 IN (list#x [col1#x])], [1 IN (list#x [col1#x]) AS (1 IN
(listquery(col1)))#x]
+ : :- GlobalLimit 1
+ : : +- LocalLimit 1
+ : : +- Project [col1#x]
+ : : +- Filter (cast(outer(col1#x) as bigint) = cast(col2#x as
bigint))
+ : : +- SubqueryAlias spark_catalog.default.t1
+ : : +- Relation spark_catalog.default.t1[col1#x,col2#x]
parquet
+ : +- GlobalLimit 1
+ : +- LocalLimit 1
+ : +- Project [col1#x]
+ : +- Filter (cast(outer(col1#x) as bigint) = cast(col2#x as
bigint))
+ : +- SubqueryAlias spark_catalog.default.t1
+ : +- Relation spark_catalog.default.t1[col1#x,col2#x]
parquet
+ +- SubqueryAlias spark_catalog.default.t3
+ +- Relation spark_catalog.default.t3[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT *
+FROM (
+ SELECT EXISTS (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+ FROM t3
+ GROUP BY EXISTS (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+)
+-- !query analysis
+Project [exists(col1)#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- Aggregate [exists#x [col1#x]], [exists#x [col1#x] AS exists(col1)#x]
+ : :- GlobalLimit 1
+ : : +- LocalLimit 1
+ : : +- Project [col1#x]
+ : : +- Filter (cast(outer(col1#x) as bigint) = cast(col2#x as
bigint))
+ : : +- SubqueryAlias spark_catalog.default.t1
+ : : +- Relation spark_catalog.default.t1[col1#x,col2#x]
parquet
+ : +- GlobalLimit 1
+ : +- LocalLimit 1
+ : +- Project [col1#x]
+ : +- Filter (cast(outer(col1#x) as bigint) = cast(col2#x as
bigint))
+ : +- SubqueryAlias spark_catalog.default.t1
+ : +- Relation spark_catalog.default.t1[col1#x,col2#x]
parquet
+ +- SubqueryAlias spark_catalog.default.t3
+ +- Relation spark_catalog.default.t3[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1 IN (SELECT col1 FROM t2)
+FROM t2
+GROUP BY col1 IN (SELECT col1 FROM t2), col1
+ORDER BY col1 IN (SELECT col1 FROM t2)
+-- !query analysis
+Sort [(col1 IN (listquery()))#x ASC NULLS FIRST], true
++- Aggregate [col1#x IN (list#x []), col1#x], [col1#x IN (list#x []) AS (col1
IN (listquery()))#x]
+ : :- Project [col1#x]
+ : : +- SubqueryAlias spark_catalog.default.t2
+ : : +- Relation spark_catalog.default.t2[col1#x] parquet
+ : +- Project [col1#x]
+ : +- SubqueryAlias spark_catalog.default.t2
+ : +- Relation spark_catalog.default.t2[col1#x] parquet
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x] parquet
+
+
+-- !query
+SELECT col1 FROM t2 GROUP BY col1 IN (SELECT col1 FROM t2), col1 ORDER BY col1
IN (SELECT col1 FROM t2)
+-- !query analysis
+Project [col1#x]
++- Sort [(col1 IN (listquery()))#x ASC NULLS FIRST], true
+ +- Aggregate [col1#x IN (list#x []), col1#x], [col1#x, col1#x IN (list#x
[]) AS (col1 IN (listquery()))#x]
+ : :- Project [col1#x]
+ : : +- SubqueryAlias spark_catalog.default.t2
+ : : +- Relation spark_catalog.default.t2[col1#x] parquet
+ : +- Project [col1#x]
+ : +- SubqueryAlias spark_catalog.default.t2
+ : +- Relation spark_catalog.default.t2[col1#x] parquet
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x] parquet
+
+
+-- !query
+SELECT col1 FROM t2 GROUP BY col1 ORDER BY 1 IN (SELECT 1)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.UNSUPPORTED_IN_EXISTS_SUBQUERY",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Sort [1 IN (list#x []) ASC NULLS FIRST], true\n: +- Project
[1 AS 1#x]\n: +- OneRowRelation\n+- Aggregate [col1#x], [col1#x]\n +-
SubqueryAlias spark_catalog.default.t2\n +- Relation
spark_catalog.default.t2[col1#x] parquet\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 46,
+ "stopIndex" : 58,
+ "fragment" : "IN (SELECT 1)"
+ } ]
+}
+
+
+-- !query
+SELECT col1 AS a, a + 1 FROM t2 GROUP BY col1 ORDER BY 1 IN (SELECT 1)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.UNSUPPORTED_IN_EXISTS_SUBQUERY",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Sort [1 IN (list#x []) ASC NULLS FIRST], true\n: +- Project
[1 AS 1#x]\n: +- OneRowRelation\n+- Project [a#x, (a#x + 1) AS
(lateralAliasReference(a) + 1)#x]\n +- Project [col1#x, col1#x AS a#x]\n
+- Aggregate [col1#x], [col1#x]\n +- SubqueryAlias
spark_catalog.default.t2\n +- Relation
spark_catalog.default.t2[col1#x] parquet\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 58,
+ "stopIndex" : 70,
+ "fragment" : "IN (SELECT 1)"
+ } ]
+}
+
+
+-- !query
+DROP TABLE t1
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t1
+
+
+-- !query
+DROP TABLE t2
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t2
+
+
+-- !query
+DROP TABLE t3
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t3
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timezone.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timezone.sql.out
index 5b55a0c21893..1fc036a7c73c 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/timezone.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/timezone.sql.out
@@ -1,4 +1,10 @@
-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t1`, false
+
+
-- !query
SET TIME ZONE 'Asia/Hong_Kong'
-- !query analysis
@@ -153,3 +159,18 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "SET TIME ZONE INTERVAL 10 HOURS 1 MILLISECOND"
} ]
}
+
+
+-- !query
+SELECT NANVL(col1, NULL) FROM t1
+-- !query analysis
+Project [nanvl(cast(col1#x as double), cast(cast(null as string) as double))
AS nanvl(col1, NULL)#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x] parquet
+
+
+-- !query
+DROP TABLE t1
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t1
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/type-coercion-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/type-coercion-edge-cases.sql.out
new file mode 100644
index 000000000000..e99594808b80
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/type-coercion-edge-cases.sql.out
@@ -0,0 +1,41 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 TIMESTAMP, col2 MAP<BIGINT, DOUBLE>)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t1`, false
+
+
+-- !query
+SELECT MEAN(col1) FROM t1
+-- !query analysis
+Aggregate [mean(cast(col1#x as double)) AS mean(col1)#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col2.field FROM t1
+-- !query analysis
+Project [col2#x[cast(field as bigint)] AS field#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT NULL:a AS b
+-- !query analysis
+org.apache.spark.SparkException
+{
+ "errorClass" : "INTERNAL_ERROR",
+ "sqlState" : "XX000",
+ "messageParameters" : {
+ "message" : "Cannot find main error class 'COLUMN_IS_NOT_VARIANT_TYPE'"
+ }
+}
+
+
+-- !query
+DROP TABLE t1
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t1
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/union-resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/union-resolution-edge-cases.sql.out
new file mode 100644
index 000000000000..4a53d0d5e5e2
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/union-resolution-edge-cases.sql.out
@@ -0,0 +1,159 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 TIMESTAMP, col2 STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t1`, false
+
+
+-- !query
+CREATE TABLE t2(col1 INT, col2 STRING)
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`t2`, false
+
+
+-- !query
+WITH cte AS (
+ SELECT col1, col1 FROM t1
+ UNION
+ SELECT col1, col1 FROM t1
+)
+SELECT col1 FROM cte
+-- !query analysis
+WithCTE
+:- CTERelationDef xxxx, false
+: +- SubqueryAlias cte
+: +- Distinct
+: +- Union false, false
+: :- Project [col1#x, col1#x AS col1#x]
+: : +- Project [col1#x, col1#x]
+: : +- SubqueryAlias spark_catalog.default.t1
+: : +- Relation spark_catalog.default.t1[col1#x,col2#x]
parquet
+: +- Project [col1#x, col1#x AS col1#x]
+: +- Project [col1#x, col1#x]
+: +- SubqueryAlias spark_catalog.default.t1
+: +- Relation spark_catalog.default.t1[col1#x,col2#x]
parquet
++- Project [col1#x]
+ +- SubqueryAlias cte
+ +- CTERelationRef xxxx, true, [col1#x, col1#x], false, false
+
+
+-- !query
+WITH cte AS (
+ SELECT col2, from_utc_timestamp(col1, 'unknown'), col2 FROM t1
+ UNION ALL
+ SELECT col2, from_utc_timestamp(col1, 'unknown'), col2 FROM t1
+)
+SELECT * FROM cte
+-- !query analysis
+WithCTE
+:- CTERelationDef xxxx, false
+: +- SubqueryAlias cte
+: +- Union false, false
+: :- Project [col2#x, from_utc_timestamp(col1, unknown)#x, col2#x AS
col2#x]
+: : +- Project [col2#x, from_utc_timestamp(col1#x, unknown) AS
from_utc_timestamp(col1, unknown)#x, col2#x]
+: : +- SubqueryAlias spark_catalog.default.t1
+: : +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
+: +- Project [col2#x, from_utc_timestamp(col1, unknown)#x, col2#x AS
col2#x]
+: +- Project [col2#x, from_utc_timestamp(col1#x, unknown) AS
from_utc_timestamp(col1, unknown)#x, col2#x]
+: +- SubqueryAlias spark_catalog.default.t1
+: +- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
++- Project [col2#x, from_utc_timestamp(col1, unknown)#x, col2#x]
+ +- SubqueryAlias cte
+ +- CTERelationRef xxxx, true, [col2#x, from_utc_timestamp(col1,
unknown)#x, col2#x], false, false
+
+
+-- !query
+SELECT col1 FROM t3 WHERE (col1, col1) IN (SELECT col1, col1 UNION SELECT
col1, col1)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`t3`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 18,
+ "stopIndex" : 19,
+ "fragment" : "t3"
+ } ]
+}
+
+
+-- !query
+SELECT col1, TRIM(col2), col1 FROM t2 UNION SELECT col1, col2, col1 FROM t2
+-- !query analysis
+Distinct
++- Union false, false
+ :- Project [col1#x, trim(col2)#x, col1#x AS col1#x]
+ : +- Project [col1#x, trim(col2#x, None) AS trim(col2)#x, col1#x]
+ : +- SubqueryAlias spark_catalog.default.t2
+ : +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+ +- Project [col1#x, col2#x, col1#x AS col1#x]
+ +- Project [col1#x, col2#x, col1#x]
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1, TRIM(col2), col1 FROM t2 MINUS SELECT col1, col2, col1 FROM t2
+-- !query analysis
+Except false
+:- Project [col1#x, trim(col2#x, None) AS trim(col2)#x, col1#x]
+: +- SubqueryAlias spark_catalog.default.t2
+: +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
++- Project [col1#x, col2#x, col1#x]
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1, LTRIM(col2), col1 FROM t2 MINUS SELECT col1, col2, col1 FROM t2
+-- !query analysis
+Except false
+:- Project [col1#x, ltrim(col2#x, None) AS ltrim(col2)#x, col1#x]
+: +- SubqueryAlias spark_catalog.default.t2
+: +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
++- Project [col1#x, col2#x, col1#x]
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1, RTRIM(col2), col1 FROM t2 EXCEPT SELECT col1, col2, col1 FROM t2
+-- !query analysis
+Except false
+:- Project [col1#x, rtrim(col2#x, None) AS rtrim(col2)#x, col1#x]
+: +- SubqueryAlias spark_catalog.default.t2
+: +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
++- Project [col1#x, col2#x, col1#x]
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+
+
+-- !query
+SELECT col1, LOWER(col2), col1 FROM t2 INTERSECT SELECT col1, col2, col1 FROM
t2
+-- !query analysis
+Intersect false
+:- Project [col1#x, lower(col2#x) AS lower(col2)#x, col1#x]
+: +- SubqueryAlias spark_catalog.default.t2
+: +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
++- Project [col1#x, col2#x, col1#x]
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[col1#x,col2#x] parquet
+
+
+-- !query
+DROP TABLE t1
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t1
+
+
+-- !query
+DROP TABLE t2
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t2
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/using-join.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/using-join.sql.out
index 3b04886afb59..54821ec09fa4 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/using-join.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/using-join.sql.out
@@ -836,3 +836,22 @@ WithCTE
: +- CTERelationRef xxxx, true, [key#x], false, false, 1
+- SubqueryAlias t2
+- CTERelationRef xxxx, true, [key#x], false, false, 1
+
+
+-- !query
+SELECT *
+FROM
+ ( SELECT col1 FROM VALUES(false) ) LEFT JOIN ( SELECT col1 FROM
VALUES(false) ) b USING(col1)
+WHERE b.col1
+-- !query analysis
+Project [col1#x]
++- Project [col1#x]
+ +- Filter col1#x: boolean
+ +- Project [col1#x, col1#x]
+ +- Join LeftOuter, (col1#x = col1#x)
+ :- SubqueryAlias __auto_generated_subquery_name
+ : +- Project [col1#x]
+ : +- LocalRelation [col1#x]
+ +- SubqueryAlias b
+ +- Project [col1#x]
+ +- LocalRelation [col1#x]
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/alias-resolution-edge-cases.sql
b/sql/core/src/test/resources/sql-tests/inputs/alias-resolution-edge-cases.sql
new file mode 100644
index 000000000000..55e32b8f6bff
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/inputs/alias-resolution-edge-cases.sql
@@ -0,0 +1,17 @@
+CREATE TABLE t1(col1 INT, col2 STRING);
+CREATE TABLE t2(col1 STRUCT<a: STRING>, a STRING);
+
+-- Update references to match fixed-point plan when realiasing expressions in
LCA resolution
+SELECT LEN(LOWER('X')) AS a, 1 AS b, b AS c GROUP BY LOWER('X') ORDER BY
LOWER('X');
+SELECT LEN(LOWER('X')) AS a, 1 AS b, b AS c GROUP BY LOWER('X') HAVING
LOWER('X') = 'x';
+
+-- LCA referencing nested field
+SELECT col1.field, field FROM VALUES(named_struct('field', 1));
+SELECT col1.field, field FROM VALUES(map('field', 1));
+
+-- LCA attributes shouldn't be counted in UNRESOLVED_GROUP_BY_ALL validation
+SELECT COUNT(col1) as alias, SUM(col1) + alias FROM t1 GROUP BY ALL;
+SELECT COUNT(col1) as alias, SUM(col1) + alias, SUM(col1) + col1 FROM t1 GROUP
BY ALL;
+
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/sql/core/src/test/resources/sql-tests/inputs/collations-basic.sql
b/sql/core/src/test/resources/sql-tests/inputs/collations-basic.sql
index dad8fca46098..6ff3ef4f4adb 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/collations-basic.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/collations-basic.sql
@@ -206,6 +206,9 @@ select try_validate_utf8(utf8_binary),
try_validate_utf8(utf8_lcase) from t1;
select try_validate_utf8(utf8_binary collate utf8_lcase),
try_validate_utf8(utf8_lcase collate utf8_binary) from t1;
select try_validate_utf8(utf8_binary collate utf8_lcase_rtrim),
try_validate_utf8(utf8_lcase collate utf8_binary_rtrim) from t1;
+-- Apply CollationTypeCoercion to condition expressions
+SELECT CASE WHEN utf8_lcase = 'XX' THEN 'XX' ELSE utf8_lcase END FROM t1;
+
drop table t1;
drop table t2;
drop table 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 81194804e917..625933302843 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/cte.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/cte.sql
@@ -1,6 +1,7 @@
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;
+create table t4(col1 TIMESTAMP);
-- WITH clause should not fall into infinite loop by referencing self
WITH s AS (SELECT 1 FROM s) SELECT * FROM s;
@@ -268,7 +269,25 @@ WITH `a.b.c` AS (
)
SELECT * FROM `a.b.c`;
+-- Expression ID assignment in CTE with JOIN
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT t4.col1 FROM t4 JOIN cte1 USING (col1)
+);
+
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT cte1.col1 FROM t4 JOIN cte1 USING
(col1)
+);
+
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT t4.col1 FROM cte1 JOIN t4 USING (col1)
+);
+
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT cte1.col1 FROM cte1 JOIN t4 USING
(col1)
+);
+
-- Clean up
DROP VIEW IF EXISTS t;
DROP VIEW IF EXISTS t2;
DROP VIEW IF EXISTS t3;
+DROP TABLE IF EXISTS t4;
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/extract-value-resolution-edge-cases.sql
b/sql/core/src/test/resources/sql-tests/inputs/extract-value-resolution-edge-cases.sql
new file mode 100644
index 000000000000..5a2784d54270
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/inputs/extract-value-resolution-edge-cases.sql
@@ -0,0 +1,10 @@
+CREATE TABLE t1(col1 STRUCT<a: STRING>, a STRING);
+
+-- Correctly fallback from ambiguous reference due to ExtractValue alias
+SELECT col1.a, a FROM t1 ORDER BY a;
+SELECT col1.a, a FROM t1 ORDER BY col1.a;
+
+-- Indexing a result of a function that returns a complex type
+SELECT split(col1, '-')[1] AS a FROM VALUES('a-b') ORDER BY split(col1,
'-')[1];
+
+DROP TABLE t1;
diff --git a/sql/core/src/test/resources/sql-tests/inputs/group-by-alias.sql
b/sql/core/src/test/resources/sql-tests/inputs/group-by-alias.sql
index c415402f6077..80456e2e5cf4 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/group-by-alias.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/group-by-alias.sql
@@ -89,3 +89,6 @@ SELECT 1 AS col FROM testData GROUP BY `col`;
-- GROUP BY attribute takes precedence over alias
SELECT 1 AS a FROM testData GROUP BY `a`;
+
+-- GROUP BY alias cannot reference nested fields
+SELECT col1 AS a FROM VALUES (NAMED_STRUCT('f1', 1)) GROUP BY a.f1;
diff --git a/sql/core/src/test/resources/sql-tests/inputs/group-by.sql
b/sql/core/src/test/resources/sql-tests/inputs/group-by.sql
index c1b5e88c43f2..f114e04ea094 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/group-by.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/group-by.sql
@@ -7,6 +7,7 @@
CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES
(1, 1), (1, 2), (2, 1), (2, 2), (3, 1), (3, 2), (null, 1), (3, null), (null,
null)
AS testData(a, b);
+CREATE TABLE t1(col1 TIMESTAMP, col2 STRING);
-- Aggregate with empty GroupBy expressions.
SELECT a, COUNT(b) FROM testData;
@@ -326,4 +327,14 @@ GROUP BY a;
-- MAP<INT, ARRAY<MAP<INT,INT>>>
SELECT count(*)
FROM VALUES (Map(1, Array(Map(1,2)), 2, Array(Map(2, 3, 1, 2)))), (Map(2,
Array(Map(1, 2, 2,3)), 1, Array(Map(1, 2)))), (Map(1, Array(Map(1,2)), 2,
Array(Map(2, 4)))) as t(a)
-GROUP BY a;
\ No newline at end of file
+GROUP BY a;
+
+-- Attributes under Last, First, AnyValue can be resolved using the hidden
output.
+SELECT col1 FROM t1 GROUP BY ALL HAVING first(col2) = 'a';
+SELECT col1 FROM t1 GROUP BY col1 HAVING EXISTS (SELECT first(t1.col2) == 0);
+SELECT col1 FROM t1 GROUP BY ALL HAVING last(col2) = 'a';
+SELECT col1 FROM t1 GROUP BY col1 HAVING EXISTS (SELECT last(t1.col2) == 0);
+SELECT col1 FROM t1 GROUP BY ALL HAVING any_value(col2) = 'a';
+SELECT col1 FROM t1 GROUP BY col1 HAVING EXISTS (SELECT any_value(t1.col2) ==
0);
+
+DROP TABLE t1;
diff --git a/sql/core/src/test/resources/sql-tests/inputs/having.sql
b/sql/core/src/test/resources/sql-tests/inputs/having.sql
index 92f1d5aec74d..0f3f9e2cad67 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/having.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/having.sql
@@ -93,6 +93,24 @@ HAVING (
);
-- Missing attribute (col2) in HAVING is added only once
-
SELECT col1 FROM VALUES(1,2) GROUP BY col1, col2 HAVING col2 = col2;
SELECT col1 AS a, a AS b FROM VALUES(1,2) GROUP BY col1, col2 HAVING col2 =
col2;
+
+-- Replacing Having condition with alias from below
+SELECT col1, col1 AS a FROM VALUES(1) GROUP BY col1 HAVING col1 > 0;
+SELECT col1 AS a, col1 FROM VALUES(1) GROUP BY col1 HAVING col1 > 0;
+SELECT make_date(col1, col2, col3) AS a, a AS b FROM VALUES(1,2,3) GROUP BY
make_date(col1, col2, col3) HAVING make_date(col1, col2, col3) > '2025-01-01';
+SELECT 1 AS a, 1 / a AS b, ZEROIFNULL(SUM(col1)) FROM VALUES(1) GROUP BY 1
HAVING ZEROIFNULL(SUM(col1)) > 0;
+SELECT col1 AS a, SUM(col2) AS b, CASE WHEN col1 = 1 THEN 1 END AS c FROM
VALUES(1,2) GROUP BY col1 HAVING CASE WHEN col1 = 1 THEN 1 END = 1;
+
+-- Deduplicate expressions before adding them to Aggregate
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING MAX(col2) == (SELECT 1 WHERE
MAX(col2) = 1);
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING (SELECT 1 WHERE MAX(col2) =
1) == MAX(col2);
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING (SELECT 1 WHERE MAX(col2) =
1) == (SELECT 1 WHERE MAX(col2) = 1);
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING bool_or(col2 = 1) AND
bool_or(col2 = 1);
+SELECT 1 GROUP BY COALESCE(1, 1) HAVING COALESCE(1, 1) = 1 OR COALESCE(1, 1)
IS NOT NULL;
+SELECT col1 FROM VALUES (1) t1 GROUP BY col1 HAVING (
+ SELECT MAX(t2.col1) FROM VALUES (1) t2 WHERE t2.col1 == MAX(t1.col1) GROUP
BY t2.col1 HAVING (
+ SELECT t3.col1 FROM VALUES (1) t3 WHERE t3.col1 == MAX(t2.col1)
+ )
+);
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/join-resolution-edge-cases.sql
b/sql/core/src/test/resources/sql-tests/inputs/join-resolution-edge-cases.sql
new file mode 100644
index 000000000000..34a8f779277a
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/inputs/join-resolution-edge-cases.sql
@@ -0,0 +1,26 @@
+CREATE TABLE t1(col1 INT, col2 STRING);
+CREATE TABLE t2(col1 INT, col2 STRING);
+
+-- Join names should be computed as intersection of names of the left and
right sides.
+SELECT * FROM t2 as t2_1 LEFT JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3;
+SELECT * FROM t2 as t2_1 RIGHT JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3;
+SELECT * FROM t2 as t2_1 CROSS JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3;
+SELECT * FROM t2 as t2_1 INNER JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3;
+
+-- Regular join hidden output should be main output ++ old metadata output
+SELECT col2 AS alias FROM t1 as t1_1 LEFT ANTI JOIN t1 as t1_2 ON t1_1.col1 =
t1_2.col1 ORDER BY col2;
+SELECT col2 AS alias FROM t1 as t1_1 LEFT SEMI JOIN t1 as t1_2 ON t1_1.col1 =
t1_2.col1 ORDER BY col2;
+SELECT col1 FROM t1 as t1_1 LEFT SEMI JOIN t1 as t1_2 ORDER BY col2;
+SELECT col1 FROM t1 as t1_1 LEFT SEMI JOIN t1 as t1_2 GROUP BY ALL HAVING
MIN(col2) > 1;
+
+-- Retain original join output under Project/Aggregate/Filter
+SELECT 1 FROM t2 NATURAL JOIN t1 JOIN t2 ON t2.col1 = t1.col1 WHERE t2.col2 =
1;
+SELECT 1 FROM t2 NATURAL JOIN t1 JOIN t2 ON t2.col1 = t1.col1 GROUP BY t2.col2
HAVING t2.col2 = 1;
+SELECT 1 FROM t2 NATURAL JOIN t1 JOIN t2 ON t2.col1 = t1.col1 ORDER BY t2.col2;
+
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/sql/core/src/test/resources/sql-tests/inputs/order-by.sql
b/sql/core/src/test/resources/sql-tests/inputs/order-by.sql
index 88c04377de0b..7dcc9de5ecd2 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/order-by.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/order-by.sql
@@ -55,9 +55,18 @@ SELECT SUM(a) + 1 + MIN(a) FROM testData ORDER BY 1 + 1 + 1
+ MIN(a) + 1 + SUM(a
SELECT SUM(b) + 1 FROM testData HAVING SUM(b) + 1 > 0 ORDER BY SUM(b) + 1;
-- Missing attribute (col2) in ORDER BY is added only once
-
SELECT col1 FROM VALUES(1,2) GROUP BY col1, col2 ORDER BY col2, col2;
SELECT col1 AS a, a AS b FROM VALUES(1,2) GROUP BY col1, col2 ORDER BY col2,
col2;
+-- Replacing SortOrder expression with alias from below
+SELECT col1, col1 AS a FROM VALUES(1) GROUP BY col1 ORDER BY col1 ASC;
+SELECT col1 AS a, col1 FROM VALUES(1) GROUP BY col1 ORDER BY col1 ASC;
+SELECT make_date(col1, col2, col3) AS a, a AS b FROM VALUES(1,2,3) GROUP BY
make_date(col1, col2, col3) ORDER BY make_date(col1, col2, col3);
+SELECT 1 AS a, 1 / a AS b, ZEROIFNULL(SUM(col1)) FROM VALUES(1) GROUP BY 1
ORDER BY ZEROIFNULL(SUM(col1));
+SELECT col1 AS a, SUM(col2) AS b, CASE WHEN col1 = 1 THEN 1 END AS c FROM
VALUES(1,2) GROUP BY col1 ORDER BY CASE WHEN col1 = 1 THEN 1 END ASC;
+
+-- Priority of aliases when replacing them in ORDER BY should be determined by
name
+SELECT col1+1 AS ltrl2, col1+1 AS ltrl1 FROM VALUES(1) GROUP BY col1+1 ORDER
BY col1+1;
+
-- Clean up
DROP VIEW IF EXISTS testData;
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/runtime-replaceable-edge-cases.sql
b/sql/core/src/test/resources/sql-tests/inputs/runtime-replaceable-edge-cases.sql
new file mode 100644
index 000000000000..948c034a5ab5
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/inputs/runtime-replaceable-edge-cases.sql
@@ -0,0 +1,2 @@
+-- Check that replacement in RuntimeReplaceable is resolved
+SELECT xpath_string('abc', date_format('X', 'unknown'));
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/session-variable-precedence.sql
b/sql/core/src/test/resources/sql-tests/inputs/session-variable-precedence.sql
new file mode 100644
index 000000000000..405c3ffdaa41
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/inputs/session-variable-precedence.sql
@@ -0,0 +1,17 @@
+CREATE TABLE t1(col1 INT, col2 STRING);
+
+DECLARE all = 1;
+DECLARE a = 1;
+
+-- Precedence with group by ALL
+SELECT col1, SUM(col2) FROM t1 GROUP BY ALL;
+SELECT col1, SUM(col2) FROM t1 GROUP BY ALL ORDER BY ALL;
+SELECT col1, col2 FROM t1 ORDER BY ALL;
+
+-- Precedence with group by alias
+SELECT col1 AS a, SUM(col2) FROM t1 GROUP BY a;
+
+DROP TEMP VARIABLE a;
+DROP TEMP VARIABLE all;
+
+DROP TABLE t1;
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/subquery/resolution-edge-cases.sql
b/sql/core/src/test/resources/sql-tests/inputs/subquery/resolution-edge-cases.sql
new file mode 100644
index 000000000000..0dd376553192
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/inputs/subquery/resolution-edge-cases.sql
@@ -0,0 +1,70 @@
+--ONLY_IF spark
+
+CREATE TABLE t1(col1 INT, col2 STRING);
+CREATE TABLE t2(col1 INT);
+CREATE TABLE t3(col1 INT, col2 STRING);
+
+-- Subquery expressions validation is done at the end of operator resolution
+SELECT *
+FROM (
+ SELECT (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+ FROM t3
+ GROUP BY (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+);
+SELECT *
+FROM (
+ SELECT 1 IN (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+ FROM t3
+ GROUP BY 1 IN (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+);
+SELECT *
+FROM (
+ SELECT EXISTS (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+ FROM t3
+ GROUP BY EXISTS (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+);
+
+SELECT col1 IN (SELECT col1 FROM t2)
+FROM t2
+GROUP BY col1 IN (SELECT col1 FROM t2), col1
+ORDER BY col1 IN (SELECT col1 FROM t2);
+
+SELECT col1 FROM t2 GROUP BY col1 IN (SELECT col1 FROM t2), col1 ORDER BY col1
IN (SELECT col1 FROM t2);
+
+SELECT col1 FROM t2 GROUP BY col1 ORDER BY 1 IN (SELECT 1);
+
+SELECT col1 AS a, a + 1 FROM t2 GROUP BY col1 ORDER BY 1 IN (SELECT 1);
+
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
diff --git a/sql/core/src/test/resources/sql-tests/inputs/timezone.sql
b/sql/core/src/test/resources/sql-tests/inputs/timezone.sql
index 105f2816fb54..49153d1d85d3 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timezone.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timezone.sql
@@ -1,3 +1,5 @@
+CREATE TABLE t1(col1 STRING);
+
-- valid time zones
SET TIME ZONE 'Asia/Hong_Kong';
SET TIME ZONE 'GMT+1';
@@ -13,3 +15,8 @@ SET TIME ZONE INTERVAL 24 HOURS;
SET TIME ZONE INTERVAL '19:40:32' HOUR TO SECOND;
SET TIME ZONE INTERVAL 10 HOURS 'GMT+1';
SET TIME ZONE INTERVAL 10 HOURS 1 MILLISECOND;
+
+-- Timezone should be applied recursively
+SELECT NANVL(col1, NULL) FROM t1;
+
+DROP TABLE t1;
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/type-coercion-edge-cases.sql
b/sql/core/src/test/resources/sql-tests/inputs/type-coercion-edge-cases.sql
new file mode 100644
index 000000000000..7003411385fa
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/type-coercion-edge-cases.sql
@@ -0,0 +1,12 @@
+CREATE TABLE t1(col1 TIMESTAMP, col2 MAP<BIGINT, DOUBLE>);
+
+-- Preserve tags after type coercing aggregate expression children
+SELECT MEAN(col1) FROM t1;
+
+-- Type coercion is applied to recursive data types
+SELECT col2.field FROM t1;
+
+-- Apply type coercion to semi_structured_extract
+SELECT NULL:a AS b;
+
+DROP TABLE t1;
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/union-resolution-edge-cases.sql
b/sql/core/src/test/resources/sql-tests/inputs/union-resolution-edge-cases.sql
new file mode 100644
index 000000000000..43214fba05c9
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/inputs/union-resolution-edge-cases.sql
@@ -0,0 +1,30 @@
+CREATE TABLE t1(col1 TIMESTAMP, col2 STRING);
+CREATE TABLE t2(col1 INT, col2 STRING);
+
+-- Duplicates in UNION under CTE
+WITH cte AS (
+ SELECT col1, col1 FROM t1
+ UNION
+ SELECT col1, col1 FROM t1
+)
+SELECT col1 FROM cte;
+
+WITH cte AS (
+ SELECT col2, from_utc_timestamp(col1, 'unknown'), col2 FROM t1
+ UNION ALL
+ SELECT col2, from_utc_timestamp(col1, 'unknown'), col2 FROM t1
+)
+SELECT * FROM cte;
+
+-- UNION with outer references in a subquery
+SELECT col1 FROM t3 WHERE (col1, col1) IN (SELECT col1, col1 UNION SELECT
col1, col1);
+
+-- SetOperationLike output deduplication across different branches should be
done only in context of UNION
+SELECT col1, TRIM(col2), col1 FROM t2 UNION SELECT col1, col2, col1 FROM t2;
+SELECT col1, TRIM(col2), col1 FROM t2 MINUS SELECT col1, col2, col1 FROM t2;
+SELECT col1, LTRIM(col2), col1 FROM t2 MINUS SELECT col1, col2, col1 FROM t2;
+SELECT col1, RTRIM(col2), col1 FROM t2 EXCEPT SELECT col1, col2, col1 FROM t2;
+SELECT col1, LOWER(col2), col1 FROM t2 INTERSECT SELECT col1, col2, col1 FROM
t2;
+
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/sql/core/src/test/resources/sql-tests/inputs/using-join.sql
b/sql/core/src/test/resources/sql-tests/inputs/using-join.sql
index f2657a91910b..600a65975112 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/using-join.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/using-join.sql
@@ -93,3 +93,9 @@ WITH
SELECT t1.key
FROM t1 FULL OUTER JOIN t2 USING (key)
WHERE t1.key NOT LIKE 'bb.%';
+
+-- ResolvesNameByHiddenOutput.retainOriginalOutput shouldn't change NameScope
output
+SELECT *
+FROM
+ ( SELECT col1 FROM VALUES(false) ) LEFT JOIN ( SELECT col1 FROM
VALUES(false) ) b USING(col1)
+WHERE b.col1;
diff --git
a/sql/core/src/test/resources/sql-tests/results/alias-resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/results/alias-resolution-edge-cases.sql.out
new file mode 100644
index 000000000000..28840992e6f6
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/alias-resolution-edge-cases.sql.out
@@ -0,0 +1,90 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 INT, col2 STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE t2(col1 STRUCT<a: STRING>, a STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT LEN(LOWER('X')) AS a, 1 AS b, b AS c GROUP BY LOWER('X') ORDER BY
LOWER('X')
+-- !query schema
+struct<a:int,b:int,c:int>
+-- !query output
+1 1 1
+
+
+-- !query
+SELECT LEN(LOWER('X')) AS a, 1 AS b, b AS c GROUP BY LOWER('X') HAVING
LOWER('X') = 'x'
+-- !query schema
+struct<a:int,b:int,c:int>
+-- !query output
+1 1 1
+
+
+-- !query
+SELECT col1.field, field FROM VALUES(named_struct('field', 1))
+-- !query schema
+struct<field:int,field:int>
+-- !query output
+1 1
+
+
+-- !query
+SELECT col1.field, field FROM VALUES(map('field', 1))
+-- !query schema
+struct<field:int,field:int>
+-- !query output
+1 1
+
+
+-- !query
+SELECT COUNT(col1) as alias, SUM(col1) + alias FROM t1 GROUP BY ALL
+-- !query schema
+struct<alias:bigint,(sum(col1) + lateralAliasReference(alias)):bigint>
+-- !query output
+0 NULL
+
+
+-- !query
+SELECT COUNT(col1) as alias, SUM(col1) + alias, SUM(col1) + col1 FROM t1 GROUP
BY ALL
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNRESOLVED_ALL_IN_GROUP_BY",
+ "sqlState" : "42803",
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 74,
+ "stopIndex" : 85,
+ "fragment" : "GROUP BY ALL"
+ } ]
+}
+
+
+-- !query
+DROP TABLE t1
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP TABLE t2
+-- !query schema
+struct<>
+-- !query output
+
diff --git
a/sql/core/src/test/resources/sql-tests/results/collations-basic.sql.out
b/sql/core/src/test/resources/sql-tests/results/collations-basic.sql.out
index f10ca3ca89c6..4be0c01df3af 100644
--- a/sql/core/src/test/resources/sql-tests/results/collations-basic.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/collations-basic.sql.out
@@ -1716,6 +1716,28 @@ kitten sitTing
İo İo
+-- !query
+SELECT CASE WHEN utf8_lcase = 'XX' THEN 'XX' ELSE utf8_lcase END FROM t1
+-- !query schema
+struct<CASE WHEN (utf8_lcase = 'XX' collate UTF8_LCASE) THEN 'XX' collate
UTF8_LCASE ELSE utf8_lcase END:string collate UTF8_LCASE>
+-- !query output
+Hello, world! Nice day.
+SQL
+Something else. Nothing here.
+a
+aBcDCbA
+aaAaAAaA
+aaAaaAaA
+aaAaaAaAaaAaaAaAaaAaaAaA
+abc
+efd2
+i̇o
+sitTing
+İo
+İo
+İo
+
+
-- !query
drop table t1
-- !query schema
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 71dd283c0d9e..39398b26a0b5 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
@@ -23,6 +23,14 @@ struct<>
+-- !query
+create table t4(col1 TIMESTAMP)
+-- !query schema
+struct<>
+-- !query output
+
+
+
-- !query
WITH s AS (SELECT 1 FROM s) SELECT * FROM s
-- !query schema
@@ -726,6 +734,46 @@ struct<1:int>
1
+-- !query
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT t4.col1 FROM t4 JOIN cte1 USING (col1)
+)
+-- !query schema
+struct<col1:timestamp>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT cte1.col1 FROM t4 JOIN cte1 USING
(col1)
+)
+-- !query schema
+struct<col1:timestamp>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT t4.col1 FROM cte1 JOIN t4 USING (col1)
+)
+-- !query schema
+struct<col1:timestamp>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM (
+ WITH cte1 AS (SELECT * FROM t4) SELECT cte1.col1 FROM cte1 JOIN t4 USING
(col1)
+)
+-- !query schema
+struct<col1:timestamp>
+-- !query output
+
+
+
-- !query
DROP VIEW IF EXISTS t
-- !query schema
@@ -748,3 +796,11 @@ DROP VIEW IF EXISTS t3
struct<>
-- !query output
+
+
+-- !query
+DROP TABLE IF EXISTS t4
+-- !query schema
+struct<>
+-- !query output
+
diff --git
a/sql/core/src/test/resources/sql-tests/results/extract-value-resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/results/extract-value-resolution-edge-cases.sql.out
new file mode 100644
index 000000000000..0565edc99b95
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/extract-value-resolution-edge-cases.sql.out
@@ -0,0 +1,39 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 STRUCT<a: STRING>, a STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT col1.a, a FROM t1 ORDER BY a
+-- !query schema
+struct<a:string,a:string>
+-- !query output
+
+
+
+-- !query
+SELECT col1.a, a FROM t1 ORDER BY col1.a
+-- !query schema
+struct<a:string,a:string>
+-- !query output
+
+
+
+-- !query
+SELECT split(col1, '-')[1] AS a FROM VALUES('a-b') ORDER BY split(col1, '-')[1]
+-- !query schema
+struct<a:string>
+-- !query output
+b
+
+
+-- !query
+DROP TABLE t1
+-- !query schema
+struct<>
+-- !query output
+
diff --git
a/sql/core/src/test/resources/sql-tests/results/group-by-alias.sql.out
b/sql/core/src/test/resources/sql-tests/results/group-by-alias.sql.out
index bbbb2821b9b5..d5ba80a8f2f5 100644
--- a/sql/core/src/test/resources/sql-tests/results/group-by-alias.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/group-by-alias.sql.out
@@ -467,3 +467,26 @@ struct<a:int>
1
1
1
+
+
+-- !query
+SELECT col1 AS a FROM VALUES (NAMED_STRUCT('f1', 1)) GROUP BY a.f1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNRESOLVED_COLUMN.WITH_SUGGESTION",
+ "sqlState" : "42703",
+ "messageParameters" : {
+ "objectName" : "`a`.`f1`",
+ "proposal" : "`col1`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 63,
+ "stopIndex" : 66,
+ "fragment" : "a.f1"
+ } ]
+}
diff --git a/sql/core/src/test/resources/sql-tests/results/group-by.sql.out
b/sql/core/src/test/resources/sql-tests/results/group-by.sql.out
index 8608dbef843f..a86cab002501 100644
--- a/sql/core/src/test/resources/sql-tests/results/group-by.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/group-by.sql.out
@@ -9,6 +9,14 @@ struct<>
+-- !query
+CREATE TABLE t1(col1 TIMESTAMP, col2 STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
-- !query
SELECT a, COUNT(b) FROM testData
-- !query schema
@@ -1170,3 +1178,101 @@ struct<count(1):bigint>
-- !query output
1
2
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY ALL HAVING first(col2) = 'a'
+-- !query schema
+struct<col1:timestamp>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY col1 HAVING EXISTS (SELECT first(t1.col2) == 0)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.CORRELATED_REFERENCE",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExprs" : "\"(first(col2) = 0) AS
`(first(outer(spark_catalog.default.t1.col2)) = 0)`\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 50,
+ "stopIndex" : 75,
+ "fragment" : "SELECT first(t1.col2) == 0"
+ } ]
+}
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY ALL HAVING last(col2) = 'a'
+-- !query schema
+struct<col1:timestamp>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY col1 HAVING EXISTS (SELECT last(t1.col2) == 0)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.CORRELATED_REFERENCE",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExprs" : "\"(last(col2) = 0) AS
`(last(outer(spark_catalog.default.t1.col2)) = 0)`\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 50,
+ "stopIndex" : 74,
+ "fragment" : "SELECT last(t1.col2) == 0"
+ } ]
+}
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY ALL HAVING any_value(col2) = 'a'
+-- !query schema
+struct<col1:timestamp>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM t1 GROUP BY col1 HAVING EXISTS (SELECT any_value(t1.col2) ==
0)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.CORRELATED_REFERENCE",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExprs" : "\"(any_value(col2) = 0) AS
`(any_value(outer(spark_catalog.default.t1.col2)) = 0)`\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 50,
+ "stopIndex" : 79,
+ "fragment" : "SELECT any_value(t1.col2) == 0"
+ } ]
+}
+
+
+-- !query
+DROP TABLE t1
+-- !query schema
+struct<>
+-- !query output
+
diff --git a/sql/core/src/test/resources/sql-tests/results/having.sql.out
b/sql/core/src/test/resources/sql-tests/results/having.sql.out
index 4ea7add3fc72..63467a76c897 100644
--- a/sql/core/src/test/resources/sql-tests/results/having.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/having.sql.out
@@ -359,3 +359,95 @@ SELECT col1 AS a, a AS b FROM VALUES(1,2) GROUP BY col1,
col2 HAVING col2 = col2
struct<a:int,b:int>
-- !query output
1 1
+
+
+-- !query
+SELECT col1, col1 AS a FROM VALUES(1) GROUP BY col1 HAVING col1 > 0
+-- !query schema
+struct<col1:int,a:int>
+-- !query output
+1 1
+
+
+-- !query
+SELECT col1 AS a, col1 FROM VALUES(1) GROUP BY col1 HAVING col1 > 0
+-- !query schema
+struct<a:int,col1:int>
+-- !query output
+1 1
+
+
+-- !query
+SELECT make_date(col1, col2, col3) AS a, a AS b FROM VALUES(1,2,3) GROUP BY
make_date(col1, col2, col3) HAVING make_date(col1, col2, col3) > '2025-01-01'
+-- !query schema
+struct<a:date,b:date>
+-- !query output
+
+
+
+-- !query
+SELECT 1 AS a, 1 / a AS b, ZEROIFNULL(SUM(col1)) FROM VALUES(1) GROUP BY 1
HAVING ZEROIFNULL(SUM(col1)) > 0
+-- !query schema
+struct<a:int,b:double,zeroifnull(sum(col1)):bigint>
+-- !query output
+1 1.0 1
+
+
+-- !query
+SELECT col1 AS a, SUM(col2) AS b, CASE WHEN col1 = 1 THEN 1 END AS c FROM
VALUES(1,2) GROUP BY col1 HAVING CASE WHEN col1 = 1 THEN 1 END = 1
+-- !query schema
+struct<a:int,b:bigint,c:int>
+-- !query output
+1 2 1
+
+
+-- !query
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING MAX(col2) == (SELECT 1 WHERE
MAX(col2) = 1)
+-- !query schema
+struct<col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING (SELECT 1 WHERE MAX(col2) =
1) == MAX(col2)
+-- !query schema
+struct<col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING (SELECT 1 WHERE MAX(col2) =
1) == (SELECT 1 WHERE MAX(col2) = 1)
+-- !query schema
+struct<col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM VALUES(1,2) GROUP BY col1 HAVING bool_or(col2 = 1) AND
bool_or(col2 = 1)
+-- !query schema
+struct<col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT 1 GROUP BY COALESCE(1, 1) HAVING COALESCE(1, 1) = 1 OR COALESCE(1, 1)
IS NOT NULL
+-- !query schema
+struct<1:int>
+-- !query output
+1
+
+
+-- !query
+SELECT col1 FROM VALUES (1) t1 GROUP BY col1 HAVING (
+ SELECT MAX(t2.col1) FROM VALUES (1) t2 WHERE t2.col1 == MAX(t1.col1) GROUP
BY t2.col1 HAVING (
+ SELECT t3.col1 FROM VALUES (1) t3 WHERE t3.col1 == MAX(t2.col1)
+ )
+)
+-- !query schema
+struct<col1:int>
+-- !query output
+1
diff --git
a/sql/core/src/test/resources/sql-tests/results/join-resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/results/join-resolution-edge-cases.sql.out
new file mode 100644
index 000000000000..da55e1c1cf5a
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/join-resolution-edge-cases.sql.out
@@ -0,0 +1,168 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 INT, col2 STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE t2(col1 INT, col2 STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM t2 as t2_1 LEFT JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3
+-- !query schema
+struct<col1:int,col2:string,col1:int,col2:string>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM t2 as t2_1 RIGHT JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3
+-- !query schema
+struct<col1:int,col2:string,col1:int,col2:string>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM t2 as t2_1 CROSS JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3
+-- !query schema
+struct<col1:int,col2:string,col1:int,col2:string>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM t2 as t2_1 INNER JOIN t2 as t2_2 ON t2_1.col1 = t2_2.col1
+ NATURAL JOIN t2 as t2_3
+-- !query schema
+struct<col1:int,col2:string,col1:int,col2:string>
+-- !query output
+
+
+
+-- !query
+SELECT col2 AS alias FROM t1 as t1_1 LEFT ANTI JOIN t1 as t1_2 ON t1_1.col1 =
t1_2.col1 ORDER BY col2
+-- !query schema
+struct<alias:string>
+-- !query output
+
+
+
+-- !query
+SELECT col2 AS alias FROM t1 as t1_1 LEFT SEMI JOIN t1 as t1_2 ON t1_1.col1 =
t1_2.col1 ORDER BY col2
+-- !query schema
+struct<alias:string>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM t1 as t1_1 LEFT SEMI JOIN t1 as t1_2 ORDER BY col2
+-- !query schema
+struct<col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM t1 as t1_1 LEFT SEMI JOIN t1 as t1_2 GROUP BY ALL HAVING
MIN(col2) > 1
+-- !query schema
+struct<col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT 1 FROM t2 NATURAL JOIN t1 JOIN t2 ON t2.col1 = t1.col1 WHERE t2.col2 = 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "AMBIGUOUS_REFERENCE",
+ "sqlState" : "42704",
+ "messageParameters" : {
+ "name" : "`t2`.`col1`",
+ "referenceNames" : "[`spark_catalog`.`default`.`t2`.`col1`,
`spark_catalog`.`default`.`t2`.`col1`]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 45,
+ "stopIndex" : 51,
+ "fragment" : "t2.col1"
+ } ]
+}
+
+
+-- !query
+SELECT 1 FROM t2 NATURAL JOIN t1 JOIN t2 ON t2.col1 = t1.col1 GROUP BY t2.col2
HAVING t2.col2 = 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "AMBIGUOUS_REFERENCE",
+ "sqlState" : "42704",
+ "messageParameters" : {
+ "name" : "`t2`.`col1`",
+ "referenceNames" : "[`spark_catalog`.`default`.`t2`.`col1`,
`spark_catalog`.`default`.`t2`.`col1`]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 45,
+ "stopIndex" : 51,
+ "fragment" : "t2.col1"
+ } ]
+}
+
+
+-- !query
+SELECT 1 FROM t2 NATURAL JOIN t1 JOIN t2 ON t2.col1 = t1.col1 ORDER BY t2.col2
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "AMBIGUOUS_REFERENCE",
+ "sqlState" : "42704",
+ "messageParameters" : {
+ "name" : "`t2`.`col1`",
+ "referenceNames" : "[`spark_catalog`.`default`.`t2`.`col1`,
`spark_catalog`.`default`.`t2`.`col1`]"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 45,
+ "stopIndex" : 51,
+ "fragment" : "t2.col1"
+ } ]
+}
+
+
+-- !query
+DROP TABLE t1
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP TABLE t2
+-- !query schema
+struct<>
+-- !query output
+
diff --git a/sql/core/src/test/resources/sql-tests/results/order-by.sql.out
b/sql/core/src/test/resources/sql-tests/results/order-by.sql.out
index 6ce8d395fb0d..0fb46cc28646 100644
--- a/sql/core/src/test/resources/sql-tests/results/order-by.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/order-by.sql.out
@@ -458,6 +458,54 @@ struct<a:int,b:int>
1 1
+-- !query
+SELECT col1, col1 AS a FROM VALUES(1) GROUP BY col1 ORDER BY col1 ASC
+-- !query schema
+struct<col1:int,a:int>
+-- !query output
+1 1
+
+
+-- !query
+SELECT col1 AS a, col1 FROM VALUES(1) GROUP BY col1 ORDER BY col1 ASC
+-- !query schema
+struct<a:int,col1:int>
+-- !query output
+1 1
+
+
+-- !query
+SELECT make_date(col1, col2, col3) AS a, a AS b FROM VALUES(1,2,3) GROUP BY
make_date(col1, col2, col3) ORDER BY make_date(col1, col2, col3)
+-- !query schema
+struct<a:date,b:date>
+-- !query output
+0001-02-03 0001-02-03
+
+
+-- !query
+SELECT 1 AS a, 1 / a AS b, ZEROIFNULL(SUM(col1)) FROM VALUES(1) GROUP BY 1
ORDER BY ZEROIFNULL(SUM(col1))
+-- !query schema
+struct<a:int,b:double,zeroifnull(sum(col1)):bigint>
+-- !query output
+1 1.0 1
+
+
+-- !query
+SELECT col1 AS a, SUM(col2) AS b, CASE WHEN col1 = 1 THEN 1 END AS c FROM
VALUES(1,2) GROUP BY col1 ORDER BY CASE WHEN col1 = 1 THEN 1 END ASC
+-- !query schema
+struct<a:int,b:bigint,c:int>
+-- !query output
+1 2 1
+
+
+-- !query
+SELECT col1+1 AS ltrl2, col1+1 AS ltrl1 FROM VALUES(1) GROUP BY col1+1 ORDER
BY col1+1
+-- !query schema
+struct<ltrl2:int,ltrl1:int>
+-- !query output
+2 2
+
+
-- !query
DROP VIEW IF EXISTS testData
-- !query schema
diff --git
a/sql/core/src/test/resources/sql-tests/results/runtime-replaceable-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/results/runtime-replaceable-edge-cases.sql.out
new file mode 100644
index 000000000000..6fee715dc73e
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/runtime-replaceable-edge-cases.sql.out
@@ -0,0 +1,24 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+SELECT xpath_string('abc', date_format('X', 'unknown'))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkDateTimeException
+{
+ "errorClass" : "CAST_INVALID_INPUT",
+ "sqlState" : "22018",
+ "messageParameters" : {
+ "ansiConfig" : "\"spark.sql.ansi.enabled\"",
+ "expression" : "'X'",
+ "sourceType" : "\"STRING\"",
+ "targetType" : "\"TIMESTAMP\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 28,
+ "stopIndex" : 54,
+ "fragment" : "date_format('X', 'unknown')"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/results/session-variable-precedence.sql.out
b/sql/core/src/test/resources/sql-tests/results/session-variable-precedence.sql.out
new file mode 100644
index 000000000000..7c724dbc9d0d
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/session-variable-precedence.sql.out
@@ -0,0 +1,79 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 INT, col2 STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DECLARE all = 1
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DECLARE a = 1
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT col1, SUM(col2) FROM t1 GROUP BY ALL
+-- !query schema
+struct<col1:int,sum(col2):double>
+-- !query output
+
+
+
+-- !query
+SELECT col1, SUM(col2) FROM t1 GROUP BY ALL ORDER BY ALL
+-- !query schema
+struct<col1:int,sum(col2):double>
+-- !query output
+
+
+
+-- !query
+SELECT col1, col2 FROM t1 ORDER BY ALL
+-- !query schema
+struct<col1:int,col2:string>
+-- !query output
+
+
+
+-- !query
+SELECT col1 AS a, SUM(col2) FROM t1 GROUP BY a
+-- !query schema
+struct<a:int,sum(col2):double>
+-- !query output
+
+
+
+-- !query
+DROP TEMP VARIABLE a
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP TEMP VARIABLE all
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP TABLE t1
+-- !query schema
+struct<>
+-- !query output
+
diff --git
a/sql/core/src/test/resources/sql-tests/results/subquery/resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/results/subquery/resolution-edge-cases.sql.out
new file mode 100644
index 000000000000..ba3d2826c38f
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/subquery/resolution-edge-cases.sql.out
@@ -0,0 +1,179 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 INT, col2 STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE t2(col1 INT)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE t3(col1 INT, col2 STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT *
+FROM (
+ SELECT (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+ FROM t3
+ GROUP BY (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+)
+-- !query schema
+struct<scalarsubquery(col1):int>
+-- !query output
+
+
+
+-- !query
+SELECT *
+FROM (
+ SELECT 1 IN (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+ FROM t3
+ GROUP BY 1 IN (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+)
+-- !query schema
+struct<(1 IN (listquery(col1))):boolean>
+-- !query output
+
+
+
+-- !query
+SELECT *
+FROM (
+ SELECT EXISTS (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+ FROM t3
+ GROUP BY EXISTS (
+ SELECT t1.col1
+ FROM t1
+ WHERE t3.col1 = t1.col2
+ LIMIT 1
+ )
+)
+-- !query schema
+struct<exists(col1):boolean>
+-- !query output
+
+
+
+-- !query
+SELECT col1 IN (SELECT col1 FROM t2)
+FROM t2
+GROUP BY col1 IN (SELECT col1 FROM t2), col1
+ORDER BY col1 IN (SELECT col1 FROM t2)
+-- !query schema
+struct<(col1 IN (listquery())):boolean>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM t2 GROUP BY col1 IN (SELECT col1 FROM t2), col1 ORDER BY col1
IN (SELECT col1 FROM t2)
+-- !query schema
+struct<col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM t2 GROUP BY col1 ORDER BY 1 IN (SELECT 1)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.UNSUPPORTED_IN_EXISTS_SUBQUERY",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Sort [1 IN (list#x []) ASC NULLS FIRST], true\n: +- Project
[1 AS 1#x]\n: +- OneRowRelation\n+- Aggregate [col1#x], [col1#x]\n +-
SubqueryAlias spark_catalog.default.t2\n +- Relation
spark_catalog.default.t2[col1#x] parquet\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 46,
+ "stopIndex" : 58,
+ "fragment" : "IN (SELECT 1)"
+ } ]
+}
+
+
+-- !query
+SELECT col1 AS a, a + 1 FROM t2 GROUP BY col1 ORDER BY 1 IN (SELECT 1)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.UNSUPPORTED_IN_EXISTS_SUBQUERY",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Sort [1 IN (list#x []) ASC NULLS FIRST], true\n: +- Project
[1 AS 1#x]\n: +- OneRowRelation\n+- Project [a#x, (a#x + 1) AS
(lateralAliasReference(a) + 1)#x]\n +- Project [col1#x, col1#x AS a#x]\n
+- Aggregate [col1#x], [col1#x]\n +- SubqueryAlias
spark_catalog.default.t2\n +- Relation
spark_catalog.default.t2[col1#x] parquet\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 58,
+ "stopIndex" : 70,
+ "fragment" : "IN (SELECT 1)"
+ } ]
+}
+
+
+-- !query
+DROP TABLE t1
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP TABLE t2
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP TABLE t3
+-- !query schema
+struct<>
+-- !query output
+
diff --git a/sql/core/src/test/resources/sql-tests/results/timezone.sql.out
b/sql/core/src/test/resources/sql-tests/results/timezone.sql.out
index 5f0fdef50e3d..c333b195607e 100644
--- a/sql/core/src/test/resources/sql-tests/results/timezone.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timezone.sql.out
@@ -1,4 +1,12 @@
-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
-- !query
SET TIME ZONE 'Asia/Hong_Kong'
-- !query schema
@@ -177,3 +185,19 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "SET TIME ZONE INTERVAL 10 HOURS 1 MILLISECOND"
} ]
}
+
+
+-- !query
+SELECT NANVL(col1, NULL) FROM t1
+-- !query schema
+struct<nanvl(col1, NULL):double>
+-- !query output
+
+
+
+-- !query
+DROP TABLE t1
+-- !query schema
+struct<>
+-- !query output
+
diff --git
a/sql/core/src/test/resources/sql-tests/results/type-coercion-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/results/type-coercion-edge-cases.sql.out
new file mode 100644
index 000000000000..da0ceafa8938
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/type-coercion-edge-cases.sql.out
@@ -0,0 +1,63 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 TIMESTAMP, col2 MAP<BIGINT, DOUBLE>)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT MEAN(col1) FROM t1
+-- !query schema
+struct<mean(col1):double>
+-- !query output
+NULL
+
+
+-- !query
+SELECT col2.field FROM t1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkNumberFormatException
+{
+ "errorClass" : "CAST_INVALID_INPUT",
+ "sqlState" : "22018",
+ "messageParameters" : {
+ "ansiConfig" : "\"spark.sql.ansi.enabled\"",
+ "expression" : "'field'",
+ "sourceType" : "\"STRING\"",
+ "targetType" : "\"BIGINT\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 17,
+ "fragment" : "col2.field"
+ } ]
+}
+
+
+-- !query
+SELECT NULL:a AS b
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkException
+{
+ "errorClass" : "INTERNAL_ERROR",
+ "sqlState" : "XX000",
+ "messageParameters" : {
+ "message" : "Cannot find main error class 'COLUMN_IS_NOT_VARIANT_TYPE'"
+ }
+}
+
+
+-- !query
+DROP TABLE t1
+-- !query schema
+struct<>
+-- !query output
+
diff --git
a/sql/core/src/test/resources/sql-tests/results/union-resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/results/union-resolution-edge-cases.sql.out
new file mode 100644
index 000000000000..bd49d52fb861
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/union-resolution-edge-cases.sql.out
@@ -0,0 +1,119 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TABLE t1(col1 TIMESTAMP, col2 STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE t2(col1 INT, col2 STRING)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+WITH cte AS (
+ SELECT col1, col1 FROM t1
+ UNION
+ SELECT col1, col1 FROM t1
+)
+SELECT col1 FROM cte
+-- !query schema
+struct<col1:timestamp>
+-- !query output
+
+
+
+-- !query
+WITH cte AS (
+ SELECT col2, from_utc_timestamp(col1, 'unknown'), col2 FROM t1
+ UNION ALL
+ SELECT col2, from_utc_timestamp(col1, 'unknown'), col2 FROM t1
+)
+SELECT * FROM cte
+-- !query schema
+struct<col2:string,from_utc_timestamp(col1, unknown):timestamp,col2:string>
+-- !query output
+
+
+
+-- !query
+SELECT col1 FROM t3 WHERE (col1, col1) IN (SELECT col1, col1 UNION SELECT
col1, col1)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`t3`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 18,
+ "stopIndex" : 19,
+ "fragment" : "t3"
+ } ]
+}
+
+
+-- !query
+SELECT col1, TRIM(col2), col1 FROM t2 UNION SELECT col1, col2, col1 FROM t2
+-- !query schema
+struct<col1:int,trim(col2):string,col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT col1, TRIM(col2), col1 FROM t2 MINUS SELECT col1, col2, col1 FROM t2
+-- !query schema
+struct<col1:int,trim(col2):string,col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT col1, LTRIM(col2), col1 FROM t2 MINUS SELECT col1, col2, col1 FROM t2
+-- !query schema
+struct<col1:int,ltrim(col2):string,col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT col1, RTRIM(col2), col1 FROM t2 EXCEPT SELECT col1, col2, col1 FROM t2
+-- !query schema
+struct<col1:int,rtrim(col2):string,col1:int>
+-- !query output
+
+
+
+-- !query
+SELECT col1, LOWER(col2), col1 FROM t2 INTERSECT SELECT col1, col2, col1 FROM
t2
+-- !query schema
+struct<col1:int,lower(col2):string,col1:int>
+-- !query output
+
+
+
+-- !query
+DROP TABLE t1
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP TABLE t2
+-- !query schema
+struct<>
+-- !query output
+
diff --git a/sql/core/src/test/resources/sql-tests/results/using-join.sql.out
b/sql/core/src/test/resources/sql-tests/results/using-join.sql.out
index 1e71191de873..6db08041f267 100644
--- a/sql/core/src/test/resources/sql-tests/results/using-join.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/using-join.sql.out
@@ -445,3 +445,14 @@ WHERE t1.key NOT LIKE 'bb.%'
struct<key:string>
-- !query output
a
+
+
+-- !query
+SELECT *
+FROM
+ ( SELECT col1 FROM VALUES(false) ) LEFT JOIN ( SELECT col1 FROM
VALUES(false) ) b USING(col1)
+WHERE b.col1
+-- !query schema
+struct<col1:boolean>
+-- !query output
+
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
