[python][postgresql] adbc_ingest silently corrupts NUMERIC when the integer part is a non-zero multiple of 10^9

[vlaurent1]

What happened?

adbc_driver_postgresql.dbapi.Cursor.adbc_ingest() silently stores incorrect
PostgreSQL NUMERIC values when both of the following conditions are true:

1. The absolute integer part is a non-zero exact multiple of 10^9.
2. The fractional part is non-zero.

A minimal affected case using NUMERIC(18,6) is:

Input:    5000000000.000001
Stored:   5000000100.000000
Expected: 5000000000.000001

A nearby control value round-trips correctly:

Input:    4999999999.000001
Stored:   4999999999.000001

The write and commit both complete successfully without any exception or
warning.

The stored result is verified with a separate psycopg2 connection using
SELECT val::text. Therefore, this is not merely an ADBC result-decoding
problem: the incorrect value is stored in PostgreSQL.

Stack Trace

No response

How can we reproduce the bug?

The Arrow decimal value should be stored exactly in the PostgreSQL NUMERIC
column.

If the value cannot be serialized correctly, the driver should reject the
write. It should not report success while storing a different value.

This is silent data corruption:

• adbc_ingest() succeeds;
• commit() succeeds;
• PostgreSQL accepts the write;
• no warning or exception is emitted;
• a subsequent independent query returns a different numeric value.

This can affect financial, accounting, scientific, measurement, and other
pipelines that depend on exact decimal storage.

repro_adbc_numeric_bug_ready.py

Minimal reproduction

Install the dependencies:

python -m pip install \
  "adbc-driver-postgresql==1.11.0" \
  "pyarrow==24.0.0" \
  "psycopg2-binary>=2.9,<3"

Start PostgreSQL 16, then set the connection URI:

export POSTGRES_URI="postgresql://postgres:postgres@localhost:5432/postgres"

On PowerShell:

$env:POSTGRES_URI = "postgresql://postgres:postgres@localhost:5432/postgres"

Run the attached script:

python repro_adbc_numeric_bug_ready.py

The script deliberately uses a regular table instead of a PostgreSQL temporary
table. The ADBC connection and the independent psycopg2 verification
connection are separate database sessions, while temporary tables are
session-local.

It exits with:

• 0 if every value round-trips correctly;
• 1 if corruption is detected;
• 2 if the test cannot complete because of an environment or runtime error.

Actual output

5000000000.000001            -> 5000000100.000000            [CORRUPTED]
1000000000.000001            -> 1000000100.000000            [CORRUPTED]
5000000000.100000            -> 5010000000.000000            [CORRUPTED]
4999999999.000001            -> 4999999999.000001            [OK]
5000000001.000001            -> 5000000001.000001            [OK]
5000000000.0000000001        -> 5000000000.0000010000        [CORRUPTED]

No stack trace is produced because no exception is raised.

Additional observed cases

Unless otherwise noted, these use NUMERIC(18,6).

Affected values

Input	Stored value	Observation
5000000000.000001	5000000100.000000	the 1e-6 fraction is displaced into the integer part
5000000000.000010	5000001000.000000	the 1e-5 fraction is displaced into the integer part
5000000000.100000	5010000000.000000	the 1e-1 fraction is displaced into the integer part
1000000000.000001	1000000100.000000	triggers at exactly 10^9
2000000000.000001	2000000100.000000	triggers at another multiple of 10^9
10000000000.000001	10000000100.000000	triggers at 10 × 10^9
5000000000.0000000001 using NUMERIC(28,10)	5000000000.0000010000	the same pattern is present at a different scale

Values that round-trip correctly

Input	Stored value	Observation
4999999999.000001	4999999999.000001	integer part is not a multiple of 10^9
5000000001.000001	5000000001.000001	integer part is not a multiple of 10^9
9999999999.999999	9999999999.999999	integer part is not a multiple of 10^9
1234567890.123456	1234567890.123456	integer part is not a multiple of 10^9
5000000000.000000	5000000000.000000	fractional part is zero
0.000001	0.000001	integer part is zero

The output pattern is deterministic and direction-preserving: the fractional
digits appear to move upward by one base-10000 digit group.

Relationship to #3485 / #3787

This appears related to #3485, which also reported altered PostgreSQL
NUMERIC values during ingestion.

However, this report has a distinct and repeatable trigger:

	#3485	This report
Trigger	decimal conversion/scale cases	non-zero integer part is an exact multiple of 10^9 and the fraction is non-zero
Corruption shape	previously reported decimal alteration	fractional groups move upward into the integer part
Current observation	reported as resolved	still reproducible in 1.11.0

The two problems may share the same general serialization area while being
different failure modes.

Suspected implementation area — hypothesis only

PostgreSQL binary NUMERIC values use metadata plus an array of base-10000
digit groups.

The observed displacement is consistent with a digit-group alignment or
weight calculation problem in the PostgreSQL binary COPY serializer. The
fractional digits appear to be serialized one base-10000 group too high.

This is only a hypothesis based on the output pattern. The exact source-level
cause has not been confirmed.

Suggested regression tests

At minimum, a regression test should include this affected/control pair:

5000000000.000001 -> must remain 5000000000.000001
4999999999.000001 -> must remain 4999999999.000001

