[ 
https://issues.apache.org/jira/browse/CALCITE-5732?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ruben Q L resolved CALCITE-5732.
--------------------------------
    Resolution: Fixed

Fixed via 
[{{3aee0b8}}|https://github.com/apache/calcite/commit/3aee0b86aa23476cbdecc75ad5d43b936a6fff7b]
 

 Thansk [~viggoc] for reporting this issue, and [~libenchao] for the review!

> EnumerableHashJoin and EnumerableMergeJoin on composite key return rows 
> matching condition 'null = null'
> --------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-5732
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5732
>             Project: Calcite
>          Issue Type: Bug
>          Components: core, linq4j
>            Reporter: Viggo Chen
>            Assignee: Ruben Q L
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.36.0
>
>
> In `EnumerableJoinTest#testMergeJoinWithCompositeKeyAndNullValues`, the query 
> is like 
> {code:java}
> select 
>   emps.empid
> from 
>   emps a join emps b
> on a.deptno = b.deptno
> and a.commission = b.commission;{code}
> and the data is like 
> {code:java}
>   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); {code}
> 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
> {code:java}
> @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)
>   ;
> } {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

Reply via email to