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

["Xingcan Cui (JIRA)" <ji...@apache.org>]

Xingcan Cui created CALCITE-2004:
------------------------------------

             Summary: 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
            Assignee: Julian Hyde


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
(v6.4.14#64029)