Bug #16674 Regression with prefix indexes and LIKE

• Submitted: 20 Jan 2006 15:01
• Modified: 13 Jul 2006 4:21
• Reporter: Hartmut Holzgraefe
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 4.1, 5.0
• OS: Linux (linux)
• Assigned to: Igor Babaev
• CPU Architecture: Any

[20 Jan 2006 15:01] Hartmut Holzgraefe

Description:
when using partial indexes and LIKE not all result rows are shown,
4.1 works fine as expected, only 5.0 shows the problem.
Affects both MyISAM and InnoDB tables so i suspect a problem on 
a higher level.

How to repeat:
-- test1.sql
SET NAMES utf8;
DROP TABLE testi;
CREATE TABLE testi (testf CHAR(255) DEFAULT '', KEY(testf(10))) 
   ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO testi SET testf = 'Käli Käli 2-4';
SELECT * FROM testi WHERE testf LIKE 'Käli Käli 2%';
INSERT INTO testi SET testf = 'Käli Käli 2-4';
SELECT * FROM testi WHERE testf LIKE 'Käli Käli 2%';

expected result (as reported by 4.1):
  testf
  Käli Käli 2-4

  testf 
  Käli Käli 2-4
  Käli Käli 2-4

actual 5.0 result:
  (empty result)

  testf 
  Käli Käli 2-4

-- test2.sql
SET NAMES utf8;
DROP TABLE IF EXISTS testi;
CREATE TABLE testi (testf CHAR(255) DEFAULT '') 
   ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO testi SET testf = 'Käli Käli 2-4';
INSERT INTO testi SET testf = 'Käli Käli 2-4';
SELECT * FROM testi WHERE testf LIKE 'Käli Käli 2%';
ALTER TABLE testi ADD KEY (testf(10));
SELECT * FROM testi WHERE testf LIKE 'Käli Käli 2%';

expected result:

  testf 
  Käli Käli 2-4
  Käli Käli 2-4

  testf
  Käli Käli 2-4
  Käli Käli 2-4

actual 5.0 result:

  testf
  Käli Käli 2-4
  Käli Käli 2-4

  (empty result)

so the result is only wrong after adding the index

[19 May 2006 17:03] Hartmut Holzgraefe

mysqltest test case

Attachment: bug16674.tar.gz (application/x-gzip, text), 826 bytes.

[24 May 2006 4:44] Igor Babaev

The same problem can be demonstrated without using a partial index:

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.0.22-debug |
+--------------+
1 row in set (0.00 sec)

mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (
    ->   testf CHAR(13) DEFAULT '',
    ->   INDEX(testf)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 SET testf = 'Käli Käli 2-4';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 SET testf = 'Käli Käli 2-4';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 SET testf = 'Käli Käli 2+4';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 SET testf = 'Käli Käli 2+4';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 SET testf = 'Käli Käli 2-6';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 SET testf = 'Käli Käli 2-6';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t2;
+-----------------+
| testf           |
+-----------------+
| Käli Käli 2+4 |
| Käli Käli 2+4 |
| Käli Käli 2-4 |
| Käli Käli 2-4 |
| Käli Käli 2-6 |
| Käli Käli 2-6 |
+-----------------+
6 rows in set (0.00 sec)

mysql> EXPLAIN SELECT testf FROM t2 WHERE testf LIKE 'Käli Käli 2-%';
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t2    | range | testf         | testf | 40      | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT testf FROM t2 WHERE testf LIKE 'Käli Käli 2-%';
Empty set (0.00 sec)

mysql> SELECT testf FROM t2 WHERE testf LIKE 'Käli Käli 2+4';
Empty set (0.00 sec)

[14 Jun 2006 7:48] Alexander Barkov

Reassigning to myself, as Monty asked me to work on this bug.

[14 Jun 2006 11:15] Alexander Barkov

Oops. I'm sorry, assigned a wrong bug to myself in a mistake.

[21 Jun 2006 2:57] 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/commits/7978

[5 Jul 2006 18:10] Evgeny Potemkin

The length of the prefix of the pattern string in the LIKE predicate that 
determined the index range to be scanned was calculated incorrectly for
multi-byte character sets. 
As a result of this in 4. 1 the the scanned range was wider then necessary
if the prefix contained not only one-byte characters.  
In 5.0 additionally it caused missing some rows from the result set.

Fixed in 4.1.21, 5.0.24, 5.1.12

[13 Jul 2006 4:21] Paul DuBois

Noted in 4.1.21, 5.0.25 (not 5.0.24), 5.1.12 changelogs.