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

[Breck_Carter]

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

[VolkerBarth]

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...

[Breck_Carter]

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.

[Former Member]

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 😉

[VolkerBarth]

...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:)

[Breck_Carter]

Thanks! ...the culprit appears to be ORDER BY.

The original question has been revised.

[MCMartin]

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 😉

[VolkerBarth]

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?

[MCMartin]

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.

[VolkerBarth]

I've both used DBISQL and dbisqlc... are you saying "there are other tools"?

[MCMartin]

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...

[Former Member]

Wow, result set depends from client! Very interesting, so "we need to Go Deeper"(c) in this problem 🙂

[VolkerBarth]

"Client related"

Now, that gives "random" a broader meaning - ask for a non-deterministic client? 🙂

[Breck_Carter]

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.

[MCMartin]

order by is passed to the engine

[MCMartin]

Now i found it insert a top x into your statement and it is ok...