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, 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>