github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/select/select.test (about) 1 -- @suite 2 3 -- @case 4 -- @desc:test for select 5 -- @label:bvt 6 drop table if exists t1; 7 create table t1 (spID int,userID int,score smallint); 8 insert into t1 values (1,1,1); 9 insert into t1 values (2,2,2); 10 insert into t1 values (2,1,4); 11 insert into t1 values (3,3,3); 12 insert into t1 values (1,1,5); 13 insert into t1 values (4,6,10); 14 insert into t1 values (5,11,99); 15 select userID, MIN(score) from t1 group by userID order by userID desc; 16 select userID, MIN(score) from t1 group by userID order by userID asc; 17 select userID, SUM(score) from t1 group by userID order by userID desc; 18 select userID as a, MIN(score) as b from t1 group by userID order by userID; 19 select userID as user, MAX(score) as max from t1 group by userID order by user; 20 select userID as user, MAX(score) as max from t1 group by userID order by max desc; 21 select userID,count(score) from t1 group by userID having count(score)>1 order by userID; 22 select userID,count(score) from t1 where userID>2 group by userID having count(score)>1 order by userID; 23 select distinct userID, count(score) from t1 group by userID order by userID; 24 select distinct spID,userID from t1; 25 select distinct spID,userID from t1 where score>2; 26 select distinct spID,userID from t1 where score>2 order by spID asc; 27 select distinct spID,userID from t1 where spID>2 order by userID desc; 28 select distinct sum(spID) as sum from t1 group by userID order by sum asc; 29 select distinct sum(spID) as sum from t1 where score>1 group by userID order by sum asc; 30 select userID,MAX(score) from t1 where userID between 2 and 3 group by userID order by userID; 31 select userID,MAX(score) from t1 where userID not between 2 and 3 group by userID order by userID desc; 32 select spID,userID,score from t1 limit 2,1; 33 select spID,userID,score from t1 limit 2 offset 1; 34 select sum(score) as sum from t1 where spID=6 group by score order by sum desc; 35 select userID, userID DIV 2 as user_dir, userID%2 as user_percent, userID MOD 2 as user_mod from t1; 36 drop table if exists a; 37 create table a(a int); 38 insert into a values(1),(2),(3),(4),(5),(6),(7),(8); 39 select count(*) from a where a>=2 and a<=8; 40 drop table if exists t1; 41 create table t1 ( id int, name varchar(50) ); 42 insert into t1 values (1, 'aaaaa'); 43 insert into t1 values (3, "aaaaa"); 44 insert into t1 values (2, 'eeeeeee'); 45 select distinct name as name1 from t1; 46 drop table if exists t2; 47 create table t2(name char(10),owner char(10), species char(10), gender char(1), weight float,age int); 48 insert into t2 values ('Sunsweet01','Dsant01','otter','f',30.11,2), ('Sunsweet02','Dsant02','otter','m',30.11,3); 49 insert into t2(name, owner, species, gender, weight, age) values ('Sunsweet03','Dsant01','otter','f',30.11,2), ('Sunsweet04','Dsant02','otter','m',30.11,3); 50 select * from t2 limit 2, 4; 51 drop table if exists t3; 52 create table t3 (spID int,userID int,score smallint); 53 insert into t3 values (1,1,1); 54 insert into t3 values (2,2,2); 55 insert into t3 values (2,1,4); 56 insert into t3 values (3,3,3); 57 insert into t3 values (1,1,5); 58 insert into t3 values (4,6,10); 59 insert into t3 values (5,11,99); 60 select userID,MAX(score) max_score from t3 where userID <2 || userID > 3 group by userID order by max_score; 61 select userID, userID DIV 2 as user_dir, userID%2 as user_percent, userID MOD 2 as user_mod from t3 where userID > 3 ; 62 select CAST(userID AS CHAR) userid_cast, userID from t3 where CAST(spID AS CHAR)='1'; 63 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; 64 select * from t3 where spID>2 AND userID <2 || userID >=2 OR userID < 2 limit 3; 65 select * from t3 where (spID >2 or spID <= 2) && score <> 1 AND userID/2>2; 66 select * from t3 where spID >2 || spID <= 2 && score !=1 limit 3; 67 select userID,MAX(score) max_score from t3 where userID <2 || userID > 3 group by userID order by max_score; 68 select * from t3 where userID/2>2; 69 drop table if exists t4; 70 create table t4(c1 int, c2 int); 71 insert into t4 values (-3, 2); 72 insert into t4 values (1, 2); 73 select c1, -c2 from t4 order by -c1 desc; 74 drop table if exists t5; 75 create table t5(a int,b varchar(10),c varchar(10)); 76 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'); 77 select * from t5 where (b='ba' or b='cb') and (c='dc' or c='ed'); 78 drop table if exists tbl_01; 79 create table tbl_01 (col1 int, a bigint unsigned, c char(10) not null); 80 insert into tbl_01 values (1,1,"a"); 81 insert into tbl_01 values (2,2,"b"); 82 insert into tbl_01 values (2,3,"c"); 83 insert into tbl_01 values (3,4,"E"); 84 insert into tbl_01 values (3,5,"C"); 85 insert into tbl_01 values (3,6,"D"); 86 drop table if exists t1; 87 drop table if exists t2; 88 create table t1 (id int primary key); 89 create table t2 (id int); 90 insert into t1 values (75); 91 insert into t1 values (79); 92 insert into t1 values (78); 93 insert into t1 values (77); 94 insert into t1 values (104); 95 insert into t1 values (103); 96 insert into t1 values (102); 97 insert into t1 values (101); 98 insert into t1 values (105); 99 insert into t1 values (106); 100 insert into t1 values (107); 101 insert into t2 values (107),(75),(1000); 102 select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id order by t1.id desc; 103 drop table t1; 104 create table t1 ( a int not null default 1, big bigint ); 105 insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807); 106 select * from t1; 107 select min(big),max(big),max(big)-1 from t1; 108 drop table t1; 109 create table t1 ( a int not null default 1, big bigint unsigned); 110 insert into t1 (big) values (12345678901234567),(9223372036854775807),(18446744073709551615); 111 select * from t1; 112 select min(big),max(big),max(big)-1 from t1; 113 select min(big),max(big),max(big)-1 from t1 group by a; 114 drop table if exists t1; 115 create table t1 ( 116 value64 bigint unsigned not null, 117 value32 int not null 118 ); 119 insert into t1 values(17156792991891826145, 1); 120 insert into t1 values(9223372036854775807, 2); 121 select * from t1; 122 drop table if exists t1; 123 drop table if exists t2; 124 drop table if exists t3; 125 create table t1 (libname1 varchar(21) not null primary key, city varchar(20)); 126 create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60)); 127 create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int); 128 insert into t2 values ('001','Daffy','Aducklife'); 129 insert into t2 values ('002','Bugs','Arabbitlife'); 130 insert into t2 values ('003','Cowboy','Lifeontherange'); 131 insert into t2 values ('000','Anonymous','Wannabuythisbook?'); 132 insert into t2 values ('004','BestSeller','OneHeckuvabook'); 133 insert into t2 values ('005','EveryoneBuys','Thisverybook'); 134 insert into t2 values ('006','SanFran','Itisasanfranlifestyle'); 135 insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book'); 136 insert into t3 values('000','NewYorkPublicLibra',1); 137 insert into t3 values('001','NewYorkPublicLibra',2); 138 insert into t3 values('002','NewYorkPublicLibra',3); 139 insert into t3 values('003','NewYorkPublicLibra',4); 140 insert into t3 values('004','NewYorkPublicLibra',5); 141 insert into t3 values('005','NewYorkPublicLibra',6); 142 insert into t3 values('006','SanFransiscoPublic',5); 143 insert into t3 values('007','BerkeleyPublic1',3); 144 insert into t3 values('007','BerkeleyPublic2',3); 145 insert into t3 values('001','NYC Lib',8); 146 insert into t1 values ('NewYorkPublicLibra','NewYork'); 147 insert into t1 values ('SanFransiscoPublic','SanFran'); 148 insert into t1 values ('BerkeleyPublic1','Berkeley'); 149 insert into t1 values ('BerkeleyPublic2','Berkeley'); 150 insert into t1 values ('NYCLib','NewYork'); 151 select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1; 152 drop table if exists t1; 153 create table t1(a int,b varchar(5)); 154 insert into t1 values(1,'a'); 155 insert into t1 values(null,null); 156 insert into t1 values(null,'b'); 157 insert into t1 values(1,null); 158 select avg(a),b from t1 group by b order by b; 159 drop table if exists t1; 160 CREATE TABLE t1 (a int default NULL); 161 INSERT INTO t1 VALUES (NULL),(NULL); 162 select * from t1; 163 drop table if exists t1; 164 drop table if exists t2; 165 create table t1 (a int, b int); 166 insert into t1 values(10,null); 167 create table t2 (c int, d int); 168 insert into t2 values(20,null); 169 drop table if exists t1; 170 CREATE TABLE t1 (a int default null, b varchar(16) default null, c datetime DEFAULT null); 171 INSERT INTO t1(a, c) values (1,"2003-01-14 03:54:55"); 172 INSERT INTO t1(a, c) values (1,"2004-01-14 03:54:55"); 173 INSERT INTO t1(a, c) values (1,"2005-01-14 03:54:55"); 174 INSERT INTO t1(a, b) values (1,"2022year"); 175 INSERT INTO t1(b,c) values ("2022year","2003-01-14 03:54:55"); 176 INSERT INTO t1(b,c) values ("2021year","2003-01-14 03:54:55"); 177 INSERT INTO t1(b,c) values ("2020year","2003-01-14 03:54:55"); 178 select max(a),b,c from t1 group by b,c order by b,c; 179 drop table if exists t1; 180 create table t1(i int); 181 insert into t1 values(1),(2),(3),(4),(5); 182 insert into t1 values(null); 183 select count(*) from t1 where i=2; 184 drop table if exists t1; 185 CREATE TABLE t1 (c0 varchar(0) DEFAULT NULL); 186 insert into t1 values(); 187 insert into t1 values(''); 188 select * from t1; 189 190 drop table if exists t1; 191 create table if not exists t1(a tinyint auto_increment); 192 drop table if exists t1; 193 create table if not exists t1(a smallint auto_increment); 194 drop table if exists t1; 195 create table if not exists t1(a int auto_increment); 196 select * from t1; 197 show columns from t1; 198 drop table if exists t1; 199 create table if not exists t1(a bigint auto_increment); 200 select * from t1; 201 drop table if exists t1; 202 create table if not exists t1(a int auto_increment primary key); 203 drop table if exists t1; 204 SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED=1 ORDER BY WORD limit 1; 205 ; 206 drop table if exists t1; 207 create table t1 (a int primary key, b int); 208 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; 209 drop table if exists t1; 210 create table t1 (a int primary key, b int, c int); 211 insert into t1 values (1, 2, 3); 212 select count(*) from t1; 213 select count(*) from t1 where b=2; 214 select count(*) from t1 where a=2; 215 drop table if exists t1; 216 create table t1 (a int(11) unsigned); 217 insert into t1 values (1), (2); 218 select * from t1 where a != 2; 219 drop database if exists db1; 220 create database db1; 221 use db1; 222 create table t1 (a int primary key, b int); 223 insert into t1 values (1,1); 224 -- @separator:table 225 select mo_ctl('dn', 'flush', 'db1.t1'); 226 select a from t1 where a = ''; 227 drop database if exists db1; 228 create database db1; 229 use db1; 230 create table t1 (a int,b int, primary key(a,b)); 231 insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5); 232 select count(*) from t1 where a is null; 233 select count(*) from t1 where a = null; 234 -- @separator:table 235 select mo_ctl('dn', 'flush', 'db1.t1'); 236 select count(*) from t1 where a is null; 237 select count(*) from t1 where a = null; 238 drop database if exists db1; 239 create database db1; 240 use db1; 241 create table t1 (a int,b int, primary key(a)); 242 insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5); 243 select count(*) from t1 where a is null; 244 select count(*) from t1 where a = null; 245 -- @separator:table 246 select mo_ctl('dn', 'flush', 'db1.t1'); 247 select count(*) from t1 where a is null; 248 select count(*) from t1 where a = null; 249 select 1 > 0; 250 251 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; 252 253 use db1; 254 drop table if exists t11; 255 create table t11 (a int, b int, primary key (a)); 256 insert into t11 (select *,* from generate_series(1, 50000, 1)g); 257 select count(*) from t11 where a=20000; 258 select count(*) from t11 where a in (1,20000); 259 260 drop table if exists t1; 261 create table t1(a int primary key, b int); 262 insert into t1 values (1,1),(2,2),(3,3); 263 -- @separator:table 264 select mo_ctl('dn', 'flush', 'select.t1'); 265 select * from t1 where a in (3,3,3,2,1);