Re: Have I found an interval arithmetic bug? - Mailing list pgsql-hackers

From Zhihong Yu
Subject Re: Have I found an interval arithmetic bug?
Date
Msg-id CALNJ-vRF71-c8oubm96wx4qBz1TQr2Dm5ocQAYbHmtmf_gBTmg@mail.gmail.com
Whole thread Raw
In response to Re: Have I found an interval arithmetic bug?  (Isaac Morland <isaac.morland@gmail.com>)
Responses Re: Have I found an interval arithmetic bug?
List pgsql-hackers
Hi,
The mix of interval and comparison with float is not easy to interpret. See the following (I got 0.0833 since the result for interval '0.3 years' + interval '0.4 years' - ... query was 1 month and 1/12 ~= 0.0833).

yugabyte=# select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7 * '1 year'::interval = '0.0833 year'::interval;
 ?column?
----------
 f

As long as Bruce's patch makes improvements over the current behavior, I think that's fine.

Cheers

On Fri, Apr 2, 2021 at 6:24 PM Isaac Morland <isaac.morland@gmail.com> wrote:
On Fri, 2 Apr 2021 at 21:08, Zhihong Yu <zyu@yugabyte.com> wrote:
Hi,
I got a local build with second patch where:

yugabyte=# SELECT  interval '0.3 years' + interval '0.4 years' -
                interval '0.7 years';
 ?column?
----------
 1 mon

I think the outcome is a bit unintuitive (I would expect result close to 0).

That's not fundamentally different from this:

odyssey=> select 12 * 3/10 + 12 * 4/10 - 12 * 7/10;
 ?column? 
----------
       -1
(1 row)

odyssey=> 

And actually the result is pretty close to 0. I mean it’s less than 0.1 year.

I wonder if it might have been better if only integers had been accepted for the components? If you want 0.3 years write 0.3 * '1 year'::interval. But changing it now would be a pretty significant backwards compatibility break.

There's really no avoiding counterintuitive behaviour though. Look at this:

odyssey=> select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7 * '1 year'::interval;
     ?column?     
------------------
 -1 mons +30 days
(1 row)

odyssey=> select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7 * '1 year'::interval = '0';
 ?column? 
----------
 t
(1 row)

odyssey=> 

In other words, doing the “same” calculation but with multiplying 1 year intervals by floats to get the values to add, you end up with an interval that while not identical to 0 does compare equal to 0. So very close to 0; in fact, as close to 0 as you can get without actually being identically 0.

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: SP-GiST confusion: indexed column's type vs. index column type
Next
From: Bruce Momjian
Date:
Subject: Re: Have I found an interval arithmetic bug?