use test; drop table if exists t; create table t(a int, b real, c int as ((a+1)) virtual, e real as ((b+a))); insert into t values (1, 2.0, default, default), (2, 2.1, default, default), (5, 3.0, default, default), (5, -1.0, default, default), (0, 0.0, default, default), (-1, -2.0, default, default), (0, 0, default, default); alter table t add index idx_c(c); alter table t add index idx_e(e); set @@sql_mode="" desc select * from t where a+1=3; id estRows task access object operator info IndexLookUp_10 10.00 root ├─IndexRangeScan_8(Build) 10.00 cop[tikv] table:t, index:idx_c(c) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_9(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t where a+1=3; a b c e 2 2.1 3 4.1 desc select a+1 from t where a+1=3; id estRows task access object operator info IndexReader_6 10.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 10.00 cop[tikv] table:t, index:idx_c(c) range:[3,3], keep order:false, stats:pseudo select a+1 from t where a+1=3; a+1 3 desc select c from t where a+1=3; id estRows task access object operator info IndexReader_6 10.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 10.00 cop[tikv] table:t, index:idx_c(c) range:[3,3], keep order:false, stats:pseudo select c from t where a+1=3; c 3 desc select * from t where b+a=3; id estRows task access object operator info IndexLookUp_10 10.00 root ├─IndexRangeScan_8(Build) 10.00 cop[tikv] table:t, index:idx_e(e) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_9(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t where b+a=3; a b c e 1 2 2 3 desc select b+a from t where b+a=3; id estRows task access object operator info IndexReader_6 10.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 10.00 cop[tikv] table:t, index:idx_e(e) range:[3,3], keep order:false, stats:pseudo select b+a from t where b+a=3; b+a 3 desc select e from t where b+a=3; id estRows task access object operator info IndexReader_6 10.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 10.00 cop[tikv] table:t, index:idx_e(e) range:[3,3], keep order:false, stats:pseudo select e from t where b+a=3; e 3 desc select a+1 from t where a+1 in (1, 2, 3); id estRows task access object operator info IndexReader_6 30.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 30.00 cop[tikv] table:t, index:idx_c(c) range:[1,1], [2,2], [3,3], keep order:false, stats:pseudo select a+1 from t where a+1 in (1, 2, 3); a+1 1 1 2 3 desc select * from t where a+1 in (1, 2, 3); id estRows task access object operator info IndexLookUp_10 30.00 root ├─IndexRangeScan_8(Build) 30.00 cop[tikv] table:t, index:idx_c(c) range:[1,1], [2,2], [3,3], keep order:false, stats:pseudo └─TableRowIDScan_9(Probe) 30.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t where a+1 in (1, 2, 3); a b c e 1 2 2 3 2 2.1 3 4.1 0 0 1 0 0 0 1 0 desc select a+1 from t where a+1 between 1 and 4; id estRows task access object operator info IndexReader_6 250.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 250.00 cop[tikv] table:t, index:idx_c(c) range:[1,4], keep order:false, stats:pseudo select a+1 from t where a+1 between 1 and 4; a+1 1 1 2 3 desc select * from t where a+1 between 1 and 4; id estRows task access object operator info IndexLookUp_10 250.00 root ├─IndexRangeScan_8(Build) 250.00 cop[tikv] table:t, index:idx_c(c) range:[1,4], keep order:false, stats:pseudo └─TableRowIDScan_9(Probe) 250.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t where a+1 between 1 and 4; a b c e 1 2 2 3 2 2.1 3 4.1 0 0 1 0 0 0 1 0 desc select * from t order by a+1; id estRows task access object operator info Projection_13 10000.00 root test.t.a, test.t.b, test.t.c, test.t.e └─IndexLookUp_12 10000.00 root ├─IndexFullScan_10(Build) 10000.00 cop[tikv] table:t, index:idx_c(c) keep order:true, stats:pseudo └─TableRowIDScan_11(Probe) 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t order by a+1; a b c e -1 -2 0 -3 0 0 1 0 0 0 1 0 1 2 2 3 2 2.1 3 4.1 5 3 6 8 5 -1 6 4 desc select a+1 from t order by a+1; id estRows task access object operator info IndexReader_13 10000.00 root index:IndexFullScan_12 └─IndexFullScan_12 10000.00 cop[tikv] table:t, index:idx_c(c) keep order:true, stats:pseudo select a+1 from t order by a+1; a+1 0 1 1 2 3 6 6 desc select b+a from t order by b+a; id estRows task access object operator info IndexReader_13 10000.00 root index:IndexFullScan_12 └─IndexFullScan_12 10000.00 cop[tikv] table:t, index:idx_e(e) keep order:true, stats:pseudo select b+a from t order by b+a; b+a -3 0 0 3 4 4.1 8 desc update t set a=1 where a+1 = 3; id estRows task access object operator info Update_4 N/A root N/A └─IndexLookUp_11 10.00 root ├─IndexRangeScan_9(Build) 10.00 cop[tikv] table:t, index:idx_c(c) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_10(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo desc update t set a=2, b = 3 where b+a = 3; id estRows task access object operator info Update_4 N/A root N/A └─IndexLookUp_11 10.00 root ├─IndexRangeScan_9(Build) 10.00 cop[tikv] table:t, index:idx_e(e) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_10(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo desc delete from t where a+1 = 3; id estRows task access object operator info Delete_4 N/A root N/A └─IndexLookUp_11 10.00 root ├─IndexRangeScan_9(Build) 10.00 cop[tikv] table:t, index:idx_c(c) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_10(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo desc delete from t where b+a = 0; id estRows task access object operator info Delete_4 N/A root N/A └─IndexLookUp_11 10.00 root ├─IndexRangeScan_9(Build) 10.00 cop[tikv] table:t, index:idx_e(e) range:[0,0], keep order:false, stats:pseudo └─TableRowIDScan_10(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo alter table t drop index idx_c; alter table t drop index idx_e; alter table t add index expr_idx_c((a+1)); alter table t add index expr_idx_e((b+a)); truncate table t; insert into t values (1, 2.0, default, default), (2, 2.1, default, default), (5, 3.0, default, default), (5, -1.0, default, default), (0, 0.0, default, default), (-1, -2.0, default, default), (0, 0, default, default); desc select * from t where a+1=3; id estRows task access object operator info Projection_4 10.00 root test.t.a, test.t.b, test.t.c, test.t.e └─IndexLookUp_10 10.00 root ├─IndexRangeScan_8(Build) 10.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_9(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t where a+1=3; a b c e 2 2.1 3 4.1 desc select a+1 from t where a+1=3; id estRows task access object operator info IndexReader_6 10.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 10.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) range:[3,3], keep order:false, stats:pseudo select a+1 from t where a+1=3; a+1 3 desc select c from t where a+1=3; id estRows task access object operator info Projection_4 10.00 root test.t.c └─IndexLookUp_10 10.00 root ├─IndexRangeScan_8(Build) 10.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_9(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo select c from t where a+1=3; c 3 desc select * from t where b+a=3; id estRows task access object operator info Projection_4 10.00 root test.t.a, test.t.b, test.t.c, test.t.e └─IndexLookUp_10 10.00 root ├─IndexRangeScan_8(Build) 10.00 cop[tikv] table:t, index:expr_idx_e(`b` + `a`) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_9(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t where b+a=3; a b c e 1 2 2 3 desc select b+a from t where b+a=3; id estRows task access object operator info IndexReader_6 10.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 10.00 cop[tikv] table:t, index:expr_idx_e(`b` + `a`) range:[3,3], keep order:false, stats:pseudo select b+a from t where b+a=3; b+a 3 desc select e from t where b+a=3; id estRows task access object operator info Projection_4 10.00 root test.t.e └─IndexLookUp_10 10.00 root ├─IndexRangeScan_8(Build) 10.00 cop[tikv] table:t, index:expr_idx_e(`b` + `a`) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_9(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo select e from t where b+a=3; e 3 desc select a+1 from t where a+1 in (1, 2, 3); id estRows task access object operator info IndexReader_6 30.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 30.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) range:[1,1], [2,2], [3,3], keep order:false, stats:pseudo select a+1 from t where a+1 in (1, 2, 3); a+1 1 1 2 3 desc select * from t where a+1 in (1, 2, 3); id estRows task access object operator info Projection_4 30.00 root test.t.a, test.t.b, test.t.c, test.t.e └─IndexLookUp_10 30.00 root ├─IndexRangeScan_8(Build) 30.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) range:[1,1], [2,2], [3,3], keep order:false, stats:pseudo └─TableRowIDScan_9(Probe) 30.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t where a+1 in (1, 2, 3); a b c e 1 2 2 3 2 2.1 3 4.1 0 0 1 0 0 0 1 0 desc select a+1 from t where a+1 between 1 and 4; id estRows task access object operator info IndexReader_6 250.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 250.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) range:[1,4], keep order:false, stats:pseudo select a+1 from t where a+1 between 1 and 4; a+1 1 1 2 3 desc select * from t where a+1 between 1 and 4; id estRows task access object operator info Projection_4 250.00 root test.t.a, test.t.b, test.t.c, test.t.e └─IndexLookUp_10 250.00 root ├─IndexRangeScan_8(Build) 250.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) range:[1,4], keep order:false, stats:pseudo └─TableRowIDScan_9(Probe) 250.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t where a+1 between 1 and 4; a b c e 1 2 2 3 2 2.1 3 4.1 0 0 1 0 0 0 1 0 desc select * from t order by a+1; id estRows task access object operator info Projection_5 10000.00 root test.t.a, test.t.b, test.t.c, test.t.e └─Projection_13 10000.00 root test.t.a, test.t.b, test.t.c, test.t.e, EMPTY_NAME └─IndexLookUp_12 10000.00 root ├─IndexFullScan_10(Build) 10000.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) keep order:true, stats:pseudo └─TableRowIDScan_11(Probe) 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t order by a+1; a b c e -1 -2 0 -3 0 0 1 0 0 0 1 0 1 2 2 3 2 2.1 3 4.1 5 3 6 8 5 -1 6 4 desc select a+1 from t order by a+1; id estRows task access object operator info IndexReader_13 10000.00 root index:IndexFullScan_12 └─IndexFullScan_12 10000.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) keep order:true, stats:pseudo select a+1 from t order by a+1; a+1 0 1 1 2 3 6 6 desc select b+a from t order by b+a; id estRows task access object operator info IndexReader_13 10000.00 root index:IndexFullScan_12 └─IndexFullScan_12 10000.00 cop[tikv] table:t, index:expr_idx_e(`b` + `a`) keep order:true, stats:pseudo select b+a from t order by b+a; b+a -3 0 0 3 4 4.1 8 desc update t set a=1 where a+1 = 3; id estRows task access object operator info Update_4 N/A root N/A └─IndexLookUp_11 10.00 root ├─IndexRangeScan_9(Build) 10.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_10(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo desc update t set a=2, b = 3 where b+a = 3; id estRows task access object operator info Update_4 N/A root N/A └─IndexLookUp_11 10.00 root ├─IndexRangeScan_9(Build) 10.00 cop[tikv] table:t, index:expr_idx_e(`b` + `a`) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_10(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo desc delete from t where a+1 = 3; id estRows task access object operator info Delete_4 N/A root N/A └─IndexLookUp_11 10.00 root ├─IndexRangeScan_9(Build) 10.00 cop[tikv] table:t, index:expr_idx_c(`a` + 1) range:[3,3], keep order:false, stats:pseudo └─TableRowIDScan_10(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo desc delete from t where b+a = 0; id estRows task access object operator info Delete_4 N/A root N/A └─IndexLookUp_11 10.00 root ├─IndexRangeScan_9(Build) 10.00 cop[tikv] table:t, index:expr_idx_e(`b` + `a`) range:[0,0], keep order:false, stats:pseudo └─TableRowIDScan_10(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo drop table if exists t; create table t(c0 char as (c1), c1 int); insert into t(c1) values (0), (1); desc select * from t; id estRows task access object operator info TableReader_5 10000.00 root data:TableFullScan_4 └─TableFullScan_4 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t; c0 c1 0 0 1 1 drop table if exists t; create table t(c0 int as (c1) unique, c1 int); insert into t(c1) values (0), (1); desc select * from t; id estRows task access object operator info Projection_3 10000.00 root test.t.c0, test.t.c0 └─IndexReader_7 10000.00 root index:IndexFullScan_6 └─IndexFullScan_6 10000.00 cop[tikv] table:t, index:c0(c0) keep order:false, stats:pseudo select * from t; c0 c1 0 0 1 1 drop table if exists t; create table t(c0 char as (c1) unique, c1 int); insert into t(c1) values (0), (1); desc select * from t; id estRows task access object operator info TableReader_5 10000.00 root data:TableFullScan_4 └─TableFullScan_4 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t; c0 c1 0 0 1 1 drop table if exists t; create table t(c0 int, c1 int as (c0+1) unique); insert into t(c0) values (1),(2); explain select c0+1 from t; id estRows task access object operator info IndexReader_7 10000.00 root index:IndexFullScan_6 └─IndexFullScan_6 10000.00 cop[tikv] table:t, index:c1(c1) keep order:false, stats:pseudo select c0+1 from t; c0+1 2 3 drop table if exists t; create table t(c0 int, c1 int as (c0) unique); insert into t(c0) values (1),(2); explain select c1 from t where c1 = 2; id estRows task access object operator info Projection_4 1.00 root test.t.c1 └─Point_Get_5 1.00 root table:t, index:c1(c1) select c1 from t where c1 = 2; c1 2 drop table if exists t; create table t(c0 double, c1 float as (c0+1) unique); insert into t(c0) values (1.1),(2.2); explain select c0+1 from t; id estRows task access object operator info Projection_3 10000.00 root plus(test.t.c0, 1)->Column#4 └─TableReader_5 10000.00 root data:TableFullScan_4 └─TableFullScan_4 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select c0+1 from t; c0+1 2.1 3.2 drop table if exists t; create table t(c0 float, c1 float as (c0+1) unique); insert into t(c0) values (1.1),(2.2); explain select c0+1 from t; id estRows task access object operator info Projection_3 10000.00 root plus(test.t.c0, 1)->Column#4 └─TableReader_5 10000.00 root data:TableFullScan_4 └─TableFullScan_4 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select c0+1 from t; c0+1 2.100000023841858 3.200000047683716 drop table if exists t; create table t(c0 int, c1 tinyint as (c0) unique); insert into t(c0) values (1),(127); desc select * from t; id estRows task access object operator info Projection_3 10000.00 root test.t.c1, test.t.c1 └─IndexReader_7 10000.00 root index:IndexFullScan_6 └─IndexFullScan_6 10000.00 cop[tikv] table:t, index:c1(c1) keep order:false, stats:pseudo select * from t; c0 c1 1 1 127 127 drop table if exists t; create table t(c0 int, c1 year as (c0) unique); insert into t(c0) values (48); desc select * from t; id estRows task access object operator info TableReader_5 10000.00 root data:TableFullScan_4 └─TableFullScan_4 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t; c0 c1 48 2048 drop table t; create table t(c0 varchar(10), c1 char(10) as (c0) unique); insert into t(c0) values ("a "); desc select * from t; id estRows task access object operator info TableReader_5 10000.00 root data:TableFullScan_4 └─TableFullScan_4 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t; c0 c1 a a drop table if exists t; create table t(c0 timestamp, c1 date as (c0) unique); insert into t(c0) values('2038-01-19 03:14:07.999999'); desc select * from t; id estRows task access object operator info TableReader_5 10000.00 root data:TableFullScan_4 └─TableFullScan_4 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t; c0 c1 2038-01-19 03:14:08 2038-01-19 drop table t; create table t(c0 decimal(5,3), c1 decimal(5,2) as (c0) unique); insert into t(c0) values (3.1415926); desc select * from t; id estRows task access object operator info TableReader_5 10000.00 root data:TableFullScan_4 └─TableFullScan_4 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t; c0 c1 3.142 3.14 drop table t; create table t(c0 char(10), c1 binary(10) as (c0) unique); select hex(c0) from (select c0 from t use index()) tt; hex(c0) drop table t; create table t(c0 char(10), c1 binary(10) as (c0) unique); select c0 from t use index(); c0 desc select c0 from t use index(); id estRows task access object operator info TableReader_5 10000.00 root data:TableFullScan_4 └─TableFullScan_4 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select hex(c0) from (select c0 from t use index()) tt; hex(c0) desc select hex(c0) from (select c0 from t use index()) tt; id estRows task access object operator info Projection_4 10000.00 root hex(test.t.c0)->Column#4 └─TableReader_6 10000.00 root data:TableFullScan_5 └─TableFullScan_5 10000.00 cop[tikv] table:t keep order:false, stats:pseudo drop table t; create table t(a enum('1', '2', '3'), b enum('a', 'b', 'c') as (a) unique); insert into t(a) values ('1'); desc select * from t; id estRows task access object operator info TableReader_5 10000.00 root data:TableFullScan_4 └─TableFullScan_4 10000.00 cop[tikv] table:t keep order:false, stats:pseudo select * from t; a b 1 a