The phenomenon of executing DML after setting 'sql_mode' to non 'strict_mode' in tidb is not as expected

[BrianLiu955]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t1(id int primary key,name varchar(20) not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(1,null);
ERROR 1048 (23000): Column 'name' cannot be null
mysql>
mysql> set session sql_mode = "";
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1,null);
Query OK, 1 row affected, 1 warning (0.00 sec)

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

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | |
+----+------+
1 row in set (0.01 sec)

2. What did you expect to see? (Required)

mysql> insert into t1 values(1,null);
ERROR 1048 (23000): Column 'name' cannot be null

3. What did you see instead (Required)

mysql> insert into t1 values(1,null);
Query OK, 1 row affected, 1 warning (0.00 sec)

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

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | |
+----+------+
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.4.0-alpha-290-gf2ed822
Edition: Community
Git Commit Hash: f2ed822
Git Branch: HEAD
UTC Build Time: 2024-09-27 01:14:04
GoVersion: go1.21.13
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[YangKeao]

When the SQL_MODE is not strict, and the column doesn't have a default value, TiDB shouldn't allow to insert NULL values into this column.

This bug is introduced by #21237