← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/func_group.test

  • Committer: Monty Taylor
  • Date: 2008-08-02 00:06:32 UTC
  • mto: (236.1.42 codestyle)
  • mto: This revision was merged to the branch mainline in revision 261.
  • Revision ID: monty@inaugust.com-20080802000632-jsse0zdd9r6ic5ku
Actually turn gettext on...

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/func_group.test
1
 
#
2
 
# simple test of all group functions
3
 
#
4
 
 
5
 
--disable_warnings
6
 
drop table if exists t1,t2;
7
 
--enable_warnings
8
 
 
9
 
set @sav_dpi= @@div_precision_increment;
10
 
set div_precision_increment= 5;
11
 
show variables like 'div_precision_increment';
12
 
create table t1 (grp int, a bigint, c char(10) not null);
13
 
insert into t1 values (1,1,"a");
14
 
insert into t1 values (2,2,"b");
15
 
insert into t1 values (2,3,"c");
16
 
insert into t1 values (3,4,"E");
17
 
insert into t1 values (3,5,"C");
18
 
insert into t1 values (3,6,"D");
19
 
 
20
 
# Test of MySQL field extension with and without matching records.
21
 
select a,c,sum(a) from t1 group by a;
22
 
select a,c,sum(a) from t1 where a > 10 group by a;
23
 
select sum(a) from t1 where a > 10;
24
 
select a from t1 order by rand(10);
25
 
select distinct a from t1 order by rand(10);
26
 
select count(distinct a),count(distinct grp) from t1;
27
 
insert into t1 values (null,null,'');
28
 
select count(distinct a),count(distinct grp) from t1;
29
 
 
30
 
create table t2 (grp int, a bigint, c char(10));
31
 
insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp;
32
 
 
33
 
# REPLACE ... SELECT doesn't yet work with PS
34
 
replace into t2 select grp, a, c from t1 limit 2,1;
35
 
select * from t2;
36
 
 
37
 
drop table t1,t2;
38
 
 
39
 
#
40
 
# Problem with std()
41
 
#
42
 
 
43
 
CREATE TABLE t1 (id int,value1 float(10,2));
44
 
INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00); 
45
 
CREATE TABLE t2 (id int,name char(20)); 
46
 
INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two'); 
47
 
select id, avg(value1), std(value1), variance(value1) from t1 group by id;
48
 
select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id;
49
 
drop table t1,t2;
50
 
 
51
 
#
52
 
# Test of bug in left join & avg
53
 
#
54
 
 
55
 
create table t1 (id int not null);
56
 
create table t2 (id int not null,rating int null);
57
 
insert into t1 values(1),(2),(3);
58
 
insert into t2 values(1, 3),(2, NULL),(2, NULL),(3, 2),(3, NULL);
59
 
select t1.id, avg(rating) from t1 left join t2 on ( t1.id = t2.id ) group by t1.id;
60
 
# Test different types with avg()
61
 
select sql_small_result t2.id, avg(rating) from t2 group by t2.id;
62
 
select sql_big_result t2.id, avg(rating) from t2 group by t2.id;
63
 
select sql_small_result t2.id, avg(rating+0.0e0) from t2 group by t2.id;
64
 
select sql_big_result t2.id, avg(rating+0.0e0) from t2 group by t2.id;
65
 
drop table t1,t2;
66
 
 
67
 
#
68
 
# test of count
69
 
#
70
 
create table t1 (a int primary key, c char(10), b text);
71
 
INSERT INTO t1 VALUES (1,'1','1');
72
 
INSERT INTO t1 VALUES (2,'2','2');
73
 
INSERT INTO t1 VALUES (4,'4','4');
74
 
 
75
 
select count(*) from t1;
76
 
select count(*) from t1 where a = 1;
77
 
select count(*) from t1 where a = 100;
78
 
select count(*) from t1 where a >= 10;
79
 
select count(a) from t1 where a = 1;
80
 
select count(a) from t1 where a = 100;
81
 
select count(a) from t1 where a >= 10;
82
 
select count(b) from t1 where b >= 2;
83
 
select count(b) from t1 where b >= 10;
84
 
select count(c) from t1 where c = 10;
85
 
drop table t1;
86
 
 
87
 
#
88
 
# Test of bug in COUNT(i)*(i+0)
89
 
#
90
 
 
91
 
CREATE TABLE t1 (d DATETIME, i INT);
92
 
INSERT INTO t1 VALUES (NOW(), 1);
93
 
SELECT COUNT(i), i, COUNT(i)*i FROM t1 GROUP BY i;
94
 
SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i; 
95
 
DROP TABLE t1;
96
 
 
97
 
#
98
 
# Another SUM() problem with 3.23.2
99
 
#
100
 
 
101
 
create table t1 (
102
 
        num float(5,2),
103
 
        user char(20)
104
 
);
105
 
insert into t1 values (10.3,'nem'),(20.53,'monty'),(30.23,'sinisa');
106
 
insert into t1 values (30.13,'nem'),(20.98,'monty'),(10.45,'sinisa');
107
 
insert into t1 values (5.2,'nem'),(8.64,'monty'),(11.12,'sinisa');
108
 
select sum(num) from t1;
109
 
select sum(num) from t1 group by user;
110
 
drop table t1;
111
 
 
112
 
#
113
 
# Test problem with MIN() optimization in case of null values
114
 
#
115
 
 
116
 
create table t1 (a1 int, a2 char(3), key k1(a1), key k2(a2));
117
 
insert into t1 values(10,'aaa'), (10,null), (10,'bbb'), (20,'zzz');
118
 
create table t2(a1 char(3), a2 int, a3 real, key k1(a1), key k2(a2, a1));
119
 
select * from t1;
120
 
# The following returned NULL in 4.0.10
121
 
select min(a2) from t1;
122
 
select max(t1.a1), max(t2.a2) from t1, t2;
123
 
select max(t1.a1) from t1, t2;
124
 
select max(t2.a2), max(t1.a1) from t1, t2;
125
 
 
126
 
explain select min(a2) from t1;
127
 
explain select max(t1.a1), max(t2.a2) from t1, t2;
128
 
 
129
 
insert into t2 values('AAA', 10, 0.5);
130
 
insert into t2 values('BBB', 20, 1.0);
131
 
--sorted_result
132
 
select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;
133
 
 
134
 
select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
135
 
select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9;
136
 
select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
137
 
select max(t1.a2) from t1 left outer join t2 on t1.a1=10;
138
 
select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20;
139
 
select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10;
140
 
select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA';
141
 
select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10;
142
 
drop table t1,t2;
143
 
 
144
 
#
145
 
# Bug #3376: avg() and an empty table
146
 
#
147
 
 
148
 
create table t1 (a int);
149
 
select avg(2) from t1;
150
 
drop table t1;
151
 
 
152
 
#
153
 
# Tests to check MIN/MAX query optimization
154
 
#
155
 
 
156
 
# Create database schema
157
 
create table t1(
158
 
  a1 char(3) primary key,
159
 
  a2 int,
160
 
  a3 char(3),
161
 
  a4 real,
162
 
  a5 date,
163
 
  key k1(a2,a3),
164
 
  key k2(a4 desc,a1),
165
 
  key k3(a5,a1)
166
 
);
167
 
create table t2(
168
 
  a1 char(3) primary key,
169
 
  a2 char(17),
170
 
  a3 char(2),
171
 
  a4 char(3),
172
 
  key k1(a3, a2),
173
 
  key k2(a4)
174
 
);
175
 
 
176
 
# Populate table t1
177
 
insert into t1 values('AME',0,'SEA',0.100,date'1942-02-19');
178
 
insert into t1 values('HBR',1,'SEA',0.085,date'1948-03-05');
179
 
insert into t1 values('BOT',2,'SEA',0.085,date'1951-11-29');
180
 
insert into t1 values('BMC',3,'SEA',0.085,date'1958-09-08');
181
 
insert into t1 values('TWU',0,'LAX',0.080,date'1969-10-05');
182
 
insert into t1 values('BDL',0,'DEN',0.080,date'1960-11-27');
183
 
insert into t1 values('DTX',1,'NYC',0.080,date'1961-05-04');
184
 
insert into t1 values('PLS',1,'WDC',0.075,date'1949-01-02');
185
 
insert into t1 values('ZAJ',2,'CHI',0.075,date'1960-06-15');
186
 
insert into t1 values('VVV',2,'MIN',0.075,date'1959-06-28');
187
 
insert into t1 values('GTM',3,'DAL',0.070,date'1977-09-23');
188
 
insert into t1 values('SSJ',null,'CHI',null,date'1974-03-19');
189
 
insert into t1 values('KKK',3,'ATL',null,null);
190
 
insert into t1 values('XXX',null,'MIN',null,null);
191
 
