Jinfeng Ni created CALCITE-554:
----------------------------------
Summary: Out join over NULL values could generate wrong query
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)
