[ https://issues.apache.org/jira/browse/CALCITE-4561?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17326183#comment-17326183 ]
Haisheng Yuan commented on CALCITE-4561: ---------------------------------------- Nice finding. it is better to generate not_null filter operator on both sides. > 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)