SEO

On Sep 3, 2006, at 12:34 , Bruce Momjian wrote:

> OK, I worked with Michael and I think this is the best we are going to
> do to fix this. It has one TSROUND call for Powerpc, and that is
> documented. Applied.

As I was working up regression tests, I found a case that this patch
doesn't handle.

select interval '4 mon' * .3 as product_h;
product_h
-----------------------
1 mon 5 days 24:00:00
(1 row)

This should be 1 mon 6 days. It fails for any number of months
greater than 3 that is not evenly divisible by 10, greater than 3
months. Do we need to look at the month remainder separately?

Michael Glaesemann
grzm seespotcode net




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Is this non-datetime integer only or both? I cannot reproduce the
failure here.

---------------------------------------------------------------------------

Michael Glaesemann wrote:
>
> On Sep 3, 2006, at 12:34 , Bruce Momjian wrote:
>
> > OK, I worked with Michael and I think this is the best we are going to
> > do to fix this. It has one TSROUND call for Powerpc, and that is
> > documented. Applied.
>
> As I was working up regression tests, I found a case that this patch
> doesn't handle.
>
> select interval '4 mon' * .3 as product_h;
> product_h
> -----------------------
> 1 mon 5 days 24:00:00
> (1 row)
>
> This should be 1 mon 6 days. It fails for any number of months
> greater than 3 that is not evenly divisible by 10, greater than 3
> months. Do we need to look at the month remainder separately?
>
> Michael Glaesemann
> grzm seespotcode net

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bruce Momjian <bruce@momjian.us> writes:
> Is this non-datetime integer only or both? I cannot reproduce the
> failure here.

On HPPA with float datetimes with today's code, Michael's case works
but it took me less than two minutes to find one that doesn't:

regression=# select interval '14 mon' * 8.2 as product_h;
product_h
---------------------------------
9 years 6 mons 23 days 24:00:00
(1 row)

I reiterate my comment that this approach will never work; any small
amount of experimentation will turn up cases that don't round correctly
on one platform or another. Float arithmetic is inherently inexact.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Michael Glaesemann wrote:
>
> On Sep 3, 2006, at 12:34 , Bruce Momjian wrote:
>
> > OK, I worked with Michael and I think this is the best we are going to
> > do to fix this. It has one TSROUND call for Powerpc, and that is
> > documented. Applied.
>
> As I was working up regression tests, I found a case that this patch
> doesn't handle.
>
> select interval '4 mon' * .3 as product_h;
> product_h
> -----------------------
> 1 mon 5 days 24:00:00
> (1 row)
>
> This should be 1 mon 6 days. It fails for any number of months
> greater than 3 that is not evenly divisible by 10, greater than 3
> months. Do we need to look at the month remainder separately?

Another question. Is this result correct?

test=> select '999 months 999 days'::interval / 100;
?column?
-------------------------
9 mons 38 days 40:33:36
(1 row)

Should that be:

9 mons 39 days 16:33:36

The core problem is that the combined remainder seconds of months and
days is > 24 hours.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Sep 4, 2006, at 4:45 , Bruce Momjian wrote:

> Another question. Is this result correct?
>
> test=> select '999 months 999 days'::interval / 100;
> ?column?
> -------------------------
> 9 mons 38 days 40:33:36
> (1 row)
>
> Should that be:
>
> 9 mons 39 days 16:33:36

Yeah, I think it should be. I had been thinking of treating the month
and day component as having separate time contributions, but it makes
more sense to think of month as a collection of days first, integral
or no, and then cascade down the fractional portion of the combined
days component to time. I.e., 9.99 mon is 9 mon 29.7 days, rather
than 9 mon 29 days 60480 sec.

Michael Glaesemann
grzm seespotcode net





---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Michael Glaesemann wrote:
>
> On Sep 4, 2006, at 4:45 , Bruce Momjian wrote:
>
> > Another question. Is this result correct?
> >
> > test=> select '999 months 999 days'::interval / 100;
> > ?column?
> > -------------------------
> > 9 mons 38 days 40:33:36
> > (1 row)
> >
> > Should that be:
> >
> > 9 mons 39 days 16:33:36
>
> Yeah, I think it should be. I had been thinking of treating the month
> and day component as having separate time contributions, but it makes
> more sense to think of month as a collection of days first, integral
> or no, and then cascade down the fractional portion of the combined
> days component to time. I.e., 9.99 mon is 9 mon 29.7 days, rather
> than 9 mon 29 days 60480 sec.

