[CALCITE-5201] Improve SemiJoinRule to match Join's right input which is unique for Join keys

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.30.0
• Fix Version/s: 1.32.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Benchao Li
• Reporter: Benchao Li
• Created: 27/Jun/22 05:04
• Updated: 19/Nov/22 09:09
• Resolved: 14/Aug/22 02:58

Description

As disscussed in CALCITE-5195, the following SQL cannot be transformed to semi join:

select * 
from emp
where exists(select * from dept where emp.deptno = dept.deptno and emp.empno = dept.deptno)

Currently SemiJoinRule requires that right child must be an Aggregate, however, sometimes it's not, e.g. in RelBuilder, if the group keys are unique, then we'll remove the Aggregate.

Issue Links

• is related to: CALCITE-330 Add a rule that converts join-on-aggregate to semi-join
• relates to: CALCITE-5229 JdbcTest#testDynamicParameterInLimitOffset throws IllegalArgumentException
• relates to: CALCITE-5195 ArrayIndexOutOfBoundsException when inferring more equal conditions from join condition for semi join
• relates to: CALCITE-5391 JoinOnUniqueToSemiJoinRule should preserve field names, if possible
• links to: GitHub Pull Request #2848