pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19810: to_char(): float V-format overflow yields 'Infinity' or NaN; format after decimal point dropped
2+ messages / 2 participants
[nested] [flat]
* BUG #19810: to_char(): float V-format overflow yields 'Infinity' or NaN; format after decimal point dropped
@ 2026-10-29 06:32 PG Bug reporting form <noreply@postgresql.org>
2026-10-30 21:09 ` Re: BUG #19810: to_char(): float V-format overflow yields 'Infinity' or NaN; format after decimal point dropped Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-10-29 06:32 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com
The following bug has been logged on the website:
Bug reference: 19810
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 18.6
Operating system: Linux x86_64
Description:
Two independent issues in to_char() formatting.
Issue 1: float4/float8 V (decimal shift) format has no overflow check.
The V template multiplies the value by 10^n, where n is the number of digits
after V. For the float types the scale factor is computed as a double; when
10^n itself overflows, the multiplication silently produces Infinity or NaN,
and to_char() returns that as literal text.
- A perfectly representable result is destroyed:
to_char(1e-300::float8, 'FM9V' || repeat('9', 309)) -> "Infinity"
The true result is 1e-300 * 10^309 = 1e9; the same format with numeric
returns 1000000000.
- Zero becomes NaN:
to_char(0::float8, 'FM9V' || repeat('9', 309)) -> "NaN" (0 * inf)
float4 already does this at n = 39.
- Ordinary overflow returns literal text instead of "#" or an error:
to_char(1e308::float8, 'FM9V9999999') -> "Infinity"
while the equivalent SQL arithmetic 1e308::float8 * 1e7 raises
ERROR: value out of range: overflow
and the sibling int8 path raises "bigint out of range".
One fewer 9, to_char(1e308::float8, 'FM9V999999'), returns "#######" -
the documented overflow marker - so two adjacent format widths disagree on
how overflow is represented.
- An already-infinite input is also inconsistent within the same function:
to_char('Infinity'::float8, 'FM9999') -> "####"
to_char('Infinity'::float8, 'FM9999999999') -> "Infinity"
The sibling branches are guarded (int4/int8 raise errors, numeric is exact);
only the float4/float8 paths lack the check.
Issue 2: template patterns after the decimal point are silently dropped,
losing the sign.
SG and PL are documented template patterns and work before or after the
digit positions. Placed after the decimal point they are silently dropped -
together with any literal text that follows - and the sign is lost:
to_char('-100'::numeric, 'FM999') -> '-100' (default
minus, correct)
to_char('-100'::numeric, 'FM999.SG') -> '100' (sign lost)
to_char('-100'::numeric, 'FM999.SG "items"') -> '100' (sign and
literal text lost)
to_char('123.4'::numeric, 'FM999.PL') -> '123' ('+' and
even the fraction dropped)
to_char('-100'::numeric, '999.SG') -> '100'
Controls - the same patterns work correctly in adjacent positions:
to_char('-100'::numeric, 'FMSG999') -> '-100'
to_char('123.4'::numeric, 'FM999PL') -> '123.4+'
to_char('-100'::numeric, 'FM999MI') -> '100-'
A negative number formatted with a format string that contains a sign
pattern (SG) must not render as an unsigned positive number.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19810: to_char(): float V-format overflow yields 'Infinity' or NaN; format after decimal point dropped
2026-10-29 06:32 BUG #19810: to_char(): float V-format overflow yields 'Infinity' or NaN; format after decimal point dropped PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-30 21:09 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Andrey Rachitskiy @ 2026-10-30 21:09 UTC (permalink / raw)
To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org
пт, 30 окт. 2026 г. в 12:13, PG Bug reporting form <noreply@postgresql.org>:
> Issue 1: float4/float8 V (decimal shift) format has no overflow check.
>
> The V template multiplies the value by 10^n, where n is the number of
> digits
> after V. For the float types the scale factor is computed as a double; when
> 10^n itself overflows, the multiplication silently produces Infinity or
> NaN,
> and to_char() returns that as literal text.
>
> - A perfectly representable result is destroyed:
> to_char(1e-300::float8, 'FM9V' || repeat('9', 309)) -> "Infinity"
> The true result is 1e-300 * 10^309 = 1e9; the same format with numeric
> returns 1000000000.
> - Zero becomes NaN:
> to_char(0::float8, 'FM9V' || repeat('9', 309)) -> "NaN" (0 * inf)
> float4 already does this at n = 39.
> - Ordinary overflow returns literal text instead of "#" or an error:
> to_char(1e308::float8, 'FM9V9999999') -> "Infinity"
> while the equivalent SQL arithmetic 1e308::float8 * 1e7 raises
> ERROR: value out of range: overflow
> and the sibling int8 path raises "bigint out of range".
> One fewer 9, to_char(1e308::float8, 'FM9V999999'), returns "#######" -
> the documented overflow marker - so two adjacent format widths disagree on
> how overflow is represented.
> - An already-infinite input is also inconsistent within the same function:
> to_char('Infinity'::float8, 'FM9999') -> "####"
> to_char('Infinity'::float8, 'FM9999999999') -> "Infinity"
>
> The sibling branches are guarded (int4/int8 raise errors, numeric is
> exact);
> only the float4/float8 paths lack the check.
>
>
In the attached patch, scaling is done in numeric instead, using
numeric_power() and numeric_mul().
If the number doesn't fit, the error is reported properly - float8 via
float_overflow_error(), float4 via numeric_float4().
> Issue 2: template patterns after the decimal point are silently dropped,
> losing the sign.
>
>
Tom fixed it on 20/10/26 in master (776df8a6d7c) and in REL_19_STABLE
(61bb2214892) - BUG 19771
--
Regards,
Rachitskiy Andrey
Attachments:
0001-Fix-float-to_char-V-scaling.patch (text/x-patch, 5.4K) download
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Sat, 31 Oct 2026 01:40:00 +0500
Subject: [PATCH] Fix float to_char V scaling overflow
float4_to_char() and float8_to_char() scaled a V format with pow().
The factor becomes Inf past FLT_MAX_10_EXP or DBL_MAX_10_EXP, and the
multiply turns a finite value into Inf and zero into NaN.
Scale with numeric_power() and numeric_mul(). float8 reports a
product that does not fit via float_overflow_error(). float4 reports
it via numeric_float4().
BUG #19810
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reported-by: chunling qin <303677365@qq.com>
Discussion: https://www.postgresql.org/message-id/19810-e14756b84da7afc8@postgresql.org
---
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 8b5f99e6a82..78aacd349cb 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -77,6 +77,7 @@
#include "utils/builtins.h"
#include "utils/date.h"
#include "utils/datetime.h"
+#include "utils/float.h"
#include "utils/formatting.h"
#include "utils/memutils.h"
#include "utils/numeric.h"
@@ -6679,6 +6680,28 @@ int8_to_char(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P((text *) result.data);
}
+/*
+ * 10^multi is computed in numeric. A float power of ten is Inf past
+ * FLT_MAX_10_EXP or DBL_MAX_10_EXP.
+ */
+static Numeric
+scale_numeric_v(Numeric nval, int multi, int32 round_digits)
+{
+ Numeric a = int64_to_numeric(10);
+ Numeric b = int64_to_numeric(multi);
+ Numeric x;
+
+ x = DatumGetNumeric(DirectFunctionCall2(numeric_power,
+ NumericGetDatum(a),
+ NumericGetDatum(b)));
+ nval = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
+ NumericGetDatum(nval),
+ NumericGetDatum(x)));
+ return DatumGetNumeric(DirectFunctionCall2(numeric_round,
+ NumericGetDatum(nval),
+ Int32GetDatum(round_digits)));
+}
+
/*
* FLOAT4 to_char()
*/
@@ -6743,9 +6766,16 @@ float4_to_char(PG_FUNCTION_ARGS)
if (IS_MULTI(&Num))
{
- float multi = pow((double) 10, (double) Num.multi);
+ if (!isnan(val) && !isinf(val))
+ {
+ Numeric nval;
- val = value * multi;
+ nval = DatumGetNumeric(DirectFunctionCall1(float4_numeric,
+ Float4GetDatum(val)));
+ nval = scale_numeric_v(nval, Num.multi, Num.post);
+ val = DatumGetFloat4(DirectFunctionCall1(numeric_float4,
+ NumericGetDatum(nval)));
+ }
Num.pre += Num.multi;
}
@@ -6855,9 +6885,18 @@ float8_to_char(PG_FUNCTION_ARGS)
if (IS_MULTI(&Num))
{
- double multi = pow((double) 10, (double) Num.multi);
-
- val = value * multi;
+ if (!isnan(val) && !isinf(val))
+ {
+ Numeric nval;
+
+ nval = DatumGetNumeric(DirectFunctionCall1(float8_numeric,
+ Float8GetDatum(val)));
+ nval = scale_numeric_v(nval, Num.multi, Num.post);
+ val = DatumGetFloat8(DirectFunctionCall1(numeric_float8_no_overflow,
+ NumericGetDatum(nval)));
+ if (isinf(val) || isnan(val))
+ float_overflow_error();
+ }
Num.pre += Num.multi;
}
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index 6269d5bb397..74ed7431b2f 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -2208,6 +2208,45 @@ SELECT to_char(1234.56::float8, '99999V99');
123456
(1 row)
+-- V on float4/float8 keeps a representable product and errors on overflow
+SELECT to_char(1e-300::float8, 'FM9V' || repeat('9', 309));
+ to_char
+------------
+ 1000000000
+(1 row)
+
+SELECT to_char((-1e-300)::float8, 'FM9V' || repeat('9', 309));
+ to_char
+-------------
+ -1000000000
+(1 row)
+
+SELECT to_char(0::float8, 'FM9V' || repeat('9', 309));
+ to_char
+---------
+ 0
+(1 row)
+
+SELECT to_char(1e308::float8, 'FM9V9999999');
+ERROR: value out of range: overflow
+SELECT to_char((-1e308)::float8, 'FM9V9999999');
+ERROR: value out of range: overflow
+SELECT to_char(1e308::float8, 'FM9V999999');
+ERROR: value out of range: overflow
+SELECT to_char(0::float4, 'FM9V' || repeat('9', 39));
+ to_char
+---------
+ 0
+(1 row)
+
+SELECT to_char(1e-30::float4, 'FM9V' || repeat('9', 39));
+ to_char
+------------
+ 1000000000
+(1 row)
+
+SELECT to_char(1::float4, 'FM9V' || repeat('9', 39));
+ERROR: "1000000000000000000000000000000000000000" is out of range for type real
-- Check parsing of literal text in a format string
SELECT to_char('100'::numeric, 'foo999');
to_char
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index e63b21e4b6b..12168047316 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -1052,6 +1052,17 @@ SELECT to_char(1234.56::numeric, '99999V99');
SELECT to_char(1234.56::float4, '99999V99');
SELECT to_char(1234.56::float8, '99999V99');
+-- V on float4/float8 keeps a representable product and errors on overflow
+SELECT to_char(1e-300::float8, 'FM9V' || repeat('9', 309));
+SELECT to_char((-1e-300)::float8, 'FM9V' || repeat('9', 309));
+SELECT to_char(0::float8, 'FM9V' || repeat('9', 309));
+SELECT to_char(1e308::float8, 'FM9V9999999');
+SELECT to_char((-1e308)::float8, 'FM9V9999999');
+SELECT to_char(1e308::float8, 'FM9V999999');
+SELECT to_char(0::float4, 'FM9V' || repeat('9', 39));
+SELECT to_char(1e-30::float4, 'FM9V' || repeat('9', 39));
+SELECT to_char(1::float4, 'FM9V' || repeat('9', 39));
+
-- Check parsing of literal text in a format string
SELECT to_char('100'::numeric, 'foo999');
SELECT to_char('100'::numeric, 'f\oo999');
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-10-30 21:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-29 06:32 BUG #19810: to_char(): float V-format overflow yields 'Infinity' or NaN; format after decimal point dropped PG Bug reporting form <noreply@postgresql.org>
2026-10-30 21:09 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox