summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_dialect.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-02-08 04:14:30 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-02-08 04:14:30 +0000
commit99980f928b5b7be237d108266072e51aa3bb354e (patch)
treece6fff00ea2b834bdbe3d84dcac90df1617d4245 /tests/dialects/test_dialect.py
parentAdding upstream version 10.6.0. (diff)
downloadsqlglot-99980f928b5b7be237d108266072e51aa3bb354e.tar.xz
sqlglot-99980f928b5b7be237d108266072e51aa3bb354e.zip
Adding upstream version 10.6.3.upstream/10.6.3
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_dialect.py')
-rw-r--r--tests/dialects/test_dialect.py58
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)",
+ },
+ )