Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

Dave Cramer <pg@fastcrypt.com> writes:
> The crux of the problem is this:  ERROR: operator does not exist: timestamp
> with time zone >= interval
> which appears to be caused by trans_date between now() - $2 and now()

> What happens if you change it to :
> trans_date between (now() - $2) and now()

That shouldn't change the results...

I think the real issue here is what is the datatype of parameter $2.
It's apparently intended to be an interval (so that now() - interval
is a timestamp, and the comparison is meaningful.)  But the new system
is evidently taking $2 as a timestamp: the timestamp minus timestamp
operator yields an interval (which is unsurprising if you think about
what it means), and that leads to the reported error when the parser
tries to figure out what the BETWEEN means.

I would bet that in the old system $2 was explicitly marked as an interval
value, but in the new system $2 isn't getting marked with any particular
datatype.  Given "timestamp minus unknown", the parser will resolve
the unknown type as timestamp, the same as is on the other side of the
operator.  (Obviously, that heuristic doesn't work all the time, but
it's right much of the time.)

So I think this is a client-side behavioral change, and what you need to
look for is how the type of that parameter is getting set, or not.

            regards, tom lane