Re: Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR

Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
> I'd interpret John's note as pointing out that SQL doesn't distinguish
> between type declarations and type casting. I think he wants A.n to
> be of type TEXT, would like to temporarily treat it as INTEGER in one
> sub-expression. PG incorrectly propagates this cast as applying to the
> whole query, leading to John getting his confusing error message.

No, that's not correct at all. John had diagnosed the problem
correctly: the planner was reordering the two separate WHERE conditions
in such a way that the run-time cast to integer was attempted before the
condition that would have eliminated the values for which the cast
fails. (I believe that the reason is that for conditions that end up in
the same plan node, the planner tries to push the more expensive ones to
the end of the list; so his example with an extra trim() call works
differently, having three operations to the other condition's two.)

regards, tom lane