Fabien Fournier <fabien@cyriacrea.net> writes:
> We are experiencing a strange error with the following request :
> select cmp
> from (select random () < 0.5 as cmp from pg_type) as tmp
> where cmp;
> With this request even false results are shown.
I think the planner will flatten this into
select random() < 0.5 as cmp from pg_type where random() < 0.5;
and of course the two random() calls will usually produce different
results.
There was some talk awhile ago of preventing flattening when the
subquery targetlist contains volatile functions, but we didn't
have any consensus that the cure would be better than the disease.
(In particular, since user-defined functions default to being
considered volatile, such a restriction could easily cripple
optimization of subqueries.)
regards, tom lane