Why MySQL’s Unordered SELECT Can Skew Lottery Results – A Hidden Bug Explained

Two weeks ago a game designer reported that a prize with an expected 0.1% win rate was awarded to the same player four times in a row. After reviewing the code, a five‑year‑old bug was uncovered that stemmed from a MySQL ordering quirk.

Business Flow

When a player registers for the activity, a row is inserted into world_lottery containing activity_id, player_uuid, apply_no and other player data. An in‑memory counter apply_total_num tracks the total number of applicants and provides the next apply_no.

The reward structure is defined as a map, for example:

awards = {
    1: 1,
    2: 15,
    3: 50,
    ...
}

After registration closes, a random sequence of apply_no values is generated whose length equals the total number of rewards. If 10,000 players signed up and there are 1,000 rewards, the system might produce:

lucky_apply_nos = [234, 123, 1356, 8765, 12, ...]

The next step retrieves the corresponding UUIDs:

lucky_uuids = SELECT player_uuid FROM world_lottery WHERE apply_no IN lucky_apply_nos;

Rewards are then assigned in order: the first UUID gets the first‑prize reward, the next 15 UUIDs get the second‑prize reward, and so on.

The Hidden MySQL Rule

The assumption that lucky_uuids will be returned in the same order as lucky_apply_nos is false because the SELECT statement lacks an ORDER BY clause. MySQL’s return order depends on the storage engine:

MyISAM returns rows in their physical storage order.

InnoDB returns rows sorted by the primary key.

In this case the table uses InnoDB with player_uuid as the primary key, so the result set is ordered by UUID, not by the list supplied in the IN clause.

Impact on the Lottery

If a player’s UUID is numerically small, that player will always appear early in the ordered result set. Consequently, whenever that player’s apply_no is included in lucky_apply_nos, they receive the highest‑value prize. This inflates the probability of winning the top prize from 0.0001⁴ to 0.1⁴ for that player.

The issue becomes noticeable when the number of “big‑prize” slots is increased, as was done in this event, making multiple high‑value wins by a single player more likely.

Simple Fix

After fetching lucky_uuids, shuffle the array before assigning rewards, ensuring the mapping between apply_no and reward remains random.

Summary

MySQL SELECT statements without ORDER BY have engine‑specific return order rules.

MyISAM → physical storage order; InnoDB → primary‑key order.

Relying on the order of an IN list can produce biased results.

Shuffle the result set or explicitly order by the list to avoid the bug.