Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out?rev=190512&r1=190511&r2=190512&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out Mon Jun 13 14:37:34 2005
@@ -1,632 +1,632 @@
-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 not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it 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 with a GROUP BY, the SELECT 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 with a GROUP BY, the SELECT 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 with a GROUP BY, the SELECT 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 on 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 1 invalid expression.  For a SELECT with a GROUP BY, the SELECT 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 not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it 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 not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it 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, 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;
-LBV                                                                                                                             
---------------------------------------------------------------------------------------------------------------------------------
-1234                                                                                                                            
-abcd                                                                                                                            
-NULL                                                                                                                            
-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                                  
-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
-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 1 aggregate then all entries must be valid aggregate expressions.
-ij> select 1 from t1 having c1 = 1;
-ERROR 42X04: Column 'C1' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it 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 1 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 1 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 1 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 1 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 1 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 1 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 1 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> 
+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;
+LBV                                                                                                                             
+--------------------------------------------------------------------------------------------------------------------------------
+1234                                                                                                                            
+abcd                                                                                                                            
+NULL                                                                                                                            
+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                                  
+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
+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> 

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant?rev=190512&r1=190511&r2=190512&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant Mon Jun 13 14:37:34 2005
@@ -76,6 +76,8 @@
 emptyStatistics.sql
 errorCode.sql
 errorCode_app.properties
+errorStream.sql
+errorStream_app.properties
 fk_nonSPS.sql
 fk_nonSPS_derby.properties
 floattypes.sql