NUMERIC read as DOUBLE doesn't match Postgres's float8 cast
Maintainers usually reply within 1 day
A pull request for this has already been merged.
- #610 by @ItsM0rty — merged
Assessment
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Newbie friendliness
- 70/100
Research direction
The issue identifies DecimalConversionDouble in the binary numeric decoder; start by finding that implementation and the tests for decoding PostgreSQL numeric values as DOUBLE. Compare binary protocol results with the text protocol and PostgreSQL's numeric::float8 cast for the reported values. Done means the binary path returns the same nearest double as PostgreSQL for these cases.
Written by the indexing model from the issue text.
Description
On main (8d201d4), numeric values that the extension reads as DOUBLE (unconstrained numeric, or a numeric too wide for DuckDB's DECIMAL) sometimes differ from Postgres's own numeric::float8 cast:
memory D ATTACH '' as pg (TYPE postgres);
memory D SELECT n AS duckdb, f AS postgres, n = f AS same
FROM postgres_query('pg', 'SELECT n, n::float8 AS f FROM (
SELECT 8629433::numeric / 1000
UNION ALL SELECT avg(x) FROM (VALUES (275.38), (232.58), (785.33)) v(x)
UNION ALL SELECT 1e32
) t(n)');
┌───────────────────────┬───────────────────┬─────────┐
│ duckdb │ postgres │ same │
│ double │ double │ boolean │
├───────────────────────┼───────────────────┼─────────┤
│ 8629.432999999999 │ 8629.433 │ false │
│ 431.09666666666664 │ 431.0966666666667 │ false │
│ 9.999999999999999e+31 │ 1e+32 │ false │
└───────────────────────┴───────────────────┴─────────┘
These aren't the nearest double to the exact value, which is what Postgres's cast (through strtod) gives.
With SET pg_use_text_protocol = true the same query returns the correct values, so the problem is in decoding the binary numeric format. The cause is DecimalConversionDouble, which builds the double with floating-point arithmetic that rounds at each step.
- Dominant language
- C++
- Stars
- 373
- Forks
- 112
- Avg merge
- 9h 48m
- Merged PRs (30d)
- 28
Getting set up
This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from duckdb/duckdb-postgres
-
Difficulty 3/5 1-2 days Newbie friendliness 62/100
duckdb/duckdb-postgres#618 ·
Maintainers usually reply within 1 day
-
Difficulty 4/5 3-5 days Newbie friendliness 25/100
duckdb/duckdb-postgres#594 · 4 comments ·
Maintainers usually reply within 1 day
-
Difficulty 4/5 3-5 days Newbie friendliness 48/100
duckdb/duckdb-postgres#575 ·
Maintainers usually reply within 1 day
-
Difficulty 3/5 1-2 days Newbie friendliness 74/100
duckdb/duckdb-postgres#564 · 1 comment ·
Maintainers usually reply within 1 day
-
Difficulty 4/5 3-5 days Newbie friendliness 55/100
duckdb/duckdb-postgres#491 · 1 comment ·
Maintainers usually reply within 1 day
All issues in duckdb/duckdb-postgres
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 70/100
FriendsofECCE/ECCE#336 · 1 comment ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 66/100
bitcoin-core/gui-qml#1030 ·
Maintainers usually reply within 3 days
-
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 85/100
tesseract-robotics/tesseract#1384 ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
NVIDIA/nccl-extensions#26 ·