# test of problems with having (Reported by Mark Rogers)
#

--disable_warnings
drop table if exists t1,t2,t3;
--enable_warnings

create table t1 (a int);
select count(a) as b from t1 where a=0 having b > 0;
insert into t1 values (null);
select count(a) as b from t1 where a=0 having b > 0;
select count(a) as b from t1 where a=0 having b >=0;
analyze table t1;
explain select count(a) as b from t1 where a=0 having b >=0;
drop table t1; 

#
# Test of problem with HAVING and AVG()
#

CREATE TABLE t1 (
  raw_id int(10) NOT NULL default '0',
  chr_start int(10) NOT NULL default '0',
  chr_end int(10) NOT NULL default '0',
  raw_start int(10) NOT NULL default '0',
  raw_end int(10) NOT NULL default '0',
  raw_ori int(2) NOT NULL default '0'
);

INSERT INTO t1 VALUES (469713,1,164123,1,164123,1),(317330,164124,317193,101,153170,1),(469434,317194,375620,101,58527,1),(591816,375621,484273,1,108653,1),(591807,484274,534671,91,50488,1),(318885,534672,649362,101,114791,1),(318728,649363,775520,102,126259,1),(336829,775521,813997,101,38577,1),(317740,813998,953227,101,139330,1),(1,813998,953227,101,139330,1);

CREATE TABLE t2 (
  id int(10) unsigned NOT NULL default '0',
  contig_id int(10) unsigned NOT NULL default '0',
  seq_start int(10) NOT NULL default '0',
  seq_end int(10) NOT NULL default '0',
  strand tinyint(2) NOT NULL default '0',
  KEY id (id)
);
INSERT INTO t2 VALUES (133195,469713,61327,61384,1),(133196,469713,64113,64387,1),(133197,1,1,1,0),(133197,1,1,1,-2);
SELECT e.id,
   MIN( IF(sgp.raw_ori=1,
          (e.seq_start+sgp.chr_start-sgp.raw_start),  
          (sgp.chr_start+sgp.raw_end-e.seq_end))) as start, 
   MAX( IF(sgp.raw_ori=1,
           (e.seq_end+sgp.chr_start-sgp.raw_start),  
           (sgp.chr_start+sgp.raw_end-e.seq_start))) as end, 
   AVG(IF (sgp.raw_ori=1,e.strand,(-e.strand))) as chr_strand 
FROM  t1 sgp,
      t2 e  
WHERE sgp.raw_id=e.contig_id 
GROUP BY e.id 
HAVING chr_strand= -1 and end >= 0 
  AND start <= 999660;
drop table t1,t2;

#
# Test problem with having and MAX() IS NOT NULL
#

CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL);
INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50);
select Fld1, max(Fld2) as q from t1 group by Fld1 having q is not null;
select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null;
select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null;
select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
drop table t1;

#
# Test problem with count(distinct) in having
#
create table t1 (id int not null, qty int not null);
insert into t1 values (1,2),(1,3),(2,4),(2,5);
select id, sum(qty) as sqty from t1 group by id having sqty>2;
select sum(qty) as sqty from t1 group by id having count(id) > 0;
select sum(qty) as sqty from t1 group by id having count(distinct id) > 0;
drop table t1;

#
# Test case for Bug #4358 Problem with HAVING clause that uses alias from the
#              select list and TEXT field 
#

CREATE TABLE t1 (
  `id` bigint(20) NOT NULL default '0',
  `description` text
);

CREATE TABLE t2 (
  `id` bigint(20) NOT NULL default '0',
  `description` varchar(20)
);

INSERT INTO t1  VALUES (1, 'test');
INSERT INTO t2 VALUES (1, 'test');

CREATE TABLE t3 (
  `id`       bigint(20) NOT NULL default '0',
  `order_id` bigint(20) NOT NULL default '0'
);

select
	a.id, a.description,
	count(b.id) as c 
from t1 a left join t3 b on a.id=b.order_id 
group by a.id, a.description 
having (a.description is not null) and (c=0);

select
	a.*, 
	count(b.id) as c 
from t2 a left join t3 b on a.id=b.order_id 
group by a.id, a.description
having (a.description is not null) and (c=0);

INSERT INTO t1  VALUES (2, 'test2');

select
	a.id, a.description,
	count(b.id) as c 
from t1 a left join t3 b on a.id=b.order_id 
group by a.id, a.description 
having (a.description is not null) and (c=0);
drop table t1,t2,t3;

