Re: Strange behavior of some volatile function like random(), nextval()

More specifically...
On Wed, Jun 29, 2016 at 7:34 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:

> On Wed, Jun 29, 2016 at 7:43 PM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)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.
​