Fun with random

[nataraj]

My colleague Alex_Ignatov@facebook.com found curious behavior of random():

postgres=# select (select random() as value ) from generate_series(1,10) as k;
       value       
-------------------
 0.715956253930926
 0.715956253930926
 0.715956253930926
 0.715956253930926
 0.715956253930926
 0.715956253930926
 0.715956253930926
 0.715956253930926
 0.715956253930926
 0.715956253930926
(10 rows)

Something like

int rnd=5; // This figure is really random. Got it by rolling a die

;-)

Comments

[alhashash mohammad (from livejournal.com)]

You should use:
select (select random() + i*0 as value ) from generate_series(1,10) as k(i);

In your example, the planer decides to optimize the subquery by evaluating it only once as it does depend on the outer query rows. By injecting the fake dependency, the planner has to evaluate it for each row.

I got confused by the same issue before until I got this answer on StackOveflow:
http://stackoverflow.com/a/11648371/1332370 (http://stackoverflow.com/a/11648371/1332370)

[alhashash mohammad (from livejournal.com)]

select (select random() + i*0 as value ) from generate_series(1,10) as k(i);

In your example, the planer decides to optimize the subquery by evaluating it only once as it does depend on the outer query rows. By injecting the fake dependency, the planner has to evaluate it for each row.

I got confused by the same issue before until I got this answer on StackOveflow:
stackoverflow.com/a/11648371/1332370

[(Anonymous)]

I do something like that for better random :-)

~# select random(), generate_series(1,10) as k;
random | k
-------------------+----
0.707322615664452 | 1
0.45797775266692 | 2
0.407297918107361 | 3
0.954678415786475 | 4
0.582185364793986 | 5
0.145706068258733 | 6
0.503531654831022 | 7
0.183244552928954 | 8
0.364730811677873 | 9
0.988858019001782 | 10
(10 rows)

[astarsan.livejournal.com]

Этой баго-фиче 100 лет в обед. ;)
А еще интересно попробовать 2 generate_series разных длин в select list засунуть и посмотреть на результаты и на их количество
(что то типа
select generate_series(1,3), generate_series(1,2);
и
select generate_series(1,3), generate_series(1,6);
)

[shaplov.livejournal.com]

-- ...исполняется впервые!
-- Какое впервые? 150 лет уже играют!
-- _Мною_ -- впервые!

[timka21213.livejournal.com]

randomize it!

[aceler.livejournal.com]

> Got it by rolling a die

Ага, прокатил смерть