Consider the following query correlated query.
The query basically returns an empty result because e.empno = null is always false.
The plan for the query after applying the sub-query remove rule is shown below:
After applying the reduce expressions rule the filter with the correlated condition will become false and the resulting plan would be the following.
Observe that now we have a LogicalCorrelate but there is no real correlation in the plan since the correlation variable on the right side disappeared. Depending on how rules are applied and which rules are used similar "trivial" correlates may appear.
The goal of this ticket is to provide the means to get rid of them.
One option would be to add a new rule (e.g., CorrelateToJoinRule) which detects that a correlate does not have correlations in the right side and turn the correlation to a join; then we could employ other existing rules (such as PruneEmptyRules) for joins and remove the newly created join altogether.
Another option, would be to introduce new pruning rule(s) for correlate (similar to those for joins) that will remove the correlate when its input is an empty values expression.