[
https://issues.apache.org/jira/browse/CALCITE-4812?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17422988#comment-17422988
]
James Starr commented on CALCITE-4812:
--------------------------------------
Their are none currently. Lateral Joins are explicitly rewritten to correlates
in SqlToRel. Lateral Joins also only generate 1 correlate id even if multiple
references occurs. Correlated queries in on clauses are not handled correctly,
and the correlate id is not populated in the join. For correlated filters,
SqlToRel also only emits a single id for multiple values. Correlate, also only
takes a single id. So sets of correlate ids used in projects and joins is
currently a bit of false api because in practice it either a set of 1 or an
empty set.
{code:sql}
SELECT * FROM emp as e1
JOIN LATERAL(
SELECT * FROM emp e2
WHERE e1.empno = e2.empno AND e2.empno = e1.empno) as e3
ON e1.empno = e3.empno
{code}
Generates:
{noformat}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalFilter(condition=[=($cor0.EMPNO, $0)])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[AND(=($cor0.EMPNO, $0), =($0, $cor0.EMPNO))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{noformat}
> RelBuilder.join should error multiple correlate ids
> ---------------------------------------------------
>
> Key: CALCITE-4812
> URL: https://issues.apache.org/jira/browse/CALCITE-4812
> Project: Calcite
> Issue Type: Improvement
> Reporter: James Starr
> Assignee: James Starr
> Priority: Major
>
> Relbuilder.join does not correctly handle multiple correlate ids. An error
> should be thrown when multiple ids are passed in.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
