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

On 29.06.2016 15:30, David G. Johnston wrote:
> More specifically...
> On Wed, Jun 29, 2016 at 7:34 AM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com <mailto: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 <mailto: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.
> ​
>
>
In this subquery(below) we have reference to outer variables but it is
not working as it should(or i dont understand something):

postgres=# postgres=# select id, ( select string_agg('a','') from
generate_series(1,trunc(10*random()+1)::int) where id=id) from
generate_series(1,10) as id;
id | string_agg
----+------------
1 | aaa
2 | aaa
...
but this query(with reference to outer var) working perfectly:
postgres=# select id,(select random() where id=id) from
generate_series(1,10) as id;
id | random
----+--------------------
1 | 0.974509597290307
2 | 0.219822214450687
...

Also this query is working good( (id-id) do the job):
postgres=# select id, ( select string_agg('a','') from
generate_series(1,trunc(10*random()+1)::int+(id-id)) ) from
generate_series(1,10) as id;
id | string_agg
----+------------
1 | aaaaaaa
2 | aaaaa
...

It means that even reference to outer variables doesn't mean that
executor execute volatile function from subquery every time. Or there is
something else what i should know?

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company