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.
