← Back to branch summary

~ubuntu-branches/ubuntu/saucy/drizzle/saucy-proposed

« back to all changes in this revision

Viewing changes to tests/t/having.test

  • Committer: Bazaar Package Importer
  • Author(s): Monty Taylor
  • Date: 2010-03-18 12:12:31 UTC
  • Revision ID: james.westby@ubuntu.com-20100318121231-k6g1xe6cshbwa0f8
Tags: upstream-2010.03.1347
Import upstream version 2010.03.1347

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/having.test
 
1
# test of problems with having (Reported by Mark Rogers)
 
2
#
 
3
 
 
4
--disable_warnings
 
5
drop table if exists t1,t2,t3;
 
6
--enable_warnings
 
7
 
 
8
create table t1 (a int);
 
9
select count(a) as b from t1 where a=0 having b > 0;
 
10
insert into t1 values (null);
 
11
select count(a) as b from t1 where a=0 having b > 0;
 
12
select count(a) as b from t1 where a=0 having b >=0;
 
13
explain extended select count(a) as b from t1 where a=0 having b >=0;
 
14
drop table t1; 
 
15
 
 
16
#
 
17
# Test of problem with HAVING and AVG()
 
18
#
 
19
 
 
20
CREATE TABLE t1 (
 
21
  raw_id int NOT NULL default '0',
 
22
  chr_start int NOT NULL default '0',
 
23
  chr_end int NOT NULL default '0',
 
24
  raw_start int NOT NULL default '0',
 
25
  raw_end int NOT NULL default '0',
 
26
  raw_ori int NOT NULL default '0'
 
27
);
 
28
 
 
29
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);
 
30
 
 
31
CREATE TABLE t2 (
 
32
  id int NOT NULL default '0',
 
33
  contig_id int NOT NULL default '0',
 
34
  seq_start int NOT NULL default '0',
 
35
  seq_end int NOT NULL default '0',
 
36
  strand int NOT NULL default '0',
 
37
  KEY id (id)
 
38
);
 
39
INSERT INTO t2 VALUES (133195,469713,61327,61384,1),(133196,469713,64113,64387,1),(133197,1,1,1,0),(133197,1,1,1,-2);
 
40
SELECT e.id,
 
41
   MIN( IF(sgp.raw_ori=1,
 
42
          (e.seq_start+sgp.chr_start-sgp.raw_start),  
 
43
          (sgp.chr_start+sgp.raw_end-e.seq_end))) as start, 
 
44
   MAX( IF(sgp.raw_ori=1,
 
45
           (e.seq_end+sgp.chr_start-sgp.raw_start),  
 
46
           (sgp.chr_start+sgp.raw_end-e.seq_start))) as end, 
 
47
   AVG(IF (sgp.raw_ori=1,e.strand,(-e.strand))) as chr_strand 
 
48
FROM  t1 sgp,
 
49
      t2 e  
 
50
WHERE sgp.raw_id=e.contig_id 
 
51
GROUP BY e.id 
 
52
HAVING chr_strand= -1 and end >= 0 
 
53
  AND start <= 999660;
 
54
drop table t1,t2;
 
55
 
 
56
#
 
57
# Test problem with having and MAX() IS NOT NULL
 
58
#
 
59
 
 
60
CREATE TABLE t1 (Fld1 int default NULL,Fld2 int default NULL);
 
61
INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50);
 
62
select Fld1, max(Fld2) as q from t1 group by Fld1 having q is not null;
 
63
select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null;
 
64
select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
 
65
select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null;
 
66
select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
 
67
drop table t1;
 
68
 
 
69
#
 
70
# Test problem with count(distinct) in having
 
71
#
 
72
create table t1 (id int not null, qty int not null);
 
73
insert into t1 values (1,2),(1,3),(2,4),(2,5);
 
74
select id, sum(qty) as sqty from t1 group by id having sqty>2;
 
75
select sum(qty) as sqty from t1 group by id having count(id) > 0;
 
