Repository: tobymao/sqlglot

Issue:
parsing `struct(number bigint)` as a DataType treats number as a data type
**Fully reproducible code snippet**

```
In [11]: import sqlglot as sg

In [12]: sg.__version__
Out[12]: '17.12.0'

In [13]: sg.parse_one("struct(number bigint)", read="duckdb", into=sg.exp.DataType)
Out[13]:
(DATATYPE this: Type.STRUCT, expressions:
  (DATATYPE this: Type.DECIMAL, expressions:
    (DATATYPESIZE this:
      (LITERAL this: 18, is_string: False)),
    (DATATYPESIZE this:
      (LITERAL this: 3, is_string: False)), nested: False, prefix: False), nested: True, prefix: False)
```

This is part of the schema inferred from running DuckDB's `read_json_auto` on a response from the [GitHub issues REST API](https://docs.github.com/en/free-pro-team@latest/rest/issues/issues?apiVersion=2022-11-28) 

**Official Documentation**

N/A


Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index efe3c6e8..9234153e 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -321,6 +321,13 @@ class ClickHouse(Dialect):
             exp.DataType.Type.UINT256: "UInt256",
             exp.DataType.Type.USMALLINT: "UInt16",
             exp.DataType.Type.UTINYINT: "UInt8",
+            exp.DataType.Type.CHAR: "String",
+            exp.DataType.Type.VARCHAR: "String",
+            exp.DataType.Type.LONGTEXT: "String",
+            exp.DataType.Type.MEDIUMTEXT: "String",
+            exp.DataType.Type.TEXT: "String",
+            exp.DataType.Type.LONGBLOB: "String",
+            exp.DataType.Type.MEDIUMBLOB: "String",
         }
 
         TRANSFORMS = {
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/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..0c576f67 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -48,7 +48,7 @@ class TestClickhouse(Validator):
         self.validate_identity("position(haystack, needle, position)")
         self.validate_identity("CAST(x AS DATETIME)")
         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 +95,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 +233,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 +283,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 +305,7 @@ ORDER BY tuple()""",
                 "clickhouse": """CREATE TABLE test (
   id UInt64,
   timestamp DateTime64,
-  data TEXT,
+  data String,
   max_hits UInt64,
   sum_hits UInt64
 )
@@ -332,8 +332,8 @@ SET
             """,
             write={
                 "clickhouse": """CREATE TABLE test (
-  id TEXT,
-  data TEXT
+  id String,
+  data String
 )
 ENGINE=AggregatingMergeTree()
 ORDER BY tuple()
@@ -416,7 +416,7 @@ WHERE
                 "clickhouse": """CREATE TABLE table_for_recompression (
   d DATETIME,
   key UInt64,
-  value TEXT
+  value String
 )
 ENGINE=MergeTree()
 ORDER BY tuple()
@@ -512,9 +512,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 +540,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..93aaf774 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)",
@@ -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))",