From c03ba18c491e52cc85d8aae1825dd9e0b4f75e32 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Thu, 26 Oct 2023 19:21:54 +0200 Subject: Merging upstream version 18.17.0. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 1 + tests/fixtures/optimizer/canonicalize.sql | 1 - tests/fixtures/optimizer/qualify_tables.sql | 8 ++++ tests/fixtures/optimizer/simplify.sql | 67 ++++++++++++++++++++++++++++- tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 4 +- 5 files changed, 76 insertions(+), 5 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 2738707..6e0a3e5 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -866,3 +866,4 @@ KILL CONNECTION 123 KILL QUERY '123' CHR(97) SELECT * FROM UNNEST(x) WITH ORDINALITY UNION ALL SELECT * FROM UNNEST(y) WITH ORDINALITY +WITH use(use) AS (SELECT 1) SELECT use FROM use diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql index 2ba762d..954b1c1 100644 --- a/tests/fixtures/optimizer/canonicalize.sql +++ b/tests/fixtures/optimizer/canonicalize.sql @@ -16,7 +16,6 @@ SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day AS "_col_0"; -------------------------------------- -- Ensure boolean predicates -------------------------------------- - SELECT a FROM x WHERE b; SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE "x"."b" <> 0; diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index f43ac01..3717cd4 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -109,3 +109,11 @@ SELECT * FROM ((SELECT * FROM c.db.t AS t) AS _q_0); # title: wrapped subquery without alias joined with a table SELECT * FROM ((SELECT * FROM t1) INNER JOIN t2 ON a = b); SELECT * FROM ((SELECT * FROM c.db.t1 AS t1) AS _q_0 INNER JOIN c.db.t2 AS t2 ON a = b); + +# title: lateral unnest with alias +SELECT x FROM t, LATERAL UNNEST(t.xs) AS x; +SELECT x FROM c.db.t AS t, LATERAL UNNEST(t.xs) AS x; + +# title: lateral unnest without alias +SELECT x FROM t, LATERAL UNNEST(t.xs); +SELECT x FROM c.db.t AS t, LATERAL UNNEST(t.xs) AS _q_0; diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index e54170c..c53a972 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -911,13 +911,76 @@ t1.a = 39 AND t2.b = t1.a AND t3.c = t2.b; t1.a = 39 AND t2.b = 39 AND t3.c = 39; x = 1 AND CASE WHEN x = 5 THEN FALSE ELSE TRUE END; -x = 1 AND CASE WHEN FALSE THEN FALSE ELSE TRUE END; +x = 1; x = 1 AND IF(x = 5, FALSE, TRUE); -x = 1 AND CASE WHEN FALSE THEN FALSE ELSE TRUE END; +x = 1; + +x = 1 AND CASE x WHEN 5 THEN FALSE ELSE TRUE END; +x = 1; x = y AND CASE WHEN x = 5 THEN FALSE ELSE TRUE END; x = y AND CASE WHEN x = 5 THEN FALSE ELSE TRUE END; x = 1 AND CASE WHEN y = 5 THEN x = z END; x = 1 AND CASE WHEN y = 5 THEN 1 = z END; + +-------------------------------------- +-- Simplify Conditionals +-------------------------------------- +IF(TRUE, x, y); +x; + +IF(FALSE, x, y); +y; + +IF(FALSE, x); +NULL; + +IF(NULL, x, y); +y; + +IF(cond, x, y); +CASE WHEN cond THEN x ELSE y END; + +CASE WHEN TRUE THEN x ELSE y END; +x; + +CASE WHEN FALSE THEN x ELSE y END; +y; + +CASE WHEN FALSE THEN x WHEN FALSE THEN y WHEN TRUE THEN z END; +z; + +CASE NULL WHEN NULL THEN x ELSE y END; +y; + +CASE 4 WHEN 1 THEN x WHEN 2 THEN y WHEN 3 THEN z ELSE w END; +w; + +CASE 4 WHEN 1 THEN x WHEN 2 THEN y WHEN 3 THEN z WHEN 4 THEN w END; +w; + +CASE WHEN value = 1 THEN x ELSE y END; +CASE WHEN value = 1 THEN x ELSE y END; + +CASE WHEN FALSE THEN x END; +NULL; + +CASE 1 WHEN 1 + 1 THEN x END; +NULL; + +CASE WHEN cond THEN x ELSE y END; +CASE WHEN cond THEN x ELSE y END; + +CASE WHEN cond THEN x END; +CASE WHEN cond THEN x END; + +CASE x WHEN y THEN z ELSE w END; +CASE WHEN x = y THEN z ELSE w END; + +CASE x WHEN y THEN z END; +CASE WHEN x = y THEN z END; + +CASE x1 + x2 WHEN x3 THEN x4 WHEN x5 + x6 THEN x7 ELSE x8 END; +CASE WHEN (x1 + x2) = x3 THEN x4 WHEN (x1 + x2) = (x5 + x6) THEN x7 ELSE x8 END; diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index 91b553e..52ee12c 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -4808,10 +4808,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 FALSE THEN NULL ELSE "foo"."stdev" / "foo"."mean" END AS "cov" + CASE WHEN "foo"."mean" = 0 THEN NULL ELSE "foo"."stdev" / "foo"."mean" END AS "cov" FROM "foo" AS "foo" WHERE - CASE "foo"."mean" WHEN FALSE THEN 0 ELSE "foo"."stdev" / "foo"."mean" END > 1 + CASE WHEN "foo"."mean" = 0 THEN 0 ELSE "foo"."stdev" / "foo"."mean" END > 1 ) SELECT "inv1"."w_warehouse_sk" AS "w_warehouse_sk", -- cgit v1.2.3