crate / crate/sqlalchemy-cratedb
Unknown column types fall back to abstract UserDefinedType
- Dominant language
- Python
- Stars
- 9
- Forks
- 4
- Avg merge
- 6d 12h
- Merged PRs (30d)
- 3
Description
## Summary
`CrateDialect._resolve_type()` falls back to `sqlalchemy.types.UserDefinedType` for any CrateDB type not present in `TYPES_MAP`. `UserDefinedType` is abstract, it has no `get_col_spec()`, so reflection appears to succeed but any subsequent DDL compilation fails with a misleading error:
```
AttributeError: 'UserDefinedType' object has no attribute 'get_col_spec'
```
Several CrateDB types are missing from `TYPES_MAP`. This is the same defect class as #226 (timestamps).The fallback itself was left in place, so the bug simply moved to the next unmapped type.
## Reproduction
```python
import sqlalchemy as sa
engine = sa.create_engine("crate://localhost:4200/")
meta = sa.MetaData(schema="sys")
table = sa.Table("summits", meta, autoload_with=engine) # succeeds
print(sa.schema.CreateTable(table).compile(engine)) # AttributeError
```
`sys.summits.coordinates` is a `geo_point`. Reflection silently produces `UserDefinedType` for that column; the failure only surfaces at compile time, far from the cause.
## Root cause
`src/sqlalchemy_cratedb/dialect.py`:
```python
def _resolve_type(self, type_):
return TYPES_MAP.get(type_, sqltypes.UserDefinedType)
```
Two distinct problems:
1. **The fallback is not a usable type.** `sqltypes.NullType` is SQLAlchemy's own sentinel for "type unknown to the dialect". It supports reflect-and-`SELECT` (the common case), and raises a clear `CompileError` naming the column if someone tries to emit DDL from it. `UserDefinedType` supports neither and produces an `AttributeError` with no indication of which column or type caused it.
2. **The failure is silent at reflection time.** Nothing is logged when a type cannot be resolved, so the information needed to diagnose it (the unmapped `data_type` string) is discarded at exactly the moment it is available.
## Missing mappings
`geo_point` and `geo_shape` are the notable case, because working implementations **already exist** in `src/sqlalchemy_cratedb/type/geo.py` (`Geopoint`, `Geoshape`, both with correct `get_col_spec()`) and are exported from the package — they are simply never registered in `TYPES_MAP`. Registering them is a two-line change.
| CrateDB type | In `TYPES_MAP` | Dialect implementation exists |
| --- | --- | --- |
| `geo_point` | no | yes (`Geopoint`) |
| `geo_shape` | no | yes (`Geoshape`) |
| `ip` | no | no |
| `bit` | no | no |
| `interval` | no | no |
| `numeric` | no | no |
| `date` | no | no |
| `time` / `time with time zone` | no | no |
| `character` / `char(n)` | no | no |
| `uuid` | no | no |
| `row` | no | no |
| `regclass`, `regproc`, `regtype` | no | no |
### Arrays are enumerated rather than derived
Array types are registered one key at a time (`integer_array`, `text_array`, … and the oddly-spelled `"double precision_array"`), which is why `object_array` works but `geo_point_array`, `ip_array`, `numeric_array` and friends do not. Since the key format is consistently `f"{inner}_array"`, `_resolve_type()` could handle the whole family by recursing on the inner type and wrapping in `ARRAY`, instead of the map growing a second entry for every type added.
## Impact
- Any table using an unmapped type cannot have DDL generated from reflection.
- Downstream, `cratedb-toolkit` had to carry a hardcoded blocklist to keep ctk cfr sys-export` from aborting on `sys.summits` (see crate/cratedb-toolkit#870), which silently dropped that table from diagnostics bundles.
Contributor guide
Assessment
This issue has not been assessed yet.