[
https://issues.apache.org/jira/browse/CALCITE-6629?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6629:
---------------------------------
Description:
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}
was:
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.
> Partitioned outer join (LEFT/RIGHT JOIN ... PARTITION BY)
> ---------------------------------------------------------
>
> 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)
