Richard Brinkman wrote: > When a perform the following query: > select random() as x from some_non_empty_table order by x desc limit 20; > I get something like: > -4348240540797173967 > -8823092517172356709 > 4237024158005380173 > 897958093325532613 > -6349939216731113298 > ... > which clearly is NOT in descending order.
Same query without some_non_empty_table: SELECT random() FROM (VALUES (0),(0),(0)) ORDER BY 1; -8895939407064619353 7039210299623844537 2630982943347068909 > Why is the order by clause not working in this case? EXPLAIN SELECT random() FROM(VALUES (0),(0),(0)) ORDER BY 1; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 0 0 00 1 InitCoroutine 1 9 2 00 2 Integer 0 2 0 00 3 Yield 1 0 0 00 4 Integer 0 2 0 00 5 Yield 1 0 0 00 6 Integer 0 2 0 00 7 Yield 1 0 0 00 8 EndCoroutine 1 0 0 00 9 SorterOpen 1 3 0 k(1,B) 00 10 InitCoroutine 1 0 2 00 11 Yield 1 17 0 00 12 Function 0 0 5 random(0) 00 13 Function 0 0 4 random(0) 00 14 MakeRecord 4 2 6 00 15 SorterInsert 1 6 0 00 16 Goto 0 11 0 00 17 OpenPseudo 2 7 3 00 18 SorterSort 1 23 0 00 19 SorterData 1 7 2 00 20 Column 2 1 5 00 21 ResultRow 5 1 0 00 22 SorterNext 1 19 0 00 23 Halt 0 0 0 00 The ORDER BY clause _is_ working, but it sorts the results of _another_ call to random(). Just because the ORDER BY clause refers to a column of the SELECT clause does not mean that the value is not computed a second time. Regards, Clemens
