[jira] [Created] (CALCITE-4561) Wrong results for plan with EnumerableHashJoin (semi) on nullable colunms

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

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)