MySQL Bugs: #27980: INSERT IGNORE wrongly ignores NULLs in unique index

Bug #27980	INSERT IGNORE wrongly ignores NULLs in unique index
Submitted:	20 Apr 2007 12:00	Modified:	9 May 2007 6:14
Reporter:	Kristian Nielsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.0-bk, 5.1-bk	OS:	Linux
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
NDB INSERT IGNORE can wrongly ignore NULL columns in a unique index:

The problem is that the code in peek_indexed_rows wrongly tries to look up a
NULL attribute in a unique hash index, which instead causes the lookup of
whatever is stored in the value position in the row, which appears to be zero.

Thus if a row with zero value for the indexed column exists, INSERT IGNORE will
skip inserting NULLs into that column.

How to repeat:
mysql> create table t (a int primary key, b int, unique key(b)) engine=ndb;
Query OK, 0 rows affected (5.43 sec)

mysql> insert ignore into t values (1,0), (2,null), (3,null);
Query OK, 1 row affected (0.03 sec)
Records: 3  Duplicates: 2  Warnings: 0

mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |    0 | 
+---+------+
1 row in set (0.04 sec)

Compare the same with myisam:

mysql> create table t (a int primary key, b int, unique key(b)) engine=myisam;
Query OK, 0 rows affected (0.12 sec)

mysql> insert ignore into t values (1,0), (2,null), (3,null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |    0 | 
| 2 | NULL | 
| 3 | NULL | 
+---+------+
3 rows in set (0.01 sec)

Suggested fix:
Backport something like this from WL#3733:

static
int
check_null_in_record(const KEY* key_info, const byte *record)
{
  KEY_PART_INFO *curr_part, *end_part;
  curr_part= key_info->key_part;
  end_part= curr_part + key_info->key_parts;

  while (curr_part != end_part)
  {
    if (curr_part->null_bit &&
        (record[curr_part->null_offset] & curr_part->null_bit))
      return 1;
    curr_part++;
  }
  return 0;
  /*
    We could instead pre-compute a bitmask in table_share with one bit for
    every null-bit in the key, and so check this just by OR'ing the bitmask
    with the null bitmap in the record.
    But not sure it's worth it.
  */
}

And add this in ha_ndbcluster::peek_indexed_rows:

   for (i= 0, key_info= table->key_info; i < table->s->keys; i++, key_info++)
   {
     if (i != table->s->primary_key &&
         key_info->flags & HA_NOSAME)
     {
+      /*
+        A unique index is defined on table.
+        We cannot look up a NULL field value in a unique index. But since
+        keys with NULLs are not indexed, such rows cannot conflict anyway, so
+        we just skip the index in this case.
+      */
+      if (check_null_in_record(key_info, record))
+      {
+        DBUG_PRINT("info", ("skipping check for key with NULL"));
+        continue;
+      }
+
      // A unique index is defined on table
      NdbIndexOperation *iop;
      const NDBINDEX *unique_index = m_index[i].unique_index;

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/25887

ChangeSet@1.2465, 2007-05-02 10:02:27+02:00, mskold@mysql.com +3 -0
  ndb_insert.test, ndb_insert.result, ha_ndbcluster.cc:
    Bug#27980 INSERT IGNORE wrongly ignores NULLs in unique index: added check for null values

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/25893

ChangeSet@1.2505, 2007-05-02 12:31:53+02:00, mskold@mysql.com +1 -0
  ha_ndbcluster.cc:
    Bug#27980 INSERT IGNORE wrongly ignores NULLs in unique index: added check for null values

Pushed into 5.1.18-beta

Pushed into 5.0.42

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.42 and 5.1.18 changelogs.