ctype_innodb_like.inc:

  New BitKeeper file ``mysql-test/include/ctype_innodb_like.inc''
Many files:
  bug#11650: LIKE pattern matching using prefix index doesn't return correct result
  min and max values were too long in the case of prefix key.
  Fix my_like_range functions not to exceed prefix length.
ctype_innodb_like.inc:
  new file

14 files changed, 236 insertions, 5 deletions

diff --git a/mysql-test/include/ctype_innodb_like.inc b/mysql-test/include/ctype_innodb_like.inc
new file mode 100644
index 00000000000..ae43342885a
--- /dev/null
+++ b/mysql-test/include/ctype_innodb_like.inc
@@ -0,0 +1,21 @@
+#
+# Bug#11650: LIKE pattern matching using prefix index
+# doesn't return correct result
+#
+--disable_warnings
+#
+# This query creates a column using
+# character_set_connection and
+# collation_connection.
+#
+create table t1 engine=innodb select repeat('a',50) as c1;
+--enable_warnings
+alter table t1 add index(c1(5));
+
+insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
+select collation(c1) from t1 limit 1;
+select c1 from t1 where c1 like 'abcdef%' order by c1;
+select c1 from t1 where c1 like 'abcde1%' order by c1;
+select c1 from t1 where c1 like 'abcde11%' order by c1;
+select c1 from t1 where c1 like 'abcde111%' order by c1;
+drop table t1;
diff --git a/mysql-test/r/ctype_big5.result b/mysql-test/r/ctype_big5.result
index c63704f6d9d..8d2c39df853 100644
--- a/mysql-test/r/ctype_big5.result
+++ b/mysql-test/r/ctype_big5.result
@@ -67,6 +67,28 @@ big5_chinese_ci	6109
 big5_chinese_ci	61
 big5_chinese_ci	6120
 drop table t1;
+create table t1 engine=innodb select repeat('a',50) as c1;
+alter table t1 add index(c1(5));
+insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
+select collation(c1) from t1 limit 1;
+collation(c1)
+big5_chinese_ci
+select c1 from t1 where c1 like 'abcdef%' order by c1;
+c1
+abcdefg
+select c1 from t1 where c1 like 'abcde1%' order by c1;
+c1
+abcde100
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde11%' order by c1;
+c1
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde111%' order by c1;
+c1
+abcde111
+drop table t1;
 SET collation_connection='big5_bin';
 create table t1 select repeat('a',4000) a;
 delete from t1;
@@ -77,6 +99,28 @@ big5_bin	6109
 big5_bin	61
 big5_bin	6120
 drop table t1;
+create table t1 engine=innodb select repeat('a',50) as c1;
+alter table t1 add index(c1(5));
+insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
+select collation(c1) from t1 limit 1;
+collation(c1)
+big5_bin
+select c1 from t1 where c1 like 'abcdef%' order by c1;
+c1
+abcdefg
+select c1 from t1 where c1 like 'abcde1%' order by c1;
+c1
+abcde100
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde11%' order by c1;
+c1
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde111%' order by c1;
+c1
+abcde111
+drop table t1;
 SET NAMES big5;
 CREATE TABLE t1 (a text) character set big5;
 INSERT INTO t1 VALUES ('��');
diff --git a/mysql-test/r/ctype_cp932.result b/mysql-test/r/ctype_cp932.result
index ad1c3b09bb6..0e28edc6df3 100644
--- a/mysql-test/r/ctype_cp932.result
+++ b/mysql-test/r/ctype_cp932.result
@@ -8586,6 +8586,28 @@ cp932_japanese_ci	6109
 cp932_japanese_ci	61
 cp932_japanese_ci	6120
 drop table t1;
