Merging upstream version 25.32.0.

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

14 files changed, 161 insertions, 58 deletions

diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index f881385..366cade 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -1640,6 +1640,11 @@ WHERE
             },
         )
 
+        self.validate_identity(
+            "SELECT * FROM ML.FEATURES_AT_TIME(TABLE mydataset.feature_table, time => '2022-06-11 10:00:00+00', num_rows => 1, ignore_feature_nulls => TRUE)"
+        )
+        self.validate_identity("SELECT * FROM ML.FEATURES_AT_TIME((SELECT 1), num_rows => 1)")
+
     def test_errors(self):
         with self.assertRaises(TokenError):
             transpile("'\\'", read="bigquery")
@@ -2145,27 +2150,37 @@ OPTIONS (
                     },
                 )
 
-            self.validate_all(
-                f"""SELECT {func}('{{"name": "Jakob", "age": "6"}}', '$.age')""",
-                write={
-                    "bigquery": f"""SELECT {func}('{{"name": "Jakob", "age": "6"}}', '$.age')""",
-                    "duckdb": """SELECT '{"name": "Jakob", "age": "6"}' ->> '$.age'""",
-                    "snowflake": """SELECT JSON_EXTRACT_PATH_TEXT('{"name": "Jakob", "age": "6"}', 'age')""",
-                },
-            )
+                sql = f"""SELECT {func}('{{"name": "Jakob", "age": "6"}}', '$.age')"""
+                self.validate_all(
+                    sql,
+                    write={
+                        "bigquery": sql,
+                        "duckdb": """SELECT '{"name": "Jakob", "age": "6"}' ->> '$.age'""",
+                        "snowflake": """SELECT JSON_EXTRACT_PATH_TEXT('{"name": "Jakob", "age": "6"}', 'age')""",
+                    },
+                )
+
+                self.assertEqual(
+                    self.parse_one(sql).sql("bigquery", normalize_functions="upper"), sql
+                )
 
     def test_json_extract_array(self):
         for func in ("JSON_QUERY_ARRAY", "JSON_EXTRACT_ARRAY"):
             with self.subTest(f"Testing BigQuery's {func}"):
+                sql = f"""SELECT {func}('{{"fruits": [1, "oranges"]}}', '$.fruits')"""
                 self.validate_all(
-                    f"""SELECT {func}('{{"fruits": [1, "oranges"]}}', '$.fruits')""",
+                    sql,
                     write={
-                        "bigquery": f"""SELECT {func}('{{"fruits": [1, "oranges"]}}', '$.fruits')""",
+                        "bigquery": sql,
                         "duckdb": """SELECT CAST('{"fruits": [1, "oranges"]}' -> '$.fruits' AS JSON[])""",
                         "snowflake": """SELECT TRANSFORM(GET_PATH(PARSE_JSON('{"fruits": [1, "oranges"]}'), 'fruits'), x -> PARSE_JSON(TO_JSON(x)))""",
                     },
                 )
 
