Merging upstream version 10.4.2.

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

27 files changed, 480 insertions, 109 deletions

diff --git a/tests/dataframe/unit/test_column.py b/tests/dataframe/unit/test_column.py
index da18502..665cc91 100644
--- a/tests/dataframe/unit/test_column.py
+++ b/tests/dataframe/unit/test_column.py
@@ -150,8 +150,8 @@ class TestDataframeColumn(unittest.TestCase):
             F.col("cola").between(datetime.date(2022, 1, 1), datetime.date(2022, 3, 1)).sql(),
         )
         self.assertEqual(
-            "cola BETWEEN CAST('2022-01-01 01:01:01.000000' AS TIMESTAMP) "
-            "AND CAST('2022-03-01 01:01:01.000000' AS TIMESTAMP)",
+            "cola BETWEEN CAST('2022-01-01T01:01:01+00:00' AS TIMESTAMP) "
+            "AND CAST('2022-03-01T01:01:01+00:00' AS TIMESTAMP)",
             F.col("cola")
             .between(datetime.datetime(2022, 1, 1, 1, 1, 1), datetime.datetime(2022, 3, 1, 1, 1, 1))
             .sql(),
diff --git a/tests/dataframe/unit/test_functions.py b/tests/dataframe/unit/test_functions.py
index 99b140d..37ea2e1 100644
--- a/tests/dataframe/unit/test_functions.py
+++ b/tests/dataframe/unit/test_functions.py
@@ -30,7 +30,7 @@ class TestFunctions(unittest.TestCase):
         test_date = SF.lit(datetime.date(2022, 1, 1))
         self.assertEqual("TO_DATE('2022-01-01')", test_date.sql())
         test_datetime = SF.lit(datetime.datetime(2022, 1, 1, 1, 1, 1))
-        self.assertEqual("CAST('2022-01-01 01:01:01.000000' AS TIMESTAMP)", test_datetime.sql())
+        self.assertEqual("CAST('2022-01-01T01:01:01+00:00' AS TIMESTAMP)", test_datetime.sql())
         test_dict = SF.lit({"cola": 1, "colb": "test"})
         self.assertEqual("STRUCT(1 AS cola, 'test' AS colb)", test_dict.sql())
 
@@ -52,7 +52,7 @@ class TestFunctions(unittest.TestCase):
         test_date = SF.col(datetime.date(2022, 1, 1))
         self.assertEqual("TO_DATE('2022-01-01')", test_date.sql())
         test_datetime = SF.col(datetime.datetime(2022, 1, 1, 1, 1, 1))
-        self.assertEqual("CAST('2022-01-01 01:01:01.000000' AS TIMESTAMP)", test_datetime.sql())
+        self.assertEqual("CAST('2022-01-01T01:01:01+00:00' AS TIMESTAMP)", test_datetime.sql())
         test_dict = SF.col({"cola": 1, "colb": "test"})
         self.assertEqual("STRUCT(1 AS cola, 'test' AS colb)", test_dict.sql())
 
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 1d60ec6..258e47f 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -318,3 +318,9 @@ class TestBigQuery(Validator):
         self.validate_identity(
             "CREATE TABLE FUNCTION a(x INT64) RETURNS TABLE <q STRING, r INT64> AS SELECT s, t"
         )
+
+    def test_group_concat(self):
+        self.validate_all(
+            "SELECT a, GROUP_CONCAT(b) FROM table GROUP BY a",
+            write={"bigquery": "SELECT a, STRING_AGG(b) FROM table GROUP BY a"},
+        )
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index 2168f55..7560d61 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -12,6 +12,76 @@ class TestDatabricks(Validator):
                 "databricks": "SELECT DATEDIFF(year, 'start', 'end')",
             },
         )
+        self.validate_all(
+            "SELECT DATEDIFF(microsecond, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(microsecond, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) * 1000000 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(millisecond, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(millisecond, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) * 1000 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(second, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(second, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(minute, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(minute, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) / 60 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(hour, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(hour, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) / 3600 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(day, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(day, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) / 86400 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(week, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(week, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 48 + EXTRACT(month FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 4 + EXTRACT(day FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) / 7 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(month, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(month, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 12 + EXTRACT(month FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(quarter, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(quarter, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 4 + EXTRACT(month FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) / 3 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(year, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(year, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) AS BIGINT)",
+            },
+        )
 
     def test_add_date(self):
         self.validate_all(
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index ee67bf1..ced7102 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -333,7 +333,7 @@ class TestDialect(Validator):
                 "drill": "CAST('2020-01-01' AS DATE)",
                 "duckdb": "CAST('2020-01-01' AS DATE)",
                 "hive": "TO_DATE('2020-01-01')",
-                "presto": "DATE_PARSE('2020-01-01', '%Y-%m-%d %H:%i:%s')",
+                "presto": "CAST('2020-01-01' AS TIMESTAMP)",
                 "starrocks": "TO_DATE('2020-01-01')",
             },
         )
@@ -343,7 +343,7 @@ class TestDialect(Validator):
                 "drill": "CAST('2020-01-01' AS TIMESTAMP)",
                 "duckdb": "CAST('2020-01-01' AS TIMESTAMP)",
                 "hive": "CAST('2020-01-01' AS TIMESTAMP)",
-                "presto": "DATE_PARSE('2020-01-01', '%Y-%m-%d %H:%i:%s')",
+                "presto": "CAST('2020-01-01' AS TIMESTAMP)",
             },
         )
         self.validate_all(
@@ -723,23 +723,23 @@ class TestDialect(Validator):
             read={
                 "postgres": "x->'y'",
                 "presto": "JSON_EXTRACT(x, 'y')",
-                "starrocks": "x->'y'",
+                "starrocks": "x -> 'y'",
             },
             write={
                 "oracle": "JSON_EXTRACT(x, 'y')",
-                "postgres": "x->'y'",
+                "postgres": "x -> 'y'",
                 "presto": "JSON_EXTRACT(x, 'y')",
-                "starrocks": "x->'y'",
+                "starrocks": "x -> 'y'",
             },
         )
         self.validate_all(
             "JSON_EXTRACT_SCALAR(x, 'y')",
             read={
-                "postgres": "x->>'y'",
+                "postgres": "x ->> 'y'",
                 "presto": "JSON_EXTRACT_SCALAR(x, 'y')",
             },
             write={
-                "postgres": "x->>'y'",
+                "postgres": "x ->> 'y'",
                 "presto": "JSON_EXTRACT_SCALAR(x, 'y')",
             },
         )
