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
UNIONed 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.
Related
