Bug #103364 | Passed string type binding for integer column incorrectly cast to integer | ||
---|---|---|---|
Submitted: | 19 Apr 2021 8:48 | Modified: | 5 Nov 2021 19:07 |
Reporter: | Orkhan Alikhanov | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Prepared statements | Severity: | S2 (Serious) |
Version: | 8.0.22,8.0.23 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | regression |
[19 Apr 2021 8:48]
Orkhan Alikhanov
[19 Apr 2021 8:54]
Orkhan Alikhanov
Title says incorrectly cast to integer, because I believe that '1.9' becomes (int) 1.9 which is 1 somewhere internally based on rows that is returns. e.g it returns row with int_column value 0 or changing comparison to <= makes it return both int_column value 0 and 1
[19 Apr 2021 9:12]
MySQL Verification Team
Hello Orkhan Alikhanov, Thank you for the report and test case. regards, Umesh
[19 Apr 2021 9:14]
MySQL Verification Team
- 8.0.23 - affected SELECT * from my_table where int_column < '1.9'; int_column 1 PREPARE stmt FROM 'SELECT * from my_table where int_column < ?'; SET @a = '1.9'; # changing this to float 1.9 makes it work EXECUTE stmt USING @a; DEALLOCATE PREPARE stmt int_column - 5.6.51/5.7.33 - looks fine SELECT * from my_table where int_column < '1.9'; int_column 1 PREPARE stmt FROM 'SELECT * from my_table where int_column < ?'; SET @a = '1.9'; # changing this to float 1.9 makes it work EXECUTE stmt USING @a; DEALLOCATE PREPARE stmt int_column 1
[5 Nov 2021 19:07]
Christine Cole
Posted by developer: Fixed as of the upcoming MySQL 8.0.28 release, and here's the proposed changelog entry from the documentation team: When executing a prepared statement that included values passed in as strings, MySQL attempted to parse them as integers and could return errors unrelated to the input value. After a recent change, dynamic parameter handling was refactored so that the derived data type for parameters was determined based on context. For example, in a comparison such as = ?, the parameter was given the same type as the (integer) column it was compared to. To preserve compatibility with existing MySQL applications, if a decimal or float value was supplied as parameter, the statement was automatically reprepared with new type assigned to the parameter based on the actual value. This handling preserved compatibility for numeric parameters. However, if a string parameter was supplied, it was still interpreted as an integer (the resolved data type) and this behavior was not compatible with older MySQL versions that detected the actual type of the value. The consequences being that if = ? is executed with the parameter value '1.7', only the integer part of the string was used, making the effective comparison = 1. To fix the issue, now when a string parameter is supplied, the parameter is analyzed to determine if it is an integer, a decimal, or a float value and the actual data type of the parameter is updated accordingly. Later, the actual type is compared to the resolved type and if it is incompatible, the statement is reprepared with the new actual type. So, the previous statement now evaluates as int_col> = 1.7 and the comparison evaluates using decimal numbers. Thank you for the bug report.