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

On Mon, Sep 08, 2008 at 01:53:03PM -0400, Tom Lane wrote:
> John Keith Hohm <john(at)hohm(dot)net> writes:
> > This fails with ERROR: invalid input syntax for integer: "JOHN":
> > select * from (
> > select * from (VALUES ('100'), ('JOHN')) as A (n)
> > where trim(trim(n), '0123456789') = ''
> > ) as B where n::integer <> -1;
>
> This isn't a bug: the optimizer is entitled to rearrange WHERE clauses
> any way it pleases. If you want an optimization fence between the inner
> and outer SELECTS, add OFFSET 0 (or LIMIT ALL if you like).

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.

I don't think PG is doing anything incorrect, it's just that there's
nothing in the language that I know of that would allow you to express
what is really intended.

Or have I missed the point entirely?

A hacky fix would be something like the following:

SELECT * FROM (
SELECT * FROM (VALUES ('100'), ('JOHN')) AS A (n)
WHERE trim(trim(n), '0123456789') = '') AS B
WHERE (n||'')::INTEGER <> -1;

It works because the cast is applying to a new expression and isn't
being interpreted as a type declaration.

Sam