diff options
Diffstat (limited to 'tests/dialects/test_oracle.py')
| -rw-r--r-- | tests/dialects/test_oracle.py | 68 |
1 files changed, 68 insertions, 0 deletions
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py index 9438507..526b0b5 100644 --- a/tests/dialects/test_oracle.py +++ b/tests/dialects/test_oracle.py @@ -94,8 +94,21 @@ class TestOracle(Validator): "SELECT * FROM t SAMPLE (0.25)", ) self.validate_identity("SELECT TO_CHAR(-100, 'L99', 'NL_CURRENCY = '' AusDollars '' ')") + self.validate_identity( + "SELECT * FROM t START WITH col CONNECT BY NOCYCLE PRIOR col1 = col2" + ) self.validate_all( + "CURRENT_TIMESTAMP BETWEEN TO_DATE(f.C_SDATE, 'yyyy/mm/dd') AND TO_DATE(f.C_EDATE, 'yyyy/mm/dd')", + read={ + "postgres": "CURRENT_TIMESTAMP BETWEEN TO_DATE(f.C_SDATE, 'yyyy/mm/dd') AND TO_DATE(f.C_EDATE, 'yyyy/mm/dd')", + }, + write={ + "oracle": "CURRENT_TIMESTAMP BETWEEN TO_DATE(f.C_SDATE, 'yyyy/mm/dd') AND TO_DATE(f.C_EDATE, 'yyyy/mm/dd')", + "postgres": "CURRENT_TIMESTAMP BETWEEN TO_DATE(f.C_SDATE, 'yyyy/mm/dd') AND TO_DATE(f.C_EDATE, 'yyyy/mm/dd')", + }, + ) + self.validate_all( "TO_CHAR(x)", write={ "doris": "CAST(x AS STRING)", @@ -103,6 +116,59 @@ class TestOracle(Validator): }, ) self.validate_all( + "TO_NUMBER(expr, fmt, nlsparam)", + read={ + "teradata": "TO_NUMBER(expr, fmt, nlsparam)", + }, + write={ + "oracle": "TO_NUMBER(expr, fmt, nlsparam)", + "teradata": "TO_NUMBER(expr, fmt, nlsparam)", + }, + ) + self.validate_all( + "TO_NUMBER(x)", + write={ + "bigquery": "CAST(x AS FLOAT64)", + "doris": "CAST(x AS DOUBLE)", + "drill": "CAST(x AS DOUBLE)", + "duckdb": "CAST(x AS DOUBLE)", + "hive": "CAST(x AS DOUBLE)", + "mysql": "CAST(x AS DOUBLE)", + "oracle": "TO_NUMBER(x)", + "postgres": "CAST(x AS DOUBLE PRECISION)", + "presto": "CAST(x AS DOUBLE)", + "redshift": "CAST(x AS DOUBLE PRECISION)", + "snowflake": "TO_NUMBER(x)", + "spark": "CAST(x AS DOUBLE)", + "spark2": "CAST(x AS DOUBLE)", + "starrocks": "CAST(x AS DOUBLE)", + "tableau": "CAST(x AS DOUBLE)", + "teradata": "TO_NUMBER(x)", + }, + ) + self.validate_all( + "TO_NUMBER(x, fmt)", + read={ + "databricks": "TO_NUMBER(x, fmt)", + "drill": "TO_NUMBER(x, fmt)", + "postgres": "TO_NUMBER(x, fmt)", + "snowflake": "TO_NUMBER(x, fmt)", + "spark": "TO_NUMBER(x, fmt)", + "redshift": "TO_NUMBER(x, fmt)", + "teradata": "TO_NUMBER(x, fmt)", + }, + write={ + "databricks": "TO_NUMBER(x, fmt)", + "drill": "TO_NUMBER(x, fmt)", + "oracle": "TO_NUMBER(x, fmt)", + "postgres": "TO_NUMBER(x, fmt)", + "snowflake": "TO_NUMBER(x, fmt)", + "spark": "TO_NUMBER(x, fmt)", + "redshift": "TO_NUMBER(x, fmt)", + "teradata": "TO_NUMBER(x, fmt)", + }, + ) + self.validate_all( "SELECT TO_CHAR(TIMESTAMP '1999-12-01 10:00:00')", write={ "oracle": "SELECT TO_CHAR(CAST('1999-12-01 10:00:00' AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS')", @@ -210,6 +276,8 @@ class TestOracle(Validator): self.validate_identity( "SELECT /*+ LEADING(e j) */ * FROM employees e, departments d, job_history j WHERE e.department_id = d.department_id AND e.hire_date = j.start_date" ) + self.validate_identity("INSERT /*+ APPEND */ INTO IAP_TBL (id, col1) VALUES (2, 'test2')") + self.validate_identity("INSERT /*+ APPEND_VALUES */ INTO dest_table VALUES (i, 'Value')") def test_xml_table(self): self.validate_identity("XMLTABLE('x')") |