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

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@gmail.com>

> On Sat, Mar 3, 2012 at 7:47 PM,  <stefano.baccianella@gmail.com> wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference:      6512
> > Logged by:          Stefano Baccianella
> > Email address:      stefano.baccianella@gmail.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
>