Thread: BUG #6512: Bug with prepared statement and timestamp + interval
BUG #6512: Bug with prepared statement and timestamp + interval
From
stefano.baccianella@gmail.com
Date:
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:=20=20=20=20=20=20=20=20 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 =3D $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.baccianella@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: =A0 =A0 =A06512 > Logged by: =A0 =A0 =A0 =A0 =A0Stefano Baccianella > Email address: =A0 =A0 =A0stefano.baccianella@gmail.com > PostgreSQL version: 9.1.1 > Operating system: =A0 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 --=20 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@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 >