Bug #103027 prepare execute result is different from a query execute directly

• Submitted: 18 Mar 2021 3:21
• Modified: 18 Mar 2021 9:00
• Reporter: Guo Wenxuan
• Status: Not a Bug
• Category: MySQL Server: Prepared statements
• Severity: S2 (Serious)
• Version: 8.0.23
• OS: Any
• CPU Architecture: Any
• Tags: regression

[18 Mar 2021 3:21] Guo Wenxuan

Description:
correct:
mysql> select col1 from t1 group by col1 having count(col1) <=> "1E2ov3rgyW7";
Empty set

incorrect:
mysql> prepare stmt from 'select col1 from t1 group by col1 having count(col1) <=> ?;';
Query OK, 0 rows affected (0.09 sec)
Statement prepared

mysql> set @a="1E2ov3rgyW7";
Query OK, 0 rows affected (0.09 sec)

mysql> execute stmt using@a;
+----------------------------------------+
| col1                                   |
+----------------------------------------+
| 夹蜥揚牷光虺募窩牿龳儮岅溊袠啘Ɲ佊瞫囡疥 |
+----------------------------------------+
1 row in set (0.45 sec)

How to repeat:
create table t1(col1 char(20));
insert into t1 values("夹蜥揚牷光虺募窩牿龳儮岅溊袠啘Ɲ佊瞫囡疥");

[18 Mar 2021 4:56] MySQL Verification Team

Hello xuan wen,

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

regards,
Umesh

[18 Mar 2021 5:09] MySQL Verification Team

--

version()
8.0.23

select col1 from t1 group by col1 having count(col1) <=> "1E2ov3rgyW7";
col1

prepare stmt from 'select col1 from t1 group by col1 having count(col1) <=> ?;';
set @a="1E2ov3rgyW7";
execute stmt using @a;
col1
夹蜥揚牷光虺募窩牿龳儮岅溊袠啘Ɲ佊瞫囡疥

[18 Mar 2021 5:10] MySQL Verification Team

- 5.6.51, 5.7.33 - looks good

--
select col1 from t1 group by col1 having count(col1) <=> "1E2ov3rgyW7";
col1

prepare stmt from 'select col1 from t1 group by col1 having count(col1) <=> ?;';
set @a="1E2ov3rgyW7";
execute stmt using @a;
col1

[18 Mar 2021 9:00] Roy Lyseng

This is not a bug.
There is a slightly different interpretation in the regular vs prepared case.
In the regular case, the equality operation is performed as a double precision
operation, and the constant "1E2ov3rgyW7" is interpreted as "1E2", which is again equal to 100.0E0.
In the prepared case, we do the equality operation as an integer operation and
the constant value is interpreted as 1, which makes the equality true.

It may be a good idea to avoid string constant values with relaxed interpretations when comparing to numeric values.