summaryrefslogtreecommitdiffstats
path: root/tests/dialects
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects')
-rw-r--r--tests/dialects/test_dialect.py25
-rw-r--r--tests/dialects/test_duckdb.py23
-rw-r--r--tests/dialects/test_mysql.py18
-rw-r--r--tests/dialects/test_postgres.py10
-rw-r--r--tests/dialects/test_presto.py4
-rw-r--r--tests/dialects/test_snowflake.py23
-rw-r--r--tests/dialects/test_tsql.py26
7 files changed, 98 insertions, 31 deletions
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index a1e1262..e1524e9 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -694,29 +694,6 @@ class TestDialect(Validator):
},
)
- # https://dev.mysql.com/doc/refman/8.0/en/join.html
- # https://www.postgresql.org/docs/current/queries-table-expressions.html
- def test_joined_tables(self):
- self.validate_identity("SELECT * FROM (tbl1 LEFT JOIN tbl2 ON 1 = 1)")
- self.validate_identity("SELECT * FROM (tbl1 JOIN tbl2 JOIN tbl3)")
- self.validate_identity("SELECT * FROM (tbl1 JOIN (tbl2 JOIN tbl3) ON bla = foo)")
- self.validate_identity("SELECT * FROM (tbl1 JOIN LATERAL (SELECT * FROM bla) AS tbl)")
-
- self.validate_all(
- "SELECT * FROM (tbl1 LEFT JOIN tbl2 ON 1 = 1)",
- write={
- "postgres": "SELECT * FROM (tbl1 LEFT JOIN tbl2 ON 1 = 1)",
- "mysql": "SELECT * FROM (tbl1 LEFT JOIN tbl2 ON 1 = 1)",
- },
- )
- self.validate_all(
- "SELECT * FROM (tbl1 JOIN LATERAL (SELECT * FROM bla) AS tbl)",
- write={
- "postgres": "SELECT * FROM (tbl1 JOIN LATERAL (SELECT * FROM bla) AS tbl)",
- "mysql": "SELECT * FROM (tbl1 JOIN LATERAL (SELECT * FROM bla) AS tbl)",
- },
- )
-
def test_lateral_subquery(self):
self.validate_identity(
"SELECT art FROM tbl1 INNER JOIN LATERAL (SELECT art FROM tbl2) AS tbl2 ON tbl1.art = tbl2.art"
@@ -856,7 +833,7 @@ class TestDialect(Validator):
"postgres": "x ILIKE '%y'",
"presto": "LOWER(x) LIKE '%y'",
"snowflake": "x ILIKE '%y'",
- "spark": "LOWER(x) LIKE '%y'",
+ "spark": "x ILIKE '%y'",
"sqlite": "LOWER(x) LIKE '%y'",
"starrocks": "LOWER(x) LIKE '%y'",
"trino": "LOWER(x) LIKE '%y'",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 298b3e9..625156b 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -48,7 +48,7 @@ class TestDuckDB(Validator):
self.validate_all(
"STRPTIME(x, '%y-%-m')",
write={
- "bigquery": "STR_TO_TIME(x, '%y-%-m')",
+ "bigquery": "PARSE_TIMESTAMP('%y-%m', x)",
"duckdb": "STRPTIME(x, '%y-%-m')",
"presto": "DATE_PARSE(x, '%y-%c')",
"hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yy-M')) AS TIMESTAMP)",
@@ -63,6 +63,16 @@ class TestDuckDB(Validator):
"hive": "CAST(x AS TIMESTAMP)",
},
)
+ self.validate_all(
+ "STRPTIME(x, '%-m/%-d/%y %-I:%M %p')",
+ write={
+ "bigquery": "PARSE_TIMESTAMP('%m/%d/%y %I:%M %p', x)",
+ "duckdb": "STRPTIME(x, '%-m/%-d/%y %-I:%M %p')",
+ "presto": "DATE_PARSE(x, '%c/%e/%y %l:%i %p')",
+ "hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'M/d/yy h:mm a')) AS TIMESTAMP)",
+ "spark": "TO_TIMESTAMP(x, 'M/d/yy h:mm a')",
+ },
+ )
def test_duckdb(self):
self.validate_all(
@@ -268,6 +278,17 @@ class TestDuckDB(Validator):
"spark": "MONTH('2021-03-01')",
},
)
+ self.validate_all(
+ "ARRAY_CONCAT(LIST_VALUE(1, 2), LIST_VALUE(3, 4))",
+ write={
+ "duckdb": "ARRAY_CONCAT(LIST_VALUE(1, 2), LIST_VALUE(3, 4))",
+ "presto": "CONCAT(ARRAY[1, 2], ARRAY[3, 4])",
+ "hive": "CONCAT(ARRAY(1, 2), ARRAY(3, 4))",
+ "spark": "CONCAT(ARRAY(1, 2), ARRAY(3, 4))",
+ "snowflake": "ARRAY_CAT([1, 2], [3, 4])",
+ "bigquery": "ARRAY_CONCAT([1, 2], [3, 4])",
+ },
+ )
with self.assertRaises(UnsupportedError):
transpile(
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 723e27c..a25871c 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -31,6 +31,24 @@ class TestMySQL(Validator):
"mysql": "_utf8mb4 'hola'",
},
)
+ self.validate_all(
+ "N 'some text'",
+ read={
+ "mysql": "N'some text'",
+ },
+ write={
+ "mysql": "N 'some text'",
+ },
+ )
+ self.validate_all(
+ "_latin1 x'4D7953514C'",
+ read={
+ "mysql": "_latin1 X'4D7953514C'",
+ },
+ write={
+ "mysql": "_latin1 x'4D7953514C'",
+ },
+ )
def test_hexadecimal_literal(self):
self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 4b8f3c3..35141e2 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -69,6 +69,8 @@ class TestPostgres(Validator):
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_identity("COMMENT ON TABLE mytable IS 'this'")
+ self.validate_identity("SELECT e'\\xDEADBEEF'")
+ self.validate_identity("SELECT CAST(e'\\176' AS BYTEA)")
self.validate_all(
"CREATE TABLE x (a UUID, b BYTEA)",
@@ -204,3 +206,11 @@ class TestPostgres(Validator):
"""'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'""",
write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON)#>>'{a,2}'"""},
)
+ self.validate_all(
+ "SELECT $$a$$",
+ write={"postgres": "SELECT 'a'"},
+ )
+ self.validate_all(
+ "SELECT $$Dianne's horse$$",
+ write={"postgres": "SELECT 'Dianne''s horse'"},
+ )
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 10c9d35..098ad2b 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -321,7 +321,7 @@ class TestPresto(Validator):
"duckdb": "SELECT APPROX_COUNT_DISTINCT(a) FROM foo",
"presto": "SELECT APPROX_DISTINCT(a, 0.1) FROM foo",
"hive": "SELECT APPROX_COUNT_DISTINCT(a) FROM foo",
- "spark": "SELECT APPROX_COUNT_DISTINCT(a) FROM foo",
+ "spark": "SELECT APPROX_COUNT_DISTINCT(a, 0.1) FROM foo",
},
)
self.validate_all(
@@ -329,7 +329,7 @@ class TestPresto(Validator):
write={
"presto": "SELECT APPROX_DISTINCT(a, 0.1) FROM foo",
"hive": UnsupportedError,
- "spark": UnsupportedError,
+ "spark": "SELECT APPROX_COUNT_DISTINCT(a, 0.1) FROM foo",
},
)
self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 8a33e2d..159b643 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -65,7 +65,7 @@ class TestSnowflake(Validator):
self.validate_all(
"SELECT TO_TIMESTAMP('2013-04-05 01:02:03')",
write={
- "bigquery": "SELECT STR_TO_TIME('2013-04-05 01:02:03', '%Y-%m-%d %H:%M:%S')",
+ "bigquery": "SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2013-04-05 01:02:03')",
"snowflake": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-mm-dd hh24:mi:ss')",
"spark": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-MM-dd HH:mm:ss')",
},
@@ -73,16 +73,17 @@ class TestSnowflake(Validator):
self.validate_all(
"SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss')",
read={
- "bigquery": "SELECT STR_TO_TIME('04/05/2013 01:02:03', '%m/%d/%Y %H:%M:%S')",
+ "bigquery": "SELECT PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', '04/05/2013 01:02:03')",
"duckdb": "SELECT STRPTIME('04/05/2013 01:02:03', '%m/%d/%Y %H:%M:%S')",
"snowflake": "SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss')",
},
write={
- "bigquery": "SELECT STR_TO_TIME('04/05/2013 01:02:03', '%m/%d/%Y %H:%M:%S')",
+ "bigquery": "SELECT PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', '04/05/2013 01:02:03')",
"snowflake": "SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss')",
"spark": "SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'MM/dd/yyyy HH:mm:ss')",
},
)
+
self.validate_all(
"SELECT IFF(TRUE, 'true', 'false')",
write={
@@ -240,11 +241,25 @@ class TestSnowflake(Validator):
},
)
self.validate_all(
- "SELECT DATE_PART(month FROM a::DATETIME)",
+ "SELECT DATE_PART(month, a::DATETIME)",
write={
"snowflake": "SELECT EXTRACT(month FROM CAST(a AS DATETIME))",
},
)
+ self.validate_all(
+ "SELECT DATE_PART(epoch_second, foo) as ddate from table_name",
+ write={
+ "snowflake": "SELECT EXTRACT(epoch_second FROM CAST(foo AS TIMESTAMPNTZ)) AS ddate FROM table_name",
+ "presto": "SELECT TO_UNIXTIME(CAST(foo AS TIMESTAMP)) AS ddate FROM table_name",
+ },
+ )
+ self.validate_all(
+ "SELECT DATE_PART(epoch_milliseconds, foo) as ddate from table_name",
+ write={
+ "snowflake": "SELECT EXTRACT(epoch_second FROM CAST(foo AS TIMESTAMPNTZ)) * 1000 AS ddate FROM table_name",
+ "presto": "SELECT TO_UNIXTIME(CAST(foo AS TIMESTAMP)) * 1000 AS ddate FROM table_name",
+ },
+ )
def test_semi_structured_types(self):
self.validate_identity("SELECT CAST(a AS VARIANT)")
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index b061784..9a6bc36 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -45,3 +45,29 @@ class TestTSQL(Validator):
"tsql": "CAST(x AS DATETIME2)",
},
)
+
+ def test_charindex(self):
+ self.validate_all(
+ "CHARINDEX(x, y, 9)",
+ write={
+ "spark": "LOCATE(x, y, 9)",
+ },
+ )
+ self.validate_all(
+ "CHARINDEX(x, y)",
+ write={
+ "spark": "LOCATE(x, y)",
+ },
+ )
+ self.validate_all(
+ "CHARINDEX('sub', 'testsubstring', 3)",
+ write={
+ "spark": "LOCATE('sub', 'testsubstring', 3)",
+ },
+ )
+ self.validate_all(
+ "CHARINDEX('sub', 'testsubstring')",
+ write={
+ "spark": "LOCATE('sub', 'testsubstring')",
+ },
+ )
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-08 21:47:22 +0000