summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures')
-rw-r--r--tests/fixtures/identity.sql31
-rw-r--r--tests/fixtures/optimizer/annotate_types.sql33
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql4
-rw-r--r--tests/fixtures/optimizer/optimizer.sql46
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql94
-rw-r--r--tests/fixtures/optimizer/qualify_columns_ddl.sql14
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql11
-rw-r--r--tests/fixtures/optimizer/simplify.sql56
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql142
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql14
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql312
-rw-r--r--tests/fixtures/pretty.sql39
12 files changed, 420 insertions, 376 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index d51a978..013eed8 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -53,6 +53,7 @@ x >> 1 | 1 & 1 ^ 1
x || y
x[ : ]
x[1 : ]
+x[ : 2]
x[1 : 2]
x[-4 : -1]
1 - -1
@@ -115,7 +116,7 @@ ARRAY(foo, time)
ARRAY(LENGTH(waiter_name) > 0)
ARRAY_CONTAINS(x, 1)
x.EXTRACT(1)
-EXTRACT(x FROM y)
+EXTRACT(X FROM y)
EXTRACT(DATE FROM y)
EXTRACT(WEEK(monday) FROM created_at)
CONCAT_WS('-', 'a', 'b')
@@ -203,6 +204,7 @@ USE ROLE x
USE WAREHOUSE x
USE DATABASE x
USE SCHEMA x.y
+USE CATALOG abc
NOT 1
NOT NOT 1
SELECT * FROM test
@@ -594,6 +596,7 @@ CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (CYCLE))
CREATE TABLE customer (period INT NOT NULL)
CREATE TABLE foo (baz_id INT REFERENCES baz (id) DEFERRABLE)
CREATE TABLE foo (baz CHAR(4) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC COMPRESS 'a')
+CREATE TABLE db.foo (id INT NOT NULL, valid_date DATE FORMAT 'YYYY-MM-DD', measurement INT COMPRESS)
CREATE TABLE foo (baz DATE FORMAT 'YYYY/MM/DD' TITLE 'title' INLINE LENGTH 1 COMPRESS ('a', 'b'))
CREATE TABLE t (title TEXT)
CREATE TABLE foo (baz INT, inline TEXT)
@@ -642,6 +645,7 @@ CREATE TABLE T3 AS (SELECT DISTINCT A FROM T1 EXCEPT (SELECT A FROM T2) LIMIT 1)
DESCRIBE x
DESCRIBE EXTENDED a.b
DESCRIBE FORMATTED a.b
+DESCRIBE SELECT 1
DROP INDEX a.b.c
DROP FUNCTION a.b.c (INT)
DROP MATERIALIZED VIEW x.y.z
@@ -732,6 +736,8 @@ SELECT (WITH x AS (SELECT 1 AS y) SELECT * FROM x) AS z
SELECT ((SELECT 1) + 1)
SELECT * FROM project.dataset.INFORMATION_SCHEMA.TABLES
SELECT CAST(x AS INT) /* comment */ FROM foo
+SELECT c /* c1 /* c2 */ c3 */
+SELECT c /* c1 /* c2 /* c3 */ */ */
SELECT c /* c1 */ AS alias /* c2 */
SELECT a /* x */, b /* x */
SELECT a /* x */ /* y */ /* z */, b /* k */ /* m */
@@ -771,17 +777,13 @@ ALTER TABLE orders DROP PARTITION(dt = '2014-05-14', country = 'IN'), PARTITION(
ALTER TABLE mydataset.mytable DELETE WHERE x = 1
ALTER TABLE table1 RENAME COLUMN c1 TO c2
ALTER TABLE table1 RENAME COLUMN IF EXISTS c1 TO c2
+ALTER TABLE table1 RENAME TO table2
+ALTER VIEW view1 AS SELECT a, b, c FROM table1
+ALTER VIEW view1 AS SELECT a, b, c FROM table1 UNION ALL SELECT a, b, c FROM table2
+ALTER VIEW view1 AS SELECT a, b, c FROM table1 UNION ALL SELECT a, b, c FROM table2 LIMIT 100
SELECT div.a FROM test_table AS div
WITH view AS (SELECT 1 AS x) SELECT * FROM view
ARRAY<STRUCT<INT, DOUBLE, ARRAY<INT>>>
-ARRAY<INT>[1, 2, 3]
-ARRAY<INT>[]
-STRUCT<x VARCHAR(10)>
-STRUCT<x VARCHAR(10)>("bla")
-STRUCT<VARCHAR(10)>("bla")
-STRUCT<INT>(5)
-STRUCT<DATE>("2011-05-05")
-STRUCT<x INT, y TEXT>(1, t.str_col)
STRUCT<int INT>
SELECT CAST(NULL AS ARRAY<INT>) IS NULL AS array_is_null
ALTER TABLE "schema"."tablename" ADD CONSTRAINT "CHK_Name" CHECK (NOT "IdDwh" IS NULL AND "IdDwh" <> (0))
@@ -867,4 +869,13 @@ SELECT only
TRUNCATE(a, b)
SELECT enum
SELECT unlogged
-SELECT name \ No newline at end of file
+SELECT name
+SELECT copy
+SELECT rollup
+SELECT unnest
+SELECT cube, cube.x FROM cube
+SELECT * FROM a STRAIGHT_JOIN b
+SELECT COUNT(DISTINCT "foo bar") FROM (SELECT 1 AS "foo bar") AS t
+SELECT vector
+WITH all AS (SELECT 1 AS count) SELECT all.count FROM all
+SELECT rename
diff --git a/tests/fixtures/optimizer/annotate_types.sql b/tests/fixtures/optimizer/annotate_types.sql
index e781765..f608851 100644
--- a/tests/fixtures/optimizer/annotate_types.sql
+++ b/tests/fixtures/optimizer/annotate_types.sql
@@ -1,13 +1,25 @@
5;
INT;
+-5;
+INT;
+
+~5;
+INT;
+
+(5);
+INT;
+
5.3;
DOUBLE;
'bla';
VARCHAR;
-True;
+true;
+bool;
+
+not true;
bool;
false;
@@ -15,6 +27,13 @@ bool;
null;
null;
+
+null and false;
+bool;
+
+null + 1;
+int;
+
CASE WHEN x THEN NULL ELSE 1 END;
INT;
@@ -55,3 +74,15 @@ STRUCT<a INT, b DOUBLE>;
# dialect: presto
ROW(1, 2.5, 'foo');
STRUCT<INT, DOUBLE, VARCHAR>;
+
+# dialect: bigquery
+EXTRACT(date from x);
+DATE;
+
+# dialect: bigquery
+EXTRACT(time from x);
+TIME;
+
+# dialect: bigquery
+EXTRACT(day from x);
+INT;
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index e4c78b7..66c6c95 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -52,6 +52,10 @@ SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE CASE WHEN COALESCE("x"."b" <> 0, 1 <
DATE('2023-01-01');
CAST('2023-01-01' AS DATE);
+-- Some dialects only allow dates
+DATE('2023-01-01 00:00:00');
+DATE('2023-01-01 00:00:00');
+
TIMESTAMP('2023-01-01');
CAST('2023-01-01' AS TIMESTAMP);
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 37ef4fd..76fc16d 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -547,7 +547,8 @@ FROM (
"tb"."b" AS "b",
"tb"."c" AS "c"
FROM "sc"."tb" AS "tb"
-) AS "_q_0" PIVOT(SUM("_q_0"."c") FOR "_q_0"."b" IN ('x', 'y', 'z')) AS "_q_1";
+) AS "_q_0"
+PIVOT(SUM("_q_0"."c") FOR "_q_0"."b" IN ('x', 'y', 'z')) AS "_q_1";
# title: pivoted source with explicit selections where one of them is excluded & selected at the same time
# note: we need to respect the exclude when selecting * from pivoted source and not include the computed column twice
@@ -564,7 +565,8 @@ FROM (
"tb"."b" AS "b",
"tb"."c" AS "c"
FROM "sc"."tb" AS "tb"
-) AS "_q_0" PIVOT(SUM("_q_0"."c") FOR "_q_0"."b" IN ('x', 'y', 'z')) AS "_q_1";
+) AS "_q_0"
+PIVOT(SUM("_q_0"."c") FOR "_q_0"."b" IN ('x', 'y', 'z')) AS "_q_1";
# title: pivoted source with implicit selections
# execute: false
@@ -579,7 +581,8 @@ FROM (
"u"."g" AS "g",
"u"."h" AS "h"
FROM "u" AS "u"
-) AS "_q_0" PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "_q_1";
+) AS "_q_0"
+PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "_q_1";
# title: selecting explicit qualified columns from pivoted source with explicit selections
# execute: false
@@ -592,7 +595,8 @@ FROM (
"u"."f" AS "f",
"u"."h" AS "h"
FROM "u" AS "u"
-) AS "_q_0" PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "piv";
+) AS "_q_0"
+PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "piv";
# title: selecting explicit unqualified columns from pivoted source with implicit selections
# execute: false
@@ -600,7 +604,8 @@ SELECT x, y FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
SELECT
"_q_0"."x" AS "x",
"_q_0"."y" AS "y"
-FROM "u" AS "u" PIVOT(SUM("u"."f") FOR "u"."h" IN ('x', 'y')) AS "_q_0";
+FROM "u" AS "u"
+PIVOT(SUM("u"."f") FOR "u"."h" IN ('x', 'y')) AS "_q_0";
# title: selecting all columns from a pivoted CTE source, using alias for the aggregation and generating bigquery
# execute: false
@@ -617,7 +622,8 @@ SELECT
`_q_0`.`g` AS `g`,
`_q_0`.`sum_x` AS `sum_x`,
`_q_0`.`sum_y` AS `sum_y`
-FROM `u_cte` AS `u_cte` PIVOT(SUM(`u_cte`.`f`) AS `sum` FOR `u_cte`.`h` IN ('x', 'y')) AS `_q_0`;
+FROM `u_cte` AS `u_cte`
+PIVOT(SUM(`u_cte`.`f`) AS `sum` FOR `u_cte`.`h` IN ('x', 'y')) AS `_q_0`;
# title: selecting all columns from a pivoted source and generating snowflake
# execute: false
@@ -627,7 +633,8 @@ SELECT
"_q_0"."G" AS "G",
"_q_0"."'x'" AS "'x'",
"_q_0"."'y'" AS "'y'"
-FROM "U" AS "U" PIVOT(SUM("U"."F") FOR "U"."H" IN ('x', 'y')) AS "_q_0";
+FROM "U" AS "U"
+PIVOT(SUM("U"."F") FOR "U"."H" IN ('x', 'y')) AS "_q_0";
# title: selecting all columns from a pivoted source and generating spark
# note: spark doesn't allow pivot aliases or qualified columns for the pivot's "field" (`h`)
@@ -641,7 +648,8 @@ SELECT
FROM (
SELECT
*
- FROM `u` AS `u` PIVOT(SUM(`u`.`f`) FOR `h` IN ('x', 'y'))
+ FROM `u` AS `u`
+ PIVOT(SUM(`u`.`f`) FOR `h` IN ('x', 'y'))
) AS `_q_0`;
# title: selecting all columns from a pivoted source, pivot has column aliases
@@ -674,7 +682,8 @@ WITH "SOURCE" AS (
SELECT
"FINAL"."ID" AS "ID",
"FINAL"."TIMESTAMP_1" AS "TIMESTAMP_1"
-FROM "SOURCE" AS "SOURCE" PIVOT(MAX("SOURCE"."VALUE") FOR "SOURCE"."KEY" IN ('a', 'b', 'c')) AS "FINAL"("ID", "TIMESTAMP_1", "TIMESTAMP_2", "COL_1", "COL_2", "COL_3");
+FROM "SOURCE" AS "SOURCE"
+PIVOT(MAX("SOURCE"."VALUE") FOR "SOURCE"."KEY" IN ('a', 'b', 'c')) AS "FINAL"("ID", "TIMESTAMP_1", "TIMESTAMP_2", "COL_1", "COL_2", "COL_3");
# title: unpivoted table source with a single value column, unpivot columns can't be qualified
# execute: false
@@ -685,7 +694,8 @@ SELECT
"_q_0"."DEPT" AS "DEPT",
"_q_0"."MONTH" AS "MONTH",
"_q_0"."SALES" AS "SALES"
-FROM "M_SALES" AS "M_SALES"("EMPID", "DEPT", "JAN", "FEB") UNPIVOT("SALES" FOR "MONTH" IN ("JAN", "FEB")) AS "_q_0"
+FROM "M_SALES" AS "M_SALES"("EMPID", "DEPT", "JAN", "FEB")
+UNPIVOT("SALES" FOR "MONTH" IN ("JAN", "FEB")) AS "_q_0"
ORDER BY
"_q_0"."EMPID";
@@ -704,7 +714,8 @@ FROM (
"m_sales"."jan" AS "jan",
"m_sales"."feb" AS "feb"
FROM "m_sales" AS "m_sales"
-) AS "m_sales" UNPIVOT("sales" FOR "month" IN ("m_sales"."jan", "m_sales"."feb")) AS "unpiv"("a", "b", "c", "d");
+) AS "m_sales"
+UNPIVOT("sales" FOR "month" IN ("m_sales"."jan", "m_sales"."feb")) AS "unpiv"("a", "b", "c", "d");
# title: unpivoted derived table source with a single value column
# execute: false
@@ -722,20 +733,22 @@ FROM (
"M_SALES"."JAN" AS "JAN",
"M_SALES"."FEB" AS "FEB"
FROM "M_SALES" AS "M_SALES"
-) AS "M_SALES" UNPIVOT("SALES" FOR "MONTH" IN ("JAN", "FEB")) AS "_q_0"
+) AS "M_SALES"
+UNPIVOT("SALES" FOR "MONTH" IN ("JAN", "FEB")) AS "_q_0"
ORDER BY
"_q_0"."EMPID";
# title: unpivoted table source with a single value column, unpivot columns can be qualified
# execute: false
# dialect: bigquery
-# note: the named columns aren't supported by BQ but we add them here to avoid defining a schema
+# note: the named columns aren not supported by BQ but we add them here to avoid defining a schema
SELECT * FROM produce AS produce(product, q1, q2, q3, q4) UNPIVOT(sales FOR quarter IN (q1, q2, q3, q4));
SELECT
`_q_0`.`product` AS `product`,
`_q_0`.`quarter` AS `quarter`,
`_q_0`.`sales` AS `sales`
-FROM `produce` AS `produce` UNPIVOT(`sales` FOR `quarter` IN (`produce`.`q1`, `produce`.`q2`, `produce`.`q3`, `produce`.`q4`)) AS `_q_0`;
+FROM `produce` AS `produce`
+UNPIVOT(`sales` FOR `quarter` IN (`produce`.`q1`, `produce`.`q2`, `produce`.`q3`, `produce`.`q4`)) AS `_q_0`;
# title: unpivoted table source with multiple value columns
# execute: false
@@ -746,7 +759,8 @@ SELECT
`_q_0`.`semesters` AS `semesters`,
`_q_0`.`first_half_sales` AS `first_half_sales`,
`_q_0`.`second_half_sales` AS `second_half_sales`
-FROM `produce` AS `produce` UNPIVOT((`first_half_sales`, `second_half_sales`) FOR `semesters` IN ((`produce`.`q1`, `produce`.`q2`) AS 'semester_1', (`produce`.`q3`, `produce`.`q4`) AS 'semester_2')) AS `_q_0`;
+FROM `produce` AS `produce`
+UNPIVOT((`first_half_sales`, `second_half_sales`) FOR `semesters` IN ((`produce`.`q1`, `produce`.`q2`) AS 'semester_1', (`produce`.`q3`, `produce`.`q4`) AS 'semester_2')) AS `_q_0`;
# title: quoting is preserved
# dialect: snowflake
@@ -824,7 +838,7 @@ SELECT
FROM `bigquery-public-data.GooGle_tReNDs.TOp_TeRmS` AS `TOp_TeRmS`
WHERE
`TOp_TeRmS`.`rank` = 1
- AND `TOp_TeRmS`.`refresh_date` >= DATE_SUB(CURRENT_DATE, INTERVAL 2 WEEK)
+ AND `TOp_TeRmS`.`refresh_date` >= DATE_SUB(CURRENT_DATE, INTERVAL '2' WEEK)
GROUP BY
`day`,
`top_term`,
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index b020a27..68c0caa 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -131,6 +131,10 @@ SELECT DATE(x.a) AS _col_0, DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(
SELECT (SELECT MIN(a) FROM UNNEST([1, 2])) AS f FROM x GROUP BY 1;
SELECT (SELECT MIN(_q_0.a) AS _col_0 FROM UNNEST(ARRAY(1, 2)) AS _q_0) AS f FROM x AS x GROUP BY 1;
+# dialect: bigquery
+WITH x AS (select 'a' as a, 1 as b) SELECT x.a AS c, y.a as d, SUM(x.b) AS y, FROM x join x as y on x.a = y.a group by 1, 2;
+WITH x AS (SELECT 'a' AS a, 1 AS b) SELECT x.a AS c, y.a AS d, SUM(x.b) AS y FROM x AS x JOIN x AS y ON x.a = y.a GROUP BY x.a, 2;
+
SELECT SUM(x.a) AS c FROM x JOIN y ON x.b = y.b GROUP BY c;
SELECT SUM(x.a) AS c FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY y.c;
@@ -210,6 +214,9 @@ SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) A
SELECT x.a FROM x AS x JOIN (SELECT * FROM x) AS y ON x.a = y.a;
SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a, x.b AS b FROM x AS x) AS y ON x.a = y.a;
+SELECT a FROM x as t1 /* there is comment */;
+SELECT t1.a AS a FROM x AS t1 /* there is comment */;
+
--------------------------------------
-- Joins
--------------------------------------
@@ -314,6 +321,33 @@ SELECT s.a AS a, s.b AS b FROM (SELECT t.a AS a, t.b AS b FROM t AS t) AS s;
SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) AS s(b);
SELECT s.b AS b FROM (SELECT t1.b AS b FROM t1 AS t1 UNION ALL SELECT t2.b AS b FROM t2 AS t2) AS s;
+# dialect: bigquery
+# execute: false
+WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct("test" AS col1, Struct(3 AS col2) AS lvl2) AS lvl1) AS col), tbl2 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct("test" AS col1, Struct(3 AS col2) AS lvl2) AS lvl1) AS col) SELECT tbl1.col.*, tbl2.col.* FROM tbl1, tbl2;
+WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct('test' AS col1, Struct(3 AS col2) AS lvl2) AS lvl1) AS col), tbl2 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct('test' AS col1, Struct(3 AS col2) AS lvl2) AS lvl1) AS col) SELECT tbl1.col.col1 AS col1, tbl1.col.col2 AS col2, tbl1.col.lvl1 AS lvl1, tbl2.col.col1 AS col1, tbl2.col.col2 AS col2, tbl2.col.lvl1 AS lvl1 FROM tbl1 AS tbl1, tbl2 AS tbl2;
+
+# dialect: bigquery
+# execute: false
+WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct("test" AS col1, Struct(3 AS col2) AS lvl2) AS lvl1, 3 AS col3) AS col) SELECT tbl1.col.lvl1.* FROM tbl1;
+WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct('test' AS col1, Struct(3 AS col2) AS lvl2) AS lvl1, 3 AS col3) AS col) SELECT tbl1.col.lvl1.col1 AS col1, tbl1.col.lvl1.lvl2 AS lvl2 FROM tbl1 AS tbl1;
+
+# dialect: bigquery
+# execute: false
+# title: Cannot expand struct star with unnamed fields
+WITH tbl1 AS (SELECT STRUCT(1 AS col1, Struct(5 AS col1)) AS col) SELECT tbl1.col.* FROM tbl1;
+WITH tbl1 AS (SELECT STRUCT(1 AS col1, Struct(5 AS col1)) AS col) SELECT tbl1.col.* FROM tbl1 AS tbl1;
+
+# dialect: bigquery
+# execute: false
+# title: Cannot expand struct star with ambiguous fields
+WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col1) AS col) SELECT tbl1.col.* FROM tbl1;
+WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col1) AS col) SELECT tbl1.col.* FROM tbl1 AS tbl1;
+
+# title: CSV files are not scanned by default
+# execute: false
+SELECT * FROM READ_CSV('file.csv');
+SELECT * FROM READ_CSV('file.csv') AS _q_0;
+
--------------------------------------
-- CTEs
--------------------------------------
@@ -356,14 +390,14 @@ WITH player AS (SELECT player.name, player.asset.info FROM players) SELECT * FRO
WITH player AS (SELECT players.player.name AS name, players.player.asset.info AS info FROM players AS players) SELECT player.name AS name, player.info AS info FROM player AS player;
--------------------------------------
--- Except and Replace
+-- Except, Replace, Rename
--------------------------------------
# execute: false
-SELECT * REPLACE(a AS d) FROM x;
+SELECT * RENAME(a AS d) FROM x;
SELECT x.a AS d, x.b AS b FROM x AS x;
# execute: false
-SELECT * EXCEPT(b) REPLACE(a AS d) FROM x;
+SELECT * EXCEPT(b) RENAME(a AS d) FROM x;
SELECT x.a AS d FROM x AS x;
SELECT x.* EXCEPT(a), y.* FROM x, y;
@@ -387,6 +421,30 @@ SELECT x.a AS a, x.b AS b, y.b AS b FROM x AS x LEFT JOIN x AS y ON x.a = y.a;
SELECT COALESCE(CAST(t1.a AS VARCHAR), '') AS a, t2.* EXCEPT (a) FROM x AS t1, x AS t2;
SELECT COALESCE(CAST(t1.a AS VARCHAR), '') AS a, t2.b AS b FROM x AS t1, x AS t2;
+# execute: false
+SELECT * REPLACE(2 AS a) FROM x;
+SELECT 2 AS a, x.b AS b FROM x AS x;
+
+# execute: false
+SELECT * EXCEPT (a, b) REPLACE (a AS a) FROM x;
+SELECT * EXCEPT (a, b) REPLACE (x.a AS a) FROM x AS x;
+
+# execute: false
+SELECT * REPLACE(COALESCE(b, a) AS a, a as b) FROM x;
+SELECT COALESCE(x.b, x.a) AS a, x.a AS b FROM x AS x;
+
+# execute: false
+SELECT * REPLACE(1 AS a) RENAME(b as alias_b) FROM x;
+SELECT 1 AS a, x.b AS alias_b FROM x AS x;
+
+# execute: false
+SELECT * EXCEPT(a) REPLACE(COALESCE(a, b) AS b) RENAME(b AS new_b) FROM x;
+SELECT COALESCE(x.a, x.b) AS new_b FROM x AS x;
+
+# execute: false
+SELECT * REPLACE(1 AS a, a AS b) RENAME(b AS new_b) FROM x;
+SELECT 1 AS a, x.a AS new_b FROM x AS x;
+
--------------------------------------
-- Using
--------------------------------------
@@ -451,6 +509,18 @@ SELECT COALESCE(t1.a, t2.a) AS a FROM t1 AS t1 JOIN t2 AS t2 ON t1.a = t2.a;
WITH m(a) AS (SELECT 1), n(b) AS (SELECT 1) SELECT * FROM m JOIN n AS foo(a) USING (a);
WITH m AS (SELECT 1 AS a), n AS (SELECT 1 AS b) SELECT COALESCE(m.a, foo.a) AS a FROM m AS m JOIN n AS foo(a) ON m.a = foo.a;
+# title: coalesce the USING clause's columns (3 joins, 2 join columns)
+WITH t1 AS (SELECT 'x' AS id, DATE '2024-01-01' AS foo, 000 AS value), t2 AS (SELECT 'x' AS id, DATE '2024-02-02' AS foo, 123 AS value), t3 AS (SELECT 'x' AS id, DATE '2024-02-02' AS foo, 456 AS value) SELECT * FROM t1 FULL OUTER JOIN t2 USING(id, foo) FULL OUTER JOIN t3 USING(id, foo);
+WITH t1 AS (SELECT 'x' AS id, CAST('2024-01-01' AS DATE) AS foo, 000 AS value), t2 AS (SELECT 'x' AS id, CAST('2024-02-02' AS DATE) AS foo, 123 AS value), t3 AS (SELECT 'x' AS id, CAST('2024-02-02' AS DATE) AS foo, 456 AS value) SELECT COALESCE(t1.id, t2.id, t3.id) AS id, COALESCE(t1.foo, t2.foo, t3.foo) AS foo, t1.value AS value, t2.value AS value, t3.value AS value FROM t1 AS t1 FULL OUTER JOIN t2 AS t2 ON t1.id = t2.id AND t1.foo = t2.foo FULL OUTER JOIN t3 AS t3 ON COALESCE(t1.id, t2.id) = t3.id AND COALESCE(t1.foo, t2.foo) = t3.foo;
+
+# title: coalesce the USING clause's columns (3 joins, 3 join columns)
+WITH t1 AS (SELECT 'x' AS id, CAST('2024-01-01' AS DATE) AS foo, 000 AS value), t2 AS (SELECT 'x' AS id, CAST('2024-02-02' AS DATE) AS foo, 123 AS value), t3 AS (SELECT 'x' AS id, CAST('2024-02-02' AS DATE) AS foo, 456 AS value) SELECT * FROM t1 FULL OUTER JOIN t2 USING (id, foo, value) FULL OUTER JOIN t3 USING (id, foo, value);
+WITH t1 AS (SELECT 'x' AS id, CAST('2024-01-01' AS DATE) AS foo, 000 AS value), t2 AS (SELECT 'x' AS id, CAST('2024-02-02' AS DATE) AS foo, 123 AS value), t3 AS (SELECT 'x' AS id, CAST('2024-02-02' AS DATE) AS foo, 456 AS value) SELECT COALESCE(t1.id, t2.id, t3.id) AS id, COALESCE(t1.foo, t2.foo, t3.foo) AS foo, COALESCE(t1.value, t2.value, t3.value) AS value FROM t1 AS t1 FULL OUTER JOIN t2 AS t2 ON t1.id = t2.id AND t1.foo = t2.foo AND t1.value = t2.value FULL OUTER JOIN t3 AS t3 ON COALESCE(t1.id, t2.id) = t3.id AND COALESCE(t1.foo, t2.foo) = t3.foo AND COALESCE(t1.value, t2.value) = t3.value;
+
+# title: coalesce the USING clause's columns (4 joins, 2 join columns)
+WITH t1 AS (SELECT 'x' AS id, CAST('2024-01-01' AS DATE) AS foo, 000 AS value), t2 AS (SELECT 'x' AS id, CAST('2024-02-02' AS DATE) AS foo, 123 AS value), t3 AS (SELECT 'x' AS id, CAST('2024-02-02' AS DATE) AS foo, 456 AS value), t4 AS (SELECT 'x' AS id, CAST('2024-03-03' AS DATE) AS foo, 789 AS value) SELECT * FROM t1 FULL OUTER JOIN t2 USING (id, foo) FULL OUTER JOIN t3 USING (id, foo) FULL OUTER JOIN t4 USING (id, foo);
+WITH t1 AS (SELECT 'x' AS id, CAST('2024-01-01' AS DATE) AS foo, 000 AS value), t2 AS (SELECT 'x' AS id, CAST('2024-02-02' AS DATE) AS foo, 123 AS value), t3 AS (SELECT 'x' AS id, CAST('2024-02-02' AS DATE) AS foo, 456 AS value), t4 AS (SELECT 'x' AS id, CAST('2024-03-03' AS DATE) AS foo, 789 AS value) SELECT COALESCE(t1.id, t2.id, t3.id, t4.id) AS id, COALESCE(t1.foo, t2.foo, t3.foo, t4.foo) AS foo, t1.value AS value, t2.value AS value, t3.value AS value, t4.value AS value FROM t1 AS t1 FULL OUTER JOIN t2 AS t2 ON t1.id = t2.id AND t1.foo = t2.foo FULL OUTER JOIN t3 AS t3 ON COALESCE(t1.id, t2.id) = t3.id AND COALESCE(t1.foo, t2.foo) = t3.foo FULL OUTER JOIN t4 AS t4 ON COALESCE(t1.id, t2.id, t3.id) = t4.id AND COALESCE(t1.foo, t2.foo, t3.foo) = t4.foo;
+
--------------------------------------
-- Hint with table reference
--------------------------------------
@@ -523,6 +593,10 @@ SELECT t.c1 AS c1, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3);
SELECT c.f::VARCHAR(MAX) AS f, e AS e FROM a.b AS c, c.d AS e;
SELECT CAST(c.f AS VARCHAR(MAX)) AS f, e AS e FROM a.b AS c, c.d AS e;
+# dialect: bigquery
+WITH cte AS (SELECT 1 AS col) SELECT * FROM cte LEFT JOIN UNNEST((SELECT ARRAY_AGG(DISTINCT x) AS agg FROM UNNEST([1]) AS x WHERE col = 1));
+WITH cte AS (SELECT 1 AS col) SELECT * FROM cte AS cte LEFT JOIN UNNEST((SELECT ARRAY_AGG(DISTINCT x) AS agg FROM UNNEST([1]) AS x WHERE cte.col = 1));
+
--------------------------------------
-- Window functions
--------------------------------------
@@ -558,6 +632,7 @@ SELECT x.a + 1 AS i, x.a + 1 + 1 AS j, x.a + 1 + 1 + 1 AS k FROM x AS x;
# title: noop - reference comes before alias
# execute: false
+# validate_qualify_columns: false
SELECT i + 1 AS j, x.a + 1 AS i FROM x;
SELECT i + 1 AS j, x.a + 1 AS i FROM x AS x;
@@ -586,6 +661,16 @@ SELECT x.a + x.b AS f, x.a + x.b AS _col_1, x.a + x.b + 5 AS _col_2 FROM x AS x;
SELECT a, SUM(b) AS c, SUM(c) OVER(PARTITION BY a) AS d from x group by 1 ORDER BY a;
SELECT x.a AS a, SUM(x.b) AS c, SUM(SUM(x.b)) OVER (PARTITION BY x.a) AS d FROM x AS x GROUP BY x.a ORDER BY a;
+# title: we can't expand aliases corresponding to recursive CTE columns (CTE names output columns)
+# execute: false
+WITH RECURSIVE t(c) AS (SELECT 1 AS c UNION ALL SELECT c + 1 AS c FROM t WHERE c <= 10) SELECT c FROM t;
+WITH RECURSIVE t(c) AS (SELECT 1 AS c UNION ALL SELECT t.c + 1 AS c FROM t AS t WHERE t.c <= 10) SELECT t.c AS c FROM t AS t;
+
+# title: we can't expand aliases corresponding to recursive CTE columns (CTE doesn't name output columns)
+# execute: false
+WITH RECURSIVE t AS (SELECT 1 AS c UNION ALL SELECT c + 1 AS c FROM t WHERE c <= 10) SELECT c FROM t;
+WITH RECURSIVE t AS (SELECT 1 AS c UNION ALL SELECT t.c + 1 AS c FROM t AS t WHERE t.c <= 10) SELECT t.c AS c FROM t AS t;
+
--------------------------------------
-- Wrapped tables / join constructs
--------------------------------------
@@ -620,3 +705,6 @@ SELECT _q_0.a AS a, _q_0.b AS b, _q_1.b AS b, _q_1.c AS c FROM ((SELECT x.a AS a
SELECT b FROM ((SELECT a FROM x) INNER JOIN y ON a = b);
SELECT y.b AS b FROM ((SELECT x.a AS a FROM x AS x) AS _q_0 INNER JOIN y AS y ON _q_0.a = y.b);
+
+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);
diff --git a/tests/fixtures/optimizer/qualify_columns_ddl.sql b/tests/fixtures/optimizer/qualify_columns_ddl.sql
index 9b4bb34..75d84ca 100644
--- a/tests/fixtures/optimizer/qualify_columns_ddl.sql
+++ b/tests/fixtures/optimizer/qualify_columns_ddl.sql
@@ -1,26 +1,26 @@
# title: Create with CTE
WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM cte;
-CREATE TABLE s AS WITH cte AS (SELECT y.b AS b FROM y AS y) SELECT cte.b AS b FROM cte AS cte;
+WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT cte.b AS b FROM cte AS cte;
# title: Create with CTE, query also has CTE
WITH cte1 AS (SELECT b FROM y) CREATE TABLE s AS WITH cte2 AS (SELECT b FROM cte1) SELECT * FROM cte2;
-CREATE TABLE s AS WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte2.b AS b FROM cte2 AS cte2;
+WITH cte1 AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS WITH cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte2.b AS b FROM cte2 AS cte2;
# title: Create without CTE
CREATE TABLE foo AS SELECT a FROM tbl;
CREATE TABLE foo AS SELECT tbl.a AS a FROM tbl AS tbl;
# title: Create with complex CTE with derived table
-WITH cte AS (SELECT a FROM (SELECT a from x)) CREATE TABLE s AS SELECT * FROM cte;
-CREATE TABLE s AS WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) SELECT cte.a AS a FROM cte AS cte;
+WITH cte AS (SELECT a FROM (SELECT a FROM x)) CREATE TABLE s AS SELECT * FROM cte;
+WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) CREATE TABLE s AS SELECT cte.a AS a FROM cte AS cte;
# title: Create wtih multiple CTEs
WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte2;
-CREATE TABLE s AS WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte2.b AS b FROM cte2 AS cte2;
+WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte2.b AS b FROM cte2 AS cte2;
# title: Create with multiple CTEs, selecting only from the first CTE (unnecessary code)
WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte1;
-CREATE TABLE s AS WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte1.b AS b FROM cte1 AS cte1;
+WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte1.b AS b FROM cte1 AS cte1;
# title: Create with multiple derived tables
CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM y));
@@ -28,7 +28,7 @@ CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT y.b A
# title: Create with a CTE and a derived table
WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM cte));
-CREATE TABLE s AS WITH cte AS (SELECT y.b AS b FROM y AS y) SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte AS cte) AS _q_0) AS _q_1;
+WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte AS cte) AS _q_0) AS _q_1;
# title: Insert with CTE
# dialect: spark
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index 104400e..61d0b96 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -158,6 +158,10 @@ ALTER TABLE c.db.t ADD PRIMARY KEY (id) NOT ENFORCED;
CREATE TABLE t1 AS (WITH cte AS (SELECT x FROM t2) SELECT * FROM cte);
CREATE TABLE c.db.t1 AS (WITH cte AS (SELECT x FROM c.db.t2 AS t2) SELECT * FROM cte AS cte);
+# title: delete statement
+DELETE FROM t1 WHERE NOT c IN (SELECT c FROM t2);
+DELETE FROM c.db.t1 WHERE NOT c IN (SELECT c FROM c.db.t2 AS t2);
+
# title: insert statement with cte
# dialect: spark
WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte;
@@ -173,3 +177,10 @@ SELECT a.id, a_2.id FROM c.db1.a AS a JOIN c.db2.a AS a_2 ON a.id = a_2.id;
SELECT cat.db1.a.id, db2.a.id FROM cat.db1.a JOIN db2.a ON cat.db1.a.id = db2.a.id;
SELECT a.id, a_2.id FROM cat.db1.a AS a JOIN c.db2.a AS a_2 ON a.id = a_2.id;
+
+COPY INTO (SELECT * FROM x) TO 'data' WITH (FORMAT 'CSV');
+COPY INTO (SELECT * FROM c.db.x AS x) TO 'data' WITH (FORMAT 'CSV');
+
+# title: tablesample
+SELECT 1 FROM x TABLESAMPLE SYSTEM (10 PERCENT) CROSS JOIN y TABLESAMPLE SYSTEM (10 PERCENT);
+SELECT 1 FROM c.db.x AS x TABLESAMPLE SYSTEM (10 PERCENT) CROSS JOIN c.db.y AS y TABLESAMPLE SYSTEM (10 PERCENT);
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 6af51bf..1f60741 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -85,6 +85,27 @@ NULL;
NULL = NULL;
NULL;
+1 AND 0;
+FALSE;
+
+0 AND 1;
+FALSE;
+
+0 OR 1;
+TRUE;
+
+0 OR NULL;
+NULL;
+
+NULL OR 0;
+NULL;
+
+0 AND NULL;
+FALSE;
+
+NULL AND 0;
+FALSE;
+
-- Can't optimize this because different engines do different things
-- mysql converts to 0 and 1 but tsql does true and false
NULL <=> NULL;
@@ -109,6 +130,13 @@ a AND b;
(x is not null) != (y is null);
(NOT x IS NULL) <> (y IS NULL);
+# dialect: mysql
+A XOR A;
+FALSE;
+
+TRUE AND TRUE OR TRUE AND FALSE;
+TRUE;
+
--------------------------------------
-- Absorption
--------------------------------------
@@ -154,6 +182,12 @@ A OR C;
A AND (B AND C) AND (D AND E);
A AND B AND C AND D AND E;
+A AND (A OR B) AND (A OR B OR C);
+A;
+
+(A OR B) AND (A OR C) AND (A OR B OR C);
+(A OR B) AND (A OR C);
+
--------------------------------------
-- Elimination
--------------------------------------
@@ -190,6 +224,15 @@ NOT A;
E OR (A AND B) OR C OR D OR (A AND NOT B);
A OR C OR D OR E;
+(A AND B) OR (A AND NOT B) OR (A AND NOT B);
+A;
+
+(A AND B) OR (A AND B) OR (A AND NOT B);
+A;
+
+(A AND B) OR (A AND NOT B) OR (A AND B) OR (A AND NOT B);
+A;
+
--------------------------------------
-- Associativity
--------------------------------------
@@ -232,6 +275,13 @@ x - 1;
A AND D AND B AND E AND F AND G AND E AND A;
A AND B AND D AND E AND F AND G;
+A OR D OR B OR E OR F OR G OR E OR A;
+A OR B OR D OR E OR F OR G;
+
+# dialect: mysql
+A XOR D XOR B XOR E XOR F XOR G XOR C;
+A XOR B XOR C XOR D XOR E XOR F XOR G;
+
A AND NOT B AND C AND B;
FALSE;
@@ -502,6 +552,9 @@ CAST('2023-01-01 22:00:00' AS DATETIME);
DATE_ADD(x, 1, 'MONTH');
DATE_ADD(x, 1, 'MONTH');
+DATE_ADD(x, 1);
+DATE_ADD(x, 1, 'DAY');
+
--------------------------------------
-- Comparisons
--------------------------------------
@@ -1018,6 +1071,9 @@ x < CAST('2021-01-02' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
TIMESTAMP_TRUNC(x, YEAR) = CAST(CAST('2021-01-01 01:02:03' AS DATE) AS DATETIME);
x < CAST('2022-01-01 00:00:00' AS DATETIME) AND x >= CAST('2021-01-01 00:00:00' AS DATETIME);
+DATE_TRUNC('day', CAST(x AS DATE)) <= CAST('2021-01-01 01:02:03' AS TIMESTAMP);
+CAST(x AS DATE) < CAST('2021-01-02 01:02:03' AS TIMESTAMP);
+
--------------------------------------
-- EQUALITY
--------------------------------------
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index a357b07..7bbeb27 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -852,10 +852,10 @@ SELECT
SUM("x"."profit") AS "profit"
FROM "x" AS "x"
GROUP BY
-ROLLUP (
- "x"."channel",
- "x"."id"
-)
+ ROLLUP (
+ "x"."channel",
+ "x"."id"
+ )
ORDER BY
"channel",
"id"
@@ -1409,31 +1409,11 @@ WITH "_u_0" AS (
"store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
)
SELECT
- CASE
- WHEN MAX("_u_0"."_col_0") > 3672
- THEN MAX("_u_1"."_col_0")
- ELSE MAX("_u_2"."_col_0")
- END AS "bucket1",
- CASE
- WHEN MAX("_u_3"."_col_0") > 3392
- THEN MAX("_u_4"."_col_0")
- ELSE MAX("_u_5"."_col_0")
- END AS "bucket2",
- CASE
- WHEN MAX("_u_6"."_col_0") > 32784
- THEN MAX("_u_7"."_col_0")
- ELSE MAX("_u_8"."_col_0")
- END AS "bucket3",
- CASE
- WHEN MAX("_u_9"."_col_0") > 26032
- THEN MAX("_u_10"."_col_0")
- ELSE MAX("_u_11"."_col_0")
- END AS "bucket4",
- CASE
- WHEN MAX("_u_12"."_col_0") > 23982
- THEN MAX("_u_13"."_col_0")
- ELSE MAX("_u_14"."_col_0")
- END AS "bucket5"
+ CASE WHEN "_u_0"."_col_0" > 3672 THEN "_u_1"."_col_0" ELSE "_u_2"."_col_0" END AS "bucket1",
+ CASE WHEN "_u_3"."_col_0" > 3392 THEN "_u_4"."_col_0" ELSE "_u_5"."_col_0" END AS "bucket2",
+ CASE WHEN "_u_6"."_col_0" > 32784 THEN "_u_7"."_col_0" ELSE "_u_8"."_col_0" END AS "bucket3",
+ CASE WHEN "_u_9"."_col_0" > 26032 THEN "_u_10"."_col_0" ELSE "_u_11"."_col_0" END AS "bucket4",
+ CASE WHEN "_u_12"."_col_0" > 23982 THEN "_u_13"."_col_0" ELSE "_u_14"."_col_0" END AS "bucket5"
FROM "reason" AS "reason"
CROSS JOIN "_u_0" AS "_u_0"
CROSS JOIN "_u_1" AS "_u_1"
@@ -2314,12 +2294,12 @@ SELECT
SUM("y"."number_sales") AS "_col_5"
FROM "y" AS "y"
GROUP BY
-ROLLUP (
- "y"."channel",
- "y"."i_brand_id",
- "y"."i_class_id",
- "y"."i_category_id"
-)
+ ROLLUP (
+ "y"."channel",
+ "y"."i_brand_id",
+ "y"."i_class_id",
+ "y"."i_category_id"
+ )
ORDER BY
"channel",
"i_brand_id",
@@ -2628,12 +2608,12 @@ JOIN "customer_address" AS "customer_address"
ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
AND "customer_address"."ca_state" IN ('KS', 'IA', 'AL', 'UT', 'VA', 'NC', 'TX')
GROUP BY
-ROLLUP (
- "item"."i_item_id",
- "customer_address"."ca_country",
- "customer_address"."ca_state",
- "customer_address"."ca_county"
-)
+ ROLLUP (
+ "item"."i_item_id",
+ "customer_address"."ca_country",
+ "customer_address"."ca_state",
+ "customer_address"."ca_county"
+ )
ORDER BY
"ca_country",
"ca_state",
@@ -2896,12 +2876,12 @@ JOIN "item" AS "item"
JOIN "warehouse" AS "warehouse"
ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk"
GROUP BY
-ROLLUP (
- "item"."i_product_name",
- "item"."i_brand",
- "item"."i_class",
- "item"."i_category"
-)
+ ROLLUP (
+ "item"."i_product_name",
+ "item"."i_brand",
+ "item"."i_class",
+ "item"."i_category"
+ )
ORDER BY
"qoh",
"i_product_name",
@@ -3367,10 +3347,10 @@ JOIN "store" AS "store"
ON "store"."s_state" IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN')
AND "store"."s_store_sk" = "store_sales"."ss_store_sk"
GROUP BY
-ROLLUP (
- "item"."i_item_id",
- "store"."s_state"
-)
+ ROLLUP (
+ "item"."i_item_id",
+ "store"."s_state"
+ )
ORDER BY
"i_item_id",
"s_state"
@@ -4483,10 +4463,10 @@ JOIN "store" AS "store"
ON "store"."s_state" IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN')
AND "store"."s_store_sk" = "store_sales"."ss_store_sk"
GROUP BY
-ROLLUP (
- "item"."i_category",
- "item"."i_class"
-)
+ ROLLUP (
+ "item"."i_category",
+ "item"."i_class"
+ )
ORDER BY
"lochierarchy" DESC,
CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
@@ -9037,16 +9017,16 @@ WITH "dw1" AS (
JOIN "store" AS "store"
ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
GROUP BY
- ROLLUP (
- "item"."i_category",
- "item"."i_class",
- "item"."i_brand",
- "item"."i_product_name",
- "date_dim"."d_year",
- "date_dim"."d_qoy",
- "date_dim"."d_moy",
- "store"."s_store_id"
- )
+ ROLLUP (
+ "item"."i_category",
+ "item"."i_class",
+ "item"."i_brand",
+ "item"."i_product_name",
+ "date_dim"."d_year",
+ "date_dim"."d_qoy",
+ "date_dim"."d_moy",
+ "store"."s_store_id"
+ )
), "dw2" AS (
SELECT
"dw1"."i_category" AS "i_category",
@@ -9416,10 +9396,10 @@ LEFT JOIN "_u_0" AS "_u_0"
WHERE
NOT "_u_0"."s_state" IS NULL
GROUP BY
-ROLLUP (
- "store"."s_state",
- "store"."s_county"
-)
+ ROLLUP (
+ "store"."s_state",
+ "store"."s_county"
+ )
ORDER BY
"lochierarchy" DESC,
CASE WHEN "lochierarchy" = 0 THEN "s_state" END,
@@ -10453,10 +10433,10 @@ SELECT
SUM("x"."profit") AS "profit"
FROM "x" AS "x"
GROUP BY
-ROLLUP (
- "x"."channel",
- "x"."id"
-)
+ ROLLUP (
+ "x"."channel",
+ "x"."id"
+ )
ORDER BY
"channel",
"id"
@@ -10957,10 +10937,10 @@ SELECT
SUM("x"."profit") AS "profit"
FROM "x" AS "x"
GROUP BY
-ROLLUP (
- "x"."channel",
- "x"."id"
-)
+ ROLLUP (
+ "x"."channel",
+ "x"."id"
+ )
ORDER BY
"channel",
"id"
@@ -11559,10 +11539,10 @@ JOIN "date_dim" AS "d1"
JOIN "item" AS "item"
ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
GROUP BY
-ROLLUP (
- "item"."i_category",
- "item"."i_class"
-)
+ ROLLUP (
+ "item"."i_category",
+ "item"."i_class"
+ )
ORDER BY
"lochierarchy" DESC,
CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index c131643..ed7a689 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -375,7 +375,7 @@ order by
SELECT
"n1"."n_name" AS "supp_nation",
"n2"."n_name" AS "cust_nation",
- EXTRACT(year FROM CAST("lineitem"."l_shipdate" AS DATE)) AS "l_year",
+ EXTRACT(YEAR FROM CAST("lineitem"."l_shipdate" AS DATE)) AS "l_year",
SUM("lineitem"."l_extendedprice" * (
1 - "lineitem"."l_discount"
)) AS "revenue"
@@ -407,7 +407,7 @@ JOIN "nation" AS "n2"
GROUP BY
"n1"."n_name",
"n2"."n_name",
- EXTRACT(year FROM CAST("lineitem"."l_shipdate" AS DATE))
+ EXTRACT(YEAR FROM CAST("lineitem"."l_shipdate" AS DATE))
ORDER BY
"supp_nation",
"cust_nation",
@@ -425,7 +425,7 @@ select
from
(
select
- extract(year from cast(o_orderdate as date)) as o_year,
+ extract(YEAR from cast(o_orderdate as date)) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
@@ -454,7 +454,7 @@ group by
order by
o_year;
SELECT
- EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE)) AS "o_year",
+ EXTRACT(YEAR FROM CAST("orders"."o_orderdate" AS DATE)) AS "o_year",
SUM(
CASE
WHEN "n2"."n_name" = 'BRAZIL'
@@ -486,7 +486,7 @@ JOIN "region" AS "region"
WHERE
"part"."p_type" = 'ECONOMY ANODIZED STEEL'
GROUP BY
- EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE))
+ EXTRACT(YEAR FROM CAST("orders"."o_orderdate" AS DATE))
ORDER BY
"o_year";
@@ -527,7 +527,7 @@ order by
o_year desc;
SELECT
"nation"."n_name" AS "nation",
- EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE)) AS "o_year",
+ EXTRACT(YEAR FROM CAST("orders"."o_orderdate" AS DATE)) AS "o_year",
SUM(
"lineitem"."l_extendedprice" * (
1 - "lineitem"."l_discount"
@@ -549,7 +549,7 @@ WHERE
"part"."p_name" LIKE '%green%'
GROUP BY
"nation"."n_name",
- EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE))
+ EXTRACT(YEAR FROM CAST("orders"."o_orderdate" AS DATE))
ORDER BY
"nation",
"o_year" DESC;
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index 45e462b..a5a35b1 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -1,243 +1,69 @@
---SELECT x.a > (SELECT SUM(y.a) AS b FROM y) FROM x;
---------------------------------------
--- Unnest Subqueries
---------------------------------------
-SELECT *
-FROM x AS x
-WHERE
- x.a = (SELECT SUM(y.a) AS a FROM y)
- AND x.a IN (SELECT y.a AS a FROM y)
- AND x.a IN (SELECT y.b AS b FROM y)
- AND x.a = ANY (SELECT y.a AS a FROM y)
- AND x.a = (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a)
- AND x.a > (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a)
- AND x.a <> ANY (SELECT y.a AS a FROM y WHERE y.a = x.a)
- AND x.a NOT IN (SELECT y.a AS a FROM y WHERE y.a = x.a)
- AND x.a IN (SELECT y.a AS a FROM y WHERE y.b = x.a)
- AND x.a < (SELECT SUM(y.a) AS a FROM y WHERE y.a = x.a and y.a = x.b and y.b <> x.d)
- AND EXISTS (SELECT y.a AS a, y.b AS b FROM y WHERE x.a = y.a)
- AND x.a IN (SELECT y.a AS a FROM y LIMIT 10)
- AND x.a IN (SELECT y.a AS a FROM y OFFSET 10)
- AND x.a IN (SELECT y.a AS a, y.b AS b FROM y)
- AND x.a > ANY (SELECT y.a FROM y)
- AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10)
- AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10)
- AND x.a > ALL (SELECT y.c FROM y WHERE y.a = x.a)
- AND x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a)
- AND x.a = SUM(SELECT 1) -- invalid statement left alone
- AND x.a IN (SELECT max(y.b) AS b FROM y GROUP BY y.a)
-;
-SELECT
- *
-FROM x AS x
-CROSS JOIN (
- SELECT
- SUM(y.a) AS a
- FROM y
-) AS _u_0
-LEFT JOIN (
- SELECT
- y.a AS a
- FROM y
- GROUP BY
- y.a
-) AS _u_1
- ON x.a = _u_1.a
-LEFT JOIN (
- SELECT
- y.b AS b
- FROM y
- GROUP BY
- y.b
-) AS _u_2
- ON x.a = _u_2.b
-LEFT JOIN (
- SELECT
- y.a AS a
- FROM y
- GROUP BY
- y.a
-) AS _u_3
- ON x.a = _u_3.a
-LEFT JOIN (
- SELECT
- SUM(y.b) AS b,
- y.a AS _u_5
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_4
- ON x.a = _u_4._u_5
-LEFT JOIN (
- SELECT
- SUM(y.b) AS b,
- y.a AS _u_7
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_6
- ON x.a = _u_6._u_7
-LEFT JOIN (
- SELECT
- y.a AS a
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_8
- ON _u_8.a = x.a
-LEFT JOIN (
- SELECT
- y.a AS a
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_9
- ON _u_9.a = x.a
-LEFT JOIN (
- SELECT
- ARRAY_AGG(y.a) AS a,
- y.b AS _u_11
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.b
-) AS _u_10
- ON _u_10._u_11 = x.a
-LEFT JOIN (
- SELECT
- SUM(y.a) AS a,
- y.a AS _u_13,
- ARRAY_AGG(y.b) AS _u_14
- FROM y
- WHERE
- TRUE AND TRUE AND TRUE
- GROUP BY
- y.a
-) AS _u_12
- ON _u_12._u_13 = x.a AND _u_12._u_13 = x.b
-LEFT JOIN (
- SELECT
- y.a AS a
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_15
- ON x.a = _u_15.a
-LEFT JOIN (
- SELECT
- ARRAY_AGG(c),
- y.a AS _u_20
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_19
- ON _u_19._u_20 = x.a
-LEFT JOIN (
- SELECT
- COUNT(*) AS d,
- y.a AS _u_22
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_21
- ON _u_21._u_22 = x.a
-LEFT JOIN (
- SELECT
- _q.b
- FROM (
- SELECT
- MAX(y.b) AS b
- FROM y
- GROUP BY
- y.a
- ) AS _q
- GROUP BY
- _q.b
-) AS _u_24
- ON x.a = _u_24.b
-WHERE
- x.a = _u_0.a
- AND NOT _u_1.a IS NULL
- AND NOT _u_2.b IS NULL
- AND NOT _u_3.a IS NULL
- AND x.a = _u_4.b
- AND x.a > _u_6.b
- AND x.a = _u_8.a
- AND NOT x.a = _u_9.a
- AND ARRAY_ANY(_u_10.a, _x -> _x = x.a)
- AND (
- x.a < _u_12.a AND ARRAY_ANY(_u_12._u_14, _x -> _x <> x.d)
- )
- AND NOT _u_15.a IS NULL
- AND x.a IN (
- SELECT
- y.a AS a
- FROM y
- LIMIT 10
- )
- AND x.a IN (
- SELECT
- y.a AS a
- FROM y
- OFFSET 10
- )
- AND x.a IN (
- SELECT
- y.a AS a,
- y.b AS b
- FROM y
- )
- AND x.a > ANY (
- SELECT
- y.a
- FROM y
- )
- AND x.a = (
- SELECT
- SUM(y.c) AS c
- FROM y
- WHERE
- y.a = x.a
- LIMIT 10
- )
- AND x.a = (
- SELECT
- SUM(y.c) AS c
- FROM y
- WHERE
- y.a = x.a
- OFFSET 10
- )
- AND ARRAY_ALL(_u_19."", _x -> _x = x.a)
- AND x.a > COALESCE(_u_21.d, 0)
- AND x.a = SUM(SELECT
- 1) /* invalid statement left alone */
- AND NOT _u_24.b IS NULL
-;
-SELECT
- CAST((
- SELECT
- x.a AS a
- FROM x
- ) AS TEXT) AS a;
-SELECT
- CAST((
- SELECT
- x.a AS a
- FROM x
- ) AS TEXT) AS a;
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.a) AS a FROM y);
+SELECT * FROM x CROSS JOIN (SELECT SUM(y.a) AS a FROM y) AS _u_0 WHERE x.a = _u_0.a;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.b AS b FROM y);
+SELECT * FROM x LEFT JOIN (SELECT y.b AS b FROM y GROUP BY y.b) AS _u_0 ON x.a = _u_0.b WHERE NOT _u_0.b IS NULL;
+
+SELECT * FROM x WHERE x.a = ANY (SELECT y.a AS a FROM y);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL;
+
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a);
+SELECT * FROM x LEFT JOIN (SELECT SUM(y.b) AS b, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0._u_1 WHERE x.a = _u_0.b;
+
+SELECT * FROM x WHERE x.a > (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a);
+SELECT * FROM x LEFT JOIN (SELECT SUM(y.b) AS b, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0._u_1 WHERE x.a > _u_0.b;
+
+SELECT * FROM x WHERE x.a <> ANY (SELECT y.a AS a FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0.a = x.a WHERE x.a <> _u_0.a;
+
+SELECT * FROM x WHERE x.a NOT IN (SELECT y.a AS a FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0.a = x.a WHERE NOT x.a = _u_0.a;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y WHERE y.b = x.a);
+SELECT * FROM x LEFT JOIN (SELECT ARRAY_AGG(y.a) AS a, y.b AS _u_1 FROM y WHERE TRUE GROUP BY y.b) AS _u_0 ON _u_0._u_1 = x.a WHERE ARRAY_ANY(_u_0.a, _x -> _x = x.a);
+
+SELECT * FROM x WHERE x.a < (SELECT SUM(y.a) AS a FROM y WHERE y.a = x.a and y.a = x.b and y.b <> x.d);
+SELECT * FROM x LEFT JOIN (SELECT SUM(y.a) AS a, y.a AS _u_1, ARRAY_AGG(y.b) AS _u_2 FROM y WHERE TRUE AND TRUE AND TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a AND _u_0._u_1 = x.b WHERE (x.a < _u_0.a AND ARRAY_ANY(_u_0._u_2, _x -> _x <> x.d));
+
+SELECT * FROM x WHERE EXISTS (SELECT y.a AS a, y.b AS b FROM y WHERE x.a = y.a);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y LIMIT 10);
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y LIMIT 10);
+
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a FROM y OFFSET 10);
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a FROM y OFFSET 10);
+
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a, y.b AS b FROM y);
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a, y.b AS b FROM y);
+
+SELECT * FROM x.a WHERE x.a > ANY (SELECT y.a FROM y);
+SELECT * FROM x.a WHERE x.a > ANY (SELECT y.a FROM y);
+
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10);
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10);
+
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10);
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10);
+
+SELECT * FROM x WHERE x.a > ALL (SELECT y.c AS c FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT ARRAY_AGG(y.c) AS c, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a WHERE ARRAY_ALL(_u_0.c, _x -> x.a > _x);
+
+SELECT * FROM x WHERE x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT COUNT(*) AS d, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a WHERE x.a > COALESCE(_u_0.d, 0);
+
+# title: invalid statement left alone
+SELECT * FROM x WHERE x.a = SUM(SELECT 1);
+SELECT * FROM x WHERE x.a = SUM(SELECT 1);
+
+SELECT * FROM x WHERE x.a IN (SELECT max(y.b) AS b FROM y GROUP BY y.a);
+SELECT * FROM x LEFT JOIN (SELECT _q.b AS b FROM (SELECT MAX(y.b) AS b FROM y GROUP BY y.a) AS _q GROUP BY _q.b) AS _u_0 ON x.a = _u_0.b WHERE NOT _u_0.b IS NULL;
+
+SELECT x.a > (SELECT SUM(y.a) AS b FROM y) FROM x;
+SELECT x.a > _u_0.b FROM x CROSS JOIN (SELECT SUM(y.a) AS b FROM y) AS _u_0;
+
+SELECT (SELECT MAX(t2.c1) AS c1 FROM t2 WHERE t2.c2 = t1.c2 AND t2.c3 <= TRUNC(t1.c3)) AS c FROM t1;
+SELECT _u_0.c1 AS c FROM t1 LEFT JOIN (SELECT MAX(t2.c1) AS c1, t2.c2 AS _u_1, MAX(t2.c3) AS _u_2 FROM t2 WHERE TRUE AND TRUE GROUP BY t2.c2) AS _u_0 ON _u_0._u_1 = t1.c2 WHERE _u_0._u_2 <= TRUNC(t1.c3);
diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql
index 62ba01c..3e5619a 100644
--- a/tests/fixtures/pretty.sql
+++ b/tests/fixtures/pretty.sql
@@ -99,14 +99,14 @@ WITH cte1 AS (
FOO(CASE WHEN a AND b THEN c AND d ELSE 3 END)
GROUP BY
x,
- GROUPING SETS (
- a,
- (b, c)
- ),
- CUBE (
- y,
- z
- )
+ GROUPING SETS (
+ a,
+ (b, c)
+ ),
+ CUBE (
+ y,
+ z
+ )
) AS x
)
SELECT
@@ -395,3 +395,26 @@ JOIN b
JOIN d
USING (f)
USING (g);
+
+('aaaaaaaaaaa', 'bbbbbbbbbbbbbbbb', 'ccccccccccccc', 'ddddddddddd', 'eeeeeeeeeeeeeeeeeeeee');
+(
+ 'aaaaaaaaaaa',
+ 'bbbbbbbbbbbbbbbb',
+ 'ccccccccccccc',
+ 'ddddddddddd',
+ 'eeeeeeeeeeeeeeeeeeeee'
+);
+
+/* COMMENT */
+INSERT FIRST WHEN salary > 4000 THEN INTO emp2
+ WHEN salary > 5000 THEN INTO emp3
+ WHEN salary > 6000 THEN INTO emp4
+SELECT salary FROM employees;
+/* COMMENT */
+INSERT FIRST
+ WHEN salary > 4000 THEN INTO emp2
+ WHEN salary > 5000 THEN INTO emp3
+ WHEN salary > 6000 THEN INTO emp4
+SELECT
+ salary
+FROM employees;
generated by cgit v1.2.3 (git 2.39.1) at 2024-09-23 09:55:51 +0000