bug: If using semi join,maybe wrong result.

[haitaoguan]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

select B.e_id, count(A.id)
  from A
 inner join B
    ON B.user_id = A.user_id
 where B.code = 'c8de2be2f1ef4dcb8bd5539aee'
   and A.code = 'c8de2be2f1ef4dcb8bd5539aee'
   and A.user_id in
       (select id
          from C
         where C.code = 'c8de2be2f1ef4dcb8bd5539aee'
           and C.d_flag = '0'
           and C.l_flag = '0')
 group by B.e_id;
+----------------------------------+-----+
| c_0                              | c_3 |
+----------------------------------+-----+
| 3746cc9efe794214813a8295291b4c21 |   0 |
| 6298aa9a086c47f7b7d592b35abb3beb |   0 |
| 3ff056ba8b0a4bddb97aee151b54f52e |   0 |
| 4cb302194ce349fca29a52df74ade390 |   0 |
| 628a93cf59cb474693747f2e4a68c6c6 |   0 |
......

set optimizer_switch='semijoin=off';
+----------------------------------+-------+
| c_0                              | c_3   |
+----------------------------------+-------+
| 3746cc9efe794214813a8295291b4c21 |  8559 |
| 6298aa9a086c47f7b7d592b35abb3beb |  7797 |
| 3ff056ba8b0a4bddb97aee151b54f52e |   457 |
| 4cb302194ce349fca29a52df74ade390 |  4358 |
| 628a93cf59cb474693747f2e4a68c6c6 |  5709 |
......

Expected behavior

No response

How To Reproduce

No response

Environment

./mysqld Ver 5.7.36-StoneDB-v1.0.3 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: 758bce8 Last commit time: Date: Mon Apr 10 19:46:20 2023 +0800 Build time: Date: Tue Apr 11 11:34:37 CST 2023

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

• [X] Yes, I will!