summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql1
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql8
-rw-r--r--tests/fixtures/optimizer/simplify.sql67
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql4
4 files changed, 75 insertions, 5 deletions
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",