github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/select/select.result (about) 1 drop table if exists t1; 2 create table t1 (spID int,userID int,score smallint); 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 select userID, MIN(score) from t1 group by userID order by userID desc; 11 userID MIN(score) 12 11 99 13 6 10 14 3 3 15 2 2 16 1 1 17 select userID, MIN(score) from t1 group by userID order by userID asc; 18 userID MIN(score) 19 1 1 20 2 2 21 3 3 22 6 10 23 11 99 24 select userID, SUM(score) from t1 group by userID order by userID desc; 25 userID SUM(score) 26 11 99 27 6 10 28 3 3 29 2 2 30 1 10 31 select userID as a, MIN(score) as b from t1 group by userID order by userID; 32 a b 33 1 1 34 2 2 35 3 3 36 6 10 37 11 99 38 select userID as user, MAX(score) as max from t1 group by userID order by user; 39 user max 40 1 5 41 2 2 42 3 3 43 6 10 44 11 99 45 select userID as user, MAX(score) as max from t1 group by userID order by max desc; 46 user max 47 11 99 48 6 10 49 1 5 50 3 3 51 2 2 52 select userID,count(score) from t1 group by userID having count(score)>1 order by userID; 53 userID count(score) 54 1 3 55 select userID,count(score) from t1 where userID>2 group by userID having count(score)>1 order by userID; 56 userID count(score) 57 select distinct userID, count(score) from t1 group by userID order by userID; 58 userID count(score) 59 1 3 60 2 1 61 3 1 62 6 1 63 11 1 64 select distinct spID,userID from t1; 65 spID userID 66 1 1 67 2 2 68 2 1 69 3 3 70 4 6 71 5 11 72 select distinct spID,userID from t1 where score>2; 73 spID userID 74 2 1 75 3 3 76 1 1 77 4 6 78 5 11 79 select distinct spID,userID from t1 where score>2 order by spID asc; 80 spID userID 81 1 1 82 2 1 83 3 3 84 4 6 85 5 11 86 select distinct spID,userID from t1 where spID>2 order by userID desc; 87 spID userID 88 5 11 89 4 6 90 3 3 91 select distinct sum(spID) as sum from t1 group by userID order by sum asc; 92 sum(spID) 93 2 94 3 95 4 96 5 97 select distinct sum(spID) as sum from t1 where score>1 group by userID order by sum asc; 98 sum(spID) 99 2 100 3 101 4 102 5 103 select userID,MAX(score) from t1 where userID between 2 and 3 group by userID order by userID; 104 userID MAX(score) 105 2 2 106 3 3 107 select userID,MAX(score) from t1 where userID not between 2 and 3 group by userID order by userID desc; 108 userID MAX(score) 109 11 99 110 6 10 111 1 5 112 select spID,userID,score from t1 limit 2,1; 113 spID userID score 114 2 1 4 115 select spID,userID,score from t1 limit 2 offset 1; 116 spID userID score 117 2 2 2 118 2 1 4 119 select sum(score) as sum from t1 where spID=6 group by score order by sum desc; 120 sum 121 select userID, userID DIV 2 as user_dir, userID%2 as user_percent, userID MOD 2 as user_mod from t1; 122 userID user_dir user_percent user_mod 123 1 0 1 1 124 2 1 0 0 125 1 0 1 1 126 3 1 1 1 127 1 0 1 1 128 6 3 0 0 129 11 5 1 1 130 drop table if exists a; 131 create table a(a int); 132 insert into a values(1),(2),(3),(4),(5),(6),(7),(8); 133 select count(*) from a where a>=2 and a<=8; 134 count(*) 135 7 136 drop table if exists t1; 137 create table t1 ( id int, name varchar(50) ); 138 insert into t1 values (1, 'aaaaa'); 139 insert into t1 values (3, "aaaaa"); 140 insert into t1 values (2, 'eeeeeee'); 141 select distinct name as name1 from t1; 142 name1 143 aaaaa 144 eeeeeee 145 drop table if exists t2; 146 create table t2(name char(10),owner char(10), species char(10), gender char(1), weight float,age int); 147 insert into t2 values ('Sunsweet01','Dsant01','otter','f',30.11,2), ('Sunsweet02','Dsant02','otter','m',30.11,3); 148 insert into t2(name, owner, species, gender, weight, age) values ('Sunsweet03','Dsant01','otter','f',30.11,2), ('Sunsweet04','Dsant02','otter','m',30.11,3); 149 select * from t2 limit 2, 4; 150 name owner species gender weight age 151 Sunsweet03 Dsant01 otter f 30.11 2 152 Sunsweet04 Dsant02 otter m 30.11 3 153 drop table if exists t3; 154 create table t3 (spID int,userID int,score smallint); 155 insert into t3 values (1,1,1); 156 insert into t3 values (2,2,2); 157 insert into t3 values (2,1,4); 158 insert into t3 values (3,3,3); 159 insert into t3 values (1,1,5); 160 insert into t3 values (4,6,10); 161 insert into t3 values (5,11,99); 162 select userID,MAX(score) max_score from t3 where userID <2 || userID > 3 group by userID order by max_score; 163 userID max_score 164 1 5 165 6 10 166 11 99 167 select userID, userID DIV 2 as user_dir, userID%2 as user_percent, userID MOD 2 as user_mod from t3 where userID > 3 ; 168 userID user_dir user_percent user_mod 169 6 3 0 0 170 11 5 1 1 171 select CAST(userID AS CHAR) userid_cast, userID from t3 where CAST(spID AS CHAR)='1'; 172 userid_cast userID 173 1 1 174 1 1 175 select CAST(userID AS DOUBLE) cast_double, CAST(userID AS FLOAT(3)) cast_float , CAST(userID AS REAL) cast_real, CAST(userID AS SIGNED) cast_signed, CAST(userID AS UNSIGNED) cast_unsigned from t3 limit 2; 176 cast_double cast_float cast_real cast_signed cast_unsigned 177 1.0 1.0 1.0 1 1 178 2.0 2.0 2.0 2 2 179 select * from t3 where spID>2 AND userID <2 || userID >=2 OR userID < 2 limit 3; 180 spID userID score 181 1 1 1 182 2 2 2 183 2 1 4 184 select * from t3 where (spID >2 or spID <= 2) && score <> 1 AND userID/2>2; 185 spID userID score 186 4 6 10 187 5 11 99 188 select * from t3 where spID >2 || spID <= 2 && score !=1 limit 3; 189 spID userID score 190 2 2 2 191 2 1 4 192 3 3 3 193 select userID,MAX(score) max_score from t3 where userID <2 || userID > 3 group by userID order by max_score; 194 userID max_score 195 1 5 196 6 10 197 11 99 198 select * from t3 where userID/2>2; 199 spID userID score 200 4 6 10 201 5 11 99 202 drop table if exists t4; 203 create table t4(c1 int, c2 int); 204 insert into t4 values (-3, 2); 205 insert into t4 values (1, 2); 206 select c1, -c2 from t4 order by -c1 desc; 207 c1 -c2 208 -3 -2 209 1 -2 210 drop table if exists t5; 211 create table t5(a int,b varchar(10),c varchar(10)); 212 insert into t5 values(1,'ab','cd'),(2,'ba','dc'),(3,'bc','de'),(4,'cb','ed'),(5,'cd','ef'),(6,'dc','fe'),(2,'de','fg'),(1,'ed','gf'); 213 select * from t5 where (b='ba' or b='cb') and (c='dc' or c='ed'); 214 a b c 215 2 ba dc 216 4 cb ed 217 drop table if exists tbl_01; 218 create table tbl_01 (col1 int, a bigint unsigned, c char(10) not null); 219 insert into tbl_01 values (1,1,"a"); 220 insert into tbl_01 values (2,2,"b"); 221 insert into tbl_01 values (2,3,"c"); 222 insert into tbl_01 values (3,4,"E"); 223 insert into tbl_01 values (3,5,"C"); 224 insert into tbl_01 values (3,6,"D"); 225 drop table if exists t1; 226 drop table if exists t2; 227 create table t1 (id int primary key); 228 create table t2 (id int); 229 insert into t1 values (75); 230 insert into t1 values (79); 231 insert into t1 values (78); 232 insert into t1 values (77); 233 insert into t1 values (104); 234 insert into t1 values (103); 235 insert into t1 values (102); 236 insert into t1 values (101); 237 insert into t1 values (105); 238 insert into t1 values (106); 239 insert into t1 values (107); 240 insert into t2 values (107),(75),(1000); 241 select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id order by t1.id desc; 242 id count(t2.id) 243 107 1 244 75 1 245 drop table t1; 246 create table t1 ( a int not null default 1, big bigint ); 247 insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807); 248 select * from t1; 249 a big 250 1 -1 251 1 12345678901234567 252 1 9223372036854775807 253 select min(big),max(big),max(big)-1 from t1; 254 min(big) max(big) max(big)-1 255 -1 9223372036854775807 9223372036854775806 256 drop table t1; 257 create table t1 ( a int not null default 1, big bigint unsigned); 258 insert into t1 (big) values (12345678901234567),(9223372036854775807),(18446744073709551615); 259 select * from t1; 260 a big 261 1 12345678901234567 262 1 9223372036854775807 263 1 18446744073709551615 264 select min(big),max(big),max(big)-1 from t1; 265 Data truncation: data out of range: data type int64, value '18446744073709551615' 266 select min(big),max(big),max(big)-1 from t1 group by a; 267 Data truncation: data out of range: data type int64, value '18446744073709551615' 268 drop table if exists t1; 269 create table t1 ( 270 value64 bigint unsigned not null, 271 value32 int not null 272 ); 273 insert into t1 values(17156792991891826145, 1); 274 insert into t1 values(9223372036854775807, 2); 275 select * from t1; 276 value64 value32 277 17156792991891826145 1 278 9223372036854775807 2 279 drop table if exists t1; 280 drop table if exists t2; 281 drop table if exists t3; 282 create table t1 (libname1 varchar(21) not null primary key, city varchar(20)); 283 create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60)); 284 create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int); 285 insert into t2 values ('001','Daffy','Aducklife'); 286 insert into t2 values ('002','Bugs','Arabbitlife'); 287 insert into t2 values ('003','Cowboy','Lifeontherange'); 288 insert into t2 values ('000','Anonymous','Wannabuythisbook?'); 289 insert into t2 values ('004','BestSeller','OneHeckuvabook'); 290 insert into t2 values ('005','EveryoneBuys','Thisverybook'); 291 insert into t2 values ('006','SanFran','Itisasanfranlifestyle'); 292 insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book'); 293 insert into t3 values('000','NewYorkPublicLibra',1); 294 insert into t3 values('001','NewYorkPublicLibra',2); 295 insert into t3 values('002','NewYorkPublicLibra',3); 296 insert into t3 values('003','NewYorkPublicLibra',4); 297 insert into t3 values('004','NewYorkPublicLibra',5); 298 insert into t3 values('005','NewYorkPublicLibra',6); 299 insert into t3 values('006','SanFransiscoPublic',5); 300 insert into t3 values('007','BerkeleyPublic1',3); 301 insert into t3 values('007','BerkeleyPublic2',3); 302 insert into t3 values('001','NYC Lib',8); 303 insert into t1 values ('NewYorkPublicLibra','NewYork'); 304 insert into t1 values ('SanFransiscoPublic','SanFran'); 305 insert into t1 values ('BerkeleyPublic1','Berkeley'); 306 insert into t1 values ('BerkeleyPublic2','Berkeley'); 307 insert into t1 values ('NYCLib','NewYork'); 308 select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1; 309 city libname1 a 310 NewYork NewYorkPublicLibra 6 311 SanFran SanFransiscoPublic 1 312 Berkeley BerkeleyPublic1 1 313 Berkeley BerkeleyPublic2 1 314 drop table if exists t1; 315 create table t1(a int,b varchar(5)); 316 insert into t1 values(1,'a'); 317 insert into t1 values(null,null); 318 insert into t1 values(null,'b'); 319 insert into t1 values(1,null); 320 select avg(a),b from t1 group by b order by b; 321 avg(a) b 322 1.0000 null 323 1.0000 a 324 null b 325 drop table if exists t1; 326 CREATE TABLE t1 (a int default NULL); 327 INSERT INTO t1 VALUES (NULL),(NULL); 328 select * from t1; 329 a 330 null 331 null 332 drop table if exists t1; 333 drop table if exists t2; 334 create table t1 (a int, b int); 335 insert into t1 values(10,null); 336 create table t2 (c int, d int); 337 insert into t2 values(20,null); 338 drop table if exists t1; 339 CREATE TABLE t1 (a int default null, b varchar(16) default null, c datetime DEFAULT null); 340 INSERT INTO t1(a, c) values (1,"2003-01-14 03:54:55"); 341 INSERT INTO t1(a, c) values (1,"2004-01-14 03:54:55"); 342 INSERT INTO t1(a, c) values (1,"2005-01-14 03:54:55"); 343 INSERT INTO t1(a, b) values (1,"2022year"); 344 INSERT INTO t1(b,c) values ("2022year","2003-01-14 03:54:55"); 345 INSERT INTO t1(b,c) values ("2021year","2003-01-14 03:54:55"); 346 INSERT INTO t1(b,c) values ("2020year","2003-01-14 03:54:55"); 347 select max(a),b,c from t1 group by b,c order by b,c; 348 max(a) b c 349 1 null 2003-01-14 03:54:55 350 1 null 2004-01-14 03:54:55 351 1 null 2005-01-14 03:54:55 352 null 2020year 2003-01-14 03:54:55 353 null 2021year 2003-01-14 03:54:55 354 1 2022year null 355 null 2022year 2003-01-14 03:54:55 356 drop table if exists t1; 357 create table t1(i int); 358 insert into t1 values(1),(2),(3),(4),(5); 359 insert into t1 values(null); 360 select count(*) from t1 where i=2; 361 count(*) 362 1 363 drop table if exists t1; 364 CREATE TABLE t1 (c0 varchar(0) DEFAULT NULL); 365 insert into t1 values(); 366 insert into t1 values(''); 367 select * from t1; 368 c0 369 null 370 371 drop table if exists t1; 372 create table if not exists t1(a tinyint auto_increment); 373 drop table if exists t1; 374 create table if not exists t1(a smallint auto_increment); 375 drop table if exists t1; 376 create table if not exists t1(a int auto_increment); 377 select * from t1; 378 a 379 show columns from t1; 380 Field Type Null Key Default Extra Comment 381 a INT(32) YES null 382 drop table if exists t1; 383 create table if not exists t1(a bigint auto_increment); 384 select * from t1; 385 a 386 drop table if exists t1; 387 create table if not exists t1(a int auto_increment primary key); 388 drop table if exists t1; 389 SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED=1 ORDER BY WORD limit 1; 390 word 391 ACCESSIBLE 392 ; 393 drop table if exists t1; 394 create table t1 (a int primary key, b int); 395 select * from t1 WHERE (a IN ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','26','27','28','29') AND b = 0) ORDER BY `a` ASC; 396 a b 397 drop table if exists t1; 398 create table t1 (a int primary key, b int, c int); 399 insert into t1 values (1, 2, 3); 400 select count(*) from t1; 401 count(*) 402 1 403 select count(*) from t1 where b=2; 404 count(*) 405 1 406 select count(*) from t1 where a=2; 407 count(*) 408 0 409 drop table if exists t1; 410 create table t1 (a int(11) unsigned); 411 insert into t1 values (1), (2); 412 select * from t1 where a != 2; 413 a 414 1 415 drop database if exists db1; 416 create database db1; 417 use db1; 418 create table t1 (a int primary key, b int); 419 insert into t1 values (1,1); 420 select mo_ctl('dn', 'flush', 'db1.t1'); 421 mo_ctl(dn, flush, db1.t1) 422 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 423 select a from t1 where a = ''; 424 invalid argument cast to int, bad value 425 drop database if exists db1; 426 create database db1; 427 use db1; 428 create table t1 (a int,b int, primary key(a,b)); 429 insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5); 430 select count(*) from t1 where a is null; 431 count(*) 432 0 433 select count(*) from t1 where a = null; 434 count(*) 435 0 436 select mo_ctl('dn', 'flush', 'db1.t1'); 437 mo_ctl(dn, flush, db1.t1) 438 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 439 select count(*) from t1 where a is null; 440 count(*) 441 0 442 select count(*) from t1 where a = null; 443 count(*) 444 0 445 drop database if exists db1; 446 create database db1; 447 use db1; 448 create table t1 (a int,b int, primary key(a)); 449 insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5); 450 select count(*) from t1 where a is null; 451 count(*) 452 0 453 select count(*) from t1 where a = null; 454 count(*) 455 0 456 select mo_ctl('dn', 'flush', 'db1.t1'); 457 mo_ctl(dn, flush, db1.t1) 458 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 459 select count(*) from t1 where a is null; 460 count(*) 461 0 462 select count(*) from t1 where a = null; 463 count(*) 464 0 465 select 1 > 0; 466 1 > 0 467 true 468 SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, CASE WHEN TABLE_TYPE='BASE TABLE' THEN CASE WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE' ELSE 'TABLE' END WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'abc' group by null HAVING TABLE_TYPE IN ('LOCAL TEMPORARY','TABLE','VIEW',null,null) ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME; 469 internal error: Invalid GROUP BY NULL 470 use db1; 471 drop table if exists t11; 472 create table t11 (a int, b int, primary key (a)); 473 insert into t11 (select *,* from generate_series(1, 50000, 1)g); 474 select count(*) from t11 where a=20000; 475 count(*) 476 1 477 select count(*) from t11 where a in (1,20000); 478 count(*) 479 2 480 drop table if exists t1; 481 create table t1(a int primary key, b int); 482 insert into t1 values (1,1),(2,2),(3,3); 483 select mo_ctl('dn', 'flush', 'select.t1'); 484 mo_ctl(dn, flush, select.t1) 485 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 486 select * from t1 where a in (3,3,3,2,1); 487 a b 488 1 1 489 2 2 490 3 3