I'm having trouble understanding why these two queries produce different results:
test=# select (select random()) from generate_series(1,10); -- rows are the same
?column?
-------------------
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
0.770797704812139
(10 rows)
test=# select (select random() where generate_series is not null) from generate_series(1,10); -- rows are different
?column?
--------------------
0.561828337144107
0.0275383000262082
0.290950470604002
0.281174722127616
0.530742571223527
0.617655908688903
0.169655770529062
0.428002137690783
0.442224354483187
0.203044794034213
(10 rows)
I understand that it's likely an optimisation thing -- postgres knows that the subselect doesn't depend on the FROM rows so it evaluates it only once, but is this really correct behaviour? Ideally, shouldn't postgres know that each invocation of random() produces different results and so decide that it should execute it for each row? If not, why?
Thanks!
--Royce