#
# Bug #14274: HAVING clause containing only set function
#

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (3), (4), (1), (3), (1);

--sorted_result
SELECT SUM(a) FROM t1 GROUP BY a HAVING SUM(a)>0;
--sorted_result
SELECT SUM(a) FROM t1 GROUP BY a HAVING SUM(a);

DROP TABLE t1;

#
# Bug #14927: HAVING clause containing constant false conjunct
#

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1), (2), (1), (3), (2), (1);

SELECT a FROM t1 GROUP BY a HAVING a > 1;
SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1;
SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;

analyze table t1;
EXPLAIN SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1;
EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;

DROP table t1;  

#
# Bug #29911: HAVING clause depending on constant table and evaluated to false
#

CREATE TABLE t1 (a int PRIMARY KEY);
CREATE TABLE t2 (b int PRIMARY KEY, a int);
CREATE TABLE t3 (b int, flag int);

INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1,1), (2,1), (3,1);
INSERT INTO t3(b,flag) VALUES (2, 1);

SELECT t1.a
  FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
    GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;

SELECT DISTINCT t1.a, MAX(t3.flag)
  FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
    GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;

SELECT DISTINCT t1.a
  FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
    GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;

DROP TABLE t1,t2,t3;

# End of 4.1 tests

#
# Tests for WL#1972 CORRECT EVALUATION OF COLUMN REFERENCES IN THE HAVING CLAUSE
# Per the SAP VERI tests and WL#1972, MySQL must ensure that HAVING can
# correctly evaluate column references from the GROUP BY clause, even if the
# same references are not also found in the select list.
#

# set global sql_mode='ansi';
# set session sql_mode='ansi';

create table t1 (col1 int, col2 varchar(5), col_t1 int);
create table t2 (col1 int, col2 varchar(5), col_t2 int);
create table t3 (col1 int, col2 varchar(5), col_t3 int);

insert into t1 values(10,'hello',10);
insert into t1 values(20,'hello',20);
insert into t1 values(30,'hello',30);
insert into t1 values(10,'bye',10);
insert into t1 values(10,'sam',10);
insert into t1 values(10,'bob',10);

insert into t2 select * from t1;
insert into t3 select * from t1;

select count(*) from t1 group by col1 having col1 = 10;
select count(*) as count_col1 from t1 group by col1 having col1 = 10;
select count(*) as count_col1 from t1 as tmp1 group by col1 having col1 = 10;
select count(*) from t1 group by col2 having col2 = 'hello';
--error 1054
select count(*) from t1 group by col2 having col1 = 10;
--sorted_result
select col1 as count_col1 from t1 as tmp1 group by col1 having col1 = 10;
--sorted_result
select col1 as count_col1 from t1 as tmp1 group by col1 having count_col1 = 10;
--sorted_result
select col1 as count_col1 from t1 as tmp1 group by count_col1 having col1 = 10;
--sorted_result
# ANSI: should return SQLSTATE 42000 Syntax error or access violation 
# MySQL: returns 10 - because of GROUP BY name resolution
--sorted_result
select col1 as count_col1 from t1 as tmp1 group by count_col1 having count_col1 = 10;
# ANSI: should return SQLSTATE 42000 Syntax error or access violation 
# MySQL: returns 10 - because of GROUP BY name resolution
--sorted_result
select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col1 = 10;
--sorted_result
select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having count_col1 = 10;
--sorted_result
select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col2 = 'hello';
--sorted_result
select col1 as count_col1,col2 as group_col2 from t1 as tmp1 group by col1,col2 having group_col2 = 'hello';
--error 1064
select sum(col1) as co12 from t1 group by col2 having col2 10; 
--sorted_result
select sum(col1) as co2, count(col2) as cc from t1 group by col1 having col1 =10; 
--error 1054
select t2.col2 from t2 group by t2.col1, t2.col2 having t1.col1 <= 10;


#
# queries with nested sub-queries
#

# the having column is resolved in the same query
select t1.col1 from t1
where t1.col2 in 
      (select t2.col2 from t2 
       group by t2.col1, t2.col2 having t2.col1 <= 10);

select t1.col1 from t1
where t1.col2 in 
      (select t2.col2 from t2
       group by t2.col1, t2.col2
       having t2.col1 <=
              (select min(t3.col1) from t3));

