github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/subquery/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.31 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.31 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.31 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 select id,min(ti) from (select * from t1) sub group by id order by id desc; 75 id min(ti) 76 9 8 77 8 7 78 7 4 79 6 3 80 5 1 81 4 7 82 3 6 83 2 2 84 1 1 85 select id,sum(ti) from (select * from t1) sub group by id; 86 id sum(ti) 87 1 1 88 2 2 89 3 6 90 4 7 91 5 1 92 6 3 93 7 4 94 8 7 95 9 8 96 select distinct(ti) from (select * from t1) sub; 97 ti 98 1 99 2 100 6 101 7 102 3 103 4 104 8 105 select distinct(ti) from (select * from t1) sub where id <6; 106 ti 107 1 108 2 109 6 110 7 111 select count(*) from (select * from t1) sub where id > 4 ; 112 count(*) 113 5 114 select * from (select * from t1) sub where id > 1 limit 3; 115 id ti si bi fl dl de ch vch dd dt 116 2 2 5 2 2252.05 225205.0 2252 bye sub query 2022-04-28 2022-04-28 22:40:11 117 3 6 6 3 3663.21 366321.0 3663 hi subquery 2022-04-28 2022-04-28 22:40:11 118 4 7 1 5 4715.22 471522.0 4715 good morning my subquery 2022-04-28 2022-04-28 22:40:11 119 select max(ti),min(si),avg(fl) from (select * from t1) sub where id < 4 || id > 5; 120 max(ti) min(si) avg(fl) 121 8 2 4815.871616908482 122 select max(ti)+10,min(si)-1,avg(fl) from (select * from t1) sub where id < 4 || id > 5; 123 max(ti)+10 min(si)-1 avg(fl) 124 18 1 4815.871616908482 125 select substr from (select * from t1) sub where id < 4 || id > 5; 126 invalid input: column substr does not exist 127 select ti,-si from (select * from t1) sub order by -si desc; 128 ti -si 129 7 -1 130 1 -2 131 3 -2 132 1 -4 133 4 -4 134 8 -4 135 2 -5 136 7 -5 137 6 -6 138 select * from (select * from t1) sub where (ti=2 or si=3) and (ch = 'bye' or vch = 'subquery'); 139 id ti si bi fl dl de ch vch dd dt 140 2 2 5 2 2252.05 225205.0 2252 bye sub query 2022-04-28 2022-04-28 22:40:11 141 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; 142 id ti si de 143 6 3 2 632 144 7 4 4 7443 145 8 7 5 8758 146 9 8 4 9849 147 select * from (select * from t1 where id > 100) sub ; 148 id ti si bi fl dl de ch vch dd dt 149 drop table if exists t1; 150 drop table if exists t2; 151 drop table if exists t3; 152 create table t1 (libname1 varchar(21) not null primary key, city varchar(20)); 153 create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60)); 154 create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int); 155 insert into t2 values ('001','Daffy','Aducklife'); 156 insert into t2 values ('002','Bugs','Arabbitlife'); 157 insert into t2 values ('003','Cowboy','Lifeontherange'); 158 insert into t2 values ('000','Anonymous','Wannabuythisbook?'); 159 insert into t2 values ('004','BestSeller','OneHeckuvabook'); 160 insert into t2 values ('005','EveryoneBuys','Thisverybook'); 161 insert into t2 values ('006','SanFran','Itisasanfranlifestyle'); 162 insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book'); 163 insert into t3 values('000','NewYorkPublicLibra',1); 164 insert into t3 values('001','NewYorkPublicLibra',2); 165 insert into t3 values('002','NewYorkPublicLibra',3); 166 insert into t3 values('003','NewYorkPublicLibra',4); 167 insert into t3 values('004','NewYorkPublicLibra',5); 168 insert into t3 values('005','NewYorkPublicLibra',6); 169 insert into t3 values('006','SanFransiscoPublic',5); 170 insert into t3 values('007','BerkeleyPublic1',3); 171 insert into t3 values('007','BerkeleyPublic2',3); 172 insert into t3 values('001','NYC Lib',8); 173 insert into t1 values ('NewYorkPublicLibra','NewYork'); 174 insert into t1 values ('SanFransiscoPublic','SanFran'); 175 insert into t1 values ('BerkeleyPublic1','Berkeley'); 176 insert into t1 values ('BerkeleyPublic2','Berkeley'); 177 insert into t1 values ('NYCLib','NewYork'); 178 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 ; 179 city libname1 a 180 NewYork NewYorkPublicLibra 6 181 SanFran SanFransiscoPublic 1 182 Berkeley BerkeleyPublic1 1 183 Berkeley BerkeleyPublic2 1 184 drop table if exists t1; 185 create table t1(a int); 186 insert into t1 values(1); 187 select * from (select * from t1) tt, (select * from t1); 188 a a 189 1 1 190 select * from (select * from t1), (select * from t1); 191 a a 192 1 1