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 22:30	Modified:	28 Jan 2008 19:09
Reporter:	Patrick Crews
Status:	Closed
Category:	Server: DML	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Gleb Shchepa	Target Version:	5.0+
Tags:	UPDATE, NOT NULL behavior
Triage:	D2 (Serious) / R2 (Low) / E3 (Medium)

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.