MySQL Bugs: #79792: NULL value is inserted explicitly into NOT NULL column unexpected

Bug #79792	NULL value is inserted explicitly into NOT NULL column unexpected
Submitted:	29 Dec 2015 3:12	Modified:	2 Dec 2019 22:41
Reporter:	Su Dylan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0/5.1/5.5/5.6/5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(c1 int not null);
alues(null);
select * Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(null);
ERROR 1048 (23000): Column 'c1' cannot be null
mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 values(null),(1);
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from t1;
+----+
| c1 |
+----+
|  0 |
|  1 |
+----+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
With one row (NULL) trying inserting into NOT NULL column, it is rejected. However, when trying two rows, with one row as (NULL), it succeeds, by replacing the NULL with 0(the implicit default for int).
This behavior is inconsistent.

How to repeat:

set sql_mode='';
drop table if exists t1;
create table t1(c1 int not null);
insert into t1 values(null);
select * from t1;
insert into t1 values(null),(1);
select * from t1;

Suggested fix:
No matter insert one row(NULL) or two rows (NULL),(1), both should be rejected with an error message "Column 'c1' cannot be null".

Thank you for the bug report. Repeatable since 5.0.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.11 Source distribution PULL: 2015-DEC-27

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.7 > use test
Database changed
mysql 5.7 > set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > drop table if exists t1;
Query OK, 0 rows affected (0.06 sec)

mysql 5.7 > create table t1(c1 int not null);
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > insert into t1 values(null);
ERROR 1048 (23000): Column 'c1' cannot be null
mysql 5.7 > select * from t1;
Empty set (0.00 sec)

mysql 5.7 > insert into t1 values(null),(1);
Query OK, 2 rows affected, 1 warning (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql 5.7 > select * from t1;
+----+
| c1 |
+----+
|  0 |
|  1 |
+----+
2 rows in set (0.00 sec)

mysql 5.7 >

This is documented behavior, but I wonder what does SQL standards say?
https://dev.mysql.com/doc/refman/5.7/en/insert.html

"Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types,..."

Posted by developer:
 
Fixed in 5.7.29