CODEWITHSUNDEEP
mysqlsql
zack_falcon
zack_falcon

Reputation: 4376

Why does my SQL statement sometimes return no results?

Given the following table:

enter image description here

We have the following SQL statement that returns a random row:

SELECT r1.id, game_desc, rarity, unlocks_prior 
FROM items_permanent AS r1 
JOIN (
    SELECT (RAND() * (SELECT MAX(id) FROM items_permanent)) AS id
) AS r2    
WHERE r1.id >= r2.id AND r1.unlocks_prior is Null 
ORDER BY r1.id ASC 
LIMIT 1

This works fine as it is, but when we need to randomize by rarity, and thus needed to changed the above into below:

SELECT r1.id, game_desc, rarity, unlocks_prior 
FROM items_permanent AS r1 
JOIN (
    SELECT (RAND() * (SELECT MAX(id) FROM items_permanent)) AS id
) AS r2 
WHERE r1.id >= r2.id AND r1.unlocks_prior is Null AND r1.rarity = ? 
ORDER BY r1.id ASC 
LIMIT 1

It sometimes returns no rows at all, with alarming regularity (I'd say, one in ten executions).

What causes this? And how could I prevent or fix it?

Upvotes: 0

Views: 777

Answers (2)

Bill
Bill

Reputation: 540

What I can see is you added below to the 2nd SQL

AND r1.rarity = ?

Take your data show in the image as example, assume you have ID 1 ~ 29, you will got nothing if you have r1.rarity = 2 with r2.id rolled as 28 (coz no rarity = 2 with ID 28-29)

This should be able to fix by changing below statement

SELECT (RAND() * (SELECT MAX(id) FROM items_permanent)) AS id

into

SELECT (RAND() * (SELECT MAX(id) FROM items_permanent WHERE rarity = ?)) AS id

Upvotes: 1

Barmar
Barmar

Reputation: 781380

Your query is picking a random number in the ID range, and then looking for the next row in the table that matches the conditions. If none of the rows after than random ID match the conditions, you won't get anything. You need to include the conditions when choosing the random ID as well.

SELECT r1.id, game_desc, rarity, unlocks_prior 
FROM items_permanent AS r1 
JOIN (
    SELECT RAND() * MAX(id) AS id
    FROM items_permanent
    WHERE unlocks_prior is Null AND rarity = ? 
) AS r2 ON r1.id >= r2.id
WHERE r1.unlocks_prior is Null AND r1.rarity = ? 
ORDER BY r1.id ASC 
LIMIT 1

Upvotes: 2

Related Questions