# How do I get ORDER BY to work with derived table involving RAND()?

 This question has been revised: This query works OK in 12.0.1.3298... ```SELECT random FROM ( SELECT RAND() AS random FROM sa_rowgenerator ( 1, 10 ) ) AS generated; random 0.49096550535921263 0.6572485722867998 0.3767544242445167 0.1116082775926256 0.8003214992584295 0.0034380364247774877 0.7830781912352323 0.19516009054852654 0.05564184908552181 0.1725575803651277 ``` It stops working properly when ORDER BY is added: ```SELECT random FROM ( SELECT RAND() AS random FROM sa_rowgenerator ( 1, 10 ) ) AS generated ORDER BY random; random 0.6829465509778571 0.6829465509778571 0.6829465509778571 0.6829465509778571 0.6829465509778571 0.6829465509778571 0.6829465509778571 0.6829465509778571 0.6829465509778571 0.6829465509778571 ``` Here is the previous version of this question: Subject: How do I stop Version 12 from caching (?) calls to RAND()? Here is an imperfect query that generates integers in the range 1 through 6 inclusive (the distribution is imperfect but that's not the point): ```SELECT @@VERSION, random AS "random number", COUNT(*) AS "occurrences" FROM ( SELECT CAST ( ROUND ( ( RAND() * 5 ) + 1, 0 ) AS INTEGER ) AS random FROM sa_rowgenerator ( 1, 1000000 ) ) AS generated GROUP BY random ORDER BY random; ``` Repeated runs in Version 12 generate 1,000,000 occurrences of one single value: ```@@VERSION,random number,occurrences '12.0.1.3298',5,1000000 @@VERSION,random number,occurrences '12.0.1.3298',2,1000000 ``` Repeated runs in Version 9 generate actual distributions: ```@@VERSION,random number,occurrences '9.0.2.3951',1,99814 '9.0.2.3951',2,199930 '9.0.2.3951',3,200285 '9.0.2.3951',4,199765 '9.0.2.3951',5,200569 '9.0.2.3951',6,99637 @@VERSION,random number,occurrences '9.0.2.3951',1,99926 '9.0.2.3951',2,200552 '9.0.2.3951',3,200022 '9.0.2.3951',4,199561 '9.0.2.3951',5,199462 '9.0.2.3951',6,100477 ``` asked 26 Feb '12, 09:36 Breck Carter 27.1k●456●622●894 accept rate: 21% Volker Barth 31.6k●322●465●678 No, I have no answer - just another question to the randomness of RAND() when used in a single query (and possibly with a derived table)... however, that's still unanswered:( FWIW: The empire of randomly cached results strikes back - cf. the comments to this answer... (26 Feb '12, 09:40) Volker Barth It's probably a feature. Same behavior in Version 11. Different (and completely bizarre) behavior in Version 10. The workaround in Version 12 appears to be to load up a temporary table with the million values, before attempting to GROUP BY etcetera. (26 Feb '12, 10:19) Breck Carter

 It's very OLD bug. Read my topic about RAND problem 7sep 2010 http://forums.sybase.com/cgi-bin/webnews.cgi?cmd=item-130455&multi=&group=sybase%2Epublic%2Esqlanywhere%2Egeneral&utoken=f82.4f4a6383.1804289383@temp:0_~2-dc7bc15b7bd86b9f05e200 For history purposes I am save and paste full thread there: NewsGroup: sybase.public.sqlanywhere.general Date: Sep 3,2010 10:23 pm Created by: Alexey Topic: rand() vs order by Hi! By results of small database competition in one blog was born my question. SA10/SA12 QUERY: select rand() as a from some_table order by a; generate dataset filled by random values, but this resultset not sorted. The same query in oracle, postgresql and mysql produce sorted resultset - they win competition. MSSQL even generate resultset with one constant random value :) Is it possible to get correct sorted by random value resultset from sa? THANKS! Created by: "HarryLai" i have not test but i think you may try to use the derived table method to sort this result set; ```select rand_col ``` from ( ``` select rand_col = rand() from some_table ``` ) t1 order by rand_sol I have test deruved table method and result bring me a sorrow: rand_col 0.9977807826352216 0.9977807826352216 ... 0.9977807826352216 Created by: "Glenn Paulley [Sybase iAnywhere]" You're quite right, Volker - this behaviour is in error. We are investigating. Glenn Volker Barth wrote: I have confirmed this with SA 12 GA - and the wrongly repeated rows show up, too, when using the "select rand() as a from some_table" as a common table expression and then do an order by over the CTE. Seems to be a case of "over-optimization" a non-deterministic function and as such as a bug, methinks. Regards Volker So as you see, problem still in investigation stage ;) answered 26 Feb '12, 12:18 AlexeyK77 707●6●12●24 accept rate: 8% ...and as you will have noticed, the forum questions cited above (in my comment on Breck's question) surely do relate on that newsgroup thread as well...not by accident:) (26 Feb '12, 12:33) Volker Barth Thanks! ...the culprit appears to be ORDER BY. The original question has been revised. (26 Feb '12, 13:47) Breck Carter
 in 12.0.1.3537 it works for me with Order by, so it seems to be a solved problem. ```"random" ``` "0,0470741330865184" "0,0856765574243276" "0,158973901606618" "0,17495478511553" "0,325405228568895" "0,44083102719897" "0,465073436715209" "0,874364302435128" "0,891899742601393" "0,965900630674279" --------- Update --------------- It seemed to be client related, but is not! Now the solution of the miracle is, our tool for which the statement worked inserts a top into the select. So I tried it in dbisql with top and voila the result is ok. Try it yourself: ```SELECT top 500 random FROM ( SELECT RAND() AS random FROM sa_rowgenerator ( 1, 10 ) ) AS generated ORDER BY random; ``` This will yield the expected results. So based on this observation I think it is time to enter a bug report ;-) answered 27 Feb '12, 07:39 Martin 8.6k●119●152●237 accept rate: 14% Hmm, I'm not sure: Running with the newest Windows version 12.0.1.3554 does return the wrong result set, i.e. ten identical rows...at least on my box. Time to compare plans/database options? (27 Feb '12, 08:26) Volker Barth Replies hidden strange, I am using the database under windows. I have tested it under 11.0.1.2569 and 12.0.1.3537 with success. I have found the explanation: I also get 10 identical rows if I use dbisql to test the statement. If I use a different tool I get 10 different rows. So it seems to be more a problem of dbisql. (27 Feb '12, 09:25) Martin I've both used DBISQL and dbisqlc... are you saying "there are other tools"? (27 Feb '12, 09:50) Volker Barth Yes, we have a tool implemented in .net, that one returns the expected results. After your question I also tried a different legacy tool based on C++ it returns like dbisql the wrong results. Funny... (27 Feb '12, 10:17) Martin 1 Wow, result set depends from client! Very interesting, so "we need to Go Deeper"(c) in this problem :) (27 Feb '12, 11:16) AlexeyK77 "Client related" Now, that gives "random" a broader meaning - ask for a non-deterministic client? :) (27 Feb '12, 12:12) Volker Barth Does your tool pass the ORDER BY to the engine, or does it sort the result itself after receiving it? FWIW I just tested it via PowerBuilder 10.2.1.9004 and it exhibits the problem when the ORDER BY is included, but works OK when the ORDER BY is omitted. (27 Feb '12, 13:53) Breck Carter order by is passed to the engine (28 Feb '12, 02:45) Martin 1 Now i found it insert a top x into your statement and it is ok... (28 Feb '12, 03:02) Martin showing 2 of 9 show all flat view
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×103
×5

question asked: 26 Feb '12, 09:36

question was seen: 2,789 times

last updated: 28 Feb '12, 03:02