[TAJO-428] CASE WHEN IS NULL condition is a problem using LEFT OUTER JOIN

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.8.0
• Fix Version/s: 0.8.0
• Component/s: Physical Operator
• Assignee: Hyunsik Choi
• Reporter: JaeHwa Jung
• Created: 18/Dec/13 09:35
• Updated: 20/Jan/14 01:51
• Resolved: 20/Jan/14 01:51

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