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)
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


Reply via email to