Обсуждение: Using random() in update produces same random value for all

Yep, interesting.  Checked with PostgreSQL 10.1.
=> select *,random() from generate_series(1,10);
  generate_series |      random
-----------------+-------------------
                1 | 0.308531506918371
                2 | 0.126279713585973
                3 | 0.984668150078505
                4 | 0.884970095474273
                5 | 0.692738385871053
                6 | 0.290897831786424
                7 | 0.914066118188202
                8 | 0.031909613404423
                9 | 0.574441066011786
               10 | 0.631192437838763
(10 rows)
=> select *,(select * from random()) from generate_series(1,10);
  generate_series |       random
-----------------+--------------------
                1 | 0.0718352268449962
                2 | 0.0718352268449962
                3 | 0.0718352268449962
                4 | 0.0718352268449962
                5 | 0.0718352268449962
                6 | 0.0718352268449962
                7 | 0.0718352268449962
                8 | 0.0718352268449962
                9 | 0.0718352268449962
               10 | 0.0718352268449962
(10 rows)
=> select *,(select random()) from generate_series(1,10);
  generate_series |      random
-----------------+-------------------
                1 | 0.848611807450652
                2 | 0.848611807450652
                3 | 0.848611807450652
                4 | 0.848611807450652
                5 | 0.848611807450652
                6 | 0.848611807450652
                7 | 0.848611807450652
                8 | 0.848611807450652
                9 | 0.848611807450652
               10 | 0.848611807450652
(10 rows)


Looked like random() is "volatile", but in subselect it works like "stable".

Olleg Samoylov <splarv@ya.ru> writes:
> Looked like random() is "volatile", but in subselect it works like "stable".

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.

            regards, tom lane

Jeff Janes <jeff.janes@gmail.com> writes:
> On Mon, Jan 22, 2018 at 9:16 AM, Tom Lane <tgl@sss.pgh.pa.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

On 2018-01-22 23:15, Tom Lane wrote:
>
> 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

The cause exists, the function is volatile and according to definition 
it must be recalculated every time. But well, one more example.

=> select generate_series,(select random+generate_series from random()) 
from generate_series(1,10);
  generate_series |     ?column?
-----------------+------------------
                1 | 1.94367738347501
                2 | 2.94367738347501
                3 | 3.94367738347501
                4 | 4.94367738347501
                5 | 5.94367738347501
                6 | 6.94367738347501
                7 | 7.94367738347501
                8 | 8.94367738347501
                9 | 9.94367738347501
               10 |  10.943677383475
(10 rows)

As you can see, sub-select is indeed recalculated, but not random(). And 
this is may be right, because random() is used as source off data. 
Another example.

=> select generate_series,(select random()+generate_series) from 
generate_series(1,10);
  generate_series |     ?column?
-----------------+------------------
                1 | 1.37678202055395
                2 |  2.5316761219874
                3 | 3.33511888468638
                4 |  4.0293406387791
                5 | 5.69305071979761
                6 | 6.33374964864925
                7 | 7.14478175388649
                8 |  8.1831739502959
                9 |  9.4472619513981
               10 | 10.2977624684572
(10 rows)

Here random() is recalculated as sub-select.

But in

=> select *,(select random()) from generate_series(1,10);
  generate_series |      random
-----------------+-------------------
                1 | 0.487761380150914
                2 | 0.487761380150914
                3 | 0.487761380150914
                4 | 0.487761380150914
                5 | 0.487761380150914
                6 | 0.487761380150914
                7 | 0.487761380150914
                8 | 0.487761380150914
                9 | 0.487761380150914
               10 | 0.487761380150914
(10 rows)

is not.

IMHO all this behavior may be not bad, but it must be well documented in 
manual in section about sub-selects. All sub-select must be documented 
as "stable" in terms of function definition. And thus will not be surprise.