Uploaded image for project: 'Tajo'
  1. Tajo
  2. TAJO-428

CASE WHEN IS NULL condition is a problem using LEFT OUTER JOIN

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 0.8.0
    • Fix Version/s: 0.8.0
    • Component/s: Physical Operator
    • Labels:
      None

      Description

      I found a error when I used LEFT OUTER JOIN.
      I created table for a start as follows:

      table1 schema
      create external table table1 (id int, name text, score float, type text) using csv with ('csvfile.delimiter'='|', 'csvfile.null'='NULL') location 'hdfs://localhost:9010/tajo/warehouse/table1' ;
      
      table1 data
      1|ooo|1.1|a
      2|ppp|2.3|b
      3|qqq|3.4|c
      4|rrr|4.5|d
      5|xxx|5.6|e
      
      table3 schema
      create external table table3 (id int, name text, score float, type text) using csv with ('csvfile.delimiter'='|', 'csvfile.null'='NULL') location 'hdfs://localhost:9010/tajo/warehouse/table3' ;
      
      table3 data
      1|NULL||a
      2|NULL||b
      NULL|NULL|10.0|c
      NULL|NULL|20.0|d
      

      And I executed query as follows:

      select a.id, a.name, b.id as id2, b.name as name2, case when b.name is null then '9991231' else b.name end from table1 a left outer join table3 b on a.id = b.id
      

      In this case, I expected the result as follows:

      id,  name,  id2,  name2,  ??
      -------------------------------
      1,  ooo,  1,  ,  9991231
      2,  ppp,  2,  ,  9991231
      3,  qqq,  null,  ,  99991231
      4,  rrr,  null,  ,  99991231
      5,  xxx,  null,  ,  99991231
      

      But, Tajo made the result as follows:

      id,  name,  id2,  name2,  ??
      -------------------------------
      1,  ooo,  1,  ,  9991231
      2,  ppp,  2,  ,  9991231
      3,  qqq,  null,  ,  
      4,  rrr,  null,  ,  
      5,  xxx,  null,  ,  
      
      1. TAJO-428.patch
        10 kB
        Jaehwa Jung

        Activity

        Hide
        hyunsik Hyunsik Choi added a comment -

        Fixed by TAJO-501.

        Show
        hyunsik Hyunsik Choi added a comment - Fixed by TAJO-501 .
        Hide
        blrunner Jaehwa Jung added a comment -

        Thanks Hyunsik.

        Show
        blrunner Jaehwa Jung added a comment - Thanks Hyunsik.
        Hide
        hyunsik Hyunsik Choi added a comment -

        Ok, I'll fix this bug.

        Show
        hyunsik Hyunsik Choi added a comment - Ok, I'll fix this bug.
        Hide
        blrunner Jaehwa Jung added a comment - - edited

        I found a bug at my patch. It couldn't correct result when I used empty table at left outer join clause. At this point, it seems to be basic solution which operate scan after operate join.
        Hyunsik Choi, Could you fix it instead of me?

        Show
        blrunner Jaehwa Jung added a comment - - edited I found a bug at my patch. It couldn't correct result when I used empty table at left outer join clause. At this point, it seems to be basic solution which operate scan after operate join. Hyunsik Choi , Could you fix it instead of me?
        Hide
        blrunner Jaehwa Jung added a comment -

        I found a case of this bug. If outer join targets is null, HashLeftOuterJoinExec initiate target columns to null values. So, I backup right tuple before it initiate, I used it to make last tuple.

        Show
        blrunner Jaehwa Jung added a comment - I found a case of this bug. If outer join targets is null, HashLeftOuterJoinExec initiate target columns to null values. So, I backup right tuple before it initiate, I used it to make last tuple.

          People

          • Assignee:
            hyunsik Hyunsik Choi
            Reporter:
            blrunner Jaehwa Jung
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development