Bug #42868 unique prefixed indexes on varchar columns don't work in memory engine

• Submitted: 15 Feb 2009 20:00
• Modified: 15 Feb 2009 20:27
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: Memory storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.74, 5.1.48
• OS: Any
• CPU Architecture: Any
• Tags: hash, prefix, unique

[15 Feb 2009 20:00] Shane Bester

Description:
when creating a unique index on a prefix of a column, the prefix uniqueness is not enforced with memory engine using hash index.

How to repeat:
drop table if exists `t2`;
create table `t2` (`c1` varchar(2),unique key using hash(`c1`(1))) engine=memory;
insert into `t2` values  ('12');
insert into `t2` values  ('13');
select * from `t2`;

[15 Feb 2009 20:27] Valeriy Kravchuk

Looks like latin1 character set is essential here:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists `t2`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t2` (`c1` varchar(2),unique key using hash(`c1`(1))) engine
=memory;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into `t2` values  ('12');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `t2` values  ('13');
ERROR 1062 (23000): Duplicate entry '13' for key 1
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` varchar(2) default NULL,
  UNIQUE KEY `c1` USING HASH (`c1`(1))
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> drop table if exists `t2`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t2` (`c1` varchar(2),unique key using hash(`c1`(1))) engine
=memory charset=latin1;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into `t2` values  ('12');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `t2` values  ('13');
Query OK, 1 row affected (0.00 sec)

I am also NOT sure UNIQUE and HASH is a nice combination.

[14 Jul 2010 6:50] MySQL Verification Team

please look at this testcase also:

drop table if exists t1;
create table t1(data varchar(30),unique(data(1))) engine=memory;
replace into t1 set data='aa';
replace into t1 set data='aa';

It yields:

mysql> replace into t1 set data='aa';
ERROR 1032 (HY000): Can't find record in 't1'

[20 May 2011 5:26] MySQL Verification Team

see also bug #61238