Repository: tobymao/sqlglot

Issue:
(hive) hive parsing error, date addition and subtraction are parsed into aliases
`sqlglot.parse_one("""cast('1998-01-06' as date) + 30 days""", "hive")
TRY_CAST('1998-01-06' AS DATE) + 30 AS days

The syntax tree is as follows:
Parse days into an alias

[(ALIAS this: 
  (ADD this: 
    (TRYCAST this: 
      (LITERAL this: 1998-01-06, is_string: True), to: 
      (DATATYPE this: Type.DATE, nested: False, prefix: False)), expression: 
    (LITERAL this: 30, is_string: False)), alias: 
  (IDENTIFIER this: days, quoted: False))]
`



Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 7c4e051e..c0b3daf8 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -397,6 +397,27 @@ class BigQuery(Dialect):
 
             return table
 
+        def _parse_json_object(self) -> exp.JSONObject:
+            json_object = super()._parse_json_object()
+            array_kv_pair = seq_get(json_object.expressions, 0)
+
+            # Converts BQ's "signature 2" of JSON_OBJECT into SQLGlot's canonical representation
+            # https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_object_signature2
+            if (
+                array_kv_pair
+                and isinstance(array_kv_pair.this, exp.Array)
+                and isinstance(array_kv_pair.expression, exp.Array)
+            ):
+                keys = array_kv_pair.this.expressions
+                values = array_kv_pair.expression.expressions
+
+                json_object.set(
+                    "expressions",
+                    [exp.JSONKeyValue(this=k, expression=v) for k, v in zip(keys, values)],
+                )
+
+            return json_object
+
     class Generator(generator.Generator):
         EXPLICIT_UNION = True
         INTERVAL_ALLOWS_PLURAL_FORM = False
@@ -619,6 +640,13 @@ class BigQuery(Dialect):
 
             return super().attimezone_sql(expression)
 
+        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
+            # https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#json_literals
+            if expression.is_type("json"):
+                return f"JSON {self.sql(expression, 'this')}"
+
+            return super().cast_sql(expression, safe_prefix=safe_prefix)
+
         def trycast_sql(self, expression: exp.TryCast) -> str:
             return self.cast_sql(expression, safe_prefix="SAFE_")
 
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 6bb6cfbb..61d87bb8 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -47,9 +47,31 @@ TIME_DIFF_FACTOR = {
     "HOUR": " / 3600",
 }
 
+INTERVAL_VARS = {
+    "SECOND",
+    "SECONDS",
+    "MINUTE",
+    "MINUTES",
+    "DAY",
+    "DAYS",
+    "MONTH",
+    "MONTHS",
+    "YEAR",
+    "YEARS",
+}
+
+
 DIFF_MONTH_SWITCH = ("YEAR", "QUARTER", "MONTH")
 
 
+def _parse_number(self: Hive.Parser, token: TokenType) -> t.Optional[exp.Expression]:
+    number = super(type(self), self).PRIMARY_PARSERS[TokenType.NUMBER](self, token)
+    if self._match(TokenType.VAR, advance=False) and self._curr.text.upper() in INTERVAL_VARS:
+        return exp.Interval(this=number, unit=self._parse_var())
+
+    return number
+
+
 def _add_date_sql(self: generator.Generator, expression: exp.DateAdd | exp.DateSub) -> str:
     unit = expression.text("unit").upper()
     func, multiplier = DATE_DELTA_INTERVAL.get(unit, ("DATE_ADD", 1))
@@ -284,6 +306,11 @@ class Hive(Dialect):
             ),
         }
 
+        PRIMARY_PARSERS = {
+            **parser.Parser.PRIMARY_PARSERS,
+            TokenType.NUMBER: _parse_number,
+        }
+
         def _parse_transform(self) -> t.Optional[exp.Transform | exp.QueryTransform]:
             if not self._match(TokenType.L_PAREN, advance=False):
                 self._retreat(self._index - 1)
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 139282ae..154784a2 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -275,6 +275,18 @@ class Postgres(Dialect):
             "SMALLSERIAL": TokenType.SMALLSERIAL,
             "TEMP": TokenType.TEMPORARY,
             "CSTRING": TokenType.PSEUDO_TYPE,
