Adding upstream version 23.12.1.upstream/23.12.1

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

11 files changed, 149 insertions, 11 deletions

diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 300d492..301cd57 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -20,6 +20,27 @@ class TestBigQuery(Validator):
     maxDiff = None
 
     def test_bigquery(self):
+        self.validate_identity(
+            "[a, a(1, 2,3,4444444444444444, tttttaoeunthaoentuhaoentuheoantu, toheuntaoheutnahoeunteoahuntaoeh), b(3, 4,5), c, d, tttttttttttttttteeeeeeeeeeeeeett, 12312312312]",
+            """[
+  a,
+  a(
+    1,
+    2,
+    3,
+    4444444444444444,
+    tttttaoeunthaoentuhaoentuheoantu,
+    toheuntaoheutnahoeunteoahuntaoeh
+  ),
+  b(3, 4, 5),
+  c,
+  d,
+  tttttttttttttttteeeeeeeeeeeeeett,
+  12312312312
+]""",
+            pretty=True,
+        )
+
         self.validate_all(
             "SELECT STRUCT(1, 2, 3), STRUCT(), STRUCT('abc'), STRUCT(1, t.str_col), STRUCT(1 as a, 'abc' AS b), STRUCT(str_col AS abc)",
             write={
@@ -40,6 +61,10 @@ class TestBigQuery(Validator):
                 "duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S.%f%z')",
             },
         )
+        self.validate_identity(
+            "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%z', x)",
+            "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%z', x)",
+        )
 
         table = parse_one("x-0._y.z", dialect="bigquery", into=exp.Table)
         self.assertEqual(table.catalog, "x-0")
@@ -57,9 +82,13 @@ class TestBigQuery(Validator):
         self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql("bigquery"), "`x.y.z`")
         self.assertEqual(exp.to_table("`x`.`y`", dialect="bigquery").sql("bigquery"), "`x`.`y`")
 
+        column = self.validate_identity("SELECT `db.t`.`c` FROM `db.t`").selects[0]
+        self.assertEqual(len(column.parts), 3)
+
         select_with_quoted_udf = self.validate_identity("SELECT `p.d.UdF`(data) FROM `p.d.t`")
         self.assertEqual(select_with_quoted_udf.selects[0].name, "p.d.UdF")
 
+        self.validate_identity("assert.true(1 = 1)")
         self.validate_identity("SELECT ARRAY_TO_STRING(list, '--') AS text")
         self.validate_identity("SELECT jsondoc['some_key']")
         self.validate_identity("SELECT `p.d.UdF`(data).* FROM `p.d.t`")
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index df3caaf..af552d1 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -93,6 +93,9 @@ class TestClickhouse(Validator):
         self.validate_identity("""SELECT JSONExtractString('{"x": {"y": 1}}', 'x', 'y')""")
         self.validate_identity("SELECT * FROM table LIMIT 1 BY a, b")
         self.validate_identity("SELECT * FROM table LIMIT 2 OFFSET 1 BY a, b")
