Re: Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR - Mailing list pgsql-general

From Tom Lane
Subject Re: Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR
Date
Msg-id 4229.1220903199@sss.pgh.pa.us
Whole thread Raw
In response to Re: Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR  (Sam Mason <sam@samason.me.uk>)
List pgsql-general
Sam Mason <sam@samason.me.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

pgsql-general by date:

Previous
From: "Urciolo, Kevin"
Date:
Subject: Postgres 8.3.1 RowExclusiveLock With JDBC XA
Next
From: Tom Lane
Date:
Subject: Re: Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR