Professional Web Applications Themes

select rand() returning unexpected results - MySQL

Hi, I've written a small program to return a random word in a database dependent on an id. The ids in the table are 0..n, consecutive and non-repeating (I've double-checked). To implement this, I tried SELECT word FROM nouns WHERE id=FLOOR(RAND()*{table row count}); I see that there's a bug in this in any case, since RAND() returns a number 0 >= n >= 1. If it returns 1, which it is bound to do at some point, it'll return the number of rows, which doesn't exist as an id in the table. This is a small problem and isn't the ...

  1. #1

    Default select rand() returning unexpected results

    Hi,

    I've written a small program to return a random word in a database
    dependent on an id. The ids in the table are 0..n, consecutive and
    non-repeating (I've double-checked). To implement this, I tried

    SELECT word FROM nouns WHERE id=FLOOR(RAND()*{table row count});

    I see that there's a bug in this in any case, since RAND() returns a
    number 0 >= n >= 1. If it returns 1, which it is bound to do at some
    point, it'll return the number of rows, which doesn't exist as an id in
    the table. This is a small problem and isn't the subject of this post.

    The problem is that executing the statement often returns an unexpected
    number of records. Each id only appears once in the table, so I'd
    think that only one record would be returned for each call. Instead,
    most often between 0..2 rows are returned, and sometimes more.

    I'd be interested if someone could give me insight into this behavior,
    as it seems counter-intuitive to me.

    Thanks,

    Walter Gildersleeve
    Freiburg, Germany

    P.S. Here's the table definition:

    word varchar(255)
    id smallint(6) PRI 0 (indexed)

    The table has 27,166 records. I tried this on "Ver 14.7 Distrib
    4.1.16, for Win32 (ia32)" and "Ver 14.7 Distrib 4.1.20, for
    redhat-linux-gnu (i686) using readline 4.3".

    Here's a page that demonstrates the problem:

    http://devhed.com/perform_query.plx

    ----------------------------
    To e-mail me directly, simply reverse the characters in the e-mail left
    of the at sign.

    Walter Guest

  2. #2

    Default Re: select rand() returning unexpected results

    Walter G. wrote: 

    I think the short answer is that the WHERE expression, hence RAND(), is
    being independently evaluated for each row.

    Try:
    SELECT word FROM nouns ORDER BY RAND() LIMIT 1;
     

    toby Guest

  3. #3

    Default Re: select rand() returning unexpected results

    Okay, that makes sense. Of course the LIMIT won't work the way I need
    it, since often I get no results (not to mention the performance hit
    I'm taking of having to create 20K rands every time I do a simple
    search). Oh well.

    Thanks,

    Walter

    toby wrote:
     

    Walter Guest

  4. #4

    Default Re: select rand() returning unexpected results

    Walter G. wrote: [/ref]

    On my 5.0.28 this *always* returns a single row, which is what you
    want? (But I think ORDER BY RAND() may not work as expected in some
    older versions. What version are you using?)

    For speed, have you tried:

    SELECT COUNT(*) FROM nouns INTO cnt; -- or keep external count
    SELECT FLOOR(RAND()*cnt) INTO id; -- or generate externally
    SELECT word FROM nouns WHERE id=id;

    toby Guest

  5. #5

    Default Re: select rand() returning unexpected results


    toby wrote: [/ref]
    >
    > On my 5.0.28 this *always* returns a single row, which is what you
    > want? (But I think ORDER BY RAND() may not work as expected in some
    > older versions. What version are you using?)[/ref]

    Oops, I see, you said 4.1.16. According to 4.1 manual, the above should
    work, returning one row every time. The manual also explains why WHERE
    RAND() fails:
    http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html#id2978318
     

    toby Guest

  6. #6

    Default Re: select rand() returning unexpected results

    toby wrote:
     

    I had seen that--my mistake was in misunderstanding how often the
    "where" clause is reevaluated. I assumed it was evaluated once per
    search, not once per row.
     [/ref]

    I've decided to produce the random indexes externally--that seems like
    the fastest method and is easier than the multiple select statements
    necessary to get MySQL to do it for me.

    Thanks,

    Walter

    Walter Guest

Similar Threads

  1. Replies: 13
    Last Post: January 29th, 03:03 PM
  2. [PHP] unexpected results using sprintf() with %u
    By Tom in forum PHP Development
    Replies: 3
    Last Post: October 3rd, 07:14 AM
  3. [PHP] unexpected date results
    By Martin Towell in forum PHP Development
    Replies: 3
    Last Post: August 21st, 10:15 AM
  4. Replies: 1
    Last Post: June 26th, 11:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139