Re: Have I found an interval arithmetic bug?

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(dot)morland(at)gmail(dot)com>
wrote:

> On Fri, 2 Apr 2021 at 21:08, Zhihong Yu <zyu(at)yugabyte(dot)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.
>