Re: bug in unicode UCA collations with LIKE comparisons and INDEX

   Hi Jeremy,

Looks like a bug. Thank you very much for reporting.
I added your report into our bug system, and will
fix it asap:

http://bugs.mysql.com/bug.php?id=5324

Regards!

Jeremy March wrote:

> I've found two bugs which appear to be related when performing a LIKE
> comparison using the unicode uca collations.  For the bug to occur there
> must be more than one row in the table being selected and there must be
> an index on the column.  With utf8_unicode_ci columns this causes mysqld
> to hang and with ucs2_unicode_ci columns mysql doesn't find the rows
> when it should.  Here are 3 tests.  I performed them on MySQL 4.1.4 with
> the most recent bitkeeper pulls.  I used Red Hat linux 9.0 on a pentium
> III processor.
> 
> 1) with utf8_general_ci which works as expected.
> 
> 2) with ucs2_unicode_ci which doesn't find any rows when it should find
> 2.
> 
> 3) with utf8_unicode_ci which causes mysqld to hang.
> 
> When these tests are done without an index they work fine.
> 
> best regards,
> Jeremy March
> 
> #test 1
> DROP TABLE IF EXISTS t;
> 
> CREATE TABLE t (c varchar(255) NOT NULL COLLATE utf8_general_ci, INDEX
> (c));
> 
> INSERT INTO t VALUES (CONVERT(_ucs2 0x039C03C903B403B11F770308 USING
> utf8));
> 
> SELECT * FROM t WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
> COLLATE utf8_general_ci;
> #ok with just one row
> 
> INSERT INTO t VALUES (CONVERT(_ucs2 0x039C03C903B4 USING utf8));
> 
> SELECT * FROM t WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
> COLLATE utf8_general_ci;
> #works fine with utf8_general_ci
> 
> DROP TABLE t;
> 
> #test2
> DROP TABLE IF EXISTS t;
> 
> CREATE TABLE t (c varchar(255) NOT NULL COLLATE ucs2_unicode_ci, INDEX
> (c));
> 
> INSERT INTO t VALUES (_ucs2 0x039C03C903B403B11F770308);
> 
> SELECT * FROM t WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci;
> #ok with just one row
> 
> INSERT INTO t VALUES (_ucs2 0x039C03C903B4);
> 
> SELECT * FROM t WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci;
> #doesn't find anything?
> 
> DROP TABLE t;
> 
> #test 3
> DROP TABLE IF EXISTS t;
> 
> CREATE TABLE t (c varchar(255) NOT NULL COLLATE utf8_unicode_ci, INDEX
> (c));
> 
> INSERT INTO t VALUES (CONVERT(_ucs2 0x039C03C903B403B11F770308 USING
> utf8));
> 
> SELECT * FROM t WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
> COLLATE utf8_unicode_ci;
> #ok with just one row
> 
> INSERT INTO t VALUES (CONVERT(_ucs2 0x039C03C903B4 USING utf8));
> 
> SELECT * FROM t WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
> COLLATE utf8_unicode_ci;
> #mysqld hangs here
> 
> DROP TABLE t;


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]