Bug #103364 Passed string type binding for integer column incorrectly cast to integer
Submitted: 19 Apr 8:48 Modified: 19 Apr 9:12
Reporter: Orkhan Alikhanov Email Updates:
Status: Verified 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

[19 Apr 8:48] Orkhan Alikhanov
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
[19 Apr 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 9:12] MySQL Verification Team
Hello Orkhan Alikhanov,

Thank you for the report and test case.

regards,
Umesh