Bug #31392 creating index results in ignored order by desc
Submitted: 4 Oct 2007 10:17 Modified: 5 Oct 2007 7:00
Reporter: Michael Scheepers Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.21-beta OS:Linux (Red Hat 3.4.6-2)
Assigned to:
Tags: INDEX, ORDER BY DESC

[4 Oct 2007 10:17] Michael Scheepers
Description:
After creating an index on a table the 'ORDER BY col DESC' in a query is ignored in the result set.

How to repeat:
The table:
CREATE TABLE `cam_online` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `camid` int(11) NOT NULL,
  `begin` datetime NOT NULL,
  `end` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

The query:
SELECT id, camid
FROM cam_online
WHERE camID = 4
ORDER BY id DESC

The result set:
 id           camid    
 -----------  -------- 
 1310713      4        
 1296057      4        
 1295943      4        
 1292293      4        
 1288052      4        
 1288022      4        
 1288002      4        

So far ok. After creating an index for column camid as follows
CREATE INDEX IX_camid ON cam_online(camid)
the same query gives the following result:
 id           camid    
 -----------  -------- 
 1288002      4        
 1288022      4        
 1288052      4        
 1292293      4        
 1295943      4        
 1296057      4        
 1310713      4        

After executing DROP INDEX IX_camid ON cam_online the result had the expected order again.
[4 Oct 2007 16:12] Nicklas Westerlund
isn't this the same as http://bugs.mysql.com/bug.php?id=31001  ?
[4 Oct 2007 16:32] Valerii Kravchuk
As this is InnoDB table, it is surely a duplicate of bug #31001.
[5 Oct 2007 7:00] Michael Scheepers
Yes it is indeed. I was searching your bug database by using order by and index so i didn't found that entry. I am sory about that.