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

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.

Either the query

SELECT * FROM table WHERE
timestamp_column < $1 + interval '1 hour' AND timestamp_column > $1

Does not work

The $1 value is a timestamp (like now() for instance).

But the query
SELECT * FROM table WHERE timestamp_column = $1 OR (
timestamp_column > $1 - interval '1 hour'
AND timestamp_column < $1 + interval '1 hour')

works as expected.

It seems that the analyzer cannot guess the value of a prepared statement
combined with an interval. For my test i'm using PHP without PDO.

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

I think that the problem is on the type inference algorithm.

Starting from:

typeof(X) = timestamp
typeof(Y) = ?
typeof(Z) = interval
typeof(Y op Z) = ? [timestamp/interval]

The inference algorithm starts from the right

X > Y + Z => X > ? + interval [here the algorithm has two choices,
timestamp or interval, he chooses interval]
timestamp + interval => error

but starting from the left side:

X > Y + Z => timestamp > Y + Z [the only way to resolve the inference is to
assign the type timestamp to Y + Z and resolve the type of Y to timestamp]
timestamp > timestamp + interval => timestamp > timestamp => timestamp

I don't know if this is a design choice or a side effect, probably the
first.

---
Stefano

2012/3/12 Robert Haas <robertmhaas(at)gmail(dot)com>

> 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
>