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

More specifically...

On Wed, Jun 29, 2016 at 7:34 AM, Michael Paquier <michael.paquier@gmail.com> wrote:

On Wed, Jun 29, 2016 at 7:43 PM, Alex Ignatov <a.ignatov@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