Bug #111483 Different truncated result between select and delete

• Submitted: 19 Jun 2023 8:48
• Modified: 19 Jun 2023 13:25
• Reporter: Siyang Weng
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Any
• 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.