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

John Keith Hohm <john@hohm.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'm also interested in responses of the form "why not just do X?".

It does raise the question of why you aren't just doing
    where trim(n) != '-1'
I'm also wondering whether the logic is even consistent: something
with a minus sign in it will never get through the inner WHERE,
so what is the point of the outer one?

            regards, tom lane