Does @row_number from MySQL 5 behave different in MySQL 8?

Simple table userpoints:

userid | points
1      | 456
2      | 3
3      | 1778
...    | ...

I used this function for years in MySQL 5 to receive the userrank:

SELECT userid, userrank FROM 
    (SELECT @row_number:[email protected]_number+1 AS userrank, userid 
    FROM `userpoints`, (SELECT @row_number := 0) r 
    ORDER BY points DESC) t 
    WHERE userid = 123

And it returned the userrank for userid 123, e.g. 3456.

With MySQL 8 I only get 1 as value for userrank with each userid I try.

What is the problem and how to fix this?


I tried the inner SELECT alone, and this gives me the list of all userids with the correct userranks.

Answer

In MySQL 8, setting user variables as side-effects in expressions is deprecated. You should use window functions instead.

SELECT t.userid, t.userrank 
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY points DESC) AS userrank, userid 
    FROM `userpoints` 
) t 
WHERE t.userid = 123;