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/06/13 23:41:35 UTC
svn commit: r190514 [6/7] - in /incubator/derby/code/branches/10.1/java:
engine/org/apache/derby/iapi/reference/
engine/org/apache/derby/iapi/services/io/
engine/org/apache/derby/iapi/services/loader/
engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/jdbc/
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/master/DerbyNet/
testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/
testing/org/apache/derbyTesting/functionTests/master/j9_13/
testing/org/apache/derbyTesting/functionTests/master/j9_22/
testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/
testing/org/apache/derbyTesting/functionTests/tests/lang/
testing/org/apache/derbyTesting/functionTests/tests/tools/
Modified: incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out?rev=190514&r1=190513&r2=190514&view=diff
==============================================================================
--- incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out (original)
+++ incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out Mon Jun 13 14:41:33 2005
@@ -1,632 +1,632 @@
-ij> -- negative tests for group by and having clauses
-create table t1 (a int, b int, c int);
-0 rows inserted/updated/deleted
-ij> create table t2 (a int, b int, c int);
-0 rows inserted/updated/deleted
-ij> insert into t2 values (1,1,1), (2,2,2);
-2 rows inserted/updated/deleted
-ij> -- group by position
-select * from t1 group by 1;
-ERROR 42X01: Syntax error: Encountered "1" at line 2, column 27.
-ij> -- column in group by list not in from list
-select a as d from t1 group by d;
-ERROR 42X04: Column 'D' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table.
-ij> -- column in group by list not in select list
-select a as b from t1 group by b;
-ERROR 42Y36: Column reference 'A' is invalid. For a SELECT with a GROUP BY, the SELECT list may only contain grouping columns and valid aggregate expressions.
-ij> select a from t1 group by b;
-ERROR 42Y36: Column reference 'A' is invalid. For a SELECT with a GROUP BY, the SELECT list may only contain grouping columns and valid aggregate expressions.
-ij> select a, char(b) from t1 group by a;
-ERROR 42Y36: Column reference 'B' is invalid. For a SELECT with a GROUP BY, the SELECT list may only contain grouping columns and valid aggregate expressions.
-ij> -- columns in group by list must be unique
-select a, b from t1 group by a, a;
-ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
-ij> select a, b from t1 group by a, t1.a;
-ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
-ij> -- cursor with group by is not updatable
-get cursor c1 as 'select a from t1 group by a for update';
-ERROR 42Y90: FOR UPDATE is not permitted on this type of statement.
-ij> -- noncorrelated subquery that returns too many rows
-select a, (select a from t2) from t1 group by a;
-ERROR 21000: Scalar subquery is only allowed to return a single row.
-ij> -- correlation on outer table
-select t2.a, (select b from t1 where t1.b = t2.b) from t1 t2 group by t2.a;
-ERROR 42Y30: The SELECT list of a grouped query contains at least 1 invalid expression. For a SELECT with a GROUP BY, the SELECT list may only contain grouping columns and valid aggregate expressions.
-ij> -- having clause
--- cannot contain column references which are not grouping columns
-select a from t1 group by a having c = 1;
-ERROR 42X04: Column 'C' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'C' is not a column in the target table.
-ij> select a from t1 o group by a having a = (select a from t1 where b = b.o);
-ERROR 42X04: Column 'B.O' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'B.O' is not a column in the target table.
-ij> -- ?s in group by
-select a from t1 group by ?;
-ERROR 42X01: Syntax error: Encountered "?" at line 2, column 27.
-ij> -- group by on long varchar type
-create table unmapped(c1 long varchar);
-0 rows inserted/updated/deleted
-ij> select c1, max(1) from unmapped group by c1;
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
-ij> -- clean up
-drop table t1;
-0 rows inserted/updated/deleted
-ij> drop table t2;
-0 rows inserted/updated/deleted
-ij> drop table unmapped;
-0 rows inserted/updated/deleted
-ij> -- Test group by and having clauses with no aggregates
--- create an all types tables
-create table t (i int, s smallint, l bigint,
- c char(10), v varchar(50), lvc long varchar,
- d double precision, r real,
- dt date, t time, ts timestamp,
- b char(2) for bit data, bv varchar(2) for bit data, lbv long varchar for bit data);
-0 rows inserted/updated/deleted
-ij> create table tab1 (
- i integer,
- s smallint,
- l bigint,
- c char(30),
- v varchar(30),
- lvc long varchar,
- d double precision,
- r real,
- dt date,
- t time,
- ts timestamp);
-0 rows inserted/updated/deleted
-ij> -- populate tables
-insert into t (i) values (null);
-1 row inserted/updated/deleted
-ij> insert into t (i) values (null);
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (1, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 200, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 2000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'goodbye', 'everyone is here', 'adios, muchachos',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'noone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 100.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 100.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'0f0f', X'ABCD');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'ffff', X'0f0f', X'1234');
-1 row inserted/updated/deleted
-ij> insert into t values (0, 100, 1000000,
- 'hello', 'everyone is here', 'what the heck do we care?',
- 200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
- X'12af', X'ffff', X'ABCD');
-1 row inserted/updated/deleted
-ij> -- bit maps to Byte[], so can't test for now
-insert into tab1
-select i, s, l, c, v, lvc, d, r, dt, t, ts from t;
-17 rows inserted/updated/deleted
-ij> -- simple grouping
-select i from t group by i order by i;
-I
------------
-0
-1
-NULL
-ij> select s from t group by s order by s;
-S
-------
-100
-200
-NULL
-ij> select l from t group by l order by l;
-L
---------------------
-1000000
-2000000
-NULL
-ij> select c from t group by c order by c;
-C
-----------
-goodbye
-hello
-NULL
-ij> select v from t group by v order by v;
-V
---------------------------------------------------
-everyone is here
-noone is here
-NULL
-ij> select d from t group by d order by d;
-D
-----------------------
-100.0
-200.0
-NULL
-ij> select r from t group by r order by r;
-R
--------------
-100.0
-200.0
-NULL
-ij> select dt from t group by dt order by dt;
-DT
-----------
-1992-01-01
-1992-09-09
-NULL
-ij> select t from t group by t order by t;
-T
---------
-12:30:30
-12:55:55
-NULL
-ij> select ts from t group by ts order by ts;
-TS
---------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
-xxxxxxFILTERED-TIMESTAMPxxxxx
-NULL
-ij> select b from t group by b order by b;
-B
-----
-12af
-ffff
-NULL
-ij> select bv from t group by bv order by bv;
-BV
-----
-0f0f
-ffff
-NULL
-ij> -- grouping by long varchar [for bit data] cols should fail in db2 mode
-select lbv from t group by lbv order by lbv;
-LBV
---------------------------------------------------------------------------------------------------------------------------------
-1234
-abcd
-NULL
-ij> -- multicolumn grouping
-select i, dt, b from t where 1=1 group by i, dt, b order by i,dt,b;
-I |DT |B
----------------------------
-0 |1992-01-01|12af
-0 |1992-01-01|ffff
-0 |1992-09-09|12af
-1 |1992-01-01|12af
-NULL |NULL |NULL
-ij> select i, dt, b from t group by i, dt, b order by i,dt,b;
-I |DT |B
----------------------------
-0 |1992-01-01|12af
-0 |1992-01-01|ffff
-0 |1992-09-09|12af
-1 |1992-01-01|12af
-NULL |NULL |NULL
-ij> select i, dt, b from t group by b, i, dt order by i,dt,b;
-I |DT |B
----------------------------
-0 |1992-01-01|12af
-0 |1992-01-01|ffff
-0 |1992-09-09|12af
-1 |1992-01-01|12af
-NULL |NULL |NULL
-ij> select i, dt, b from t group by dt, i, b order by i,dt,b;
-I |DT |B
----------------------------
-0 |1992-01-01|12af
-0 |1992-01-01|ffff
-0 |1992-09-09|12af
-1 |1992-01-01|12af
-NULL |NULL |NULL
-ij> -- group by expression
-select expr1, expr2
-from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1 order by expr2,expr1;
-EXPR1 |EXPR2
-------------------------------------------------------------------------
-0 |goodbye everyone is here
-0 |hello everyone is here
-0 |hello everyone is here
-100 |hello everyone is here
-0 |hello noone is here
-NULL |NULL
-ij> -- group by correlated subquery
-select i, expr1
-from (select i, (select distinct i from t m where m.i = t.i) from t) t (i, expr1)
- group by i, expr1 order by i,expr1;
-I |EXPR1
------------------------
-0 |0
-1 |1
-NULL |NULL
-ij> -- distinct and group by
-select distinct i, dt, b from t group by i, dt, b order by i,dt,b;
-I |DT |B
----------------------------
-0 |1992-01-01|12af
-0 |1992-01-01|ffff
-0 |1992-09-09|12af
-1 |1992-01-01|12af
-NULL |NULL |NULL
-ij> -- order by and group by
--- same order
-select i, dt, b from t group by i, dt, b order by i, dt, b;
-I |DT |B
----------------------------
-0 |1992-01-01|12af
-0 |1992-01-01|ffff
-0 |1992-09-09|12af
-1 |1992-01-01|12af
-NULL |NULL |NULL
-ij> -- subset in same order
-select i, dt, b from t group by i, dt, b order by i, dt;
-I |DT |B
----------------------------
-0 |1992-01-01|ffff
-0 |1992-01-01|12af
-0 |1992-09-09|12af
-1 |1992-01-01|12af
-NULL |NULL |NULL
-ij> -- different order
-select i, dt, b from t group by i, dt, b order by b, dt, i;
-I |DT |B
----------------------------
-0 |1992-01-01|12af
-1 |1992-01-01|12af
-0 |1992-09-09|12af
-0 |1992-01-01|ffff
-NULL |NULL |NULL
-ij> -- subset in different order
-select i, dt, b from t group by i, dt, b order by b, dt;
-I |DT |B
----------------------------
-0 |1992-01-01|12af
-1 |1992-01-01|12af
-0 |1992-09-09|12af
-0 |1992-01-01|ffff
-NULL |NULL |NULL
-ij> -- group by without having in from subquery
-select * from
-(select i, dt from t group by i, dt) t (t_i, t_dt),
-(select i, dt from t group by i, dt) m (m_i, m_dt)
-where t_i = m_i and t_dt = m_dt order by t_i,t_dt,m_i,m_dt;
-T_I |T_DT |M_I |M_DT
----------------------------------------------
-0 |1992-01-01|0 |1992-01-01
-0 |1992-09-09|0 |1992-09-09
-1 |1992-01-01|1 |1992-01-01
-ij> select * from
-(select i, dt from t group by i, dt) t (t_i, t_dt),
-(select i, dt from t group by i, dt) m (m_i, m_dt)
-group by t_i, t_dt, m_i, m_dt order by t_i,t_dt,m_i,m_dt;
-T_I |T_DT |M_I |M_DT
----------------------------------------------
-0 |1992-01-01|0 |1992-01-01
-0 |1992-01-01|0 |1992-09-09
-0 |1992-01-01|1 |1992-01-01
-0 |1992-01-01|NULL |NULL
-0 |1992-09-09|0 |1992-01-01
-0 |1992-09-09|0 |1992-09-09
-0 |1992-09-09|1 |1992-01-01
-0 |1992-09-09|NULL |NULL
-1 |1992-01-01|0 |1992-01-01
-1 |1992-01-01|0 |1992-09-09
-1 |1992-01-01|1 |1992-01-01
-1 |1992-01-01|NULL |NULL
-NULL |NULL |0 |1992-01-01
-NULL |NULL |0 |1992-09-09
-NULL |NULL |1 |1992-01-01
-NULL |NULL |NULL |NULL
-ij> select * from
-(select i, dt from t group by i, dt) t (t_i, t_dt),
-(select i, dt from t group by i, dt) m (m_i, m_dt)
-where t_i = m_i and t_dt = m_dt
-group by t_i, t_dt, m_i, m_dt order by t_i,t_dt,m_i,m_dt;
-T_I |T_DT |M_I |M_DT
----------------------------------------------
-0 |1992-01-01|0 |1992-01-01
-0 |1992-09-09|0 |1992-09-09
-1 |1992-01-01|1 |1992-01-01
-ij> select t.*, m.* from
-(select i, dt from t group by i, dt) t (t_i, t_dt),
-(select i, dt from t group by i, dt) m (t_i, t_dt)
-where t.t_i = m.t_i and t.t_dt = m.t_dt
-group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,t.t_dt,m.t_i,m.t_dt;
-T_I |T_DT |T_I |T_DT
----------------------------------------------
-0 |1992-01-01|0 |1992-01-01
-0 |1992-09-09|0 |1992-09-09
-1 |1992-01-01|1 |1992-01-01
-ij> select t.t_i, t.t_dt, m.* from
-(select i, dt from t group by i, dt) t (t_i, t_dt),
-(select i, dt from t group by i, dt) m (t_i, t_dt)
-where t.t_i = m.t_i and t.t_dt = m.t_dt
-group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,t.t_dt,m.t_i,m.t_dt;
-T_I |T_DT |T_I |T_DT
----------------------------------------------
-0 |1992-01-01|0 |1992-01-01
-0 |1992-09-09|0 |1992-09-09
-1 |1992-01-01|1 |1992-01-01
-ij> -- additional columns in group by list not in select list
-select i, dt, b from t group by i, dt, b order by i,dt,b;
-I |DT |B
----------------------------
-0 |1992-01-01|12af
-0 |1992-01-01|ffff
-0 |1992-09-09|12af
-1 |1992-01-01|12af
-NULL |NULL |NULL
-ij> select t.i from t group by i, dt, b order by i;
-I
------------
-0
-0
-0
-1
-NULL
-ij> select t.dt from t group by i, dt, b order by dt;
-DT
-----------
-1992-01-01
-1992-01-01
-1992-01-01
-1992-09-09
-NULL
-ij> select t.b from t group by i, dt, b order by b;
-B
-----
-12af
-12af
-12af
-ffff
-NULL
-ij> select t.t_i, m.t_i from
-(select i, dt from t group by i, dt) t (t_i, t_dt),
-(select i, dt from t group by i, dt) m (t_i, t_dt)
-where t.t_i = m.t_i and t.t_dt = m.t_dt
-group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,m.t_i;
-T_I |T_I
------------------------
-0 |0
-0 |0
-1 |1
-ij> -- having
--- parameters in having clause
-prepare p1 as 'select i, dt, b from t group by i, dt, b having i = ? order by i,dt,b';
-ij> execute p1 using 'values 0';
-IJ WARNING: Autocommit may close using result set
-I |DT |B
----------------------------
-0 |1992-01-01|12af
-0 |1992-01-01|ffff
-0 |1992-09-09|12af
-ij> remove p1;
-ij> -- group by with having in from subquery
-select * from
-(select i, dt from t group by i, dt having 1=1) t (t_i, t_dt),
-(select i, dt from t group by i, dt having i = 0) m (m_i, m_dt)
-where t_i = m_i and t_dt = m_dt order by t_i,t_dt,m_i,m_dt;
-T_I |T_DT |M_I |M_DT
----------------------------------------------
-0 |1992-01-01|0 |1992-01-01
-0 |1992-09-09|0 |1992-09-09
-ij> select * from
-(select i, dt from t group by i, dt having 1=1) t (t_i, t_dt),
-(select i, dt from t group by i, dt having i = 0) m (m_i, m_dt)
-group by t_i, t_dt, m_i, m_dt order by t_i,t_dt,m_i,m_dt;
-T_I |T_DT |M_I |M_DT
----------------------------------------------
-0 |1992-01-01|0 |1992-01-01
-0 |1992-01-01|0 |1992-09-09
-0 |1992-09-09|0 |1992-01-01
-0 |1992-09-09|0 |1992-09-09
-1 |1992-01-01|0 |1992-01-01
-1 |1992-01-01|0 |1992-09-09
-NULL |NULL |0 |1992-01-01
-NULL |NULL |0 |1992-09-09
-ij> select * from
-(select i, dt from t group by i, dt having 1=1) t (t_i, t_dt),
-(select i, dt from t group by i, dt having i = 0) m (m_i, m_dt)
-where t_i = m_i and t_dt = m_dt
-group by t_i, t_dt, m_i, m_dt
-having t_i * m_i = m_i * t_i order by t_i,t_dt,m_i,m_dt;
-T_I |T_DT |M_I |M_DT
----------------------------------------------
-0 |1992-01-01|0 |1992-01-01
-0 |1992-09-09|0 |1992-09-09
-ij> -- correlated subquery in having clause
-select i, dt from t
-group by i, dt
-having i = (select distinct i from tab1 where t.i = tab1.i) order by i,dt;
-I |DT
-----------------------
-0 |1992-01-01
-0 |1992-09-09
-1 |1992-01-01
-ij> select i, dt from t
-group by i, dt
-having i = (select i from t m group by i having t.i = m.i) order by i,dt;
-I |DT
-----------------------
-0 |1992-01-01
-0 |1992-09-09
-1 |1992-01-01
-ij> -- column references in having clause match columns in group by list
-select i as outer_i, dt from t
-group by i, dt
-having i = (select i from t m group by i having t.i = m.i) order by outer_i,dt;
-OUTER_I |DT
-----------------------
-0 |1992-01-01
-0 |1992-09-09
-1 |1992-01-01
-ij> -- additional columns in group by list not in select list
-select i, dt from t group by i, dt order by i,dt;
-I |DT
-----------------------
-0 |1992-01-01
-0 |1992-09-09
-1 |1992-01-01
-NULL |NULL
-ij> select t.dt from t group by i, dt having i = 0 order by t.dt;
-DT
-----------
-1992-01-01
-1992-09-09
-ij> select t.dt from t group by i, dt having i <> 0 order by t.dt;
-DT
-----------
-1992-01-01
-ij> select t.dt from t group by i, dt having i != 0 order by t.dt;
-DT
-----------
-1992-01-01
-ij> -- drop tables
-drop table t;
-0 rows inserted/updated/deleted
-ij> drop table tab1;
-0 rows inserted/updated/deleted
-ij> -- negative tests for selects with a having clause without a group by
--- create a table
-create table t1(c1 int, c2 int);
-0 rows inserted/updated/deleted
-ij> -- binding of having clause
-select 1 from t1 having 1;
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
-ij> -- column references in having clause not allowed if no group by
-select * from t1 having c1 = 1;
-ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least 1 aggregate then all entries must be valid aggregate expressions.
-ij> select 1 from t1 having c1 = 1;
-ERROR 42X04: Column 'C1' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'C1' is not a column in the target table.
-ij> -- correlated subquery in having clause
-select * from t1 t1_outer
-having 1 = (select 1 from t1 where c1 = t1_outer.c1);
-ERROR 42Y35: Column reference 'T1_OUTER.C1' is invalid. When the SELECT list contains at least 1 aggregate then all entries must be valid aggregate expressions.
-ij> -- drop the table
-drop table t1;
-0 rows inserted/updated/deleted
-ij> -- bug 5653
--- test (almost useful) restrictions on a having clause without a group by clause
--- create the table
-create table t1 (c1 float);
-0 rows inserted/updated/deleted
-ij> -- populate the table
-insert into t1 values 0.0, 90.0;
-2 rows inserted/updated/deleted
-ij> -- this is the only query that should not fail
--- filter out all rows
-select 1 from t1 having 1=0;
-1
------------
-ij> -- all 6 queries below should fail after bug 5653 is fixed
--- select *
-select * from t1 having 1=1;
-ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least 1 aggregate then all entries must be valid aggregate expressions.
-ij> -- select column
-select c1 from t1 having 1=1;
-ERROR 42Y35: Column reference 'C1' is invalid. When the SELECT list contains at least 1 aggregate then all entries must be valid aggregate expressions.
-ij> -- select with a built-in function sqrt
-select sqrt(c1) from t1 having 1=1;
-ERROR 42Y35: Column reference 'C1' is invalid. When the SELECT list contains at least 1 aggregate then all entries must be valid aggregate expressions.
-ij> -- non-correlated subquery in having clause
-select * from t1 having 1 = (select 1 from t1 where c1 = 0.0);
-ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least 1 aggregate then all entries must be valid aggregate expressions.
-ij> -- expression in select list
-select (c1 * c1) / c1 from t1 where c1 <> 0 having 1=1;
-ERROR 42Y35: Column reference 'C1' is invalid. When the SELECT list contains at least 1 aggregate then all entries must be valid aggregate expressions.
-ij> -- between
-select * from t1 having 1 between 1 and 2;
-ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least 1 aggregate then all entries must be valid aggregate expressions.
-ij> -- drop the table
-drop table t1;
-0 rows inserted/updated/deleted
-ij> -- bug 5920
--- test that HAVING without GROUPBY makes one group
-create table t(c int, d int);
-0 rows inserted/updated/deleted
-ij> insert into t(c,d) values (1,10),(2,20),(2,20),(3,30),(3,30),(3,30);
-6 rows inserted/updated/deleted
-ij> select avg(c) from t having 1 < 2;
-1
------------
-2
-ij> -- used to give several rows, now gives only one
-select 10 from t having 1 < 2;
-1
------------
-10
-ij> -- ok, gives one row
-select 10,avg(c) from t having 1 < 2;
-1 |2
------------------------
-10 |2
-ij> drop table t;
-0 rows inserted/updated/deleted
-ij>
+ij> -- negative tests for group by and having clauses
+create table t1 (a int, b int, c int);
+0 rows inserted/updated/deleted
+ij> create table t2 (a int, b int, c int);
+0 rows inserted/updated/deleted
+ij> insert into t2 values (1,1,1), (2,2,2);
+2 rows inserted/updated/deleted
+ij> -- group by position
+select * from t1 group by 1;
+ERROR 42X01: Syntax error: Encountered "1" at line 2, column 27.
+ij> -- column in group by list not in from list
+select a as d from t1 group by d;
+ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table.
+ij> -- column in group by list not in select list
+select a as b from t1 group by b;
+ERROR 42Y36: Column reference 'A' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
+ij> select a from t1 group by b;
+ERROR 42Y36: Column reference 'A' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
+ij> select a, char(b) from t1 group by a;
+ERROR 42Y36: Column reference 'B' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
+ij> -- columns in group by list must be unique
+select a, b from t1 group by a, a;
+ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
+ij> select a, b from t1 group by a, t1.a;
+ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
+ij> -- cursor with group by is not updatable
+get cursor c1 as 'select a from t1 group by a for update';
+ERROR 42Y90: FOR UPDATE is not permitted in this type of statement.
+ij> -- noncorrelated subquery that returns too many rows
+select a, (select a from t2) from t1 group by a;
+ERROR 21000: Scalar subquery is only allowed to return a single row.
+ij> -- correlation on outer table
+select t2.a, (select b from t1 where t1.b = t2.b) from t1 t2 group by t2.a;
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
+ij> -- having clause
+-- cannot contain column references which are not grouping columns
+select a from t1 group by a having c = 1;
+ERROR 42X04: Column 'C' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'C' is not a column in the target table.
+ij> select a from t1 o group by a having a = (select a from t1 where b = b.o);
+ERROR 42X04: Column 'B.O' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'B.O' is not a column in the target table.
+ij> -- ?s in group by
+select a from t1 group by ?;
+ERROR 42X01: Syntax error: Encountered "?" at line 2, column 27.
+ij> -- group by on long varchar type
+create table unmapped(c1 long varchar);
+0 rows inserted/updated/deleted
+ij> select c1, max(1) from unmapped group by c1;
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
+ij> -- clean up
+drop table t1;
+0 rows inserted/updated/deleted
+ij> drop table t2;
+0 rows inserted/updated/deleted
+ij> drop table unmapped;
+0 rows inserted/updated/deleted
+ij> -- Test group by and having clauses with no aggregates
+-- create an all types tables
+create table t (i int, s smallint, l bigint,
+ c char(10), v varchar(50), lvc long varchar,
+ d double precision, r real,
+ dt date, t time, ts timestamp,
+ b char(2) for bit data, bv varchar(2) for bit data, lbv long varchar for bit data);
+0 rows inserted/updated/deleted
+ij> create table tab1 (
+ i integer,
+ s smallint,
+ l bigint,
+ c char(30),
+ v varchar(30),
+ lvc long varchar,
+ d double precision,
+ r real,
+ dt date,
+ t time,
+ ts timestamp);
+0 rows inserted/updated/deleted
+ij> -- populate tables
+insert into t (i) values (null);
+1 row inserted/updated/deleted
+ij> insert into t (i) values (null);
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (1, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 200, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 2000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'goodbye', 'everyone is here', 'adios, muchachos',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'noone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 100.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 100.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'0f0f', X'ABCD');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'ffff', X'0f0f', X'1234');
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 1000000,
+ 'hello', 'everyone is here', 'what the heck do we care?',
+ 200.0e0, 200.0e0,
+ date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ X'12af', X'ffff', X'ABCD');
+1 row inserted/updated/deleted
+ij> -- bit maps to Byte[], so can't test for now
+insert into tab1
+select i, s, l, c, v, lvc, d, r, dt, t, ts from t;
+17 rows inserted/updated/deleted
+ij> -- simple grouping
+select i from t group by i order by i;
+I
+-----------
+0
+1
+NULL
+ij> select s from t group by s order by s;
+S
+------
+100
+200
+NULL
+ij> select l from t group by l order by l;
+L
+--------------------
+1000000
+2000000
+NULL
+ij> select c from t group by c order by c;
+C
+----------
+goodbye
+hello
+NULL
+ij> select v from t group by v order by v;
+V
+--------------------------------------------------
+everyone is here
+noone is here
+NULL
+ij> select d from t group by d order by d;
+D
+----------------------
+100.0
+200.0
+NULL
+ij> select r from t group by r order by r;
+R
+-------------
+100.0
+200.0
+NULL
+ij> select dt from t group by dt order by dt;
+DT
+----------
+1992-01-01
+1992-09-09
+NULL
+ij> select t from t group by t order by t;
+T
+--------
+12:30:30
+12:55:55
+NULL
+ij> select ts from t group by ts order by ts;
+TS
+--------------------------
+xxxxxxFILTERED-TIMESTAMPxxxxx
+xxxxxxFILTERED-TIMESTAMPxxxxx
+NULL
+ij> select b from t group by b order by b;
+B
+----
+12af
+ffff
+NULL
+ij> select bv from t group by bv order by bv;
+BV
+----
+0f0f
+ffff
+NULL
+ij> -- grouping by long varchar [for bit data] cols should fail in db2 mode
+select lbv from t group by lbv order by lbv;
+LBV
+--------------------------------------------------------------------------------------------------------------------------------
+1234
+abcd
+NULL
+ij> -- multicolumn grouping
+select i, dt, b from t where 1=1 group by i, dt, b order by i,dt,b;
+I |DT |B
+---------------------------
+0 |1992-01-01|12af
+0 |1992-01-01|ffff
+0 |1992-09-09|12af
+1 |1992-01-01|12af
+NULL |NULL |NULL
+ij> select i, dt, b from t group by i, dt, b order by i,dt,b;
+I |DT |B
+---------------------------
+0 |1992-01-01|12af
+0 |1992-01-01|ffff
+0 |1992-09-09|12af
+1 |1992-01-01|12af
+NULL |NULL |NULL
+ij> select i, dt, b from t group by b, i, dt order by i,dt,b;
+I |DT |B
+---------------------------
+0 |1992-01-01|12af
+0 |1992-01-01|ffff
+0 |1992-09-09|12af
+1 |1992-01-01|12af
+NULL |NULL |NULL
+ij> select i, dt, b from t group by dt, i, b order by i,dt,b;
+I |DT |B
+---------------------------
+0 |1992-01-01|12af
+0 |1992-01-01|ffff
+0 |1992-09-09|12af
+1 |1992-01-01|12af
+NULL |NULL |NULL
+ij> -- group by expression
+select expr1, expr2
+from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1 order by expr2,expr1;
+EXPR1 |EXPR2
+------------------------------------------------------------------------
+0 |goodbye everyone is here
+0 |hello everyone is here
+0 |hello everyone is here
+100 |hello everyone is here
+0 |hello noone is here
+NULL |NULL
+ij> -- group by correlated subquery
+select i, expr1
+from (select i, (select distinct i from t m where m.i = t.i) from t) t (i, expr1)
+ group by i, expr1 order by i,expr1;
+I |EXPR1
+-----------------------
+0 |0
+1 |1
+NULL |NULL
+ij> -- distinct and group by
+select distinct i, dt, b from t group by i, dt, b order by i,dt,b;
+I |DT |B
+---------------------------
+0 |1992-01-01|12af
+0 |1992-01-01|ffff
+0 |1992-09-09|12af
+1 |1992-01-01|12af
+NULL |NULL |NULL
+ij> -- order by and group by
+-- same order
+select i, dt, b from t group by i, dt, b order by i, dt, b;
+I |DT |B
+---------------------------
+0 |1992-01-01|12af
+0 |1992-01-01|ffff
+0 |1992-09-09|12af
+1 |1992-01-01|12af
+NULL |NULL |NULL
+ij> -- subset in same order
+select i, dt, b from t group by i, dt, b order by i, dt;
+I |DT |B
+---------------------------
+0 |1992-01-01|ffff
+0 |1992-01-01|12af
+0 |1992-09-09|12af
+1 |1992-01-01|12af
+NULL |NULL |NULL
+ij> -- different order
+select i, dt, b from t group by i, dt, b order by b, dt, i;
+I |DT |B
+---------------------------
+0 |1992-01-01|12af
+1 |1992-01-01|12af
+0 |1992-09-09|12af
+0 |1992-01-01|ffff
+NULL |NULL |NULL
+ij> -- subset in different order
+select i, dt, b from t group by i, dt, b order by b, dt;
+I |DT |B
+---------------------------
+0 |1992-01-01|12af
+1 |1992-01-01|12af
+0 |1992-09-09|12af
+0 |1992-01-01|ffff
+NULL |NULL |NULL
+ij> -- group by without having in from subquery
+select * from
+(select i, dt from t group by i, dt) t (t_i, t_dt),
+(select i, dt from t group by i, dt) m (m_i, m_dt)
+where t_i = m_i and t_dt = m_dt order by t_i,t_dt,m_i,m_dt;
+T_I |T_DT |M_I |M_DT
+---------------------------------------------
+0 |1992-01-01|0 |1992-01-01
+0 |1992-09-09|0 |1992-09-09
+1 |1992-01-01|1 |1992-01-01
+ij> select * from
+(select i, dt from t group by i, dt) t (t_i, t_dt),
+(select i, dt from t group by i, dt) m (m_i, m_dt)
+group by t_i, t_dt, m_i, m_dt order by t_i,t_dt,m_i,m_dt;
+T_I |T_DT |M_I |M_DT
+---------------------------------------------
+0 |1992-01-01|0 |1992-01-01
+0 |1992-01-01|0 |1992-09-09
+0 |1992-01-01|1 |1992-01-01
+0 |1992-01-01|NULL |NULL
+0 |1992-09-09|0 |1992-01-01
+0 |1992-09-09|0 |1992-09-09
+0 |1992-09-09|1 |1992-01-01
+0 |1992-09-09|NULL |NULL
+1 |1992-01-01|0 |1992-01-01
+1 |1992-01-01|0 |1992-09-09
+1 |1992-01-01|1 |1992-01-01
+1 |1992-01-01|NULL |NULL
+NULL |NULL |0 |1992-01-01
+NULL |NULL |0 |1992-09-09
+NULL |NULL |1 |1992-01-01
+NULL |NULL |NULL |NULL
+ij> select * from
+(select i, dt from t group by i, dt) t (t_i, t_dt),
+(select i, dt from t group by i, dt) m (m_i, m_dt)
+where t_i = m_i and t_dt = m_dt
+group by t_i, t_dt, m_i, m_dt order by t_i,t_dt,m_i,m_dt;
+T_I |T_DT |M_I |M_DT
+---------------------------------------------
+0 |1992-01-01|0 |1992-01-01
+0 |1992-09-09|0 |1992-09-09
+1 |1992-01-01|1 |1992-01-01
+ij> select t.*, m.* from
+(select i, dt from t group by i, dt) t (t_i, t_dt),
+(select i, dt from t group by i, dt) m (t_i, t_dt)
+where t.t_i = m.t_i and t.t_dt = m.t_dt
+group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,t.t_dt,m.t_i,m.t_dt;
+T_I |T_DT |T_I |T_DT
+---------------------------------------------
+0 |1992-01-01|0 |1992-01-01
+0 |1992-09-09|0 |1992-09-09
+1 |1992-01-01|1 |1992-01-01
+ij> select t.t_i, t.t_dt, m.* from
+(select i, dt from t group by i, dt) t (t_i, t_dt),
+(select i, dt from t group by i, dt) m (t_i, t_dt)
+where t.t_i = m.t_i and t.t_dt = m.t_dt
+group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,t.t_dt,m.t_i,m.t_dt;
+T_I |T_DT |T_I |T_DT
+---------------------------------------------
+0 |1992-01-01|0 |1992-01-01
+0 |1992-09-09|0 |1992-09-09
+1 |1992-01-01|1 |1992-01-01
+ij> -- additional columns in group by list not in select list
+select i, dt, b from t group by i, dt, b order by i,dt,b;
+I |DT |B
+---------------------------
+0 |1992-01-01|12af
+0 |1992-01-01|ffff
+0 |1992-09-09|12af
+1 |1992-01-01|12af
+NULL |NULL |NULL
+ij> select t.i from t group by i, dt, b order by i;
+I
+-----------
+0
+0
+0
+1
+NULL
+ij> select t.dt from t group by i, dt, b order by dt;
+DT
+----------
+1992-01-01
+1992-01-01
+1992-01-01
+1992-09-09
+NULL
+ij> select t.b from t group by i, dt, b order by b;
+B
+----
+12af
+12af
+12af
+ffff
+NULL
+ij> select t.t_i, m.t_i from
+(select i, dt from t group by i, dt) t (t_i, t_dt),
+(select i, dt from t group by i, dt) m (t_i, t_dt)
+where t.t_i = m.t_i and t.t_dt = m.t_dt
+group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,m.t_i;
+T_I |T_I
+-----------------------
+0 |0
+0 |0
+1 |1
+ij> -- having
+-- parameters in having clause
+prepare p1 as 'select i, dt, b from t group by i, dt, b having i = ? order by i,dt,b';
+ij> execute p1 using 'values 0';
+IJ WARNING: Autocommit may close using result set
+I |DT |B
+---------------------------
+0 |1992-01-01|12af
+0 |1992-01-01|ffff
+0 |1992-09-09|12af
+ij> remove p1;
+ij> -- group by with having in from subquery
+select * from
+(select i, dt from t group by i, dt having 1=1) t (t_i, t_dt),
+(select i, dt from t group by i, dt having i = 0) m (m_i, m_dt)
+where t_i = m_i and t_dt = m_dt order by t_i,t_dt,m_i,m_dt;
+T_I |T_DT |M_I |M_DT
+---------------------------------------------
+0 |1992-01-01|0 |1992-01-01
+0 |1992-09-09|0 |1992-09-09
+ij> select * from
+(select i, dt from t group by i, dt having 1=1) t (t_i, t_dt),
+(select i, dt from t group by i, dt having i = 0) m (m_i, m_dt)
+group by t_i, t_dt, m_i, m_dt order by t_i,t_dt,m_i,m_dt;
+T_I |T_DT |M_I |M_DT
+---------------------------------------------
+0 |1992-01-01|0 |1992-01-01
+0 |1992-01-01|0 |1992-09-09
+0 |1992-09-09|0 |1992-01-01
+0 |1992-09-09|0 |1992-09-09
+1 |1992-01-01|0 |1992-01-01
+1 |1992-01-01|0 |1992-09-09
+NULL |NULL |0 |1992-01-01
+NULL |NULL |0 |1992-09-09
+ij> select * from
+(select i, dt from t group by i, dt having 1=1) t (t_i, t_dt),
+(select i, dt from t group by i, dt having i = 0) m (m_i, m_dt)
+where t_i = m_i and t_dt = m_dt
+group by t_i, t_dt, m_i, m_dt
+having t_i * m_i = m_i * t_i order by t_i,t_dt,m_i,m_dt;
+T_I |T_DT |M_I |M_DT
+---------------------------------------------
+0 |1992-01-01|0 |1992-01-01
+0 |1992-09-09|0 |1992-09-09
+ij> -- correlated subquery in having clause
+select i, dt from t
+group by i, dt
+having i = (select distinct i from tab1 where t.i = tab1.i) order by i,dt;
+I |DT
+----------------------
+0 |1992-01-01
+0 |1992-09-09
+1 |1992-01-01
+ij> select i, dt from t
+group by i, dt
+having i = (select i from t m group by i having t.i = m.i) order by i,dt;
+I |DT
+----------------------
+0 |1992-01-01
+0 |1992-09-09
+1 |1992-01-01
+ij> -- column references in having clause match columns in group by list
+select i as outer_i, dt from t
+group by i, dt
+having i = (select i from t m group by i having t.i = m.i) order by outer_i,dt;
+OUTER_I |DT
+----------------------
+0 |1992-01-01
+0 |1992-09-09
+1 |1992-01-01
+ij> -- additional columns in group by list not in select list
+select i, dt from t group by i, dt order by i,dt;
+I |DT
+----------------------
+0 |1992-01-01
+0 |1992-09-09
+1 |1992-01-01
+NULL |NULL
+ij> select t.dt from t group by i, dt having i = 0 order by t.dt;
+DT
+----------
+1992-01-01
+1992-09-09
+ij> select t.dt from t group by i, dt having i <> 0 order by t.dt;
+DT
+----------
+1992-01-01
+ij> select t.dt from t group by i, dt having i != 0 order by t.dt;
+DT
+----------
+1992-01-01
+ij> -- drop tables
+drop table t;
+0 rows inserted/updated/deleted
+ij> drop table tab1;
+0 rows inserted/updated/deleted
+ij> -- negative tests for selects with a having clause without a group by
+-- create a table
+create table t1(c1 int, c2 int);
+0 rows inserted/updated/deleted
+ij> -- binding of having clause
+select 1 from t1 having 1;
+ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
+ij> -- column references in having clause not allowed if no group by
+select * from t1 having c1 = 1;
+ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
+ij> select 1 from t1 having c1 = 1;
+ERROR 42X04: Column 'C1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'C1' is not a column in the target table.
+ij> -- correlated subquery in having clause
+select * from t1 t1_outer
+having 1 = (select 1 from t1 where c1 = t1_outer.c1);
+ERROR 42Y35: Column reference 'T1_OUTER.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
+ij> -- drop the table
+drop table t1;
+0 rows inserted/updated/deleted
+ij> -- bug 5653
+-- test (almost useful) restrictions on a having clause without a group by clause
+-- create the table
+create table t1 (c1 float);
+0 rows inserted/updated/deleted
+ij> -- populate the table
+insert into t1 values 0.0, 90.0;
+2 rows inserted/updated/deleted
+ij> -- this is the only query that should not fail
+-- filter out all rows
+select 1 from t1 having 1=0;
+1
+-----------
+ij> -- all 6 queries below should fail after bug 5653 is fixed
+-- select *
+select * from t1 having 1=1;
+ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
+ij> -- select column
+select c1 from t1 having 1=1;
+ERROR 42Y35: Column reference 'C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
+ij> -- select with a built-in function sqrt
+select sqrt(c1) from t1 having 1=1;
+ERROR 42Y35: Column reference 'C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
+ij> -- non-correlated subquery in having clause
+select * from t1 having 1 = (select 1 from t1 where c1 = 0.0);
+ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
+ij> -- expression in select list
+select (c1 * c1) / c1 from t1 where c1 <> 0 having 1=1;
+ERROR 42Y35: Column reference 'C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
+ij> -- between
+select * from t1 having 1 between 1 and 2;
+ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
+ij> -- drop the table
+drop table t1;
+0 rows inserted/updated/deleted
+ij> -- bug 5920
+-- test that HAVING without GROUPBY makes one group
+create table t(c int, d int);
+0 rows inserted/updated/deleted
+ij> insert into t(c,d) values (1,10),(2,20),(2,20),(3,30),(3,30),(3,30);
+6 rows inserted/updated/deleted
+ij> select avg(c) from t having 1 < 2;
+1
+-----------
+2
+ij> -- used to give several rows, now gives only one
+select 10 from t having 1 < 2;
+1
+-----------
+10
+ij> -- ok, gives one row
+select 10,avg(c) from t having 1 < 2;
+1 |2
+-----------------------
+10 |2
+ij> drop table t;
+0 rows inserted/updated/deleted
+ij>
Modified: incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/miscerrors.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/miscerrors.out?rev=190514&r1=190513&r2=190514&view=diff
==============================================================================
--- incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/miscerrors.out (original)
+++ incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/miscerrors.out Mon Jun 13 14:41:33 2005
@@ -28,14 +28,4 @@
ij> -- set isolation to repeatable read
set isolation serializable;
0 rows inserted/updated/deleted
-ij> -- Print the output of the ErrorLogVTI, make sure it's valid
-SELECT * FROM new org.apache.derby.diag.ErrorLogReader() vti;
-TS |THREADID |XID |LCCID |DATABASE |DRDAID |LOGTEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |92 |0 | wombat | null |Cleanup action starting
-xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |92 |0 | wombat | null |Failed Statement is: --
-xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |97 |0 | wombat | null |Cleanup action starting
-xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |97 |0 | wombat | null |Failed Statement is: create table a (one int, two int)
-xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |100 |0 | wombat | null |Cleanup action starting
-xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |100 |0 | wombat | null |Failed Statement is: create table a (one int)
ij>