MySQL Bugs: #13814: index including TINYBLOB breaks SELECT functionality

Bug #13814	index including TINYBLOB breaks SELECT functionality
Submitted:	6 Oct 2005 19:09	Modified:	9 Nov 2005 3:47
Reporter:	Mark Modrall	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.12/4.1 BK source	OS:	Linux (linux)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any
Tags:	corruption, myisam

Description:
I have a table that was keeping a hex string as a VARCHAR.  In an attempt to see how using a TINYBLOB of the hex number might improve performance (fewer bytes to shuffle around), I discovered that previously working functionality stopped working.  Specifically, when the column that was converted from VARCHAR to TINYBLOB is included in an index, SELECTs and UPDATEs on the table stopped working.

Drop the index, and SELECTs and UPDATEs work as expected.

I should note that both the VARCHAR and TINYBLOB could be null, and it's in the null cases in particular that I noticed the problem.

It can be reproduced in Query Browser with no code.

Specifically the UPDATEs and SELECTs don't appear to work when mixed with another column value.  See example.

How to repeat:
create table if not exists indexBug (
  cip INT NOT NULL,
  time TIME NOT NULL,
  score INT NOT NULL DEFAULT 0,
  bob TINYBLOB
)

insert into indexBug (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3, '00:03')

insert into indexBug (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b', '00:05'), (6, 'c', '00:06')

select * from indexBug where bob is null and cip=1;

create index bug on indexBug (bob(22), cip, time)

select * from indexBug where bob is null and cip=1;

------------------
you can try updates which fail because the implied select fails

Suggested fix:
don't have one.

By the by, the original table had bob VARCHAR(44) instead.  The value could be null or text and the index and selects still worked.

Thank you for the bug report.

This bug not affects 5.0

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table if not exists indexBug (
    ->   cip INT NOT NULL,
    ->   time TIME NOT NULL,
    ->   score INT NOT NULL DEFAULT 0,
    ->   bob TINYBLOB
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into indexBug (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3,
    -> '00:03');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into indexBug (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b',
    -> '00:05'), (6, 'c', '00:06');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from indexBug where bob is null and cip=1;
+-----+----------+-------+------+
| cip | time     | score | bob  |
+-----+----------+-------+------+
|   1 | 00:01:00 |     0 | NULL |
+-----+----------+-------+------+
1 row in set (0.01 sec)

mysql> create index bug on indexBug (bob(22), cip, time);
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from indexBug where bob is null and cip=1;
Empty set (0.00 sec)

----------------------------------------------------------------------------

mysql> create table if not exists indexBug (
    ->   cip INT NOT NULL,
    ->   time TIME NOT NULL,
    ->   score INT NOT NULL DEFAULT 0,
    ->   bob TINYBLOB
    -> )
    -> ;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into indexBug (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3,
    -> '00:03')
    -> 
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into indexBug (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b',
    -> '00:05'), (6, 'c', '00:06')
    -> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from indexBug where bob is null and cip=1;
+-----+----------+-------+------+
| cip | time     | score | bob  |
+-----+----------+-------+------+
|   1 | 00:01:00 |     0 | NULL |
+-----+----------+-------+------+
1 row in set (0.00 sec)

mysql> create index bug on indexBug (bob(22), cip, time)
    -> ;
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from indexBug where bob is null and cip=1;
+-----+----------+-------+------+
| cip | time     | score | bob  |
+-----+----------+-------+------+
|   1 | 00:01:00 |     0 | NULL |
+-----+----------+-------+------+
1 row in set (0.01 sec)

mysql> 
mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.15-rc-debug |
+-----------------+
1 row in set (0.00 sec)

Thanks...  Just heard from our operations dept that I'm not allowed to move to 5 until "Redhat starts bundling it" unfortunately.

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/internals/31632

The fix has been pushed into 4.1.16 tree.
Bug description for the changelog:
 One can get wrong query results (or wrong actions by UPDATE/DELETE) if 
  - The source table is as MyISAM table
  - There is a multipart index idx1 that covers a BLOB field fieldX (or part of it), and the fieldX is not the last key part.
  - The query performs index lookup on index idx1 using a key value tuple that
    = has NULL value for fieldX
    = has some values for subsequent keypart(s), i.e. the key value tuple covers keyparts up to fieldX and at least one key part after it.

Noted in 4.1.16 changelog.