Adding upstream version 16.4.0.upstream/16.4.0

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

1 files changed, 21 insertions, 6 deletions

diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 4f37be5..852b494 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -7,11 +7,11 @@ class TestPresto(Validator):
 
     def test_cast(self):
         self.validate_all(
-            "SELECT DATE_DIFF('week', CAST(SUBSTR(CAST('2009-01-01' AS VARCHAR), 1, 10) AS DATE), CAST(SUBSTR(CAST('2009-12-31' AS VARCHAR), 1, 10) AS DATE))",
+            "SELECT DATE_DIFF('week', CAST(CAST('2009-01-01' AS TIMESTAMP) AS DATE), CAST(CAST('2009-12-31' AS TIMESTAMP) AS DATE))",
             read={"redshift": "SELECT DATEDIFF(week, '2009-01-01', '2009-12-31')"},
         )
         self.validate_all(
-            "SELECT DATE_ADD('month', 18, CAST(SUBSTR(CAST('2008-02-28' AS VARCHAR), 1, 10) AS DATE))",
+            "SELECT DATE_ADD('month', 18, CAST(CAST('2008-02-28' AS TIMESTAMP) AS DATE))",
             read={"redshift": "SELECT DATEADD(month, 18, '2008-02-28')"},
         )
         self.validate_all(
@@ -664,16 +664,31 @@ class TestPresto(Validator):
                 "spark": "TO_JSON(x)",
             },
             write={
+                "bigquery": "TO_JSON_STRING(x)",
+                "duckdb": "CAST(TO_JSON(x) AS TEXT)",
                 "presto": "JSON_FORMAT(x)",
                 "spark": "TO_JSON(x)",
             },
         )
-
         self.validate_all(
-            "JSON_FORMAT(JSON 'x')",
+            """JSON_FORMAT(JSON '"x"')""",
+            write={
+                "bigquery": """TO_JSON_STRING(CAST('"x"' AS JSON))""",
+                "duckdb": """CAST(TO_JSON(CAST('"x"' AS JSON)) AS TEXT)""",
+                "presto": """JSON_FORMAT(CAST('"x"' AS JSON))""",
+                "spark": """REGEXP_EXTRACT(TO_JSON(FROM_JSON('["x"]', SCHEMA_OF_JSON('["x"]'))), '^.(.*).$', 1)""",
+            },
+        )
+        self.validate_all(
+            """SELECT JSON_FORMAT(JSON '{"a": 1, "b": "c"}')""",
+            write={
+                "spark": """SELECT REGEXP_EXTRACT(TO_JSON(FROM_JSON('[{"a": 1, "b": "c"}]', SCHEMA_OF_JSON('[{"a": 1, "b": "c"}]'))), '^.(.*).$', 1)""",
+            },
+        )
+        self.validate_all(
+            """SELECT JSON_FORMAT(JSON '[1, 2, 3]')""",
             write={
-                "presto": "JSON_FORMAT(CAST('x' AS JSON))",
-                "spark": "TO_JSON('x')",
+                "spark": "SELECT REGEXP_EXTRACT(TO_JSON(FROM_JSON('[[1, 2, 3]]', SCHEMA_OF_JSON('[[1, 2, 3]]'))), '^.(.*).$', 1)",
             },
         )