CODEWITHSUNDEEP
mysqlsql
user1488793
user1488793

Reputation: 294

MySQL ORDER BY random field does not work with additional operation

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???

Upvotes: 6

Views: 742

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

The issue seems to be caused by what other databases call "volatile" functions and MySQL calls "non-deterministic" functions. These are functions that return different values each time they are called.

Based on your observation, your second query is being evaluated as:

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

That is, the expression for r is being plugged into the ORDER BY. Because rand() is non-deterministic, it returns a different value each time it is called. The result is a randomly ordered result set, but different from the ordering of the r column.

In my opinion, this is a bug in MySQL It is using expression substitution for a function that is (or at least should be) declared NOT DETERMINISTIC. The "bug" may simply be that the built-in rand() function is not properly declared as not deterministic. It could be that the declaration is ignored. Obviously, MySQL should be using the value that the alias refers to, rather than the expression, especially for a non-deterministic function.

Note: This has a potential impact beyond deterministic functions. If the expression is a complex, time-consuming expression, it may end up being evaluated multiple additional times for the order by.

Upvotes: 0

Hart CO
Hart CO

Reputation: 34784

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;

Upvotes: 2

Related Questions