RE: SQL RAND function question

I think you have found a very interesting bug in the rand() function! I guess you should have the honor to report it to IBM.

Very interesting finding. Although I agree that it is rather uncommon, given a large enough sample, this could break your programs.

Thank you, Loyd!

luis


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of lgoodbar@xxxxxxxxxxxxxx
Sent: Wednesday, February 13, 2008 10:34 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL RAND function question

Hi Luis,

Changing to floor() made no appreciable difference. Here is the revised
program and results. The workaround I see so far is to calculate 0-12,
but use only 0-11. The results for the topmost number are so small they
would not skew the distribution.

d x s 10i 0 inz
/free
exec sql set option commit=*none;
for x = 1 to 10000;
exec sql insert into loyd/randno (number)
values(floor(rand()*11));
endfor;
*inlr = *on;
return;
/end-free

10,000:
....+....1....+....2....+....3
NUMBER COUNT ( * )
0 931
1 934
2 922
3 913
4 906
5 896
6 908
7 934
8 851
9 920
10 884
11 1
******** End of data ********

100,000:
....+....1....+....2....+....3
NUMBER COUNT ( * )
0 9,196
1 9,045
2 8,989
3 9,204
4 9,174
5 8,925
6 9,169
7 9,122
8 9,162
9 8,921
10 9,091
11 2
******** End of data ********

1,000,000:
....+....1....+....2....+....3
NUMBER COUNT ( * )
0 90,792
1 91,277
2 90,710
3 91,019
4 90,531
5 91,222
6 90,865
7 90,754
8 90,962
9 90,898
10 90,947
11 23
******** End of data ********


Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Colorado
Sent: Wednesday, February 13, 2008 09:09
To: Midrange Systems Technical Discussion
Subject: RE: SQL RAND function question

Loyd,

This is a bit surprising. Every implementation that I have seen of
rand() or rnd() in other languages gives a random number in the
following range: [0,1)

I would say that maybe there is a bug in the cast to int, which
apparently rounds up to 1 the number 0.99999999... at some unknown
precision.

Would you try this again, using floor() instead of the cast? Something
like

exec sql insert into loyd/randno (number)
values(floor(rand()*11));

It would be interesting to see if the problem is the cast or the rand()
function itself.

Great job!

Luis