MySQL Bugs: #12178: Bug on index using a field with ucs2

Bug #12178	Bug on index using a field with ucs2_bin collation
Submitted:	26 Jul 2005 14:50	Modified:	6 Oct 2005 0:47
Reporter:	Xavier FOURNET	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.12a/4.1.13/4.1 BK source	OS:	Windows (Windows XP SP2/Linux)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
A SELECT command return an empty set while it must return a row.
This happens when using CHARACTER SET ucs2 COLLATE ucs2_bin definition on indexed fields that are used by the SELECT command.

How to repeat:
Use MySQL 4.1.12a or 4.1.13. Doesn't affect 5.0.9

First a simple script that shows the problem.
It works without "COLLATE ucs2_bin", or by replacing ucs2_bin by ucs2_general_ci or without the CREATE UNIQUE INDEX

DROP TABLE IF EXISTS TestTable;

CREATE TABLE TestTable (
  Id int NOT NULL PRIMARY KEY,
  Name varchar (255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL
) TYPE=InnoDB;

CREATE UNIQUE INDEX UQ_TestTable ON TestTable(Name);

INSERT INTO TestTable(Id,Name) VALUES(1,'old');
UPDATE TestTable SET Name='new' WHERE Id=1;
SELECT Id,Name FROM TestTable WHERE Name='new';
SELECT Id,Name FROM TestTable;
SELECT Id,Name FROM TestTable WHERE Name='new';

A second more complex script show same type of problems.
Be carefull, this is more tricky because the bug appears only at the seven iteration!

DROP TABLE IF EXISTS TestTable;

CREATE TABLE TestTable (
  Id int NOT NULL AUTO_INCREMENT,
  DomainId int NOT NULL,
  Name varchar (255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  FullName varchar (448) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  ParentId int NULL,
  CONSTRAINT PK_TestTable PRIMARY KEY (ID)
) TYPE=InnoDB;

CREATE INDEX IX_TestTable_ParentId ON TestTable(ParentId);
CREATE UNIQUE INDEX UQ_TestTable ON TestTable(DomainId,Name,ParentId);
CREATE UNIQUE INDEX UQ_TestTable_FullName ON TestTable(DomainId, FullName(383));
ALTER TABLE TestTable ADD CONSTRAINT FK_TestTable_ParentId FOREIGN KEY (ParentId) REFERENCES TestTable (Id);

-- iteration#1
INSERT INTO TestTable(DomainId,Name,FullName,ParentId) VALUES(1,'old','old',NULL);
SET @p=LAST_INSERT_ID();
UPDATE TestTable SET Name='new',FullName='new',ParentId=NULL WHERE Id=@p;
SELECT Id,DomainId,Name,FullName,ParentId FROM TestTable WHERE DomainId=1 AND Name='new' AND ParentId IS NULL;
DELETE FROM TestTable WHERE Id=@p;

-- iteration#2
INSERT INTO TestTable(DomainId,Name,FullName,ParentId) VALUES(1,'old','old',NULL);
SET @p=LAST_INSERT_ID();
UPDATE TestTable SET Name='new',FullName='new',ParentId=NULL WHERE Id=@p;
SELECT Id,DomainId,Name,FullName,ParentId FROM TestTable WHERE DomainId=1 AND Name='new' AND ParentId IS NULL;
DELETE FROM TestTable WHERE Id=@p;

-- iteration#3
INSERT INTO TestTable(DomainId,Name,FullName,ParentId) VALUES(1,'old','old',NULL);
SET @p=LAST_INSERT_ID();
UPDATE TestTable SET Name='new',FullName='new',ParentId=NULL WHERE Id=@p;
SELECT Id,DomainId,Name,FullName,ParentId FROM TestTable WHERE DomainId=1 AND Name='new' AND ParentId IS NULL;
DELETE FROM TestTable WHERE Id=@p;

-- iteration#4
INSERT INTO TestTable(DomainId,Name,FullName,ParentId) VALUES(1,'old','old',NULL);
SET @p=LAST_INSERT_ID();
UPDATE TestTable SET Name='new',FullName='new',ParentId=NULL WHERE Id=@p;
SELECT Id,DomainId,Name,FullName,ParentId FROM TestTable WHERE DomainId=1 AND Name='new' AND ParentId IS NULL;
DELETE FROM TestTable WHERE Id=@p;

-- iteration#5
INSERT INTO TestTable(DomainId,Name,FullName,ParentId) VALUES(1,'old','old',NULL);
SET @p=LAST_INSERT_ID();
UPDATE TestTable SET Name='new',FullName='new',ParentId=NULL WHERE Id=@p;
SELECT Id,DomainId,Name,FullName,ParentId FROM TestTable WHERE DomainId=1 AND Name='new' AND ParentId IS NULL;
DELETE FROM TestTable WHERE Id=@p;

-- iteration#6
INSERT INTO TestTable(DomainId,Name,FullName,ParentId) VALUES(1,'old','old',NULL);
SET @p=LAST_INSERT_ID();
UPDATE TestTable SET Name='new',FullName='new',ParentId=NULL WHERE Id=@p;
SELECT Id,DomainId,Name,FullName,ParentId FROM TestTable WHERE DomainId=1 AND Name='new' AND ParentId IS NULL;
DELETE FROM TestTable WHERE Id=@p;

-- iteration#7
INSERT INTO TestTable(DomainId,Name,FullName,ParentId) VALUES(1,'old','old',NULL);
SET @p=LAST_INSERT_ID();
UPDATE TestTable SET Name='new',FullName='new',ParentId=NULL WHERE Id=@p;
SELECT Id,DomainId,Name,FullName,ParentId FROM TestTable WHERE DomainId=1 AND Name='new' AND ParentId IS NULL;
DELETE FROM TestTable WHERE Id=@p;

-- iteration#8
INSERT INTO TestTable(DomainId,Name,FullName,ParentId) VALUES(1,'old','old',NULL);
SET @p=LAST_INSERT_ID();
UPDATE TestTable SET Name='new',FullName='new',ParentId=NULL WHERE Id=@p;
SELECT Id,DomainId,Name,FullName,ParentId FROM TestTable WHERE DomainId=1 AND Name='new' AND ParentId IS NULL;
DELETE FROM TestTable WHERE Id=@p;

Suggested fix:
Workaround around is obvious not to use the ucs2_bin collation but it is not applicable for me because i need a case sensitive indexed unique key.
By the way, is it planned that mysql have ucs2 case sensitive collation ?

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14-debug-log

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

mysql> use test;
Database changed
mysql> CREATE TABLE TestTable (
    ->   Id int NOT NULL PRIMARY KEY,
    ->   Name varchar (255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> CREATE UNIQUE INDEX UQ_TestTable ON TestTable(Name);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO TestTable(Id,Name) VALUES(1,'old');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE TestTable SET Name='new' WHERE Id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT Id,Name FROM TestTable WHERE Name='new';
Empty set (0.00 sec)

mysql> SELECT Id,Name FROM TestTable;
+----+------+
| Id | Name |
+----+------+
|  1 | new  |
+----+------+
1 row in set (0.01 sec)

mysql> SELECT Id,Name FROM TestTable WHERE Name='new';
Empty set (0.00 sec)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.11-beta

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

mysql> use test;
Database changed
mysql> CREATE TABLE TestTable (
    ->   Id int NOT NULL PRIMARY KEY,
    ->   Name varchar (255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> 
mysql> CREATE UNIQUE INDEX UQ_TestTable ON TestTable(Name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO TestTable(Id,Name) VALUES(1,'old');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE TestTable SET Name='new' WHERE Id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT Id,Name FROM TestTable WHERE Name='new';
+----+------+
| Id | Name |
+----+------+
|  1 | new  |
+----+------+
1 row in set (0.00 sec)

mysql> SELECT Id,Name FROM TestTable;
+----+------+
| Id | Name |
+----+------+
|  1 | new  |
+----+------+
1 row in set (0.00 sec)

mysql> SELECT Id,Name FROM TestTable WHERE Name='new';
+----+------+
| Id | Name |
+----+------+
|  1 | new  |
+----+------+
1 row in set (0.00 sec)

mysql>

An easier script reproducing the same problem:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  Name varchar (255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  INDEX Name (Name)
) TYPE=InnoDB;

# This returns one row as expected
INSERT INTO t1 VALUES ('old');
SELECT name as name_after_insert FROM t1 WHERE Name='old';

# This doesn't return rows: wrong result
UPDATE t1 SET Name='new';
SELECT name as name_after_update FROM t1 WHERE Name='new';

# This returns one row as expected again
ALTER TABLE t1 DROP INDEX Name;
ALTER TABLE t1 ADD INDEX Name(Name);
SELECT name as name_after_recreate FROM t1 WHERE Name='new';

DROP TABLE t1;

Notes:

1. If I change row type to CHAR instead of VARCHAR, it works fine with InnoDB.
2. If I change ENGINE to MyISAM, HEAP or BDB, it works fine with VARCHAR

It seems to be a bug in InnoDB. Reassigning to Heikki.

Assigning this UCS2_bin bug to Marko. I can still repeat this with 4.1.14.

Works on 5.0.14-rc-debug / Linux:

mysql> CREATE TABLE t1 (
    ->   Name varchar (255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
    ->   INDEX Name (Name)
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.17 sec)

mysql> INSERT INTO t1 VALUES ('old');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT name as name_after_insert FROM t1 WHERE Name='old';
+-------------------+
| name_after_insert |
+-------------------+
| old               |
+-------------------+
1 row in set (0.00 sec)
mysql> UPDATE t1 SET Name='new';
Query OK, 1 row affected (0.38 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT name as name_after_update FROM t1 WHERE Name='new';
+-------------------+
| name_after_update |
+-------------------+
| new               |
+-------------------+
1 row in set (0.01 sec)

mysql> ALTER TABLE t1 DROP INDEX Name;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX Name(Name);
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT name as name_after_recreate FROM t1 WHERE Name='new';
+---------------------+
| name_after_recreate |
+---------------------+
| new                 |
+---------------------+
1 row in set (0.00 sec)

So should I understand that no fix will be done on the 4.1 branch and that we have to wait that the 5.0 will be 'production ready' ?

I will have a look at it.

The cause of the bug is that when InnoDB tries to strip trailing spaces, it will actually strip 0x20 bytes instead of 0x0020 words. This is related to at least the following bugs:
http://bugs.mysql.com/bug.php?id=10511
http://bugs.mysql.com/bug.php?id=7350

Heikki, can we backport the mbminlen and mbmaxlen fields of dtype from 5.0 to 4.1? Or should we leave the UCS2 bugs in 4.1 unfixed?

MySQL/InnoDB 4.1 does trim and pad trailing spaces UCS2 strings properly, but somehow the 'new' value in the UPDATE statement will not be trimmed.

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/internals/30558

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
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

This bug was already fixed in recent 5.0 versions, in a different way. The new fix will be included in the next 4.1 release.
Please see also Bug #10511.

Sorry, I forgot that this fix needs to be documented in the manual.

Noted in 4.1.15 changelog.