Bug #106558 A float point cast happened in UPDATE statement
Submitted: 24 Feb 2022 12:10 Modified: 24 Feb 2022 14:48
Reporter: John Jove Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.28 OS:Ubuntu (20.04)
Assigned to: CPU Architecture:x86

[24 Feb 2022 12:10] John Jove
Description:
In the UPDATE statement, I think CAST(0.4 AS CHAR) is forbidden and UPDATE should fail. But I run the following statements and UPDATE succeeded.

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 INT);
INSERT INTO t0 VALUES(1);
UPDATE t0 SET c0 = 2 WHERE CAST(CAST(0.4 AS CHAR)^1 AS CHAR);
[24 Feb 2022 14:31] MySQL Verification Team
HI Mr. Jove,

Thank you for your bug report.

The behaviour of the UPDATE is quite correct in this case. That is because the filtering condition uses only constants, which render a result of 1. This is identical as writing the statement as :

UPDATE t0 SET c0 = 2 WHERE 1;

Hence, MySQL server has to update all rows since the filtering condition is 100 % deterministic. It might not be what you are looking for, which is why we recommend to beginners to use --safe-updates on the client side. 

When we use that option, then this update will return the error .......

You can use that option in your own application, by setting:

ET SQL_SAFE_UPDATES=1;

Not a bug.
[24 Feb 2022 14:48] John Jove
I think this UPDATE statement can be rewritten as:
UPDATE t0 SET c0 = 2 WHERE CAST('0.4'^1 AS CHAR);

Because I use the statement SELECT CAST(0.4 AS CHAR) = '0.4'; to check the equality, and the query result is true. While I execute the above UPDATE statement, a data truncate error is returned. That's why I think the UPDATE statement in the top case should also fail. I want to know how can this difference happen.
[24 Feb 2022 14:54] MySQL Verification Team
A difference occurs due to the common denominator between the two types used in the last expression.

It is all explained in our Reference Manual, where you can find all answers on how are expressions evaluated in different conditions, including using different domains and operators.

We recommend that every user of our software reads entire Reference Manual.