5 files changed, 73 insertions, 3 deletions
diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
index 8542c3e..e18d2a4 100644
--- a/tests/fixtures/optimizer/eliminate_subqueries.sql
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -97,3 +97,7 @@ WITH cte1 AS (SELECT a FROM x), cte2 AS (SELECT a FROM cte1 AS cte3) SELECT a FR
 -- Wrapped subquery joined with table
 SELECT * FROM ((SELECT c FROM t1) JOIN t2);
 WITH cte AS (SELECT c FROM t1) SELECT * FROM (cte AS cte, t2);
+
+-- Wrapped subquery with redundant parentheses
+SELECT * FROM (((SELECT * FROM tbl)));
+WITH cte AS (SELECT * FROM tbl) SELECT * FROM cte AS cte;
diff --git a/tests/fixtures/optimizer/optimize_joins.sql b/tests/fixtures/optimizer/optimize_joins.sql
index b8e39c3..6d5bed2 100644
--- a/tests/fixtures/optimizer/optimize_joins.sql
+++ b/tests/fixtures/optimizer/optimize_joins.sql
@@ -14,7 +14,7 @@ SELECT * FROM x INNER JOIN z ON x.id = z.id;
 SELECT * FROM x JOIN z ON x.id = z.id;
 
 SELECT * FROM x LEFT OUTER JOIN z;
-SELECT * FROM x LEFT JOIN z;
+SELECT * FROM x LEFT JOIN z ON TRUE;
 
 SELECT * FROM x CROSS JOIN z;
 SELECT * FROM x CROSS JOIN z;
@@ -22,11 +22,17 @@ SELECT * FROM x CROSS JOIN z;
 SELECT * FROM x JOIN z;
 SELECT * FROM x CROSS JOIN z;
 
+SELECT * FROM x FULL JOIN z;
+SELECT * FROM x FULL JOIN z ON TRUE;
+
 SELECT * FROM x NATURAL JOIN z;
-SELECT * FROM x NATURAL JOIN z;
+SELECT * FROM x NATURAL JOIN z ON TRUE;
 
 SELECT * FROM x RIGHT JOIN z;
-SELECT * FROM x RIGHT JOIN z;
+SELECT * FROM x RIGHT JOIN z ON TRUE;
 
 SELECT * FROM x JOIN z USING (id);
 SELECT * FROM x JOIN z USING (id);
+
+SELECT * FROM x CROSS JOIN z ON TRUE;
+SELECT * FROM x CROSS JOIN z;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index b318a92..18ee804 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -950,3 +950,40 @@ JOIN "n" AS "foo"("a")
 SELECT CONCAT('a', 'b') || CONCAT(CONCAT('c', 'd'), CONCAT('e', 'f')) + ('g' || 'h' || 'i');
 SELECT
   'abcdefghi' AS "_col_0";
+
+# title: complex query with derived tables and redundant parentheses
+# execute: false
+# dialect: snowflake
+SELECT
+  ("SUBQUERY_0"."KEY") AS "SUBQUERY_1_COL_0"
+FROM
+  (
+    SELECT
+      *
+    FROM
+      (((
+          SELECT
+            *
+          FROM
+            (
+              SELECT
+                event_name AS key,
+                insert_ts
+              FROM
+                (
+                  SELECT
+                    insert_ts,
+                    event_name
+                  FROM
+                    sales
+                  WHERE
+                    insert_ts > '2023-08-07 21:03:35.590 -0700'
+                )
+            )
+      ))) AS "SF_CONNECTOR_QUERY_ALIAS"
+  ) AS "SUBQUERY_0";
+SELECT
+  "SALES"."EVENT_NAME" AS "SUBQUERY_1_COL_0"
+FROM "SALES" AS "SALES"
+WHERE
+  "SALES"."INSERT_TS" > '2023-08-07 21:03:35.590 -0700';
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index 79ce353..cfa69fb 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -36,3 +36,11 @@ WITH t1 AS (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) A
 
 WITH m AS (SELECT a, b FROM (VALUES (1, 2)) AS a1(a, b)), n AS (SELECT a, b FROM m WHERE m.a = 1), o AS (SELECT a, b FROM m WHERE m.a = 2) SELECT n.a, n.b, n.a, o.b FROM n FULL OUTER JOIN o ON n.a = o.a;
 WITH m AS (SELECT a, b FROM (VALUES (1, 2)) AS a1(a, b)), n AS (SELECT a, b FROM m WHERE m.a = 1), o AS (SELECT a, b FROM m WHERE m.a = 2) SELECT n.a, n.b, n.a, o.b FROM n FULL OUTER JOIN o ON n.a = o.a;
+
+-- Pushdown predicate to HAVING (CNF)
+SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt FROM x AS x) AS x WHERE x.cnt > 0;
+SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt FROM x AS x HAVING COUNT(1) > 0) AS x WHERE TRUE;
+
+-- Pushdown predicate to HAVING (DNF)
+SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt, COUNT(x.a) AS cnt_a, COUNT(x.b) AS cnt_b FROM x AS x) AS x WHERE (x.cnt_a > 0 AND x.cnt_b > 0) OR x.cnt > 0;
+SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt, COUNT(x.a) AS cnt_a, COUNT(x.b) AS cnt_b FROM x AS x HAVING COUNT(1) > 0 OR (COUNT(x.a) > 0 AND COUNT(x.b) > 0)) AS x WHERE x.cnt > 0 OR (x.cnt_a > 0 AND x.cnt_b > 0);
+\ No newline at end of file
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 3ed02cd..66fb19c 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -264,6 +264,9 @@ TRUE;
 (FALSE);
 FALSE;
 
+((TRUE));
+TRUE;
+
 (FALSE OR TRUE);
 TRUE;
 
@@ -288,6 +291,9 @@ x = y AND z;
 x * (1 - y);
 x * (1 - y);
 
+(((x % 20) = 0) = TRUE);
+((x % 20) = 0) = TRUE;
+
 --------------------------------------
 -- Literals
 --------------------------------------
@@ -612,6 +618,9 @@ TRUE;
 x = 2018 OR x <> 2018;
 x <> 2018 OR x = 2018;
 
+t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y;
+t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y;
+
 --------------------------------------
 -- Coalesce
 --------------------------------------
@@ -645,6 +654,12 @@ x = 1 OR x IS NULL;
 COALESCE(x, 1) IS NULL;
 FALSE;
 
+COALESCE(ROW() OVER (), 1) = 1;
+ROW() OVER () = 1 OR ROW() OVER () IS NULL;
+
+a AND b AND COALESCE(ROW() OVER (), 1) = 1;
+a AND b AND (ROW() OVER () = 1 OR ROW() OVER () IS NULL);
+
 --------------------------------------
 -- CONCAT
 --------------------------------------