On 29.06.2016 15:30, David G. Johnston wrote:
More specifically...
On Wed, Jun 29, 2016 at 7:34 AM, Michael Paquier <michael.paqu...@gmail.com <mailto:michael.paqu...@gmail.com>>wrote:

    On Wed, Jun 29, 2016 at 7:43 PM, Alex Ignatov
    <a.igna...@postgrespro.ru <mailto: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)

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

Reply via email to