Re: [BUGS][PATCH] BUG #14486: Inserting and selecting interval have different constraints

On 1/4/17, Pantelis Theodosiou <ypercube(at)gmail(dot)com> wrote:
> On Wed, Jan 4, 2017 at 3:03 PM, <web+postgresql(at)modin(dot)io> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 14486
>> Logged by: Per Modin
>> Email address: web+postgresql(at)modin(dot)io
>> PostgreSQL version: 9.6.1
>> Operating system: Linux 303a92173594 4.8.15-moby #1 SMP Sat Dec 17 0
>> Description:
>>
>> Found this bug in 9.4.8, tried it in docker towards psql 9.6.1 and it's
>> in
>> there as well. A minimum working example would be as follows:
>>
>> ```
>> postgres=# CREATE TABLE tbl AS SELECT 9223372036854 * interval '1 second'
>> col; TABLE tbl;
>> SELECT 1
>> ERROR: interval out of range
>> ```
>>
>> ```
>> postgres=# SELECT count(*) FROM tbl;
>> count
>> -------
>> 1
>> (1 row)
>> ```
>>
>> It seems that inserting and retrieving data have different constraints.
>> As
>> you
>> can see from query 2, the data still gets inserted.
>>
>> ```
>> postgres=# select version();
>> version
>> ------------------------------------------------------------
>> ------------------------------
>> PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian
>> 4.9.2-10)
>> 4.9.2, 64-bit
>> (1 row)
>> ```
>>
>> Best regards,
>> Per Modin
>>
>>
> And these work without error:
>
> postgres=# select col - 9223372036854 * interval '1 second' from tbl ;
> ?column?
> ----------
> 00:00:00
> (1 row)
>
> postgres=# select col from xx where col < interval '100000 year' ;
> col
> -----
> (0 rows)
>

Yes, it is a bug, but it is not a constraint, it is just different
internal checks.
Moreover even if a function does not raise an error, output could be wrong
(pay attention to the duplicated '-' sign)
postgres=# SELECT '-2147483647:59:59'::interval - '1s'::interval;
?column?
--------------------
--2147483648:00:00
(1 row)

I've written a patch which fixes that bug (in attachment).
Should it be registered in the CF?
--
Best regards,
Vitaly Burovoy