Re: Invalid optimization of VOLATILE function in WHERE clause?

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