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="" # test generate column substitution # substitute where desc select * from t where a+1=3; select * from t where a+1=3; desc select a+1 from t where a+1=3; select a+1 from t where a+1=3; desc select c from t where a+1=3; select c from t where a+1=3; desc select * from t where b+a=3; select * from t where b+a=3; desc select b+a from t where b+a=3; select b+a from t where b+a=3; desc select e from t where b+a=3; select e from t where b+a=3; desc select a+1 from t where a+1 in (1, 2, 3); select a+1 from t where a+1 in (1, 2, 3); desc select * from t where a+1 in (1, 2, 3); select * from t where a+1 in (1, 2, 3); desc select a+1 from t where a+1 between 1 and 4; select a+1 from t where a+1 between 1 and 4; desc select * from t where a+1 between 1 and 4; select * from t where a+1 between 1 and 4; # substitute group by # uncomment these test case after we support virtual generate column push down #desc select * from t group by a+1; #select * from t group by a+1; #desc select a+1 from t group by a+1; #select a+1 from t group by a+1; #desc select b, avg(a+1) from t group by a+1; #select b, avg(a+1) from t group by a+1; #desc select count(a+1), max(a+1) from t group by a+1; #select count(a+1), max(a+1) from t group by a+1; #desc select * from t group by b+a; #select * from t group by b+a; #desc select b+a from t group by b+a; #select b+a from t group by b+a; #desc select b, avg(b+a) from t group by b+a; #select b, avg(b+a) from t group by b+a; #desc select count(b+a), max(b+a) from t group by b+a; #select count(b+a), max(b+a) from t group by b+a; # substitute order by desc select * from t order by a+1; select * from t order by a+1; desc select a+1 from t order by a+1; select a+1 from t order by a+1; desc select b+a from t order by b+a; select b+a from t order by b+a; # test update desc update t set a=1 where a+1 = 3; desc update t set a=2, b = 3 where b+a = 3; # test delete desc delete from t where a+1 = 3; desc delete from t where b+a = 0; # test expression index substitution 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); # substitute where desc select * from t where a+1=3; select * from t where a+1=3; desc select a+1 from t where a+1=3; select a+1 from t where a+1=3; desc select c from t where a+1=3; select c from t where a+1=3; desc select * from t where b+a=3; select * from t where b+a=3; desc select b+a from t where b+a=3; select b+a from t where b+a=3; desc select e from t where b+a=3; select e from t where b+a=3; desc select a+1 from t where a+1 in (1, 2, 3); select a+1 from t where a+1 in (1, 2, 3); desc select * from t where a+1 in (1, 2, 3); select * from t where a+1 in (1, 2, 3); desc select a+1 from t where a+1 between 1 and 4; select a+1 from t where a+1 between 1 and 4; desc select * from t where a+1 between 1 and 4; select * from t where a+1 between 1 and 4; # substitute group by # uncomment these test case after we support virtual generate column push down #desc select * from t group by a+1; #select * from t group by a+1; #desc select a+1 from t group by a+1; #select a+1 from t group by a+1; #desc select b, avg(a+1) from t group by a+1; #select b, avg(a+1) from t group by a+1; #desc select count(a+1), max(a+1) from t group by a+1; #select count(a+1), max(a+1) from t group by a+1; #desc select * from t group by b+a; #select * from t group by b+a; #desc select b+a from t group by b+a; #select b+a from t group by b+a; #desc select b, avg(b+a) from t group by b+a; #select b, avg(b+a) from t group by b+a; #desc select count(b+a), max(b+a) from t group by b+a; #select count(b+a), max(b+a) from t group by b+a; # substitute order by desc select * from t order by a+1; select * from t order by a+1; desc select a+1 from t order by a+1; select a+1 from t order by a+1; desc select b+a from t order by b+a; select b+a from t order by b+a; # test update desc update t set a=1 where a+1 = 3; desc update t set a=2, b = 3 where b+a = 3; # test delete desc delete from t where a+1 = 3; desc delete from t where b+a = 0; # test different column type 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; select * from t; 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; select * from t; 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; select * from t; 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; select c0+1 from t; 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; select c1 from t where 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; select c0+1 from t; 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; select c0+1 from t; 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; select * from t; 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; select * from t; 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; select * from t; 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; select * from t; 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; select * from t; 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; drop table t; create table t(c0 char(10), c1 binary(10) as (c0) unique); select c0 from t use index(); desc select c0 from t use index(); select hex(c0) from (select c0 from t use index()) tt; desc select hex(c0) from (select c0 from t use index()) tt; 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; select * from t;