Enable dynamic filters for nested joins

[nuno-faria]

Is your feature request related to a problem or challenge?

I would like to enable dynamic filter pushdown for inner joins which use other joins as subqueries. For example:

select *
from (
    select *
    from t1
    left anti join t2 on t1.k = t2.k
) a
join t1 b on a.k = b.k
where b.v = 1;

Even though a dynamic filter is created (DynamicFilter [ k@0 >= 1 AND k@0 <= 1 AND k@0 IN (SET) ([1]) ]), it is not pushed to subquery a.

Describe the solution you'd like

I think the issue is that gather_filters_for_pushdown is used to both build dynamic filters and determine if they can be applied. Currently, the dynamic filters are dropped for all join types apart from inner ones, which explains why it is not pushed to the subquery above which uses a left anti join. I tried locally and this can be solved by making gather_filters_for_pushdown not disallowing filters, but still preventing them from being generated from non-inner joins which is known to cause issues (see #16973).

I think the only potential issue might be when we use left/right joins with duplicate column names, which could cause the filter to be pushed to the wrong table. But I found out that this also happens right now with inner joins, so I opened #20213 to track it. Anti joins are, however, completely safe in any case.

@adriangb what do you think? Does this make sense, or am I missing some edge case?

Describe alternatives you've considered

No response

Additional context

No response