Bug #9289 return result selected by using prefix index is not correct

• Submitted: 18 Mar 2005 22:37
• Modified: 20 Mar 2005 10:43
• Reporter: Hongyi Gao
• Status: Can't repeat
• Category: MySQL Server: MyISAM storage engine
• Severity: S1 (Critical)
• Version: 4.1.7
• OS: Linux (Gentoo Linux 2.6.9)
• CPU Architecture: Any

[18 Mar 2005 22:37] Hongyi Gao

Description:

On UTF8 table, if we have prefix index on first 10 chars of a varchar(200) field, when there is a range select using that index, it may fail.

This happens when I inserted two records that have the same chars in the first 10+ chars. Then if I do exact match select use =, it will find the right record. If I replace the = with LIKE, it won't. If I force it not use index, the LIKE works as well.

Weird things will happen if you use LIKE <= or => try to match the record.

How to repeat:
CREATE TABLE test (
  name varchar(200) default NULL,
  KEY name (name(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test VALUES 
('passthrough/mybook/email.txt'),
('passthrough/mybook/chat.txt');

now, try:

select * from test force index(name) where name like 'passthrough/mybook/chat.txt';

select * from test ignore index(name) where name like 'passthrough/mybook/chat.txt';

select * from test force index(name) where name = 'passthrough/mybook/chat.txt';

For range match (LIKE statement), it works correctly without index but incorrectly with index.
For exact match, it works either way.
You can try replace = with <= or >=, or swap chat.txt with email.txt, combine them with enable/disable index, see what happens.

Suggested fix:

Check the search machanism of range selects (LIKE, <=  >=) with prefix index (index 10 out of varchar(200)) on UTF8 table, on data start with the same characters for the first 10+ chars.
Tracing my sample table should be good to start with.

[20 Mar 2005 10:43] MySQL Verification Team

I wasn't able to repeat with latest BK source:

mysql> select * from test force index(name) where name like
    -> 'passthrough/mybook/chat.txt';
+-----------------------------+
| name                        |
+-----------------------------+
| passthrough/mybook/chat.txt |
+-----------------------------+
1 row in set (0.03 sec)

mysql> select * from test ignore index(name) where name like
    -> 'passthrough/mybook/chat.txt';
+-----------------------------+
| name                        |
+-----------------------------+
| passthrough/mybook/chat.txt |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select * from test force index(name) where name =
    -> 'passthrough/mybook/chat.txt';
+-----------------------------+
| name                        |
+-----------------------------+
| passthrough/mybook/chat.txt |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.11-debug-log |
+------------------+
1 row in set (0.02 sec)

mysql> 

1 row in set (0.02 sec)

mysql>