A Little Noise

March 9, 2011

UNION and ORDER and LIMIT

Filed under: MySQL Gotchas — snoyes @ 10:09 am

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 ▼

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress