db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bakk...@apache.org
Subject svn commit: r329187 [37/66] - in /db/derby/code/trunk: ./ frameworks/NetworkServer/ frameworks/NetworkServer/bin/ frameworks/embedded/bin/ java/build/ java/build/org/apache/derbyBuild/ java/build/org/apache/derbyBuild/eclipse/ java/build/org/apache/der...
Date Fri, 28 Oct 2005 12:52:21 GMT
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out Fri Oct 28 04:51:50 2005
@@ -1,627 +1,627 @@
-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
-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> 
+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
+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> 

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/lobLengthTests.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/lobLengthTests.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/lobLengthTests.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/lobLengthTests.out Fri Oct 28 04:51:50 2005
@@ -1,2 +1,2 @@
-Testing server read of lob length > 2^24 bytes.
-PASS.
+Testing server read of lob length > 2^24 bytes.
+PASS.

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/lobLengthTests.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/logSwitchFail.out
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec1.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec1.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec1.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec1.out Fri Oct 28 04:51:50 2005
@@ -1,2 +1,2 @@
-Beginning test: test1
-Ending test: test1
+Beginning test: test1
+Ending test: test1

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec1.out
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec2.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec2.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec2.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec2.out Fri Oct 28 04:51:50 2005
@@ -1,2 +1,2 @@
-Beginning test: test1
-Ending test: test1
+Beginning test: test1
+Ending test: test1

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec2.out
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec3.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec3.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec3.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec3.out Fri Oct 28 04:51:50 2005
@@ -1,2 +1,2 @@
-Beginning test: test1
-Ending test: test1
+Beginning test: test1
+Ending test: test1

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec3.out
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec4.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec4.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec4.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec4.out Fri Oct 28 04:51:50 2005
@@ -1,2 +1,2 @@
-Beginning test: test1
-Ending test: test1
+Beginning test: test1
+Ending test: test1

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/oc_rec4.out
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/onlineCompressTable.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/onlineCompressTable.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/onlineCompressTable.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/onlineCompressTable.out Fri Oct 28 04:51:50 2005
@@ -1,88 +1,88 @@
-ij> autocommit off;
-ij> -- start with simple test, does the call work?
-create table test1 (a int);
-0 rows inserted/updated/deleted
-ij> -- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE('APP', 'TEST1');
--- expect failures schema/table does not exist
--- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE(null, 'test2');
--- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE('APP', 'test2');
--- non existent schema
--- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE('doesnotexist', 'a');
--- cleanup
-drop table test1;
-0 rows inserted/updated/deleted
-ij> -- load up a table, delete most of it's rows and then see what compress does.
-create table test1 (keycol int, a char(250), b char(250), c char(250), d char(250));
-0 rows inserted/updated/deleted
-ij> insert into test1 values (1, 'a', 'b', 'c', 'd');
-1 row inserted/updated/deleted
-ij> insert into test1 (select keycol + 1, a, b, c, d from test1);
-1 row inserted/updated/deleted
-ij> insert into test1 (select keycol + 2, a, b, c, d from test1);
-2 rows inserted/updated/deleted
-ij> insert into test1 (select keycol + 4, a, b, c, d from test1);
-4 rows inserted/updated/deleted
-ij> insert into test1 (select keycol + 8, a, b, c, d from test1);
-8 rows inserted/updated/deleted
-ij> insert into test1 (select keycol + 16, a, b, c, d from test1);
-16 rows inserted/updated/deleted
-ij> insert into test1 (select keycol + 32, a, b, c, d from test1);
-32 rows inserted/updated/deleted
-ij> insert into test1 (select keycol + 64, a, b, c, d from test1);
-64 rows inserted/updated/deleted
-ij> insert into test1 (select keycol + 128, a, b, c, d from test1);
-128 rows inserted/updated/deleted
-ij> insert into test1 (select keycol + 256, a, b, c, d from test1);
-256 rows inserted/updated/deleted
-ij> create index test1_idx on test1(keycol);
-0 rows inserted/updated/deleted
-ij> commit;
-ij> select 
-    conglomeratename, isindex, numallocatedpages, numfreepages, pagesize, 
-    estimspacesaving
-        from new org.apache.derby.diag.SpaceTable('TEST1') t
-                order by conglomeratename;
-CONGLOMERATENAME                                                                                                                |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE   |ESTIMSPACESAVING    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-TEST1                                                                                                                           |0     |171                 |0                   |4096       |0                   
-TEST1_IDX                                                                                                                       |1     |4                   |0                   |4096       |0                   
-ij> delete from test1 where keycol > 300;
-212 rows inserted/updated/deleted
-ij> commit;
-ij> delete from test1 where keycol < 100;
-99 rows inserted/updated/deleted
-ij> commit;
-ij> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'TEST1', 1, 0, 0);
-0 rows inserted/updated/deleted
-ij> select 
-    conglomeratename, isindex, numallocatedpages, numfreepages, pagesize, 
-    estimspacesaving
-        from new org.apache.derby.diag.SpaceTable('TEST1') t
-                order by conglomeratename;
-CONGLOMERATENAME                                                                                                                |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE   |ESTIMSPACESAVING    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-TEST1                                                                                                                           |0     |68                  |103                 |4096       |421888              
-TEST1_IDX                                                                                                                       |1     |4                   |0                   |4096       |0                   
-ij> commit;
-ij> -- call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'TEST1', 0, 1, 0);
-select 
-    conglomeratename, isindex, numallocatedpages, numfreepages, pagesize, 
-    estimspacesaving
-        from new org.apache.derby.diag.SpaceTable('TEST1') t
-                order by conglomeratename;
-CONGLOMERATENAME                                                                                                                |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE   |ESTIMSPACESAVING    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-TEST1                                                                                                                           |0     |68                  |103                 |4096       |421888              
-TEST1_IDX                                                                                                                       |1     |4                   |0                   |4096       |0                   
-ij> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'TEST1', 0, 0, 1);
-0 rows inserted/updated/deleted
-ij> select 
-    conglomeratename, isindex, numallocatedpages, numfreepages, pagesize, 
-    estimspacesaving
-        from new org.apache.derby.diag.SpaceTable('TEST1') t
-                order by conglomeratename;
-CONGLOMERATENAME                                                                                                                |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE   |ESTIMSPACESAVING    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-TEST1                                                                                                                           |0     |68                  |32                  |4096       |131072              
-TEST1_IDX                                                                                                                       |1     |4                   |0                   |4096       |0                   
-ij> 
+ij> autocommit off;
+ij> -- start with simple test, does the call work?
+create table test1 (a int);
+0 rows inserted/updated/deleted
+ij> -- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE('APP', 'TEST1');
+-- expect failures schema/table does not exist
+-- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE(null, 'test2');
+-- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE('APP', 'test2');
+-- non existent schema
+-- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE('doesnotexist', 'a');
+-- cleanup
+drop table test1;
+0 rows inserted/updated/deleted
+ij> -- load up a table, delete most of it's rows and then see what compress does.
+create table test1 (keycol int, a char(250), b char(250), c char(250), d char(250));
+0 rows inserted/updated/deleted
+ij> insert into test1 values (1, 'a', 'b', 'c', 'd');
+1 row inserted/updated/deleted
+ij> insert into test1 (select keycol + 1, a, b, c, d from test1);
+1 row inserted/updated/deleted
+ij> insert into test1 (select keycol + 2, a, b, c, d from test1);
+2 rows inserted/updated/deleted
+ij> insert into test1 (select keycol + 4, a, b, c, d from test1);
+4 rows inserted/updated/deleted
+ij> insert into test1 (select keycol + 8, a, b, c, d from test1);
+8 rows inserted/updated/deleted
+ij> insert into test1 (select keycol + 16, a, b, c, d from test1);
+16 rows inserted/updated/deleted
+ij> insert into test1 (select keycol + 32, a, b, c, d from test1);
+32 rows inserted/updated/deleted
+ij> insert into test1 (select keycol + 64, a, b, c, d from test1);
+64 rows inserted/updated/deleted
+ij> insert into test1 (select keycol + 128, a, b, c, d from test1);
+128 rows inserted/updated/deleted
+ij> insert into test1 (select keycol + 256, a, b, c, d from test1);
+256 rows inserted/updated/deleted
+ij> create index test1_idx on test1(keycol);
+0 rows inserted/updated/deleted
+ij> commit;
+ij> select 
+    conglomeratename, isindex, numallocatedpages, numfreepages, pagesize, 
+    estimspacesaving
+        from new org.apache.derby.diag.SpaceTable('TEST1') t
+                order by conglomeratename;
+CONGLOMERATENAME                                                                                                                |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE   |ESTIMSPACESAVING    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+TEST1                                                                                                                           |0     |171                 |0                   |4096       |0                   
+TEST1_IDX                                                                                                                       |1     |4                   |0                   |4096       |0                   
+ij> delete from test1 where keycol > 300;
+212 rows inserted/updated/deleted
+ij> commit;
+ij> delete from test1 where keycol < 100;
+99 rows inserted/updated/deleted
+ij> commit;
+ij> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'TEST1', 1, 0, 0);
+0 rows inserted/updated/deleted
+ij> select 
+    conglomeratename, isindex, numallocatedpages, numfreepages, pagesize, 
+    estimspacesaving
+        from new org.apache.derby.diag.SpaceTable('TEST1') t
+                order by conglomeratename;
+CONGLOMERATENAME                                                                                                                |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE   |ESTIMSPACESAVING    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+TEST1                                                                                                                           |0     |68                  |103                 |4096       |421888              
+TEST1_IDX                                                                                                                       |1     |4                   |0                   |4096       |0                   
+ij> commit;
+ij> -- call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'TEST1', 0, 1, 0);
+select 
+    conglomeratename, isindex, numallocatedpages, numfreepages, pagesize, 
+    estimspacesaving
+        from new org.apache.derby.diag.SpaceTable('TEST1') t
+                order by conglomeratename;
+CONGLOMERATENAME                                                                                                                |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE   |ESTIMSPACESAVING    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+TEST1                                                                                                                           |0     |68                  |103                 |4096       |421888              
+TEST1_IDX                                                                                                                       |1     |4                   |0                   |4096       |0                   
+ij> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'TEST1', 0, 0, 1);
+0 rows inserted/updated/deleted
+ij> select 
+    conglomeratename, isindex, numallocatedpages, numfreepages, pagesize, 
+    estimspacesaving
+        from new org.apache.derby.diag.SpaceTable('TEST1') t
+                order by conglomeratename;
+CONGLOMERATENAME                                                                                                                |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE   |ESTIMSPACESAVING    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+TEST1                                                                                                                           |0     |68                  |32                  |4096       |131072              
+TEST1_IDX                                                                                                                       |1     |4                   |0                   |4096       |0                   
+ij> 

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/onlineCompressTable.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/recoverBadLog1.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/recoverBadLog2.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/recoverBadLog3.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/recoverBadLog4.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/recoverBadLog5.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/recoverBadLog6.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/recoverBadLog7.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/recoverBadLogSetup.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/recoverySetup.out
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/recoveryTest.out
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message