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)
      

        Activity

        Hide
        julianhyde Julian Hyde added a comment -

        Closing now that 1.0.0-incubating has been released.

        Show
        julianhyde Julian Hyde added a comment - Closing now that 1.0.0-incubating has been released.
        Hide
        julianhyde Julian Hyde added a comment -

        Fixed issue with spurious rows from FULL JOIN in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/89d42b50.

        Show
        julianhyde Julian Hyde added a comment - Fixed issue with spurious rows from FULL JOIN in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/89d42b50 .
        Hide
        julianhyde Julian Hyde added a comment -

        Yeah, you're right. I'm looking into it.

        Show
        julianhyde Julian Hyde added a comment - Yeah, you're right. I'm looking into it.
        Hide
        vladimirsitnikov Vladimir Sitnikov added a comment -

        Here's what Oracle gives for null=null join:

        with t1(x, y) as (select 1, 10 from dual union all select 2, 20 from dual union all select cast(null as integer), 30 from dual),
          t2(x, y) as (select 1, 100 from dual union all select cast(null as integer), 200 from dual)
          select * from t1 left join t2 on t1.x = t2.x;
        
        --	X,	Y,	X,	Y
        	1	10	1	100
        	[NULL]	30	[NULL]	[NULL]
        	2	20	[NULL]	[NULL]
        

        In your outer.oq you return non-null Y0 for null=null case somehow:

        ++---+----+----+-----+
        +| X | Y  | X0 | Y0  |
        ++---+----+----+-----+
        +| 1 | 10 |  1 | 100 |
        +| 2 | 20 |    |     |
        +|   | 30 |    | 200 | <-- How's that?!
        ++---+----+----+-----+
        

        Just in case,

        • Oracle full join:
          	X,	Y,	X,	Y
          	1	10	1	100
          	2	20	[NULL]	[NULL]
          	[NULL]	30	[NULL]	[NULL]
          	[NULL]	[NULL]	[NULL]	200
        • Oracle right join:
          	X,	Y,	X,	Y
          	1	10	1	100
          	[NULL]	[NULL]	[NULL]	200
        Show
        vladimirsitnikov Vladimir Sitnikov added a comment - Here's what Oracle gives for null=null join: with t1(x, y) as ( select 1, 10 from dual union all select 2, 20 from dual union all select cast(null as integer), 30 from dual), t2(x, y) as ( select 1, 100 from dual union all select cast(null as integer), 200 from dual) select * from t1 left join t2 on t1.x = t2.x; -- X, Y, X, Y 1 10 1 100 [NULL] 30 [NULL] [NULL] 2 20 [NULL] [NULL] In your outer.oq you return non-null Y0 for null=null case somehow: ++---+----+----+-----+ +| X | Y | X0 | Y0 | ++---+----+----+-----+ +| 1 | 10 | 1 | 100 | +| 2 | 20 | | | +| | 30 | | 200 | <-- How's that?! ++---+----+----+-----+ Just in case, Oracle full join: X, Y, X, Y 1 10 1 100 2 20 [NULL] [NULL] [NULL] 30 [NULL] [NULL] [NULL] [NULL] [NULL] 200 Oracle right join: X, Y, X, Y 1 10 1 100 [NULL] [NULL] [NULL] 200
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/1edcba06 .

          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:

              Development