[CALCITE-554] Outer join over NULL keys generates wrong result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.0.0-incubating
• Assignee: Julian Hyde
• Reporter: Jinfeng Ni
• Created: 08/Jan/15 00:24
• Updated: 08/Feb/15 08:56
• Resolved: 11/Jan/15 04:11

Description

Consider the following query, where both LHS and RHS contains NULL in the join keys:

 with 
 t1(x) as (select * from  (values (1),(2), (case when 1 = 1 then null else 3 end)) as t(x)),
 t2(x) as (select * from  (values (1),(case when 1 = 1 then null else 3 end)) as t(x))
 select t1.x from t1 left join t2 on t1.x = t2.x

Calcite would return 2 rows, while the correct result seems to be 3 rows (including the row of null in T1).

 @Test public void testOutJoinNull() {
    String sql = " with \n"
        + " t1(x) as (select * from  (values (1),(2), (case when 1 = 1 then null else 3 end)) as t(x)),\n"
        + " t2(x) as (select * from  (values (1),(case when 1 = 1 then null else 3 end)) as t(x))\n"
        + " select t1.x from t1 left join t2 on t1.x = t2.x";
    CalciteAssert.that().query(sql).returnsCount(3);
  }

Caused by: java.lang.AssertionError: expected:<3> but was:<2>

Here is the result for the same query, if run on Postgres9.3:

mydb=# with
mydb-#  t1(x) as (select * from  (values (1),(2), (case when 1 = 1 then null else 3 end)) as t(x)),
mydb-#  t2(x) as (select * from  (values (1),(case when 1 = 1 then null else 3 end)) as t(x))
mydb-#  select t1.x from t1 left join t2 on t1.x = t2.x
mydb-# ;
 x
---
 1
 2

(3 rows)