Odd issue with DISTINCT in 5.0.15-nt

[jreider]

Running on Windows Server 2003
I've got a table with ticket issues in it, each having its own status (of about 6 different status types). The most simple query

SELECT DISTINCT ticket_status FROM new_tickets WHERE ticket_status <> "closed" ORDER BY ticket_status ASC

is called to return all non-closed status types. It's currently returning 0 results, but if I change the WHERE section to be <> "new", I'll get results. I checked reserved words, "closed" doesn't appear to be in there, and this query has worked on all other versions of MySQL up to 5. The odd thing is, by changing "closed" to one of the other status' , it works

table structure:

DROP TABLE IF EXISTS `ims`.`new_tickets`;
CREATE TABLE  `ims`.`new_tickets` (
  `billable` varchar(5) default '',
  `ticket_unique_id` varchar(10) NOT NULL default '0',
  `ticket_status` varchar(25) NOT NULL default 'new',
  `new_ticket_date` date NOT NULL default '0000-00-00',
  `new_ticket_time` time NOT NULL default '00:00:00',
  `new_ticket_real_time` time NOT NULL default '00:00:00',
  `new_ticket_real_date` date NOT NULL default '0000-00-00',
  `ticket_tech` varchar(50) NOT NULL default '',
  `assigned_to` varchar(25) NOT NULL default '',
  `billed` varchar(5) NOT NULL default '',
  `client_unique_id` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`ticket_unique_id`),
  KEY `ticket_tech` (`ticket_tech`),
  KEY `ticket_status` (`ticket_status`),
  KEY `client_unique_id` (`client_unique_id`),
  KEY `ticket_unique_id` (`ticket_unique_id`),
  KEY `billable` (`billable`),
  KEY `assigned_to` (`assigned_to`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Any ideas???

[jreider]

UPDATE:

this makes it work, but seems awful silly to have to add it....

SELECT COUNT(DISTINCT ticket_status) as CStat, ticket_status FROM new_tickets WHERE ticket_status <> 'closed' GROUP BY ticket_status

Still curious if anyone else has seen oddities like this? I tried many other DISTINCT queries after stumbling upon this, they all worked as they should.

[r937]

could the difference perhaps be "closed" versus 'closed'?

i don't have 5 to test on, so i'm guessing

according to standard sql, "closed" is a column name, while 'closed' is a string

i heard that version 5 is a little more rigorous about standards than previous versions

[longneck]

also, try running a repair on your table. your first query only looks at your indexes, whereas your second query probably results in a table scan and skips the indexes. if your index is damaged, the repair will fix it.