[CALCITE-254] Propagate RelCollation on aliased columns in JoinRule

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.1.0-incubating
• Labels: github-import
• Assignee: Unassigned
• Reporter: GitHub Import
• Created: 18/Jun/14 08:51
• Updated: 23/Mar/15 08:08
• Resolved: 08/Feb/15 17:33

Description

Consider this query:

SELECT *
FROM emp JOIN dept ON emp.deptno = dept.deptno
ORDER BY dept.deptno;

If table emp is sorted on emp.deptno (for example, table emp has index on emp.deptno, or table emp comes from a subquery which sorts on emp.deptno), the join predicate emp.deptno = dept.deptno indicates the two columns are aliased columns in terms of ordering. If JoinRule keep track of the RelCollation trait for the aliased columns, the query essentially become as following

SELECT *
FROM emp JOIN dept ON emp.deptno = dept.deptno
ORDER BY emp.deptno;

The ORDER BY should not require a SortRel, since the input table emp is sorted on emp.depno.

Currently, optiq framework does not recognize the aliased columns from join = predicates, and may introduce redundant sort to the plan.

This issue is relevant to issue (#41 | CALCITE-41)
https://github.com/julianhyde/optiq/issues/41

---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/254
Created by: jinfengni
Labels:
Created at: Thu Apr 17 22:53:46 CEST 2014
State: open

Issue Links

• is related to: CALCITE-526 Add EnumerableMergeJoin, which exploits sorted inputs