MySQL Bugs: #14583: Bug on query using a LIKE on indexed field with ucs2

Bug #14583	Bug on query using a LIKE on indexed field with ucs2_bin collation
Submitted:	2 Nov 2005 18:33	Modified:	6 Jan 2006 5:46
Reporter:	Xavier FOURNET	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.15 / 5.0.15/BK source	OS:	Windows (Windows XP SP2/linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
A SELECT command returns an incomplete result set, some expected rows are missing.
This happens when doing a query using a LIKE predicate on a field defined as VARCHAR(...) CHARACTER SET ucs2 COLLATE ucs2_bin.

This bug occurs differently according storage type and MySQL version:
- on 4.1.15 with MyISAM the bug seems to not occur
- on 4.1.15 with InnoDB the bug occurs whatever the number of rows in the table
- on 5.0.15 with MyISAM the bug occurs if there are 1 or more rows in the table
- on 5.0.15 with InnoDB the bug occurs if there are 5 or more rows in the table

May be related to fixed bug #12178 ?

How to repeat:
Use the following script (InnoDB can be changed to MyISAM):

DROP TABLE IF EXISTS TestTable;

CREATE TABLE TestTable (
  Name varchar (255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL
) TYPE=InnoDB;

INSERT INTO TestTable(Name) VALUES('a');

-- without index all is working fine, 'a' record is returned
SELECT Name FROM TestTable WHERE Name LIKE 'a%';

CREATE INDEX IX_TestTable ON TestTable(Name);

-- 4.1.15/InnoDB : 'a' record is not returned!
SELECT Name FROM TestTable WHERE Name LIKE 'a%';

INSERT INTO TestTable(Name) VALUES('b');

-- 5.0.15/MyISAM : 'a' record is not returned!
SELECT Name FROM TestTable WHERE Name LIKE 'a%';

INSERT INTO TestTable(Name) VALUES('c');
INSERT INTO TestTable(Name) VALUES('d');
-- 5.0.15/InnoDB: 4 rows, result is OK
SELECT Name FROM TestTable WHERE Name LIKE 'a%';
INSERT INTO TestTable(Name) VALUES('e');
-- 5.0.15/InnoDB: 5 rows, result is KO
SELECT Name FROM TestTable WHERE Name LIKE 'a%';

DROP INDEX IX_TestTable ON TestTable;
-- again without index all is working fine, 'a' record is returned
SELECT Name FROM TestTable WHERE Name LIKE 'a%';

---------------------------------------------------------------------------
mysql> -- 5.0.15/InnoDB: 4 rows, result is OK
mysql> SELECT Name FROM TestTable WHERE Name LIKE 'a%';
+------+
| Name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> INSERT INTO TestTable(Name) VALUES('e');
Query OK, 1 row affected (0.01 sec)

mysql> -- 5.0.15/InnoDB: 5 rows, result is KO
mysql> SELECT Name FROM TestTable WHERE Name LIKE 'a%';
Empty set (0.01 sec)

mysql> 
mysql> DROP INDEX IX_TestTable ON TestTable;
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> -- again without index all is working fine, 'a' record is returned
mysql> SELECT Name FROM TestTable WHERE Name LIKE 'a%';
+------+
| Name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.16-debug |
+--------------+
1 row in set (0.02 sec)

---------------------------------------------------------------------------
mysql> CREATE INDEX IX_TestTable ON TestTable(Name);
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> 
mysql> -- 4.1.15/InnoDB : 'a' record is not returned!
mysql> SELECT Name FROM TestTable WHERE Name LIKE 'a%';
Empty set (0.00 sec)

mysql> 
mysql> INSERT INTO TestTable(Name) VALUES('b');
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> -- 5.0.15/MyISAM : 'a' record is not returned!
mysql> SELECT Name FROM TestTable WHERE Name LIKE 'a%';
Empty set (0.00 sec)

mysql> 
mysql> INSERT INTO TestTable(Name) VALUES('c');
Query OK, 1 row affected (0.01 sec)

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

mysql> -- 5.0.15/InnoDB: 4 rows, result is OK
mysql> SELECT Name FROM TestTable WHERE Name LIKE 'a%';
Empty set (0.00 sec)

mysql> INSERT INTO TestTable(Name) VALUES('e');
Query OK, 1 row affected (0.01 sec)

mysql> -- 5.0.15/InnoDB: 5 rows, result is KO
mysql> SELECT Name FROM TestTable WHERE Name LIKE 'a%';
Empty set (0.01 sec)

mysql> 
mysql> DROP INDEX IX_TestTable ON TestTable;
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> -- again without index all is working fine, 'a' record is returned
mysql> SELECT Name FROM TestTable WHERE Name LIKE 'a%';
+------+
| Name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.16-debug-log |
+------------------+
1 row in set (0.01 sec)

Marko,

please look at this ucs2_bin bug.

Regards,

Heikki

In 4.1-bk, the query
SELECT Name FROM TestTable WHERE Name LIKE 'a%'
returns an empty set after adding the index, because in handler::read_range_first() MySQL passes start_key as follows:
{key = 0x8bab548 "", length = 510, flag = HA_READ_KEY_OR_NEXT}
That is, key = {0x0061, 0x0000, 0x0000, ... }.

However, InnoDB stores the field as the single character 0x0061. The comparison of start_key to the record is delegated to my_strnncollsp_ucs2_bin(), which determines that the longer string is lexicographically greater than the shorter one.

This does not seem to be an InnoDB bug. I will check 5.0-bk shortly.

Marko Mäkelä

Not an InnoDB bug in 5.0-bk either. The statement
SELECT Name FROM TestTable WHERE Name LIKE 'a%';
fails, because in check_quick_keys(), MySQL has min_range.key equal to the 1.5-character string {0x0061,0x00}. Also the max_range.key looks very suspicious.
I'm assigning this bug away from me.

Hello,
I would like to known if a bugfix is planned for this issue. This is blocking for our customers (Swapcom is a software editor for mobile network operators) who wants to deploy our software on MySQL instead of Oracle/SQLServer that we currently support.
Best regards.

Yes, of course. Bugfix is planned.

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/428

The fix is fine. Ok to push.

When InnoDB compares varchar field in ucs2 with given key using bin collation,
it calls my_strnncollsp_ucs2_bin() to perform comparison.
Because field length was lesser than length of key field should be padded
with trailing spaces in order to get correct result. 
Because  my_strnncollsp_ucs2_bin() was calling my_strnncollp_ucs2_bin(), which
doesn't pads field, wrong comparison result was returned. This results in
wrong result set.

Fixed in 4.1.17, cset 1.2466.1.1

Fixed in 5.0.19

Fixed in 5.1.5

Noted in 4.1.17, 5.0.19, 5.1.5 changelogs.