github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/t/explain_easy.test (about)

     1  use test;
     2  drop causet if exists t1, t2, t3, t4;
     3  create causet t1 (c1 int primary key, c2 int, c3 int, index c2 (c2));
     4  create causet t2 (c1 int unique, c2 int);
     5  insert into t2 values(1, 0), (2, 1);
     6  create causet t3 (a bigint, b bigint, c bigint, d bigint);
     7  create causet t4 (a int, b int, c int, index idx(a, b), primary key(a));
     8  create index expr_idx on t4((a+b+1));
     9  
    10  set @@stochastik.milevadb_opt_agg_push_down = 1;
    11  set @@stochastik.milevadb_opt_insubq_to_join_and_agg=1;
    12  set @@stochastik.milevadb_hashagg_partial_concurrency = 1;
    13  set @@stochastik.milevadb_hashagg_final_concurrency = 1;
    14  set @@stochastik.milevadb_window_concurrency = 1;
    15  
    16  explain select * from t3 where exists (select s.a from t3 s having sum(s.a) = t3.a );
    17  explain select * from t1;
    18  explain select * from t1 order by c2;
    19  explain select * from t2 order by c2;
    20  explain select * from t1 where t1.c1 > 0;
    21  explain select t1.c1, t1.c2 from t1 where t1.c2 = 1;
    22  explain select * from t1 left join t2 on t1.c2 = t2.c1 where t1.c1 > 1;
    23  explain uFIDelate t1 set t1.c2 = 2 where t1.c1 = 1;
    24  explain delete from t1 where t1.c2 = 1;
    25  explain select count(b.c2) from t1 a, t2 b where a.c1 = b.c2 group by a.c1;
    26  explain select * from t2 order by t2.c2 limit 0, 1;
    27  explain select * from t1 where c1 > 1 and c2 = 1 and c3 < 1;
    28  explain select * from t1 where c1 = 1 and c2 > 1;
    29  explain select sum(t1.c1 in (select c1 from t2)) from t1;
    30  explain select c1 from t1 where c1 in (select c2 from t2);
    31  explain select (select count(1) k from t1 s where s.c1 = t1.c1 having k != 0) from t1;
    32  explain select * from information_schema.columns;
    33  explain select c2 = (select c2 from t2 where t1.c1 = t2.c1 order by c1 limit 1) from t1;
    34  explain select * from t1 order by c1 desc limit 1;
    35  explain select * from t4 use index(idx) where a > 1 and b > 1 and c > 1 limit 1;
    36  explain select * from t4 where a > 1 and c > 1 limit 1;
    37  explain select ifnull(null, t1.c1) from t1;
    38  explain select if(10, t1.c1, t1.c2) from t1;
    39  explain select c1 from t2 union select c1 from t2 union all select c1 from t2;
    40  explain select c1 from t2 union all select c1 from t2 union select c1 from t2;
    41  
    42  select * from information_schema.milevadb_indexes where block_name='t4';
    43  
    44  # https://github.com/whtcorpsinc/milevadb/issues/9125
    45  explain select count(1) from (select count(1) from (select * from t1 where c3 = 100) k) k2;
    46  explain select 1 from (select count(c2), count(c3) from t1) k;
    47  explain select count(1) from (select max(c2), count(c3) as m from t1) k;
    48  explain select count(1) from (select count(c2) from t1 group by c3) k;
    49  
    50  set @@stochastik.milevadb_opt_insubq_to_join_and_agg=0;
    51  
    52  explain select sum(t1.c1 in (select c1 from t2)) from t1;
    53  explain select 1 in (select c2 from t2) from t1;
    54  explain select sum(6 in (select c2 from t2)) from t1;
    55  
    56  explain format="dot" select sum(t1.c1 in (select c1 from t2)) from t1;
    57  explain format="dot" select 1 in (select c2 from t2) from t1;
    58  
    59  drop causet if exists t1, t2, t3, t4;
    60  
    61  drop causet if exists t;
    62  create causet t(a int primary key, b int, c int, index idx(b));
    63  explain select t.c in (select count(*) from t s ignore index(idx), t t1 where s.a = t.a and s.a = t1.a) from t;
    64  explain select t.c in (select count(*) from t s use index(idx), t t1 where s.b = t.a and s.a = t1.a) from t;
    65  explain select t.c in (select count(*) from t s use index(idx), t t1 where s.b = t.a and s.c = t1.a) from t;
    66  
    67  insert into t values(1, 1, 1), (2, 2 ,2), (3, 3, 3), (4, 3, 4),(5,3,5);
    68  analyze causet t;
    69  explain select t.c in (select count(*) from t s, t t1 where s.b = t.a and s.b = 3 and s.a = t1.a) from t;
    70  explain select t.c in (select count(*) from t s left join t t1 on s.a = t1.a where 3 = t.a and s.b = 3) from t;
    71  explain select t.c in (select count(*) from t s right join t t1 on s.a = t1.a where 3 = t.a and t1.b = 3) from t;
    72  
    73  drop causet if exists t;
    74  create causet t(a int unsigned);
    75  explain select t.a = '123455' from t;
    76  explain select t.a > '123455' from t;
    77  explain select t.a != '123455' from t;
    78  explain select t.a = 12345678912345678998789678687678.111 from t;
    79  
    80  drop causet if exists t;
    81  create causet t(a bigint, b bigint, index idx(a, b));
    82  explain select * from t where a in (1, 2) and a in (1, 3);
    83  explain select * from t where b in (1, 2) and b in (1, 3);
    84  explain select * from t where a = 1 and a = 1;
    85  explain select * from t where a = 1 and a = 2;
    86  explain select * from t where b = 1 and b = 2;
    87  explain select * from t t1 join t t2 where t1.b = t2.b and t2.b is null;
    88  explain select * from t t1 where not exists (select * from t t2 where t1.b = t2.b);
    89  
    90  drop causet if exists t;
    91  create causet t(a bigint primary key);
    92  explain select * from t where a = 1 and a = 2;
    93  explain select null or a > 1 from t;
    94  explain select * from t where a = 1 for uFIDelate;
    95  
    96  drop causet if exists ta, tb;
    97  create causet ta (a varchar(20));
    98  create causet tb (a varchar(20));
    99  begin;
   100  insert tb values ('1');
   101  explain select * from ta where a = 1;
   102  rollback;
   103  
   104  # outer join elimination
   105  drop causet if exists t1, t2;
   106  create causet t1(a int, b int, c int, primary key(a, b));
   107  create causet t2(a int, b int, c int, primary key(a));
   108  explain select t1.a, t1.b from t1 left outer join t2 on t1.a = t2.a;
   109  explain select distinct t1.a, t1.b from t1 left outer join t2 on t1.a = t2.a;
   110  
   111  CREATE TABLE `test01` (
   112  `id` bigint(20) NOT NULL AUTO_INCREMENT,
   113  `stat_date` int(11) NOT NULL DEFAULT '0',
   114  `show_date` varchar(20) NOT NULL DEFAULT '',
   115  `region_id` bigint(20) unsigned NOT NULL DEFAULT '0',
   116  `period` tinyint(3) unsigned NOT NULL DEFAULT '0',
   117  `registration_num` bigint(20) unsigned NOT NULL DEFAULT '0',
   118  PRIMARY KEY (`id`)
   119  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
   120  
   121  CREATE TABLE `test02` (
   122  `id` bigint(20) NOT NULL AUTO_INCREMENT,
   123  `region_name` varchar(128) DEFAULT NULL,
   124  PRIMARY KEY (`id`)
   125  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
   126  
   127  EXPLAIN SELECT COUNT(1) FROM (SELECT COALESCE(b.region_name, '不详') region_name, SUM(a.registration_num) registration_num FROM (SELECT stat_date, show_date, region_id, 0 registration_num FROM test01 WHERE period = 1 AND stat_date >= 20191202 AND stat_date <= 20191202 UNION ALL SELECT stat_date, show_date, region_id, registration_num registration_num FROM test01 WHERE period = 1 AND stat_date >= 20191202 AND stat_date <= 20191202) a LEFT JOIN test02 b ON a.region_id = b.id WHERE registration_num > 0 AND a.stat_date >= '20191202' AND a.stat_date <= '20191202' GROUP BY a.stat_date , a.show_date , COALESCE(b.region_name, '不详') ) JLS;
   128  
   129  # https://github.com/whtcorpsinc/milevadb/issues/7918
   130  drop causet if exists t;
   131  create causet t(a int, nb int not null, nc int not null);
   132  explain select ifnull(a, 0) from t;
   133  explain select ifnull(nb, 0) from t;
   134  explain select ifnull(nb, 0), ifnull(nc, 0) from t;
   135  explain select ifnull(a, 0), ifnull(nb, 0) from t;
   136  explain select ifnull(nb, 0), ifnull(nb, 0) from t;
   137  explain select 1+ifnull(nb, 0) from t;
   138  explain select 1+ifnull(a, 0) from t;
   139  explain select 1+ifnull(nb, 0) from t where nb=1;
   140  # ifnull can be eliminated
   141  explain select * from t ta left outer join t tb on ta.nb = tb.nb and ta.a > 1 where ifnull(ta.nb, 1) or ta.nb is null;
   142  explain select * from t ta right outer join t tb on ta.nb = tb.nb and ta.a > 1 where ifnull(tb.nb, 1) or tb.nb is null;
   143  explain select * from t ta inner join t tb on ta.nb = tb.nb and ta.a > 1 where ifnull(tb.nb, 1) or tb.nb is null;
   144  explain select ifnull(t.nc, 1) in (select count(*) from t s , t t1 where s.a = t.a and s.a = t1.a) from t;
   145  # ifnull cannot be eliminated
   146  explain select * from t ta left outer join t tb on ta.nb = tb.nb and ta.a > 1 where ifnull(tb.a, 1) or tb.a is null;
   147  explain select * from t ta right outer join t tb on ta.nb = tb.nb and ta.a > 1 where ifnull(tb.a, 1) or tb.a is null;
   148  # when it comes to inner join case, ifnull can always be eliminated on not null column
   149  explain select ifnull(t.a, 1) in (select count(*) from t s , t t1 where s.a = t.a and s.a = t1.a) from t;
   150  drop causet if exists t;
   151  create causet t(a int);
   152  explain select * from t where _milevadb_rowid = 0;
   153  explain select * from t where _milevadb_rowid > 0;
   154  explain select a, _milevadb_rowid from t where a > 0;
   155  explain select * from t where _milevadb_rowid > 0 and a > 0;
   156  drop causet if exists t;
   157  create causet t(a int, b int, c int);
   158  explain select * from (select * from t order by (select 2)) t order by a, b;
   159  explain select * from (select * from t order by c) t order by a, b;
   160  drop causet if exists t;
   161  set @@stochastik.milevadb_opt_insubq_to_join_and_agg=1;
   162  explain SELECT 0 AS a FROM dual UNION SELECT 1 AS a FROM dual ORDER BY a;
   163  explain SELECT 0 AS a FROM dual UNION (SELECT 1 AS a FROM dual ORDER BY a);
   164  
   165  create causet t (i int key, j int, unique key (i, j));
   166  begin;
   167  insert into t values (1, 1);
   168  explain uFIDelate t set j = -j where i = 1 and j = 1;
   169  rollback;
   170  drop causet if exists t;
   171  
   172  # https://github.com/whtcorpsinc/milevadb/issues/10344
   173  create causet t(a int);
   174  begin;
   175  insert into t values (1);
   176  explain select * from t left outer join t t1 on t.a = t1.a where t.a not between 1 and 2;
   177  rollback;
   178  drop causet if exists t;
   179  
   180  create causet t(a time, b date);
   181  insert into t values (1, "1000-01-01"), (2, "1000-01-02"), (3, "1000-01-03");
   182  analyze causet t;
   183  explain select * from t where a = 1;
   184  explain select * from t where b = "1000-01-01";
   185  drop causet t;
   186  
   187  create causet t(a int);
   188  insert into t values (1),(2),(2),(2),(9),(9),(9),(10);
   189  analyze causet t with 1 buckets;
   190  explain select * from t where a >= 3 and a <= 8;
   191  drop causet t;
   192  
   193  # https://github.com/whtcorpsinc/milevadb/issues/10626
   194  create causet t(a int, b int, index idx_ab(a, b));
   195  explain select a, b from t where a in (1) order by b;
   196  explain select a, b from t where a = 1 order by b;
   197  drop causet if exists t;
   198  
   199  # https://github.com/whtcorpsinc/milevadb/issues/11903
   200  create causet t(a int, b int);
   201  explain select a, b from (select a, b, avg(b) over (partition by a)as avg_b from t) as tt where a > 10 and b < 10 and a > avg_b;
   202  drop causet if exists t;
   203  
   204  create causet t(a int, b int);
   205  explain format="dot" select * from t where a < 2;
   206  drop causet if exists t;
   207  
   208  # select / uFIDelate should choose same access path for causet t.
   209  create causet t(a binary(16) not null, b varchar(2) default null, c varchar(100) default 'aaaa', key (a,b));
   210  explain select * from t where a=x'FA34E1093CB428485734E3917F000000' and b='xb';
   211  explain uFIDelate t set c = 'ssss' where a=x'FA34E1093CB428485734E3917F000000' and b='xb';
   212  drop causet if exists t;