[
https://issues.apache.org/jira/browse/CALCITE-554?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-554:
--------------------------------
Summary: Outer join over NULL keys generates wrong result (was: Out join
over NULL values could generate wrong query result. )
> 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
>
> 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)
