Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2004

The TableFunction left outer join was improperly translated

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.20.0
    • Component/s: None

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                xccui Xingcan Cui
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 10m
                  1h 10m