Bug #111728 Incorrect result

Submitted: 12 Jul 2023 8:08 Modified: 12 Jul 2023 8:40
Reporter: Doris Li Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.28, 8.0.33 OS:Any
Assigned to: CPU Architecture:Any

[12 Jul 2023 8:08] Doris Li
Description:
When sql force DO NOT use index, the result is incorrect.

mysql> select value from tb where value in (select id from ta);
Empty set (0.00 sec)

mysql> select value from tb where value in (select /*+no_index(ta)*/ id from ta);
+---------------------+
| value               |
+---------------------+
| 1801425248110076222 |
+---------------------+
1 row in set (0.00 sec)

How to repeat:
mysql> create table ta(`id` bigint, primary key(`id`));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ta (`id`) values (1801425248110076165);
Query OK, 1 row affected (0.01 sec)

mysql> create table tb(value varchar(50));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb (value) values ("1801425248110076222");
Query OK, 1 row affected (0.01 sec)

mysql> select value from tb where value in (select id from ta);
Empty set (0.00 sec)

mysql> select value from tb where value in (select /*+no_index(ta)*/ id from ta);
+---------------------+
| value               |
+---------------------+
| 1801425248110076222 |
+---------------------+
1 row in set (0.00 sec)

This two sql should return the same result, but they don't.
[12 Jul 2023 8:40] MySQL Verification Team
Hello Doris Li,

Thank you for the report and test case.

regards,
Umesh

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.047 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.