76
select sum(qty) as sqty from t1 group by id having count(distinct id) > 0;
 
77
drop table t1;
 
78
 
 
79
#
 
80
# Test case for Bug #4358 Problem with HAVING clause that uses alias from the
 
81
#              select list and TEXT field 
 
82
#
 
83
 
 
84
CREATE TEMPORARY TABLE t1 (
 
85
  `id` bigint NOT NULL default '0',
 
86
  `description` text
 
87
) ENGINE=MyISAM;
 
88
 
 
89
CREATE TEMPORARY TABLE t2 (
 
90
  `id` bigint NOT NULL default '0',
 
91
  `description` varchar(20)
 
92
) ENGINE=MyISAM;
 
93
 
 
94
INSERT INTO t1  VALUES (1, 'test');
 
95
INSERT INTO t2 VALUES (1, 'test');
 
96
 
 
97
CREATE TEMPORARY TABLE t3 (
 
98
  `id`       bigint NOT NULL default '0',
 
99
  `order_id` bigint NOT NULL default '0'
 
100
) ENGINE=MyISAM;
 
101
 
 
102
select
 
103
        a.id, a.description,
 
104
        count(b.id) as c 
 
105
from t1 a left join t3 b on a.id=b.order_id 
 
106
group by a.id, a.description 
 
107
having (a.description is not null) and (c=0);
 
108
 
 
109
select
 
110
        a.*, 
 
111
        count(b.id) as c 
 
112
from t2 a left join t3 b on a.id=b.order_id 
 
113
group by a.id, a.description
 
114
having (a.description is not null) and (c=0);
 
115
 
 
116
INSERT INTO t1  VALUES (2, 'test2');
 
117
 
 
118
select
 
119
        a.id, a.description,
 
120
        count(b.id) as c 
 
121
from t1 a left join t3 b on a.id=b.order_id 
 
122
group by a.id, a.description 
 
123
having (a.description is not null) and (c=0);
 
124
drop table t1,t2,t3;
 
125
 
 
126
#
 
127
# Bug #14274: HAVING clause containing only set function
 
128
#
 
129
 
 
130
CREATE TABLE t1 (a int);
 
131
INSERT INTO t1 VALUES (3), (4), (1), (3), (1);
 
132
 
 
133
SELECT SUM(a) FROM t1 GROUP BY a HAVING SUM(a)>0;
 
134
SELECT SUM(a) FROM t1 GROUP BY a HAVING SUM(a);
 
135
 
 
136
DROP TABLE t1;
 
137
 
 
138
#
 
139
# Bug #14927: HAVING clause containing constant false conjunct
 
140
#
 
141
 
 
142
CREATE TABLE t1 (a int);
 
143
INSERT INTO t1 VALUES (1), (2), (1), (3), (2), (1);
 
144
 
 
145
SELECT a FROM t1 GROUP BY a HAVING a > 1;
 
146
SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1;
 
147
SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
 
148
 
 
149
EXPLAIN SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1;
 
150
EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
 
151
 
 
152
DROP table t1;  
 
153
 
 
154
#
 
155
# Bug #29911: HAVING clause depending on constant table and evaluated to false
 
156
#
 
157
 
 
158
CREATE TABLE t1 (a int PRIMARY KEY);
 
159
CREATE TABLE t2 (b int PRIMARY KEY, a int);
 
160
CREATE TABLE t3 (b int, flag int);
 
161
 
 
162
INSERT INTO t1 VALUES (1);
 
163
INSERT INTO t2 VALUES (1,1), (2,1), (3,1);
 
164
INSERT INTO t3(b,flag) VALUES (2, 1);
 
165
 
 
166
SELECT t1.a
 
167
  FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
 
168
    GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
 
169
 
 
170
SELECT DISTINCT t1.a, MAX(t3.flag)
 
171
  FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
 
172
    GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
 
173
 
 
174
SELECT DISTINCT t1.a
 
175
  FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
 
176
    GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
 
177
 
 
178
DROP TABLE t1,t2,t3;
 
