Re: Invalid optimization of VOLATILE function in WHERE clause? - Mailing list pgsql-hackers

Florian.Schoppmann@emc.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.
        regards, tom lane