MySQL Bugs: #25467: Where clausse behaves differently for Select and delete with NOT NULL

Bug #25467	Where clausse behaves differently for Select and delete with NOT NULL
Submitted:	8 Jan 2007 16:09	Modified:	17 Apr 2007 17:06
Reporter:	Damien Seguy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.14, 5.0.32	OS:	MacOS (osx, debian)
Assigned to:		CPU Architecture:	Any
Tags:	datetime, delete, NOT NULL, SELECT

Description:
On a DATETIME NOT NULL column, "WHERE column is null" behaves differently with select and delete. 
Select will select the lines, and delete will not delete them. See reproduce code;

How to repeat:
// setting up
create table mysql_strange (id tinyint unsigned, visite datetime not null);
insert into mysql_strange values (1, 0);

// Testing with select
select count(*) from mysql_strange WHERE visite = 0;
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)
// OK

select count(*) from mysql_strange WHERE visite is null;
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)
// Null and 0 are the same for datetime? 

delete from mysql_strange WHERE visite is null;
Query OK, 0 rows affected (0.00 sec)
// OK, no null there, but why did it worked for SELECT and not for DELETE?

delete from mysql_strange WHERE visite = 0;
Query OK, 1 row affected (0.00 sec)
// OK, no null there.

// where clause is handled differently by select and delete? 

Suggested fix:
Make behaviour of SELECT and DELETE consistent with the same WHERE CLAUSE.

OR

Warn of this behavior in the docs.

Hi Damien, 

i can't reproduce this, for me both DELETE statements delete the row. 

This might be due to different configuration settings though, i'm running tests with a default server without any my.cnf file. Can you attach your configuration to this bug? I'm especially interested in your SQL_MODE settings.

Hi Hartmut,

So, I added some more check : Same problem on Mac osx running 5.1.6 and 5.1.14
debian running 5.0.32 and 4.1.15.

I check SQL_MODE, which is always empty string.

On my two macs, I don't use any cnf either. The two others might be difficult to check, but I can get you a show variables.

Is this possibly a duplicate of either:
http://bugs.mysql.com/bug.php?id=8143
or
http://bugs.mysql.com/bug.php?id=23412

Please, try to repeat with a newer version, 5.0.27 and/or 5.1.16, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi

Just to report : I just tested this on MySQL 5.1.17, OSX of i386, and it works consistently now.

Thanks for fixing this.