Bug #49028 LIKE doesn't work correct when using a key over a ucs2 column
Submitted: 24 Nov 2009 10:17 Modified: 12 Mar 2010 16:46
Reporter: Alexander Barkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:4.1, 5.0, 5,1, 5.4 bzr OS:Any
Assigned to: Mattias Jonsson
Triage: Triaged: D2 (Serious)

[24 Nov 2009 10:17] Alexander Barkov
Description:
LIKE doesn't work correct when using a key over a ucs2 column.

If you add "IGNORE KEY" clause or drop the key, the query start to return
the expected result.

How to repeat:
drop table t1;
create table t1 (a varchar(2) character set ucs2 collate ucs2_bin, key(a)); 
insert into t1 values ('A'), ('A'),('B'),('C'),('D'),('A\t'),('A\0'),('A\0'),('A\0'),('A\0'),('AZ');
select hex(a) from t1 where a like 'A_';
select hex(a) from t1 ignore key(a) where a like 'A_';

And the output is:

+----------+
| hex(a)   |
+----------+
| 0041005A |
+----------+
1 row in set (0.00 sec)

+----------+
| hex(a)   |
+----------+
| 00410009 |
| 00410000 |
| 00410000 |
| 00410000 |
| 00410000 |
| 0041005A |
+----------+
6 rows in set (0.00 sec)

Suggested fix:
Make the "SELECT..LIKE.." query return 6 rows both
with the "IGNORE KEY" clause, and without.
[24 Nov 2009 10:57] Sveta Smirnova
Thank you for the report.

Verified as described.
[24 Nov 2009 11:37] 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/91405
[26 Nov 2009 9:22] Alexander Barkov
The joint patches for Bug#48737 and Bug#49028 look Ok to push:

http://lists.mysql.com/commits/91400
http://lists.mysql.com/commits/91405
[14 Dec 2009 15:12] 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/93939
[15 Dec 2009 13:20] Mattias Jonsson
Pushed to mysql-5.1-bugteam and mysql-pe.
[19 Dec 2009 8:29] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:33] Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091216134707-o96eqw0u2ynvo9gm) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:37] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[8 Jan 2010 1:17] Paul Dubois
Noted in 5.5.1, 6.0.14 changelogs.

The LIKE operator did not work correctly when using an index for a
ucs2 column. 

Setting report to NDI pending push to 5.1.x, Celosia.
[15 Jan 2010 9:02] Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:mattias.jonsson@sun.com-20091215124801-pae3fhznhzrnvqoj) (merge vers: 5.1.42) (pib:16)
[15 Jan 2010 18:45] Paul Dubois
Noted in 5.1.43 changelog.

Setting report to NDI pending push to Celosia.
[12 Mar 2010 14:09] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:25] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:39] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 16:46] Paul Dubois
Fixed in earlier 5.1.x, 5.5.x.