Обсуждение: Bug #484: TIMESTAMP arithmetic insconsistencies

Rick Mason (mp@grymmjack.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
someonecan 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@postgresql.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