The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
26.6k418575824
accept rate: 21%

edited 26 Feb '12, 15:03

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644

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.6k114149237
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:

×101
×4

question asked: 26 Feb '12, 09:36

question was seen: 2,396 times

last updated: 28 Feb '12, 03:02