github.com/matrixorigin/matrixone@v1.2.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; 356 drop table if exists t1; 357 drop table if exists t2; 358 drop table if exists t3; 359 drop table if exists t4; 360 drop view if exists v1; 361 drop view if exists v2; 362 create table t1 (col1 varchar(20), col2 char(20)); 363 create table t2 (col1 varchar(64), col2 char(64)); 364 create table t3 (col1 varchar(20), col2 char(20)); 365 create table t4 (col1 varchar(20), col2 char(20)); 366 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; 367 desc v1; 368 Field Type Null Key Default Extra Comment 369 col1 VARCHAR(64) YES null 370 col2 CHAR(64) YES null 371 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; 372 desc v2; 373 Field Type Null Key Default Extra Comment 374 col1 VARCHAR(20) YES null 375 col2 CHAR(20) YES null 376 drop view v1; 377 drop view v2; 378 drop table t1; 379 drop table t2; 380 drop table t3; 381 drop table t4;