Repository: tobymao/sqlglot

Issue:
TSQL Dialect Yields Invalid Syntax when Creating Indexes and Replacing Data
Creating Indexes:
`>>> sqlglot.transpile('CREATE SCHEMA IF NOT EXISTS "sqlmesh_example"', 'mysql', 'tsql')`

`['CREATE SCHEMA IF NOT EXISTS "sqlmesh_example"']`

[Docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-ver16)

Expected result: 
`IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE SCHEMA_NAME = 'sqlmesh_example') EXEC('CREATE SCHEMA "sqlmesh_example"')`

Replacing Data:
`>>> sqlglot.transpile("""INSERT INTO "test_table" ("a", "ds") REPLACE WHERE "ds" BETWEEN '2022-01-01' AND '2022-01-02' SELECT * FROM (SELECT CAST("a" AS INTEGER) AS "a", CAST("ds" AS TEXT) AS "ds" FROM (VALUES (1, '2022-01-01'), (2, '2022-01-02')) AS "test_table"("a", "ds")) AS "_subquery" WHERE "ds" BETWEEN '2022-01-01' AND '2022-01-02'""", 'mysql', 'tsql')`

`['INSERT INTO "test_table" (\'a\', \'ds\') REPLACE WHERE \'ds\' BETWEEN \'2022-01-01\' AND \'2022-01-02\' SELECT * FROM (SELECT CAST(\'a\' AS INTEGER) AS "a", 
CAST(\'ds\' AS TEXT) AS "ds" FROM (VALUES (1, \'2022-01-01\'), (2, \'2022-01-02\')) AS "test_table"("a", "ds")) AS "_subquery" WHERE \'ds\' BETWEEN \'2022-01-01\' AND \'2022-01-02\'']`

Not sure which would be the best SQL Server flavor but here are a few docs:
[Update/Delete/Insert](https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/update-delete-and-insert-statements?view=sql-server-ver16)
[Merge](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16)
[Switch Partition](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16#c-switch-partitions-between-tables)

Possible expected result: 
`DELETE FROM "test_table" WHERE "ds" BETWEEN '2022-01-01' AND '2022-01-02'`

`INSERT INTO "test_table" ("a", "ds") SELECT * FROM (SELECT CAST("a" AS INTEGER) AS "a", CAST("ds" AS TEXT) AS "ds" FROM (VALUES (1, '2022-01-01'), (2, '2022-01-02')) AS "test_table"("a", "ds")) AS "_subquery" WHERE "ds" BETWEEN '2022-01-01' AND '2022-01-02'`

Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index efe3c6e8..23b63fe4 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -294,8 +294,20 @@ class ClickHouse(Dialect):
         STRUCT_DELIMITER = ("(", ")")
         NVL2_SUPPORTED = False
 
+        STRING_TYPE_MAPPING = {
+            exp.DataType.Type.CHAR: "String",
+            exp.DataType.Type.LONGBLOB: "String",
+            exp.DataType.Type.LONGTEXT: "String",
+            exp.DataType.Type.MEDIUMBLOB: "String",
+            exp.DataType.Type.MEDIUMTEXT: "String",
+            exp.DataType.Type.TEXT: "String",
+            exp.DataType.Type.VARBINARY: "String",
+            exp.DataType.Type.VARCHAR: "String",
+        }
+
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,
+            **STRING_TYPE_MAPPING,
             exp.DataType.Type.ARRAY: "Array",
             exp.DataType.Type.BIGINT: "Int64",
             exp.DataType.Type.DATETIME64: "DateTime64",
@@ -365,6 +377,16 @@ class ClickHouse(Dialect):
             "NAMED COLLECTION",
         }
 
