Bug #12873 BTREE index on MEMORY table with multiple NULL values doesn't work properly
Submitted: 30 Aug 2005 10:30 Modified: 5 Jun 2006 21:43
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Memory storage engine Severity:S3 (Non-critical)
Version:4.1.14/5.0 OS:Linux (linux)
Assigned to: Sergey Vojtovich CPU Architecture:Any

[30 Aug 2005 10:30] jocelyn fournier 
Description:
When trying to create a BTREE index on a MEMORYtable with multiple NULL values, MySQL fails with a Duplicate entre 'NULL' for key 1 error.
With a HASH index, no error occurs.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a int NULL) TYPE=MEMORY;
INSERT INTO t1 VALUES (NULL),(NULL);
ALTER TABLE t1 ADD UNIQUE a USING BTREE (a);

ERROR 1062 (23000): Duplicate entry 'NULL' for key 1

ALTER TABLE t1 ADD UNIQUE (a);

Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0

Suggested fix:
Accept multiple NULL values for BTREE index on a MEMORY table.
[11 May 2006 16:16] 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/6257
[24 May 2006 14:05] Sergey Vojtovich 
Pushed into tree currently marked as 5.0.23.
[2 Jun 2006 10:42] Sergey Vojtovich 
Merged into tree currently tagged as 5.1.12.
[5 Jun 2006 21:43] Mike Hillyer 
Documented in changelog:

      
      <listitem>
        <para>
          A <literal>B-TREE</literal> index on a
          <literal>MEMORY</literal> table erroneously reported duplicate
          entry error for multiple <literal>NULL</literal> values. (Bug
          #12873)
        </para>
      </listitem>