Bug #5679 utf8_unicode_ci LIKE--trailing % doesn't equal zero characters

• Submitted: 21 Sep 2004 3:18
• Modified: 21 Oct 2004 10:06
• Reporter: Jeremy March
• Status: Closed
• Category: Server
• Severity: S2 (Serious)
• Version: 4.1.5
• OS: Linux (redhat 9.0)
• Assigned to: Alexander Barkov

[21 Sep 2004 3:18] Jeremy March

Description:
When performing a LIKE comparison using the utf8_unicode_ci collation and using an index
a trailing % doesn't equal zero characters.  % at the beginning and middle of a search
string does allow itself to equal no character.

So
SELECT c FROM t WHERE c LIKE 'a%';
will not select "a" but it will select "aa".

This works fine in utf8_general_ci and ucs2_unicode_ci.  I tested with the latest bk
pulls.

How to repeat:
#trailing wild card with index bug in LIKE comparisons
DROP TABLE IF EXISTS t;

CREATE TABLE t (
  c char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO t VALUES ('a'),('ab'),('aba');

SELECT c FROM t WHERE c LIKE 'a%';
#selects 3 rows without an index

ALTER TABLE t ADD INDEX (c);

SELECT c FROM t WHERE c LIKE 'a%';
#should find 3 rows but only finds 2

DROP TABLE t;