@@ -749,7 +749,7 @@ class TestDialect(Validator):
                 "postgres": "x#>'y'",
             },
             write={
-                "postgres": "x#>'y'",
+                "postgres": "x #> 'y'",
             },
         )
         self.validate_all(
@@ -758,7 +758,7 @@ class TestDialect(Validator):
                 "postgres": "x#>>'y'",
             },
             write={
-                "postgres": "x#>>'y'",
+                "postgres": "x #>> 'y'",
             },
         )
 
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 99b0493..a37062c 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -59,7 +59,7 @@ class TestDuckDB(Validator):
             "TO_TIMESTAMP(x)",
             write={
                 "duckdb": "CAST(x AS TIMESTAMP)",
-                "presto": "DATE_PARSE(x, '%Y-%m-%d %H:%i:%s')",
+                "presto": "CAST(x AS TIMESTAMP)",
                 "hive": "CAST(x AS TIMESTAMP)",
             },
         )
@@ -302,3 +302,20 @@ class TestDuckDB(Validator):
                 read="duckdb",
                 unsupported_level=ErrorLevel.IMMEDIATE,
             )
+
+    def test_array(self):
+        self.validate_identity("ARRAY(SELECT id FROM t)")
+
+    def test_cast(self):
+        self.validate_all(
+            "123::CHARACTER VARYING",
+            write={
+                "duckdb": "CAST(123 AS TEXT)",
+            },
+        )
+
+    def test_bool_or(self):
+        self.validate_all(
+            "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
+            write={"duckdb": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
+        )
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 5ac8714..a7f3b8f 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -268,10 +268,10 @@ class TestHive(Validator):
         self.validate_all(
             "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
             write={
-                "duckdb": "STRFTIME('2020-01-01', '%Y-%m-%d %H:%M:%S')",
-                "presto": "DATE_FORMAT('2020-01-01', '%Y-%m-%d %H:%i:%S')",
-                "hive": "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
-                "spark": "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
+                "duckdb": "STRFTIME(CAST('2020-01-01' AS TIMESTAMP), '%Y-%m-%d %H:%M:%S')",
+                "presto": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), '%Y-%m-%d %H:%i:%S')",
+                "hive": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), 'yyyy-MM-dd HH:mm:ss')",
+                "spark": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), 'yyyy-MM-dd HH:mm:ss')",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 5064dbe..7cd686d 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -91,12 +91,12 @@ class TestMySQL(Validator):
             },
         )
         self.validate_all(
-            "N 'some text'",
+            "N'some text'",
             read={
-                "mysql": "N'some text'",
+                "mysql": "n'some text'",
             },
             write={
-                "mysql": "N 'some text'",
+                "mysql": "N'some text'",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 962b28b..1e048d5 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -3,6 +3,7 @@ from tests.dialects.test_dialect import Validator
 
 
 class TestPostgres(Validator):
+    maxDiff = None
     dialect = "postgres"
 
     def test_ddl(self):
@@ -94,6 +95,7 @@ class TestPostgres(Validator):
         self.validate_identity("COMMENT ON TABLE mytable IS 'this'")
         self.validate_identity("SELECT e'\\xDEADBEEF'")
         self.validate_identity("SELECT CAST(e'\\176' AS BYTEA)")
+        self.validate_identity("""SELECT * FROM JSON_TO_RECORDSET(z) AS y("rank" INT)""")
 
         self.validate_all(
             "END WORK AND NO CHAIN",
@@ -112,6 +114,14 @@ class TestPostgres(Validator):
                 "spark": "CREATE TABLE x (a UUID, b BINARY)",
             },
         )
+        self.validate_all(
+            "123::CHARACTER VARYING",
+            write={"postgres": "CAST(123 AS VARCHAR)"},
+        )
+        self.validate_all(
+            "TO_TIMESTAMP(123::DOUBLE PRECISION)",
+            write={"postgres": "TO_TIMESTAMP(CAST(123 AS DOUBLE PRECISION))"},
+        )
 
         self.validate_identity(
             "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
@@ -193,15 +203,21 @@ class TestPostgres(Validator):
             },
         )
         self.validate_all(
-            "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE WHERE pname IS NULL",
-            read={
+            "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) pname ON TRUE WHERE pname IS NULL",
+            write={
                 "postgres": "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE WHERE pname IS NULL",
             },
         )
         self.validate_all(
             "SELECT p1.id, p2.id, v1, v2 FROM polygons AS p1, polygons AS p2, LATERAL VERTICES(p1.poly) v1, LATERAL VERTICES(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id <> p2.id",
-            read={
-                "postgres": "SELECT p1.id, p2.id, v1, v2 FROM polygons p1, polygons p2, LATERAL VERTICES(p1.poly) v1, LATERAL VERTICES(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id",
+            write={
+                "postgres": "SELECT p1.id, p2.id, v1, v2 FROM polygons AS p1, polygons AS p2, LATERAL VERTICES(p1.poly) AS v1, LATERAL VERTICES(p2.poly) AS v2 WHERE (v1 <-> v2) < 10 AND p1.id <> p2.id",
+            },
+        )
+        self.validate_all(
+            "SELECT * FROM r CROSS JOIN LATERAL unnest(array(1)) AS s(location)",
+            write={
+                "postgres": "SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)",
             },
         )
         self.validate_all(
@@ -218,35 +234,46 @@ class TestPostgres(Validator):
         )
         self.validate_all(
             "'[1,2,3]'::json->2",
-            write={"postgres": "CAST('[1,2,3]' AS JSON)->'2'"},
+            write={"postgres": "CAST('[1,2,3]' AS JSON) -> '2'"},
         )
         self.validate_all(
             """'{"a":1,"b":2}'::json->'b'""",
-            write={"postgres": """CAST('{"a":1,"b":2}' AS JSON)->'b'"""},
+            write={"postgres": """CAST('{"a":1,"b":2}' AS JSON) -> 'b'"""},
         )
         self.validate_all(
             """'{"x": {"y": 1}}'::json->'x'->'y'""",
-            write={"postgres": """CAST('{"x": {"y": 1}}' AS JSON)->'x'->'y'"""},
+            write={"postgres": """CAST('{"x": {"y": 1}}' AS JSON) -> 'x' -> 'y'"""},
         )
         self.validate_all(
             """'{"x": {"y": 1}}'::json->'x'::json->'y'""",
-            write={"postgres": """CAST(CAST('{"x": {"y": 1}}' AS JSON)->'x' AS JSON)->'y'"""},
+            write={"postgres": """CAST(CAST('{"x": {"y": 1}}' AS JSON) -> 'x' AS JSON) -> 'y'"""},
         )
         self.validate_all(
             """'[1,2,3]'::json->>2""",
-            write={"postgres": "CAST('[1,2,3]' AS JSON)->>'2'"},
+            write={"postgres": "CAST('[1,2,3]' AS JSON) ->> '2'"},
         )
         self.validate_all(
             """'{"a":1,"b":2}'::json->>'b'""",
-            write={"postgres": """CAST('{"a":1,"b":2}' AS JSON)->>'b'"""},
+            write={"postgres": """CAST('{"a":1,"b":2}' AS JSON) ->> 'b'"""},
         )
         self.validate_all(
             """'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'""",
-            write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON)#>'{a,2}'"""},
+            write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON) #> '{a,2}'"""},
         )
         self.validate_all(
             """'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'""",
-            write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON)#>>'{a,2}'"""},
+            write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON) #>> '{a,2}'"""},
+        )
+        self.validate_all(
+            """SELECT JSON_ARRAY_ELEMENTS((foo->'sections')::JSON) AS sections""",
+            write={
+                "postgres": """SELECT JSON_ARRAY_ELEMENTS(CAST((foo -> 'sections') AS JSON)) AS sections""",
+                "presto": """SELECT JSON_ARRAY_ELEMENTS(CAST((JSON_EXTRACT(foo, 'sections')) AS JSON)) AS sections""",
+            },
+        )
+        self.validate_all(
+            """x ? 'x'""",
+            write={"postgres": "x ? 'x'"},
         )
         self.validate_all(
             "SELECT $$a$$",
@@ -260,3 +287,49 @@ class TestPostgres(Validator):
             "UPDATE MYTABLE T1 SET T1.COL = 13",
             write={"postgres": "UPDATE MYTABLE AS T1 SET T1.COL = 13"},
         )
