db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From fuzzylo...@apache.org
Subject svn commit: r151234 [3/3] - in incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: harness/FileCompare.java harness/RunList.java harness/RunTest.java harness/j9_22.java master/j9_22/ master/j9_22/distinct.out master/j9_22/groupBy.out tests/lang/copyfiles.ant tests/lang/procedure_app.properties
Date Thu, 03 Feb 2005 21:28:32 GMT
Added: 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?view=auto&rev=151234
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out
(added)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out
Thu Feb  3 13:28:29 2005
@@ -0,0 +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> 

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?view=diff&r1=151233&r2=151234
==============================================================================
--- 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
Thu Feb  3 13:28:29 2005
@@ -132,6 +132,7 @@
 predicatesIntoViews.sql
 predicatesIntoViews_derby.properties
 primarykey.sql
+procedure_app.properties
 procedure_derby.properties
 refActions.sql
 refActions1.sql

Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedure_app.properties
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedure_app.properties?view=auto&rev=151234
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedure_app.properties
(added)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedure_app.properties
Thu Feb  3 13:28:29 2005
@@ -0,0 +1,2 @@
+# disabling running with j9 because of slightly different behavior.
+runwithj9=false



Mime
View raw message