Repository: tobymao/sqlglot
Pull request: fix(duckdb): Fix STRUCT_PACK -> ROW due to is_struct_cast
Fixes #3808

The recent PR https://github.com/tobymao/sqlglot/pull/3751 enabled transpilation of BQ's `STRUCT<type>(values)` to DuckDB `CAST(ROW(<values>) AS STRUCT(<struct>))`, and the core DDB generation logic was based on the `is_struct_cast` condition. However, a struct can be casted to other types such as `JSON` which will trip this condition.

This PR fixes this regression by making sure the `is_struct_cast` more explicit, i.e it now searches the `exp.Cast` for a `STRUCT` definition under it before transforming to the casted `ROW` representation  

Problem statement:
SqlGlot messes up DuckDb Struct_pack + Json
DuckDB transforms struct_pack to json to row, which is wrong:

```python
import sqlglot

print(
    sqlglot.parse_one(
        """select struct_pack(pig:='other 42')::json""",
        dialect="duckdb",
    ).sql("duckdb")
)
# prints SELECT CAST(ROW('other 42') AS JSON)
```

Which is wrong, since it drops the struct key

Interestingly, the same does not happen without ::json

Hints from issue discussion:

Commit message: fix(duckdb): Fix STRUCT_PACK -> ROW due to is_struct_cast

Patch to review:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index ca747069af..aae2c18030 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -134,7 +134,14 @@ def _struct_sql(self: DuckDB.Generator, expression: exp.Struct) -> str:
 
     # BigQuery allows inline construction such as "STRUCT<a STRING, b INTEGER>('str', 1)" which is
     # canonicalized to "ROW('str', 1) AS STRUCT(a TEXT, b INT)" in DuckDB
-    is_struct_cast = expression.find_ancestor(exp.Cast)
+    # The transformation to ROW will take place if a cast to STRUCT / ARRAY of STRUCTs is found
+    ancestor_cast = expression.find_ancestor(exp.Cast)
+    is_struct_cast = ancestor_cast and any(
+        [
+            casted_type.is_type(exp.DataType.Type.STRUCT)
+            for casted_type in ancestor_cast.find_all(exp.DataType)
+        ]
+    )
 
     for i, expr in enumerate(expression.expressions):
         is_property_eq = isinstance(expr, exp.PropertyEQ)
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 63e8179adc..d092d3bbb7 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -1056,7 +1056,14 @@ def test_cast(self):
             "CAST([STRUCT_PACK(a := 1)] AS STRUCT(a BIGINT)[])",
             "CAST([ROW(1)] AS STRUCT(a BIGINT)[])",
         )
-
+        self.validate_identity(
+            "STRUCT_PACK(a := 'b')::json",
+            "CAST({'a': 'b'} AS JSON)",
+        )
+        self.validate_identity(
+            "STRUCT_PACK(a := 'b')::STRUCT(a TEXT)",
+            "CAST(ROW('b') AS STRUCT(a TEXT))",
+        )
         self.validate_all(
             "CAST(x AS VARCHAR(5))",
             write={


Code review comments:
diff_hunk: @@ -134,7 +134,14 @@ def _struct_sql(self: DuckDB.Generator, expression: exp.Struct) -> str:
 
     # BigQuery allows inline construction such as "STRUCT<a STRING, b INTEGER>('str', 1)" which is
     # canonicalized to "ROW('str', 1) AS STRUCT(a TEXT, b INT)" in DuckDB
-    is_struct_cast = expression.find_ancestor(exp.Cast)
+    # The transformation to ROW will take place if a cast to STRUCT / ARRAY of STRUCTs is found
+    ancestor_cast = expression.find_ancestor(exp.Cast)
+    is_struct_cast = ancestor_cast and any(
+        [
path: sqlglot/dialects/duckdb.py
text: @user1:
i think this bracket isn't necessary, can do any on a generator