Bug #110536 Confusion about select null handling

• Submitted: 29 Mar 2023 2:59
• Modified: 29 Mar 2023 12:22
• Reporter: chaizhigang chai
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.7+ or 8.0+
• OS: Any
• CPU Architecture: Any

[29 Mar 2023 2:59] chaizhigang chai

Description:
insert into t1(name) values(null);

insert into t1 select null;

In some cases the processing logic may be different

How to repeat:
CREATE TABLE `t1` (
  `name` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> set sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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

# insert into ... values  or insert into ... select
All methods are reported wrong, this is in line with expectations
Then set sql_mode to null,

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

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

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

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

# insert into t1 select null 
The SQL execution was successful. This does not seem to be as expected

Suggested fix:
Not sure if this is a bug

[29 Mar 2023 12:22] MySQL Verification Team

Hi Mr. chai,

Thank you for your bug report.

However, it is not a bug.

In the second example, when sql_mode is empty, MySQL has to treat SELECT NULL as part of the expression in the INSERT statement. INSERT ...... (from) .......  SELECT ...... is not covered by SQL standards, so we chose to treat is an expression. Since sql_mode is empty, MySQL converts that expression to the nearest one for the CHAR  / VARCHAR domain.

In short, this is expected behaviour. 

BTW, it is not recommended to play with sql_mode, since it might lead to unexpected results. 

Not a bug.