Bug #7350 Error in UCS2 comparisons of InnoDB tables
Submitted: 16 Dec 2004 11:08 Modified: 14 Jan 2005 13:24
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.1.8, 5.0.3 OS:Any (all)
Assigned to: Marko Mäkelä CPU Architecture:Any

[16 Dec 2004 11:08] Marko Mäkelä
The InnoDB function cmp_whole_field() in rem0cmp.c strips trailing 0x20 bytes from the fields being compared. This does not work with character sets where the 0x20 byte can occur in the encodings of other characters than space (' ').

How to repeat:
create table foo6(a char(2) primary key)engine=innodb charset=ucs2;

-- inserting 'a' and 'aG' (with dot above the G) fails
mysql> insert into foo6 values('a'),(0x00610120);
ERROR 1062 (23000): Duplicate entry '' for key 1
-- inserting 'a' and 'ag' (with dot above the g) succeeds:
mysql> insert into foo6 values('a'),(0x00610121);
Query OK, 2 rows affected (0,01 sec)
Records: 2  Duplicates: 0  Warnings: 0

Suggested fix:
Remove the trimming of 0x20 bytes from rem0cmp.c.
[16 Dec 2004 14:45] Marko Mäkelä
There are related errors in the MySQL->InnoDB and InnoDB->MySQL row format conversions that affect VARCHAR columns. When UCS2 VARCHARs are converted to MySQL, they are padded with 0x2020 instead of spaces. I'm working on fixes.
[16 Dec 2004 21:50] Hartmut Holzgraefe
I've been able to verify this using the create statement given above
and the following two INSERT statements on InnoDB, MyISAM and HEAP
so it seems to be a more general problem and not neccesarily InnoDB specific

INSERT INTO foo6 VALUES(0x0120);
INSERT INTO foo6 VALUES(0x0121);
[17 Dec 2004 22:18] Marko Mäkelä
I've committed a fix to the 4.1 tree.
Hartmut, are you sure that U+0120 and U+0121 are unequal in the collation you're using? In the default UCS2 collation, both characters correspond to 'g'. Instead of U+0121, try something completely different, such as U+01ff. I didn't find any error in MyISAM, but I didn't try very hard.
[22 Dec 2004 0:46] Hartmut Holzgraefe
ok, my fault ...
i just tested 0x0121, 0x0122 and 0x0123, should have tried one more ... :o
[14 Jan 2005 13:24] Marko Mäkelä
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at

Additional info:

I have now pushed the fix to the 4.1 tree. It'll be included in 4.1.10 and 5.0.4.