Hello!
Got some strange behavior of random() function:
postgres=# select (select random() ) from generate_series(1,10) as i;
random
-------------------
0.831577288918197
0.831577288918197
0.831577288918197
0.831577288918197
0.831577288918197
0.831577288918197
0.831577288918197
0.831577288918197
0.831577288918197
0.831577288918197
(10 rows)
postgres=# select (select random()+i*0 ) from generate_series(1,10) as i;
?column?
--------------------
0.97471913928166
0.0532126761972904
0.331358563620597
0.0573496259748936
0.321165383327752
0.48836630070582
0.444201893173158
0.0729857799597085
0.661443184129894
0.706566562876105
(10 rows)
postgres=# explain select (select random() ) from generate_series(1,10)
as i;
QUERY PLAN
--------------------------------------------------------------------------
Function Scan on generate_series i (cost=0.02..10.01 rows=1000 width=0)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0)
(3 rows)
postgres=# explain select (select random()+i*0 ) from
generate_series(1,10) as i;
QUERY PLAN
--------------------------------------------------------------------------
Function Scan on generate_series i (cost=0.00..30.00 rows=1000 width=4)
SubPlan 1
-> Result (cost=0.00..0.02 rows=1 width=0)
(3 rows)
postgres=# \df+ random();
List of functions
Schema | Name | Result data type | Argument data types | Type |
Security | Volatility | Owner | Language | Source code | Description
------------+--------+------------------+---------------------+--------+----------+------------+----------+----------+-------------+--------------
pg_catalog | random | double precision | | normal
| invoker | volatile | postgres | internal | drandom | random value
(1 row)
Also:
postgres=# create sequence test;
CREATE SEQUENCE
postgres=# SELECT (SELECT nextval('test')) FROM generate_series(1,10) as i;
nextval
---------
1
1
1
1
1
1
1
1
1
1
(10 rows)
postgres=# SELECT (SELECT nextval('test')+i*0) FROM
generate_series(1,10) as i;
?column?
----------
2
3
4
5
6
7
8
9
10
11
(10 rows)
postgres=# \df+ nextval() ;
List of functions
Schema | Name | Result data type | Argument data types | Type
| Security | Volatility | Owner | Language | Source code |
Description
------------+---------+------------------+---------------------+--------+----------+------------+----------+----------+-------------+---------------------
pg_catalog | nextval | bigint | regclass | normal
| invoker | volatile | postgres | internal | nextval_oid | sequence
next value
(1 row)
Both function is volatile so from docs :
"A VOLATILE function can do anything, including modifying the database.
It can return different results on successive calls with the same
arguments. The optimizer makes no assumptions about the behavior of such
functions. A query using a volatile function will re-evaluate the
function at every row where its value is needed."
Something wrong with executor? Is it bug or executor feature related
with subquery?
--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
