select_to_arrow: all-NULL columns lose their type (Arrow null) instead of the DB column type

[cofin]

Summary

select_to_arrow builds the Arrow table from row values (pa.Table.from_pylist) rather than from the driver's column type descriptors. When a result column is NULL in every row, PyArrow infers it as Arrow null type instead of the column's actual DB type (e.g. varchar). Downstream consumers that rely on the declared type then break — most visibly, loading the Arrow table into DuckDB materializes a null-typed column as INTEGER, so any string operation on it (COALESCE(col, 'literal'), concatenation, etc.) raises a conversion error.

The type information is available at query time (asyncpg exposes it via PreparedStatement.get_attributes()), but it is discarded during the dict→Arrow conversion.

Environment

• sqlspec 0.54.3
• adapter: asyncpg
• pyarrow (any recent)

Root cause

sqlspec/utils/arrow_helpers.py → convert_dict_to_arrow / records_to_arrow_table use pa.Table.from_pylist(data), which infers types from values only. For an all-None column there is nothing to infer from, so the result is Arrow null.

Minimal demonstration of the inference gap (no DB required):

import pyarrow as pa

# All-NULL column -> type is lost:
print(pa.Table.from_pylist([{"label": None}, {"label": None}]).schema.field("label").type)
# -> null

# Any non-NULL value present -> inferred correctly:
print(pa.Table.from_pylist([{"label": None}, {"label": "x"}]).schema.field("label").type)
# -> string

Reproduction via the public API (asyncpg + Postgres)

import asyncio
from sqlspec.adapters.asyncpg import AsyncpgConfig

async def main() -> None:
    config = AsyncpgConfig(
        connection_config={"dsn": "postgresql://postgres:postgres@localhost:5432/postgres"}
    )
    async with config.provide_session() as driver:
        await driver.execute_script(
            "CREATE TEMP TABLE demo (id integer, label varchar);"
            "INSERT INTO demo (id, label) VALUES (1, NULL), (2, NULL);"
        )
        table = (await driver.select_to_arrow("SELECT id, label FROM demo")).get_data()
        print(table.schema)

asyncio.run(main())

Observed:

id: int64
label: null        # expected: string  (the column is varchar)

Because label is varchar but NULL in every row, it comes back as Arrow null. Registering this table in DuckDB and selecting from it then treats label as INTEGER, and COALESCE(label, 'x') fails with Could not convert string 'x' to INT32.

Suggested fix

Build the Arrow schema from the driver's column type metadata instead of inferring from values:

• asyncpg: PreparedStatement.get_attributes() gives each column's type (name/oid); map those OIDs to Arrow types and pass an explicit schema= to the Arrow construction (sqlspec already has convert_dict_to_arrow_with_schema / cast_arrow_table_schema).
• As a minimum, when a column infers to Arrow null, fall back to a concrete type (e.g. string) rather than emitting null — though type-accurate from the descriptor is preferable and also fixes wrong inference on other all-NULL types (numeric, timestamp, etc.).

Workaround

After select_to_arrow, retype null-typed columns before handing the table to a strict consumer:

import pyarrow as pa

def retype_null_columns(table: pa.Table) -> pa.Table:
    if not any(pa.types.is_null(f.type) for f in table.schema):
        return table
    fields = [f.with_type(pa.string()) if pa.types.is_null(f.type) else f for f in table.schema]
    return table.cast(pa.schema(fields))