github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_max.test

#NULL
SELECT max(null);

#DATATYPE
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));
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");
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");
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");
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");
select max(a) from t1;
select max(b) from t1;
select max(c) from t1;
select max(d) from t1;
select max(e) from t1;
select max(f) from t1;
select max(g) from t1;
select max(h) from t1;
select max(i) from t1;
select max(k) from t1;
select max(l) from t1;
select max(m) from t1;
select max(n) from t1;
drop table t1;


#0.5暂不支持time类型
#create table t1(a time)
#insert into t1 values("10:03:12");
#insert into t1 values("10:03:12");
#insert into t1 values("10:03:12");
#insert into t1 values("10:03:12");
#select max(a) from t1;
#drop table t1;

#EXTREME VALUE, 算术操作
select max(99999999999999999.99999);
select max(999999999999999933193939.99999);
select max(9999999999999999999999999999999999.9999999999999);

create table t1(a bigint);
select max(a) from t1;
insert into t1 values(null),(null),(null),(null);
select max(a) from t1;
insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515);
select max(a) from t1;
drop table t1;
create table t1 ( a int not null default 1, big bigint );
insert into t1 (big) values (-1),(1234567890167),(92233720368547),(18446744073709515);
select * from t1;
select min(big),max(big),max(big)-1 from t1;
select min(big),max(big),max(big)-1 from t1 group by a;
insert into t1 (big) values (184467440737615);
select * from t1;
select min(big),max(big),max(big)-1 from t1;
select min(big),max(big),max(big)-1 from t1 group by a;
drop table t1;


#DATA type
create table t1 (name char(20) not null);
create table t2 (name char(20) not null);
insert into t1 values ("å");
insert into t1 values ("ä");
insert into t1 values ("ö");
insert into t2 select * from t1;
select * from t1 order by name;
select concat_ws(",","*",name,"*") from t1 order by 1;
-- @bvt:issue#3344
select min(name),min(concat_ws(",","*",name,"*")),max(name),max(concat_ws("*",name,"*")) from t1;
-- @bvt:issue
drop table t1;
drop table t2;

#HAVING,DISTINCT
CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL);
INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50);
#select Fld1, max(distinct Fld2) as q from t1 group by Fld1 having q is not null;
select distinct Fld1,  max( Fld2) as q from t1 group by Fld1 having q is not null;
select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null;
select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null;
select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
drop table t1;



create table t1 (grp int, a bigint unsigned, c char(10) not null);
insert into t1 values (1,1,"a");
insert into t1 values (2,2,"b");
insert into t1 values (2,3,"c");
insert into t1 values (3,4,"E");
insert into t1 values (3,5,"C");
insert into t1 values (3,6,"D");
select sum(a) from t1 where a > 10;
select count(distinct a),count(distinct grp) from t1;
insert into t1 values (null,null,'');
select count(distinct a),count(distinct grp) from t1;
create table t2 (grp int, a bigint unsigned, c char(10));
insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp;
drop table t1;
drop table t2;



create table t1 (a1 int, a2 char(3));
insert into t1 values(10,'aaa'), (10,null), (10,'bbb'), (20,'zzz');
create table t2(a1 char(3), a2 int, a3 real);
select * from t1;
-- @bvt:issue#3344
select min(a2) from t1;
-- @bvt:issue
select max(t1.a1), max(t2.a2) from t1, t2;
select max(t1.a1) from t1, t2;
select max(t2.a2), max(t1.a1) from t1, t2;
insert into t2 values('AAA', 10, 0.5);
insert into t2 values('BBB', 20, 1.0);
select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;
select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9;
select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
select max(t1.a2) from t1 left outer join t2 on t1.a1=10;
select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20;
select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10;
select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA';
select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10;
drop table t1;
drop table t2;


CREATE TABLE t1 (a int, b int);
select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1;
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;
insert into t1 values (1,null);
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;
insert into t1 values (1,null);
insert into t1 values (2,null);
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;
insert into t1 values (2,1);
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;
insert into t1 values (3,1);
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;
drop table t1;



create table t1(a1 char(3) primary key,a2 smallint,a3 char(3),a4 real,a5 date);
create table t2(a1 char(3) primary key,a2 char(17),a3 char(2),a4 char(3));
insert into t1 values('AME',0,'SEA',0.100,date'1942-02-19');
insert into t1 values('HBR',1,'SEA',0.085,date'1948-03-05');
insert into t1 values('BOT',2,'SEA',0.085,date'1951-11-29');
insert into t1 values('BMC',3,'SEA',0.085,date'1958-09-08');
insert into t1 values('TWU',0,'LAX',0.080,date'1969-10-05');
insert into t1 values('BDL',0,'DEN',0.080,date'1960-11-27');
insert into t1 values('DTX',1,'NYC',0.080,date'1961-05-04');
insert into t1 values('PLS',1,'WDC',0.075,date'1949-01-02');
insert into t1 values('ZAJ',2,'CHI',0.075,date'1960-06-15');
insert into t1 values('VVV',2,'MIN',0.075,date'1959-06-28');
insert into t1 values('GTM',3,'DAL',0.070,date'1977-09-23');
insert into t1 values('SSJ',null,'CHI',null,date'1974-03-19');
insert into t1 values('KKK',3,'ATL',null,null);
insert into t1 values('XXX',null,'MIN',null,null);
insert into t1 values('WWW',1,'LED',null,null);

