In September, while researching the to_char() buffer overflow bugs fixed
in 9.4.1 (commit 0150ab567bcf5e5913e2b62a1678f84cc272441f), I found an
inconsistency in how to_char() does zero-padding for float4/8 values.
Now that 9.4.1 is released and I am home for a while, I am ready to
address this.
For example, to_char(int4) properly pads with trailing zeros, e.g.
SELECT to_char(int4 '1999999999', '9999999999999999D' || repeat('9',
1000));
------
1999999999.000000000000000000000000000000...
Numeric does the same thing:
SELECT to_char(numeric '99999999999', '9999999999999999D' ||
repeat('9', 1000));
------
99999999999.00000000000000000000000000000...
However, float4/8 do not supply the requested zero padding:
SELECT to_char(float4 '99999999999', '9999999999999999D' || repeat('9',
1000));
------
99999997952
and
SELECT to_char(float8 '99999999999', '9999999999999999D' || repeat('9',
1000));
------
99999999999.0000
float4/8 are padding to the internal precision, while int4/numeric are
padding based on the requested precision. This is inconsistent.
The first attached patch fixes this, and also zeros the "junk" digits
which exceed the precision of the underlying type:
SELECT to_char(float4 '99999999999', '9999999999999999D' || repeat('9',
1000));
------
99999900000.00000000000000000000000000000...
SELECT to_char(float8 '99999999999', '9999999999999999D' || repeat('9',
1000));
------
99999999999.0000000000000000000000000000....
This "junk" digit zeroing matches the Oracle behavior:
SELECT to_char(1.123456789123456789123456789d,
'9.9999999999999999999999999999999999999') as x from dual;
------
1.1234567891234568000000000000000000000
Our output with the patch would be:
SELECT to_char(float8 '1.123456789123456789123456789',
'9.9999999999999999999999999999999999999');
------
1.1234567891234500000000000000000000000
which is pretty close.
The second patch adds regression tests for these.
I would like to apply this for 9.5 while I remember what I was doing,
but I guess now that I have written this email, I will be able to keep
it for 9.6 if people prefer.
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 40a353f..25b247e
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
***************
*** 113,125 ****
#define DCH_MAX_ITEM_SIZ 12 /* max localized day name */
#define NUM_MAX_ITEM_SIZ 8 /* roman number (RN has 15 chars) */
- /* ----------
- * More is in float.c
- * ----------
- */
- #define MAXFLOATWIDTH 60
- #define MAXDOUBLEWIDTH 500
-
/* ----------
* Format parser structs
--- 113,118 ----
*************** int4_to_char(PG_FUNCTION_ARGS)
*** 5214,5221 ****
/* we can do it easily because float8 won't lose any precision */
float8 val = (float8) value;
! orgnum = (char *) palloc(MAXDOUBLEWIDTH + 1);
! snprintf(orgnum, MAXDOUBLEWIDTH + 1, "%+.*e", Num.post, val);
/*
* Swap a leading positive sign for a space.
--- 5207,5213 ----
/* we can do it easily because float8 won't lose any precision */
float8 val = (float8) value;
! orgnum = psprintf("%+.*e", Num.post, val);
/*
* Swap a leading positive sign for a space.
*************** float4_to_char(PG_FUNCTION_ARGS)
*** 5414,5420 ****
numstr = orgnum = int_to_roman((int) rint(value));
else if (IS_EEEE(&Num))
{
- numstr = orgnum = (char *) palloc(MAXDOUBLEWIDTH + 1);
if (isnan(value) || is_infinite(value))
{
/*
--- 5406,5411 ----
*************** float4_to_char(PG_FUNCTION_ARGS)
*** 5428,5442 ****
}
else
{
! snprintf(orgnum, MAXDOUBLEWIDTH + 1, "%+.*e", Num.post, value);
/*
* Swap a leading positive sign for a space.
*/
! if (*orgnum == '+')
! *orgnum = ' ';
!
! numstr = orgnum;
}
}
else
--- 5419,5447 ----
}
else
{
! numstr = psprintf("%+.*e", Num.post, value);
!
! /* prevent the display of imprecise/junk digits */
! if (Num.pre + Num.post > FLT_DIG)
! {
! int digits = 0;
! char *numstr_p;
!
! for (numstr_p = numstr; *numstr_p && *numstr_p != 'e'; numstr_p++)
! {
! if (isdigit(*numstr_p))
! {
! if (++digits > FLT_DIG)
! *numstr_p = '0';
! }
! }
! }
/*
* Swap a leading positive sign for a space.
*/
! if (*numstr == '+')
! *numstr = ' ';
}
}
else
*************** float4_to_char(PG_FUNCTION_ARGS)
*** 5452,5467 ****
Num.pre += Num.multi;
}
! orgnum = (char *) palloc(MAXFLOATWIDTH + 1);
! snprintf(orgnum, MAXFLOATWIDTH + 1, "%.0f", fabs(val));
! numstr_pre_len = strlen(orgnum);
! /* adjust post digits to fit max float digits */
! if (numstr_pre_len >= FLT_DIG)
! Num.post = 0;
! else if (numstr_pre_len + Num.post > FLT_DIG)
! Num.post = FLT_DIG - numstr_pre_len;
! snprintf(orgnum, MAXFLOATWIDTH + 1, "%.*f", Num.post, val);
if (*orgnum == '-')
{ /* < 0 */
--- 5457,5480 ----
Num.pre += Num.multi;
}
! /* let psprintf() do the rounding */
! orgnum = psprintf("%.*f", Num.post, val);
! /* prevent the display of imprecise/junk digits */
! if (Num.pre + Num.post > FLT_DIG)
! {
! int digits = 0;
! char *orgnum_p;
!
! for (orgnum_p = orgnum; *orgnum_p; orgnum_p++)
! {
! if (isdigit(*orgnum_p))
! {
! if (++digits > FLT_DIG)
! *orgnum_p = '0';
! }
! }
! }
if (*orgnum == '-')
{ /* < 0 */
*************** float8_to_char(PG_FUNCTION_ARGS)
*** 5520,5526 ****
numstr = orgnum = int_to_roman((int) rint(value));
else if (IS_EEEE(&Num))
{
- numstr = orgnum = (char *) palloc(MAXDOUBLEWIDTH + 1);
if (isnan(value) || is_infinite(value))
{
/*
--- 5533,5538 ----
*************** float8_to_char(PG_FUNCTION_ARGS)
*** 5534,5548 ****
}
else
{
! snprintf(orgnum, MAXDOUBLEWIDTH + 1, "%+.*e", Num.post, value);
/*
* Swap a leading positive sign for a space.
*/
! if (*orgnum == '+')
! *orgnum = ' ';
!
! numstr = orgnum;
}
}
else
--- 5546,5574 ----
}
else
{
! numstr = psprintf("%+.*e", Num.post, value);
!
! /* prevent the display of imprecise/junk digits */
! if (Num.pre + Num.post > DBL_DIG)
! {
! int digits = 0;
! char *numstr_p;
!
! for (numstr_p = numstr; *numstr_p && *numstr_p != 'e'; numstr_p++)
! {
! if (isdigit(*numstr_p))
! {
! if (++digits > DBL_DIG)
! *numstr_p = '0';
! }
! }
! }
/*
* Swap a leading positive sign for a space.
*/
! if (*numstr == '+')
! *numstr = ' ';
}
}
else
*************** float8_to_char(PG_FUNCTION_ARGS)
*** 5557,5571 ****
val = value * multi;
Num.pre += Num.multi;
}
- orgnum = (char *) palloc(MAXDOUBLEWIDTH + 1);
- numstr_pre_len = snprintf(orgnum, MAXDOUBLEWIDTH + 1, "%.0f", fabs(val));
! /* adjust post digits to fit max double digits */
! if (numstr_pre_len >= DBL_DIG)
! Num.post = 0;
! else if (numstr_pre_len + Num.post > DBL_DIG)
! Num.post = DBL_DIG - numstr_pre_len;
! snprintf(orgnum, MAXDOUBLEWIDTH + 1, "%.*f", Num.post, val);
if (*orgnum == '-')
{ /* < 0 */
--- 5583,5607 ----
val = value * multi;
Num.pre += Num.multi;
}
! /* let psprintf() do the rounding */
! orgnum = psprintf("%.*f", Num.post, val);
!
! /* prevent the display of imprecise/junk digits */
! if (Num.pre + Num.post > DBL_DIG)
! {
! int digits = 0;
! char *orgnum_p;
!
! for (orgnum_p = orgnum; *orgnum_p; orgnum_p++)
! {
! if (isdigit(*orgnum_p))
! {
! if (++digits > DBL_DIG)
! *orgnum_p = '0';
! }
! }
! }
if (*orgnum == '-')
{ /* < 0 */
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
new file mode 100644
index 9d68145..46b0c72
*** a/src/test/regress/expected/numeric.out
--- b/src/test/regress/expected/numeric.out
*************** select * from generate_series(1::numeric
*** 1499,1501 ****
--- 1499,1571 ----
3 | 4
(10 rows)
+ --
+ -- Test code path for high-precision output
+ --
+ SELECT to_char(float8 '99999999999', '9999999999999999D99999999');
+ to_char
+ ----------------------------
+ 99999999999.00000000
+ (1 row)
+
+ SELECT to_char(float8 '99999999999', '9999999999999999D' || repeat('9', 1000));
+ to_char
+ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 99999999999.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
+ (1 row)
+
+ SELECT to_char(float8 '1e9','999999999999999999999D9');
+ to_char
+ --------------------------
+ 1000000000.0
+ (1 row)
+
+ SELECT to_char(float8 '1e20','999999999999999999999D9');
+ to_char
+ --------------------------
+ 100000000000000000000.0
+ (1 row)
+
+ SELECT to_char(1e20, '999999999999999999999D9');
+ to_char
+ --------------------------
+ 100000000000000000000.0
+ (1 row)
+
+ SELECT to_char(float8 '1.123456789123456789', '9.' || repeat('9', 55));
+ to_char
+ ------------------------------------------------------------
+ 1.1234567891234500000000000000000000000000000000000000000
+ (1 row)
+
+ SELECT to_char(float8 '1999999999999999999999999999999999999999999999.123456789123456789',
+ repeat('9', 50) || '.' || repeat('9', 50));
+ to_char
+ --------------------------------------------------------------------------------------------------------
+ 1999999999999990000000000000000000000000000000.00000000000000000000000000000000000000000000000000
+ (1 row)
+
+ SELECT to_char(float8 '0.1', '9D' || repeat('9', 1000));
+ to_char
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ .1000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
+ (1 row)
+
+ SELECT to_char(int4 '1', '9D' || repeat('9', 1000) || 'EEEE');
+ to_char
+ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000e+00
+ (1 row)
+
+ SELECT to_char(float4 '1', '9D' || repeat('9', 1000) || 'EEEE');
+ to_char
+ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000e+00
+ (1 row)
+
+ SELECT to_char(float8 '1', '9D' || repeat('9', 1000) || 'EEEE');
+ to_char
+ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000e+00
+ (1 row)
+
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
new file mode 100644
index 19f909f..79e65f6
*** a/src/test/regress/expected/window.out
--- b/src/test/regress/expected/window.out
*************** SELECT to_char(SUM(n::float8) OVER (ORDE
*** 1806,1812 ****
FROM (VALUES(1,1e20),(2,1)) n(i,n);
to_char
--------------------------
! 100000000000000000000
1.0
(2 rows)
--- 1806,1812 ----
FROM (VALUES(1,1e20),(2,1)) n(i,n);
to_char
--------------------------
! 100000000000000000000.0
1.0
(2 rows)
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
new file mode 100644
index 1633e4c..a6301ea
*** a/src/test/regress/sql/numeric.sql
--- b/src/test/regress/sql/numeric.sql
*************** select (i / (10::numeric ^ 131071))::num
*** 858,860 ****
--- 858,877 ----
select * from generate_series(1::numeric, 3::numeric) i, generate_series(i,3) j;
select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,i) j;
select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,5,i) j;
+
+ --
+ -- Test code path for high-precision output
+ --
+
+ SELECT to_char(float8 '99999999999', '9999999999999999D99999999');
+ SELECT to_char(float8 '99999999999', '9999999999999999D' || repeat('9', 1000));
+ SELECT to_char(float8 '1e9','999999999999999999999D9');
+ SELECT to_char(float8 '1e20','999999999999999999999D9');
+ SELECT to_char(1e20, '999999999999999999999D9');
+ SELECT to_char(float8 '1.123456789123456789', '9.' || repeat('9', 55));
+ SELECT to_char(float8 '1999999999999999999999999999999999999999999999.123456789123456789',
+ repeat('9', 50) || '.' || repeat('9', 50));
+ SELECT to_char(float8 '0.1', '9D' || repeat('9', 1000));
+ SELECT to_char(int4 '1', '9D' || repeat('9', 1000) || 'EEEE');
+ SELECT to_char(float4 '1', '9D' || repeat('9', 1000) || 'EEEE');
+ SELECT to_char(float8 '1', '9D' || repeat('9', 1000) || 'EEEE');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
