Re: Have I found an interval arithmetic bug? - Mailing list pgsql-hackers

> On 27-Jul-2021, at 14:13, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Tue, Jul 27, 2021 at 04:01:54PM -0400, Tom Lane wrote:
>> Bruce Momjian <bruce@momjian.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
theonly 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
whoseabsolute 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
formonths, days, and seconds is converted to the internal [mm, dd, ss] representation—where mm and dd are 4-byte
integersand 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
sensibleresult. Anyway, I don’t believe that I’ve ever argued that it’s a bug. I wanted only to know what rationale
informedthe 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.