# the having column is resolved in the SELECT clause of the outer query -
# works in ANSI
select t1.col1 from t1
where t1.col2 in
      (select t2.col2 from t2 
       group by t2.col1, t2.col2 having t1.col1 <= 10);

# the having column is resolved in the FROM clause of the outer query -
# works in ANSI
select t1.col1 from t1
where t1.col2 in 
      (select t2.col2 from t2 
       group by t2.col1, t2.col2 having col_t1 <= 10);

# Item_field must be resolved in the same way as Item_ref
select sum(col1) from t1
group by col_t1
having (select col_t1 from t2 where col_t1 = col_t2 order by col_t2 limit 1);

# nested queries with HAVING, inner having column resolved in outer FROM clause
# the outer having column is not referenced in GROUP BY which results in an error
--error 1054
select t1.col1 from t1
where t1.col2 in 
      (select t2.col2 from t2 
       group by t2.col1, t2.col2 having col_t1 <= 10)
having col_t1 <= 20;

# both having columns are resolved in the GROUP clause of the outer query
--source include/turn_off_only_full_group_by.inc
select t1.col1 from t1
where t1.col2 in 
      (select t2.col2 from t2 
       group by t2.col1, t2.col2 having col_t1 <= 10)
group by col_t1
having col_t1 <= 20;
--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc

#
# nested HAVING clauses
#

# non-correlated subqueries
select col_t1, sum(col1) from t1
group by col_t1
having col_t1 > 10 and
       exists (select sum(t2.col1) from t2
               group by t2.col2 having t2.col2 > 'b');

# correlated subqueries - inner having column 't1.col2' resolves to
# the outer FROM clause, which cannot be used because the outer query
# is grouped
--error 1054
select sum(col1) from t1
group by col_t1
having col_t1 in (select sum(t2.col1) from t2
                  group by t2.col2, t2.col1 having t2.col1 = t1.col1);

# correlated subqueries - inner having column 'col_t1' resolves to
# the outer GROUP clause
select sum(col1) from t1
group by col_t1
having col_t1 in (select sum(t2.col1) from t2
                  group by t2.col2, t2.col1 having t2.col1 = col_t1);

#
# queries with joins and ambiguous column names
#
--error 1052
select t1.col1, t2.col1 from t1, t2 where t1.col1 = t2.col1
group by t1.col1, t2.col1 having col1 = 2;

--error 1052
select t1.col1*10+t2.col1 from t1,t2 where t1.col1=t2.col1
group by t1.col1, t2.col1 having col1 = 2;

drop table t1, t2, t3;

# More queries to test ANSI compatibility
create table t1 (s1 int);
insert into t1 values (1),(2),(3);

select count(*) from t1 group by s1 having s1 is null;

# prepared statements prints warnings too early
--disable_ps_protocol
select s1*0 as s1 from t1 group by s1 having s1 <> 0;
--enable_ps_protocol

# ANSI requires: 3 rows
# MySQL returns: 0 rows - because of GROUP BY name resolution

select s1*0 from t1 group by s1 having s1 = 0;

select s1 from t1 group by 1 having 1 = 0;

select count(s1) from t1 group by s1 having count(1+1)=2;
# ANSI requires: 3 rows
# MySQL returns: 0 rows - because of GROUP BY name resolution

select count(s1) from t1 group by s1 having s1*0=0;

-- error 1052
select * from t1 a, t1 b group by a.s1 having s1 is null;
# ANSI requires: 0 rows
# MySQL returns:
# "ERROR 1052 (23000): Column 's1' in having clause is ambiguous"
# I think the column is ambiguous in ANSI too.
# It is the same as:
#   select a.s1, b.s1 from t1 a, t1 b group by a.s1 having s1 is null;
# currently we first check SELECT, thus s1 is ambiguous.

drop table t1;

set names latin1;
create table t1 (s1 char character set latin1 collate latin1_german1_ci);
insert ignore into t1 values ('ü'),('y');

--source include/turn_off_only_full_group_by.inc
select s1,count(s1) from t1
group by s1 collate latin1_swedish_ci having s1 = 'y';
set names utf8mb4;
# ANSI requires: 1 row, with count(s1) = 2
# MySQL returns: 1 row, with count(s1) = 1
--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc

drop table t1;


#
# Bug #15917: unexpected complain for a name in having clause
# when the server is run on Windows or with --lower-case-table-names=1
#

--disable_warnings
DROP SCHEMA IF EXISTS HU;
--enable_warnings
CREATE SCHEMA HU ;
USE HU ;

