MySQL Bugs: #39265: SQL_MODE OLD_NOT_NULL_CHECK_BEHAVIOUR for backward compatibility

Bug #39265	SQL_MODE OLD_NOT_NULL_CHECK_BEHAVIOUR for backward compatibility
Submitted:	5 Sep 2008 6:13	Modified:	17 Feb 23:28
Reporter:	Mikiya Okuno
Status:	Closed
Category:	Server: General	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Gleb Shchepa	Target Version:	5.1+
Triage:	Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

Description:
Before the bug #33699 is fixed, MySQL Server accepted NULL for UPDATE statement, but
didn't accept NULL for INSERT statement. (Omitting value was acceptable for INSERTs.)

Bug #33699 rejects NULL values for both INSERT and UPDATE. This could be an good
behavior, however it breaks compatibility when the application expects that NULL should
be accepted for UPDATEs. As a result, a user might re-write his or her application code.

How to repeat:
mysql> CREATE TABLE t1 (a INT NOT NULL);
mysql> INSERT INTO t1 VALUES(1);
mysql> UPDATE t1 SET a=NULL;

In 5.1.23-rc/6.0.4-alpha or earlier, column is set to a default value. But in
5.1.24-rc/6.0.5-alpha or later, this resulted in an error.

Suggested fix:
In order to keep backward compatibility, this behavior should be changeable/configurable.

Test:
create table t(a int not null);
insert into t values(1);
update t set a=NULL;

Behaviour in 5.0:
Rows matched: 1  Changed: 1  Warnings: 1
show warnings;
Warning | 1048 | Column 'a' cannot be null

SELECT * from t;
+---+
| a |
+---+
| 0 | 
+---+

update t set a=1;
set sql_mode=traditional;
update t set a=NULL;
ERROR 1048 (23000): Column 'a' cannot be null

As you can see in 5.0 when you update a column with NOT NULL CONSTRAINT by using non
strict mode it will insert 0 instead of NULL.

Behaviour in 5.1:
Consider, I used non strict mode.
update t set a=NULL;
ERROR 1048 (23000): Column 'a' cannot be null

According to the documentation and to the innoDB guys, strict/non strict mode not effects
constraints.

This means, the behaviour of 5.0 is a bug, because it should get an error message also by
using non strict mode.
Or there should be non constraints checks at all in non strict mode ... means also non
checks of unique, default or foreign key constraints.

Anyway, this was a very old bug and it was fixed.

Now it isn't backward compatible anymore.

Because trying to insert NULL in a column with NOT NULL Constraints by using non strict
mode occurs just a warning and 0 or '' instead of NULL this behaviour should configurable
in 5.1.

Because deleting all constraint checks from non strict mode would break old applications
too, I would suggest a new configure variable or a new sql mode with which you can
enable/disable not null checks.

The behaviour is not a bug at all. It is just a fix from a very old bug. SQL Standard
defines that you can't insert NULL in NOT NULL columns.

Feature Request: SQL_MODE=NOT_NULL_CHECK

This SQL mode should be enabled by default and also should be set by default for all
strict modes like traditional.

Only if the user will have problems with old applications or replication he/she should be
possible to enable the old NOT NULL CHECK behaviour.

Here is a workaround example:

CREATE TABLE t(i INTEGER NOT NULL);

ALTER TABLE t MODIFY i INTEGER;

DELIMITER §

CREATE TRIGGER tr_null BEFORE INSERT ON t
FOR EACH ROW BEGIN
IF NEW.i IS NULL THEN
SET NEW.i = 0;
END IF;
END§

DELIMITER ;

INSERT INTO t(i) VALUES(NULL);

SELECT * FROM t;
Result: i is 0

Just ran across this myself while trying to upgrade from 4.1 to 5.1.  This affects the
default install of interchange (http://www.icdevgroup.org/) when it is setup to use MySQL
(it supports other DBs).  While I agree the new behavior is more intuitive, I think there
really needs to be a config option as a workaround since this will affect a lot of
applications, and in the case of interchange, potentially some large open source projects
which are used to the old behavior.

I just realized that many who monitor the mysql bug db may not have heard of interchange. 
It is a perl-based application server that is used primarily for ecommerce sites.  It has
a moderate size following of developers and a very high install base due to its
incorporation into cpanel http://www.cpanel.net/docs/cpanel/Interchange_Shopping_Cart.htm
 I think a lot of hosting companies would expect sql mode MYSQL40 to replicate mysql 4.x's
behavior and I can see this bug causing some problems there.

I just hit this problem by an automatic upgrade in my linux 
distribution to the 5.1 GA code packages.

A compatiblity mode flag would simplify the transition to the 
new behaviour.  

(my current workaround is to downgrade to 5.0)

thanks,
-bill

Also see bug #37787.

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/65164

2776 Gleb Shchepa	2009-02-04
      Bug #39265: fix for the bug 33699 should be reverted
      
      Documented behaviour was broken by the patch for bug 33699
      that actually is not a bug.
      
      This fix reverts patch for bug 33699 and reverts the
      UPDATE of NOT NULL field with NULL query to old
      behavior.

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/65309

2784 Gleb Shchepa	2009-02-05
      Bug #39265: fix for the bug 33699 should be reverted
      
      Documented behaviour was broken by the patch for bug 33699
      that actually is not a bug.
      
      This fix reverts patch for bug 33699 and reverts the
      UPDATE of NOT NULL field with NULL query to old
      behavior.

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/65344

2786 Gleb Shchepa	2009-02-05
      after-push test update (bug #39265)

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/65390

2788 Gleb Shchepa	2009-02-05
       after-after-push testcase update (bug #39265)

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/65433

3024 Gleb Shchepa	2009-02-06
      after-push test case update (bug #39265)

Pushed into 5.1.32 (revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (version
source revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (merge vers: 5.1.32)
(pib:6)

Pushed into 6.0.10-alpha (revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy)
(version source revid:alexey.kopytov@sun.com-20090206100220-tkvd9v83791i895x) (merge
vers: 6.0.10-alpha) (pib:6)

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef)
(version source revid:tomas.ulin@sun.com-20090216083408-rmvyaxjt6mk8sg1y) (merge vers:
5.1.32-ndb-6.3.23) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau)
(version source revid:tomas.ulin@sun.com-20090216083646-m8st11oj1hhfuuh5) (merge vers:
5.1.32-ndb-6.4.3) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j)
(version source revid:tomas.ulin@sun.com-20090211111208-wf0acl7c1vl5653e) (merge vers:
5.1.32-ndb-6.2.17) (pib:6)

Noted in 5.1.32, 6.0.10 changelogs.

The change to UPDATE introduced by Bug#33699 was reverted to restore
the original behavior: 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
http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html