[
https://issues.apache.org/jira/browse/CALCITE-4561?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-4561:
-----------------------------------------
Description:
When the query plan contains an {{EnumerableHashJoin}} with type {{SEMI}} over
nullable columns the results are wrong since the operator incorrectly considers
{{NULL=NULL}} as {{TRUE}} instead of {{UNKNOWN}}/{{FALSE}}.
The problem can be reproduced by putting the following test in
{{EnumerableHashJoinTest}}.
{code:java}
@Test void semiJoinWithNulls() {
tester(false, new JdbcTest.HrSchema())
.query(
"SELECT e1.name FROM emps e1 WHERE e1.commission in (SELECT
e2.commission FROM emps e2)")
.explainContains("EnumerableCalc(expr#0..1=[{inputs}], name=[$t0])\n"
+ " EnumerableHashJoin(condition=[=($1, $6)], joinType=[semi])\n"
+ " EnumerableCalc(expr#0..4=[{inputs}], name=[$t2],
commission=[$t4])\n"
+ " EnumerableTableScan(table=[[s, emps]])\n"
+ " EnumerableTableScan(table=[[s, emps]])\n\n")
.returnsUnordered(
"name=Bill",
"name=Eric",
"name=Theodore");
}
{code}
At the moment 'Sebastian' (with commission NULL) is wrongly included in the
result set.
was:
When the query plan contains an {{EnumerableHashJoin}} with type {{SEMI}} over
nullable columns the results are wrong since the operator incorrectly considers
{{NULL=NULL}} as {{TRUE}} instead of {{UNKNOWN}}/{{FALSE}}.
The problem can be reproduced by putting the following test in
{{EnumerableHashJoinTest}}.
{noformat}
@Test void semiJoinWithNulls() {
tester(false, new JdbcTest.HrSchema())
.query(
"SELECT e1.name FROM emps e1 WHERE e1.commission in (SELECT
e2.commission FROM emps e2)")
.explainContains("EnumerableCalc(expr#0..1=[{inputs}], name=[$t0])\n"
+ " EnumerableHashJoin(condition=[=($1, $6)], joinType=[semi])\n"
+ " EnumerableCalc(expr#0..4=[{inputs}], name=[$t2],
commission=[$t4])\n"
+ " EnumerableTableScan(table=[[s, emps]])\n"
+ " EnumerableTableScan(table=[[s, emps]])\n\n")
.returnsUnordered(
"name=Bill",
"name=Eric",
"name=Theodore");
}
{noformat}
At the moment 'Sebastian' (with commission NULL) is wrongly included in the
result set.
> Wrong results for plan with EnumerableHashJoin (semi) on nullable colunms
> -------------------------------------------------------------------------
>
> Key: CALCITE-4561
> URL: https://issues.apache.org/jira/browse/CALCITE-4561
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.27.0
> Reporter: Stamatis Zampetakis
> Priority: Major
>
> When the query plan contains an {{EnumerableHashJoin}} with type {{SEMI}}
> over nullable columns the results are wrong since the operator incorrectly
> considers {{NULL=NULL}} as {{TRUE}} instead of {{UNKNOWN}}/{{FALSE}}.
> The problem can be reproduced by putting the following test in
> {{EnumerableHashJoinTest}}.
> {code:java}
> @Test void semiJoinWithNulls() {
> tester(false, new JdbcTest.HrSchema())
> .query(
> "SELECT e1.name FROM emps e1 WHERE e1.commission in (SELECT
> e2.commission FROM emps e2)")
> .explainContains("EnumerableCalc(expr#0..1=[{inputs}], name=[$t0])\n"
> + " EnumerableHashJoin(condition=[=($1, $6)],
> joinType=[semi])\n"
> + " EnumerableCalc(expr#0..4=[{inputs}], name=[$t2],
> commission=[$t4])\n"
> + " EnumerableTableScan(table=[[s, emps]])\n"
> + " EnumerableTableScan(table=[[s, emps]])\n\n")
> .returnsUnordered(
> "name=Bill",
> "name=Eric",
> "name=Theodore");
> }
> {code}
> At the moment 'Sebastian' (with commission NULL) is wrongly included in the
> result set.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