CREATE TABLE STAFF
 (EMPNUM   CHAR(3) NOT NULL UNIQUE,
  EMPNAME  CHAR(20),
  GRADE    DECIMAL(4),
  CITY     CHAR(15));

CREATE TABLE PROJ
 (PNUM     CHAR(3) NOT NULL UNIQUE,
  PNAME    CHAR(20),
  PTYPE    CHAR(6),
  BUDGET   DECIMAL(9),
  CITY     CHAR(15));

INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');
INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');
INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');
INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');
INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');

INSERT INTO PROJ VALUES  ('P1','MXSS','Design',10000,'Deale');
INSERT INTO PROJ VALUES  ('P2','CALM','Code',30000,'Vienna');
INSERT INTO PROJ VALUES  ('P3','SDP','Test',30000,'Tampa');
INSERT INTO PROJ VALUES  ('P4','SDP','Design',20000,'Deale');
INSERT INTO PROJ VALUES  ('P5','IRM','Test',10000,'Vienna');
INSERT INTO PROJ VALUES  ('P6','PAYR','Design',50000,'Deale');

SELECT EMPNUM, GRADE*1000
  FROM HU.STAFF WHERE GRADE * 1000 > 
                  ANY (SELECT SUM(BUDGET) FROM HU.PROJ
                         GROUP BY CITY, PTYPE
                           HAVING HU.PROJ.CITY = HU.STAFF.CITY);

DROP SCHEMA HU;
USE test;
#
# Bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode.
#
create table t1(f1 int);

--source include/turn_off_only_full_group_by.inc
select f1 from t1 having max(f1)=f1;
--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
select f1 from t1 group by f1 having max(f1)=f1;
set session sql_mode='ONLY_FULL_GROUP_BY';
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
select f1 from t1 having max(f1)=f1;
select f1 from t1 group by f1 having max(f1)=f1;
set session sql_mode='';
drop table t1;


#
# Bug #38637: COUNT DISTINCT prevents NULL testing in HAVING clause
#
CREATE TABLE t1 ( a INT, b INT);
INSERT INTO t1 VALUES (1, 1), (2,2), (3, NULL);
SELECT b, COUNT(DISTINCT a) FROM t1 GROUP BY b HAVING b is NULL;
DROP TABLE t1;


--echo #
--echo # Bug#50995 Having clause on subquery result produces incorrect results.
--echo #

CREATE TABLE t1
(
 id1 INT,
 id2 INT NOT NULL,
 INDEX id1(id2)
);

INSERT INTO t1 SET id1=1, id2=1;
INSERT INTO t1 SET id1=2, id2=1;
INSERT INTO t1 SET id1=3, id2=1;

SELECT t1.id1,
(SELECT 0 FROM DUAL
 WHERE t1.id1=t1.id1) AS amount FROM t1
WHERE t1.id2 = 1
HAVING amount > 0
ORDER BY t1.id1;

DROP TABLE t1;

--echo #
--echo # Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
--echo #
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT);
INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9);

SELECT table1.f1, table2.f2
FROM t1 AS table1
JOIN t1 AS table2 ON table1.f3 = table2.f3
WHERE table2.f1 = 2
GROUP BY table1.f1, table2.f2
HAVING (table2.f2 = 8 AND table1.f1 >= 6);

EXPLAIN
SELECT table1.f1, table2.f2
FROM t1 AS table1
JOIN t1 AS table2 ON table1.f3 = table2.f3
WHERE table2.f1 = 2
GROUP BY table1.f1, table2.f2
HAVING (table2.f2 = 8 AND table1.f1 >= 6);

EXPLAIN
SELECT table1.f1, table2.f2
FROM t1 AS table1
JOIN t1 AS table2 ON table1.f3 = table2.f3
WHERE table2.f1 = 2
GROUP BY table1.f1, table2.f2
HAVING (table2.f2 = 8);

DROP TABLE t1;

--echo #
--echo # Bug#52336 Segfault / crash in 5.1 copy_fields (param=0x9872980) at sql_select.cc:15355
--echo #
CREATE TABLE t1(f1 INT, f2 INT);
INSERT INTO t1 VALUES (10,8);
CREATE TABLE t2 (f1 INT);
INSERT INTO t2 VALUES (5);

SELECT  COUNT(f1) FROM t2
HAVING (7, 9)  IN (SELECT f1, MIN(f2) FROM t1);

