5 files changed, 167 insertions, 3 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index f999620..17506e4 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -401,6 +401,7 @@ SELECT 1 FROM a INNER JOIN b ON a.x = b.x
 SELECT 1 FROM a LEFT JOIN b ON a.x = b.x
 SELECT 1 FROM a RIGHT JOIN b ON a.x = b.x
 SELECT 1 FROM a CROSS JOIN b ON a.x = b.x
+SELECT 1 FROM a SEMI JOIN b ON a.x = b.x
 SELECT 1 FROM a LEFT SEMI JOIN b ON a.x = b.x
 SELECT 1 FROM a LEFT ANTI JOIN b ON a.x = b.x
 SELECT 1 FROM a RIGHT SEMI JOIN b ON a.x = b.x
@@ -859,3 +860,7 @@ SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1)
 /* comment */ CREATE TABLE foo AS SELECT 1
 SELECT next, transform, if
 SELECT "any", "case", "if", "next"
+SELECT x FROM y ORDER BY x ASC
+KILL '123'
+KILL CONNECTION 123
+KILL QUERY '123'
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index 1fc44ef..e27b2d3 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -28,3 +28,15 @@ SELECT "x"."a" AS "a" FROM "x" AS "x" GROUP BY "x"."a" HAVING SUM("x"."b") <> 0
 
 SELECT a FROM x WHERE 1;
 SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE 1 <> 0;
+
+--------------------------------------
+-- Replace date functions
+--------------------------------------
+DATE('2023-01-01');
+CAST('2023-01-01' AS DATE);
+
+TIMESTAMP('2023-01-01');
+CAST('2023-01-01' AS TIMESTAMP);
+
+TIMESTAMP('2023-01-01', '12:00:00');
+TIMESTAMP('2023-01-01', '12:00:00');
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 66fb19c..584e9d6 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -680,3 +680,137 @@ CONCAT('a', x, y, 'bc');
 
 'a' || 'b' || x;
 CONCAT('ab', x);