+
+        self.validate_identity("x ~ 'y'")
+        self.validate_identity("x ~* 'y'")
+        self.validate_all(
+            "x !~ 'y'",
+            write={"postgres": "NOT x ~ 'y'"},
+        )
+        self.validate_all(
+            "x !~* 'y'",
+            write={"postgres": "NOT x ~* 'y'"},
+        )
+
+        self.validate_all(
+            "x ~~ 'y'",
+            write={"postgres": "x LIKE 'y'"},
+        )
+        self.validate_all(
+            "x ~~* 'y'",
+            write={"postgres": "x ILIKE 'y'"},
+        )
+        self.validate_all(
+            "x !~~ 'y'",
+            write={"postgres": "NOT x LIKE 'y'"},
+        )
+        self.validate_all(
+            "x !~~* 'y'",
+            write={"postgres": "NOT x ILIKE 'y'"},
+        )
+        self.validate_all(
+            "'45 days'::interval day",
+            write={"postgres": "CAST('45 days' AS INTERVAL day)"},
+        )
+        self.validate_all(
+            "'x' 'y' 'z'",
+            write={"postgres": "CONCAT('x', 'y', 'z')"},
+        )
+        self.validate_identity("SELECT ARRAY(SELECT 1)")
+
+        self.validate_all(
+            "x::cstring",
+            write={"postgres": "CAST(x AS CSTRING)"},
+        )
+
+        self.validate_identity(
+            "SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
+        )
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 3034df5..f650c98 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -53,7 +53,7 @@ class TestRedshift(Validator):
         self.validate_all(
             "SELECT DISTINCT ON (a) a, b FROM x ORDER BY c DESC",
             write={
-                "redshift": 'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS "_row_number" FROM x) WHERE "_row_number" = 1',
+                "redshift": 'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) WHERE "_row_number" = 1',
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index e3d0cff..df62c6c 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -7,6 +7,12 @@ class TestSnowflake(Validator):
 
     def test_snowflake(self):
         self.validate_all(
+            "SELECT * FROM xxx WHERE col ilike '%Don''t%'",
+            write={
+                "snowflake": "SELECT * FROM xxx WHERE col ILIKE '%Don\\'t%'",
+            },
+        )
+        self.validate_all(
             'x:a:"b c"',
             write={
                 "duckdb": "x['a']['b c']",
@@ -509,3 +515,11 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS f, LATERA
                 "snowflake": "SELECT 1 MINUS SELECT 1",
             },
         )
+
+    def test_values(self):
+        self.validate_all(
+            'SELECT c0, c1 FROM (VALUES (1, 2), (3, 4)) AS "t0"(c0, c1)',
+            read={
+                "spark": "SELECT `c0`, `c1` FROM (VALUES (1, 2), (3, 4)) AS `t0`(`c0`, `c1`)",
+            },
+        )
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 3a9f918..7395e72 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -101,6 +101,18 @@ TBLPROPERTIES (
                 "spark": "CACHE TABLE testCache OPTIONS('storageLevel' = 'DISK_ONLY') AS SELECT * FROM testData"
             },
         )
+        self.validate_all(
+            "ALTER TABLE StudentInfo ADD COLUMNS (LastName STRING, DOB TIMESTAMP)",
+            write={
+                "spark": "ALTER TABLE StudentInfo ADD COLUMNS (LastName STRING, DOB TIMESTAMP)",
+            },
+        )
+        self.validate_all(
+            "ALTER TABLE StudentInfo DROP COLUMNS (LastName, DOB)",
+            write={
+                "spark": "ALTER TABLE StudentInfo DROP COLUMNS (LastName, DOB)",
+            },
+        )
 
     def test_to_date(self):
         self.validate_all(
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index e4c6e60..b4ac094 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -431,11 +431,11 @@ class TestTSQL(Validator):
     def test_string(self):
         self.validate_all(
             "SELECT N'test'",
-            write={"spark": "SELECT 'test'"},
+            write={"spark": "SELECT N'test'"},
         )
         self.validate_all(
             "SELECT n'test'",
-            write={"spark": "SELECT 'test'"},
+            write={"spark": "SELECT N'test'"},
         )
         self.validate_all(
             "SELECT '''test'''",
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index e12b673..e6a6e6b 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -17,6 +17,7 @@ SUM(CASE WHEN x > 1 THEN 1 ELSE 0 END) / y
 '\x'
 "x"
 ""
+N'abc'
 x
 x % 1
 x < 1
@@ -33,6 +34,10 @@ x << 1
 x >> 1
 x >> 1 | 1 & 1 ^ 1
 x || y
+x[ : ]
+x[1 : ]
+x[1 : 2]
+x[-4 : -1]
 1 - -1
 - -5
 dec.x + y
@@ -62,6 +67,8 @@ x BETWEEN 'a' || b AND 'c' || d
 NOT x IS NULL
 x IS TRUE
 x IS FALSE
+x IS TRUE IS TRUE
+x LIKE y IS TRUE
 time
 zone
 ARRAY<TEXT>
@@ -93,10 +100,11 @@ x LIKE '%y%' ESCAPE '\'
 x ILIKE '%y%' ESCAPE '\'
 1 AS escape
 INTERVAL '1' day
-INTERVAL '1' month
+INTERVAL '1' MONTH
 INTERVAL '1 day'
 INTERVAL 2 months
-INTERVAL 1 + 3 days
+INTERVAL 1 + 3 DAYS
+CAST('45' AS INTERVAL DAYS)
 TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), 1, DAY)
 DATETIME_DIFF(CURRENT_DATE, 1, DAY)
 QUANTILE(x, 0.5)
@@ -144,6 +152,7 @@ SELECT 1 AS count FROM test
 SELECT 1 AS comment FROM test
 SELECT 1 AS numeric FROM test
 SELECT 1 AS number FROM test
+SELECT COALESCE(offset, 1)
 SELECT t.count
 SELECT DISTINCT x FROM test
 SELECT DISTINCT x, y FROM test
@@ -196,6 +205,7 @@ SELECT JSON_EXTRACT_SCALAR(x, '$.name')
 SELECT x LIKE '%x%' FROM test
 SELECT * FROM test LIMIT 100
 SELECT * FROM test LIMIT 100 OFFSET 200
+SELECT * FROM test FETCH FIRST ROWS ONLY
 SELECT * FROM test FETCH FIRST 1 ROWS ONLY
 SELECT * FROM test FETCH NEXT 1 ROWS ONLY
 SELECT (1 > 2) AS x FROM test
@@ -460,6 +470,7 @@ CREATE TABLE z (end INT)
 CREATE TABLE z (a ARRAY<TEXT>, b MAP<TEXT, DOUBLE>, c DECIMAL(5, 3))
 CREATE TABLE z (a INT, b VARCHAR COMMENT 'z', c VARCHAR(100) COMMENT 'z', d DECIMAL(5, 3))
 CREATE TABLE z (a INT(11) DEFAULT UUID())
+CREATE TABLE z (n INT DEFAULT 0 NOT NULL)
 CREATE TABLE z (a INT(11) DEFAULT NULL COMMENT '客户id')
 CREATE TABLE z (a INT(11) NOT NULL DEFAULT 1)
 CREATE TABLE z (a INT(11) NOT NULL DEFAULT -1)
@@ -511,7 +522,13 @@ INSERT OVERWRITE TABLE a.b PARTITION(ds) SELECT x FROM y
 INSERT OVERWRITE TABLE a.b PARTITION(ds='YYYY-MM-DD') SELECT x FROM y
 INSERT OVERWRITE TABLE a.b PARTITION(ds, hour) SELECT x FROM y
 INSERT OVERWRITE TABLE a.b PARTITION(ds='YYYY-MM-DD', hour='hh') SELECT x FROM y
+ALTER AGGREGATE bla(foo) OWNER TO CURRENT_USER
+ALTER RULE foo ON bla RENAME TO baz
+ALTER ROLE CURRENT_USER WITH REPLICATION
+ALTER SEQUENCE IF EXISTS baz RESTART WITH boo
 ALTER TYPE electronic_mail RENAME TO email
+ALTER VIEW foo ALTER COLUMN bla SET DEFAULT 'NOT SET'
+ALTER DOMAIN foo VALIDATE CONSTRAINT bla
 ANALYZE a.y
 DELETE FROM x WHERE y > 1
 DELETE FROM y
@@ -596,3 +613,17 @@ SELECT x AS INTO FROM bla
 SELECT * INTO newevent FROM event
 SELECT * INTO TEMPORARY newevent FROM event
 SELECT * INTO UNLOGGED newevent FROM event
+ALTER TABLE integers ADD COLUMN k INT
+ALTER TABLE integers ADD COLUMN IF NOT EXISTS k INT
+ALTER TABLE IF EXISTS integers ADD COLUMN k INT
+ALTER TABLE integers ADD COLUMN l INT DEFAULT 10
+ALTER TABLE measurements ADD COLUMN mtime TIMESTAMPTZ DEFAULT NOW()
+ALTER TABLE integers DROP COLUMN k
+ALTER TABLE integers DROP COLUMN IF EXISTS k
+ALTER TABLE integers DROP COLUMN k CASCADE
+ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR
+ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR USING CONCAT(i, '_', j)
+ALTER TABLE integers ALTER COLUMN i SET DEFAULT 10
+ALTER TABLE integers ALTER COLUMN i DROP DEFAULT
+ALTER TABLE mydataset.mytable DROP COLUMN A, DROP COLUMN IF EXISTS B
+ALTER TABLE mydataset.mytable ADD COLUMN A TEXT, ADD COLUMN IF NOT EXISTS B INT
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index 8880881..8c7cd45 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -1,11 +1,11 @@
 SELECT w.d + w.e AS c FROM w AS w;
-SELECT CONCAT(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 DATE) 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;
+SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w";
 
 SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w;
-SELECT 1 + 3.2 AS a FROM w AS w;
+SELECT 1 + 3.2 AS "a" FROM "w" AS "w";
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a692c7d..b502d81 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -291,3 +291,81 @@ SELECT a1 FROM cte1;
 SELECT
   "x"."a" AS "a1"
 FROM "x" AS "x";
+
+# title: recursive cte
+WITH RECURSIVE cte1 AS (
+  SELECT *
+  FROM (
+      SELECT 1 AS a, 2 AS b
+  ) base
+  CROSS JOIN (SELECT 3 c) y
+  UNION ALL
+  SELECT *
+  FROM cte1
+  WHERE a < 1
+)
+SELECT *
+FROM cte1;
+WITH RECURSIVE "base" AS (
+  SELECT
+    1 AS "a",
+    2 AS "b"
+), "y" AS (
+  SELECT
+    3 AS "c"
+), "cte1" AS (
+  SELECT
+    "base"."a" AS "a",
+    "base"."b" AS "b",
+    "y"."c" AS "c"
+  FROM "base" AS "base"
+  CROSS JOIN "y" AS "y"
+  UNION ALL
+  SELECT
+    "cte1"."a" AS "a",
+    "cte1"."b" AS "b",
+    "cte1"."c" AS "c"
+  FROM "cte1"
+  WHERE
+    "cte1"."a" < 1
+)
+SELECT
+  "cte1"."a" AS "a",
+  "cte1"."b" AS "b",
+  "cte1"."c" AS "c"
+FROM "cte1";
+
+# title: right join should not push down to from
+SELECT x.a, y.b
+FROM x
+RIGHT JOIN y
+ON x.a = y.b
+WHERE x.b = 1;
+SELECT
+  "x"."a" AS "a",
+  "y"."b" AS "b"
+FROM "x" AS "x"
+RIGHT JOIN "y" AS "y"
+  ON "x"."a" = "y"."b"
+WHERE
+  "x"."b" = 1;
+
+# title: right join can push down to itself
+SELECT x.a, y.b
+FROM x
+RIGHT JOIN y
+ON x.a = y.b
+WHERE y.b = 1;
+WITH "y_2" AS (
+  SELECT
+    "y"."b" AS "b"
+  FROM "y" AS "y"
+  WHERE
+    "y"."b" = 1
+)
+SELECT
+  "x"."a" AS "a",
+  "y"."b" AS "b"
+FROM "x" AS "x"
+RIGHT JOIN "y_2" AS "y"
+  ON "x"."a" = "y"."b";
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index ba4bf45..2a21f65 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -1,32 +1,32 @@
 SELECT a FROM (SELECT * FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
 
 SELECT 1 FROM (SELECT * FROM x) WHERE b = 2;
-SELECT 1 AS "_col_0" FROM (SELECT x.b AS b FROM x AS x) AS "_q_0" WHERE "_q_0".b = 2;
+SELECT 1 AS _col_0 FROM (SELECT x.b AS b FROM x AS x) AS _q_0 WHERE _q_0.b = 2;
 
 SELECT (SELECT c FROM y WHERE q.b = y.b) FROM (SELECT * FROM x) AS q;
-SELECT (SELECT y.c AS c FROM y AS y WHERE q.b = y.b) AS "_col_0" FROM (SELECT x.b AS b FROM x AS x) AS q;
+SELECT (SELECT y.c AS c FROM y AS y WHERE q.b = y.b) AS _col_0 FROM (SELECT x.b AS b FROM x AS x) AS q;
 
 SELECT a FROM x JOIN (SELECT b, c FROM y) AS z ON x.b = z.b;
 SELECT x.a AS a FROM x AS x JOIN (SELECT y.b AS b FROM y AS y) AS z ON x.b = z.b;
 
 SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2;
-SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS "_" FROM x AS x) AS x2;
+SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2;
 
 SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2;
-SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS "_" FROM x AS x) AS x2;
+SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2;
 
 SELECT a FROM (SELECT DISTINCT a, b FROM x);