insert into t1 values('WWW',1,'LED',null,null);
192
 
 
193
 
# Populate table t2
194
 
insert into t2 values('TKF','Seattle','WA','AME');
195
 
insert into t2 values('LCC','Los Angeles','CA','TWU');
196
 
insert into t2 values('DEN','Denver','CO','BDL');
197
 
insert into t2 values('SDC','San Diego','CA','TWU');
198
 
insert into t2 values('NOL','New Orleans','LA','GTM');
199
 
insert into t2 values('LAK','Los Angeles','CA','TWU');
200
 
insert into t2 values('AAA','AAA','AA','AME');
201
 
 
202
 
# Show the table contents
203
 
--sorted_result
204
 
select * from t1;
205
 
--sorted_result
206
 
select * from t2;
207
 
 
208
 
# Queries with min/max functions 
209
 
# which regular min/max optimization are applied to
210
 
 
211
 
explain 
212
 
select min(a1) from t1;
213
 
select min(a1) from t1;
214
 
explain 
215
 
select max(a4) from t1;
216
 
select max(a4) from t1;
217
 
explain 
218
 
select min(a5), max(a5) from t1;
219
 
select min(a5), max(a5) from t1;
220
 
explain 
221
 
select min(a3) from t1 where a2 = 2;
222
 
select min(a3) from t1 where a2 = 2;
223
 
explain 
224
 
select min(a1), max(a1) from t1 where a4 = 0.080;
225
 
select min(a1), max(a1) from t1 where a4 = 0.080;
226
 
 
227
 
explain 
228
 
select min(t1.a5), max(t2.a3) from t1, t2;
229
 
select min(t1.a5), max(t2.a3) from t1, t2;
230
 
explain 
231
 
select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA';
232
 
select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA';
233
 
 
234
 
# Queries with min/max functions 
235
 
# which extended min/max optimization are applied to
236
 
 
237
 
explain 
238
 
select min(a1) from t1 where a1 > 'KKK';
239
 
select min(a1) from t1 where a1 > 'KKK';
240
 
explain 
241
 
select min(a1) from t1 where a1 >= 'KKK';
242
 
select min(a1) from t1 where a1 >= 'KKK';
243
 
explain 
244
 
select max(a3) from t1 where a2 = 2 and a3 < 'SEA';
245
 
select max(a3) from t1 where a2 = 2 and a3 < 'SEA';
246
 
explain 
247
 
select max(a5) from t1 where a5 < date'1970-01-01';
248
 
select max(a5) from t1 where a5 < date'1970-01-01';
249
 
explain 
250
 
select max(a3) from t1 where a2 is null;
251
 
select max(a3) from t1 where a2 is null;
252
 
explain 
253
 
select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q';
254
 
select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q';
255
 
explain
256
 
select min(a1), max(a1) from t1 where a1 between 'A' and 'P';
257
 
select min(a1), max(a1) from t1 where a1 between 'A' and 'P';
258
 
explain 
259
 
select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN';
260
 
select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN';
261
 
explain 
262
 
select max(a3) from t1 where a3 = 'MIN' and a2 = 2;
263
 
select max(a3) from t1 where a3 = 'MIN' and a2 = 2;
264
 
explain 
265
 
select max(a3) from t1 where a3 = 'DEN' and a2 = 2;
266
 
select max(a3) from t1 where a3 = 'DEN' and a2 = 2;
267
 
 
268
 
explain
269
 
select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA';
270
 
select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA';
271
 
 
272
 
explain
273
 
select max(a3) from t1 where a2 is null and a2 = 2;
274
 
select max(a3) from t1 where a2 is null and a2 = 2;
275
 
 
276
 
explain
277
 
select max(a2) from t1 where a2 >= 1;
278
 
select max(a2) from t1 where a2 >= 1;
279
 
explain
280
 
select min(a3) from t1 where a2 = 2 and a3 < 'SEA';
281
 
select min(a3) from t1 where a2 = 2 and a3 < 'SEA';
282
 
 
283
 
explain
284
 
select min(a3) from t1 where a2 = 4;
285
 
select min(a3) from t1 where a2 = 4;
286
 
explain
287
 
select min(a3) from t1 where a2 = 2 and a3 > 'SEA';
288
 
select min(a3) from t1 where a2 = 2 and a3 > 'SEA';
289
 
explain
290
 
select (min(a4)+max(a4))/2 from t1;
291
 
