MySQL Bugs: #12796: key on MEMORY table fails to find row

Bug #12796	key on MEMORY table fails to find row
Submitted:	25 Aug 2005 6:35	Modified:	2 Feb 2006 13:34
Reporter:	Timothy Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.14	OS:	Any (any)
Assigned to:	Mikael Ronström	CPU Architecture:	Any

Description:
I built from the current 4.1-bk code to test this.

The test case is simple (see How to repeat).

If the "select * from t1" statement is not there, then the bug doesn't show.  However, that can be any statement that uses the table.  I tested with "SHOW TABLE STATUS LIKE 't1" and "SELECT COUNT(*) FROM t1".

How to repeat:
mysql -e 'drop table if exists t1' test

mysql -e 'create table t1 (id int, key (id)) engine=heap' test

mysql -e 'delete from t1; select * from t1; insert delayed into t1 values (0), (1); select * from t1; select * from t1 where id = 0' test

mysql -e 'delete from t1; select * from t1; insert delayed into t1 values (0), (1); select * from t1; select * from t1 where id = 0' test

When I run the above, I get:

18:30 ~/m/csc/6289$ sh bug.sh
+------+
| id   |
+------+
|    0 |
|    1 |
+------+
+------+
| id   |
+------+
|    0 |
+------+
+------+
| id   |
+------+
|    0 |
|    1 |
+------+
18:30 ~/m/csc/6289$ 

Notice that the second "select * from t1 where id = 0" returns no rows.  If the query is changed to "select * from t1 ignore index (id) where id = 0", then it returns the row as expected.

Suggested fix:

I'm still not sure what the source of the problem is, unfortunately.

It may be helpful to have this simpler test case:

drop table if exists t1;
create table t1 (id int, key (id)) engine=heap;
insert delayed into t1 values (0);
delete from t1;

-- If this is taken out, the bug doesn't show up
select * from t1;

insert delayed into t1 values (0), (1);

-- This returns the empty set
select * from t1 where id = 0;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32667

The fix for this will appear in 4.1.19
It has some slight change of behaviour in some sequences when previously it
could choose both the hash and btree index dependent on which was first
defined whereas in those situations it will now always select the btree since
the btree knows it is only in record in range whereas the hash can only provide
that as an estimate.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 4.1.19 changelog. Closed.

Updated synopsis to reflect the fact that we now refer to the table handler in question as MEMORY.