aggbuiltin.out

derby database source code.good for you.

15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(s) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(l) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(c) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(v) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(lvc) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(d) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(r) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(dt) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(t) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(ts) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(b) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(bv) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(lbv) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(dc) from t;
1          
-----------
15         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(i) from t group by i;
1          
-----------
14         
1          
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(s) from t group by s;
1          
-----------
14         
1          
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(l) from t group by l;
1          
-----------
14         
1          
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(c) from t group by c;
1          
-----------
14         
1          
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(v) from t group by v;
1          
-----------
1          
14         
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(d) from t group by d;
1          
-----------
1          
14         
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(r) from t group by r;
1          
-----------
1          
14         
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(dt) from t group by dt;
1          
-----------
14         
1          
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(t) from t group by t;
1          
-----------
14         
1          
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(ts) from t group by ts;
1          
-----------
14         
1          
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(b) from t group by b;
1          
-----------
14         
1          
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(bv) from t group by bv;
1          
-----------
14         
1          
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(lbv) from t group by lbv;
ERROR X0X67: Columns of type 'LONG VARCHAR FOR BIT DATA' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> select count(dc) from t group by dc;
1          
-----------
13         
2          
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- constants
select count(1) from t;
1          
-----------
17         
ij> select count('hello') from t;
1          
-----------
17         
ij> select count(1.1) from t;
1          
-----------
17         
ij> select count(1e1) from t;
1          
-----------
17         
ij> select count(X'11') from t;
1          
-----------
17         
ij> select count(date('1999-06-06')) from t;
1          
-----------
17         
ij> select count(time('12:30:30')) from t;
1          
-----------
17         
ij> select count(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t;
1          
-----------
17         
ij> select count(1) from t group by i;
1          
-----------
14         
1          
2          
ij> select count('hello') from t group by c;
1          
-----------
14         
1          
2          
ij> select count(1.1) from t group by dc;
1          
-----------
13         
2          
2          
ij> select count(1e1) from t group by r;
1          
-----------
1          
14         
2          
ij> select count(X'11') from t group by b;
1          
-----------
14         
1          
2          
ij> select count(date('1999-06-06')) from t group by dt;
1          
-----------
14         
1          
2          
ij> select count(time('12:30:30')) from t group by t;
1          
-----------
14         
1          
2          
ij> select count(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t group by ts;
1          
-----------
14         
1          
2          
ij> -- multicolumn grouping
select count(i), count(dt), count(b) from t group by i, dt, b;
1          |2          |3          
-----------------------------------
12         |12         |12         
1          |1          |1          
1          |1          |1          
1          |1          |1          
0          |0          |0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select l, dt, count(i), count(dt), count(b), i from t group by i, dt, b, l;
L                   |DT        |3          |4          |5          |I          
-------------------------------------------------------------------------------
1000000             |1992-01-01|11         |11         |11         |0          
2000000             |1992-01-01|1          |1          |1          |0          
1000000             |1992-01-01|1          |1          |1          |0          
1000000             |1992-09-09|1          |1          |1          |0          
1000000             |1992-01-01|1          |1          |1          |1          
NULL                |NULL      |0          |0          |0          |NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- group by expression
select count(expr1), count(expr2)
from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1;
1          |2          
-----------------------
1          |1          
12         |12         
1          |1          
1          |1          
0          |0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- distinct and group by
select distinct count(i) from t group by i, dt;
1          
-----------
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
1          
13         
ij> -- insert select
create table tmp (x int, y smallint);
0 rows inserted/updated/deleted
ij> insert into tmp (x, y) select count(i), count(c) from t;
1 row inserted/updated/deleted
ij> select * from tmp;
X          |Y     
------------------
15         |15    
ij> insert into tmp (x, y) select count(i), count(c) from t group by b;
3 rows inserted/updated/deleted
ij> select * from tmp;
X          |Y     
------------------
15         |15    
14         |14    
1          |1     
0          |0     
ij> drop table tmp;
0 rows inserted/updated/deleted
ij> -- drop tables
drop table t;
0 rows inserted/updated/deleted
ij> drop table empty;
0 rows inserted/updated/deleted
ij> -- ** insert countStar.sql
-- Test the COUNT() aggregate
-- 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);
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);
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'ABCD');
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'ABCD');
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'ABCD');
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'ABCD');
1 row inserted/updated/deleted
ij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
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'ABCD');
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'ABCD');
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'ABCD');
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'ABCD');