[DRILL-1887] Wrong result for hash join on null valued columns

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.8.0
• Assignee: Aman Sinha
• Reporter: Rahul Kumar Challapalli
• Created: 18/Dec/14 16:01
• Updated: 23/Apr/15 20:46
• Resolved: 19/Dec/14 16:53

Description

git.commit.id.abbrev=9dfa4a1

Dataset1:

{
 "col1":1,
 "col2":"abc"
}
{
 "col1":2,
 "col2":null
}

Dataset 2:

{
 "col1":1,
 "col2":null
}
{
 "col1":2,
 "col2":null
}
{
 "col1":2,
 "col2":"abc"
}

Query :

select * from `a.json` a join `b.json` b on a.col2=b.col2; 
+------------+------------+------------+------------+
|    col1    |    col2    |   col10    |   col20    |
+------------+------------+------------+------------+
| 1          | abc        | 2          | abc        |
| 2          | null       | 1          | null       |
| 2          | null       | 2          | null       |
+------------+------------+------------+------------+

Most database engines treat 2 NULL values as not being equal. This makes it hard to compare DRILL's results against any Standard SQL engine. However I am not sure whether drill is intentionally designed to behave this way.