Decimal with a positive exponent is silently stored as its mantissa (Decimal('1E+2') → 1)

[leonz12]

What happens

Binding a decimal.Decimal whose as_tuple().exponent is positive to a DECIMAL column stores the mantissa and silently discards the magnitude. No exception, no warning.

Decimal('1E+2') (== 100) is stored as 1. Decimal('1000.0000').normalize() (== 1000) is stored as 1.

This is reachable from ordinary Python: Decimal.normalize() produces positive exponents, as does Decimal('1e3'), and division can (Decimal(1000) / Decimal('0.25') → Decimal('4.0E+3')).

Reproduction

from decimal import Decimal
import duckdb, sys, platform

con = duckdb.connect()
con.execute("CREATE TABLE t (v DECIMAL(28,8))")
print(f"duckdb {duckdb.__version__} | python {sys.version.split()[0]} | {platform.system()} {platform.machine()}\n")
print(f"{'input':<10} {'true value':>22} {'stored':>22}  {'bound type':<14} ok")
for s in ["1E+2","123E+2","1.5E+3","12.34E+5","999E+9","100","1.23","1E+18","1E+19","1E+20","1E+21"]:
    d = Decimal(s); con.execute("DELETE FROM t")
    try:
        con.execute("INSERT INTO t VALUES (?)", [d])
        stored = con.execute("SELECT v FROM t").fetchone()[0]
    except Exception as e:
        stored = f"<{type(e).__name__}>"
    typ = con.execute("SELECT typeof(?)", [d]).fetchone()[0]
    print(f"{s:<10} {str(d):>22} {str(stored):>22}  {typ:<14} {'OK' if stored == d else '**WRONG**'}")

Actual:

duckdb 1.5.5 | python 3.11.14 | Linux x86_64

input                  true value                 stored  bound type     ok
1E+2                         1E+2             1.00000000  DECIMAL(3,2)   **WRONG**
123E+2                    1.23E+4           123.00000000  DECIMAL(5,2)   **WRONG**
1.5E+3                     1.5E+3            15.00000000  DECIMAL(4,2)   **WRONG**
12.34E+5                 1.234E+6          1234.00000000  DECIMAL(7,3)   **WRONG**
999E+9                   9.99E+11           999.00000000  DECIMAL(12,9)  **WRONG**
100                           100           100.00000000  DECIMAL(3,0)   OK
1.23                         1.23             1.23000000  DECIMAL(3,2)   OK
1E+18                       1E+18             1.00000000  DECIMAL(19,18) **WRONG**
1E+19                       1E+19             0.10000000  DECIMAL(20,19) **WRONG**
1E+20                       1E+20            -0.08446744  DECIMAL(21,20) **WRONG**   <-- sign flip
1E+21                       1E+21             0.00776628  DECIMAL(22,21) **WRONG**

Expected: every row round-trips, i.e. 100, 12300, 1500, 1234000, 9.99E+11, …

Note the bound type column — the inferred type is wrong, not just the value. Decimal('1E+2') binds as DECIMAL(3,2), which is what gives it away.

What is and isn't affected

path	result for Decimal('1E+2')
execute("… VALUES (?)", [d])	1.00 ✗
executemany	1.00 ✗
bare SELECT ?	binds as DECIMAL(3,2) ✗
pandas object-dtype replacement scan	1.00 ✗
PyArrow decimal128	100.00000000 ✓
SQL CAST('1E+2' AS DECIMAL(28,8))	100.00000000 ✓

So the fault is specific to the Python value converter; Arrow ingestion and the SQL cast are both correct.

Root cause

PyDecimal::SetExponent (src/native/python_objects.cpp) routes exponent >= 0 to EXPONENT_POWER. PyDecimal::ToDuckValue then does:

case PyDecimalExponentType::EXPONENT_POWER: {
    uint8_t scale = exponent_value;   // positive exponent used as the SCALE
    width += scale;
    return PyDecimalCastSwitch<PyDecimalPowerConverter>(*this, width, scale);
}

and PyDecimalPowerConverter::Operation (python_objects.hpp) multiplies the mantissa by 10^scale before returning Value::DECIMAL(value, width, scale) — which interprets the integer as divided by 10^scale. The two cancel exactly, so the stored value is the mantissa.

For a positive exponent the scale should be 0 — such a value has no fractional digits. exponent_value genuinely is the scale in the EXPONENT_SCALE branch (where it is the absolute value of a negative exponent), and that is where the confusion appears to originate. The enum comment is already inconsistent with the code:

EXPONENT_POWER,    //! How many zeros behind the decimal point

Exponent 0 takes the same branch, but scale == 0 makes the double-application a no-op — which is why every ordinarily-written integer is unaffected and this has gone unnoticed.

Two further defects in the same converter, visible at large exponents:

int64_t multiplier = NumericHelper::POWERS_OF_TEN[MinValue<uint8_t>(scale, NumericHelper::CACHED_POWERS_OF_TEN - 1)];
for (auto power = scale; power > NumericHelper::CACHED_POWERS_OF_TEN; power--) { multiplier *= 10; }

With CACHED_POWERS_OF_TEN = 19 (table holds 10^0..10^18, cast_helpers.hpp):

1. Off-by-one: at scale == 19 the loop condition power > 19 is already false, so the multiplier silently stays 10^18 → the 1E+19 → 0.1 row above.
2. multiplier is int64_t while value may be hugeint. At scale >= 20, 10^scale overflows int64 — 10^19 mod 2^64 == -8446744073709551616 — producing the 1E+20 → -0.08446744 sign flip.

1E+38 takes the width > MAX_WIDTH_DECIMAL escape to CastToDouble and then fails loudly, so only the moderate range is silent.

I'd suggest one fix rather than three: rewriting EXPONENT_POWER to fold the exponent into the digits and emit scale 0 removes all three.

Version matrix — not a regression

Identical behaviour on every version I tested, via uv run --no-project --with duckdb==X:

version	1E+2	123E+2	1.5E+3	1e20
1.5.5	1	123	15	-0.08446744
1.5.2	1	123	15	-0.08446744
1.4.0	1	123	15	-0.08446744
1.2.0	1	123	15	0E-8
0.10.0	1	123	15	0E-8

The mantissa bug is invariant back to 0.10.0; only the overflow flavour changed. The relevant code on main is unchanged apart from the pybind11→nanobind rename.

Why this one bites

The failure is silent and the magnitude error is large — a value wrong by 100× or 1000×, or with a flipped sign, stored into a DECIMAL column with no error raised. DECIMAL is the type people reach for precisely when they cannot tolerate that.

We found it in a financial application, where an operator entering a quantity as 1e3 would have had 1000 sent downstream while 1 was recorded. Our data turned out clean (nobody had used scientific notation yet), but the failure mode is not one an application can detect after the fact without an independent record to reconcile against.

Workaround, for anyone who lands here

Normalise the exponent before binding. Note Decimal(str(d)) does not help — it preserves the exponent:

def bind_decimal(d: Decimal) -> Decimal:
    sign, digits, exponent = d.as_tuple()
    if not isinstance(exponent, int) or exponent <= 0:
        return d
    return Decimal((sign, (*digits, *((0,) * exponent)), 0))

The digit-tuple rewrite is exact and context-free. d + Decimal(0) and d.quantize(Decimal(1)) also clear the exponent but round under the active decimal context. Routing through PyArrow or binding str(d) and letting SQL cast both work too.

Environment

• duckdb 1.5.5 (also 1.5.2, 1.4.0, 1.2.0, 0.10.0)
• Python 3.11.14
• Linux x86_64