summaryrefslogtreecommitdiffstats
path: root/tests/dialects
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects')
-rw-r--r--tests/dialects/test_bigquery.py5
-rw-r--r--tests/dialects/test_clickhouse.py4
-rw-r--r--tests/dialects/test_dialect.py78
-rw-r--r--tests/dialects/test_drill.py6
-rw-r--r--tests/dialects/test_duckdb.py5
-rw-r--r--tests/dialects/test_presto.py41
-rw-r--r--tests/dialects/test_redshift.py9
-rw-r--r--tests/dialects/test_snowflake.py35
-rw-r--r--tests/dialects/test_spark.py3
-rw-r--r--tests/dialects/test_sqlite.py27
10 files changed, 191 insertions, 22 deletions
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 22387da..e731b50 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -8,6 +8,9 @@ class TestBigQuery(Validator):
def test_bigquery(self):
self.validate_identity("SELECT STRUCT<ARRAY<STRING>>(['2023-01-17'])")
self.validate_identity("SELECT * FROM q UNPIVOT(values FOR quarter IN (b, c))")
+ self.validate_identity(
+ "SELECT * FROM (SELECT * FROM `t`) AS a UNPIVOT((c) FOR c_name IN (v1, v2))"
+ )
self.validate_all("LEAST(x, y)", read={"sqlite": "MIN(x, y)"})
self.validate_all(
@@ -280,7 +283,7 @@ class TestBigQuery(Validator):
"duckdb": "CURRENT_DATE + INTERVAL 1 DAY",
"mysql": "DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)",
"postgres": "CURRENT_DATE + INTERVAL '1' DAY",
- "presto": "DATE_ADD(DAY, 1, CURRENT_DATE)",
+ "presto": "DATE_ADD('DAY', 1, CURRENT_DATE)",
"hive": "DATE_ADD(CURRENT_DATE, 1)",
"spark": "DATE_ADD(CURRENT_DATE, 1)",
},
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 905e1f4..d206bb1 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -16,6 +16,10 @@ class TestClickhouse(Validator):
self.validate_identity("SELECT * FROM foo ANY JOIN bla")
self.validate_identity("SELECT quantile(0.5)(a)")
self.validate_identity("SELECT quantiles(0.5)(a) AS x FROM t")
+ self.validate_identity("SELECT quantiles(0.1, 0.2, 0.3)(a)")
+ self.validate_identity("SELECT histogram(5)(a)")
+ self.validate_identity("SELECT groupUniqArray(2)(a)")
+ self.validate_identity("SELECT exponentialTimeDecayedAvg(60)(a, b)")
self.validate_identity("SELECT * FROM foo WHERE x GLOBAL IN (SELECT * FROM bar)")
self.validate_identity("position(haystack, needle)")
self.validate_identity("position(haystack, needle, position)")
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 21f6be6..6214c43 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -519,7 +519,7 @@ class TestDialect(Validator):
"duckdb": "x + INTERVAL 1 day",
"hive": "DATE_ADD(x, 1)",
"mysql": "DATE_ADD(x, INTERVAL 1 DAY)",
- "postgres": "x + INTERVAL '1' 'day'",
+ "postgres": "x + INTERVAL '1' day",
"presto": "DATE_ADD('day', 1, x)",
"snowflake": "DATEADD(day, 1, x)",
"spark": "DATE_ADD(x, 1)",
@@ -543,12 +543,49 @@ class TestDialect(Validator):
)
self.validate_all(
"DATE_TRUNC('day', x)",
+ read={
+ "bigquery": "DATE_TRUNC(x, day)",
+ "duckdb": "DATE_TRUNC('day', x)",
+ "spark": "TRUNC(x, 'day')",
+ },
write={
+ "bigquery": "DATE_TRUNC(x, day)",
+ "duckdb": "DATE_TRUNC('day', x)",
"mysql": "DATE(x)",
+ "presto": "DATE_TRUNC('day', x)",
+ "postgres": "DATE_TRUNC('day', x)",
"snowflake": "DATE_TRUNC('day', x)",
+ "starrocks": "DATE_TRUNC('day', x)",
+ "spark": "TRUNC(x, 'day')",
+ },
+ )
+ self.validate_all(
+ "TIMESTAMP_TRUNC(x, day)",
+ read={
+ "bigquery": "TIMESTAMP_TRUNC(x, day)",
+ "presto": "DATE_TRUNC('day', x)",
+ "postgres": "DATE_TRUNC('day', x)",
+ "snowflake": "DATE_TRUNC('day', x)",
+ "starrocks": "DATE_TRUNC('day', x)",
+ "spark": "DATE_TRUNC('day', x)",
+ },
+ )
+ self.validate_all(
+ "DATE_TRUNC('day', CAST(x AS DATE))",
+ read={
+ "presto": "DATE_TRUNC('day', x::DATE)",
+ "snowflake": "DATE_TRUNC('day', x::DATE)",
},
)
self.validate_all(
+ "TIMESTAMP_TRUNC(CAST(x AS DATE), day)",
+ read={
+ "postgres": "DATE_TRUNC('day', x::DATE)",
+ "starrocks": "DATE_TRUNC('day', x::DATE)",
+ },
+ )
+
+ self.validate_all(
"DATE_TRUNC('week', x)",
write={
"mysql": "STR_TO_DATE(CONCAT(YEAR(x), ' ', WEEK(x, 1), ' 1'), '%Y %u %w')",
@@ -582,8 +619,6 @@ class TestDialect(Validator):
"DATE_TRUNC('year', x)",
read={
"bigquery": "DATE_TRUNC(x, year)",
- "snowflake": "DATE_TRUNC(year, x)",
- "starrocks": "DATE_TRUNC('year', x)",
"spark": "TRUNC(x, 'year')",
},
write={
@@ -599,7 +634,10 @@ class TestDialect(Validator):
"TIMESTAMP_TRUNC(x, year)",
read={
"bigquery": "TIMESTAMP_TRUNC(x, year)",
+ "postgres": "DATE_TRUNC(year, x)",
"spark": "DATE_TRUNC('year', x)",
+ "snowflake": "DATE_TRUNC(year, x)",
+ "starrocks": "DATE_TRUNC('year', x)",
},
write={
"bigquery": "TIMESTAMP_TRUNC(x, year)",
@@ -752,7 +790,6 @@ class TestDialect(Validator):
"trino": "REDUCE(x, 0, (acc, x) -> acc + x, acc -> acc)",
"duckdb": "REDUCE(x, 0, (acc, x) -> acc + x, acc -> acc)",
"hive": "REDUCE(x, 0, (acc, x) -> acc + x, acc -> acc)",
- "presto": "REDUCE(x, 0, (acc, x) -> acc + x, acc -> acc)",
"spark": "AGGREGATE(x, 0, (acc, x) -> acc + x, acc -> acc)",
"presto": "REDUCE(x, 0, (acc, x) -> acc + x, acc -> acc)",
},
@@ -1455,3 +1492,36 @@ SELECT
"postgres": "SUBSTRING('123456' FROM 2 FOR 3)",
},
)
+
+ def test_count_if(self):
+ self.validate_identity("COUNT_IF(DISTINCT cond)")
+
+ self.validate_all(
+ "SELECT COUNT_IF(cond) FILTER", write={"": "SELECT COUNT_IF(cond) AS FILTER"}
+ )
+ self.validate_all(
+ "SELECT COUNT_IF(col % 2 = 0) FROM foo",
+ write={
+ "": "SELECT COUNT_IF(col % 2 = 0) FROM foo",
+ "databricks": "SELECT COUNT_IF(col % 2 = 0) FROM foo",
+ "presto": "SELECT COUNT_IF(col % 2 = 0) FROM foo",
+ "snowflake": "SELECT COUNT_IF(col % 2 = 0) FROM foo",
+ "sqlite": "SELECT SUM(CASE WHEN col % 2 = 0 THEN 1 ELSE 0 END) FROM foo",
+ "tsql": "SELECT COUNT_IF(col % 2 = 0) FROM foo",
+ },
+ )
+ self.validate_all(
+ "SELECT COUNT_IF(col % 2 = 0) FILTER(WHERE col < 1000) FROM foo",
+ read={
+ "": "SELECT COUNT_IF(col % 2 = 0) FILTER(WHERE col < 1000) FROM foo",
+ "databricks": "SELECT COUNT_IF(col % 2 = 0) FILTER(WHERE col < 1000) FROM foo",
+ "tsql": "SELECT COUNT_IF(col % 2 = 0) FILTER(WHERE col < 1000) FROM foo",
+ },
+ write={
+ "": "SELECT COUNT_IF(col % 2 = 0) FILTER(WHERE col < 1000) FROM foo",
+ "databricks": "SELECT COUNT_IF(col % 2 = 0) FILTER(WHERE col < 1000) FROM foo",
+ "presto": "SELECT COUNT_IF(col % 2 = 0) FILTER(WHERE col < 1000) FROM foo",
+ "sqlite": "SELECT SUM(CASE WHEN col % 2 = 0 THEN 1 ELSE 0 END) FILTER(WHERE col < 1000) FROM foo",
+ "tsql": "SELECT COUNT_IF(col % 2 = 0) FILTER(WHERE col < 1000) FROM foo",
+ },
+ )
diff --git a/tests/dialects/test_drill.py b/tests/dialects/test_drill.py
index e41bd34..f035176 100644
--- a/tests/dialects/test_drill.py
+++ b/tests/dialects/test_drill.py
@@ -4,6 +4,12 @@ from tests.dialects.test_dialect import Validator
class TestDrill(Validator):
dialect = "drill"
+ def test_drill(self):
+ self.validate_all(
+ "DATE_FORMAT(a, 'yyyy')",
+ write={"drill": "TO_CHAR(a, 'yyyy')"},
+ )
+
def test_string_literals(self):
self.validate_all(
"SELECT '2021-01-01' + INTERVAL 1 MONTH",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index c314163..1cabade 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -125,6 +125,11 @@ class TestDuckDB(Validator):
"SELECT a['x space'] FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a)"
)
+ self.validate_all("SELECT * FROM 'x.y'", write={"duckdb": 'SELECT * FROM "x.y"'})
+ self.validate_all(
+ "WITH 'x' AS (SELECT 1) SELECT * FROM x",
+ write={"duckdb": 'WITH "x" AS (SELECT 1) SELECT * FROM x'},
+ )
self.validate_all(
"CREATE TABLE IF NOT EXISTS table (cola INT, colb STRING) USING ICEBERG PARTITIONED BY (colb)",
write={
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index bf22652..0a9111c 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -63,8 +63,8 @@ class TestPresto(Validator):
"bigquery": "CAST(x AS TIMESTAMPTZ)",
"duckdb": "CAST(x AS TIMESTAMPTZ(9))",
"presto": "CAST(x AS TIMESTAMP(9) WITH TIME ZONE)",
- "hive": "CAST(x AS TIMESTAMPTZ)",
- "spark": "CAST(x AS TIMESTAMPTZ)",
+ "hive": "CAST(x AS TIMESTAMP)",
+ "spark": "CAST(x AS TIMESTAMP)",
},
)
@@ -189,34 +189,38 @@ class TestPresto(Validator):
)
self.validate_all(
- "DAY_OF_WEEK(timestamp '2012-08-08 01:00')",
+ "DAY_OF_WEEK(timestamp '2012-08-08 01:00:00')",
write={
- "spark": "DAYOFWEEK(CAST('2012-08-08 01:00' AS TIMESTAMP))",
- "presto": "DAY_OF_WEEK(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+ "spark": "DAYOFWEEK(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
+ "presto": "DAY_OF_WEEK(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
+ "duckdb": "DAYOFWEEK(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
},
)
self.validate_all(
- "DAY_OF_MONTH(timestamp '2012-08-08 01:00')",
+ "DAY_OF_MONTH(timestamp '2012-08-08 01:00:00')",
write={
- "spark": "DAYOFMONTH(CAST('2012-08-08 01:00' AS TIMESTAMP))",
- "presto": "DAY_OF_MONTH(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+ "spark": "DAYOFMONTH(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
+ "presto": "DAY_OF_MONTH(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
+ "duckdb": "DAYOFMONTH(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
},
)
self.validate_all(
- "DAY_OF_YEAR(timestamp '2012-08-08 01:00')",
+ "DAY_OF_YEAR(timestamp '2012-08-08 01:00:00')",
write={
- "spark": "DAYOFYEAR(CAST('2012-08-08 01:00' AS TIMESTAMP))",
- "presto": "DAY_OF_YEAR(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+ "spark": "DAYOFYEAR(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
+ "presto": "DAY_OF_YEAR(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
+ "duckdb": "DAYOFYEAR(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
},
)
self.validate_all(
- "WEEK_OF_YEAR(timestamp '2012-08-08 01:00')",
+ "WEEK_OF_YEAR(timestamp '2012-08-08 01:00:00')",
write={
- "spark": "WEEKOFYEAR(CAST('2012-08-08 01:00' AS TIMESTAMP))",
- "presto": "WEEK_OF_YEAR(CAST('2012-08-08 01:00' AS TIMESTAMP))",
+ "spark": "WEEKOFYEAR(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
+ "presto": "WEEK_OF_YEAR(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
+ "duckdb": "WEEKOFYEAR(CAST('2012-08-08 01:00:00' AS TIMESTAMP))",
},
)
@@ -366,6 +370,15 @@ class TestPresto(Validator):
self.validate_identity("APPROX_PERCENTILE(a, b, c, d)")
self.validate_all(
+ "ARRAY_AGG(x ORDER BY y DESC)",
+ write={
+ "hive": "COLLECT_LIST(x)",
+ "presto": "ARRAY_AGG(x ORDER BY y DESC)",
+ "spark": "COLLECT_LIST(x)",
+ "trino": "ARRAY_AGG(x ORDER BY y DESC)",
+ },
+ )
+ self.validate_all(
"SELECT a FROM t GROUP BY a, ROLLUP(b), ROLLUP(c), ROLLUP(d)",
write={
"presto": "SELECT a FROM t GROUP BY a, ROLLUP (b, c, d)",
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index f5b8a43..ff730f8 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -7,6 +7,9 @@ class TestRedshift(Validator):
def test_redshift(self):
self.validate_all("CONVERT(INTEGER, x)", write={"redshift": "CAST(x AS INTEGER)"})
self.validate_all(
+ "DATEADD('day', ndays, caldate)", write={"redshift": "DATEADD(day, ndays, caldate)"}
+ )
+ self.validate_all(
'create table "group" ("col" char(10))',
write={
"redshift": 'CREATE TABLE "group" ("col" CHAR(10))',
@@ -80,10 +83,10 @@ class TestRedshift(Validator):
},
)
self.validate_all(
- "DATEDIFF(d, a, b)",
+ "DATEDIFF('day', a, b)",
write={
- "redshift": "DATEDIFF(d, a, b)",
- "presto": "DATE_DIFF(d, a, b)",
+ "redshift": "DATEDIFF(day, a, b)",
+ "presto": "DATE_DIFF('day', a, b)",
},
)
self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 1ac910c..5f6efce 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -18,6 +18,41 @@ class TestSnowflake(Validator):
self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'")
self.validate_all(
+ "SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1",
+ write={
+ "": "SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o NULLS LAST) = 1",
+ "databricks": "SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o NULLS LAST) = 1",
+ "hive": "SELECT i, p, o FROM (SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o NULLS LAST) AS _w FROM qt) AS _t WHERE _w = 1",
+ "presto": "SELECT i, p, o FROM (SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS _w FROM qt) AS _t WHERE _w = 1",
+ "snowflake": "SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1",
+ "spark": "SELECT i, p, o FROM (SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o NULLS LAST) AS _w FROM qt) AS _t WHERE _w = 1",
+ "sqlite": "SELECT i, p, o FROM (SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o NULLS LAST) AS _w FROM qt) AS _t WHERE _w = 1",
+ "trino": "SELECT i, p, o FROM (SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS _w FROM qt) AS _t WHERE _w = 1",
+ },
+ )
+ self.validate_all(
+ "SELECT BOOLOR_AGG(c1), BOOLOR_AGG(c2) FROM test",
+ write={
+ "": "SELECT LOGICAL_OR(c1), LOGICAL_OR(c2) FROM test",
+ "duckdb": "SELECT BOOL_OR(c1), BOOL_OR(c2) FROM test",
+ "postgres": "SELECT BOOL_OR(c1), BOOL_OR(c2) FROM test",
+ "snowflake": "SELECT BOOLOR_AGG(c1), BOOLOR_AGG(c2) FROM test",
+ "spark": "SELECT BOOL_OR(c1), BOOL_OR(c2) FROM test",
+ "sqlite": "SELECT MAX(c1), MAX(c2) FROM test",
+ },
+ )
+ self.validate_all(
+ "SELECT BOOLAND_AGG(c1), BOOLAND_AGG(c2) FROM test",
+ write={
+ "": "SELECT LOGICAL_AND(c1), LOGICAL_AND(c2) FROM test",
+ "duckdb": "SELECT BOOL_AND(c1), BOOL_AND(c2) FROM test",
+ "postgres": "SELECT BOOL_AND(c1), BOOL_AND(c2) FROM test",
+ "snowflake": "SELECT BOOLAND_AGG(c1), BOOLAND_AGG(c2) FROM test",
+ "spark": "SELECT BOOL_AND(c1), BOOL_AND(c2) FROM test",
+ "sqlite": "SELECT MIN(c1), MIN(c2) FROM test",
+ },
+ )
+ self.validate_all(
"TO_CHAR(x, y)",
read={
"": "TO_CHAR(x, y)",
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 9328eaa..5b21349 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -214,6 +214,9 @@ TBLPROPERTIES (
self.validate_identity("TRIM(TRAILING 'SL' FROM 'SSparkSQLS')")
self.validate_all(
+ "CAST(x AS TIMESTAMP)", read={"trino": "CAST(x AS TIMESTAMP(6) WITH TIME ZONE)"}
+ )
+ self.validate_all(
"SELECT DATE_ADD(my_date_column, 1)",
write={
"spark": "SELECT DATE_ADD(my_date_column, 1)",
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index f889445..98c4a79 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -57,6 +57,33 @@ class TestSQLite(Validator):
def test_sqlite(self):
self.validate_all(
+ "CURRENT_DATE",
+ read={
+ "": "CURRENT_DATE",
+ "snowflake": "CURRENT_DATE()",
+ },
+ )
+ self.validate_all(
+ "CURRENT_TIME",
+ read={
+ "": "CURRENT_TIME",
+ "snowflake": "CURRENT_TIME()",
+ },
+ )
+ self.validate_all(
+ "CURRENT_TIMESTAMP",
+ read={
+ "": "CURRENT_TIMESTAMP",
+ "snowflake": "CURRENT_TIMESTAMP()",
+ },
+ )
+ self.validate_all(
+ "SELECT DATE('2020-01-01 16:03:05')",
+ read={
+ "snowflake": "SELECT CAST('2020-01-01 16:03:05' AS DATE)",
+ },
+ )
+ self.validate_all(
"SELECT CAST([a].[b] AS SMALLINT) FROM foo",
write={
"sqlite": 'SELECT CAST("a"."b" AS INTEGER) FROM foo',
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-26 22:33:01 +0000