Skip to content

Don't silently convert NUMERIC NaN and Infinity to zero - #1070

Open
LiangrunDa wants to merge 1 commit into
duckdb:mainfrom
LiangrunDa:fix-numeric-special-values
Open

LiangrunDa wants to merge 1 commit into
duckdb:mainfrom
LiangrunDa:fix-numeric-special-values

Conversation

@LiangrunDa

@LiangrunDa LiangrunDa commented Aug 18, 2026

Copy link
Copy Markdown

Fixes #1069.

Postgres stores NaN and ±Infinity as "special" numerics, whose digit array is empty. FromNumeric() converted those to a NumericVar without checking NUMERIC_IS_SPECIAL(), and ConvertDecimal() turns anything with no digits into 0. Reading such a value from a table through the DuckDB execution engine therefore produced a silently wrong result:

CREATE TABLE special_numerics (id int, dec numeric(10,2), unbound numeric);
INSERT INTO special_numerics VALUES
    (1, 'NaN', 'NaN'), (2, 10.00, 10), (3, NULL, 'Infinity'), (4, NULL, '-Infinity');

SET duckdb.force_execution = false;
SELECT sum(dec), avg(dec), min(dec) FROM special_numerics;
--  sum | avg |  min                 <-- Postgres
--  NaN | NaN | 10.00

SET duckdb.force_execution = true;
SELECT sum(dec), avg(dec), min(dec) FROM special_numerics;
--   sum  | avg | min                <-- before this PR: all three wrong, no warning
--  10.00 |   5 | 0.00

The vendored pg_numeric_c.hpp header documents that this must not happen, right above NUMERIC_SIGN:

Note that we don't trouble to ensure that dscale and weight read as zero for an infinity; however, that doesn't matter since we never convert "special" numerics to NumericVar form.

What this changes

A DOUBLE can represent all three values, so the NUMERICDOUBLE conversion (used when duckdb.convert_unsupported_numeric_to_double is on) now maps them onto their IEEE 754 counterparts. Before, NaN, Infinity and -Infinity all came back as 0.

A DECIMAL is a fixed point integer and has no representation for them at all, so that path raises a conversion error instead of returning a wrong number. This makes a NaN read from a table behave like a NaN constant, which already fails this way today (#1039):

ERROR:  Conversion Error: Cannot convert a NaN or Infinity NUMERIC to a DuckDB
DECIMAL, because DECIMAL is a fixed point type that cannot represent those. Run
this query in Postgres instead by using 'SET duckdb.force_execution = false'.

I considered rejecting numeric(p,s) columns at planning time instead, so that such queries would transparently fall back to Postgres. That would be correct in all cases, but it costs DuckDB acceleration for every numeric column whether or not it ever holds a special value, which seemed far too high a price. Happy to reconsider if you disagree.

Testing

Adds numeric_special_values to the regression schedule, covering the DECIMAL error, the DOUBLE round-trip, rows without special values, and the float types. There was no NaN or Infinity coverage anywhere in test/regression/ or test/pycheck/ before this.

Verified on Postgres 14.24 and 18.6: the new test passes and the existing suite stays green (67/67).

Unrelated, but worth flagging since CI will show it: main currently does not build against the tip of REL_19_STABLE (19beta3). src/vendor/pg_ruleutils_19.c references Query.groupByAll and ForPortionOfExpr.range_name, which have since been renamed upstream. This reproduces on an unmodified checkout of main, so it is independent of this PR — let me know if you would like a separate issue or PR for it.

Postgres stores NaN and +/-Infinity as "special" numerics, whose digit
array is empty. FromNumeric() converted those to a NumericVar without
checking NUMERIC_IS_SPECIAL(), and ConvertDecimal() turns anything with
no digits into 0. Reading such a value from a table through the DuckDB
execution engine therefore produced a silently wrong result: sum(), avg(),
min() and max() over a NUMERIC column containing a NaN all disagreed with
Postgres, without a warning.

The vendored pg_numeric_c.hpp header documents that this must not happen:
"we never convert special numerics to NumericVar form".

A DOUBLE can represent all three values, so the NUMERIC-as-DOUBLE
conversion now maps them onto their IEEE 754 counterparts. A DECIMAL is a
fixed point integer and has no representation for them at all, so that
path raises a conversion error instead, the same way a NaN constant
already fails today (duckdb#1039).

Fixes duckdb#1069
@LiangrunDa
LiangrunDa force-pushed the fix-numeric-special-values branch from 298504f to dfe17e9 Compare August 18, 2026 05:43
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

NUMERIC NaN read from a Postgres table is silently converted to 0 when executed by DuckDB

1 participant