Problem with index on MEMORY table type, MySQL 4.1.8

[Steve Huth]

I have a temporary table of type memory that has been working fine until this weekend when we updated to 4.1.8 from 4.1.7 (haven't gone to 4.1.9 yet because we use Gentoo portage, and 4.1.9 does not seem to be there yet). The table has an index on a column, the column is defined in the create statement as varchar(5) NOT NULL default ''. Immediately after creation of the table I insert several thousand records and run a select using the above column/index, with a simple where clause in the form "WHERE column='MyCharacterValue' ". After the update to 4.1.8, no results are returned (which is incorrect).

I tested several situations: If I change the table type to InnoDB and leave everything else the same, the where clause returns the correct records (over 400 of them).

If I leave the table type at MEMORY, but drop the index, the where clause again works. If I leave the index on and change the where clause to read "WHERE column LIKE 'MyCharacterValue' " (no wildcards) it also works--in fact, every condition seems to work just fine except the equals sign when the column is indexed.

When I use an explain statement with the select (with the original table definition of type memory and the index defined) it lists the column as a possible key, a query type of simple (correct) and a join type of const (which can't be right as there is more than one result row), but no key is actually chosen. Any other type of select (using <>, like, etc...) lists all as the join type, and therefore there is no key but the records seem to be getting picked up by the table scan. I believe the join type should be ref, and the ref should be const, but the optimizer currently disagrees with me.

Any suggestions on what is happening here and what I can do about it? I don't see anything in the 4.1.9 readme that suggests it has been dealt with and I can't find any bug reports on the issue either. For now I'm going to revert to 4.1.7. Thanks,