~drizzle-trunk/drizzle/development

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