Adding upstream version 10.6.3.upstream/10.6.3

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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)",
+            },
+        )