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.
