Boolean error

Hello,

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. This behaviour doesn't
appear when we don't join the boolean expression to a table to have
multiple lines. It is also seems to be independent of the selected
table.

Could you enlighten us about what is happening ?

Thanks !
--
Fabien Fournier

Fabien Fournier <fabien(at)cyriacrea(dot)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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> 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.)

Thinking aloud... Postgres could have a VOLATILE function attribute to
explicitly mark functions requiring special care.

The default could be sort of a best-guess
usually-volatile-but-sometimes-takes-liberties-when-convenient compromise.
Perhaps eventually warning on functions created without being explicitly
VOLATILE/STABLE/IMMUTABLE.

Well, I guess Postgres can't warn on a valid SQL function if it's interested
in spec conformance. But it could be an option to do so.

--
greg