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

["Vladimir Sitnikov (JIRA)" <ji...@apache.org>]

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

Vladimir Sitnikov reopened CALCITE-554:
---------------------------------------

Here's what Oracle gives for null=null join:
{code:sql}
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;
{code}

{noformat}
--	X,	Y,	X,	Y
	1	10	1	100
	[NULL]	30	[NULL]	[NULL]
	2	20	[NULL]	[NULL]
{noformat}

In your {{outer.oq}} you return non-null Y0 for null=null case somehow:
{noformat}
++---+----+----+-----+
+| X | Y  | X0 | Y0  |
++---+----+----+-----+
+| 1 | 10 |  1 | 100 |
+| 2 | 20 |    |     |
+|   | 30 |    | 200 | <-- How's that?!
++---+----+----+-----+
{noformat}

Just in case,
* Oracle {{full}} join:
{noformat}
	X,	Y,	X,	Y
	1	10	1	100
	2	20	[NULL]	[NULL]
	[NULL]	30	[NULL]	[NULL]
	[NULL]	[NULL]	[NULL]	200{noformat}

* Oracle {{right}} join:
{noformat}
	X,	Y,	X,	Y
	1	10	1	100
	[NULL]	[NULL]	[NULL]	200{noformat}

> 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)