bug:If the field you used in the join condition is primary key, using "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 exists (select 1
    ->           from tt2
    ->          where tt1.id = tt2.id and name='BBB');
+----+------+---------+
| id | name | copy_id |
+----+------+---------+
|  1 | AAA  |       1 |
|  2 | AAA  |       2 |
|  3 | BBB  |       3 |
|  4 | BBB  |       4 |
|  5 | CCC  |       5 |
+----+------+---------+
5 rows in set (0.01 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 exists (select 1
    ->           from tt2
    ->          where tt1.id = tt2.id and name='BBB');
+----+------+---------+
| id | name | copy_id |
+----+------+---------+
|  1 | AAA  |       1 |
|  2 | AAA  |       2 |
+----+------+---------+
2 rows in set (0.00 sec)

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

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

Expected behavior

+----+------+---------+
| id | name | copy_id |
+----+------+---------+
|  1 | AAA  |       1 |
|  2 | AAA  |       2 |
+----+------+---------+

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]

ACK

[adofsauron]

Code stack using primary keys:

(gdb) bt
#0  Tianmu::core::Descriptor::IsleftIndexSearch (this=0x7f8784020c90) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/descriptor.cpp:1922
#1  0x0000000002fa7178 in Tianmu::core::Descriptor::EvaluateOnIndex (this=0x7f8784020c90, mit=..., limit=1)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/descriptor.cpp:1930
#2  0x000000000301c7b7 in Tianmu::core::ParameterizedFilter::ApplyDescriptor (this=0x7f878490c1f0, desc_number=0, limit=1)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/parameterized_filter.cpp:1384
#3  0x000000000301b2f0 in Tianmu::core::ParameterizedFilter::UpdateMultiIndex (this=0x7f878490c1f0, count_only=false, limit=1)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/parameterized_filter.cpp:1167
#4  0x0000000002cde719 in Tianmu::core::TempTable::ProcessParameters (this=0x7f878490c0a0, mit=..., alias=-2)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/temp_table.cpp:1753
#5  0x0000000002dfa08a in Tianmu::vcolumn::SubSelectColumn::PrepareSubqResult (this=0x7f8784913b60, mit=..., exists_only=true)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/vc/subselect_column.cpp:388
#6  0x0000000002dfa932 in Tianmu::vcolumn::SubSelectColumn::CheckExists (this=0x7f8784913b60, mit=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/vc/subselect_column.cpp:497
#7  0x0000000002fa079e in Tianmu::core::Descriptor::CheckCondition (this=0x7f8784913790, mit=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/descriptor.cpp:1079
#8  0x000000000308537a in Tianmu::core::JoinerGeneral::ExecuteInnerJoinLoopSingleThread (this=0x7f8784914860, mit=..., cond=..., new_mind=..., all_dims=..., 
    pack_desc_locked=std::vector<bool> of length 1, capacity 64 = {...}, tuples_in_output=@0x7f892e327498: 0, limit=-1, count_only=false)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/joiner_general.cpp:244
#9  0x0000000003084696 in Tianmu::core::JoinerGeneral::ExecuteJoinConditions (this=0x7f8784914860, cond=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/joiner_general.cpp:81
#10 0x0000000003018399 in Tianmu::core::ParameterizedFilter::UpdateJoinCondition (this=0x7f878490b9d0, cond=..., tips=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/parameterized_filter.cpp:651
#11 0x000000000301c1be in Tianmu::core::ParameterizedFilter::UpdateMultiIndex (this=0x7f878490b9d0, count_only=false, limit=-1)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/parameterized_filter.cpp:1304
#12 0x0000000002c99ae0 in Tianmu::core::Query::Preexecute (this=0x7f892e328810, qu=..., sender=0x7f8784913f40, display_now=true)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/query.cpp:795
#13 0x0000000002c6bbb3 in Tianmu::core::Engine::Execute (this=0x5c5f7a0, thd=0x7f8784003400, lex=0x7f8784005728, result_output=0x7f8784008b40, unit_for_union=0x0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/engine_execute.cpp:477
#14 0x0000000002c6aa54 in Tianmu::core::Engine::HandleSelect (this=0x5c5f7a0, thd=0x7f8784003400, lex=0x7f8784005728, result=@0x7f892e328dd8: 0x7f8784008b40, setup_tables_done_option=0, 
    res=@0x7f892e328dd4: 0, optimize_after_tianmu=@0x7f892e328dcc: 1, tianmu_free_join=@0x7f892e328dd0: 1, with_insert=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/engine_execute.cpp:238
#15 0x0000000002d6cd17 in Tianmu::handler::ha_my_tianmu_query (thd=0x7f8784003400, lex=0x7f8784005728, result_output=@0x7f892e328dd8: 0x7f8784008b40, setup_tables_done_option=0, res=@0x7f892e328dd4: 0, 
    optimize_after_tianmu=@0x7f892e328dcc: 1, tianmu_free_join=@0x7f892e328dd0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/handler/ha_my_tianmu.cpp:88
#16 0x00000000023af443 in execute_sqlcom_select (thd=0x7f8784003400, all_tables=0x7f8784008510) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:5184
#17 0x00000000023a87d9 in mysql_execute_command (thd=0x7f8784003400, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:2831
#18 0x00000000023b04a9 in mysql_parse (thd=0x7f8784003400, parser_state=0x7f892e329f90) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:5622
#19 0x00000000023a55b4 in dispatch_command (thd=0x7f8784003400, com_data=0x7f892e32a730, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:1495
#20 0x00000000023a43f5 in do_command (thd=0x7f8784003400) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:1034
#21 0x00000000024d5a89 in handle_connection (arg=0x903a400) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/conn_handler/connection_handler_per_thread.cc:313
#22 0x0000000002ba2dd2 in pfs_spawn_thread (arg=0x8edb170) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/perfschema/pfs.cc:2197
#23 0x00007f897e63e1ca in start_thread () from /lib64/libpthread.so.0
#24 0x00007f897b990e73 in clone () from /lib64/libc.so.6