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: | |
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
[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.