MySQL Bugs: #12936: MyISAM table returns 0 rows when using: WHERE , ORDER BY , LIMIT

Bug #12936	MyISAM table returns 0 rows when using: WHERE , ORDER BY , LIMIT
Submitted:	1 Sep 2005 19:16	Modified:	1 Sep 2005 19:40
Reporter:	Micah Wedemeyer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	MySQL 4.1.8-nt-max	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
A MyISAM table I have is returning an empty set for a query that should return rows.  By experimenting with various queries, I was able to determine that there is some sort of negative interaction occurring between the WHERE, ORDER BY, and LIMIT clauses.

Further, changing the engine to InnoDB solved the problem.  Therefore, I would guess that it is a bug with MyISAM.

How to repeat:
Create a database with the following table:

CREATE TABLE agent_core_union (
    ID varchar(30) NOT NULL DEFAULT '-1',
    Core varchar(255) NOT NULL DEFAULT 'unknown_core',
    Agent varchar(255) NOT NULL DEFAULT 'unknown_agent',
    BasicStatus varchar(255) NOT NULL DEFAULT '-1',
    ExtendedStatus text NOT NULL,
    ActualTime varchar(30) NOT NULL DEFAULT '0',
    KEY (ID),
    KEY(ActualTime)
) TYPE=MyISAM DEFAULT CHARACTER SET latin1;

Add a few records (2 should be enough).

Execute the following query:
SELECT Core, Agent, BasicStatus, ActualTime
FROM agent_core_union
WHERE ID = '__some_ID_here__'
ORDER BY ActualTime ASC
LIMIT 1

The expected behavior would be to return a single row with the lowest value for ActualTime.  The actual behavior is the return of an empty set.

Removing the LIMIT clause returns a full record set.
Removing the WHERE and ORDER BY clauses returns the first record from the table.

Suggested fix:
Unknown.  Whatever is required to bring the expected behavior in line with the actual behavior.

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

I was unable to repeat with 4.1.14.

i am have the a similar issue with the latest community edition

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