Key takeaway
Preserve the input values, numeric type and aggregation method. A small residual after cancellation can depend on how the arithmetic was performed.
Start with a disagreement you can reproduce
Two teams receive the same three numeric inputs and report different totals. One assumes a row disappeared; the other assumes the first team rounded badly. There is an earlier question: which numeric values and operations did each calculation actually use? Floating-point arithmetic can make grouping matter even when the input list is identical.
PostgreSQL describes real and double precision as inexact types, with some values stored as approximations. Python’s floating-point tutorial explains representation and accumulation effects and distinguishes plain additions from summation tools. These references support a technical reconciliation. They do not establish that any unexplained production discrepancy is harmless or within an acceptable tolerance.
Three invented adjustments, two groupings
The hypothetical fixture uses unitless values 10,000,000,000,000,000, 1 and −10,000,000,000,000,000. They are deliberately extreme to expose cancellation. In the checked Python binary floating-point calculation, adding the positive large value to 1 first loses that small increment in the intermediate result; adding the negative large value then yields 0. Cancelling the large pair first and adding 1 yields 1.
The exact integer arithmetic total is 1. This fixture does not describe a normal financial transaction, a measured sensor range or a claimed frequency of errors. It is a diagnostic that lets an owner isolate one failure mechanism before interpreting a real archive. The table names the actual grouping so “same inputs” does not conceal different operations.
| Synthetic calculation | Checked result | What the test establishes |
|---|---|---|
| (1e16 + 1.0) + (−1e16) | 0.0 | Plain binary additions lost the increment |
| (1e16 + (−1e16)) + 1.0 | 1.0 | A different grouping changed the residual |
| math.fsum([1e16, 1.0, −1e16]) | 1.0 | This summation tool retained this fixture’s residual |
| Decimal strings for the three integers | 1 | This exact-input decimal calculation agrees with integer arithmetic |
Do not turn one fixture into a universal fix
Python documents math.fsum as tracking lost digits to improve summation accuracy. Its tutorial also describes improvements in the built-in sum operation. A statement that every Python sum is merely a naive left-to-right addition would misdescribe the current documentation. Record the function and runtime actually used; do not infer the method from a total column’s name.
A decimal type can be appropriate where the application requires exact decimal values. PostgreSQL recommends numeric where exact storage and calculation are required, while also documenting precision and scale choices. That does not restore precision already lost upstream. Converting a rounded binary value to a decimal after extraction cannot prove that the original decimal input survived.
Specify the required arithmetic for the real use, including scale, rounding stage, units and acceptable comparison method. If a decision needs a tolerance, its owner must justify that tolerance from the quantity and use. The toy discrepancy is not permission to select an arbitrary percentage or to dismiss a missing row that happens to have a small value.
Retain a calculation fingerprint
The reconciliation record contains the source value representation, serialization, receiving numeric type, relevant precision/scale, query or function, runtime, grouping or partition method where known, and the unformatted result. Display rounding belongs in a separate field. A screenshot showing the same two decimal places cannot establish that the underlying totals match.
Run a bounded synthetic suite with cancellation, small increments beside large magnitudes, repeated fractional inputs and empty or missing values under the declared policy. Check identity and row coverage separately. When a system parallelizes aggregation, obtain its documented guarantees rather than promising the grouping will remain identical across plans. Preserve an unresolved method when the engine cannot expose it.
Choose the next investigation from the evidence
For this fixture, the plain-addition residual is explained by the stated arithmetic, while the exact-input decimal and fsum runs return 1. For a real package, reproduce the discrepancy within the approved environment before assigning a cause. If the values differ before aggregation, investigate representation or extraction; if they agree and operations differ, investigate calculation semantics.
A receiver can initially request this calculation fingerprint and the synthetic test without obtaining operational rows. The resulting decision describes which total can support the intended comparison and what remains held. It does not certify the underlying measurements, authorize disclosure or establish commercial value.