Adding upstream version 22.2.0.upstream/22.2.0

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

16 files changed, 585 insertions, 168 deletions

diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 8c47948..0d94d19 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -5,7 +5,9 @@ from sqlglot import (
     ParseError,
     TokenError,
     UnsupportedError,
+    exp,
     parse,
+    parse_one,
     transpile,
 )
 from sqlglot.helper import logger as helper_logger
@@ -18,6 +20,51 @@ class TestBigQuery(Validator):
     maxDiff = None
 
     def test_bigquery(self):
+        self.validate_all(
+            "SELECT STRUCT(1, 2, 3), STRUCT(), STRUCT('abc'), STRUCT(1, t.str_col), STRUCT(1 as a, 'abc' AS b), STRUCT(str_col AS abc)",
+            write={
+                "bigquery": "SELECT STRUCT(1, 2, 3), STRUCT(), STRUCT('abc'), STRUCT(1, t.str_col), STRUCT(1 AS a, 'abc' AS b), STRUCT(str_col AS abc)",
+                "duckdb": "SELECT {'_0': 1, '_1': 2, '_2': 3}, {}, {'_0': 'abc'}, {'_0': 1, '_1': t.str_col}, {'a': 1, 'b': 'abc'}, {'abc': str_col}",
+                "hive": "SELECT STRUCT(1, 2, 3), STRUCT(), STRUCT('abc'), STRUCT(1, t.str_col), STRUCT(1, 'abc'), STRUCT(str_col)",
+                "spark2": "SELECT STRUCT(1, 2, 3), STRUCT(), STRUCT('abc'), STRUCT(1, t.str_col), STRUCT(1 AS a, 'abc' AS b), STRUCT(str_col AS abc)",
+                "spark": "SELECT STRUCT(1, 2, 3), STRUCT(), STRUCT('abc'), STRUCT(1, t.str_col), STRUCT(1 AS a, 'abc' AS b), STRUCT(str_col AS abc)",
+                "snowflake": "SELECT OBJECT_CONSTRUCT('_0', 1, '_1', 2, '_2', 3), OBJECT_CONSTRUCT(), OBJECT_CONSTRUCT('_0', 'abc'), OBJECT_CONSTRUCT('_0', 1, '_1', t.str_col), OBJECT_CONSTRUCT('a', 1, 'b', 'abc'), OBJECT_CONSTRUCT('abc', str_col)",
+                # fallback to unnamed without type inference
+                "trino": "SELECT ROW(1, 2, 3), ROW(), ROW('abc'), ROW(1, t.str_col), CAST(ROW(1, 'abc') AS ROW(a INTEGER, b VARCHAR)), ROW(str_col)",
+            },
+        )
+        self.validate_all(
+            "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E6S%z', x)",
+            write={
+                "bigquery": "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E6S%z', x)",
+                "duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S.%f%z')",
+            },
+        )
+
+        table = parse_one("x-0._y.z", dialect="bigquery", into=exp.Table)
+        self.assertEqual(table.catalog, "x-0")
+        self.assertEqual(table.db, "_y")
+        self.assertEqual(table.name, "z")
+
+        table = parse_one("x-0._y", dialect="bigquery", into=exp.Table)
+        self.assertEqual(table.db, "x-0")
+        self.assertEqual(table.name, "_y")
+
+        self.validate_identity("SELECT * FROM x-0.y")
+        self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql(), '"x"."y"."z"')
+        self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql("bigquery"), "`x.y.z`")
+        self.assertEqual(exp.to_table("`x`.`y`", dialect="bigquery").sql("bigquery"), "`x`.`y`")
+
+        select_with_quoted_udf = self.validate_identity("SELECT `p.d.UdF`(data) FROM `p.d.t`")
+        self.assertEqual(select_with_quoted_udf.selects[0].name, "p.d.UdF")
+
+        self.validate_identity("SELECT `p.d.UdF`(data).* FROM `p.d.t`")
+        self.validate_identity("SELECT * FROM `my-project.my-dataset.my-table`")
+        self.validate_identity("CREATE OR REPLACE TABLE `a.b.c` CLONE `a.b.d`")
+        self.validate_identity("SELECT x, 1 AS y GROUP BY 1 ORDER BY 1")
+        self.validate_identity("SELECT * FROM x.*")
+        self.validate_identity("SELECT * FROM x.y*")
+        self.validate_identity("CASE A WHEN 90 THEN 'red' WHEN 50 THEN 'blue' ELSE 'green' END")
         self.validate_identity("CREATE SCHEMA x DEFAULT COLLATE 'en'")
         self.validate_identity("CREATE TABLE x (y INT64) DEFAULT COLLATE 'en'")
         self.validate_identity("PARSE_JSON('{}', wide_number_mode => 'exact')")
@@ -90,6 +137,16 @@ class TestBigQuery(Validator):
         self.validate_identity("LOG(n, b)")
         self.validate_identity("SELECT COUNT(x RESPECT NULLS)")
         self.validate_identity("SELECT LAST_VALUE(x IGNORE NULLS) OVER y AS x")
+        self.validate_identity("SELECT ARRAY((SELECT AS STRUCT 1 AS a, 2 AS b))")
+        self.validate_identity("SELECT ARRAY((SELECT AS STRUCT 1 AS a, 2 AS b) LIMIT 10)")
+        self.validate_identity("CAST(x AS CHAR)", "CAST(x AS STRING)")
+        self.validate_identity("CAST(x AS NCHAR)", "CAST(x AS STRING)")
+        self.validate_identity("CAST(x AS NVARCHAR)", "CAST(x AS STRING)")
+        self.validate_identity("CAST(x AS TIMESTAMPTZ)", "CAST(x AS TIMESTAMP)")
+        self.validate_identity("CAST(x AS RECORD)", "CAST(x AS STRUCT)")
+        self.validate_identity(
+            "SELECT * FROM `SOME_PROJECT_ID.SOME_DATASET_ID.INFORMATION_SCHEMA.SOME_VIEW`"
+        )
         self.validate_identity(
             "SELECT * FROM test QUALIFY a IS DISTINCT FROM b WINDOW c AS (PARTITION BY d)"
         )
@@ -121,6 +178,10 @@ class TestBigQuery(Validator):
             """SELECT JSON_EXTRACT_SCALAR('5')""", """SELECT JSON_EXTRACT_SCALAR('5', '$')"""
         )
         self.validate_identity(
+            "SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)",
+            "SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b)",
+        )
+        self.validate_identity(
             "select array_contains([1, 2, 3], 1)",
             "SELECT EXISTS(SELECT 1 FROM UNNEST([1, 2, 3]) AS _col WHERE _col = 1)",
         )
@@ -169,10 +230,6 @@ class TestBigQuery(Validator):
             """SELECT PARSE_JSON('"foo"') AS json_data""",
         )
         self.validate_identity(
-            "CREATE OR REPLACE TABLE `a.b.c` CLONE `a.b.d`",
-            "CREATE OR REPLACE TABLE a.b.c CLONE a.b.d",
-        )
-        self.validate_identity(
             "SELECT * FROM UNNEST(x) WITH OFFSET EXCEPT DISTINCT SELECT * FROM UNNEST(y) WITH OFFSET",
             "SELECT * FROM UNNEST(x) WITH OFFSET AS offset EXCEPT DISTINCT SELECT * FROM UNNEST(y) WITH OFFSET AS offset",
         )
