aggbuiltin.out

derby database source code.good for you.

ij> -- 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));
0 rows inserted/updated/deleted
ij> -- 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));
0 rows inserted/updated/deleted
ij> -- populate tables
insert into t (i) values (null);
1 row inserted/updated/deleted
ij> insert into t (i) values (null);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', x'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 222.22);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 2000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 222.22);
ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'ffff', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'1111111111111111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> --------------------------------------
-- NEGATIVE TESTS
--------------------------------------
-- cannot aggregate datatypes that don't support NumberDataValue
select avg(c) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type CHAR.
ij> select avg(v) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type VARCHAR.
ij> select avg(lvc) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type LONG VARCHAR.
ij> select avg(dt) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type DATE.
ij> select avg(t) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type TIME.
ij> select avg(ts) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type TIMESTAMP.
ij> select avg(b) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type CHAR () FOR BIT DATA.
ij> select avg(bv) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type VARCHAR () FOR BIT DATA.
ij> select avg(lbv) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type LONG VARCHAR FOR BIT DATA.
ij> select avg(c) from t group by c;
ERROR 42Y22: Aggregate AVG cannot operate on type CHAR.
ij> select avg(v) from t group by c;
ERROR 42Y22: Aggregate AVG cannot operate on type VARCHAR.
ij> select avg(lvc) from t group by c;
ERROR 42Y22: Aggregate AVG cannot operate on type LONG VARCHAR.
ij> select avg(dt) from t group by c;
ERROR 42Y22: Aggregate AVG cannot operate on type DATE.
ij> select avg(t) from t group by c;
ERROR 42Y22: Aggregate AVG cannot operate on type TIME.
ij> select avg(ts) from t group by c;
ERROR 42Y22: Aggregate AVG cannot operate on type TIMESTAMP.
ij> select avg(b) from t group by c;
ERROR 42Y22: Aggregate AVG cannot operate on type CHAR () FOR BIT DATA.
ij> select avg(bv) from t group by c;
ERROR 42Y22: Aggregate AVG cannot operate on type VARCHAR () FOR BIT DATA.
ij> select avg(lbv) from t group by c;
ERROR 42Y22: Aggregate AVG cannot operate on type LONG VARCHAR FOR BIT DATA.
ij> -- long varchar datatypes too
create table t1 (c1 long varchar);
0 rows inserted/updated/deleted
ij> select avg(c1) from t1;
ERROR 42Y22: Aggregate AVG cannot operate on type LONG VARCHAR.
ij> drop table t1;
0 rows inserted/updated/deleted
ij> -- constants
select avg('hello') from t;
ERROR 42Y22: Aggregate AVG cannot operate on type CHAR.
ij> select avg(X'11') from t;
ERROR 42Y22: Aggregate AVG cannot operate on type CHAR () FOR BIT DATA.
ij> select avg(date('1999-06-06')) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type DATE.
ij> select avg(time('12:30:30')) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type TIME.
ij> select avg(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type TIMESTAMP.
ij> ---------------------------
-- NULL AGGREGATION
---------------------------
-- scalar
select avg(i) from empty;
1          
-----------
NULL       
ij> select avg(s) from empty;
1     
------
NULL  
ij> select avg(d) from empty;
1                     
----------------------
NULL                  
ij> select avg(l) from empty;
1                   
--------------------
NULL                
ij> select avg(r) from empty;
1            
-------------
NULL         
ij> select avg(dc) from empty;
1          
-----------
NULL       
ij> -- variations
select avg(i), avg(s), avg(r), avg(l) from empty;
1          |2     |3            |4                   
-----------------------------------------------------
NULL       |NULL  |NULL         |NULL                
ij> select avg(i+1) from empty;
1          
-----------
NULL       
ij> -- vector
select avg(i) from empty group by i;
1          
-----------
ij> select avg(s) from empty group by s;
1     
------
ij> select avg(d) from empty group by d;
1                     
----------------------
ij> select avg(l) from empty group by l;
1                   
--------------------
ij> select avg(r) from empty group by r;
1            
-------------
ij> select avg(dc) from empty group by dc;
1          
-----------
ij> --------------------------------
-- BASIC ACCEPTANCE TESTS
--------------------------------
select avg(i) from t;
1          
-----------
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(s) from t;
1     
------
107   
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(d) from t;
1                     
----------------------
192.85714285714286    
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(l) from t;
1                   
--------------------
1000000             
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(r) from t;
1            
-------------
192.85715    
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(dc) from t;
1          
-----------
119.0464   
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(i) from t group by i;
1          
-----------
0          
1          
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(s) from t group by s;
1     
------
100   
200   
NULL  
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(d) from t group by d;
1                     
----------------------
100.0                 
200.0                 
NULL                  
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(l) from t group by l;
1                   
--------------------
1000000             
NULL                
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(r) from t group by r;
1            
-------------
100.0        
200.0        
NULL         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select avg(dc), sum(dc), count(dc) from t group by dc;
1          |2          |3          
-----------------------------------
111.1100   |1444.43    |13         
222.2200   |222.22     |1          
NULL       |NULL       |0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- constants
select avg(1) from t;
1          
-----------
1          
ij> select avg(1.1) from t;
1        
---------
1.1000   
ij> select avg(1e1) from t;
1                     
----------------------
10.0                  
ij> select avg(1) from t group by i;
1          
-----------
1          
1          
1          
ij> select avg(1.1) from t group by r;
1        
---------
1.1000   
1.1000   
1.1000   
ij> select avg(1e1) from t group by r;
1                     
----------------------
10.0                  
10.0                  
10.0                  
ij> -- multicolumn grouping
select avg(i), avg(l), avg(r) from t group by i, dt, b;
1          |2                   |3            
----------------------------------------------
0          |1000000             |190.90909    
0          |1000000             |200.0        
0          |1000000             |200.0        
1          |1000000             |200.0        
NULL       |NULL                |NULL         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select i, dt, avg(i), avg(r), avg(l), l from t group by i, dt, b, l;
I          |DT        |3          |4            |5                   |L                   
------------------------------------------------------------------------------------------
0          |1992-01-01|0          |190.90909    |1000000             |1000000             
0          |1992-01-01|0          |200.0        |1000000             |1000000             
0          |1992-09-09|0          |200.0        |1000000             |1000000             
1          |1992-01-01|1          |200.0        |1000000             |1000000             
NULL       |NULL      |NULL       |NULL         |NULL                |NULL                
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- group by expression
select avg(expr1), avg(expr2)
from (select i * s, r * 2 from t) t (expr1, expr2) group by expr2, expr1;
1          |2            
-------------------------
0          |200.0        
0          |400.0        
100        |400.0        
NULL       |NULL         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- distinct and group by
select distinct avg(i) from t group by i, dt;
1          
-----------
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
1          
NULL       
ij> -- insert select
create table tmp (x int, y smallint);
0 rows inserted/updated/deleted
ij> insert into tmp (x, y) select avg(i), avg(s) from t;
1 row inserted/updated/deleted
ij> select * from tmp;
X          |Y     
------------------
0          |107   
ij> insert into tmp (x, y) select avg(i), avg(s) from t group by b;
3 rows inserted/updated/deleted
ij> select * from tmp;
X          |Y     
------------------
0          |107