groupby.out

derby database source code.good for you.

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> -- subset in same order
select i, dt, b from t group by i, dt, b order by i, dt;
I          |DT        |B   
---------------------------
0          |1992-01-01|ffff
0          |1992-01-01|12af
0          |1992-09-09|12af
1          |1992-01-01|12af
NULL       |NULL      |NULL
ij> -- different order
select i, dt, b from t group by i, dt, b order by b, dt, i;
I          |DT        |B   
---------------------------
0          |1992-01-01|12af
1          |1992-01-01|12af
0          |1992-09-09|12af
0          |1992-01-01|ffff
NULL       |NULL      |NULL
ij> -- subset in different order
select i, dt, b from t group by i, dt, b order by b, dt;
I          |DT        |B   
---------------------------
0          |1992-01-01|12af
1          |1992-01-01|12af
0          |1992-09-09|12af
0          |1992-01-01|ffff
NULL       |NULL      |NULL
ij> -- group by without having in from subquery
select * from
(select i, dt from t group by i, dt) t (t_i, t_dt),
(select i, dt from t group by i, dt) m (m_i, m_dt)
where t_i = m_i and t_dt = m_dt order by t_i,t_dt,m_i,m_dt;
T_I        |T_DT      |M_I        |M_DT      
---------------------------------------------
0          |1992-01-01|0          |1992-01-01
0          |1992-09-09|0          |1992-09-09
1          |1992-01-01|1          |1992-01-01
ij> select * from
(select i, dt from t group by i, dt) t (t_i, t_dt),
(select i, dt from t group by i, dt) m (m_i, m_dt)
group by t_i, t_dt, m_i, m_dt order by t_i,t_dt,m_i,m_dt;
T_I        |T_DT      |M_I        |M_DT      
---------------------------------------------
0          |1992-01-01|0          |1992-01-01
0          |1992-01-01|0          |1992-09-09
0          |1992-01-01|1          |1992-01-01
0          |1992-01-01|NULL       |NULL      
0          |1992-09-09|0          |1992-01-01
0          |1992-09-09|0          |1992-09-09
0          |1992-09-09|1          |1992-01-01
0          |1992-09-09|NULL       |NULL      
1          |1992-01-01|0          |1992-01-01
1          |1992-01-01|0          |1992-09-09
1          |1992-01-01|1          |1992-01-01
1          |1992-01-01|NULL       |NULL      
NULL       |NULL      |0          |1992-01-01
NULL       |NULL      |0          |1992-09-09
NULL       |NULL      |1          |1992-01-01
NULL       |NULL      |NULL       |NULL      
ij> select * from
(select i, dt from t group by i, dt) t (t_i, t_dt),
(select i, dt from t group by i, dt) m (m_i, m_dt)
where t_i = m_i and t_dt = m_dt
group by t_i, t_dt, m_i, m_dt order by t_i,t_dt,m_i,m_dt;
T_I        |T_DT      |M_I        |M_DT      
---------------------------------------------
0          |1992-01-01|0          |1992-01-01
0          |1992-09-09|0          |1992-09-09
1          |1992-01-01|1          |1992-01-01
ij> select t.*, m.* from
(select i, dt from t group by i, dt) t (t_i, t_dt),
(select i, dt from t group by i, dt) m (t_i, t_dt)
where t.t_i = m.t_i and t.t_dt = m.t_dt
group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,t.t_dt,m.t_i,m.t_dt;
T_I        |T_DT      |T_I        |T_DT      
---------------------------------------------
0          |1992-01-01|0          |1992-01-01
0          |1992-09-09|0          |1992-09-09
1          |1992-01-01|1          |1992-01-01
ij> select t.t_i, t.t_dt, m.* from
(select i, dt from t group by i, dt) t (t_i, t_dt),
(select i, dt from t group by i, dt) m (t_i, t_dt)
where t.t_i = m.t_i and t.t_dt = m.t_dt
group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,t.t_dt,m.t_i,m.t_dt;
T_I        |T_DT      |T_I        |T_DT      
---------------------------------------------
0          |1992-01-01|0          |1992-01-01
0          |1992-09-09|0          |1992-09-09
1          |1992-01-01|1          |1992-01-01
ij> -- additional columns in group by list not in select list
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 t.i from t group by i, dt, b order by i;
I          
-----------
0          
0          
0          
1          
NULL       
ij> select t.dt from t group by i, dt, b order by dt;
DT        
----------
1992-01-01
1992-01-01
1992-01-01
1992-09-09
NULL      
ij> select t.b from t group by i, dt, b order by b;
B   
----
12af
12af
12af
ffff
NULL
ij> select t.t_i, m.t_i from
(select i, dt from t group by i, dt) t (t_i, t_dt),
(select i, dt from t group by i, dt) m (t_i, t_dt)
where t.t_i = m.t_i and t.t_dt = m.t_dt
group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,m.t_i;
T_I        |T_I        
-----------------------
0          |0          
0          |0          
1          |1          
ij> -- having
-- parameters in having clause
prepare p1 as 'select i, dt, b from t group by i, dt, b having i = ? order by i,dt,b';
ij> execute p1 using 'values 0';
IJ WARNING: Autocommit may close using result set
I          |DT        |B   
---------------------------
0          |1992-01-01|12af
0          |1992-01-01|ffff
0          |1992-09-09|12af
ij> remove p1;
ij> -- group by with having in from subquery
select * from
(select i, dt from t group by i, dt having 1=1) t (t_i, t_dt),
(select i, dt from t group by i, dt having i = 0) m (m_i, m_dt)
where t_i = m_i and t_dt = m_dt order by t_i,t_dt,m_i,m_dt;
T_I        |T_DT      |M_I        |M_DT      
---------------------------------------------
0          |1992-01-01|0          |1992-01-01
0          |1992-09-09|0          |1992-09-09
ij> select * from
(select i, dt from t group by i, dt having 1=1) t (t_i, t_dt),
(select i, dt from t group by i, dt having i = 0) m (m_i, m_dt)
group by t_i, t_dt, m_i, m_dt order by t_i,t_dt,m_i,m_dt;
T_I        |T_DT      |M_I        |M_DT      
---------------------------------------------
0          |1992-01-01|0          |1992-01-01
0          |1992-01-01|0          |1992-09-09
0          |1992-09-09|0          |1992-01-01
0          |1992-09-09|0          |1992-09-09
1          |1992-01-01|0          |1992-01-01
1          |1992-01-01|0          |1992-09-09
NULL       |NULL      |0          |1992-01-01
NULL       |NULL      |0          |1992-09-09
ij> select * from
(select i, dt from t group by i, dt having 1=1) t (t_i, t_dt),
(select i, dt from t group by i, dt having i = 0) m (m_i, m_dt)
where t_i = m_i and t_dt = m_dt
group by t_i, t_dt, m_i, m_dt
having t_i * m_i = m_i * t_i order by t_i,t_dt,m_i,m_dt;
T_I        |T_DT      |M_I        |M_DT      
---------------------------------------------
0          |1992-01-01|0          |1992-01-01
0          |1992-09-09|0          |1992-09-09
ij> -- correlated subquery in having clause
select i, dt from t
group by i, dt
having i = (select distinct i from tab1 where t.i = tab1.i) order by i,dt;
I          |DT        
----------------------
0          |1992-01-01
0          |1992-09-09
1          |1992-01-01
ij> select i, dt from t
group by i, dt
having i = (select i from t m group by i having t.i = m.i) order by i,dt;
I          |DT        
----------------------
0          |1992-01-01
0          |1992-09-09
1          |1992-01-01
ij> -- column references in having clause match columns in group by list
select i as outer_i, dt from t
group by i, dt
having i = (select i from t m group by i having t.i = m.i) order by outer_i,dt;
OUTER_I    |DT        
----------------------
0          |1992-01-01
0          |1992-09-09
1          |1992-01-01
ij> -- additional columns in group by list not in select list
select i, dt from t group by i, dt order by i,dt;
I          |DT        
----------------------
0          |1992-01-01
0          |1992-09-09
1          |1992-01-01
NULL       |NULL      
ij> select t.dt from t group by i, dt having i = 0 order by t.dt;
DT        
----------
1992-01-01
1992-09-09
ij> select t.dt from t group by i, dt having i <> 0 order by t.dt;
DT        
----------
1992-01-01
ij> select t.dt from t group by i, dt having i != 0 order by t.dt;
DT        
----------
1992-01-01
ij> -- drop tables
drop table t;
0 rows inserted/updated/deleted
ij> drop table tab1;
0 rows inserted/updated/deleted
ij> -- negative tests for selects with a having clause without a group by
-- create a table
create table t1(c1 int, c2 int);
0 rows inserted/updated/deleted
ij> -- binding of having clause
select 1 from t1 having 1;
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> -- column references in having clause not allowed if no group by
select * from t1 having c1 = 1;
ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
ij> select 1 from t1 having c1 = 1;
ERROR 42X04: Column 'C1' 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 'C1' is not a column in the target table.
ij> -- correlated subquery in having clause
select * from t1 t1_outer 
having 1 = (select 1 from t1 where c1 = t1_outer.c1);
ERROR 42Y35: Column reference 'T1_OUTER.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
ij> -- drop the table
drop table t1;
0 rows inserted/updated/deleted
ij> -- bug 5653
-- test (almost useful) restrictions on a having clause without a group by clause
-- create the table
create table t1 (c1 float);
0 rows inserted/updated/deleted
ij> -- populate the table
insert into t1 values 0.0, 90.0;
2 rows inserted/updated/deleted
ij> -- this is the only query that should not fail
-- filter out all rows
select 1 from t1 having 1=0;
1          
-----------
ij> -- all 6 queries below should fail after bug 5653 is fixed
-- select * 
select * from t1 having 1=1;
ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
ij> -- select column
select c1 from t1 having 1=1;
ERROR 42Y35: Column reference 'C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
ij> -- select with a built-in function sqrt
select sqrt(c1) from t1 having 1=1;
ERROR 42Y35: Column reference 'C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
ij> -- non-correlated subquery in having clause
select * from t1 having 1 = (select 1 from t1 where c1 = 0.0);
ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
ij> -- expression in select list
select (c1 * c1) / c1 from t1 where c1 <> 0 having 1=1;
ERROR 42Y35: Column reference 'C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
ij> -- between
select * from t1 having 1 between 1 and 2;
ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
ij> -- drop the table
drop table t1;
0 rows inserted/updated/deleted
ij> -- bug 5920
-- test that HAVING without GROUPBY makes one group
create table t(c int, d int);
0 rows inserted/updated/deleted
ij> insert into t(c,d) values (1,10),(2,20),(2,20),(3,30),(3,30),(3,30);
6 rows inserted/updated/deleted
ij> select avg(c) from t having 1 < 2;
1          
-----------
2          
ij> -- used to give several rows, now gives only one
select 10 from t having 1 < 2;
1          
-----------
10         
ij> -- ok, gives one row
select 10,avg(c) from t having 1 < 2;
1          |2          
-----------------------
10         |2          
ij> drop table t;
0 rows inserted/updated/deleted
ij>