CODEWITHSUNDEEP
phpmysql
Jane Panda
Jane Panda

Reputation: 1671

Why would this MySQL query using rand() return no results about a third of the time?

I have a table of itemproperties that I want to grab a random itemproperty from, however I want the random-ness of it to lean more towards some itemproperties than others.

To accomplish this I've setup a column called rarity that stores how often that item should show up (higher = more often), and one called rarity_position, which is configured using this query:

SET @i:=0;UPDATE itemproperty SET rarity_position = @i:=@i+rarity;

This provides the following results:

rarity results

Then to grab a random value, I thought I would use:

SELECT * FROM itemproperty 
WHERE id = (
        SELECT id 
        FROM itemproperty 
        WHERE rarity_position >= FLOOR
                (
                    RAND()*
                    (
                        SELECT MAX(rarity_position) FROM itemproperty
                    )
                )
        ORDER BY rarity_position 
        ASC LIMIT 1
) LIMIT 1

In order to select a random number with a maximum of the highest rarity_position, then grab the itemproperty with a rarity_position that is just above that.

Most of the time this works as expected, however about a third of the time no results come back.

Possibly relevant server info:

Software: MySQL
Software version: 5.0.95-log - MySQL Community Server (GPL)
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)

You can reproduce the behaviour with this model :

create table itemproperty (
  id int, rarity int, rarity_position int
);
insert into itemproperty
values
  ( 1, 50, 50 ),
  ( 2, 50, 100 ),
  ( 3, 50, 150 ),
  ( 4, 50, 200 ),
  ( 5, 50, 250 ),
  ( 6, 50, 270 ),
  ( 7, 50, 320 ),
  ( 8, 50, 370 ),
  ( 9, 50, 420 ),
  ( 10, 50, 470 ),
  ( 11, 50, 520 )
;

If you try to do the subquery only, it works every time :

    SELECT id 
    FROM itemproperty 
    WHERE rarity_position >= FLOOR
            (
                RAND()*
                (
                    SELECT MAX(rarity_position) FROM itemproperty
                )
            )
    ORDER BY rarity_position 
    ASC LIMIT 1

But if you encapsulate it in :

SELECT * FROM itemproperty
WHERE id = (
 // here the above request
) LIMIT 1;

It will not work as expected.

What is causing it to return no results every time?

Upvotes: 2

Views: 221

Answers (2)

Jane Panda
Jane Panda

Reputation: 1671

Found a workable solution similar to zerkms method:

ORDER BY -LOG(1.0 – RAND()) / rarity

Source

Notably, the missing pieces seem like they might be related to this question: MySQL query with RAND() subquery condition

I guess having RAND() in WHERE causes some weirdness.

Upvotes: 0

zerkms
zerkms

Reputation: 254944

SELECT *
FROM itemproperty 
ORDER BY RAND() * rarity DESC
LIMIT 1

Upvotes: 2

Related Questions