[CALCITE-330] Add a rule that converts join-on-aggregate to semi-join

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 03/Jul/14 01:12
• Updated: 14/Aug/22 03:08
• Resolved: 14/Aug/22 03:08

Description

Once CALCITE-328 is completed and Join has a flag eliminateRightDuplicates, we need a rule to populate that flag from SQL queries.

Transformers that deal with 'IN subquery' (both correlated and uncorrelated) should set this flag. (In particular check TPC-DS Q95.)

Also queries written '... JOIN (SELECT DISTINCT ...)'.

Also handle queries whose right input is known to be already unique, e.g.

SELECT emp.name
FROM emp JOIN dept USING (deptno)
WHERE dept.location = 'San Francisco'

Issue Links

• duplicates: CALCITE-368 Add SemiJoinRule, planner rule to convert project-join-aggregate into semi-join
• relates to: CALCITE-1495 SemiJoinRule should not apply to RIGHT and FULL JOIN
• relates to: CALCITE-5201 Improve SemiJoinRule to match Join's right input which is unique for Join keys