MySQL Bugs: #27929: wrong order if we have same date

Bug #27929	wrong order if we have same date
Submitted:	18 Apr 2007 15:30	Modified:	19 Apr 2007 13:18
Reporter:	Abdelmonaam KALLALI	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.0.22-community-nt	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any

Description:
if I do query with order by date (i.e. 2007-02-15)desc the result is ordered asc for the rows with the same date

How to repeat:
select * from workDone where (serialNum='123') and dateDone between date('2007-03-01') and date('2007-03-01')order by dateDone desc
the result gives the workdone table in ascending order

Suggested fix:
we should get the rows in a descending order for same date rows as well

Thank you for the bug report. Could you please provide the exactly server
version i.e: 5.0.37 and the complete test case with create table, insert
data, queries and the result you got. Thanks in advance.

mysql> CREATE TABLE `workDone` ( `serialNum` char(20) NOT NULL default '', `work
` char(8) NOT NULL default '', `dateDone` date default '1111-11-11') ENGINE=Inno
DB DEFAULT CHARSET=latin1;
ERROR 1046 (3D000): No database selected
mysql> CREATE TABLE `production`. `workDone` ( `serialNum` char(20) NOT NULL def
ault '', `work` char(8) NOT NULL default '', `dateDone` date default '1111-11-11
') ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.56 sec)

mysql> insert into workDone (serialNum, work, dateDone) values('123', 'WD110', d
ate('2007-04-18'));
ERROR 1046 (3D000): No database selected
mysql> insert into production. workDone (serialNum, work, dateDone) values('123'
, 'WD110', date('2007-04-18'));
Query OK, 1 row affected (0.08 sec)

mysql> insert into production. workDone (serialNum, work, dateDone) values('124'
, 'CD103', date('2007-04-18'));
Query OK, 1 row affected (0.03 sec)

mysql> insert into productio. workDone (serialNum, work, dateDone) values('125',
 'WD009', date('2007-04-18'));
ERROR 1146 (42S02): Table 'productio.workdone' doesn't exist
mysql> insert into production. workDone (serialNum, work, dateDone) values('125'
, 'WD009', date('2007-04-18'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into production.workDone (serialNum, work, dateDone) values('111',
 'WD236', date('2007-04-18'));
Query OK, 1 row affected (0.02 sec)

mysql> insert into production.workDone (serialNum, work, dateDone) values('105',
 'CD453', date('2007-04-18'));
Query OK, 1 row affected (0.02 sec)

mysql> select serialNum from production.workDone where  dateDone between date('2
007-04-18') and date('2007-04-18') order by dateDone desc;
+-----------+
| serialNum |
+-----------+
| 123       |
| 124       |
| 125       |
| 111       |
| 105       |
+-----------+
5 rows in set (0.03 sec)

mysql>
I should get
+-----------+
| 105       |
| 111       |
| 125       |
| 124       |
| 123       |
+-----------+

You order by doneDate only and as all rows have the same done date
both the result you got and the one you expected would be equally
correct. The server chose to simply return rows with the same doneDate
field in the same order they were inserted in this case, but it could
have chosen any other order if it had better fit the query optimizer, too.

If you want the results ordered by the serialNum field, too, you have
to explicitly say so in your query.

I do not want to order them by serialNum but by the sequence of the entry desc as well this problem could be solved if we are querying one table by adding an auto-incremented column
the problem is when we are joining two or more tables on date