[jira] [Comment Edited] (CALCITE-4210) Sub Query is 'On' clause does not correctly expand in SqlToRelConverter

["James Starr (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4210?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17193177#comment-17193177 ] 

James Starr edited comment on CALCITE-4210 at 9/10/20, 2:11 AM:
----------------------------------------------------------------

This is the same problem as CALCITE-4100.

I have reworked to replay subqueries on the right side of the join then generate the join on top of that new right node.


was (Author: jamesstarr):
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)