Bug #11245 LIKE doesn't return rows when used with index

• Submitted: 10 Jun 2005 13:45
• Modified: 10 Jun 2005 14:13
• Reporter: Jakub Vrana (Candidate Quality Contributor)
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.11
• OS: Windows (Windows XP)
• CPU Architecture: Any

[10 Jun 2005 13:45] Jakub Vrana

Description:
If there is an index on the column used on the left side of LIKE and this index is used (i.e. right side of LIKE starts with a constant string of length at least 5), MySQL doesn't return any rows.

Dropping index or using '%' at the beginning of right side of LIKE or using RLIKE solves the problem.

How to repeat:
CREATE TABLE jazyky (domena varchar(50), PRIMARY KEY (domena));
INSERT INTO jazyky VALUES ('www.a-prague.com'), ('www.a-praha.com');
SELECT * FROM jazyky WHERE domena LIKE 'www.a-%';
-- Empty set (0.00 sec)

EXPLAIN SELECT * FROM jazyky WHERE domena LIKE 'www.a-%';
           id: 1
  select_type: SIMPLE
        table: jazyky
         type: range
possible_keys: domena
          key: domena
      key_len: 50
          ref: NULL
         rows: 1
        Extra: Using where; Using index

[10 Jun 2005 14:13] Jorge del Conde

Hi!

I was unable to reproduce this bug using 4.1.13 from bk:

mysql> SELECT * FROM jazyky WHERE domena LIKE 'www.a-%';
+------------------+
| domena           |
+------------------+
| www.a-prague.com |
| www.a-praha.com  |
+------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM jazyky WHERE domena LIKE 'www.a-%';
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | jazyky | range | PRIMARY       | PRIMARY |     150 | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql>