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

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)

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