Bug #61238 Hash indexes with a prefix length do not work
Submitted: 20 May 2011 4:17 Modified: 20 May 2011 15:25
Reporter: Alexey Kopytov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Memory storage engine Severity:S3 (Non-critical)
Version:5.1.58, 5.5 OS:Mac OS X
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[20 May 2011 4:17] Alexey Kopytov
Description:
Hash indexes do not work when created with a prefix length:

mysql> create table t1(a varchar(64)) engine=memory;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values ('aa'), ('bb');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 where a = 'aa';
+------+
| a    |
+------+
| aa   |
+------+
1 row in set (0.00 sec)

# Add a hash index with a prefix length

mysql> alter table t1 add key (a(1));
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 where a = 'aa';
Empty set (0.00 sec)

# Change the index to B-Tree

mysql> alter table t1 drop key a, add key (a(1)) using btree;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 where a = 'aa';
+------+
| a    |
+------+
| aa   |
+------+
1 row in set (0.00 sec)

How to repeat:
create table t1(a varchar(64)) engine=memory;
insert into t1 values ('aa'), ('bb');
select * from t1 where a = 'aa';
alter table t1 add key (a(1));
select * from t1 where a = 'aa';
[20 May 2011 5:25] Shane Bester
see also bug #42868
[20 May 2011 6:34] Valeriy Kravchuk
What exact versions are we talking about? I do not see the problem with 5.5.11 on Windows, for example:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.22 sec)

mysql> create table t1(a varchar(64)) engine=memory;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values ('aa'), ('bb');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 where a = 'aa';
+------+
| a    |
+------+
| aa   |
+------+
1 row in set (0.00 sec)

mysql> alter table t1 add key (a(1));
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 where a = 'aa';
+------+
| a    |
+------+
| aa   |
+------+
1 row in set (0.02 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` varchar(64) DEFAULT NULL,
  KEY `a` (`a`(1))
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> alter table t1 drop key a, add key (a(1)) using hash;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` varchar(64) DEFAULT NULL,
  KEY `a` (`a`(1)) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t1 where a = 'aa';
+------+
| a    |
+------+
| aa   |
+------+
1 row in set (0.00 sec)
[20 May 2011 6:42] Alexey Kopytov
Orignally I tested it on a relatively fresh 5.5 clone from Launchpad on Mac. 

Now I tried mysql-5.5.10-linux2.6-x86_64.tar.gz with the same results:

mysql> create table t1(a varchar(64)) engine=memory;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values ('aa'), ('bb');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 where a = 'aa';
+------+
| a    |
+------+
| aa   |
+------+
1 row in set (0.01 sec)

mysql> alter table t1 add key (a(1));
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 where a = 'aa';
Empty set (0.02 sec)
[20 May 2011 15:25] Valeriy Kravchuk
Verified with current mysql-5.1 from bzr on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.58-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(a varchar(64)) engine=memory;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values ('aa'), ('bb');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 where a = 'aa';
+------+
| a    |
+------+
| aa   |
+------+
1 row in set (0.00 sec)

mysql> alter table t1 add key (a(1));
Query OK, 2 rows affected (0.28 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 where a = 'aa';
Empty set (0.03 sec)