Bug #103194 Prepare execution result is wrong

• Submitted: 2 Apr 2021 8:02
• Modified: 2 Apr 2021 15:32
• Reporter: Guo Wenxuan
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.23
• OS: Any
• CPU Architecture: Any

[2 Apr 2021 8:02] Guo Wenxuan

Description:
incorrect:
mysql>  prepare stmt from 'select col1 from t1 group by col1 having col1 = ?;';
Query OK, 0 rows affected (0.04 sec)
Statement prepared
 
mysql> set @a=4774268323486106588;
Query OK, 0 rows affected (0.04 sec)
 
mysql> execute stmt using @a;
Empty set
 
mysql> set @a="mysql";
Query OK, 0 rows affected (0.04 sec)
 
mysql> execute stmt using @a;
+--------+
| col1   |
+--------+
| mysql  |
| oracle |
+--------+
2 rows in set (0.04 sec)
 
correct:

mysql> select col1 from t1 group by col1 having col1 = "mysql";
+-------+
| col1  |
+-------+
| mysql |
+-------+
1 row in set (0.04 sec)
 

How to repeat:
CREATE TABLE t1(
  col1 varchar(255) COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into t1 values("mysql");
insert into t1 values("oracle");

[2 Apr 2021 9:13] MySQL Verification Team

Thank you for the bug report.

[2 Apr 2021 15:32] Roy Lyseng

This is not a bug.
On first execution, the statement is automatically reprepared so that col1 is
compared to an integer. On second execution, the parameter is interpreted as
an integer, and "mysql" is then interpreted as zero.
You can avoid this kind of anomaly by using consistent types for parameters
when executing prepared statements.