Strange behavior of some volatile function like random(), nextval() - Mailing list pgsql-hackers

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

</pre>