~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
drop table if exists t1,t2;
2
set @sav_dpi= @@div_precision_increment;
3
set div_precision_increment= 5;
4
show variables like 'div_precision_increment';
5
Variable_name	Value
6
div_precision_increment	5
512 by Brian Aker
Adding back more test cases.
7
create table t1 (grp int, a bigint, c char(10) not null);
1 by brian
clean slate
8
insert into t1 values (1,1,"a");
9
insert into t1 values (2,2,"b");
10
insert into t1 values (2,3,"c");
11
insert into t1 values (3,4,"E");
12
insert into t1 values (3,5,"C");
13
insert into t1 values (3,6,"D");
14
select a,c,sum(a) from t1 group by a;
15
a	c	sum(a)
16
1	a	1
17
2	b	2
18
3	c	3
19
4	E	4
20
5	C	5
21
6	D	6
22
select a,c,sum(a) from t1 where a > 10 group by a;
23
a	c	sum(a)
24
select sum(a) from t1 where a > 10;
25
sum(a)
26
NULL
27
select a from t1 order by rand(10);
28
a
29
2
30
6
31
1
32
3
33
5
34
4
35
select distinct a from t1 order by rand(10);
36
a
37
2
38
6
39
1
40
3
41
5
42
4
43
select count(distinct a),count(distinct grp) from t1;
44
count(distinct a)	count(distinct grp)
45
6	3
46
insert into t1 values (null,null,'');
47
select count(distinct a),count(distinct grp) from t1;
48
count(distinct a)	count(distinct grp)
49
6	3
512 by Brian Aker
Adding back more test cases.
50
create table t2 (grp int, a bigint, c char(10));
1 by brian
clean slate
51
insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp;
52
replace into t2 select grp, a, c from t1 limit 2,1;
53
select * from t2;
54
grp	a	c
55
NULL	NULL	
56
1	2	a
57
2	5	c
58
3	9	E
59
2	3	c
60
drop table t1,t2;
512 by Brian Aker
Adding back more test cases.
61
CREATE TABLE t1 (id int,value1 float(10,2));
1 by brian
clean slate
62
INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00);
512 by Brian Aker
Adding back more test cases.
63
CREATE TABLE t2 (id int,name char(20));
1 by brian
clean slate
64
INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two');
65
select id, avg(value1), std(value1), variance(value1) from t1 group by id;
66
id	avg(value1)	std(value1)	variance(value1)
67
1	1.0000000	0.816497	0.666667
68
2	11.0000000	0.816497	0.666667
69
select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id;
70
name	avg(value1)	std(value1)	variance(value1)
71
Set One	1.0000000	0.816497	0.666667
72
Set Two	11.0000000	0.816497	0.666667
73
drop table t1,t2;
74
create table t1 (id int not null);
75
create table t2 (id int not null,rating int null);
76
insert into t1 values(1),(2),(3);
77
insert into t2 values(1, 3),(2, NULL),(2, NULL),(3, 2),(3, NULL);
78
select t1.id, avg(rating) from t1 left join t2 on ( t1.id = t2.id ) group by t1.id;
79
id	avg(rating)
80
1	3.00000
81
2	NULL
82
3	2.00000
83
select sql_small_result t2.id, avg(rating) from t2 group by t2.id;
84
id	avg(rating)
85
1	3.00000
86
2	NULL
87
3	2.00000
88
select sql_big_result t2.id, avg(rating) from t2 group by t2.id;
89
id	avg(rating)
90
1	3.00000
91
2	NULL
92
3	2.00000
93
select sql_small_result t2.id, avg(rating+0.0e0) from t2 group by t2.id;
94
id	avg(rating+0.0e0)
95
1	3
96
2	NULL
97
3	2
98
select sql_big_result t2.id, avg(rating+0.0e0) from t2 group by t2.id;
99
id	avg(rating+0.0e0)
100
1	3
101
2	NULL
102
3	2
103
drop table t1,t2;
512 by Brian Aker
Adding back more test cases.
104
create table t1 (a int primary key, c char(10), b text);
1 by brian
clean slate
105
INSERT INTO t1 VALUES (1,'1','1');
106
INSERT INTO t1 VALUES (2,'2','2');
107
INSERT INTO t1 VALUES (4,'4','4');
108
select count(*) from t1;
109
count(*)
110
3
111
select count(*) from t1 where a = 1;
112
count(*)
113
1
114
select count(*) from t1 where a = 100;
115
count(*)
116
0
117
select count(*) from t1 where a >= 10;
118
count(*)
119
0
120
select count(a) from t1 where a = 1;
121
count(a)
122
1
123
select count(a) from t1 where a = 100;
124
count(a)
125
0
126
select count(a) from t1 where a >= 10;
127
count(a)
128
0
129
select count(b) from t1 where b >= 2;
130
count(b)
131
2
132
select count(b) from t1 where b >= 10;
133
count(b)
134
0
135
select count(c) from t1 where c = 10;
136
count(c)
137
0
138
drop table t1;
139
CREATE TABLE t1 (d DATETIME, i INT);
140
INSERT INTO t1 VALUES (NOW(), 1);
141
SELECT COUNT(i), i, COUNT(i)*i FROM t1 GROUP BY i;
142
COUNT(i)	i	COUNT(i)*i 
143
1	1	1
144
SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i;
145
COUNT(i)	(i+0)	COUNT(i)*(i+0)
146
1	1	1
147
DROP TABLE t1;
148
create table t1 (
149
num float(5,2),
150
user char(20)
151
);
152
insert into t1 values (10.3,'nem'),(20.53,'monty'),(30.23,'sinisa');
153
insert into t1 values (30.13,'nem'),(20.98,'monty'),(10.45,'sinisa');
154
insert into t1 values (5.2,'nem'),(8.64,'monty'),(11.12,'sinisa');
155
select sum(num) from t1;
156
sum(num)
157
147.58
158
select sum(num) from t1 group by user;
159
sum(num)
160
50.15
161
45.63
162
51.80
163
drop table t1;
164
create table t1 (a1 int, a2 char(3), key k1(a1), key k2(a2));
165
insert into t1 values(10,'aaa'), (10,null), (10,'bbb'), (20,'zzz');
166
create table t2(a1 char(3), a2 int, a3 real, key k1(a1), key k2(a2, a1));
167
select * from t1;
168
a1	a2
169
10	aaa
170
10	NULL
171
10	bbb
172
20	zzz
173
select min(a2) from t1;
174
min(a2)
175
aaa
176
select max(t1.a1), max(t2.a2) from t1, t2;
177
max(t1.a1)	max(t2.a2)
178
NULL	NULL
179
select max(t1.a1) from t1, t2;
180
max(t1.a1)
181
NULL
182
select max(t2.a2), max(t1.a1) from t1, t2;
183
max(t2.a2)	max(t1.a1)
184
NULL	NULL
185
explain select min(a2) from t1;
186
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
187
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
188
explain select max(t1.a1), max(t2.a2) from t1, t2;
189
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
190
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
191
insert into t2 values('AAA', 10, 0.5);
192
insert into t2 values('BBB', 20, 1.0);
193
select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;
194
a1	a2	a1	a2
1718.2.1 by Lee Bieber
For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size)
195
10	NULL	AAA	10
196
10	NULL	BBB	20
1 by brian
clean slate
197
10	aaa	AAA	10
198
10	aaa	BBB	20
199
10	bbb	AAA	10
200
10	bbb	BBB	20
201
20	zzz	AAA	10
202
20	zzz	BBB	20
203
select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
204
max(t1.a1)	max(t2.a1)
205
NULL	NULL
206
select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9;
207
max(t2.a1)	max(t1.a1)
208
NULL	NULL
209
select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
210
a1	a2	a1	a2
211
10	aaa	AAA	10
212
10	aaa	BBB	20
213
10	NULL	AAA	10
214
10	NULL	BBB	20
215
10	bbb	AAA	10
216
10	bbb	BBB	20
217
20	zzz	NULL	NULL
218
select max(t1.a2) from t1 left outer join t2 on t1.a1=10;
219
max(t1.a2)
220
zzz
221
select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20;
222
max(t2.a1)
223
BBB
224
select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10;
225
max(t2.a1)
226
AAA
227
select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA';
228
max(t2.a1)
229
NULL
230
select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10;
231
max(t1.a2)	max(t2.a1)
232
zzz	BBB
233
drop table t1,t2;
234
create table t1 (a int);
235
select avg(2) from t1;
236
avg(2)
237
NULL
238
drop table t1;
239
create table t1(
240
a1 char(3) primary key,
512 by Brian Aker
Adding back more test cases.
241
a2 int,
1 by brian
clean slate
242
a3 char(3),
243
a4 real,
244
a5 date,
245
key k1(a2,a3),
246
key k2(a4 desc,a1),
247
key k3(a5,a1)
248
);
249
create table t2(
250
a1 char(3) primary key,
251
a2 char(17),
252
a3 char(2),
253
a4 char(3),
254
key k1(a3, a2),
255
key k2(a4)
256
);
257
insert into t1 values('AME',0,'SEA',0.100,date'1942-02-19');
258
insert into t1 values('HBR',1,'SEA',0.085,date'1948-03-05');
259
insert into t1 values('BOT',2,'SEA',0.085,date'1951-11-29');
260
insert into t1 values('BMC',3,'SEA',0.085,date'1958-09-08');
261
insert into t1 values('TWU',0,'LAX',0.080,date'1969-10-05');
262
insert into t1 values('BDL',0,'DEN',0.080,date'1960-11-27');
263
insert into t1 values('DTX',1,'NYC',0.080,date'1961-05-04');
264
insert into t1 values('PLS',1,'WDC',0.075,date'1949-01-02');
265
insert into t1 values('ZAJ',2,'CHI',0.075,date'1960-06-15');
266
insert into t1 values('VVV',2,'MIN',0.075,date'1959-06-28');
267
insert into t1 values('GTM',3,'DAL',0.070,date'1977-09-23');
268
insert into t1 values('SSJ',null,'CHI',null,date'1974-03-19');
269
insert into t1 values('KKK',3,'ATL',null,null);
270
insert into t1 values('XXX',null,'MIN',null,null);
271
insert into t1 values('WWW',1,'LED',null,null);
272
insert into t2 values('TKF','Seattle','WA','AME');
273
insert into t2 values('LCC','Los Angeles','CA','TWU');
274
insert into t2 values('DEN','Denver','CO','BDL');
275
insert into t2 values('SDC','San Diego','CA','TWU');
276
insert into t2 values('NOL','New Orleans','LA','GTM');
277
insert into t2 values('LAK','Los Angeles','CA','TWU');
278
insert into t2 values('AAA','AAA','AA','AME');
279
select * from t1;
280
a1	a2	a3	a4	a5
281
AME	0	SEA	0.1	1942-02-19
512 by Brian Aker
Adding back more test cases.
282
BDL	0	DEN	0.08	1960-11-27
283
BMC	3	SEA	0.085	1958-09-08
1 by brian
clean slate
284
BOT	2	SEA	0.085	1951-11-29
285
DTX	1	NYC	0.08	1961-05-04
512 by Brian Aker
Adding back more test cases.
286
GTM	3	DAL	0.07	1977-09-23
287
HBR	1	SEA	0.085	1948-03-05
288
KKK	3	ATL	NULL	NULL
1 by brian
clean slate
289
PLS	1	WDC	0.075	1949-01-02
512 by Brian Aker
Adding back more test cases.
290
SSJ	NULL	CHI	NULL	1974-03-19
291
TWU	0	LAX	0.08	1969-10-05
1 by brian
clean slate
292
VVV	2	MIN	0.075	1959-06-28
512 by Brian Aker
Adding back more test cases.
293
WWW	1	LED	NULL	NULL
1 by brian
clean slate
294
XXX	NULL	MIN	NULL	NULL
512 by Brian Aker
Adding back more test cases.
295
ZAJ	2	CHI	0.075	1960-06-15
1 by brian
clean slate
296
select * from t2;
297
a1	a2	a3	a4
512 by Brian Aker
Adding back more test cases.
298
AAA	AAA	AA	AME
299
DEN	Denver	CO	BDL
300
LAK	Los Angeles	CA	TWU
1 by brian
clean slate
301
LCC	Los Angeles	CA	TWU
512 by Brian Aker
Adding back more test cases.
302
NOL	New Orleans	LA	GTM
1 by brian
clean slate
303
SDC	San Diego	CA	TWU
512 by Brian Aker
Adding back more test cases.
304
TKF	Seattle	WA	AME
1 by brian
clean slate
305
explain 
306
select min(a1) from t1;
307
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
308
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
309
select min(a1) from t1;
310
min(a1)
311
AME
312
explain 
313
select max(a4) from t1;
314
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
315
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
316
select max(a4) from t1;
317
max(a4)
318
0.1
319
explain 
320
select min(a5), max(a5) from t1;
321
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
322
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
323
select min(a5), max(a5) from t1;
324
min(a5)	max(a5)
325
1942-02-19	1977-09-23
326
explain 
327
select min(a3) from t1 where a2 = 2;
328
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
329
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
330
select min(a3) from t1 where a2 = 2;
331
min(a3)
332
CHI
333
explain 
334
select min(a1), max(a1) from t1 where a4 = 0.080;
335
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
336
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
337
select min(a1), max(a1) from t1 where a4 = 0.080;
338
min(a1)	max(a1)
339
BDL	TWU
340
explain 
341
select min(t1.a5), max(t2.a3) from t1, t2;
342
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
343
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
344
select min(t1.a5), max(t2.a3) from t1, t2;
345
min(t1.a5)	max(t2.a3)
346
1942-02-19	WA
347
explain 
348
select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA';
349
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
350
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
351
select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA';
352
min(t1.a3)	max(t2.a2)
353
DEN	San Diego
354
explain 
355
select min(a1) from t1 where a1 > 'KKK';
356
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
357
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
358
select min(a1) from t1 where a1 > 'KKK';
359
min(a1)
360
PLS
361
explain 
362
select min(a1) from t1 where a1 >= 'KKK';
363
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
364
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
365
select min(a1) from t1 where a1 >= 'KKK';
366
min(a1)
367
KKK
368
explain 
369
select max(a3) from t1 where a2 = 2 and a3 < 'SEA';
370
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
371
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
372
select max(a3) from t1 where a2 = 2 and a3 < 'SEA';
373
max(a3)
374
MIN
375
explain 
376
select max(a5) from t1 where a5 < date'1970-01-01';
377
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
378
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
379
select max(a5) from t1 where a5 < date'1970-01-01';
380
max(a5)
381
1969-10-05
382
explain 
383
select max(a3) from t1 where a2 is null;
384
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
385
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
386
select max(a3) from t1 where a2 is null;
387
max(a3)
388
MIN
389
explain 
390
select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q';
391
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
392
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
393
select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q';
394
max(a3)
395
LAX
396
explain
397
select min(a1), max(a1) from t1 where a1 between 'A' and 'P';
398
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
399
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
400
select min(a1), max(a1) from t1 where a1 between 'A' and 'P';
401
min(a1)	max(a1)
402
AME	KKK
403
explain 
404
select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN';
405
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
406
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
407
select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN';
408
max(a3)
409
MIN
410
explain 
411
select max(a3) from t1 where a3 = 'MIN' and a2 = 2;
412
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
413
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
414
select max(a3) from t1 where a3 = 'MIN' and a2 = 2;
415
max(a3)
416
MIN
417
explain 
418
select max(a3) from t1 where a3 = 'DEN' and a2 = 2;
419
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
420
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
421
select max(a3) from t1 where a3 = 'DEN' and a2 = 2;
422
max(a3)
423
NULL
424
explain
425
select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA';
426
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
427
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
428
select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA';
429
max(t1.a3)	min(t2.a2)
430
CHI	Los Angeles
431
explain
432
select max(a3) from t1 where a2 is null and a2 = 2;
433
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
434
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
435
select max(a3) from t1 where a2 is null and a2 = 2;
436
max(a3)
437
NULL
438
explain
439
select max(a2) from t1 where a2 >= 1;
440
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
441
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
442
select max(a2) from t1 where a2 >= 1;
443
max(a2)
444
3
445
explain
446
select min(a3) from t1 where a2 = 2 and a3 < 'SEA';
447
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
448
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
449
select min(a3) from t1 where a2 = 2 and a3 < 'SEA';
450
min(a3)
451
CHI
452
explain
453
select min(a3) from t1 where a2 = 4;
454
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
455
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
456
select min(a3) from t1 where a2 = 4;
457
min(a3)
458
NULL
459
explain
460
select min(a3) from t1 where a2 = 2 and a3 > 'SEA';
461
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
462
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
463
select min(a3) from t1 where a2 = 2 and a3 > 'SEA';
464
min(a3)
465
NULL
466
explain
467
select (min(a4)+max(a4))/2 from t1;
468
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
469
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
470
select (min(a4)+max(a4))/2 from t1;
471
(min(a4)+max(a4))/2
472
0.085
473
explain
474
select min(a3) from t1 where 2 = a2;
475
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
476
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
477
select min(a3) from t1 where 2 = a2;
478
min(a3)
479
CHI
480
explain
481
select max(a3) from t1 where a2 = 2 and 'SEA' > a3;
482
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
483
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
484
select max(a3) from t1 where a2 = 2 and 'SEA' > a3;
485
max(a3)
486
MIN
487
explain
488
select max(a3) from t1 where a2 = 2 and 'SEA' < a3;
489
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
490
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
491
select max(a3) from t1 where a2 = 2 and 'SEA' < a3;
492
max(a3)
493
NULL
494
explain
495
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI';
496
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
497
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
498
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI';
499
min(a3)
500
CHI
501
explain
502
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA';
503
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
504
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
505
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA';
506
min(a3)
507
CHI
508
explain
509
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN';
510
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
511
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
512
select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN';
513
min(a3)
514
MIN
515
explain
516
select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN';
517
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
518
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
519
select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN';
520
min(a3)
521
NULL
522
explain
523
select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK';
524
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
525
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
526
select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK';
527
min(t1.a1)	min(t2.a4)
528
AME	AME
529
explain 
530
select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX';
531
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1819.9.66 by Stewart Smith
EXPLAIN in test result changes due to modified InnoDB stats
532
1	SIMPLE	t1	index	PRIMARY	PRIMARY	14	NULL	15	Using where; Using index
1 by brian
clean slate
533
explain 
534
select min(a1) from t1 where a1 != 'KKK';
535
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
536
1	SIMPLE	t1	index	PRIMARY	PRIMARY	14	NULL	15	Using where; Using index
1 by brian
clean slate
537
explain
538
select max(a3) from t1 where a2 < 2 and a3 < 'SEA';
539
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
540
1	SIMPLE	t1	range	k1	k1	5	NULL	6	Using where; Using index
1 by brian
clean slate
541
explain
542
select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA';
543
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
544
1	SIMPLE	t1	range	k1	k1	20	NULL	1	Using where; Using index
545
1	SIMPLE	t2	range	k1	k1	11	NULL	3	Using where; Using index; Using join buffer
1 by brian
clean slate
546
explain
547
select min(a4 - 0.01) from t1;
548
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
549
1	SIMPLE	t1	index	NULL	PRIMARY	14	NULL	15	
1 by brian
clean slate
550
explain
551
select max(a4 + 0.01) from t1;
552
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
553
1	SIMPLE	t1	index	NULL	PRIMARY	14	NULL	15	
1 by brian
clean slate
554
explain
555
select min(a3) from t1 where (a2 +1 ) is null;
556
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
557
1	SIMPLE	t1	index	NULL	PRIMARY	14	NULL	15	Using where
1 by brian
clean slate
558
explain
559
select min(a3) from t1 where (a2 + 1) = 2;
560
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
561
1	SIMPLE	t1	index	NULL	PRIMARY	14	NULL	15	Using where
1 by brian
clean slate
562
explain
563
select min(a3) from t1 where 2 = (a2 + 1);
564
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
565
1	SIMPLE	t1	index	NULL	PRIMARY	14	NULL	15	Using where
1 by brian
clean slate
566
explain
567
select min(a2) from t1 where a2 < 2 * a2 - 8;
568
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
569
1	SIMPLE	t1	index	NULL	PRIMARY	14	NULL	15	Using where
1 by brian
clean slate
570
explain
571
select min(a1) from t1  where a1 between a3 and 'KKK';
572
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
573
1	SIMPLE	t1	index	PRIMARY	PRIMARY	14	NULL	15	Using where
1 by brian
clean slate
574
explain
575
select min(a4) from t1  where (a4 + 0.01) between 0.07 and 0.08;
576
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
512 by Brian Aker
Adding back more test cases.
577
1	SIMPLE	t1	index	NULL	PRIMARY	14	NULL	15	Using where
1 by brian
clean slate
578
explain
579
select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME';
580
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1819.9.66 by Stewart Smith
EXPLAIN in test result changes due to modified InnoDB stats
581
1	SIMPLE	t2	range	k2	k2	15	NULL	6	Using where; Using index
512 by Brian Aker
Adding back more test cases.
582
1	SIMPLE	t1	index	NULL	PRIMARY	14	NULL	15	Using index; Using join buffer
1 by brian
clean slate
583
drop table t1, t2;
512 by Brian Aker
Adding back more test cases.
584
create table t1 (a char);
1 by brian
clean slate
585
insert into t1 values ('a'),('b');
586
show create table t1;
587
Table	Create Table
512 by Brian Aker
Adding back more test cases.
588
t1	CREATE TABLE `t1` (
1743.5.2 by LinuxJedi
Alter many test cases for the new SHOW CREATE TABLE output
589
  `a` VARCHAR(1) COLLATE utf8_general_ci DEFAULT NULL
1638.10.71 by Stewart Smith
func_group.result with explicit COLLATE in SHOW CREATE TABLE
590
) ENGINE=DEFAULT COLLATE = utf8_general_ci
1 by brian
clean slate
591
create table t2 select max(a),min(a) from t1;
592
show create table t2;
593
Table	Create Table
512 by Brian Aker
Adding back more test cases.
594
t2	CREATE TABLE `t2` (
1743.5.2 by LinuxJedi
Alter many test cases for the new SHOW CREATE TABLE output
595
  `max(a)` VARCHAR(1) COLLATE utf8_general_ci DEFAULT NULL,
596
  `min(a)` VARCHAR(1) COLLATE utf8_general_ci DEFAULT NULL
1638.10.71 by Stewart Smith
func_group.result with explicit COLLATE in SHOW CREATE TABLE
597
) ENGINE=DEFAULT COLLATE = utf8_general_ci
1 by brian
clean slate
598
drop table t2;
599
create table t2 select concat(a) from t1;
600
show create table t2;
601
Table	Create Table
512 by Brian Aker
Adding back more test cases.
602
t2	CREATE TABLE `t2` (
1743.5.2 by LinuxJedi
Alter many test cases for the new SHOW CREATE TABLE output
603
  `concat(a)` VARCHAR(1) COLLATE utf8_general_ci DEFAULT NULL
1638.10.71 by Stewart Smith
func_group.result with explicit COLLATE in SHOW CREATE TABLE
604
) ENGINE=DEFAULT COLLATE = utf8_general_ci
1 by brian
clean slate
605
drop table t2,t1;
606
create table t1 (a int);
607
insert into t1 values (1);
608
select max(a) as b from t1 having b=1;
609
b
610
1
611
select a from t1 having a=1;
612
a
613
1
614
drop table t1;
615
create table t1 (a int);
616
select variance(2) from t1;
617
variance(2)
618
NULL
619
select stddev(2) from t1;
620
stddev(2)
621
NULL
622
drop table t1;
623
create table t1 (a int);
624
insert into t1 values (1),(2);
625
SELECT COUNT(*) FROM t1;
626
COUNT(*)
627
2
628
SELECT COUNT(*) FROM t1;
629
COUNT(*)
630
2
631
SELECT COUNT(*) FROM t1;
632
COUNT(*)
633
2
634
drop table t1;
635
create table t1 (a int, primary key(a));
636
insert into t1 values (1),(2);
637
SELECT max(a) FROM t1;
638
max(a)
639
2
640
SELECT max(a) FROM t1;
641
max(a)
642
2
643
SELECT max(a) FROM t1;
644
max(a)
645
2
646
drop table t1;
647
CREATE TABLE t1 (a int primary key);
648
INSERT INTO t1 VALUES (1),(2),(3),(4);
649
SELECT MAX(a) FROM t1 WHERE a > 5;
650
MAX(a)
651
NULL
652
SELECT MIN(a) FROM t1 WHERE a < 0;
653
MIN(a)
654
NULL
655
DROP TABLE t1;
1063.9.3 by Brian Aker
Partial fix for tests for tmp
656
CREATE TEMPORARY TABLE t1 (
512 by Brian Aker
Adding back more test cases.
657
id int NOT NULL auto_increment,
1 by brian
clean slate
658
val enum('one','two','three') NOT NULL default 'one',
659
PRIMARY KEY  (id)
512 by Brian Aker
Adding back more test cases.
660
) ENGINE=MyISAM;
1 by brian
clean slate
661
INSERT INTO t1 VALUES
662
(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
663
select val, count(*) from t1 group by val;
664
val	count(*)
665
one	2
666
two	2
667
three	1
668
drop table t1;
669
create table t1(a int, b datetime);
670
insert into t1 values (1, NOW()), (2, NOW());
671
create table t2 select MAX(b) from t1 group by a;
672
show create table t2;
673
Table	Create Table
512 by Brian Aker
Adding back more test cases.
674
t2	CREATE TABLE `t2` (
1743.5.2 by LinuxJedi
Alter many test cases for the new SHOW CREATE TABLE output
675
  `MAX(b)` DATETIME DEFAULT NULL
1638.10.71 by Stewart Smith
func_group.result with explicit COLLATE in SHOW CREATE TABLE
676
) ENGINE=DEFAULT COLLATE = utf8_general_ci
1 by brian
clean slate
677
drop table t1, t2;
678
create table t1(f1 datetime);
679
insert into t1 values (now());
680
create table t2 select f2 from (select max(now()) f2 from t1) a;
681
show columns from t2;
1309.2.4 by Brian Aker
New version of show columns code.
682
Field	Type	Null	Default	Default_is_NULL	On_Update
1660 by Brian Aker
MErge in change to do YES/NO like standard requires.
683
f2	DATETIME	YES		YES	
1 by brian
clean slate
684
drop table t2;
685
create table t2 select f2 from (select now() f2 from t1) a;
686
show columns from t2;
1309.2.4 by Brian Aker
New version of show columns code.
687
Field	Type	Null	Default	Default_is_NULL	On_Update
1660 by Brian Aker
MErge in change to do YES/NO like standard requires.
688
f2	DATETIME	YES		YES	
1 by brian
clean slate
689
drop table t2, t1;
690
CREATE TABLE t1(
691
id int PRIMARY KEY,
692
a  int,
693
b  int,
694
INDEX i_b_id(a,b,id),
695
INDEX i_id(a,id)
696
);
697
INSERT INTO t1 VALUES 
698
(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
699
SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
700
MAX(id)
701
NULL
702
DROP TABLE t1;
703
CREATE TABLE t1(
704
id int PRIMARY KEY,
705
a  int,
706
b  int,
707
INDEX i_id(a,id),
708
INDEX i_b_id(a,b,id)
709
);
710
INSERT INTO t1 VALUES 
711
(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
712
SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
713
MAX(id)
714
NULL
715
DROP TABLE t1;
716
CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));
717
INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
718
SELECT * FROM t1;
719
id	b
720
1	xx
721
2	aa
722
SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
723
MAX(b)
724
aa
725
SHOW WARNINGS;
726
Level	Code	Message
727
SELECT MAX(b) FROM t1 WHERE b < 'pp';
728
MAX(b)
729
aa
730
DROP TABLE t1;
731
CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4)));
732
INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa');
733
SELECT MAX(b) FROM t1;
734
MAX(b)
735
xxxxbbbb
736
EXPLAIN SELECT MAX(b) FROM t1;
737
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
738
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
739
DROP TABLE t1;
740
CREATE TABLE t1 (a INT, b INT);
741
INSERT INTO t1 VALUES (1,1),(1,2),(2,3);
742
SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
743
(SELECT COUNT(DISTINCT t1.b))
744
2
745
1
746
SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;
747
(SELECT COUNT(DISTINCT 12))
748
1
749
1
512 by Brian Aker
Adding back more test cases.
750
SELECT AVG(2), COUNT(*), COUNT(12),
1 by brian
clean slate
751
COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2),
752
GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);
512 by Brian Aker
Adding back more test cases.
753
AVG(2)	COUNT(*)	COUNT(12)	COUNT(DISTINCT 12)	MIN(2)	MAX(2)	STD(2)	VARIANCE(2)	SUM(2)	GROUP_CONCAT(2)	GROUP_CONCAT(DISTINCT 2)
754
2.00000	1	1	1	2	2	0.00000	0.00000	2	2	2
1 by brian
clean slate
755
DROP TABLE t1;
756
create table t2 (ff double);
757
insert into t2 values (2.2);
758
select cast(sum(distinct ff) as decimal(5,2)) from t2;
759
cast(sum(distinct ff) as decimal(5,2))
760
2.20
512 by Brian Aker
Adding back more test cases.
761
select sum(distinct ff) from t2;
762
sum(distinct ff)
763
2.2
1 by brian
clean slate
764
select cast(variance(ff) as decimal(10,3)) from t2;
765
cast(variance(ff) as decimal(10,3))
766
0.000
767
select cast(min(ff) as decimal(5,2)) from t2;
768
cast(min(ff) as decimal(5,2))
769
2.20
770
create table t1 (df decimal(5,1));
771
insert into t1 values(1.1);
772
insert into t1 values(2.2);
512 by Brian Aker
Adding back more test cases.
773
select sum(distinct df) from t1;
774
sum(distinct df)
775
3.3
776
select min(df) from t1;
777
min(df)
778
1.1
1 by brian
clean slate
779
select 1e8 * sum(distinct df) from t1;
780
1e8 * sum(distinct df)
781
330000000
782
select 1e8 * min(df) from t1;
783
1e8 * min(df)
1108.5.2 by rm
fix test results so they expect valid precision, rather than more than can be relied upon
784
110000000
1 by brian
clean slate
785
create table t3 (ifl int);
786
insert into t3 values(1), (2);
787
select cast(min(ifl) as decimal(5,2)) from t3;
788
cast(min(ifl) as decimal(5,2))
789
1.00
790
drop table t1, t2, t3;
512 by Brian Aker
Adding back more test cases.
791
CREATE TABLE t1 (id int,value1 float(10,2));
1 by brian
clean slate
792
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);
793
select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id;
794
id	stddev_pop(value1)	var_pop(value1)	stddev_samp(value1)	var_samp(value1)
795
1	0.816497	0.666667	1.000000	1.000000
796
2	1.118034	1.250000	1.290994	1.666667
797
DROP TABLE t1;
798
CREATE TABLE t1 (col1 decimal(16,12));
799
INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002);
800
insert into t1 select * from t1;
801
select col1,count(col1),sum(col1),avg(col1) from t1 group by col1;
802
col1	count(col1)	sum(col1)	avg(col1)
803
-5.000000000030	2	-10.000000000060	-5.00000000003000000
804
-5.000000000020	4	-20.000000000080	-5.00000000002000000
805
-5.000000000010	4	-20.000000000040	-5.00000000001000000
806
-5.000000000000	2	-10.000000000000	-5.00000000000000000
807
DROP TABLE t1;
808
create table t1 (col1 decimal(16,12));
809
insert into t1 values (-5.00000000001);
810
insert into t1 values (-5.00000000001);
811
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
812
col1	sum(col1)	max(col1)	min(col1)
813
-5.000000000010	-10.000000000020	-5.000000000010	-5.000000000010
814
delete from t1;
815
insert into t1 values (5.00000000001);
816
insert into t1 values (5.00000000001);
817
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
818
col1	sum(col1)	max(col1)	min(col1)
819
5.000000000010	10.000000000020	5.000000000010	5.000000000010
820
DROP TABLE t1;
821
CREATE TABLE t1 (a VARCHAR(400));
822
INSERT INTO t1 (a) VALUES ("A"), ("a"), ("a "), ("a   "),
823
("B"), ("b"), ("b "), ("b   ");
824
SELECT COUNT(DISTINCT a) FROM t1;
825
COUNT(DISTINCT a)
826
2
827
DROP TABLE t1;
828
CREATE TABLE t1 (a int, b int, c int);
829
INSERT INTO t1 (a, b, c) VALUES
830
(1,1,1), (1,1,2), (1,1,3),
831
(1,2,1), (1,2,2), (1,2,3),
832
(1,3,1), (1,3,2), (1,3,3),
833
(2,1,1), (2,1,2), (2,1,3),
834
(2,2,1), (2,2,2), (2,2,3),
835
(2,3,1), (2,3,2), (2,3,3),
836
(3,1,1), (3,1,2), (3,1,3),
837
(3,2,1), (3,2,2), (3,2,3),
838
(3,3,1), (3,3,2), (3,3,3);
839
SELECT b/c as v, a FROM t1 ORDER BY v;
840
v	a
841
0.33333	1
842
0.33333	2
942.3.1 by Vladimir Kolesnikov
test generalizations
843
0.33333	3
1 by brian
clean slate
844
0.50000	1
845
0.50000	2
846
0.50000	3
942.3.1 by Vladimir Kolesnikov
test generalizations
847
0.66667	1
1 by brian
clean slate
848
0.66667	2
849
0.66667	3
942.3.1 by Vladimir Kolesnikov
test generalizations
850
1.00000	1
851
1.00000	1
852
1.00000	1
853
1.00000	2
854
1.00000	2
855
1.00000	2
856
1.00000	3
857
1.00000	3
858
1.00000	3
859
1.50000	1
860
1.50000	2
1 by brian
clean slate
861
1.50000	3
862
2.00000	1
942.3.1 by Vladimir Kolesnikov
test generalizations
863
2.00000	2
1 by brian
clean slate
864
2.00000	3
942.3.1 by Vladimir Kolesnikov
test generalizations
865
3.00000	1
866
3.00000	2
1 by brian
clean slate
867
3.00000	3
868
SELECT b/c as v, SUM(a) FROM t1 GROUP BY v;
869
v	SUM(a)
1865.1.2 by Andrew Hutchings
Fix test case that was also showing this bug
870
0.33333	6
1 by brian
clean slate
871
0.50000	6
1865.1.2 by Andrew Hutchings
Fix test case that was also showing this bug
872
0.66667	6
1 by brian
clean slate
873
1.00000	18
874
1.50000	6
875
2.00000	6
876
3.00000	6
877
SELECT SUM(a) FROM t1 GROUP BY b/c;
878
SUM(a)
879
6
1865.1.2 by Andrew Hutchings
Fix test case that was also showing this bug
880
6
881
6
1 by brian
clean slate
882
18
883
6
884
6
885
6
886
DROP TABLE t1;
887
set div_precision_increment= @sav_dpi;
888
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
889
INSERT INTO t1 VALUES (1,1), (2,2);
890
CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
891
INSERT INTO t2 VALUES (1,1), (3,3);
512 by Brian Aker
Adding back more test cases.
892
SELECT 
1 by brian
clean slate
893
(SELECT SUM(c.a) FROM t1 ttt, t2 ccc 
894
WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid   
895
FROM t1 t, t2 c WHERE t.a = c.b;
896
minid
512 by Brian Aker
Adding back more test cases.
897
NULL
1 by brian
clean slate
898
DROP TABLE t1,t2;
899
create table t1 select variance(0);
900
show create table t1;
901
Table	Create Table
512 by Brian Aker
Adding back more test cases.
902
t1	CREATE TABLE `t1` (
1743.5.2 by LinuxJedi
Alter many test cases for the new SHOW CREATE TABLE output
903
  `variance(0)` DOUBLE(8,4) DEFAULT NULL
1638.10.71 by Stewart Smith
func_group.result with explicit COLLATE in SHOW CREATE TABLE
904
) ENGINE=DEFAULT COLLATE = utf8_general_ci
1 by brian
clean slate
905
drop table t1;
906
create table t1 select stddev(0);
907
show create table t1;
908
Table	Create Table
512 by Brian Aker
Adding back more test cases.
909
t1	CREATE TABLE `t1` (
1743.5.2 by LinuxJedi
Alter many test cases for the new SHOW CREATE TABLE output
910
  `stddev(0)` DOUBLE(8,4) DEFAULT NULL
1638.10.71 by Stewart Smith
func_group.result with explicit COLLATE in SHOW CREATE TABLE
911
) ENGINE=DEFAULT COLLATE = utf8_general_ci
1 by brian
clean slate
912
drop table t1;
512 by Brian Aker
Adding back more test cases.
913
create table bug22555 (i int primary key auto_increment, s1 int, s2 int, e decimal(30,10), o double);
1 by brian
clean slate
914
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);
915
select std(s1/s2) from bug22555 group by i;
916
std(s1/s2)
917
0.00000000
918
0.00000000
919
0.00000000
920
0.00000000
921
0.00000000
922
0.00000000
923
0.00000000
924
0.00000000
925
0.00000000
926
0.00000000
927
select std(e) from bug22555 group by i;
928
std(e)
929
0.00000000000000
930
0.00000000000000
931
0.00000000000000
932
0.00000000000000
933
0.00000000000000
934
0.00000000000000
935
0.00000000000000
936
0.00000000000000
937
0.00000000000000
938
0.00000000000000
939
select std(o) from bug22555 group by i;
940
std(o)
941
0
942
0
943
0
944
0
945
0
946
0
947
0
948
0
949
0
950
0
951
drop table bug22555;
512 by Brian Aker
Adding back more test cases.
952
create table bug22555 (i int, s1 int, s2 int, o1 double, o2 double, e1 decimal, e2 decimal);
1 by brian
clean slate
953
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);
954
select i, count(*) from bug22555 group by i;
955
i	count(*)
956
1	1
957
2	1
958
3	1
959
select std(s1/s2) from bug22555 where i=1;
960
std(s1/s2)
961
0.00000000
962
select std(s1/s2) from bug22555 where i=2;
963
std(s1/s2)
964
0.00000000
965
select std(s1/s2) from bug22555 where i=3;
966
std(s1/s2)
967
0.00000000
968
select std(s1/s2) from bug22555 where i=1 group by i;
969
std(s1/s2)
970
0.00000000
971
select std(s1/s2) from bug22555 where i=2 group by i;
972
std(s1/s2)
973
0.00000000
974
select std(s1/s2) from bug22555 where i=3 group by i;
975
std(s1/s2)
976
0.00000000
977
select std(s1/s2) from bug22555 group by i order by i;
978
std(s1/s2)
979
0.00000000
980
0.00000000
981
0.00000000
982
select i, count(*), std(o1/o2) from bug22555 group by i order by i;
983
i	count(*)	std(o1/o2)
984
1	1	0
985
2	1	0
986
3	1	0
987
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
988
i	count(*)	std(e1/e2)
989
1	1	0.00000000
990
2	1	0.00000000
991
3	1	0.00000000
992
set @saved_div_precision_increment=@@div_precision_increment;
993
set div_precision_increment=19;
994
select i, count(*), variance(s1/s2) from bug22555 group by i order by i;
995
i	count(*)	variance(s1/s2)
996
1	1	0.000000000000000000000000000000
997
2	1	0.000000000000000000000000000000
998
3	1	0.000000000000000000000000000000
999
select i, count(*), variance(o1/o2) from bug22555 group by i order by i;
1000
i	count(*)	variance(o1/o2)
1001
1	1	0
1002
2	1	0
1003
3	1	0
1004
select i, count(*), variance(e1/e2) from bug22555 group by i order by i;
1005
i	count(*)	variance(e1/e2)
1006
1	1	0.000000000000000000000000000000
1007
2	1	0.000000000000000000000000000000
1008
3	1	0.000000000000000000000000000000
1009
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
1010
i	count(*)	std(s1/s2)
1011
1	1	0.000000000000000000000000000000
1012
2	1	0.000000000000000000000000000000
1013
3	1	0.000000000000000000000000000000
1014
select i, count(*), std(o1/o2) from bug22555 group by i order by i;
1015
i	count(*)	std(o1/o2)
1016
1	1	0
1017
2	1	0
1018
3	1	0
1019
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
1020
i	count(*)	std(e1/e2)
1021
1	1	0.000000000000000000000000000000
1022
2	1	0.000000000000000000000000000000
1023
3	1	0.000000000000000000000000000000
1024
set div_precision_increment=20;
1025
select i, count(*), variance(s1/s2) from bug22555 group by i order by i;
1026
i	count(*)	variance(s1/s2)
1027
1	1	0.000000000000000000000000000000
1028
2	1	0.000000000000000000000000000000
1029
3	1	0.000000000000000000000000000000
1030
select i, count(*), variance(o1/o2) from bug22555 group by i order by i;
1031
i	count(*)	variance(o1/o2)
1032
1	1	0
1033
2	1	0
1034
3	1	0
1035
select i, count(*), variance(e1/e2) from bug22555 group by i order by i;
1036
i	count(*)	variance(e1/e2)
1037
1	1	0.000000000000000000000000000000
1038
2	1	0.000000000000000000000000000000
1039
3	1	0.000000000000000000000000000000
1040
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
1041
i	count(*)	std(s1/s2)
1042
1	1	0.000000000000000000000000000000
1043
2	1	0.000000000000000000000000000000
1044
3	1	0.000000000000000000000000000000
1045
select i, count(*), std(o1/o2) from bug22555 group by i order by i;
1046
i	count(*)	std(o1/o2)
1047
1	1	0
1048
2	1	0
1049
3	1	0
1050
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
1051
i	count(*)	std(e1/e2)
1052
1	1	0.000000000000000000000000000000
1053
2	1	0.000000000000000000000000000000
1054
3	1	0.000000000000000000000000000000
1055
set @@div_precision_increment=@saved_div_precision_increment;
1056
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);
1057
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);
1058
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);
1059
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
1060
i	count(*)	std(s1/s2)
1061
1	4	0.00000000
1062
2	4	0.00000000
1063
3	4	0.00000000
1064
select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
1065
i	count(*)	round(std(o1/o2), 16)
1066
1	4	0.0000000000000000
1067
2	4	0.0000000000000000
1068
3	4	0.0000000000000000
1069
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
1070
i	count(*)	std(e1/e2)
1071
1	4	0.00000000
1072
2	4	0.00000000
1073
3	4	0.00000000
1074
select std(s1/s2) from bug22555;
1075
std(s1/s2)
1076
0.21325764
1077
select std(o1/o2) from bug22555;
1078
std(o1/o2)
1108.5.2 by rm
fix test results so they expect valid precision, rather than more than can be relied upon
1079
0.213257635866493
1 by brian
clean slate
1080
select std(e1/e2) from bug22555;
1081
std(e1/e2)
1082
0.21325764
1083
set @saved_div_precision_increment=@@div_precision_increment;
1084
set div_precision_increment=19;
1085
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
1086
i	count(*)	std(s1/s2)
1087
1	4	0.000000000000000000000000000000
1088
2	4	0.000000000000000000000000000000
1089
3	4	0.000000000000000000000000000000
1090
select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
1091
i	count(*)	round(std(o1/o2), 16)
1092
1	4	0.0000000000000000
1093
2	4	0.0000000000000000
1094
3	4	0.0000000000000000
1095
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
1096
i	count(*)	std(e1/e2)
1097
1	4	0.000000000000000000000000000000
1098
2	4	0.000000000000000000000000000000
1099
3	4	0.000000000000000000000000000000
1100
select round(std(s1/s2), 17) from bug22555;
1101
round(std(s1/s2), 17)
1102
0.21325763586649340
1103
select std(o1/o2) from bug22555;
1104
std(o1/o2)
1108.5.2 by rm
fix test results so they expect valid precision, rather than more than can be relied upon
1105
0.213257635866493
1 by brian
clean slate
1106
select round(std(e1/e2), 17) from bug22555;
1107
round(std(e1/e2), 17)
1108
0.21325763586649340
1109
set div_precision_increment=20;
1110
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
1111
i	count(*)	std(s1/s2)
1112
1	4	0.000000000000000000000000000000
1113
2	4	0.000000000000000000000000000000
1114
3	4	0.000000000000000000000000000000
1115
select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
1116
i	count(*)	round(std(o1/o2), 16)
1117
1	4	0.0000000000000000
1118
2	4	0.0000000000000000
1119
3	4	0.0000000000000000
1120
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
1121
i	count(*)	std(e1/e2)
1122
1	4	0.000000000000000000000000000000
1123
2	4	0.000000000000000000000000000000
1124
3	4	0.000000000000000000000000000000
1125
select round(std(s1/s2), 17) from bug22555;
1126
round(std(s1/s2), 17)
1127
0.21325763586649340
1128
select std(o1/o2) from bug22555;
1129
std(o1/o2)
1108.5.2 by rm
fix test results so they expect valid precision, rather than more than can be relied upon
1130
0.213257635866493
1 by brian
clean slate
1131
select round(std(e1/e2), 17) from bug22555;
1132
round(std(e1/e2), 17)
1133
0.21325763586649340
1134
set @@div_precision_increment=@saved_div_precision_increment;
1135
drop table bug22555;
512 by Brian Aker
Adding back more test cases.
1136
create table bug22555 (s int, o double, e decimal);
1 by brian
clean slate
1137
insert into bug22555 values (1,1,1),(2,2,2),(3,3,3),(6,6,6),(7,7,7);
1138
select var_samp(s), var_pop(s) from bug22555;
1139
var_samp(s)	var_pop(s)
1140
6.7000	5.3600
1141
select var_samp(o), var_pop(o) from bug22555;
1142
var_samp(o)	var_pop(o)
1143
6.7	5.36
1144
select var_samp(e), var_pop(e) from bug22555;
1145
var_samp(e)	var_pop(e)
1146
6.7000	5.3600
1147
drop table bug22555;
512 by Brian Aker
Adding back more test cases.
1148
create table bug22555 (s int, o double, e decimal);
1 by brian
clean slate
1149
insert into bug22555 values (null,null,null),(null,null,null);
1150
select var_samp(s) as 'null', var_pop(s) as 'null' from bug22555;
1151
null	null
1152
NULL	NULL
1153
select var_samp(o) as 'null', var_pop(o) as 'null' from bug22555;
1154
null	null
1155
NULL	NULL
1156
select var_samp(e) as 'null', var_pop(e) as 'null' from bug22555;
1157
null	null
1158
NULL	NULL
1159
insert into bug22555 values (1,1,1);
1160
select var_samp(s) as 'null', var_pop(s) as '0' from bug22555;
1161
null	0
1162
NULL	0.0000
1163
select var_samp(o) as 'null', var_pop(o) as '0' from bug22555;
1164
null	0
1165
NULL	0
1166
select var_samp(e) as 'null', var_pop(e) as '0' from bug22555;
1167
null	0
1168
NULL	0.0000
1169
insert into bug22555 values (2,2,2);
1170
select var_samp(s) as '0.5', var_pop(s) as '0.25' from bug22555;
1171
0.5	0.25
1172
0.5000	0.2500
1173
select var_samp(o) as '0.5', var_pop(o) as '0.25' from bug22555;
1174
0.5	0.25
1175
0.5	0.25
1176
select var_samp(e) as '0.5', var_pop(e) as '0.25' from bug22555;
1177
0.5	0.25
1178
0.5000	0.2500
1179
drop table bug22555;
1180
create table t1 (a decimal(20));
1181
insert into t1 values (12345678901234567890);
1182
select count(a) from t1;
1183
count(a)
1184
1
1185
select count(distinct a) from t1;
1186
count(distinct a)
1187
1
1188
drop table t1;
1189
CREATE TABLE t1 (a INT, b INT);
1190
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
1191
INSERT INTO t1 SELECT a, b+8       FROM t1;
1192
INSERT INTO t1 SELECT a, b+16      FROM t1;
1193
INSERT INTO t1 SELECT a, b+32      FROM t1;
1194
INSERT INTO t1 SELECT a, b+64      FROM t1;
1195
INSERT INTO t1 SELECT a, b+128     FROM t1;
1196
INSERT INTO t1 SELECT a, b+256     FROM t1;
1197
INSERT INTO t1 SELECT a, b+512     FROM t1;
1198
INSERT INTO t1 SELECT a, b+1024    FROM t1;
1199
INSERT INTO t1 SELECT a, b+2048    FROM t1;
1200
INSERT INTO t1 SELECT a, b+4096    FROM t1;
1201
INSERT INTO t1 SELECT a, b+8192    FROM t1;
1202
INSERT INTO t1 SELECT a, b+16384   FROM t1;
1203
INSERT INTO t1 SELECT a, b+32768   FROM t1;
1204
SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50;
1205
a	cnt
1206
1	65536
1207
SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50;
1208
a	sumation
1209
1	2147516416
1210
SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
1211
a	average
1212
1	32768.5000
1213
DROP TABLE t1;
1214
CREATE TABLE t1 ( a INT, b INT, KEY(a) );
1215
INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
1216
EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
1217
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1218
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1219
SELECT MIN(a), MIN(b) FROM t1;
1220
MIN(a)	MIN(b)
1221
NULL	1
1222
CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
1223
INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
1224
EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
1225
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1819.9.66 by Stewart Smith
EXPLAIN in test result changes due to modified InnoDB stats
1226
1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	3	Using where
1 by brian
clean slate
1227
SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
1228
MIN(b)	MIN(c)
1229
3	2
1230
CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
1231
INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2),  (2, NULL, 2),  (3, NULL, 3);
1232
EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
1233
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1234
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
1235
SELECT MIN(a), MIN(b) FROM t3 where a = 2;
1236
MIN(a)	MIN(b)
1237
2	NULL
1238
CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
1239
INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2),  (2, NULL, 2),  (3, 1, 3);
1240
EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
1241
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1242
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
1243
SELECT MIN(a), MIN(b) FROM t4 where a = 2;
1244
MIN(a)	MIN(b)
1245
2	NULL
1246
SELECT MIN(b), min(c) FROM t4 where a = 2;
1247
MIN(b)	min(c)
1248
NULL	2
1249
CREATE TABLE t5( a INT, b INT, KEY( a, b) );
1250
INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
1251
EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
1252
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1253
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
1254
SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
1255
MIN(a)	MIN(b)
1256
1	1
1257
SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
1258
MIN(a)	MIN(b)
1259
1	2
1260
DROP TABLE t1, t2, t3, t4, t5;
1261
CREATE TABLE t1 (a int, b date NOT NULL, KEY k1 (a,b));
1262
SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01';
1263
MIN(b)
1264
NULL
1265
DROP TABLE t1;
1266
CREATE TABLE t1(a DOUBLE);
1267
INSERT INTO t1 VALUES (10), (20);
1268
SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1;
1269
AVG(a)	CAST(AVG(a) AS DECIMAL)
1270
15	15
1271
DROP TABLE t1;
1272
End of 5.0 tests