Merging upstream version 6.3.1.

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

7 files changed, 175 insertions, 7 deletions

diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 7110eac..8921924 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -153,6 +153,10 @@ class TestBigQuery(Validator):
         )
 
         self.validate_identity(
+            "SELECT item, purchases, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular FROM Produce WINDOW item_window AS (ORDER BY purchases)"
+        )
+
+        self.validate_identity(
             "SELECT LAST_VALUE(a IGNORE NULLS) OVER y FROM x WINDOW y AS (PARTITION BY CATEGORY)",
         )
 
@@ -223,6 +227,20 @@ class TestBigQuery(Validator):
             },
         )
         self.validate_all(
+            "DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY)",
+            write={
+                "bigquery": "DATE_DIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE), DAY)",
+                "mysql": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))",
+            },
+        )
+        self.validate_all(
+            "DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', MINUTE)",
+            write={
+                "bigquery": "DATE_DIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE), MINUTE)",
+                "mysql": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))",
+            },
+        )
+        self.validate_all(
             "CURRENT_DATE('UTC')",
             write={
                 "mysql": "CURRENT_DATE AT TIME ZONE 'UTC'",
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index e5b1516..715bf10 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -8,6 +8,8 @@ class TestClickhouse(Validator):
         self.validate_identity("dictGet(x, 'y')")
         self.validate_identity("SELECT * FROM x FINAL")
         self.validate_identity("SELECT * FROM x AS y FINAL")
+        self.validate_identity("'a' IN mapKeys(map('a', 1, 'b', 2))")
+        self.validate_identity("CAST((1, 2) AS Tuple(a Int8, b Int16))")
 
         self.validate_all(
             "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname",
@@ -20,6 +22,12 @@ class TestClickhouse(Validator):
         self.validate_all(
             "CAST(1 AS NULLABLE(Int64))",
             write={
-                "clickhouse": "CAST(1 AS Nullable(BIGINT))",
+                "clickhouse": "CAST(1 AS Nullable(Int64))",
+            },
+        )
+        self.validate_all(
+            "CAST(1 AS Nullable(DateTime64(6, 'UTC')))",
+            write={
+                "clickhouse": "CAST(1 AS Nullable(DateTime64(6, 'UTC')))",
             },
         )
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index a9a313c..53edb42 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -82,6 +82,24 @@ class TestDialect(Validator):
             },
         )
         self.validate_all(
+            "CAST(MAP('a', '1') AS MAP(TEXT, TEXT))",
+            write={
+                "clickhouse": "CAST(map('a', '1') AS Map(TEXT, TEXT))",
+            },
+        )
+        self.validate_all(
+            "CAST(ARRAY(1, 2) AS ARRAY<TINYINT>)",
+            write={
+                "clickhouse": "CAST([1, 2] AS Array(Int8))",
+            },
+        )
+        self.validate_all(
+            "CAST((1, 2) AS STRUCT<a: TINYINT, b: SMALLINT, c: INT, d: BIGINT>)",
+            write={
+                "clickhouse": "CAST((1, 2) AS Tuple(a Int8, b Int16, c Int32, d Int64))",
+            },
+        )
+        self.validate_all(
             "CAST(a AS DATETIME)",
             write={
                 "postgres": "CAST(a AS TIMESTAMP)",
@@ -170,7 +188,7 @@ class TestDialect(Validator):
             "CAST(a AS DOUBLE)",
             write={
                 "bigquery": "CAST(a AS FLOAT64)",
-                "clickhouse": "CAST(a AS DOUBLE)",
+                "clickhouse": "CAST(a AS Float64)",
                 "duckdb": "CAST(a AS DOUBLE)",
                 "mysql": "CAST(a AS DOUBLE)",
                 "hive": "CAST(a AS DOUBLE)",
@@ -234,6 +252,8 @@ class TestDialect(Validator):
             write={
                 "duckdb": "STRPTIME('2020-01-01', '%Y-%m-%d')",
                 "hive": "CAST('2020-01-01' AS TIMESTAMP)",
+                "oracle": "TO_TIMESTAMP('2020-01-01', 'YYYY-MM-DD')",
+                "postgres": "TO_TIMESTAMP('2020-01-01', 'YYYY-MM-DD')",
                 "presto": "DATE_PARSE('2020-01-01', '%Y-%m-%d')",
                 "redshift": "TO_TIMESTAMP('2020-01-01', 'YYYY-MM-DD')",
                 "spark": "TO_TIMESTAMP('2020-01-01', 'yyyy-MM-dd')",
@@ -245,6 +265,8 @@ class TestDialect(Validator):
                 "duckdb": "STRPTIME(x, '%y')",
                 "hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yy')) AS TIMESTAMP)",
                 "presto": "DATE_PARSE(x, '%y')",
+                "oracle": "TO_TIMESTAMP(x, 'YY')",
+                "postgres": "TO_TIMESTAMP(x, 'YY')",
                 "redshift": "TO_TIMESTAMP(x, 'YY')",
                 "spark": "TO_TIMESTAMP(x, 'yy')",
             },
@@ -288,6 +310,8 @@ class TestDialect(Validator):
             write={
                 "duckdb": "STRFTIME(x, '%Y-%m-%d')",
                 "hive": "DATE_FORMAT(x, 'yyyy-MM-dd')",
+                "oracle": "TO_CHAR(x, 'YYYY-MM-DD')",
+                "postgres": "TO_CHAR(x, 'YYYY-MM-DD')",
                 "presto": "DATE_FORMAT(x, '%Y-%m-%d')",
                 "redshift": "TO_CHAR(x, 'YYYY-MM-DD')",
             },
@@ -348,6 +372,8 @@ class TestDialect(Validator):
             write={
                 "duckdb": "TO_TIMESTAMP(CAST(x AS BIGINT))",
                 "hive": "FROM_UNIXTIME(x)",
+                "oracle": "TO_DATE('1970-01-01','YYYY-MM-DD') + (x / 86400)",
+                "postgres": "TO_TIMESTAMP(x)",
                 "presto": "FROM_UNIXTIME(x)",
                 "starrocks": "FROM_UNIXTIME(x)",
             },
@@ -704,6 +730,7 @@ class TestDialect(Validator):
             "SELECT * FROM a UNION SELECT * FROM b",
             read={
                 "bigquery": "SELECT * FROM a UNION DISTINCT SELECT * FROM b",
+                "clickhouse": "SELECT * FROM a UNION DISTINCT SELECT * FROM b",
                 "duckdb": "SELECT * FROM a UNION SELECT * FROM b",
                 "presto": "SELECT * FROM a UNION SELECT * FROM b",
                 "spark": "SELECT * FROM a UNION SELECT * FROM b",
@@ -719,6 +746,7 @@ class TestDialect(Validator):
             "SELECT * FROM a UNION ALL SELECT * FROM b",
             read={
                 "bigquery": "SELECT * FROM a UNION ALL SELECT * FROM b",
+                "clickhouse": "SELECT * FROM a UNION ALL SELECT * FROM b",
                 "duckdb": "SELECT * FROM a UNION ALL SELECT * FROM b",
                 "presto": "SELECT * FROM a UNION ALL SELECT * FROM b",
                 "spark": "SELECT * FROM a UNION ALL SELECT * FROM b",
@@ -848,15 +876,28 @@ class TestDialect(Validator):
                 "postgres": "STRPOS(x, ' ')",
                 "presto": "STRPOS(x, ' ')",
                 "spark": "LOCATE(' ', x)",
+                "clickhouse": "position(x, ' ')",
+                "snowflake": "POSITION(' ', x)",
             },
         )
         self.validate_all(
-            "STR_POSITION(x, 'a')",
+            "STR_POSITION('a', x)",
             write={
                 "duckdb": "STRPOS(x, 'a')",
                 "postgres": "STRPOS(x, 'a')",
                 "presto": "STRPOS(x, 'a')",
                 "spark": "LOCATE('a', x)",
+                "clickhouse": "position(x, 'a')",
+                "snowflake": "POSITION('a', x)",
+            },
+        )
+        self.validate_all(
+            "POSITION('a', x, 3)",
+            write={
+                "presto": "STRPOS(SUBSTR(x, 3), 'a') + 3 - 1",
+                "spark": "LOCATE('a', x, 3)",
+                "clickhouse": "position(x, 'a', 3)",
+                "snowflake": "POSITION('a', x, 3)",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index d335921..acb3be9 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -247,7 +247,7 @@ class TestHive(Validator):
                 "presto": "DATE_DIFF('day', CAST(SUBSTR(CAST(b AS VARCHAR), 1, 10) AS DATE), CAST(SUBSTR(CAST(a AS VARCHAR), 1, 10) AS DATE))",
                 "hive": "DATEDIFF(TO_DATE(a), TO_DATE(b))",
                 "spark": "DATEDIFF(TO_DATE(a), TO_DATE(b))",
-                "": "DATE_DIFF(TS_OR_DS_TO_DATE(a), TS_OR_DS_TO_DATE(b))",
+                "": "DATEDIFF(TS_OR_DS_TO_DATE(a), TS_OR_DS_TO_DATE(b))",
             },
         )
         self.validate_all(
@@ -295,7 +295,7 @@ class TestHive(Validator):
                 "presto": "DATE_DIFF('day', CAST(SUBSTR(CAST(x AS VARCHAR), 1, 10) AS DATE), CAST(SUBSTR(CAST(CAST(SUBSTR(CAST(y AS VARCHAR), 1, 10) AS DATE) AS VARCHAR), 1, 10) AS DATE))",
                 "hive": "DATEDIFF(TO_DATE(TO_DATE(y)), TO_DATE(x))",
                 "spark": "DATEDIFF(TO_DATE(TO_DATE(y)), TO_DATE(x))",
-                "": "DATE_DIFF(TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE(y)), TS_OR_DS_TO_DATE(x))",
+                "": "DATEDIFF(TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE(y)), TS_OR_DS_TO_DATE(x))",
             },
         )
         self.validate_all(
@@ -450,11 +450,21 @@ class TestHive(Validator):
         )
         self.validate_all(
             "MAP(a, b, c, d)",
+            read={
+                "": "VAR_MAP(a, b, c, d)",
+                "clickhouse": "map(a, b, c, d)",
+                "duckdb": "MAP(LIST_VALUE(a, c), LIST_VALUE(b, d))",
+                "hive": "MAP(a, b, c, d)",
+                "presto": "MAP(ARRAY[a, c], ARRAY[b, d])",
+                "spark": "MAP(a, b, c, d)",
+            },
             write={
+                "": "MAP(ARRAY(a, c), ARRAY(b, d))",
+                "clickhouse": "map(a, b, c, d)",
                 "duckdb": "MAP(LIST_VALUE(a, c), LIST_VALUE(b, d))",
                 "presto": "MAP(ARRAY[a, c], ARRAY[b, d])",
                 "hive": "MAP(a, b, c, d)",
-                "spark": "MAP_FROM_ARRAYS(ARRAY(a, c), ARRAY(b, d))",
+                "spark": "MAP(a, b, c, d)",
             },
         )
         self.validate_all(
@@ -463,7 +473,7 @@ class TestHive(Validator):
                 "duckdb": "MAP(LIST_VALUE(a), LIST_VALUE(b))",
                 "presto": "MAP(ARRAY[a], ARRAY[b])",
                 "hive": "MAP(a, b)",
-                "spark": "MAP_FROM_ARRAYS(ARRAY(a), ARRAY(b))",
+                "spark": "MAP(a, b)",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index e0934d7..dc93c3a 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -67,6 +67,7 @@ class TestPostgres(Validator):
         self.validate_identity("SELECT SUBSTRING('bla' + 'foo' || 'bar' FROM 3 - 1 + 5 FOR 4 + SOME_FUNC(arg1, arg2))")
         self.validate_identity("SELECT TRIM(' X' FROM ' XXX ')")
         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_all(
             "CREATE TABLE x (a UUID, b BYTEA)",
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 2145966..8a33e2d 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -305,3 +305,35 @@ class TestSnowflake(Validator):
         self.validate_identity(
             "CREATE PROCEDURE a.b.c(x INT, y VARIANT) RETURNS OBJECT EXECUTE AS CALLER AS 'BEGIN SELECT 1; END;'"
         )
+
+    def test_table_literal(self):
+        # All examples from https://docs.snowflake.com/en/sql-reference/literals-table.html
+        self.validate_all(
+            r"""SELECT * FROM TABLE('MYTABLE')""", write={"snowflake": r"""SELECT * FROM TABLE('MYTABLE')"""}
+        )
+
+        self.validate_all(
+            r"""SELECT * FROM TABLE('MYDB."MYSCHEMA"."MYTABLE"')""",
+            write={"snowflake": r"""SELECT * FROM TABLE('MYDB."MYSCHEMA"."MYTABLE"')"""},
+        )
+
+        # Per Snowflake documentation at https://docs.snowflake.com/en/sql-reference/literals-table.html
+        # one can use either a  " ' " or " $$ " to enclose the object identifier.
+        # Capturing the single tokens seems like lot of work. Hence adjusting tests to use these interchangeably,
+        self.validate_all(
+            r"""SELECT * FROM TABLE($$MYDB. "MYSCHEMA"."MYTABLE"$$)""",
+            write={"snowflake": r"""SELECT * FROM TABLE('MYDB. "MYSCHEMA"."MYTABLE"')"""},
+        )
+
+        self.validate_all(r"""SELECT * FROM TABLE($MYVAR)""", write={"snowflake": r"""SELECT * FROM TABLE($MYVAR)"""})
+
+        self.validate_all(r"""SELECT * FROM TABLE(?)""", write={"snowflake": r"""SELECT * FROM TABLE(?)"""})
+
+        self.validate_all(
+            r"""SELECT * FROM TABLE(:BINDING)""", write={"snowflake": r"""SELECT * FROM TABLE(:BINDING)"""}
+        )
+
+        self.validate_all(
+            r"""SELECT * FROM TABLE($MYVAR) WHERE COL1 = 10""",
+            write={"snowflake": r"""SELECT * FROM TABLE($MYVAR) WHERE COL1 = 10"""},
+        )
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 8377e47..9a7e64c 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -111,12 +111,70 @@ TBLPROPERTIES (
             "SELECT /*+ COALESCE(3) */ * FROM x",
             write={
                 "spark": "SELECT /*+ COALESCE(3) */ * FROM x",
+                "bigquery": "SELECT * FROM x",
             },
         )
         self.validate_all(
             "SELECT /*+ COALESCE(3), REPARTITION(1) */ * FROM x",
             write={
                 "spark": "SELECT /*+ COALESCE(3), REPARTITION(1) */ * FROM x",
+                "bigquery": "SELECT * FROM x",
+            },
+        )
+        self.validate_all(
+            "SELECT /*+ BROADCAST(table) */ cola FROM table",
+            write={
+                "spark": "SELECT /*+ BROADCAST(table) */ cola FROM table",
+                "bigquery": "SELECT cola FROM table",
+            },
+        )
+        self.validate_all(
+            "SELECT /*+ BROADCASTJOIN(table) */ cola FROM table",
+            write={
+                "spark": "SELECT /*+ BROADCASTJOIN(table) */ cola FROM table",
+                "bigquery": "SELECT cola FROM table",
+            },
+        )
+        self.validate_all(
+            "SELECT /*+ MAPJOIN(table) */ cola FROM table",
+            write={
+                "spark": "SELECT /*+ MAPJOIN(table) */ cola FROM table",
+                "bigquery": "SELECT cola FROM table",
+            },
+        )
+        self.validate_all(
+            "SELECT /*+ MERGE(table) */ cola FROM table",
+            write={
+                "spark": "SELECT /*+ MERGE(table) */ cola FROM table",
+                "bigquery": "SELECT cola FROM table",
+            },
+        )
+        self.validate_all(
+            "SELECT /*+ SHUFFLEMERGE(table) */ cola FROM table",
+            write={
+                "spark": "SELECT /*+ SHUFFLEMERGE(table) */ cola FROM table",
+                "bigquery": "SELECT cola FROM table",
+            },
+        )
+        self.validate_all(
+            "SELECT /*+ MERGEJOIN(table) */ cola FROM table",
+            write={
+                "spark": "SELECT /*+ MERGEJOIN(table) */ cola FROM table",
+                "bigquery": "SELECT cola FROM table",
+            },
+        )
+        self.validate_all(
+            "SELECT /*+ SHUFFLE_HASH(table) */ cola FROM table",
+            write={
+                "spark": "SELECT /*+ SHUFFLE_HASH(table) */ cola FROM table",
+                "bigquery": "SELECT cola FROM table",
+            },
+        )
+        self.validate_all(
+            "SELECT /*+ SHUFFLE_REPLICATE_NL(table) */ cola FROM table",
+            write={
+                "spark": "SELECT /*+ SHUFFLE_REPLICATE_NL(table) */ cola FROM table",
+                "bigquery": "SELECT cola FROM table",
             },
         )