+                self.assertEqual(
+                    self.parse_one(sql).sql("bigquery", normalize_functions="upper"), sql
+                )
+
     def test_unix_seconds(self):
         self.validate_all(
             "SELECT UNIX_SECONDS('2008-12-25 15:30:00+00')",
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index f0711fc..c1aa054 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -2854,6 +2854,13 @@ FROM subquery2""",
             },
         )
 
+        self.validate_all(
+            "SELECT ARRAY_LENGTH(GENERATE_DATE_ARRAY(DATE '2020-01-01', DATE '2020-02-01', INTERVAL 1 WEEK))",
+            write={
+                "snowflake": "SELECT ARRAY_SIZE((SELECT ARRAY_AGG(*) FROM (SELECT DATEADD(WEEK, CAST(value AS INT), CAST('2020-01-01' AS DATE)) AS value FROM TABLE(FLATTEN(INPUT => ARRAY_GENERATE_RANGE(0, (DATEDIFF(WEEK, CAST('2020-01-01' AS DATE), CAST('2020-02-01' AS DATE)) + 1 - 1) + 1))) AS _u(seq, key, path, index, value, this))))",
+            },
+        )
+
     def test_set_operation_specifiers(self):
         self.validate_all(
             "SELECT 1 EXCEPT ALL SELECT 1",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 6007e99..13caf1b 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -380,10 +380,6 @@ class TestDuckDB(Validator):
             "x ->> '$.family'",
         )
         self.validate_identity(
-            "ATTACH DATABASE ':memory:' AS new_database", check_command_warning=True
-        )
-        self.validate_identity("DETACH DATABASE new_database", check_command_warning=True)
-        self.validate_identity(
             "SELECT {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'}"
         )
         self.validate_identity(
@@ -1392,3 +1388,20 @@ class TestDuckDB(Validator):
             else:
                 self.assertEqual(ignore_null.sql("duckdb"), func.sql("duckdb"))
                 self.assertNotIn("IGNORE NULLS", windowed_ignore_null.sql("duckdb"))
+
+    def test_attach_detach(self):
+        # ATTACH
+        self.validate_identity("ATTACH 'file.db'")
+        self.validate_identity("ATTACH ':memory:' AS db_alias")
+        self.validate_identity("ATTACH IF NOT EXISTS 'file.db' AS db_alias")
+        self.validate_identity("ATTACH 'file.db' AS db_alias (READ_ONLY)")
+        self.validate_identity("ATTACH 'file.db' (READ_ONLY FALSE, TYPE sqlite)")
+        self.validate_identity("ATTACH 'file.db' (TYPE POSTGRES, SCHEMA 'public')")
+
+        self.validate_identity("ATTACH DATABASE 'file.db'", "ATTACH 'file.db'")
+
+        # DETACH
+        self.validate_identity("DETACH new_database")
+        self.validate_identity("DETACH IF EXISTS file")
+
+        self.validate_identity("DETACH DATABASE db", "DETACH db")
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index d7aef4f..fd6b36f 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -708,6 +708,16 @@ class TestMySQL(Validator):
         )
 
     def test_mysql(self):
+        for func in ("CHAR_LENGTH", "CHARACTER_LENGTH"):
+            with self.subTest(f"Testing MySQL's {func}"):
+                self.validate_all(
+                    f"SELECT {func}('foo')",
+                    write={
+                        "duckdb": "SELECT LENGTH('foo')",
+                        "mysql": "SELECT CHAR_LENGTH('foo')",
+                    },
+                )
+
         self.validate_all(
             "SELECT CONCAT('11', '22')",
             read={
@@ -1319,3 +1329,6 @@ COMMENT='客户账户表'"""
         expression = self.parse_one("EXPLAIN ANALYZE SELECT * FROM t")
         self.assertIsInstance(expression, exp.Describe)
         self.assertEqual(expression.text("style"), "ANALYZE")
+
+        for format in ("JSON", "TRADITIONAL", "TREE"):
+            self.validate_identity(f"DESCRIBE FORMAT={format} UPDATE test SET test_col = 'abc'")
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 0784810..2bb7a2d 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -287,6 +287,17 @@ class TestOracle(Validator):
                 "clickhouse": "TRIM(BOTH 'h' FROM 'Hello World')",
             },
         )
