Merging upstream version 18.17.0.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

5 files changed, 76 insertions, 5 deletions

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",