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

Bug #263	Incorrect results from SELECT ... ORDER BY
Submitted:	9 Apr 2003 17:56	Modified:	15 May 2003 10:38
Reporter:	Matt Solnit
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.0.12	OS:	Microsoft Windows (Windows XP Professional SP1)
Assigned to:	Michael Widenius	Target Version:

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.