Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Interval aggregate regression failure

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org, Michael Paesold <mpaesold(at)gmx(dot)at>, PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [HACKERS] Interval aggregate regression failure
Date: 2006-08-31 23:56:28
Message-ID: 1DEE48F0-348D-4A10-8A34-4E1B491C24A6@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
On Sep 1, 2006, at 5:05 , Bruce Momjian wrote:

> Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)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


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-09-01 00:01:49
Subject: Re: GRANT role docs inconsistency
Previous:From: Tom LaneDate: 2006-08-31 23:56:21
Subject: Re: python / 7.4 / FC5 / x86_64

pgsql-patches by date

Next:From: Tom LaneDate: 2006-09-01 00:12:43
Subject: Re: Interval month, week -> day
Previous:From: Tom LaneDate: 2006-08-31 23:56:21
Subject: Re: python / 7.4 / FC5 / x86_64

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group