← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/having.test

  • Committer: Brian Aker
  • Date: 2008-08-11 04:55:59 UTC
  • Revision ID: brian@tangent.org-20080811045559-azgfc343y0igyzsz
ulong cleanup, remove log code from myisam.

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 TABLE t1 (
85
 
  `id` bigint NOT NULL default '0',
86
 
  `description` text
87
 
) ENGINE=MyISAM;
88
 
 
89
 
CREATE 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 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
 
select * from t1 a, t1 b group by a.s1 having s1 is null;
356
 
# ANSI requires: 0 rows
357
 
# MySQL returns:
358
 
# "ERROR 1052 (23000): Column 's1' in having clause is ambiguous"
359
 
# I think the column is ambiguous in ANSI too.
360
 
# It is the same as:
361
 
#   select a.s1, b.s1 from t1 a, t1 b group by a.s1 having s1 is null;
362
 
# currently we first check SELECT, thus s1 is ambiguous.
363
 
 
364
 
drop table t1;
365
 
 
366
 
create table t1 (s1 char);
367
 
insert into t1 values ('b'),('y');
368
 
 
369
 
select s1,count(s1) from t1 group by s1 having s1 = 'y';
370
 
# ANSI requires: 1 row, with count(s1) = 2
371
 
# MySQL returns: 1 row, with count(s1) = 1
372
 
 
373
 
drop table t1;
374
 
 
375
 
 
376
 
#
377
 
# Bug #15917: unexpected complain for a name in having clause
378
 
# when the server is run on Windows or with --lower-case-table-names=1
379
 
#
380
 
 
381
 
--disable_warnings
382
 
DROP SCHEMA IF EXISTS HU;
383
 
--enable_warnings
384
 
CREATE SCHEMA HU ;
385
 
USE HU ;
386
 
 
387
 
CREATE TABLE STAFF
388
 
 (EMPNUM   CHAR(3) NOT NULL UNIQUE,
389
 
  EMPNAME  CHAR(20),
390
 
  GRADE    DECIMAL(4),
391
 
  CITY     CHAR(15));
392
 
 
393
 
CREATE TABLE PROJ
394
 
 (PNUM     CHAR(3) NOT NULL UNIQUE,
395
 
  PNAME    CHAR(20),
396
 
  PTYPE    CHAR(6),
397
 
  BUDGET   DECIMAL(9),
398
 
  CITY     CHAR(15));
399
 
 
400
 
INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');
401
 
INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');
402
 
INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');
403
 
INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');
404
 
INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');
405
 
 
406
 
INSERT INTO PROJ VALUES  ('P1','MXSS','Design',10000,'Deale');
407
 
INSERT INTO PROJ VALUES  ('P2','CALM','Code',30000,'Vienna');
408
 
INSERT INTO PROJ VALUES  ('P3','SDP','Test',30000,'Tampa');
409
 
INSERT INTO PROJ VALUES  ('P4','SDP','Design',20000,'Deale');
410
 
INSERT INTO PROJ VALUES  ('P5','IRM','Test',10000,'Vienna');
411
 
INSERT INTO PROJ VALUES  ('P6','PAYR','Design',50000,'Deale');
412
 
 
413
 
SELECT EMPNUM, GRADE*1000
414
 
  FROM HU.STAFF WHERE GRADE * 1000 > 
415
 
                  ANY (SELECT SUM(BUDGET) FROM HU.PROJ
416
 
                         GROUP BY CITY, PTYPE
417
 
                           HAVING HU.PROJ.CITY = HU.STAFF.CITY);
418
 
 
419
 
DROP SCHEMA HU;
420
 
USE test;
421
 
#
422
 
# Bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode.
423
 
#
424
 
create table t1(f1 int);
425
 
select f1 from t1 having max(f1)=f1;
426
 
select f1 from t1 group by f1 having max(f1)=f1;
427
 
select f1 from t1 having max(f1)=f1;
428
 
select f1 from t1 group by f1 having max(f1)=f1;
429
 
drop table t1;