+            "OID": TokenType.OBJECT_IDENTIFIER,
+            "REGCLASS": TokenType.OBJECT_IDENTIFIER,
+            "REGCOLLATION": TokenType.OBJECT_IDENTIFIER,
+            "REGCONFIG": TokenType.OBJECT_IDENTIFIER,
+            "REGDICTIONARY": TokenType.OBJECT_IDENTIFIER,
+            "REGNAMESPACE": TokenType.OBJECT_IDENTIFIER,
+            "REGOPER": TokenType.OBJECT_IDENTIFIER,
+            "REGOPERATOR": TokenType.OBJECT_IDENTIFIER,
+            "REGPROC": TokenType.OBJECT_IDENTIFIER,
+            "REGPROCEDURE": TokenType.OBJECT_IDENTIFIER,
+            "REGROLE": TokenType.OBJECT_IDENTIFIER,
+            "REGTYPE": TokenType.OBJECT_IDENTIFIER,
         }
 
         SINGLE_TOKENS = {
diff --git a/sqlglot/dialects/spark2.py b/sqlglot/dialects/spark2.py
index f5c00ae0..1d1d77f0 100644
--- a/sqlglot/dialects/spark2.py
+++ b/sqlglot/dialects/spark2.py
@@ -2,7 +2,7 @@ from __future__ import annotations
 
 import typing as t
 
-from sqlglot import exp, transforms
+from sqlglot import exp, parser, transforms
 from sqlglot.dialects.dialect import (
     binary_from_function,
     create_with_partitions_sql,
@@ -165,6 +165,9 @@ class Spark2(Hive):
             "SHUFFLE_REPLICATE_NL": lambda self: self._parse_join_hint("SHUFFLE_REPLICATE_NL"),
         }
 
+        # We dont' want to inherit Hive's TokenType.NUMBER override
+        PRIMARY_PARSERS = parser.Parser.PRIMARY_PARSERS.copy()
+
         def _parse_add_column(self) -> t.Optional[exp.Expression]:
             return self._match_text_seq("ADD", "COLUMNS") and self._parse_schema()
 
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index d4402a58..7103b9c8 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -3665,6 +3665,11 @@ class PseudoType(Expression):
     pass
 
 
+# https://www.postgresql.org/docs/15/datatype-oid.html
+class ObjectIdentifier(Expression):
+    pass
+
+
 # WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
 class SubqueryPredicate(Predicate):
     pass
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 3b1d76e9..306df812 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1224,6 +1224,9 @@ class Generator:
     def pseudotype_sql(self, expression: exp.PseudoType) -> str:
         return expression.name.upper()
 
+    def objectidentifier_sql(self, expression: exp.ObjectIdentifier) -> str:
+        return expression.name.upper()
+
     def onconflict_sql(self, expression: exp.OnConflict) -> str:
         conflict = "ON DUPLICATE KEY" if expression.args.get("duplicate") else "ON CONFLICT"
         constraint = self.sql(expression, "constraint")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 210a7b3e..214998c7 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -196,6 +196,7 @@ class Parser(metaclass=_Parser):
         TokenType.IMAGE,
         TokenType.VARIANT,
         TokenType.OBJECT,
+        TokenType.OBJECT_IDENTIFIER,
         TokenType.INET,
         TokenType.IPADDRESS,
         TokenType.IPPREFIX,
@@ -3261,6 +3262,9 @@ class Parser(metaclass=_Parser):
         if type_token == TokenType.PSEUDO_TYPE:
             return self.expression(exp.PseudoType, this=self._prev.text)
 
+        if type_token == TokenType.OBJECT_IDENTIFIER:
+            return self.expression(exp.ObjectIdentifier, this=self._prev.text)
+
         nested = type_token in self.NESTED_TYPE_TOKENS
         is_struct = type_token in self.STRUCT_TYPE_TOKENS
         expressions = None
@@ -4144,7 +4148,7 @@ class Parser(metaclass=_Parser):
 
     def _parse_json_key_value(self) -> t.Optional[exp.JSONKeyValue]:
         self._match_text_seq("KEY")
-        key = self._parse_field()
+        key = self._parse_column()
         self._match_set((TokenType.COLON, TokenType.COMMA))
         self._match_text_seq("VALUE")
         value = self._parse_column()
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 57848a88..6be22643 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -261,6 +261,7 @@ class TokenType(AutoName):
     NEXT = auto()
     NOTNULL = auto()
     NULL = auto()
+    OBJECT_IDENTIFIER = auto()
     OFFSET = auto()
     ON = auto()
     ORDER_BY = auto()


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 8f6dc3f6..b776bdd3 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -9,6 +9,7 @@ class TestBigQuery(Validator):
     maxDiff = None
 
     def test_bigquery(self):
+        self.validate_identity("""SELECT JSON '"foo"' AS json_data""")
         self.validate_identity("SELECT * FROM tbl FOR SYSTEM_TIME AS OF z")
 
         self.validate_all(
@@ -789,5 +790,22 @@ WHERE
         self.validate_identity("SELECT JSON_OBJECT('foo', 10, 'bar', TRUE) AS json_data")
         self.validate_identity("SELECT JSON_OBJECT('foo', 10, 'bar', ['a', 'b']) AS json_data")
         self.validate_identity("SELECT JSON_OBJECT('a', 10, 'a', 'foo') AS json_data")
+        self.validate_identity(
+            "SELECT JSON_OBJECT(['a', 'b'], [10, NULL]) AS json_data",
+            "SELECT JSON_OBJECT('a', 10, 'b', NULL) AS json_data",
+        )
+        self.validate_identity(
+            """SELECT JSON_OBJECT(['a', 'b'], [JSON '10', JSON '"foo"']) AS json_data""",
+            """SELECT JSON_OBJECT('a', JSON '10', 'b', JSON '"foo"') AS json_data""",
+        )
+        self.validate_identity(
+            "SELECT JSON_OBJECT(['a', 'b'], [STRUCT(10 AS id, 'Red' AS color), STRUCT(20 AS id, 'Blue' AS color)]) AS json_data",
+            "SELECT JSON_OBJECT('a', STRUCT(10 AS id, 'Red' AS color), 'b', STRUCT(20 AS id, 'Blue' AS color)) AS json_data",
+        )
+        self.validate_identity(
+            "SELECT JSON_OBJECT(['a', 'b'], [TO_JSON(10), TO_JSON(['foo', 'bar'])]) AS json_data",
+            "SELECT JSON_OBJECT('a', TO_JSON(10), 'b', TO_JSON(['foo', 'bar'])) AS json_data",
+        )
+
         with self.assertRaises(ParseError):
             transpile("SELECT JSON_OBJECT('a', 1, 'b') AS json_data", read="bigquery")
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 70a05fd9..2588c334 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -393,6 +393,14 @@ class TestHive(Validator):
         self.validate_identity("SELECT * FROM my_table TIMESTAMP AS OF DATE_ADD(CURRENT_DATE, -1)")
         self.validate_identity("SELECT * FROM my_table VERSION AS OF DATE_ADD(CURRENT_DATE, -1)")
 
+        self.validate_identity(
+            "SELECT CAST('1998-01-01' AS DATE) + 30 years",
+            "SELECT CAST('1998-01-01' AS DATE) + INTERVAL 30 years",
+        )
+        self.validate_identity(
+            "SELECT 30 + 50 bla",
+            "SELECT 30 + 50 AS bla",
+        )
         self.validate_identity(
             "SELECT ROW() OVER (DISTRIBUTE BY x SORT BY y)",
             "SELECT ROW() OVER (PARTITION BY x ORDER BY y)",
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 11740239..8740acab 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -536,6 +536,54 @@ class TestPostgres(Validator):
             "x::cstring",
             write={"postgres": "CAST(x AS CSTRING)"},
         )
+        self.validate_all(
+            "x::oid",
+            write={"postgres": "CAST(x AS OID)"},
+        )
+        self.validate_all(
+            "x::regclass",
+            write={"postgres": "CAST(x AS REGCLASS)"},
+        )
+        self.validate_all(
+            "x::regcollation",
+            write={"postgres": "CAST(x AS REGCOLLATION)"},
+        )
+        self.validate_all(
+            "x::regconfig",
+            write={"postgres": "CAST(x AS REGCONFIG)"},
+        )
+        self.validate_all(
+            "x::regdictionary",
+            write={"postgres": "CAST(x AS REGDICTIONARY)"},
+        )
+        self.validate_all(
+            "x::regnamespace",
+            write={"postgres": "CAST(x AS REGNAMESPACE)"},
+        )
+        self.validate_all(
+            "x::regoper",
+            write={"postgres": "CAST(x AS REGOPER)"},
+        )
+        self.validate_all(
+            "x::regoperator",
+            write={"postgres": "CAST(x AS REGOPERATOR)"},
+        )
+        self.validate_all(
+            "x::regproc",
+            write={"postgres": "CAST(x AS REGPROC)"},
+        )
+        self.validate_all(
+            "x::regprocedure",
+            write={"postgres": "CAST(x AS REGPROCEDURE)"},
+        )
+        self.validate_all(
+            "x::regrole",
+            write={"postgres": "CAST(x AS REGROLE)"},
+        )
+        self.validate_all(
+            "x::regtype",
+            write={"postgres": "CAST(x AS REGTYPE)"},
+        )
         self.validate_all(
             "TRIM(BOTH 'as' FROM 'as string as')",
             write={
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 0043ad08..dbca5b3f 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -824,7 +824,7 @@ class TestPresto(Validator):
         self.validate_all(
             """JSON_FORMAT(JSON '"x"')""",
             write={
-                "bigquery": """TO_JSON_STRING(CAST('"x"' AS JSON))""",
+                "bigquery": """TO_JSON_STRING(JSON '"x"')""",
                 "duckdb": """CAST(TO_JSON(CAST('"x"' AS JSON)) AS TEXT)""",
                 "presto": """JSON_FORMAT(CAST('"x"' AS JSON))""",
                 "spark": """REGEXP_EXTRACT(TO_JSON(FROM_JSON('["x"]', SCHEMA_OF_JSON('["x"]'))), '^.(.*).$', 1)""",
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index a892b0f1..3db55b82 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -243,6 +243,10 @@ TBLPROPERTIES (
             "SELECT STR_TO_MAP('a:1,b:2,c:3')",
             "SELECT STR_TO_MAP('a:1,b:2,c:3', ',', ':')",
         )
+        self.validate_identity(
+            "SELECT CAST('1998-01-01' AS DATE) + 30 years",
+            "SELECT CAST('1998-01-01' AS DATE) + 30 AS years",
+        )
 
         self.validate_all(
             "foo.bar",