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,  ,  
      

        Attachments

          Activity

            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: