MySQL Forums
Forum List  »  Quality Assurance

5.1.23-rc possible order by desc bug
Posted by: Mark Harburn
Date: February 16, 2008 02:55AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
5.1.23-rc possible order by desc bug
4355
February 16, 2008 02:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.