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:Microsoft Windows (Windows XP SP2/linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[2 Nov 2005 18:33] Xavier FOURNET
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%';
[2 Nov 2005 23:07] Godofredo Miguel Solorzano
---------------------------------------------------------------------------
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)
[3 Nov 2005 11:01] Heikki Tuuri
Marko,

please look at this ucs2_bin bug.

Regards,

Heikki
[3 Nov 2005 12:36] Marko Mäkelä
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ä
[3 Nov 2005 13:29] 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.
[29 Nov 2005 9:45] Xavier FOURNET
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.
[29 Nov 2005 12:27] Sergei Golubchik
Yes, of course. Bugfix is planned.
[27 Dec 2005 17:16] Bugs System
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
[27 Dec 2005 17:50] Alexander Barkov
The fix is fine. Ok to push.
[28 Dec 2005 15:26] Evgeny Potemkin
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
[28 Dec 2005 16:16] Evgeny Potemkin
Fixed in 5.0.19
[28 Dec 2005 18:52] Evgeny Potemkin
Fixed in 5.1.5
[6 Jan 2006 5:46] Paul Dubois
Noted in 4.1.17, 5.0.19, 5.1.5 changelogs.