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

On 21-Jul-2021, at 17:07, Bruce Momjian <bruce@momjian.us> wrote:

On Wed, Jul 21, 2021 at 01:29:49PM -0400, Tom Lane wrote:

Bryn Llewellyn <bryn@yugabyte.com> writes:

It was me that started the enormous thread with the title “Have I found an interval arithmetic bug?” on 01-Apr-2021. I presented this testcase:

select interval '-1.7 years';                          -- -1 years -8 mons

select interval '29.4 months';                         --  2 years  5 mons 12 days

select interval '-1.7 years 29.4 months';              --           8 mons 12 days << wrong
select interval '29.4 months -1.7 years';              --           9 mons 12 days

select interval '-1.7 years' + interval '29.4 months'; --           9 mons 12 days
select interval '29.4 months' + interval '-1.7 years'; --           9 mons 12 days

The consensus was that the outcome that I flagged with “wrong” does indeed have that status.

Yeah, I think it's self-evident that your last four cases should
produce the same results.  Whether '9 mons 12 days' is the best
possible result is debatable --- in a perfect world, maybe we'd
produce '9 mons' exactly --- but given that the first two cases
produce what they do, that does seem self-consistent.  I think
we should be setting out to fix that outlier without causing
any of the other five results to change.

OK, I decided to reverse some of the changes I was proposing once I
started to think about the inaccuracy of not spilling down from 'weeks'
to seconds when hours also appear.  The fundamental issue is that the
months-to-days conversion is almost always an approximation, while the
days to seconds conversion is almost always accurate.  This means we are
never going to have consistent spill-down that is useful.

Therefore, I went ahead and accepted that years and larger units spill
only to months, months spill only to days, and weeks and lower spill all
the way down to seconds.  I also spelled this out in the docs, and
explained why we have this behavior.

Also, with my patch, the last four queries return the same result
because of the proper rounding also added by the patch, attached.

“months-to-days conversion is almost always an approximation, while the days to seconds conversion is almost always accurate.”