MySQL Bugs: #263: Incorrect results from SELECT ... ORDER BY

Bug #263	Incorrect results from SELECT ... ORDER BY
Submitted:	9 Apr 2003 15:56	Modified:	15 May 2003 8:38
Reporter:	Matt Solnit	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.12	OS:	Windows (Windows XP Professional SP1)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
Hi all!  I found the following bug in MySQL 4.0.12.  The documentation states the following:

Section 3.3.4.6. Working with NULL values
"When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC. 
Note that between MySQL 4.0.2 - 4.0.10, NULL values incorrectly were always sorted first regardless of the sort direction."

This works correctly.  However, if I order by an expression that evaluates to NULL, the NULL's will always be placed at the beginning of the list, even if I do an ORDER BY DESC.

How to repeat:
mysql> CREATE TABLE MyBug (a int, b int) TYPE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO MyBug VALUES (1, 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MyBug VALUES (3, 4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MyBug VALUES (5, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM MyBug ORDER BY b;
+------+------+
| a    | b    |
+------+------+
|    5 | NULL | <-- correct!
|    1 |    2 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM MyBug ORDER BY b DESC;
+------+------+
| a    | b    |
+------+------+
|    3 |    4 |
|    1 |    2 |
|    5 | NULL | <-- correct!
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM MyBug ORDER BY (a + b);
+------+------+
| a    | b    |
+------+------+
|    5 | NULL | <-- correct!
|    1 |    2 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM MyBug ORDER BY (a + b) DESC;
+------+------+
| a    | b    |
+------+------+
|    5 | NULL | <-- incorrect??
|    3 |    4 |
|    1 |    2 |
+------+------+
3 rows in set (0.00 sec)

This is not InnoDB-related, the bug appears on MyISAM tables as well.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

sorry, it's not closed yet

Hi Sergei.  I found the behavior in 4.0.12.  Does your second message mean you agree the bug is still there?

What Sergei meant is that it's fixed in the development tree and will be in 4.0.13 and 4.1.1

To be sure the bug is fixed, we have added your test case to our test suite which is tested on all platforms for which we do binary releases.