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
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;
|
|
|