Re: BUG #6512: Bug with prepared statement and timestamp + interval

On Sat, Mar 3, 2012 at 7:47 PM, <stefano(dot)baccianella(at)gmail(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6512
> Logged by:          Stefano Baccianella
> Email address:      stefano(dot)baccianella(at)gmail(dot)com
> PostgreSQL version: 9.1.1
> Operating system:   Windows 7 64bit
> Description:
>
> When trying to execute a query like:
>
> SELECT * FROM table WHERE
> timestamp_column > $1
> AND timestamp_column < $1 + interval '1 hour'
>
> There is no problems, but when executing
>
> SELECT * FROM table WHERE
> timestamp_column > $1 - interval '1 hour'
> AND timestamp_column < $1 + interval '1 hour'
>
> The database return a error saying the the operator timestamp > interval
> does not exist.

This appears to be a type resolution problem. I find that it doesn't
matter whether I compare timestamp_column to $1 using > or <, nor does
it matter whether I use + to add an interval or - to subtract one.
However, if the first reference to $1 in the query is a direct
comparison against timestamp_column, then everything is fine; if the
first reference involves additional or subtraction of an interval,
then it breaks.

Here's what I think is happening: when PostgreSQL sees $1 + interval
'1 hour' first, it concludes that $1 must be intended to be an
interval, so that $1 + interval '1 hour' is also an interval, and that
can't be compared to the timestamp column, so it errors out. But when
it sees timestamp_column > $1 first, it concludes that $1 must be
intended to be a timestamp. After that, when it subsequently sees $1
+ interval '1 hour', it's already decided that $1 is a timestamp, so
it uses the timestamp + interval operator here rather than interval +
interval; that yields a timestamp, so everything's fine.

The right fix here is probably to explicitly specify the types you
want for the parameters, rather than making PostgreSQL guess. That
is, instead of saying:

PREPARE x AS SELECT * FROM foo WHERE timestamp_column > $1 - interval
'1 hour' AND timestamp_column < $1 + interval '1 hour'

Instead do:

PREPARE x (timestamp) AS SELECT * FROM foo WHERE timestamp_column > $1

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company