From d7f0758e21b5111b5327f3839c5c9f49a04d272b Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Wed, 4 Oct 2023 14:14:40 +0200 Subject: Adding upstream version 18.11.2. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 6 ++++-- tests/fixtures/optimizer/canonicalize.sql | 12 +++++++++++ tests/fixtures/optimizer/normalize_identifiers.sql | 8 ++++++++ tests/fixtures/optimizer/optimizer.sql | 22 +++++++++++++++++++++ tests/fixtures/optimizer/simplify.sql | 23 ++++++++++++++++++++++ tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 6 +++--- 6 files changed, 72 insertions(+), 5 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 17506e4..2738707 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -771,8 +771,8 @@ ALTER TABLE integers DROP COLUMN k ALTER TABLE integers DROP PRIMARY KEY ALTER TABLE integers DROP COLUMN IF EXISTS k ALTER TABLE integers DROP COLUMN k CASCADE -ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR -ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR USING CONCAT(i, '_', j) +ALTER TABLE integers ALTER COLUMN i SET DATA TYPE VARCHAR +ALTER TABLE integers ALTER COLUMN i SET DATA TYPE VARCHAR USING CONCAT(i, '_', j) ALTER TABLE integers ALTER COLUMN i SET DEFAULT 10 ALTER TABLE integers ALTER COLUMN i DROP DEFAULT ALTER TABLE mydataset.mytable DROP COLUMN A, DROP COLUMN IF EXISTS B @@ -864,3 +864,5 @@ SELECT x FROM y ORDER BY x ASC KILL '123' KILL CONNECTION 123 KILL QUERY '123' +CHR(97) +SELECT * FROM UNNEST(x) WITH ORDINALITY UNION ALL SELECT * FROM UNNEST(y) WITH ORDINALITY diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql index e27b2d3..2ba762d 100644 --- a/tests/fixtures/optimizer/canonicalize.sql +++ b/tests/fixtures/optimizer/canonicalize.sql @@ -29,6 +29,12 @@ SELECT "x"."a" AS "a" FROM "x" AS "x" GROUP BY "x"."a" HAVING SUM("x"."b") <> 0 SELECT a FROM x WHERE 1; SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE 1 <> 0; +SELECT a FROM x WHERE COALESCE(0, 1); +SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE COALESCE(0 <> 0, 1 <> 0); + +SELECT a FROM x WHERE CASE WHEN COALESCE(b, 1) THEN 1 ELSE 0 END; +SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE CASE WHEN COALESCE("x"."b" <> 0, 1 <> 0) THEN 1 ELSE 0 END <> 0; + -------------------------------------- -- Replace date functions -------------------------------------- @@ -40,3 +46,9 @@ CAST('2023-01-01' AS TIMESTAMP); TIMESTAMP('2023-01-01', '12:00:00'); TIMESTAMP('2023-01-01', '12:00:00'); + +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'); diff --git a/tests/fixtures/optimizer/normalize_identifiers.sql b/tests/fixtures/optimizer/normalize_identifiers.sql index 2ab4778..4cb7dd1 100644 --- a/tests/fixtures/optimizer/normalize_identifiers.sql +++ b/tests/fixtures/optimizer/normalize_identifiers.sql @@ -62,3 +62,11 @@ SELECT a AS a FROM x UNION SELECT a AS a FROM x; (SELECT A AS A FROM X); (SELECT a AS a FROM x); + +# dialect: snowflake +SELECT a /* sqlglot.meta case_sensitive */, b FROM table /* sqlglot.meta case_sensitive */; +SELECT a /* sqlglot.meta case_sensitive */, B FROM table /* sqlglot.meta case_sensitive */; + +# dialect: redshift +SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM table; +SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM table; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index e59f14d..4cc62c9 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -1023,3 +1023,25 @@ SELECT FROM "table1" AS "table1" LEFT JOIN "alias3" ON "table1"."cid" = "alias3"."cid"; + +# title: CTE with EXPLODE cannot be merged +# dialect: spark +# execute: false +SELECT Name, + FruitStruct.`$id`, + FruitStruct.value + FROM + (SELECT Name, + explode(Fruits) as FruitStruct + FROM fruits_table); +WITH `_q_0` AS ( + SELECT + `fruits_table`.`name` AS `name`, + EXPLODE(`fruits_table`.`fruits`) AS `fruitstruct` + FROM `fruits_table` AS `fruits_table` +) +SELECT + `_q_0`.`name` AS `name`, + `_q_0`.`fruitstruct`.`$id` AS `$id`, + `_q_0`.`fruitstruct`.`value` AS `value` +FROM `_q_0` AS `_q_0`; diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index 584e9d6..a9ae192 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -444,6 +444,9 @@ CAST('1998-09-02 00:00:00' AS DATETIME); CAST(x AS DATETIME) + interval '1' week; 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); + -------------------------------------- -- Comparisons -------------------------------------- @@ -681,6 +684,9 @@ CONCAT('a', x, y, 'bc'); 'a' || 'b' || x; CONCAT('ab', x); +CONCAT(a, b) IN (SELECT * FROM foo WHERE cond); +CONCAT(a, b) IN (SELECT * FROM foo WHERE cond); + -------------------------------------- -- DATE_TRUNC -------------------------------------- @@ -740,6 +746,9 @@ x >= CAST('2022-01-01' AS DATE); DATE_TRUNC('year', x) > CAST('2021-01-02' AS DATE); 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); + -- right is not a date DATE_TRUNC('year', x) <> '2021-01-02'; DATE_TRUNC('year', x) <> '2021-01-02'; @@ -758,6 +767,17 @@ x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); TIMESTAMP_TRUNC(x, YEAR) = CAST('2021-01-01' AS DATETIME); x < CAST('2022-01-01 00:00:00' AS DATETIME) AND x >= CAST('2021-01-01 00:00:00' AS DATETIME); +-- right side is not a date literal +DATE_TRUNC('day', x) = CAST(y AS DATE); +DATE_TRUNC('day', x) = CAST(y AS DATE); + +-- nested cast +DATE_TRUNC('day', x) = CAST(CAST('2021-01-01 01:02:03' AS DATETIME) AS DATE); +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); + -------------------------------------- -- EQUALITY -------------------------------------- @@ -794,6 +814,9 @@ x = 2; x - INTERVAL 1 DAY = CAST('2021-01-01' AS DATE); x = CAST('2021-01-02' AS DATE); +x - INTERVAL 1 DAY = TS_OR_DS_TO_DATE('2021-01-01 00:00:01'); +x = CAST('2021-01-02' AS DATE); + x - INTERVAL 1 HOUR > CAST('2021-01-01' AS DATETIME); x > CAST('2021-01-01 01:00:00' AS DATETIME); diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index f50cf0b..2218182 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -4793,10 +4793,10 @@ WITH "foo" AS ( "foo"."i_item_sk" AS "i_item_sk", "foo"."d_moy" AS "d_moy", "foo"."mean" AS "mean", - CASE "foo"."mean" WHEN 0 THEN NULL ELSE "foo"."stdev" / "foo"."mean" END AS "cov" + CASE "foo"."mean" WHEN FALSE THEN NULL ELSE "foo"."stdev" / "foo"."mean" END AS "cov" FROM "foo" AS "foo" WHERE - CASE "foo"."mean" WHEN 0 THEN 0 ELSE "foo"."stdev" / "foo"."mean" END > 1 + CASE "foo"."mean" WHEN FALSE THEN 0 ELSE "foo"."stdev" / "foo"."mean" END > 1 ) SELECT "inv1"."w_warehouse_sk" AS "w_warehouse_sk", @@ -9775,7 +9775,7 @@ JOIN "date_dim" AS "d1" ON "catalog_sales"."cs_sold_date_sk" = "d1"."d_date_sk" AND "d1"."d_week_seq" = "d2"."d_week_seq" AND "d1"."d_year" = 2002 - AND "d3"."d_date" > CONCAT("d1"."d_date", INTERVAL '5' day) + AND "d3"."d_date" > "d1"."d_date" + INTERVAL '5' day GROUP BY "item"."i_item_desc", "warehouse"."w_warehouse_name", -- cgit v1.2.3