Bug #484: TIMESTAMP arithmetic insconsistencies

Rick Mason (mp(at)grymmjack(dot)com) reports a bug with a severity of 4
The lower the number the more severe it is.

Short Description
TIMESTAMP arithmetic insconsistencies

Long Description
I have found some inconsistencies relating to TIMESTAMP arithmetic. I am not sure if this is a bug, but perhaps someone can give me a hint as to what is happening.

I have verified my results on these two systems:
PostgreSQL 7.1.2 on i386--freebsd4.3, compiled by GCC 2.95.3
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96

(any differences in version are noted below)

First try these queries:

select now()-'2001-09-30';
> "16 days xx:xx:xx"

select now()-'2001-09-30' where (now()-'2001-09-30') < 50.0;
> "16 days xx:xx:xx"

select now()-'2001-09-30' where (now()-'2001-09-30') < '50 days';
> "16 days xx:xx:xx"

select now()-'2001-09-30' where (now()-'2001-09-30') < 50;
> returned 0 rows

select now()-'2005-09-30';
> "-1444 days -xx:xx:xx"

select now()-'2005-09-30' where (now()-'2005-09-30') < 50.0;
> "-1444 days -xx:xx:xx"

select now()-'2005-09-30' where (now()-'2005-09-30') < '50 days';
> "-1444 days -xx:xx:xx"

select now()-'2005-09-30' where (now()-'2005-09-30') < 50;
> "-1444 days -xx:xx:xx"

Now is where things get a little wierd, create a table such as:
CREATE TABLE tryme (invoice_date TIMESTAMP);
now fill the table with alot of dates, including some in the past and some in the future
(my source data is ~1000 rows, 99% of which have 00:00:00 in the time part of the time field)

select now()-invoice_date from tryme order by (now()-invoice_date);
> returns all intervals (positive and negative)

select now()-invoice_date from tryme where (now()-invoice_date)<50 order by (now()-invoice_date);
> returns all negative intervals only

select now()-invoice_date from tryme where (now()-invoice_date)<50. order by (now()-invoice_date);
> returns intervals (<50 days OR >100 days) (including negatives)

select now()-invoice_date from tryme where (now()-invoice_date)<60. order by (now()-invoice_date);
> returns intervals (<60 days OR >100 days) (including negatives)

select now()-invoice_date from tryme where (now()-invoice_date)<120. order by (now()-invoice_date);
>7.1.2 returns intervals (>100 days AND <120 days) (including negatives)
>7.1.3 returns intervals (>100 days AND <120 days) (excluding negatives)

select now()-invoice_date from tryme where (now()-invoice_date)>50 order by (now()-invoice_date);
> returns all positive intervals only

select now()-invoice_date from tryme where (now()-invoice_date)>50. order by (now()-invoice_date);
> returns intervals ((>50 days AND < 100 days) OR >500 days)

select now()-invoice_date from tryme where (now()-invoice_date)>120. order by (now()-invoice_date);
>7.1.2 returns intervals (<100 days OR >120 days) (excluding negatives)
>7.1.3 returns intervals (<100 days OR >120 days) (including negatives)

Now if you replace the numbers with an interval string (example: 50 to '50 days') then all of the queries work fine.

Sample Code

No file was uploaded with this report

pgsql-bugs(at)postgresql(dot)org writes:
> I have found some inconsistencies relating to TIMESTAMP arithmetic.

I dug into this a little, and what seems to be causing the inconsistency
is a surprising implicit coercion.

> select now()-'2001-09-30' where (now()-'2001-09-30') < 50;
>> returned 0 rows

I did this to see how the parser interpreted this expression:

regression=# create view v as select (now()-'2001-09-30') < 50;
CREATE
regression=# \d v
View "v"
Column | Type | Modifiers
----------+---------+-----------
?column? | boolean |
View definition: SELECT (reltime((now() - '2001-09-30 00:00:00-04'::timestamptz)) < (50)::reltime);

Since now()-'2001-09-30' yields an interval, the choice of reltime to do
the comparison is not too surprising (apparently there's an
int4->reltime coercion but not int4->interval). Anyway the result is at
least somewhat sensible: the 50 gets interpreted as 50 seconds.

> select now()-'2001-09-30' where (now()-'2001-09-30') < 50.0;
>> "16 days xx:xx:xx"

regression=# drop view v;
DROP
regression=# create view v as select (now()-'2001-09-30') < 50.0;
CREATE
regression=# \d v
View "v"
Column | Type | Modifiers
----------+---------+-----------
?column? | boolean |
View definition: SELECT (text((now() - '2001-09-30 00:00:00-04'::timestamptz)) < '50'::text);

Text!!?? Well, apparently textlt is the only available operator whose
input types can be reached in one coercion step from both interval and
float8. But it seems a pretty surprising choice anyway. I wonder
whether we have too many implicit coercions to text available.

regards, tom lane