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%20Carter's gravatar image

Breck Carter
25.7k427586845
accept rate: 20%

edited 26 Feb '12, 15:03

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659

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 ;)

permanent link

answered 26 Feb '12, 12:18

AlexeyK77's gravatar image

AlexeyK77
70761224
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 ;-)

permanent link

answered 27 Feb '12, 07:39

Martin's gravatar image

Martin
8.6k116150237
accept rate: 14%

edited 28 Feb '12, 03:02

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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:

×102
×4

question asked: 26 Feb '12, 09:36

question was seen: 2,512 times

last updated: 28 Feb '12, 03:02