github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/operator/operator.result (about) 1 drop table if exists t1; 2 create table t1 (spID smallint,userID bigint,score int); 3 insert into t1 values (1,1,1); 4 insert into t1 values (2,2,2); 5 insert into t1 values (2,1,4); 6 insert into t1 values (3,3,3); 7 insert into t1 values (1,1,5); 8 insert into t1 values (4,6,10); 9 insert into t1 values (5,11,99); 10 create table t2(product VARCHAR(32),country_id INTEGER NOT NULL,year INTEGER,profit INTEGER); 11 insert into t2 values ( 'Computer', 2,2000, 1200), 12 ( 'TV', 1, 1999, 150), 13 ( 'Calculator', 1, 1999,50), 14 ( 'Computer', 1, 1999,1500), 15 ( 'Computer', 1, 2000,1500), 16 ( 'TV', 1, 2000, 150), 17 ( 'TV', 2, 2000, 100), 18 ( 'TV', 2, 2000, 100), 19 ( 'Calculator', 1, 2000,75), 20 ( 'Calculator', 2, 2000,75), 21 ( 'TV', 1, 1999, 100), 22 ( 'Computer', 1, 1999,1200), 23 ( 'Computer', 2, 2000,1500), 24 ( 'Calculator', 2, 2000,75), 25 ( 'Phone', 3, 2003,10); 26 select userID,spID,score from t1 where userID=spID and userID<>score; 27 userid spid score 28 1 1 5 29 select userID,spID,score from t1 where userID=spID and userID!=score; 30 userid spid score 31 1 1 5 32 select userID,spID,score from t1 where userID between spID and score; 33 userid spid score 34 1 1 1 35 2 2 2 36 3 3 3 37 1 1 5 38 6 4 10 39 11 5 99 40 select userID,spID,score from t1 where userID not between spID and score; 41 userid spid score 42 1 2 4 43 select * from t1 where userID between 3 and 6; 44 spid userid score 45 3 3 3 46 4 6 10 47 select userID,spID,score from t1 where spID>=userID*score; 48 userid spid score 49 1 1 1 50 select userID,score,spID from t1 where userID<=score/spID; 51 userid score spid 52 1 1 1 53 1 4 2 54 1 5 1 55 11 99 5 56 select spID,userID,score from t1 where spID>(userID-1); 57 spid userid score 58 1 1 1 59 2 2 2 60 2 1 4 61 3 3 3 62 1 1 5 63 select spID,userID,score from t1 where score<(spID*userID+1); 64 spid userid score 65 1 1 1 66 2 2 2 67 3 3 3 68 4 6 10 69 select userID, AVG(score) from t1 WHERE spID=2 group by userID order by userID; 70 userid avg(score) 71 1 4.0 72 2 2.0 73 select product, SUM(profit) from t2 where year>1999 group by product order by product desc; 74 product sum(profit) 75 TV 350 76 Phone 10 77 Computer 4200 78 Calculator 225 79 select product, SUM(profit),AVG(profit) from t2 where product!='TV' group by product order by product asc; 80 product sum(profit) avg(profit) 81 Calculator 275 68.75 82 Computer 6900 1380.0 83 Phone 10 10.0 84 select product, SUM(profit),AVG(profit) from t2 where product<>'TV' group by product order by product asc; 85 product sum(profit) avg(profit) 86 Calculator 275 68.75 87 Computer 6900 1380.0 88 Phone 10 10.0 89 select product, SUM(profit),AVG(profit) from t2 where product='Phone' group by product order by product asc; 90 product sum(profit) avg(profit) 91 Phone 10 10.0 92 select product, SUM(profit) from t2 where year>1999 and year<=2002 group by product order by product desc; 93 product sum(profit) 94 TV 350 95 Computer 4200 96 Calculator 225 97 select * from t1 where 2<10; 98 spid userid score 99 1 1 1 100 2 2 2 101 2 1 4 102 3 3 3 103 1 1 5 104 4 6 10 105 5 11 99 106 select userID, userID DIV 2 as user_div, userID%2 as user_percent, userID MOD 2 as user_mod from t1 WHERE userID > 3; 107 userid user_div user_percent user_mod 108 6 3 0 0 109 11 5 1 1 110 select * from t1 where userID-2>2 && (userID+spID)/3<>0 && score MOD 2 > 0; 111 spid userid score 112 5 11 99 113 select * from t1 where spID >2 && userID < 6 && score != 1; 114 spid userid score 115 3 3 3 116 drop table if exists t2; 117 create table t2(c1 int, c2 int); 118 insert into t2 values (-3, 2); 119 insert into t2 values (1, 2); 120 select -c1 from t2; 121 -c1 122 3 123 -1 124 select c1, c2 from t2 order by -c1 desc; 125 c1 c2 126 -3 2 127 1 2 128 drop table if exists t3; 129 create table t3 (c1 varchar(80)); 130 insert into t3 values ("a"), 131 ("abc"), 132 ("abcd"), 133 ("hello"), 134 ("test"), 135 ("C:\Program Files(x86)"), 136 ("C:\\Program Files(x86)"); 137 select * from t3; 138 c1 139 a 140 abc 141 abcd 142 hello 143 test 144 C:Program Files(x86) 145 C:\Program Files(x86) 146 create database if not exists likedb; 147 create database if not exists dblike; 148 show databases like 'like%'; 149 Database 150 likedb 151 show databases like "%like%"; 152 Database 153 likedb 154 dblike 155 show databases like "%aa%"; 156 Database 157 drop database likedb; 158 drop database dblike; 159 SELECT * FROM t1 where t1.userID<6 OR NOT t1.userID; 160 spid userid score 161 1 1 1 162 2 2 2 163 2 1 4 164 3 3 3 165 1 1 5 166 SELECT * FROM t1 where NOT t1.userID OR t1.userID<6; 167 spid userid score 168 1 1 1 169 2 2 2 170 2 1 4 171 3 3 3 172 1 1 5 173 SELECT * FROM t1 where NOT t1.userID || t1.userID<6; 174 spid userid score 175 1 1 1 176 2 2 2 177 2 1 4 178 3 3 3 179 1 1 5 180 drop table if exists t1; 181 create table t1 (a int); 182 insert into t1 values (0),(1),(NULL); 183 select * from t1; 184 a 185 0 186 1 187 null 188 select * from t1 where not a between 2 and 3; 189 a 190 0 191 1 192 drop table if exists t3; 193 CREATE TABLE t3( 194 cont_nr int(11) NOT NULL primary key, 195 ver_nr int(11) NOT NULL default 0, 196 aufnr int(11) NOT NULL default 0, 197 username varchar(50) NOT NULL default '' 198 ); 199 INSERT INTO t3 VALUES (3359356,405,3359356,'Mustermann Musterfrau'); 200 INSERT INTO t3 VALUES (3359357,468,3359357,'Mustermann Musterfrau'); 201 INSERT INTO t3 VALUES (3359359,468,3359359,'Mustermann musterfrau'); 202 INSERT INTO t3 VALUES (3359360,0,0,'Mustermann Masterfrau'); 203 INSERT INTO t3 VALUES (3359361,406,3359361,'Mastermann Masterfrau'); 204 INSERT INTO t3 VALUES (3359362,406,3359362,'Mustermann MusterFrau'); 205 select username from t3 where username like 'Ma%'; 206 username 207 Mastermann Masterfrau 208 select username from t3 where username like '%Frau'; 209 username 210 Mustermann MusterFrau 211 select username from t3 where username like '%Mast%'; 212 username 213 Mustermann Masterfrau 214 Mastermann Masterfrau 215 select username from t3 where username like '%a_t%'; 216 username 217 Mustermann Masterfrau 218 Mastermann Masterfrau 219 drop table if exists t2; 220 create table t2(a int,b varchar(5),c float, d date, e datetime); 221 insert into t2 values(1,'a',1.001,'2022-02-08','2022-02-08 12:00:00'); 222 insert into t2 values(2,'b',2.001,'2022-02-09','2022-02-09 12:00:00'); 223 insert into t2 values(1,'c',3.001,'2022-02-10','2022-02-10 12:00:00'); 224 insert into t2 values(4,'d',4.001,'2022-02-11','2022-02-11 12:00:00'); 225 select * from t2 where a in (2,4); 226 a b c d e 227 2 b 2.001 2022-02-09 2022-02-09 12:00:00 228 4 d 4.001 2022-02-11 2022-02-11 12:00:00 229 select * from t2 where a not in (2,4); 230 a b c d e 231 1 a 1.001 2022-02-08 2022-02-08 12:00:00 232 1 c 3.001 2022-02-10 2022-02-10 12:00:00 233 select * from t2 where c in (2.001,2.002); 234 a b c d e 235 2 b 2.001 2022-02-09 2022-02-09 12:00:00 236 select * from t2 where b not in ('e',"f"); 237 a b c d e 238 1 a 1.001 2022-02-08 2022-02-08 12:00:00 239 2 b 2.001 2022-02-09 2022-02-09 12:00:00 240 1 c 3.001 2022-02-10 2022-02-10 12:00:00 241 4 d 4.001 2022-02-11 2022-02-11 12:00:00 242 select sum(a),c from t2 where a in (1,2) and d in ('2022-02-10','2022-02-11') group by c order by c; 243 sum(a) c 244 1 3.001 245 select * from t2 where d in ('20220208','2022-02-09'); 246 a b c d e 247 1 a 1.001 2022-02-08 2022-02-08 12:00:00 248 2 b 2.001 2022-02-09 2022-02-09 12:00:00 249 select * from t2 where e not in ('2022-02-09 12:00:00') and a in (4,5); 250 a b c d e 251 4 d 4.001 2022-02-11 2022-02-11 12:00:00 252 select sum(a) as suma,e from t2 where e not in ('2022-02-09 12:00:00') and a in (4,5) group by e order by suma; 253 suma e 254 4 2022-02-11 12:00:00 255 select * from t2 where c in (2.001,3); 256 a b c d e 257 2 b 2.001 2022-02-09 2022-02-09 12:00:00 258 drop table if exists t1; 259 create table t1(a int, b int unsigned); 260 insert into t1 values (-1, 1), (-5, 5); 261 select 1 & 2; 262 1 & 2 263 0 264 select -1 & 2; 265 -1 & 2 266 2 267 select null & 2; 268 null & 2 269 null 270 select a & 2, b & 2 from t1; 271 a & 2 b & 2 272 2 0 273 2 0 274 select 1 | 2; 275 1 | 2 276 3 277 select -1 | 2; 278 -1 | 2 279 -1 280 select null | 2; 281 null | 2 282 null 283 select a | 2, b | 2 from t1; 284 a | 2 b | 2 285 -1 3 286 -5 7 287 select 1 ^ 2; 288 1 ^ 2 289 3 290 select -1 ^ 2; 291 -1 ^ 2 292 -3 293 select null ^ 2; 294 null ^ 2 295 null 296 select a ^ 2, b ^ 2 from t1; 297 a ^ 2 b ^ 2 298 -3 3 299 -7 7 300 select 1 << 2; 301 1 << 2 302 4 303 select -1 << 2; 304 -1 << 2 305 -4 306 select null << 2; 307 null << 2 308 null 309 select a << 2, b << 2 from t1; 310 a << 2 b << 2 311 -4 4 312 -20 20 313 delete from t1; 314 insert into t1 values (-5, 1024); 315 select 1024 >> 2; 316 1024 >> 2 317 256 318 select -5 >> 2; 319 -5 >> 2 320 -2 321 select null >> 2; 322 null >> 2 323 null 324 select a >> 2, b >> 2 from t1; 325 a >> 2 b >> 2 326 -2 256 327 delete from t1; 328 insert into t1 values (-5, 5); 329 select ~5; 330 ~5 331 18446744073709551610 332 select ~-5; 333 ~ (-5) 334 4 335 select ~null; 336 ~null 337 null 338 select ~a, ~b from t1; 339 ~a ~b 340 4 18446744073709551610 341 select 2 << -2; 342 2 << -2 343 0 344 select 2 >> -2; 345 2 >> -2 346 0 347 drop table if exists t1; 348 create table t1 (a tinyint); 349 insert into t1 values (2); 350 select a << 20 from t1; 351 a << 20 352 2097152 353 select !true; 354 !true 355 false