← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/func_group.test

  • Committer: Monty Taylor
  • Date: 2008-09-15 17:24:04 UTC
  • Revision ID: monty@inaugust.com-20080915172404-ygh6hiyu0q7qpa9x
Removed strndup calls.

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