[jira] [Closed] (CALCITE-554) Outer join over NULL keys generates wrong result

["Julian Hyde (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-554?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde closed CALCITE-554.
-------------------------------

Closing now that 1.0.0-incubating has been released.

> Outer join over NULL keys generates wrong result
> ------------------------------------------------
>
>                 Key: CALCITE-554
>                 URL: https://issues.apache.org/jira/browse/CALCITE-554
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Jinfeng Ni
>            Assignee: Julian Hyde
>             Fix For: 1.0.0-incubating
>
>
> Consider the following query, where both LHS and RHS contains NULL in the join keys:
> {code}
>  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
> {code}
> Calcite would return 2 rows, while the correct result seems to be 3 rows (including the row of null in T1).
> {code}
>  @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>
> {code}
> Here is the result for the same query, if run on Postgres9.3:
> {code}
> 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)
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)