MySQL Bugs: #44694: Delete where not null auto_increment column is null affects rows

Bug #44694	Delete where not null auto_increment column is null affects rows
Submitted:	6 May 2009 13:33	Modified:	7 May 2009 11:33
Reporter:	Luís Arcaro	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.34	OS:	Any (Tested only Windows)
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, delete, IS NULL

Description:
Deleting rows where an not null auto_increment column is null affects more than zero rows.

** Closing the client and then running the same DELETE clause runs correctly.

How to repeat:
1) Create a table with an not null auto_increment column;
2) Insert a single row in it;
3) Delete any row where the not null auto_increment column IS null.

----------------------------------------------------------------------

Expected: Delete no rows, as the column is not nullable.
Received: 1 row deleted.

----------------------------------------------------------------------

CREATE TABLE `test` (
  `cd` int(11) NOT NULL auto_increment,
  `value` varchar(50) NULL,
  PRIMARY KEY  (`cd`)
) ENGINE=InnoDB; -- Empty table

INSERT INTO test (value) VALUES ('test'); -- Insert row

DELETE FROM test WHERE cd IS NULL; -- (x > 0) rows affected?

Looks like this is a (surprising) result of IS NULL behavior with auto_increment columns, as described in the manual, http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_is-null. 

Please, check if setting sql_auto_is_null = 0 solves the problem.

Solved.