Bug #6345 Unexpected behaviour with partial indices

• Submitted: 31 Oct 2004 14:05
• Modified: 8 Nov 2004 9:18
• Reporter: Moriyoshi Koizumi
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.7
• OS: Linux (Linux (Fedora Core 1))
• Assigned to: Alexander Barkov
• CPU Architecture: Any

[31 Oct 2004 14:05] Moriyoshi Koizumi

Description:
When I issue a query with a condition involved with a partial index, every row in the result that 
has the column of a string longer than the prefix length of the index on it randomly gets 
truncated. The results varied by MyISAM and InnoDB.

How to repeat:
SET collation_connection='ujis_japanese_ci';
SET character_set_client='ujis';

DROP TABLE IF EXISTS test1;

CREATE TABLE test1
(
  id INTEGER NOT NULL,
  file VARCHAR(50) NOT NULL DEFAULT '',
  PRIMARY KEY  (id),
  KEY file (file(10))
) TYPE=InnoDB CHARACTER SET 'ujis' COLLATE 'ujis_japanese_ci';

INSERT INTO test1 (id, file) VALUES (0, 'aaabbbcccddd');
INSERT INTO test1 (id, file) VALUES (1, 'eeefffggghhh');
INSERT INTO test1 (id, file) VALUES (2, 'iiijjjkkkl');

SELECT test1.* FROM test1 WHERE file='aaabbbcccddd';

SELECT test1.* FROM test1 WHERE file='aaabbbcccddd' ORDER BY id;

SELECT test1.* FROM test1 WHERE file='iiijjjkkkl';

SELECT test1.* FROM test1 WHERE file='iiijjjkkkl' ORDER BY id;

DROP TABLE IF EXISTS test1;

CREATE TABLE test1
(
  id INTEGER NOT NULL,
  file VARCHAR(50) NOT NULL DEFAULT '',
  PRIMARY KEY  (id),
  KEY file (file(10))
) TYPE=MyISAM CHARACTER SET 'ujis' COLLATE 'ujis_japanese_ci';

INSERT INTO test1 (id, file) VALUES (0, 'aaabbbcccddd');
INSERT INTO test1 (id, file) VALUES (1, 'eeefffggghhh');
INSERT INTO test1 (id, file) VALUES (2, 'iiijjjkkkl');

SELECT test1.* FROM test1 WHERE file='aaabbbcccddd';

SELECT test1.* FROM test1 WHERE file='aaabbbcccddd' ORDER BY id;

SELECT test1.* FROM test1 WHERE file='iiijjjkkkl';

SELECT test1.* FROM test1 WHERE file='iiijjjkkkl' ORDER BY id;

[1 Nov 2004 7:11] Heikki Tuuri

Moryoshi,

please test with 4.1.7. There were many bug fixes associated with column prefix indexes and multi-byte charsets.

Best regards,

Heikki

[4 Nov 2004 0:03] Moriyoshi Koizumi

Unfortunately, the problem still persists.

The bulk copy'n'paste below is the output in my environment,
whereas the second SQL statement
=============================================
SELECT test1.* FROM test1 WHERE file='aaabbbcccddd' ORDER BY id;
=============================================
should return any result.

================================================
mysql> \s
--------------
mysql  Ver 14.7 Distrib 4.1.7, for pc-linux (i686)

Connection id:          7
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         4.1.7
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    ujis
Client characterset:    ujis
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 3 min 10 sec

Threads: 1  Questions: 135  Slow queries: 0  Opens: 23  Flush tables: 1  Open tables: 5  Queries 
per second avg: 0.711
--------------

mysql> \. /tmp/test.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+----+--------------+
| id | file         |
+----+--------------+
|  0 | aaabbbcccddd |
+----+--------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

+----+------------+
| id | file       |
+----+------------+
|  2 | iiijjjkkkl |
+----+------------+
1 row in set (0.00 sec)

+----+------------+
| id | file       |
+----+------------+
|  2 | iiijjjkkkl |
+----+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+----+--------------+
| id | file         |
+----+--------------+
|  0 | aaabbbcccddd |
+----+--------------+
1 row in set (0.00 sec)

Empty set (0.01 sec)

+----+------------+
| id | file       |
+----+------------+
|  2 | iiijjjkkkl |
+----+------------+
1 row in set (0.00 sec)

+----+------------+
| id | file       |
+----+------------+
|  2 | iiijjjkkkl |
+----+------------+
1 row in set (0.00 sec)

mysql>