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

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'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