| Bug #11650 | LIKE pattern matching using prefix index doesn't return correct result | ||
|---|---|---|---|
| Submitted: | 30 Jun 2005 0:44 | Modified: | 20 Jul 2005 15:11 |
| Reporter: | Shuichi Tamagawa | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.12/5.0.8-bk,5.1.46-5.1.49 | OS: | Linux (SuSE Linux 9.2/Windows) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
| Tags: | cp932, index prefix, like, prefix | ||
[30 Jun 2005 11:48]
MySQL Verification Team
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>
[30 Jun 2005 12:54]
Heikki Tuuri
Shuichi, please show the contents of your my.cnf. Regards, Heikki
[30 Jun 2005 16:32]
Shuichi Tamagawa
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.
[30 Jun 2005 17:06]
Heikki Tuuri
Assigning this to Marko.
[30 Jun 2005 17:56]
Aleksey Kishkin
verifyed on win xp sp2, mysql 4.1.12, sjis charset.
[4 Jul 2005 16:02]
Marko Mäkelä
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.
[6 Jul 2005 12:17]
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/internals/26714
[19 Jul 2005 13:14]
Peter Gulutzan
OK to push.
[20 Jul 2005 9:13]
Alexander Barkov
Pushed into 5.1.10 and 4.1.14.
[20 Jul 2005 15:11]
Mike Hillyer
Documented in 5.0.10 and 4.1.14 changelogs.
[25 Sep 2010 0:49]
Chris Calender
This bug has cropped up in some 5.1 versions (specifically, and at least, 5.1.46, 5.1.46sp1, 5.1.47, 5.1.48, and 5.1.49). I did not check prior to 5.1.46.
I've confirmed it works again properly in 5.1.50 though, both with the InnoDB plugin enabled and without.
And in this case, it was with regards to the 'cp932' character set.
Here is a simple test case:
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`c1` varchar(10),
KEY (`c1`(1))
) ENGINE=InnoDB DEFAULT CHARSET=cp932;
INSERT INTO `t` (`c1`) VALUES ('aaa');
select `c1` from `t` where `c1` like 'aaa';
It should return the row.
5.1.46 Output:
mysql> select version();
+---------------------------------------+
| version() |
+---------------------------------------+
| 5.1.46sp1-enterprise-gpl-advanced-log |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE IF EXISTS `t`;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE `t` (
-> `c1` varchar(10),
-> KEY (`c1`(1))
-> ) ENGINE=InnoDB DEFAULT CHARSET=cp932;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO `t` (`c1`) VALUES ('aa');
Query OK, 1 row affected (0.04 sec)
mysql> select `c1` from `t` where `c1` like 'aa';
Empty set (0.00 sec)
5.1.50 Output:
mysql> select version();
+--------------------------------+
| version() |
+--------------------------------+
| 5.1.50-enterprise-gpl-advanced |
+--------------------------------+
1 row in set (0.13 sec)
mysql> DROP TABLE IF EXISTS `t`;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `t` (
-> `c1` varchar(10),
-> KEY (`c1`(1))
-> ) ENGINE=InnoDB DEFAULT CHARSET=cp932;
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO `t` (`c1`) VALUES ('aa');
Query OK, 1 row affected (0.05 sec)
mysql> select `c1` from `t` where `c1` like 'aa';
+------+
| c1 |
+------+
| aa |
+------+
1 row in set (0.00 sec)
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