DROP TABLE t1, t2;

CREATE TABLE t1 (f1 INT, f2 VARCHAR(1));
INSERT INTO t1 VALUES (16,'f');
INSERT INTO t1 VALUES (16,'f');
CREATE TABLE t2 (f1 INT, f2 VARCHAR(1));
INSERT INTO t2 VALUES (13,'f');
INSERT INTO t2 VALUES (20,'f');
CREATE TABLE t3 (f1 INT, f2 VARCHAR(1));
INSERT INTO t3 VALUES (7,'f');

SELECT t1.f2 FROM t1
STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2  = t2.f2  ) ON t3 .f2  = t2 .f2
HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
ORDER BY f2;

DROP TABLES t1,t2,t3;

--echo #
--echo # Bug#52340 Segfault: read_cached_record (tab=0x94a2634) at sql_select.cc:14411
--echo #
CREATE TABLE t1 (f1 INT, f2 VARCHAR(1));
INSERT INTO t1 VALUES (16,'d');
CREATE TABLE t2 (f1 INT, f2 VARCHAR(1));
INSERT INTO t2 VALUES (13,'e');
INSERT INTO t2 VALUES (20,'d');

SELECT MAX(t2.f2) FROM t2 JOIN t1 ON t1.f2
HAVING ('e' , 'd') IN
(SELECT ts1.f2, ts2.f2 FROM t2 ts1 JOIN t2 ts2 ON ts1.f1)
ORDER BY t1.f2;

DROP TABLE t1,t2;

--echo End of 5.0 tests

--echo #
--echo # Bug#54416 MAX from JOIN with HAVING returning NULL with 5.1 and Empty set
--echo #

CREATE TABLE t1 (f1 INT(11), f2 VARCHAR(1), PRIMARY KEY (f1));
INSERT INTO t1 VALUES (1,'f');

CREATE TABLE t2 (f1 INT(11), f2 VARCHAR(1));
INSERT INTO t2 VALUES (2,'m');
INSERT INTO t2 VALUES (3,'m');
INSERT INTO t2 VALUES (11,NULL);
INSERT INTO t2 VALUES (12,'k');

SELECT MAX(t1.f1) field1
FROM t1 JOIN t2 ON t2.f2 LIKE 'x'
HAVING field1 < 7;

DROP TABLE t1,t2;

--echo #
--echo # Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
--echo #

CREATE TABLE t1 (f1 INT, f2 INT);
INSERT INTO t1 VALUES (1, 0), (2, 1), (3, 2);
CREATE TABLE t2 (f1 INT, f2 INT);

SELECT t1.f1
FROM t1
HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
ORDER BY t1.f1;

SELECT t1.f1
FROM t1
HAVING (3, 2) IN (SELECT 4, 2) AND t1.f1  >= 0
ORDER BY t1.f1;

SELECT t1.f1
FROM t1
HAVING 2 IN (SELECT f2 FROM t2) AND t1.f1  >= 0
ORDER BY t1.f1;

DROP TABLE t1,t2;


--echo End of 5.1 tests

--echo #
--echo # Bug#12402590 ASSERT AT PROTOCOL::END_STATEMENT ON MYSQL-TRUNK
--echo #              RUNNING RQG_OPT_SUBQUERY TEST
--echo #

CREATE TABLE t2 (
  f1 INT,
  PRIMARY KEY (f1)
);
INSERT INTO t2 VALUES (1), (2);

CREATE TABLE t1 (
  f1 INT,
  f2 VARCHAR(1),
  f3 VARCHAR(1),
  PRIMARY KEY (f1),
  KEY (f2, f1)
);
INSERT INTO t1 VALUES (8, 'g', 'g'), (11, 'a', 'a');

SELECT t1.f1 FROM t1 JOIN t2 ON t2.f1 = t1.f1
WHERE t1.f3 AND t1.f2 IN ('f')
HAVING (1 ,6) IN (SELECT 3, 6)
ORDER BY t1.f1;

DROP TABLE t1, t2;

--echo #
--echo # Bug#11760517: MIN/MAX FAILS TO EVALUATE HAVING CONDITION, 
--echo #               RETURNS INCORRECT NULL RESULT
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, i4 INT);
INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);

--echo
SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
FROM t1 as table1, t1 as table2
WHERE table1.pk = 1;

--echo
SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
FROM t1 as table1, t1 as table2
WHERE table1.pk = 1
HAVING min_pk <= 10;

