fix(oracle)!: Decouple NVL() from COALESCE() (#3734)

VaggelisD · web-flow · commit fb066a6167e1 · 2024-07-03T18:29:01.000+03:00
* fix(oracle): Decouple NVL() from COALESCE()

* Change parse_one() to validate_identity()

Revert Snowflake
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
@@ -107,6 +107,7 @@ class Parser(parser.Parser):
             "TO_TIMESTAMP": build_formatted_time(exp.StrToTime, "oracle"),
             "TO_DATE": build_formatted_time(exp.StrToDate, "oracle"),
         }
+        FUNCTIONS.pop("NVL")
 
         FUNCTION_PARSERS: t.Dict[str, t.Callable] = {
             **parser.Parser.FUNCTION_PARSERS,
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
@@ -86,9 +86,9 @@ def test_oracle(self):
             "SELECT DISTINCT col1, col2 FROM table",
         )
         self.validate_identity(
-            "SELECT * FROM T ORDER BY I OFFSET nvl(:variable1, 10) ROWS FETCH NEXT nvl(:variable2, 10) ROWS ONLY",
-            "SELECT * FROM T ORDER BY I OFFSET COALESCE(:variable1, 10) ROWS FETCH NEXT COALESCE(:variable2, 10) ROWS ONLY",
+            "SELECT * FROM T ORDER BY I OFFSET NVL(:variable1, 10) ROWS FETCH NEXT NVL(:variable2, 10) ROWS ONLY",
         )
+        self.validate_identity("NVL(x, y)").assert_is(exp.Anonymous)
         self.validate_identity(
             "SELECT * FROM t SAMPLE (.25)",
             "SELECT * FROM t SAMPLE (0.25)",
@@ -189,13 +189,6 @@ def test_oracle(self):
                 "spark": "SELECT CAST(NULL AS VARCHAR(2328)) AS COL1",
             },
         )
-        self.validate_all(
-            "NVL(NULL, 1)",
-            write={
-                "": "COALESCE(NULL, 1)",
-                "oracle": "COALESCE(NULL, 1)",
-            },
-        )
         self.validate_all(
             "DATE '2022-01-01'",
             write={

Original file line number	Diff line number	Diff line change
`@@ -107,6 +107,7 @@ class Parser(parser.Parser):`
`107`	`107`	`"TO_TIMESTAMP": build_formatted_time(exp.StrToTime, "oracle"),`
`108`	`108`	`"TO_DATE": build_formatted_time(exp.StrToDate, "oracle"),`
`109`	`109`	`}`
	`110`	`+ FUNCTIONS.pop("NVL")`
`110`	`111`
`111`	`112`	`FUNCTION_PARSERS: t.Dict[str, t.Callable] = {`
`112`	`113`	`**parser.Parser.FUNCTION_PARSERS,`