MySQL Bugs: #16256: A problem with MySQL ordering based on dates

Bug #16256	A problem with MySQL ordering based on dates
Submitted:	6 Jan 2006 16:10	Modified:	12 Feb 2006 16:13
Reporter:	Rohan Abraham	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	MySQL 5.0.17nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I have been testing MySQL 5 with a few queries and this one gave the wrong output. I use MySQL query browser there is no other connectors or anything used. The MySQL server does not return a empty resultset when a query is given to the server. Basically the query is a SELECT query as shown below:

SELECT Invoice_ID, Invoice_Date, DATE_FORMAT( Invoice_Period_Start, '%d/%m/%Y' ) as Invoice_Period_Start,
DATE_FORMAT( Invoice_Period_End, '%d/%m/%Y' ) as Invoice_Period_End,
DATE_FORMAT(Invoice_Date, '%d/%m/%Y') as Invoice_Date_Display,
Invoice_Number
FROM invoice 
order by Invoice_Date DESC

This query returns 0 results.. However if I remove the DESC the query returns 53 results (the correct amount of results). This is causing concern as I am not sure why this happens, there are no triggers, no stored procedures, nothing.. just a basic query which does not give a resultset when the order by is set to DESC.

How to repeat:
Whenever the query is run there is no resultset returned. However if the order by is left without DESC the query returns results.

SELECT Invoice_ID, Invoice_Date, DATE_FORMAT( Invoice_Period_Start, '%d/%m/%Y' ) as Invoice_Period_Start,
DATE_FORMAT( Invoice_Period_End, '%d/%m/%Y' ) as Invoice_Period_End,
DATE_FORMAT(Invoice_Date, '%d/%m/%Y') as Invoice_Date_Display,
Invoice_Number
FROM invoice 
order by Invoice_Date DESC

Suggested fix:
The table has dates that are 0000/00/00, but still MySQL should return the same resultset if the order by is ASC or DESC. This is causing concern.. I get no results when the order by is DESC and I get the correct number of results when I dont give DESC with the order by..

Besides this, I can sort by any other field and the results returned are fine.

Thank you for a problem report. Please, send the results of SHOW CREATE TABLE invoice. Some sample data to demonstrate a problem will be useful too.

Sorry, but I was not able to repeat the problem you described with 5.0.19-BK on Linux with a simple test case:

mysql> CREATE TABLE `invoice` (
    ->   `Invoice_ID` int(11) NOT NULL auto_increment,
    ->   `Invoice_Date` datetime default NULL,
...
    ->   KEY `ContractIDDate` (`Contract_ID`,`Invoice_Date`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into invoice(Invoice_Date) values (now());
Query OK, 1 row affected (0.01 sec)
...
mysql> insert into invoice(Invoice_Date) values ('0000/00/00');
Query OK, 1 row affected (0.00 sec)
...
mysql> insert into invoice(Invoice_Date) values ('2006/01/03');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT Invoice_ID, Invoice_Date, DATE_FORMAT( Invoice_Period_Start, '%d/%m/%Y' )
    -> as Invoice_Period_Start,
    -> DATE_FORMAT( Invoice_Period_End, '%d/%m/%Y' ) as Invoice_Period_End,
    -> DATE_FORMAT(Invoice_Date, '%d/%m/%Y') as Invoice_Date_Display,
    -> Invoice_Number
    -> FROM invoice
    -> order by Invoice_Date DESC;
+------------+---------------------+----------------------+--------------------+
----------------------+----------------+
| Invoice_ID | Invoice_Date        | Invoice_Period_Start | Invoice_Period_End |
 Invoice_Date_Display | Invoice_Number |
+------------+---------------------+----------------------+--------------------+
----------------------+----------------+
|          3 | 2006-01-12 18:59:22 | NULL                 | NULL               |
 12/01/2006           | NULL           |
|          2 | 2006-01-12 18:59:21 | NULL                 | NULL               |
 12/01/2006           | NULL           |
|          1 | 2006-01-12 18:59:19 | NULL                 | NULL               |
 12/01/2006           | NULL           |
|          8 | 2006-01-03 00:00:00 | NULL                 | NULL               |
 03/01/2006           | NULL           |
|          7 | 2006-01-02 00:00:00 | NULL                 | NULL               |
 02/01/2006           | NULL           |
|          6 | 2006-01-01 00:00:00 | NULL                 | NULL               |
 01/01/2006           | NULL           |
|          4 | 0000-00-00 00:00:00 | NULL                 | NULL               |
 00/00/0000           | NULL           |
|          5 | 0000-00-00 00:00:00 | NULL                 | NULL               |
 00/00/0000           | NULL           |
+------------+---------------------+----------------------+--------------------+
----------------------+----------------+
8 rows in set (0.00 sec)

mysql> SELECT Invoice_ID, Invoice_Date, DATE_FORMAT( Invoice_Period_Start, '%d/
%m/%Y' ) as Invoice_Period_Start, DATE_FORMAT( Invoice_Period_End, '%d/%m/%Y' )
 as Invoice_Period_End, DATE_FORMAT(Invoice_Date, '%d/%m/%Y') as Invoice_Date_D
isplay, Invoice_Number FROM invoice  order by Invoice_Date;
+------------+---------------------+----------------------+--------------------+
----------------------+----------------+
| Invoice_ID | Invoice_Date        | Invoice_Period_Start | Invoice_Period_End |
 Invoice_Date_Display | Invoice_Number |
+------------+---------------------+----------------------+--------------------+
----------------------+----------------+
|          4 | 0000-00-00 00:00:00 | NULL                 | NULL               |
 00/00/0000           | NULL           |
|          5 | 0000-00-00 00:00:00 | NULL                 | NULL               |
 00/00/0000           | NULL           |
|          6 | 2006-01-01 00:00:00 | NULL                 | NULL               |
 01/01/2006           | NULL           |
|          7 | 2006-01-02 00:00:00 | NULL                 | NULL               |
 02/01/2006           | NULL           |
|          8 | 2006-01-03 00:00:00 | NULL                 | NULL               |
 03/01/2006           | NULL           |
|          1 | 2006-01-12 18:59:19 | NULL                 | NULL               |
 12/01/2006           | NULL           |
|          2 | 2006-01-12 18:59:21 | NULL                 | NULL               |
 12/01/2006           | NULL           |
|          3 | 2006-01-12 18:59:22 | NULL                 | NULL               |
 12/01/2006           | NULL           |
+------------+---------------------+----------------------+--------------------+
----------------------+----------------+
8 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

So, it is either some problem in your table or the bug (if any) is already fixed. Please, try to CHECK your table or to repeat with a newer version, 5.0.18, just as I demonstrated. Inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".