John Keith Hohm <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general