-SELECT "_q_0".a AS a FROM (SELECT DISTINCT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT DISTINCT x.a AS a, x.b AS b FROM x AS x) AS _q_0;
 
 SELECT a FROM (SELECT a, b FROM x UNION ALL SELECT a, b FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x UNION ALL SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION ALL SELECT x.a AS a FROM x AS x) AS _q_0;
 
 WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x UNION ALL SELECT z.b AS b, z.c AS c FROM z) SELECT a, b FROM t1;
 WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x AS x UNION ALL SELECT z.b AS b, z.c AS c FROM z AS z) SELECT t1.a AS a, t1.b AS b FROM t1;
 
 SELECT a FROM (SELECT a, b FROM x UNION SELECT a, b FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x UNION SELECT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x UNION SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0;
 
 WITH y AS (SELECT * FROM x) SELECT a FROM y;
 WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y;
@@ -38,10 +38,10 @@ WITH z AS (SELECT * FROM x) SELECT a FROM z UNION SELECT a FROM z;
 WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z UNION SELECT z.a AS a FROM z;
 
 SELECT b FROM (SELECT a, SUM(b) AS b FROM x GROUP BY a);
-SELECT "_q_0".b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS "_q_0";
+SELECT _q_0.b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS _q_0;
 
 SELECT b FROM (SELECT a, SUM(b) AS b FROM x ORDER BY a);
-SELECT "_q_0".b AS b FROM (SELECT x.a AS a, SUM(x.b) AS b FROM x AS x ORDER BY a) AS "_q_0";
+SELECT _q_0.b AS b FROM (SELECT x.a AS a, SUM(x.b) AS b FROM x AS x ORDER BY a) AS _q_0;
 
 SELECT x FROM (VALUES(1, 2)) AS q(x, y);
 SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y);
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 1176078..9c5a0be 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -21,15 +21,15 @@ SELECT x.a AS b FROM x AS x;
 
 # execute: false
 SELECT 1, 2 FROM x;
