Re: Invalid optimization of VOLATILE function in WHERE clause?

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> It still seems like awfully weird behavior.
> 
> Why?  The WHERE condition relates only to the output of the _stats
> subquery, so why shouldn't it be evaluated there, rather than
> after the join?

In another thread, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It's easier to understand why this is if you realize that SQL has
> a very clear model of a "pipeline" of query execution. 
> Conceptually, what happens is:
> 
> 1. Form the cartesian product of the tables listed in FROM (ie,
> all combinations of rows).
> 
> 2. Apply the WHERE condition to each row from 1, and drop rows
> that don't pass it.
People expect that the results will be consistent with this model,
even if the implementation is optimized "under the covers".  I think
correct semantics should trump performance here.
-Kevin