MySQL Bugs: #62548: Different result from memory and myisam table

Bug #62548	Different result from memory and myisam table
Submitted:	27 Sep 2011 10:22	Modified:	27 Sep 2011 10:54
Reporter:	Zoltn Varga	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S2 (Serious)
Version:	5.1.49, 5.1.58	OS:	Linux (Ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	heap, Memory, myisam, null, unique key

Description:
I have a table with two fields:
CREATE TABLE IF NOT EXISTS `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uni` char(200) COLLATE utf8_hungarian_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni` (`uni`)
) ENGINE=MyISAM;

!!!UNIQUE KEY `uni` (`uni`)!!!

I have two records (1,'a') and (2,'b');
Next I run the following queries:
update test set uni=null;
select * from test where uni is null;
I get two record (1,null),(2,null);

But if I change the storage type to Memory (ALTER TABLE  `test` ENGINE = MEMORY) and run the queries I get only one record:

ALTER TABLE `test` ENGINE = MEMORY;
update test set uni=null;
select * from test where uni is null;
I get one record (2,null).

If I change back to myisam I get the right result (2 record).
Is this a bug?

How to repeat:
I can repeat anytime just run the queries.

Suggested fix:
If the uni field not an unique key the result is correct but this is not a good fix...

Thank you for the bug report. Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.1.58-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE IF NOT EXISTS `test` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `uni` char(200) COLLATE utf8_hungarian_ci DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `uni` (`uni`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test values (1, 'a'), (2, 'b');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update test set uni=null;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from test;
+----+------+
| id | uni  |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from test where uni is null;
+----+------+
| id | uni  |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+
2 rows in set (0.02 sec)

mysql> alter table test engine=MEMORY;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test where uni is null;
+----+------+
| id | uni  |
+----+------+
|  2 | NULL |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test;
+----+------+
| id | uni  |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

mysql> alter table test engine=MyISAM;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test where uni is null;
+----+------+
| id | uni  |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

I'm curious as to whether this has been fixed after 2 years, perhaps in a later release, because it seems like a big enough bug that it ought to have been.

The problem isn't related to altering tables, or to MyISAM at all.  It's a problem with how the MEMORY engine handles NULL values in unique indexes.

Much simpler test case:

CREATE TEMPORARY TABLE t (ID int, uniqueVal int UNIQUE) ENGINE=MEMORY;
INSERT INTO t VALUES (1,NULL),(2,NULL);

SELECT * FROM t;

SELECT COUNT(*) FROM t WHERE uniqueVal IS NULL;
SELECT * FROM t WHERE uniqueVal IS NULL;

On a linux (x64) machine running MySQL v5.5.42-cll, I can confirm that the last two SELECTs *INCORRECTLY* return "1" and a single row, respectively.

On a Windows 8.1 x64 machine running MySQL v5.6.24-log, I could not reproduce the problem: I got "2" and both rows returned, respectively, which is the correct result.

I would hazard a guess that the problem occurs because the WHERE clause is doing a simple lookup on a unique index, and the MEMORY engine assumes there will be only one row returned, ignoring the special case that exists for NULL values (namely, you can have more than one).

I'm not sure if my Windows machine result means that the bug is fixed in v5.6, but if not... this is a nearly 4-year-old bug that reproducibly produces blatantly incorrect results from really simple queries, under situations that probably are quite common in the wild (i.e. MEMORY temp table with a UNIQUE KEY and NULL values in it).  It isn't some subtle edge-case problem!

I'd have thought that (other than crashing the server) a bug could hardly *get* more serious than this!  Why hasn't this been dealt with in nearly 4 years?!?