select (min(a4)+max(a4))/2 from t1;
292
 
explain
293
 
select min(a3) from t1 where 2 = a2;
294
 
select min(a3) from t1 where 2 = a2;
295
 
explain
296
 
select max(a3) from t1 where a2 = 2 and 'SEA' > a3;
297
 
select max(a3) from t1 where a2 = 2 and 'SEA' > a3;
298
 
explain
299
 
select max(a3) from t1 where a2 = 2 and 'SEA' < a3;
300
 
select max(a3) from t1 where a2 = 2 and 'SEA' < a3;
301
 
explain
302
 
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI';
303
 
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI';
304
 
explain
305
 
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA';
306
 
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA';
307
 
explain
308
 
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN';
309
 
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN';
310
 
explain
311
 
select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN';
312
 
select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN';
313
 
 
314
 
explain
315
 
select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK';
316
 
select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK';
317
 
 
318
 
# Queries to which max/min optimization is not applied
319
 
 
320
 
explain 
321
 
select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX';
322
 
explain 
323
 
select min(a1) from t1 where a1 != 'KKK';
324
 
explain
325
 
select max(a3) from t1 where a2 < 2 and a3 < 'SEA';
326
 
explain
327
 
select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA';
328
 
 
329
 
explain
330
 
select min(a4 - 0.01) from t1;
331
 
explain
332
 
select max(a4 + 0.01) from t1;
333
 
explain
334
 
select min(a3) from t1 where (a2 +1 ) is null;
335
 
explain
336
 
select min(a3) from t1 where (a2 + 1) = 2;
337
 
explain
338
 
select min(a3) from t1 where 2 = (a2 + 1);
339
 
explain
340
 
select min(a2) from t1 where a2 < 2 * a2 - 8;
341
 
explain
342
 
select min(a1) from t1  where a1 between a3 and 'KKK';
343
 
explain
344
 
select min(a4) from t1  where (a4 + 0.01) between 0.07 and 0.08;
345
 
explain
346
 
select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME';
347
 
drop table t1, t2;
348
 
 
349
 
# Moved to func_group_innodb
350
 
#--disable_warnings
351
 
#create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB;
352
 
#--enable_warnings
353
 
#insert into t1 values (1, 3);
354
 
#select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ;
355
 
#select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;
356
 
#drop table t1;
357
 
 
358
 
#
359
 
# Bug #6658 MAX(column) returns incorrect coercibility
360
 
#
361
 
create table t1 (a char);
362
 
insert into t1 values ('a'),('b');
363
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
364
 
show create table t1;
365
 
create table t2 select max(a),min(a) from t1;
366
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
367
 
show create table t2;
368
 
drop table t2;
369
 
create table t2 select concat(a) from t1;
370
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
371
 
show create table t2;
372
 
drop table t2,t1;
373
 
 
374
 
#
375
 
# aggregate functions on static tables
376
 
#
377
 
create table t1 (a int);
378
 
insert into t1 values (1);
379
 
select max(a) as b from t1 having b=1;
380
 
select a from t1 having a=1;
381
 
drop table t1;
382
 
 
383
 
#
384
 
# Bug #3435: variance(const), stddev(const) and an empty table
385
 
#
386
 
 
387
 
create table t1 (a int);
388
 
select variance(2) from t1;
389
 
select stddev(2) from t1;
390
 
drop table t1;
391
 
 
392
 
 
393
 
#
394
 
# cleunup() of optimized away count(*) and max/min
395
 
#
396
 
create table t1 (a int);
397
 
insert into t1 values (1),(2);
398
 
SELECT COUNT(*) FROM t1;
399
 
SELECT COUNT(*) FROM t1;
400
 
SELECT COUNT(*) FROM t1;
401
 
drop table t1;
402
 
 
403
 
create table t1 (a int, primary key(a));
404
 
insert into t1 values (1),(2);
405
 
SELECT max(a) FROM t1;
406
 
SELECT max(a) FROM t1;
407
 
SELECT max(a) FROM t1;
408
 
drop table t1;
409
 
 
410
 
#
411
 
# Bug #5406 min/max optimization for empty set
412
 
#
413
 
 
414
 
CREATE TABLE t1 (a int primary key);
415
 
INSERT INTO t1 VALUES (1),(2),(3),(4);
416
 
 
417
 
SELECT MAX(a) FROM t1 WHERE a > 5;
418
 
SELECT MIN(a) FROM t1 WHERE a < 0;
419
 
 
420
 
