[CALCITE-2004] The TableFunction left outer join was improperly translated

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.20.0
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Xingcan Cui
• Created: 09/Oct/17 03:05
• Updated: 24/Jun/19 15:02
• Resolved: 02/Apr/19 15:41

Description

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.

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;])

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.

Issue Links

• Blocked: FLINK-7865 Remove predicate restrictions on TableFunction left outer join
• blocks: CALCITE-2893 Type mismatch assertion error when creating join condition
• is blocked by: CALCITE-2903 Exception thrown when decorrelating query with TEMPORAL TABLE
• links to: GitHub Pull Request #1096