aggbuiltin.sql

derby database source code.good for you.

-- drop tables
drop table t;
drop table empty;
-- ** insert max.sql
-- create an all types tables
create table t (i int, s smallint, l bigint,
				c char(10), v varchar(50), 
				d double precision, r real, 
				dt date, t time, ts timestamp,
				b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2));

-- empty table
create table empty (i int, s smallint, l bigint,
				c char(10), v varchar(50), 
				d double precision, r real, 
				dt date, t time, ts timestamp,
				b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2));
-- bit maps to Byte[], so can't test for now

-- populate tables
insert into t (i) values (null);
insert into t (i) values (null);

insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (1, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 200, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 222.22);
insert into t values (0, 100, 2000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 222.22);
insert into t values (0, 100, 1000000,
					  'goodbye', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'noone is here', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  100.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 100.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'ffff', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'1111111111111111', 111.11);

--------------------------------------
-- NEGATIVE TESTS
--------------------------------------

-- long varchar datatypes too
create table t1 (c1 long varchar);
select max(c1) from t1;
drop table t1;

---------------------------
-- NULL AGGREGATION
---------------------------

-- scalar
select max(i) from empty;
select max(s) from empty;
select max(l) from empty;
select max(c) from empty;
select max(v) from empty;
select max(d) from empty;
select max(r) from empty;
select max(dt) from empty;
select max(t) from empty;
select max(ts) from empty;
select max(b) from empty;
select max(bv) from empty;
select max(dc) from empty;

-- variations
select max(i), max(b), max(i), max(s) from empty;
select max(i+1) from empty;

-- vector
select max(i) from empty group by i;
select max(s) from empty group by s;
select max(l) from empty group by l;
select max(c) from empty group by c;
select max(v) from empty group by v;
select max(d) from empty group by d;
select max(r) from empty group by r;
select max(dt) from empty group by dt;
select max(t) from empty group by t;
select max(ts) from empty group by ts;
select max(b) from empty group by b;
select max(bv) from empty group by bv;
select max(dc) from empty group by dc;


--------------------------------
-- BASIC ACCEPTANCE TESTS
--------------------------------
select max(i) from t;
select max(s) from t;
select max(l) from t;
select max(c) from t;
select max(v) from t;
select max(d) from t;
select max(r) from t;
select max(dt) from t;
select max(t) from t;
select max(ts) from t;
select max(b) from t;
select max(bv) from t;
select max(dc) from t;

select max(i) from t group by i;
select max(s) from t group by s;
select max(l) from t group by l;
select max(c) from t group by c;
select max(v) from t group by v;
select max(d) from t group by d;
select max(r) from t group by r;
select max(dt) from t group by dt;
select max(t) from t group by t;
select max(ts) from t group by ts;
select max(b) from t group by b;
select max(bv) from t group by bv;
select max(dc) from t group by dc;

-- constants
select max(1) from t;
select max('hello') from t;
select max(1.1) from t;
select max(1e1) from t;
select max(X'11') from t;
select max(date('1999-06-06')) from t;
select max(time('12:30:30')) from t;
select max(timestamp('1999-06-06 12:30:30')) from t;

select max(1) from t group by i;
select max('hello') from t group by c;
select max(1.1) from t group by dc;
select max(1e1) from t group by d;
select max(X'11') from t group by b;
select max(date('1999-06-06')) from t group by dt;
select max(time('12:30:30')) from t group by t;
select max(timestamp('1999-06-06 12:30:30')) from t group by ts;

-- multicolumn grouping
select max(i), max(dt), max(b) from t group by i, dt, b;
select l, dt, max(i), max(dt), max(b), i from t group by i, dt, b, l; 

-- group by expression
select max(expr1), max(expr2)
from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1;

-- distinct and group by
select distinct max(i) from t group by i, dt;

-- insert select
create table tmp (x int, y char(20));
insert into tmp (x, y) select max(i), max(c) from t;
select * from tmp;
insert into tmp (x, y) select max(i), max(c) from t group by b;
select * from tmp;
drop table tmp;

