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