+        def datatype_sql(self, expression: exp.DataType) -> str:
+            # String is the standard ClickHouse type, every other variant is just an alias.
+            # Additionally, any supplied length parameter will be ignored.
+            #
+            # https://clickhouse.com/docs/en/sql-reference/data-types/string
+            if expression.this in self.STRING_TYPE_MAPPING:
+                return "String"
+
+            return super().datatype_sql(expression)
+
         def safeconcat_sql(self, expression: exp.SafeConcat) -> str:
             # Clickhouse errors out if we try to cast a NULL value to TEXT
             expression = expression.copy()
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index dea7f26a..eb891a16 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -207,6 +207,9 @@ class DuckDB(Dialect):
 
             return this
 
+        def _parse_struct_types(self) -> t.Optional[exp.Expression]:
+            return self._parse_field_def()
+
         def _pivot_column_names(self, aggregations: t.List[exp.Expression]) -> t.List[str]:
             if len(aggregations) == 1:
                 return super()._pivot_column_names(aggregations)
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index eb3a1580..c43c020d 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -22,7 +22,7 @@ def _parse_xml_table(self: parser.Parser) -> exp.XMLTable:
     by_ref = self._match_text_seq("RETURNING", "SEQUENCE", "BY", "REF")
 
     if self._match_text_seq("COLUMNS"):
-        columns = self._parse_csv(lambda: self._parse_column_def(self._parse_field(any_token=True)))
+        columns = self._parse_csv(self._parse_field_def)
 
     return self.expression(exp.XMLTable, this=this, passing=passing, columns=columns, by_ref=by_ref)
 
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index 1f5c351b..1470455c 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -666,6 +666,16 @@ class TSQL(Dialect):
 
             return sql
 
+        def create_sql(self, expression: exp.Create) -> str:
+            kind = self.sql(expression, "kind").upper()
+            exists = expression.args.get("exists")
+
+            if exists and kind == "SCHEMA":
+                schema_name = self.sql(expression, "this")
+                return f"IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE SCHEMA_NAME = {schema_name}) EXEC('CREATE SCHEMA {schema_name}')"
+
+            return super().create_sql(expression)
+
         def offset_sql(self, expression: exp.Offset) -> str:
             return f"{super().offset_sql(expression)} ROWS"
 
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 24f12cba..c1d86954 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3489,14 +3489,14 @@ class Parser(metaclass=_Parser):
         if not self._match(TokenType.L_PAREN):
             return this
 
-        args = self._parse_csv(
-            lambda: self._parse_constraint()
-            or self._parse_column_def(self._parse_field(any_token=True))
-        )
+        args = self._parse_csv(lambda: self._parse_constraint() or self._parse_field_def())
 
         self._match_r_paren()
         return self.expression(exp.Schema, this=this, expressions=args)
 
+    def _parse_field_def(self) -> t.Optional[exp.Expression]:
+        return self._parse_column_def(self._parse_field(any_token=True))
+
     def _parse_column_def(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]:
         # column defs are not really columns, they're identifiers
         if isinstance(this, exp.Column):
@@ -4506,7 +4506,7 @@ class Parser(metaclass=_Parser):
 
         self._match(TokenType.COLUMN)
         exists_column = self._parse_exists(not_=True)
-        expression = self._parse_column_def(self._parse_field(any_token=True))
+        expression = self._parse_field_def()
 
         if expression:
             expression.set("exists", exists_column)


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 583be3ef..c014aa0e 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -47,8 +47,10 @@ class TestClickhouse(Validator):
         self.validate_identity("position(haystack, needle)")
         self.validate_identity("position(haystack, needle, position)")
         self.validate_identity("CAST(x AS DATETIME)")
