github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/view/view-from-subquery.result (about) 1 drop table if exists t1; 2 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); 3 insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11'); 4 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'); 5 insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11'); 6 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'); 7 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'); 8 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'); 9 insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11'); 10 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'); 11 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'); 12 drop table if exists t2; 13 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); 14 insert into t2 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11'); 15 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'); 16 insert into t2 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11'); 17 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'); 18 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'); 19 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'); 20 insert into t2 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11'); 21 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'); 22 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'); 23 select * from (select * from t1) sub where id > 4; 24 id ti si bi fl dl de ch vch dd dt 25 5 1 2 6 51.26 5126.0 51 byebye is subquery? 2022-04-28 2022-04-28 22:40:11 26 6 3 2 1 632.1 6321.0 632 good night maybe subquery 2022-04-28 2022-04-28 22:40:11 27 7 4 4 3 7443.11 744311.0 7443 yes subquery 2022-04-28 2022-04-28 22:40:11 28 8 7 5 8 8758.0 875800.0 8758 nice to meet just subquery 2022-04-28 2022-04-28 22:40:11 29 9 8 4 9 9849.312 9849312.0 9849 see you subquery 2022-04-28 2022-04-28 22:40:11 30 select ti as t,fl as f from (select * from t1) sub where dl <> 4; 31 t f 32 1 1113.32 33 2 2252.05 34 6 3663.21 35 7 4715.22 36 1 51.26 37 3 632.1 38 4 7443.11 39 7 8758.0 40 8 9849.312 41 select * from (select ti as t,fl as f from t1 where dl <> 4) sub; 42 t f 43 1 1113.32 44 2 2252.05 45 6 3663.21 46 7 4715.22 47 1 51.26 48 3 632.1 49 4 7443.11 50 7 8758.0 51 8 9849.312 52 select id,min(ti) from (select * from t1) sub group by id; 53 id min(ti) 54 1 1 55 2 2 56 3 6 57 4 7 58 5 1 59 6 3 60 7 4 61 8 7 62 9 8 63 select * from (select id,min(ti) from (select * from t1) t1 group by id) sub; 64 id min(ti) 65 1 1 66 2 2 67 3 6 68 4 7 69 5 1 70 6 3 71 7 4 72 8 7 73 9 8 74 create view v1 as select * from (select * from t1) sub where id > 4; 75 create view v2 as select ti as t,fl as f from (select * from t1) sub where dl <> 4; 76 create view v3 as select * from (select ti as t,fl as f from t1 where dl <> 4) sub; 77 create view v4 as select id,min(ti) from (select * from t1) sub group by id; 78 create view v5 as select * from (select id,min(ti) from (select * from t1) t1 group by id) sub; 79 select * from v1; 80 id ti si bi fl dl de ch vch dd dt 81 5 1 2 6 51.26 5126.0 51 byebye is subquery? 2022-04-28 2022-04-28 22:40:11 82 6 3 2 1 632.1 6321.0 632 good night maybe subquery 2022-04-28 2022-04-28 22:40:11 83 7 4 4 3 7443.11 744311.0 7443 yes subquery 2022-04-28 2022-04-28 22:40:11 84 8 7 5 8 8758.0 875800.0 8758 nice to meet just subquery 2022-04-28 2022-04-28 22:40:11 85 9 8 4 9 9849.312 9849312.0 9849 see you subquery 2022-04-28 2022-04-28 22:40:11 86 select * from v2; 87 t f 88 1 1113.32 89 2 2252.05 90 6 3663.21 91 7 4715.22 92 1 51.26 93 3 632.1 94 4 7443.11 95 7 8758.0 96 8 9849.312 97 select * from v3; 98 t f 99 1 1113.32 100 2 2252.05 101 6 3663.21 102 7 4715.22 103 1 51.26 104 3 632.1 105 4 7443.11 106 7 8758.0 107 8 9849.312 108 select * from v4; 109 id min(ti) 110 1 1 111 2 2 112 3 6 113 4 7 114 5 1 115 6 3 116 7 4 117 8 7 118 9 8 119 select * from v5; 120 id min(ti) 121 1 1 122 2 2 123 3 6 124 4 7 125 5 1 126 6 3 127 7 4 128 8 7 129 9 8 130 drop view v1; 131 drop view v2; 132 drop view v3; 133 drop view v4; 134 drop view v5; 135 select id,min(ti) from (select * from t1) sub group by id order by id desc; 136 id min(ti) 137 9 8 138 8 7 139 7 4 140 6 3 141 5 1 142 4 7 143 3 6 144 2 2 145 1 1 146 select id,sum(ti) from (select * from t1) sub group by id; 147 id sum(ti) 148 1 1 149 2 2 150 3 6 151 4 7 152 5 1 153 6 3 154 7 4 155 8 7 156 9 8 157 select distinct(ti) from (select * from t1) sub; 158 ti 159 1 160 2 161 6 162 7 163 3 164 4 165 8 166 select distinct(ti) from (select * from t1) sub where id <6; 167 ti 168 1 169 2 170 6 171 7 172 create view v1 as select id,min(ti) from (select * from t1) sub group by id order by id desc; 173 create view v2 as select id,sum(ti) from (select * from t1) sub group by id; 174 create view v3 as select distinct(ti) from (select * from t1) sub; 175 create view v4 as select distinct(ti) from (select * from t1) sub where id <6; 176 select * from v1; 177 id min(ti) 178 9 8 179 8 7 180 7 4 181 6 3 182 5 1 183 4 7 184 3 6 185 2 2 186 1 1 187 select * from v2; 188 id sum(ti) 189 1 1 190 2 2 191 3 6 192 4 7 193 5 1 194 6 3 195 7 4 196 8 7 197 9 8 198 select * from v3; 199 ti 200 1 201 2 202 6 203 7 204 3 205 4 206 8 207 select * from v4; 208 ti 209 1 210 2 211 6 212 7 213 drop view v1; 214 drop view v2; 215 drop view v3; 216 drop view v4; 217 select count(*) from (select * from t1) sub where id > 4 ; 218 count(*) 219 5 220 select * from (select * from t1) sub where id > 1 limit 3; 221 id ti si bi fl dl de ch vch dd dt 222 2 2 5 2 2252.05 225205.0 2252 bye sub query 2022-04-28 2022-04-28 22:40:11 223 3 6 6 3 3663.21 366321.0 3663 hi subquery 2022-04-28 2022-04-28 22:40:11 224 4 7 1 5 4715.22 471522.0 4715 good morning my subquery 2022-04-28 2022-04-28 22:40:11 225 select max(ti),min(si),avg(fl) from (select * from t1) sub where id < 4 || id > 5; 226 max(ti) min(si) avg(fl) 227 8 2 4815.871616908482 228 select max(ti)+10,min(si)-1,avg(fl) from (select * from t1) sub where id < 4 || id > 5; 229 max(ti) + 10 min(si) - 1 avg(fl) 230 18 1 4815.871616908482 231 select substr from (select * from t1) sub where id < 4 || id > 5; 232 invalid input: column substr does not exist 233 select ti,-si from (select * from t1) sub order by -si desc; 234 ti -si 235 7 -1 236 1 -2 237 3 -2 238 1 -4 239 4 -4 240 8 -4 241 2 -5 242 7 -5 243 6 -6 244 select * from (select * from t1) sub where (ti=2 or si=3) and (ch = 'bye' or vch = 'subquery'); 245 id ti si bi fl dl de ch vch dd dt 246 2 2 5 2 2252.05 225205.0 2252 bye sub query 2022-04-28 2022-04-28 22:40:11 247 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; 248 id ti si de 249 6 3 2 632 250 7 4 4 7443 251 8 7 5 8758 252 9 8 4 9849 253 select * from (select * from t1 where id > 100) sub ; 254 id ti si bi fl dl de ch vch dd dt 255 create view v1 as select count(*) from (select * from t1) sub where id > 4 ; 256 create view v2 as select * from (select * from t1) sub where id > 1 limit 3; 257 create view v3 as select max(ti),min(si),avg(fl) from (select * from t1) sub where id < 4 || id > 5; 258 create view v4 as select max(ti)+10,min(si)-1,avg(fl) from (select * from t1) sub where id < 4 || id > 5; 259 create view v5 as select substr from (select * from t1) sub where id < 4 || id > 5; 260 invalid input: column substr does not exist 261 create view v6 as select ti,-si from (select * from t1) sub order by -si desc; 262 create view v7 as select * from (select * from t1) sub where (ti=2 or si=3) and (ch = 'bye' or vch = 'subquery'); 263 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; 264 create view v9 as select * from (select * from t1 where id > 100) sub ; 265 select * from v1; 266 count(*) 267 5 268 select * from v2; 269 id ti si bi fl dl de ch vch dd dt 270 2 2 5 2 2252.05 225205.0 2252 bye sub query 2022-04-28 2022-04-28 22:40:11 271 3 6 6 3 3663.21 366321.0 3663 hi subquery 2022-04-28 2022-04-28 22:40:11 272 4 7 1 5 4715.22 471522.0 4715 good morning my subquery 2022-04-28 2022-04-28 22:40:11 273 select * from v3; 274 max(ti) min(si) avg(fl) 275 8 2 4815.871616908482 276 select * from v4; 277 max(ti) + 10 min(si) - 1 avg(fl) 278 18 1 4815.871616908482 279 select * from v5; 280 SQL parser error: table "v5" does not exist 281 select * from v6; 282 ti -si 283 7 -1 284 1 -2 285 3 -2 286 1 -4 287 4 -4 288 8 -4 289 2 -5 290 7 -5 291 6 -6 292 select * from v7; 293 id ti si bi fl dl de ch vch dd dt 294 2 2 5 2 2252.05 225205.0 2252 bye sub query 2022-04-28 2022-04-28 22:40:11 295 select * from v8; 296 id ti si de 297 6 3 2 632 298 7 4 4 7443 299 8 7 5 8758 300 9 8 4 9849 301 select * from v9; 302 id ti si bi fl dl de ch vch dd dt 303 drop view v1; 304 drop view v2; 305 drop view v3; 306 drop view v4; 307 drop view v5; 308 invalid view 'view-from-subquery.v5' 309 drop view v6; 310 drop view v7; 311 drop view v8; 312 drop view v9; 313 drop table if exists t1; 314 drop table if exists t2; 315 drop table if exists t3; 316 create table t1 (libname1 varchar(21) not null primary key, city varchar(20)); 317 create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60)); 318 create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int); 319 insert into t2 values ('001','Daffy','Aducklife'); 320 insert into t2 values ('002','Bugs','Arabbitlife'); 321 insert into t2 values ('003','Cowboy','Lifeontherange'); 322 insert into t2 values ('000','Anonymous','Wannabuythisbook?'); 323 insert into t2 values ('004','BestSeller','OneHeckuvabook'); 324 insert into t2 values ('005','EveryoneBuys','Thisverybook'); 325 insert into t2 values ('006','SanFran','Itisasanfranlifestyle'); 326 insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book'); 327 insert into t3 values('000','NewYorkPublicLibra',1); 328 insert into t3 values('001','NewYorkPublicLibra',2); 329 insert into t3 values('002','NewYorkPublicLibra',3); 330 insert into t3 values('003','NewYorkPublicLibra',4); 331 insert into t3 values('004','NewYorkPublicLibra',5); 332 insert into t3 values('005','NewYorkPublicLibra',6); 333 insert into t3 values('006','SanFransiscoPublic',5); 334 insert into t3 values('007','BerkeleyPublic1',3); 335 insert into t3 values('007','BerkeleyPublic2',3); 336 insert into t3 values('001','NYC Lib',8); 337 insert into t1 values ('NewYorkPublicLibra','NewYork'); 338 insert into t1 values ('SanFransiscoPublic','SanFran'); 339 insert into t1 values ('BerkeleyPublic1','Berkeley'); 340 insert into t1 values ('BerkeleyPublic2','Berkeley'); 341 insert into t1 values ('NYCLib','NewYork'); 342 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 ; 343 city libname1 a 344 NewYork NewYorkPublicLibra 6 345 SanFran SanFransiscoPublic 1 346 Berkeley BerkeleyPublic1 1 347 Berkeley BerkeleyPublic2 1 348 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 ; 349 select * from v1; 350 city libname1 a 351 NewYork NewYorkPublicLibra 6 352 SanFran SanFransiscoPublic 1 353 Berkeley BerkeleyPublic1 1 354 Berkeley BerkeleyPublic2 1 355 drop view v1;