Re: Invalid optimization of VOLATILE function in WHERE clause?

On Wed, Sep 19, 2012 at 10:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Florian(dot)Schoppmann(at)emc(dot)com (Florian Schoppmann) writes:
>> In PostgreSQL 9.1 and 9.2 (possibly also in earlier versions), the query
>
>> --8<--
>> WITH source AS (
>> SELECT i FROM generate_series(1,10) AS i
>> )
>> SELECT
>> i
>> FROM
>> source, (
>> SELECT
>> count(*) AS _n
>> FROM source
>> ) AS _stats
>> WHERE
>> random() < 5::DOUBLE PRECISION/_n;
>> -->8--
>
> [ doesn't do what you think it should ]
>
> I can't get excited about this. Any time you put a volatile function
> into WHERE, you're playing with fire. The docs warn against it:
> http://www.postgresql.org/docs/9.2/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
>
> To do what you want, I'd suggest wrapping the join into a sub-select
> with an "OFFSET 0" clause, which will serve as an optimization fence
> that prevents the random() call from being pushed down.

You've repeatedly objected to complaints on pgsql-performance on the
grounds that WITH is an optimization fence. It seems awfully
inconsistent to turn around and say, oh, sometimes it's not a fence
after all. It seems that users may not rely on WITH either to do the
optimizations necessary to have good performance or to fail to do
optimizations that lead to wrong results. Ouch.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company