MySQL Bugs: #31392: creating index results in ignored order by desc

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:		CPU Architecture:	Any
Tags:	INDEX, ORDER BY DESC

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.

isn't this the same as http://bugs.mysql.com/bug.php?id=31001  ?

As this is InnoDB table, it is surely a duplicate of bug #31001.

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.