From a discussion on Freenode (and don't feel bad if you don't get it; I had to ask Sinisa and Monty to explain it to me. Sinisa explained how it worked, and Monty found a bug.)
USE test;
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`a` int(11) DEFAULT NULL
);
INSERT INTO t VALUES (RAND() * 50);
INSERT INTO t SELECT RAND() * 50 FROM t;
Repeat that last INSERT a few times until you have about 1000 rows.
What would you expect from the following?
SELECT * FROM t WHERE false UNION SELECT * FROM t ORDER BY a LIMIT 19, 1;
(SELECT * FROM t WHERE false) UNION (SELECT * FROM t ORDER BY a LIMIT 19, 1);
(SELECT * FROM t WHERE false) UNION (SELECT * FROM t ORDER BY a) LIMIT 19, 1;
Show Answer ▼
Two things to remember:

UNION
is equivalent to
UNION DISTINCT
 an
ORDER BY
with no
LIMIT
is optimized away in
UNION
ed queries
The first query:
SELECT * FROM t WHERE false UNION SELECT * FROM t ORDER BY a LIMIT 19, 1;
is equivalent to:
(SELECT * FROM t WHERE false UNION SELECT * FROM t) ORDER BY a LIMIT 19, 1;
Since the UNION
is by default DISTINCT
, get each distinct value from the table, then order by `a`. Take the row at offset 19. Assuming even distribution of our random data, your value should be '19'.
The second query:
(SELECT * FROM t WHERE false) UNION (SELECT * FROM t ORDER BY a LIMIT 19, 1);
Order all the rows by `a`. That's roughly 20 rows of value '1', then 20 rows of value '2', then 20 rows of value '3', etc. Take the row at offset 19. Assuming even distribution of our random data, your value should be '1'.
The third query:
(SELECT * FROM t WHERE false) UNION (SELECT * FROM t ORDER BY a) LIMIT 19, 1;
The ORDER BY
is discarded. We get distinct values in random order. Take the row at offset 19. Since the values are in random order, the value is not predictable.
Leave a comment