A Little Noise

Month: September 2006

  • Empty Set Equality

    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?
    MySQL says no.

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

  • Single-query UNION with LIMIT and ORDER

    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…)