Bug #11502 Problem with SELECT ... LIKE and index

• Submitted: 22 Jun 2005 13:21
• Modified: 22 Jun 2005 13:48
• Reporter: Radoslav Petrov
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.10, 4.1.7
• OS: Windows (Windows XP)
• CPU Architecture: Any

[22 Jun 2005 13:21] Radoslav Petrov

Description:
We have the following table:

CREATE TABLE `Permission` (
  `uid` int(11) NOT NULL default '0',
  `name` varchar(250) character set cp1251 collate cp1251_bin default NULL,
  `description` varchar(250) character set cp1251 collate cp1251_bin default NULL,
  `application` int(11) default NULL,
  PRIMARY KEY  (`uid`),
  KEY `Permission_index_1` (`name`,`application`),
  KEY `Permission_index_2` (`application`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

When we use this SQL:
SELECT * FROM Permission
WHERE name LIKE 'a%';

the result set is empty even when we have many records cointainig char 'a' in the begining of name.

If we use this SQL:
SELECT * FROM Permission
WHERE name LIKE '%a';
the result in the result set is correct!

No matter what we search for the resultset is empty.

If we delete the FIRST index everything works just fine.
We tested this on Windows XP, MySQL 4.1.7 and 4.1.10, MySQL QueryBrowser and MySQLFront.

How to repeat:
1.Create the table.
2.Insert some data.
3.Execute the SQL queries.

Delete the first index. And Everything works just fine!:)

Suggested fix:
We don't have a fix for this.
Maybe with some big sql where part that emulates the BEHAVIOUR of LIKE.

[22 Jun 2005 13:48] MySQL Verification Team

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.12a-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table Permission\G
*************************** 1. row ***************************
       Table: Permission
Create Table: CREATE TABLE `permission` (
  `uid` int(11) NOT NULL default '0',
  `name` varchar(250) character set cp1251 collate cp1251_bin default NULL,
  `description` varchar(250) character set cp1251 collate cp1251_bin default NULL,
  `application` int(11) default NULL,
  PRIMARY KEY  (`uid`),
  KEY `Permission_index_1` (`name`,`application`),
  KEY `Permission_index_2` (`application`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251
1 row in set (0.00 sec)

mysql> SELECT * FROM Permission
    -> WHERE name LIKE 'a%';
+-----+---------+-------------+-------------+
| uid | name    | description | application |
+-----+---------+-------------+-------------+
|   1 | america | north       |          12 |
+-----+---------+-------------+-------------+
1 row in set (0.00 sec)

mysql>

[23 Jun 2005 15:44] Radoslav Petrov

Sorry for this bothering.
Thanks a lot for the quick response.