5.1.23-rc possible order by desc bug
Hi,
I'm not very good at bug reports and need some help doing one.
Bassically i have a join on an innodb table to another table and then a order by desc statement on an non-primary key on the joined table. This is on 64 bit version of mysql 5.1.23 on the rhel 5 rpm
If you look at trackdate in the below tables you'll see what happens:
mysql> select tv.fromClientID as clientID,tv.trackDate,p.realname,p.lastSeen,p.clientName, p.image_serverID,p.age,p.maritalStatus,p.imageID,p.imageCategoryID from trackviews tv left join mym p on (tv.fromClientID=p.clientID) where tv.clientID=32 ORDER BY trackdate DESC limit 5;
+----------+------------+-----------------+------------+-----------------+----------------+------+---------------+---------+-----------------+
| clientID | trackDate | realname | lastSeen | clientName | image_serverID | age | maritalStatus | imageID | imageCategoryID |
+----------+------------+-----------------+------------+-----------------+----------------+------+---------------+---------+-----------------+
| 396326 | 1202945744 | stepha | 1203032636 | S t e f o | 0 | 17 | 1 | 6115283 | 1 |
| 396069 | 1202669189 | t | 1202990363 | yes babes x | 0 | 14 | 1 | 6107723 | 1 |
| 395891 | 1202525736 | da | 1202528344 | DavidFlesher | 0 | 13 | 0 | 0 | 0 |
| 395218 | 1202549293 | sop | 1202549247 | sophiaxx | 0 | 15 | 1 | 0 | 0 |
| 394466 | 1202929904 | LaurenAndNic | 1203148914 | LaurenAndNicole | 0 | 18 | 1 | 6066326 | 1 |
+----------+------------+-----------------+------------+-----------------+----------------+------+---------------+---------+-----------------+
5 rows in set (0.00 sec)
mysql> select tv.fromClientID as clientID,tv.trackDate,p.realname,p.lastSeen,p.clientName, p.image_serverID,p.age,p.maritalStatus,p.imageID,p.imageCategoryID from trackviews tv left join mym p on (tv.fromClientID=p.clientID) where tv.clientID=32 ORDER BY trackdate ASC limit 5;
+----------+------------+---------------+------------+--------------+----------------+------+---------------+---------+-----------------+
| clientID | trackDate | realname | lastSeen | clientName | image_serverID | age | maritalStatus | imageID | imageCategoryID |
+----------+------------+---------------+------------+--------------+----------------+------+---------------+---------+-----------------+
| 387476 | 1202522055 | KUM | 1202522254 | remzi | 0 | 20 | 0 | 5866205 | 1 |
| 26643 | 1202523829 | Br | 1203117151 | brido | 0 | 23 | 1 | 1748151 | 1 |
| 395891 | 1202525736 | da | 1202528344 | DavidFlesher | 0 | 13 | 0 | 0 | 0 |
| 50539 | 1202547691 | gavin bloo | 1202573475 | tigger224 | 0 | 23 | 2 | 6085499 | 1 |
| 395218 | 1202549293 | sop | 1202549247 | sophiaxx | 0 | 15 | 1 | 0 | 0 |
+----------+------------+---------------+------------+--------------+----------------+------+---------------+---------+-----------------+
5 rows in set (0.01 sec)
trackviews table:
CREATE TABLE `trackviews` (
`clientID` int(11) NOT NULL DEFAULT '0',
`fromClientID` int(11) NOT NULL DEFAULT '0',
`trackDate` int(11) NOT NULL DEFAULT '0',
`data` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`clientID`,`fromClientID`),
KEY `trackDate` (`trackDate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
*UPDATE* Changed engine to MyISAM and the issue went away, it's innodb only.
Edited 2 time(s). Last edit at 02/16/2008 03:06AM by Mark Harburn.