Re: Interval aggregate regression failure (expected seems

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Michael Glaesemann <grzm(at)myrealbox(dot)com>
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Michael Paesold <mpaesold(at)gmx(dot)at>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Interval aggregate regression failure (expected seems
Date: 2006-07-01 05:54:10
Message-ID: 1A3CA234-15EC-439F-ABF8-F8384CD50DB1@seespotcode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

On Jun 23, 2006, at 9:47 , Michael Glaesemann wrote:

> # select '41 mon'::interval / 10;
> ?column?
> ------------------------
> 4 mons 2 days 24:00:00

> My understanding is that as month_remainder is a float (as is
> month_remainder_days), month_remainder_days may be equal to 24
> hours after rounding. As we're converting from months to days, and
> from days to time, rather than from months to time directly, we're
> assuming that we should only have time less than 24 hours remaining
> in the month_remainder_days when it's added to day_remainder.

This behavior is the same as applying justify_hours before adding the
days and time components to the result. With this in mind, I rewrote
interval_div to call interval_justify_hours. Good news is that --
enable-integer-datetimes works as expected. Bad news is that without
--enable-integer-datetimes, still get the current behavior.

I also came across something I think is odd:

# select version();

version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.1.4 on powerpc-apple-darwin8.7.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5341)
(1 row)

select justify_hours(a/10) as divided_and_justified
, justify_hours(b) as justified
, a/10 = b as are_equal
from (select '41 mon'::interval,'4 mons 2 days 24:00:00'::interval)
as s(a,b);

without --enable-integer-datetimes:

divided_and_justified | justified | are_equal
------------------------+---------------+-----------
4 mons 2 days 24:00:00 | 4 mons 3 days | t
(1 row)

with --enable-integer-datetimes:

divided_and_justified | justified | are_equal
-----------------------+---------------+-----------
4 mons 3 days | 4 mons 3 days | t

I think this just confirms that there is some kind of rounding (or
lack of) in interval_div. Kind of frustrating that it's not visible
in the result.

Anyway, there's another data point.

Michael Glaesemann
grzm seespotcode net

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Martijn van Oosterhout 2006-07-01 12:35:33 Re: different sort order in windows and linux version
Previous Message Tom Lane 2006-07-01 03:20:28 Re: Index corruption

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2006-07-01 20:16:22 Re: Interval aggregate regression failure (expected seems
Previous Message Robert Treat 2006-06-30 19:10:16 update commercial services link