+
+--------------------------------------
+-- DATE_TRUNC
+--------------------------------------
+DATE_TRUNC('year', x) = CAST('2021-01-01' AS DATE);
+x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
+
+DATE_TRUNC('quarter', x) = CAST('2021-01-01' AS DATE);
+x < CAST('2021-04-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
+
+DATE_TRUNC('month', x) = CAST('2021-01-01' AS DATE);
+x < CAST('2021-02-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
+
+DATE_TRUNC('week', x) = CAST('2021-01-04' AS DATE);
+x < CAST('2021-01-11' AS DATE) AND x >= CAST('2021-01-04' AS DATE);
+
+DATE_TRUNC('day', x) = CAST('2021-01-01' AS DATE);
+x < CAST('2021-01-02' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
+
+CAST('2021-01-01' AS DATE) = DATE_TRUNC('year', x);
+x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
+
+-- Always false, except for nulls
+DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE);
+DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE);
+
+DATE_TRUNC('year', x) <> CAST('2021-01-01' AS DATE);
+x < CAST('2021-01-01' AS DATE) AND x >= CAST('2022-01-01' AS DATE);
+
+-- Always true, except for nulls
+DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE);
+DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE);
+
+DATE_TRUNC('year', x) <= CAST('2021-01-01' AS DATE);
+x < CAST('2022-01-01' AS DATE);
+
+DATE_TRUNC('year', x) <= CAST('2021-01-02' AS DATE);
+x < CAST('2022-01-01' AS DATE);
+
+CAST('2021-01-01' AS DATE) >= DATE_TRUNC('year', x);
+x < CAST('2022-01-01' AS DATE);
+
+DATE_TRUNC('year', x) < CAST('2021-01-01' AS DATE);
+x < CAST('2021-01-01' AS DATE);
+
+DATE_TRUNC('year', x) < CAST('2021-01-02' AS DATE);
+x < CAST('2021-01-01' AS DATE);
+
+DATE_TRUNC('year', x) >= CAST('2021-01-01' AS DATE);
+x >= CAST('2021-01-01' AS DATE);
+
+DATE_TRUNC('year', x) >= CAST('2021-01-02' AS DATE);
+x >= CAST('2022-01-01' AS DATE);
+
+DATE_TRUNC('year', x) > CAST('2021-01-01' AS DATE);
+x >= CAST('2022-01-01' AS DATE);
+
+DATE_TRUNC('year', x) > CAST('2021-01-02' AS DATE);
+x >= CAST('2022-01-01' AS DATE);
+
+-- right is not a date
+DATE_TRUNC('year', x) <> '2021-01-02';
+DATE_TRUNC('year', x) <> '2021-01-02';
+
+DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2023-01-01' AS DATE));
+(x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE)) OR (x < CAST('2024-01-01' AS DATE) AND x >= CAST('2023-01-01' AS DATE));
+
+-- merge ranges
+DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2022-01-01' AS DATE));
+x < CAST('2023-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
+
+-- one of the values will always be false
+DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2022-01-02' AS DATE));
+x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
+
+TIMESTAMP_TRUNC(x, YEAR) = CAST('2021-01-01' AS DATETIME);
+x < CAST('2022-01-01 00:00:00' AS DATETIME) AND x >= CAST('2021-01-01 00:00:00' AS DATETIME);
+
+--------------------------------------
+-- EQUALITY
+--------------------------------------
+x + 1 = 3;
+x = 2;
+
+1 + x = 3;
+x = 2;
+
+3 = x + 1;
+x = 2;
+
+x - 1 = 3;
+x = 4;
+
+x + 1 > 3;
+x > 2;
+
+x + 1 >= 3;
+x >= 2;
+
+x + 1 <= 3;
+x <= 2;
+
+x + 1 <= 3;
+x <= 2;
+
+x + 1 <> 3;
+x <> 2;
+
+1 + x + 1 = 3 + 1;
+x = 2;
+
+x - INTERVAL 1 DAY = CAST('2021-01-01' AS DATE);
+x = CAST('2021-01-02' AS DATE);
+
+x - INTERVAL 1 HOUR > CAST('2021-01-01' AS DATETIME);
+x > CAST('2021-01-01 01:00:00' AS DATETIME);
+
+DATETIME_ADD(x, 1, HOUR) < CAST('2021-01-01' AS DATETIME);
+x < CAST('2020-12-31 23:00:00' AS DATETIME);
+
+DATETIME_SUB(x, 1, DAY) >= CAST('2021-01-01' AS DATETIME);
+x >= CAST('2021-01-02 00:00:00' AS DATETIME);
+
+DATE_ADD(x, 1, DAY) <= CAST('2021-01-01' AS DATE);
+x <= CAST('2020-12-31' AS DATE);
+
+DATE_SUB(x, 1, DAY) <> CAST('2021-01-01' AS DATE);
+x <> CAST('2021-01-02' AS DATE);
+
+DATE_ADD(DATE_ADD(DATE_TRUNC('week', DATE_SUB(x, 1, DAY)), 1, DAY), 1, YEAR) < CAST('2021-01-08' AS DATE);
+x < CAST('2020-01-07' AS DATE);
+
+x - INTERVAL '1' day = CAST(y AS DATE);
+x - INTERVAL '1' day = CAST(y AS DATE);
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index 9d760e0..e78bed0 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -209,3 +209,15 @@ WHERE
   )
   AND ARRAY_ALL(_u_19."", _x -> _x = x.a)
   AND x.a > COALESCE(_u_21.d, 0);
+SELECT
+  CAST((
+    SELECT
+      x.a AS a
+    FROM x
+  ) AS TEXT) AS a;
+SELECT
+  CAST((
+    SELECT
+      x.a AS a
+    FROM x
+  ) AS TEXT) AS a;
diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql
index 1a61334..23d9511 100644
--- a/tests/fixtures/pretty.sql
+++ b/tests/fixtures/pretty.sql
@@ -346,7 +346,7 @@ SELECT
     fruit,
     basket_index
 FROM table_data
-CROSS JOIN UNNEST(fruit_basket) AS fruit WITH OFFSET basket_index;
+CROSS JOIN UNNEST(fruit_basket) WITH ORDINALITY AS fruit(basket_index);
 WITH table_data AS (
   SELECT
     'bob' AS name,
@@ -357,11 +357,12 @@ SELECT
   fruit,
   basket_index
 FROM table_data
-CROSS JOIN UNNEST(fruit_basket) AS fruit WITH OFFSET AS basket_index;
+CROSS JOIN UNNEST(fruit_basket) WITH ORDINALITY AS fruit(basket_index);
 SELECT A.* EXCEPT A.COL_1, A.COL_2 FROM TABLE_1 A;
 SELECT
   A.*
-  EXCEPT (A.COL_1, A.COL_2)
+  EXCEPT (A.COL_1),
+  A.COL_2
 FROM TABLE_1 AS A;
 
 SELECT *