[
https://issues.apache.org/jira/browse/CALCITE-1493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15733494#comment-15733494
]
Vineet Garg commented on CALCITE-1493:
--------------------------------------
Ignore my last comment. I found a bug with this patch. This patch is not
generating plan with count(), count(\*) to consider null values.
===Reproducer====
{code}
create table t1(a int, b int);
insert into t1 values(1,0);
insert into t1 values(1,0);
insert into t1 values(1,0);
create table t2(a int, b int);
insert into t2 values(2,1);
insert into t2 values(3,1);
insert into t2 values(NULL,1);
select t1.a from t1 where t1.b NOT IN (select t2.a from t2 where t2.b=t1.a);
--Expected result:
zero rows
--Actual result
1
1
1
Fetched: 3 row(s)
{code}
This is what the plan looks like on HIVE side just after decorrelation
{noformat}
HiveProject(a=[$0])
LogicalProject(a=[$0], b=[$1], BLOCK__OFFSET__INSIDE__FILE=[$2],
INPUT__FILE__NAME=[$3], ROW__ID=[$4])
LogicalFilter(condition=[NOT(CASE(IS NOT NULL($7), true, IS NULL($1), null,
false))])
LogicalJoin(condition=[AND(=($0, $6), =($1, $8))], joinType=[left])
HiveTableScan(table=[[default.t_1]], table:alias=[t_1])
LogicalJoin(condition=[=($3, $0)], joinType=[inner])
LogicalProject(a=[$0], a0=[$1], $f2=[true])
LogicalAggregate(group=[{0, 1}])
LogicalProject(a=[$0], a0=[$2], i=[$1])
LogicalProject(a=[$0], i=[true], a0=[$1])
HiveProject(a=[$0], a0=[$5])
LogicalJoin(condition=[=($1, $5)], joinType=[inner])
HiveTableScan(table=[[default.t_2]], table:alias=[t_2])
LogicalAggregate(group=[{0}])
LogicalProject(a=[$0])
HiveTableScan(table=[[default.t_1]],
table:alias=[t_1])
LogicalAggregate(group=[{0}])
LogicalProject(b=[$1])
HiveTableScan(table=[[default.t_1]], table:alias=[t_1])
{noformat}
As you can notice this plan is not doing null check using count, count\*
> Wrong plan for NOT IN correlated queries
> ----------------------------------------
>
> Key: CALCITE-1493
> URL: https://issues.apache.org/jira/browse/CALCITE-1493
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Vineet Garg
> Assignee: Julian Hyde
> Labels: sub-query
> Attachments: CALCITE-1493.test.patch
>
>
> Plan generated by calcite with SubqueryRemoveRule followed by de-correlation
> for the following query:
> {noformat} select sal from emp where empno NOT IN (select deptno from dept
> where emp.job = dept.name) {noformat}
> is
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[IS NULL($11)])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8],
> DEPTNO0=[CAST($9):INTEGER], JOB0=[CAST($10):VARCHAR(10) CHARACTER SET
> "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], $f2=[CAST($11):BOOLEAN])
> LogicalJoin(condition=[AND(=($2, $10), =($0, $9))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(DEPTNO=[$0], JOB=[$1], $f2=[true])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$0], JOB=[$2], i=[$1])
> LogicalProject(DEPTNO=[$0], i=[true], JOB=[$1])
> LogicalProject(DEPTNO=[$0], JOB=[$2])
> LogicalJoin(condition=[=($2, $1)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalAggregate(group=[{0}])
> LogicalProject(JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {noformat}
> As you can notice instead of doing Left Outer Join Calcite is doing Inner
> Join. This will produce wrong results.
> Plan for same query just before SubqueryRemove Rule is:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalFilter(condition=[NOT(IN($0, {
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> }))], variablesSet=[[$cor0]])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {noformat}
> Plan just after SubqueryRemove Rule:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[IS NULL($10)])
> LogicalFilter(condition=[=($0, $9)])
> LogicalCorrelate(correlation=[$cor0], joinType=[LEFT],
> requiredColumns=[{2}])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$0], i=[true])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
> Looking at above it seems RelDecorrelator have some issue where it is coming
> up with Inner Join.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
