MySQL Bugs: #101806: MySQL tries to parse string-bound float as integer and returns wrong error

Bug #101806	MySQL tries to parse string-bound float as integer and returns wrong error
Submitted:	30 Nov 2020 16:38	Modified:	5 Nov 2021 19:05
Reporter:	Domen Kermc	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This bug report contains two issues. Both are related to error emitted when executing queries below. Error: "ERROR 1292 (22007): Truncated incorrect INTEGER value: '0.3'".

1. When executing prepared statement using float value passed as a string, MySQL tries to parse it as integer and returns error.

In PHP, variables are passed to prepared statements as strings by default. PHP currently does not support float-bound parameters. See discussion on https://bugs.php.net/bug.php?id=80445

2. Returned SQLSTATE error code is 22007, which is related to date/time values.

How to repeat:
CREATE TABLE `test` (`field` INT NOT NULL) ENGINE=InnoDB;
INSERT INTO `test` (`field`) VALUES (0);
PREPARE stmt FROM 'UPDATE `test` SET `field` = 100 * ?';
SET @a = '0.3';
EXECUTE stmt USING @a;

Suggested fix:
As suggested on mentioned PHP bug, there are at least two possible workarounds:

  100.0 * ?

and

  100 * CAST(? AS FLOAT)

Thank you for the bug report.

I have a similar situation, but the error code is 1292.
The query executing direct works fine. But using a prepared statement returns an error.

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.