Tom Lane wrote:

I've also confirmed that the problem is in interval_div; you can reproduce the failure with select '41 years 1 mon 11 days'::interval / 10; which should give '4 years 1 mon 9 days 26:24:00', but when timestamp.o is compiled with "-mcpu=pentium4 -march=pentium4", you get '4 years 1 mon 10 days 02:24:00'. --enable-integer-datetimes is not relevant because the interesting part is all double/integer arithmetic. Looking at this, though, I wonder if the pentium4 answer isn't "more correct". If I'm doing the math by hand correctly, what we end up with is having to cascade 3/10ths of a month down into the days field, and since the conversion factor is supposed to be 30 days/month, that should be exactly 9 days. Plus the one full day from the 11/10 days gives 10 days. I think what is happening on all the non-Pentium platforms is that (3.0/10.0)*30.0 is producing something just a shade less than 9.0, whereas the Pentium gives 9.0 or a shade over, possiblydue to rearrangement of the calculation. I think we can still filethisas a compiler bug, because I'm pretty sure the C spec does not allow rearrangement of floating-point calculations ... but we might want to think about tweaking the code's roundoff behavior just a bit.

`I took a naive look at this, but have been unable to come up with a`

`satisfactory solution. Here's an even simpler example that exhibits`

`the behavior:`

# select '41 mon'::interval / 10; ?column? ------------------------ 4 mons 2 days 24:00:00 And one using a non-integer divisor: # select '2 mon'::interval / 1.5; ?column? ----------------------- 1 mon 9 days 24:00:00

`Here's the relevant code in interval_div (timestamp.c c2573).`

`month_remainder holds the fractional part of the months field of the`

`original interval (41) divided by the divisor (10) as a double.`

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

`result->time = rint(span->time / factor + day_remainder *`

`USECS_PER_DAY);`

#else result->time = span->time / factor + day_remainder * SECS_PER_DAY; #endif

`My understanding is that as month_remainder is a float (as is`

`month_remainder_days), month_remainder_days may be equal to 24 hours`

`after rounding. As we're converting from months to days, and from`

`days to time, rather than from months to time directly, we're`

`assuming that we should only have time less than 24 hours remaining`

`in the month_remainder_days when it's added to day_remainder. If`

`month_remainder_days is equal to 24 hours, we should increment result-`

`>day rather than carrying that down into result->time.`

With that in mind, I produced this hack: #ifdef HAVE_INT64_TIMESTAMP

`month_remainder_seconds = month_remainder_day_fraction *`

`USECS_PER_DAY;`

if ( USECS_PER_DAY == rint(month_remainder_seconds) ) result->day++; else if ( USECS_PER_DAY == - rint(month_remainder_seconds) ) result->day--; else day_remainder += month_remainder_day_fraction;

`result->time = rint(span->time / factor + day_remainder *`

`USECS_PER_DAY);`

#else

`month_remainder_seconds = month_remainder_day_fraction *`

`SECS_PER_DAY;`

if ( SECS_PER_DAY == (int32) month_remainder_seconds ) result->day++; else if ( SECS_PER_DAY == - (int32) month_remainder_seconds) result->day--; else day_remainder += month_remainder_day_fraction; result->time = span->time / factor + day_remainder * SECS_PER_DAY; #endif It works okay with --enable-integer-datetimes postgres=# select '41 mon'::interval/10; ?column? --------------- 4 mons 3 days (1 row) postgres=# select '2 mon'::interval/1.5; ?column? --------------- 1 mon 10 days (1 row)

`It also changes the result of the aggregate test for intervals, but I`

`think that's to be expected.`

*** ./expected/interval.out Tue Mar 7 07:49:17 2006 --- ./results/interval.out Thu Jun 22 22:52:41 2006 *************** *** 218,224 **** select avg(f1) from interval_tbl; avg ------------------------------------------------- ! @ 4 years 1 mon 9 days 28 hours 18 mins 23 secs (1 row) -- test long interval input --- 218,224 ---- select avg(f1) from interval_tbl; avg ------------------------------------------------- ! @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs (1 row) -- test long interval input

`But doesn't work without --enable-integer-datetimes. It still gives`

`the same answer as before. I don't have a firm grasp of floating`

`point math so I'm fully aware this might be entirely the wrong way to`

`go about this. It definitely feels kludgy (especially my sign-`

`handling), but I submit this in the hope it moves the discussion`

`forward a little. If someone wanted to point me in the right`

`direction, I'll try to finish this up, updating the regression test`

`and adding a few more to test this more thoroughly.`

Thanks! Michael Glaesemann grzm myrealbox com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org