A Little Noise

Single-query UNION with LIMIT and ORDER

Written by

snoyes

in

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.

Comments

10 responses to “Single-query UNION with LIMIT and ORDER”

  1. Toby Avatar
    Toby

    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 |
    +——+

  2. david Avatar
    david

    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)

  3. snoyes Avatar
    snoyes

    +————————-+——————————-+
    | 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 |
    +——+

  4. swconsult Avatar
    swconsult

    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)

  5. Felix Geerinckx Avatar
    Felix Geerinckx

    +————————-+——————————+
    | 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>

  6. Toasty Avatar
    Toasty

    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

  7. Xaprb Avatar
    Xaprb

    Two data points here too.

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

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

  8. Brian Moon Avatar
    Brian Moon

    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)

  9. Matt Montgomery Avatar
    Matt Montgomery

    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

  10. DaTa Avatar
    DaTa

    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)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More posts