+        self.validate_identity(
+            "SELECT id, quantileGK(100, 0.95)(reading) OVER (PARTITION BY id ORDER BY id RANGE BETWEEN 30000 PRECEDING AND CURRENT ROW) AS window FROM table"
+        )
 
         self.validate_identity(
             "SELECT $1$foo$1$",
@@ -409,6 +412,19 @@ class TestClickhouse(Validator):
         self.validate_identity("SELECT FORMAT")
         self.validate_identity("1 AS FORMAT").assert_is(exp.Alias)
 
+        self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d', '%T')")
+        self.validate_all(
+            "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+            read={
+                "clickhouse": "SELECT formatDateTime(NOW(), '%Y-%m-%d')",
+                "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+            },
+            write={
+                "clickhouse": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+                "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+            },
+        )
+
     def test_cte(self):
         self.validate_identity("WITH 'x' AS foo SELECT foo")
         self.validate_identity("WITH ['c'] AS field_names SELECT field_names")
@@ -813,3 +829,30 @@ LIFETIME(MIN 0 MAX 0)""",
         self.validate_identity(
             "CREATE TABLE t1 (a String EPHEMERAL, b String EPHEMERAL func(), c String MATERIALIZED func(), d String ALIAS func()) ENGINE=TinyLog()"
         )
+
+    def test_agg_functions(self):
+        def extract_agg_func(query):
+            return parse_one(query, read="clickhouse").selects[0].this
+
+        self.assertIsInstance(
+            extract_agg_func("select quantileGK(100, 0.95) OVER (PARTITION BY id) FROM table"),
+            exp.AnonymousAggFunc,
+        )
+        self.assertIsInstance(
+            extract_agg_func(
+                "select quantileGK(100, 0.95)(reading) OVER (PARTITION BY id) FROM table"
+            ),
+            exp.ParameterizedAgg,
+        )
+        self.assertIsInstance(
+            extract_agg_func("select quantileGKIf(100, 0.95) OVER (PARTITION BY id) FROM table"),
+            exp.CombinedAggFunc,
+        )
+        self.assertIsInstance(
+            extract_agg_func(
+                "select quantileGKIf(100, 0.95)(reading) OVER (PARTITION BY id) FROM table"
+            ),
+            exp.CombinedParameterizedAgg,
+        )
+
+        parse_one("foobar(x)").assert_is(exp.Anonymous)
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 691beb9..ea38521 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -2426,3 +2426,18 @@ FROM c""",
             """CREATE TEMPORARY SEQUENCE seq START WITH = 1 INCREMENT BY = 2""",
             """CREATE TEMPORARY SEQUENCE seq START WITH 1 INCREMENT BY 2""",
         )
+
+    def test_reserved_keywords(self):
+        order = exp.select("*").from_("order")
+
+        for dialect in ("presto", "redshift"):
+            dialect = Dialect.get_or_raise(dialect)
+            self.assertEqual(
+                order.sql(dialect=dialect),
+                f"SELECT * FROM {dialect.IDENTIFIER_START}order{dialect.IDENTIFIER_END}",
+            )
+
+        self.validate_identity(
+            """SELECT partition.d FROM t PARTITION (d)""",
+            """SELECT partition.d FROM t AS PARTITION(d)""",
+        )
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 0b13a70..9105a49 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -652,8 +652,14 @@ class TestDuckDB(Validator):
             "SELECT CAST('2020-05-06' AS DATE) + INTERVAL 5 DAY",
             read={"bigquery": "SELECT DATE_ADD(CAST('2020-05-06' AS DATE), INTERVAL 5 DAY)"},
         )