DROP TABLE t1;
421
 
 
422
 
#
423
 
# Bug #5555 GROUP BY enum_field" returns incorrect results
424
 
#
425
 
 
426
 
CREATE TEMPORARY TABLE t1 (
427
 
  id int NOT NULL auto_increment,
428
 
  val enum('one','two','three') NOT NULL default 'one',
429
 
  PRIMARY KEY  (id)
430
 
) ENGINE=MyISAM;
431
 
 
432
 
INSERT INTO t1 VALUES
433
 
(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
434
 
 
435
 
select val, count(*) from t1 group by val;
436
 
drop table t1;
437
 
 
438
 
#
439
 
# Bug #5615: type of aggregate function column wrong when using group by
440
 
#
441
 
 
442
 
create table t1(a int, b datetime);
443
 
insert into t1 values (1, NOW()), (2, NOW());
444
 
create table t2 select MAX(b) from t1 group by a;
445
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
446
 
show create table t2;
447
 
drop table t1, t2;
448
 
 
449
 
#
450
 
# Bug 7833:  Wrong datatype of aggregate column is returned
451
 
#
452
 
 
453
 
create table t1(f1 datetime);
454
 
insert into t1 values (now());
455
 
create table t2 select f2 from (select max(now()) f2 from t1) a;
456
 
show columns from t2;
457
 
drop table t2;
458
 
create table t2 select f2 from (select now() f2 from t1) a;
459
 
show columns from t2;
460
 
drop table t2, t1;
461
 
 
462
 
#
463
 
# Bug 8893: wrong result for min/max optimization with 2 indexes
464
 
#
465
 
 
466
 
CREATE TABLE t1(
467
 
  id int PRIMARY KEY,
468
 
  a  int,
469
 
  b  int,
470
 
  INDEX i_b_id(a,b,id),
471
 
  INDEX i_id(a,id)
472
 
);
473
 
INSERT INTO t1 VALUES 
474
 
  (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
475
 
SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
476
 
DROP TABLE t1;
477
 
 
478
 
# change the order of the last two index definitions
479
 
 
480
 
CREATE TABLE t1(
481
 
  id int PRIMARY KEY,
482
 
  a  int,
483
 
  b  int,
484
 
  INDEX i_id(a,id),
485
 
  INDEX i_b_id(a,b,id)
486
 
);
487
 
INSERT INTO t1 VALUES 
488
 
  (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
489
 
SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
490
 
DROP TABLE t1;
491
 
 
492
 
 
493
 
#
494
 
# Bug #12882    min/max inconsistent on empty table
495
 
#
496
 
# Test case moved to func_group_innodb
497
 
#
498
 
# Bug #18206: min/max optimization cannot be applied to partial index
499
 
#
500
 
 
501
 
CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));
502
 
INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
503
 
SELECT * FROM t1;
504
 
 
505
 
SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
506
 
SHOW WARNINGS;
507
 
SELECT MAX(b) FROM t1 WHERE b < 'pp';
508
 
DROP TABLE t1;
509
 
 
510
 
CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4)));
511
 
INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa');
512
 
SELECT MAX(b) FROM t1;
513
 
EXPLAIN SELECT MAX(b) FROM t1;
514
 
DROP TABLE t1;
515
 
 
516
 
#
517
 
# Bug #16792  query with subselect, join, and group not returning proper values
518
 
#
519
 
CREATE TABLE t1 (a INT, b INT);
520
 
INSERT INTO t1 VALUES (1,1),(1,2),(2,3);
521
 
 
522
 
SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
523
 
SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;
524
 
# an attempt to test all aggregate function with no table.
525
 
SELECT AVG(2), COUNT(*), COUNT(12),
526
 
       COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2),
527
 
       GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);
528
 
DROP TABLE t1;
529
 
 
530
 
# End of 4.1 tests
531
 
 
532
 
#
533
 
# decimal-related tests
534
 
#
535
 
create table t2 (ff double);
536
 
insert into t2 values (2.2);
537
 
select cast(sum(distinct ff) as decimal(5,2)) from t2;
538
 
select sum(distinct ff) from t2;
539
 
select cast(variance(ff) as decimal(10,3)) from t2;
540
 
select cast(min(ff) as decimal(5,2)) from t2;
541
 
 
542
 
create table t1 (df decimal(5,1));
543
 
insert into t1 values(1.1);
544
 
insert into t1 values(2.2);
545
 
