summaryrefslogtreecommitdiffstats
path: root/tests/dialects
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects')
-rw-r--r--tests/dialects/test_bigquery.py18
-rw-r--r--tests/dialects/test_clickhouse.py10
-rw-r--r--tests/dialects/test_dialect.py45
-rw-r--r--tests/dialects/test_hive.py18
-rw-r--r--tests/dialects/test_postgres.py1
-rw-r--r--tests/dialects/test_snowflake.py32
-rw-r--r--tests/dialects/test_spark.py58
7 files changed, 175 insertions, 7 deletions
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 7110eac..8921924 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -153,6 +153,10 @@ class TestBigQuery(Validator):
)
self.validate_identity(
+ "SELECT item, purchases, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular FROM Produce WINDOW item_window AS (ORDER BY purchases)"
+ )
+
+ self.validate_identity(
"SELECT LAST_VALUE(a IGNORE NULLS) OVER y FROM x WINDOW y AS (PARTITION BY CATEGORY)",
)
@@ -223,6 +227,20 @@ class TestBigQuery(Validator):
},
)
self.validate_all(
+ "DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY)",
+ write={
+ "bigquery": "DATE_DIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE), DAY)",
+ "mysql": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))",
+ },
+ )
+ self.validate_all(
+ "DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', MINUTE)",
+ write={
+ "bigquery": "DATE_DIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE), MINUTE)",
+ "mysql": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))",
+ },
+ )
+ self.validate_all(
"CURRENT_DATE('UTC')",
write={
"mysql": "CURRENT_DATE AT TIME ZONE 'UTC'",
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index e5b1516..715bf10 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -8,6 +8,8 @@ class TestClickhouse(Validator):
self.validate_identity("dictGet(x, 'y')")
self.validate_identity("SELECT * FROM x FINAL")
self.validate_identity("SELECT * FROM x AS y FINAL")
+ self.validate_identity("'a' IN mapKeys(map('a', 1, 'b', 2))")
+ self.validate_identity("CAST((1, 2) AS Tuple(a Int8, b Int16))")
self.validate_all(
"SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname",
@@ -20,6 +22,12 @@ class TestClickhouse(Validator):
self.validate_all(
"CAST(1 AS NULLABLE(Int64))",
write={
- "clickhouse": "CAST(1 AS Nullable(BIGINT))",
+ "clickhouse": "CAST(1 AS Nullable(Int64))",
+ },
+ )
+ self.validate_all(
+ "CAST(1 AS Nullable(DateTime64(6, 'UTC')))",
+ write={
+ "clickhouse": "CAST(1 AS Nullable(DateTime64(6, 'UTC')))",
},
)
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index a9a313c..53edb42 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -82,6 +82,24 @@ class TestDialect(Validator):
},
)
self.validate_all(
+ "CAST(MAP('a', '1') AS MAP(TEXT, TEXT))",
+ write={
+ "clickhouse": "CAST(map('a', '1') AS Map(TEXT, TEXT))",
+ },
+ )
+ self.validate_all(
+ "CAST(ARRAY(1, 2) AS ARRAY<TINYINT>)",
+ write={
+ "clickhouse": "CAST([1, 2] AS Array(Int8))",
+ },
+ )
+ self.validate_all(
+ "CAST((1, 2) AS STRUCT<a: TINYINT, b: SMALLINT, c: INT, d: BIGINT>)",
+ write={
+ "clickhouse": "CAST((1, 2) AS Tuple(a Int8, b Int16, c Int32, d Int64))",
+ },
+ )
+ self.validate_all(
"CAST(a AS DATETIME)",
write={
"postgres": "CAST(a AS TIMESTAMP)",
@@ -170,7 +188,7 @@ class TestDialect(Validator):
"CAST(a AS DOUBLE)",
write={
"bigquery": "CAST(a AS FLOAT64)",
- "clickhouse": "CAST(a AS DOUBLE)",
+ "clickhouse": "CAST(a AS Float64)",
"duckdb": "CAST(a AS DOUBLE)",
"mysql": "CAST(a AS DOUBLE)",
"hive": "CAST(a AS DOUBLE)",
@@ -234,6 +252,8 @@ class TestDialect(Validator):
write={
"duckdb": "STRPTIME('2020-01-01', '%Y-%m-%d')",
"hive": "CAST('2020-01-01' AS TIMESTAMP)",
+ "oracle": "TO_TIMESTAMP('2020-01-01', 'YYYY-MM-DD')",
+ "postgres": "TO_TIMESTAMP('2020-01-01', 'YYYY-MM-DD')",
"presto": "DATE_PARSE('2020-01-01', '%Y-%m-%d')",
"redshift": "TO_TIMESTAMP('2020-01-01', 'YYYY-MM-DD')",
"spark": "TO_TIMESTAMP('2020-01-01', 'yyyy-MM-dd')",
@@ -245,6 +265,8 @@ class TestDialect(Validator):
"duckdb": "STRPTIME(x, '%y')",
"hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yy')) AS TIMESTAMP)",
"presto": "DATE_PARSE(x, '%y')",
+ "oracle": "TO_TIMESTAMP(x, 'YY')",
+ "postgres": "TO_TIMESTAMP(x, 'YY')",
"redshift": "TO_TIMESTAMP(x, 'YY')",
"spark": "TO_TIMESTAMP(x, 'yy')",
},
@@ -288,6 +310,8 @@ class TestDialect(Validator):
write={
"duckdb": "STRFTIME(x, '%Y-%m-%d')",
"hive": "DATE_FORMAT(x, 'yyyy-MM-dd')",
+ "oracle": "TO_CHAR(x, 'YYYY-MM-DD')",
+ "postgres": "TO_CHAR(x, 'YYYY-MM-DD')",
"presto": "DATE_FORMAT(x, '%Y-%m-%d')",
"redshift": "TO_CHAR(x, 'YYYY-MM-DD')",
},
@@ -348,6 +372,8 @@ class TestDialect(Validator):
write={
"duckdb": "TO_TIMESTAMP(CAST(x AS BIGINT))",
"hive": "FROM_UNIXTIME(x)",
+ "oracle": "TO_DATE('1970-01-01','YYYY-MM-DD') + (x / 86400)",
+ "postgres": "TO_TIMESTAMP(x)",
"presto": "FROM_UNIXTIME(x)",
"starrocks": "FROM_UNIXTIME(x)",
},
@@ -704,6 +730,7 @@ class TestDialect(Validator):
"SELECT * FROM a UNION SELECT * FROM b",
read={
"bigquery": "SELECT * FROM a UNION DISTINCT SELECT * FROM b",
+ "clickhouse": "SELECT * FROM a UNION DISTINCT SELECT * FROM b",
"duckdb": "SELECT * FROM a UNION SELECT * FROM b",
"presto": "SELECT * FROM a UNION SELECT * FROM b",
"spark": "SELECT * FROM a UNION SELECT * FROM b",
@@ -719,6 +746,7 @@ class TestDialect(Validator):
"SELECT * FROM a UNION ALL SELECT * FROM b",
read={
"bigquery": "SELECT * FROM a UNION ALL SELECT * FROM b",
+ "clickhouse": "SELECT * FROM a UNION ALL SELECT * FROM b",
"duckdb": "SELECT * FROM a UNION ALL SELECT * FROM b",
"presto": "SELECT * FROM a UNION ALL SELECT * FROM b",
"spark": "SELECT * FROM a UNION ALL SELECT * FROM b",
@@ -848,15 +876,28 @@ class TestDialect(Validator):
"postgres": "STRPOS(x, ' ')",
"presto": "STRPOS(x, ' ')",
"spark": "LOCATE(' ', x)",
+ "clickhouse": "position(x, ' ')",
+ "snowflake": "POSITION(' ', x)",
},
)
self.validate_all(
- "STR_POSITION(x, 'a')",
+ "STR_POSITION('a', x)",
write={
"duckdb": "STRPOS(x, 'a')",
"postgres": "STRPOS(x, 'a')",
"presto": "STRPOS(x, 'a')",
"spark": "LOCATE('a', x)",
+ "clickhouse": "position(x, 'a')",
+ "snowflake": "POSITION('a', x)",
+ },
+ )
+ self.validate_all(
+ "POSITION('a', x, 3)",
+ write={
+ "presto": "STRPOS(SUBSTR(x, 3), 'a') + 3 - 1",
+ "spark": "LOCATE('a', x, 3)",
+ "clickhouse": "position(x, 'a', 3)",
+ "snowflake": "POSITION('a', x, 3)",
},
)
self.validate_all(
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index d335921..acb3be9 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -247,7 +247,7 @@ class TestHive(Validator):
"presto": "DATE_DIFF('day', CAST(SUBSTR(CAST(b AS VARCHAR), 1, 10) AS DATE), CAST(SUBSTR(CAST(a AS VARCHAR), 1, 10) AS DATE))",
"hive": "DATEDIFF(TO_DATE(a), TO_DATE(b))",
"spark": "DATEDIFF(TO_DATE(a), TO_DATE(b))",
- "": "DATE_DIFF(TS_OR_DS_TO_DATE(a), TS_OR_DS_TO_DATE(b))",
+ "": "DATEDIFF(TS_OR_DS_TO_DATE(a), TS_OR_DS_TO_DATE(b))",
},
)
self.validate_all(
@@ -295,7 +295,7 @@ class TestHive(Validator):
"presto": "DATE_DIFF('day', CAST(SUBSTR(CAST(x AS VARCHAR), 1, 10) AS DATE), CAST(SUBSTR(CAST(CAST(SUBSTR(CAST(y AS VARCHAR), 1, 10) AS DATE) AS VARCHAR), 1, 10) AS DATE))",
"hive": "DATEDIFF(TO_DATE(TO_DATE(y)), TO_DATE(x))",
"spark": "DATEDIFF(TO_DATE(TO_DATE(y)), TO_DATE(x))",
- "": "DATE_DIFF(TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE(y)), TS_OR_DS_TO_DATE(x))",
+ "": "DATEDIFF(TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE(y)), TS_OR_DS_TO_DATE(x))",
},
)
self.validate_all(
@@ -450,11 +450,21 @@ class TestHive(Validator):
)
self.validate_all(
"MAP(a, b, c, d)",
+ read={
+ "": "VAR_MAP(a, b, c, d)",
+ "clickhouse": "map(a, b, c, d)",
+ "duckdb": "MAP(LIST_VALUE(a, c), LIST_VALUE(b, d))",
+ "hive": "MAP(a, b, c, d)",
+ "presto": "MAP(ARRAY[a, c], ARRAY[b, d])",
+ "spark": "MAP(a, b, c, d)",
+ },
write={
+ "": "MAP(ARRAY(a, c), ARRAY(b, d))",
+ "clickhouse": "map(a, b, c, d)",
"duckdb": "MAP(LIST_VALUE(a, c), LIST_VALUE(b, d))",
"presto": "MAP(ARRAY[a, c], ARRAY[b, d])",
"hive": "MAP(a, b, c, d)",
- "spark": "MAP_FROM_ARRAYS(ARRAY(a, c), ARRAY(b, d))",
+ "spark": "MAP(a, b, c, d)",
},
)
self.validate_all(
@@ -463,7 +473,7 @@ class TestHive(Validator):
"duckdb": "MAP(LIST_VALUE(a), LIST_VALUE(b))",
"presto": "MAP(ARRAY[a], ARRAY[b])",
"hive": "MAP(a, b)",
- "spark": "MAP_FROM_ARRAYS(ARRAY(a), ARRAY(b))",
+ "spark": "MAP(a, b)",
},
)
self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index e0934d7..dc93c3a 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -67,6 +67,7 @@ class TestPostgres(Validator):
self.validate_identity("SELECT SUBSTRING('bla' + 'foo' || 'bar' FROM 3 - 1 + 5 FOR 4 + SOME_FUNC(arg1, arg2))")
self.validate_identity("SELECT TRIM(' X' FROM ' XXX ')")
self.validate_identity("SELECT TRIM(LEADING 'bla' FROM ' XXX ' COLLATE utf8_bin)")
+ self.validate_identity("SELECT TO_TIMESTAMP(1284352323.5), TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY')")
self.validate_all(
"CREATE TABLE x (a UUID, b BYTEA)",
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 2145966..8a33e2d 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -305,3 +305,35 @@ class TestSnowflake(Validator):
self.validate_identity(
"CREATE PROCEDURE a.b.c(x INT, y VARIANT) RETURNS OBJECT EXECUTE AS CALLER AS 'BEGIN SELECT 1; END;'"
)
+
+ def test_table_literal(self):
+ # All examples from https://docs.snowflake.com/en/sql-reference/literals-table.html
+ self.validate_all(
+ r"""SELECT * FROM TABLE('MYTABLE')""", write={"snowflake": r"""SELECT * FROM TABLE('MYTABLE')"""}
+ )
+
+ self.validate_all(
+ r"""SELECT * FROM TABLE('MYDB."MYSCHEMA"."MYTABLE"')""",
+ write={"snowflake": r"""SELECT * FROM TABLE('MYDB."MYSCHEMA"."MYTABLE"')"""},
+ )
+
+ # Per Snowflake documentation at https://docs.snowflake.com/en/sql-reference/literals-table.html
+ # one can use either a " ' " or " $$ " to enclose the object identifier.
+ # Capturing the single tokens seems like lot of work. Hence adjusting tests to use these interchangeably,
+ self.validate_all(
+ r"""SELECT * FROM TABLE($$MYDB. "MYSCHEMA"."MYTABLE"$$)""",
+ write={"snowflake": r"""SELECT * FROM TABLE('MYDB. "MYSCHEMA"."MYTABLE"')"""},
+ )
+
+ self.validate_all(r"""SELECT * FROM TABLE($MYVAR)""", write={"snowflake": r"""SELECT * FROM TABLE($MYVAR)"""})
+
+ self.validate_all(r"""SELECT * FROM TABLE(?)""", write={"snowflake": r"""SELECT * FROM TABLE(?)"""})
+
+ self.validate_all(
+ r"""SELECT * FROM TABLE(:BINDING)""", write={"snowflake": r"""SELECT * FROM TABLE(:BINDING)"""}
+ )
+
+ self.validate_all(
+ r"""SELECT * FROM TABLE($MYVAR) WHERE COL1 = 10""",
+ write={"snowflake": r"""SELECT * FROM TABLE($MYVAR) WHERE COL1 = 10"""},
+ )
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 8377e47..9a7e64c 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -111,12 +111,70 @@ TBLPROPERTIES (
"SELECT /*+ COALESCE(3) */ * FROM x",
write={
"spark": "SELECT /*+ COALESCE(3) */ * FROM x",
+ "bigquery": "SELECT * FROM x",
},
)
self.validate_all(
"SELECT /*+ COALESCE(3), REPARTITION(1) */ * FROM x",
write={
"spark": "SELECT /*+ COALESCE(3), REPARTITION(1) */ * FROM x",
+ "bigquery": "SELECT * FROM x",
+ },
+ )
+ self.validate_all(
+ "SELECT /*+ BROADCAST(table) */ cola FROM table",
+ write={
+ "spark": "SELECT /*+ BROADCAST(table) */ cola FROM table",
+ "bigquery": "SELECT cola FROM table",
+ },
+ )
+ self.validate_all(
+ "SELECT /*+ BROADCASTJOIN(table) */ cola FROM table",
+ write={
+ "spark": "SELECT /*+ BROADCASTJOIN(table) */ cola FROM table",
+ "bigquery": "SELECT cola FROM table",
+ },
+ )
+ self.validate_all(
+ "SELECT /*+ MAPJOIN(table) */ cola FROM table",
+ write={
+ "spark": "SELECT /*+ MAPJOIN(table) */ cola FROM table",
+ "bigquery": "SELECT cola FROM table",
+ },
+ )
+ self.validate_all(
+ "SELECT /*+ MERGE(table) */ cola FROM table",
+ write={
+ "spark": "SELECT /*+ MERGE(table) */ cola FROM table",
+ "bigquery": "SELECT cola FROM table",
+ },
+ )
+ self.validate_all(
+ "SELECT /*+ SHUFFLEMERGE(table) */ cola FROM table",
+ write={
+ "spark": "SELECT /*+ SHUFFLEMERGE(table) */ cola FROM table",
+ "bigquery": "SELECT cola FROM table",
+ },
+ )
+ self.validate_all(
+ "SELECT /*+ MERGEJOIN(table) */ cola FROM table",
+ write={
+ "spark": "SELECT /*+ MERGEJOIN(table) */ cola FROM table",
+ "bigquery": "SELECT cola FROM table",
+ },
+ )
+ self.validate_all(
+ "SELECT /*+ SHUFFLE_HASH(table) */ cola FROM table",
+ write={
+ "spark": "SELECT /*+ SHUFFLE_HASH(table) */ cola FROM table",
+ "bigquery": "SELECT cola FROM table",
+ },
+ )
+ self.validate_all(
+ "SELECT /*+ SHUFFLE_REPLICATE_NL(table) */ cola FROM table",
+ write={
+ "spark": "SELECT /*+ SHUFFLE_REPLICATE_NL(table) */ cola FROM table",
+ "bigquery": "SELECT cola FROM table",
},
)
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-01 06:48:08 +0000