179
 
 
180
# End of 4.1 tests
 
181
 
 
182
#
 
183
# Tests for WL#1972 CORRECT EVALUATION OF COLUMN REFERENCES IN THE HAVING CLAUSE
 
184
# Per the SAP VERI tests and WL#1972, MySQL must ensure that HAVING can
 
185
# correctly evaluate column references from the GROUP BY clause, even if the
 
186
# same references are not also found in the select list.
 
187
#
 
188
 
 
189
# set global sql_mode='ansi';
 
190
# set session sql_mode='ansi';
 
191
 
 
192
create table t1 (col1 int, col2 varchar(5), col_t1 int);
 
193
create table t2 (col1 int, col2 varchar(5), col_t2 int);
 
194
create table t3 (col1 int, col2 varchar(5), col_t3 int);
 
195
 
 
196
insert into t1 values(10,'hello',10);
 
197
insert into t1 values(20,'hello',20);
 
198
insert into t1 values(30,'hello',30);
 
199
insert into t1 values(10,'bye',10);
 
200
insert into t1 values(10,'sam',10);
 
201
insert into t1 values(10,'bob',10);
 
202
 
 
203
insert into t2 select * from t1;
 
204
insert into t3 select * from t1;
 
205
 
 
206
select count(*) from t1 group by col1 having col1 = 10;
 
207
select count(*) as count_col1 from t1 group by col1 having col1 = 10;
 
208
select count(*) as count_col1 from t1 as tmp1 group by col1 having col1 = 10;
 
209
select count(*) from t1 group by col2 having col2 = 'hello';
 
210
--error 1054
 
211
select count(*) from t1 group by col2 having col1 = 10;
 
212
select col1 as count_col1 from t1 as tmp1 group by col1 having col1 = 10;
 
213
select col1 as count_col1 from t1 as tmp1 group by col1 having count_col1 = 10;
 
214
select col1 as count_col1 from t1 as tmp1 group by count_col1 having col1 = 10;
 
215
# ANSI: should return SQLSTATE 42000 Syntax error or access violation 
 
216
# MySQL: returns 10 - because of GROUP BY name resolution
 
217
select col1 as count_col1 from t1 as tmp1 group by count_col1 having count_col1 = 10;
 
218
# ANSI: should return SQLSTATE 42000 Syntax error or access violation 
 
219
# MySQL: returns 10 - because of GROUP BY name resolution
 
220
select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col1 = 10;
 
221
select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having count_col1 = 10;
 
222
select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col2 = 'hello';
 
223
select col1 as count_col1,col2 as group_col2 from t1 as tmp1 group by col1,col2 having group_col2 = 'hello';
 
224
--error 1064
 
225
select sum(col1) as co12 from t1 group by col2 having col2 10; 
 
226
select sum(col1) as co2, count(col2) as cc from t1 group by col1 having col1 =10; 
 
227
--error 1054
 
228
select t2.col2 from t2 group by t2.col1, t2.col2 having t1.col1 <= 10;
 
229
 
 
230
 
 
231
#
 
232
# queries with nested sub-queries
 
233
#
 
234
 
 
235
# the having column is resolved in the same query
 
236
select t1.col1 from t1
 
237
where t1.col2 in 
 
238
      (select t2.col2 from t2 
 
239
       group by t2.col1, t2.col2 having t2.col1 <= 10);
 
240
 
 
241
select t1.col1 from t1
 
242
where t1.col2 in 
 
243
      (select t2.col2 from t2
 
244
       group by t2.col1, t2.col2
 
245
       having t2.col1 <=
 
246
              (select min(t3.col1) from t3));
 
247
 
 
248
# the having column is resolved in the SELECT clause of the outer query -
 
249
# works in ANSI
 
250
select t1.col1 from t1
 
251
where t1.col2 in
 
252
      (select t2.col2 from t2 
 
253
       group by t2.col1, t2.col2 having t1.col1 <= 10);
 
254
 
 
255
# the having column is resolved in the SELECT clause of the outer query -
 
