Adding upstream version 10.4.2.upstream/10.4.2

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

11 files changed, 224 insertions, 32 deletions

diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 1d60ec6..258e47f 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -318,3 +318,9 @@ class TestBigQuery(Validator):
         self.validate_identity(
             "CREATE TABLE FUNCTION a(x INT64) RETURNS TABLE <q STRING, r INT64> AS SELECT s, t"
         )
+
+    def test_group_concat(self):
+        self.validate_all(
+            "SELECT a, GROUP_CONCAT(b) FROM table GROUP BY a",
+            write={"bigquery": "SELECT a, STRING_AGG(b) FROM table GROUP BY a"},
+        )
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index 2168f55..7560d61 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -12,6 +12,76 @@ class TestDatabricks(Validator):
                 "databricks": "SELECT DATEDIFF(year, 'start', 'end')",
             },
         )
+        self.validate_all(
+            "SELECT DATEDIFF(microsecond, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(microsecond, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) * 1000000 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(millisecond, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(millisecond, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) * 1000 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(second, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(second, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(minute, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(minute, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) / 60 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(hour, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(hour, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) / 3600 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(day, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(day, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) / 86400 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(week, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(week, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 48 + EXTRACT(month FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 4 + EXTRACT(day FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) / 7 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(month, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(month, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 12 + EXTRACT(month FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(quarter, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(quarter, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 4 + EXTRACT(month FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) / 3 AS BIGINT)",
+            },
+        )
+        self.validate_all(
+            "SELECT DATEDIFF(year, 'start', 'end')",
+            write={
+                "databricks": "SELECT DATEDIFF(year, 'start', 'end')",
+                "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) AS BIGINT)",
+            },
+        )
 
     def test_add_date(self):
         self.validate_all(
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index ee67bf1..ced7102 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -333,7 +333,7 @@ class TestDialect(Validator):
                 "drill": "CAST('2020-01-01' AS DATE)",
                 "duckdb": "CAST('2020-01-01' AS DATE)",
                 "hive": "TO_DATE('2020-01-01')",
-                "presto": "DATE_PARSE('2020-01-01', '%Y-%m-%d %H:%i:%s')",
+                "presto": "CAST('2020-01-01' AS TIMESTAMP)",
                 "starrocks": "TO_DATE('2020-01-01')",
             },
         )
@@ -343,7 +343,7 @@ class TestDialect(Validator):
                 "drill": "CAST('2020-01-01' AS TIMESTAMP)",
                 "duckdb": "CAST('2020-01-01' AS TIMESTAMP)",
                 "hive": "CAST('2020-01-01' AS TIMESTAMP)",
-                "presto": "DATE_PARSE('2020-01-01', '%Y-%m-%d %H:%i:%s')",
+                "presto": "CAST('2020-01-01' AS TIMESTAMP)",
             },
         )
         self.validate_all(
@@ -723,23 +723,23 @@ class TestDialect(Validator):
             read={
                 "postgres": "x->'y'",
                 "presto": "JSON_EXTRACT(x, 'y')",
-                "starrocks": "x->'y'",
+                "starrocks": "x -> 'y'",
             },
             write={
                 "oracle": "JSON_EXTRACT(x, 'y')",
-                "postgres": "x->'y'",
+                "postgres": "x -> 'y'",
                 "presto": "JSON_EXTRACT(x, 'y')",
-                "starrocks": "x->'y'",
+                "starrocks": "x -> 'y'",
             },
         )
         self.validate_all(
             "JSON_EXTRACT_SCALAR(x, 'y')",
             read={
-                "postgres": "x->>'y'",
+                "postgres": "x ->> 'y'",
                 "presto": "JSON_EXTRACT_SCALAR(x, 'y')",
             },
             write={
-                "postgres": "x->>'y'",
+                "postgres": "x ->> 'y'",
                 "presto": "JSON_EXTRACT_SCALAR(x, 'y')",
             },
         )
