github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_aggr_max.result (about) 1 SELECT max(null); 2 max(null) 3 null 4 create table t1(a tinyint, b SMALLINT, c bigint, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 5 insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf"); 6 insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf"); 7 insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f"); 8 insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf"); 9 select max(a) from t1; 10 max(a) 11 71 12 select max(b) from t1; 13 max(b) 14 71 15 select max(c) from t1; 16 max(c) 17 21 18 select max(d) from t1; 19 max(d) 20 43 21 select max(e) from t1; 22 max(e) 23 54 24 select max(f) from t1; 25 max(f) 26 53.5 27 select max(g) from t1; 28 max(g) 29 431.13 30 select max(h) from t1; 31 max(h) 32 124.3140000000000000000 33 select max(i) from t1; 34 max(i) 35 2012-03-22 36 select max(k) from t1; 37 max(k) 38 2019-03-12 10:03:12 39 select max(l) from t1; 40 max(l) 41 2032-03-12 13:04:12 42 select max(m) from t1; 43 max(m) 44 afbc 45 select max(n) from t1; 46 max(n) 47 dcvf 48 drop table t1; 49 select max(99999999999999999.99999); 50 max(99999999999999999.99999) 51 99999999999999999.99999 52 select max(999999999999999933193939.99999); 53 max(999999999999999933193939.99999) 54 999999999999999933193939.99999 55 select max(9999999999999999999999999999999999.9999999999999); 56 max(9999999999999999999999999999999999.9999999999999) 57 9999999999999999999999999999999999.9999999999999 58 create table t1(a bigint); 59 select max(a) from t1; 60 max(a) 61 null 62 insert into t1 values(null),(null),(null),(null); 63 select max(a) from t1; 64 max(a) 65 null 66 insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515); 67 select max(a) from t1; 68 max(a) 69 124125152651515 70 drop table t1; 71 create table t1 ( a int not null default 1, big bigint ); 72 insert into t1 (big) values (-1),(1234567890167),(92233720368547),(18446744073709515); 73 select * from t1; 74 a big 75 1 -1 76 1 1234567890167 77 1 92233720368547 78 1 18446744073709515 79 select min(big),max(big),max(big)-1 from t1; 80 min(big) max(big) max(big)-1 81 -1 18446744073709515 18446744073709514 82 select min(big),max(big),max(big)-1 from t1 group by a; 83 min(big) max(big) max(big)-1 84 -1 18446744073709515 18446744073709514 85 insert into t1 (big) values (184467440737615); 86 select * from t1; 87 a big 88 1 -1 89 1 1234567890167 90 1 92233720368547 91 1 18446744073709515 92 1 184467440737615 93 select min(big),max(big),max(big)-1 from t1; 94 min(big) max(big) max(big)-1 95 -1 18446744073709515 18446744073709514 96 select min(big),max(big),max(big)-1 from t1 group by a; 97 min(big) max(big) max(big)-1 98 -1 18446744073709515 18446744073709514 99 drop table t1; 100 create table t1 (name char(20) not null); 101 create table t2 (name char(20) not null); 102 insert into t1 values ("å"); 103 insert into t1 values ("ä"); 104 insert into t1 values ("ö"); 105 insert into t2 select * from t1; 106 select * from t1 order by name; 107 name 108 ä 109 å 110 ö 111 select concat_ws(",","*",name,"*") from t1 order by 1; 112 concat_ws(",","*",name,"*") 113 *,ä,* 114 *,å,* 115 *,ö,* 116 select min(name),min(concat_ws(",","*",name,"*")),max(name),max(concat_ws("*",name,"*")) from t1; 117 min(name) min(concat_ws(",","*",name,"*")) max(name) max(concat_ws("*",name,"*")) 118 å *,å,* ö ö** 119 drop table t1; 120 drop table t2; 121 CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL); 122 INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50); 123 select distinct Fld1, max( Fld2) as q from t1 group by Fld1 having q is not null; 124 SQL syntax error: column "q" must appear in the GROUP BY clause or be used in an aggregate function 125 select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null; 126 Fld1 max(Fld2) 127 1 20 128 3 50 129 select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null; 130 Fld1 max(Fld2) 131 1 20 132 3 50 133 select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null; 134 Fld1 max(Fld2) 135 1 20 136 3 50 137 select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null; 138 Fld1 max(Fld2) 139 1 20 140 3 50 141 drop table t1; 142 create table t1 (grp int, a bigint unsigned, c char(10) not null); 143 insert into t1 values (1,1,"a"); 144 insert into t1 values (2,2,"b"); 145 insert into t1 values (2,3,"c"); 146 insert into t1 values (3,4,"E"); 147 insert into t1 values (3,5,"C"); 148 insert into t1 values (3,6,"D"); 149 select sum(a) from t1 where a > 10; 150 sum(a) 151 null 152 select count(distinct a),count(distinct grp) from t1; 153 count(distinct a) count(distinct grp) 154 6 3 155 insert into t1 values (null,null,''); 156 select count(distinct a),count(distinct grp) from t1; 157 count(distinct a) count(distinct grp) 158 6 3 159 create table t2 (grp int, a bigint unsigned, c char(10)); 160 insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp; 161 drop table t1; 162 drop table t2; 163 create table t1 (a1 int, a2 char(3)); 164 insert into t1 values(10,'aaa'), (10,null), (10,'bbb'), (20,'zzz'); 165 create table t2(a1 char(3), a2 int, a3 real); 166 select * from t1; 167 a1 a2 168 10 aaa 169 10 null 170 10 bbb 171 20 zzz 172 select min(a2) from t1; 173 min(a2) 174 aaa 175 select max(t1.a1), max(t2.a2) from t1, t2; 176 max(t1.a1) max(t2.a2) 177 null null 178 select max(t1.a1) from t1, t2; 179 max(t1.a1) 180 null 181 select max(t2.a2), max(t1.a1) from t1, t2; 182 max(t2.a2) max(t1.a1) 183 null null 184 insert into t2 values('AAA', 10, 0.5); 185 insert into t2 values('BBB', 20, 1.0); 186 select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2; 187 a1 a2 a1 a2 188 10 aaa BBB 20 189 10 aaa AAA 10 190 10 null BBB 20 191 10 null AAA 10 192 10 bbb BBB 20 193 10 bbb AAA 10 194 20 zzz BBB 20 195 20 zzz AAA 10 196 select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9; 197 max(t1.a1) max(t2.a1) 198 null null 199 select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9; 200 max(t2.a1) max(t1.a1) 201 null null 202 select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10; 203 a1 a2 a1 a2 204 10 aaa BBB 20 205 10 aaa AAA 10 206 10 null BBB 20 207 10 null AAA 10 208 10 bbb BBB 20 209 10 bbb AAA 10 210 20 zzz null null 211 select max(t1.a2) from t1 left outer join t2 on t1.a1=10; 212 max(t1.a2) 213 zzz 214 select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20; 215 max(t2.a1) 216 BBB 217 select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10; 218 max(t2.a1) 219 AAA 220 select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA'; 221 max(t2.a1) 222 null 223 select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10; 224 max(t1.a2) max(t2.a1) 225 zzz BBB 226 drop table t1; 227 drop table t2; 228 CREATE TABLE t1 (a int, b int); 229 select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; 230 count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 231 0 null null null null null null null 232 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 233 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 234 insert into t1 values (1,null); 235 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 236 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 237 1 0 null null null null null null null 238 insert into t1 values (1,null); 239 insert into t1 values (2,null); 240 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 241 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 242 1 0 null null null null null null null 243 2 0 null null null null null null null 244 insert into t1 values (2,1); 245 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 246 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 247 1 0 null null null null null null null 248 2 1 1 1.0 0.0 1 1 1 1 249 insert into t1 values (3,1); 250 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 251 a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 252 1 0 null null null null null null null 253 2 1 1 1.0 0.0 1 1 1 1 254 3 1 1 1.0 0.0 1 1 1 1 255 drop table t1; 256 create table t1(a1 char(3) primary key,a2 smallint,a3 char(3),a4 real,a5 date); 257 create table t2(a1 char(3) primary key,a2 char(17),a3 char(2),a4 char(3)); 258 insert into t1 values('AME',0,'SEA',0.100,date'1942-02-19'); 259 insert into t1 values('HBR',1,'SEA',0.085,date'1948-03-05'); 260 insert into t1 values('BOT',2,'SEA',0.085,date'1951-11-29'); 261 insert into t1 values('BMC',3,'SEA',0.085,date'1958-09-08'); 262 insert into t1 values('TWU',0,'LAX',0.080,date'1969-10-05'); 263 insert into t1 values('BDL',0,'DEN',0.080,date'1960-11-27'); 264 insert into t1 values('DTX',1,'NYC',0.080,date'1961-05-04'); 265 insert into t1 values('PLS',1,'WDC',0.075,date'1949-01-02'); 266 insert into t1 values('ZAJ',2,'CHI',0.075,date'1960-06-15'); 267 insert into t1 values('VVV',2,'MIN',0.075,date'1959-06-28'); 268 insert into t1 values('GTM',3,'DAL',0.070,date'1977-09-23'); 269 insert into t1 values('SSJ',null,'CHI',null,date'1974-03-19'); 270 insert into t1 values('KKK',3,'ATL',null,null); 271 insert into t1 values('XXX',null,'MIN',null,null); 272 insert into t1 values('WWW',1,'LED',null,null); 273 insert into t2 values('TKF','Seattle','WA','AME'); 274 insert into t2 values('LCC','Los Angeles','CA','TWU'); 275 insert into t2 values('DEN','Denver','CO','BDL'); 276 insert into t2 values('SDC','San Diego','CA','TWU'); 277 insert into t2 values('NOL','New Orleans','LA','GTM'); 278 insert into t2 values('LAK','Los Angeles','CA','TWU'); 279 insert into t2 values('AAA','AAA','AA','AME'); 280 select * from t1; 281 a1 a2 a3 a4 a5 282 AME 0 SEA 0.1 1942-02-19 283 BDL 0 DEN 0.08 1960-11-27 284 BMC 3 SEA 0.085 1958-09-08 285 BOT 2 SEA 0.085 1951-11-29 286 DTX 1 NYC 0.08 1961-05-04 287 GTM 3 DAL 0.07 1977-09-23 288 HBR 1 SEA 0.085 1948-03-05 289 KKK 3 ATL null null 290 PLS 1 WDC 0.075 1949-01-02 291 SSJ null CHI null 1974-03-19 292 TWU 0 LAX 0.08 1969-10-05 293 VVV 2 MIN 0.075 1959-06-28 294 WWW 1 LED null null 295 XXX null MIN null null 296 ZAJ 2 CHI 0.075 1960-06-15 297 select * from t2; 298 a1 a2 a3 a4 299 AAA AAA AA AME 300 DEN Denver CO BDL 301 LAK Los Angeles CA TWU 302 LCC Los Angeles CA TWU 303 NOL New Orleans LA GTM 304 SDC San Diego CA TWU 305 TKF Seattle WA AME 306 select min(a1) from t1; 307 min(a1) 308 AME 309 select max(a4) from t1; 310 max(a4) 311 0.1 312 select min(a5), max(a5) from t1; 313 min(a5) max(a5) 314 1942-02-19 1977-09-23 315 select min(a3) from t1 where a2 = 2; 316 min(a3) 317 CHI 318 select min(a1), max(a1) from t1 where a4 = 0.080; 319 min(a1) max(a1) 320 BDL TWU 321 select min(t1.a5), max(t2.a3) from t1, t2; 322 min(t1.a5) max(t2.a3) 323 1942-02-19 WA 324 select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA'; 325 min(t1.a3) max(t2.a2) 326 DEN San Diego 327 select min(a1) from t1 where a1 > 'KKK'; 328 min(a1) 329 PLS 330 select min(a1) from t1 where a1 >= 'KKK'; 331 min(a1) 332 KKK 333 select max(a3) from t1 where a2 = 2 and a3 < 'SEA'; 334 max(a3) 335 MIN 336 select max(a5) from t1 where a5 < date'1970-01-01'; 337 max(a5) 338 1969-10-05 339 select max(a3) from t1 where a2 is null; 340 max(a3) 341 MIN 342 select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q'; 343 max(a3) 344 LAX 345 select min(a1), max(a1) from t1 where a1 between 'A' and 'P'; 346 min(a1) max(a1) 347 AME KKK 348 select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN'; 349 max(a3) 350 MIN 351 select max(a3) from t1 where a3 = 'MIN' and a2 = 2; 352 max(a3) 353 MIN 354 select max(a3) from t1 where a3 = 'DEN' and a2 = 2; 355 max(a3) 356 null 357 select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA'; 358 max(t1.a3) min(t2.a2) 359 CHI Los Angeles 360 select max(a3) from t1 where a2 is null and a2 = 2; 361 max(a3) 362 null 363 select max(a2) from t1 where a2 >= 1; 364 max(a2) 365 3 366 select min(a3) from t1 where a2 = 2 and a3 < 'SEA'; 367 min(a3) 368 CHI 369 select min(a3) from t1 where a2 = 4; 370 min(a3) 371 null 372 select min(a3) from t1 where a2 = 2 and a3 > 'SEA'; 373 min(a3) 374 null 375 select (min(a4)+max(a4))/2 from t1; 376 (min(a4)+max(a4))/2 377 0.085 378 select min(a3) from t1 where 2 = a2; 379 min(a3) 380 CHI 381 select max(a3) from t1 where a2 = 2 and 'SEA' > a3; 382 max(a3) 383 MIN 384 select max(a3) from t1 where a2 = 2 and 'SEA' < a3; 385 max(a3) 386 null 387 select min(a3) from t1 where a2 = 2 and a3 >= 'CHI'; 388 min(a3) 389 CHI 390 select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA'; 391 min(a3) 392 CHI 393 select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN'; 394 min(a3) 395 MIN 396 select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN'; 397 min(a3) 398 null 399 select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK'; 400 min(t1.a1) min(t2.a4) 401 AME AME 402 drop table t1; 403 drop table t2; 404 create table t1 (a int); 405 insert into t1 values (1); 406 select max(a) as b from t1 having b=1; 407 SQL syntax error: column "b" must appear in the GROUP BY clause or be used in an aggregate function 408 select a from t1 having a=1; 409 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 410 drop table t1; 411 CREATE TABLE t1 (a int primary key); 412 INSERT INTO t1 VALUES (1),(2),(3),(4); 413 SELECT MAX(a) FROM t1 WHERE a > 5; 414 MAX(a) 415 null 416 SELECT MIN(a) FROM t1 WHERE a < 0; 417 MIN(a) 418 null 419 DROP TABLE t1; 420 CREATE TABLE t1 (id int PRIMARY KEY, b char(3)); 421 INSERT INTO t1 VALUES (1,'xx'), (2,'aa'); 422 SELECT * FROM t1; 423 id b 424 1 xx 425 2 aa 426 SELECT MAX(b) FROM t1 WHERE b < 'ppppp'; 427 MAX(b) 428 aa 429 SELECT MAX(b) FROM t1 WHERE b < 'pp'; 430 MAX(b) 431 aa 432 DROP TABLE t1; 433 CREATE TABLE t1 (id int PRIMARY KEY, b char(16)); 434 INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa'); 435 SELECT MAX(b) FROM t1; 436 MAX(b) 437 xxxxbbbb 438 DROP TABLE t1; 439 create table t1 (col1 decimal(16,12)); 440 insert into t1 values (-5.00000000001); 441 insert into t1 values (-5.00000000001); 442 select col1,sum(col1),max(col1),min(col1) from t1 group by col1; 443 col1 sum(col1) max(col1) min(col1) 444 -5.000000000010 -10.000000000020 -5.000000000010 -5.000000000010 445 delete from t1; 446 insert into t1 values (5.00000000001); 447 insert into t1 values (5.00000000001); 448 select col1,sum(col1),max(col1),min(col1) from t1 group by col1; 449 col1 sum(col1) max(col1) min(col1) 450 5.000000000010 10.000000000020 5.000000000010 5.000000000010 451 DROP TABLE t1; 452 create table t1 (f1 int, f2 int, f3 date, f4 datetime); 453 insert into t1 values(98,1998,"1998-01-01","1998-01-01 00:00:00"); 454 insert into t1 values('00',2000,20000101,"2000-01-01 00:00:01"); 455 invalid argument operator cast, bad value [BIGINT DATE] 456 insert into t1 values(02,2002,20020101,"2002-01-01 23:59:59"); 457 invalid argument operator cast, bad value [BIGINT DATE] 458 insert into t1 values(60,2060,20600101,"2060-01-01 11:11:11"); 459 invalid argument operator cast, bad value [BIGINT DATE] 460 insert into t1 values(70,1970,19700101,"1970-11-11 22:22:22"); 461 invalid argument operator cast, bad value [BIGINT DATE] 462 insert into t1 values(NULL,NULL,NULL,NULL); 463 select min(f1),max(f1) from t1; 464 min(f1) max(f1) 465 0 98 466 select min(f2),max(f2) from t1; 467 min(f2) max(f2) 468 0 1998 469 select min(f3),max(f3) from t1; 470 min(f3) max(f3) 471 0001-01-01 1998-01-01 472 select min(f4),max(f4) from t1; 473 min(f4) max(f4) 474 0001-01-01 00:00:00 1998-01-01 00:00:00 475 drop table t1; 476 create table t1 (grp int, a bigint unsigned, c char(10) not null); 477 insert into t1 values (1,1,"a"); 478 insert into t1 values (2,2,"b"); 479 insert into t1 values (2,3,"c"); 480 insert into t1 values (3,4,"E"); 481 insert into t1 values (3,5,"C"); 482 insert into t1 values (3,6,"D"); 483 select max(distinct a),max(distinct grp) from t1; 484 max(distinct a) max(distinct grp) 485 6 3 486 insert into t1 values (null,null,''); 487 select max(distinct a),max(distinct grp) from t1; 488 max(distinct a) max(distinct grp) 489 6 3 490 drop table t1;