MySQL Bugs: #28500: Falcon: searches fail if unique prefixed index

Bug #28500	Falcon: searches fail if unique prefixed index
Submitted:	17 May 2007 18:23	Modified:	6 Jun 2007 12:09
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0.1-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
I create a Falcon table with a varchar utf8 column, Turkish collation.
I create a unique prefix index on the column.
I insert three rows, one of which is 'iiiii'.
I search for 'iiiii'.
I find nothing.

ChangeSet@1.2519, 2007-05-16

How to repeat:
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create table i4 (i varchar(100) character set utf8 collate utf8_turkish_ci) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into i4 values ('iiii'),('iiiii'),('IIIII');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table i4 engine=falcon;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create unique index i on i4 (i(4));
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from i4 where i = 'iiiii';
Empty set (0.01 sec)

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/commits/27044

ChangeSet@1.2525, 2007-05-20 13:58:20+02:00, hakank@lu0011.wdf.sap.corp +3 -0
  Added test case for Bug#28500.

# This bug is not related to a Turkish index. The underlying problem is
#   that a partial unique index violation is not detected.
#   Therefore, the test was simplified to show the real problem.

CREATE TABLE t1 (a varchar(5)) Engine Falcon;
CREATE UNIQUE INDEX i1 on t1 (a(4));
INSERT INTO t1 VALUES ('1234');

-- This should fail but it doesn't.
INSERT INTO t1 VALUES ('1234');

In order to make Falcon assure that unique partial keys are unique for the stored portion of the key, changes were made to the function Table::isDuplicate() and a new pure virtual function called truncate was added to the Collation class and each of it's derived classes.  This is usefull since each collation may have different rules as to how to apply the partial key.  MySQL Collations interpret the partial key value as the maximum number of bytes that may be needed to store the partial number of characters.

falcon_bug_28500.test passes now. Also the original bug report is fixed now:

[11:58] root@test>create table i4 (i varchar(100) character set utf8 collate
    -> utf8_turkish_ci) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

[11:58] root@test>insert into i4 values ('iiii'),('iiiii'),('IIIII');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

[11:58] root@test>alter table i4 engine=falcon;
Query OK, 3 rows affected (0.49 sec)
Records: 3  Duplicates: 0  Warnings: 0

[11:58] root@test>create unique index i on i4 (i(4));
ERROR 1582 (23000): Duplicate entry 'iiiii' for key 'i'
6.0.1-alpha-debug

A note has been added to the 6.0.1 changelog.