github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimizer/pushdown.test (about)

     1  drop database if exists d1;
     2  create database d1;
     3  use d1;
     4  drop table if exists t1;
     5  drop table if exists t2;
     6  drop table if exists t3;
     7  create table t1(c1 int primary key);
     8  create table t2(c1 int primary key, c2 int, c3 int);
     9  create table t3(c1 int, c2 int, c3 int, primary key(c1,c2));
    10  insert into t1 select * from generate_series(10000) g;
    11  insert into t2 select c1, c1, c1 from t1;
    12  insert into t2 select c1+10000, c1+10000, c1+10000 from t1;
    13  insert into t3 select c1, c1, c1 from t1;
    14  insert into t3 select c1+10000, c1+10000, c1+10000 from t1;
    15  insert into t3 select c1+20000, c1+20000, c1+20000 from t1;
    16  -- @separator:table
    17  select mo_ctl('dn', 'flush', 'd1.t1');
    18  -- @separator:table
    19  select mo_ctl('dn', 'flush', 'd1.t2');
    20  -- @separator:table
    21  select mo_ctl('dn', 'flush', 'd1.t3');
    22  -- @separator:table
    23  explain select * from (select * from t1 where c1%3=0 order by c1 desc limit 10) tmpt where c1 % 2 = 0;
    24  -- @separator:table
    25  explain select * from (select v2.c1 as c1, v2.c2 as c2, v2.c3 as c3 from (select * from t1 where c1!=0 limit 10) v1 join (select * from t2 where c1!=1 limit 10) v2 on v1.c1=v2.c1 where v2.c2>0 limit 10) v3 join (select * from t3 where c1!=2 limit 10) v4 on v3.c3=v4.c3 where v4.c2>0 limit 10;
    26  -- @separator:table
    27  explain select * from t1 left join t2 on t1.c1=t2.c1 order by t1.c1 limit 10;
    28  select * from t1 left join t2 on t1.c1=t2.c1 order by t1.c1 limit 10;
    29  -- @separator:table
    30  explain select * from t1 left join t2 on t1.c1=t2.c1 order by t1.c1 limit 10 offset 20;
    31  select * from t1 left join t2 on t1.c1=t2.c1 order by t1.c1 limit 10 offset 20;
    32  prepare s from select * from t1 left join t2 on t1.c1=t2.c1 order by t1.c1 limit ? offset ?;
    33  set @a_var = 5;
    34  set @b_var = 30;
    35  execute s using @a_var,@b_var;
    36  drop database if exists d1;