[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [PATCHES] [HACKERS] Interval aggregate regression failure
From: Michael Glaesemann <grzm () seespotcode ! net>
Date: 2006-08-31 23:56:28
Message-ID: 1DEE48F0-348D-4A10-8A34-4E1B491C24A6 () seespotcode ! net
[Download RAW message or body]
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic