github.com/matrixorigin/matrixone@v0.7.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.0000 72 2 2.0000 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.7500 82 Computer 6900 1380.0000 83 Phone 10 10.0000 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.7500 87 Computer 6900 1380.0000 88 Phone 10 10.0000 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.0000 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 (like%) 150 likedb 151 show databases like "%like%"; 152 Database (%like%) 153 dblike 154 likedb 155 show databases like "%aa%"; 156 Database (%aa%) 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 select * from t2 where b not in ('e',"f"); 236 a b c d e 237 1 a 1.001 2022-02-08 2022-02-08 12:00:00 238 2 b 2.001 2022-02-09 2022-02-09 12:00:00 239 1 c 3.001 2022-02-10 2022-02-10 12:00:00 240 4 d 4.001 2022-02-11 2022-02-11 12:00:00 241 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; 242 sum(a) c 243 1 3.001 244 select * from t2 where d in ('20220208','2022-02-09'); 245 a b c d e 246 1 a 1.001 2022-02-08 2022-02-08 12:00:00 247 2 b 2.001 2022-02-09 2022-02-09 12:00:00 248 select * from t2 where e not in ('2022-02-09 12:00:00') and a in (4,5); 249 a b c d e 250 4 d 4.001 2022-02-11 2022-02-11 12:00:00 251 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; 252 suma e 253 4 2022-02-11 12:00:00 254 select * from t2 where c in (2.001,3); 255 a b c d e 256 drop table if exists t1; 257 create table t1(a int, b int unsigned); 258 insert into t1 values (-1, 1), (-5, 5); 259 select 1 & 2; 260 1 & 2 261 0 262 select -1 & 2; 263 -1 & 2 264 2 265 select null & 2; 266 null & 2 267 NULL 268 select a & 2, b & 2 from t1; 269 a & 2 b & 2 270 2 0 271 2 0 272 select 1 | 2; 273 1 | 2 274 3 275 select -1 | 2; 276 -1 | 2 277 -1 278 select null | 2; 279 null | 2 280 NULL 281 select a | 2, b | 2 from t1; 282 a | 2 b | 2 283 -1 3 284 -5 7 285 select 1 ^ 2; 286 1 ^ 2 287 3 288 select -1 ^ 2; 289 -1 ^ 2 290 -3 291 select null ^ 2; 292 null ^ 2 293 NULL 294 select a ^ 2, b ^ 2 from t1; 295 a ^ 2 b ^ 2 296 -3 3 297 -7 7 298 select 1 << 2; 299 1 << 2 300 4 301 select -1 << 2; 302 -1 << 2 303 18446744073709551612 304 select null << 2; 305 null << 2 306 NULL 307 select a << 2, b << 2 from t1; 308 a << 2 b << 2 309 18446744073709551612 4 310 18446744073709551596 20 311 delete from t1; 312 insert into t1 values (-5, 1024); 313 select 1024 >> 2; 314 1024 >> 2 315 256 316 select -5 >> 2; 317 -5 >> 2 318 4611686018427387902 319 select null >> 2; 320 null >> 2 321 NULL 322 select a >> 2, b >> 2 from t1; 323 a << 2 b << 2 324 -2 256 325 delete from t1; 326 insert into t1 values (-5, 5); 327 select ~5; 328 ~ 5 329 18446744073709551610 330 select ~-5; 331 ~ -5 332 4 333 select ~null; 334 ~ null 335 NULL 336 select ~a, ~b from t1; 337 ~a ~b 338 4 18446744073709551610 339 select 2 << -2; 340 2<<-2 341 0 342 select 2 >> -2; 343 2>>-2 344 0 345 drop table if exists t1; 346 create table t1 (a tinyint); 347 insert into t1 values (2); 348 select a << 20 from t1; 349 a<<20 350 2097152