Use varying ARRAY inner types in CrateDB's `OBJECT(IGNORED)` columns

cratedb_object_ignored_varying_inner_types.py

"""
## About
Use varying ARRAY inner types in CrateDB's `OBJECT(IGNORED)` columns.

## Usage
```
uv run cratedb_object_ignored_varying_inner_types.py
```

## Observation
April 2026: It works with CrateDB 6.3, but fails on CrateDB nightly.

## References
https://github.com/crate/commons-codec/issues/147
"""

# /// script
# requires-python = ">=3.9"
# dependencies = [
#   "sqlalchemy-cratedb",
# ]
# ///

import sqlalchemy as sa
from pprint import pprint


def workload():
    """
    # Works on CrateDB 6.3
    docker run --rm --publish=4200:4200 --publish=5432:5432 crate/crate:6.3.1 -Cdiscovery.type=single-node
    uv run cratedb_object_ignored_varying_inner_types.py
    ```
    {'varying_inner_types': [{'one': 1.0}, ['two', 2.0], 'Three', 4, True, None]}
    ```

    # Fails on CrateDB nightly
    docker run --rm --publish=4200:4200 --publish=5432:5432 crate/crate:nightly -Cdiscovery.type=single-node
    uv run cratedb_object_ignored_varying_inner_types.py
    ```
    SQLParseException['double precision_array' is not convertible to 'object']
    [SQL: INSERT INTO t01 (data) VALUES (?);]
    [parameters: ({'varying_inner_types': [{'one': 1.0}, ['two', 2.0], 'Three', 4, True, None]},)]
    ```
    """

    engine = sa.create_engine("crate://")
    with engine.connect() as conn:
        conn.execute(sa.text("DROP TABLE IF EXISTS t01;"))
        conn.execute(sa.text("CREATE TABLE t01 (data OBJECT(IGNORED));"))
        conn.execute(
            sa.text("INSERT INTO t01 (data) VALUES (:value);"),
            {"value": {"varying_inner_types": [{"one": 1.0}, ["two", 2.0], "Three", 4, True, None]}},
        )
        conn.execute(sa.text("REFRESH TABLE t01;"))
        pprint(conn.execute(sa.text("SELECT * FROM t01;")).scalar())


if __name__ == "__main__":
    workload()