Merging upstream version 26.0.1.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 55 insertions, 0 deletions

diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 2640145..ecb6eee 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -191,6 +191,10 @@ SELECT SOME_UDF(data).* FROM t;
 SELECT SOME_UDF(t.data).* FROM t AS t;
 
 # execute: false
+SELECT p.* FROM p UNION ALL SELECT p2.* FROM p2;
+SELECT p.* FROM p AS p UNION ALL SELECT p2.* FROM p2 AS p2;
+
+# execute: false
 # allow_partial_qualification: true
 # validate_qualify_columns: false
 SELECT a + 1 AS i, missing_column FROM x;
@@ -201,6 +205,30 @@ SELECT x.a + 1 AS i, missing_column AS missing_column FROM x AS x;
 SELECT s, arr1, arr2 FROM arrays_test LEFT ARRAY JOIN arr1, arrays_test.arr2;
 SELECT arrays_test.s AS s, arrays_test.arr1 AS arr1, arrays_test.arr2 AS arr2 FROM arrays_test AS arrays_test LEFT ARRAY JOIN arrays_test.arr1, arrays_test.arr2;
 
+# execute: false
+# dialect: snowflake
+WITH employees AS (
+    SELECT *
+    FROM (VALUES ('President', 1, NULL),
+                 ('Vice President Engineering', 10, 1),
+                 ('Programmer', 100, 10),
+                 ('QA Engineer', 101, 10),
+                 ('Vice President HR', 20, 1),
+                 ('Health Insurance Analyst', 200, 20)
+    ) AS t(title, employee_ID, manager_ID)
+)
+SELECT
+  employee_ID,
+  manager_ID,
+  title,
+  level
+FROM employees
+START WITH title = 'President'
+CONNECT BY manager_ID = PRIOR employee_id
+ORDER BY
+  employee_ID NULLS LAST;
+WITH EMPLOYEES AS (SELECT T.TITLE AS TITLE, T.EMPLOYEE_ID AS EMPLOYEE_ID, T.MANAGER_ID AS MANAGER_ID FROM (VALUES ('President', 1, NULL), ('Vice President Engineering', 10, 1), ('Programmer', 100, 10), ('QA Engineer', 101, 10), ('Vice President HR', 20, 1), ('Health Insurance Analyst', 200, 20)) AS T(TITLE, EMPLOYEE_ID, MANAGER_ID)) SELECT EMPLOYEES.EMPLOYEE_ID AS EMPLOYEE_ID, EMPLOYEES.MANAGER_ID AS MANAGER_ID, EMPLOYEES.TITLE AS TITLE, EMPLOYEES.LEVEL AS LEVEL FROM EMPLOYEES AS EMPLOYEES START WITH EMPLOYEES.TITLE = 'President' CONNECT BY EMPLOYEES.MANAGER_ID = PRIOR EMPLOYEES.EMPLOYEE_ID ORDER BY EMPLOYEE_ID;
+
 --------------------------------------
 -- Derived tables
 --------------------------------------
@@ -727,3 +755,30 @@ SELECT y.b AS b FROM ((SELECT x.a AS a FROM x AS x) AS _q_0 INNER JOIN y AS y ON
 
 SELECT a, c FROM x TABLESAMPLE SYSTEM (10 ROWS) CROSS JOIN y TABLESAMPLE SYSTEM (10 ROWS);
 SELECT x.a AS a, y.c AS c FROM x AS x TABLESAMPLE SYSTEM (10 ROWS) CROSS JOIN y AS y TABLESAMPLE SYSTEM (10 ROWS);
+
+--------------------------------------
+-- Snowflake allows column alias to be used in almost all clauses
+--------------------------------------
+# title: Snowflake column alias in JOIN
+# dialect: snowflake
+# execute: false
+SELECT x.a AS foo FROM x JOIN y ON foo = y.b;
+SELECT X.A AS FOO FROM X AS X JOIN Y AS Y ON X.A = Y.B;
+
+# title: Snowflake column alias in QUALIFY
+# dialect: snowflake
+# execute: false
+SELECT x.a AS foo FROM x QUALIFY foo = 1;
+SELECT X.A AS FOO FROM X AS X QUALIFY X.A = 1;
+
+# title: Snowflake column alias in GROUP BY
+# dialect: snowflake
+# execute: false
+SELECT x.a AS foo FROM x GROUP BY foo = 1;
+SELECT X.A AS FOO FROM X AS X GROUP BY X.A = 1;
+
+# title: Snowflake column alias in WHERE
+# dialect: snowflake
+# execute: false
+SELECT x.a AS foo FROM x WHERE foo = 1;
+SELECT X.A AS FOO FROM X AS X WHERE X.A = 1;
\ No newline at end of file