[ https://issues.apache.org/jira/browse/CALCITE-3128?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16874592#comment-16874592 ]
Danny Chan commented on CALCITE-3128: ------------------------------------- [~julianhyde] thanks, {quote}if the condition is '(t1.c is null) = (t2.c is null)', I think the problem would still occur. {quote} I checked this query: {code:sql} select dept.deptno, emp.ename from dept join emp on (dept.dname is null) = (emp.sal is null) order by emp.ename limit 10; {code} and finally plan is {code:xml} EnumerableLimit(fetch=[10]) EnumerableSort(sort0=[$1], dir0=[ASC]) EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t0], ENAME=[$t3]) EnumerableHashJoin(condition=[=($1, $4)], joinType=[inner]) EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NULL($t1)], DEPTNO=[$t0], $f3=[$t3]) EnumerableTableScan(table=[[scott, DEPT]]) EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NULL($t5)], proj#0..1=[{exprs}], $f8=[$t8]) EnumerableTableScan(table=[[scott, EMP]]) {code} The condition '(t1.c is null) = (t2.c is null)' for hash join are all boolean keys, which means they will never be null, so i don't think there is any problem for this case. {quote}The key factor, I think, is that one side has just one column, and a null in that column. {quote} This is not true, like the case: {code:sql} SELECT * FROM (VALUES (NULLIF(5, 5)), (NULLIF(5, 5))) a, (VALUES (NULLIF(5, 5)), (NULLIF(5, 5))) b {code} > Joining two tables producing only NULLs will return 0 rows > ---------------------------------------------------------- > > Key: CALCITE-3128 > URL: https://issues.apache.org/jira/browse/CALCITE-3128 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.20.0 > Reporter: Muhammad Gelbana > Assignee: Danny Chan > Priority: Major > Labels: pull-request-available > Time Spent: 2h 10m > Remaining Estimate: 0h > > The following queries will return 0 rows while they're expected to ruturn > rows with NULLs in them. > {code:sql} > SELECT * > FROM (SELECT NULLIF(5, 5)) a, (SELECT NULLIF(5, 5)) b > {code} > {code:sql} > SELECT * > FROM (VALUES (NULLIF(5, 5)), (NULLIF(5, 5))) a, (VALUES (NULLIF(5, 5)), > (NULLIF(5, 5))) b > {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)