MySQL Bugs: #103364: Passed string type binding for integer column incorrectly cast to integer

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:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	8.0.22,8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
There seems to be a bug in 8.0.22 and 8.0.23 which is not in 8.0.21 where I have an integer column and want to query rows less than let's say '1.9'. Only one row with value 1 should returned if those rows are (1, 2, 3). This is the case in 8.0.21 but not in 8.0.22 and 8.0.23. 

I experienced this in php pdo and it boiled down to mysql's prepare statement.

How to repeat:
# prepare table
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (int_column INT NOT NULL);

INSERT INTO my_table VALUES (1), (2), (3), (4);

# this works
SELECT * from my_table where int_column < '1.9';

# does not work in 8.0.22 and 8.0.23 but works in 8.0.21
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

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

Hello Orkhan Alikhanov,

Thank you for the report and test case.

regards,
Umesh

- 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

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.