--echo
DROP TABLE t1;

--echo End of 5.6 tests

--echo
--echo ONLY_FULL_GROUP_BY now allows using an alias in HAVING
SET @old_sql_mode = @@sql_mode;
SET @@sql_mode='ONLY_FULL_GROUP_BY';
CREATE TABLE t1 (a INT, b INT);
insert into t1 values(1,10),(2,20),(3,30);
select a from t1 having a=3;
select a as x from t1 having x=3;
select avg(a) as x from t1 having x=2;
select a as foo, sum(b) as bar from t1 group by a having foo<10;
select a as foo, sum(b) as bar from t1
 group by a
 having bar>10
 order by foo+10;
select a as foo,
  (select t1_inner.b from t1 as t1_inner where
   t1_inner.a=t1_outer.a+1)
 as bar from t1 as t1_outer
 group by a
 having bar<30
 order by bar+5;
select a as foo,
  (select t1_inner.b from t1 as t1_inner where
   t1_inner.a=t1_outer.a+1)
 as bar from t1 as t1_outer
 group by foo
 having bar<30
 order by bar+5;
DROP TABLE t1;

#
# Bug#19811896 ASSERTION FAILED: CURR_TABLE->TABLE_REF ||
#              !(HAVING_COND->USED_TABLES() & ~(1 |    
#
set sql_mode='';
CREATE TABLE t1 (a INT) ENGINE=INNODB;
SELECT a FROM (SELECT 1 FROM t1 AS From1) AS From2 
  NATURAL RIGHT OUTER JOIN t1 AS Outjoin3 
GROUP BY 1 HAVING (
  SELECT 1 FROM t1 AS Grouphaving4 GROUP BY 1 HAVING a);
SELECT a FROM (SELECT 1 FROM t1 AS From1) AS From2
  NATURAL RIGHT OUTER JOIN t1 AS Outjoin3
GROUP BY 1 HAVING
  sin((SELECT 1 FROM t1 AS Grouphaving4 GROUP BY 1 HAVING a));

CREATE TABLE t2 (a INT) ENGINE=INNODB;
SELECT Outjoin3.a FROM (SELECT 1 FROM t1 AS From1) AS From2 
NATURAL RIGHT OUTER JOIN t2 AS Outjoin3 
GROUP BY 1 HAVING (
SELECT 1 FROM t1 AS GroupHaving4 GROUP BY 1 HAVING a);

DROP TABLE t1, t2;

SET @@sql_mode = @old_sql_mode;

--echo #
--echo # Bug#17055185: WRONG RESULTS WHEN RUNNING A SELECT THAT INCLUDE
--echo #               A HAVING BASED ON A FUNCTION.
--echo #

# Generate series 1, 0, 1, 0....
CREATE TABLE series (
  val INT(10) UNSIGNED NOT NULL
);
INSERT INTO series VALUES(1);
CREATE TABLE seq_calls (
  c INT
);
INSERT INTO seq_calls VALUES(0);

# This function toggles between 0 and 1 at each call.
# As it doesn't depend only on its input parameters (which are none),
# per the SQL std it is not deterministic.

DELIMITER |;
CREATE FUNCTION next_seq_value() RETURNS INT
BEGIN
 DECLARE next_val INT;
 SELECT val INTO next_val FROM series;
 UPDATE series SET val=mod(val + 1, 2);
 UPDATE seq_calls SET c=c+1;
 RETURN next_val;
END;
|
DELIMITER ;|

CREATE TABLE t1 (t INT, u INT, KEY(t));
INSERT INTO t1 VALUES(10, 10), (11, 11), (12, 12), (12, 13),(14, 15), (15, 16),
                     (16, 17), (17, 18);
ANALYZE TABLE t1;
# Print starting point, for debugging
SELECT * FROM series, seq_calls;

# NOTE: This currently gives the wrong result, because HAVING
# and send() both evaluate next_seq_value() instead
# of sharing the result between them. See the comment in
# JOIN::create_access_path_for_join() for more details.
--skip_if_hypergraph  # Depends on the query plan, no guarantees about side effects in HAVING aliases.
SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t)
       GROUP BY t HAVING r = 1  ORDER BY t1.u;

# Statement with select distinct so reference to functions in having will
# result in re-execution of functions.
--skip_if_hypergraph  # Depends on the query plan, no guarantees about side effects in HAVING aliases.
SELECT * FROM series, seq_calls;
UPDATE series set val=0;