+        self.validate_identity(
+            "SELECT /*+ ORDERED */* FROM tbl", "SELECT /*+ ORDERED */ * FROM tbl"
+        )
+        self.validate_identity(
+            "SELECT /* test */ /*+ ORDERED */* FROM tbl",
+            "/* test */ SELECT /*+ ORDERED */ * FROM tbl",
+        )
+        self.validate_identity(
+            "SELECT /*+ ORDERED */*/* test */ FROM tbl",
+            "SELECT /*+ ORDERED */ * /* test */ FROM tbl",
+        )
 
     def test_join_marker(self):
         self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y")
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index ffe08c6..8f84d9f 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -128,10 +128,6 @@ class TestPostgres(Validator):
             "ORDER BY 2, 3"
         )
         self.validate_identity(
-            "/*+ some comment*/ SELECT b.foo, b.bar FROM baz AS b",
-            "/* + some comment */ SELECT b.foo, b.bar FROM baz AS b",
-        )
-        self.validate_identity(
             "SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]",
             "SELECT ARRAY[1, 2] @> ARRAY[1, 2, 3]",
         )
@@ -819,6 +815,11 @@ class TestPostgres(Validator):
             },
         )
 
+        self.validate_identity(
+            "/*+ some comment*/ SELECT b.foo, b.bar FROM baz AS b",
+            "/* + some comment */ SELECT b.foo, b.bar FROM baz AS b",
+        )
+
     def test_ddl(self):
         # Checks that user-defined types are parsed into DataType instead of Identifier
         self.parse_one("CREATE TABLE t (a udt)").this.expressions[0].args["kind"].assert_is(
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 31a078c..c087089 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -7,6 +7,12 @@ class TestPresto(Validator):
     dialect = "presto"
 
     def test_cast(self):
+        self.validate_identity("DEALLOCATE PREPARE my_query", check_command_warning=True)
+        self.validate_identity("DESCRIBE INPUT x", check_command_warning=True)
+        self.validate_identity("DESCRIBE OUTPUT x", check_command_warning=True)
+        self.validate_identity(
+            "RESET SESSION hive.optimized_reader_enabled", check_command_warning=True
+        )
         self.validate_identity("SELECT * FROM x qualify", "SELECT * FROM x AS qualify")
         self.validate_identity("CAST(x AS IPADDRESS)")
         self.validate_identity("CAST(x AS IPPREFIX)")
@@ -722,7 +728,7 @@ class TestPresto(Validator):
             "SELECT MIN_BY(a.id, a.timestamp, 3) FROM a",
             write={
                 "clickhouse": "SELECT argMin(a.id, a.timestamp) FROM a",
-                "duckdb": "SELECT ARG_MIN(a.id, a.timestamp) FROM a",
+                "duckdb": "SELECT ARG_MIN(a.id, a.timestamp, 3) FROM a",
                 "presto": "SELECT MIN_BY(a.id, a.timestamp, 3) FROM a",
                 "snowflake": "SELECT MIN_BY(a.id, a.timestamp, 3) FROM a",
                 "spark": "SELECT MIN_BY(a.id, a.timestamp) FROM a",
diff --git a/tests/dialects/test_risingwave.py b/tests/dialects/test_risingwave.py
index 7d6d50c..80c5265 100644
--- a/tests/dialects/test_risingwave.py
+++ b/tests/dialects/test_risingwave.py
@@ -12,3 +12,12 @@ class TestRisingWave(Validator):
                 "": "SELECT a FROM tbl FOR UPDATE",
             },
         )
+        self.validate_identity(
+            "CREATE SOURCE from_kafka (*, gen_i32_field INT AS int32_field + 2, gen_i64_field INT AS int64_field + 2, WATERMARK FOR time_col AS time_col - INTERVAL '5 SECOND') INCLUDE header foo VARCHAR AS myheader INCLUDE key AS mykey WITH (connector='kafka', topic='my_topic') FORMAT PLAIN ENCODE PROTOBUF (A=1, B=2) KEY ENCODE PROTOBUF (A=3, B=4)"
+        )
+        self.validate_identity(
+            "CREATE SINK my_sink AS SELECT * FROM A WITH (connector='kafka', topic='my_topic') FORMAT PLAIN ENCODE PROTOBUF (A=1, B=2) KEY ENCODE PROTOBUF (A=3, B=4)"
+        )
+        self.validate_identity(
+            "WITH t1 AS MATERIALIZED (SELECT 1), t2 AS NOT MATERIALIZED (SELECT 2) SELECT * FROM t1, t2"
+        )
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 515a07c..8058bcf 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -1479,13 +1479,20 @@ WHERE
                 "snowflake": "CREATE OR REPLACE TRANSIENT TABLE a (id INT)",
             },
         )
