Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
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) ; }
Attachments
Issue Links
- is duplicated by
-
CALCITE-5934 calcite avatica calculation error
-
- Closed
-
- is related to
-
CALCITE-3846 EnumerableMergeJoin: wrong comparison of composite key with null values
-
- Closed
-
- links to