# NOTE: This currently gives the wrong result, for the same
# reason as above.
--skip_if_hypergraph  # Depends on the query plan, no guarantees about side effects in HAVING aliases.
SELECT DISTINCT t1.t, next_seq_value() r  FROM t1, t1 P
       WHERE P.t = t1.t HAVING r = 1 ORDER BY t1.u;

# Prepare statement.
PREPARE STMT FROM 'SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t)
              GROUP BY t HAVING (SELECT 1 FROM t1 WHERE r = 1 LIMIT 1)
              ORDER BY t1.u';
--skip_if_hypergraph  # Depends on the query plan, no guarantees about side effects in HAVING aliases.
SELECT * FROM series, seq_calls;
UPDATE series set val=0;

--skip_if_hypergraph  # Depends on the query plan, no guarantees about side effects in HAVING aliases.
EXECUTE stmt;
--skip_if_hypergraph  # Depends on the query plan, no guarantees about side effects in HAVING aliases.
SELECT * FROM series, seq_calls;
UPDATE series set val=1;

--skip_if_hypergraph  # Depends on the query plan, no guarantees about side effects in HAVING aliases.
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

# In Loose-index scan end_send do not switch to items3, so Item_ref re-evaluate
# the functions.
INSERT INTO t1 SELECT * FROM t1;
ALTER TABLE t1 ADD KEY(t, u);
ANALYZE TABLE t1;
--skip_if_hypergraph  # Depends on the query plan, no guarantees about side effects in HAVING aliases.
SELECT * FROM series, seq_calls;
UPDATE series set val=0;
SELECT next_seq_value() r, MIN(u) FROM t1 GROUP BY t HAVING r = 1;

# Cleanup
DROP TABLE t1;
DROP FUNCTION next_seq_value;
DROP TABLE series, seq_calls;


--echo #
--echo #  Bug#21243772: NOT IN SUBQUERY RETURNS INCORRECT RESULTS WHEN INNER
--echo #                QUERY HAS COUNT DISTINCT.
--echo #

CREATE TABLE t1 (t TEXT NOT NULL, u TEXT);
INSERT INTO t1 VALUES('2', '1'), ('3', '1'), ('4', '1');
ANALYZE TABLE t1;
SELECT * FROM t1 WHERE (t, u) in (SELECT a.t,  COUNT(DISTINCT a.u)
                                                       FROM t1 a, t1 b
                                                       GROUP BY a.t);
SELECT * FROM t1 WHERE (t, u) not in (SELECT a.t,  COUNT(DISTINCT a.u)
                                                       FROM t1 a, t1 b
                                                       GROUP BY a.t);
DROP TABLE t1;


--echo #
--echo #  Bug#21507072: SIG 11 IN TABLE_LIST::MAP AT SQL/TABLE.H:2463.
--echo #

CREATE TABLE t1 (col_varchar_key varchar(1) DEFAULT NULL,
                                 KEY col_varchar_key (col_varchar_key));

INSERT INTO t1 VALUES ('a'), ('b'), ('c'), ('h'), ('i'), ('i'), ('j');

SELECT SQL_BUFFER_RESULT col_varchar_key AS field1
  FROM t1 AS table1
  GROUP BY field1
  HAVING field1 = 'a';

DROP TABLE t1;

--echo # Bug#21613422: Assertion failed: !thd->is_error() in ::prepare()

CREATE TABLE a(f INTEGER, g INTEGER) engine=innodb;

--error ER_NON_UNIQ_ERROR
SELECT (SELECT 1 FROM a HAVING g)
FROM (SELECT 1) AS e
     JOIN a AS t on TRUE
     JOIN a USING (f)
     JOIN a AS h on TRUE;

DROP TABLE a;

--echo #
--echo # Bug #22655856: COLUMN NAME IN HAVING, WHICH IS CONTAINED IN SELECT
--echo #                LIST '*', PRODUCES ERROR
--echo #

CREATE TABLE t1(c1 INT) ENGINE=INNODB;
CREATE TABLE t2(c2 INT) ENGINE=INNODB;

EXPLAIN SELECT c1 FROM t1 WHERE EXISTS(SELECT * FROM t2 HAVING c2>0);
SELECT c1 FROM t1 WHERE EXISTS(SELECT * FROM t2 HAVING c2>0);

INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2);

