bug:If the field you used in the join condition is primary key, using "NOT EXISTS" will return wrong result.

[haitaoguan]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

create table tt1(id int primary key,name varchar(5),copy_id int) engine=tianmu;
insert into tt1 values(1,'AAA',1),(2,'AAA',2),(3,'BBB',3),(4,'BBB',4),(5,'CCC',5);
create table tt2(id int primary key,name varchar(5),copy_id int) engine=tianmu;
insert into tt2 values(1,'BBB',1),(2,'BBB',2),(3,'CCC',3),(4,'CCC',4),(5,'DDD',5);

mysql> select *
    ->   from tt1
    ->  where not exists (select 1
    ->           from tt2
    ->          where tt1.id = tt2.id and name='BBB');
Empty set (0.00 sec)

If we set the parameter “tianmu_index_search=off", will return right result.

mysql> set global tianmu_index_search=off;
Query OK, 0 rows affected (0.00 sec)

mysql> select *
    ->   from tt1
    ->  where not exists (select 1
    ->           from tt2
    ->          where tt1.copy_id = tt2.copy_id and name='BBB');
+----+------+---------+
| id | name | copy_id |
+----+------+---------+
|  3 | BBB  |       3 |
|  4 | BBB  |       4 |
|  5 | CCC  |       5 |
+----+------+---------+
3 rows in set (0.00 sec)

If the field you used in the join condition is not an index column, using "NOT EXISTS" will return right result.Independent of parameters.

mysql> select *
    ->   from tt1
    ->  where not exists (select 1
    ->           from tt2
    ->          where tt1.copy_id = tt2.copy_id and name='BBB');
+----+------+---------+
| id | name | copy_id |
+----+------+---------+
|  3 | BBB  |       3 |
|  4 | BBB  |       4 |
|  5 | CCC  |       5 |
+----+------+---------+
3 rows in set (0.01 sec)

Expected behavior

mysql> select *
    ->   from tt1
    ->  where not exists (select 1
    ->           from tt2
    ->          where tt1.id = tt2.id and name='BBB');
+----+------+---------+
| id | name | copy_id |
+----+------+---------+
|  3 | BBB  |       3 |
|  4 | BBB  |       4 |
|  5 | CCC  |       5 |
+----+------+---------+
3 rows in set (0.00 sec)

How To Reproduce

No response

Environment

./mysqld Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
Branch name: stonedb-5.7-dev
Last commit ID: c91a29e
Last commit time: Date: Mon Oct 24 03:25:06 2022 +0000
Build time: Date: Mon 24 Oct 2022 11:09:00 AM UTC

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[adofsauron]

the same as #971