Inconsistent rand() results with MySQL Ruby (long)

ruby-talk
1

Using MySQL Ruby for a website, and trying to use the built-in RAND()
function to get a list of 10 links from a database. Now, on the
server in the command-line, it works just fine:

%mysql> select id from links order by rand() * 10 limit 10;
%±—+
%| id |
%±—+
%| 37 |
%| 26 |
%| 24 |
%| 46 |
%| 53 |
%| 43 |
%| 38 |
%| 23 |
%| 21 |
%| 58 |
%±—+
%10 rows in set (0.00 sec)

Great! But when I try and do this using Ruby and MySQL Ruby. . .

%links = my.query(‘select id from links order by rand() * 10 limit
10;’)
%
%links.each_hash do |row|
%print row[‘id’]
%print “
”
%end

produces:

9
37
10
18
36
22
29
50
11
21

Doesn’t look too bad, I know. The problem comes when I run it a
couple times. Here are some subsequent result sets:

9
10
49
22
53
40
59
11
29
35

48
53
9
10
20
49
30
23
24
13

9
54
50
10
60
11
58
29
32
25

She’s nay very random, Captain! Just to make sure it wasn’t MySQL
itself that was the problem, I went back into the command-line and got
the following:

9 |
10 |
22 |
19 |
30 |
41 |
26 |
11 |
34 |
45 |

25 |
30 |
14 |
46 |
51 |
40 |
20 |
19 |
35 |
57 |

58 |
29 |
38 |
59 |
50 |
13 |
46 |
33 |
39 |
55 |

None of which show the same pattern of repeating - at least from what
I see - 9, 10, and possibly 11 in every set that the MySQL Ruby
version shows. Am I going crazy, or is there something weird in the
way my queries are getting interpreted? Anyone else run into anything
like this with MySQL Ruby, or know how to fix it?

Thanks,
Dave Dembinski

2

Hallo Dave,

%links = my.query(‘select id from links order by rand() * 10 limit
10;’)
%
%links.each_hash do |row|
%print row[‘id’]
%print “
”
%end

I’ve tried with dbd and dbi-mysql
seems good.

require ‘dbi’
dbh = DBI.connect( ‘DBI:Mysql:db’, ‘user’, ‘pass’ )
dbh.execute(‘select usr_aid from user order by rand() limit 10;’) do |stm |
stm.fetch do | row |
print row[ ‘usr_aid’ ], “\n”
end
end

777
1055
568
870
652
215
514
44
89
954

1091
540
276
845
365
762
883
899
443
41

···

In message “Inconsistent rand() results with MySQL Ruby (long)” on 25.06.2003, Dave Dembinski thealmightydaev@hotmail.com writes:

–
Mit freundlichen Gruessen,
Wild Karl-Heinz
kh.wild at wicom.li

3

“Dave Dembinski” thealmightydaev@hotmail.com schrieb im Newsbeitrag
news:c67f8ee3.0306251214.295d28a8@posting.google.com…

Using MySQL Ruby for a website, and trying to use the built-in RAND()
function to get a list of 10 links from a database. Now, on the
server in the command-line, it works just fine:

%mysql> select id from links order by rand() * 10 limit 10;
%±—+
%| id |
%±—+
%| 37 |
%| 26 |
%| 24 |
%| 46 |
%| 53 |
%| 43 |
%| 38 |
%| 23 |
%| 21 |
%| 58 |
%±—+
%10 rows in set (0.00 sec)

Great! But when I try and do this using Ruby and MySQL Ruby. . .

%links = my.query(‘select id from links order by rand() * 10 limit
10;’)

I don’t know mysql but I’m sure ruby does not change the query in any way
that would affect this. Your probe is very small to be statistically
significant. I’d do an automated test, that does statistical evaluation
to verify the distribution.

A reason for different behavior could be the transaction handling: maybe
RAND is initialized for every transaction / connection and you had
different handling in ruby and on the command line. I’d vary this for the
tests to see whether it makes any difference.

The most likely source for problems is IMHO the implementation of RAND or
the handling of “oder by rand() * 10 limit 10”.

Regards

robert
4

[summary: ORDER BY RAND() * 10 doesn’t seem very random]

What’s your version of MySQL? A change was made in 3.23.56 to intialize
the randomizer better.

···

At 5:26 +0900 6/26/03, Dave Dembinski wrote:

5

Paul DuBois paul@snake.net wrote in message news:<p05210624bb20cc97d5c0@[192.168.0.34]>…

[summary: ORDER BY RAND() * 10 doesn’t seem very random]

What’s your version of MySQL? A change was made in 3.23.56 to intialize
the randomizer better.

Aha! I’ve 3.23.55. Well, I guess that explains part of the problem.
I want to thank everyone for helping, and it’s now working somewhat
the way I want it. Instead of relying on rand() by itself I’m passing
it a seed composed of a number generated by ruby’s rand() and the
current system time in seconds, and that’s giving me some comfortably
random results.

_dave

···

At 5:26 +0900 6/26/03, Dave Dembinski wrote: