Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

This question has been revised:

This query works OK in

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

       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

@@VERSION,random number,occurrences

Repeated runs in Version 9 generate actual distributions:

@@VERSION,random number,occurrences

@@VERSION,random number,occurrences

asked 26 Feb '12, 09:36

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 26 Feb '12, 15:03

Volker%20Barth's gravatar image

Volker Barth

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

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


By results of small database competition in one blog was born my question.


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?


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




Created by: "Glenn Paulley [Sybase iAnywhere]"

You're quite right, Volker - this behaviour is in error. We are investigating.


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

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 it works for me with Order by, so it seems to be a solved problem.


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

accept rate: 14%

edited 28 Feb '12, 03:02

Hmm, I'm not sure:

Running with the newest Windows version does return the wrong result set, i.e. ten identical 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 and 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

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

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 26 Feb '12, 09:36

question was seen: 5,468 times

last updated: 28 Feb '12, 03:02