Stamatis Zampetakis created CALCITE-4561: --------------------------------------------
Summary: 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 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. -- This message was sent by Atlassian Jira (v8.3.4#803005)