On 2015-08-27 04:50 PM, Scott Hess wrote: > I keep thinking I remember a thread from years ago where a lot of this was > hashed out, but I cannot find it. > //....// > There is already some precedent for this, because ORDER BY RANDOM() must > internally be holding the random values used fixed during the course of the > sort, rather than regenerating them each time it looks the value up.
Firstly, I agree on the broad content, but just to point out, the last statement is not quite true, in that there is no need for SQLite or any other sorter to /MUST/ have a list of random values to sort with. The following statement is perfectly capable (as the comparator in a sort-engine) of sorting in a random order: if Random() > Random() then swap = 1 else if Random() < Random() then swap = -1 else swap = 0; There is no prerequisite for a list of any kind. (This is probably a close approximation of what happens inside SQLite when a column value contains simply a Random() function - but that is just a guess. Postgres however /MUST/ produce a list, we know this because any actual sort-order would be impossible on non pre-rendered Random function results).
