[
https://issues.apache.org/jira/browse/CALCITE-6629?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17890539#comment-17890539
]
Julian Hyde commented on CALCITE-6629:
--------------------------------------
I believe that the semantics can be captured in a rewrite rule. As we know,
outer join is equivalent to inner join plus union:
{code}
select * from t left join u on t.a = u.a
{code}
is equivalent to
{code}
select t.*, u.* from t join u on t.a = u.a
union
select t.*, <nulls> from t where not exists
(select * from u where u.a = t.a)
{code}
Partitioned outer join has a similar rewrite:
{code}
select * from t left join u partition by (u.b) on t.a = u.a
{code}
is equivalent to
{code}
select t.*, u.* from t join u on t.a = u.a
union
select t.*, v.b, <nulls> from t
cross join (select distinct b from u) as v
where not exists
(select * from u where u.a = t.a and u.b = v.b)
{code}
(It's possible that "select distinct b from u" needs a "where" clause.)
> 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)
