The filter of outer table happens multiple time after optimizing in-subquery to join

[ygf11]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.

Create table:

❯ create table t1(a int, b int, c int) as values(1,1,1);
❯ create table t2(a int, b int, c int) as values(1,1,1);

run query:

❯ explain verbose select * from t1 where t1.a in (select t2.a from t2) 
                                   and t1.b in(select t2.b from t2) 
                                   and t1.c > 10;

The output plan of decorrelate_where_in:

| logical_plan after decorrelate_where_in                    | Projection: t1.a, t1.b, t1.c                                                                                                          |
|                                                            |   Filter: t1.c > Int32(10) -- filter                                                                                                           |
|                                                            |     LeftSemi Join: t1.b = __correlated_sq_2.b                                                                                         |
|                                                            |       Filter: t1.c > Int32(10) -- filter                                                                                                         |
|                                                            |         LeftSemi Join: t1.a = __correlated_sq_1.a                                                                                     |
|                                                            |           TableScan: t1                                                                                                               |
|                                                            |           SubqueryAlias: __correlated_sq_1                                                                                            |
|                                                            |             Projection: t2.a AS a                                                                                                     |
|                                                            |               TableScan: t2                                                                                                           |
|                                                            |       SubqueryAlias: __correlated_sq_2                                                                                                |
|                                                            |         Projection: t2.b AS b                                                                                                         |
|                                                            |           TableScan: t2  

We can see Filter: t1.c > Int32(10) happen twice.

Describe the solution you'd like
I would like to add the Filter only once.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

[ygf11]

The DecorrelateWhereExists also has same problem.

❯ explain verbose
select *
from t1
where exists
(
    select 1 from t2 where t1.a = t2.a
)
      and exists
(
    select 1 from t2 where t1.b = t2.b
)
      and t1.c > 0;

| logical_plan after decorrelate_where_exists                | Projection: t1.a, t1.b, t1.c                                                                                                          |
|                                                            |   Filter: t1.c > Int32(0) --- filter                                                                                                            |
|                                                            |     LeftSemi Join: t1.b = t2.b                                                                                                        |
|                                                            |       Filter: t1.c > Int32(0) ---filter                                                                                                        |
|                                                            |         LeftSemi Join: t1.a = t2.a                                                                                                    |
|                                                            |           TableScan: t1                                                                                                               |
|                                                            |           TableScan: t2                                                                                                               |
|                                                            |       TableScan: t2  

[alippai]

Filtering before the join can lead to significant speedups when the selectivity is high enough. Is this something to consider? Or will we see it in the TPCH benchmarks? never mind, I see this is mainly about push downs, sorry for the noise :)