Why do these two queries produce different results?
vik=# select random(), random(), random() from generate_series(1, 5);
random | random | random
-------------------+-------------------+-------------------
0.47517032455653 | 0.631991865579039 | 0.985628996044397
0.341754949185997 | 0.304212234914303 | 0.545252074021846
0.684523592237383 | 0.595671262592077 | 0.560677206143737
0.352716268971562 | 0.131561728194356 | 0.399888414423913
0.877433629240841 | 0.543397729285061 | 0.133583522867411
(5 rows)
vik=# select random(), random(), random() from generate_series(1, 5)
order by random();
random | random | random
-------------------+-------------------+-------------------
0.108651491813362 | 0.108651491813362 | 0.108651491813362
0.178489942103624 | 0.178489942103624 | 0.178489942103624
0.343531942460686 | 0.343531942460686 | 0.343531942460686
0.471797252073884 | 0.471797252073884 | 0.471797252073884
0.652373222634196 | 0.652373222634196 | 0.652373222634196
(5 rows)
Obviously I'm not talking about the actual values, but the fact that
when the volatile function is put in the ORDER BY clause, it seems to
get called just once per row rather than each time like the first query.
Is this as designed? It's certainly unexpected, and my initial reaction
is undesirable.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support