Re: Zero-padding and zero-masking fixes for to_char(float)

On Sun, Mar 22, 2015 at 04:41:19PM -0400, Noah Misch wrote:
> When you posted this, I made a note to review it.
>
> 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
> >
> > which is pretty close.
>
> PostgreSQL 9.4 returns "1.12345678912346". Your patch truncates digits past
> DBL_DIG, whereas both Oracle and PostgreSQL 9.4 round to the nearest DBL_DIG
> digits. PostgreSQL must continue to round.

Ah, that rounding is a big problem. I can't just round because if the
digit to be rounded up is '9', I have to set that to zero and increment
the previous digit, and that could cascade and shift the entire string
one over. I think I have to go back to the original code that does the
weird computations to figure out how many digits are on the left of the
decimal point, then set the format string after. I was hoping my patch
could clean that all up, but we now need snprintf to do that rounding
for us. :-(

> 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.

> > *************** 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);
>
> Neither the submission notes nor the commit messages mentioned it, but this
> improves behavior for to_char(int4, text). Specifically, it helps EEEE
> formats with more than about 500 decimal digits:
>
> SELECT length(to_char(1, '9D' || repeat('9', 800) || 'EEEE'));

Wow, that is surprising.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +