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

     1  drop database if exists d1;
     2  create database d1;
     3  use d1;
     4  drop table if exists t1;
     5  create table t1(c1 int primary key, c2 int, c3 int);
     6  create table t2(c1 int primary key, c2 int, c3 int);
     7  insert into t1 select *,*,* from generate_series(1000000) g;
     8  insert into t2 select *,*,* from generate_series(1000000) g;
     9  -- @separator:table
    10  select mo_ctl('dn', 'flush', 'd1.t1');
    11  -- @separator:table
    12  select mo_ctl('dn', 'flush', 'd1.t2');
    13  select Sleep(1);
    14  -- @separator:table
    15  explain select * from t1 order by c1 limit 100 offset 200;
    16  -- @separator:table
    17  explain select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 44444 order by t1.c1 limit 10 offset 10;
    18  select * from t1 order by c1 limit 10 ;
    19  select * from t1 order by c1 limit 10 offset 200;
    20  select * from t1 order by c1 desc limit 10 ;
    21  select * from t1 order by c1 desc limit 10 offset 200;
    22  select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 22 and 44 order by t1.c1 limit 10;
    23  select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 22 and 44 order by t1.c1 desc limit 10 offset 10;
    24  select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 44444 order by t1.c1 limit 10;
    25  select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 44444 order by t1.c1 desc limit 10 offset 10;
    26  select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 555555 order by t1.c1 limit 10;
    27  -- @separator:table
    28  explain select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 555555 order by t1.c1 desc limit 10 offset 10;
    29  select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 555555 order by t1.c1 desc limit 10 offset 10;
    30  drop database if exists d1;