[CALCITE-3539] EnumerableDefaults#nestedLoopJoin returns duplicates for JoinType.SEMI

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 1.21.0
• Fix Version/s: 1.22.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Ruben Q L
• Reporter: Ruben Q L
• Created: 27/Nov/19 08:11
• Updated: 06/Mar/20 04:35
• Resolved: 28/Nov/19 15:47

Description

The issue can be reproduced by adding the following test to EnumerablesTest.java:

@Test public void testNestedLoopSemiJoin() {
  assertThat(
    EnumerableDefaults.nestedLoopJoin(DEPTS, EMPS, (d, e) -> d.deptno == e.deptno,
          (d, e) -> d.toString(), JoinType.SEMI).toList().toString(),
    equalTo("[Dept(20, Sales)]"));
}

This test performs a "DEPT semi-join EMP" (i.e. select all departments which have at least one employee):

SELECT * FROM DEPT
WHERE EXISTS (SELECT 1 FROM EMP WHERE DEPT.DEPTNO = EMP.DEPTNO)

This semi-join query must not return duplicates, however the test returns them (it returns "Sales" two times because it has two employees, but this is wrong, it should be returned just once):

Expected: "[Dept(20, Sales)]"
 but: was "[Dept(20, Sales), Dept(20, Sales)]"

Issue Links

• links to: GitHub Pull Request #1607