Bug #33699 UPDATE allows NULL as new value on NOT NULL columns (= default datatype value)
Submitted: 4 Jan 2008 21:30 Modified: 28 Jan 2008 18:09
Reporter: Patrick Crews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1, 6.0 OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: NOT NULL behavior, UPDATE

[4 Jan 2008 21:30] Patrick Crews
Description:
UPDATE <table> set <non-nullable-column> = NULL where <condition> is accepted by the server and results in the UPDATE'd column being set to the default value for the datatype.

This seems like inconsistent behavior as INSERT into <table> values (NULL,...) will not be accepted.

NOTE:  This only applies to non-strict mode.

How to repeat:
Use the following SQL:

CREATE TABLE t1 (a int NOT NULL);
INSERT INTO t1 VALUES(1);
UPDATE t1 SET a = NULL where a = 1;

Will result in a being set to 0 (int default).

INSERT INTO t1 VALUES(NULL); will fail -- even in non-strict mode

Suggested fix:
Recommend consistent treatment of NULL for any statements that add or modify data to a non-nullable column.
[4 Jan 2008 21:58] MySQL Verification Team
Thank you for the bug report.
[10 Jan 2008 23:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40886

ChangeSet@1.2656, 2008-01-11 03:52:32+04:00, gshchepa@host.loc +18 -0
  Bug#33699: The UPDATE statement allows NULL as new value on a NOT NULL
  columns (default datatype value is assigned).
  
  The mysql_update function has been modified to generate
  an error if try to set a NOT NULL field to NULL rather than a warning
  in the set_field_to_null_with_conversions function.
[11 Jan 2008 1:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40887

ChangeSet@1.2656, 2008-01-11 05:06:08+04:00, gshchepa@host.loc +18 -0
  Bug#33699: The UPDATE statement allows NULL as new value on a NOT NULL
  columns (default datatype value is assigned).
  
  The mysql_update function has been modified to generate
  an error when trying to set a NOT NULL field to NULL rather than a warning
  in the set_field_to_null_with_conversions function.
[24 Jan 2008 11:56] Bugs System
Pushed into 6.0.5-alpha
[24 Jan 2008 12:01] Bugs System
Pushed into 5.1.24-rc
[28 Jan 2008 18:09] Paul DuBois
Noted in 5.1.24, 6.0.5 changelog.

The UPDATE statement allowed NULL to be assigned to NOT NULL columns
(the default data type value was assigned). An error occurs now.
[6 Mar 2008 15:50] Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[28 Mar 2008 10:20] Kristian Koehntopp
The fix for this bug breaks replication. If you replicate for example from a 5.0 server that still has this bug to any slave with the fix, the slave will refuse to execute the command and stop.

There must be a compatibility mode for the old behaviour (SQL Mode?) or another way to update a replication network that has servers with and without that bug without breaking replication.
[2 Apr 2008 16:53] Jon Stephens
Also note fix in the 5.1.23-ndb-6.3.11 changelog.
[17 Feb 2009 22:27] Paul DuBois
This change was reverted, because the original report was determined
not to be a bug: Assigning NULL to a NOT NULL column in an UPDATE
statement should produce an error only in strict SQL mode and set the
column to the implicit default with a warning otherwise, which was
the original behavior. See Bug#39265.
[10 Aug 2012 20:59] Rob Neal
Are you kidding me? That's crazy! Do database developers really think that if a column is defined as non-nullable that it is acceptable for that column to be assigned a null value via an update statement (regardless of "strict mode")? The column is defined as not nullable! Enforce that, just as you do for an insert statement (both statements are DML). Yes. Do it as the database level because that's how the database level and table structure is defined. I'm counting on the database structure to enforce what I tell it to and not suffer losses because of some silly "mode" configuration. Not nullable means not nullable does it not? Or does "not nullable" mean "allow empty strings"?