SELECT c1 FROM t1 WHERE EXISTS(SELECT * FROM t2 HAVING c2>0);

DROP TABLE t1, t2;

--echo #
--echo # BUG#26781725 INCORRECT RESULTS FOR QUERY(MAX FUNC+HAVING CLAUSE) WHEN USED INSIDE VIEW
--echo #

CREATE TABLE CC (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_varchar_key varchar(1) NOT NULL,
  col_varchar varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key)
);
INSERT INTO CC VALUES
 (10,'v','v'),(18,'a','a'),(19,'v','v'),(20,'u','u'),(21,'s','s'),(22,'y','y'),
 (23,'z','z'),(28,'y','y');

let $query=
SELECT MAX(col_varchar_key) AS field1 FROM CC AS alias1
 HAVING field1 <> 5;

eval $query;
eval CREATE OR REPLACE VIEW v1 AS $query;

SELECT * FROM v1;

DROP TABLE CC;
DROP VIEW v1;

--echo # Bug#28934388: LIMIT is applied before HAVING when you have a subquery

CREATE TABLE t (id INT PRIMARY KEY, value INT);
INSERT INTO t VALUES (1, 99), (2,98), (3, 97);

CREATE TABLE o(c INTEGER);
INSERT INTO o(c) VALUES(1), (2);

# The query from the bug report
SELECT t1.id, (SELECT t2.value FROM t t2 WHERE t1.id = t2.id) AS sub_value
FROM t t1
HAVING sub_value = 99
ORDER BY value
LIMIT 1;

# The query extended with a random function
SELECT t1.id, (SELECT t2.value FROM t t2 WHERE t1.id = t2.id) AS sub_value
FROM t t1
HAVING sub_value = 99 AND RAND() >= 0.0E0
ORDER BY value
LIMIT 1;

# The query embedded in a subquery, with an outer reference
SELECT (SELECT (SELECT t2.value+o.c FROM t t2 WHERE t1.id = t2.id) AS sub_value
        FROM t t1
        HAVING sub_value = 99
        ORDER BY value
        LIMIT 1) AS sub_value
FROM o;

DROP TABLE t, o;

--echo # Bug#31429865: Assertion `table_share->tmp_table != NO_TMP_TABLE ||
--echo #                m_lock_type == 1' failed.

CREATE TABLE t(a INTEGER);
INSERT INTO t VALUES(1),(2);
CREATE VIEW v AS SELECT a FROM t HAVING 0<>1;
--error ER_NON_UPDATABLE_TABLE
DELETE FROM v LIMIT 1;
DROP VIEW v;
DROP TABLE t;

--echo # Bug#32239484: Item_sum_json::val_json: assertion `!m_wrapper->empty()'

SELECT 1 HAVING COUNT(*) = 1;
SELECT 1 WHERE TRUE HAVING COUNT(*) = 1;
SELECT 1 WHERE FALSE HAVING COUNT(*) = 0;
SELECT 1 HAVING json_objectagg(utc_date(), 1416) <> 0;

--echo #
--echo # Bug #33152269: No result set returned when using HAVING COUNT(DISTINCT ...)
--echo #

CREATE TABLE t1 (a INTEGER);
INSERT INTO t1 VALUES (1), (2);

SELECT DISTINCT
  COUNT(DISTINCT t1.a) AS da
FROM
  t1
  JOIN t1 AS t2 ON t1.a = t2.a
GROUP BY t1.a
HAVING COUNT(DISTINCT t1.a) = 1;

DROP TABLE t1;

--echo #
--echo # Bug #30617496: BAD ITEM REF FROM CORRELATED SUBQUERY TO OUTER DISTINCT TABLE
--echo #

CREATE TABLE t1 (f1 INT, f2 TEXT, f3 INT, PRIMARY KEY(f1));
CREATE TABLE t2 LIKE t1;

--error ER_BAD_FIELD_ERROR
SELECT DISTINCT f3 FROM t1 HAVING (SELECT 1 FROM t2 HAVING f2 LIMIT 1) ORDER BY f3;

DROP TABLE t1;
DROP TABLE t2;

--echo # Bug#34923792: Mysqld assert - JOIN::add_having_as_tmp_table_cond

CREATE TABLE t1 (f1 INTEGER);

SELECT SUM(t1.f1)+1 AS field1
FROM t1 JOIN t1 AS t2
GROUP BY t1.f1
HAVING field1 < 7
ORDER BY field1;

DROP TABLE t1;