It’s just possible that Calcite is being smart. I see a filter LogicalFilter(condition=[<=($0, 20)])
deep in your plan, so maybe Calcite is exploiting the constraint derived from that, and the fact that it’s the same table inside and outside the sub-query (work we did in CALCITE-1494). I also see LogicalTableScan(table=[[TEST4DMP, test]]) and yet there is no mention of TEST4DMP.test in your query. Did you post the right query? The best way to proceed is if you can you create a query that produces the wrong results, either on your own data set of one of the built-in data sets (emp, dept etc.). Julian > On Mar 8, 2017, at 3:34 AM, baofeng.zbf <[email protected]> wrote: > > > Hi, > > I got a question on `in (non-scalar-subquery1) or not in > (non-scalar-subquery2)` pattern, > > for SQL: > ``` > select count(*) from db.foo where (int_test in (select int_test from db.foo > where id<=20 )) or (long_test not in (select long_test from db.foo where id > <=1)); > ``` > > plan generated: > ``` > LogicalAggregate(group=[{}], EXPR$0=[COUNT()]) > LogicalProject($f0=[0]) > LogicalFilter(condition=[OR(true, CAST(NOT(CASE(=($10, 0), false, IS NOT > NULL($14), true, IS NULL($12), null, <($11, $10), null, false))):BOOLEAN)]) > LogicalJoin(condition=[=($12, $13)], joinType=[left]) > LogicalProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], > $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$9], $f10=[$10], $f11=[$11], > $f12=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalJoin(condition=[=($1, $9)], joinType=[inner]) > LogicalTableScan(table=[[TEST4DMP, test]]) > LogicalAggregate(group=[{0}]) > LogicalProject(int_test=[$1]) > LogicalFilter(condition=[<=($0, 20)]) > LogicalTableScan(table=[[db, foo]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject($f0=[$0], $f1=[true]) > LogicalProject(long_test=[$7]) > LogicalFilter(condition=[<=($0, 1)]) > LogicalTableScan(table=[[db, foo]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject($f0=[$0], $f1=[true]) > LogicalProject(long_test=[$7]) > LogicalFilter(condition=[<=($0, 1)]) > LogicalTableScan(table=[[db, foo]]) > ``` > > I don’t think the `LogicalFilter` condition is right, and it will be folded > to be ALWAYS TRUE. > > Any one can help me? > > Thx, > Baofeng Zhang >
