364 lines
22 KiB
Plaintext
364 lines
22 KiB
Plaintext
set tidb_cost_model_version=2;
|
|
drop table if exists t1, t2;
|
|
create table t1(id bigint primary key, a int, b int);
|
|
create table t2(id bigint primary key, a int, b int);
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a > t2.a and t1.a = 1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader, left cond:[eq(expression__explain.t1.a, 1)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] gt(1, expression__explain.t2.a)
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a > t2.a where t1.a = 1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] eq(expression__explain.t1.a, 1)
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] gt(1, expression__explain.t2.a)
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a = t2.a and t1.a > 1;
|
|
id task access object operator info
|
|
HashJoin root left outer join, left side:TableReader, equal:[eq(expression__explain.t1.a, expression__explain.t2.a)], left cond:[gt(expression__explain.t1.a, 1)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] gt(expression__explain.t2.a, 1), not(isnull(expression__explain.t2.a))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a = t2.a where t1.a > 1;
|
|
id task access object operator info
|
|
HashJoin root left outer join, left side:TableReader, equal:[eq(expression__explain.t1.a, expression__explain.t2.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] gt(expression__explain.t2.a, 1), not(isnull(expression__explain.t2.a))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] gt(expression__explain.t1.a, 1)
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 right join t2 on t1.a > t2.a where t2.a = 1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN right outer join, left side:TableReader
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] eq(expression__explain.t2.a, 1)
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] gt(expression__explain.t1.a, 1)
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 right join t2 on t1.a = t2.a where t2.a > 1;
|
|
id task access object operator info
|
|
HashJoin root right outer join, left side:TableReader, equal:[eq(expression__explain.t1.a, expression__explain.t2.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] gt(expression__explain.t2.a, 1)
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] gt(expression__explain.t1.a, 1), not(isnull(expression__explain.t1.a))
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 right join t2 on t1.a = t2.a and t2.a > 1;
|
|
id task access object operator info
|
|
HashJoin root right outer join, left side:TableReader, equal:[eq(expression__explain.t1.a, expression__explain.t2.a)], right cond:gt(expression__explain.t2.a, 1)
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] gt(expression__explain.t1.a, 1), not(isnull(expression__explain.t1.a))
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 right join t2 on t1.a > t2.a and t2.a = 1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN right outer join, left side:TableReader, right cond:eq(expression__explain.t2.a, 1)
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] gt(expression__explain.t1.a, 1)
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a = t2.a and t2.a > 1;
|
|
id task access object operator info
|
|
HashJoin root left outer join, left side:TableReader, equal:[eq(expression__explain.t1.a, expression__explain.t2.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] gt(expression__explain.t2.a, 1), not(isnull(expression__explain.t2.a))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a > t2.a and t2.a = 1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader, other cond:gt(expression__explain.t1.a, expression__explain.t2.a)
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] eq(expression__explain.t2.a, 1)
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 right join t2 on t1.a > t2.a and t1.a = 1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN right outer join, left side:TableReader, other cond:gt(expression__explain.t1.a, expression__explain.t2.a)
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] eq(expression__explain.t1.a, 1)
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 right join t2 on t1.a = t2.a and t1.a > 1;
|
|
id task access object operator info
|
|
HashJoin root right outer join, left side:TableReader, equal:[eq(expression__explain.t1.a, expression__explain.t2.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] gt(expression__explain.t1.a, 1), not(isnull(expression__explain.t1.a))
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a = t1.b and t1.a > 1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader, left cond:[eq(expression__explain.t1.a, expression__explain.t1.b) gt(expression__explain.t1.a, 1)]
|
|
├─TableReader(Build) root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t2.a = t2.b and t2.a > 1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] eq(expression__explain.t2.a, expression__explain.t2.b), gt(expression__explain.t2.a, 1), gt(expression__explain.t2.b, 1)
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on true where t1.a = 1 and false;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t1 left join t2 on true where t1.a = 1 and null;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t1 left join t2 on true where t1.a = null;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t1 left join t2 on true where t1.a = 1 and t1.a = 2;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t1 left join t2 on true where t1.a = 1 and t1.a = 1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] eq(expression__explain.t1.a, 1)
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on false;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader
|
|
├─TableDual(Build) root rows:0
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 right join t2 on false;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN right outer join, left side:TableDual
|
|
├─TableDual(Build) root rows:0
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a = 1 and t1.a = 2;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader
|
|
├─TableDual(Build) root rows:0
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a =1 where t1.a = 2;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader
|
|
├─TableDual(Build) root rows:0
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] eq(expression__explain.t1.a, 2)
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t2.a = 1 and t2.a = 2;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader
|
|
├─TableDual(Build) root rows:0
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a = 1 or (t1.a = 2 and t1.a = 3);
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader, left cond:[or(eq(expression__explain.t1.a, 1), 0)]
|
|
├─TableReader(Build) root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on true where t1.a = 1 or (t1.a = 2 and t1.a = 3);
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] eq(expression__explain.t1.a, 1)
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 where t1.b > 1 or t1.b in (select b from t2);
|
|
id task access object operator info
|
|
Projection root expression__explain.t1.id, expression__explain.t1.a, expression__explain.t1.b
|
|
└─Selection root or(gt(expression__explain.t1.b, 1), Column)
|
|
└─HashJoin root CARTESIAN left outer semi join, left side:TableReader, other cond:eq(expression__explain.t1.b, expression__explain.t2.b)
|
|
├─TableReader(Build) root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t1 left join t2 on t1.a = t2.a where ifnull(t2.b, t1.a) = 1;
|
|
id task access object operator info
|
|
Selection root eq(ifnull(expression__explain.t2.b, expression__explain.t1.a), 1)
|
|
└─HashJoin root left outer join, left side:TableReader, equal:[eq(expression__explain.t1.a, expression__explain.t2.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(expression__explain.t2.a))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
drop table if exists t;
|
|
create table t(id int primary key, a bigint unsigned not null, b bigint unsigned);
|
|
explain format = 'plan_tree' select * from t where a is null;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t where a is not null;
|
|
id task access object operator info
|
|
TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t where a > -1;
|
|
id task access object operator info
|
|
TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t where a <= -1;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t where a < 0;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t where a >= 0;
|
|
id task access object operator info
|
|
TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t where a = -1;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t where a <=> -1;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t where a != -1;
|
|
id task access object operator info
|
|
TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t where 0 > a;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t where 0 <= a;
|
|
id task access object operator info
|
|
TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t where -1 < a;
|
|
id task access object operator info
|
|
TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t where -1 >= a;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t where -1 = a;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t where -1 <=> a;
|
|
id task access object operator info
|
|
TableDual root rows:0
|
|
explain format = 'plan_tree' select * from t where -1 != a;
|
|
id task access object operator info
|
|
TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t where b >= 0;
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] ge(expression__explain.t.b, 0)
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t where b != -1;
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] ne(expression__explain.t.b, -1)
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select * from t where a = 0xFFFFFFFFFFFFFFFF;
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] eq(expression__explain.t.a, 18446744073709551615)
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
drop table if exists t;
|
|
create table t(id int, col1 varchar(10), col2 varchar(10), col3 int, col4 int, col5 int, index key1 (col1, col2, col3, col4), index key2 (col4, col3, col2, col1));
|
|
insert into t values(1,'211111','311',4,5,6),(2,'311111','411',5,6,7),(3,'411111','511',6,7,8),(4,'511111','611',7,8,9),(5,'611111','711',8,9,10);
|
|
explain format = 'plan_tree' select col2, col1 from t use index(key1) where col2 like '5%' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
id task access object operator info
|
|
Projection root expression__explain.t.col2, expression__explain.t.col1
|
|
└─Selection root eq(from_base64(to_base64(substr(expression__explain.t.col1, 1, 1))), "4")
|
|
└─IndexReader root index:Selection
|
|
└─Selection cop[tikv] like(expression__explain.t.col2, "5%", 92)
|
|
└─IndexFullScan cop[tikv] table:t, index:key1(col1, col2, col3, col4) keep order:false, stats:pseudo
|
|
select col2, col1 from t use index(key1) where col2 like '5%' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
col2 col1
|
|
511 411111
|
|
select count(col2) from t use index(key1) where col2 like '5%' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
count(col2)
|
|
1
|
|
explain format = 'plan_tree' select col1, col2 from t use index(key2) where from_base64(to_base64(substr(col2, 1, 1))) = '5' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
id task access object operator info
|
|
Selection root eq(from_base64(to_base64(substr(expression__explain.t.col1, 1, 1))), "4"), eq(from_base64(to_base64(substr(expression__explain.t.col2, 1, 1))), "5")
|
|
└─IndexReader root index:IndexFullScan
|
|
└─IndexFullScan cop[tikv] table:t, index:key2(col4, col3, col2, col1) keep order:false, stats:pseudo
|
|
select col1, col2 from t use index(key2) where from_base64(to_base64(substr(col2, 1, 1))) = '5' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
col1 col2
|
|
411111 511
|
|
select count(col1) from t use index(key2) where from_base64(to_base64(substr(col2, 1, 1))) = '5' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
count(col1)
|
|
1
|
|
explain format = 'plan_tree' select id from t use index(key1) where col2 like '5%' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
id task access object operator info
|
|
Projection root expression__explain.t.id
|
|
└─Selection root eq(from_base64(to_base64(substr(expression__explain.t.col1, 1, 1))), "4")
|
|
└─IndexLookUp root
|
|
├─Selection(Build) cop[tikv] like(expression__explain.t.col2, "5%", 92)
|
|
│ └─IndexFullScan cop[tikv] table:t, index:key1(col1, col2, col3, col4) keep order:false, stats:pseudo
|
|
└─TableRowIDScan(Probe) cop[tikv] table:t keep order:false, stats:pseudo
|
|
select id from t use index(key1) where col2 like '5%' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
id
|
|
3
|
|
select count(id) from t use index(key1) where col2 like '5%' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
count(id)
|
|
1
|
|
explain format = 'plan_tree' select id from t use index(key2) where from_base64(to_base64(substr(col2, 1, 1))) = '5' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
id task access object operator info
|
|
Projection root expression__explain.t.id
|
|
└─Selection root eq(from_base64(to_base64(substr(expression__explain.t.col1, 1, 1))), "4"), eq(from_base64(to_base64(substr(expression__explain.t.col2, 1, 1))), "5")
|
|
└─IndexLookUp root
|
|
├─IndexFullScan(Build) cop[tikv] table:t, index:key2(col4, col3, col2, col1) keep order:false, stats:pseudo
|
|
└─TableRowIDScan(Probe) cop[tikv] table:t keep order:false, stats:pseudo
|
|
select id from t use index(key2) where from_base64(to_base64(substr(col2, 1, 1))) = '5' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
id
|
|
3
|
|
select count(id) from t use index(key2) where from_base64(to_base64(substr(col2, 1, 1))) = '5' and from_base64(to_base64(substr(col1, 1, 1))) = '4';
|
|
count(id)
|
|
1
|
|
drop table if exists reg;
|
|
create table reg(a varchar(20) null,b varchar(20) null,rep varchar(20) null) charset=utf8mb4 collate=utf8mb4_general_ci;
|
|
explain format = 'plan_tree' select a from reg where regexp_like(a, b);
|
|
id task access object operator info
|
|
TableReader root data:Projection
|
|
└─Projection cop[tikv] expression__explain.reg.a
|
|
└─Selection cop[tikv] regexp_like(expression__explain.reg.a, expression__explain.reg.b)
|
|
└─TableFullScan cop[tikv] table:reg keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select a from reg where regexp_instr(a, b);
|
|
id task access object operator info
|
|
TableReader root data:Projection
|
|
└─Projection cop[tikv] expression__explain.reg.a
|
|
└─Selection cop[tikv] regexp_instr(expression__explain.reg.a, expression__explain.reg.b)
|
|
└─TableFullScan cop[tikv] table:reg keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select a from reg where regexp_substr(a, b);
|
|
id task access object operator info
|
|
TableReader root data:Projection
|
|
└─Projection cop[tikv] expression__explain.reg.a
|
|
└─Selection cop[tikv] regexp_substr(expression__explain.reg.a, expression__explain.reg.b)
|
|
└─TableFullScan cop[tikv] table:reg keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select a from reg where regexp_replace(a, b, rep);
|
|
id task access object operator info
|
|
TableReader root data:Projection
|
|
└─Projection cop[tikv] expression__explain.reg.a
|
|
└─Selection cop[tikv] regexp_replace(expression__explain.reg.a, expression__explain.reg.b, expression__explain.reg.rep)
|
|
└─TableFullScan cop[tikv] table:reg keep order:false, stats:pseudo
|
|
drop table if exists regbin;
|
|
create table regbin(a varchar(20) null,b varchar(20) null,rep varchar(20) null) charset=binary collate=binary;
|
|
explain format = 'plan_tree' select a from regbin where regexp_like(a, b);
|
|
id task access object operator info
|
|
Projection root expression__explain.regbin.a
|
|
└─Selection root regexp_like(expression__explain.regbin.a, expression__explain.regbin.b)
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:regbin keep order:false, stats:pseudo
|