Re: Using random() in update produces same random value for all

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Mon, Jan 22, 2018 at 9:16 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The point here is that that's an uncorrelated subselect --- ie, it
>> contains no outer references --- so it need not be, and is not,
>> re-evaluated at every outer row.

> That seems rather circular. Why shouldn't a volatile be honored as
> volatile just because it is in an uncorrelated sub-select?

It is honored as volatile: it will be re-evaluated every time the
sub-select is re-evaluated. It's just that there's no cause to
re-evaluate the sub-select.

I poked through the SQL standard to see if it spells out the semantics
of uncorrelated subqueries anywhere, and couldn't find anything relevant
at all. But this is how Postgres has understood the behavior of
sub-selects for a very long time (~20 years). I'm pretty certain
that there are people depending on it to behave this way.

regards, tom lane