bug: to_polars fails on duckdb with optional struct column (works with polars backend)

[vspinu]

What happened?

Duckdb backend fails to extract polars data frame with optional (null) values in structs:

# this works
ibis.set_backend("polars")
data = [
    {'col': {'1': "xyz", '2': None}},
]
tbl = ibis.memtable(pl.DataFrame(data))
tbl.to_polars()
 

# this fails:
ibis.set_backend("duckdb")
data = [
    {'col': {'1': "xyz", '2': None}},
]
tbl = ibis.memtable(pl.DataFrame(data))
tbl.to_polars()


  File ~...venv/lib/python3.11/site-packages/ibis/expr/types/core.py:611 in to_polars
    return self._find_backend(use_default=True).to_polars(

  File ~...venv/lib/python3.11/site-packages/ibis/backends/__init__.py:265 in to_polars
    table = self.to_pyarrow(expr.as_table(), params=params, limit=limit, **kwargs)

  File ~...venv/lib/python3.11/site-packages/ibis/backends/duckdb/__init__.py:1425 in to_pyarrow
    return expr.__pyarrow_result__(table, data_mapper=DuckDBPyArrowData)

  File ~...venv/lib/python3.11/site-packages/ibis/expr/types/relations.py:222 in __pyarrow_result__
    return data_mapper.convert_table(

  File ~...venv/lib/python3.11/site-packages/ibis/formats/pyarrow.py:332 in convert_table
    arrays = [

  File ~...venv/lib/python3.11/site-packages/ibis/formats/pyarrow.py:333 in <listcomp>
    cls.convert_column(table[name], dtype) for name, dtype in schema.items()

  File ~...venv/lib/python3.11/site-packages/ibis/backends/duckdb/converter.py:31 in convert_column
    return super().convert_column(column, dtype)

  File ~...venv/lib/python3.11/site-packages/ibis/formats/pyarrow.py:323 in convert_column
    return column.cast(desired_type)

  File pyarrow/table.pxi:566 in pyarrow.lib.ChunkedArray.cast

  File ~...venv/lib/python3.11/site-packages/pyarrow/compute.py:404 in cast
    return call_function("cast", [arr], options, memory_pool)

  File pyarrow/_compute.pyx:590 in pyarrow._compute.call_function

  File pyarrow/_compute.pyx:385 in pyarrow._compute.Function.call

  File pyarrow/error.pxi:154 in pyarrow.lib.pyarrow_internal_check_status

  File pyarrow/error.pxi:91 in pyarrow.lib.check_status

ArrowNotImplementedError: Unsupported cast from int32 to null using function cast_null

What version of ibis are you using?

10.4.0

What backend(s) are you using, if any?

duckdb, polars

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

Thanks for the report, taking a look now!

[cpcloud]

This is a long-standing issue in DuckDB. I still don't quite understand why, and I can't find the place where this was discussed on GitHub (it might've been on Discord), but all NULL columns/fields are always returned with int32 type.

For top-level fields, we can handle this easily. For nested types, since arrow doesn't allow casting really anything that could be not-null to the null type, we would have to recursively reconstruct the entire column to paper over this issue.

So, there are three ways forward:

1. Do nothing.
2. DuckDB exposes a NULL type, which they already have, it's just not public.
3. Arrow grows an API that allows casting to the null type from things that could potentially be non-null.