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 4:13	Modified:	17 Feb 2009 22:28
Reporter:	Mikiya Okuno	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

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

Bug #53455 was marked as duplicate of this one.

It is happening again in MySQL 8.0.11

Hello Jesús,

> It is happening again in MySQL 8.0.11

No, as documented, "If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type":

Server version: 8.0.11-debug Source distribution
...
mysql> CREATE TABLE t1 (a INT NOT NULL);                                                                                                                                                                           Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t1 VALUES(1);                                                                                                                                                                                   Query OK, 1 row affected (0.04 sec)

-- Enable the strict mode (STRICT_TRANS_TABLES):

mysql> SET sql_mode=DEFAULT;                                                                                                                                                                                       Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

-- STRICT_TRANS_TABLES is "on", UPDATE fails with an error:

mysql> UPDATE t1 SET a=NULL;                                                                                                                                                                                       ERROR 1048 (23000): Column 'a' cannot be null

-- Disable the strict mode:

mysql> SET sql_mode=0;
Query OK, 0 rows affected (0.00 sec)

-- STRICT_TRANS_TABLES is "off", UPDATE succeeds with a warning:

mysql> UPDATE t1 SET a=NULL;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'a' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

-- `a` is updated with the implicit default value for INT:

mysql> SELECT a FROM t1;                                                                                                                                                                                           +---+
| a |
+---+
| 0 |
+---+
1 row in set (0.00 sec)