-SELECT 1 AS "_col_0", 2 AS "_col_1" FROM x AS x;
+SELECT 1 AS _col_0, 2 AS _col_1 FROM x AS x;
 
 # execute: false
 SELECT a + b FROM x;
-SELECT x.a + x.b AS "_col_0" FROM x AS x;
+SELECT x.a + x.b AS _col_0 FROM x AS x;
 
 # execute: false
 SELECT a, SUM(b) FROM x WHERE a > 1 AND b > 1 GROUP BY a;
-SELECT x.a AS a, SUM(x.b) AS "_col_1" FROM x AS x WHERE x.a > 1 AND x.b > 1 GROUP BY x.a;
+SELECT x.a AS a, SUM(x.b) AS _col_1 FROM x AS x WHERE x.a > 1 AND x.b > 1 GROUP BY x.a;
 
 SELECT SUM(a) AS c FROM x HAVING SUM(a) > 3;
 SELECT SUM(x.a) AS c FROM x AS x HAVING SUM(x.a) > 3;
@@ -59,7 +59,7 @@ SELECT SUM(x.a) AS c, SUM(x.b) AS d FROM x AS x ORDER BY SUM(x.a), SUM(x.b);
 
 # execute: false
 SELECT SUM(a), SUM(b) AS c FROM x ORDER BY 1, 2;
-SELECT SUM(x.a) AS "_col_0", SUM(x.b) AS c FROM x AS x ORDER BY SUM(x.a), SUM(x.b);
+SELECT SUM(x.a) AS _col_0, SUM(x.b) AS c FROM x AS x ORDER BY SUM(x.a), SUM(x.b);
 
 SELECT a AS j, b FROM x GROUP BY j, b;
 SELECT x.a AS j, x.b AS b FROM x AS x GROUP BY x.a, x.b;
@@ -72,7 +72,7 @@ SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a, x.b;
 
 # execute: false
 SELECT DATE(a), DATE(b) AS c FROM x GROUP BY 1, 2;
