br...@momjian.us wrote:
>
> z...@yugabyte.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?
