MySQL Bugs: #7520: Left join with TEXT column will not obey FORCE INDEX hint

Bug #7520	Left join with TEXT column will not obey FORCE INDEX hint
Submitted:	24 Dec 2004 0:28	Modified:	3 Mar 2005 16:15
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.24, 4.1.9	OS:
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
A LEFT JOIN across TEXT columns will not obey FORCE INDEX in 4.1.9.  Against 4.0.24, it does obey FORCE INDEX but EXPLAIN reports wildly inaccurate row estimates.

How to repeat:
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 ( a TEXT, INDEX (a(20)) );
CREATE TABLE t2 ( a TEXT, INDEX (a(20)) );
INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');
INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING (a);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) USING (a);
DROP TABLE t1, t2;

Against 4.1.9 it simply will not use the index.

Against 4.0.24:

mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) USING (a);
+-------+------+---------------+------+---------+-----------+------------+-------+
| table | type | possible_keys | key  | key_len | ref       | rows       | Extra |
+-------+------+---------------+------+---------+-----------+------------+-------+
| t1    | ALL  | NULL          | NULL |    NULL | NULL      |          5 |       |
| t2    | ref  | a             | a    |      23 | test.t1.a | 1022611265 |       |
+-------+------+---------------+------+---------+-----------+------------+-------+

Suggested fix:
n/a

For version I verified the following problem:

mysql> CREATE TABLE t1 ( a BLOB, INDEX (a(20)) );
Query OK, 0 rows affected (0.28 sec)

mysql> CREATE TABLE t2 ( a BLOB, INDEX (a(20)) );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;
+-------+------+---------------+------+---------+------+------+-------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+-------+------+---------------+------+---------+------+------+-------+
| t1    | ALL  | NULL          | NULL |    NULL | NULL |    5 |       |
| t2    | ALL  | a             | NULL |    NULL | NULL |    4 |       |
+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a;
+-------+------+---------------+------+---------+------+------------+-------+
| table | type | possible_keys | key  | key_len | ref  | rows       | Extra |
+-------+------+---------------+------+---------+------+------------+-------+
| t1    | ALL  | NULL          | NULL |    NULL | NULL |          5 |       |
| t2    | ref  | a             | a    |      23 | t1.a | 1022611265 |       |
+-------+------+---------------+------+---------+------+------------+-------+
2 rows in set (0.01 sec)

As you can see the first query does not use the index to access the table t2 at all, while the second query uses the index but returns some garbage in the
'rows' column.

My investigation shows that it happens because max_key_length is set less than key_length for the index a of the table t2 (21 and 23 respectively). A wrong value for max_key_length is set only for indexes defined on blob fields.

The same behaviour I observe for version 4.1.10.
It should be mentioned that version 4.1 does not use indexes to access TEXT fields. So the above behavior can be watched only for BLOB fields.

I fixed the bug, merged it into 4.1. When I started merging it into 5.0 I discovered 
that it was actually fixed there earlier, yet formally in a different way.
I decided to cancel my changes and to apply the fix I had found in 5.0.

ChangeSet
  1.2054 05/02/10 22:15:38 igor@rurik.mysql.com +3 -0
  select.result, select.test:
    Added a test case for bug #7520.
  table.cc:
    Fixed bug #7520.
    The bug was caused by a wrong calculation of the field max_key_length for
    a TABLE structure when there was an index on a blob field.

ChangeSet
  1.2057 05/02/11 10:39:26 igor@rurik.mysql.com +2 -0
  select.result:
    After revision of the fix for bug #7520.
  table.cc:
    Revised the fix for bug #7520.
    Made it compliant with 5.0 code where the bug does not exist.

Noted in 4.1.11 changelog.