@@ -749,7 +749,7 @@ class TestDialect(Validator):
                 "postgres": "x#>'y'",
             },
             write={
-                "postgres": "x#>'y'",
+                "postgres": "x #> 'y'",
             },
         )
         self.validate_all(
@@ -758,7 +758,7 @@ class TestDialect(Validator):
                 "postgres": "x#>>'y'",
             },
             write={
-                "postgres": "x#>>'y'",
+                "postgres": "x #>> 'y'",
             },
         )
 
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 99b0493..a37062c 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -59,7 +59,7 @@ class TestDuckDB(Validator):
             "TO_TIMESTAMP(x)",
             write={
                 "duckdb": "CAST(x AS TIMESTAMP)",
-                "presto": "DATE_PARSE(x, '%Y-%m-%d %H:%i:%s')",
+                "presto": "CAST(x AS TIMESTAMP)",
                 "hive": "CAST(x AS TIMESTAMP)",
             },
         )
@@ -302,3 +302,20 @@ class TestDuckDB(Validator):
                 read="duckdb",
                 unsupported_level=ErrorLevel.IMMEDIATE,
             )
+
+    def test_array(self):
+        self.validate_identity("ARRAY(SELECT id FROM t)")
+
+    def test_cast(self):
+        self.validate_all(
+            "123::CHARACTER VARYING",
+            write={
+                "duckdb": "CAST(123 AS TEXT)",
+            },
+        )
+
+    def test_bool_or(self):
+        self.validate_all(
+            "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
+            write={"duckdb": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
+        )
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 5ac8714..a7f3b8f 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -268,10 +268,10 @@ class TestHive(Validator):
         self.validate_all(
             "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
             write={
-                "duckdb": "STRFTIME('2020-01-01', '%Y-%m-%d %H:%M:%S')",
-                "presto": "DATE_FORMAT('2020-01-01', '%Y-%m-%d %H:%i:%S')",
-                "hive": "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
-                "spark": "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
+                "duckdb": "STRFTIME(CAST('2020-01-01' AS TIMESTAMP), '%Y-%m-%d %H:%M:%S')",
+                "presto": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), '%Y-%m-%d %H:%i:%S')",
+                "hive": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), 'yyyy-MM-dd HH:mm:ss')",
+                "spark": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), 'yyyy-MM-dd HH:mm:ss')",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 5064dbe..7cd686d 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -91,12 +91,12 @@ class TestMySQL(Validator):
             },
         )
         self.validate_all(
-            "N 'some text'",
+            "N'some text'",
             read={
-                "mysql": "N'some text'",
+                "mysql": "n'some text'",
             },
             write={
-                "mysql": "N 'some text'",
+                "mysql": "N'some text'",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 962b28b..1e048d5 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -3,6 +3,7 @@ from tests.dialects.test_dialect import Validator
 
 
 class TestPostgres(Validator):
+    maxDiff = None
     dialect = "postgres"
 
     def test_ddl(self):
@@ -94,6 +95,7 @@ class TestPostgres(Validator):
         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_identity("""SELECT * FROM JSON_TO_RECORDSET(z) AS y("rank" INT)""")
 
         self.validate_all(
             "END WORK AND NO CHAIN",
@@ -112,6 +114,14 @@ class TestPostgres(Validator):
                 "spark": "CREATE TABLE x (a UUID, b BINARY)",
             },
         )
+        self.validate_all(
+            "123::CHARACTER VARYING",
+            write={"postgres": "CAST(123 AS VARCHAR)"},
+        )
+        self.validate_all(
+            "TO_TIMESTAMP(123::DOUBLE PRECISION)",
+            write={"postgres": "TO_TIMESTAMP(CAST(123 AS DOUBLE PRECISION))"},
+        )
 
         self.validate_identity(
             "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
@@ -193,15 +203,21 @@ class TestPostgres(Validator):
             },
         )
         self.validate_all(
-            "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE WHERE pname IS NULL",
-            read={
+            "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) pname ON TRUE WHERE pname IS NULL",
+            write={
                 "postgres": "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE WHERE pname IS NULL",
             },
         )
         self.validate_all(
             "SELECT p1.id, p2.id, v1, v2 FROM polygons AS p1, polygons AS p2, LATERAL VERTICES(p1.poly) v1, LATERAL VERTICES(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id <> p2.id",
-            read={
-                "postgres": "SELECT p1.id, p2.id, v1, v2 FROM polygons p1, polygons p2, LATERAL VERTICES(p1.poly) v1, LATERAL VERTICES(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id",
+            write={
+                "postgres": "SELECT p1.id, p2.id, v1, v2 FROM polygons AS p1, polygons AS p2, LATERAL VERTICES(p1.poly) AS v1, LATERAL VERTICES(p2.poly) AS v2 WHERE (v1 <-> v2) < 10 AND p1.id <> p2.id",
+            },
+        )
+        self.validate_all(
+            "SELECT * FROM r CROSS JOIN LATERAL unnest(array(1)) AS s(location)",
+            write={
+                "postgres": "SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)",
             },
         )
         self.validate_all(
@@ -218,35 +234,46 @@ class TestPostgres(Validator):
         )
         self.validate_all(
             "'[1,2,3]'::json->2",
-            write={"postgres": "CAST('[1,2,3]' AS JSON)->'2'"},
+            write={"postgres": "CAST('[1,2,3]' AS JSON) -> '2'"},
         )
         self.validate_all(
             """'{"a":1,"b":2}'::json->'b'""",
-            write={"postgres": """CAST('{"a":1,"b":2}' AS JSON)->'b'"""},
+            write={"postgres": """CAST('{"a":1,"b":2}' AS JSON) -> 'b'"""},
         )
         self.validate_all(
             """'{"x": {"y": 1}}'::json->'x'->'y'""",
-            write={"postgres": """CAST('{"x": {"y": 1}}' AS JSON)->'x'->'y'"""},
+            write={"postgres": """CAST('{"x": {"y": 1}}' AS JSON) -> 'x' -> 'y'"""},
         )
         self.validate_all(
             """'{"x": {"y": 1}}'::json->'x'::json->'y'""",
-            write={"postgres": """CAST(CAST('{"x": {"y": 1}}' AS JSON)->'x' AS JSON)->'y'"""},
+            write={"postgres": """CAST(CAST('{"x": {"y": 1}}' AS JSON) -> 'x' AS JSON) -> 'y'"""},
         )
         self.validate_all(
             """'[1,2,3]'::json->>2""",
-            write={"postgres": "CAST('[1,2,3]' AS JSON)->>'2'"},
+            write={"postgres": "CAST('[1,2,3]' AS JSON) ->> '2'"},
         )
         self.validate_all(
             """'{"a":1,"b":2}'::json->>'b'""",
-            write={"postgres": """CAST('{"a":1,"b":2}' AS JSON)->>'b'"""},
+            write={"postgres": """CAST('{"a":1,"b":2}' AS JSON) ->> 'b'"""},
         )
         self.validate_all(
             """'{"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}'"""},
+            write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON) #> '{a,2}'"""},
         )
         self.validate_all(
             """'{"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}'"""},
