[
https://issues.apache.org/jira/browse/CALCITE-4210?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17193177#comment-17193177
]
James Starr commented on CALCITE-4210:
--------------------------------------
This is the same problem as CALCITE-4100.
I am going to raise an error for sub selects in ON clauses because they will
not generate the correct rel nodes and it is preferable to have an exception
than incorrect results.
Supporting Inner joins correctly would not be particularly difficult. Looking
something like this:
{code:java}
...
convertFrom(rightBlackboard, right);
RelNode rightRel = rightBlackboard.root;
bb.register(rightRel, JoinRelType.INNER);
convertWhere(bb,join.getCondition());{code}
However, support left and outer joins would take a bit more of a rework.
A naive approach for left joins too would be to add a project, instead of a
filter to the top that simply nulls out columns when the condition is not
meant. This probably would not play well with most optimization rules down
stream.
Alternatively, the left rel node could be built up to be a joined composition
of the left root node and all subquery nodes.
> Sub Query is 'On' clause does not correctly expand in SqlToRelConverter
> -----------------------------------------------------------------------
>
> Key: CALCITE-4210
> URL: https://issues.apache.org/jira/browse/CALCITE-4210
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: James Starr
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Aggregation is dropped during decorreleation.
> {code:java}
> SELECT "employee"."department_id", "employee"."salary"
> FROM "department"
> LEFT JOIN "employee" ON "employee"."salary" = (
> SELECT max("employee"."salary")
> FROM "employee"
> WHERE "employee"."department_id" = "department"."department_id"
> )
> {code}
> {code:java}
> LogicalProject(department_id=[$9], salary=[$13])
> LogicalJoin(condition=[=($13, $0)], joinType=[left])
> LogicalTableScan(table=[[foodmart, department]])
> LogicalTableScan(table=[[foodmart, employee]]){code}
> This may be related to CALCITE-4206.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
