Bug #21762 UPDATE setting a field to null when already set to null results in a changed row
Submitted: 21 Aug 2006 19:08 Modified: 21 Aug 2006 22:36
Reporter: Nicholas Costa Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.20a OS:Linux (Ubuntu)
Assigned to: CPU Architecture:Any

[21 Aug 2006 19:08] Nicholas Costa
Description:
If a particular field is set to null before an UPDATE query, and that field is set to null in the operation and no other changes are made, MySQL will report that the row was changed. The affected row count will not be zero, as it should be, and worse, any TIMESTAMP fields in the row will be updated. This should not occur because replacing a null value with null does not represent a change of the row.

How to repeat:
# create a sample table
CREATE TABLE myTable ( myvalue VARCHAR(5), TIMESTAMP );

# insert a row. the row will have a zeroed timestamp.
INSERT INTO myTable ( myValue ) VALUES ( null, '0000-00-00 00:00:00' );

# change the row. this should not change the timestamp, but it does.
UPDATE myTable SET myValue = null;
[21 Aug 2006 22:36] MySQL Verification Team
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional Info:

The server behaves according the SQL Standard (NULL = UNKNOWN) then in any
case an update is performed. Please see also:

http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html