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
