aggregate.out

derby database source code.good for you.

ij> -- ** insert aggregatesPositive.sql
autocommit on;
ij> -- General aggregate tests.  Aggregate
-- specifics are tested in specific test (e.g. sum.jsql).
--
-- Note that this test does NOT test multiple datatypes,
-- that is exercised in the specific aggregate tests.
-- INSERT SELECT is also in the specific aggregate tests.
-- 
-- need to add: objects
create table t1 (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> create table t2 (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> create table oneRow (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> insert into oneRow values(1,1);
1 row inserted/updated/deleted
ij> create table empty (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> create table emptyNull (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> insert into emptyNull values (null, null);
1 row inserted/updated/deleted
ij> insert into t1 values (null, null), (1,1), (null, null), (2,1), (3,1), (10,10);
6 rows inserted/updated/deleted
ij> insert into t2 values (null, null), (1,1), (null, null), (2,1), (3,1), (10,10);
6 rows inserted/updated/deleted
ij> select * from t1;
C1         |C2         
-----------------------
NULL       |NULL       
1          |1          
NULL       |NULL       
2          |1          
3          |1          
10         |10         
ij> --------------------------------------
-- Expressions within an aggregate
--------------------------------------
select max(c1+10) from t1;
1          
-----------
20         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(c1+10) from t1 group by c2;
1          
-----------
13         
20         
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(2*10) from t1;
1          
-----------
20         
ij> select max(2*10) from t1 group by c2;
1          
-----------
20         
20         
20         
ij> -- conditional operator within aggregate
select max(case when c1 <> 1 then 666 else 999 end) from oneRow;
1          
-----------
999        
ij> select max(case when c1 = 1 then 666 else c2 end) from oneRow;
1          
-----------
666        
ij> select max(case when c1 = 1 then 666 else c1 end) from oneRow;
1          
-----------
666        
ij> -- subquery in aggregate
select max((select c1 from empty)) from t1;
1          
-----------
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- cast to string in aggregate
select max(cast (c1 as char(1))) from oneRow;
1   
----
1   
ij> -- cast to string in aggregate and concatenate with another
select max(cast(c1 as char(1)) || cast (c2 as char(1))) from oneRow;
1   
----
11  
ij> -- unary
select max(-c1) from t1;
1          
-----------
-1         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- count
select count(c1) from t1;
1          
-----------
4          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- cast
select count(cast (null as int)) from t1;
1          
-----------
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- avg
-- DB2 returns error 22003
-- CS returns no error!
select avg(2147483647) from t1;
1          
-----------
2147483647 
ij> --------------------------------------
-- Expressions on an aggregates/with aggregates
--------------------------------------
select 10+sum(c1) from t1;
1          
-----------
26         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select 10+sum(c1+10) from t1;
1          
-----------
66         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- conditional operator on aggregate
select (case when max(c1) = 1 then 666 else 1 end) from t1;
1          
-----------
1          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select (case when max(c1) = 1 then 666 else c1 end) from t1 group by c1;
1          
-----------
666        
2          
3          
10         
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- method call on aggregate, cannot use nulls
select cast (max(c1) as char(1)) from oneRow;
1   
----
1   
ij> select cast (max(c1) as char(1)) from oneRow group by c1;
1   
----
1   
ij> select (cast(c1 as char(1)) || (cast (max(c2) as char(1)))) from oneRow group by c1;
1   
----
11  
ij> -- subquery on aggregate
select (select max(c1) from t2)from t1;
1          
-----------
10         
10         
10         
10         
10         
10         
ij> select (select max(c1) from oneRow group by c2)from t1;
1          
-----------
1          
1          
1          
1          
1          
1          
ij> -- unary
select -max(c1) from t1;
1          
-----------
-10        
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select -max(c1) from t1 group by c1;
1          
-----------
-1         
-2         
-3         
-10        
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- cast
select cast (null as int), count(c1) from t1 group by c1;
1          |2          
-----------------------
NULL       |1          
NULL       |1          
NULL       |1          
NULL       |1          
NULL       |0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(cast (null as int)) from t1 group by c1;
1          
-----------
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
0          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- binary list operator
-- beetle 5571 - transient boolean type not allowed in DB2
select (1 in (1,2)), count(c1) from t1 group by c1;
ERROR 42X01: Syntax error: Encountered "in" at line 3, column 11.
ij> select count((1 in (1,2))) from t1 group by c1;
1          
-----------
1          
1          
1          
1          
2          
ij> -- some group by specific tests
select c2, 10+sum(c1), c2 from t1 group by c2;
C2         |2          |C2         
-----------------------------------
1          |16         |1          
10         |20         |10         
NULL       |NULL       |NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select c2, 10+sum(c1+10), c2*2 from t1 group by c2;
C2         |2          |3          
-----------------------------------
1          |46         |2          
10         |30         |20         
NULL       |NULL       |NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select c2+sum(c1)+c2 from t1 group by c2;
1          
-----------
8          
30         
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select (c2+sum(c1)+c2)+10, c1, c2 from t1 group by c1, c2;
1          |C1         |C2         
-----------------------------------
13         |1          |1          
14         |2          |1          
15         |3          |1          
40         |10         |10         
NULL       |NULL       |NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select c1+10, c2, c1*1, c1, c2*5 from t1 group by c1, c2;
1          |C2         |3          |C1         |5          
-----------------------------------------------------------
11         |1          |1          |1          |5          
12         |1          |2          |2          |5          
13         |1          |3          |3          |5          
20         |10         |10         |10         |50         
NULL       |NULL       |NULL       |NULL       |NULL       
ij> --------------------------------------
-- Distincts
--------------------------------------
select sum(c1) from t1;
1          
-----------
16         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1) from t1;
1          
-----------
16         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1), sum(c1) from t1;
1          |2          
-----------------------
16         |16         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1), sum(c1) from oneRow;
1          |2          
-----------------------
1          |1          
ij> select max(c1), sum(distinct c1), sum(c1) from t1;
1          |2          |3          
-----------------------------------
10         |16         |16         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1) from empty;
1          
-----------
NULL       
ij> select sum(distinct c1) from emptyNull;
1          
-----------
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(c1) from t1 group by c2;
1          
-----------
6          
10         
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1) from t1 group by c2;
1          
-----------
6          
10         
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1), sum(c1) from t1 group by c2;
1          |2          
-----------------------
6          |6          
10         |10         
NULL       |NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1), sum(c1) from oneRow group by c2;
1          |2          
-----------------------
1          |1          
ij> select max(c1), sum(distinct c1), sum(c1) from t1 group by c2;
1          |2          |3          
-----------------------------------
3          |6          |6          
10         |10         |10         
NULL       |NULL       |NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select c2, max(c1), c2+1, sum(distinct c1), c2+2, sum(c1) from t1 group by c2;
C2         |2          |3          |4          |5          |6          
-----------------------------------------------------------------------
1          |3          |2          |6          |3          |6          
10         |10         |11         |10         |12         |10         
NULL       |NULL       |NULL       |NULL       |NULL       |NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1) from empty group by c2;
1          
-----------
ij> select sum(distinct c1) from emptyNull group by c2;
1          
-----------
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> --------------------------------------
-- Subqueries in where clause
--------------------------------------
-- subqueries that might return more than 1 row
select c1 from t1 where c1 not in (select sum(c1) from t2);
C1         
-----------
1          
WARNING 01003: Null values were eliminated from the argument of a column function.
2          
3          
10         
ij> select c1 from t1 where c1 not in (select sum(distinct c1) from t2);
C1         
-----------
1          
WARNING 01003: Null values were eliminated from the argument of a column function.
2          
3          
10         
ij> select c1 from t1 where c1 not in (select sum(distinct c1)+10 from t2);
C1         
-----------
1          
WARNING 01003: Null values were eliminated from the argument of a column function.
2          
3          
10         
ij> select c1 from t1 where c1 in (select max(c1) from t2 group by c2);
C1         
-----------
3          
WARNING 01003: Null values were eliminated from the argument of a column function.
10         
ij> select c1 from t1 where c1 in (select max(distinct c1) from t2 group by c2);
C1         
-----------
3          
WARNING 01003: Null values were eliminated from the argument of a column function.
10         
ij> select c1 from t1 where c1 in (select max(distinct c1)+10 from t2 group by c2);
C1         
-----------
ij> -- subqueries that return 1 row
select c1 from t1 where c1 = (select max(c1) from t2);
C1         
-----------
10         
ij> select c1 from t1 where c1 = (select max(distinct c1) from t2);
C1         
-----------
10         
ij> select c1 from t1 where c1 = (select max(distinct c1)+10 from t2);
C1         
-----------
ij> select c1 from t1 where c1 = (select max(c1) from oneRow group by c2);
C1         
-----------
1          
ij> select c1 from t1 where c1 = (select max(distinct c1) from oneRow group by c2);
C1         
-----------
1          
ij> select c1 from t1 where c1 = (select max(distinct c1)+10 from oneRow group by c2);
C1         
-----------
ij> --------------------------------------
-- From Subqueries (aka table expressions)
--------------------------------------
select tmpC1 from 
	(select max(c1+10) from t1) as tmp (tmpC1);
TMPC1      
-----------
20         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(tmpC1) from 
	(select max(c1+10) from t1) as tmp (tmpC1);
1          
-----------
20         
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select tmpC1 from 
	(select max(c1+10) from t1 group by c2) as tmp (tmpC1);
TMPC1      
-----------
13         
20         
NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(tmpC1) from 
	(select max(c1+10) from t1 group by c2) as tmp (tmpC1);
1          
-----------
20         
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(tmpC1), tmpC2 from 
	(select max(c1+10), c2 from t1 group by c2) as tmp (tmpC1, tmpC2)
group by tmpC2;