summaryrefslogtreecommitdiffstats
path: root/tests/dialects
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects')
-rw-r--r--tests/dialects/test_databricks.py15
-rw-r--r--tests/dialects/test_dialect.py4
-rw-r--r--tests/dialects/test_duckdb.py22
-rw-r--r--tests/dialects/test_hive.py38
-rw-r--r--tests/dialects/test_postgres.py45
-rw-r--r--tests/dialects/test_presto.py7
-rw-r--r--tests/dialects/test_snowflake.py32
-rw-r--r--tests/dialects/test_spark.py9
8 files changed, 157 insertions, 15 deletions
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index 7560d61..1d80dc0 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -102,3 +102,18 @@ class TestDatabricks(Validator):
"databricks": "SELECT DATEADD(DAY, 1, '2020-01-01')",
},
)
+
+ def test_without_as(self):
+ self.validate_all(
+ "CREATE TABLE x (SELECT 1)",
+ write={
+ "databricks": "CREATE TABLE x AS (SELECT 1)",
+ },
+ )
+
+ self.validate_all(
+ "WITH x (select 1) SELECT * FROM x",
+ write={
+ "databricks": "WITH x AS (SELECT 1) SELECT * FROM x",
+ },
+ )
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index f1144ce..5a13655 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -9,9 +9,9 @@ class Validator(unittest.TestCase):
def parse_one(self, sql):
return parse_one(sql, read=self.dialect)
- def validate_identity(self, sql, write_sql=None):
+ def validate_identity(self, sql, write_sql=None, pretty=False):
expression = self.parse_one(sql)
- self.assertEqual(write_sql or sql, expression.sql(dialect=self.dialect))
+ self.assertEqual(write_sql or sql, expression.sql(dialect=self.dialect, pretty=pretty))
return expression
def validate_all(self, sql, read=None, write=None, pretty=False, identify=False):
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index a37062c..f6446ca 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -75,6 +75,19 @@ class TestDuckDB(Validator):
)
def test_duckdb(self):
+ self.validate_identity("SELECT {'a': 1} AS x")
+ self.validate_identity("SELECT {'a': {'b': {'c': 1}}, 'd': {'e': 2}} AS x")
+ self.validate_identity("SELECT {'x': 1, 'y': 2, 'z': 3}")
+ self.validate_identity(
+ "SELECT {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'}"
+ )
+ self.validate_identity("SELECT {'key1': 'string', 'key2': 1, 'key3': 12.345}")
+ self.validate_identity("SELECT ROW(x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)")
+ self.validate_identity("SELECT (x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)")
+ self.validate_identity("SELECT a.x FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a)")
+ self.validate_identity(
+ "SELECT a['x space'] FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a)"
+ )
self.validate_all(
"CREATE TABLE IF NOT EXISTS table (cola INT, colb STRING) USING ICEBERG PARTITIONED BY (colb)",
write={
@@ -229,11 +242,18 @@ class TestDuckDB(Validator):
self.validate_all(
"STRUCT_PACK(x := 1, y := '2')",
write={
- "duckdb": "STRUCT_PACK(x := 1, y := '2')",
+ "duckdb": "{'x': 1, 'y': '2'}",
"spark": "STRUCT(x = 1, y = '2')",
},
)
self.validate_all(
+ "STRUCT_PACK(key1 := 'value1', key2 := 42)",
+ write={
+ "duckdb": "{'key1': 'value1', 'key2': 42}",
+ "spark": "STRUCT(key1 = 'value1', key2 = 42)",
+ },
+ )
+ self.validate_all(
"ARRAY_SORT(x)",
write={
"duckdb": "ARRAY_SORT(x)",
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index d485593..c41e4f7 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -338,6 +338,27 @@ class TestHive(Validator):
)
def test_hive(self):
+ self.validate_identity(
+ "INSERT OVERWRITE TABLE zipcodes PARTITION(state = '0') VALUES (896, 'US', 'TAMPA', 33607)"
+ )
+ self.validate_identity(
+ "INSERT OVERWRITE TABLE zipcodes PARTITION(state = 0) VALUES (896, 'US', 'TAMPA', 33607)"
+ )
+ self.validate_identity(
+ "SELECT a, b, SUM(c) FROM tabl AS t GROUP BY a, b GROUPING SETS ((a, b), a)"
+ )
+ self.validate_identity(
+ "SELECT a, b, SUM(c) FROM tabl AS t GROUP BY a, b GROUPING SETS ((t.a, b), a)"
+ )
+ self.validate_identity(
+ "SELECT a, b, SUM(c) FROM tabl AS t GROUP BY a, FOO(b) GROUPING SETS ((a, FOO(b)), a)"
+ )
+ self.validate_identity(
+ "SELECT key, value, GROUPING__ID, COUNT(*) FROM T1 GROUP BY key, value WITH CUBE"
+ )
+ self.validate_identity(
+ "SELECT key, value, GROUPING__ID, COUNT(*) FROM T1 GROUP BY key, value WITH ROLLUP"
+ )
self.validate_all(
"SELECT A.1a AS b FROM test_a AS A",
write={
@@ -615,3 +636,20 @@ class TestHive(Validator):
"spark": "SELECT * FROM x TABLESAMPLE(1) AS foo",
},
)
+ self.validate_all(
+ "SELECT * FROM x TABLESAMPLE(1) AS foo",
+ read={
+ "presto": "SELECT * FROM x AS foo TABLESAMPLE(1)",
+ },
+ write={
+ "presto": "SELECT * FROM x AS foo TABLESAMPLE(1)",
+ "hive": "SELECT * FROM x TABLESAMPLE(1) AS foo",
+ "spark": "SELECT * FROM x TABLESAMPLE(1) AS foo",
+ },
+ )
+ self.validate_all(
+ "SELECT a, SUM(c) FROM t GROUP BY a, DATE_FORMAT(b, 'yyyy') GROUPING SETS ((a, DATE_FORMAT(b, 'yyyy')), a)",
+ write={
+ "hive": "SELECT a, SUM(c) FROM t GROUP BY a, DATE_FORMAT(CAST(b AS TIMESTAMP), 'yyyy') GROUPING SETS ((a, DATE_FORMAT(CAST(b AS TIMESTAMP), 'yyyy')), a)",
+ },
+ )
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 2351e3b..8a17b78 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -56,7 +56,22 @@ class TestPostgres(Validator):
)
def test_postgres(self):
+ self.validate_all(
+ "x ^ y",
+ write={
+ "": "POWER(x, y)",
+ "postgres": "x ^ y",
+ },
+ )
+ self.validate_all(
+ "x # y",
+ write={
+ "": "x ^ y",
+ "postgres": "x # y",
+ },
+ )
self.validate_identity("SELECT ARRAY[1, 2, 3]")
+ self.validate_identity("SELECT ARRAY(SELECT 1)")
self.validate_identity("SELECT ARRAY_LENGTH(ARRAY[1, 2, 3], 1)")
self.validate_identity("STRING_AGG(x, y)")
self.validate_identity("STRING_AGG(x, ',' ORDER BY y)")
@@ -88,6 +103,14 @@ class TestPostgres(Validator):
self.validate_identity("SELECT e'\\xDEADBEEF'")
self.validate_identity("SELECT CAST(e'\\176' AS BYTEA)")
self.validate_identity("""SELECT * FROM JSON_TO_RECORDSET(z) AS y("rank" INT)""")
+ self.validate_identity(
+ "SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
+ )
+ self.validate_identity(
+ "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
+ )
+ self.validate_identity("x ~ 'y'")
+ self.validate_identity("x ~* 'y'")
self.validate_all(
"END WORK AND NO CHAIN",
@@ -118,10 +141,6 @@ class TestPostgres(Validator):
"SELECT to_timestamp(123)::time without time zone",
write={"postgres": "SELECT CAST(TO_TIMESTAMP(123) AS TIME)"},
)
-
- self.validate_identity(
- "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
- )
self.validate_all(
"SELECT SUM(x) OVER (PARTITION BY a ORDER BY d ROWS 1 PRECEDING)",
write={
@@ -283,9 +302,6 @@ class TestPostgres(Validator):
"UPDATE MYTABLE T1 SET T1.COL = 13",
write={"postgres": "UPDATE MYTABLE AS T1 SET T1.COL = 13"},
)
-
- self.validate_identity("x ~ 'y'")
- self.validate_identity("x ~* 'y'")
self.validate_all(
"x !~ 'y'",
write={"postgres": "NOT x ~ 'y'"},
@@ -319,13 +335,20 @@ class TestPostgres(Validator):
"'x' 'y' 'z'",
write={"postgres": "CONCAT('x', 'y', 'z')"},
)
- self.validate_identity("SELECT ARRAY(SELECT 1)")
-
self.validate_all(
"x::cstring",
write={"postgres": "CAST(x AS CSTRING)"},
)
+ self.validate_all(
+ "TRIM(BOTH 'as' FROM 'as string as')",
+ write={
+ "postgres": "TRIM(BOTH 'as' FROM 'as string as')",
+ "spark": "TRIM(BOTH 'as' FROM 'as string as')",
+ },
+ )
- self.validate_identity(
- "SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
+ def test_bool_or(self):
+ self.validate_all(
+ "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
+ write={"postgres": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
)
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 195e382..5ecd69a 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -174,6 +174,13 @@ class TestPresto(Validator):
"spark": "DATE_ADD(x, 1)",
},
)
+ self.validate_all(
+ "NOW()",
+ write={
+ "presto": "CURRENT_TIMESTAMP()",
+ "hive": "CURRENT_TIMESTAMP()",
+ },
+ )
def test_ddl(self):
self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 7bac166..f3e8e24 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -571,3 +571,35 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS f, LATERA
"spark": "DESCRIBE db.table",
},
)
+
+ def test_match_recognize(self):
+ for row in (
+ "ONE ROW PER MATCH",
+ "ALL ROWS PER MATCH",
+ "ALL ROWS PER MATCH SHOW EMPTY MATCHES",
+ "ALL ROWS PER MATCH OMIT EMPTY MATCHES",
+ "ALL ROWS PER MATCH WITH UNMATCHED ROWS",
+ ):
+ for after in (
+ "AFTER MATCH SKIP",
+ "AFTER MATCH SKIP PAST LAST ROW",
+ "AFTER MATCH SKIP TO NEXT ROW",
+ "AFTER MATCH SKIP TO FIRST x",
+ "AFTER MATCH SKIP TO LAST x",
+ ):
+ self.validate_identity(
+ f"""SELECT
+ *
+FROM x
+MATCH_RECOGNIZE (
+ PARTITION BY a, b
+ ORDER BY
+ x DESC
+ MEASURES y AS b
+ {row}
+ {after}
+ PATTERN (^ S1 S2*? ( {{- S3 -}} S4 )+ | PERMUTE(S1, S2){{1,2}} $)
+ DEFINE x AS y
+)""",
+ pretty=True,
+ )
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index fad858c..02d43aa 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -208,6 +208,13 @@ TBLPROPERTIES (
def test_spark(self):
self.validate_identity("SELECT UNIX_TIMESTAMP()")
+ self.validate_identity("TRIM(' SparkSQL ')")
+ self.validate_identity("TRIM(BOTH 'SL' FROM 'SSparkSQLS')")
+ self.validate_identity("TRIM(LEADING 'SL' FROM 'SSparkSQLS')")
+ self.validate_identity("TRIM(TRAILING 'SL' FROM 'SSparkSQLS')")
+ self.validate_all(
+ "TRIM('SL', 'SSparkSQLS')", write={"spark": "TRIM('SL' FROM 'SSparkSQLS')"}
+ )
self.validate_all(
"ARRAY_SORT(x, (left, right) -> -1)",
write={
@@ -314,5 +321,5 @@ TBLPROPERTIES (
def test_bool_or(self):
self.validate_all(
"SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
- write={"duckdb": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
+ write={"spark": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
)
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-02 16:53:11 +0000