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.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
