| Bug #114997 | Floating Point Precision Issues in CHECK Constraints | ||
|---|---|---|---|
| Submitted: | 14 May 2024 11:22 | Modified: | 15 May 2024 9:01 |
| Reporter: | Niccolò Parlanti | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 8.4.0 | OS: | Linux |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | check constraint | ||
[14 May 2024 13:43]
MySQL Verification Team
Hello Niccolò Parlanti, Thank you for the report and feedback. regards, Umesh
[15 May 2024 7:48]
Roy Lyseng
Posted by developer: This is not a bug. It is well known that rounding errors when converting to and from floating point values may cause issues like this. Here, you have a column that is a character string. The CHECK constraint interprets this as an integer number, however with MySQL expression rules it is converted to a floating point value. Further, a decimal value (although wrapped in quotes so that it adheres to the VARCHAR specification) is inserted. Thus, to validate the CHECK constrain, a number of conversions have to be performed. It would be better that you either make the column c1 a numeric type, or add an explicit CAST of the value in the check constraint. The CAST could be to a SIGNED value, or a DECIMAL value, depending on your intentions.
[15 May 2024 9:01]
Niccolò Parlanti
Okay, thank you for the feedback !
Description: CHECK constraints with floating-point comparisons allow unexpected data due to precision issues. How to repeat: CREATE TABLE t1 ( id INT AUTO_INCREMENT PRIMARY KEY, c1 VARCHAR(255), CONSTRAINT chk_c1 CHECK (c1 = 65) ); INSERT INTO t1 (c1) VALUES ('65.000000000000001');