MySQL Bugs: #32815: Problem with ORDER BY if WHERE is used with NOT NULL

Bug #32815	Problem with ORDER BY if WHERE is used with NOT NULL
Submitted:	28 Nov 2007 13:25	Modified:	14 Jan 2008 18:13
Reporter:	Jrme Despatis (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux (Debian testing)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	NOT NULL, order by

Description:
Impossible to use ORDER BY if the WHERE clause contains a IS NULL

How to repeat:
With the file.sql provided, it creates a table, and just launch the query:

SELECT * FROM `bug` WHERE ref_member = 19130380 OR ref_member IS NULL ORDER BY date DESC

=> The listing is not order by date DESC

however, if i modify my query to no more use IS NULL, it works:
SELECT * FROM `bug` WHERE ref_member = 19130380 OR type = 'member' ORDER BY date DESC

=> The listing is ordered by date DESC

File used in my bug report

Attachment: bug.sql (text/x-sql), 965 bytes.

This is probably a bug in MySQL's query optimizer.

Thank you for the bug report. 5.1 server isn't affected:

[miguel@skybr 5.0]$ bin/mysql -uroot db1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.54-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM `bug` WHERE ref_member = 19130380 OR ref_member IS NULL ORDER BY date DESC;
+------------+-------------+--------+---------------------+
| ref_member | ref_message | type   | date                |
+------------+-------------+--------+---------------------+
|   19130380 |          11 | member | 2007-11-28 14:02:24 | 
|       NULL |           8 | member | 2007-11-28 14:16:35 | 
|       NULL |           4 | member | 2007-11-28 14:15:54 | 
+------------+-------------+--------+---------------------+
3 rows in set (0.00 sec)

*********************_________*************************

[miguel@skybr 5.1]$ bin/mysqladmin -uroot create db1
[miguel@skybr 5.1]$ bin/mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.23-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source /home/miguel/a/bug.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `bug` WHERE ref_member = 19130380 OR ref_member IS NULL ORDER BY date DESC;
+------------+-------------+--------+---------------------+
| ref_member | ref_message | type   | date                |
+------------+-------------+--------+---------------------+
|       NULL |           8 | member | 2007-11-28 14:16:35 | 
|       NULL |           4 | member | 2007-11-28 14:15:54 | 
|   19130380 |          11 | member | 2007-11-28 14:02:24 | 
+------------+-------------+--------+---------------------+
3 rows in set (0.00 sec)

mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39594

ChangeSet@1.2595, 2007-12-07 17:14:59-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #32815.
  The index (key_part_1, key_part-2) was erroneously considered as compatible
  with the required ordering in the function test_test_if_order_by_key when 
  a query with an ORDER BY clause contained a condition of the form
    key_part_1=const OR key_part_1 IS NULL 
  and the order list contained only key_part_2. This happened because the value
  of the const_key_parts field in the KEYUSE structure was not formed correctly
  for the keys that could be used for ref_or_null access. 
  This was fixed in the code of the update_ref_and_keys function.
  The problem could not manifest itself for MyISAM databases because the
  implementation of the keys_to_use_for_scanning() handler function always
  returns an empty bitmap for the MyISAM engine.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Since this issue does not appear in 5.1 (per note from Miguel) or 6.0 (per my test with an unpatched 6.0-bk), fix was documented in 5.0.54 changelog only as follows:

        An ORDER BY query using IS
        NULL in the WHERE clause did not
        return correct results.