Bug #4371 InnoDB fails to return all rows where a column is null
Submitted: 2 Jul 2004 11:51 Modified: 5 Jul 2004 7:48
Reporter: Martin Rode Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0.16 OS:Linux (Debian Linux i386)
Assigned to: CPU Architecture:Any

[2 Jul 2004 11:51] Martin Rode 
Description:
In InnoDB on an indexed column Mysql finds only the first row, when the column is set to Null. 

How to repeat:
CREATE TABLE pf_templates (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	keystr VARCHAR(50) not null,
	father_id INT,
	UNIQUE(keystr, father_id),
        UNIQUE(father_id)
) Type=InnoDB;

INSERT INTO pf_templates SET keystr='HEADER';
INSERT INTO pf_templates SET keystr='MENU_ITEMS';

-------------------------
mysql> select keystr,id,father_id from pf_templates;
+------------+----+-----------+
| keystr     | id | father_id |
+------------+----+-----------+
| HEADER     |  1 |      NULL |
| MENU_ITEMS |  2 |      NULL |
+------------+----+-----------+
2 rows in set (0.00 sec)

mysql> select keystr,id,father_id from pf_templates where father_id is null;
+--------+----+-----------+
| keystr | id | father_id |
+--------+----+-----------+
| HEADER |  1 |      NULL |
+--------+----+-----------+
1 row in set (0.00 sec)

This works fine in MyISAM tables!!!

Suggested fix:
Make it behave like in MyIsam tables!! Columns with "null" should not be indexed.
[2 Jul 2004 12:17] Marko Mäkelä 
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

The development versions of 4.0 and 4.1 appear to behave correctly. Please upgrade to 4.0.20.
[5 Jul 2004 7:48] Heikki Tuuri 
Hi!

This bug was introduced in 4.0.16 and fixed in 4.0.18.

"
C.9.6 MySQL/InnoDB-4.0.18, February 13, 2004
...

Fixed a bug: If there was a UNIQUE secondary index, and NULL values in that unique index, then with the IS NULL predicate, InnoDB returned only the first matching row, though there can be many. This bug was introduced in 4.0.16. (Bug #2483) 
"

Best regards,

Heikki