4 files changed, 116 insertions, 0 deletions
diff --git a/tests/fixtures/optimizer/annotate_types.sql b/tests/fixtures/optimizer/annotate_types.sql
new file mode 100644
index 0000000..e781765
--- /dev/null
+++ b/tests/fixtures/optimizer/annotate_types.sql
@@ -0,0 +1,57 @@
+5;
+INT;
+
+5.3;
+DOUBLE;
+
+'bla';
+VARCHAR;
+
+True;
+bool;
+
+false;
+bool;
+
+null;
+null;
+CASE WHEN x THEN NULL ELSE 1 END;
+INT;
+
+CASE WHEN x THEN 1 ELSE NULL END;
+INT;
+
+IF(true, 1, null);
+INT;
+
+IF(true, null, 1);
+INT;
+
+STRUCT(1 AS col);
+STRUCT<col INT>;
+
+STRUCT(1 AS col, 2.5 AS row);
+STRUCT<col INT, row DOUBLE>;
+
+STRUCT(1);
+STRUCT<INT>;
+
+STRUCT(1 AS col, 2.5 AS row, struct(3.5 AS inner_col, 4 AS inner_row) AS nested_struct);
+STRUCT<col INT, row DOUBLE, nested_struct STRUCT<inner_col DOUBLE, inner_row INT>>;
+
+STRUCT(1 AS col, 2.5, ARRAY[1, 2, 3] AS nested_array, 'foo');
+STRUCT<col INT, DOUBLE, nested_array ARRAY<INT>, VARCHAR>;
+
+STRUCT(1, 2.5, 'bar');
+STRUCT<INT, DOUBLE, VARCHAR>;
+
+STRUCT(1 AS "CaseSensitive");
+STRUCT<"CaseSensitive" INT>;
+
+# dialect: duckdb
+STRUCT_PACK(a := 1, b := 2.5);
+STRUCT<a INT, b DOUBLE>;
+
+# dialect: presto
+ROW(1, 2.5, 'foo');
+STRUCT<INT, DOUBLE, VARCHAR>;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index cc72e6d..37ef4fd 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -1388,3 +1388,45 @@ WHERE
 ORDER BY
   COUNT(DISTINCT `cs1`.`cs_order_number`)
 LIMIT 100;
+
+# execute: false
+SELECT
+  *
+FROM event
+WHERE priority = 'High' AND tagname IN (
+  SELECT
+    tag_input AS tagname
+  FROM cascade
+  WHERE tag_input = 'XXX' OR tag_output = 'XXX'
+  UNION
+  SELECT
+    tag_output AS tagname
+  FROM cascade
+  WHERE tag_input = 'XXX' OR tag_output = 'XXX'
+);
+WITH "_u_0" AS (
+  SELECT
+    "cascade"."tag_input" AS "tagname"
+  FROM "cascade" AS "cascade"
+  WHERE
+    "cascade"."tag_input" = 'XXX' OR "cascade"."tag_output" = 'XXX'
+  UNION
+  SELECT
+    "cascade"."tag_output" AS "tagname"
+  FROM "cascade" AS "cascade"
+  WHERE
+    "cascade"."tag_input" = 'XXX' OR "cascade"."tag_output" = 'XXX'
+), "_u_1" AS (
+  SELECT
+    "cascade"."tag_input" AS "tagname"
+  FROM "_u_0" AS "_u_0"
+  GROUP BY
+    "cascade"."tag_input"
+)
+SELECT
+  *
+FROM "event" AS "event"
+LEFT JOIN "_u_1" AS "_u_1"
+  ON "_u_1"."tagname" = "event"."tagname"
+WHERE
+  "event"."priority" = 'High' AND NOT "_u_1"."tagname" IS NULL;
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 289145b..8baf961 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -492,6 +492,11 @@ SELECT x AS x, offset AS offset FROM UNNEST([1, 2]) AS x WITH OFFSET AS offset;
 select * from unnest ([1, 2]) as x with offset as y;
 SELECT x AS x, y AS y FROM UNNEST([1, 2]) AS x WITH OFFSET AS y;
 
+# dialect: bigquery
+# execute: false
+select x, a, x.a from unnest([STRUCT(1 AS a)]) as x;
+SELECT x AS x, a AS a, x.a AS a FROM UNNEST([STRUCT(1 AS a)]) AS x;
+
 # dialect: presto
 SELECT x.a, i.b FROM x CROSS JOIN UNNEST(SPLIT(CAST(b AS VARCHAR), ',')) AS i(b);
 SELECT x.a AS a, i.b AS b FROM x AS x CROSS JOIN UNNEST(SPLIT(CAST(x.b AS VARCHAR), ',')) AS i(b);
@@ -508,6 +513,11 @@ SELECT t.c1 AS c1, t.c2 AS c2, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3);
 SELECT c1, c3 FROM foo(bar) AS t(c1, c2, c3);
 SELECT t.c1 AS c1, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3);
 
+# dialect: redshift
+# execute: false
+SELECT c.f::VARCHAR(MAX) AS f, e AS e FROM a.b AS c, c.d AS e;
+SELECT CAST(c.f AS VARCHAR(MAX)) AS f, e AS e FROM a.b AS c, c.d AS e;
+
 --------------------------------------
 -- Window functions
 --------------------------------------
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index f651a87..104400e 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -166,3 +166,10 @@ WITH cte AS (SELECT b FROM c.db.y AS y) INSERT INTO c.db.s SELECT * FROM cte AS
 # title: qualify wrapped query
 (SELECT x FROM t);
 (SELECT x FROM c.db.t AS t);
+
+# title: replace columns with db/catalog refs
+SELECT db1.a.id, db2.a.id FROM db1.a JOIN db2.a ON db1.a.id = db2.a.id;
+SELECT a.id, a_2.id FROM c.db1.a AS a JOIN c.db2.a AS a_2 ON a.id = a_2.id;
+
+SELECT cat.db1.a.id, db2.a.id FROM cat.db1.a JOIN db2.a ON cat.db1.a.id = db2.a.id;
+SELECT a.id, a_2.id FROM cat.db1.a AS a JOIN c.db2.a AS a_2 ON a.id = a_2.id;