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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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