MySQL Bugs: #30885: MEMORY returns incorrect data if BTREE index is used for NULL lookup

Bug #30885	MEMORY returns incorrect data if BTREE index is used for NULL lookup
Submitted:	7 Sep 2007 7:17	Modified:	24 Oct 2007 20:03
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S2 (Serious)
Version:	5.0.50-bk	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	inconsistency, Memory

Description:
If two varchar fields on MEMORY table have covering B-Tree index, lookups for NULL in leftmost field fail

This bug exists in 5.0 (all latest versions / BK), and does not exist in 5.1

How to repeat:
mysql> create table memt1 (a varchar(255), b varchar(255), key ab using btree (a,b)) engine=MEMORY;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into memt1 values (1,1),(2,2),(3,3),(NULL,1),(NULL,NULL);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * From memt1;
+------+------+
| a    | b    |
+------+------+
| 1    | 1    | 
| 2    | 2    | 
| 3    | 3    | 
| NULL | 1    | 
| NULL | NULL | 
+------+------+
5 rows in set (0.00 sec)

mysql> select * from memt1 where a is null;
Empty set (0.00 sec)

Suggested fix:
handle NULL properly.

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/commits/34958

ChangeSet@1.2517, 2007-10-05 16:33:15+05:00, ramil@mysql.com +3 -0
  Fix for bug #30885: MEMORY returns incorrect data if BTREE index is used for NULL lookup
  
  Problem: creating an rb-tree key we store length (2 bytes) before the actual data for 
  varchar key parts. The fact was missed for NULL key parts, when we set NULL byte and 
  skip the rest.
  
  Fix: take into account the length of the varchar key parts for NULLs.

Pushed into 5.1.23-beta

Pushed into 5.0.52

Noted in 5.0.52, 5.1.23 changelogs.

For MEMORY tables, lookups for NULL values in BTREE indexes could
return incorrect results.