Repository: tobymao/sqlglot

Issue:
NVL should NOT be replaced by COALESCE in Oracle but stay NVL. Their behavior are not the same in term of data type conversion.
**Fully reproducible code snippet**
In oracle DB :
```
CREATE TABLE TEST1 (COL1 INT, COL2 VARCHAR2(10))
INSERT INTO TEST1 VALUES (1,'2')
```
```
SELECT * FROM TEST1 WHERE NVL(COL2, 1) <> COL1
```
--> COL1   COL2
              1   2

```
SELECT * FROM TEST1 WHERE COALESCE(COL2, 1) <> COL1
```
--> ORA-00932: inconsistent datatypes: expected : CHAR ; got : NUMBER


As we can see, NVL does NOT care about the type, COALESCE does. Therefore both function are not identical.

In python with sqlglot:
sqlQuery: str = """ SELECT * FROM TEST1 WHERE NVL(COL2, 1) <> COL1"""
sql = sqlglot.parse_one(sql=sqlQuery, read='oracle')
print(sql.sql(dialect='oracle'))
-> SELECT * FROM TEST1 WHERE COALESCE(COL2, 1) <> COL1
I should have NVL and not COALESCE. These 2 functions are not identical.


**Official Documentation**
Please include links to official SQL documentation related to your issue.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL.html
"If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1."


