Re: Bug in date arithmetic

On Mon, Aug 24, 2009 at 09:12:07AM -0700, David Fetter wrote:
> shackle(at)shackle:5432=# SELECT now() + '900000';
> ?column?
> -------------------------------
> 2009-09-03 19:03:43.195514-07
> (1 row)
>
> shackle(at)shackle:5432=# SELECT now() - '900000';
> ERROR: date/time field value out of range: "900000"
> HINT: Perhaps you need a different "datestyle" setting.
>
> I'd be tempted to call it a flat-out bug in the first case. Adding a
> string literal to a timestamptz should just result in an error, IMHO.

But you're not adding a string literal, you're adding an interval of
900000 seconds, or 250 hours! This is the weirdness resulting from the
lack of difference in syntax between string literals and other literals.
i.e.

SELECT timestamptz '2000-01-01';
=> 2000-01-01 00:00:00+00
SELECT timestamptz '2000-01-01' + '900000';
=> 2000-01-11 10:00:00+00
SELECT timestamptz '2000-01-01' + interval '900000';
=> 2000-01-11 10:00:00+00
SELECT timestamptz '2000-01-01' + 900000;
=> no operator matches (rhs is of type INT)
SELECT timestamptz '2000-01-01' + text '900000';
=> no operator matches (rhs is of type TEXT)

It seems to be deciding the unknown type is of type date with
subtraction for some reason:

SELECT timestamptz '2000-01-01' + '900000';
=> date/time field value out of range
SELECT timestamptz '2000-01-01' - date '900000';
=> date/time field value out of range

Whereas you were expecting it to be using an interval as before:

SELECT timestamptz '2000-01-01' - interval '900000';
=> 1999-12-21 14:00:00+00

A "timestamptz + date" operator doesn't exist though, so it wouldn't
wouldn't be able to pick it and is why you were seeing this odd
behavior.

Not sure what good fixes would be; a couple of simple (and bad) ones
would be:

1) assume literals are of type text, unless otherwise specified.

2) require the type of the literal to be specified if there is any
ambiguity

The second option is nicer, but defining "any ambiguity" doesn't seem
possible in general; worse, it would seem to change over time as
operators/types were added/removed and would cause things that used to
work to start breaking. Then again they would now, so I'm not sure why
this would be worse.

--
Sam http://samason.me.uk/