Better planning for subquery IN predicates that can be pre-evaluated

With a query such as:

with test as  /*+ materialize */ (select e1 from pm2.g1), test1 as  /*+ materialize */ (select e3 from pm2.g3) select bet.e1 from (pm1.g1 AS bet LEFT OUTER JOIN pm1.g2 AS bre ON bet.e1 = bre.e1) LEFT OUTER JOIN pm1.g3 AS zak ON bet.e2 = zak.e2 where zak.e1 in (select e1 from test) and zak.e3 in (select e3 from test1)

When subquery unnest is off by default, only a single dependent join is created. This is due to the late optimization logic converting the subquery into a join that relies on inserting a sort node that is almost certainly not needed here because this can be processed as semi-dependent join. While that could be improved it would be better still to understand that the whole IN predicate could be inlined. The best possible scenario would not require the use of materialized common tables, but rather both push and keep select node above the access node. In the event that the pushed one could not be applied, the one that was not pushed would be. This is a variation on the pre-evaluation logic for exists/scalar subqueries in that they are not conditional.