MySQL Bugs: #102776: insert into select on tinyint type, no warning, but with wrong result

Bug #102776	insert into select on tinyint type, no warning, but with wrong result
Submitted:	2 Mar 2021 7:10	Modified:	3 Mar 2021 15:52
Reporter:	tong gao	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> create table t1(a tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2(a tinyint signed);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` tinyint unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` tinyint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

mysql> insert ignore into t2 values("256");
Query OK, 1 row affected, 1 warning (0.00 sec)

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

mysql> select * from t2;
+------+
| a    |
+------+
|  127 |
+------+
1 row in set (0.00 sec)

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

mysql> select * from t2;
+------+
| a    |
+------+
|  127 |
|   -1 |
+------+
2 rows in set (0.00 sec)

mysql> \q
Bye

if "insert into t2 select * from t1;" no warning happend, the result should be:
mysql> select * from t2;
+------+
| a    |
+------+
|  127 |
|  255 |
+------+

How to repeat:
like Description.

Hi Mr. gao,

Thank you for your bug report.

However, this is not a bug.

What you describe is exactly how MySQL database server is designed. When it can not insert (or change) the value into a column, due to underflow or overflow, it does not stop transaction, but continues with a warning that is reported. All that user has to do is to view the warnings. It is so by design. Strict type checking will not be implemented soon, since it would brake millions of applications. Hence ....

Not a bug.