MySQL Bugs: #33699: UPDATE allows NULL as new value on NOT NULL columns (= default datatype value)

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

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.

Thank you for the bug report.

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.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

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.

Also documented for 5.1.23-ndb-6.2.14.

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.

Also note fix in the 5.1.23-ndb-6.3.11 changelog.

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.

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"?