Bug #101727 insert null into a not null column in the empty SQL mode is not consistent

• Submitted: 24 Nov 2020 4:07
• Modified: 24 Nov 2020 6:44
• Reporter: wj huang
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.20, 5.7.32, 8.0.22
• OS: Any
• CPU Architecture: Any

[24 Nov 2020 4:07] wj huang

Description:
mysql> create table t1 (id int NOT NULL DEFAULT 8);
Query OK, 0 rows affected (0.01 sec)

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

mysql> insert into t1 values (null);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into t1 values (1), (null);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1
mysql> insert into t1 values (null), (null);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+----------------------------+
| Level   | Code | Message                    |
+---------+------+----------------------------+
| Warning | 1048 | Column 'id' cannot be null |
+---------+------+----------------------------+
1 row in set (0.01 sec)

I hope that both of the statements return warnings or errors. But What I see is that, If insert only a NULL, I get an error. If insert more than one value, I get a warning.

How to repeat:
create table t1 (id int NOT NULL DEFAULT 8);
SET sql_mode = '';
insert into t1 values (null);
insert into t1 values (1), (null);
insert into t1 values (null), (null);

[24 Nov 2020 6:31] MySQL Verification Team

Hello wj huang,

Thank you for the report and test case.

regards,
Umesh

[24 Nov 2020 6:44] MySQL Verification Team

Imho this is duplicate of our Shane's Bug #90848. Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug instead. Thank you for your interest in MySQL.

regards,
Umesh