You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by fu...@apache.org on 2005/02/03 22:28:32 UTC
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
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