diff options
Diffstat (limited to 'tests/dialects/test_dialect.py')
| -rw-r--r-- | tests/dialects/test_dialect.py | 58 |
1 files changed, 49 insertions, 9 deletions
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index 5a13655..a456415 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -1,6 +1,7 @@ import unittest from sqlglot import Dialect, Dialects, ErrorLevel, UnsupportedError, parse_one +from sqlglot.dialects import Hive class Validator(unittest.TestCase): @@ -67,6 +68,11 @@ class TestDialect(Validator): self.assertIsNotNone(Dialect.get_or_raise(dialect)) self.assertIsNotNone(Dialect[dialect.value]) + def test_get_or_raise(self): + self.assertEqual(Dialect.get_or_raise(Hive), Hive) + self.assertEqual(Dialect.get_or_raise(Hive()), Hive) + self.assertEqual(Dialect.get_or_raise("hive"), Hive) + def test_cast(self): self.validate_all( "CAST(a AS TEXT)", @@ -280,6 +286,21 @@ class TestDialect(Validator): write={"oracle": "CAST(a AS NUMBER)"}, ) + def test_if_null(self): + self.validate_all( + "SELECT IFNULL(1, NULL) FROM foo", + write={ + "": "SELECT COALESCE(1, NULL) FROM foo", + "redshift": "SELECT COALESCE(1, NULL) FROM foo", + "postgres": "SELECT COALESCE(1, NULL) FROM foo", + "mysql": "SELECT COALESCE(1, NULL) FROM foo", + "duckdb": "SELECT COALESCE(1, NULL) FROM foo", + "spark": "SELECT COALESCE(1, NULL) FROM foo", + "bigquery": "SELECT COALESCE(1, NULL) FROM foo", + "presto": "SELECT COALESCE(1, NULL) FROM foo", + }, + ) + def test_time(self): self.validate_all( "STR_TO_TIME(x, '%Y-%m-%dT%H:%M:%S')", @@ -287,10 +308,10 @@ class TestDialect(Validator): "duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S')", }, write={ - "mysql": "STR_TO_DATE(x, '%Y-%m-%dT%H:%i:%S')", + "mysql": "STR_TO_DATE(x, '%Y-%m-%dT%T')", "duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S')", "hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yyyy-MM-ddTHH:mm:ss')) AS TIMESTAMP)", - "presto": "DATE_PARSE(x, '%Y-%m-%dT%H:%i:%S')", + "presto": "DATE_PARSE(x, '%Y-%m-%dT%T')", "drill": "TO_TIMESTAMP(x, 'yyyy-MM-dd''T''HH:mm:ss')", "redshift": "TO_TIMESTAMP(x, 'YYYY-MM-DDTHH:MI:SS')", "spark": "TO_TIMESTAMP(x, 'yyyy-MM-ddTHH:mm:ss')", @@ -356,7 +377,7 @@ class TestDialect(Validator): write={ "duckdb": "EPOCH(CAST('2020-01-01' AS TIMESTAMP))", "hive": "UNIX_TIMESTAMP('2020-01-01')", - "presto": "TO_UNIXTIME(DATE_PARSE('2020-01-01', '%Y-%m-%d %H:%i:%S'))", + "presto": "TO_UNIXTIME(DATE_PARSE('2020-01-01', '%Y-%m-%d %T'))", }, ) self.validate_all( @@ -418,7 +439,7 @@ class TestDialect(Validator): self.validate_all( "UNIX_TO_STR(x, y)", write={ - "duckdb": "STRFTIME(TO_TIMESTAMP(CAST(x AS BIGINT)), y)", + "duckdb": "STRFTIME(TO_TIMESTAMP(x), y)", "hive": "FROM_UNIXTIME(x, y)", "presto": "DATE_FORMAT(FROM_UNIXTIME(x), y)", "starrocks": "FROM_UNIXTIME(x, y)", @@ -427,7 +448,7 @@ class TestDialect(Validator): self.validate_all( "UNIX_TO_TIME(x)", write={ - "duckdb": "TO_TIMESTAMP(CAST(x AS BIGINT))", + "duckdb": "TO_TIMESTAMP(x)", "hive": "FROM_UNIXTIME(x)", "oracle": "TO_DATE('1970-01-01','YYYY-MM-DD') + (x / 86400)", "postgres": "TO_TIMESTAMP(x)", @@ -438,7 +459,7 @@ class TestDialect(Validator): self.validate_all( "UNIX_TO_TIME_STR(x)", write={ - "duckdb": "CAST(TO_TIMESTAMP(CAST(x AS BIGINT)) AS TEXT)", + "duckdb": "CAST(TO_TIMESTAMP(x) AS TEXT)", "hive": "FROM_UNIXTIME(x)", "presto": "CAST(FROM_UNIXTIME(x) AS VARCHAR)", }, @@ -575,10 +596,10 @@ class TestDialect(Validator): }, write={ "drill": "TO_DATE(x, 'yyyy-MM-dd''T''HH:mm:ss')", - "mysql": "STR_TO_DATE(x, '%Y-%m-%dT%H:%i:%S')", - "starrocks": "STR_TO_DATE(x, '%Y-%m-%dT%H:%i:%S')", + "mysql": "STR_TO_DATE(x, '%Y-%m-%dT%T')", + "starrocks": "STR_TO_DATE(x, '%Y-%m-%dT%T')", "hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yyyy-MM-ddTHH:mm:ss')) AS DATE)", - "presto": "CAST(DATE_PARSE(x, '%Y-%m-%dT%H:%i:%S') AS DATE)", + "presto": "CAST(DATE_PARSE(x, '%Y-%m-%dT%T') AS DATE)", "spark": "TO_DATE(x, 'yyyy-MM-ddTHH:mm:ss')", }, ) @@ -709,6 +730,7 @@ class TestDialect(Validator): "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)", }, ) @@ -1381,3 +1403,21 @@ SELECT "spark": "MERGE INTO a AS b USING c AS d ON b.id = d.id WHEN MATCHED AND EXISTS(SELECT b.name EXCEPT SELECT d.name) THEN UPDATE SET b.name = d.name", }, ) + + def test_substring(self): + self.validate_all( + "SUBSTR('123456', 2, 3)", + write={ + "bigquery": "SUBSTR('123456', 2, 3)", + "oracle": "SUBSTR('123456', 2, 3)", + "postgres": "SUBSTR('123456', 2, 3)", + }, + ) + self.validate_all( + "SUBSTRING('123456', 2, 3)", + write={ + "bigquery": "SUBSTRING('123456', 2, 3)", + "oracle": "SUBSTR('123456', 2, 3)", + "postgres": "SUBSTRING('123456' FROM 2 FOR 3)", + }, + ) |