-        self.validate_identity("SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t")
-        self.validate_identity("SELECT PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t")
+        self.validate_identity(
+            "SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t",
+            "SELECT QUANTILE_CONT(y, 0.25 ORDER BY y DESC) FROM t",
+        )
+        self.validate_identity(
+            "SELECT PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t",
+            "SELECT QUANTILE_DISC(y, 0.25 ORDER BY y DESC) FROM t",
+        )
         self.validate_all(
             "SELECT QUANTILE_CONT(x, q) FROM t",
             write={
@@ -1075,6 +1081,15 @@ class TestDuckDB(Validator):
             write={
                 "snowflake": "ALTER TABLE db.t1 RENAME TO db.t2",
                 "duckdb": "ALTER TABLE db.t1 RENAME TO t2",
+                "tsql": "EXEC sp_rename 'db.t1', 't2'",
+            },
+        )
+        self.validate_all(
+            'ALTER TABLE "db"."t1" RENAME TO "db"."t2"',
+            write={
+                "snowflake": 'ALTER TABLE "db"."t1" RENAME TO "db"."t2"',
+                "duckdb": 'ALTER TABLE "db"."t1" RENAME TO "t2"',
+                "tsql": "EXEC sp_rename '[db].[t1]', 't2'",
             },
         )
 
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 33294ee..9215f05 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -429,6 +429,9 @@ class TestHive(Validator):
             "INSERT OVERWRITE TABLE zipcodes PARTITION(state = 0) VALUES (896, 'US', 'TAMPA', 33607)"
         )
         self.validate_identity(
+            "INSERT OVERWRITE DIRECTORY 'x' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':' LINES TERMINATED BY '' STORED AS TEXTFILE SELECT * FROM `a`.`b`"
+        )
+        self.validate_identity(
             "SELECT a, b, SUM(c) FROM tabl AS t GROUP BY a, b, GROUPING SETS ((a, b), a)"
         )
         self.validate_identity(
@@ -524,9 +527,11 @@ class TestHive(Validator):
         self.validate_all(
             "APPROX_COUNT_DISTINCT(a)",
             write={
+                "bigquery": "APPROX_COUNT_DISTINCT(a)",
                 "duckdb": "APPROX_COUNT_DISTINCT(a)",
                 "presto": "APPROX_DISTINCT(a)",
                 "hive": "APPROX_COUNT_DISTINCT(a)",
+                "snowflake": "APPROX_COUNT_DISTINCT(a)",
                 "spark": "APPROX_COUNT_DISTINCT(a)",
             },
         )
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 6558c97..e8af5c6 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -223,6 +223,7 @@ class TestMySQL(Validator):
         self.validate_identity("CHAR(0)")
         self.validate_identity("CHAR(77, 121, 83, 81, '76')")
         self.validate_identity("CHAR(77, 77.3, '77.3' USING utf8mb4)")
+        self.validate_identity("SELECT * FROM t1 PARTITION(p0)")
 
     def test_types(self):
         self.validate_identity("CAST(x AS MEDIUMINT) + CAST(y AS YEAR(4))")
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index e1d8c06..4bafc08 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -871,7 +871,7 @@ class TestPresto(Validator):
             "SELECT ARRAY_SORT(x, (left, right) -> -1)",
             write={
                 "duckdb": "SELECT ARRAY_SORT(x)",
-                "presto": "SELECT ARRAY_SORT(x, (left, right) -> -1)",
+                "presto": 'SELECT ARRAY_SORT(x, ("left", "right") -> -1)',
                 "hive": "SELECT SORT_ARRAY(x)",
                 "spark": "SELECT ARRAY_SORT(x, (left, right) -> -1)",
             },
diff --git a/tests/dialects/test_prql.py b/tests/dialects/test_prql.py
index 69e2e28..1a0eec2 100644
--- a/tests/dialects/test_prql.py
+++ b/tests/dialects/test_prql.py
@@ -58,3 +58,11 @@ class TestPRQL(Validator):
         self.validate_identity(
             "from x intersect y", "SELECT * FROM x INTERSECT ALL SELECT * FROM y"
         )
+        self.validate_identity(
+            "from x filter a == null filter null != b",
+            "SELECT * FROM x WHERE a IS NULL AND NOT b IS NULL",
+        )
+        self.validate_identity(
+            "from x filter (a > 1 || null != b || c != null)",
+            "SELECT * FROM x WHERE (a > 1 OR NOT b IS NULL OR NOT c IS NULL)",
+        )
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index b652541..1cbf68c 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -456,7 +456,7 @@ WHERE
                 },
                 write={
                     "": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x NULLS LAST){suffix}",
-                    "duckdb": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+                    "duckdb": f"SELECT QUANTILE_CONT(x, 0.5 ORDER BY x){suffix}",
                     "postgres": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
                     "snowflake": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
                 },
@@ -821,6 +821,13 @@ WHERE
                 "snowflake": "CASE WHEN x = a OR (x IS NULL AND a IS NULL) THEN b WHEN x = c OR (x IS NULL AND c IS NULL) THEN d ELSE e END",
             },
         )
+        self.validate_all(
+            "SELECT LISTAGG(col1, ', ') WITHIN GROUP (ORDER BY col2) FROM t",
+            write={
+                "duckdb": "SELECT GROUP_CONCAT(col1, ', ' ORDER BY col2) FROM t",
+                "snowflake": "SELECT LISTAGG(col1, ', ') WITHIN GROUP (ORDER BY col2) FROM t",
+            },
+        )
 
     def test_null_treatment(self):
         self.validate_all(
@@ -1038,11 +1045,17 @@ WHERE
             "SELECT CAST('2019-02-28' AS DATE) + INTERVAL '1 day, 1 year'",
         )
 
