tidb allows NULL value to NOT NULL column

[mzhang77]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> create table t( pk int primary key not null auto_increment, data binary(12) not null);
Query OK, 0 rows affected (0.06 sec)

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

mysql> select * from t;
+----+----------------------------+
| pk | data                       |
+----+----------------------------+
|  1 | 0x000000000000000000000000 |
+----+----------------------------+
1 row in set (0.00 sec)

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

Tidb should not allow insert of NULL value to NOT NULL column. See below mysql result:

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> create table t( pk int primary key not null auto_increment, data binary(12) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t(data) values (null);
ERROR 1048 (23000): Column 'data' cannot be null
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.40    |
+-----------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

tidb allows insert of NULL value to NOT NULL column, it's incompatible with mysql

4. What is your TiDB version? (Required)

mysql> select @@version;
+--------------------+
| @@version          |
+--------------------+
| 8.0.11-TiDB-v7.5.3 |
+--------------------+
1 row in set (0.00 sec)

[dveeden]

To make things easy to copy-paste:

select @@sql_mode;
create table t( pk int primary key not null auto_increment, data binary(12) not null);
insert into t(data) values (null);
select @@version;

Looks like a SET SESSION sql_mode='' might be missing.

MySQL 9.1.0:

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

mysql-9.1.0> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql-9.1.0> create table t( pk int primary key not null auto_increment, data binary(12) not null);
Query OK, 0 rows affected (0.02 sec)

mysql-9.1.0> insert into t(data) values (null);
ERROR 1048 (23000): Column 'data' cannot be null
mysql-9.1.0> select @@version;
+-----------+
| @@version |
+-----------+
| 9.1.0     |
+-----------+
1 row in set (0.00 sec)

TiDB v8.4.0:

mysql-8.0.11-TiDB-v8.4.0> \W
Show warnings enabled.
mysql-8.0.11-TiDB-v8.4.0> SET SESSION sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql-8.0.11-TiDB-v8.4.0> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql-8.0.11-TiDB-v8.4.0> create table t( pk int primary key not null auto_increment, data binary(12) not null);
Query OK, 0 rows affected (0.05 sec)

mysql-8.0.11-TiDB-v8.4.0> insert into t(data) values (null);
Query OK, 1 row affected, 1 warning (0.01 sec)

Warning (Code 1048): Column 'data' cannot be null
mysql-8.0.11-TiDB-v8.4.0> select @@version;
+--------------------+
| @@version          |
+--------------------+
| 8.0.11-TiDB-v8.4.0 |
+--------------------+
1 row in set (0.00 sec)

So the issue here is that without any SQL mode set MySQL returns an error for 1048 and TiDB returns a warning.

Note that MySQL 5.7.33 also returns an error, just like MySQL 8.0 and MySQL 9.1

[dveeden]

Errror 1048 is known in the code as ErrBadNull

[dveeden]

Extending the test to include INSERT IGNORE:

SET SESSION sql_mode='';
select @@sql_mode;
create table t( pk int primary key not null auto_increment, data binary(12) not null);
insert into t(data) values (null);
insert ignore into t(data) values (null);
select @@version;

[YangKeao]

Maybe fixed by #55477. Let me verify it.

[dveeden]

@YangKeao I was also working on #57504

[dveeden]

mysql-8.0.11-TiDB-v8.5.0-alpha-199-g14ff938d8c> \W
Show warnings enabled.
mysql-8.0.11-TiDB-v8.5.0-alpha-199-g14ff938d8c> SET SESSION sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql-8.0.11-TiDB-v8.5.0-alpha-199-g14ff938d8c> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql-8.0.11-TiDB-v8.5.0-alpha-199-g14ff938d8c> create table t( pk int primary key not null auto_increment, data binary(12) not null);
Query OK, 0 rows affected (0.01 sec)

mysql-8.0.11-TiDB-v8.5.0-alpha-199-g14ff938d8c> insert into t(data) values (null);
ERROR 1048 (23000): Column 'data' cannot be null
mysql-8.0.11-TiDB-v8.5.0-alpha-199-g14ff938d8c> insert ignore into t(data) values (null);
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1048): Column 'data' cannot be null
mysql-8.0.11-TiDB-v8.5.0-alpha-199-g14ff938d8c> select @@version;
+------------------------------------------+
| @@version                                |
+------------------------------------------+
| 8.0.11-TiDB-v8.5.0-alpha-199-g14ff938d8c |
+------------------------------------------+
1 row in set (0.00 sec)

mysql-8.0.11-TiDB-v8.5.0-alpha-199-g14ff938d8c> 

So this is fixed in master:

• INSERT with NULL results in an error, not a warning
• INSERT IGNORE with NULL results in a warning