← Back to branch summary

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

« back to all changes in this revision

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