Floating-point precision issue in UPDATE statements for double precision columns #226
Description
Problem Description
A database conflict occurs when updating columns of type double precision. This issue originates from the geodiff library, which generates SQL UPDATE statements that use full floating-point precision in the WHERE clause, causing the update to fail.
When a double value is changed (e.g., from 32.8 to 33.8), the mergin-db-sync tool, which uses geodiff, produces a conflict.
Error Log from mergin-db-sync:
mergin-db-sync | Warn: CONFLICT: update_nothing:
mergin-db-sync | {
mergin-db-sync | "changes": [
mergin-db-sync | {
mergin-db-sync | "column": 11,
mergin-db-sync | "new": 33.8,
mergin-db-sync | "old": 32.8
mergin-db-sync | },
mergin-db-sync | {
mergin-db-sync | "column": 76,
mergin-db-sync | "old": 4214
mergin-db-sync | }
mergin-db-sync | ],
mergin-db-sync | "table": "stabla",
mergin-db-sync | "type": "update"
mergin-db-sync | }
mergin-db-sync | Warn: Wrong number of affected rows! Expected 1, got: 0
mergin-db-sync | SQL: UPDATE "zk_base"."stabla" SET "visina" = 33.799999999999997 WHERE "visina" = 32.799999999999997 AND "id_stablo" = 4214
Root Cause Analysis
The issue is caused by the WHERE clause in the generated SQL statement. Comparing floating-point numbers using exact equality (=) is unreliable because the stored binary representation of a number like 32.8 might not be exactly 32.799999999999997.
The UPDATE statement correctly includes the table's primary key ("id_stablo" = 4214) to identify the row. However, the additional condition on the value being updated ("visina" = 32.799999999999997) is intended as a safeguard but fails for double types due to precision mismatches.
The code responsible for this is located in the editTable function within the database drivers.
We are using latest docker db-sync image, and have done initial sync from pg. And that is perhaps another problem.
Pg - visina numeric(10, 2)
GPKG - visina REAL