crate / crate/sqlalchemy-cratedb

Unknown column types fall back to abstract UserDefinedType

Open
#295 0 comments 1 reaction 1 assignee Claimed by @florinutz View on GitHub
bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.