+create table t1 engine=innodb select repeat('a',50) as c1;
+alter table t1 add index(c1(5));
+insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
+select collation(c1) from t1 limit 1;
+collation(c1)
+cp932_japanese_ci
+select c1 from t1 where c1 like 'abcdef%' order by c1;
+c1
+abcdefg
+select c1 from t1 where c1 like 'abcde1%' order by c1;
+c1
+abcde100
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde11%' order by c1;
+c1
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde111%' order by c1;
+c1
+abcde111
+drop table t1;
 SET collation_connection='cp932_bin';
 create table t1 select repeat('a',4000) a;
 delete from t1;
@@ -8596,3 +8618,25 @@ cp932_bin	6109
 cp932_bin	61
 cp932_bin	6120
 drop table t1;
+create table t1 engine=innodb select repeat('a',50) as c1;
+alter table t1 add index(c1(5));
+insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
+select collation(c1) from t1 limit 1;
+collation(c1)
+cp932_bin
+select c1 from t1 where c1 like 'abcdef%' order by c1;
+c1
+abcdefg
+select c1 from t1 where c1 like 'abcde1%' order by c1;
+c1
+abcde100
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde11%' order by c1;
+c1
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde111%' order by c1;
+c1
+abcde111
+drop table t1;
diff --git a/mysql-test/r/ctype_sjis.result b/mysql-test/r/ctype_sjis.result
index 98e5992f374..e6669c63621 100644
--- a/mysql-test/r/ctype_sjis.result
+++ b/mysql-test/r/ctype_sjis.result
@@ -81,6 +81,28 @@ sjis_japanese_ci	6109
 sjis_japanese_ci	61
 sjis_japanese_ci	6120
 drop table t1;
+create table t1 engine=innodb select repeat('a',50) as c1;
+alter table t1 add index(c1(5));
+insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
+select collation(c1) from t1 limit 1;
+collation(c1)
+sjis_japanese_ci
+select c1 from t1 where c1 like 'abcdef%' order by c1;
+c1
+abcdefg
+select c1 from t1 where c1 like 'abcde1%' order by c1;
+c1
+abcde100
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde11%' order by c1;
+c1
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde111%' order by c1;
+c1
+abcde111
+drop table t1;
 SET collation_connection='sjis_bin';
 create table t1 select repeat('a',4000) a;
 delete from t1;
@@ -91,6 +113,28 @@ sjis_bin	6109
 sjis_bin	61
 sjis_bin	6120
 drop table t1;
