Bug #103941 select result error
Submitted: 8 Jun 2021 8:59 Modified: 9 Jun 2021 6:03
Reporter: Lu Sander Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[8 Jun 2021 8:59] Lu Sander 
Description:
select result error

How to repeat:
CREATE TABLE `UK_MU13363_tmp` (
  `COL1` text COLLATE utf8mb4_bin,
  `COL2` smallint DEFAULT NULL,
  `COL3` timestamp NULL DEFAULT NULL,
  `COL4` int DEFAULT NULL,
  UNIQUE KEY `U_M_COL2` (`COL2`) /*!80000 INVISIBLE */,
  UNIQUE KEY `U_M_COL3` (`COL3`) /*!80000 INVISIBLE */,
  UNIQUE KEY `U_M_COL` (`COL1`(10)) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

insert into UK_MU13363_tmp values('6387696006641199718',31968,'1996-06-30 08:56:21',828975366);

select * from UK_MU13363_tmp where COL2=31986
Empty set (0.70 sec)

when run 
insert into UK_MU13363_tmp(col1, col2) values(4889693311706689718, -13122), (0x48, 31968) on duplicate key update col1 = 6387696006641199718, col2 = 31968;

The sql run successfully, i think the sql should not be executed successfully.
[8 Jun 2021 13:13] MySQL Verification Team 
Hi Mr. Sander,

Thank you for your bug report.

However, it is not a bug.

In your first query, you mistyped the constant for COL2. When the error was removed, the query worked just fine:

COL1	COL2	COL3	COL4
6387696006641199718	31968	1996-06-30 08:56:21	828975366

Regarding your second quibble that INSERT should not succeed, it can not be considered a bug, since this was a design decision. That is how MySQL RDBMS was designed. It is not a 100 % strong typed SQL database.

There are plans to make it strong typed, but those will not be implemented so soon, because changing to the strong typed server would breaks too many applications.

Not a bug.
[9 Jun 2021 5:57] Lu Sander 
Hi, 
Thanks for replying to me.

For COL2, why is 31968 not a legal value? The value range of smallest shown in the document is -32768～32767.(https://dev.mysql.com/doc/refman/8.0/en/integer-types.html)
[9 Jun 2021 6:03] Lu Sander 
Hi, 
Thanks for replying to me.

For COL2, why is 31968 not a legal value? The value range of smallint shown in the document is -32768～32767.(https://dev.mysql.com/doc/refman/8.0/en/integer-types.html)
[9 Jun 2021 12:31] MySQL Verification Team 
Hi Mr. Sander,

31968 is a perfectly legal value, but the value in INSERT for the TEXT column are not.