~drizzle-trunk/drizzle/development

# 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;

explain extended 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

) ENGINE=MyISAM;

CREATE TABLE t2 (

  `id` bigint(20) NOT NULL default '0',

  `description` varchar(20)

) ENGINE=MyISAM;

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'

) ENGINE=MyISAM;

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);

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

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;

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;

select col1 as count_col1 from t1 as tmp1 group by col1 having col1 = 10;

select col1 as count_col1 from t1 as tmp1 group by col1 having count_col1 = 10;

select col1 as count_col1 from t1 as tmp1 group by count_col1 having col1 = 10;

# ANSI: should return SQLSTATE 42000 Syntax error or access violation 

# MySQL: returns 10 - because of GROUP BY name resolution

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

select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col1 = 10;

select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having count_col1 = 10;

select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col2 = 'hello';

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; 

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 SELECT clause of the outer query -

# error in ANSI, works with MySQL extension

select t1.col1 as tmp_col from t1

where t1.col2 in 

      (select t2.col2 from t2 

       group by t2.col1, t2.col2 having tmp_col <= 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

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;

#

# 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;

create table t1 (s1 char character set latin1 collate latin1_german1_ci);

insert into t1 values ('b'),('y');

select s1,count(s1) from t1

group by s1 collate latin1_swedish_ci having s1 = 'y';

# ANSI requires: 1 row, with count(s1) = 2

# MySQL returns: 1 row, with count(s1) = 1

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);

select f1 from t1 having max(f1)=f1;

select f1 from t1 group by f1 having max(f1)=f1;

select f1 from t1 having max(f1)=f1;

select f1 from t1 group by f1 having max(f1)=f1;

drop table t1;