Re: [HACKERS] Interval aggregate regression failure

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Michael Glaesemann <grzm(at)seespotcode(dot)net>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 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-29 22:12:54
Message-ID: 200608292212.k7TMCsf01477@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

Michael Glaesemann wrote:
>
> On Aug 30, 2006, at 1:13 , Bruce Momjian wrote:
>
> > Uh, I came up with a cleaner one, I think. I didn't test
> > --enable-integer-datetimes yet.
>
> Cool. It's indeed much cleaner. Thanks, Bruce. I'm about to head to
> bed, but I'll look at it more closely tomorrow.
>
> I also noticed that my regression tests didn't exercise the code I
> thought it did. If you have a chance before I get to it, you might
> want to try these as well:
>
> 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 2 days -40:48:00 | -4 mons -2 days
> +40:48:00 | -4 mons -4 days -40:48:00
> (1 row)
>
> 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 12 days 122:24:00 | 1 year 6 days -122:23:60.00 | -1 years -6
> days +122:24:00 | -1 years -12 days -122:23:60.00
> (1 row)
>
> The quotients look fine, but I'm wondering if another set of rounding
> is needed to bump those -122:23:60.00 to -122:24:00 in product_b and
> product_d.

Here are the results using my newest patch:

test=> 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 2 days -40:48:00 | -4 mons -2 days +40:48:00 | -4 mons -4 days -40:48:00
(1 row)

test=> 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 12 days 122:24:00 | 1 year 6 days -122:24:00 | -1 years -6 days +122:24:00 | -1 years -12 days -122:24:00
(1 row)

I see no "23:60" entries.

I realize the problem with my first patch. I was rounding at the
'seconds' level, but that is too late in the process. The rounding has
to happen right after the division. In fact the only rounding problem I
can find is with month_remainder_days, because of a division by factor,
and a multiplication to convert it to days. The combination of steps
is where the rounding problem is happening. The patch is even smaller
now.

The code assume if it is within 0.000001 of a whole number, it should be
rounded to a whole number. Patch attached with comments added.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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

Attachment Content-Type Size
unknown_filename text/plain 1.6 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-08-29 22:31:24 Re: updated patch for selecting large results sets in psql using cursors
Previous Message Martijn van Oosterhout 2006-08-29 21:00:29 Re: [PATCHES] Another VPATH patch for ecpg

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2006-08-29 22:31:24 Re: updated patch for selecting large results sets in psql using cursors
Previous Message Martijn van Oosterhout 2006-08-29 21:00:29 Re: [PATCHES] Another VPATH patch for ecpg