groupby.out

derby database source code.good for you.

ij> -- negative tests for group by and having clauses
create table t1 (a int, b int, c int);
0 rows inserted/updated/deleted
ij> create table t2 (a int, b int, c int);
0 rows inserted/updated/deleted
ij> insert into t2 values (1,1,1), (2,2,2);
2 rows inserted/updated/deleted
ij> -- group by position
select * from t1 group by 1;
ERROR 42X01: Syntax error: Encountered "1" at line 2, column 27.
ij> -- column in group by list not in from list
select a as d from t1 group by d;
ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'D' is not a column in the target table.
ij> -- column in group by list not in select list
select a as b from t1 group by b;
ERROR 42Y36: Column reference 'A' is invalid.  For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.  
ij> select a from t1 group by b;
ERROR 42Y36: Column reference 'A' is invalid.  For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.  
ij> select a, char(b) from t1 group by a;
ERROR 42Y36: Column reference 'B' is invalid.  For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.  
ij> -- columns in group by list must be unique
select a, b from t1 group by a, a;
ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
ij> select a, b from t1 group by a, t1.a;
ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
ij> -- cursor with group by is not updatable
get cursor c1 as 'select a from t1 group by a for update';
ERROR 42Y90: FOR UPDATE is not permitted in this type of statement.  
ij> -- noncorrelated subquery that returns too many rows
select a, (select a from t2) from t1 group by a;
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- correlation on outer table
select t2.a, (select b from t1 where t1.b = t2.b) from t1 t2 group by t2.a;
ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.  
ij> -- having clause
-- cannot contain column references which are not grouping columns
select a from t1 group by a having c = 1;
ERROR 42X04: Column 'C' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C' is not a column in the target table.
ij> select a from t1 o group by a having a = (select a from t1 where b = b.o);
ERROR 42X04: Column 'B.O' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B.O' is not a column in the target table.
ij> -- ?s in group by
select a from t1 group by ?;
ERROR 42X01: Syntax error: Encountered "?" at line 2, column 27.
ij> -- group by on long varchar type
create table unmapped(c1 long varchar);
0 rows inserted/updated/deleted
ij> select c1, max(1) from unmapped group by c1;
ERROR X0X67: Columns of type 'LONG VARCHAR' 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> -- clean up
drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table unmapped;
0 rows inserted/updated/deleted
ij> -- Test group by and having clauses with no aggregates
-- 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(2) for bit data, lbv long varchar for bit data);
0 rows inserted/updated/deleted
ij> create table tab1 (
				i integer, 
				s smallint, 
				l bigint,
				c char(30),
				v varchar(30),
				lvc long varchar,
				d double precision,
				r real,
				dt date, 
				t time, 
				ts timestamp);
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,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (1, 100, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 200, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 2000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'goodbye', 'everyone is here', 'adios, muchachos',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'hello', 'noone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  100.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 100.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'0f0f', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'ffff', X'0f0f', X'1234');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
					  'hello', 'everyone is here', 'what the heck do we care?',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
					  X'12af', X'ffff', X'ABCD');
1 row inserted/updated/deleted
ij> -- bit maps to Byte[], so can't test for now
insert into tab1
select i, s, l, c, v, lvc, d, r, dt, t, ts from t;
17 rows inserted/updated/deleted
ij> -- simple grouping
select i from t group by i order by i;
I          
-----------
0          
1          
NULL       
ij> select s from t group by s order by s;
S     
------
100   
200   
NULL  
ij> select l from t group by l order by l;
L                   
--------------------
1000000             
2000000             
NULL                
ij> select c from t group by c order by c;
C         
----------
goodbye   
hello     
NULL      
ij> select v from t group by v order by v;
V                                                 
--------------------------------------------------
everyone is here                                  
noone is here                                     
NULL                                              
ij> select d from t group by d order by d;
D                     
----------------------
100.0                 
200.0                 
NULL                  
ij> select r from t group by r order by r;
R            
-------------
100.0        
200.0        
NULL         
ij> select dt from t group by dt order by dt;
DT        
----------
1992-01-01
1992-09-09
NULL      
ij> select t from t group by t order by t;
T       
--------
12:30:30
12:55:55
NULL    
ij> select ts from t group by ts order by ts;
TS                        
--------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx
xxxxxxFILTERED-TIMESTAMPxxxxx
NULL                      
ij> select b from t group by b order by b;
B   
----
12af
ffff
NULL
ij> select bv from t group by bv order by bv;
BV  
----
0f0f
ffff
NULL
ij> -- grouping by long varchar [for bit data] cols should fail in db2 mode
select lbv from t group by lbv order 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> -- multicolumn grouping
select i, dt, b from t where 1=1 group by i, dt, b order by i,dt,b;
I          |DT        |B   
---------------------------
0          |1992-01-01|12af
0          |1992-01-01|ffff
0          |1992-09-09|12af
1          |1992-01-01|12af
NULL       |NULL      |NULL
ij> select i, dt, b from t group by i, dt, b order by i,dt,b;
I          |DT        |B   
---------------------------
0          |1992-01-01|12af
0          |1992-01-01|ffff
0          |1992-09-09|12af
1          |1992-01-01|12af
NULL       |NULL      |NULL
ij> select i, dt, b from t group by b, i, dt order by i,dt,b;
I          |DT        |B   
---------------------------
0          |1992-01-01|12af
0          |1992-01-01|ffff
0          |1992-09-09|12af
1          |1992-01-01|12af
NULL       |NULL      |NULL
ij> select i, dt, b from t group by dt, i, b order by i,dt,b;
I          |DT        |B   
---------------------------
0          |1992-01-01|12af
0          |1992-01-01|ffff
0          |1992-09-09|12af
1          |1992-01-01|12af
NULL       |NULL      |NULL
ij> -- group by expression
select expr1, expr2
from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1 order by expr2,expr1;
EXPR1      |EXPR2                                                       
------------------------------------------------------------------------
0          |goodbye   everyone is here                                  
0          |hello     everyone is here                                  
100        |hello     everyone is here                                  
0          |hello     noone is here                                     
NULL       |NULL                                                        
ij> -- group by correlated subquery
select i, expr1
from (select i, (select distinct i from t m where m.i = t.i) from t) t (i, expr1)
 group by i, expr1 order by i,expr1;
I          |EXPR1      
-----------------------
0          |0          
1          |1          
NULL       |NULL       
ij> -- distinct and group by
select distinct i, dt, b from t group by i, dt, b order by i,dt,b;
I          |DT        |B   
---------------------------
0          |1992-01-01|12af
0          |1992-01-01|ffff
0          |1992-09-09|12af
1          |1992-01-01|12af
NULL       |NULL      |NULL
ij> -- order by and group by
-- same order