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: | |
| 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: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.
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.