-        self.validate_identity("DATE(x)").assert_is(exp.Anonymous)
-        self.validate_identity("TO_DATE(x)").assert_is(exp.Anonymous)
-        self.validate_identity("TRY_TO_DATE(x)").assert_is(exp.Anonymous)
+        self.validate_identity("TO_DATE(x)").assert_is(exp.TsOrDsToDate)
+        self.validate_identity("TRY_TO_DATE(x)").assert_is(exp.TsOrDsToDate)
 
         self.validate_all(
+            "DATE(x)",
+            write={
+                "duckdb": "CAST(x AS DATE)",
+                "snowflake": "TO_DATE(x)",
+            },
+        )
+        self.validate_all(
             "TO_DATE(x, 'MM-DD-YYYY')",
             write={
                 "snowflake": "TO_DATE(x, 'mm-DD-yyyy')",
@@ -1490,7 +1503,7 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS _flattene
             write={
                 "bigquery": "REGEXP_EXTRACT(subject, pattern, pos, occ)",
                 "hive": "REGEXP_EXTRACT(subject, pattern, group)",
-                "presto": "REGEXP_EXTRACT(subject, pattern, group)",
+                "presto": 'REGEXP_EXTRACT(subject, pattern, "group")',
                 "snowflake": "REGEXP_SUBSTR(subject, pattern, pos, occ, params, group)",
                 "spark": "REGEXP_EXTRACT(subject, pattern, group)",
             },
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index d2285e0..7534573 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -234,7 +234,7 @@ TBLPROPERTIES (
         self.validate_identity("first_value(col, true)", "FIRST_VALUE(col) IGNORE NULLS")
         self.validate_identity("last(col, true)", "LAST(col) IGNORE NULLS")
         self.validate_identity("last_value(col, true)", "LAST_VALUE(col) IGNORE NULLS")
-        self.validate_identity("DESCRIBE EXTENDED db.table")
+        self.validate_identity("DESCRIBE EXTENDED db.tbl")
         self.validate_identity("SELECT * FROM test TABLESAMPLE (50 PERCENT)")
         self.validate_identity("SELECT * FROM test TABLESAMPLE (5 ROWS)")
         self.validate_identity("SELECT * FROM test TABLESAMPLE (BUCKET 4 OUT OF 10)")
@@ -566,7 +566,7 @@ TBLPROPERTIES (
             "ARRAY_SORT(x, (left, right) -> -1)",
             write={
                 "duckdb": "ARRAY_SORT(x)",
-                "presto": "ARRAY_SORT(x, (left, right) -> -1)",
+                "presto": 'ARRAY_SORT(x, ("left", "right") -> -1)',
                 "hive": "SORT_ARRAY(x)",
                 "spark": "ARRAY_SORT(x, (left, right) -> -1)",
             },
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index aefd857..4a475f6 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -455,7 +455,6 @@ class TestTSQL(Validator):
         self.validate_identity("CAST(x AS UNIQUEIDENTIFIER)")
         self.validate_identity("CAST(x AS MONEY)")
         self.validate_identity("CAST(x AS SMALLMONEY)")
-        self.validate_identity("CAST(x AS ROWVERSION)")
         self.validate_identity("CAST(x AS IMAGE)")
         self.validate_identity("CAST(x AS SQL_VARIANT)")
         self.validate_identity("CAST(x AS BIT)")
@@ -475,6 +474,16 @@ class TestTSQL(Validator):
                 "hive": "CAST(x AS TIMESTAMP)",
             },
         )
+        self.validate_all(
+            "CAST(x AS ROWVERSION)",
+            read={
+                "tsql": "CAST(x AS TIMESTAMP)",
+            },
+            write={
+                "tsql": "CAST(x AS ROWVERSION)",
+                "hive": "CAST(x AS BINARY)",
+            },
+        )
 
     def test__types_ints(self):
         self.validate_all(