More specifically... On Wed, Jun 29, 2016 at 7:34 AM, Michael Paquier <michael.paqu...@gmail.com> wrote:
> On Wed, Jun 29, 2016 at 7:43 PM, Alex Ignatov <a.igna...@postgrespro.ru> > wrote: > > Hello! > > > > Got some strange behavior of random() function: > > > > postgres=# select (select random() ) from generate_series(1,10) as i; > > random > > ------------------- > > 0.831577288918197 > > [...] > > (10 rows) > > I recall that this is treated as an implicit LATERAL, meaning that > random() is calculated only once. > A non-correlated (i.e., does not refer to outer variables) subquery placed into the target-list need only have its value computed once - so that is what happens. The fact that a volatile function can return different values given the same arguments doesn't mean much when the function is only ever called a single time. > > postgres=# select (select random()+i*0 ) from generate_series(1,10) as i; > > ?column? > > -------------------- > > 0.97471913928166 > > [...] > > (10 rows) > > But not that. So those results do not surprise me. > > A correlated subquery, on the other hand, has to be called once for every row and is evaluated within the context supplied by said row. Each time random is called it returns a new value. Section 4.2.11 (9.6 docs) https://www.postgresql.org/docs/9.6/static/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES Maybe this could be worded better but the first part talks about a single execution while "any one execution" is mentioned in reference to "the surrounding query". I do think that defining "correlated" and "non-correlated" subqueries within this section would be worthwhile. David J.