256
# error in ANSI, works with MySQL extension
 
257
select t1.col1 as tmp_col from t1
 
258
where t1.col2 in 
 
259
      (select t2.col2 from t2 
 
260
       group by t2.col1, t2.col2 having tmp_col <= 10);
 
261
 
 
262
# the having column is resolved in the FROM clause of the outer query -
 
263
# works in ANSI
 
264
select t1.col1 from t1
 
265
where t1.col2 in 
 
266
      (select t2.col2 from t2 
 
267
       group by t2.col1, t2.col2 having col_t1 <= 10);
 
268
 
 
269
# Item_field must be resolved in the same way as Item_ref
 
270
select sum(col1) from t1
 
271
group by col_t1
 
272
having (select col_t1 from t2 where col_t1 = col_t2 order by col_t2 limit 1);
 
273
 
 
274
# nested queries with HAVING, inner having column resolved in outer FROM clause
 
275
# the outer having column is not referenced in GROUP BY which results in an error
 
276
--error 1054
 
277
select t1.col1 from t1
 
278
where t1.col2 in 
 
279
      (select t2.col2 from t2 
 
280
       group by t2.col1, t2.col2 having col_t1 <= 10)
 
281
having col_t1 <= 20;
 
282
 
 
283
# both having columns are resolved in the GROUP clause of the outer query
 
284
select t1.col1 from t1
 
285
where t1.col2 in 
 
286
      (select t2.col2 from t2 
 
287
       group by t2.col1, t2.col2 having col_t1 <= 10)
 
288
group by col_t1
 
289
having col_t1 <= 20;
 
290
 
 
291
#
 
292
# nested HAVING clauses
 
293
#
 
294
 
 
295
# non-correlated subqueries
 
296
select col_t1, sum(col1) from t1
 
297
group by col_t1
 
298
having col_t1 > 10 and
 
299
       exists (select sum(t2.col1) from t2
 
300
               group by t2.col2 having t2.col2 > 'b');
 
301
 
 
302
# correlated subqueries - inner having column 't1.col2' resolves to
 
303
# the outer FROM clause, which cannot be used because the outer query
 
304
# is grouped
 
305
--error 1054
 
306
select sum(col1) from t1
 
307
group by col_t1
 
308
having col_t1 in (select sum(t2.col1) from t2
 
309
                  group by t2.col2, t2.col1 having t2.col1 = t1.col1);
 
310
 
 
311
# correlated subqueries - inner having column 'col_t1' resolves to
 
312
# the outer GROUP clause
 
313
select sum(col1) from t1
 
314
group by col_t1
 
315
having col_t1 in (select sum(t2.col1) from t2
 
316
                  group by t2.col2, t2.col1 having t2.col1 = col_t1);
 
317
 
 
318
#
 
319
# queries with joins and ambiguous column names
 
320
#
 
321
--error 1052
 
322
select t1.col1, t2.col1 from t1, t2 where t1.col1 = t2.col1
 
323
group by t1.col1, t2.col1 having col1 = 2;
 
324
 
 
325
--error 1052
 
326
select t1.col1*10+t2.col1 from t1,t2 where t1.col1=t2.col1
 
327
group by t1.col1, t2.col1 having col1 = 2;
 
328
 
 
329
drop table t1, t2, t3;
 
330
 
 
331
# More queries to test ANSI compatibility
 
332
create table t1 (s1 int);
 
333
insert into t1 values (1),(2),(3);
 
334
 
 
335
select count(*) from t1 group by s1 having s1 is null;
 
336
 
 
337
# prepared statements prints warnings too early
 
338
--disable_ps_protocol
 
339
select s1*0 as s1 from t1 group by s1 having s1 <> 0;
 
340
--enable_ps_protocol
 
341
 
 
342
# ANSI requires: 3 rows
 
343
# MySQL returns: 0 rows - because of GROUP BY name resolution
 
344
 
 
345
select s1*0 from t1 group by s1 having s1 = 0;
 