Discussion:
georgesittas: > Came across Snowflake which supports `NVL()` (alias of `IFNULL`) _and_ `COALESCE()`. Their differences seem very subtle:
> 
>     * `NVL / IFNULL` only accept 2 arguments, `COALESCE` is varlen
> 
>     * The docs seem identical except maybe [the last bullet here](https://docs.snowflake.com/en/sql-reference/functions/nvl#usage-notes)
> 
> 
> [Snowflake NVL/IFNULL](https://docs.snowflake.com/en/sql-reference/functions/nvl#usage-notes) | [Snowflake COALESCE](https://docs.snowflake.com/en/sql-reference/functions/coalesce#usage-notes)

Yeah that makes sense, Oracle and Snowflake are very similar. Let's fix this for Snowflake too then I guess.
VaggelisD: Reverted the Snowflake change, can't find a tangible difference between `NVL/NULLIF` and `COALESCE` except the varlen args.

Patch:
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index e4dad2cc..ed01ee8b 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -107,6 +107,7 @@ class Oracle(Dialect):
             "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/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 1eab756b..a3e84f47 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -504,10 +504,11 @@ class Snowflake(Dialect):
 
             return lateral
 
-        def _parse_at_before(self, table: exp.Table) -> exp.Table:
+        def _parse_historical_data(self) -> t.Optional[exp.HistoricalData]:
             # https://docs.snowflake.com/en/sql-reference/constructs/at-before
             index = self._index
-            if self._match_texts(("AT", "BEFORE")):
+            historical_data = None
+            if self._match_texts(self.HISTORICAL_DATA_PREFIX):
                 this = self._prev.text.upper()
                 kind = (
                     self._match(TokenType.L_PAREN)
@@ -518,14 +519,27 @@ class Snowflake(Dialect):
 
                 if expression:
                     self._match_r_paren()
-                    when = self.expression(
+                    historical_data = self.expression(
                         exp.HistoricalData, this=this, kind=kind, expression=expression
                     )
-                    table.set("when", when)
                 else:
                     self._retreat(index)
 
-            return table
+            return historical_data
+
+        def _parse_changes(self) -> t.Optional[exp.Changes]:
+            if not self._match_text_seq("CHANGES", "(", "INFORMATION", "=>"):
+                return None
+
+            information = self._parse_var(any_token=True)
+            self._match_r_paren()
+
+            return self.expression(
+                exp.Changes,
+                information=information,
+                at_before=self._parse_historical_data(),
+                end=self._parse_historical_data(),
+            )
 
         def _parse_table_parts(
             self, schema: bool = False, is_db_reference: bool = False, wildcard: bool = False
@@ -559,7 +573,15 @@ class Snowflake(Dialect):
             else:
                 table = super()._parse_table_parts(schema=schema, is_db_reference=is_db_reference)
 
-            return self._parse_at_before(table)
+            changes = self._parse_changes()
+            if changes:
+                table.set("changes", changes)
+
+            at_before = self._parse_historical_data()
+            if at_before:
+                table.set("when", at_before)
+
+            return table
 
         def _parse_id_var(
             self,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 764810dc..79511da4 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2002,6 +2002,10 @@ class Check(Expression):
     pass
 
 
+class Changes(Expression):
+    arg_types = {"information": True, "at_before": False, "end": False}
+
+
 # https://docs.snowflake.com/en/sql-reference/constructs/connect-by
 class Connect(Expression):
     arg_types = {"start": False, "connect": True, "nocycle": False}
@@ -3034,6 +3038,7 @@ class Table(Expression):
         "when": False,
         "only": False,
         "partition": False,
+        "changes": False,
     }
 
     @property
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 6a2589f3..40ad4680 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1749,7 +1749,10 @@ class Generator(metaclass=_Generator):
         if when:
             table = f"{table} {when}"
 
-        return f"{only}{table}{partition}{version}{file_format}{alias}{hints}{pivots}{joins}{laterals}{ordinality}"
+        changes = self.sql(expression, "changes")
+        changes = f" {changes}" if changes else ""
+
+        return f"{only}{table}{changes}{partition}{version}{file_format}{alias}{hints}{pivots}{joins}{laterals}{ordinality}"
 
     def tablesample_sql(
         self,
@@ -4028,3 +4031,13 @@ class Generator(metaclass=_Generator):
             expression.args.get("format"),
             expression.args.get("zone"),
         )
+
+    def changes_sql(self, expression: exp.Changes) -> str:
+        information = self.sql(expression, "information")
+        information = f"INFORMATION => {information}"
+        at_before = self.sql(expression, "at_before")
+        at_before = f"{self.seg('')}{at_before}" if at_before else ""
+        end = self.sql(expression, "end")
+        end = f"{self.seg('')}{end}" if end else ""
+
+        return f"CHANGES ({information}){at_before}{end}"
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index d5fe792b..d655b849 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1132,6 +1132,7 @@ class Parser(metaclass=_Parser):
     INSERT_ALTERNATIVES = {"ABORT", "FAIL", "IGNORE", "REPLACE", "ROLLBACK"}
 
     CLONE_KEYWORDS = {"CLONE", "COPY"}
+    HISTORICAL_DATA_PREFIX = {"AT", "BEFORE", "END"}
     HISTORICAL_DATA_KIND = {"TIMESTAMP", "OFFSET", "STATEMENT", "STREAM"}
 
     OPCLASS_FOLLOW_KEYWORDS = {"ASC", "DESC", "NULLS", "WITH"}


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 70b8595d..1d9fd99b 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -86,9 +86,9 @@ class TestOracle(Validator):
             "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 @@ class TestOracle(Validator):
                 "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={
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 80f8faaa..88b2148d 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -190,18 +190,6 @@ WHERE
             """SELECT PARSE_JSON('{"food":{"fruit":"banana"}}'):food.fruit::VARCHAR""",
             """SELECT CAST(GET_PATH(PARSE_JSON('{"food":{"fruit":"banana"}}'), 'food.fruit') AS VARCHAR)""",
         )
-        self.validate_identity(
-            "SELECT * FROM foo at",
-            "SELECT * FROM foo AS at",
-        )
-        self.validate_identity(
-            "SELECT * FROM foo before",
-            "SELECT * FROM foo AS before",
-        )
-        self.validate_identity(
-            "SELECT * FROM foo at (col)",
-            "SELECT * FROM foo AS at(col)",
-        )
         self.validate_identity(
             "SELECT * FROM unnest(x) with ordinality",
             "SELECT * FROM TABLE(FLATTEN(INPUT => x)) AS _u(seq, key, path, index, value, this)",
@@ -1204,6 +1192,17 @@ WHERE
             "SELECT oldt.*, newt.* FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt FULL OUTER JOIN my_table AT (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt ON oldt.id = newt.id WHERE oldt.id IS NULL OR newt.id IS NULL",
         )
 
+        # Make sure that the historical data keywords can still be used as aliases
+        for historical_data_prefix in ("AT", "BEFORE", "END", "CHANGES"):
+            for schema_suffix in ("", "(col)"):
+                with self.subTest(
+                    f"Testing historical data prefix alias: {historical_data_prefix}{schema_suffix}"
+                ):
+                    self.validate_identity(
+                        f"SELECT * FROM foo {historical_data_prefix}{schema_suffix}",
+                        f"SELECT * FROM foo AS {historical_data_prefix}{schema_suffix}",
+                    )
+
     def test_ddl(self):
         for constraint_prefix in ("WITH ", ""):
             with self.subTest(f"Constraint prefix: {constraint_prefix}"):
@@ -2033,3 +2032,15 @@ SINGLE = TRUE""",
 
         self.validate_identity("ALTER TABLE foo UNSET TAG a, b, c")
         self.validate_identity("ALTER TABLE foo UNSET DATA_RETENTION_TIME_IN_DAYS, CHANGE_TRACKING")
+
+    def test_from_changes(self):
+        self.validate_identity(
+            """SELECT C1 FROM t1 CHANGES (INFORMATION => APPEND_ONLY) AT (STREAM => 's1') END (TIMESTAMP => $ts2)"""
+        )
+        self.validate_identity(
+            """SELECT C1 FROM t1 CHANGES (INFORMATION => APPEND_ONLY) BEFORE (STATEMENT => 'STMT_ID') END (TIMESTAMP => $ts2)"""
+        )
+        self.validate_identity(
+            """SELECT 1 FROM some_table CHANGES (INFORMATION => APPEND_ONLY) AT (TIMESTAMP => TO_TIMESTAMP_TZ('2024-07-01 00:00:00+00:00')) END (TIMESTAMP => TO_TIMESTAMP_TZ('2024-07-01 14:28:59.999999+00:00'))""",
+            """SELECT 1 FROM some_table CHANGES (INFORMATION => APPEND_ONLY) AT (TIMESTAMP => CAST('2024-07-01 00:00:00+00:00' AS TIMESTAMPTZ)) END (TIMESTAMP => CAST('2024-07-01 14:28:59.999999+00:00' AS TIMESTAMPTZ))""",
+        )