MySQL Bugs: #104595: result in prepare statement is different from dynamic SQL.

Bug #104595	result in prepare statement is different from dynamic SQL.
Submitted:	11 Aug 2021 11:50	Modified:	13 Aug 2021 1:53
Reporter:	casa zhang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The result in prepare statement sometimes is different from dynamic SQL. It seems like some conversions between datatypes are not unified. 

How to repeat:
mysql> prepare stmt1 from "select * from t_bigint where id = ?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @value = '1.1';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt1 using @value;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set, 3 warnings (0.00 sec)

mysql> select * from t_bigint where id = '1.1';
Empty set (0.00 sec)

Suggested fix:
In prepared statement, convert the both string and int to double?

data:
CREATE TABLE t_bigint(id BIGINT);
INSERT INTO t_bigint VALUES (1), (2);

the results in oracle is same.

and mysql has the warnings:
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '1.1' |
| Warning | 1292 | Truncated incorrect INTEGER value: '1.1' |
| Warning | 1292 | Truncated incorrect INTEGER value: '1.1' |
+---------+------+------------------------------------------+

is that a bug?

Hi Mr. zhang,

Thank you for your bug report.

However, it is not a bug.

Integer columns do not accept floating point nor fixed point values. Hence, your prepared statement can not return any rows.

Not a bug.

mysql> select * from t_bigint where id = '1.1';
Empty set (0.00 sec)

In dynamoc SQl, in prepare phase, `id` column converts to double type, and '1.1' string also converts to double type, so it return no results.

mysql> prepare stmt1 from "select * from t_bigint where id = ?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @value = '1.1';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt1 using @value;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set, 3 warnings (0.00 sec)

But why in prepare statement, `id` column doesn't convert to double type, so `execute stmt1 using @value` return one row?

Hi,

It is due to the fact that strong typing was not enforced on our server, although it would have been a cleaner solution.

This is due to the fact that its introduction would break millions of applications. Hence, our server is designed to do what is possible, under the circumstances.

thanks

You are truly welcome ......