-SELECT DATE(x.a) AS "_col_0", DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(x.b);
+SELECT DATE(x.a) AS _col_0, DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(x.b);
 
 SELECT SUM(x.a) AS c FROM x JOIN y ON x.b = y.b GROUP BY c;
 SELECT SUM(x.a) AS c FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY y.c;
@@ -130,10 +130,10 @@ SELECT a FROM (SELECT a FROM x AS x) y;
 SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y;
 
 SELECT a FROM (SELECT a AS a FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
 
 SELECT a FROM (SELECT a FROM (SELECT a FROM x));
-SELECT "_q_1".a AS a FROM (SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0") AS "_q_1";
+SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS _q_1;
 
 SELECT x.a FROM x AS x JOIN (SELECT * FROM x) AS y ON x.a = y.a;
 SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a, x.b AS b FROM x AS x) AS y ON x.a = y.a;
@@ -157,7 +157,7 @@ SELECT a FROM x UNION SELECT a FROM x UNION SELECT a FROM x;
 SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x;
 
 SELECT a FROM (SELECT a FROM x UNION SELECT a FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x) AS _q_0;
 
 --------------------------------------
 -- Subqueries
@@ -167,10 +167,10 @@ SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT y.c AS c FROM y AS y);
 
 # execute: false
 SELECT (SELECT c FROM y) FROM x;
-SELECT (SELECT y.c AS c FROM y AS y) AS "_col_0" FROM x AS x;
+SELECT (SELECT y.c AS c FROM y AS y) AS _col_0 FROM x AS x;
 
 SELECT a FROM (SELECT a FROM x) WHERE a IN (SELECT b FROM (SELECT b FROM y));
-SELECT "_q_1".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_1" WHERE "_q_1".a IN (SELECT "_q_0".b AS b FROM (SELECT y.b AS b FROM y AS y) AS "_q_0");
+SELECT _q_1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_1 WHERE _q_1.a IN (SELECT _q_0.b AS b FROM (SELECT y.b AS b FROM y AS y) AS _q_0);
 
 --------------------------------------
 -- Correlated subqueries
@@ -215,10 +215,10 @@ SELECT x.*, y.* FROM x JOIN y ON x.b = y.b;
 SELECT x.a AS a, x.b AS b, y.b AS b, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
 
 SELECT a FROM (SELECT * FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0;
 
 SELECT * FROM (SELECT a FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
 
 --------------------------------------
 -- CTEs
diff --git a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
index ee46c23..05253f3 100644
--- a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
+++ b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
@@ -11,10 +11,10 @@ SELECT x.b AS b FROM x AS x;
 -- Derived tables
 --------------------------------------
 SELECT x.a FROM x AS x JOIN (SELECT * FROM x);
-SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a FROM x AS x) AS _q_0;
 
 SELECT x.b FROM x AS x JOIN (SELECT b FROM x);
-SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS "_q_0";
+SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
 
 --------------------------------------
 -- Expand *
@@ -29,7 +29,7 @@ SELECT * FROM y JOIN z ON y.c = z.c;
 SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.c = z.c;
 
 SELECT a FROM (SELECT * FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
 
 SELECT * FROM (SELECT a FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index dc373a0..a444945 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -30,14 +30,14 @@ CROSS JOIN (
   SELECT
     SUM(y.a) AS a
   FROM y
-) AS "_u_0"
+) AS _u_0
 LEFT JOIN (
   SELECT
     y.a AS a
   FROM y
   GROUP BY
     y.a
-) AS "_u_1"
+) AS _u_1
   ON x.a = "_u_1"."a"
 LEFT JOIN (
   SELECT
@@ -45,7 +45,7 @@ LEFT JOIN (
   FROM y
   GROUP BY
     y.b
-) AS "_u_2"
+) AS _u_2
   ON x.a = "_u_2"."b"
 LEFT JOIN (
   SELECT
@@ -53,7 +53,7 @@ LEFT JOIN (
   FROM y
   GROUP BY
     y.a
-) AS "_u_3"
+) AS _u_3
   ON x.a = "_u_3"."a"
 LEFT JOIN (
   SELECT
@@ -64,8 +64,8 @@ LEFT JOIN (
     TRUE
   GROUP BY
     y.a
-) AS "_u_4"
-  ON x.a = "_u_4"."_u_5"
+) AS _u_4
+  ON x.a = _u_4._u_5
 LEFT JOIN (
   SELECT
     SUM(y.b) AS b,
@@ -75,8 +75,8 @@ LEFT JOIN (
     TRUE
   GROUP BY
     y.a
-) AS "_u_6"
-  ON x.a = "_u_6"."_u_7"
+) AS _u_6
+  ON x.a = _u_6._u_7
 LEFT JOIN (
   SELECT
     y.a AS a
@@ -85,8 +85,8 @@ LEFT JOIN (
     TRUE
   GROUP BY
     y.a
-) AS "_u_8"
-  ON "_u_8".a = x.a
+) AS _u_8
+  ON _u_8.a = x.a
 LEFT JOIN (
   SELECT
     y.a AS a
@@ -95,8 +95,8 @@ LEFT JOIN (
     TRUE
   GROUP BY
     y.a
-) AS "_u_9"
-  ON "_u_9".a = x.a
+) AS _u_9
+  ON _u_9.a = x.a
 LEFT JOIN (
   SELECT
     ARRAY_AGG(y.a) AS a,
@@ -106,8 +106,8 @@ LEFT JOIN (
     TRUE
   GROUP BY
     y.b
-) AS "_u_10"
-  ON "_u_10"."_u_11" = x.a
+) AS _u_10
+  ON _u_10._u_11 = x.a
 LEFT JOIN (
   SELECT
     SUM(y.a) AS a,
@@ -118,8 +118,8 @@ LEFT JOIN (
     TRUE AND TRUE AND TRUE
   GROUP BY
     y.a
-) AS "_u_12"
-  ON "_u_12"."_u_13" = x.a AND "_u_12"."_u_13" = x.b
+) AS _u_12
+  ON _u_12._u_13 = x.a AND _u_12._u_13 = x.b
 LEFT JOIN (
   SELECT
     y.a AS a
@@ -128,38 +128,38 @@ LEFT JOIN (
     TRUE
   GROUP BY
     y.a
-) AS "_u_15"
-  ON x.a = "_u_15".a
+) AS _u_15
+  ON x.a = _u_15.a
 WHERE
-  x.a = "_u_0".a
+  x.a = _u_0.a
   AND NOT "_u_1"."a" IS NULL
   AND NOT "_u_2"."b" IS NULL
   AND NOT "_u_3"."a" IS NULL
   AND (
-    x.a = "_u_4".b AND NOT "_u_4"."_u_5" IS NULL
+    x.a = _u_4.b AND NOT _u_4._u_5 IS NULL
   )
   AND (
-    x.a > "_u_6".b AND NOT "_u_6"."_u_7" IS NULL
+    x.a > _u_6.b AND NOT _u_6._u_7 IS NULL
   )
   AND (
-    None = "_u_8".a AND NOT "_u_8".a IS NULL
+    None = _u_8.a AND NOT _u_8.a IS NULL
   )
   AND NOT (
-    x.a = "_u_9".a AND NOT "_u_9".a IS NULL
+    x.a = _u_9.a AND NOT _u_9.a IS NULL
   )
   AND (
-    ARRAY_ANY("_u_10".a, _x -> _x = x.a) AND NOT "_u_10"."_u_11" IS NULL
+    ARRAY_ANY(_u_10.a, _x -> _x = x.a) AND NOT _u_10._u_11 IS NULL
   )
   AND (
     (
       (
-        x.a < "_u_12".a AND NOT "_u_12"."_u_13" IS NULL
-      ) AND NOT "_u_12"."_u_13" IS NULL
+        x.a < _u_12.a AND NOT _u_12._u_13 IS NULL
+      ) AND NOT _u_12._u_13 IS NULL
     )
-    AND ARRAY_ANY("_u_12"."_u_14", "_x" -> "_x" <> x.d)
+    AND ARRAY_ANY(_u_12._u_14, "_x" -> _x <> x.d)
   )
   AND (
-    NOT "_u_15".a IS NULL AND NOT "_u_15".a IS NULL
+    NOT _u_15.a IS NULL AND NOT _u_15.a IS NULL
   )
   AND x.a IN (
     SELECT
diff --git a/tests/test_build.py b/tests/test_build.py
index b014a3a..a1a268d 100644
--- a/tests/test_build.py
+++ b/tests/test_build.py
@@ -481,6 +481,19 @@ class TestBuild(unittest.TestCase):
             ),
             (lambda: exp.delete("y", where="x > 1"), "DELETE FROM y WHERE x > 1"),
             (lambda: exp.delete("y", where=exp.and_("x > 1")), "DELETE FROM y WHERE x > 1"),
