From 8fe30fd23dc37ec3516e530a86d1c4b604e71241 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 10 Dec 2023 11:46:01 +0100 Subject: Merging upstream version 20.1.0. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/canonicalize.sql | 45 ++++++++++++++++++++++++ tests/fixtures/optimizer/optimizer.sql | 41 ++++++++++++++++++++++ tests/fixtures/optimizer/qualify_columns.sql | 17 +++++++--- tests/fixtures/optimizer/quote_identifiers.sql | 31 +++++++++++++++++ tests/fixtures/optimizer/quote_identities.sql | 11 ------ tests/fixtures/optimizer/simplify.sql | 47 +++++++++++++++++++++++++- tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 16 ++++----- 7 files changed, 183 insertions(+), 25 deletions(-) create mode 100644 tests/fixtures/optimizer/quote_identifiers.sql delete mode 100644 tests/fixtures/optimizer/quote_identities.sql (limited to 'tests/fixtures/optimizer') diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql index 954b1c1..302acb9 100644 --- a/tests/fixtures/optimizer/canonicalize.sql +++ b/tests/fixtures/optimizer/canonicalize.sql @@ -10,15 +10,27 @@ SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w"; SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w; SELECT 1 + 3.2 AS "a" FROM "w" AS "w"; +SELECT '1' + 1 AS "col"; +SELECT '1' + 1 AS "col"; + +SELECT '1' + '1' AS "col"; +SELECT CONCAT('1', '1') AS "col"; + SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day; SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day AS "_col_0"; +SELECT CAST('2022-01-01' AS DATE) IS NULL AS "a"; +SELECT CAST('2022-01-01' AS DATE) IS NULL AS "a"; + -------------------------------------- -- Ensure boolean predicates -------------------------------------- SELECT a FROM x WHERE b; SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE "x"."b" <> 0; +SELECT NOT b FROM x; +SELECT NOT "x"."b" <> 0 AS "_col_0" FROM "x" AS "x"; + SELECT a FROM x GROUP BY a HAVING SUM(b); SELECT "x"."a" AS "a" FROM "x" AS "x" GROUP BY "x"."a" HAVING SUM("x"."b") <> 0; @@ -46,8 +58,41 @@ CAST('2023-01-01' AS TIMESTAMP); TIMESTAMP('2023-01-01', '12:00:00'); TIMESTAMP('2023-01-01', '12:00:00'); +-------------------------------------- +-- Coerce date function args +-------------------------------------- +'2023-01-01' + INTERVAL '1' DAY; +CAST('2023-01-01' AS DATE) + INTERVAL '1' DAY; + +'2023-01-01' + INTERVAL '1' HOUR; +CAST('2023-01-01' AS DATETIME) + INTERVAL '1' HOUR; + +'2023-01-01 00:00:01' + INTERVAL '1' HOUR; +CAST('2023-01-01 00:00:01' AS DATETIME) + INTERVAL '1' HOUR; + +CAST('2023-01-01' AS DATE) + INTERVAL '1' HOUR; +CAST(CAST('2023-01-01' AS DATE) AS DATETIME) + INTERVAL '1' HOUR; + +SELECT t.d + INTERVAL '1' HOUR FROM temporal AS t; +SELECT CAST("t"."d" AS DATETIME) + INTERVAL '1' HOUR AS "_col_0" FROM "temporal" AS "t"; + DATE_ADD(CAST("x" AS DATE), 1, 'YEAR'); DATE_ADD(CAST("x" AS DATE), 1, 'YEAR'); DATE_ADD('2023-01-01', 1, 'YEAR'); DATE_ADD(CAST('2023-01-01' AS DATE), 1, 'YEAR'); + +DATE_ADD('2023-01-01 00:00:00', 1, 'DAY'); +DATE_ADD(CAST('2023-01-01 00:00:00' AS DATETIME), 1, 'DAY'); + +SELECT DATE_ADD(t.d, 1, 'HOUR') FROM temporal AS t; +SELECT DATE_ADD(CAST("t"."d" AS DATETIME), 1, 'HOUR') AS "_col_0" FROM "temporal" AS "t"; + +SELECT DATE_TRUNC('SECOND', t.d) FROM temporal AS t; +SELECT DATE_TRUNC('SECOND', CAST("t"."d" AS DATETIME)) AS "_col_0" FROM "temporal" AS "t"; + +DATE_TRUNC('DAY', '2023-01-01'); +DATE_TRUNC('DAY', CAST('2023-01-01' AS DATE)); + +DATEDIFF('2023-01-01', '2023-01-02', DAY); +DATEDIFF(CAST('2023-01-01' AS DATETIME), CAST('2023-01-02' AS DATETIME), DAY); diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index a9d6584..f81d54a 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -548,6 +548,23 @@ FROM ( FROM "sc"."tb" AS "tb" ) 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 +# execute: false +SELECT * EXCEPT (x), CAST(x AS TEXT) AS x FROM (SELECT a, b, c FROM sc.tb) PIVOT (SUM(c) FOR b IN ('x','y','z')); +SELECT + "_q_1"."a" AS "a", + "_q_1"."y" AS "y", + "_q_1"."z" AS "z", + CAST("_q_1"."x" AS TEXT) AS "x" +FROM ( + SELECT + "tb"."a" AS "a", + "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"; + # title: pivoted source with implicit selections # execute: false SELECT * FROM (SELECT * FROM u) PIVOT (SUM(f) FOR h IN ('x', 'y')); @@ -1074,3 +1091,27 @@ SELECT `_q_0`.`fruitstruct`.`$id` AS `$id`, `_q_0`.`fruitstruct`.`value` AS `value` FROM `_q_0` AS `_q_0`; + +# title: mysql is case-sensitive by default +# dialect: mysql +# execute: false +WITH T AS (SELECT 1 AS CoL) SELECT * FROM `T`; +WITH `T` AS ( + SELECT + 1 AS `CoL` +) +SELECT + `T`.`CoL` AS `CoL` +FROM `T`; + +# title: override mysql's settings so it normalizes to lowercase +# dialect: mysql, normalization_strategy = lowercase +# execute: false +WITH T AS (SELECT 1 AS `CoL`) SELECT * FROM T; +WITH `t` AS ( + SELECT + 1 AS `CoL` +) +SELECT + `t`.`CoL` AS `CoL` +FROM `t`; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 3224a83..43127a9 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -65,10 +65,10 @@ SELECT a AS j, b FROM x ORDER BY j; SELECT x.a AS j, x.b AS b FROM x AS x ORDER BY j; SELECT a AS j, b AS a FROM x ORDER BY 1; -SELECT x.a AS j, x.b AS a FROM x AS x ORDER BY x.a; +SELECT x.a AS j, x.b AS a FROM x AS x ORDER BY j; SELECT SUM(a) AS c, SUM(b) AS d FROM x ORDER BY 1, 2; -SELECT SUM(x.a) AS c, SUM(x.b) AS d FROM x AS x ORDER BY SUM(x.a), SUM(x.b); +SELECT SUM(x.a) AS c, SUM(x.b) AS d FROM x AS x ORDER BY c, d; # execute: false SELECT CAST(a AS INT) FROM x ORDER BY a; @@ -76,7 +76,7 @@ SELECT CAST(x.a AS INT) AS a FROM x AS x ORDER BY a; # execute: false SELECT SUM(a), SUM(b) AS c FROM x ORDER BY 1, 2; -SELECT SUM(x.a) AS _col_0, SUM(x.b) AS c FROM x AS x ORDER BY SUM(x.a), SUM(x.b); +SELECT SUM(x.a) AS _col_0, SUM(x.b) AS c FROM x AS x ORDER BY _col_0, c; SELECT a AS j, b FROM x GROUP BY j, b; SELECT x.a AS j, x.b AS b FROM x AS x GROUP BY x.a, x.b; @@ -85,7 +85,10 @@ SELECT a, b FROM x GROUP BY 1, 2; SELECT x.a AS a, x.b AS b FROM x AS x GROUP BY x.a, x.b; SELECT a, b FROM x ORDER BY 1, 2; -SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a, x.b; +SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY a, b; + +SELECT DISTINCT a AS c, b AS d FROM x ORDER BY 1; +SELECT DISTINCT x.a AS c, x.b AS d FROM x AS x ORDER BY c; SELECT 2 FROM x GROUP BY 1; SELECT 2 AS "2" FROM x AS x GROUP BY 1; @@ -306,6 +309,10 @@ WITH cte AS (SELECT 1 AS x) SELECT cte.a AS a FROM cte AS cte(a); WITH cte(x, y) AS (SELECT 1, 2) SELECT cte.* FROM cte AS cte(a); WITH cte AS (SELECT 1 AS x, 2 AS y) SELECT cte.a AS a, cte.y AS y FROM cte AS cte(a); +# execute: false +WITH player AS (SELECT player.name, player.asset.info FROM players) SELECT * FROM player; +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; + -------------------------------------- -- Except and Replace -------------------------------------- @@ -488,7 +495,7 @@ FROM ( ); SELECT _q_0.i AS i, _q_0.j AS j FROM (SELECT x.a + 1 AS i, x.a + 1 + 1 AS j FROM x AS x) AS _q_0; -# title: wrap expanded alias to ensure operator precedence isn't broken +# title: wrap expanded alias to ensure operator precedence isnt broken # execute: false SELECT x.a + x.b AS f, f * x.b FROM x; SELECT x.a + x.b AS f, (x.a + x.b) * x.b AS _col_1 FROM x AS x; diff --git a/tests/fixtures/optimizer/quote_identifiers.sql b/tests/fixtures/optimizer/quote_identifiers.sql new file mode 100644 index 0000000..21181f7 --- /dev/null +++ b/tests/fixtures/optimizer/quote_identifiers.sql @@ -0,0 +1,31 @@ +SELECT a FROM x; +SELECT "a" FROM "x"; + +SELECT "a" FROM "x"; +SELECT "a" FROM "x"; + +SELECT x.a AS a FROM db.x; +SELECT "x"."a" AS "a" FROM "db"."x"; + +SELECT @x; +SELECT @x; + +# dialect: snowflake +SELECT * FROM DUAL; +SELECT * FROM DUAL; + +# dialect: snowflake +SELECT * FROM "DUAL"; +SELECT * FROM "DUAL"; + +# dialect: snowflake +SELECT * FROM "dual"; +SELECT * FROM "dual"; + +# dialect: snowflake +SELECT dual FROM t; +SELECT "dual" FROM "t"; + +# dialect: snowflake +SELECT * FROM t AS dual; +SELECT * FROM "t" AS "dual"; diff --git a/tests/fixtures/optimizer/quote_identities.sql b/tests/fixtures/optimizer/quote_identities.sql deleted file mode 100644 index d6cfbf8..0000000 --- a/tests/fixtures/optimizer/quote_identities.sql +++ /dev/null @@ -1,11 +0,0 @@ -SELECT a FROM x; -SELECT "a" FROM "x"; - -SELECT "a" FROM "x"; -SELECT "a" FROM "x"; - -SELECT x.a AS a FROM db.x; -SELECT "x"."a" AS "a" FROM "db"."x"; - -SELECT @x; -SELECT @x; diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index f50f688..2206e28 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -43,6 +43,9 @@ TRUE; 1.0 = 1; TRUE; +CAST('2023-01-01' AS DATE) = CAST('2023-01-01' AS DATE); +TRUE; + 'x' = 'y'; FALSE; @@ -360,6 +363,15 @@ x * (1 - y); (1.0 * 3) * 4 - 2 * (5 / 2); 12.0 - 2 * (5 / 2); +a * 0.5 / 10 / (2.0 + 3); +a * 0.5 / 10 / 5.0; + +a * 0.5 - 10 - (2.0 + 3); +a * 0.5 - 10 - 5.0; + +x * (10 - 5); +x * 5; + 6 - 2 + 4 * 2 + a; 12 + a; @@ -414,6 +426,9 @@ FALSE; 1 IS NOT NULL; TRUE; +date '1998-12-01' - interval x day; +CAST('1998-12-01' AS DATE) - INTERVAL x day; + date '1998-12-01' - interval '90' day; CAST('1998-09-02' AS DATE); @@ -447,6 +462,24 @@ CAST(x AS DATETIME) + INTERVAL '1' week; TS_OR_DS_TO_DATE('1998-12-01 00:00:01') - interval '90' day; CAST('1998-09-02' AS DATE); +DATE_ADD(CAST('2023-01-02' AS DATE), -2, 'MONTH'); +CAST('2022-11-02' AS DATE); + +DATE_SUB(CAST('2023-01-02' AS DATE), 1 + 1, 'DAY'); +CAST('2022-12-31' AS DATE); + +DATE_ADD(CAST('2023-01-02' AS DATETIME), -2, 'HOUR'); +CAST('2023-01-01 22:00:00' AS DATETIME); + +DATETIME_ADD(CAST('2023-01-02' AS DATETIME), -2, 'HOUR'); +CAST('2023-01-01 22:00:00' AS DATETIME); + +DATETIME_SUB(CAST('2023-01-02' AS DATETIME), 1 + 1, 'HOUR'); +CAST('2023-01-01 22:00:00' AS DATETIME); + +DATE_ADD(x, 1, 'MONTH'); +DATE_ADD(x, 1, 'MONTH'); + -------------------------------------- -- Comparisons -------------------------------------- @@ -663,6 +696,15 @@ ROW() OVER () = 1 OR ROW() OVER () IS NULL; a AND b AND COALESCE(ROW() OVER (), 1) = 1; a AND b AND (ROW() OVER () = 1 OR ROW() OVER () IS NULL); +COALESCE(1, 2); +1; + +COALESCE(CAST(CAST('2023-01-01' AS TIMESTAMP) AS DATE), x); +CAST(CAST('2023-01-01' AS TIMESTAMP) AS DATE); + +COALESCE(CAST(NULL AS DATE), x); +COALESCE(CAST(NULL AS DATE), x); + -------------------------------------- -- CONCAT -------------------------------------- @@ -673,7 +715,7 @@ CONCAT_WS(sep, x, y); CONCAT_WS(sep, x, y); CONCAT(x); -x; +CONCAT(x); CONCAT('a', 'b', 'c'); 'abc'; @@ -776,6 +818,9 @@ x >= CAST('2022-01-01' AS DATE); DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02')); x >= CAST('2022-01-01' AS DATE); +DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02', '%Y')); +DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02', '%Y')); + -- right is not a date DATE_TRUNC('year', x) <> '2021-01-02'; DATE_TRUNC('year', x) <> '2021-01-02'; diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index 62f1d79..d8cf64f 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -5699,7 +5699,7 @@ WHERE END > 0.1 ORDER BY "v1"."sum_sales" - "v1"."avg_monthly_sales", - "v1"."d_moy" + "d_moy" LIMIT 100; -------------------------------------- @@ -6020,9 +6020,9 @@ WITH "date_dim_2" AS ( WHERE "store"."currency_rank" <= 10 OR "store"."return_rank" <= 10 ORDER BY - 1, - "store"."return_rank", - "store"."currency_rank" + "channel", + "return_rank", + "currency_rank" LIMIT 100 ), "cte_4" AS ( SELECT @@ -6997,7 +6997,7 @@ WHERE END > 0.1 ORDER BY "v1"."sum_sales" - "v1"."avg_monthly_sales", - "v1"."avg_monthly_sales" + "avg_monthly_sales" LIMIT 100; -------------------------------------- @@ -10061,9 +10061,9 @@ WHERE AND "t_s_firstyear"."year1" = 1999 AND "t_s_firstyear"."year_total" > 0 ORDER BY - "t_s_secyear"."customer_id", - "t_s_secyear"."customer_first_name", - "t_s_secyear"."customer_last_name" + "customer_id", + "customer_first_name", + "customer_last_name" LIMIT 100; -------------------------------------- -- cgit v1.2.3