-- drop tables
drop table t;
drop table empty;
-- ** insert min.sql
-- create an all types tables
create table t (i int, s smallint, l bigint,
				c char(10), v varchar(50), 
				d double precision, r real, 
				dt date, t time, ts timestamp,
				b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2));

-- empty table
create table empty (i int, s smallint, l bigint,
				c char(10), v varchar(50), 
				d double precision, r real, 
				dt date, t time, ts timestamp,
				b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2));

-- populate tables
insert into t (i) values (null);
insert into t (i) values (null);

insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (1, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 200, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 222.22);
insert into t values (0, 100, 2000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 222.22);
insert into t values (0, 100, 1000000,
					  'goodbye', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'noone is here', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  100.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 100.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'ffff', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'1111111111111111', 111.11);

--------------------------------------
-- NEGATIVE TESTS
--------------------------------------

-- long varchar datatypes too
create table t1 (c1 long varchar);
select min(c1) from t1;
drop table t1;

---------------------------
-- NULL AGGREGATION
---------------------------

-- scalar
select min(i) from empty;
select min(s) from empty;
select min(l) from empty;
select min(c) from empty;
select min(v) from empty;
select min(d) from empty;
select min(r) from empty;
select min(dt) from empty;
select min(t) from empty;
select min(ts) from empty;
select min(b) from empty;
select min(bv) from empty;
select min(dc) from empty;

-- variations
select min(i), min(b), min(i), min(s) from empty;
select min(i+1) from empty;

-- vector
select min(i) from empty group by i;
select min(s) from empty group by s;
select min(l) from empty group by l;
select min(c) from empty group by c;
select min(v) from empty group by v;
select min(d) from empty group by d;
select min(r) from empty group by r;
select min(dt) from empty group by dt;
select min(t) from empty group by t;
select min(ts) from empty group by ts;
select min(b) from empty group by b;
select min(bv) from empty group by bv;
select min(dc) from empty group by dc;


--------------------------------
-- BASIC ACCEPTANCE TESTS
--------------------------------
select min(i) from t;
select min(s) from t;
select min(l) from t;
select min(c) from t;
select min(v) from t;
select min(d) from t;
select min(r) from t;
select min(dt) from t;
select min(t) from t;
select min(ts) from t;
select min(b) from t;
select min(bv) from t;
select min(dc) from t;

select min(i) from t group by i;
select min(s) from t group by s;
select min(l) from t group by l;
select min(c) from t group by c;
select min(v) from t group by v;
select min(d) from t group by d;
select min(r) from t group by r;
select min(dt) from t group by dt;
select min(t) from t group by t;
select min(ts) from t group by ts;
select min(b) from t group by b;
select min(bv) from t group by bv;
select min(dc) from t group by dc;

-- constants
select min(1) from t;
select min('hello') from t;
select min(1.1) from t;
select min(1e1) from t;
select min(X'11') from t;
select min(date('1999-06-06')) from t;
select min(time('12:30:30')) from t;
select min(timestamp('1999-06-06 12:30:30')) from t;

select min(1) from t group by i;
select min('hello') from t group by c;
select min(1.1) from t group by dc;
select min(1e1) from t group by d;
select min(X'11') from t group by b;
select min(date('1999-06-06')) from t group by dt;
select min(time('12:30:30')) from t group by t;
select min(timestamp('1999-06-06 12:30:30')) from t group by ts;

-- multicolumn grouping
select min(i), min(dt), min(b) from t group by i, dt, b;
select l, dt, min(i), min(dt), min(b), i from t group by i, dt, b, l; 

-- group by expression
select min(expr1), min(expr2)
from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1;

-- distinct and group by
select distinct min(i) from t group by i, dt;

-- insert select
create table tmp (x int, y char(20));
insert into tmp (x, y) select min(i), min(c) from t;
select * from tmp;
insert into tmp (x, y) select min(i), min(c) from t group by b;
select * from tmp;
drop table tmp;

-- drop tables
drop table t;
drop table empty;