diff options
Diffstat (limited to '')
| -rw-r--r-- | tests/fixtures/optimizer/annotate_functions.sql | 309 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/annotate_types.sql | 33 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/canonicalize.sql | 4 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/merge_subqueries.sql | 18 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/optimizer.sql | 46 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_columns.sql | 72 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_tables.sql | 10 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/simplify.sql | 33 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 180 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/tpc-h/tpc-h.sql | 14 |
10 files changed, 602 insertions, 117 deletions
diff --git a/tests/fixtures/optimizer/annotate_functions.sql b/tests/fixtures/optimizer/annotate_functions.sql new file mode 100644 index 0000000..1f59a5a --- /dev/null +++ b/tests/fixtures/optimizer/annotate_functions.sql @@ -0,0 +1,309 @@ +-------------------------------------- +-- Dialect +-------------------------------------- +ABS(1); +INT; + +ABS(1.5); +DOUBLE; + +GREATEST(1, 2, 3); +INT; + +GREATEST(1, 2.5, 3); +DOUBLE; + +LEAST(1, 2, 3); +INT; + +LEAST(1, 2.5, 3); +DOUBLE; + +-------------------------------------- +-- Spark2 / Spark3 / Databricks +-------------------------------------- + +# dialect: spark2, spark, databricks +SUBSTRING(tbl.str_col, 0, 0); +STRING; + +# dialect: spark2, spark, databricks +SUBSTRING(tbl.bin_col, 0, 0); +BINARY; + +# dialect: spark2, spark, databricks +CONCAT(tbl.bin_col, tbl.bin_col); +BINARY; + +# dialect: spark2, spark, databricks +CONCAT(tbl.bin_col, tbl.str_col); +STRING; + +# dialect: spark2, spark, databricks +CONCAT(tbl.str_col, tbl.bin_col); +STRING; + +# dialect: spark2, spark, databricks +CONCAT(tbl.str_col, tbl.str_col); +STRING; + +# dialect: spark2, spark, databricks +CONCAT(tbl.str_col, unknown); +STRING; + +# dialect: spark2, spark, databricks +CONCAT(tbl.bin_col, unknown); +UNKNOWN; + +# dialect: spark2, spark, databricks +CONCAT(unknown, unknown); +UNKNOWN; + +# dialect: spark2, spark, databricks +LPAD(tbl.bin_col, 1, tbl.bin_col); +BINARY; + +# dialect: spark2, spark, databricks +RPAD(tbl.bin_col, 1, tbl.bin_col); +BINARY; + +# dialect: spark2, spark, databricks +LPAD(tbl.bin_col, 1, tbl.str_col); +STRING; + +# dialect: spark2, spark, databricks +RPAD(tbl.bin_col, 1, tbl.str_col); +STRING; + +# dialect: spark2, spark, databricks +LPAD(tbl.str_col, 1, tbl.bin_col); +STRING; + +# dialect: spark2, spark, databricks +RPAD(tbl.str_col, 1, tbl.bin_col); +STRING; + +# dialect: spark2, spark, databricks +LPAD(tbl.str_col, 1, tbl.str_col); +STRING; + +# dialect: spark2, spark, databricks +RPAD(tbl.str_col, 1, tbl.str_col); +STRING; + + +-------------------------------------- +-- BigQuery +-------------------------------------- + +# dialect: bigquery +SIGN(1); +INT; + +# dialect: bigquery +SIGN(1.5); +DOUBLE; + +# dialect: bigquery +CEIL(1); +DOUBLE; + +# dialect: bigquery +CEIL(5.5); +DOUBLE; + +# dialect: bigquery +CEIL(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +FLOOR(1); +DOUBLE; + +# dialect: bigquery +FLOOR(5.5); +DOUBLE; + +# dialect: bigquery +FLOOR(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +SQRT(1); +DOUBLE; + +# dialect: bigquery +SQRT(5.5); +DOUBLE; + +# dialect: bigquery +SQRT(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +LN(1); +DOUBLE; + +# dialect: bigquery +LN(5.5); +DOUBLE; + +# dialect: bigquery +LN(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +LOG(1); +DOUBLE; + +# dialect: bigquery +LOG(5.5); +DOUBLE; + +# dialect: bigquery +LOG(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +ROUND(1); +DOUBLE; + +# dialect: bigquery +ROUND(5.5); +DOUBLE; + +# dialect: bigquery +ROUND(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +EXP(1); +DOUBLE; + +# dialect: bigquery +EXP(5.5); +DOUBLE; + +# dialect: bigquery +EXP(tbl.bignum_col); +BIGDECIMAL; + +# dialect: bigquery +CONCAT(tbl.str_col, tbl.str_col); +STRING; + +# dialect: bigquery +CONCAT(tbl.bin_col, tbl.bin_col); +BINARY; + +# dialect: bigquery +LEFT(tbl.str_col, 1); +STRING; + +# dialect: bigquery +LEFT(tbl.bin_col, 1); +BINARY; + +# dialect: bigquery +RIGHT(tbl.str_col, 1); +STRING; + +# dialect: bigquery +RIGHT(tbl.bin_col, 1); +BINARY; + +# dialect: bigquery +LOWER(tbl.str_col); +STRING; + +# dialect: bigquery +LOWER(tbl.bin_col); +BINARY; + +# dialect: bigquery +UPPER(tbl.str_col); +STRING; + +# dialect: bigquery +UPPER(tbl.bin_col); +BINARY; + +# dialect: bigquery +LPAD(tbl.str_col, 1, tbl.str_col); +STRING; + +# dialect: bigquery +LPAD(tbl.bin_col, 1, tbl.bin_col); +BINARY; + +# dialect: bigquery +RPAD(tbl.str_col, 1, tbl.str_col); +STRING; + +# dialect: bigquery +RPAD(tbl.bin_col, 1, tbl.bin_col); +BINARY; + +# dialect: bigquery +LTRIM(tbl.str_col); +STRING; + +# dialect: bigquery +LTRIM(tbl.bin_col, tbl.bin_col); +BINARY; + +# dialect: bigquery +RTRIM(tbl.str_col); +STRING; + +# dialect: bigquery +RTRIM(tbl.bin_col, tbl.bin_col); +BINARY; + +# dialect: bigquery +TRIM(tbl.str_col); +STRING; + +# dialect: bigquery +TRIM(tbl.bin_col, tbl.bin_col); +BINARY; + +# dialect: bigquery +REGEXP_EXTRACT(tbl.str_col, pattern); +STRING; + +# dialect: bigquery +REGEXP_EXTRACT(tbl.bin_col, pattern); +BINARY; + +# dialect: bigquery +REGEXP_REPLACE(tbl.str_col, pattern, replacement); +STRING; + +# dialect: bigquery +REGEXP_REPLACE(tbl.bin_col, pattern, replacement); +BINARY; + +# dialect: bigquery +REPEAT(tbl.str_col, 1); +STRING; + +# dialect: bigquery +REPEAT(tbl.bin_col, 1); +BINARY; + +# dialect: bigquery +SUBSTRING(tbl.str_col, 1); +STRING; + +# dialect: bigquery +SUBSTRING(tbl.bin_col, 1); +BINARY; + +# dialect: bigquery +SPLIT(tbl.str_col, delim); +ARRAY<STRING>; + +# dialect: bigquery +SPLIT(tbl.bin_col, delim); +ARRAY<BINARY>; 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/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index ce5a435..e39e7d1 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -446,3 +446,21 @@ SELECT 1 AS a; WITH q AS (SELECT x.a AS a FROM x AS x ORDER BY x.a) SELECT q.a AS a FROM q AS q UNION ALL SELECT 1 AS a; +# title: Consecutive inner - outer conflicting names +WITH tbl AS (select 1 as id) +SELECT + id +FROM ( + SELECT OTBL.id + FROM ( + SELECT OTBL.id + FROM ( + SELECT OTBL.id + FROM tbl AS OTBL + LEFT OUTER JOIN tbl AS ITBL ON OTBL.id = ITBL.id + ) AS OTBL + LEFT OUTER JOIN tbl AS ITBL ON OTBL.id = ITBL.id + ) AS OTBL + LEFT OUTER JOIN tbl AS ITBL ON OTBL.id = ITBL.id +) AS ITBL; +WITH tbl AS (SELECT 1 AS id) SELECT OTBL.id AS id FROM tbl AS OTBL LEFT OUTER JOIN tbl AS ITBL_2 ON OTBL.id = ITBL_2.id LEFT OUTER JOIN tbl AS ITBL_3 ON OTBL.id = ITBL_3.id LEFT OUTER JOIN tbl AS ITBL ON OTBL.id = ITBL.id; 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 ea96fe5..7c901ce 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -190,6 +190,17 @@ SELECT x._col_0 AS _col_0, x._col_1 AS _col_1 FROM (VALUES (1, 2)) AS x(_col_0, SELECT SOME_UDF(data).* FROM t; SELECT SOME_UDF(t.data).* FROM t AS t; +# execute: false +# allow_partial_qualification: true +# validate_qualify_columns: false +SELECT a + 1 AS i, missing_column FROM x; +SELECT x.a + 1 AS i, missing_column AS missing_column FROM x AS x; + +# execute: false +# dialect: clickhouse +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; + -------------------------------------- -- Derived tables -------------------------------------- @@ -343,6 +354,11 @@ WITH tbl1 AS (SELECT STRUCT(1 AS col1, Struct(5 AS col1)) AS col) SELECT tbl1.co 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 -------------------------------------- @@ -385,14 +401,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; @@ -416,6 +432,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 -------------------------------------- @@ -480,6 +520,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 -------------------------------------- @@ -591,6 +643,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; @@ -619,6 +672,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 -------------------------------------- @@ -653,3 +716,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_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index 30bf834..49e07fa 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -177,3 +177,13 @@ 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); + +WITH cte_tbl AS (SELECT 1 AS col2) UPDATE y SET col1 = (SELECT * FROM x) WHERE EXISTS(SELECT 1 FROM cte_tbl); +WITH cte_tbl AS (SELECT 1 AS col2) UPDATE c.db.y SET col1 = (SELECT * FROM c.db.x AS x) WHERE EXISTS(SELECT 1 FROM cte_tbl AS cte_tbl); diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index 87b42d1..1842e55 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; @@ -116,6 +137,12 @@ FALSE; TRUE AND TRUE OR TRUE AND FALSE; TRUE; +COALESCE(x, y) <> ALL (SELECT z FROM w); +COALESCE(x, y) <> ALL (SELECT z FROM w); + +SELECT NOT (2 <> ALL (SELECT 2 UNION ALL SELECT 3)); +SELECT 2 = ANY(SELECT 2 UNION ALL SELECT 3); + -------------------------------------- -- Absorption -------------------------------------- @@ -531,6 +558,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 -------------------------------------- @@ -1047,6 +1077,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 5b004fa..290d276 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" @@ -991,9 +991,9 @@ FROM store_sales, date_dim, store, (SELECT ca_zip - FROM (SELECT Substr(ca_zip, 1, 5) ca_zip + FROM (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip FROM customer_address - WHERE Substr(ca_zip, 1, 5) IN ( '67436', '26121', '38443', + WHERE SUBSTRING(ca_zip, 1, 5) IN ( '67436', '26121', '38443', '63157', '68856', '19485', '86425', '26741', @@ -1195,7 +1195,7 @@ FROM store_sales, '92564' ) INTERSECT SELECT ca_zip - FROM (SELECT Substr(ca_zip, 1, 5) ca_zip, + FROM (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip, Count(*) cnt FROM customer_address, customer @@ -1207,13 +1207,13 @@ WHERE ss_store_sk = s_store_sk AND ss_sold_date_sk = d_date_sk AND d_qoy = 2 AND d_year = 2000 - AND ( Substr(s_zip, 1, 2) = Substr(V1.ca_zip, 1, 2) ) + AND ( SUBSTRING(s_zip, 1, 2) = SUBSTRING(V1.ca_zip, 1, 2) ) GROUP BY s_store_name ORDER BY s_store_name LIMIT 100; WITH "a1" AS ( SELECT - SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip" + SUBSTRING("customer_address"."ca_zip", 1, 5) AS "ca_zip" FROM "customer_address" AS "customer_address" JOIN "customer" AS "customer" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" @@ -1224,10 +1224,10 @@ WITH "a1" AS ( COUNT(*) > 10 ), "a2" AS ( SELECT - SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip" + SUBSTRING("customer_address"."ca_zip", 1, 5) AS "ca_zip" FROM "customer_address" AS "customer_address" WHERE - SUBSTR("customer_address"."ca_zip", 1, 5) IN ('67436', '26121', '38443', '63157', '68856', '19485', '86425', '26741', '70991', '60899', '63573', '47556', '56193', '93314', '87827', '62017', '85067', '95390', '48091', '10261', '81845', '41790', '42853', '24675', '12840', '60065', '84430', '57451', '24021', '91735', '75335', '71935', '34482', '56943', '70695', '52147', '56251', '28411', '86653', '23005', '22478', '29031', '34398', '15365', '42460', '33337', '59433', '73943', '72477', '74081', '74430', '64605', '39006', '11226', '49057', '97308', '42663', '18187', '19768', '43454', '32147', '76637', '51975', '11181', '45630', '33129', '45995', '64386', '55522', '26697', '20963', '35154', '64587', '49752', '66386', '30586', '59286', '13177', '66646', '84195', '74316', '36853', '32927', '12469', '11904', '36269', '17724', '55346', '12595', '53988', '65439', '28015', '63268', '73590', '29216', '82575', '69267', '13805', '91678', '79460', '94152', '14961', '15419', '48277', '62588', '55493', '28360', '14152', '55225', '18007', '53705', '56573', '80245', '71769', '57348', '36845', '13039', '17270', '22363', '83474', '25294', '43269', '77666', '15488', '99146', '64441', '43338', '38736', '62754', '48556', '86057', '23090', '38114', '66061', '18910', '84385', '23600', '19975', '27883', '65719', '19933', '32085', '49731', '40473', '27190', '46192', '23949', '44738', '12436', '64794', '68741', '15333', '24282', '49085', '31844', '71156', '48441', '17100', '98207', '44982', '20277', '71496', '96299', '37583', '22206', '89174', '30589', '61924', '53079', '10976', '13104', '42794', '54772', '15809', '56434', '39975', '13874', '30753', '77598', '78229', '59478', '12345', '55547', '57422', '42600', '79444', '29074', '29752', '21676', '32096', '43044', '39383', '37296', '36295', '63077', '16572', '31275', '18701', '40197', '48242', '27219', '49865', '84175', '30446', '25165', '13807', '72142', '70499', '70464', '71429', '18111', '70857', '29545', '36425', '52706', '36194', '42963', '75068', '47921', '74763', '90990', '89456', '62073', '88397', '73963', '75885', '62657', '12530', '81146', '57434', '25099', '41429', '98441', '48713', '52552', '31667', '14072', '13903', '44709', '85429', '58017', '38295', '44875', '73541', '30091', '12707', '23762', '62258', '33247', '78722', '77431', '14510', '35656', '72428', '92082', '35267', '43759', '24354', '90952', '11512', '21242', '22579', '56114', '32339', '52282', '41791', '24484', '95020', '28408', '99710', '11899', '43344', '72915', '27644', '62708', '74479', '17177', '32619', '12351', '91339', '31169', '57081', '53522', '16712', '34419', '71779', '44187', '46206', '96099', '61910', '53664', '12295', '31837', '33096', '10813', '63048', '31732', '79118', '73084', '72783', '84952', '46965', '77956', '39815', '32311', '75329', '48156', '30826', '49661', '13736', '92076', '74865', '88149', '92397', '52777', '68453', '32012', '21222', '52721', '24626', '18210', '42177', '91791', '75251', '82075', '44372', '45542', '20609', '60115', '17362', '22750', '90434', '31852', '54071', '33762', '14705', '40718', '56433', '30996', '40657', '49056', '23585', '66455', '41021', '74736', '72151', '37007', '21729', '60177', '84558', '59027', '93855', '60022', '86443', '19541', '86886', '30532', '39062', '48532', '34713', '52077', '22564', '64638', '15273', '31677', '36138', '62367', '60261', '80213', '42818', '25113', '72378', '69802', '69096', '55443', '28820', '13848', '78258', '37490', '30556', '77380', '28447', '44550', '26791', '70609', '82182', '33306', '43224', '22322', '86959', '68519', '14308', '46501', '81131', '34056', '61991', '19896', '87804', '65774', '92564') + SUBSTRING("customer_address"."ca_zip", 1, 5) IN ('67436', '26121', '38443', '63157', '68856', '19485', '86425', '26741', '70991', '60899', '63573', '47556', '56193', '93314', '87827', '62017', '85067', '95390', '48091', '10261', '81845', '41790', '42853', '24675', '12840', '60065', '84430', '57451', '24021', '91735', '75335', '71935', '34482', '56943', '70695', '52147', '56251', '28411', '86653', '23005', '22478', '29031', '34398', '15365', '42460', '33337', '59433', '73943', '72477', '74081', '74430', '64605', '39006', '11226', '49057', '97308', '42663', '18187', '19768', '43454', '32147', '76637', '51975', '11181', '45630', '33129', '45995', '64386', '55522', '26697', '20963', '35154', '64587', '49752', '66386', '30586', '59286', '13177', '66646', '84195', '74316', '36853', '32927', '12469', '11904', '36269', '17724', '55346', '12595', '53988', '65439', '28015', '63268', '73590', '29216', '82575', '69267', '13805', '91678', '79460', '94152', '14961', '15419', '48277', '62588', '55493', '28360', '14152', '55225', '18007', '53705', '56573', '80245', '71769', '57348', '36845', '13039', '17270', '22363', '83474', '25294', '43269', '77666', '15488', '99146', '64441', '43338', '38736', '62754', '48556', '86057', '23090', '38114', '66061', '18910', '84385', '23600', '19975', '27883', '65719', '19933', '32085', '49731', '40473', '27190', '46192', '23949', '44738', '12436', '64794', '68741', '15333', '24282', '49085', '31844', '71156', '48441', '17100', '98207', '44982', '20277', '71496', '96299', '37583', '22206', '89174', '30589', '61924', '53079', '10976', '13104', '42794', '54772', '15809', '56434', '39975', '13874', '30753', '77598', '78229', '59478', '12345', '55547', '57422', '42600', '79444', '29074', '29752', '21676', '32096', '43044', '39383', '37296', '36295', '63077', '16572', '31275', '18701', '40197', '48242', '27219', '49865', '84175', '30446', '25165', '13807', '72142', '70499', '70464', '71429', '18111', '70857', '29545', '36425', '52706', '36194', '42963', '75068', '47921', '74763', '90990', '89456', '62073', '88397', '73963', '75885', '62657', '12530', '81146', '57434', '25099', '41429', '98441', '48713', '52552', '31667', '14072', '13903', '44709', '85429', '58017', '38295', '44875', '73541', '30091', '12707', '23762', '62258', '33247', '78722', '77431', '14510', '35656', '72428', '92082', '35267', '43759', '24354', '90952', '11512', '21242', '22579', '56114', '32339', '52282', '41791', '24484', '95020', '28408', '99710', '11899', '43344', '72915', '27644', '62708', '74479', '17177', '32619', '12351', '91339', '31169', '57081', '53522', '16712', '34419', '71779', '44187', '46206', '96099', '61910', '53664', '12295', '31837', '33096', '10813', '63048', '31732', '79118', '73084', '72783', '84952', '46965', '77956', '39815', '32311', '75329', '48156', '30826', '49661', '13736', '92076', '74865', '88149', '92397', '52777', '68453', '32012', '21222', '52721', '24626', '18210', '42177', '91791', '75251', '82075', '44372', '45542', '20609', '60115', '17362', '22750', '90434', '31852', '54071', '33762', '14705', '40718', '56433', '30996', '40657', '49056', '23585', '66455', '41021', '74736', '72151', '37007', '21729', '60177', '84558', '59027', '93855', '60022', '86443', '19541', '86886', '30532', '39062', '48532', '34713', '52077', '22564', '64638', '15273', '31677', '36138', '62367', '60261', '80213', '42818', '25113', '72378', '69802', '69096', '55443', '28820', '13848', '78258', '37490', '30556', '77380', '28447', '44550', '26791', '70609', '82182', '33306', '43224', '22322', '86959', '68519', '14308', '46501', '81131', '34056', '61991', '19896', '87804', '65774', '92564') INTERSECT SELECT "a1"."ca_zip" AS "ca_zip" @@ -1244,7 +1244,7 @@ JOIN "date_dim" AS "date_dim" JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "a2" AS "a2" - ON SUBSTR("a2"."ca_zip", 1, 2) = SUBSTR("store"."s_zip", 1, 2) + ON SUBSTRING("a2"."ca_zip", 1, 2) = SUBSTRING("store"."s_zip", 1, 2) GROUP BY "store"."s_store_name" ORDER BY @@ -2294,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", @@ -2319,7 +2319,7 @@ FROM catalog_sales, date_dim WHERE cs_bill_customer_sk = c_customer_sk AND c_current_addr_sk = ca_address_sk - AND ( Substr(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405', + AND ( SUBSTRING(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792' ) OR ca_state IN ( 'CA', 'WA', 'GA' ) @@ -2344,7 +2344,7 @@ JOIN "customer_address" AS "customer_address" ON ( "catalog_sales"."cs_sales_price" > 500 OR "customer_address"."ca_state" IN ('CA', 'WA', 'GA') - OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') + OR SUBSTRING("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') ) AND "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" GROUP BY @@ -2608,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", @@ -2643,7 +2643,7 @@ WHERE d_date_sk = ss_sold_date_sk AND d_year = 1998 AND ss_customer_sk = c_customer_sk AND c_current_addr_sk = ca_address_sk - AND Substr(ca_zip, 1, 5) <> Substr(s_zip, 1, 5) + AND SUBSTRING(ca_zip, 1, 5) <> SUBSTRING(s_zip, 1, 5) AND ss_store_sk = s_store_sk GROUP BY i_brand, i_brand_id, @@ -2672,7 +2672,7 @@ JOIN "customer_address" AS "customer_address" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk" - AND SUBSTR("customer_address"."ca_zip", 1, 5) <> SUBSTR("store"."s_zip", 1, 5) + AND SUBSTRING("customer_address"."ca_zip", 1, 5) <> SUBSTRING("store"."s_zip", 1, 5) WHERE "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998 GROUP BY @@ -2876,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", @@ -2895,7 +2895,7 @@ LIMIT 100; -------------------------------------- # execute: true WITH frequent_ss_items - AS (SELECT Substr(i_item_desc, 1, 30) itemdesc, + AS (SELECT SUBSTRING(i_item_desc, 1, 30) itemdesc, i_item_sk item_sk, d_date solddate, Count(*) cnt @@ -2905,7 +2905,7 @@ WITH frequent_ss_items WHERE ss_sold_date_sk = d_date_sk AND ss_item_sk = i_item_sk AND d_year IN ( 1998, 1998 + 1, 1998 + 2, 1998 + 3 ) - GROUP BY Substr(i_item_desc, 1, 30), + GROUP BY SUBSTRING(i_item_desc, 1, 30), i_item_sk, d_date HAVING Count(*) > 4), @@ -2962,7 +2962,7 @@ WITH "frequent_ss_items" AS ( JOIN "item" AS "item" ON "item"."i_item_sk" = "store_sales"."ss_item_sk" GROUP BY - SUBSTR("item"."i_item_desc", 1, 30), + SUBSTRING("item"."i_item_desc", 1, 30), "item"."i_item_sk", "date_dim"."d_date" HAVING @@ -3347,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" @@ -4463,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, @@ -5296,7 +5296,7 @@ FROM web_sales, WHERE ws_bill_customer_sk = c_customer_sk AND c_current_addr_sk = ca_address_sk AND ws_item_sk = i_item_sk - AND ( Substr(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405', + AND ( SUBSTRING(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792' ) OR i_item_id IN (SELECT i_item_id @@ -5340,7 +5340,7 @@ JOIN "customer_address" AS "customer_address" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" WHERE NOT "_u_0"."i_item_id" IS NULL - OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') + OR SUBSTRING("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') GROUP BY "customer_address"."ca_zip", "customer_address"."ca_state" @@ -7585,7 +7585,7 @@ LIMIT 100; -- TPC-DS 62 -------------------------------------- # execute: true -SELECT Substr(w_warehouse_name, 1, 20) AS "_col_0", +SELECT SUBSTRING(w_warehouse_name, 1, 20) AS "_col_0", sm_type, web_name, Sum(CASE @@ -7622,15 +7622,15 @@ WHERE d_month_seq BETWEEN 1222 AND 1222 + 11 AND ws_warehouse_sk = w_warehouse_sk AND ws_ship_mode_sk = sm_ship_mode_sk AND ws_web_site_sk = web_site_sk -GROUP BY Substr(w_warehouse_name, 1, 20), +GROUP BY SUBSTRING(w_warehouse_name, 1, 20), sm_type, web_name -ORDER BY Substr(w_warehouse_name, 1, 20), +ORDER BY SUBSTRING(w_warehouse_name, 1, 20), sm_type, web_name LIMIT 100; SELECT - SUBSTR("warehouse"."w_warehouse_name", 1, 20) AS "_col_0", + SUBSTRING("warehouse"."w_warehouse_name", 1, 20) AS "_col_0", "ship_mode"."sm_type" AS "sm_type", "web_site"."web_name" AS "web_name", SUM( @@ -7683,7 +7683,7 @@ JOIN "warehouse" AS "warehouse" JOIN "web_site" AS "web_site" ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk" GROUP BY - SUBSTR("warehouse"."w_warehouse_name", 1, 20), + SUBSTRING("warehouse"."w_warehouse_name", 1, 20), "ship_mode"."sm_type", "web_site"."web_name" ORDER BY @@ -9017,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", @@ -9396,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, @@ -10433,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" @@ -10638,7 +10638,7 @@ LIMIT 100; # execute: true SELECT c_last_name, c_first_name, - Substr(s_city, 1, 30) AS "_col_2", + SUBSTRING(s_city, 1, 30) AS "_col_2", ss_ticket_number, amt, profit @@ -10667,7 +10667,7 @@ FROM (SELECT ss_ticket_number, WHERE ss_customer_sk = c_customer_sk ORDER BY c_last_name, c_first_name, - Substr(s_city, 1, 30), + SUBSTRING(s_city, 1, 30), profit LIMIT 100; WITH "ms" AS ( @@ -10701,7 +10701,7 @@ WITH "ms" AS ( SELECT "customer"."c_last_name" AS "c_last_name", "customer"."c_first_name" AS "c_first_name", - SUBSTR("ms"."s_city", 1, 30) AS "_col_2", + SUBSTRING("ms"."s_city", 1, 30) AS "_col_2", "ms"."ss_ticket_number" AS "ss_ticket_number", "ms"."amt" AS "amt", "ms"."profit" AS "profit" @@ -10711,7 +10711,7 @@ JOIN "customer" AS "customer" ORDER BY "c_last_name", "c_first_name", - SUBSTR("ms"."s_city", 1, 30), + SUBSTRING("ms"."s_city", 1, 30), "profit" LIMIT 100; @@ -10937,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" @@ -11371,7 +11371,7 @@ LIMIT 100; -- TPC-DS 85 -------------------------------------- # execute: true -SELECT Substr(r_reason_desc, 1, 20) AS "_col_0", +SELECT SUBSTRING(r_reason_desc, 1, 20) AS "_col_0", Avg(ws_quantity) AS "_col_1", Avg(wr_refunded_cash) AS "_col_2", Avg(wr_fee) AS "_col_3" @@ -11417,13 +11417,13 @@ WHERE ws_web_page_sk = wp_web_page_sk AND ca_state IN ( 'FL', 'WI', 'KS' ) AND ws_net_profit BETWEEN 50 AND 250 ) ) GROUP BY r_reason_desc -ORDER BY Substr(r_reason_desc, 1, 20), +ORDER BY SUBSTRING(r_reason_desc, 1, 20), Avg(ws_quantity), Avg(wr_refunded_cash), Avg(wr_fee) LIMIT 100; SELECT - SUBSTR("reason"."r_reason_desc", 1, 20) AS "_col_0", + SUBSTRING("reason"."r_reason_desc", 1, 20) AS "_col_0", AVG("web_sales"."ws_quantity") AS "_col_1", AVG("web_returns"."wr_refunded_cash") AS "_col_2", AVG("web_returns"."wr_fee") AS "_col_3" @@ -11539,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, @@ -12617,7 +12617,7 @@ ORDER BY -- TPC-DS 99 -------------------------------------- # execute: true -SELECT Substr(w_warehouse_name, 1, 20) AS "_col_0", +SELECT SUBSTRING(w_warehouse_name, 1, 20) AS "_col_0", sm_type, cc_name, Sum(CASE @@ -12654,15 +12654,15 @@ WHERE d_month_seq BETWEEN 1200 AND 1200 + 11 AND cs_warehouse_sk = w_warehouse_sk AND cs_ship_mode_sk = sm_ship_mode_sk AND cs_call_center_sk = cc_call_center_sk -GROUP BY Substr(w_warehouse_name, 1, 20), +GROUP BY SUBSTRING(w_warehouse_name, 1, 20), sm_type, cc_name -ORDER BY Substr(w_warehouse_name, 1, 20), +ORDER BY SUBSTRING(w_warehouse_name, 1, 20), sm_type, cc_name LIMIT 100; SELECT - SUBSTR("warehouse"."w_warehouse_name", 1, 20) AS "_col_0", + SUBSTRING("warehouse"."w_warehouse_name", 1, 20) AS "_col_0", "ship_mode"."sm_type" AS "sm_type", "call_center"."cc_name" AS "cc_name", SUM( @@ -12715,7 +12715,7 @@ JOIN "ship_mode" AS "ship_mode" JOIN "warehouse" AS "warehouse" ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk" GROUP BY - SUBSTR("warehouse"."w_warehouse_name", 1, 20), + SUBSTRING("warehouse"."w_warehouse_name", 1, 20), "ship_mode"."sm_type", "call_center"."cc_name" ORDER BY 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; |