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