MySQL Bugs: #53455: Different INSERT and UPDATE behaviour against NOT NULL fields constraint

Bug #53455	Different INSERT and UPDATE behaviour against NOT NULL fields constraint
Submitted:	6 May 2010 7:08	Modified:	6 May 2010 8:50
Reporter:	Vincent SALARD	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.30	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	1048, NOT NULL, UPDATE

Description:
Good morning,

On a simple table (whatever the engine) containing a NOT NULL (no default) constrained field, INSERT with NULL values is considered as 0 while UPDATE fails with error 1048.

This behavious doesn't exist on 5.0 versions.

How to repeat:
drop table if exists testtable;
create table testtable (a int, b int not null);
insert into testtable select 1, 0;
insert testtable select 3, NULL;
update testtable set a = 2, b = NULL where a = 1;
select * from testtable;

Suggested fix:
I consider throwing an exception also on INSERT as the most appropriate behaviour.

Please, check with a newer version and inform about the results. This is what I see with 5.1.46:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.1.46-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists testtable;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> create table testtable (a int, b int not null);
Query OK, 0 rows affected (0.20 sec)

mysql> insert into testtable select 1, 0;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert testtable select 3, NULL;
ERROR 1048 (23000): Column 'b' cannot be null
mysql> update testtable set a = 2, b = NULL where a = 1;
ERROR 1048 (23000): Column 'b' cannot be null
mysql> select * from testtable;
+------+---+
| a    | b |
+------+---+
|    1 | 0 |
+------+---+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.03 sec)

Thank you for the report.

This seems to be side effect of fix for bug #33699 and was removed when bug #39265 is fixed in version 5.1.32.

Please upgrade to current version 5.1.46, try with it and inform us if problem still exists.

I've just tested 5.1.46 on similar distribution and I confirm behaviour is restored to what existed in 5.0.x versions for default SQL_MODE and doesn't accept anymore NULL on NOT NULL fields insertions for STRICT_TRANS_TABLES SQL_MODE.

Thx for your fast feedback on this.

Thank you for the feedback.

Closed as duplicate of bug #39265.