diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2023-05-03 09:12:28 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2023-05-03 09:12:28 +0000 |
commit | 67c28dbe67209effad83d93b850caba5ee1e20e3 (patch) | |
tree | dffdfbfb4f0899c92a4c978e6eac55af2ff76367 /tests/dialects/test_oracle.py | |
parent | Releasing debian version 11.5.2-1. (diff) | |
download | sqlglot-67c28dbe67209effad83d93b850caba5ee1e20e3.tar.xz sqlglot-67c28dbe67209effad83d93b850caba5ee1e20e3.zip |
Merging upstream version 11.7.1.
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_oracle.py')
-rw-r--r-- | tests/dialects/test_oracle.py | 48 |
1 files changed, 47 insertions, 1 deletions
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, + ) |