MySQL Bugs: #5832: SELECT doesn't return records in some cases

Bug #5832	SELECT doesn't return records in some cases
Submitted:	30 Sep 2004 20:19	Modified:	5 Oct 2004 15:18
Reporter:	Victoria Reznichenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1, 5.0	OS:	Windows (Windows, Linux)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
If there is an index on the prefix of the column, SELECT doesn't return records in some cases.

Problem appears only on MyISAM tables, with InnoDB it works fine.

mysql> INSERT INTO terms SET list_id = 1, term = "letterc";
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO terms SET list_id = 1, term = "letterb";
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO terms SET list_id = 1, term = "lettera";
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO terms SET list_id = 1, term = "letterd";
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT id FROM terms WHERE (list_id = 1) AND (term = "letterc");
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT id FROM terms WHERE (list_id = 1) AND (term = "letterb");
Empty set (0.00 sec)

mysql> SELECT id FROM terms WHERE (list_id = 1) AND (term = "lettera");
Empty set (0.00 sec)

mysql> SELECT id FROM terms WHERE (list_id = 1) AND (term = "letterd");
+----+
| id |
+----+
|  4 |
+----+
1 row in set (0.00 sec)

How to repeat:
SET SESSION character_set_client     = utf8;
SET SESSION character_set_connection = utf8;
SET SESSION character_set_results    = utf8;
SET SESSION character_set_server     = utf8;

DROP DATABASE IF EXISTS select_test;
CREATE DATABASE select_test DEFAULT CHARACTER SET utf8;
USE select_test;

CREATE TABLE terms (
    id       int unsigned NOT NULL auto_increment,
    list_id  smallint unsigned NOT NULL,
    term     TEXT NOT NULL,
    PRIMARY KEY(id),
    INDEX(list_id, term(4))
) TYPE=MyISAM CHARSET=utf8;

INSERT INTO terms SET list_id = 1, term = "letterc";
INSERT INTO terms SET list_id = 1, term = "letterb";
INSERT INTO terms SET list_id = 1, term = "lettera";
INSERT INTO terms SET list_id = 1, term = "letterd";

SELECT id FROM terms WHERE (list_id = 1) AND (term = "letterc");
SELECT id FROM terms WHERE (list_id = 1) AND (term = "letterb");
SELECT id FROM terms WHERE (list_id = 1) AND (term = "lettera");
SELECT id FROM terms WHERE (list_id = 1) AND (term = "letterd");

Verified on Windows (version: 4.1.5 and 5.0.1) and Linux(4.1.6, 5.0.2)

Hi!

The bug now manifests also in the latest InnoDB-4.0.6 tree, because InnoDB now stores only n UTF-chars to an index defined as a(n), where a is a string type column.

The bug happens because MySQL looks from the index with the FULL 7 character string, while the index only contains the first 4 characters.

Regards,

Heikki

(gdb) print search_tuple->fields[1]
$3 = {data = 0x8b4527c, len = 7, type = {mtype = 5, prtype = 2163196,
    len = 10, prec = 0}}
(gdb) x/7b 0x8b4527c
0x8b4527c:      0x6c    0x65    0x74    0x74    0x65    0x72    0x61
(gdb)

Note v4.1.5 works if you remove all of the utf8 settings in the example.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html