MySQL ORDER BY random field does not work with additional operation

Question

I face a strange behavior of MySQL. When I select a field that uses RAND() in its calculation and order the result by this field, everything works fine:

SELECT 
RAND() AS r
FROM SomeTable
ORDER BY r DESC;

If, however, I perform an additional operation within the ORDER BY clause such as +,-,*. / or whatever you like with a constant or another field, the result is no longer sorted. For example:

SELECT 
RAND() AS r
FROM SomeTable
ORDER BY r+1 DESC;

Of course I can move the operation in an additional field, but I was just curious whether I do something wrong. I assume that for some unknown reason MySQL re-evaluates the field if some operation is performed in the ORDER BY clause.

Is this a correct assumption???

Answer 1

MySQL 5.0 Reference:

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.

RAND() is being evaluated every time it's called, even though you've aliased it as r it gets re-evaluated and does not hold the same value as the column.

If you want to ORDER BY r+1 you'll need a subquery:

SELECT *
FROM (SELECT col1, RAND() AS r
      FROM Table1
      ) sub
ORDER BY (r+1) DESC;