@@ -186,6 +243,39 @@ class TestBigQuery(Validator):
         )
 
         self.validate_all(
+            "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E6S%z', x)",
+            write={
+                "bigquery": "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E6S%z', x)",
+                "duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S.%f%z')",
+            },
+        )
+        self.validate_all(
+            "SELECT results FROM Coordinates, Coordinates.position AS results",
+            write={
+                "bigquery": "SELECT results FROM Coordinates, UNNEST(Coordinates.position) AS results",
+                "presto": "SELECT results FROM Coordinates, UNNEST(Coordinates.position) AS _t(results)",
+            },
+        )
+        self.validate_all(
+            "SELECT results FROM Coordinates, `Coordinates.position` AS results",
+            write={
+                "bigquery": "SELECT results FROM Coordinates, `Coordinates.position` AS results",
+                "presto": 'SELECT results FROM Coordinates, "Coordinates"."position" AS results',
+            },
+        )
+        self.validate_all(
+            "SELECT results FROM Coordinates AS c, UNNEST(c.position) AS results",
+            read={
+                "presto": "SELECT results FROM Coordinates AS c, UNNEST(c.position) AS _t(results)",
+                "redshift": "SELECT results FROM Coordinates AS c, c.position AS results",
+            },
+            write={
+                "bigquery": "SELECT results FROM Coordinates AS c, UNNEST(c.position) AS results",
+                "presto": "SELECT results FROM Coordinates AS c, UNNEST(c.position) AS _t(results)",
+                "redshift": "SELECT results FROM Coordinates AS c, c.position AS results",
+            },
+        )
+        self.validate_all(
             "TIMESTAMP(x)",
             write={
                 "bigquery": "TIMESTAMP(x)",
@@ -434,8 +524,8 @@ class TestBigQuery(Validator):
         self.validate_all(
             "CREATE OR REPLACE TABLE `a.b.c` COPY `a.b.d`",
             write={
-                "bigquery": "CREATE OR REPLACE TABLE a.b.c COPY a.b.d",
-                "snowflake": "CREATE OR REPLACE TABLE a.b.c CLONE a.b.d",
+                "bigquery": "CREATE OR REPLACE TABLE `a.b.c` COPY `a.b.d`",
+                "snowflake": 'CREATE OR REPLACE TABLE "a"."b"."c" CLONE "a"."b"."d"',
             },
         )
         (
@@ -475,11 +565,6 @@ class TestBigQuery(Validator):
             ),
         )
         self.validate_all("LEAST(x, y)", read={"sqlite": "MIN(x, y)"})
-        self.validate_all("CAST(x AS CHAR)", write={"bigquery": "CAST(x AS STRING)"})
-        self.validate_all("CAST(x AS NCHAR)", write={"bigquery": "CAST(x AS STRING)"})
-        self.validate_all("CAST(x AS NVARCHAR)", write={"bigquery": "CAST(x AS STRING)"})
-        self.validate_all("CAST(x AS TIMESTAMPTZ)", write={"bigquery": "CAST(x AS TIMESTAMP)"})
-        self.validate_all("CAST(x AS RECORD)", write={"bigquery": "CAST(x AS STRUCT)"})
         self.validate_all(
             'SELECT TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE)',
             write={
@@ -566,11 +651,11 @@ class TestBigQuery(Validator):
             read={"spark": "select posexplode_outer([])"},
         )
         self.validate_all(
-            "SELECT AS STRUCT ARRAY(SELECT AS STRUCT b FROM x) AS y FROM z",
+            "SELECT AS STRUCT ARRAY(SELECT AS STRUCT 1 AS b FROM x) AS y FROM z",
             write={
-                "": "SELECT AS STRUCT ARRAY(SELECT AS STRUCT b FROM x) AS y FROM z",
-                "bigquery": "SELECT AS STRUCT ARRAY(SELECT AS STRUCT b FROM x) AS y FROM z",
-                "duckdb": "SELECT {'y': ARRAY(SELECT {'b': b} FROM x)} FROM z",
+                "": "SELECT AS STRUCT ARRAY(SELECT AS STRUCT 1 AS b FROM x) AS y FROM z",
+                "bigquery": "SELECT AS STRUCT ARRAY(SELECT AS STRUCT 1 AS b FROM x) AS y FROM z",
+                "duckdb": "SELECT {'y': ARRAY(SELECT {'b': 1} FROM x)} FROM z",
             },
         )
         self.validate_all(
@@ -585,25 +670,9 @@ class TestBigQuery(Validator):
                 "bigquery": "PARSE_TIMESTAMP('%Y.%m.%d %I:%M:%S%z', x)",
             },
         )
-        self.validate_all(
+        self.validate_identity(
             "CREATE TEMP TABLE foo AS SELECT 1",
-            write={"bigquery": "CREATE TEMPORARY TABLE foo AS SELECT 1"},
-        )
-        self.validate_all(
-            "SELECT * FROM `SOME_PROJECT_ID.SOME_DATASET_ID.INFORMATION_SCHEMA.SOME_VIEW`",
-            write={
-                "bigquery": "SELECT * FROM SOME_PROJECT_ID.SOME_DATASET_ID.INFORMATION_SCHEMA.SOME_VIEW",
-            },
-        )
-        self.validate_all(
-            "SELECT * FROM `my-project.my-dataset.my-table`",
-            write={"bigquery": "SELECT * FROM `my-project`.`my-dataset`.`my-table`"},
-        )
-        self.validate_all(
-            "SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)",
-            write={
-                "bigquery": "SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b)",
-            },
+            "CREATE TEMPORARY TABLE foo AS SELECT 1",
         )
         self.validate_all(
             "REGEXP_CONTAINS('foo', '.*')",
@@ -1088,6 +1157,35 @@ WHERE
 
             self.assertIn("unsupported syntax", cm.output[0])
 
+        with self.assertLogs(helper_logger):
+            statements = parse(
+                """
+                BEGIN
+                    DECLARE MY_VAR INT64 DEFAULT 1;
+                    SET MY_VAR = (SELECT 0);
+
+                    IF MY_VAR = 1 THEN SELECT 'TRUE';
+                    ELSEIF MY_VAR = 0 THEN SELECT 'FALSE';
+                    ELSE SELECT 'NULL';
+                    END IF;
+                END
+                """,
+                read="bigquery",
+            )
+
+            expected_statements = (
+                "BEGIN DECLARE MY_VAR INT64 DEFAULT 1",
+                "SET MY_VAR = (SELECT 0)",
+                "IF MY_VAR = 1 THEN SELECT 'TRUE'",
+                "ELSEIF MY_VAR = 0 THEN SELECT 'FALSE'",
+                "ELSE SELECT 'NULL'",
+                "END IF",
+                "END",
+            )
+
+            for actual, expected in zip(statements, expected_statements):
+                self.assertEqual(actual.sql(dialect="bigquery"), expected)
+
         with self.assertLogs(helper_logger) as cm:
             self.validate_identity(
                 "SELECT * FROM t AS t(c1, c2)",
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 0148812..edf3da1 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -6,6 +6,21 @@ class TestClickhouse(Validator):
     dialect = "clickhouse"
 
     def test_clickhouse(self):
+        self.validate_all(
+            "SELECT * FROM x PREWHERE y = 1 WHERE z = 2",
+            write={
+                "": "SELECT * FROM x WHERE z = 2",
+                "clickhouse": "SELECT * FROM x PREWHERE y = 1 WHERE z = 2",
+            },
+        )
+        self.validate_all(
+            "SELECT * FROM x AS prewhere",
+            read={
+                "clickhouse": "SELECT * FROM x AS prewhere",
+                "duckdb": "SELECT * FROM x prewhere",
+            },
+        )
+
         self.validate_identity("SELECT * FROM x LIMIT 1 UNION ALL SELECT * FROM y")
 
         string_types = [
@@ -77,6 +92,7 @@ class TestClickhouse(Validator):
         self.validate_identity("""SELECT JSONExtractString('{"x": {"y": 1}}', 'x', 'y')""")
         self.validate_identity("SELECT * FROM table LIMIT 1 BY a, b")
         self.validate_identity("SELECT * FROM table LIMIT 2 OFFSET 1 BY a, b")
+
         self.validate_identity(
             "SELECT $1$foo$1$",
             "SELECT 'foo'",
@@ -134,6 +150,9 @@ class TestClickhouse(Validator):
         self.validate_identity(
             "CREATE MATERIALIZED VIEW test_view (id UInt8) TO db.table1 AS SELECT * FROM test_data"
         )
+        self.validate_identity("TRUNCATE TABLE t1 ON CLUSTER test_cluster")
+        self.validate_identity("TRUNCATE DATABASE db")
+        self.validate_identity("TRUNCATE DATABASE db ON CLUSTER test_cluster")
 
         self.validate_all(
             "SELECT arrayJoin([1,2,3])",
@@ -373,6 +392,7 @@ class TestClickhouse(Validator):
 
     def test_cte(self):
         self.validate_identity("WITH 'x' AS foo SELECT foo")
+        self.validate_identity("WITH ['c'] AS field_names SELECT field_names")
         self.validate_identity("WITH SUM(bytes) AS foo SELECT foo FROM system.parts")
         self.validate_identity("WITH (SELECT foo) AS bar SELECT bar + 5")
         self.validate_identity("WITH test1 AS (SELECT i + 1, j + 1 FROM test1) SELECT * FROM test1")
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index 8222170..94f2dc2 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -38,6 +38,11 @@ class TestDatabricks(Validator):
             "CREATE FUNCTION add_one(x INT) RETURNS INT LANGUAGE PYTHON AS $FOO$def add_one(x):\n  return x+1$FOO$"
         )
 
+        self.validate_identity("TRUNCATE TABLE t1 PARTITION(age = 10, name = 'test', address)")
+        self.validate_identity(
+            "TRUNCATE TABLE t1 PARTITION(age = 10, name = 'test', city LIKE 'LA')"
+        )
+
         self.validate_all(
             "CREATE TABLE foo (x INT GENERATED ALWAYS AS (YEAR(y)))",
             write={
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index b50fec8..5faed51 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1108,6 +1108,11 @@ class TestDialect(Validator):
         )
 
     def test_order_by(self):
+        self.validate_identity(
+            "SELECT c FROM t ORDER BY a, b,",
+            "SELECT c FROM t ORDER BY a, b",
+        )
+
         self.validate_all(
             "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname",
             write={
@@ -1777,7 +1782,7 @@ class TestDialect(Validator):
             "CREATE TABLE t (c CHAR, nc NCHAR, v1 VARCHAR, v2 VARCHAR2, nv NVARCHAR, nv2 NVARCHAR2)",
             write={
                 "duckdb": "CREATE TABLE t (c TEXT, nc TEXT, v1 TEXT, v2 TEXT, nv TEXT, nv2 TEXT)",
-                "hive": "CREATE TABLE t (c CHAR, nc CHAR, v1 STRING, v2 STRING, nv STRING, nv2 STRING)",
+                "hive": "CREATE TABLE t (c STRING, nc STRING, v1 STRING, v2 STRING, nv STRING, nv2 STRING)",
                 "oracle": "CREATE TABLE t (c CHAR, nc NCHAR, v1 VARCHAR2, v2 VARCHAR2, nv NVARCHAR2, nv2 NVARCHAR2)",
                 "postgres": "CREATE TABLE t (c CHAR, nc CHAR, v1 VARCHAR, v2 VARCHAR, nv VARCHAR, nv2 VARCHAR)",
                 "sqlite": "CREATE TABLE t (c TEXT, nc TEXT, v1 TEXT, v2 TEXT, nv TEXT, nv2 TEXT)",
@@ -2301,3 +2306,9 @@ SELECT
                 "tsql": UnsupportedError,
             },
         )
+
+    def test_truncate(self):
+        self.validate_identity("TRUNCATE TABLE table")
+        self.validate_identity("TRUNCATE TABLE db.schema.test")
+        self.validate_identity("TRUNCATE TABLE IF EXISTS db.schema.test")
+        self.validate_identity("TRUNCATE TABLE t1, t2, t3")
diff --git a/tests/dialects/test_doris.py b/tests/dialects/test_doris.py
index 5ae23ad..035289b 100644
--- a/tests/dialects/test_doris.py
+++ b/tests/dialects/test_doris.py
@@ -26,6 +26,16 @@ class TestDoris(Validator):
                 "doris": "SELECT ARRAY_SUM(x -> x * x, ARRAY(2, 3))",
             },
         )
+        self.validate_all(
+            "MONTHS_ADD(d, n)",
+            read={
+                "oracle": "ADD_MONTHS(d, n)",
+            },
+            write={
+                "doris": "MONTHS_ADD(d, n)",
+                "oracle": "ADD_MONTHS(d, n)",
+            },
+        )
 
     def test_identity(self):
         self.validate_identity("COALECSE(a, b, c, d)")
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 9c48f69..58d1f06 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -7,9 +7,14 @@ class TestDuckDB(Validator):
     dialect = "duckdb"
 
     def test_duckdb(self):
-        struct_pack = parse_one('STRUCT_PACK("a b" := 1)', read="duckdb")
-        self.assertIsInstance(struct_pack.expressions[0].this, exp.Identifier)
-        self.assertEqual(struct_pack.sql(dialect="duckdb"), "{'a b': 1}")
+        self.validate_all(
+            'STRUCT_PACK("a b" := 1)',
+            write={
+                "duckdb": "{'a b': 1}",
+                "spark": "STRUCT(1 AS `a b`)",
+                "snowflake": "OBJECT_CONSTRUCT('a b', 1)",
+            },
+        )
 
         self.validate_all(
             "SELECT SUM(X) OVER (ORDER BY x)",
@@ -52,8 +57,21 @@ class TestDuckDB(Validator):
             exp.select("*").from_("t").offset(exp.select("5").subquery()).sql(dialect="duckdb"),
         )
 
-        for struct_value in ("{'a': 1}", "struct_pack(a := 1)"):
-            self.validate_all(struct_value, write={"presto": UnsupportedError})
+        self.validate_all(
+            "{'a': 1, 'b': '2'}", write={"presto": "CAST(ROW(1, '2') AS ROW(a INTEGER, b VARCHAR))"}
+        )
+        self.validate_all(
+            "struct_pack(a := 1, b := 2)",
+            write={"presto": "CAST(ROW(1, 2) AS ROW(a INTEGER, b INTEGER))"},
+        )
+
+        self.validate_all(
+            "struct_pack(a := 1, b := x)",
+            write={
+                "duckdb": "{'a': 1, 'b': x}",
+                "presto": UnsupportedError,
+            },
+        )
 
         for join_type in ("SEMI", "ANTI"):
             exists = "EXISTS" if join_type == "SEMI" else "NOT EXISTS"
@@ -171,7 +189,6 @@ class TestDuckDB(Validator):
             },
         )
 
-        self.validate_identity("SELECT i FROM RANGE(5) AS _(i) ORDER BY i ASC")
         self.validate_identity("INSERT INTO x BY NAME SELECT 1 AS y")
         self.validate_identity("SELECT 1 AS x UNION ALL BY NAME SELECT 2 AS x")
         self.validate_identity("SELECT SUM(x) FILTER (x = 1)", "SELECT SUM(x) FILTER(WHERE x = 1)")
@@ -209,6 +226,10 @@ class TestDuckDB(Validator):
         self.validate_identity("FROM (FROM tbl)", "SELECT * FROM (SELECT * FROM tbl)")
         self.validate_identity("FROM tbl", "SELECT * FROM tbl")
         self.validate_identity("x -> '$.family'")
+        self.validate_identity("CREATE TABLE color (name ENUM('RED', 'GREEN', 'BLUE'))")
+        self.validate_identity(
+            "SELECT * FROM x LEFT JOIN UNNEST(y)", "SELECT * FROM x LEFT JOIN UNNEST(y) ON TRUE"
+        )
         self.validate_identity(
             """SELECT '{"foo": [1, 2, 3]}' -> 'foo' -> 0""",
             """SELECT '{"foo": [1, 2, 3]}' -> '$.foo' -> '$[0]'""",
@@ -623,6 +644,27 @@ class TestDuckDB(Validator):
             },
         )
 
+        self.validate_identity("SELECT * FROM RANGE(1, 5, 10)")
+        self.validate_identity("SELECT * FROM GENERATE_SERIES(2, 13, 4)")
+
+        self.validate_all(
+            "WITH t AS (SELECT i, i * i * i * i * i AS i5 FROM RANGE(1, 5) t(i)) SELECT * FROM t",
+            write={
+                "duckdb": "WITH t AS (SELECT i, i * i * i * i * i AS i5 FROM RANGE(1, 5) AS t(i)) SELECT * FROM t",
+                "sqlite": "WITH t AS (SELECT i, i * i * i * i * i AS i5 FROM (SELECT value AS i FROM GENERATE_SERIES(1, 5)) AS t) SELECT * FROM t",
+            },
+        )
+
+        self.validate_identity(
+            """SELECT i FROM RANGE(5) AS _(i) ORDER BY i ASC""",
+            """SELECT i FROM RANGE(0, 5) AS _(i) ORDER BY i ASC""",
+        )
+
+        self.validate_identity(
+            """SELECT i FROM GENERATE_SERIES(12) AS _(i) ORDER BY i ASC""",
+            """SELECT i FROM GENERATE_SERIES(0, 12) AS _(i) ORDER BY i ASC""",
+        )
+
     def test_array_index(self):
         with self.assertLogs(helper_logger) as cm:
             self.validate_all(
@@ -994,3 +1036,10 @@ class TestDuckDB(Validator):
             read={"bigquery": "IS_INF(x)"},
             write={"bigquery": "IS_INF(x)", "duckdb": "ISINF(x)"},
         )
+
+    def test_parameter_token(self):
+        self.validate_all(
+            "SELECT $foo",
+            read={"bigquery": "SELECT @foo"},
+            write={"bigquery": "SELECT @foo", "duckdb": "SELECT $foo"},
+        )
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index ea28f29..b892dd6 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -440,6 +440,9 @@ class TestHive(Validator):
         self.validate_identity(
             "SELECT key, value, GROUPING__ID, COUNT(*) FROM T1 GROUP BY key, value WITH ROLLUP"
         )
+        self.validate_identity(
+            "TRUNCATE TABLE t1 PARTITION(age = 10, name = 'test', address = 'abc')"
+        )
 
         self.validate_all(
             "SELECT ${hiveconf:some_var}",
@@ -612,12 +615,6 @@ class TestHive(Validator):
             },
         )
         self.validate_all(
-            "STRUCT(a = b, c = d)",
-            read={
-                "snowflake": "OBJECT_CONSTRUCT(a, b, c, d)",
-            },
-        )
-        self.validate_all(
             "MAP(a, b, c, d)",
             read={
                 "": "VAR_MAP(a, b, c, d)",
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index fd27a1e..5f23c44 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -29,6 +29,7 @@ class TestMySQL(Validator):
         self.validate_identity("CREATE TABLE foo (a BIGINT, INDEX USING BTREE (b))")
         self.validate_identity("CREATE TABLE foo (a BIGINT, FULLTEXT INDEX (b))")
         self.validate_identity("CREATE TABLE foo (a BIGINT, SPATIAL INDEX (b))")
+        self.validate_identity("ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=EXCLUSIVE")
         self.validate_identity(
             "CREATE TABLE `oauth_consumer` (`key` VARCHAR(32) NOT NULL, UNIQUE `OAUTH_CONSUMER_KEY` (`key`))"
         )
@@ -68,6 +69,26 @@ class TestMySQL(Validator):
         self.validate_identity(
             "CREATE OR REPLACE VIEW my_view AS SELECT column1 AS `boo`, column2 AS `foo` FROM my_table WHERE column3 = 'some_value' UNION SELECT q.* FROM fruits_table, JSON_TABLE(Fruits, '$[*]' COLUMNS(id VARCHAR(255) PATH '$.$id', value VARCHAR(255) PATH '$.value')) AS q",
         )
+        self.validate_identity(
+            "CREATE TABLE `foo` (`id` char(36) NOT NULL DEFAULT (uuid()), PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`))",
+            "CREATE TABLE `foo` (`id` CHAR(36) NOT NULL DEFAULT (UUID()), PRIMARY KEY (`id`), UNIQUE `id` (`id`))",
+        )
+        self.validate_identity(
+            "CREATE TABLE IF NOT EXISTS industry_info (a BIGINT(20) NOT NULL AUTO_INCREMENT, b BIGINT(20) NOT NULL, c VARCHAR(1000), PRIMARY KEY (a), UNIQUE KEY d (b), KEY e (b))",
+            "CREATE TABLE IF NOT EXISTS industry_info (a BIGINT(20) NOT NULL AUTO_INCREMENT, b BIGINT(20) NOT NULL, c VARCHAR(1000), PRIMARY KEY (a), UNIQUE d (b), INDEX e (b))",
+        )
+        self.validate_identity(
+            "CREATE TABLE test (ts TIMESTAMP, ts_tz TIMESTAMPTZ, ts_ltz TIMESTAMPLTZ)",
+            "CREATE TABLE test (ts DATETIME, ts_tz TIMESTAMP, ts_ltz TIMESTAMP)",
+        )
+        self.validate_identity(
+            "ALTER TABLE test_table ALTER COLUMN test_column SET DATA TYPE LONGTEXT",
+            "ALTER TABLE test_table MODIFY COLUMN test_column LONGTEXT",
+        )
+        self.validate_identity(
+            "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC",
+            "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()) DEFAULT CHARACTER SET=utf8 ROW_FORMAT=DYNAMIC",
+        )
 
         self.validate_all(
             "CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x'",
@@ -79,12 +100,6 @@ class TestMySQL(Validator):
             },
         )
         self.validate_all(
-            "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC",
-            write={
-                "mysql": "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()) DEFAULT CHARACTER SET=utf8 ROW_FORMAT=DYNAMIC",
-            },
-        )
-        self.validate_all(
             "CREATE TABLE x (id int not null auto_increment, primary key (id))",
             write={
                 "sqlite": "CREATE TABLE x (id INTEGER NOT NULL AUTOINCREMENT PRIMARY KEY)",
@@ -96,33 +111,9 @@ class TestMySQL(Validator):
                 "sqlite": "CREATE TABLE x (id INTEGER NOT NULL)",
             },
         )
-        self.validate_all(
-            "CREATE TABLE `foo` (`id` char(36) NOT NULL DEFAULT (uuid()), PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`))",
-            write={
-                "mysql": "CREATE TABLE `foo` (`id` CHAR(36) NOT NULL DEFAULT (UUID()), PRIMARY KEY (`id`), UNIQUE `id` (`id`))",
-            },
-        )
-        self.validate_all(
-            "CREATE TABLE IF NOT EXISTS industry_info (a BIGINT(20) NOT NULL AUTO_INCREMENT, b BIGINT(20) NOT NULL, c VARCHAR(1000), PRIMARY KEY (a), UNIQUE KEY d (b), KEY e (b))",
-            write={
-                "mysql": "CREATE TABLE IF NOT EXISTS industry_info (a BIGINT(20) NOT NULL AUTO_INCREMENT, b BIGINT(20) NOT NULL, c VARCHAR(1000), PRIMARY KEY (a), UNIQUE d (b), INDEX e (b))",
-            },
-        )
-        self.validate_all(
-            "CREATE TABLE test (ts TIMESTAMP, ts_tz TIMESTAMPTZ, ts_ltz TIMESTAMPLTZ)",
-            write={
-                "mysql": "CREATE TABLE test (ts DATETIME, ts_tz TIMESTAMP, ts_ltz TIMESTAMP)",
-            },
-        )
-        self.validate_all(
-            "ALTER TABLE test_table ALTER COLUMN test_column SET DATA TYPE LONGTEXT",
-            write={
-                "mysql": "ALTER TABLE test_table MODIFY COLUMN test_column LONGTEXT",
-            },
-        )
-        self.validate_identity("ALTER TABLE test_table ALTER COLUMN test_column SET DEFAULT 1")
 
     def test_identity(self):
+        self.validate_identity("ALTER TABLE test_table ALTER COLUMN test_column SET DEFAULT 1")
         self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00.0000')")
         self.validate_identity("SELECT @var1 := 1, @var2")
         self.validate_identity("UNLOCK TABLES")
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index bc8f8bb..9438507 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -1,4 +1,4 @@
-from sqlglot import exp, parse_one
+from sqlglot import exp
 from sqlglot.errors import UnsupportedError
 from tests.dialects.test_dialect import Validator
 
@@ -7,11 +7,18 @@ class TestOracle(Validator):
     dialect = "oracle"
 
     def test_oracle(self):
-        self.validate_identity("REGEXP_REPLACE('source', 'search')")
-        parse_one("ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol", dialect="oracle").assert_is(
-            exp.AlterTable
+        self.validate_all(
+            "SELECT CONNECT_BY_ROOT x y",
+            write={
+                "": "SELECT CONNECT_BY_ROOT(x) AS y",
+                "oracle": "SELECT CONNECT_BY_ROOT x AS y",
+            },
         )
+        self.parse_one("ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol").assert_is(exp.AlterTable)
 
+        self.validate_identity("CREATE GLOBAL TEMPORARY TABLE t AS SELECT * FROM orders")
+        self.validate_identity("CREATE PRIVATE TEMPORARY TABLE t AS SELECT * FROM orders")
+        self.validate_identity("REGEXP_REPLACE('source', 'search')")
         self.validate_identity("TIMESTAMP(3) WITH TIME ZONE")
         self.validate_identity("CURRENT_TIMESTAMP(precision)")
         self.validate_identity("ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol")
@@ -89,6 +96,13 @@ class TestOracle(Validator):
         self.validate_identity("SELECT TO_CHAR(-100, 'L99', 'NL_CURRENCY = '' AusDollars '' ')")
 
         self.validate_all(
+            "TO_CHAR(x)",
+            write={
+                "doris": "CAST(x AS STRING)",
+                "oracle": "TO_CHAR(x)",
+            },
+        )
+        self.validate_all(
             "SELECT TO_CHAR(TIMESTAMP '1999-12-01 10:00:00')",
             write={
                 "oracle": "SELECT TO_CHAR(CAST('1999-12-01 10:00:00' AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS')",
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index d1ecb2a..1d0ea8b 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -8,8 +8,10 @@ class TestPostgres(Validator):
     dialect = "postgres"
 
     def test_postgres(self):
+        self.validate_identity("1.x", "1. AS x")
         self.validate_identity("|/ x", "SQRT(x)")
         self.validate_identity("||/ x", "CBRT(x)")
+
         expr = parse_one(
             "SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)", read="postgres"
         )
@@ -82,6 +84,7 @@ class TestPostgres(Validator):
         self.validate_identity("CAST(1 AS DECIMAL) / CAST(2 AS DECIMAL) * -100")
         self.validate_identity("EXEC AS myfunc @id = 123", check_command_warning=True)
         self.validate_identity("SELECT CURRENT_USER")
+        self.validate_identity("SELECT * FROM ONLY t1")
         self.validate_identity(
             """LAST_VALUE("col1") OVER (ORDER BY "col2" RANGE BETWEEN INTERVAL '1 DAY' PRECEDING AND '1 month' FOLLOWING)"""
         )
@@ -164,6 +167,9 @@ class TestPostgres(Validator):
             "SELECT 'Dianne''s horse'",
         )
         self.validate_identity(
+            "COMMENT ON TABLE mytable IS $$doc this$$", "COMMENT ON TABLE mytable IS 'doc this'"
+        )
+        self.validate_identity(
             "UPDATE MYTABLE T1 SET T1.COL = 13",
             "UPDATE MYTABLE AS T1 SET T1.COL = 13",
         )
@@ -320,6 +326,7 @@ class TestPostgres(Validator):
             "MERGE INTO x USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET x.a = y.b WHEN NOT MATCHED THEN INSERT (a, b) VALUES (y.a, y.b)",
             "MERGE INTO x USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET a = y.b WHEN NOT MATCHED THEN INSERT (a, b) VALUES (y.a, y.b)",
         )
+        self.validate_identity("SELECT * FROM t1*", "SELECT * FROM t1")
 
         self.validate_all(
             "SELECT JSON_EXTRACT_PATH_TEXT(x, k1, k2, k3) FROM t",
@@ -653,6 +660,12 @@ class TestPostgres(Validator):
         self.validate_identity("CREATE TABLE t (c CHAR(2) UNIQUE NOT NULL) INHERITS (t1)")
         self.validate_identity("CREATE TABLE s.t (c CHAR(2) UNIQUE NOT NULL) INHERITS (s.t1, s.t2)")
         self.validate_identity("CREATE FUNCTION x(INT) RETURNS INT SET search_path = 'public'")
+        self.validate_identity("TRUNCATE TABLE t1 CONTINUE IDENTITY")
+        self.validate_identity("TRUNCATE TABLE t1 RESTART IDENTITY")
+        self.validate_identity("TRUNCATE TABLE t1 CASCADE")
+        self.validate_identity("TRUNCATE TABLE t1 RESTRICT")
+        self.validate_identity("TRUNCATE TABLE t1 CONTINUE IDENTITY CASCADE")
+        self.validate_identity("TRUNCATE TABLE t1 RESTART IDENTITY RESTRICT")
         self.validate_identity(
             "CREATE TABLE cust_part3 PARTITION OF customers FOR VALUES WITH (MODULUS 3, REMAINDER 2)"
         )
@@ -785,6 +798,10 @@ class TestPostgres(Validator):
         self.validate_identity(
             "CREATE INDEX index_ci_pipelines_on_project_idandrefandiddesc ON public.ci_pipelines USING btree(project_id, ref, id DESC)"
         )
+        self.validate_identity(
+            "TRUNCATE TABLE ONLY t1, t2*, ONLY t3, t4, t5* RESTART IDENTITY CASCADE",
+            "TRUNCATE TABLE ONLY t1, t2, ONLY t3, t4, t5 RESTART IDENTITY CASCADE",
+        )
 
         with self.assertRaises(ParseError):
             transpile("CREATE TABLE products (price DECIMAL CHECK price > 0)", read="postgres")
@@ -911,3 +928,31 @@ class TestPostgres(Validator):
         """See https://github.com/tobymao/sqlglot/pull/2404 for details."""
         self.assertIsInstance(parse_one("'thomas' ~ '.*thomas.*'", read="postgres"), exp.Binary)
         self.assertIsInstance(parse_one("'thomas' ~* '.*thomas.*'", read="postgres"), exp.Binary)
+
+    def test_unnest_json_array(self):
+        trino_input = """
+            WITH t(boxcrate) AS (
+              SELECT JSON '[{"boxes": [{"name": "f1", "type": "plant", "color": "red"}]}]'
+            )
+            SELECT
+              JSON_EXTRACT_SCALAR(boxes,'$.name')  AS name,
+              JSON_EXTRACT_SCALAR(boxes,'$.type')  AS type,
+              JSON_EXTRACT_SCALAR(boxes,'$.color') AS color
+            FROM t
+            CROSS JOIN UNNEST(CAST(boxcrate AS array(json))) AS x(tbox)
+            CROSS JOIN UNNEST(CAST(json_extract(tbox, '$.boxes') AS array(json))) AS y(boxes)
+        """
+
+        expected_postgres = """WITH t(boxcrate) AS (
+  SELECT
+    CAST('[{"boxes": [{"name": "f1", "type": "plant", "color": "red"}]}]' AS JSON)
+)
+SELECT
+  JSON_EXTRACT_PATH_TEXT(boxes, 'name') AS name,
+  JSON_EXTRACT_PATH_TEXT(boxes, 'type') AS type,
+  JSON_EXTRACT_PATH_TEXT(boxes, 'color') AS color
+FROM t
+CROSS JOIN JSON_ARRAY_ELEMENTS(CAST(boxcrate AS JSON)) AS x(tbox)
+CROSS JOIN JSON_ARRAY_ELEMENTS(CAST(JSON_EXTRACT_PATH(tbox, 'boxes') AS JSON)) AS y(boxes)"""
+
+        self.validate_all(expected_postgres, read={"trino": trino_input}, pretty=True)
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index d3d1a76..2ea595e 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -647,6 +647,7 @@ class TestPresto(Validator):
             """JSON '"foo"'""",
             write={
                 "bigquery": """PARSE_JSON('"foo"')""",
+                "postgres": """CAST('"foo"' AS JSON)""",
                 "presto": """JSON_PARSE('"foo"')""",
                 "snowflake": """PARSE_JSON('"foo"')""",
             },
@@ -1142,3 +1143,18 @@ MATCH_RECOGNIZE (
                 "presto": "DATE_FORMAT(ts, '%y')",
             },
         )
+
+    def test_signum(self):
+        self.validate_all(
+            "SIGN(x)",
+            read={
+                "presto": "SIGN(x)",
+                "spark": "SIGNUM(x)",
+                "starrocks": "SIGN(x)",
+            },
+            write={
+                "presto": "SIGN(x)",
+                "spark": "SIGN(x)",
+                "starrocks": "SIGN(x)",
+            },
+        )
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 33cfa0c..506f429 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -515,6 +515,11 @@ FROM (
         )
 
     def test_column_unnesting(self):
+        self.validate_identity("SELECT c.*, o FROM bloo AS c, c.c_orders AS o")
+        self.validate_identity(
+            "SELECT c.*, o, l FROM bloo AS c, c.c_orders AS o, o.o_lineitems AS l"
+        )
+
         ast = parse_one("SELECT * FROM t.t JOIN t.c1 ON c1.c2 = t.c3", read="redshift")
         ast.args["from"].this.assert_is(exp.Table)
         ast.args["joins"][0].this.assert_is(exp.Table)
@@ -522,7 +527,7 @@ FROM (
 
         ast = parse_one("SELECT * FROM t AS t CROSS JOIN t.c1", read="redshift")
         ast.args["from"].this.assert_is(exp.Table)
-        ast.args["joins"][0].this.assert_is(exp.Column)
+        ast.args["joins"][0].this.assert_is(exp.Unnest)
         self.assertEqual(ast.sql("redshift"), "SELECT * FROM t AS t CROSS JOIN t.c1")
 
         ast = parse_one(
@@ -530,9 +535,9 @@ FROM (
         )
         joins = ast.args["joins"]
         ast.args["from"].this.assert_is(exp.Table)
-        joins[0].this.this.assert_is(exp.Column)
-        joins[1].this.this.assert_is(exp.Column)
-        joins[2].this.this.assert_is(exp.Dot)
+        joins[0].this.assert_is(exp.Unnest)
+        joins[1].this.assert_is(exp.Unnest)
+        joins[2].this.assert_is(exp.Unnest).expressions[0].assert_is(exp.Dot)
         self.assertEqual(
             ast.sql("redshift"), "SELECT * FROM x AS a, a.b AS c, c.d.e AS f, f.g.h.i.j.k AS l"
         )
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 4e4feb3..e48f811 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -40,6 +40,7 @@ WHERE
   )""",
         )
 
+        self.validate_identity("ALTER TABLE authors ADD CONSTRAINT c1 UNIQUE (id, email)")
         self.validate_identity("RM @parquet_stage", check_command_warning=True)
         self.validate_identity("REMOVE @parquet_stage", check_command_warning=True)
         self.validate_identity("SELECT TIMESTAMP_FROM_PARTS(d, t)")
@@ -84,6 +85,7 @@ WHERE
         self.validate_identity(
             "SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) AS x TABLESAMPLE (0.1)"
         )
+        self.validate_identity("x:from", "GET_PATH(x, 'from')")
         self.validate_identity(
             "value:values::string",
             "CAST(GET_PATH(value, 'values') AS TEXT)",
@@ -371,15 +373,17 @@ WHERE
             write={"snowflake": "SELECT * FROM (VALUES (0)) AS foo(bar)"},
         )
         self.validate_all(
-            "OBJECT_CONSTRUCT(a, b, c, d)",
+            "OBJECT_CONSTRUCT('a', b, 'c', d)",
             read={
-                "": "STRUCT(a as b, c as d)",
+                "": "STRUCT(b as a, d as c)",
             },
             write={
                 "duckdb": "{'a': b, 'c': d}",
-                "snowflake": "OBJECT_CONSTRUCT(a, b, c, d)",
+                "snowflake": "OBJECT_CONSTRUCT('a', b, 'c', d)",
             },
         )
+        self.validate_identity("OBJECT_CONSTRUCT(a, b, c, d)")
+
         self.validate_all(
             "SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1",
             write={
@@ -1461,26 +1465,22 @@ MATCH_RECOGNIZE (
                     pretty=True,
                 )
 
-    def test_show(self):
-        # Parsed as Show
-        self.validate_identity("SHOW PRIMARY KEYS")
-        self.validate_identity("SHOW PRIMARY KEYS IN ACCOUNT")
-        self.validate_identity("SHOW PRIMARY KEYS IN DATABASE")
-        self.validate_identity("SHOW PRIMARY KEYS IN DATABASE foo")
-        self.validate_identity("SHOW PRIMARY KEYS IN TABLE")
-        self.validate_identity("SHOW PRIMARY KEYS IN TABLE foo")
-        self.validate_identity(
-            'SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."customers"',
-            'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."customers"',
-        )
-        self.validate_identity(
-            'SHOW TERSE PRIMARY KEYS IN "TEST"."PUBLIC"."customers"',
-            'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."customers"',
-        )
+    def test_show_users(self):
+        self.validate_identity("SHOW USERS")
+        self.validate_identity("SHOW TERSE USERS")
+        self.validate_identity("SHOW USERS LIKE '_foo%' STARTS WITH 'bar' LIMIT 5 FROM 'baz'")
+
+    def test_show_schemas(self):
         self.validate_identity(
             "show terse schemas in database db1 starts with 'a' limit 10 from 'b'",
             "SHOW TERSE SCHEMAS IN DATABASE db1 STARTS WITH 'a' LIMIT 10 FROM 'b'",
         )
+
+        ast = parse_one("SHOW SCHEMAS IN DATABASE db1", read="snowflake")
+        self.assertEqual(ast.args.get("scope_kind"), "DATABASE")
+        self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), "db1")
+
+    def test_show_objects(self):
         self.validate_identity(
             "show terse objects in schema db1.schema1 starts with 'a' limit 10 from 'b'",
             "SHOW TERSE OBJECTS IN SCHEMA db1.schema1 STARTS WITH 'a' LIMIT 10 FROM 'b'",
@@ -1489,6 +1489,23 @@ MATCH_RECOGNIZE (
             "show terse objects in db1.schema1 starts with 'a' limit 10 from 'b'",
             "SHOW TERSE OBJECTS IN SCHEMA db1.schema1 STARTS WITH 'a' LIMIT 10 FROM 'b'",
         )
+
+        ast = parse_one("SHOW OBJECTS IN db1.schema1", read="snowflake")
+        self.assertEqual(ast.args.get("scope_kind"), "SCHEMA")
+        self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), "db1.schema1")
+
+    def test_show_columns(self):
+        self.validate_identity("SHOW COLUMNS")
+        self.validate_identity("SHOW COLUMNS IN TABLE dt_test")
+        self.validate_identity("SHOW COLUMNS LIKE '_foo%' IN TABLE dt_test")
+        self.validate_identity("SHOW COLUMNS IN VIEW")
+        self.validate_identity("SHOW COLUMNS LIKE '_foo%' IN VIEW dt_test")
+
+        ast = parse_one("SHOW COLUMNS LIKE '_testing%' IN dt_test", read="snowflake")
+        self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), "dt_test")
+        self.assertEqual(ast.find(exp.Literal).sql(dialect="snowflake"), "'_testing%'")
+
+    def test_show_tables(self):
         self.validate_identity(
             "SHOW TABLES LIKE 'line%' IN tpch.public",
             "SHOW TABLES LIKE 'line%' IN SCHEMA tpch.public",
@@ -1506,47 +1523,97 @@ MATCH_RECOGNIZE (
             "SHOW TERSE TABLES IN SCHEMA db1.schema1 STARTS WITH 'a' LIMIT 10 FROM 'b'",
         )
 
-        ast = parse_one('SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."customers"', read="snowflake")
-        table = ast.find(exp.Table)
+        ast = parse_one("SHOW TABLES IN db1.schema1", read="snowflake")
+        self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), "db1.schema1")
+
+    def test_show_primary_keys(self):
+        self.validate_identity("SHOW PRIMARY KEYS")
+        self.validate_identity("SHOW PRIMARY KEYS IN ACCOUNT")
+        self.validate_identity("SHOW PRIMARY KEYS IN DATABASE")
+        self.validate_identity("SHOW PRIMARY KEYS IN DATABASE foo")
+        self.validate_identity("SHOW PRIMARY KEYS IN TABLE")
+        self.validate_identity("SHOW PRIMARY KEYS IN TABLE foo")
+        self.validate_identity(
+            'SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."foo"',
+            'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."foo"',
+        )
+        self.validate_identity(
+            'SHOW TERSE PRIMARY KEYS IN "TEST"."PUBLIC"."foo"',
+            'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."foo"',
+        )
 
-        self.assertEqual(table.sql(dialect="snowflake"), '"TEST"."PUBLIC"."customers"')
+        ast = parse_one('SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."foo"', read="snowflake")
+        self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), '"TEST"."PUBLIC"."foo"')
 
-        self.validate_identity("SHOW COLUMNS")
-        self.validate_identity("SHOW COLUMNS IN TABLE dt_test")
-        self.validate_identity("SHOW COLUMNS LIKE '_foo%' IN TABLE dt_test")
-        self.validate_identity("SHOW COLUMNS IN VIEW")
-        self.validate_identity("SHOW COLUMNS LIKE '_foo%' IN VIEW dt_test")
+    def test_show_views(self):
+        self.validate_identity("SHOW TERSE VIEWS")
+        self.validate_identity("SHOW VIEWS")
+        self.validate_identity("SHOW VIEWS LIKE 'foo%'")
+        self.validate_identity("SHOW VIEWS IN ACCOUNT")
+        self.validate_identity("SHOW VIEWS IN DATABASE")
+        self.validate_identity("SHOW VIEWS IN DATABASE foo")
+        self.validate_identity("SHOW VIEWS IN SCHEMA foo")
+        self.validate_identity(
+            "SHOW VIEWS IN foo",
+            "SHOW VIEWS IN SCHEMA foo",
+        )
 
-        self.validate_identity("SHOW USERS")
-        self.validate_identity("SHOW TERSE USERS")
-        self.validate_identity("SHOW USERS LIKE '_foo%' STARTS WITH 'bar' LIMIT 5 FROM 'baz'")
+        ast = parse_one("SHOW VIEWS IN db1.schema1", read="snowflake")
+        self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), "db1.schema1")
 
-        ast = parse_one("SHOW COLUMNS LIKE '_testing%' IN dt_test", read="snowflake")
-        table = ast.find(exp.Table)
-        literal = ast.find(exp.Literal)
+    def test_show_unique_keys(self):
+        self.validate_identity("SHOW UNIQUE KEYS")
+        self.validate_identity("SHOW UNIQUE KEYS IN ACCOUNT")
+        self.validate_identity("SHOW UNIQUE KEYS IN DATABASE")
+        self.validate_identity("SHOW UNIQUE KEYS IN DATABASE foo")
+        self.validate_identity("SHOW UNIQUE KEYS IN TABLE")
+        self.validate_identity("SHOW UNIQUE KEYS IN TABLE foo")
+        self.validate_identity(
+            'SHOW UNIQUE KEYS IN "TEST"."PUBLIC"."foo"',
+            'SHOW UNIQUE KEYS IN SCHEMA "TEST"."PUBLIC"."foo"',
+        )
+        self.validate_identity(
+            'SHOW TERSE UNIQUE KEYS IN "TEST"."PUBLIC"."foo"',
+            'SHOW UNIQUE KEYS IN SCHEMA "TEST"."PUBLIC"."foo"',
+        )
 
-        self.assertEqual(table.sql(dialect="snowflake"), "dt_test")
+        ast = parse_one('SHOW UNIQUE KEYS IN "TEST"."PUBLIC"."foo"', read="snowflake")
+        self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), '"TEST"."PUBLIC"."foo"')
 
-        self.assertEqual(literal.sql(dialect="snowflake"), "'_testing%'")
+    def test_show_imported_keys(self):
+        self.validate_identity("SHOW IMPORTED KEYS")
+        self.validate_identity("SHOW IMPORTED KEYS IN ACCOUNT")
+        self.validate_identity("SHOW IMPORTED KEYS IN DATABASE")
+        self.validate_identity("SHOW IMPORTED KEYS IN DATABASE foo")
+        self.validate_identity("SHOW IMPORTED KEYS IN TABLE")
+        self.validate_identity("SHOW IMPORTED KEYS IN TABLE foo")
+        self.validate_identity(
+            'SHOW IMPORTED KEYS IN "TEST"."PUBLIC"."foo"',
+            'SHOW IMPORTED KEYS IN SCHEMA "TEST"."PUBLIC"."foo"',
+        )
+        self.validate_identity(
+            'SHOW TERSE IMPORTED KEYS IN "TEST"."PUBLIC"."foo"',
+            'SHOW IMPORTED KEYS IN SCHEMA "TEST"."PUBLIC"."foo"',
+        )
 
-        ast = parse_one("SHOW SCHEMAS IN DATABASE db1", read="snowflake")
-        self.assertEqual(ast.args.get("scope_kind"), "DATABASE")
-        table = ast.find(exp.Table)
-        self.assertEqual(table.sql(dialect="snowflake"), "db1")
+        ast = parse_one('SHOW IMPORTED KEYS IN "TEST"."PUBLIC"."foo"', read="snowflake")
+        self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), '"TEST"."PUBLIC"."foo"')
 
-        ast = parse_one("SHOW OBJECTS IN db1.schema1", read="snowflake")
-        self.assertEqual(ast.args.get("scope_kind"), "SCHEMA")
-        table = ast.find(exp.Table)
-        self.assertEqual(table.sql(dialect="snowflake"), "db1.schema1")
+    def test_show_sequences(self):
+        self.validate_identity("SHOW TERSE SEQUENCES")
+        self.validate_identity("SHOW SEQUENCES")
+        self.validate_identity("SHOW SEQUENCES LIKE '_foo%' IN ACCOUNT")
+        self.validate_identity("SHOW SEQUENCES LIKE '_foo%' IN DATABASE")
+        self.validate_identity("SHOW SEQUENCES LIKE '_foo%' IN DATABASE foo")
+        self.validate_identity("SHOW SEQUENCES LIKE '_foo%' IN SCHEMA")
+        self.validate_identity("SHOW SEQUENCES LIKE '_foo%' IN SCHEMA foo")
+        self.validate_identity(
+            "SHOW SEQUENCES LIKE '_foo%' IN foo",
+            "SHOW SEQUENCES LIKE '_foo%' IN SCHEMA foo",
+        )
 
-        ast = parse_one("SHOW TABLES IN db1.schema1", read="snowflake")
+        ast = parse_one("SHOW SEQUENCES IN dt_test", read="snowflake")
         self.assertEqual(ast.args.get("scope_kind"), "SCHEMA")
-        table = ast.find(exp.Table)
-        self.assertEqual(table.sql(dialect="snowflake"), "db1.schema1")
-
-        users_exp = self.validate_identity("SHOW USERS")
-        self.assertTrue(isinstance(users_exp, exp.Show))
-        self.assertEqual(users_exp.this, "USERS")
 
     def test_storage_integration(self):
         self.validate_identity(
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 196735b..1cf1ede 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -16,6 +16,7 @@ class TestSpark(Validator):
         self.validate_identity(
             "CREATE TABLE foo (col STRING) CLUSTERED BY (col) SORTED BY (col) INTO 10 BUCKETS"
         )
+        self.validate_identity("TRUNCATE TABLE t1 PARTITION(age = 10, name = 'test', address)")
 
         self.validate_all(
             "CREATE TABLE db.example_table (col_a struct<struct_col_a:int, struct_col_b:string>)",
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index f7a3dd7..2421987 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -1,5 +1,7 @@
 from tests.dialects.test_dialect import Validator
 
+from sqlglot.helper import logger as helper_logger
+
 
 class TestSQLite(Validator):
     dialect = "sqlite"
@@ -76,6 +78,7 @@ class TestSQLite(Validator):
         self.validate_identity(
             """SELECT item AS "item", some AS "some" FROM data WHERE (item = 'value_1' COLLATE NOCASE) AND (some = 't' COLLATE NOCASE) ORDER BY item ASC LIMIT 1 OFFSET 0"""
         )
+        self.validate_identity("SELECT * FROM GENERATE_SERIES(1, 5)")
 
         self.validate_all("SELECT LIKE(y, x)", write={"sqlite": "SELECT x LIKE y"})
         self.validate_all("SELECT GLOB('*y*', 'xyz')", write={"sqlite": "SELECT 'xyz' GLOB '*y*'"})
@@ -178,3 +181,12 @@ class TestSQLite(Validator):
             "CREATE TABLE foo (bar LONGVARCHAR)",
             write={"sqlite": "CREATE TABLE foo (bar TEXT)"},
         )
+
+    def test_warnings(self):
+        with self.assertLogs(helper_logger) as cm:
+            self.validate_identity(
+                "SELECT * FROM t AS t(c1, c2)",
+                "SELECT * FROM t AS t",
+            )
+
+            self.assertIn("Named columns are not supported in table alias.", cm.output[0])
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index a304a9e..ed474fd 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -1,6 +1,7 @@
 from sqlglot import exp, parse, parse_one
 from sqlglot.parser import logger as parser_logger
 from tests.dialects.test_dialect import Validator
+from sqlglot.errors import ParseError
 
 
 class TestTSQL(Validator):
@@ -27,6 +28,7 @@ class TestTSQL(Validator):
         self.validate_identity("SELECT * FROM t WHERE NOT c", "SELECT * FROM t WHERE NOT c <> 0")
         self.validate_identity("1 AND true", "1 <> 0 AND (1 = 1)")
         self.validate_identity("CAST(x AS int) OR y", "CAST(x AS INTEGER) <> 0 OR y <> 0")
+        self.validate_identity("TRUNCATE TABLE t1 WITH (PARTITIONS(1, 2 TO 5, 10 TO 20, 84))")
 
         self.validate_all(
             "SELECT IIF(cond <> 0, 'True', 'False')",
@@ -142,7 +144,7 @@ class TestTSQL(Validator):
                 "tsql": "CREATE TABLE #mytemptable (a INTEGER)",
                 "snowflake": "CREATE TEMPORARY TABLE mytemptable (a INT)",
                 "duckdb": "CREATE TEMPORARY TABLE mytemptable (a INT)",
-                "oracle": "CREATE TEMPORARY TABLE mytemptable (a NUMBER)",
+                "oracle": "CREATE GLOBAL TEMPORARY TABLE mytemptable (a NUMBER)",
                 "hive": "CREATE TEMPORARY TABLE mytemptable (a INT)",
                 "spark2": "CREATE TEMPORARY TABLE mytemptable (a INT) USING PARQUET",
                 "spark": "CREATE TEMPORARY TABLE mytemptable (a INT) USING PARQUET",
@@ -281,7 +283,7 @@ class TestTSQL(Validator):
             "CONVERT(INT, CONVERT(NUMERIC, '444.75'))",
             write={
                 "mysql": "CAST(CAST('444.75' AS DECIMAL) AS SIGNED)",
-                "tsql": "CAST(CAST('444.75' AS NUMERIC) AS INTEGER)",
+                "tsql": "CONVERT(INTEGER, CONVERT(NUMERIC, '444.75'))",
             },
         )
         self.validate_all(
@@ -356,6 +358,76 @@ class TestTSQL(Validator):
         self.validate_identity("HASHBYTES('MD2', 'x')")
         self.validate_identity("LOG(n, b)")
 
+    def test_option(self):
+        possible_options = [
+            "HASH GROUP",
+            "ORDER GROUP",
+            "CONCAT UNION",
+            "HASH UNION",
+            "MERGE UNION",
+            "LOOP JOIN",
+            "MERGE JOIN",
+            "HASH JOIN",
+            "DISABLE_OPTIMIZED_PLAN_FORCING",
+            "EXPAND VIEWS",
+            "FAST 15",
+            "FORCE ORDER",
+            "FORCE EXTERNALPUSHDOWN",
+            "DISABLE EXTERNALPUSHDOWN",
+            "FORCE SCALEOUTEXECUTION",
+            "DISABLE SCALEOUTEXECUTION",
+            "IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX",
+            "KEEP PLAN",
+            "KEEPFIXED PLAN",
+            "MAX_GRANT_PERCENT = 5",
+            "MIN_GRANT_PERCENT = 10",
+            "MAXDOP 13",
+            "MAXRECURSION 8",
+            "NO_PERFORMANCE_SPOOL",
+            "OPTIMIZE FOR UNKNOWN",
+            "PARAMETERIZATION SIMPLE",
+            "PARAMETERIZATION FORCED",
+            "QUERYTRACEON 99",
+            "RECOMPILE",
+            "ROBUST PLAN",
+            "USE PLAN N'<xml_plan>'",
+            "LABEL = 'MyLabel'",
+        ]
+
+        possible_statements = [
+            # These should be un-commented once support for the OPTION clause is added for DELETE, MERGE and UPDATE
+            # "DELETE FROM Table1",
+            # "MERGE INTO Locations AS T USING locations_stage AS S ON T.LocationID = S.LocationID WHEN MATCHED THEN UPDATE SET LocationName = S.LocationName",
+            # "UPDATE Customers SET ContactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE CustomerID = 1",
+            "SELECT * FROM Table1",
+            "SELECT * FROM Table1 WHERE id = 2",
+        ]
+
+        for statement in possible_statements:
+            for option in possible_options:
+                query = f"{statement} OPTION({option})"
+                result = self.validate_identity(query)
+                options = result.args.get("options")
+                self.assertIsInstance(options, list, f"When parsing query {query}")
+                is_query_options = map(lambda o: isinstance(o, exp.QueryOption), options)
+                self.assertTrue(all(is_query_options), f"When parsing query {query}")
+
+            self.validate_identity(
+                f"{statement} OPTION(RECOMPILE, USE PLAN N'<xml_plan>', MAX_GRANT_PERCENT = 5)"
+            )
+
+        raising_queries = [
+            # Missing parentheses
+            "SELECT * FROM Table1 OPTION HASH GROUP",
+            # Must be followed by 'PLAN"
+            "SELECT * FROM Table1 OPTION(KEEPFIXED)",
+            # Missing commas
+            "SELECT * FROM Table1 OPTION(HASH GROUP HASH GROUP)",
+        ]
+        for query in raising_queries:
+            with self.assertRaises(ParseError, msg=f"When running '{query}'"):
+                self.parse_one(query)
+
     def test_types(self):
         self.validate_identity("CAST(x AS XML)")
         self.validate_identity("CAST(x AS UNIQUEIDENTIFIER)")
@@ -525,7 +597,7 @@ class TestTSQL(Validator):
             "CAST(x as NCHAR(1))",
             write={
                 "spark": "CAST(x AS CHAR(1))",
-                "tsql": "CAST(x AS CHAR(1))",
+                "tsql": "CAST(x AS NCHAR(1))",
             },
         )
 
@@ -533,7 +605,7 @@ class TestTSQL(Validator):
             "CAST(x as NVARCHAR(2))",
             write={
                 "spark": "CAST(x AS VARCHAR(2))",
-                "tsql": "CAST(x AS VARCHAR(2))",
+                "tsql": "CAST(x AS NVARCHAR(2))",
             },
         )
 
@@ -692,12 +764,7 @@ class TestTSQL(Validator):
             "SELECT * INTO foo.bar.baz FROM (SELECT * FROM a.b.c) AS temp",
             read={
                 "": "CREATE TABLE foo.bar.baz AS SELECT * FROM a.b.c",
-            },
-        )
-        self.validate_all(
-            "SELECT * INTO foo.bar.baz FROM (SELECT * FROM a.b.c) AS temp",
-            read={
-                "": "CREATE TABLE foo.bar.baz AS (SELECT * FROM a.b.c)",
+                "duckdb": "CREATE TABLE foo.bar.baz AS (SELECT * FROM a.b.c)",
             },
         )
         self.validate_all(
@@ -759,11 +826,6 @@ class TestTSQL(Validator):
         )
 
     def test_transaction(self):
-        # BEGIN { TRAN | TRANSACTION }
-        #    [ { transaction_name | @tran_name_variable }
-        #    [ WITH MARK [ 'description' ] ]
-        #    ]
-        # [ ; ]
         self.validate_identity("BEGIN TRANSACTION")
         self.validate_all("BEGIN TRAN", write={"tsql": "BEGIN TRANSACTION"})
         self.validate_identity("BEGIN TRANSACTION transaction_name")
@@ -771,8 +833,6 @@ class TestTSQL(Validator):
         self.validate_identity("BEGIN TRANSACTION transaction_name WITH MARK 'description'")
 
     def test_commit(self):
-        # COMMIT [ { TRAN | TRANSACTION }  [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] [ ; ]
-
         self.validate_all("COMMIT", write={"tsql": "COMMIT TRANSACTION"})
         self.validate_all("COMMIT TRAN", write={"tsql": "COMMIT TRANSACTION"})
         self.validate_identity("COMMIT TRANSACTION")
@@ -787,11 +847,6 @@ class TestTSQL(Validator):
         )
 
     def test_rollback(self):
-        # Applies to SQL Server and Azure SQL Database
-        # ROLLBACK { TRAN | TRANSACTION }
-        #     [ transaction_name | @tran_name_variable
-        #     | savepoint_name | @savepoint_variable ]
-        # [ ; ]
         self.validate_all("ROLLBACK", write={"tsql": "ROLLBACK TRANSACTION"})
         self.validate_all("ROLLBACK TRAN", write={"tsql": "ROLLBACK TRANSACTION"})
         self.validate_identity("ROLLBACK TRANSACTION")
@@ -911,7 +966,7 @@ WHERE
 
         expected_sqls = [
             "CREATE PROC [dbo].[transform_proc] AS DECLARE @CurrentDate VARCHAR(20)",
-            "SET @CurrentDate = CAST(FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS VARCHAR(20))",
+            "SET @CurrentDate = CONVERT(VARCHAR(20), GETDATE(), 120)",
             "CREATE TABLE [target_schema].[target_table] (a INTEGER) WITH (DISTRIBUTION=REPLICATE, HEAP)",
         ]
 
@@ -1090,155 +1145,173 @@ WHERE
             },
         )
 
-    def test_convert_date_format(self):
+    def test_convert(self):
         self.validate_all(
             "CONVERT(NVARCHAR(200), x)",
             write={
                 "spark": "CAST(x AS VARCHAR(200))",
+                "tsql": "CONVERT(NVARCHAR(200), x)",
             },
         )
         self.validate_all(
             "CONVERT(NVARCHAR, x)",
             write={
                 "spark": "CAST(x AS VARCHAR(30))",
+                "tsql": "CONVERT(NVARCHAR, x)",
             },
         )
         self.validate_all(
             "CONVERT(NVARCHAR(MAX), x)",
             write={
                 "spark": "CAST(x AS STRING)",
+                "tsql": "CONVERT(NVARCHAR(MAX), x)",
             },
         )
         self.validate_all(
             "CONVERT(VARCHAR(200), x)",
             write={
                 "spark": "CAST(x AS VARCHAR(200))",
+                "tsql": "CONVERT(VARCHAR(200), x)",
             },
         )
         self.validate_all(
             "CONVERT(VARCHAR, x)",
             write={
                 "spark": "CAST(x AS VARCHAR(30))",
+                "tsql": "CONVERT(VARCHAR, x)",
             },
         )
         self.validate_all(
             "CONVERT(VARCHAR(MAX), x)",
             write={
                 "spark": "CAST(x AS STRING)",
+                "tsql": "CONVERT(VARCHAR(MAX), x)",
             },
         )
         self.validate_all(
             "CONVERT(CHAR(40), x)",
             write={
                 "spark": "CAST(x AS CHAR(40))",
+                "tsql": "CONVERT(CHAR(40), x)",
             },
         )
         self.validate_all(
             "CONVERT(CHAR, x)",
             write={
                 "spark": "CAST(x AS CHAR(30))",
+                "tsql": "CONVERT(CHAR, x)",
             },
         )
         self.validate_all(
             "CONVERT(NCHAR(40), x)",
             write={
                 "spark": "CAST(x AS CHAR(40))",
+                "tsql": "CONVERT(NCHAR(40), x)",
             },
         )
         self.validate_all(
             "CONVERT(NCHAR, x)",
             write={
                 "spark": "CAST(x AS CHAR(30))",
+                "tsql": "CONVERT(NCHAR, x)",
             },
         )
         self.validate_all(
             "CONVERT(VARCHAR, x, 121)",
             write={
                 "spark": "CAST(DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS') AS VARCHAR(30))",
+                "tsql": "CONVERT(VARCHAR, x, 121)",
             },
         )
         self.validate_all(
             "CONVERT(VARCHAR(40), x, 121)",
             write={
                 "spark": "CAST(DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS') AS VARCHAR(40))",
+                "tsql": "CONVERT(VARCHAR(40), x, 121)",
             },
         )
         self.validate_all(
             "CONVERT(VARCHAR(MAX), x, 121)",
             write={
-                "spark": "DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS')",
+                "spark": "CAST(DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS') AS STRING)",
+                "tsql": "CONVERT(VARCHAR(MAX), x, 121)",
             },
         )
         self.validate_all(
             "CONVERT(NVARCHAR, x, 121)",
             write={
                 "spark": "CAST(DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS') AS VARCHAR(30))",
+                "tsql": "CONVERT(NVARCHAR, x, 121)",
             },
         )
         self.validate_all(
             "CONVERT(NVARCHAR(40), x, 121)",
             write={
                 "spark": "CAST(DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS') AS VARCHAR(40))",
+                "tsql": "CONVERT(NVARCHAR(40), x, 121)",
             },
         )
         self.validate_all(
             "CONVERT(NVARCHAR(MAX), x, 121)",
             write={
-                "spark": "DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS')",
+                "spark": "CAST(DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS') AS STRING)",
+                "tsql": "CONVERT(NVARCHAR(MAX), x, 121)",
             },
         )
         self.validate_all(
             "CONVERT(DATE, x, 121)",
             write={
                 "spark": "TO_DATE(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS')",
+                "tsql": "CONVERT(DATE, x, 121)",
             },
         )
         self.validate_all(
             "CONVERT(DATETIME, x, 121)",
             write={
                 "spark": "TO_TIMESTAMP(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS')",
+                "tsql": "CONVERT(DATETIME2, x, 121)",
             },
         )
         self.validate_all(
             "CONVERT(DATETIME2, x, 121)",
             write={
                 "spark": "TO_TIMESTAMP(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS')",
+                "tsql": "CONVERT(DATETIME2, x, 121)",
             },
         )
         self.validate_all(
             "CONVERT(INT, x)",
             write={
                 "spark": "CAST(x AS INT)",
+                "tsql": "CONVERT(INTEGER, x)",
             },
         )
         self.validate_all(
             "CONVERT(INT, x, 121)",
             write={
                 "spark": "CAST(x AS INT)",
+                "tsql": "CONVERT(INTEGER, x, 121)",
             },
         )
         self.validate_all(
             "TRY_CONVERT(NVARCHAR, x, 121)",
             write={
                 "spark": "TRY_CAST(DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss.SSSSSS') AS VARCHAR(30))",
+                "tsql": "TRY_CONVERT(NVARCHAR, x, 121)",
             },
         )
         self.validate_all(
             "TRY_CONVERT(INT, x)",
             write={
                 "spark": "TRY_CAST(x AS INT)",
+                "tsql": "TRY_CONVERT(INTEGER, x)",
             },
         )
         self.validate_all(
             "TRY_CAST(x AS INT)",
             write={
                 "spark": "TRY_CAST(x AS INT)",
-            },
-        )
-        self.validate_all(
-            "CAST(x AS INT)",
-            write={
-                "spark": "CAST(x AS INT)",
+                "tsql": "TRY_CAST(x AS INTEGER)",
             },
         )
         self.validate_all(
@@ -1246,6 +1319,7 @@ WHERE
             write={
                 "mysql": "SELECT CAST(DATE_FORMAT(testdb.dbo.test.x, '%Y-%m-%d %T') AS CHAR(10)) AS y FROM testdb.dbo.test",
                 "spark": "SELECT CAST(DATE_FORMAT(testdb.dbo.test.x, 'yyyy-MM-dd HH:mm:ss') AS VARCHAR(10)) AS y FROM testdb.dbo.test",
+                "tsql": "SELECT CONVERT(VARCHAR(10), testdb.dbo.test.x, 120) AS y FROM testdb.dbo.test",
             },
         )
         self.validate_all(
@@ -1253,12 +1327,14 @@ WHERE
             write={
                 "mysql": "SELECT CAST(y.x AS CHAR(10)) AS z FROM testdb.dbo.test AS y",
                 "spark": "SELECT CAST(y.x AS VARCHAR(10)) AS z FROM testdb.dbo.test AS y",
+                "tsql": "SELECT CONVERT(VARCHAR(10), y.x) AS z FROM testdb.dbo.test AS y",
             },
         )
         self.validate_all(
             "SELECT CAST((SELECT x FROM y) AS VARCHAR) AS test",
             write={
                 "spark": "SELECT CAST((SELECT x FROM y) AS STRING) AS test",
+                "tsql": "SELECT CAST((SELECT x FROM y) AS VARCHAR) AS test",
             },
         )
 
@@ -1654,7 +1730,7 @@ FROM OPENJSON(@json) WITH (
     Date DATETIME2 '$.Order.Date',
     Customer VARCHAR(200) '$.AccountNumber',
     Quantity INTEGER '$.Item.Quantity',
-    [Order] VARCHAR(MAX) AS JSON
+    [Order] NVARCHAR(MAX) AS JSON
 )"""
             },
             pretty=True,