[SPARK-23756] [Performance] Redundant join elimination

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Ioana Delaney
• Created: 20/Mar/18 19:25
• Updated: 29/Mar/20 02:37

Description

This rewrite eliminates self-joins on unique keys. Self-joins may be introduced after view expansion.

User view:

create view manager(mgrno, income) as 
select e.empno, e.salary + e.bonus
from employee e, department d
where e.empno = d.mgrno;

User query:

select e.empname, e.empno
from employee e, manager m
where e.empno = m.mgrno and m.income > 100K

Internal query after view expansion:

select e.lastname, e.empno
from employee e, employee m, department d
where e.empno = m.empno /* PK = PK */ and e.empno = d.mgrno and 
m.salary + m.bonus > 100K

Internal query after join elimination:

select e.lastname, e.empno
from employee e, department d
where e.empno = d.mgrno and 
e.salary + e.bonus > 100K