+            write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON) #>> '{a,2}'"""},
+        )
+        self.validate_all(
+            """SELECT JSON_ARRAY_ELEMENTS((foo->'sections')::JSON) AS sections""",
+            write={
+                "postgres": """SELECT JSON_ARRAY_ELEMENTS(CAST((foo -> 'sections') AS JSON)) AS sections""",
+                "presto": """SELECT JSON_ARRAY_ELEMENTS(CAST((JSON_EXTRACT(foo, 'sections')) AS JSON)) AS sections""",
+            },
+        )
+        self.validate_all(
+            """x ? 'x'""",
+            write={"postgres": "x ? 'x'"},
         )
         self.validate_all(
             "SELECT $$a$$",
@@ -260,3 +287,49 @@ class TestPostgres(Validator):
             "UPDATE MYTABLE T1 SET T1.COL = 13",
             write={"postgres": "UPDATE MYTABLE AS T1 SET T1.COL = 13"},
         )
+
+        self.validate_identity("x ~ 'y'")
+        self.validate_identity("x ~* 'y'")
+        self.validate_all(
+            "x !~ 'y'",
+            write={"postgres": "NOT x ~ 'y'"},
+        )
+        self.validate_all(
+            "x !~* 'y'",
+            write={"postgres": "NOT x ~* 'y'"},
+        )
+
+        self.validate_all(
+            "x ~~ 'y'",
+            write={"postgres": "x LIKE 'y'"},
+        )
+        self.validate_all(
+            "x ~~* 'y'",
+            write={"postgres": "x ILIKE 'y'"},
+        )
+        self.validate_all(
+            "x !~~ 'y'",
+            write={"postgres": "NOT x LIKE 'y'"},
+        )
+        self.validate_all(
+            "x !~~* 'y'",
+            write={"postgres": "NOT x ILIKE 'y'"},
+        )
+        self.validate_all(
+            "'45 days'::interval day",
+            write={"postgres": "CAST('45 days' AS INTERVAL day)"},
+        )
+        self.validate_all(
+            "'x' 'y' 'z'",
+            write={"postgres": "CONCAT('x', 'y', 'z')"},
+        )
+        self.validate_identity("SELECT ARRAY(SELECT 1)")
+
+        self.validate_all(
+            "x::cstring",
+            write={"postgres": "CAST(x AS CSTRING)"},
+        )
+
+        self.validate_identity(
+            "SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
+        )
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 3034df5..f650c98 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -53,7 +53,7 @@ class TestRedshift(Validator):
         self.validate_all(
             "SELECT DISTINCT ON (a) a, b FROM x ORDER BY c DESC",
             write={
-                "redshift": 'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS "_row_number" FROM x) WHERE "_row_number" = 1',
+                "redshift": 'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) WHERE "_row_number" = 1',
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index e3d0cff..df62c6c 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -7,6 +7,12 @@ class TestSnowflake(Validator):
 
     def test_snowflake(self):
         self.validate_all(
+            "SELECT * FROM xxx WHERE col ilike '%Don''t%'",
+            write={
+                "snowflake": "SELECT * FROM xxx WHERE col ILIKE '%Don\\'t%'",
+            },
+        )
+        self.validate_all(
             'x:a:"b c"',
             write={
                 "duckdb": "x['a']['b c']",
@@ -509,3 +515,11 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS f, LATERA
                 "snowflake": "SELECT 1 MINUS SELECT 1",
             },
         )
+
+    def test_values(self):
+        self.validate_all(
+            'SELECT c0, c1 FROM (VALUES (1, 2), (3, 4)) AS "t0"(c0, c1)',
+            read={
+                "spark": "SELECT `c0`, `c1` FROM (VALUES (1, 2), (3, 4)) AS `t0`(`c0`, `c1`)",
+            },
+        )
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 3a9f918..7395e72 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -101,6 +101,18 @@ TBLPROPERTIES (
                 "spark": "CACHE TABLE testCache OPTIONS('storageLevel' = 'DISK_ONLY') AS SELECT * FROM testData"
             },
         )
+        self.validate_all(
+            "ALTER TABLE StudentInfo ADD COLUMNS (LastName STRING, DOB TIMESTAMP)",
+            write={
+                "spark": "ALTER TABLE StudentInfo ADD COLUMNS (LastName STRING, DOB TIMESTAMP)",
+            },
+        )
+        self.validate_all(
+            "ALTER TABLE StudentInfo DROP COLUMNS (LastName, DOB)",
+            write={
+                "spark": "ALTER TABLE StudentInfo DROP COLUMNS (LastName, DOB)",
+            },
+        )
 
     def test_to_date(self):
         self.validate_all(
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index e4c6e60..b4ac094 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -431,11 +431,11 @@ class TestTSQL(Validator):
     def test_string(self):
         self.validate_all(
             "SELECT N'test'",
-            write={"spark": "SELECT 'test'"},
+            write={"spark": "SELECT N'test'"},
         )
         self.validate_all(
             "SELECT n'test'",
-            write={"spark": "SELECT 'test'"},
+            write={"spark": "SELECT N'test'"},
         )
         self.validate_all(
             "SELECT '''test'''",