Re: Have I found an interval arithmetic bug?

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Zhihong Yu <zyu(at)yugabyte(dot)com>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, Isaac Morland <isaac(dot)morland(at)gmail(dot)com>, John W Higgins <wishdev(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Tom Lane PostgreSQL <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Have I found an interval arithmetic bug?
Date: 2021-04-12 22:09:48
Message-ID: 8707105F-C45D-4ACB-8F71-51CE5BED58A5@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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?

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Wieck 2021-04-12 22:33:34 Re: The Amazon CloudFront distribution is configured to block access from your country.
Previous Message Virendra Kumar 2021-04-12 21:49:55 Re: Prepare Statement VS Literal Values

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2021-04-12 22:47:04 Re: Curious test case added by collation version tracking patch
Previous Message Thomas Munro 2021-04-12 22:08:07 Re: Curious test case added by collation version tracking patch