Julian Hyde created CALCITE-6629:
------------------------------------
Summary: 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
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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
