Bug #47704 hash index on VARCHAR prefix not working correctly
Submitted: 29 Sep 2009 9:13 Modified: 6 Mar 2012 1:23
Reporter: Axel Schwenke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Memory storage engine Severity:S2 (Serious)
Version:5.0.86, 5.1.39 OS:Linux
Assigned to: CPU Architecture:Any

[29 Sep 2009 9:13] Axel Schwenke
Description:
An index scan on a hash index for a VARCHAR column prefix does not find some rows. This seems to happen only under certain conditions:

- the column must be VARCHAR (not CHAR)
- must be a HASH index (not BTREE)
- the index must be on a prefix of the column

There is similar bug #42868 about unique hash index on column prefix. Not sure if this is related.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    c1 VARCHAR(10) NOT NULL,
    KEY i1 (c1(3))
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES ('foo1'), ('bar2'), ('baz3');

SELECT * FROM t1 WHERE c1='bar2';
-- empty result!

SELECT * FROM t1 IGNORE INDEX (i1) WHERE c1='bar2';
-- works!

Suggested fix:
It is debatable if a hash index on a column prefix makes sense at all. Maybe this should be forbidden? But if it is legal, it must work!
[29 Nov 2009 5:43] zhiwen xiao
test on 5.1.36

Attachment: patch.txt (text/plain), 1.90 KiB.

[29 Nov 2009 5:52] zhiwen xiao
heap hash index on a column prefix build hash value on full column and search with prefix.
following patch may work, not verified.
http://bugs.mysql.com/file.php?id=13447
[6 Mar 2012 1:23] Paul DuBois
Noted in 5.6.5 changelog. 

For MEMORY tables, a scan of a HASH index on a VARCHAR column could 
fail to find some rows if the index was on a prefix of the column.