Re: Have I found an interval arithmetic bug?

> On 27-Jul-2021, at 14:13, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Tue, Jul 27, 2021 at 04:01:54PM -0400, Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>> I went ahead and modified the interval multiplication/division functions
>>> to use the same logic as fractional interval units:
>>
>> Wait. A. Minute.
>>
>> What I think we have consensus on is that interval_in is doing the
>> wrong thing in a particular corner case. I have heard nobody but
>> you suggesting that we should start undertaking behavioral changes
>> in other interval functions, and I don't believe that that's a good
>> road to start going down. These behaviors have stood for many years.
>> Moreover, since the whole thing is by definition operating with
>> inadequate information, it is inevitable that for every case you
>> make better there will be another one you make worse.
>
> Bryn mentioned this so I thought I would see what the result looks like.
> I am fine to skip them.
>
>> I'm really not on board with changing anything except interval_in,
>> and even there, we had better be certain that everything we change
>> is a case that is certainly being made better.
>
> Well, I think what I had before the multiply/divide changes were
> acceptable to everyone except Bryn, who was looking for more
> consistency.
>
>> BTW, please do not post patches as gzipped attachments, unless
>> they're enormous. You're just adding another step making it
>> harder for people to look at them.
>
> OK, what is large for you? 100k bytes? I was using 10k bytes.

Before I say anything else, I’ll stress what I wrote recently (under the heading “summary”). I support Tom’s idea that the only appropriate change to make is to fix only the exactly self-evident bug that I reported at the start of this thread.

I fear that Bruce doesn’t understand my point about interval multiplication (which includes multiplying by a number whose absolute value lies between 0 and 1). Here it is. I believe that the semantics are (and should be) defined like this:

[mm, dd, ss]*n == post_spilldown([mm*n, dd*n, ss*n])

where the function post_spilldown() applies the rules that are used when an interval literal that specifies only values for months, days, and seconds is converted to the internal [mm, dd, ss] representation—where mm and dd are 4-byte integers and ss is an 80byte integer that represents microseconds.

Here’s a simple test that’s consistent with that hypothesis:

with
c1 as (
select
'1 month 1 day 1 second'::interval as i1,
'1.234 month 1.234 day 1.234 second'::interval as i3),

c2 as (
select i1*1.234 as i2, i3 from c1)

select i2::text as i2_txt, i3::text from c2 as i3_txt;

Here’s the result:

i2_txt | i3
---------------------------+---------------------------
1 mon 8 days 06:05:46.834 | 1 mon 8 days 06:05:46.834

So I’m so far happy.

But, like I said, I’d forgotten a orthogonal quirk. This test shows it. It’s informed by the fact that 1.2345*12.0 is 14.8140.

select
('1.2345 years' ::interval)::text as i1_txt,
('14.8140 months'::interval)::text as i2_txt;

Here’s the result:

i1_txt | i2_txt
---------------+--------------------------------
1 year 2 mons | 1 year 2 mons 24 days 10:04:48

It seems to be to be crazy behavior. I haven’t found any account of it in the PG docs. Others have argued that it’s a sensible result. Anyway, I don’t believe that I’ve ever argued that it’s a bug. I wanted only to know what rationale informed the design. I agree that changing the behavior here would be problematic for extant code.

This quirk explains the outcome of this test:

select
('1.2345 years'::interval)::text as i1_txt,
('14.8140 months'::interval)::text as i2_txt,
(1.2345*('1 years'::interval))::text as i3_txt;

This is the result:

i1_txt | i2_txt | i3_txt
---------------+--------------------------------+--------------------------------
1 year 2 mons | 1 year 2 mons 24 days 10:04:48 | 1 year 2 mons 24 days 10:04:48

Notice that the same text is reported for i2_txt as for i3_txt.