calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <jh...@apache.org>
Subject Re: Question on `in or not in` subquery plan
Date Wed, 08 Mar 2017 19:10:01 GMT
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 <baofeng.zbf@alibaba-inc.com> 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
> 


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message