Additional useful coverage would include:

• positive and negative multiples of 10^9;
• values immediately below and above the boundary;
• zero and non-zero fractional parts;
• scales 1, 4, 5, 6, and 10;
• decimal128;
• decimal256, where supported;
• multiple PostgreSQL versions;
• Windows and Linux.

Environment/Setup

The issue was reproduced with Python packages installed using pip.

Component	Tested value
adbc-driver-postgresql	1.11.0
adbc-driver-manager	1.11.0
pyarrow	24.0.0
psycopg2-binary	2.9.x
Python	3.11
PostgreSQL	16
Platforms	Windows amd64; Linux aarch64

The following adbc-driver-postgresql versions were separately tested and all
showed the corruption:

Version	Result
1.2.0	corrupted
1.5.0	corrupted
1.8.0	corrupted
1.10.0	corrupted
1.11.0	corrupted

This only claims coverage for the versions listed above; intermediate releases
were not tested.

[lidavidm]

I believe @Mandukhai-Alimaa already fixed this on HEAD

[lidavidm]

See #3485.

You(r agent) can test the nightly wheel to confirm?

[vlaurent1]

See #3485.

You(r agent) can test the nightly wheel to confirm?

Tested nightly v1.12.0 on Windows — bug still present

Tested adbc-driver-postgresql v1.12.0 (nightly from Gemfury) on Windows against PostgreSQL 18.4 with decimal128(18, 6) values. The bug is still there — same corruption pattern as the stable release.

Results

5000000000.000001  →  5000000100.000000  [CORRUPTED]
1000000000.000001  →  1000000100.000000  [CORRUPTED]
5000000000.100000  →  5010000000.0       [CORRUPTED]
4999999999.000001  →  4999999999.000001  [OK]
5000000001.000001  →  5000000001.000001  [OK]
5000000000.000000  →  5000000000         [OK — display only]

4 of 6 cases corrupted. Control values (integer part not a multiple of 10^9) round-trip correctly.

Why it's still broken

PR #3787 fixed the fast path (WriteDecimal128Fast) — it uses 128-bit integer arithmetic and produces correct results. But it's only enabled when __SIZEOF_INT128__ is defined (GCC/Clang). Windows/MSVC falls back to the slow path (WriteDecimal128Slow), which builds the PostgreSQL binary format through string parsing and digit grouping.

The bug is in GroupIntegerDigits() at c/driver/postgresql/copy/writer.h:524-526:

// Skip trailing zeros
if (val != 0 || !digits.empty()) {
    digits.insert(digits.begin(), val);
}

It strips trailing zero base-10000 digit groups from the integer part. For 5000000000, the correct digit groups are [50, 0, 0] (3 groups), but it returns [50] (1 group). This shifts the weight by 2 positions, moving fractional digits into the wrong place — so 5000000000.000001 becomes 5000000100.000000.

This means:

• Linux/macOS + DECIMAL128: should pass (fast path)
• Windows + DECIMAL128: fails (slow path, confirmed above)
• All platforms + DECIMAL256: fails (always uses slow path)

Suggested Fix

Remove the trailing-zero skip in GroupIntegerDigits. All base-10000 digit groups must be preserved — zero groups are needed to compute the correct weight for the fractional part. This is a small change and fixes the slow path for everyone.

[lidavidm]

#3787 has nothing to do with the fast path, that's a different PR.

[lidavidm]

That said, reading around the AI junk, it seems there's a potential bug in the code still. @Mandukhai-Alimaa can you confirm? (Probably easiest to disable the new int128 fast path on your local machine instead of hunting for Windows.) Also we should make sure tests are covering both paths on Linux/macOS instead of only testing the fast path.

[eyevz]

Hi @lidavidm.

#3485 is a different problem, already fixed in recent release(s).

The issue here still exists, and is a bit more general than reported in OP. More AI output for your bleeding eyes (with apologies - work done in a strict VPC):

Furthermore:

• Seems independent of particular Arrow decimal128 precision and scale.
• Seems independent of particular NUMERIC column precision and scale.

[lidavidm]

@eyevz again: is that testing the latest HEAD?

[eyevz]

@lidavidm I just came to clarify the scope! But now I can say this:

On macOS 15.7.4, with nanoarrow 0.8.0, against postgres 16, at 9be0298 it seems this bug is fixed: all of the values in my post above round trip correctly.

Looks like this was fixed as a side effect of #4498 and would still exist on some platforms and for decimal256.

[vlaurent1]

Thanks for the quick responses folks!

I re-ran on Windows with the nightly v1.12.0 (main 9be0298) against 21 DECIMAL128 + 10 DECIMAL256 cases.
Bug still on Windows (MSVC hits the slow path). All values where integer part >= 10^9 with non-zero fraction are corrupted — 12/21 DECIMAL128 and 6/10 DECIMAL256 on my Windows test. DECIMAL256 is also affected on every platform since no fast path exists for it.
The remaining bug is in GroupIntegerDigits() at writer.h:703-705 — the "Skip trailing zeros" guard drops zero base-10000 digit groups, shifting the weight and displacing fractional digits. Fix is probably to remove that guard and always insert every digit group.