Re: Boolean error

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Fabien Fournier <fabien(at)cyriacrea(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Boolean error
Date: 2004-11-27 18:27:43
Message-ID: 27109.1101580063@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Uwe C. Schroeder 2004-11-27 18:28:21 Re: how many JOINs?
Previous Message Tom Lane 2004-11-27 18:21:09 Re: Could we hide the table name listing from unprivileged user?