346
 
 
347
select s1 from t1 group by 1 having 1 = 0;
 
348
 
 
349
select count(s1) from t1 group by s1 having count(1+1)=2;
 
350
# ANSI requires: 3 rows
 
351
# MySQL returns: 0 rows - because of GROUP BY name resolution
 
352
 
 
353
select count(s1) from t1 group by s1 having s1*0=0;
 
354
 
 
355
-- error 1052
 
356
select * from t1 a, t1 b group by a.s1 having s1 is null;
 
357
# ANSI requires: 0 rows
 
358
# MySQL returns:
 
359
# "ERROR 1052 (23000): Column 's1' in having clause is ambiguous"
 
360
# I think the column is ambiguous in ANSI too.
 
361
# It is the same as:
 
362
#   select a.s1, b.s1 from t1 a, t1 b group by a.s1 having s1 is null;
 
363
# currently we first check SELECT, thus s1 is ambiguous.
 
364
 
 
365
drop table t1;
 
366
 
 
367
create table t1 (s1 char);
 
368
insert into t1 values ('b'),('y');
 
369
 
 
370
select s1,count(s1) from t1 group by s1 having s1 = 'y';
 
371
# ANSI requires: 1 row, with count(s1) = 2
 
372
# MySQL returns: 1 row, with count(s1) = 1
 
373
 
 
374
drop table t1;
 
375
 
 
376
 
 
377
#
 
378
# Bug #15917: unexpected complain for a name in having clause
 
379
# when the server is run on Windows or with --lower-case-table-names=1
 
380
#
 
381
 
 
382
--disable_warnings
 
383
DROP SCHEMA IF EXISTS HU;
 
384
--enable_warnings
 
385
CREATE SCHEMA HU ;
 
386
USE HU ;
 
387
 
 
388
CREATE TABLE STAFF
 
389
 (EMPNUM   CHAR(3) NOT NULL UNIQUE,
 
390
  EMPNAME  CHAR(20),
 
391
  GRADE    DECIMAL(4),
 
392
  CITY     CHAR(15));
 
393
 
 
394
CREATE TABLE PROJ
 
395
 (PNUM     CHAR(3) NOT NULL UNIQUE,
 
396
  PNAME    CHAR(20),
 
397
  PTYPE    CHAR(6),
 
398
  BUDGET   DECIMAL(9),
 
399
  CITY     CHAR(15));
 
400
 
 
401
INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');
 
402
INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');
 
403
INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');
 
404
INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');
 
405
INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');
 
406
 
 
407
INSERT INTO PROJ VALUES  ('P1','MXSS','Design',10000,'Deale');
 
408
INSERT INTO PROJ VALUES  ('P2','CALM','Code',30000,'Vienna');
 
409
INSERT INTO PROJ VALUES  ('P3','SDP','Test',30000,'Tampa');
 
410
INSERT INTO PROJ VALUES  ('P4','SDP','Design',20000,'Deale');
 
411
INSERT INTO PROJ VALUES  ('P5','IRM','Test',10000,'Vienna');
 
412
INSERT INTO PROJ VALUES  ('P6','PAYR','Design',50000,'Deale');
 
413
 
 
414
SELECT EMPNUM, GRADE*1000
 
415
  FROM HU.STAFF WHERE GRADE * 1000 > 
 
416
                  ANY (SELECT SUM(BUDGET) FROM HU.PROJ
 
417
                         GROUP BY CITY, PTYPE
 
418
                           HAVING HU.PROJ.CITY = HU.STAFF.CITY);
 
419
 
 
420
DROP SCHEMA HU;
 
421
USE test;
 
422
#
 
423
# Bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode.
 
424
#
 
425
create table t1(f1 int);
 
426
select f1 from t1 having max(f1)=f1;
 
427
select f1 from t1 group by f1 having max(f1)=f1;
 
428
select f1 from t1 having max(f1)=f1;
 
429
select f1 from t1 group by f1 having max(f1)=f1;
 
430
drop table t1;