+create table t1 engine=innodb select repeat('a',50) as c1;
+alter table t1 add index(c1(5));
+insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
+select collation(c1) from t1 limit 1;
+collation(c1)
+sjis_bin
+select c1 from t1 where c1 like 'abcdef%' order by c1;
+c1
+abcdefg
+select c1 from t1 where c1 like 'abcde1%' order by c1;
+c1
+abcde100
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde11%' order by c1;
+c1
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde111%' order by c1;
+c1
+abcde111
+drop table t1;
 SET NAMES sjis;
 SELECT HEX('�����@\�\') FROM DUAL;
 HEX('�����@�_�\')
diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result
index fd6c501499b..dec1baf4a4b 100644
--- a/mysql-test/r/ctype_ujis.result
+++ b/mysql-test/r/ctype_ujis.result
@@ -2217,6 +2217,28 @@ ujis_japanese_ci	6109
 ujis_japanese_ci	61
 ujis_japanese_ci	6120
 drop table t1;
+create table t1 engine=innodb select repeat('a',50) as c1;
+alter table t1 add index(c1(5));
+insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
+select collation(c1) from t1 limit 1;
+collation(c1)
+ujis_japanese_ci
+select c1 from t1 where c1 like 'abcdef%' order by c1;
+c1
+abcdefg
+select c1 from t1 where c1 like 'abcde1%' order by c1;
+c1
+abcde100
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde11%' order by c1;
+c1
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde111%' order by c1;
+c1
+abcde111
+drop table t1;
 SET collation_connection='ujis_bin';
 create table t1 select repeat('a',4000) a;
 delete from t1;
@@ -2227,3 +2249,25 @@ ujis_bin	6109
 ujis_bin	61
 ujis_bin	6120
 drop table t1;
+create table t1 engine=innodb select repeat('a',50) as c1;
+alter table t1 add index(c1(5));
+insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111');
+select collation(c1) from t1 limit 1;
+collation(c1)
+ujis_bin
+select c1 from t1 where c1 like 'abcdef%' order by c1;
+c1
+abcdefg
+select c1 from t1 where c1 like 'abcde1%' order by c1;
+c1
+abcde100
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde11%' order by c1;
+c1
+abcde110
+abcde111
+select c1 from t1 where c1 like 'abcde111%' order by c1;
+c1
+abcde111
+drop table t1;
diff --git a/mysql-test/t/ctype_big5.test b/mysql-test/t/ctype_big5.test
index b5cf610d941..da31a8f3245 100644
--- a/mysql-test/t/ctype_big5.test
+++ b/mysql-test/t/ctype_big5.test
@@ -14,8 +14,10 @@ SET @test_collation= 'big5_chinese_ci';
 SET NAMES big5;
 SET collation_connection='big5_chinese_ci';
 -- source include/ctype_filesort.inc
+-- source include/ctype_innodb_like.inc
 SET collation_connection='big5_bin';
 -- source include/ctype_filesort.inc
+-- source include/ctype_innodb_like.inc
 
 #
 # Bugs#9357: TEXT columns break string with special word in BIG5 charset.
diff --git a/mysql-test/t/ctype_cp932.test b/mysql-test/t/ctype_cp932.test
index 31eb032361f..70f666f734e 100644
--- a/mysql-test/t/ctype_cp932.test
+++ b/mysql-test/t/ctype_cp932.test
@@ -404,5 +404,7 @@ DROP TABLE t3;
 
 SET collation_connection='cp932_japanese_ci';
 -- source include/ctype_filesort.inc
+-- source include/ctype_innodb_like.inc
 SET collation_connection='cp932_bin';
 -- source include/ctype_filesort.inc
+-- source include/ctype_innodb_like.inc
diff --git a/mysql-test/t/ctype_sjis.test b/mysql-test/t/ctype_sjis.test
index 50d286f28b9..9b53d448374 100644
--- a/mysql-test/t/ctype_sjis.test
+++ b/mysql-test/t/ctype_sjis.test
@@ -66,8 +66,10 @@ drop table t1;
 
 SET collation_connection='sjis_japanese_ci';
 -- source include/ctype_filesort.inc
+-- source include/ctype_innodb_like.inc
 SET collation_connection='sjis_bin';
 -- source include/ctype_filesort.inc
+-- source include/ctype_innodb_like.inc
 
 # Check parsing of string literals in SJIS with multibyte characters that
 # have an embedded \ in them. (Bug #8303)
diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test
index 407287be30a..8657d5eaa86 100644
--- a/mysql-test/t/ctype_ujis.test
+++ b/mysql-test/t/ctype_ujis.test
@@ -1145,5 +1145,7 @@ DROP TABLE t1;
 
 SET collation_connection='ujis_japanese_ci';
 -- source include/ctype_filesort.inc
+-- source include/ctype_innodb_like.inc
 SET collation_connection='ujis_bin';
 -- source include/ctype_filesort.inc
+-- source include/ctype_innodb_like.inc
diff --git a/strings/ctype-big5.c b/strings/ctype-big5.c
index 52886116ff2..ff4ce6fdb51 100644
--- a/strings/ctype-big5.c
+++ b/strings/ctype-big5.c
@@ -392,9 +392,14 @@ static my_bool my_like_range_big5(CHARSET_INFO *cs __attribute__((unused)),
 				  uint res_length, char *min_str,char *max_str,
 				  uint *min_length,uint *max_length)
 {
-  const char *end=ptr+ptr_length;
+  const char *end;
   char *min_org=min_str;
   char *min_end=min_str+res_length;
+  uint charlen= my_charpos(cs, ptr, ptr+ptr_length, res_length/cs->mbmaxlen);
+
+  if (charlen < ptr_length)
+    ptr_length= charlen;
+  end= ptr + ptr_length;
 
   for (; ptr != end && min_str != min_end ; ptr++)
   {
diff --git a/strings/ctype-cp932.c b/strings/ctype-cp932.c
index d9e3bbd5866..377b5aa9871 100644
--- a/strings/ctype-cp932.c
+++ b/strings/ctype-cp932.c
@@ -324,9 +324,14 @@ static my_bool my_like_range_cp932(CHARSET_INFO *cs __attribute__((unused)),
 				  uint res_length, char *min_str,char *max_str,
 				  uint *min_length,uint *max_length)
 {
-  const char *end=ptr+ptr_length;
+  const char *end;
   char *min_org=min_str;
   char *min_end=min_str+res_length;
+  uint charlen= my_charpos(cs, ptr, ptr+ptr_length, res_length/cs->mbmaxlen);
+
+  if (charlen < ptr_length)
+    ptr_length= charlen;
+  end= ptr + ptr_length;
 
   while (ptr < end && min_str < min_end) {
     if (ismbchar_cp932(cs, ptr, end)) {
diff --git a/strings/ctype-gbk.c b/strings/ctype-gbk.c
index 9daa9f90f3c..89d28320fe1 100644
--- a/strings/ctype-gbk.c
+++ b/strings/ctype-gbk.c
@@ -2705,9 +2705,14 @@ static my_bool my_like_range_gbk(CHARSET_INFO *cs __attribute__((unused)),
                                  uint res_length, char *min_str,char *max_str,
                                  uint *min_length,uint *max_length)
 {
-  const char *end=ptr+ptr_length;
+  const char *end;
   char *min_org=min_str;
   char *min_end=min_str+res_length;
+  uint charlen= my_charpos(cs, ptr, ptr+ptr_length, res_length/cs->mbmaxlen);
+
+  if (charlen < ptr_length)
+    ptr_length= charlen;
+  end= ptr + ptr_length;
 
   for (; ptr != end && min_str != min_end ; ptr++)
   {
diff --git a/strings/ctype-simple.c b/strings/ctype-simple.c
index bd5131b7448..9e3a328ec26 100644
--- a/strings/ctype-simple.c
+++ b/strings/ctype-simple.c
@@ -997,9 +997,15 @@ my_bool my_like_range_simple(CHARSET_INFO *cs,
 			     char *min_str,char *max_str,
 			     uint *min_length,uint *max_length)
 {
-  const char *end=ptr+ptr_length;
+  const char *end;
   char *min_org=min_str;
   char *min_end=min_str+res_length;
+#ifdef USE_MB
+  uint charlen= my_charpos(cs, ptr, ptr+ptr_length, res_length/cs->mbmaxlen);
+  if (charlen < ptr_length)
+    ptr_length= charlen;
+#endif
+  end= ptr + ptr_length;
 
   for (; ptr != end && min_str != min_end ; ptr++)
   {
diff --git a/strings/ctype-sjis.c b/strings/ctype-sjis.c
index b018e148ffe..7f34ee3fa2d 100644
--- a/strings/ctype-sjis.c
+++ b/strings/ctype-sjis.c
@@ -322,9 +322,14 @@ static my_bool my_like_range_sjis(CHARSET_INFO *cs __attribute__((unused)),
 				  uint res_length, char *min_str,char *max_str,
 				  uint *min_length,uint *max_length)
 {
-  const char *end=ptr+ptr_length;
+  const char *end;
   char *min_org=min_str;
   char *min_end=min_str+res_length;
+  uint charlen= my_charpos(cs, ptr, ptr+ptr_length, res_length/cs->mbmaxlen);
+
+  if (charlen < ptr_length)
+    ptr_length= charlen;
+  end= ptr + ptr_length;
 
   while (ptr < end && min_str < min_end) {
     if (ismbchar_sjis(cs, ptr, end)) {