Re: Invalid optimization of VOLATILE function in WHERE clause?

On Wed, Sep 19, 2012 at 02:39:12PM -0500, Kevin Grittner wrote:
> 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
> 

It seems like this is what happens here except that the function is
evaluated once for the WHERE and not once per ROW. Both of these meet
the criterion for 2 above and Tom's earlier comments both hold.

Regards,
Ken