Question on `in or not in` subquery plan

[baofeng.zbf]

Hi,

I got a question on `in (non-scalar-subquery1) or not in 
(non-scalar-subquery2)` pattern, 

for SQL:
```
select count(*) from db.foo where (int_test in (select int_test from db.foo 
where id<=20 )) or  (long_test  not in (select long_test from db.foo where id 
<=1));
```

plan generated:
```
LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
  LogicalProject($f0=[0])
    LogicalFilter(condition=[OR(true, CAST(NOT(CASE(=($10, 0), false, IS NOT 
NULL($14), true, IS NULL($12), null, <($11, $10), null, false))):BOOLEAN)])
      LogicalJoin(condition=[=($12, $13)], joinType=[left])
        LogicalProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], 
$f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$9], $f10=[$10], $f11=[$11], 
$f12=[$7])
          LogicalJoin(condition=[true], joinType=[inner])
            LogicalJoin(condition=[=($1, $9)], joinType=[inner])
              LogicalTableScan(table=[[TEST4DMP, test]])
              LogicalAggregate(group=[{0}])
                LogicalProject(int_test=[$1])
                  LogicalFilter(condition=[<=($0, 20)])
                    LogicalTableScan(table=[[db, foo]])
            LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
              LogicalProject($f0=[$0], $f1=[true])
                LogicalProject(long_test=[$7])
                  LogicalFilter(condition=[<=($0, 1)])
                    LogicalTableScan(table=[[db, foo]])
        LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
          LogicalProject($f0=[$0], $f1=[true])
            LogicalProject(long_test=[$7])
              LogicalFilter(condition=[<=($0, 1)])
                LogicalTableScan(table=[[db, foo]])
```

I don’t think the `LogicalFilter` condition is right, and it will be folded to 
be ALWAYS TRUE.

Any one can help me?

Thx,
Baofeng Zhang

smime.p7s
Description: S/MIME cryptographic signature