Volatile function weirdness

From: Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Volatile function weirdness
Date: 2019-05-02 09:04:52
Message-ID: 0c62c8e2-9307-d8f5-c22b-2f068abbff5a@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Julien Rouhaud 2019-05-02 09:11:32 Re: Volatile function weirdness
Previous Message Amit Kapila 2019-05-02 08:56:52 Re: Unhappy about API changes in the no-fsm-for-small-rels patch