select sum(distinct df) from t1;
546
 
select min(df) from t1;
547
 
select 1e8 * sum(distinct df) from t1;
548
 
select 1e8 * min(df) from t1;
549
 
 
550
 
create table t3 (ifl int);
551
 
insert into t3 values(1), (2);
552
 
select cast(min(ifl) as decimal(5,2)) from t3;
553
 
 
554
 
drop table t1, t2, t3;
555
 
 
556
 
 
557
 
#
558
 
# BUG#3190, WL#1639: Standard Deviation STDDEV - 2 different calculations
559
 
#
560
 
 
561
 
CREATE TABLE t1 (id int,value1 float(10,2));
562
 
INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00), (2,13.00);
563
 
select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id;
564
 
DROP TABLE t1;
565
 
 
566
 
#
567
 
# BUG#8464 decimal AVG returns incorrect result
568
 
#
569
 
 
570
 
CREATE TABLE t1 (col1 decimal(16,12));
571
 
INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002);
572
 
insert into t1 select * from t1;
573
 
select col1,count(col1),sum(col1),avg(col1) from t1 group by col1;
574
 
DROP TABLE t1;
575
 
 
576
 
#
577
 
# BUG#8465 decimal MIN and MAX return incorrect result
578
 
#
579
 
 
580
 
create table t1 (col1 decimal(16,12));
581
 
insert into t1 values (-5.00000000001);
582
 
insert into t1 values (-5.00000000001);
583
 
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
584
 
delete from t1;
585
 
insert into t1 values (5.00000000001);
586
 
insert into t1 values (5.00000000001);
587
 
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
588
 
DROP TABLE t1;
589
 
 
590
 
#
591
 
# Test that new VARCHAR correctly works with COUNT(DISTINCT)
592
 
#
593
 
 
594
 
CREATE TABLE t1 (a VARCHAR(400));
595
 
INSERT INTO t1 (a) VALUES ("A"), ("a"), ("a "), ("a   "),
596
 
                          ("B"), ("b"), ("b "), ("b   ");
597
 
SELECT COUNT(DISTINCT a) FROM t1;
598
 
DROP TABLE t1;
599
 
 
600
 
#
601
 
# Test for buf #9210: GROUP BY with expression if a decimal type
602
 
#
603
 
 
604
 
CREATE TABLE t1 (a int, b int, c int);
605
 
INSERT INTO t1 (a, b, c) VALUES
606
 
  (1,1,1), (1,1,2), (1,1,3),
607
 
  (1,2,1), (1,2,2), (1,2,3),
608
 
  (1,3,1), (1,3,2), (1,3,3),
609
 
  (2,1,1), (2,1,2), (2,1,3),
610
 
  (2,2,1), (2,2,2), (2,2,3),
611
 
  (2,3,1), (2,3,2), (2,3,3),
612
 
  (3,1,1), (3,1,2), (3,1,3),
613
 
  (3,2,1), (3,2,2), (3,2,3),
614
 
  (3,3,1), (3,3,2), (3,3,3);
615
 
 
616
 
--sorted_result
617
 
SELECT b/c as v, a FROM t1 ORDER BY v;
618
 
SELECT b/c as v, SUM(a) FROM t1 GROUP BY v;
619
 
SELECT SUM(a) FROM t1 GROUP BY b/c;
620
 
 
621
 
DROP TABLE t1;
622
 
set div_precision_increment= @sav_dpi;
623
 
 
624
 
#
625
 
# Bug #20868: Client connection is broken on SQL query error
626
 
#
627
 
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
628
 
INSERT INTO t1 VALUES (1,1), (2,2);
629
 
 
630
 
CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
631
 
INSERT INTO t2 VALUES (1,1), (3,3);
632
 
 
633
 
--sorted_result
634
 
