On Tue, Mar 24, 2015 at 09:47:56AM -0400, Noah Misch wrote:
> On Sun, Mar 22, 2015 at 10:53:12PM -0400, Bruce Momjian wrote:
> > On Sun, Mar 22, 2015 at 04:41:19PM -0400, Noah Misch wrote:
> > > On Wed, Mar 18, 2015 at 05:52:44PM -0400, Bruce Momjian wrote:
> > > > 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
>
> > > These outputs show Oracle treating 17 digits as significant while
> > > PostgreSQL
> > > treats 15 digits as significant. Should we match Oracle in this respect
> > > while
> > > we're breaking compatibility anyway? I tend to think yes.
> >
> > Uh, I am hesistant to adjust our precision to match Oracle as I don't
> > know what they are using internally.
>
> http://sqlfiddle.com/#!4/8b4cf/5 strongly implies 17 significant digits for
> float8 and 9 digits for float4.
I was able to get proper rounding with the attached patch.
test=> SELECT to_char(float8 '1.123456789123456789123456789',
'9.9999999999999999999999999999999999999');
to_char
------------------------------------------
1.1234567891234600000000000000000000000
(1 row)
Handling rounding for exponent-format values turned out to be simple.
What has me stuck now is how to do rounding in the non-decimal part of
the number, e.g.
test=> SELECT to_char(float4
'15555555555555.912345678912345678900000000000000000000000',
repeat('9', 50) || '.' || repeat('9', 50));
to_char
--------------------------------------------------------------------------------------------------------
15555555753984.00000000000000000000000000000000000000000000000000
(1 row)
This should return something like 15555600000000.000... (per Oracle
output at the URL above, float4 has 6 significant digits on my compiler)
but I can't seem to figure how to get printf() to round non-fractional
parts. I am afraid the only solution is to use printf's %e format and
place the decimal point myself.
The fact I still don't have a complete solution suggests this is 9.6
material but I still want to work on it so it is ready.
--
Bruce Momjian <[email protected]> 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..2b5a440
*** 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 ----
*************** static DCHCacheEntry *DCH_cache_getnew(c
*** 989,994 ****
--- 982,988 ----
static NUMCacheEntry *NUM_cache_search(char *str);
static NUMCacheEntry *NUM_cache_getnew(char *str);
static void NUM_cache_remove(NUMCacheEntry *ent);
+ static char *add_zero_padding(char *num_str, int pad_digits);
/* ----------
*************** do { \
*** 5016,5021 ****
--- 5010,5056 ----
SET_VARSIZE(result, len + VARHDRSZ); \
} while (0)
+ /*
+ * add_zero_padding
+ *
+ * Some sprintf() implementations have a 512-digit precision limit, and we
+ * need sprintf() to round to the internal precision, so this function adds
+ * zero padding between the mantissa and exponent of an exponential-format
+ * number, or after the supplied string for non-exponent strings.
+ */
+ static char *
+ add_zero_padding(char *num_str, int pad_digits)
+ {
+ /* one for decimal point, one for trailing null byte */
+ char *out = palloc(strlen(num_str) + pad_digits + 1 + 1), *out_p = out;
+ char *num_str_p = num_str;
+ bool found_decimal = false;
+
+ /* copy the number before 'e', or the entire string if no 'e' */
+ while (*num_str_p && *num_str_p != 'e' && *num_str_p != 'E')
+ {
+ if (*num_str_p == '.')
+ found_decimal = true;
+ *(out_p++) = *(num_str_p++);
+ }
+
+ if (!found_decimal)
+ *(out_p++) = '.';
+
+ /* add zero pad digits */
+ while (pad_digits-- > 0)
+ *(out_p++) = '0';
+
+ /* copy 'e' and everything after */
+ while (*num_str_p)
+ *(out_p++) = *(num_str_p++);
+
+ *(out_p++) = '\0';
+
+ pfree(num_str);
+ return out;
+ }
+
/* -------------------
* NUMERIC to_number() (convert string to numeric)
* -------------------
*************** 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.
--- 5249,5260 ----
/* we can do it easily because float8 won't lose any precision */
float8 val = (float8) value;
! /* Use '1' if there is any Num.post, so we get a decimal point */
! orgnum = psprintf("%+.*e", Min(Num.post, 1), val);
!
! /* Add any additional zeros */
! if (Num.post > 1)
! orgnum = add_zero_padding(orgnum, Num.post - 1);
/*
* Swap a leading positive sign for a space.
*************** int4_to_char(PG_FUNCTION_ARGS)
*** 5253,5265 ****
/* post-decimal digits? Pad out with zeros. */
if (Num.post)
! {
! numstr = (char *) palloc(numstr_pre_len + Num.post + 2);
! strcpy(numstr, orgnum);
! *(numstr + numstr_pre_len) = '.';
! memset(numstr + numstr_pre_len + 1, '0', Num.post);
! *(numstr + numstr_pre_len + Num.post + 1) = '\0';
! }
else
numstr = orgnum;
--- 5292,5298 ----
/* post-decimal digits? Pad out with zeros. */
if (Num.post)
! numstr = add_zero_padding(pstrdup(orgnum), Num.post);
else
numstr = orgnum;
*************** int8_to_char(PG_FUNCTION_ARGS)
*** 5363,5375 ****
/* post-decimal digits? Pad out with zeros. */
if (Num.post)
! {
! numstr = (char *) palloc(numstr_pre_len + Num.post + 2);
! strcpy(numstr, orgnum);
! *(numstr + numstr_pre_len) = '.';
! memset(numstr + numstr_pre_len + 1, '0', Num.post);
! *(numstr + numstr_pre_len + Num.post + 1) = '\0';
! }
else
numstr = orgnum;
--- 5396,5402 ----
/* post-decimal digits? Pad out with zeros. */
if (Num.post)
! numstr = add_zero_padding(pstrdup(orgnum), Num.post);
else
numstr = orgnum;
*************** 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))
{
/*
--- 5441,5446 ----
*************** float4_to_char(PG_FUNCTION_ARGS)
*** 5428,5448 ****
}
else
{
! snprintf(orgnum, MAXDOUBLEWIDTH + 1, "%+.*e", Num.post, value);
/*
* Swap a leading positive sign for a space.
*/
! if (*orgnum == '+')
! *orgnum = ' ';
!
! numstr = orgnum;
}
}
else
{
float4 val = value;
! int numstr_pre_len;
if (IS_MULTI(&Num))
{
--- 5454,5479 ----
}
else
{
! /* We already have one decimal digit before the decimal point. */
! numstr = psprintf("%+.*e",
! Min(Num.post, FLT_DIG + extra_float_digits - 1), value);
!
! if (Num.post > FLT_DIG + extra_float_digits - 1)
! numstr = add_zero_padding(numstr,
! Num.post - FLT_DIG - extra_float_digits + 1);
/*
* Swap a leading positive sign for a space.
*/
! if (*numstr == '+')
! *numstr = ' ';
}
}
else
{
float4 val = value;
! int numstr_pre_len, val_exp = 0;
! char exp_buf[10];
if (IS_MULTI(&Num))
{
*************** 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 */
--- 5483,5505 ----
Num.pre += Num.multi;
}
! /* Find possible negative exponent */
! snprintf(exp_buf, sizeof(exp_buf), "%.0e", val);
! if (strchr(exp_buf, 'e') != NULL)
! {
! val_exp = atoi(strchr(exp_buf, 'e') + 1);
! /* exp assumes one digit before the decimal point, so increment */
! if (++val_exp > FLT_DIG + extra_float_digits)
! val_exp = FLT_DIG + extra_float_digits;
! }
! /* let psprintf() do the rounding */
! orgnum = psprintf("%.*f",
! Min(Num.post, FLT_DIG + extra_float_digits - val_exp), val);
!
! if (Num.post > FLT_DIG + extra_float_digits - val_exp)
! orgnum = add_zero_padding(orgnum,
! Num.post - FLT_DIG - extra_float_digits + val_exp);
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))
{
/*
--- 5558,5563 ----
*************** float8_to_char(PG_FUNCTION_ARGS)
*** 5534,5554 ****
}
else
{
! snprintf(orgnum, MAXDOUBLEWIDTH + 1, "%+.*e", Num.post, value);
/*
* Swap a leading positive sign for a space.
*/
! if (*orgnum == '+')
! *orgnum = ' ';
!
! numstr = orgnum;
}
}
else
{
float8 val = value;
! int numstr_pre_len;
if (IS_MULTI(&Num))
{
--- 5571,5596 ----
}
else
{
! /* We already have one decimal digit before the decimal point. */
! numstr = psprintf("%+.*e",
! Min(Num.post, DBL_DIG + extra_float_digits - 1), value);
!
! if (Num.post > DBL_DIG + extra_float_digits - 1)
! numstr = add_zero_padding(numstr,
! Num.post - DBL_DIG - extra_float_digits + 1);
/*
* Swap a leading positive sign for a space.
*/
! if (*numstr == '+')
! *numstr = ' ';
}
}
else
{
float8 val = value;
! int numstr_pre_len, val_exp = 0;
! char exp_buf[10];
if (IS_MULTI(&Num))
{
*************** 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 */
--- 5599,5622 ----
val = value * multi;
Num.pre += Num.multi;
}
! /* Find possible negative exponent */
! snprintf(exp_buf, sizeof(exp_buf), "%.0e", val);
! if (strchr(exp_buf, 'e') != NULL)
! {
! val_exp = atoi(strchr(exp_buf, 'e') + 1);
! /* exp assumes one digit before the decimal point, so increment */
! if (++val_exp > DBL_DIG + extra_float_digits)
! val_exp = DBL_DIG + extra_float_digits;
! }
!
! /* let psprintf() do the rounding */
! orgnum = psprintf("%.*f",
! Min(Num.post, DBL_DIG + extra_float_digits - val_exp), val);
!
! if (Num.post > DBL_DIG + extra_float_digits - val_exp)
! orgnum = add_zero_padding(orgnum,
! Num.post - DBL_DIG - extra_float_digits + val_exp);
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..0113522
*** 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.1234567891234600000000000000000000000000000000000000000
+ (1 row)
+
+ SELECT to_char(float8 '1999999999999999999999999999999999999999999999.123456789123456789',
+ repeat('9', 50) || '.' || repeat('9', 50));
+ to_char
+ --------------------------------------------------------------------------------------------------------
+ 1999999999999999859514578049071102439861518336.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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
