From 24e839c04c39d1f4423b267c371e8e5b5bc33867 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 12 Feb 2023 11:03:37 +0100 Subject: Adding upstream version 11.0.1. Signed-off-by: Daniel Baumann --- tests/dialects/test_databricks.py | 3 +++ tests/dialects/test_dialect.py | 8 +++---- tests/dialects/test_duckdb.py | 4 ++++ tests/dialects/test_hive.py | 2 +- tests/dialects/test_snowflake.py | 48 +++++++++++++++++++++++++++++++++++---- 5 files changed, 56 insertions(+), 9 deletions(-) (limited to 'tests/dialects') diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py index 1d80dc0..5ae5c6f 100644 --- a/tests/dialects/test_databricks.py +++ b/tests/dialects/test_databricks.py @@ -4,6 +4,9 @@ from tests.dialects.test_dialect import Validator class TestDatabricks(Validator): dialect = "databricks" + def test_databricks(self): + self.validate_identity("CREATE FUNCTION a.b(x INT) RETURNS INT RETURN x + 1") + def test_datediff(self): self.validate_all( "SELECT DATEDIFF(year, 'start', 'end')", diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index a456415..442fbbb 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -508,7 +508,7 @@ class TestDialect(Validator): }, write={ "bigquery": "DATE_ADD(x, INTERVAL 1 'day')", - "drill": "DATE_ADD(x, INTERVAL '1' DAY)", + "drill": "DATE_ADD(x, INTERVAL 1 DAY)", "duckdb": "x + INTERVAL 1 day", "hive": "DATE_ADD(x, 1)", "mysql": "DATE_ADD(x, INTERVAL 1 DAY)", @@ -525,7 +525,7 @@ class TestDialect(Validator): "DATE_ADD(x, 1)", write={ "bigquery": "DATE_ADD(x, INTERVAL 1 'day')", - "drill": "DATE_ADD(x, INTERVAL '1' DAY)", + "drill": "DATE_ADD(x, INTERVAL 1 DAY)", "duckdb": "x + INTERVAL 1 DAY", "hive": "DATE_ADD(x, 1)", "mysql": "DATE_ADD(x, INTERVAL 1 DAY)", @@ -628,7 +628,7 @@ class TestDialect(Validator): self.validate_all( "TS_OR_DS_ADD('2021-02-01', 1, 'DAY')", write={ - "drill": "DATE_ADD(CAST('2021-02-01' AS DATE), INTERVAL '1' DAY)", + "drill": "DATE_ADD(CAST('2021-02-01' AS DATE), INTERVAL 1 DAY)", "duckdb": "CAST('2021-02-01' AS DATE) + INTERVAL 1 DAY", "hive": "DATE_ADD('2021-02-01', 1)", "presto": "DATE_ADD('DAY', 1, DATE_PARSE(SUBSTR('2021-02-01', 1, 10), '%Y-%m-%d'))", @@ -638,7 +638,7 @@ class TestDialect(Validator): self.validate_all( "DATE_ADD(CAST('2020-01-01' AS DATE), 1)", write={ - "drill": "DATE_ADD(CAST('2020-01-01' AS DATE), INTERVAL '1' DAY)", + "drill": "DATE_ADD(CAST('2020-01-01' AS DATE), INTERVAL 1 DAY)", "duckdb": "CAST('2020-01-01' AS DATE) + INTERVAL 1 DAY", "hive": "DATE_ADD(CAST('2020-01-01' AS DATE), 1)", "presto": "DATE_ADD('day', 1, CAST('2020-01-01' AS DATE))", diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py index f01a604..e5cb833 100644 --- a/tests/dialects/test_duckdb.py +++ b/tests/dialects/test_duckdb.py @@ -343,6 +343,10 @@ class TestDuckDB(Validator): }, ) + self.validate_all( + "CAST(x AS DATE) + INTERVAL (7 * -1) DAY", read={"spark": "DATE_SUB(x, 7)"} + ) + def test_bool_or(self): self.validate_all( "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a", diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py index 1f35d1d..42d9943 100644 --- a/tests/dialects/test_hive.py +++ b/tests/dialects/test_hive.py @@ -287,7 +287,7 @@ class TestHive(Validator): self.validate_all( "DATE_SUB('2020-01-01', 1)", write={ - "duckdb": "CAST('2020-01-01' AS DATE) + INTERVAL 1 * -1 DAY", + "duckdb": "CAST('2020-01-01' AS DATE) + INTERVAL (1 * -1) DAY", "presto": "DATE_ADD('DAY', 1 * -1, DATE_PARSE(SUBSTR('2020-01-01', 1, 10), '%Y-%m-%d'))", "hive": "DATE_ADD('2020-01-01', 1 * -1)", "spark": "DATE_ADD('2020-01-01', 1 * -1)", diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index f3e8e24..201cc4e 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -6,6 +6,43 @@ class TestSnowflake(Validator): dialect = "snowflake" def test_snowflake(self): + self.validate_identity("SELECT REGEXP_LIKE(a, b, c)") + self.validate_identity("PUT file:///dir/tmp.csv @%table") + self.validate_identity("CREATE TABLE foo (bar FLOAT AUTOINCREMENT START 0 INCREMENT 1)") + self.validate_identity( + 'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)' + ) + + self.validate_all( + "CREATE OR REPLACE TEMPORARY TABLE x (y NUMBER IDENTITY(0, 1))", + write={ + "snowflake": "CREATE OR REPLACE TEMPORARY TABLE x (y DECIMAL AUTOINCREMENT START 0 INCREMENT 1)", + }, + ) + self.validate_all( + "CREATE TEMPORARY TABLE x (y NUMBER AUTOINCREMENT(0, 1))", + write={ + "snowflake": "CREATE TEMPORARY TABLE x (y DECIMAL AUTOINCREMENT START 0 INCREMENT 1)", + }, + ) + self.validate_all( + "CREATE TABLE x (y NUMBER IDENTITY START 0 INCREMENT 1)", + write={ + "snowflake": "CREATE TABLE x (y DECIMAL AUTOINCREMENT START 0 INCREMENT 1)", + }, + ) + self.validate_all( + "ALTER TABLE foo ADD COLUMN id INT identity(1, 1)", + write={ + "snowflake": "ALTER TABLE foo ADD COLUMN id INT AUTOINCREMENT START 1 INCREMENT 1", + }, + ) + self.validate_all( + "SELECT DAYOFWEEK('2016-01-02T23:39:20.123-07:00'::TIMESTAMP)", + write={ + "snowflake": "SELECT DAYOFWEEK(CAST('2016-01-02T23:39:20.123-07:00' AS TIMESTAMPNTZ))", + }, + ) self.validate_all( "SELECT * FROM xxx WHERE col ilike '%Don''t%'", write={ @@ -165,10 +202,10 @@ class TestSnowflake(Validator): self.validate_all( r"SELECT $$a ' \ \t \x21 z $ $$", write={ - "snowflake": r"SELECT 'a \' \\ \\t \\x21 z $ '", + "snowflake": r"SELECT 'a \' \\ \t \\x21 z $ '", }, ) - self.validate_identity("SELECT REGEXP_LIKE(a, b, c)") + self.validate_identity(r"REGEXP_REPLACE('target', 'pattern', '\n')") self.validate_all( "SELECT RLIKE(a, b)", write={ @@ -253,6 +290,8 @@ class TestSnowflake(Validator): ) def test_timestamps(self): + self.validate_identity("SELECT EXTRACT(month FROM a)") + self.validate_all( "SELECT CAST(a AS TIMESTAMP)", write={ @@ -277,7 +316,6 @@ class TestSnowflake(Validator): "snowflake": "SELECT CAST(a AS TIMESTAMPLTZ)", }, ) - self.validate_identity("SELECT EXTRACT(month FROM a)") self.validate_all( "SELECT EXTRACT('month', a)", write={ @@ -313,6 +351,8 @@ class TestSnowflake(Validator): def test_semi_structured_types(self): self.validate_identity("SELECT CAST(a AS VARIANT)") + self.validate_identity("SELECT CAST(a AS ARRAY)") + self.validate_all( "SELECT a::VARIANT", write={ @@ -320,7 +360,6 @@ class TestSnowflake(Validator): "tsql": "SELECT CAST(a AS SQL_VARIANT)", }, ) - self.validate_identity("SELECT CAST(a AS ARRAY)") self.validate_all( "ARRAY_CONSTRUCT(0, 1, 2)", write={ @@ -343,6 +382,7 @@ class TestSnowflake(Validator): "CREATE TABLE a (x DATE, y BIGINT) WITH (PARTITION BY (x), integration='q', auto_refresh=TRUE, file_format=(type = parquet))" ) self.validate_identity("CREATE MATERIALIZED VIEW a COMMENT='...' AS SELECT 1 FROM x") + self.validate_all( "CREATE OR REPLACE TRANSIENT TABLE a (id INT)", read={ -- cgit v1.2.3