A Little Noise

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;

Here are three results I get on Windows:

4.0.27-nt
+----+
| id |
+----+
|  5 |
|  4 |
|  3 |
+----+
5.0.15
+----+
| id |
+----+
|  5 | 
|  4 | 
|  3 | 
|  2 | 
|  1 | 
+----+
5.0.24a-community-nt
+------+
| id   |
+------+
|    3 |
|    4 |
|    5 |
+------+



That last result is the one I expect.

10 Comments »

  1. 2 data points:

    | version | 5.0.24a-pro-gpl-log |
    | version_comment | MySQL Pro (GPL) |
    | version_compile_machine | i386 |
    | version_compile_os | pc-solaris2.10 |
    +————————-+———————+
    4 rows in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+

    | version | 5.0.24-log |
    | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (August 26, 2006) |
    | version_comment | Gentoo Linux mysql-5.0.24 |
    | version_compile_machine | i686 |
    | version_compile_os | pc-linux-gnu |
    +————————-+———————————————————–+
    5 rows in set (0.01 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 4 |
    | 5 |
    | 5 |
    +——+

    Comment by Toby — September 12, 2006 @ 11:55 pm

  2. mysql> SHOW VARIABLES LIKE ‘version%’;
    +————————-+——————————————+
    | Variable_name | Value |
    +————————-+——————————————+
    | version | 5.0.22-standard |
    | version_comment | MySQL Community Edition – Standard (GPL) |
    | version_compile_machine | powerpc |
    | version_compile_os | apple-darwin8.6.0 |
    +————————-+——————————————+
    4 rows in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+
    3 rows in set (0.06 sec)

    Comment by david — September 13, 2006 @ 2:19 am

  3. +————————-+——————————-+
    | Variable_name | Value |
    +————————-+——————————-+
    | version | 5.0.22-community-nt |
    | version_comment | MySQL Community Edition (GPL) |
    | version_compile_machine | ia32 |
    | version_compile_os | Win32 |
    +————————-+——————————-+

    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+

    Comment by snoyes — September 13, 2006 @ 4:20 am

  4. mysql> SHOW VARIABLES LIKE ‘version%’;
    +————————-+——————————————————–+
    | Variable_name | Value |
    +————————-+——————————————————–+
    | version | 5.0.22 |
    | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 25, 2006) |
    | version_comment | FreeBSD port: mysql-server-5.0.22 |
    | version_compile_machine | i386 |
    | version_compile_os | portbld-freebsd6.1 |
    +————————-+——————————————————–+
    5 rows in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+
    3 rows in set (0.02 sec)

    Comment by swconsult — September 13, 2006 @ 6:09 am

  5. +————————-+——————————+
    | Variable_name | Value |
    +————————-+——————————+
    | version | 5.1.11-beta |
    | version_comment | MySQL Community Server (GPL) |
    | version_compile_machine | i686 |
    | version_compile_os | pc-linux-gnu |
    +————————-+——————————+
    4 rows in set

    +—-+
    | id |
    +—-+
    | 3 |
    | 4 |
    | 5 |
    +—-+
    3 rows in set

    mysql>

    Comment by Felix Geerinckx — September 13, 2006 @ 9:02 am

  6. Here you go, have fun :)

    mysql> SHOW VARIABLES LIKE ‘version%’;
    +————————-+——————————————+
    | Variable_name | Value |
    +————————-+——————————————+
    | version | 5.0.22-standard-log |
    | version_comment | MySQL Community Edition – Standard (GPL) |
    | version_compile_machine | i686 |
    | version_compile_os | pc-linux-gnu |
    +————————-+——————————————+
    4 rows in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+
    3 rows in set (0.00 sec)

    mysql> SHOW VARIABLES LIKE ‘version%’;
    +————————-+———————+
    | Variable_name | Value |
    +————————-+———————+
    | version | 5.0.20a-log |
    | version_comment | Source distribution |
    | version_compile_machine | x86_64 |
    | version_compile_os | unknown-linux-gnu |
    +————————-+———————+
    4 rows in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 5 |
    | 4 |
    | 3 |
    | 2 |
    | 1 |
    +——+
    5 rows in set (0.00 sec)

    mysql> SHOW VARIABLES LIKE ‘version%’;
    +————————-+——————————————+
    | Variable_name | Value |
    +————————-+——————————————+
    | version | 4.1.21-standard-log |
    | version_comment | MySQL Community Edition – Standard (GPL) |
    | version_compile_machine | x86_64 |
    | version_compile_os | unknown-linux-gnu |
    +————————-+——————————————+
    4 rows in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+
    3 rows in set (0.01 sec)

    mysql> SHOW VARIABLES LIKE ‘version%’;
    +—————+———————+
    | Variable_name | Value |
    +—————+———————+
    | version | 4.0.16-standard-log |
    +—————+———————+
    1 row in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+
    3 rows in set (0.01 sec)

    mysql> SHOW VARIABLES LIKE ‘version%’;
    +—————+—————–+
    | Variable_name | Value |
    +—————+—————–+
    | version | 4.0.14-standard |
    +—————+—————–+
    1 row in set (0.03 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+
    3 rows in set (0.00 sec)

    mysql> SHOW VARIABLES LIKE ‘version%’;
    +—————+————-+
    | Variable_name | Value |
    +—————+————-+
    | version | 3.23.58-log |
    +—————+————-+
    1 row in set (0.01 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    ERROR 1064: You have an error in your SQL syntax near ‘(SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC’ at line 1

    Comment by Toasty — September 13, 2006 @ 10:58 am

  7. Two data points here too.

    4.1.21-log
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+

    5.0.21-log
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+>

    Comment by Xaprb — September 13, 2006 @ 12:33 pm

  8. mysql> CREATE TABLE `test` (`id` tinyint);
    Query OK, 0 rows affected (0.00 sec)

    mysql> INSERT INTO test VALUES (1), (2), (3), (4), (5);
    Query OK, 5 rows affected (0.00 sec)
    Records: 5 Duplicates: 0 Warnings: 0

    mysql> SHOW VARIABLES LIKE ‘version%’;
    +————————-+———————————————-+
    | Variable_name | Value |
    +————————-+———————————————-+
    | version | 5.0.20a-max |
    | version_comment | MySQL Community Edition – Experimental (GPL) |
    | version_compile_machine | i686 |
    | version_compile_os | apple-darwin8.5.1 |
    +————————-+———————————————-+
    4 rows in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 5 |
    | 4 |
    | 3 |
    | 2 |
    | 1 |
    +——+
    5 rows in set (0.00 sec)

    Comment by Brian Moon — September 13, 2006 @ 3:01 pm

  9. It may or may not be related to this still pending “show stopper” class bug, but it’s a fun one none the less:

    http://bugs.mysql.com/bug.php?id=21787

    Comment by Matt Montgomery — September 15, 2006 @ 5:00 am

  10. mysql> SHOW VARIABLES LIKE ‘version%’;
    +————————-+———————————————————+
    | Variable_name | Value |
    +————————-+———————————————————+
    | version | 4.1.21 |
    | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (July 19, 2006) |
    | version_comment | FreeBSD port: mysql-server-4.1.21 |
    | version_compile_machine | i386 |
    | version_compile_os | portbld-freebsd6.1 |
    +————————-+———————————————————+
    5 rows in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+
    3 rows in set (0.00 sec)

    mysql> SHOW VARIABLES LIKE ‘version%’;
    +————————-+————————–+
    | Variable_name | Value |
    +————————-+————————–+
    | version | 5.0.24a-Debian_3-log |
    | version_comment | Debian etch distribution |
    | version_compile_machine | i486 |
    | version_compile_os | pc-linux-gnu |
    +————————-+————————–+
    4 rows in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+
    3 rows in set (0.00 sec)

    mysql> SHOW VARIABLES LIKE ‘version%’;
    +————————-+———————+
    | Variable_name | Value |
    +————————-+———————+
    | version | 4.1.19 |
    | version_comment | Source distribution |
    | version_compile_machine | i686 |
    | version_compile_os | pc-linux-gnu |
    +————————-+———————+
    4 rows in set (0.00 sec)

    mysql> (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
    +——+
    | id |
    +——+
    | 3 |
    | 4 |
    | 5 |
    +——+
    3 rows in set (0.00 sec)

    Comment by DaTa — September 18, 2006 @ 4:24 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress