diff options
Diffstat (limited to 'tests/dialects')
| -rw-r--r-- | tests/dialects/test_dialect.py | 42 | ||||
| -rw-r--r-- | tests/dialects/test_hive.py | 15 | ||||
| -rw-r--r-- | tests/dialects/test_mysql.py | 14 | ||||
| -rw-r--r-- | tests/dialects/test_postgres.py | 45 | ||||
| -rw-r--r-- | tests/dialects/test_redshift.py | 16 | ||||
| -rw-r--r-- | tests/dialects/test_snowflake.py | 5 | ||||
| -rw-r--r-- | tests/dialects/test_spark.py | 5 | ||||
| -rw-r--r-- | tests/dialects/test_teradata.py | 28 | ||||
| -rw-r--r-- | tests/dialects/test_tsql.py | 44 |
9 files changed, 184 insertions, 30 deletions
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index 0d43b2a..2546c98 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -839,10 +839,6 @@ class TestDialect(Validator): ) self.validate_all( "STR_TO_DATE(x, '%Y-%m-%dT%H:%M:%S')", - read={ - "mysql": "STR_TO_DATE(x, '%Y-%m-%dT%H:%i:%S')", - "starrocks": "STR_TO_DATE(x, '%Y-%m-%dT%H:%i:%S')", - }, write={ "drill": "TO_DATE(x, 'yyyy-MM-dd''T''HH:mm:ss')", "mysql": "STR_TO_DATE(x, '%Y-%m-%dT%T')", @@ -1861,3 +1857,41 @@ SELECT "tsql": "SELECT * FROM (SELECT *, COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1", }, ) + + def test_nested_ctes(self): + self.validate_all( + "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + write={ + "bigquery": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "clickhouse": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "databricks": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "duckdb": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "hive": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT c FROM t) AS subq", + "mysql": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "postgres": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "presto": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "redshift": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "snowflake": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "spark": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "spark2": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT c FROM t) AS subq", + "sqlite": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "trino": "SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq", + "tsql": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT c FROM t) AS subq", + }, + ) + self.validate_all( + "SELECT * FROM (SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq1) AS subq2", + write={ + "bigquery": "SELECT * FROM (SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq1) AS subq2", + "duckdb": "SELECT * FROM (SELECT * FROM (WITH t AS (SELECT 1 AS c) SELECT c FROM t) AS subq1) AS subq2", + "hive": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT * FROM (SELECT c FROM t) AS subq1) AS subq2", + "tsql": "WITH t AS (SELECT 1 AS c) SELECT * FROM (SELECT * FROM (SELECT c FROM t) AS subq1) AS subq2", + }, + ) + self.validate_all( + "WITH t1(x) AS (SELECT 1) SELECT * FROM (WITH t2(y) AS (SELECT 2) SELECT y FROM t2) AS subq", + write={ + "duckdb": "WITH t1(x) AS (SELECT 1) SELECT * FROM (WITH t2(y) AS (SELECT 2) SELECT y FROM t2) AS subq", + "tsql": "WITH t1(x) AS (SELECT 1), t2(y) AS (SELECT 2) SELECT * FROM (SELECT y FROM t2) AS subq", + }, + ) diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py index 26f0189..ba95442 100644 --- a/tests/dialects/test_hive.py +++ b/tests/dialects/test_hive.py @@ -429,6 +429,21 @@ class TestHive(Validator): self.validate_identity( "SELECT key, value, GROUPING__ID, COUNT(*) FROM T1 GROUP BY key, value WITH ROLLUP" ) + + self.validate_all( + "SET hiveconf:some_var = 5", + write={ + "hive": "SET hiveconf:some_var = 5", + "spark": "SET hiveconf:some_var = 5", + }, + ) + self.validate_all( + "SELECT ${hiveconf:some_var}", + write={ + "hive": "SELECT ${hiveconf:some_var}", + "spark": "SELECT ${hiveconf:some_var}", + }, + ) self.validate_all( "SELECT A.1a AS b FROM test_a AS A", write={ diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py index dce2b9d..3c165a3 100644 --- a/tests/dialects/test_mysql.py +++ b/tests/dialects/test_mysql.py @@ -564,9 +564,23 @@ class TestMySQL(Validator): "STR_TO_DATE(x, '%M')", read={"": "TS_OR_DS_TO_DATE(x, '%B')"}, ) + self.validate_all( + "STR_TO_DATE(x, '%Y-%m-%d')", + write={"presto": "CAST(DATE_PARSE(x, '%Y-%m-%d') AS DATE)"}, + ) + self.validate_all( + "STR_TO_DATE(x, '%Y-%m-%dT%T')", write={"presto": "DATE_PARSE(x, '%Y-%m-%dT%T')"} + ) def test_mysql(self): self.validate_all( + # MySQL doesn't support FULL OUTER joins + "SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.x = t2.x UNION SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.x = t2.x", + read={ + "postgres": "SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.x = t2.x", + }, + ) + self.validate_all( "a XOR b", read={ "mysql": "a XOR b", diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py index 3121cb0..0e5f1a1 100644 --- a/tests/dialects/test_postgres.py +++ b/tests/dialects/test_postgres.py @@ -9,22 +9,39 @@ class TestPostgres(Validator): dialect = "postgres" def test_ddl(self): - self.validate_identity( - "CREATE INDEX foo ON bar.baz USING btree(col1 varchar_pattern_ops ASC, col2)" - ) - self.validate_identity( - "CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZONE[][])", - "CREATE TABLE test (x TIMESTAMP[][])", - ) self.validate_identity("CREATE INDEX idx_x ON x USING BTREE(x, y) WHERE (NOT y IS NULL)") self.validate_identity("CREATE TABLE test (elems JSONB[])") self.validate_identity("CREATE TABLE public.y (x TSTZRANGE NOT NULL)") self.validate_identity("CREATE TABLE test (foo HSTORE)") self.validate_identity("CREATE TABLE test (foo JSONB)") self.validate_identity("CREATE TABLE test (foo VARCHAR(64)[])") + self.validate_identity("CREATE TABLE test (foo INT) PARTITION BY HASH(foo)") self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING a") self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING a, b") self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING *") + self.validate_identity("UPDATE tbl_name SET foo = 123 RETURNING a") + self.validate_identity("CREATE TABLE cities_partdef PARTITION OF cities DEFAULT") + self.validate_identity( + "CREATE TABLE cust_part3 PARTITION OF customers FOR VALUES WITH (MODULUS 3, REMAINDER 2)" + ) + self.validate_identity( + "CREATE TABLE measurement_y2016m07 PARTITION OF measurement (unitsales DEFAULT 0) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')" + ) + self.validate_identity( + "CREATE TABLE measurement_ym_older PARTITION OF measurement_year_month FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11)" + ) + self.validate_identity( + "CREATE TABLE measurement_ym_y2016m11 PARTITION OF measurement_year_month FOR VALUES FROM (2016, 11) TO (2016, 12)" + ) + self.validate_identity( + "CREATE TABLE cities_ab PARTITION OF cities (CONSTRAINT city_id_nonzero CHECK (city_id <> 0)) FOR VALUES IN ('a', 'b')" + ) + self.validate_identity( + "CREATE TABLE cities_ab PARTITION OF cities (CONSTRAINT city_id_nonzero CHECK (city_id <> 0)) FOR VALUES IN ('a', 'b') PARTITION BY RANGE(population)" + ) + self.validate_identity( + "CREATE INDEX foo ON bar.baz USING btree(col1 varchar_pattern_ops ASC, col2)" + ) self.validate_identity( "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO NOTHING RETURNING *" ) @@ -43,7 +60,10 @@ class TestPostgres(Validator): self.validate_identity( "DELETE FROM event USING sales AS s WHERE event.eventid = s.eventid RETURNING a" ) - self.validate_identity("UPDATE tbl_name SET foo = 123 RETURNING a") + self.validate_identity( + "CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZONE[][])", + "CREATE TABLE test (x TIMESTAMP[][])", + ) self.validate_all( "CREATE OR REPLACE FUNCTION function_name (input_a character varying DEFAULT NULL::character varying)", @@ -187,6 +207,15 @@ class TestPostgres(Validator): self.validate_identity( """ALTER TABLE ONLY "Album" ADD CONSTRAINT "FK_AlbumArtistId" FOREIGN KEY ("ArtistId") REFERENCES "Artist" ("ArtistId") ON DELETE RESTRICT""" ) + self.validate_identity( + "SELECT * FROM JSON_ARRAY_ELEMENTS('[1,true, [2,false]]') WITH ORDINALITY" + ) + self.validate_identity( + "SELECT * FROM JSON_ARRAY_ELEMENTS('[1,true, [2,false]]') WITH ORDINALITY AS kv_json" + ) + self.validate_identity( + "SELECT * FROM JSON_ARRAY_ELEMENTS('[1,true, [2,false]]') WITH ORDINALITY AS kv_json(a, b)" + ) self.validate_identity("x @@ y") self.validate_identity("CAST(x AS MONEY)") self.validate_identity("CAST(x AS INT4RANGE)") diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py index c848010..3e42525 100644 --- a/tests/dialects/test_redshift.py +++ b/tests/dialects/test_redshift.py @@ -7,9 +7,25 @@ class TestRedshift(Validator): def test_redshift(self): self.validate_identity( + "SELECT DATE_DIFF('month', CAST('2020-02-29 00:00:00' AS TIMESTAMP), CAST('2020-03-02 00:00:00' AS TIMESTAMP))", + "SELECT DATEDIFF(month, CAST(CAST('2020-02-29 00:00:00' AS TIMESTAMP) AS DATE), CAST(CAST('2020-03-02 00:00:00' AS TIMESTAMP) AS DATE))", + ) + self.validate_identity( "SELECT * FROM x WHERE y = DATEADD('month', -1, DATE_TRUNC('month', (SELECT y FROM #temp_table)))", "SELECT * FROM x WHERE y = DATEADD(month, -1, CAST(DATE_TRUNC('month', (SELECT y FROM #temp_table)) AS DATE))", ) + + self.validate_all( + "LISTAGG(sellerid, ', ')", + read={ + "duckdb": "STRING_AGG(sellerid, ', ')", + }, + write={ + # GROUP_CONCAT and STRING_AGG are aliases in DuckDB + "duckdb": "GROUP_CONCAT(sellerid, ', ')", + "redshift": "LISTAGG(sellerid, ', ')", + }, + ) self.validate_all( "SELECT APPROXIMATE COUNT(DISTINCT y)", read={ diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 65b77ea..2cad1d2 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -13,6 +13,7 @@ class TestSnowflake(Validator): expr.selects[0].assert_is(exp.AggFunc) self.assertEqual(expr.sql(dialect="snowflake"), "SELECT APPROX_TOP_K(C4, 3, 5) FROM t") + self.validate_identity("SELECT OBJECT_CONSTRUCT()") self.validate_identity("SELECT DAYOFMONTH(CURRENT_TIMESTAMP())") self.validate_identity("SELECT DAYOFYEAR(CURRENT_TIMESTAMP())") self.validate_identity("LISTAGG(data['some_field'], ',')") @@ -77,6 +78,10 @@ class TestSnowflake(Validator): "SELECT {fn CEILING(5.3)}", "SELECT CEIL(5.3)", ) + self.validate_identity( + "SELECT TO_TIMESTAMP(x) FROM t", + "SELECT CAST(x AS TIMESTAMPNTZ) FROM t", + ) self.validate_all("CAST(x AS BYTEINT)", write={"snowflake": "CAST(x AS INT)"}) self.validate_all("CAST(x AS CHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"}) diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py index e08915b..841a005 100644 --- a/tests/dialects/test_spark.py +++ b/tests/dialects/test_spark.py @@ -592,9 +592,10 @@ TBLPROPERTIES ( self.validate_all( "INSERT OVERWRITE TABLE table WITH cte AS (SELECT cola FROM other_table) SELECT cola FROM cte", write={ - "spark": "WITH cte AS (SELECT cola FROM other_table) INSERT OVERWRITE TABLE table SELECT cola FROM cte", + "databricks": "INSERT OVERWRITE TABLE table WITH cte AS (SELECT cola FROM other_table) SELECT cola FROM cte", + "hive": "WITH cte AS (SELECT cola FROM other_table) INSERT OVERWRITE TABLE table SELECT cola FROM cte", + "spark": "INSERT OVERWRITE TABLE table WITH cte AS (SELECT cola FROM other_table) SELECT cola FROM cte", "spark2": "WITH cte AS (SELECT cola FROM other_table) INSERT OVERWRITE TABLE table SELECT cola FROM cte", - "databricks": "WITH cte AS (SELECT cola FROM other_table) INSERT OVERWRITE TABLE table SELECT cola FROM cte", }, ) diff --git a/tests/dialects/test_teradata.py b/tests/dialects/test_teradata.py index b5c0fe8..14703c4 100644 --- a/tests/dialects/test_teradata.py +++ b/tests/dialects/test_teradata.py @@ -148,18 +148,21 @@ class TestTeradata(Validator): self.validate_all("a MOD b", write={"teradata": "a MOD b", "mysql": "a % b"}) def test_abbrev(self): - self.validate_all("a LT b", write={"teradata": "a < b"}) - self.validate_all("a LE b", write={"teradata": "a <= b"}) - self.validate_all("a GT b", write={"teradata": "a > b"}) - self.validate_all("a GE b", write={"teradata": "a >= b"}) - self.validate_all("a ^= b", write={"teradata": "a <> b"}) - self.validate_all("a NE b", write={"teradata": "a <> b"}) - self.validate_all("a NOT= b", write={"teradata": "a <> b"}) - - self.validate_all( - "SEL a FROM b", - write={"teradata": "SELECT a FROM b"}, - ) + self.validate_identity("a LT b", "a < b") + self.validate_identity("a LE b", "a <= b") + self.validate_identity("a GT b", "a > b") + self.validate_identity("a GE b", "a >= b") + self.validate_identity("a ^= b", "a <> b") + self.validate_identity("a NE b", "a <> b") + self.validate_identity("a NOT= b", "a <> b") + self.validate_identity("a EQ b", "a = b") + self.validate_identity("SEL a FROM b", "SELECT a FROM b") + self.validate_identity( + "SELECT col1, col2 FROM dbc.table1 WHERE col1 EQ 'value1' MINUS SELECT col1, col2 FROM dbc.table2", + "SELECT col1, col2 FROM dbc.table1 WHERE col1 = 'value1' EXCEPT SELECT col1, col2 FROM dbc.table2", + ) + self.validate_identity("UPD a SET b = 1", "UPDATE a SET b = 1") + self.validate_identity("DEL FROM a", "DELETE FROM a") def test_datatype(self): self.validate_all( @@ -187,3 +190,4 @@ class TestTeradata(Validator): "": "STR_TO_DATE('1992-01', '%Y-%d')", }, ) + self.validate_identity("CAST('1992-01' AS FORMAT 'YYYY-DD')") diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index 4775020..0ac94f2 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -7,6 +7,36 @@ class TestTSQL(Validator): def test_tsql(self): self.validate_all( + "WITH t(c) AS (SELECT 1) SELECT * INTO foo FROM (SELECT c FROM t) AS temp", + read={ + "duckdb": "CREATE TABLE foo AS WITH t(c) AS (SELECT 1) SELECT c FROM t", + }, + ) + self.validate_all( + "WITH y AS (SELECT 2 AS c) INSERT INTO t SELECT * FROM y", + read={ + "duckdb": "WITH y AS (SELECT 2 AS c) INSERT INTO t SELECT * FROM y", + }, + ) + self.validate_all( + "WITH t(c) AS (SELECT 1) SELECT 1 AS c UNION (SELECT c FROM t)", + read={ + "duckdb": "SELECT 1 AS c UNION (WITH t(c) AS (SELECT 1) SELECT c FROM t)", + }, + ) + self.validate_all( + "WITH t(c) AS (SELECT 1) MERGE INTO x AS z USING (SELECT c FROM t) AS y ON a = b WHEN MATCHED THEN UPDATE SET a = y.b", + read={ + "postgres": "MERGE INTO x AS z USING (WITH t(c) AS (SELECT 1) SELECT c FROM t) AS y ON a = b WHEN MATCHED THEN UPDATE SET a = y.b", + }, + ) + self.validate_all( + "WITH t(n) AS (SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM t WHERE n < 4) SELECT * FROM (SELECT SUM(n) AS s4 FROM t) AS subq", + read={ + "duckdb": "SELECT * FROM (WITH RECURSIVE t(n) AS (SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM t WHERE n < 4) SELECT SUM(n) AS s4 FROM t) AS subq", + }, + ) + self.validate_all( "CREATE TABLE #mytemptable (a INTEGER)", read={ "duckdb": "CREATE TEMPORARY TABLE mytemptable (a INT)", @@ -825,12 +855,18 @@ WHERE def test_datename(self): self.validate_all( - "SELECT DATENAME(mm,'1970-01-01')", - write={"spark": "SELECT DATE_FORMAT(CAST('1970-01-01' AS TIMESTAMP), 'MMMM')"}, + "SELECT DATENAME(mm, '1970-01-01')", + write={ + "spark": "SELECT DATE_FORMAT(CAST('1970-01-01' AS TIMESTAMP), 'MMMM')", + "tsql": "SELECT FORMAT(CAST('1970-01-01' AS DATETIME2), 'MMMM')", + }, ) self.validate_all( - "SELECT DATENAME(dw,'1970-01-01')", - write={"spark": "SELECT DATE_FORMAT(CAST('1970-01-01' AS TIMESTAMP), 'EEEE')"}, + "SELECT DATENAME(dw, '1970-01-01')", + write={ + "spark": "SELECT DATE_FORMAT(CAST('1970-01-01' AS TIMESTAMP), 'EEEE')", + "tsql": "SELECT FORMAT(CAST('1970-01-01' AS DATETIME2), 'dddd')", + }, ) def test_datepart(self): |