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

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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. +

In response to

Responses

pgsql-hackers by date

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

pgsql-patches by date

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

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