vlaurent1 opened a new issue, #4507: URL: https://github.com/apache/arrow-adbc/issues/4507
### 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: ```text Input: 5000000000.000001 Stored: 5000000100.000000 Expected: 5000000000.000001 ``` A nearby control value round-trips correctly: ```text 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](https://github.com/user-attachments/files/29986120/repro_adbc_numeric_bug_ready.py) ## Minimal reproduction Install the dependencies: ```bash 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: ```bash export POSTGRES_URI="postgresql://postgres:postgres@localhost:5432/postgres" ``` On PowerShell: ```powershell $env:POSTGRES_URI = "postgresql://postgres:postgres@localhost:5432/postgres" ``` Run the attached script: ```bash 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 ```text 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: ```text 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. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
