SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    New Castle, PA
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Odd issue with DISTINCT in 5.0.15-nt

    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

    Code:
    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:
    Code:
    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???

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    New Castle, PA
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    UPDATE:

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

    Code:
    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.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •