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: | |
Category: | MySQL Server | Severity: | S5 (Performance) |
Version: | 5.0.22-community-nt | OS: | Windows |
Assigned to: | CPU Architecture: | Any |
[18 Apr 2007 15:30]
Abdelmonaam KALLALI
[18 Apr 2007 18:13]
MySQL Verification Team
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.
[18 Apr 2007 19:26]
Abdelmonaam KALLALI
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 | +-----------+
[19 Apr 2007 7:29]
Hartmut Holzgraefe
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.
[19 Apr 2007 13:18]
Abdelmonaam KALLALI
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