MySQL Bugs: #107811: Type conversion caused by reprepare would produce wrong results

Bug #107811	Type conversion caused by reprepare would produce wrong results
Submitted:	8 Jul 2022 10:31	Modified:	16 Jul 2022 8:43
Reporter:	Xiaodi Z	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	prepared statement, reprepare

Description:
Type conversion caused by reprepare would produce wrong results

Reported by GoldenDB Team

How to repeat:
create table t1(c1 int unsigned, c2 bigint unsigned);
insert into t1 values(1, 18446744073709551615);
prepare p1 from 'select * from t1 where c2 = ?';
prepare p2 from 'select * from t1 where c2 = ? and c1 = ?';
set @1 = '18446744073709551615';
set @2 = 18446744073709551615;
set @3 = 1;

mysql> execute p1 using @1;     // OK
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | 18446744073709551615 |
+------+----------------------+
1 row in set (0.00 sec)

mysql> execute p2 using @1, @3; // reprepare
Empty set (0.00 sec)

mysql> execute p2 using @2, @3; // reprepare again
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | 18446744073709551615 |
+------+----------------------+
1 row in set (0.00 sec)

mysql> execute p2 using @1, @3;
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | 18446744073709551615 |
+------+----------------------+
1 row in set (0.00 sec)

The results of case 2 and 4 are different, and it could be repeated in JDBC

Hello Xiaodi Z,

Thank you for the report and test case.
This issue seems to be fixed in 8.0.28, quoting from 8.0.28 change log "

When executing a prepared statement that included values passed in as strings, MySQL attempted to parse them as integers and could return an error 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 int_col = ?, 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 int_col = ? is executed with the parameter value '1.7', only the integer part of the string was used, making the effective comparison int_col = 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. (Bug #101806, Bug #32213576, Bug #103364, Bug #32787037). Please see https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html

Could you please check and confirm at your end? Thank you.

- 8.0.28/29

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table t1(c1 int unsigned, c2 bigint unsigned);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1, 18446744073709551615);
Query OK, 1 row affected (0.01 sec)

mysql> prepare p1 from 'select * from t1 where c2 = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> prepare p2 from 'select * from t1 where c2 = ? and c1 = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

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

mysql> set @2 = 18446744073709551615;
Query OK, 0 rows affected (0.00 sec)

mysql> set @3 = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute p1 using @1;
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | 18446744073709551615 |
+------+----------------------+
1 row in set (0.00 sec)

mysql> execute p2 using @1, @3;
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | 18446744073709551615 |
+------+----------------------+
1 row in set (0.00 sec)

mysql> execute p2 using @2, @3;
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | 18446744073709551615 |
+------+----------------------+
1 row in set (0.00 sec)

mysql>  execute p2 using @1, @3;
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | 18446744073709551615 |
+------+----------------------+
1 row in set (0.00 sec)

- 8.0.27

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> create table t1(c1 int unsigned, c2 bigint unsigned);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1, 18446744073709551615);
Query OK, 1 row affected (0.00 sec)

mysql> prepare p1 from 'select * from t1 where c2 = ?';
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> prepare p2 from 'select * from t1 where c2 = ? and c1 = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

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

mysql> set @2 = 18446744073709551615;
Query OK, 0 rows affected (0.00 sec)

mysql> set @3 = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute p1 using @1;
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | 18446744073709551615 |
+------+----------------------+
1 row in set (0.00 sec)

mysql> execute p2 using @1, @3;
Empty set (0.00 sec)

mysql>  execute p2 using @2, @3;
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | 18446744073709551615 |
+------+----------------------+
1 row in set (0.00 sec)

mysql> execute p2 using @1, @3;
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | 18446744073709551615 |
+------+----------------------+
1 row in set (0.00 sec)

regards,
Umesh
"

Thank you for your reply. This issue has been fixed in 8.0.28. Let me close it.