github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/view/view-from-subquery.sql (about) 1 2 -- @suite 3 -- @setup 4 drop table if exists t1; 5 create table t1 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime); 6 insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11'); 7 insert into t1 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11'); 8 insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11'); 9 insert into t1 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11'); 10 insert into t1 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11'); 11 insert into t1 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11'); 12 insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11'); 13 insert into t1 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11'); 14 insert into t1 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11'); 15 16 drop table if exists t2; 17 create table t2 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime); 18 insert into t2 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11'); 19 insert into t2 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11'); 20 insert into t2 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11'); 21 insert into t2 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11'); 22 insert into t2 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11'); 23 insert into t2 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11'); 24 insert into t2 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11'); 25 insert into t2 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11'); 26 insert into t2 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11'); 27 28 -- @case 29 -- @desc:test for from subquery 30 -- @label:bvt 31 32 select * from (select * from t1) sub where id > 4; 33 select ti as t,fl as f from (select * from t1) sub where dl <> 4; 34 select * from (select ti as t,fl as f from t1 where dl <> 4) sub; 35 36 select id,min(ti) from (select * from t1) sub group by id; 37 select * from (select id,min(ti) from (select * from t1) t1 group by id) sub; 38 39 create view v1 as select * from (select * from t1) sub where id > 4; 40 create view v2 as select ti as t,fl as f from (select * from t1) sub where dl <> 4; 41 create view v3 as select * from (select ti as t,fl as f from t1 where dl <> 4) sub; 42 create view v4 as select id,min(ti) from (select * from t1) sub group by id; 43 create view v5 as select * from (select id,min(ti) from (select * from t1) t1 group by id) sub; 44 select * from v1; 45 select * from v2; 46 select * from v3; 47 select * from v4; 48 select * from v5; 49 50 drop view v1; 51 drop view v2; 52 drop view v3; 53 drop view v4; 54 drop view v5; 55 56 --待确认 57 --select id,min(ti) from (select * from t1) sub order by id desc; 58 --select * from (select id,min(ti) from t1 sub order by id desc) sub; 59 60 select id,min(ti) from (select * from t1) sub group by id order by id desc; 61 select id,sum(ti) from (select * from t1) sub group by id; 62 63 select distinct(ti) from (select * from t1) sub; 64 select distinct(ti) from (select * from t1) sub where id <6; 65 66 create view v1 as select id,min(ti) from (select * from t1) sub group by id order by id desc; 67 create view v2 as select id,sum(ti) from (select * from t1) sub group by id; 68 create view v3 as select distinct(ti) from (select * from t1) sub; 69 create view v4 as select distinct(ti) from (select * from t1) sub where id <6; 70 select * from v1; 71 select * from v2; 72 select * from v3; 73 select * from v4; 74 75 drop view v1; 76 drop view v2; 77 drop view v3; 78 drop view v4; 79 80 81 -- mysql 不同，待确认 82 -- select distinct(ti),de from (select * from t1) sub where id < 6 order by ti asc; 83 84 select count(*) from (select * from t1) sub where id > 4 ; 85 select * from (select * from t1) sub where id > 1 limit 3; 86 select max(ti),min(si),avg(fl) from (select * from t1) sub where id < 4 || id > 5; 87 select max(ti)+10,min(si)-1,avg(fl) from (select * from t1) sub where id < 4 || id > 5; 88 89 select substr from (select * from t1) sub where id < 4 || id > 5; 90 91 select ti,-si from (select * from t1) sub order by -si desc; 92 93 select * from (select * from t1) sub where (ti=2 or si=3) and (ch = 'bye' or vch = 'subquery'); 94 95 select * from (select * from (select * from (select id,ti,si,de from (select * from t1 ) sub3 where fl <> 4.5 ) sub2 where ti > 1) sub1 where id >2 ) sub where id > 4; 96 97 select * from (select * from t1 where id > 100) sub ; 98 99 create view v1 as select count(*) from (select * from t1) sub where id > 4 ; 100 create view v2 as select * from (select * from t1) sub where id > 1 limit 3; 101 create view v3 as select max(ti),min(si),avg(fl) from (select * from t1) sub where id < 4 || id > 5; 102 create view v4 as select max(ti)+10,min(si)-1,avg(fl) from (select * from t1) sub where id < 4 || id > 5; 103 create view v5 as select substr from (select * from t1) sub where id < 4 || id > 5; 104 create view v6 as select ti,-si from (select * from t1) sub order by -si desc; 105 create view v7 as select * from (select * from t1) sub where (ti=2 or si=3) and (ch = 'bye' or vch = 'subquery'); 106 create view v8 as select * from (select * from (select * from (select id,ti,si,de from (select * from t1 ) sub3 where fl <> 4.5 ) sub2 where ti > 1) sub1 where id >2 ) sub where id > 4; 107 create view v9 as select * from (select * from t1 where id > 100) sub ; 108 109 select * from v1; 110 select * from v2; 111 select * from v3; 112 select * from v4; 113 select * from v5; 114 select * from v6; 115 select * from v7; 116 select * from v8; 117 select * from v9; 118 119 drop view v1; 120 drop view v2; 121 drop view v3; 122 drop view v4; 123 drop view v5; 124 drop view v6; 125 drop view v7; 126 drop view v8; 127 drop view v9; 128 129 130 -- @suite 131 -- @setup 132 drop table if exists t1; 133 drop table if exists t2; 134 drop table if exists t3; 135 create table t1 (libname1 varchar(21) not null primary key, city varchar(20)); 136 create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60)); 137 create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int); 138 insert into t2 values ('001','Daffy','Aducklife'); 139 insert into t2 values ('002','Bugs','Arabbitlife'); 140 insert into t2 values ('003','Cowboy','Lifeontherange'); 141 insert into t2 values ('000','Anonymous','Wannabuythisbook?'); 142 insert into t2 values ('004','BestSeller','OneHeckuvabook'); 143 insert into t2 values ('005','EveryoneBuys','Thisverybook'); 144 insert into t2 values ('006','SanFran','Itisasanfranlifestyle'); 145 insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book'); 146 insert into t3 values('000','NewYorkPublicLibra',1); 147 insert into t3 values('001','NewYorkPublicLibra',2); 148 insert into t3 values('002','NewYorkPublicLibra',3); 149 insert into t3 values('003','NewYorkPublicLibra',4); 150 insert into t3 values('004','NewYorkPublicLibra',5); 151 insert into t3 values('005','NewYorkPublicLibra',6); 152 insert into t3 values('006','SanFransiscoPublic',5); 153 insert into t3 values('007','BerkeleyPublic1',3); 154 insert into t3 values('007','BerkeleyPublic2',3); 155 insert into t3 values('001','NYC Lib',8); 156 insert into t1 values ('NewYorkPublicLibra','NewYork'); 157 insert into t1 values ('SanFransiscoPublic','SanFran'); 158 insert into t1 values ('BerkeleyPublic1','Berkeley'); 159 insert into t1 values ('BerkeleyPublic2','Berkeley'); 160 insert into t1 values ('NYCLib','NewYork'); 161 -- @case 162 -- @desc:test for from subquery with join 163 -- @label:bvt 164 select * from (select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1) sub ; 165 create view v1 as select * from (select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1) sub ; 166 select * from v1; 167 drop view v1; 168 169 drop table if exists t1; 170 drop table if exists t2; 171 drop table if exists t3; 172 drop table if exists t4; 173 drop view if exists v1; 174 drop view if exists v2; 175 create table t1 (col1 varchar(20), col2 char(20)); 176 create table t2 (col1 varchar(64), col2 char(64)); 177 create table t3 (col1 varchar(20), col2 char(20)); 178 create table t4 (col1 varchar(20), col2 char(20)); 179 create view v1 as select t1.col1 as col1, t1.col2 as col2 from t1 union select t2.col1 as col1, t2.col2 as col2 from t2; 180 desc v1; 181 create view v2 as select t3.col1 as col1, t3.col2 as col2 from t3 union select t4.col1 as col1, t4.col2 as col2 from t4; 182 desc v2; 183 drop view v1; 184 drop view v2; 185 drop table t1; 186 drop table t2; 187 drop table t3; 188 drop table t4;