-
         self.validate_all(
             "CREATE TABLE a (b INT)",
             read={"teradata": "CREATE MULTISET TABLE a (b INT)"},
             write={"snowflake": "CREATE TABLE a (b INT)"},
         )
 
+        self.validate_identity("CREATE TABLE a TAG (key1='value_1', key2='value_2')")
+        self.validate_all(
+            "CREATE TABLE a TAG (key1='value_1')",
+            read={
+                "snowflake": "CREATE TABLE a WITH TAG (key1='value_1')",
+            },
+        )
+
         for action in ("SET", "DROP"):
             with self.subTest(f"ALTER COLUMN {action} NOT NULL"):
                 self.validate_all(
@@ -2250,3 +2257,13 @@ SINGLE = TRUE""",
         self.validate_identity(
             "GRANT ALL PRIVILEGES ON FUNCTION mydb.myschema.ADD5(number) TO ROLE analyst"
         )
+
+    def test_window_function_arg(self):
+        query = "SELECT * FROM TABLE(db.schema.FUNC(a) OVER ())"
+
+        ast = self.parse_one(query)
+        window = ast.find(exp.Window)
+
+        self.assertEqual(ast.sql("snowflake"), query)
+        self.assertEqual(len(list(ast.find_all(exp.Column))), 1)
+        self.assertEqual(window.this.sql("snowflake"), "db.schema.FUNC(a)")
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index e4bd9a7..094e5f2 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -1308,6 +1308,12 @@ WHERE
             },
         )
 
+        for fmt in ("WEEK", "WW", "WK"):
+            self.validate_identity(
+                f"SELECT DATEPART({fmt}, '2024-11-21')",
+                "SELECT DATEPART(WK, CAST('2024-11-21' AS DATETIME2))",
+            )
+
     def test_convert(self):
         self.validate_all(
             "CONVERT(NVARCHAR(200), x)",
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 0e13a64..67145b1 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -883,3 +883,5 @@ GRANT SELECT, INSERT ON FUNCTION tbl TO user
 GRANT SELECT ON orders TO ROLE PUBLIC
 GRANT SELECT ON nation TO alice WITH GRANT OPTION
 GRANT DELETE ON SCHEMA finance TO bob
+SELECT attach
+SELECT detach
\ No newline at end of file
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index 66c6c95..3610f16 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -2,7 +2,7 @@ SELECT w.d + w.e AS c FROM w AS w;
 SELECT CONCAT("w"."d", "w"."e") AS "c" FROM "w" AS "w";
 
 SELECT CAST(w.d AS DATE) > w.e AS a FROM w AS w;
-SELECT CAST("w"."d" AS DATE) > CAST("w"."e" AS DATETIME) AS "a" FROM "w" AS "w";
+SELECT CAST("w"."d" AS DATE) > CAST("w"."e" AS DATE) AS "a" FROM "w" AS "w";
 
 SELECT CAST(1 AS VARCHAR) AS a FROM w AS w;
 SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w";
@@ -102,7 +102,7 @@ DATEDIFF('2023-01-01', '2023-01-02', DAY);
 DATEDIFF(CAST('2023-01-01' AS DATETIME), CAST('2023-01-02' AS DATETIME), DAY);
 
 SELECT "t"."d" > '2023-01-01' AS "d" FROM "temporal" AS "t";
-SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t";
+SELECT "t"."d" > CAST('2023-01-01' AS DATE) AS "d" FROM "temporal" AS "t";
 
 SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t";
 SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t";
@@ -110,6 +110,17 @@ SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t";
 SELECT "t"."t" > '2023-01-01 00:00:01' AS "t" FROM "temporal" AS "t";
 SELECT "t"."t" > CAST('2023-01-01 00:00:01' AS DATETIME) AS "t" FROM "temporal" AS "t";
 
+WITH "t" AS (SELECT CAST("ext"."created_at" AS TIMESTAMP) AS "created_at" FROM "ext" AS "ext") SELECT "t"."created_at" > '2024-10-01 12:05:02' AS "col" FROM "t" AS "t";
+WITH "t" AS (SELECT CAST("ext"."created_at" AS TIMESTAMP) AS "created_at" FROM "ext" AS "ext") SELECT "t"."created_at" > CAST('2024-10-01 12:05:02' AS TIMESTAMP) AS "col" FROM "t" AS "t";
+
+# dialect: mysql
+SELECT `t`.`d` < '2023-01-01 00:00:01' AS `col` FROM `temporal` AS `t`;
+SELECT CAST(`t`.`d` AS DATETIME) < CAST('2023-01-01 00:00:01' AS DATETIME) AS `col` FROM `temporal` AS `t`;
+
+# dialect: mysql
+SELECT CAST(`t`.`some_col` AS DATE) < CAST(`t`.`other_col` AS CHAR) AS `col` FROM `other_table` AS `t`;
+SELECT CAST(CAST(`t`.`some_col` AS DATE) AS DATETIME) < CAST(CAST(`t`.`other_col` AS CHAR) AS DATETIME) AS `col` FROM `other_table` AS `t`;
+
 --------------------------------------
 -- Remove redundant casts
 --------------------------------------
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 290d276..59bc432 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -736,8 +736,8 @@ WITH "salesreturns" AS (
     "date_dim"."d_date" AS "d_date"
   FROM "date_dim" AS "date_dim"
   WHERE
-    CAST("date_dim"."d_date" AS DATETIME) <= CAST('2002-09-05' AS DATE)
-    AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2002-08-22' AS DATE)
+    CAST("date_dim"."d_date" AS DATE) <= CAST('2002-09-05' AS DATE)
+    AND CAST("date_dim"."d_date" AS DATE) >= CAST('2002-08-22' AS DATE)
 ), "ssr" AS (
   SELECT
     "store"."s_store_id" AS "s_store_id",
@@ -1853,8 +1853,8 @@ SELECT
 FROM "web_sales" AS "web_sales"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
-  AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-06-10' AS DATE)
-  AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-05-11' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-10' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-11' AS DATE)
 JOIN "item" AS "item"
   ON "item"."i_category" IN ('Home', 'Men', 'Women')
   AND "item"."i_item_sk" = "web_sales"."ws_item_sk"
@@ -2422,7 +2422,7 @@ JOIN "date_dim" AS "date_dim"
   AND "date_dim"."d_date" >= '2002-3-01'
   AND (
     CAST('2002-3-01' AS DATE) + INTERVAL '60' DAY
-  ) >= CAST("date_dim"."d_date" AS DATETIME)
+  ) >= CAST("date_dim"."d_date" AS DATE)
 WHERE
   "_u_3"."_u_4" IS NULL
   AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "cs1"."cs_warehouse_sk" <> "_x")
@@ -2731,8 +2731,8 @@ SELECT
 FROM "catalog_sales" AS "catalog_sales"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
-  AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2001-03-05' AS DATE)
-  AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2001-02-03' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) <= CAST('2001-03-05' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) >= CAST('2001-02-03' AS DATE)
 JOIN "item" AS "item"
   ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   AND "item"."i_category" IN ('Children', 'Women', 'Electronics')
@@ -2811,8 +2811,8 @@ WITH "x" AS (
   FROM "inventory" AS "inventory"
   JOIN "date_dim" AS "date_dim"
     ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
-    AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-06-12' AS DATE)
-    AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-04-13' AS DATE)
+    AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-12' AS DATE)
+    AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-04-13' AS DATE)
   JOIN "item" AS "item"
     ON "inventory"."inv_item_sk" = "item"."i_item_sk"
     AND "item"."i_current_price" <= 1.49
@@ -3944,7 +3944,7 @@ WITH "catalog_sales_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_date" >= '2001-03-04'
-    AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2001-06-02' AS DATE)
+    AND CAST("date_dim"."d_date" AS DATE) <= CAST('2001-06-02' AS DATE)
 ), "_u_0" AS (
   SELECT
     1.3 * AVG("catalog_sales"."cs_ext_discount_amt") AS "_col_0",
@@ -4510,8 +4510,8 @@ JOIN "inventory" AS "inventory"
   AND "inventory"."inv_quantity_on_hand" >= 100
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
-  AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('1999-05-05' AS DATE)
-  AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('1999-03-06' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) <= CAST('1999-05-05' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) >= CAST('1999-03-06' AS DATE)
 WHERE
   "item"."i_current_price" <= 50
   AND "item"."i_current_price" >= 20
@@ -4787,8 +4787,8 @@ LEFT JOIN "catalog_returns" AS "catalog_returns"
   AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
-  AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2002-07-01' AS DATE)
-  AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2002-05-02' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) <= CAST('2002-07-01' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) >= CAST('2002-05-02' AS DATE)
 JOIN "item" AS "item"
   ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   AND "item"."i_current_price" <= 1.49
@@ -10318,8 +10318,8 @@ WITH "date_dim_2" AS (
     "date_dim"."d_date" AS "d_date"
   FROM "date_dim" AS "date_dim"
   WHERE
-    CAST("date_dim"."d_date" AS DATETIME) <= CAST('2001-09-15' AS DATE)
-    AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2001-08-16' AS DATE)
+    CAST("date_dim"."d_date" AS DATE) <= CAST('2001-09-15' AS DATE)
+    AND CAST("date_dim"."d_date" AS DATE) >= CAST('2001-08-16' AS DATE)
 ), "store_2" AS (
   SELECT
     "store"."s_store_sk" AS "s_store_sk"
@@ -10828,8 +10828,8 @@ WITH "date_dim_2" AS (
     "date_dim"."d_date" AS "d_date"
   FROM "date_dim" AS "date_dim"
   WHERE
-    CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-09-25' AS DATE)
-    AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-08-26' AS DATE)
+    CAST("date_dim"."d_date" AS DATE) <= CAST('2000-09-25' AS DATE)
+    AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-08-26' AS DATE)
 ), "item_2" AS (
   SELECT
     "item"."i_item_sk" AS "i_item_sk",
@@ -11109,8 +11109,8 @@ JOIN "store_sales" AS "store_sales"
   ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
-  AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('1998-06-26' AS DATE)
-  AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('1998-04-27' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) <= CAST('1998-06-26' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) >= CAST('1998-04-27' AS DATE)
 WHERE
   "item"."i_current_price" <= 93
   AND "item"."i_current_price" >= 63
@@ -12180,7 +12180,7 @@ WITH "web_sales_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_date" >= '2002-03-29'
-    AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2002-06-27' AS DATE)
+    AND CAST("date_dim"."d_date" AS DATE) <= CAST('2002-06-27' AS DATE)
 ), "_u_0" AS (
   SELECT
     1.3 * AVG("web_sales"."ws_ext_discount_amt") AS "_col_0",
@@ -12321,7 +12321,7 @@ JOIN "date_dim" AS "date_dim"
   AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk"
   AND (
     CAST('2000-3-01' AS DATE) + INTERVAL '60' DAY
-  ) >= CAST("date_dim"."d_date" AS DATETIME)
+  ) >= CAST("date_dim"."d_date" AS DATE)
 JOIN "web_site" AS "web_site"
   ON "web_site"."web_company_name" = 'pri'
   AND "web_site"."web_site_sk" = "ws1"."ws_web_site_sk"
@@ -12411,7 +12411,7 @@ JOIN "date_dim" AS "date_dim"
   AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk"
   AND (
     CAST('2000-4-01' AS DATE) + INTERVAL '60' DAY
-  ) >= CAST("date_dim"."d_date" AS DATETIME)
+  ) >= CAST("date_dim"."d_date" AS DATE)
 JOIN "web_site" AS "web_site"
   ON "web_site"."web_company_name" = 'pri'
   AND "web_site"."web_site_sk" = "ws1"."ws_web_site_sk"
@@ -12595,8 +12595,8 @@ SELECT
 FROM "store_sales" AS "store_sales"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
-  AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-06-17' AS DATE)
-  AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-05-18' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-17' AS DATE)
+  AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-18' AS DATE)
 JOIN "item" AS "item"
   ON "item"."i_category" IN ('Men', 'Home', 'Electronics')
   AND "item"."i_item_sk" = "store_sales"."ss_item_sk"
diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py
index 0fa4ff6..4a41e4a 100644
--- a/tests/test_optimizer.py
+++ b/tests/test_optimizer.py
@@ -132,7 +132,6 @@ class TestOptimizer(unittest.TestCase):
         func,
         pretty=False,
         execute=False,
-        set_dialect=False,
         only=None,
         **kwargs,
     ):
@@ -158,7 +157,7 @@ class TestOptimizer(unittest.TestCase):
                         validate_qualify_columns
                     )
 
-                if set_dialect and dialect:
+                if dialect:
                     func_kwargs["dialect"] = dialect
 
                 future = pool.submit(parse_and_optimize, func, sql, dialect, **func_kwargs)
@@ -207,7 +206,6 @@ class TestOptimizer(unittest.TestCase):
             pretty=True,
             execute=True,
             schema=schema,
-            set_dialect=True,
         )
 
     def test_isolate_table_selects(self):
@@ -235,7 +233,6 @@ class TestOptimizer(unittest.TestCase):
             optimizer.qualify_tables.qualify_tables,
             db="db",
             catalog="c",
-            set_dialect=True,
         )
 
     def test_normalize(self):
@@ -446,11 +443,8 @@ class TestOptimizer(unittest.TestCase):
             qualify_columns,
             execute=True,
             schema=self.schema,
-            set_dialect=True,
-        )
-        self.check_file(
-            "qualify_columns_ddl", qualify_columns, schema=self.schema, set_dialect=True
         )
+        self.check_file("qualify_columns_ddl", qualify_columns, schema=self.schema)
 
     def test_qualify_columns__with_invisible(self):
         schema = MappingSchema(self.schema, {"x": {"a"}, "y": {"b"}, "z": {"b"}})
@@ -475,7 +469,6 @@ class TestOptimizer(unittest.TestCase):
         self.check_file(
             "normalize_identifiers",
             optimizer.normalize_identifiers.normalize_identifiers,
-            set_dialect=True,
         )
 
         self.assertEqual(optimizer.normalize_identifiers.normalize_identifiers("a%").sql(), '"a%"')
@@ -484,14 +477,13 @@ class TestOptimizer(unittest.TestCase):
         self.check_file(
             "quote_identifiers",
             optimizer.qualify_columns.quote_identifiers,
-            set_dialect=True,
         )
 
     def test_pushdown_projection(self):
         self.check_file("pushdown_projections", pushdown_projections, schema=self.schema)
 
     def test_simplify(self):
-        self.check_file("simplify", simplify, set_dialect=True)
+        self.check_file("simplify", simplify)
 
         expression = parse_one("SELECT a, c, b FROM table1 WHERE 1 = 1")
         self.assertEqual(simplify(simplify(expression.find(exp.Where))).sql(), "WHERE TRUE")