|
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;
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;
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 |
|
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:
So as you see, problem still in investigation stage ;) ...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 ;-) 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
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
|
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...
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.