+            (
+                lambda: select("AVG(a) OVER b")
+                .from_("table")
+                .window("b AS (PARTITION BY c ORDER BY d)"),
+                "SELECT AVG(a) OVER b FROM table WINDOW b AS (PARTITION BY c ORDER BY d)",
+            ),
+            (
+                lambda: select("AVG(a) OVER b", "MIN(c) OVER d")
+                .from_("table")
+                .window("b AS (PARTITION BY e ORDER BY f)")
+                .window("d AS (PARTITION BY g ORDER BY h)"),
+                "SELECT AVG(a) OVER b, MIN(c) OVER d FROM table WINDOW b AS (PARTITION BY e ORDER BY f), d AS (PARTITION BY g ORDER BY h)",
+            ),
         ]:
             with self.subTest(sql):
                 self.assertEqual(expression().sql(dialect[0] if dialect else None), sql)
diff --git a/tests/test_executor.py b/tests/test_executor.py
index 4fe6399..b705551 100644
--- a/tests/test_executor.py
+++ b/tests/test_executor.py
@@ -74,7 +74,7 @@ class TestExecutor(unittest.TestCase):
                 )
             return expression
 
-        for i, (sql, _) in enumerate(self.sqls[0:18]):
+        for i, (sql, _) in enumerate(self.sqls):
             with self.subTest(f"tpch-h {i + 1}"):
                 a = self.cached_execute(sql)
                 sql = parse_one(sql).transform(to_csv).sql(pretty=True)
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 0e13ade..1e23983 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -1,4 +1,5 @@
 import datetime
+import math
 import unittest
 
 from sqlglot import alias, exp, parse_one
@@ -491,7 +492,7 @@ class TestExpressions(unittest.TestCase):
         self.assertEqual(alias("foo", "bar-1").sql(), 'foo AS "bar-1"')
         self.assertEqual(alias("foo", "bar_1").sql(), "foo AS bar_1")
         self.assertEqual(alias("foo * 2", "2bar").sql(), 'foo * 2 AS "2bar"')
-        self.assertEqual(alias('"foo"', "_bar").sql(), '"foo" AS "_bar"')
+        self.assertEqual(alias('"foo"', "_bar").sql(), '"foo" AS _bar')
         self.assertEqual(alias("foo", "bar", quoted=True).sql(), 'foo AS "bar"')
 
     def test_unit(self):
@@ -503,6 +504,8 @@ class TestExpressions(unittest.TestCase):
     def test_identifier(self):
         self.assertTrue(exp.to_identifier('"x"').quoted)
         self.assertFalse(exp.to_identifier("x").quoted)
+        self.assertTrue(exp.to_identifier("foo ").quoted)
+        self.assertFalse(exp.to_identifier("_x").quoted)
 
     def test_function_normalizer(self):
         self.assertEqual(parse_one("HELLO()").sql(normalize_functions="lower"), "hello()")
@@ -549,14 +552,15 @@ class TestExpressions(unittest.TestCase):
             ([1, "2", None], "ARRAY(1, '2', NULL)"),
             ({"x": None}, "MAP('x', NULL)"),
             (
-                datetime.datetime(2022, 10, 1, 1, 1, 1),
-                "TIME_STR_TO_TIME('2022-10-01 01:01:01.000000')",
+                datetime.datetime(2022, 10, 1, 1, 1, 1, 1),
+                "TIME_STR_TO_TIME('2022-10-01T01:01:01.000001+00:00')",
             ),
             (
                 datetime.datetime(2022, 10, 1, 1, 1, 1, tzinfo=datetime.timezone.utc),
-                "TIME_STR_TO_TIME('2022-10-01 01:01:01.000000+0000')",
+                "TIME_STR_TO_TIME('2022-10-01T01:01:01+00:00')",
             ),
             (datetime.date(2022, 10, 1), "DATE_STR_TO_DATE('2022-10-01')"),
+            (math.nan, "NULL"),
         ]:
             with self.subTest(value):
                 self.assertEqual(exp.convert(value).sql(), expected)
diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py
index 0c5f6cd..1c97be7 100644
--- a/tests/test_optimizer.py
+++ b/tests/test_optimizer.py
@@ -164,9 +164,6 @@ class TestOptimizer(unittest.TestCase):
                 with self.assertRaises(OptimizeError):
                     optimizer.qualify_columns.qualify_columns(parse_one(sql), schema=self.schema)
 
