Can someone explain why this select statement behaves the way it does

Question

While working on generating rankings for a game, I wrote a query similar to the following code (a lot of columns left out for brevity), and noticed the rank was producing seeming gibberish (first query). However changing the table in which gamenumber was referenced from produces the correct result (second query).

mysql> select @i:=@i+1 as rank, a.usernum, s.buildings from account_data as a, statistics as s join (select @i:=0) rank
where a.gamenumber=144 and a.usernum=s.usernum order by s.buildings desc limit 10;
+------+---------+-----------+
| rank | usernum | buildings |
+------+---------+-----------+
|   49 |   31071 |     87557 |
|   45 |   31047 |     86858 |
|   24 |   31064 |     84753 |
|   69 |   31089 |     79682 |
|   17 |   31103 |     76892 |
|   38 |   31106 |     66186 |
|   29 |   31053 |     65069 |
|   47 |   31081 |     59093 |
|   61 |   31036 |     58056 |
|  100 |   31061 |     56833 |
+------+---------+-----------+
10 rows in set (0.01 sec)
mysql> select @i:=@i+1 as rank, a.usernum, s.buildings from account_data as a, statistics as s join (select @i:=0) rank
where s.gamenumber=144 and a.usernum=s.usernum order by s.buildings desc limit 10;
+------+---------+-----------+
| rank | usernum | buildings |
+------+---------+-----------+
|    1 |   31071 |     87557 |
|    2 |   31047 |     86858 |
|    3 |   31064 |     84753 |
|    4 |   31089 |     79682 |
|    5 |   31103 |     76892 |
|    6 |   31106 |     66186 |
|    7 |   31053 |     65069 |
|    8 |   31081 |     59093 |
|    9 |   31036 |     58056 |
|   10 |   31061 |     56833 |
+------+---------+-----------+
10 rows in set (0.00 sec)

Can anyone explain to me why the different results in rank when the order is correct?

Answer 1

Using a variable in this way is something of a hack:

select @i:=@i+1 as rank

You could expect this to work if you select from only one table. When you join more tables together, MySQL might evaluate @i:=@i+1 for a row that is later filtered out by the where clause.

To solve this, ensure that the rank is calculated outside a join. One way is to store the join results in a temporary table, and calculate the rank on that.