Re: Have I found an interval arithmetic bug?

On Mon, Apr 12, 2021 at 4:22 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Mon, Apr 12, 2021 at 03:09:48PM -0700, Bryn Llewellyn wrote:
> > I showed you all this example a long time ago:
> >
> > select (
> > '
> > 3.853467 years
> > '::interval
> > )::text as i;
> >
> > This behavior is the same in the env. of Bruce’s patch as in unpatched
> PG 13.2. This is the result.
> >
> > 3 years 10 mons
> >
> > Notice that "3.853467 years" is "3 years" plus "10.241604 months". This
> explains the "10 mons" in the result. But the 0.241604 months remainder did
> not spill down into days.
> >
> > Can anybody defend this quirk? An extension of this example with a real
> number of month in the user input is correspondingly yet more quirky. The
> rules can be written down. But they’re too tortuos to allow an ordinary
> mortal confidently to design code that relies on them.
> >
> > (I was unable to find any rule statement that lets the user predict this
> in the doc. But maybe that’s because of my feeble searching skills.)
> >
> > If there is no defense (and I cannot imagine one) might Bruce’s patch
> normalize this too to follow this rule:
> >
> > — convert 'y years m months' to the real number y*12 + m.
> >
> > — record truc( y*12 + m) in the "months" field of the internal
> representation
> >
> > — flow the remainder down to days (but no further)
> >
> > After all, you've bitten the bullet now and changed the behavior. This
> means that the semantics of some extant applications will change. So... in
> for a penny, in for a pound?
>
> The docs now say:
>
> Field values can have fractional parts; for example, <literal>'1.5
> weeks'</literal> or <literal>'01:02:03.45'</literal>. The fractional
> --> parts are used to compute appropriate values for the next lower-order
> internal fields (months, days, seconds).
>
> meaning fractional years flows to the next lower internal unit, months,
> and no further. Fractional months would flow to days. The idea of not
> flowing past the next lower-order internal field is that the
> approximations between units are not precise enough to flow accurately.
>
> With my patch, the output is now:
>
> SELECT INTERVAL '3 years 10.241604 months';
> interval
> ------------------------
> 3 years 10 mons 7 days
>
> It used to flow to seconds.
>
> --
> 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.
>
>
Based on the results of more samples, I restore +1 to Bruce's latest patch.

Cheers