Re: [HACKERS] Interval aggregate regression failure

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > I am unclear about this report.  The patch was not meant to fix every
> > interval issue, but merely to improve multiplication and division
> > computations.  Does it do that?
> 
> According to Michael's last report, your patch fails under
> --enable-integer-datetimes.

Where does it fail?  Here?

	select interval '41 mon 12 days 360:00' * 0.3 as product_a
	     , interval '-41 mon -12 days +360:00' * 0.3 as product_b
	     , interval '-41 mon 12 days 360:00' * 0.3 as product_c
	     , interval '-41 mon -12 days -360:00' * 0.3 as product_d;
	         product_a         |          product_b          |
	product_c          |            product_d
	--------------------------+-----------------------------
	+----------------------------+---------------------------------
	1 year 11 days 146:24:00 | -1 years -11 days +69:36:00 | -1 years -5
	days +98:24:00 | -1 years -11 days -146:23:60.00
	                                        -----

That is wrong, but I think we need another fix for that.  Notice the
problem is in minutes/seconds, not hours.

> This is an issue where you have to be "as simple as possible, but no
> simpler".  I think the approach you are proposing is too simple.
> Michael's last patch here:
> http://archives.postgresql.org/pgsql-patches/2006-08/msg00447.php
> looks considerably more likely to lead to a workable answer.

I see he is taking the fractional part of the result and finding if that
should round.  I am confused why that would help the -146:23:60.00 value
above.  Notice we only see it for negative values too.

I do like that he is rounding the computation spillover, and not the
total time value, which is what I started with.

I believe my provides a more accurate computation, and doesn't have the
problems of rounding.  The only bug we can find is the powerpc one for
-146:23:60 minutes.

-- 
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +