[
https://issues.apache.org/jira/browse/CALCITE-6629?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6629:
---------------------------------
Summary: Partitioned outer join (LEFT JOIN ... PARTITION BY and PARTITION
BY ... RIGHT JOIN) (was: Partitioned outer join (LEFT/RIGHT JOIN ... PARTITION
BY))
> Partitioned outer join (LEFT JOIN ... PARTITION BY and PARTITION BY ... RIGHT
> JOIN)
> -----------------------------------------------------------------------------------
>
> Key: CALCITE-6629
> URL: https://issues.apache.org/jira/browse/CALCITE-6629
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> Partitioned outer join is a feature, generalizing outer join, that generates
> missing rows so that there is at least one row for each value of a
> paritioning column. For example,
> {noformat}
> select d.dname, d.deptno, e.job, e.ename
> from dept d
> left join emp e
> partition by (e.job)
> on e.deptno = d.deptno
> order by 2;
> DNAME DEPTNO JOB ENAME
> ========== ====== ========= ======
> ACCOUNTING 10 PRESIDENT KING
> ACCOUNTING 10 CLERK MILLER
> ACCOUNTING 10 MANAGER CLARK
> ACCOUNTING 10 SALESMAN -
> ACCOUNTING 10 ANALYST -
> RESEARCH 20 MANAGER JONES
> RESEARCH 20 CLERK SMITH
> RESEARCH 20 CLERK ADAMS
> RESEARCH 20 SALESMAN -
> RESEARCH 20 ANALYST FORD
> RESEARCH 20 ANALYST SCOTT
> RESEARCH 20 PRESIDENT -
> SALES 30 CLERK JAMES
> SALES 30 ANALYST -
> SALES 30 MANAGER BLAKE
> SALES 30 SALESMAN MARTIN
> SALES 30 SALESMAN ALLEN
> SALES 30 SALESMAN WARD
> SALES 30 SALESMAN TURNER
> SALES 30 PRESIDENT -
> OPERATIONS 40 CLERK -
> OPERATIONS 40 PRESIDENT -
> OPERATIONS 40 ANALYST -
> OPERATIONS 40 SALESMAN -
> OPERATIONS 40 MANAGER - {noformat}
> Note that there are 5 additional rows for department 40 (one for each job)
> but also additional rows for the other departments.
> The following query reverses the tables and uses a right join. It returns
> exactly the same results as the previous query. Note that "{{partition by}}"
> occurs before "{{right join}}".
> {noformat}
> select d.dname, d.deptno, e.job, e.ename
> from emp e
> partition by (e.job)
> right join dept d
> on e.deptno = d.deptno
> order by 2;
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
