Description:
Most people seem to use ORDER BY RAND() to select random rows and/or rows in random order, despite that it's slow and potentially biased, just because it's fast to type.
How to repeat:
See http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-ta...
Suggested fix:
I propose the creation of a SHUFFLE clause for the SELECT statement, to go between the ORDER BY and LIMIT clauses. SHUFFLE's only parameter would be an optional keyword indicating the quality of randomness needed:
With no keyword, MySQL would guarantee that the seed and internal state were at least 32 bits, that no row would be put in a given position by more than twice as many seed values as any other row (unless more than 2^32 rows were selected), and that exactly predicting the seed required, at least, knowing the date and time to the nearest second (according to the server's system clock) when the query was to run.
With CRYPTOGRAPHIC, MySQL would guarantee that the shuffle was cryptographically secure, as long as the operating system didn't present as a CSPRNG something that wasn't. On Unix-like systems it could use /dev/urandom; on Windows it could use System.Security.Cryptography.RNGCryptoServiceProvider.
With HARDWARE, MySQL would further guarantee that it was using at least as many bits of hardware entropy as the base-2 logarithm of the number of permutations, as long as the sources specified by the admin (e.g. /dev/random, external servers) weren't overestimating the amount of entropy they supplied. If no sources had been specified, SHUFFLE HARDWARE would give an error.
SHUFFLE CRYPTOGRAPHIC and SHUFFLE HARDWARE could be restricted to specific users, to prevent malicious users from running repeated queries in order to determine the CSPRNG's state or drain the entropy pool dry.
