Adding upstream version 11.3.6.upstream/11.3.6

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

1 files changed, 61 insertions, 36 deletions

diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index a1a0090..c314163 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -21,10 +21,10 @@ class TestDuckDB(Validator):
         self.validate_all(
             "EPOCH_MS(x)",
             write={
-                "bigquery": "UNIX_TO_TIME(CAST(x / 1000 AS INT64))",
+                "bigquery": "UNIX_TO_TIME(x / 1000)",
                 "duckdb": "TO_TIMESTAMP(x / 1000)",
                 "presto": "FROM_UNIXTIME(x / 1000)",
-                "spark": "FROM_UNIXTIME(CAST(x / 1000 AS INT))",
+                "spark": "FROM_UNIXTIME(x / 1000)",
             },
         )
         self.validate_all(
@@ -120,28 +120,17 @@ class TestDuckDB(Validator):
         self.validate_identity("SELECT ROW(x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)")
         self.validate_identity("SELECT (x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)")
         self.validate_identity("SELECT a.x FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a)")
+        self.validate_identity("ATTACH DATABASE ':memory:' AS new_database")
         self.validate_identity(
             "SELECT a['x space'] FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a)"
         )
+
         self.validate_all(
             "CREATE TABLE IF NOT EXISTS table (cola INT, colb STRING) USING ICEBERG PARTITIONED BY (colb)",
             write={
                 "duckdb": "CREATE TABLE IF NOT EXISTS table (cola INT, colb TEXT)",
             },
         )
-
-        self.validate_all(
-            "COL::BIGINT[]",
-            write={
-                "duckdb": "CAST(COL AS BIGINT[])",
-                "presto": "CAST(COL AS ARRAY(BIGINT))",
-                "hive": "CAST(COL AS ARRAY<BIGINT>)",
-                "spark": "CAST(COL AS ARRAY<LONG>)",
-                "postgres": "CAST(COL AS BIGINT[])",
-                "snowflake": "CAST(COL AS ARRAY)",
-            },
-        )
-
         self.validate_all(
             "LIST_VALUE(0, 1, 2)",
             read={
@@ -212,7 +201,6 @@ class TestDuckDB(Validator):
                 "spark": "x.`y`.`abc`",
             },
         )
-
         self.validate_all(
             "QUANTILE(x, 0.5)",
             write={
@@ -222,14 +210,6 @@ class TestDuckDB(Validator):
                 "spark": "PERCENTILE(x, 0.5)",
             },
         )
-
-        self.validate_all(
-            "CAST(x AS DATE)",
-            write={
-                "duckdb": "CAST(x AS DATE)",
-                "": "CAST(x AS DATE)",
-            },
-        )
         self.validate_all(
             "UNNEST(x)",
             read={
@@ -240,7 +220,6 @@ class TestDuckDB(Validator):
                 "spark": "EXPLODE(x)",
             },
         )
-
         self.validate_all(
             "1d",
             write={
@@ -249,13 +228,6 @@ class TestDuckDB(Validator):
             },
         )
         self.validate_all(
-            "CAST(1 AS DOUBLE)",
-            read={
-                "hive": "1d",
-                "spark": "1d",
-            },
-        )
-        self.validate_all(
             "POWER(CAST(2 AS SMALLINT), 3)",
             read={
                 "hive": "POW(2S, 3)",
@@ -351,8 +323,6 @@ class TestDuckDB(Validator):
             },
         )
 
-        self.validate_identity("ATTACH DATABASE ':memory:' AS new_database")
-
         with self.assertRaises(UnsupportedError):
             transpile(
                 "SELECT a FROM b PIVOT(SUM(x) FOR y IN ('z', 'q'))",
@@ -372,13 +342,44 @@ class TestDuckDB(Validator):
         self.validate_identity("ARRAY(SELECT id FROM t)")
 
     def test_cast(self):
+        self.validate_identity("CAST(x AS REAL)")
+        self.validate_identity("CAST(x AS UINTEGER)")
+        self.validate_identity("CAST(x AS UBIGINT)")
+        self.validate_identity("CAST(x AS USMALLINT)")
+        self.validate_identity("CAST(x AS UTINYINT)")
+        self.validate_identity("CAST(x AS TEXT)")
+
+        self.validate_all("CAST(x AS NUMERIC)", write={"duckdb": "CAST(x AS DOUBLE)"})
+        self.validate_all("CAST(x AS CHAR)", write={"duckdb": "CAST(x AS TEXT)"})
+        self.validate_all("CAST(x AS BPCHAR)", write={"duckdb": "CAST(x AS TEXT)"})
+        self.validate_all("CAST(x AS STRING)", write={"duckdb": "CAST(x AS TEXT)"})
+        self.validate_all("CAST(x AS INT1)", write={"duckdb": "CAST(x AS TINYINT)"})
+        self.validate_all("CAST(x AS FLOAT4)", write={"duckdb": "CAST(x AS REAL)"})
+        self.validate_all("CAST(x AS FLOAT)", write={"duckdb": "CAST(x AS REAL)"})
+        self.validate_all("CAST(x AS INT4)", write={"duckdb": "CAST(x AS INT)"})
+        self.validate_all("CAST(x AS INTEGER)", write={"duckdb": "CAST(x AS INT)"})
+        self.validate_all("CAST(x AS SIGNED)", write={"duckdb": "CAST(x AS INT)"})
+        self.validate_all("CAST(x AS BLOB)", write={"duckdb": "CAST(x AS BLOB)"})
+        self.validate_all("CAST(x AS BYTEA)", write={"duckdb": "CAST(x AS BLOB)"})
+        self.validate_all("CAST(x AS BINARY)", write={"duckdb": "CAST(x AS BLOB)"})
+        self.validate_all("CAST(x AS VARBINARY)", write={"duckdb": "CAST(x AS BLOB)"})
+        self.validate_all("CAST(x AS LOGICAL)", write={"duckdb": "CAST(x AS BOOLEAN)"})
+        self.validate_all(
+            "CAST(x AS BIT)",
+            read={
+                "duckdb": "CAST(x AS BITSTRING)",
+            },
+            write={
+                "duckdb": "CAST(x AS BIT)",
+                "tsql": "CAST(x AS BIT)",
+            },
+        )
         self.validate_all(
             "123::CHARACTER VARYING",
             write={
                 "duckdb": "CAST(123 AS TEXT)",
             },
         )
-
         self.validate_all(
             "cast([[1]] as int[][])",
             write={
@@ -386,10 +387,34 @@ class TestDuckDB(Validator):
                 "spark": "CAST(ARRAY(ARRAY(1)) AS ARRAY<ARRAY<INT>>)",
             },
         )
-
         self.validate_all(
             "CAST(x AS DATE) + INTERVAL (7 * -1) DAY", read={"spark": "DATE_SUB(x, 7)"}
         )
+        self.validate_all(
+            "CAST(1 AS DOUBLE)",
+            read={
+                "hive": "1d",
+                "spark": "1d",
+            },
+        )
+        self.validate_all(
+            "CAST(x AS DATE)",
+            write={
+                "duckdb": "CAST(x AS DATE)",
+                "": "CAST(x AS DATE)",
+            },
+        )
+        self.validate_all(
+            "COL::BIGINT[]",
+            write={
+                "duckdb": "CAST(COL AS BIGINT[])",
+                "presto": "CAST(COL AS ARRAY(BIGINT))",
+                "hive": "CAST(COL AS ARRAY<BIGINT>)",
+                "spark": "CAST(COL AS ARRAY<LONG>)",
+                "postgres": "CAST(COL AS BIGINT[])",
+                "snowflake": "CAST(COL AS ARRAY)",
+            },
+        )
 
     def test_bool_or(self):
         self.validate_all(