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

[7 Sep 2007 7:17] Domas Mituzas
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.
[5 Oct 2007 11:33] Bugs System
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.
[18 Oct 2007 21:35] Bugs System
Pushed into 5.1.23-beta
[18 Oct 2007 21:36] Bugs System
Pushed into 5.0.52
[24 Oct 2007 20:03] Paul Dubois
Noted in 5.0.52, 5.1.23 changelogs.

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