ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- 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 constant. should compile but fail because -- it is not a valid grouping expression. select * from t1 group by 1; 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 valid grouping expressions and valid aggregate expressions. 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 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 valid grouping expressions and valid aggregate expressions. ij> select a from t1 group by b; 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 valid grouping expressions and valid aggregate expressions. ij> select a, char(b) from t1 group by 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 valid grouping expressions and valid aggregate expressions. 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 valid grouping expressions 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: ?. ij> -- group by on long varchar type create table unmapped(c1 long varchar); 0 rows inserted/updated/deleted ij> select c1, max(1) from unmapped group by c1; ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> -- clean up drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table unmapped; 0 rows inserted/updated/deleted ij> -- Test group by and having clauses with no aggregates -- create an all types tables create table t (i int, s smallint, l bigint, c char(10), v varchar(50), lvc long varchar, d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(2) for bit data, lbv long varchar for bit data); 0 rows inserted/updated/deleted ij> create table tab1 ( i integer, s smallint, l bigint, c char(30), v varchar(30), lvc long varchar, d double precision, r real, dt date, t time, ts timestamp); 0 rows inserted/updated/deleted ij> -- populate tables insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (1, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 200, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 2000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'goodbye', 'everyone is here', 'adios, muchachos', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'noone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 100.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 100.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0f0f', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'ffff', X'0f0f', X'1234'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'hello', 'everyone is here', 'what the heck do we care?', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'ffff', X'ABCD'); 1 row inserted/updated/deleted ij> -- bit maps to Byte[], so can't test for now insert into tab1 select i, s, l, c, v, lvc, d, r, dt, t, ts from t; 17 rows inserted/updated/deleted ij> -- simple grouping select i from t group by i order by i; I ----------- 0 1 NULL ij> select s from t group by s order by s; S ------ 100 200 NULL ij> select l from t group by l order by l; L -------------------- 1000000 2000000 NULL ij> select c from t group by c order by c; C ---------- goodbye hello NULL ij> select v from t group by v order by v; V -------------------------------------------------- everyone is here noone is here NULL ij> select d from t group by d order by d; D ---------------------- 100.0 200.0 NULL ij> select r from t group by r order by r; R ------------- 100.0 200.0 NULL ij> select dt from t group by dt order by dt; DT ---------- 1992-01-01 1992-09-09 NULL ij> select t from t group by t order by t; T -------- 12:30:30 12:55:55 NULL ij> select ts from t group by ts order by ts; TS -------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx NULL ij> select b from t group by b order by b; B ---- 12af ffff NULL ij> select bv from t group by bv order by bv; BV ---- 0f0f ffff NULL ij> -- grouping by long varchar [for bit data] cols should fail in db2 mode select lbv from t group by lbv order by lbv; ERROR X0X67: Columns of type 'LONG VARCHAR FOR BIT DATA' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> -- multicolumn grouping select i, dt, b from t where 1=1 group by i, dt, b order by i,dt,b; I |DT |B --------------------------- 0 |1992-01-01|12af 0 |1992-01-01|ffff 0 |1992-09-09|12af 1 |1992-01-01|12af NULL |NULL |NULL ij> select i, dt, b from t group by i, dt, b order by i,dt,b; I |DT |B --------------------------- 0 |1992-01-01|12af 0 |1992-01-01|ffff 0 |1992-09-09|12af 1 |1992-01-01|12af NULL |NULL |NULL ij> select i, dt, b from t group by b, i, dt order by i,dt,b; I |DT |B --------------------------- 0 |1992-01-01|12af 0 |1992-01-01|ffff 0 |1992-09-09|12af 1 |1992-01-01|12af NULL |NULL |NULL ij> select i, dt, b from t group by dt, i, b order by i,dt,b; I |DT |B --------------------------- 0 |1992-01-01|12af 0 |1992-01-01|ffff 0 |1992-09-09|12af 1 |1992-01-01|12af NULL |NULL |NULL ij> -- group by expression select expr1, expr2 from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1 order by expr2,expr1; EXPR1 |EXPR2 ------------------------------------------------------------------------ 0 |goodbye everyone is here 0 |hello everyone is here 100 |hello everyone is here 0 |hello noone is here NULL |NULL ij> -- group by correlated subquery select i, expr1 from (select i, (select distinct i from t m where m.i = t.i) from t) t (i, expr1) group by i, expr1 order by i,expr1; I |EXPR1 ----------------------- 0 |0 1 |1 NULL |NULL ij> -- distinct and group by select distinct i, dt, b from t group by i, dt, b order by i,dt,b; I |DT |B --------------------------- 0 |1992-01-01|12af 0 |1992-01-01|ffff 0 |1992-09-09|12af 1 |1992-01-01|12af NULL |NULL |NULL ij> -- order by and group by -- same order select i, dt, b from t group by i, dt, b order by i, dt, b; I |DT |B --------------------------- 0 |1992-01-01|12af 0 |1992-01-01|ffff 0 |1992-09-09|12af 1 |1992-01-01|12af NULL |NULL |NULL ij> -- subset in same order select i, dt, b from t group by i, dt, b order by i, dt; I |DT |B --------------------------- 0 |1992-01-01|12af 0 |1992-01-01|ffff 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 --------------------------- 1 |1992-01-01|12af 0 |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>