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

SELECT max(null);
max(null)
null
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;
max(a)
71
select max(b) from t1;
max(b)
71
select max(c) from t1;
max(c)
21
select max(d) from t1;
max(d)
43
select max(e) from t1;
max(e)
54
select max(f) from t1;
max(f)
53.5
select max(g) from t1;
max(g)
431.13
select max(h) from t1;
max(h)
124.3140000000000000000
select max(i) from t1;
max(i)
2012-03-22
select max(k) from t1;
max(k)
2019-03-12 10:03:12
select max(l) from t1;
max(l)
2032-03-12 13:04:12
select max(m) from t1;
max(m)
afbc
select max(n) from t1;
max(n)
dcvf
drop table t1;
select max(99999999999999999.99999);
max(99999999999999999.99999)
99999999999999999.99999
select max(999999999999999933193939.99999);
max(999999999999999933193939.99999)
999999999999999933193939.99999
select max(9999999999999999999999999999999999.9999999999999);
max(9999999999999999999999999999999999.9999999999999)
9999999999999999999999999999999999.9999999999999
create table t1(a bigint);
select max(a) from t1;
max(a)
null
insert into t1 values(null),(null),(null),(null);
select max(a) from t1;
max(a)
null
insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515);
select max(a) from t1;
max(a)
124125152651515
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;
a	big
1	-1
1	1234567890167
1	92233720368547
1	18446744073709515
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
-1	18446744073709515	18446744073709514
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
-1	18446744073709515	18446744073709514
insert into t1 (big) values (184467440737615);
select * from t1;
a	big
1	-1
1	1234567890167
1	92233720368547
1	18446744073709515
1	184467440737615
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
-1	18446744073709515	18446744073709514
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
-1	18446744073709515	18446744073709514
drop table t1;
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;
name
ä
å
ö
select concat_ws(",","*",name,"*") from t1 order by 1;
concat_ws(",","*",name,"*")
*,ä,*
*,å,*
*,ö,*
select min(name),min(concat_ws(",","*",name,"*")),max(name),max(concat_ws("*",name,"*")) from t1;
min(name)	min(concat_ws(",","*",name,"*"))	max(name)	max(concat_ws("*",name,"*"))
å	*,å,*	ö	ö**
drop table t1;
drop table t2;
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 distinct Fld1,  max( Fld2) as q from t1 group by Fld1 having q is not null;
fld1    q
1    20
3    50
select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null;
Fld1	max(Fld2)
1	20
3	50
select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
Fld1	max(Fld2)
1	20
3	50
select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null;
Fld1	max(Fld2)
1	20
3	50
select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
Fld1	max(Fld2)
1	20
3	50
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;
sum(a)
null
select count(distinct a),count(distinct grp) from t1;
count(distinct a)	count(distinct grp)
6	3
insert into t1 values (null,null,'');
select count(distinct a),count(distinct grp) from t1;
count(distinct a)	count(distinct grp)
6	3
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;
a1	a2
10	aaa
10	null
10	bbb
20	zzz
select min(a2) from t1;
min(a2)
aaa
select max(t1.a1), max(t2.a2) from t1, t2;
max(t1.a1)	max(t2.a2)
null	null
select max(t1.a1) from t1, t2;
max(t1.a1)
null
select max(t2.a2), max(t1.a1) from t1, t2;
max(t2.a2)	max(t1.a1)
null	null
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;
a1	a2	a1	a2
10	aaa	BBB	20
10	aaa	AAA	10
10	null	BBB	20
10	null	AAA	10
10	bbb	BBB	20
10	bbb	AAA	10
20	zzz	BBB	20
20	zzz	AAA	10
select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
max(t1.a1)	max(t2.a1)
null	null
select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9;
max(t2.a1)	max(t1.a1)
null	null
select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
a1	a2	a1	a2
10	aaa	BBB	20
10	aaa	AAA	10
10	null	BBB	20
10	null	AAA	10
10	bbb	BBB	20
10	bbb	AAA	10
20	zzz	null	null
select max(t1.a2) from t1 left outer join t2 on t1.a1=10;
max(t1.a2)
zzz
select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20;
max(t2.a1)
BBB
select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10;
max(t2.a1)
AAA
select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA';
max(t2.a1)
null
select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10;
max(t1.a2)	max(t2.a1)
zzz	BBB
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;
count(b)	sum(b)	avg(b)	std(b)	min(b)	max(b)	bit_and(b)	bit_or(b)
0	null	null	null	null	null	null	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;
a	count(b)	sum(b)	avg(b)	std(b)	min(b)	max(b)	bit_and(b)	bit_or(b)
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;
a	count(b)	sum(b)	avg(b)	std(b)	min(b)	max(b)	bit_and(b)	bit_or(b)
1	0	null	null	null	null	null	null	null
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;
a	count(b)	sum(b)	avg(b)	std(b)	min(b)	max(b)	bit_and(b)	bit_or(b)
1	0	null	null	null	null	null	null	null
2	0	null	null	null	null	null	null	null
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;
a	count(b)	sum(b)	avg(b)	std(b)	min(b)	max(b)	bit_and(b)	bit_or(b)
1	0	null	null	null	null	null	null	null
2	1	1	1.0	0.0	1	1	1	1
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;
a	count(b)	sum(b)	avg(b)	std(b)	min(b)	max(b)	bit_and(b)	bit_or(b)
1	0	null	null	null	null	null	null	null
2	1	1	1.0	0.0	1	1	1	1
3	1	1	1.0	0.0	1	1	1	1
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;
a1	a2	a3	a4	a5
AME	0	SEA	0.1	1942-02-19
BDL	0	DEN	0.08	1960-11-27
BMC	3	SEA	0.085	1958-09-08
BOT	2	SEA	0.085	1951-11-29
DTX	1	NYC	0.08	1961-05-04
GTM	3	DAL	0.07	1977-09-23
HBR	1	SEA	0.085	1948-03-05
KKK	3	ATL	null	null
PLS	1	WDC	0.075	1949-01-02
SSJ	null	CHI	null	1974-03-19
TWU	0	LAX	0.08	1969-10-05
VVV	2	MIN	0.075	1959-06-28
WWW	1	LED	null	null
XXX	null	MIN	null	null
ZAJ	2	CHI	0.075	1960-06-15
select * from t2;
a1	a2	a3	a4
AAA	AAA	AA	AME
DEN	Denver	CO	BDL
LAK	Los Angeles	CA	TWU
LCC	Los Angeles	CA	TWU
NOL	New Orleans	LA	GTM
SDC	San Diego	CA	TWU
TKF	Seattle	WA	AME
select min(a1) from t1;
min(a1)
AME
select max(a4) from t1;
max(a4)
0.1
select min(a5), max(a5) from t1;
min(a5)	max(a5)
1942-02-19	1977-09-23
select min(a3) from t1 where a2 = 2;
min(a3)
CHI
select min(a1), max(a1) from t1 where a4 = 0.080;
min(a1)	max(a1)
BDL	TWU
select min(t1.a5), max(t2.a3) from t1, t2;
min(t1.a5)	max(t2.a3)
1942-02-19	WA
select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA';
min(t1.a3)	max(t2.a2)
DEN	San Diego
select min(a1) from t1 where a1 > 'KKK';
min(a1)
PLS
select min(a1) from t1 where a1 >= 'KKK';
min(a1)
KKK
select max(a3) from t1 where a2 = 2 and a3 < 'SEA';
max(a3)
MIN
select max(a5) from t1 where a5 < date'1970-01-01';
max(a5)
1969-10-05
select max(a3) from t1 where a2 is null;
max(a3)
MIN
select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q';
max(a3)
LAX
select min(a1), max(a1) from t1 where a1 between 'A' and 'P';
min(a1)	max(a1)
AME	KKK
select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN';
max(a3)
MIN
select max(a3) from t1 where a3 = 'MIN' and a2 = 2;
max(a3)
MIN
select max(a3) from t1 where a3 = 'DEN' and a2 = 2;
max(a3)
null
select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA';
max(t1.a3)	min(t2.a2)
CHI	Los Angeles
select max(a3) from t1 where a2 is null and a2 = 2;
max(a3)
null
select max(a2) from t1 where a2 >= 1;
max(a2)
3
select min(a3) from t1 where a2 = 2 and a3 < 'SEA';
min(a3)
CHI
select min(a3) from t1 where a2 = 4;
min(a3)
null
select min(a3) from t1 where a2 = 2 and a3 > 'SEA';
min(a3)
null
select (min(a4)+max(a4))/2 from t1;
(min(a4)+max(a4))/2
0.085
select min(a3) from t1 where 2 = a2;
min(a3)
CHI
select max(a3) from t1 where a2 = 2 and 'SEA' > a3;
max(a3)
MIN
select max(a3) from t1 where a2 = 2 and 'SEA' < a3;
max(a3)
null
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI';
min(a3)
CHI
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA';
min(a3)
CHI
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN';
min(a3)
MIN
select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN';
min(a3)
null
select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK';
min(t1.a1)	min(t2.a4)
AME	AME
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;
b
1
select a from t1 having a=1;
SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
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;
MAX(a)
null
SELECT MIN(a) FROM t1 WHERE a < 0;
MIN(a)
null
DROP TABLE t1;
CREATE TABLE t1 (id int PRIMARY KEY, b char(3));
INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
SELECT * FROM t1;
id	b
1	xx
2	aa
SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
MAX(b)
aa
SELECT MAX(b) FROM t1 WHERE b < 'pp';
MAX(b)
aa
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;
MAX(b)
xxxxbbbb
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;
col1	sum(col1)	max(col1)	min(col1)
-5.000000000010	-10.000000000020	-5.000000000010	-5.000000000010
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;
col1	sum(col1)	max(col1)	min(col1)
5.000000000010	10.000000000020	5.000000000010	5.000000000010
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");
invalid argument operator cast, bad value [BIGINT DATE]
insert into t1 values(02,2002,20020101,"2002-01-01 23:59:59");
invalid argument operator cast, bad value [BIGINT DATE]
insert into t1 values(60,2060,20600101,"2060-01-01 11:11:11");
invalid argument operator cast, bad value [BIGINT DATE]
insert into t1 values(70,1970,19700101,"1970-11-11 22:22:22");
invalid argument operator cast, bad value [BIGINT DATE]
insert into t1 values(NULL,NULL,NULL,NULL);
select min(f1),max(f1) from t1;
min(f1)	max(f1)
0	98
select min(f2),max(f2) from t1;
min(f2)	max(f2)
0	1998
select min(f3),max(f3) from t1;
min(f3)	max(f3)
0001-01-01	1998-01-01
select min(f4),max(f4) from t1;
min(f4)	max(f4)
0001-01-01 00:00:00	1998-01-01 00:00:00
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;
max(distinct a)	    max(distinct grp)
6	3
insert into t1 values (null,null,'');
select max(distinct a),max(distinct grp) from t1;
max(distinct a)	    max(distinct grp)
6	3
drop table t1;

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