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

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.