aggbuiltin.sql

derby database source code.good for you.

-- Note that bug 5704 occurs throughout this test
-- Decimal results may be outside the range of valid types in Cloudscape

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

-- empty table
create table empty (i int, s smallint, l bigint,
				c char(10), v varchar(50), lvc long varchar,
				d double precision, r real, 
				dt date, t time, ts timestamp,
				b char(2) for bit data, bv varchar(8) for bit data, lbv long varchar 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', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', x'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (1, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 200, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 222.22);
insert into t values (0, 100, 2000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 222.22);
insert into t values (0, 100, 1000000,
					  'goodbye', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'noone is here', 'jimmie noone was here',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  100.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 100.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'ffff', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'1111111111111111', X'1234', 111.11);

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

-- cannot aggregate datatypes that don't support NumberDataValue
select avg(c) from t;
select avg(v) from t;
select avg(lvc) from t;
select avg(dt) from t;
select avg(t) from t;
select avg(ts) from t;
select avg(b) from t;
select avg(bv) from t;
select avg(lbv) from t;

select avg(c) from t group by c;
select avg(v) from t group by c;
select avg(lvc) from t group by c;
select avg(dt) from t group by c;
select avg(t) from t group by c;
select avg(ts) from t group by c;
select avg(b) from t group by c;
select avg(bv) from t group by c;
select avg(lbv) from t group by c;

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

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

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

-- scalar
select avg(i) from empty;
select avg(s) from empty;
select avg(d) from empty;
select avg(l) from empty;
select avg(r) from empty;
select avg(dc) from empty;

-- variations
select avg(i), avg(s), avg(r), avg(l) from empty;
select avg(i+1) from empty;

-- vector
select avg(i) from empty group by i;
select avg(s) from empty group by s;
select avg(d) from empty group by d;
select avg(l) from empty group by l;
select avg(r) from empty group by r;
select avg(dc) from empty group by dc;


--------------------------------
-- BASIC ACCEPTANCE TESTS
--------------------------------
select avg(i) from t;
select avg(s) from t;
select avg(d) from t;
select avg(l) from t;
select avg(r) from t;
select avg(dc) from t;

select avg(i) from t group by i;
select avg(s) from t group by s;
select avg(d) from t group by d;
select avg(l) from t group by l;
select avg(r) from t group by r;
select avg(dc), sum(dc), count(dc) from t group by dc;


-- constants
select avg(1) from t;
select avg(1.1) from t;
select avg(1e1) from t;

select avg(1) from t group by i;
select avg(1.1) from t group by r;
select avg(1e1) from t group by r;

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

-- group by expression
select avg(expr1), avg(expr2)
from (select i * s, r * 2 from t) t (expr1, expr2) group by expr2, expr1;

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

-- insert select
create table tmp (x int, y smallint);
insert into tmp (x, y) select avg(i), avg(s) from t;
select * from tmp;
insert into tmp (x, y) select avg(i), avg(s) from t group by b;
select * from tmp;
drop table tmp;

-- some accuracy tests
create table tmp (x int);
insert into tmp values (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647);

values(2147483647);
select avg(x) from tmp;
select avg(-(x - 1)) from tmp;
select avg(x) from tmp group by x;
select avg(-(x - 1)) from tmp group by x;
drop table tmp;

-- now lets try some simple averages to see what
-- type of accuracy we get
create table tmp(x double precision, y int);
prepare scalar as 'select avg(x) from tmp';
prepare vector as 'select avg(x) from tmp group by y';
insert into tmp values (1,1);
execute scalar;
execute vector;
insert into tmp values (2,1);
execute scalar;
execute vector;
insert into tmp values (3,1);
execute scalar;
execute vector;
insert into tmp values (4,1);
execute scalar;
execute vector;
insert into tmp values (5,1);
execute scalar;
execute vector;
insert into tmp values (6,1);
execute scalar;
execute vector;
insert into tmp values (7,1);
execute scalar;
execute vector;
insert into tmp values (10000,1);
execute scalar;
execute vector;
remove vector;
remove scalar;
drop table tmp;

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

-- empty table
create table empty (i int, s smallint, l bigint,
				c char(10), v varchar(50), lvc long varchar,
				d double precision, r real, 
				dt date, t time, ts timestamp,
				b char(2) for bit data, bv varchar(8) for bit data, lbv long varchar 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', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (1, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 200, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 222.22);
insert into t values (0, 100, 2000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 222.22);
insert into t values (0, 100, 1000000,
					  'goodbye', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'noone is here', 'jimmie noone was here',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  100.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 100.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
					  X'12af', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'ffff', X'0000111100001111', X'1234', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'1111111111111111', X'1234', 111.11);

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

-- scalar
select count(i) from empty;
select count(s) from empty;
select count(l) from empty;
select count(c) from empty;
select count(v) from empty;
select count(lvc) from empty;
select count(d) from empty;
select count(r) from empty;
select count(dt) from empty;
select count(t) from empty;
select count(ts) from empty;
select count(b) from empty;
select count(bv) from empty;
-- bug: should fail in db2 mode
-- after for bit data is completely implemented
select count(lbv) from empty;
select count(dc) from empty;

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

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


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