Repository: tobymao/sqlglot

Issue:
Interval parsing does not meet expectations
When adding other keywords after interval, there is a problem with interval parsing, and it is not parsed into the format of the standard interval '71' day.
Reproduce code example：
`expression = sqlglot.parse_one("""select a from tbl where a <= date '1998-12-01' - interval '71 days' group by b;`
result：`SELECT a FROM tbl WHERE a <= CAST('1998-12-01' AS DATE) - INTERVAL '71 days' GROUP BY b`

`expression = sqlglot.parse_one("""select a from tbl where a <= date '1998-12-01' - interval '71 days';`
result：`SELECT a FROM tbl WHERE a <= CAST('1998-12-01' AS DATE) - INTERVAL '71' days`

Discussion:

Patch:
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 27c1f62b..279ed314 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -133,7 +133,6 @@ class Oracle(Dialect):
             ),
             exp.Group: transforms.preprocess([transforms.unalias_group]),
             exp.ILike: no_ilike_sql,
-            exp.Coalesce: rename_func("NVL"),
             exp.Select: transforms.preprocess([transforms.eliminate_distinct_on]),
             exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})",
             exp.Subquery: lambda self, e: self.subquery_sql(e, sep=" "),
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 214998c7..f9f8bd4d 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3154,12 +3154,12 @@ class Parser(metaclass=_Parser):
 
             if len(parts) == 2:
                 if unit:
-                    # this is not actually a unit, it's something else
+                    # This is not actually a unit, it's something else (e.g. a "window side")
                     unit = None
                     self._retreat(self._index - 1)
-                else:
-                    this = exp.Literal.string(parts[0])
-                    unit = self.expression(exp.Var, this=parts[1])
+
+                this = exp.Literal.string(parts[0])
+                unit = self.expression(exp.Var, this=parts[1])
 
         return self.expression(exp.Interval, this=this, unit=unit)
 
@@ -4151,7 +4151,7 @@ class Parser(metaclass=_Parser):
         key = self._parse_column()
         self._match_set((TokenType.COLON, TokenType.COMMA))
         self._match_text_seq("VALUE")
-        value = self._parse_column()
+        value = self._parse_bitwise()
 
         if not key and not value:
             return None


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 7d9ab900..01a9ca3a 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -6,6 +6,8 @@ class TestOracle(Validator):
     dialect = "oracle"
 
     def test_oracle(self):
+        self.validate_identity("SELECT JSON_OBJECT('name': first_name || ' ' || last_name) FROM t")
+        self.validate_identity("COALESCE(c1, c2, c3)")
         self.validate_identity("SELECT * FROM TABLE(foo)")
         self.validate_identity("SELECT a$x#b")
         self.validate_identity("SELECT :OBJECT")
@@ -43,7 +45,7 @@ class TestOracle(Validator):
             "NVL(NULL, 1)",
             write={
                 "": "COALESCE(NULL, 1)",
-                "oracle": "NVL(NULL, 1)",
+                "oracle": "COALESCE(NULL, 1)",
             },
         )
         self.validate_all(
diff --git a/tests/test_parser.py b/tests/test_parser.py
index a853a757..7135dd89 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -711,3 +711,11 @@ class TestParser(unittest.TestCase):
             parse_one("SELECT a, b ?? c ?? 'No Data' FROM z").sql(),
             "SELECT a, COALESCE(COALESCE(b, c), 'No Data') FROM z",
         )
+
+    def test_parse_intervals(self):
+        ast = parse_one(
+            "SELECT a FROM tbl WHERE a <= DATE '1998-12-01' - INTERVAL '71 days' GROUP BY b"
+        )
+
+        self.assertEqual(ast.find(exp.Interval).this.sql(), "'71'")
+        self.assertEqual(ast.find(exp.Interval).unit.assert_is(exp.Var).sql(), "days")