In the filter criteria associated with multiple tables, the filter result of the subquery by not in or in is incorrect.

create testtable as follow:

create table test101 (id string,id2 string);
create table test102 (id string,id2 string);
create table test103 (id string,id2 string);
create table test104 (id string,id2 string);

when cbo is false，run the following SQL statement:

explain select count(1) from test101 t1 
left join test102 t2 on t1.id=t2.id
left join test103 t3 on t1.id=t3.id2
where t1.id in (select s.id from test104 s)
and t3.id2='123';

you will see：

The filter criteria in the right table are lost.

The execution plan is as follows:

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------