Re: Have I found an interval arithmetic bug?

bruce(at)momjian(dot)us wrote:
>
> zyu(at)yugabyte(dot)com wrote:
>> Among previous examples given by Bryn, the following produces correct result based on Bruce's patch.
>>
>> # select interval '-1.7 years 29.4 months';
>> interval
>> ----------------
>> 9 mons 12 days
>
> Yes, that changed is caused by the rounding fixes, and not by the unit pushdown adjustments.

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?