[ https://issues.apache.org/jira/browse/CALCITE-1513?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16774826#comment-16774826 ]
Vineet Garg edited comment on CALCITE-1513 at 2/22/19 6:21 AM: --------------------------------------------------------------- The query doesn't throw assertion anymore. Following is the plan generated by this query which looks correct to me: {code:sql} EnumerableAggregate(group=[{}], C=[COUNT()]) EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)], expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)], expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}], $condition=[$t18]) EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left]) EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)], expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6, $t9)], proj#0..5=[{exprs}], $condition=[$t10]) EnumerableJoin(condition=[=($1, $3)], joinType=[left]) EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5]) EnumerableTableScan(table=[[scott, EMP]]) EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)]) EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)], proj#0..2=[{exprs}], $condition=[$t3]) EnumerableTableScan(table=[[scott, DEPT]]) EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3], DNAME=[$t4], SAL=[$t0]) EnumerableJoin(condition=[=($1, $2)], joinType=[inner]) EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0, $t1)], SAL=[$t0], $f1=[$t2]) EnumerableAggregate(group=[{5}]) EnumerableTableScan(table=[[scott, EMP]]) EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4]) EnumerableTableScan(table=[[scott, DEPT]]) {code} I'll create a pull request with the test case. Edit: The query I used for above plan is a bit different from the jira: {code:sql} select count(*) as c from "scott".emp as e where sal + 100 not in ( select deptno from dept where dname = e.ename); {code} was (Author: vgarg): The query doesn't throw assertion anymore. Following is the plan generated by this query which looks correct to me: {code:sql} EnumerableAggregate(group=[{}], C=[COUNT()]) EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)], expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)], expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}], $condition=[$t18]) EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left]) EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)], expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6, $t9)], proj#0..5=[{exprs}], $condition=[$t10]) EnumerableJoin(condition=[=($1, $3)], joinType=[left]) EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5]) EnumerableTableScan(table=[[scott, EMP]]) EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)]) EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)], proj#0..2=[{exprs}], $condition=[$t3]) EnumerableTableScan(table=[[scott, DEPT]]) EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3], DNAME=[$t4], SAL=[$t0]) EnumerableJoin(condition=[=($1, $2)], joinType=[inner]) EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0, $t1)], SAL=[$t0], $f1=[$t2]) EnumerableAggregate(group=[{5}]) EnumerableTableScan(table=[[scott, EMP]]) EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4]) EnumerableTableScan(table=[[scott, DEPT]]) {code} I'll create a pull request with the test case. > Correlated NOT IN query throws AssertionError > --------------------------------------------- > > Key: CALCITE-1513 > URL: https://issues.apache.org/jira/browse/CALCITE-1513 > Project: Calcite > Issue Type: Bug > Reporter: Julian Hyde > Priority: Major > Labels: sub-query > > A correlated NOT IN query throws AssertionError during decorrelation. Here is > a patch that reproduces the case: > {noformat} > diff --git a/core/src/test/resources/sql/subquery.iq > b/core/src/test/resources/sql/subquery.iq > index ad71655..975aae1 100644 > --- a/core/src/test/resources/sql/subquery.iq > +++ b/core/src/test/resources/sql/subquery.iq > @@ -370,6 +370,22 @@ where e.job not in ( > !plan > !} > > +# Correlated condition in NOT IN. > +# Tested on Oracle. > +select count(*) as c > +from "scott".emp as e > +where sal + 100 not in ( > + select comm > + from "scott".emp > + where job = e.job); > + EMPNO ENAME JOB MGR HIREDATE SAL COMM > DEPTNO > +---------- ---------- --------- ---------- --------- ---------- ---------- > ---------- > + 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 > 30 > + 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 > 30 > + 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 > 30 > + 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 > 30 > +!ok > + > # [CALCITE-864] Correlation variable has incorrect row type if it is > populated > # by right side of a Join > select * > {noformat} > And here is the stack: > {noformat} > java.lang.AssertionError: Internal error: While invoking method 'public > org.apache.calcite.sql2rel.RelDecorrelator$Frame > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(org.apache.calcite.rel.logical.LogicalAggregate)' > at org.apache.calcite.util.Util.newInternal(Util.java:792) > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:534) > at > org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:601) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:242) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:211) > at > org.apache.calcite.tools.Programs$DecorrelateProgram.run(Programs.java:370) > at > org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:351) > at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:155) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:286) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:195) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:748) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:610) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:580) > Caused by: java.lang.reflect.InvocationTargetException > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531) > ... 29 more > Caused by: java.lang.AssertionError > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:454) > ... 34 more{noformat} -- This message was sent by Atlassian JIRA (v7.6.3#76005)