Adding upstream version 9.0.1.upstream/9.0.1

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

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