You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 

224 lines
7.0 KiB

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;