Description:
When a prepared statement is first created, it can successfully be executed as expected with its arguments bound with either numeric or string typed values.
After the first time a prepared statement is executed with a numeric value bound to an argument, string values can no longer be used. At that time, when the prepared statement is executed with a string value bound it emits a conversion warning with code 1292 for each row evaluated.
This bug is new in mysql version 8, and is/was not present in mysql server 5.7.
How to repeat:
mysql> create table t (
-> id integer primary key,
-> val json
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t(id, val) values (1, '1'), (2, '"two"'), (3, '"three"');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> prepare stmt from 'select *, val = ? from t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> set @one = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> set @two = 'two';
Query OK, 0 rows affected (0.00 sec)
mysql> set @three = 'three';
Query OK, 0 rows affected (0.00 sec)
mysql> execute stmt using @three;
+----+---------+---------+
| id | val | val = ? |
+----+---------+---------+
| 1 | 1 | 0 |
| 2 | "two" | 0 |
| 3 | "three" | 1 |
+----+---------+---------+
3 rows in set (0.00 sec)
mysql> execute stmt using @two;
+----+---------+---------+
| id | val | val = ? |
+----+---------+---------+
| 1 | 1 | 0 |
| 2 | "two" | 1 |
| 3 | "three" | 0 |
+----+---------+---------+
3 rows in set (0.00 sec)
mysql> execute stmt using @one;
+----+---------+---------+
| id | val | val = ? |
+----+---------+---------+
| 1 | 1 | 1 |
| 2 | "two" | 0 |
| 3 | "three" | 0 |
+----+---------+---------+
3 rows in set (0.00 sec)
mysql> execute stmt using @two;
+----+---------+---------+
| id | val | val = ? |
+----+---------+---------+
| 1 | 1 | 0 |
| 2 | "two" | 0 |
| 3 | "three" | 0 |
+----+---------+---------+
3 rows in set, 3 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'two' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'two' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'two' |
+---------+------+------------------------------------------+
3 rows in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.2.0 |
+-----------+
1 row in set (0.00 sec)
mysql>
Suggested fix:
Executing a prepared statement with a numeric value bound to its arguments should not permanently change the state of the prepared statement. It should be possible to execute the prepared statement with string values bound after that.
Description: When a prepared statement is first created, it can successfully be executed as expected with its arguments bound with either numeric or string typed values. After the first time a prepared statement is executed with a numeric value bound to an argument, string values can no longer be used. At that time, when the prepared statement is executed with a string value bound it emits a conversion warning with code 1292 for each row evaluated. This bug is new in mysql version 8, and is/was not present in mysql server 5.7. How to repeat: mysql> create table t ( -> id integer primary key, -> val json -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into t(id, val) values (1, '1'), (2, '"two"'), (3, '"three"'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> prepare stmt from 'select *, val = ? from t'; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> set @one = 1; Query OK, 0 rows affected (0.00 sec) mysql> set @two = 'two'; Query OK, 0 rows affected (0.00 sec) mysql> set @three = 'three'; Query OK, 0 rows affected (0.00 sec) mysql> execute stmt using @three; +----+---------+---------+ | id | val | val = ? | +----+---------+---------+ | 1 | 1 | 0 | | 2 | "two" | 0 | | 3 | "three" | 1 | +----+---------+---------+ 3 rows in set (0.00 sec) mysql> execute stmt using @two; +----+---------+---------+ | id | val | val = ? | +----+---------+---------+ | 1 | 1 | 0 | | 2 | "two" | 1 | | 3 | "three" | 0 | +----+---------+---------+ 3 rows in set (0.00 sec) mysql> execute stmt using @one; +----+---------+---------+ | id | val | val = ? | +----+---------+---------+ | 1 | 1 | 1 | | 2 | "two" | 0 | | 3 | "three" | 0 | +----+---------+---------+ 3 rows in set (0.00 sec) mysql> execute stmt using @two; +----+---------+---------+ | id | val | val = ? | +----+---------+---------+ | 1 | 1 | 0 | | 2 | "two" | 0 | | 3 | "three" | 0 | +----+---------+---------+ 3 rows in set, 3 warnings (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: 'two' | | Warning | 1292 | Truncated incorrect INTEGER value: 'two' | | Warning | 1292 | Truncated incorrect INTEGER value: 'two' | +---------+------+------------------------------------------+ 3 rows in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 8.2.0 | +-----------+ 1 row in set (0.00 sec) mysql> Suggested fix: Executing a prepared statement with a numeric value bound to its arguments should not permanently change the state of the prepared statement. It should be possible to execute the prepared statement with string values bound after that.