Repository: tobymao/sqlglot

Issue:
teradata `AS FORMAT` support
**Fully reproducible code snippet**
This query should parse properly.
```
select CAST(TimestampCol AS FORMAT 'YYYY-MM-DD') FROM dbc.table
```
There's a lot of other potential formats, e.g. '$$9.99'	or 'E4'.

**Official Documentation**
- https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Data-Types-and-Literals/Data-Type-Formats-and-Format-Phrases/FORMAT-Phrase-and-DateTime-Formats
- https://docs.teradata.com/r/Teradata-Warehouse-Miner-User-Guide-Volume-2ADS-Generation/February-2018/Analytic-Data-Sets/Variable-Creation/Initiating-a-Variable-Creation-Function/Variable-Creation-INPUT-Variables-SQL-Elements/Other/Cast-Function
- https://docs.teradata.com/r/SQL-Functions-Operators-Expressions-and-Predicates/June-2017/Data-Type-Conversions/Period-to-DATE-Conversion/CAST-Syntax


Discussion:

Patch:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index fba18f85..d6e874ef 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -246,6 +246,7 @@ class Generator:
         exp.DataType.Type.LONGBLOB: "BLOB",
         exp.DataType.Type.TINYBLOB: "BLOB",
         exp.DataType.Type.INET: "INET",
+        exp.DataType.Type.UNKNOWN: "",
     }
 
     STAR_MAPPING = {
@@ -2400,7 +2401,9 @@ class Generator:
     def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
         format_sql = self.sql(expression, "format")
         format_sql = f" FORMAT {format_sql}" if format_sql else ""
-        return f"{safe_prefix or ''}CAST({self.sql(expression, 'this')} AS {self.sql(expression, 'to')}{format_sql})"
+        to_sql = self.sql(expression, "to")
+        to_sql = f" {to_sql}" if to_sql else ""
+        return f"{safe_prefix or ''}CAST({self.sql(expression, 'this')} AS{to_sql}{format_sql})"
 
     def currentdate_sql(self, expression: exp.CurrentDate) -> str:
         zone = self.sql(expression, "this")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 7ff0aabf..1dab6000 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -4246,17 +4246,12 @@ class Parser(metaclass=_Parser):
         fmt = None
         to = self._parse_types()
 
-        if not to:
-            self.raise_error("Expected TYPE after CAST")
-        elif isinstance(to, exp.Identifier):
-            to = exp.DataType.build(to.name, udt=True)
-        elif to.this == exp.DataType.Type.CHAR:
-            if self._match(TokenType.CHARACTER_SET):
-                to = self.expression(exp.CharacterSet, this=self._parse_var_or_string())
-        elif self._match(TokenType.FORMAT):
+        if self._match(TokenType.FORMAT):
             fmt_string = self._parse_string()
             fmt = self._parse_at_time_zone(fmt_string)
 
+            if not to:
+                to = exp.DataType.build(exp.DataType.Type.UNKNOWN)
             if to.this in exp.DataType.TEMPORAL_TYPES:
                 this = self.expression(
                     exp.StrToDate if to.this == exp.DataType.Type.DATE else exp.StrToTime,
@@ -4272,8 +4267,14 @@ class Parser(metaclass=_Parser):
 
                 if isinstance(fmt, exp.AtTimeZone) and isinstance(this, exp.StrToTime):
                     this.set("zone", fmt.args["zone"])
-
                 return this
+        elif not to:
+            self.raise_error("Expected TYPE after CAST")
+        elif isinstance(to, exp.Identifier):
+            to = exp.DataType.build(to.name, udt=True)
+        elif to.this == exp.DataType.Type.CHAR:
+            if self._match(TokenType.CHARACTER_SET):
+                to = self.expression(exp.CharacterSet, this=self._parse_var_or_string())
 
         return self.expression(
             exp.Cast if strict else exp.TryCast, this=this, to=to, format=fmt, safe=safe


Test patch:
diff --git a/tests/dialects/test_teradata.py b/tests/dialects/test_teradata.py
index 50d44229..7348806e 100644
--- a/tests/dialects/test_teradata.py
+++ b/tests/dialects/test_teradata.py
@@ -188,3 +188,4 @@ class TestTeradata(Validator):
                 "": "STR_TO_DATE('1992-01', '%Y-%d')",
             },
         )
+        self.validate_identity("CAST('1992-01' AS FORMAT 'YYYY-DD')")
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 6c489433..2dae2886 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -443,7 +443,7 @@ class TestExpressions(unittest.TestCase):
                 return None
             return node
 
-        self.assertEqual(expression.transform(remove_non_list_arg).sql(), "CAST(x AS )")
+        self.assertEqual(expression.transform(remove_non_list_arg).sql(), "CAST(x AS)")
 
         expression = parse_one("SELECT a, b FROM x")
 
@@ -855,16 +855,16 @@ FROM foo""",
         self.assertEqual(exp.DataType.build("HSTORE", dialect="postgres").sql(), "HSTORE")
         self.assertEqual(exp.DataType.build("NULL").sql(), "NULL")
         self.assertEqual(exp.DataType.build("NULL", dialect="bigquery").sql(), "NULL")
-        self.assertEqual(exp.DataType.build("UNKNOWN").sql(), "UNKNOWN")
-        self.assertEqual(exp.DataType.build("UNKNOWN", dialect="bigquery").sql(), "UNKNOWN")
-        self.assertEqual(exp.DataType.build("UNKNOWN", dialect="snowflake").sql(), "UNKNOWN")
+        self.assertEqual(exp.DataType.build("UNKNOWN").sql(), "")
+        self.assertEqual(exp.DataType.build("UNKNOWN", dialect="bigquery").sql(), "")
+        self.assertEqual(exp.DataType.build("UNKNOWN", dialect="snowflake").sql(), "")
         self.assertEqual(exp.DataType.build("TIMESTAMP", dialect="bigquery").sql(), "TIMESTAMPTZ")
         self.assertEqual(
             exp.DataType.build("struct<x int>", dialect="spark").sql(), "STRUCT<x INT>"
         )
         self.assertEqual(exp.DataType.build("USER-DEFINED").sql(), "USER-DEFINED")
 
-        self.assertEqual(exp.DataType.build("ARRAY<UNKNOWN>").sql(), "ARRAY<UNKNOWN>")
+        self.assertEqual(exp.DataType.build("ARRAY<UNKNOWN>").sql(), "ARRAY")
         self.assertEqual(exp.DataType.build("ARRAY<NULL>").sql(), "ARRAY<NULL>")
         self.assertEqual(exp.DataType.build("varchar(100) collate 'en-ci'").sql(), "VARCHAR(100)")