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

Wrong plan for IS NULL in join query

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Cannot Reproduce
    • None
    • 1.24.0
    • None
    • None

    Description

      sql: 

       SELECT count(*) FROM T6
       INNER JOIN T5 
       ON T6.NUM = T5.NUM
       OR ((T6.NUM IS NULL) AND (T5.NUM IS NULL));

      However the plan is 

      AggregateRel(group=[{0}], ctx=[])
        ProjectRel(NUM1=[$2], ctx=[])
          JoinRel(condition=[=($1, $5)], joinType=[inner], ctx=[])
            TableScan(table=[[SAMPLE, T6]], ctx=[], fields=[[0, 1, 2, 3]])
            TableScan(table=[[SAMPLE, T5]], ctx=[], fields=[[0, 1, 2, 3]])
      

      It seem the filter IS NULL  is optimized , and the plan is wrong.

      Attachments

        Activity

          People

            Unassigned Unassigned
            Zhixiong Chen Zhixiong Chen
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: