[jira] [Commented] (CALCITE-2004) The TableFunction left outer join was improperly translated

["Haisheng Yuan (JIRA)" <ji...@apache.org>]

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

Haisheng Yuan commented on CALCITE-2004:
----------------------------------------

I opened a PR for this issue: https://github.com/apache/calcite/pull/1096
[~julianhyde] Can you take a look?

> The TableFunction left outer join was improperly translated
> -----------------------------------------------------------
>
>                 Key: CALCITE-2004
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2004
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Xingcan Cui
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> Currently the TableFunction left outer join is translated to a {{LogicalCorrelate}} followed by a {{LogicalFilter}}. For instance, given a simple table {{WordCount(word:String, frequency:Int)}}, a table function {{split: word:String => (letter:String, length:String)}}, and a query {{SELECT word, letter, length FROM WordCount LEFT JOIN LATERAL TABLE(split(word)) AS T (letter, length) ON frequency = length OR length < 5"}} the query will be translated to the logical plan below.
> {code:java}
> LogicalProject(word=[$0], name=[$2], length=[$3])
>   LogicalFilter(condition=[OR(=($1, CAST($3):BIGINT), <($3, 5))])
>     LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
>       LogicalTableScan(table=[[WordCount]])
>       LogicalTableFunctionScan(invocation=[split($cor0.word)], rowType=[RecordType(VARCHAR(65536) _1, INTEGER _2)], elementType=[class [Ljava.lang.Object;])
> {code}
> With this plan, an outer row might be completely filtered out (instead of being padded with null) if the join predicate filter filters out all join results produced by a correlate join for the outer row. Maybe we should attach the filter to the correlate operation.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)