From 98d5537435b2951b36c45f1fda667fa27c165794 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Wed, 3 May 2023 11:12:24 +0200
Subject: Adding upstream version 11.7.1.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 tests/dialects/test_oracle.py | 48 ++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 47 insertions(+), 1 deletion(-)

(limited to 'tests/dialects/test_oracle.py')

diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 80fa0f1..dd297d6 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -6,6 +6,27 @@ class TestOracle(Validator):
 
     def test_oracle(self):
         self.validate_identity("SELECT * FROM V$SESSION")
+        self.validate_identity(
+            "SELECT MIN(column_name) KEEP (DENSE_RANK FIRST ORDER BY column_name DESC) FROM table_name"
+        )
+
+        self.validate_all(
+            "NVL(NULL, 1)",
+            write={
+                "oracle": "NVL(NULL, 1)",
+                "": "IFNULL(NULL, 1)",
+            },
+        )
+
+        self.validate_all(
+            "DATE '2022-01-01'",
+            write={
+                "": "DATE_STR_TO_DATE('2022-01-01')",
+                "mysql": "CAST('2022-01-01' AS DATE)",
+                "oracle": "TO_DATE('2022-01-01', 'YYYY-MM-DD')",
+                "postgres": "CAST('2022-01-01' AS DATE)",
+            },
+        )
 
     def test_join_marker(self):
         self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y")
@@ -81,7 +102,7 @@ FROM warehouses, XMLTABLE(
 FROM XMLTABLE(
   'ROWSET/ROW'
   PASSING
-    dbms_xmlgen.getxmltype ("SELECT table_name, column_name, data_default FROM user_tab_columns")
+    dbms_xmlgen.GETXMLTYPE('SELECT table_name, column_name, data_default FROM user_tab_columns')
   COLUMNS
     table_name VARCHAR2(128) PATH '*[1]',
     column_name VARCHAR2(128) PATH '*[2]',
@@ -90,3 +111,28 @@ FROM XMLTABLE(
             },
             pretty=True,
         )
+
+    def test_match_recognize(self):
+        self.validate_identity(
+            """SELECT
+  *
+FROM sales_history
+MATCH_RECOGNIZE (
+  PARTITION BY product
+  ORDER BY
+    tstamp
+  MEASURES
+    STRT.tstamp AS start_tstamp,
+    LAST(UP.tstamp) AS peak_tstamp,
+    LAST(DOWN.tstamp) AS end_tstamp,
+    MATCH_NUMBER() AS mno
+  ONE ROW PER MATCH
+  AFTER MATCH SKIP TO LAST DOWN
+  PATTERN (STRT UP+ FLAT* DOWN+)
+  DEFINE
+    UP AS UP.units_sold > PREV(UP.units_sold),
+    FLAT AS FLAT.units_sold = PREV(FLAT.units_sold),
+    DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
+) MR""",
+            pretty=True,
+        )
-- 
cgit v1.2.3