Repository: tobymao/sqlglot
Pull request: Fix(hive): parse <number> <date_part> as an interval instead of an alias
Fixes #2123

Tested in https://demo.gethue.com/hue/editor/?type=hiv (enter `demo` for both the username and the password):

<img width="1165" alt="Screenshot 2023-09-04 at 7 15 59 PM" src="https://github.com/tobymao/sqlglot/assets/46752250/b1095ff1-2882-44d1-9b0b-141884d09742">

The date parts were chosen based on Hive's [docs](https://cwiki.apache.org/confluence/display/hive/languagemanual+types#LanguageManualTypes-Intervals) for the interval type:

![image](https://github.com/tobymao/sqlglot/assets/46752250/2aeb23ea-e4f9-40da-abcd-ea1b60567577)



Problem statement:
(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))]
`

Hints from issue discussion:
this is the correct behavior.

```
>>> spark.sql("select cast('1998-01-06' as date) + 30 year ").collect()
[Row(year=datetime.date(1998, 2, 5))]
```

in this case i change it to year and you can see spark returns the column name year as an alias
@tobymao In hive, this does not seem to be in the form of an alias, please see the figure
![img_v2_2d435aed-ad90-4307-8718-d9ae3e48e70g](https://github.com/tobymao/sqlglot/assets/61498169/1cc07b7c-bb11-4237-bc41-141e02f44ee4)

@GeorgeSittas Can you help me look into this issue again? Thank you very much.
> @GeorgeSittas Can you help me look into this issue again? Thank you very much.

I'll take a look soon

Commit message: Fix(hive): parse <number> <date_part> as an interval instead of an alias

Patch to review:
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 6bb6cfbba3..cbe8b9e869 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -47,9 +47,31 @@
     "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 = parser.Parser.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 Parser(parser.Parser):
             ),
         }
 
+        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/spark2.py b/sqlglot/dialects/spark2.py
index f5c00ae0de..1d1d77f00d 100644
--- a/sqlglot/dialects/spark2.py
+++ b/sqlglot/dialects/spark2.py
@@ -2,7 +2,7 @@
 
 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 Parser(Hive.Parser):
             "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/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 70a05fd9d0..2588c3343b 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -393,6 +393,14 @@ def test_hive(self):
         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_spark.py b/tests/dialects/test_spark.py
index a892b0f110..3db55b82e4 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -243,6 +243,10 @@ def test_spark(self):
             "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",


Code review comments:
diff_hunk: @@ -47,9 +47,31 @@
     "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 = parser.Parser.PRIMARY_PARSERS[TokenType.NUMBER](self, token)
path: sqlglot/dialects/hive.py
text: @user1:
why not call super?

@author:
because it's not in the context of a class definition and so python won't accept it

@user1:
super(...).x