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