+        self.validate_identity("CAST(x AS VARCHAR(255))", "CAST(x AS String)")
+        self.validate_identity("CAST(x AS BLOB)", "CAST(x AS String)")
         self.validate_identity(
-            'SELECT CAST(tuple(1 AS "a", 2 AS "b", 3.0 AS "c").2 AS Nullable(TEXT))'
+            'SELECT CAST(tuple(1 AS "a", 2 AS "b", 3.0 AS "c").2 AS Nullable(String))'
         )
         self.validate_identity(
             "CREATE TABLE test (id UInt8) ENGINE=AggregatingMergeTree() ORDER BY tuple()"
@@ -95,11 +97,11 @@ class TestClickhouse(Validator):
             },
         )
         self.validate_all(
-            "CONCAT(CASE WHEN COALESCE(CAST(a AS TEXT), '') IS NULL THEN COALESCE(CAST(a AS TEXT), '') ELSE CAST(COALESCE(CAST(a AS TEXT), '') AS TEXT) END, CASE WHEN COALESCE(CAST(b AS TEXT), '') IS NULL THEN COALESCE(CAST(b AS TEXT), '') ELSE CAST(COALESCE(CAST(b AS TEXT), '') AS TEXT) END)",
+            "CONCAT(CASE WHEN COALESCE(CAST(a AS String), '') IS NULL THEN COALESCE(CAST(a AS String), '') ELSE CAST(COALESCE(CAST(a AS String), '') AS String) END, CASE WHEN COALESCE(CAST(b AS String), '') IS NULL THEN COALESCE(CAST(b AS String), '') ELSE CAST(COALESCE(CAST(b AS String), '') AS String) END)",
             read={"postgres": "CONCAT(a, b)"},
         )
         self.validate_all(
-            "CONCAT(CASE WHEN a IS NULL THEN a ELSE CAST(a AS TEXT) END, CASE WHEN b IS NULL THEN b ELSE CAST(b AS TEXT) END)",
+            "CONCAT(CASE WHEN a IS NULL THEN a ELSE CAST(a AS String) END, CASE WHEN b IS NULL THEN b ELSE CAST(b AS String) END)",
             read={"mysql": "CONCAT(a, b)"},
         )
         self.validate_all(
@@ -233,7 +235,7 @@ class TestClickhouse(Validator):
         self.validate_all(
             "SELECT {abc: UInt32}, {b: String}, {c: DateTime},{d: Map(String, Array(UInt8))}, {e: Tuple(UInt8, String)}",
             write={
-                "clickhouse": "SELECT {abc: UInt32}, {b: TEXT}, {c: DATETIME}, {d: Map(TEXT, Array(UInt8))}, {e: Tuple(UInt8, String)}",
+                "clickhouse": "SELECT {abc: UInt32}, {b: String}, {c: DATETIME}, {d: Map(String, Array(UInt8))}, {e: Tuple(UInt8, String)}",
                 "": "SELECT :abc, :b, :c, :d, :e",
             },
         )
@@ -283,8 +285,8 @@ class TestClickhouse(Validator):
                 "clickhouse": """CREATE TABLE example1 (
   timestamp DATETIME,
   x UInt32 TTL now() + INTERVAL '1' MONTH,
-  y TEXT TTL timestamp + INTERVAL '1' DAY,
-  z TEXT
+  y String TTL timestamp + INTERVAL '1' DAY,
+  z String
 )
 ENGINE=MergeTree
 ORDER BY tuple()""",
@@ -305,7 +307,7 @@ ORDER BY tuple()""",
                 "clickhouse": """CREATE TABLE test (
   id UInt64,
   timestamp DateTime64,
-  data TEXT,
+  data String,
   max_hits UInt64,
   sum_hits UInt64
 )
@@ -332,8 +334,8 @@ SET
             """,
             write={
                 "clickhouse": """CREATE TABLE test (
-  id TEXT,
-  data TEXT
+  id String,
+  data String
 )
 ENGINE=AggregatingMergeTree()
 ORDER BY tuple()
@@ -416,7 +418,7 @@ WHERE
                 "clickhouse": """CREATE TABLE table_for_recompression (
   d DATETIME,
   key UInt64,
-  value TEXT
+  value String
 )
 ENGINE=MergeTree()
 ORDER BY tuple()
@@ -512,9 +514,9 @@ RANGE(MIN discount_start_date MAX discount_end_date)""",
             """,
             write={
                 "clickhouse": """CREATE DICTIONARY my_ip_trie_dictionary (
-  prefix TEXT,
+  prefix String,
   asn UInt32,
-  cca2 TEXT DEFAULT '??'
+  cca2 String DEFAULT '??'
 )
 PRIMARY KEY (prefix)
 SOURCE(CLICKHOUSE(
@@ -540,7 +542,7 @@ LIFETIME(MIN 0 MAX 3600)""",
             write={
                 "clickhouse": """CREATE DICTIONARY polygons_test_dictionary (
   key Array(Array(Array(Tuple(Float64, Float64)))),
-  name TEXT
+  name String
 )
 PRIMARY KEY (key)
 SOURCE(CLICKHOUSE(
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 100f4a06..5f2afd75 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -78,7 +78,7 @@ class TestDialect(Validator):
             "CAST(a AS TEXT)",
             write={
                 "bigquery": "CAST(a AS STRING)",
-                "clickhouse": "CAST(a AS TEXT)",
+                "clickhouse": "CAST(a AS String)",
                 "drill": "CAST(a AS VARCHAR)",
                 "duckdb": "CAST(a AS TEXT)",
                 "mysql": "CAST(a AS CHAR)",
@@ -116,7 +116,7 @@ class TestDialect(Validator):
             "CAST(a AS VARBINARY(4))",
             write={
                 "bigquery": "CAST(a AS BYTES)",
-                "clickhouse": "CAST(a AS VARBINARY(4))",
+                "clickhouse": "CAST(a AS String)",
                 "duckdb": "CAST(a AS BLOB(4))",
                 "mysql": "CAST(a AS VARBINARY(4))",
                 "hive": "CAST(a AS BINARY(4))",
@@ -133,7 +133,7 @@ class TestDialect(Validator):
         self.validate_all(
             "CAST(MAP('a', '1') AS MAP(TEXT, TEXT))",
             write={
-                "clickhouse": "CAST(map('a', '1') AS Map(TEXT, TEXT))",
+                "clickhouse": "CAST(map('a', '1') AS Map(String, String))",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index c800e589..99a331ac 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -508,6 +508,10 @@ class TestDuckDB(Validator):
         self.validate_identity("CAST(x AS INT128)")
         self.validate_identity("CAST(x AS DOUBLE)")
         self.validate_identity("CAST(x AS DECIMAL(15, 4))")
+        self.validate_identity("CAST(x AS STRUCT(number BIGINT))")
+        self.validate_identity(
+            "CAST(ROW(1, ROW(1)) AS STRUCT(number BIGINT, row STRUCT(number BIGINT)))"
+        )
 
         self.validate_all("CAST(x AS NUMERIC(1, 2))", write={"duckdb": "CAST(x AS DECIMAL(1, 2))"})
         self.validate_all("CAST(x AS HUGEINT)", write={"duckdb": "CAST(x AS INT128)"})
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 530522da..11740239 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -608,7 +608,7 @@ class TestPostgres(Validator):
             "a || b",
             write={
                 "": "a || b",
-                "clickhouse": "CONCAT(CAST(a AS TEXT), CAST(b AS TEXT))",
+                "clickhouse": "CONCAT(CAST(a AS String), CAST(b AS String))",
                 "duckdb": "a || b",
                 "postgres": "a || b",
                 "presto": "CONCAT(CAST(a AS VARCHAR), CAST(b AS VARCHAR))",
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index a3fba58f..8d3c7d70 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -410,6 +410,12 @@ class TestTSQL(Validator):
         )
 
     def test_ddl(self):
+        self.validate_all(
+            "IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE SCHEMA_NAME = foo) EXEC('CREATE SCHEMA foo')",
+            read={
+                "": "CREATE SCHEMA IF NOT EXISTS foo",
+            },
+        )
         self.validate_all(
             "CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIME(4), d FLOAT(24))",
             write={