calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "baofeng.zbf" <baofeng....@alibaba-inc.com>
Subject Question on `in or not in` subquery plan
Date Wed, 08 Mar 2017 11:34:46 GMT

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
View raw message