On Mon, 08 Sep 2008 13:53:03 -0400 Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > 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). Thanks for telling me about LIMIT ALL being an optimization fence. I believe you that it is not a bug, but I admin I cannot find any documentation of that entitlement. The description of sub-selects in the SELECT statement documentation suggests that a sub-select is equivalent to creating a temporary table with the inner select for the duration of that statement, and clearly that would not have the same effect. And the documentation of LIMIT ALL suggests it is optional noise. http://www.postgresql.org/docs/8.3/interactive/sql-select.html: A sub-SELECT can appear in the FROM clause. This acts as though its output were created as a temporary table for the duration of this single SELECT command. [...] http://www.postgresql.org/docs/8.3/interactive/queries-limit.html: [...] LIMIT ALL is the same as omitting the LIMIT clause. Is there a SQL standard document that gives permission for various optimizations, and the PostgreSQL documentation contains some technically inaccurate simplifications for exposition? > 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? Sorry, in minimizing my example to demonstrate the problem I made it appear pointless; the actual query that motivated the discussion is: select * from ( select * from Inquiry where nullif(trim(Member_Nbr), '') is not NULL and trim(trim(Member_Nbr), '0123456789') = '' -- and I've added LIMIT ALL here to make it work ) as valid_Inquiry where Member_Nbr::integer not in ( select Member_Nbr::integer from Member ); ...which is part of the conversion process for data dumped from an Access database. -- John Keith Hohm <john@xxxxxxxx>
