From bd2d949d1f2fb728cf4c429dd3ae9a1510e10182 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Mon, 25 Sep 2023 10:20:06 +0200 Subject: Adding upstream version 18.7.0. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 5 + tests/fixtures/optimizer/canonicalize.sql | 12 +++ tests/fixtures/optimizer/simplify.sql | 134 +++++++++++++++++++++++++ tests/fixtures/optimizer/unnest_subqueries.sql | 12 +++ tests/fixtures/pretty.sql | 7 +- 5 files changed, 167 insertions(+), 3 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index f999620..17506e4 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -401,6 +401,7 @@ SELECT 1 FROM a INNER JOIN b ON a.x = b.x SELECT 1 FROM a LEFT JOIN b ON a.x = b.x SELECT 1 FROM a RIGHT JOIN b ON a.x = b.x SELECT 1 FROM a CROSS JOIN b ON a.x = b.x +SELECT 1 FROM a SEMI JOIN b ON a.x = b.x SELECT 1 FROM a LEFT SEMI JOIN b ON a.x = b.x SELECT 1 FROM a LEFT ANTI JOIN b ON a.x = b.x SELECT 1 FROM a RIGHT SEMI JOIN b ON a.x = b.x @@ -859,3 +860,7 @@ SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1) /* comment */ CREATE TABLE foo AS SELECT 1 SELECT next, transform, if SELECT "any", "case", "if", "next" +SELECT x FROM y ORDER BY x ASC +KILL '123' +KILL CONNECTION 123 +KILL QUERY '123' diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql index 1fc44ef..e27b2d3 100644 --- a/tests/fixtures/optimizer/canonicalize.sql +++ b/tests/fixtures/optimizer/canonicalize.sql @@ -28,3 +28,15 @@ 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; + +-------------------------------------- +-- Replace date functions +-------------------------------------- +DATE('2023-01-01'); +CAST('2023-01-01' AS DATE); + +TIMESTAMP('2023-01-01'); +CAST('2023-01-01' AS TIMESTAMP); + +TIMESTAMP('2023-01-01', '12:00:00'); +TIMESTAMP('2023-01-01', '12:00:00'); diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index 66fb19c..584e9d6 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -680,3 +680,137 @@ CONCAT('a', x, y, 'bc'); 'a' || 'b' || x; CONCAT('ab', x); + +-------------------------------------- +-- DATE_TRUNC +-------------------------------------- +DATE_TRUNC('year', x) = CAST('2021-01-01' AS DATE); +x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); + +DATE_TRUNC('quarter', x) = CAST('2021-01-01' AS DATE); +x < CAST('2021-04-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); + +DATE_TRUNC('month', x) = CAST('2021-01-01' AS DATE); +x < CAST('2021-02-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); + +DATE_TRUNC('week', x) = CAST('2021-01-04' AS DATE); +x < CAST('2021-01-11' AS DATE) AND x >= CAST('2021-01-04' AS DATE); + +DATE_TRUNC('day', x) = CAST('2021-01-01' AS DATE); +x < CAST('2021-01-02' AS DATE) AND x >= CAST('2021-01-01' AS DATE); + +CAST('2021-01-01' AS DATE) = DATE_TRUNC('year', x); +x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); + +-- Always false, except for nulls +DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE); +DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE); + +DATE_TRUNC('year', x) <> CAST('2021-01-01' AS DATE); +x < CAST('2021-01-01' AS DATE) AND x >= CAST('2022-01-01' AS DATE); + +-- Always true, except for nulls +DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE); +DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE); + +DATE_TRUNC('year', x) <= CAST('2021-01-01' AS DATE); +x < CAST('2022-01-01' AS DATE); + +DATE_TRUNC('year', x) <= CAST('2021-01-02' AS DATE); +x < CAST('2022-01-01' AS DATE); + +CAST('2021-01-01' AS DATE) >= DATE_TRUNC('year', x); +x < CAST('2022-01-01' AS DATE); + +DATE_TRUNC('year', x) < CAST('2021-01-01' AS DATE); +x < CAST('2021-01-01' AS DATE); + +DATE_TRUNC('year', x) < CAST('2021-01-02' AS DATE); +x < CAST('2021-01-01' AS DATE); + +DATE_TRUNC('year', x) >= CAST('2021-01-01' AS DATE); +x >= CAST('2021-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) > CAST('2021-01-01' AS DATE); +x >= CAST('2022-01-01' AS DATE); + +DATE_TRUNC('year', x) > CAST('2021-01-02' AS DATE); +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'; + +DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2023-01-01' AS DATE)); +(x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE)) OR (x < CAST('2024-01-01' AS DATE) AND x >= CAST('2023-01-01' AS DATE)); + +-- merge ranges +DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2022-01-01' AS DATE)); +x < CAST('2023-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); + +-- one of the values will always be false +DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2022-01-02' AS DATE)); +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); + +-------------------------------------- +-- EQUALITY +-------------------------------------- +x + 1 = 3; +x = 2; + +1 + x = 3; +x = 2; + +3 = x + 1; +x = 2; + +x - 1 = 3; +x = 4; + +x + 1 > 3; +x > 2; + +x + 1 >= 3; +x >= 2; + +x + 1 <= 3; +x <= 2; + +x + 1 <= 3; +x <= 2; + +x + 1 <> 3; +x <> 2; + +1 + x + 1 = 3 + 1; +x = 2; + +x - INTERVAL 1 DAY = CAST('2021-01-01' AS DATE); +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); + +DATETIME_ADD(x, 1, HOUR) < CAST('2021-01-01' AS DATETIME); +x < CAST('2020-12-31 23:00:00' AS DATETIME); + +DATETIME_SUB(x, 1, DAY) >= CAST('2021-01-01' AS DATETIME); +x >= CAST('2021-01-02 00:00:00' AS DATETIME); + +DATE_ADD(x, 1, DAY) <= CAST('2021-01-01' AS DATE); +x <= CAST('2020-12-31' AS DATE); + +DATE_SUB(x, 1, DAY) <> CAST('2021-01-01' AS DATE); +x <> CAST('2021-01-02' AS DATE); + +DATE_ADD(DATE_ADD(DATE_TRUNC('week', DATE_SUB(x, 1, DAY)), 1, DAY), 1, YEAR) < CAST('2021-01-08' AS DATE); +x < CAST('2020-01-07' AS DATE); + +x - INTERVAL '1' day = CAST(y AS DATE); +x - INTERVAL '1' day = CAST(y AS DATE); diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql index 9d760e0..e78bed0 100644 --- a/tests/fixtures/optimizer/unnest_subqueries.sql +++ b/tests/fixtures/optimizer/unnest_subqueries.sql @@ -209,3 +209,15 @@ WHERE ) AND ARRAY_ALL(_u_19."", _x -> _x = x.a) AND x.a > COALESCE(_u_21.d, 0); +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; diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql index 1a61334..23d9511 100644 --- a/tests/fixtures/pretty.sql +++ b/tests/fixtures/pretty.sql @@ -346,7 +346,7 @@ SELECT fruit, basket_index FROM table_data -CROSS JOIN UNNEST(fruit_basket) AS fruit WITH OFFSET basket_index; +CROSS JOIN UNNEST(fruit_basket) WITH ORDINALITY AS fruit(basket_index); WITH table_data AS ( SELECT 'bob' AS name, @@ -357,11 +357,12 @@ SELECT fruit, basket_index FROM table_data -CROSS JOIN UNNEST(fruit_basket) AS fruit WITH OFFSET AS basket_index; +CROSS JOIN UNNEST(fruit_basket) WITH ORDINALITY AS fruit(basket_index); SELECT A.* EXCEPT A.COL_1, A.COL_2 FROM TABLE_1 A; SELECT A.* - EXCEPT (A.COL_1, A.COL_2) + EXCEPT (A.COL_1), + A.COL_2 FROM TABLE_1 AS A; SELECT * -- cgit v1.2.3