2
# simple test of all group functions
6
drop table if exists t1,t2;
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");
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;
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;
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;
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;
39
# REPLACE ... SELECT doesn't yet work with PS
40
replace into t2 select grp, a, c from t1 limit 2,1;
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;
58
# Test of bug in left join & avg
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;
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');
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;
94
# Test of bug in COUNT(i)*(i+0)
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;
104
# Another SUM() problem with 3.23.2
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;
119
# Test problem with MIN() optimization in case of null values
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));
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;
132
explain select min(a2) from t1;
133
explain select max(t1.a1), max(t2.a2) from t1, t2;
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;
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;
150
# Test of group function and NULL values
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;
172
# Bug #1972: test for bit_and(), bit_or() and negative values
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;
181
# Bug #3376: avg() and an empty table
184
create table t1 (a int);
185
select avg(2) from t1;
189
# Tests to check MIN/MAX query optimization
192
# Create database schema
194
a1 char(3) primary key,
204
a1 char(3) primary key,
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);
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');
238
# Show the table contents
242
# Queries with min/max functions
243
# which regular min/max optimization are applied to
246
select min(a1) from t1;
247
select min(a1) from t1;
249
select max(a4) from t1;
250
select max(a4) from t1;
252
select min(a5), max(a5) from t1;
253
select min(a5), max(a5) from t1;
255
select min(a3) from t1 where a2 = 2;
256
select min(a3) from t1 where a2 = 2;
258
select min(a1), max(a1) from t1 where a4 = 0.080;
259
select min(a1), max(a1) from t1 where a4 = 0.080;
262
select min(t1.a5), max(t2.a3) from t1, t2;
263
select min(t1.a5), max(t2.a3) from t1, t2;
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';
268
# Queries with min/max functions
269
# which extended min/max optimization are applied to
272
select min(a1) from t1 where a1 > 'KKK';
273
select min(a1) from t1 where a1 > 'KKK';
275
select min(a1) from t1 where a1 >= 'KKK';
276
select min(a1) from t1 where a1 >= 'KKK';
278
select max(a3) from t1 where a2 = 2 and a3 < 'SEA';
279
select max(a3) from t1 where a2 = 2 and a3 < 'SEA';
281
select max(a5) from t1 where a5 < date'1970-01-01';
282
select max(a5) from t1 where a5 < date'1970-01-01';
284
select max(a3) from t1 where a2 is null;
285
select max(a3) from t1 where a2 is null;
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';
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';
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';
296
select max(a3) from t1 where a3 = 'MIN' and a2 = 2;
297
select max(a3) from t1 where a3 = 'MIN' and a2 = 2;
299
select max(a3) from t1 where a3 = 'DEN' and a2 = 2;
300
select max(a3) from t1 where a3 = 'DEN' and a2 = 2;
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';
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;
311
select max(a2) from t1 where a2 >= 1;
312
select max(a2) from t1 where a2 >= 1;
314
select min(a3) from t1 where a2 = 2 and a3 < 'SEA';
315
select min(a3) from t1 where a2 = 2 and a3 < 'SEA';
318
select min(a3) from t1 where a2 = 4;
319
select min(a3) from t1 where a2 = 4;
321
select min(a3) from t1 where a2 = 2 and a3 > 'SEA';
322
select min(a3) from t1 where a2 = 2 and a3 > 'SEA';
324
select (min(a4)+max(a4))/2 from t1;
325
select (min(a4)+max(a4))/2 from t1;
327
select min(a3) from t1 where 2 = a2;
328
select min(a3) from t1 where 2 = a2;
330
select max(a3) from t1 where a2 = 2 and 'SEA' > a3;
331
select max(a3) from t1 where a2 = 2 and 'SEA' > a3;
333
select max(a3) from t1 where a2 = 2 and 'SEA' < a3;
334
select max(a3) from t1 where a2 = 2 and 'SEA' < a3;
336
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI';
337
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI';
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';
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';
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';
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';
352
# Queries to which max/min optimization is not applied
355
select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX';
357
select min(a1) from t1 where a1 != 'KKK';
359
select max(a3) from t1 where a2 < 2 and a3 < 'SEA';
361
select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA';
364
select min(a4 - 0.01) from t1;
366
select max(a4 + 0.01) from t1;
368
select min(a3) from t1 where (a2 +1 ) is null;
370
select min(a3) from t1 where (a2 + 1) = 2;
372
select min(a3) from t1 where 2 = (a2 + 1);
374
select min(a2) from t1 where a2 < 2 * a2 - 8;
376
select min(a1) from t1 where a1 between a3 and 'KKK';
378
select min(a4) from t1 where (a4 + 0.01) between 0.07 and 0.08;
380
select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME';
383
# Moved to func_group_innodb
385
#create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB;
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;
393
create table t1 (a char(10));
394
insert into t1 values ('a'),('b'),('c');
395
select coercibility(max(a)) from t1;
399
# Bug #6658 MAX(column) returns incorrect coercibility
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;
409
create table t2 select concat(a) from t1;
410
show create table t2;
414
# aggregate functions on static tables
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;
423
# Bug #3435: variance(const), stddev(const) and an empty table
426
create table t1 (a int);
427
select variance(2) from t1;
428
select stddev(2) from t1;
433
# cleunup() of optimized away count(*) and max/min
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;
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;
450
# Bug #5406 min/max optimization for empty set
453
CREATE TABLE t1 (a int primary key);
454
INSERT INTO t1 VALUES (1),(2),(3),(4);
456
SELECT MAX(a) FROM t1 WHERE a > 5;
457
SELECT MIN(a) FROM t1 WHERE a < 0;
462
# Bug #5555 GROUP BY enum_field" returns incorrect results
466
id int(10) unsigned NOT NULL auto_increment,
467
val enum('one','two','three') NOT NULL default 'one',
469
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
471
INSERT INTO t1 VALUES
472
(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
474
select val, count(*) from t1 group by val;
478
id int(10) unsigned NOT NULL auto_increment,
479
val set('one','two','three') NOT NULL default 'one',
481
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
483
INSERT INTO t1 VALUES
484
(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
486
select val, count(*) from t1 group by val;
490
# Bug #5615: type of aggregate function column wrong when using group by
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;
500
# Bug 7833: Wrong datatype of aggregate column is returned
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;
508
create table t2 select f2 from (select now() f2 from t1) a;
509
show columns from t2;
513
# Bug 8893: wrong result for min/max optimization with 2 indexes
520
INDEX i_b_id(a,b,id),
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;
528
# change the order of the last two index definitions
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;
544
# Bug #12882 min/max inconsistent on empty table
546
# Test case moved to func_group_innodb
548
# Bug #18206: min/max optimization cannot be applied to partial index
551
CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));
552
INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
555
SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
557
SELECT MAX(b) FROM t1 WHERE b < 'pp';
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;
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"));
571
SELECT MAX(b) FROM t1;
572
EXPLAIN SELECT MAX(b) FROM t1;
575
# Bug #16792 query with subselect, join, and group not returning proper values
577
CREATE TABLE t1 (a INT, b INT);
578
INSERT INTO t1 VALUES (1,1),(1,2),(2,3);
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);
591
# decimal-related tests
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;
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;
608
create table t3 (ifl int);
609
insert into t3 values(1), (2);
610
select cast(min(ifl) as decimal(5,2)) from t3;
612
drop table t1, t2, t3;
616
# BUG#3190, WL#1639: Standard Deviation STDDEV - 2 different calculations
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;
625
# BUG#8464 decimal AVG returns incorrect result
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;
635
# BUG#8465 decimal MIN and MAX return incorrect result
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;
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;
649
# Test that new VARCHAR correctly works with COUNT(DISTINCT)
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;
659
# Test for buf #9210: GROUP BY with expression if a decimal type
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);
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;
679
set div_precision_increment= @sav_dpi;
682
# Bug #20868: Client connection is broken on SQL query error
684
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
685
INSERT INTO t1 VALUES (1,1), (2,2);
687
CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
688
INSERT INTO t2 VALUES (1,1), (3,3);
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;
698
# Bug #10966: Variance functions return wrong data type
701
create table t1 select variance(0);
702
show create table t1;
704
create table t1 select stddev(0);
705
show create table t1;
710
# Bug#22555: STDDEV yields positive result for groups with only one row
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;
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);
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;
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;
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;
800
# Bug #21976: Unnecessary warning with count(decimal)
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;
810
# Bug #23184: SELECT causes server crash
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;
834
# Bug #27573: MIN() on an indexed column which is always NULL sets _other_
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;
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;
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;
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;
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;
864
DROP TABLE t1, t2, t3, t4, t5;
867
# Bug #30715: Assertion failed: item_field->field->real_maybe_null(), file
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';
876
# Bug #34512: CAST( AVG( double ) AS DECIMAL ) returns wrong results
879
CREATE TABLE t1(a DOUBLE);
880
INSERT INTO t1 VALUES (10), (20);
881
SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1;
886
--echo End of 5.0 tests
added
removed
mysql-test/t/func_group.test
