Description:
I'm running the RT request tracker 3.0.2 (see www.bestpractical.com/rt)
which uses MySQL, and discovered the following query was returning
incorrect results -- example table and queries are supplied below:
SELECT DISTINCT main.id,main.Name FROM XQueues main
WHERE ((main.Disabled = '0')) ORDER BY main.Name ASC;
The results were ordered by the "id" when they should have been ordered by "Name".
Either deleteing the DISTINCT keyword or adding SQL_BIG_RESULT gives
the right ordering.
How to repeat:
--
-- Table structure for table 'XQueues'
--
DROP TABLE IF EXISTS XQueues;
CREATE TABLE XQueues (
id int(11) NOT NULL auto_increment,
Name varchar(200) NOT NULL default '',
Description varchar(255) default NULL,
CorrespondAddress varchar(120) default NULL,
CommentAddress varchar(120) default NULL,
InitialPriority int(11) NOT NULL default '0',
FinalPriority int(11) NOT NULL default '0',
DefaultDueIn int(11) NOT NULL default '0',
Creator int(11) NOT NULL default '0',
Created datetime default NULL,
LastUpdatedBy int(11) NOT NULL default '0',
LastUpdated datetime default NULL,
Disabled smallint(6) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY XQueues1 (Name),
KEY XQueues2 (Disabled)
) TYPE=InnoDB;
--
-- Dumping data for table 'XQueues'
--
LOCK TABLES XQueues WRITE;
INSERT INTO XQueues VALUES (1,'General','The default queue','','',0,0,0,1,'2003-06-26 01:34:29',12,'2003-06-26 05:10:08',1),
(2,'___Approvals','A system-internal queue for the approvals system','','',0,0,0,1,'2003-06-26 01:34:29',1,'2003-06-26 01:34:29',2),
(3,'Humanities','Ask Us! -- MIT Libraries','humanities@omars-test.mit.edu','humanities-comment@omars-test.mit.edu',0,0,0,12,'2003-06-26 05:18:13',12,'2003-06-26 05:18:14',0),
(4,'Engineering','Ask Us! -- MIT Libraries','engineering@rt.mit.edu','engineering-comment@rt.mit.edu',0,0,0,28,'2003-06-27 02:56:50',28,'2003-06-27 03:04:32',0),
(5,'Arch-Plan','Ask Us! -- MIT Libraries','arch-plan@rt.mit.edu','arch-plan-comment@rt.mit.edu',0,0,0,28,'2003-06-27 03:06:52',28,'2003-06-27 03:06:53',0),
(6,'Bus-Man','Ask Us! -- MIT Libraries','bus-man@rt.mit.edu','bus-man-comment@rt.mit.edu',0,0,0,28,'2003-06-27 03:08:20',28,'2003-06-27 03:08:21',0),
(7,'Science','Ask Us! -- MIT Libraries','science@rt.mit.edu','science-comment@rt.mit.edu',0,0,0,28,'2003-06-27 03:08:55',28,'2003-06-27 03:08:55', 0),
(8,'SocSci','Ask Us! -- MIT Libraries','socsci@rt.mit.edu','socsci-comment@rt.mit.edu',0,0,0,28,'2003-06-27 03:09:42',28,'2003-06-27 03:09:42',0),
(9,'Test1', 'Ask Us! -- MIT Libraries','test1@rt.mit.edu','test1-comment@rt.mit.edu',0,0,0,28,'2003-06-27 04:57:56',28,'2003-06-27 04:57:57',0),
(10,'Test2','Ask Us! -- MIT Libraries','test2@rt.mit.edu','test2-comment@rt.mit.edu',0,0,0,28,'2003-06-27 04:58:54',28,'2003-06-27 04:58:54',0),
(11,'General-Help','Ask Us! -- MIT Libraries','general-help@rt.mit.edu','general-help-comment@rt.mit.edu',0,0,0,28,'2003-06-27 19:18:02',28,'2003-06-27 19:21:22',0),
(12,'Test3','Ask Us! -- MIT Libraries', 'test3@rt.mit.edu','test3-comment@rt.mit.edu',0,0,0,28,'2003-07-03 20:06:50',28,'2003-07-03 20:07:42',0);
UNLOCK TABLES;
-- Demonstrate problem:
-- This query returns correct results, sorted by Name:
SELECT main.id,main.Name FROM XQueues main
WHERE ((main.Disabled = '0')) ORDER BY main.Name ASC;
-- This query returns the WRONG results; they appear to be sorted by "id", not Name:
-- The only difference is the addition of DISTINCT. It works
-- correctly when the table is created as type MyISAM.
-- It *also* works correctly if SQL_BIG_RESULT is specified along with DISTINCT.
SELECT DISTINCT main.id,main.Name FROM XQueues main
WHERE ((main.Disabled = '0')) ORDER BY main.Name ASC;