No, I don't think so. If we do that, there is no reason to cascade at
all. Why not just say 9.1 months?

I am going to work on a patch to fix the >24 hours case, which will fix
your 24:00:00 case at the same time. Will post in an hour.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Is this non-datetime integer only or both? I cannot reproduce the
> > failure here.
>
> On HPPA with float datetimes with today's code, Michael's case works
> but it took me less than two minutes to find one that doesn't:
>
> regression=# select interval '14 mon' * 8.2 as product_h;
> product_h
> ---------------------------------
> 9 years 6 mons 23 days 24:00:00
> (1 row)
>
> I reiterate my comment that this approach will never work; any small
> amount of experimentation will turn up cases that don't round correctly
> on one platform or another. Float arithmetic is inherently inexact.

Working on a new patch to round; will post.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Bruce Momjian wrote:
> Michael Glaesemann wrote:
> >
> > On Sep 3, 2006, at 12:34 , Bruce Momjian wrote:
> >
> > > OK, I worked with Michael and I think this is the best we are going to
> > > do to fix this. It has one TSROUND call for Powerpc, and that is
> > > documented. Applied.
> >
> > As I was working up regression tests, I found a case that this patch
> > doesn't handle.
> >
> > select interval '4 mon' * .3 as product_h;
> > product_h
> > -----------------------
> > 1 mon 5 days 24:00:00
> > (1 row)
> >
> > This should be 1 mon 6 days. It fails for any number of months
> > greater than 3 that is not evenly divisible by 10, greater than 3
> > months. Do we need to look at the month remainder separately?
>
> Another question. Is this result correct?
>
> test=> select '999 months 999 days'::interval / 100;
> ?column?
> -------------------------
> 9 mons 38 days 40:33:36
> (1 row)
>
> Should that be:
>
> 9 mons 39 days 16:33:36
>
> The core problem is that the combined remainder seconds of months and
> days is > 24 hours.

OK, updated patch. It will fix the >=24:00:00 case because it cascades
up if the remainder number of seconds is greater or equal to one day.
One open item is that it still might show >24 hours if the seconds
computation combined with the remaning seconds >24 hours. Not sure if
that should be handled or not. If you fix that, you really are
cascading up because the resulting seconds might be less than the
computed value, e.g. result is 23:00:00, remainder is 02:00:00, cascade
up would be 1 day, 01:00:00. I am unsure we want to do that. Right
now, this will show 25:00:00.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Bruce Momjian wrote:
> OK, updated patch. It will fix the >=24:00:00 case because it cascades
> up if the remainder number of seconds is greater or equal to one day.
> One open item is that it still might show >24 hours if the seconds
> computation combined with the remaning seconds >24 hours. Not sure if
> that should be handled or not. If you fix that, you really are
> cascading up because the resulting seconds might be less than the
> computed value, e.g. result is 23:00:00, remainder is 02:00:00, cascade
> up would be 1 day, 01:00:00. I am unsure we want to do that. Right
> now, this will show 25:00:00.

Updated patch that uses TSROUND for partial month and days. Michael
says the tests look good on his system. I think we are done with this
bug. :-)

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Sep 5, 2006, at 10:14 , Bruce Momjian wrote:

> Bruce Momjian wrote:
>> OK, updated patch. It will fix the >=24:00:00 case because it
>> cascades
>> up if the remainder number of seconds is greater or equal to one day.
>> One open item is that it still might show >24 hours if the seconds
>> computation combined with the remaning seconds >24 hours. Not
>> sure if
>> that should be handled or not. If you fix that, you really are
>> cascading up because the resulting seconds might be less than the
>> computed value, e.g. result is 23:00:00, remainder is 02:00:00,
>> cascade
>> up would be 1 day, 01:00:00. I am unsure we want to do that. Right
>> now, this will show 25:00:00.
>
> Updated patch that uses TSROUND for partial month and days. Michael
> says the tests look good on his system. I think we are done with this
> bug. :-)

Please find attached regression tests for this patch.

Michael Glaesemann
grzm seespotcode net





---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend