github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/select/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 drop table if exists t2; 186 drop table if exists t3; 187 create table t1(c1 int, c2 int); 188 insert into t1 values(1,1); 189 insert into t1 values(1,2); 190 insert into t1 values(1,3); 191 insert into t1 values(1,4); 192 insert into t1 values(1,5); 193 insert into t1 values(1,6); 194 create table t2(c3 int, c4 int); 195 insert into t2 values(1,1); 196 insert into t2 values(1,2); 197 insert into t2 values(1,3); 198 insert into t2 values(1,4); 199 insert into t2 values(1,5); 200 insert into t2 values(1,6); 201 create table t3(c5 int, c6 int); 202 insert into t3 values(1,1); 203 insert into t3 values(1,2); 204 insert into t3 values(1,3); 205 insert into t3 values(1,4); 206 insert into t3 values(1,5); 207 insert into t3 values(1,6); 208 select * from t1 where(select count(*) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)=1; 209 aggregation with non equal predicate in SCALAR subquery will be supported in future version is not yet implemented 210 select * from t1 where(select count(*) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)<10; 211 aggregation with non equal predicate in SCALAR subquery will be supported in future version is not yet implemented 212 select * from t1 where(select avg(t1.c1) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)=1; 213 correlated columns in aggregate function is not yet implemented 214 select * from t1 where(select avg(t2.c3) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)=1; 215 aggregation with non equal predicate in SCALAR subquery will be supported in future version is not yet implemented 216 select * from t1 where(select avg(t2.c4) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)=1; 217 aggregation with non equal predicate in SCALAR subquery will be supported in future version is not yet implemented 218 select * from t1 where(select max(t2.c3) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)=1; 219 aggregation with non equal predicate in SCALAR subquery will be supported in future version is not yet implemented 220 select * from t1 where(select count(t2.c3) from t2 where t1.c1=t2.c3 and t1.c2=t2.c4)=1; 221 c1 c2 222 1 1 223 1 2 224 1 3 225 1 4 226 1 5 227 1 6 228 select * from t1 where(select count(t2.c3) from t2 where t1.c1=t2.c3 and t2.c4<10)=1; 229 c1 c2 230 select * from t1 where t1.c1 in (select c6 from t3 where t1.c1=t3.c5 and t1.c2<t3.c6 and (select count(*) from t2 where t3.c5=t2.c3 and t3.c6=t2.c4)=1); 231 c1 c2 232 select * from t1 where t1.c1 not in (select c6 from t3 where t1.c1=t3.c5 and t1.c2<t3.c6 and (select count(*) from t2 where t3.c5=t2.c3 and t3.c6=t2.c4)=1); 233 c1 c2 234 1 1 235 1 2 236 1 3 237 1 4 238 1 5 239 1 6 240 select * from t1 where(select count(*) from t2 where t1.c1=t2.c3 and t1.c2=t2.c4+2)=0; 241 c1 c2 242 1 1 243 1 2 244 select * from t1 where(select count(*) from t2 where t1.c1=t2.c3 and t1.c2=t2.c4)<5; 245 c1 c2 246 1 1 247 1 2 248 1 3 249 1 4 250 1 5 251 1 6 252 select * from t1 where t1.c1 not in (select c6 from t3 where t1.c1=t3.c5 and t1.c2<t3.c6 and (select count(*) from t2 where t3.c5=t2.c3 and t3.c6=t2.c4)=1); 253 c1 c2 254 1 1 255 1 2 256 1 3 257 1 4 258 1 5 259 1 6 260 select * from t1 where t1.c2 not in (select c6 from t3 where t1.c2=t3.c6 and (select count(*) from t2 where t3.c6=t2.c4+2 )=1); 261 c1 c2 262 1 1 263 1 2 264 select * from t1 where t1.c2 not in (select c6 from t3 where t1.c2=t3.c6 and (select count(*) from t2 where t3.c6=t2.c4+2 )=t1.c2-2); 265 c1 c2 266 1 1 267 1 4 268 1 5 269 1 6 270 select * from t1 where t1.c2 not in (select c5 from t3 where t1.c2<t3.c6 and (select count(*) from t2 where t3.c6=t2.c4+2 )=t1.c2); 271 c1 c2 272 1 2 273 1 3 274 1 4 275 1 5 276 1 6 277 drop table if exists t1; 278 drop table if exists t2; 279 drop table if exists t3;