Re: Unexpected interval comparison

Hello,

At Tue, 21 Mar 2017 07:52:25 -0700, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote in <375c9e5a-960f-942c-913f-55632a1f0a90(at)aklaver(dot)com>
> On 03/21/2017 07:42 AM, Tom Lane wrote:
> > Frazer McLean <frazer(at)frazermclean(dot)co(dot)uk> writes:
> >> I came across an unexpected comparison (tested on PostgreSQL 9.4 and
> >> 9.6) for intervals with a large difference in magnitude.
> >
> >> '1 year'::interval > '32618665 years'::interval;
> >
> >> Is this a bug?
> >
> > It looks like the problem is overflow of the result of
> > interval_cmp_value,
> > because it's trying to compute
> >
> > =# select '32618665'::int8 * 30 * 86400 * 1000000;
> > ERROR: bigint out of range
> >
> > It's not immediately obvious how to avoid that while preserving the
> > same
> > comparison semantics :-(

This is an apparent bug of interval comparison. During comparison
interval is converted into int64 in milliseconds but it overflows
in the case.

Detecting the overflow during the conversion can fix it and
preserving the semantics (except value range). The current code
tells a lie anyway for the cases but I'm not sure limting the
range of value is acceptable or not.

| =# select '106751990 days 24:59:59'::interval;
| interval
| -------------------------
| 106751990 days 24:59:59
| =# select '106751990 days 24:59:59'::interval > '1 year'::interval;
| ERROR: interval out of range during comparison

If this is not acceptable, some refactoring would be required.

> Not sure if it helps but this works:
>
> test=# select extract(epoch from '1 year'::interval) > extract(epoch
> from '32618665 years'::interval);
> ?column?
> ----------
> f

It calculates in seconds. So it is useful if subseconds are not
significant. But extract also silently overflows during
converting the same interval to usecs. This seems to need the
same amendment.

> =# select extract(usec from '32618665 years'::interval);
> date_part
> -----------
> 0

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center