Bug #35963 'select', 'where', 'order by' return no rows

• Submitted: 10 Apr 2008 16:11
• Modified: 10 Apr 2008 18:24
• Reporter: aaron sulwer
• Status: Duplicate
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: 6.0.4
• OS: Windows (vista ultimate service pack 1 build 6001)
• CPU Architecture: Any

[10 Apr 2008 16:11] aaron sulwer

Description:
MySQL 6.0.4-alpha-community

here is how i created the table

CREATE TABLE `posts` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `topicid` int(10) unsigned NOT NULL default '0',
  `userid` int(10) unsigned NOT NULL default '0',
  `added` datetime default NULL,
  `body` text,
  `editedby` int(10) unsigned NOT NULL default '0',
  `editedat` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `topicid` (`topicid`),
  KEY `userid` (`userid`),
  FULLTEXT KEY `body` (`body`)
) ENGINE=MyISAM AUTO_INCREMENT=1;

here is the select query that returns no results

SELECT * FROM posts WHERE topicid=1 ORDER BY id

now when i run this one all of the results display when there is data to display of course

SELECT * FROM posts
and this one too
SELECT * FROM posts WHERE topicid=1
which would display only 1

i have tested this using query analyzer and from my web app with the same results

How to repeat:
create the table, add a few rows then perform select query with where and order by should return nothing, remove order by and something is returned

Suggested fix:
workaround? removed order by when in a non critical situation but SHOULD work with order by

[10 Apr 2008 18:24] Sveta Smirnova

Thank you for the report.

This seems to be duplicate of bug #33758. After version 6.0.5 is released please test patch with your data and bug still exists with your data reopen the report.