September « 2006 « A Little Noise

September 27, 2006

Empty Set Equality

Filed under: MySQL Gotchas — snoyes @ 3:48 pm

SELECT 1 LIMIT 0;
Empty set (0.00 sec)

SELECT 2 LIMIT 0;
Empty set (0.00 sec)

SELECT NULL LIMIT 0;
Empty set (0.00 sec)

Exactly what I’d expect. So here’s the question:

SELECT '1 = 1' AS equality, (SELECT 1 LIMIT 0) = (SELECT 1 LIMIT 0) AS result
UNION SELECT '1 = 2', (SELECT 1 LIMIT 0) = (SELECT 2 LIMIT 0)
UNION SELECT '1 = NULL', (SELECT 1 LIMIT 0) = (SELECT NULL LIMIT 0);

What do you think? Are all empty sets created identical?
Show Answer ▼

MySQL says no.

+----------+--------+
| equality | result |
+----------+--------+
| 1 = 1    |      1 |
| 1 = 2    |      0 |
| 1 = NULL |   NULL |
+----------+--------+

Comments (0)

September 12, 2006

Single-query UNION with LIMIT and ORDER

Filed under: MySQL — snoyes @ 9:20 pm

I have a query that behaves differently in every version of MySQL I’ve tried. Could you do me a favor? Run the following four lines and post a comment with your results. And if you know of a particular bug fix that addresses this issue, let me know about that too.

CREATE TABLE `test` (`id` tinyint);
INSERT INTO test VALUES (1), (2), (3), (4), (5);
SHOW VARIABLES LIKE 'version%';
(SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;

(more…)

Comments (10)