MySQL Bugs: #102942: prepare execute result is different from a query execute directly

Bug #102942	prepare execute result is different from a query execute directly
Submitted:	12 Mar 2021 6:53	Modified:	15 Mar 2021 9:34
Reporter:	track ay	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S1 (Critical)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
The result of prepare execute is different from a query directly.

MySQL test> select col1 from t group by col1 having col1 <=> 3218366921382928867;
+------+
| col1 |
+------+

0 rows in set
Time: 0.057s
MySQL test> select col1 from t group by col1 having col1 <=> 'abctest';
+---------+
| col1    |
+---------+
| abctest |
+---------+

mysql root@172.16.4.131:test>  prepare stmt from 'select col1 from t group by col1 having col1 <=> ?;';
Query OK, 0 rows affected
Time: 0.056s

MySQL test> set @a=3218366921382928867;
Query OK, 0 rows affected
Time: 0.044s
MySQL test> execute stmt using@a;
+------+
| col1 |
+------+
MySQL test>  set @a='abctest';
Query OK, 0 rows affected
Time: 0.046s
MySQL test> execute stmt using@a;
+----------+
| col1     |
+----------+
| abbtest1 |
| abctest  |
+----------+

How to repeat:
CREATE TABLE `t` (  `COL1` tinytext COLLATE utf8mb4_bin NOT NULL,  `COL2` tinyint NOT NULL,  `COL3` year NOT NULL,  PRIMARY KEY (`COL1`(5),`COL2`,`COL3`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into t (COL1,COL2,COL3) values("abbtest1",-109,"2110");
insert into t (COL1,COL2,COL3) values("abctest",-109,"2110");

prepare stmt from 'select col1 from t group by col1 having col1 <=> ?;';
set @a=3218366921382928867;
execute stmt using@a;
set @a='abctest';
execute stmt using@a;

Suggested fix:
The results of prepare execute should be same with a query that executes directly

There is also a problem with the next scene

mysql> prepare stmt from 'select col1 from t where col1= ?';
Query OK, 0 rows affected (0.05 sec)
Statement prepared

mysql> set @a=3218366921382928867;
Query OK, 0 rows affected (0.05 sec)

mysql> execute stmt using@a;
Empty set (0.05 sec)

mysql> set @a='abctest';
Query OK, 0 rows affected (0.04 sec)

mysql> execute stmt using@a;
+----------+
| col1     |
+----------+
| abbtest1 |
| abctest  |
+----------+
2 rows in set, 1 warning (0.05 sec)

Hello track ay,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

We do not consider this to be a bug.

The statement is first prepared so that the parameter is assumed to be a string,
since it is compared to a column (col1) with a string type.

On first execution, the parameter is specified as an integer. 
This is incompatible with the current preparation, so the statement is
reprepared so that the parameter is assumed to be a number, and the
equality operation is performed as a float operation.
This is done for compatibility with earlier MySQL versions.

On second execution, the parameter is specified as a string.
It is expected to be a number, due to the current preparation,
however MySQL will usually interpret such string values as a number,
so the current preparation is kept. Both the column values and the parameter
are converted to numbers, all of them being zero, due to MySQL's relaxed
conversion rules, hence both rows are returned by the statement.

Thus, for prepared statements, we support compatibility with earlier versions
when the type of the parameter is kept the same for each execution.
If the type of parameter is changed (why would you do that in an application?),
you should either explicitly reprepare the statement, or keep one statement per type of parameter.

This change has been done so that we can have more efficient prepared statements, by not having to reprepare the statement for each execution.