crate / crate/sqlalchemy-cratedb

Numeric is rendered as BIGINT and silently truncates every value the database could store exactly

Open
#292 2 comments 0 reactions 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

**Versions**

- sqlalchemy-cratedb 0.43.1
- SQLAlchemy 2.0.51
- Python 3.12.9, macOS
- Server: official `crate` image, CrateDB 6.4.1

## What happens

The dialect renders Core's `Numeric` as **`BIGINT`**. `SHOW CREATE TABLE` confirms it directly: a column declared `Numeric(10, 2)` through SQLAlchemy is created as `"c" BIGINT`.

The scale is therefore thrown away at write time — and the loss is invisible on read, because SQLAlchemy's own `Numeric` type re-applies the scale on the way out. You put in `Decimal("1.25")` and get back `Decimal("1.00")`, with no error anywhere:

| declared as | value in | value out |
|---|---|---|
| `Numeric(10, 2)` via SQLAlchemy | `Decimal("1.25")` | `Decimal("1.00")` |
| `Numeric(38, 4)` via SQLAlchemy | `Decimal("1.25")` | `Decimal("1.0000")` |
| `NUMERIC(10,2)` in raw SQL, literal value | `1.25` | `1.25` |
| `NUMERIC(10,2)` in raw SQL, bound `Decimal` | `Decimal("1.25")` | `1.25` |

The last two rows are the point: **the database supports the type and stores it exactly.** Only the path through the dialect loses data.

## Why I'm filing this separately from #163

\#163 tracks adding support for CrateDB's `NUMERIC` as a feature. This is narrower and, I'd argue, more urgent: the current behaviour isn't "unsupported", it's *silently wrong*. A column that refuses to be created gets found on day one. A column that quietly truncates every value and then hands back a plausible-looking `Decimal` with the right number of decimal places can run for months. I only caught it because I was comparing the same fixture across engines.

Feel free to fold this into #163 if you'd rather track it in one place — I mostly wanted the data-loss shape on the record.

## Suggested fix

Map `Numeric(precision, scale)` onto CrateDB's `NUMERIC(precision, scale)`. If that's blocked on something, raising at compile time would be much better than the current silent truncation.

I've had to add `Numeric` to the list of types I refuse to write on this dialect, which is a shame given the database handles it correctly.

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.