Date: 2015-06-26 09:18:20 +0200
From: John Thomas <>
To: SQL devs <>
Version: 11.19.15 (Oct2014-SP4)
CC: @njnes

Last updated: 2015-08-28 13:43:02 +0200

Comment 20943

Date: 2015-06-26 09:18:20 +0200
From: John Thomas <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.124 Safari/537.36
Build Identifier:

When joining on a column that contains nulls, the resulting output is incorrect.

Reproducible: Always

Steps to Reproduce:

1. Very basic test case setup:

create table foo (a string, b int);
create table bar (a string, b int);
insert into foo values ('hi',0),('there',null),('monet',1);
insert into bar values ('sup',0),('dude',1);

2. Perform join on column with null present.
3. Observe failure.

Actual Results:

select foo.a,foo.b,bar.b,bar.a from foo join bar on foo.b=bar.b;
+-------+------+------+------+
| a | b | b | a |
+=======+======+======+======+
| hi | 0 | 0 | sup |
| there | null | 1 | dude |
+-------+------+------+------+

Expected Results:

+-------+------+------+------+
| a | b | b | a |
+=======+======+======+======+
| hi | 0 | 0 | sup |
| monet | 1 | 1 | dude |
+-------+------+------+------+

From what I've observed of larger test cases, the result seems to have the correct number of rows, with the correct values on the right and incorrect values on the left.

This bug is present in all releases from Jan2014 (11.17.9) through Oct2014-SP4 (11.19.15). The Feb2013 release behaves correctly.

Interestingly, if you add "where foo.b is not null" to the test query above, the optimizer performs that filter prior to the join and produces the expected result. This suggests that null values aren't being handled correctly in the join function itself.

Comment 20944

Date: 2015-06-26 20:23:32 +0200
From: @njnes

The mergejoin code didn't handle the nil properly (fixed in Jul2015).

Comment 21228

Date: 2015-08-28 13:43:02 +0200
From: @sjoerdmullender

Jul2015 has been released.