SELECT 
635
 
  (SELECT SUM(c.a) FROM t1 ttt, t2 ccc 
636
 
   WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid   
637
 
FROM t1 t, t2 c WHERE t.a = c.b;
638
 
 
639
 
DROP TABLE t1,t2;
640
 
 
641
 
#
642
 
# Bug #10966: Variance functions return wrong data type
643
 
#
644
 
 
645
 
create table t1 select variance(0);                                               
646
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
647
 
show create table t1;                                                           
648
 
drop table t1;                                                                  
649
 
create table t1 select stddev(0);
650
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
651
 
show create table t1;
652
 
drop table t1;
653
 
 
654
 
 
655
 
#
656
 
# Bug#22555: STDDEV yields positive result for groups with only one row
657
 
#
658
 
 
659
 
create table bug22555 (i int primary key auto_increment, s1 int, s2 int, e decimal(30,10), o double);
660
 
insert into bug22555 (s1, s2, e, o) values (53, 78, 11.4276528, 6.828112), (17, 78, 5.916793, 1.8502951), (18, 76, 2.679231, 9.17975591), (31, 62, 6.07831, 0.1), (19, 41, 5.37463, 15.1), (83, 73, 14.567426, 7.959222), (92, 53, 6.10151, 13.1856852), (7, 12, 13.92272, 3.442007), (92, 35, 11.95358909, 6.01376678), (38, 84, 2.572, 7.904571);
661
 
select std(s1/s2) from bug22555 group by i;
662
 
select std(e) from bug22555 group by i;
663
 
select std(o) from bug22555 group by i;
664
 
drop table bug22555;
665
 
 
666
 
create table bug22555 (i int, s1 int, s2 int, o1 double, o2 double, e1 decimal, e2 decimal);
667
 
insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
668
 
select i, count(*) from bug22555 group by i;
669
 
select std(s1/s2) from bug22555 where i=1;
670
 
select std(s1/s2) from bug22555 where i=2;
671
 
select std(s1/s2) from bug22555 where i=3;
672
 
select std(s1/s2) from bug22555 where i=1 group by i;
673
 
select std(s1/s2) from bug22555 where i=2 group by i;
674
 
select std(s1/s2) from bug22555 where i=3 group by i;
675
 
select std(s1/s2) from bug22555 group by i order by i;
676
 
select i, count(*), std(o1/o2) from bug22555 group by i order by i;
677
 
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
678
 
set @saved_div_precision_increment=@@div_precision_increment;
679
 
set div_precision_increment=19;
680
 
select i, count(*), variance(s1/s2) from bug22555 group by i order by i;
681
 
select i, count(*), variance(o1/o2) from bug22555 group by i order by i;
682
 
select i, count(*), variance(e1/e2) from bug22555 group by i order by i;
683
 
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
684
 
select i, count(*), std(o1/o2) from bug22555 group by i order by i;
685
 
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
686
 
set div_precision_increment=20;
687
 
select i, count(*), variance(s1/s2) from bug22555 group by i order by i;
688
 
select i, count(*), variance(o1/o2) from bug22555 group by i order by i;
689
 
select i, count(*), variance(e1/e2) from bug22555 group by i order by i;
690
 
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
691
 
select i, count(*), std(o1/o2) from bug22555 group by i order by i;
692
 
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
693
 
set @@div_precision_increment=@saved_div_precision_increment;
694
 
insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
695
 
insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
696
 
insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
697
 
 
698
 
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
699
 
select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
700
 
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
701
 
select std(s1/s2) from bug22555;
702
 
select std(o1/o2) from bug22555;
703
 
select std(e1/e2) from bug22555;
704
 
set @saved_div_precision_increment=@@div_precision_increment;
705
 
set div_precision_increment=19;
706
 
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
707
 
select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
708
 
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
709
 
select round(std(s1/s2), 17) from bug22555;
710
 
select std(o1/o2) from bug22555;
711
 
select round(std(e1/e2), 17) from bug22555;
712
 
set div_precision_increment=20;
713
 
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
714
 
select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
715
 
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
716
 
select round(std(s1/s2), 17) from bug22555;
717
 
select std(o1/o2) from bug22555;
718
 
select round(std(e1/e2), 17) from bug22555;
719
 
set @@div_precision_increment=@saved_div_precision_increment;
720
 
drop table bug22555;
721
 
 
722
 
create table bug22555 (s int, o double, e decimal);
723
 
insert into bug22555 values (1,1,1),(2,2,2),(3,3,3),(6,6,6),(7,7,7);
724
 
select var_samp(s), var_pop(s) from bug22555;
725
 
select var_samp(o), var_pop(o) from bug22555;
726
 
select var_samp(e), var_pop(e) from bug22555;
727
 
drop table bug22555;
728
 
 
729
 
create table bug22555 (s int, o double, e decimal);
730
 
insert into bug22555 values (null,null,null),(null,null,null);
731
 
select var_samp(s) as 'null', var_pop(s) as 'null' from bug22555;
732
 
select var_samp(o) as 'null', var_pop(o) as 'null' from bug22555;
733
 
select var_samp(e) as 'null', var_pop(e) as 'null' from bug22555;
734
 
insert into bug22555 values (1,1,1);
735
 
select var_samp(s) as 'null', var_pop(s) as '0' from bug22555;
736
 
select var_samp(o) as 'null', var_pop(o) as '0' from bug22555;
737
 
select var_samp(e) as 'null', var_pop(e) as '0' from bug22555;
738
 
insert into bug22555 values (2,2,2);
739
 
select var_samp(s) as '0.5', var_pop(s) as '0.25' from bug22555;
740
 
select var_samp(o) as '0.5', var_pop(o) as '0.25' from bug22555;
741
 
select var_samp(e) as '0.5', var_pop(e) as '0.25' from bug22555;
742
 
drop table bug22555;
743
 
 
744
 
 
745
 
#
746
 
# Bug #21976: Unnecessary warning with count(decimal)
747
 
#
748
 
 
749
 
create table t1 (a decimal(20));
750
 
insert into t1 values (12345678901234567890);
751
 
select count(a) from t1;
752
 
select count(distinct a) from t1;
753
 
drop table t1;
754
 
 
755
 
#
756
 
# Bug #23184: SELECT causes server crash
757
 
# 
758
 
CREATE TABLE t1 (a INT, b INT);
759
 
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
760
 
INSERT INTO t1 SELECT a, b+8       FROM t1;
761
 
INSERT INTO t1 SELECT a, b+16      FROM t1;
762
 
INSERT INTO t1 SELECT a, b+32      FROM t1;
763
 
INSERT INTO t1 SELECT a, b+64      FROM t1;
764
 
INSERT INTO t1 SELECT a, b+128     FROM t1;
765
 
INSERT INTO t1 SELECT a, b+256     FROM t1;
766
 
INSERT INTO t1 SELECT a, b+512     FROM t1;
767
 
INSERT INTO t1 SELECT a, b+1024    FROM t1;
768
 
INSERT INTO t1 SELECT a, b+2048    FROM t1;
769
 
INSERT INTO t1 SELECT a, b+4096    FROM t1;
770
 
INSERT INTO t1 SELECT a, b+8192    FROM t1;
771
 
INSERT INTO t1 SELECT a, b+16384   FROM t1;
772
 
INSERT INTO t1 SELECT a, b+32768   FROM t1;
773
 
SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50;
774
 
SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50;
775
 
SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
776
 
 
777
 
DROP TABLE t1;
778
 
 
779
 
#
780
 
# Bug #27573: MIN() on an indexed column which is always NULL sets _other_ 
781
 
# results to NULL
782
 
#
783
 
CREATE TABLE t1 ( a INT, b INT, KEY(a) );
784
 
INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
785
 
EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
786
 
SELECT MIN(a), MIN(b) FROM t1;
787
 
 
788
 
CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
789
 
INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
790
 
EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
791
 
SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
792
 
 
793
 
CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
794
 
INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2),  (2, NULL, 2),  (3, NULL, 3);
795
 
EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
796
 
SELECT MIN(a), MIN(b) FROM t3 where a = 2;
797
 
 
798
 
CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
799
 
INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2),  (2, NULL, 2),  (3, 1, 3);
800
 
EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
801
 
SELECT MIN(a), MIN(b) FROM t4 where a = 2;
802
 
SELECT MIN(b), min(c) FROM t4 where a = 2;
803
 
 
804
 
CREATE TABLE t5( a INT, b INT, KEY( a, b) ); 
805
 
INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
806
 
EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
807
 
SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
808
 
SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
809
 
 
810
 
DROP TABLE t1, t2, t3, t4, t5;
811
 
 
812
 
#
813
 
# Bug #30715: Assertion failed: item_field->field->real_maybe_null(), file
814
 
# .\opt_sum.cc, line
815
 
#
816
 
 
817
 
CREATE TABLE t1 (a int, b date NOT NULL, KEY k1 (a,b));
818
 
SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01';
819
 
DROP TABLE t1;
820
 
 
821
 
#
822
 
# Bug #34512: CAST( AVG( double ) AS DECIMAL ) returns wrong results
823
 
#
824
 
 
825
 
CREATE TABLE t1(a DOUBLE);
826
 
INSERT INTO t1 VALUES (10), (20);
827
 
SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1;
828
 
 
829
 
DROP TABLE t1;
830
 
 
831
 
###
832
 
--echo End of 5.0 tests