Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-554

Outer join over NULL keys generates wrong result

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.0.0-incubating
    • Component/s: None
    • Labels:
      None

      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)
      

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              jni Jinfeng Ni
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: