Re: Have I found an interval arithmetic bug?

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).

Cheers

On Fri, Apr 2, 2021 at 5:07 PM Zhihong Yu <zyu(at)yugabyte(dot)com> wrote:

> Hi,
> bq. My new code returns 0.2 months for this, not zero
>
> Can you clarify (the output below that was 2 mons, not 0.2) ?
>
> Thanks
>
> On Fri, Apr 2, 2021 at 4:58 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
>> On Fri, Apr 2, 2021 at 02:00:03PM -0700, John W Higgins wrote:
>> > On Fri, Apr 2, 2021 at 11:05 AM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > While maybe there is an argument to fixing the negative/positive
>> rounding issue
>> > - there is no way this gets solved without breaking the current
>> implementation
>> >
>> > select interval '0.3 years' + interval '0.4 years' - interval '0.7
>> years' +
>> > interval '0.1 years' should not equal 0 but it certainly does.
>>
>> My new code returns 0.2 months for this, not zero:
>>
>> SELECT interval '0.3 years' + interval '0.4 years' -
>> interval '0.7 years' + interval '0.1 years';
>> ?column?
>> ----------
>> 2 mons
>>
>> which is also wrong since:
>>
>> SELECT interval '0.1 years';
>> interval
>> ----------
>> 1 mon
>>
>> > Unless we take the concept of 0.3 years = 3 months and move to
>> something along
>> > the lines of
>> >
>> > 1 year = 360 days
>> > 1 month = 30 days
>> >
>> > so therefore
>> >
>> > 0.3 years = 360 days * 0.3 = 108 days = 3 months 18 days
>> > 0.4 years = 360 days * 0.4 = 144 days = 4 months 24 days
>> > 0.7 years = 360 days * 0.7 = 252 days = 8 months 12 days
>> >
>> > Then, and only if we don't go to any more than tenths of a year, does
>> the math
>> > work. Probably this should resolve down to seconds and then work
>> backwards -
>> > but unless we're looking at breaking the entire way it currently
>> resolves
>> > things - I don't think this is of much value.
>> >
>> > Doing math on intervals is like doing math on rounded numbers - there
>> is always
>> > going to be a pile of issues because the level of precision just is not
>> good
>> > enough.
>>
>> I think the big question is what units do people want with fractional
>> values. I have posted a follow-up email that spills only for one unit,
>> which I think is the best approach.
>>
>> --
>> Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
>> EDB https://enterprisedb.com
>>
>> If only the physical world exists, free will is an illusion.
>>
>>
>>
>>