Re: [PATCHES] Interval aggregate regression failure - Mailing list pgsql-hackers

On Sep 1, 2006, at 5:05 , Bruce Momjian wrote:

> Tom Lane wrote:
>> Bruce Momjian <bruce@momjian.us> writes:
>>> Well, the patch only multiplies by 30, so the interval would have to
>>> span +5 million years to overflow.  I don't see any reason to add
>>> rounding until we get an actual query that needs it
>>
>> Have you tried your patch against the various cases that have been
>> discussed in the past?  In particular there were several distinct
>> examples of this behavior posted at the beginning of the thread, and
>> I'd not assume that a fix for one handles them all.
>
> Yes, it fixes all posted examples, except one that displays 23:60.  I
> cannot reproduce that failure from Powerpc so am waiting for
> Michael to
> test it.

Here's your patch tested on my machine, both with and without --
enable-integer-datetimes. I've tweaked the ad hoc test suite to
include a case where the days and time differ in sign and added a
couple of queries to the ad hoc test suite to include the problems
Tom referred to--not that this patch will fix them, but to keep the
known problems together. I hope to add more to this to test more edge
cases.

Unfortunately the problem still occur (see product_d), and --enable-
integer-datetimes is pretty broken with this patch.

Michael Glaesemann
grzm seespotcode net


-- test queries
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;

select interval '41 mon 12 days 360:00' / 10 as quotient_a
     , interval '-41 mon -12 days +360:00' / 10 as quotient_b
     , interval '-41 mon 12 days 360:00' / 10 as quotient_c
     , interval '-41 mon -12 days -360:00' / 10 as quotient_d;

select interval '-12 days' * 0.3;

select 10000 * '1000000 hours'::interval as "ten billion";

set time zone 'EST5EDT';
select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval as
"2005-01-30 13:22:00-05";
select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29
13:22:00-04'::timestamptz as "a day";
set time zone local;

-- end test queries


-- without --enable-integer-datetimes

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
(1 row)


select interval '41 mon 12 days 360:00' / 10 as quotient_a
     , interval '-41 mon -12 days +360:00' / 10 as quotient_b
     , interval '-41 mon 12 days 360:00' / 10 as quotient_c
     , interval '-41 mon -12 days -360:00' / 10 as quotient_d;
        quotient_a       |        quotient_b         |
quotient_c         |        quotient_d
------------------------+---------------------------
+---------------------------+---------------------------
4 mons 4 days 40:48:00 | -4 mons -4 days +31:12:00 | -4 mons -2 days
+40:48:00 | -4 mons -4 days -40:48:00
(1 row)


select interval '-12 days' * 0.3;
        ?column?
----------------------
-3 days -14:23:60.00
(1 row)


select 10000 * '1000000 hours'::interval as "ten billion";
    ten billion
------------------
2147483647:00:00
(1 row)


set time zone 'EST5EDT';
SET
select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval as
"2005-01-30 13:22:00-05";
2005-01-30 13:22:00-05
------------------------
2005-10-30 13:22:00-05
(1 row)

select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29
13:22:00-04'::timestamptz as "a day";
      a day
----------------
1 day 01:00:00
(1 row)

set time zone local;
SET

-- with --enable-integer-datetimes

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:24:00
(1 row)


select interval '41 mon 12 days 360:00' / 10 as quotient_a
     , interval '-41 mon -12 days +360:00' / 10 as quotient_b
     , interval '-41 mon 12 days 360:00' / 10 as quotient_c
     , interval '-41 mon -12 days -360:00' / 10 as quotient_d;
        quotient_a       |        quotient_b         |
quotient_c         |        quotient_d
------------------------+---------------------------
+---------------------------+---------------------------
4 mons 4 days 40:48:00 | -4 mons -4 days +31:12:00 | -4 mons -2 days
+40:48:00 | -4 mons -4 days -40:48:00
(1 row)


select interval '-12 days' * 0.3;
      ?column?
-------------------
-3 days -14:24:00
(1 row)


select 10000 * '1000000 hours'::interval as "ten billion";
    ten billion
------------------
-00:00:00.000001
(1 row)


set time zone 'EST5EDT';
SET
select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval as
"2005-01-30 13:22:00-05";
2005-01-30 13:22:00-05
------------------------
2005-10-30 13:22:00-05
(1 row)

select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29
13:22:00-04'::timestamptz as "a day";
      a day
----------------
1 day 01:00:00
(1 row)

set time zone local;
SET