-    def test_quote_identities(self):
-        self.check_file("quote_identities", optimizer.quote_identities.quote_identities)
-
     def test_lower_identities(self):
         self.check_file("lower_identities", optimizer.lower_identities.lower_identities)
 
@@ -555,3 +552,29 @@ FROM READ_CSV('tests/fixtures/optimizer/tpc-h/nation.csv.gz', 'delimiter', '|')
                 parse_one(f"SELECT {func}(x.{col}) AS _col_0 FROM x AS x"), schema=schema
             )
             self.assertEqual(expression.expressions[0].type.this, target_type)
+
+    def test_recursive_cte(self):
+        query = parse_one(
+            """
+            with recursive t(n) AS
+            (
+              select 1
+              union all
+              select n + 1
+              FROM t
+              where n < 3
+            ), y AS (
+              select n
+              FROM t
+              union all
+              select n + 1
+              FROM y
+              where n < 2
+            )
+            select * from y
+            """
+        )
+
+        scope_t, scope_y = build_scope(query).cte_scopes
+        self.assertEqual(set(scope_t.cte_sources), {"t"})
+        self.assertEqual(set(scope_y.cte_sources), {"t", "y"})
diff --git a/tests/test_parser.py b/tests/test_parser.py
index 0be15e4..ae2e4cd 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -76,6 +76,9 @@ class TestParser(unittest.TestCase):
         tables = [t.sql() for t in parse_one("select * from a, b.c, .d").find_all(exp.Table)]
         self.assertEqual(tables, ["a", "b.c", "d"])
 
+    def test_union_order(self):
+        self.assertIsInstance(parse_one("SELECT * FROM (SELECT 1) UNION SELECT 2"), exp.Union)
+
     def test_select(self):
         self.assertIsNotNone(parse_one("select 1 natural"))
         self.assertIsNotNone(parse_one("select * from (select 1) x order by x.y").args["order"])
diff --git a/tests/test_transforms.py b/tests/test_transforms.py
index 0bcd2ca..cfb8d2b 100644
--- a/tests/test_transforms.py
+++ b/tests/test_transforms.py
@@ -40,17 +40,17 @@ class TestTime(unittest.TestCase):
         self.validate(
             eliminate_distinct_on,
             "SELECT DISTINCT ON (a) a, b FROM x ORDER BY c DESC",
-            'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS "_row_number" FROM x) WHERE "_row_number" = 1',
+            'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) WHERE "_row_number" = 1',
         )
         self.validate(
             eliminate_distinct_on,
             "SELECT DISTINCT ON (a) a, b FROM x",
-            'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a) AS "_row_number" FROM x) WHERE "_row_number" = 1',
+            'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a) AS _row_number FROM x) WHERE "_row_number" = 1',
         )
         self.validate(
             eliminate_distinct_on,
             "SELECT DISTINCT ON (a, b) a, b FROM x ORDER BY c DESC",
-            'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY c DESC) AS "_row_number" FROM x) WHERE "_row_number" = 1',
+            'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY c DESC) AS _row_number FROM x) WHERE "_row_number" = 1',
         )
         self.validate(
             eliminate_distinct_on,
@@ -60,5 +60,5 @@ class TestTime(unittest.TestCase):
         self.validate(
             eliminate_distinct_on,
             "SELECT DISTINCT ON (_row_number) _row_number FROM x ORDER BY c DESC",
-            'SELECT _row_number FROM (SELECT _row_number, ROW_NUMBER() OVER (PARTITION BY _row_number ORDER BY c DESC) AS "_row_number_2" FROM x) WHERE "_row_number_2" = 1',
+            'SELECT _row_number FROM (SELECT _row_number, ROW_NUMBER() OVER (PARTITION BY _row_number ORDER BY c DESC) AS _row_number_2 FROM x) WHERE "_row_number_2" = 1',
         )
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
index 7bf53e5..9253ded 100644
--- a/tests/test_transpile.py
+++ b/tests/test_transpile.py
@@ -28,7 +28,7 @@ class TestTranspile(unittest.TestCase):
         self.assertEqual(transpile("SELECT 1 current_datetime")[0], "SELECT 1 AS current_datetime")
         self.assertEqual(transpile("SELECT 1 row")[0], "SELECT 1 AS row")
 
-        for key in ("union", "filter", "over", "from", "join"):
+        for key in ("union", "over", "from", "join"):
             with self.subTest(f"alias {key}"):
                 self.validate(f"SELECT x AS {key}", f"SELECT x AS {key}")
                 self.validate(f'SELECT x "{key}"', f'SELECT x AS "{key}"')
@@ -263,6 +263,25 @@ FROM bar /* comment 5 */, tbl /*          comment 6 */""",
             "WITH a AS (SELECT 1), WITH b AS (SELECT 2) SELECT *",
             "WITH a AS (SELECT 1), b AS (SELECT 2) SELECT *",
         )
+        self.validate(
+            "WITH A(filter) AS (VALUES 1, 2, 3) SELECT * FROM A WHERE filter >= 2",
+            "WITH A(filter) AS (VALUES (1), (2), (3)) SELECT * FROM A WHERE filter >= 2",
+        )
+
+    def test_alter(self):
+        self.validate(
+            "ALTER TABLE integers ADD k INTEGER",
+            "ALTER TABLE integers ADD COLUMN k INT",
+        )
+        self.validate("ALTER TABLE integers DROP k", "ALTER TABLE integers DROP COLUMN k")
+        self.validate(
+            "ALTER TABLE integers ALTER i SET DATA TYPE VARCHAR",
+            "ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR",
+        )
+        self.validate(
+            "ALTER TABLE integers ALTER i TYPE VARCHAR COLLATE foo USING bar",
+            "ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR COLLATE foo USING bar",
+        )
 
     def test_time(self):
         self.validate("TIMESTAMP '2020-01-01'", "CAST('2020-01-01' AS TIMESTAMP)")
@@ -403,6 +422,14 @@ FROM bar /* comment 5 */, tbl /*          comment 6 */""",
             with self.subTest(sql):
                 self.assertEqual(transpile(sql)[0], sql.strip())
 
+    def test_normalize_name(self):
+        self.assertEqual(
+            transpile("cardinality(x)", read="presto", write="presto", normalize_functions="lower")[
+                0
+            ],
+            "cardinality(x)",
+        )
+
     def test_partial(self):
         for sql in load_sql_fixtures("partial.sql"):
             with self.subTest(sql):