MySQL Bugs: #11650: LIKE pattern matching using prefix index doesn't return correct result

Bug #11650	LIKE pattern matching using prefix index doesn't return correct result
Submitted:	30 Jun 2005 2:44	Modified:	20 Jul 2005 17:11
Reporter:	Shuichi Tamagawa
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	4.1.12/5.0.8-bk	OS:	Linux (SuSE Linux 9.2/Windows)
Assigned to:	Alexander Barkov	Target Version:

Description:
If the length of the string in LIKE pattern matching condition is longer than the prefix
length, returned result is not correct.

How to repeat:
drop table if exists t1;
create table t1(c1 varchar(50) not null, index(c1(5))) engine = innodb;
insert into t1 values('abcdefg');
insert into t1 values('abcde100');
insert into t1 values('abcde110');
insert into t1 values('abcde111');

mysql> select * from t1 where c1 like 'abcde%'; /*correct*/
+----------+
| c1       |
+----------+
| abcdefg  |
| abcde100 |
| abcde110 |
| abcde111 |
+----------+
4 rows in set (0.07 sec)

mysql> select * from t1 where c1 like 'abcdef%';
Empty set (0.01 sec)
#should return abcdefg

mysql> select * from t1 where c1 like 'abcde1%'; 
Empty set (0.01 sec)
#should return abcde100,abcde110,abcde111,

mysql> select * from t1 where c1 like 'abcde11%';
Empty set (0.00 sec)
#should return abcde110,abcde111,

mysql> select * from t1 where c1 like 'abcde111%';
Empty set (0.00 sec)
#should return abcde111

Note:
This is reproducable only on 4.1 + Linux + InnoDB.
It works fine on 5.0, Windows, MyISAM

Suggested fix:
N/A

I was unable to repeat with current BK source on Linux Slackware:

mysql> select * from t1 where c1 like 'abcde%';
+----------+
| c1       |
+----------+
| abcdefg  |
| abcde100 |
| abcde110 |
| abcde111 |
+----------+
4 rows in set (0.01 sec)

mysql> select * from t1 where c1 like 'abcdef%';
+---------+
| c1      |
+---------+
| abcdefg |
+---------+
1 row in set (0.00 sec)

mysql> select * from t1 where c1 like 'abcde1%'; 
+----------+
| c1       |
+----------+
| abcde100 |
| abcde110 |
| abcde111 |
+----------+
3 rows in set (0.00 sec)

mysql> select * from t1 where c1 like 'abcde11%';
+----------+
| c1       |
+----------+
| abcde110 |
| abcde111 |
+----------+
2 rows in set (0.01 sec)

mysql> select * from t1 where c1 like 'abcde111%';
+----------+
| c1       |
+----------+
| abcde111 |
+----------+
1 row in set (0.01 sec)

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

mysql>

Shuichi,

please show the contents of your my.cnf.

Regards,

Heikki

Hi Miguel, Heikki

It seems that it happens when the table's character set is multi-byte except utf8, ucs2
(sjis,ujis,gbk,big5,euckr,gb2312,cp932,eucjpms).

Please try with the table below.

create table t1(c1 varchar(50) not null, index(c1(5))) engine = innodb default character
set = sjis;

I was able to repeat on both 4.1 and 5.0 (Windows/Linux). It works fine with MyISAM.

Assigning this to Marko.

verifyed on win xp sp2, mysql 4.1.12, sjis charset.

In 5.0.9-bk, the difference between utf8 and sjis seems to be that
ha_innobase::records_in_range() gets the range "abcde"<=c1<="abcde" for utf8, but
"abcdef\0\0\0\0"<=c1<="abcdef\xff\xff\xff\xff" for sjis. This does not seem to be an
InnoDB bug; there does not appear anything within InnoDB code that would treat utf8
differently from sjis. I didn't analyze this in 4.1.12. I'm assigning this to our
character set expert.

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/internals/26714

OK to push.

Pushed into 5.1.10 and 4.1.14.

Documented in 5.0.10 and 4.1.14 changelogs.