insert into t2 values('TKF','Seattle','WA','AME');
insert into t2 values('LCC','Los Angeles','CA','TWU');
insert into t2 values('DEN','Denver','CO','BDL');
insert into t2 values('SDC','San Diego','CA','TWU');
insert into t2 values('NOL','New Orleans','LA','GTM');
insert into t2 values('LAK','Los Angeles','CA','TWU');
insert into t2 values('AAA','AAA','AA','AME');


select * from t1;
select * from t2;
select min(a1) from t1;
select max(a4) from t1;
select min(a5), max(a5) from t1;
select min(a3) from t1 where a2 = 2;
select min(a1), max(a1) from t1 where a4 = 0.080;
select min(t1.a5), max(t2.a3) from t1, t2;
select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA';
select min(a1) from t1 where a1 > 'KKK';
select min(a1) from t1 where a1 >= 'KKK';
select max(a3) from t1 where a2 = 2 and a3 < 'SEA';
select max(a5) from t1 where a5 < date'1970-01-01';
select max(a3) from t1 where a2 is null;
select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q';
select min(a1), max(a1) from t1 where a1 between 'A' and 'P';
select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN';
select max(a3) from t1 where a3 = 'MIN' and a2 = 2;
select max(a3) from t1 where a3 = 'DEN' and a2 = 2;
select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA';
select max(a3) from t1 where a2 is null and a2 = 2;
select max(a2) from t1 where a2 >= 1;
select min(a3) from t1 where a2 = 2 and a3 < 'SEA';
select min(a3) from t1 where a2 = 4;
select min(a3) from t1 where a2 = 2 and a3 > 'SEA';
select (min(a4)+max(a4))/2 from t1;
select min(a3) from t1 where 2 = a2;
select max(a3) from t1 where a2 = 2 and 'SEA' > a3;
select max(a3) from t1 where a2 = 2 and 'SEA' < a3;
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI';
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA';
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN';
select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN';
select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK';
drop table t1;
drop table t2;


create table t1 (a int);
insert into t1 values (1);
select max(a) as b from t1 having b=1;
select a from t1 having a=1;
drop table t1;


CREATE TABLE t1 (a int primary key);
INSERT INTO t1 VALUES (1),(2),(3),(4);

SELECT MAX(a) FROM t1 WHERE a > 5;
SELECT MIN(a) FROM t1 WHERE a < 0;

DROP TABLE t1;



CREATE TABLE t1 (id int PRIMARY KEY, b char(3));
INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
SELECT * FROM t1;
SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
SELECT MAX(b) FROM t1 WHERE b < 'pp';
DROP TABLE t1;


CREATE TABLE t1 (id int PRIMARY KEY, b char(16));
INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa');
SELECT MAX(b) FROM t1;
DROP TABLE t1;


create table t1 (col1 decimal(16,12));
insert into t1 values (-5.00000000001);
insert into t1 values (-5.00000000001);
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
delete from t1;
insert into t1 values (5.00000000001);
insert into t1 values (5.00000000001);
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
DROP TABLE t1;



create table t1 (f1 int, f2 int, f3 date, f4 datetime);
insert into t1 values(98,1998,"1998-01-01","1998-01-01 00:00:00");
insert into t1 values('00',2000,20000101,"2000-01-01 00:00:01");
insert into t1 values(02,2002,20020101,"2002-01-01 23:59:59");
insert into t1 values(60,2060,20600101,"2060-01-01 11:11:11");
insert into t1 values(70,1970,19700101,"1970-11-11 22:22:22");
insert into t1 values(NULL,NULL,NULL,NULL);
-- @bvt:issue#3523
select min(f1),max(f1) from t1;
select min(f2),max(f2) from t1;
select min(f3),max(f3) from t1;
select min(f4),max(f4) from t1;
-- @bvt:issue
drop table t1;

create table t1 (grp int, a bigint unsigned, c char(10) not null);
insert into t1 values (1,1,"a");
insert into t1 values (2,2,"b");
insert into t1 values (2,3,"c");
insert into t1 values (3,4,"E");
insert into t1 values (3,5,"C");
insert into t1 values (3,6,"D");
select max(distinct a),max(distinct grp) from t1;
insert into t1 values (null,null,'');
select max(distinct a),max(distinct grp) from t1;
drop table t1;

CREATE TABLE t1 (a INT);
INSERT INTO t1 SELECT result FROM generate_series(1,100000) g;
SELECT MAX(a) FROM t1;
DELETE FROM t1 WHERE a>50000;
SELECT MAX(a) FROM t1;
DELETE FROM t1 WHERE a%2=0;
SELECT MAX(a) FROM t1;
DROP TABLE t1;