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

On Mon, 08 Sep 2008 13:53:03 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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(at)hohm(dot)net>