Minor Description
SemiJoinRule looks for an INNER or LEFT OUTER Join with its right child as an Aggregate. It also tries to match a Project as the parent of the Join so that it can abort if any columns from the right child of the Join is referenced. There is another situation though, where the right child is an empty Aggregate that returns 0 columns and thus we don't need to look for a Project. So an alternative match pattern for SemiJoinRule would be just a Join over an Aggregate without a Project. For example, the following query can match the alternative pattern and fire the rule:
select e."deptno" from "hr"."emps" as e left join (select count(*) from "hr"."depts") on true
, where after field trimming the inner query will become an empty Aggregate.