Re: Interval aggregate regression failure (expected seems

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Glaesemann <grzm(at)seespotcode(dot)net>
Cc: Michael Glaesemann <grzm(at)myrealbox(dot)com>, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Michael Paesold <mpaesold(at)gmx(dot)at>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Interval aggregate regression failure (expected seems
Date: 2006-07-01 20:16:22
Message-ID: 3752.1151784982@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
> ... I think this just confirms that there is some kind of rounding (or
> lack of) in interval_div. Kind of frustrating that it's not visible
> in the result.

I think the fundamental problem is that the float8 results of division
are inaccurate, and yet we're assuming that we can (for instance) coerce
them to integer and get exactly the right answer. For instance, in the
'41 months'/10 example, I get month_remainder_days being computed as

(gdb) p month_remainder
$19 = 0.099999999999999645
(gdb) s
2575 result->day += (int32) month_remainder_days;
(gdb) p month_remainder_days
$20 = 2.9999999999999893

The only way we can really fix this is to be willing to round off
the numbers, and I think the only principled way to do that is to
settle on a specific target accuracy, probably 1 microsecond.
Then the thing to do would be to scale up all the intermediate
float results to microseconds and apply rint(). Something like
(untested)

month_remainder = rint(span->month * USECS_PER_MONTH / factor);
day_remainder = rint(span->day * USECS_PER_DAY / factor);
result->month = (int32) (month_remainder / USECS_PER_MONTH);
result->day = (int32) (day_remainder / USECS_PER_DAY);
month_remainder -= result->month * USECS_PER_MONTH;
day_remainder -= result->day * USECS_PER_DAY;

/*
* Handle any fractional parts the same way as in interval_mul.
*/

/* fractional months full days into days */
month_remainder_days = month_remainder * DAYS_PER_MONTH;
extra_days = (int32) (month_remainder_days / USECS_PER_DAY);
result->day += extra_days;
/* fractional months partial days into time */
day_remainder += month_remainder_days - extra_days * USECS_PER_DAY;

#ifdef HAVE_INT64_TIMESTAMP
result->time = rint(span->time / factor + day_remainder);
#else
result->time = rint(span->time * 1.0e6 / factor + day_remainder) / 1.0e6;
#endif

This might need a few more rint() calls --- I'm assuming that float ops
with exact integral inputs will be OK, which is an assumption used
pretty widely in the datetime code, but ...

Per the comment, if we do this here we probably want to make
interval_mul work similarly.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomi NA 2006-07-01 22:06:44 Re: different sort order in windows and linux version
Previous Message Tom Lane 2006-07-01 18:59:54 Re: optimizing constant quals within outer joins

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2006-07-02 02:00:47 Re: ADD/DROPS INHERIT (actually INHERIT / NO INHERIT)
Previous Message Michael Glaesemann 2006-07-01 05:54:10 Re: Interval aggregate regression failure (expected seems