Bug #111483 Different truncated result between select and delete
Submitted: 19 Jun 2023 8:48 Modified: 19 Jun 2023 13:25
Reporter: Siyang Weng Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.33 OS:Any
Assigned to: CPU Architecture:Any

[19 Jun 2023 8:48] Siyang Weng
Description:
The truncation between double and string performs differently for predicates in select and delete. 
When select with predicate `1e0 + '--1'`, it truncates string '--1' and returns 1e0. But delete with the same predicate reports a error and can't delete 1e0.

How to repeat:
init:
create table t (a double);
insert into t values(1e0);

select * from t0 where a = 1e0 + "--1"; -- return 1e0, i.e. 1
delete from t0 where a = 1e0 + "--1"; -- report ERROR 1292 (22007): Truncated incorrect DOUBLE value: '--1'
[19 Jun 2023 13:25] MySQL Verification Team
Hi Mr. Weng,

Thank you for your bug report.

However, it is not a bug.

In SELECT, MySQL does its best to accommodate two completely different data types.being DOUBLE and CHAR / VARCHAR. This SELECT usually returns a warning, when that (warning) command is executed. That is because SELECT is a read-only statement and can not change data.

However, with DELETE, more strict typing is required in order to prevent the erasure by mistake.

Not a bug.