[CALCITE-5732] EnumerableHashJoin and EnumerableMergeJoin on composite key return rows matching condition 'null = null'

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.36.0
• Component/s: core, linq4j
• Labels: pull-request-available
• Assignee: Ruben Q L
• Reporter: Viggo Chen
• Created: 30/May/23 10:02
• Updated: 10/Nov/23 11:34
• Resolved: 08/Sep/23 09:42

Description

In `EnumerableJoinTest#testMergeJoinWithCompositeKeyAndNullValues`, the query is like 

select 
  emps.empid
from 
  emps a join emps b
on a.deptno = b.deptno
and a.commission = b.commission;

and the data is like 

  INSERT INTO "emps" VALUES (100, 10, 'Bill', 10000, 1000);
  INSERT INTO "emps" VALUES (200, 20, 'Eric', 8000, 500);
  INSERT INTO "emps" VALUES (150, 10, 'Sebastian', 7000, null);
  INSERT INTO "emps" VALUES (110, 10, 'Theodore', 11500, 250); 

And row with empid = 150 is in expected result. Is this the expected result of join with null condition.

Whats more hash join result with condition a.deptno = b.deptno and a.commission = b.commission is same as merge join. And if there is just one condition a.commission = b.commission, the result do not include empid = 150.

 

Here is a unit test for it

@Test void testHashJoinWithCompositeKeyAndNullValues() {
  // Both join side 'commission' a limited to null, so a.commission = b.commission should always be false.
  // So all columns in right table b are expected to be null, this sql should result in 0 rows.
  final String sql = "select * from\n"
      + " (select empid, salary, commission from emps where commission is null) as a\n"
      + " left join\n"
      + " (select empid, salary, commission from emps where commission is null) as b\n"
      + " on a.salary = b.salary and a.commission = b.commission\n"
      + " where b.empid is not null";

  CalciteAssert.that()
      .with(CalciteConnectionProperty.LEX, Lex.JAVA)
      .with(CalciteConnectionProperty.FORCE_DECORRELATE, false)
      .withSchema("s", new ReflectiveSchema(new HrSchemaBig()))
      .query(sql)
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        planner.removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE);
        planner.addRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
      })
      .explainContains("EnumerableHashJoin")
      .returnsCount(0)
  ;
} 

Issue Links

• is duplicated by: CALCITE-5934 calcite avatica calculation error
• is related to: CALCITE-3846 EnumerableMergeJoin: wrong comparison of composite key with null values
• links to: GitHub Pull Request #3311