Bug #44771 Unique Hash index in memory engine will give wrong query result for NULL value
Submitted: 11 May 2009 2:27 Modified: 15 Dec 2010 0:52
Reporter: Xiao Heng Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Memory storage engine Severity:S2 (Serious)
Version:5.1.34, 4.1, 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: Nirbhay Choubey CPU Architecture:Any
Tags: memory unique hash index

[11 May 2009 2:27] Xiao Heng
Description:
When there are more than one NULL value in a unique key column. The select by the column for NULL value will return 1 row.

How to repeat:
create table test
(
  pk int primary key,
  val int,
  unique key using hash(val)
) engine=memory;

insert into test values(1, NULL);
insert into test values(2, NULL);

select * from test;
+----+------+
| pk | val  |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+

select * from test where val is NULL;
+----+------+
| pk | val  |
+----+------+
|  2 | NULL |
+----+------+
[12 May 2009 9:32] Sveta Smirnova
Thank you for the report.

Verified as described. BTREE indexes were fixed some time ago: see bug #30885
[6 Jan 2010 20:15] Eric Jensen
This still exists in 5.0.86, producing incorrect query results, any update on fixing it?
[27 Sep 2010 14:18] 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/119178

3519 Nirbhay Choubey	2010-09-27
      Bug#44771 : Unique Hash index in memory engine will give wrong query result for
                   NULL value.
      
      In a memory table when there are more than one NULL values for a unique key
      column using HASH index, a search for NULL on that column returns just one
      row.
      
      In memory engine, while reading a hash key which is NULL, it doesn't get copied
      to info->lastkey, which inturn is used to search next records.
      
      Fixed by adding the condition to check for NULL key, which lets copying of key
      to info->lastkey.
     @ mysql-test/r/heap_hash.result
        Bug#44771 : Unique Hash index in memory engine will give wrong query result for
                     NULL value.
     @ mysql-test/t/heap_hash.test
        Bug#44771 : Unique Hash index in memory engine will give wrong query result for
                     NULL value.
     @ storage/heap/hp_rkey.c
        Bug#44771 : Unique Hash index in memory engine will give wrong query result for
                     NULL value.
        
        Modified the condition to check for NULL key which is checked before copying key
        to info->lastkey in heap_rkey function. info->lastkey is further used by
        heap_rnext to search for the records with the same key.
[19 Oct 2010 12:20] 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/121131

3326 Nirbhay Choubey	2010-10-19
      Bug#44771 : Unique Hash index in memory engine will give wrong query result for
                  NULL value.
      
      In a memory table when there are more than one NULL values for a unique key
      column using HASH index, a search for NULL on that column returns just one
      row.
      
      In memory engine, while reading a hash key which is NULL, it doesn't get copied
      to info->lastkey, which inturn is used to search next records.
      
      Fixed by adding the condition to check for NULL key, which lets copying of key
      to info->lastkey.
     @ mysql-test/r/heap_hash.result
        Bug#44771 : Unique Hash index in memory engine will give wrong query result for
                    NULL value.
        Added a test case for bug#44771.
     @ mysql-test/t/heap_hash.test
        Bug#44771 : Unique Hash index in memory engine will give wrong query result for
                    NULL value.
        Added a test case for bug#44771.
     @ storage/heap/hp_rkey.c
        Bug#44771 : Unique Hash index in memory engine will give wrong query result for
                    NULL value.
        
        Modified the condition to check for NULL key which is checked before copying key
        to info->lastkey in heap_rkey function. info->lastkey is further used by
        heap_rnext to search for the records with the same key.
[13 Nov 2010 16:24] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[13 Nov 2010 16:36] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (pib:21)
[15 Dec 2010 0:52] Paul DuBois
Bug does not appear in any released 5.6.x version. No changelog entry needed.