« back to all changes in this revision
Viewing changes to tests/r/pbxt/func_group.result
- browse files at revision 1455.3.1
- compare with another revision
- compare with revision 1089
- download diff
- download tarball
- view history from revision 1455.3.1
- Committer: Vladimir Kolesnikov
- Date: 2010-04-08 16:11:29 UTC
- mto: (1457.1.1 bad-staging) (1455.3.13 drizzle-pbxt-6)
- mto: This revision was merged to the branch mainline in revision 1462.
- Revision ID: vladimir@primebase.org-20100408161129-x85uvbu9qavjck66
lp:drizzle + pbxt 1.1 + test results
- files added:
- plugin/pbxt
- plugin/pbxt/bin
- plugin/pbxt/src
- tests/r/pbxt
- files modified:
- drizzled/sql_table.cc
added
removed
tests/r/pbxt/func_group.result
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
7
create table t1 (grp int, a bigint, c char(10) not null);
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
50
create table t2 (grp int, a bigint, c char(10));
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;
61
CREATE TABLE t1 (id int,value1 float(10,2));
62
INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00);
63
CREATE TABLE t2 (id int,name char(20));
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;
104
create table t1 (a int primary key, c char(10), b text);
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
195
10 aaa AAA 10
196
10 aaa BBB 20
197
10 NULL AAA 10
198
10 NULL 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,
241
a2 int,
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
282
BDL 0 DEN 0.08 1960-11-27
283
BMC 3 SEA 0.085 1958-09-08
284
BOT 2 SEA 0.085 1951-11-29
285
DTX 1 NYC 0.08 1961-05-04
286
GTM 3 DAL 0.07 1977-09-23
287
HBR 1 SEA 0.085 1948-03-05
288
KKK 3 ATL NULL NULL
289
PLS 1 WDC 0.075 1949-01-02
290
SSJ NULL CHI NULL 1974-03-19
291
TWU 0 LAX 0.08 1969-10-05
292
VVV 2 MIN 0.075 1959-06-28
293
WWW 1 LED NULL NULL
294
XXX NULL MIN NULL NULL
295
ZAJ 2 CHI 0.075 1960-06-15
296
select * from t2;
297
a1 a2 a3 a4
298
AAA AAA AA AME
299
DEN Denver CO BDL
300
LAK Los Angeles CA TWU
301
LCC Los Angeles CA TWU
302
NOL New Orleans LA GTM
303
SDC San Diego CA TWU
304
TKF Seattle WA AME
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
532
1 SIMPLE t1 range PRIMARY PRIMARY 0 NULL 1 Using where; Using index
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
536
1 SIMPLE t1 index PRIMARY PRIMARY 14 NULL 15 Using where; Using index
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
540
1 SIMPLE t1 range k1 k1 5 NULL 1 Using where; Using index
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
544
1 SIMPLE t1 range k1 k1 20 NULL 1 Using where; Using index
545
1 SIMPLE t2 range k1 k1 11 NULL 1 Using where; Using index; Using join buffer
546
explain
547
select min(a4 - 0.01) from t1;
548
id select_type table type possible_keys key key_len ref rows Extra
549
1 SIMPLE t1 index NULL k2 23 NULL 15 Using index
550
explain
551
select max(a4 + 0.01) from t1;
552
id select_type table type possible_keys key key_len ref rows Extra
553
1 SIMPLE t1 index NULL k2 23 NULL 15 Using index
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
557
1 SIMPLE t1 index NULL k1 20 NULL 15 Using where; Using index
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
561
1 SIMPLE t1 index NULL k1 20 NULL 15 Using where; Using index
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
565
1 SIMPLE t1 index NULL k1 20 NULL 15 Using where; Using index
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
569
1 SIMPLE t1 index NULL k1 20 NULL 15 Using where; Using index
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
573
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 15 Using where
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
577
1 SIMPLE t1 index NULL k2 23 NULL 15 Using where; Using index
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
581
1 SIMPLE t2 index k2 k2 15 NULL 7 Using where; Using index
582
1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using index; Using join buffer
583
drop table t1, t2;
584
create table t1 (a char(10));
585
insert into t1 values ('a'),('b'),('c');
586
select coercibility(max(a)) from t1;
587
coercibility(max(a))
588
2
589
drop table t1;
590
create table t1 (a char);
591
insert into t1 values ('a'),('b');
592
show create table t1;
593
Table Create Table
594
t1 CREATE TABLE `t1` (
595
`a` varchar(1) DEFAULT NULL
596
) ENGINE=DEFAULT
597
create table t2 select max(a),min(a) from t1;
598
show create table t2;
599
Table Create Table
600
t2 CREATE TABLE `t2` (
601
`max(a)` varchar(1) DEFAULT NULL,
602
`min(a)` varchar(1) DEFAULT NULL
603
) ENGINE=DEFAULT
604
drop table t2;
605
create table t2 select concat(a) from t1;
606
show create table t2;
607
Table Create Table
608
t2 CREATE TABLE `t2` (
609
`concat(a)` varchar(1) DEFAULT NULL
610
) ENGINE=DEFAULT
611
drop table t2,t1;
612
create table t1 (a int);
613
insert into t1 values (1);
614
select max(a) as b from t1 having b=1;
615
b
616
1
617
select a from t1 having a=1;
618
a
619
1
620
drop table t1;
621
create table t1 (a int);
622
select variance(2) from t1;
623
variance(2)
624
NULL
625
select stddev(2) from t1;
626
stddev(2)
627
NULL
628
drop table t1;
629
create table t1 (a int);
630
insert into t1 values (1),(2);
631
SELECT COUNT(*) FROM t1;
632
COUNT(*)
633
2
634
SELECT COUNT(*) FROM t1;
635
COUNT(*)
636
2
637
SELECT COUNT(*) FROM t1;
638
COUNT(*)
639
2
640
drop table t1;
641
create table t1 (a int, primary key(a));
642
insert into t1 values (1),(2);
643
SELECT max(a) FROM t1;
644
max(a)
645
2
646
SELECT max(a) FROM t1;
647
max(a)
648
2
649
SELECT max(a) FROM t1;
650
max(a)
651
2
652
drop table t1;
653
CREATE TABLE t1 (a int primary key);
654
INSERT INTO t1 VALUES (1),(2),(3),(4);
655
SELECT MAX(a) FROM t1 WHERE a > 5;
656
MAX(a)
657
NULL
658
SELECT MIN(a) FROM t1 WHERE a < 0;
659
MIN(a)
660
NULL
661
DROP TABLE t1;
662
CREATE TEMPORARY TABLE t1 (
663
id int NOT NULL auto_increment,
664
val enum('one','two','three') NOT NULL default 'one',
665
PRIMARY KEY (id)
666
) ENGINE=MyISAM;
667
INSERT INTO t1 VALUES
668
(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
669
select val, count(*) from t1 group by val;
670
val count(*)
671
one 2
672
two 2
673
three 1
674
drop table t1;
675
create table t1(a int, b datetime);
676
insert into t1 values (1, NOW()), (2, NOW());
677
create table t2 select MAX(b) from t1 group by a;
678
show create table t2;
679
Table Create Table
680
t2 CREATE TABLE `t2` (
681
`MAX(b)` datetime DEFAULT NULL
682
) ENGINE=DEFAULT
683
drop table t1, t2;
684
create table t1(f1 datetime);
685
insert into t1 values (now());
686
create table t2 select f2 from (select max(now()) f2 from t1) a;
687
show columns from t2;
688
Field Type Null Key Default Extra
689
f2 datetime YES NULL
690
drop table t2;
691
create table t2 select f2 from (select now() f2 from t1) a;
692
show columns from t2;
693
Field Type Null Key Default Extra
694
f2 datetime YES NULL
695
drop table t2, t1;
696
CREATE TABLE t1(
697
id int PRIMARY KEY,
698
a int,
699
b int,
700
INDEX i_b_id(a,b,id),
701
INDEX i_id(a,id)
702
);
703
INSERT INTO t1 VALUES
704
(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
705
SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
706
MAX(id)
707
NULL
708
DROP TABLE t1;
709
CREATE TABLE t1(
710
id int PRIMARY KEY,
711
a int,
712
b int,
713
INDEX i_id(a,id),
714
INDEX i_b_id(a,b,id)
715
);
716
INSERT INTO t1 VALUES
717
(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
718
SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
719
MAX(id)
720
NULL
721
DROP TABLE t1;
722
CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));
723
INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
724
SELECT * FROM t1;
725
id b
726
1 xx
727
2 aa
728
SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
729
MAX(b)
730
aa
731
SHOW WARNINGS;
732
Level Code Message
733
SELECT MAX(b) FROM t1 WHERE b < 'pp';
734
MAX(b)
735
aa
736
DROP TABLE t1;
737
CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4)));
738
INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa');
739
SELECT MAX(b) FROM t1;
740
MAX(b)
741
xxxxbbbb
742
EXPLAIN SELECT MAX(b) FROM t1;
743
id select_type table type possible_keys key key_len ref rows Extra
744
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
745
DROP TABLE t1;
746
CREATE TABLE t1 (a INT, b INT);
747
INSERT INTO t1 VALUES (1,1),(1,2),(2,3);
748
SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
749
(SELECT COUNT(DISTINCT t1.b))
750
2
751
1
752
SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;
753
(SELECT COUNT(DISTINCT 12))
754
1
755
1
756
SELECT AVG(2), COUNT(*), COUNT(12),
757
COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2),
758
GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);
759
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)
760
2.00000 1 1 1 2 2 0.00000 0.00000 2 2 2
761
DROP TABLE t1;
762
create table t2 (ff double);
763
insert into t2 values (2.2);
764
select cast(sum(distinct ff) as decimal(5,2)) from t2;
765
cast(sum(distinct ff) as decimal(5,2))
766
2.20
767
select sum(distinct ff) from t2;
768
sum(distinct ff)
769
2.2
770
select cast(variance(ff) as decimal(10,3)) from t2;
771
cast(variance(ff) as decimal(10,3))
772
0.000
773
select cast(min(ff) as decimal(5,2)) from t2;
774
cast(min(ff) as decimal(5,2))
775
2.20
776
create table t1 (df decimal(5,1));
777
insert into t1 values(1.1);
778
insert into t1 values(2.2);
779
select sum(distinct df) from t1;
780
sum(distinct df)
781
3.3
782
select min(df) from t1;
783
min(df)
784
1.1
785
select 1e8 * sum(distinct df) from t1;
786
1e8 * sum(distinct df)
787
330000000
788
select 1e8 * min(df) from t1;
789
1e8 * min(df)
790
110000000
791
create table t3 (ifl int);
792
insert into t3 values(1), (2);
793
select cast(min(ifl) as decimal(5,2)) from t3;
794
cast(min(ifl) as decimal(5,2))
795
1.00
796
drop table t1, t2, t3;
797
CREATE TABLE t1 (id int,value1 float(10,2));
798
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);
799
select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id;
800
id stddev_pop(value1) var_pop(value1) stddev_samp(value1) var_samp(value1)
801
1 0.816497 0.666667 1.000000 1.000000
802
2 1.118034 1.250000 1.290994 1.666667
803
DROP TABLE t1;
804
CREATE TABLE t1 (col1 decimal(16,12));
805
INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002);
806
insert into t1 select * from t1;
807
select col1,count(col1),sum(col1),avg(col1) from t1 group by col1;
808
col1 count(col1) sum(col1) avg(col1)
809
-5.000000000030 2 -10.000000000060 -5.00000000003000000
810
-5.000000000020 4 -20.000000000080 -5.00000000002000000
811
-5.000000000010 4 -20.000000000040 -5.00000000001000000
812
-5.000000000000 2 -10.000000000000 -5.00000000000000000
813
DROP TABLE t1;
814
create table t1 (col1 decimal(16,12));
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
delete from t1;
821
insert into t1 values (5.00000000001);
822
insert into t1 values (5.00000000001);
823
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
824
col1 sum(col1) max(col1) min(col1)
825
5.000000000010 10.000000000020 5.000000000010 5.000000000010
826
DROP TABLE t1;
827
CREATE TABLE t1 (a VARCHAR(400));
828
INSERT INTO t1 (a) VALUES ("A"), ("a"), ("a "), ("a "),
829
("B"), ("b"), ("b "), ("b ");
830
SELECT COUNT(DISTINCT a) FROM t1;
831
COUNT(DISTINCT a)
832
2
833
DROP TABLE t1;
834
CREATE TABLE t1 (a int, b int, c int);
835
INSERT INTO t1 (a, b, c) VALUES
836
(1,1,1), (1,1,2), (1,1,3),
837
(1,2,1), (1,2,2), (1,2,3),
838
(1,3,1), (1,3,2), (1,3,3),
839
(2,1,1), (2,1,2), (2,1,3),
840
(2,2,1), (2,2,2), (2,2,3),
841
(2,3,1), (2,3,2), (2,3,3),
842
(3,1,1), (3,1,2), (3,1,3),
843
(3,2,1), (3,2,2), (3,2,3),
844
(3,3,1), (3,3,2), (3,3,3);
845
SELECT b/c as v, a FROM t1 ORDER BY v;
846
v a
847
0.33333 1
848
0.33333 2
849
0.33333 3
850
0.50000 1
851
0.50000 2
852
0.50000 3
853
0.66667 1
854
0.66667 2
855
0.66667 3
856
1.00000 1
857
1.00000 1
858
1.00000 1
859
1.00000 2
860
1.00000 2
861
1.00000 2
862
1.00000 3
863
1.00000 3
864
1.00000 3
865
1.50000 1
866
1.50000 2
867
1.50000 3
868
2.00000 1
869
2.00000 2
870
2.00000 3
871
3.00000 1
872
3.00000 2
873
3.00000 3
874
SELECT b/c as v, SUM(a) FROM t1 GROUP BY v;
875
v SUM(a)
876
0.50000 6
877
1.00000 18
878
1.50000 6
879
2.00000 6
880
3.00000 6
881
9999999999.99999 12
882
SELECT SUM(a) FROM t1 GROUP BY b/c;
883
SUM(a)
884
6
885
18
886
6
887
6
888
6
889
12
890
DROP TABLE t1;
891
set div_precision_increment= @sav_dpi;
892
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
893
INSERT INTO t1 VALUES (1,1), (2,2);
894
CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
895
INSERT INTO t2 VALUES (1,1), (3,3);
896
SELECT
897
(SELECT SUM(c.a) FROM t1 ttt, t2 ccc
898
WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
899
FROM t1 t, t2 c WHERE t.a = c.b;
900
minid
901
1
902
DROP TABLE t1,t2;
903
create table t1 select variance(0);
904
show create table t1;
905
Table Create Table
906
t1 CREATE TABLE `t1` (
907
`variance(0)` double(8,4) DEFAULT NULL
908
) ENGINE=DEFAULT
909
drop table t1;
910
create table t1 select stddev(0);
911
show create table t1;
912
Table Create Table
913
t1 CREATE TABLE `t1` (
914
`stddev(0)` double(8,4) DEFAULT NULL
915
) ENGINE=DEFAULT
916
drop table t1;
917
create table bug22555 (i int primary key auto_increment, s1 int, s2 int, e decimal(30,10), o double);
918
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);
919
select std(s1/s2) from bug22555 group by i;
920
std(s1/s2)
921
0.00000000
922
0.00000000
923
0.00000000
924
0.00000000
925
0.00000000
926
0.00000000
927
0.00000000
928
0.00000000
929
0.00000000
930
0.00000000
931
select std(e) from bug22555 group by i;
932
std(e)
933
0.00000000000000
934
0.00000000000000
935
0.00000000000000
936
0.00000000000000
937
0.00000000000000
938
0.00000000000000
939
0.00000000000000
940
0.00000000000000
941
0.00000000000000
942
0.00000000000000
943
select std(o) from bug22555 group by i;
944
std(o)
945
0
946
0
947
0
948
0
949
0
950
0
951
0
952
0
953
0
954
0
955
drop table bug22555;
956
create table bug22555 (i int, s1 int, s2 int, o1 double, o2 double, e1 decimal, e2 decimal);
957
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);
958
select i, count(*) from bug22555 group by i;
959
i count(*)
960
1 1
961
2 1
962
3 1
963
select std(s1/s2) from bug22555 where i=1;
964
std(s1/s2)
965
0.00000000
966
select std(s1/s2) from bug22555 where i=2;
967
std(s1/s2)
968
0.00000000
969
select std(s1/s2) from bug22555 where i=3;
970
std(s1/s2)
971
0.00000000
972
select std(s1/s2) from bug22555 where i=1 group by i;
973
std(s1/s2)
974
0.00000000
975
select std(s1/s2) from bug22555 where i=2 group by i;
976
std(s1/s2)
977
0.00000000
978
select std(s1/s2) from bug22555 where i=3 group by i;
979
std(s1/s2)
980
0.00000000
981
select std(s1/s2) from bug22555 group by i order by i;
982
std(s1/s2)
983
0.00000000
984
0.00000000
985
0.00000000
986
select i, count(*), std(o1/o2) from bug22555 group by i order by i;
987
i count(*) std(o1/o2)
988
1 1 0
989
2 1 0
990
3 1 0
991
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
992
i count(*) std(e1/e2)
993
1 1 0.00000000
994
2 1 0.00000000
995
3 1 0.00000000
996
set @saved_div_precision_increment=@@div_precision_increment;
997
set div_precision_increment=19;
998
select i, count(*), variance(s1/s2) from bug22555 group by i order by i;
999
i count(*) variance(s1/s2)
1000
1 1 0.000000000000000000000000000000
1001
2 1 0.000000000000000000000000000000
1002
3 1 0.000000000000000000000000000000
1003
select i, count(*), variance(o1/o2) from bug22555 group by i order by i;
1004
i count(*) variance(o1/o2)
1005
1 1 0
1006
2 1 0
1007
3 1 0
1008
select i, count(*), variance(e1/e2) from bug22555 group by i order by i;
1009
i count(*) variance(e1/e2)
1010
1 1 0.000000000000000000000000000000
1011
2 1 0.000000000000000000000000000000
1012
3 1 0.000000000000000000000000000000
1013
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
1014
i count(*) std(s1/s2)
1015
1 1 0.000000000000000000000000000000
1016
2 1 0.000000000000000000000000000000
1017
3 1 0.000000000000000000000000000000
1018
select i, count(*), std(o1/o2) from bug22555 group by i order by i;
1019
i count(*) std(o1/o2)
1020
1 1 0
1021
2 1 0
1022
3 1 0
1023
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
1024
i count(*) std(e1/e2)
1025
1 1 0.000000000000000000000000000000
1026
2 1 0.000000000000000000000000000000
1027
3 1 0.000000000000000000000000000000
1028
set div_precision_increment=20;
1029
select i, count(*), variance(s1/s2) from bug22555 group by i order by i;
1030
i count(*) variance(s1/s2)
1031
1 1 0.000000000000000000000000000000
1032
2 1 0.000000000000000000000000000000
1033
3 1 0.000000000000000000000000000000
1034
select i, count(*), variance(o1/o2) from bug22555 group by i order by i;
1035
i count(*) variance(o1/o2)
1036
1 1 0
1037
2 1 0
1038
3 1 0
1039
select i, count(*), variance(e1/e2) from bug22555 group by i order by i;
1040
i count(*) variance(e1/e2)
1041
1 1 0.000000000000000000000000000000
1042
2 1 0.000000000000000000000000000000
1043
3 1 0.000000000000000000000000000000
1044
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
1045
i count(*) std(s1/s2)
1046
1 1 0.000000000000000000000000000000
1047
2 1 0.000000000000000000000000000000
1048
3 1 0.000000000000000000000000000000
1049
select i, count(*), std(o1/o2) from bug22555 group by i order by i;
1050
i count(*) std(o1/o2)
1051
1 1 0
1052
2 1 0
1053
3 1 0
1054
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
1055
i count(*) std(e1/e2)
1056
1 1 0.000000000000000000000000000000
1057
2 1 0.000000000000000000000000000000
1058
3 1 0.000000000000000000000000000000
1059
set @@div_precision_increment=@saved_div_precision_increment;
1060
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);
1061
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);
1062
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);
1063
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
1064
i count(*) std(s1/s2)
1065
1 4 0.00000000
1066
2 4 0.00000000
1067
3 4 0.00000000
1068
select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
1069
i count(*) round(std(o1/o2), 16)
1070
1 4 0.0000000000000000
1071
2 4 0.0000000000000000
1072
3 4 0.0000000000000000
1073
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
1074
i count(*) std(e1/e2)
1075
1 4 0.00000000
1076
2 4 0.00000000
1077
3 4 0.00000000
1078
select std(s1/s2) from bug22555;
1079
std(s1/s2)
1080
0.21325764
1081
select std(o1/o2) from bug22555;
1082
std(o1/o2)
1083
0.213257635866493
1084
select std(e1/e2) from bug22555;
1085
std(e1/e2)
1086
0.21325764
1087
set @saved_div_precision_increment=@@div_precision_increment;
1088
set div_precision_increment=19;
1089
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
1090
i count(*) std(s1/s2)
1091
1 4 0.000000000000000000000000000000
1092
2 4 0.000000000000000000000000000000
1093
3 4 0.000000000000000000000000000000
1094
select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
1095
i count(*) round(std(o1/o2), 16)
1096
1 4 0.0000000000000000
1097
2 4 0.0000000000000000
1098
3 4 0.0000000000000000
1099
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
1100
i count(*) std(e1/e2)
1101
1 4 0.000000000000000000000000000000
1102
2 4 0.000000000000000000000000000000
1103
3 4 0.000000000000000000000000000000
1104
select round(std(s1/s2), 17) from bug22555;
1105
round(std(s1/s2), 17)
1106
0.21325763586649340
1107
select std(o1/o2) from bug22555;
1108
std(o1/o2)
1109
0.213257635866493
1110
select round(std(e1/e2), 17) from bug22555;
1111
round(std(e1/e2), 17)
1112
0.21325763586649340
1113
set div_precision_increment=20;
1114
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
1115
i count(*) std(s1/s2)
1116
1 4 0.000000000000000000000000000000
1117
2 4 0.000000000000000000000000000000
1118
3 4 0.000000000000000000000000000000
1119
select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
1120
i count(*) round(std(o1/o2), 16)
1121
1 4 0.0000000000000000
1122
2 4 0.0000000000000000
1123
3 4 0.0000000000000000
1124
select i, count(*), std(e1/e2) from bug22555 group by i order by i;
1125
i count(*) std(e1/e2)
1126
1 4 0.000000000000000000000000000000
1127
2 4 0.000000000000000000000000000000
1128
3 4 0.000000000000000000000000000000
1129
select round(std(s1/s2), 17) from bug22555;
1130
round(std(s1/s2), 17)
1131
0.21325763586649340
1132
select std(o1/o2) from bug22555;
1133
std(o1/o2)
1134
0.213257635866493
1135
select round(std(e1/e2), 17) from bug22555;
1136
round(std(e1/e2), 17)
1137
0.21325763586649340
1138
set @@div_precision_increment=@saved_div_precision_increment;
1139
drop table bug22555;
1140
create table bug22555 (s int, o double, e decimal);
1141
insert into bug22555 values (1,1,1),(2,2,2),(3,3,3),(6,6,6),(7,7,7);
1142
select var_samp(s), var_pop(s) from bug22555;
1143
var_samp(s) var_pop(s)
1144
6.7000 5.3600
1145
select var_samp(o), var_pop(o) from bug22555;
1146
var_samp(o) var_pop(o)
1147
6.7 5.36
1148
select var_samp(e), var_pop(e) from bug22555;
1149
var_samp(e) var_pop(e)
1150
6.7000 5.3600
1151
drop table bug22555;
1152
create table bug22555 (s int, o double, e decimal);
1153
insert into bug22555 values (null,null,null),(null,null,null);
1154
select var_samp(s) as 'null', var_pop(s) as 'null' from bug22555;
1155
null null
1156
NULL NULL
1157
select var_samp(o) as 'null', var_pop(o) as 'null' from bug22555;
1158
null null
1159
NULL NULL
1160
select var_samp(e) as 'null', var_pop(e) as 'null' from bug22555;
1161
null null
1162
NULL NULL
1163
insert into bug22555 values (1,1,1);
1164
select var_samp(s) as 'null', var_pop(s) as '0' from bug22555;
1165
null 0
1166
NULL 0.0000
1167
select var_samp(o) as 'null', var_pop(o) as '0' from bug22555;
1168
null 0
1169
NULL 0
1170
select var_samp(e) as 'null', var_pop(e) as '0' from bug22555;
1171
null 0
1172
NULL 0.0000
1173
insert into bug22555 values (2,2,2);
1174
select var_samp(s) as '0.5', var_pop(s) as '0.25' from bug22555;
1175
0.5 0.25
1176
0.5000 0.2500
1177
select var_samp(o) as '0.5', var_pop(o) as '0.25' from bug22555;
1178
0.5 0.25
1179
0.5 0.25
1180
select var_samp(e) as '0.5', var_pop(e) as '0.25' from bug22555;
1181
0.5 0.25
1182
0.5000 0.2500
1183
drop table bug22555;
1184
create table t1 (a decimal(20));
1185
insert into t1 values (12345678901234567890);
1186
select count(a) from t1;
1187
count(a)
1188
1
1189
select count(distinct a) from t1;
1190
count(distinct a)
1191
1
1192
drop table t1;
1193
CREATE TABLE t1 (a INT, b INT);
1194
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
1195
INSERT INTO t1 SELECT a, b+8 FROM t1;
1196
INSERT INTO t1 SELECT a, b+16 FROM t1;
1197
INSERT INTO t1 SELECT a, b+32 FROM t1;
1198
INSERT INTO t1 SELECT a, b+64 FROM t1;
1199
INSERT INTO t1 SELECT a, b+128 FROM t1;
1200
INSERT INTO t1 SELECT a, b+256 FROM t1;
1201
INSERT INTO t1 SELECT a, b+512 FROM t1;
1202
INSERT INTO t1 SELECT a, b+1024 FROM t1;
1203
INSERT INTO t1 SELECT a, b+2048 FROM t1;
1204
INSERT INTO t1 SELECT a, b+4096 FROM t1;
1205
INSERT INTO t1 SELECT a, b+8192 FROM t1;
1206
INSERT INTO t1 SELECT a, b+16384 FROM t1;
1207
INSERT INTO t1 SELECT a, b+32768 FROM t1;
1208
SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50;
1209
a cnt
1210
1 65536
1211
SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50;
1212
a sumation
1213
1 2147516416
1214
SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
1215
a average
1216
1 32768.5000
1217
DROP TABLE t1;
1218
CREATE TABLE t1 ( a INT, b INT, KEY(a) );
1219
INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
1220
EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
1221
id select_type table type possible_keys key key_len ref rows Extra
1222
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1223
SELECT MIN(a), MIN(b) FROM t1;
1224
MIN(a) MIN(b)
1225
NULL 1
1226
CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
1227
INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
1228
EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
1229
id select_type table type possible_keys key key_len ref rows Extra
1230
1 SIMPLE t2 ref a a 5 const 1
1231
SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
1232
MIN(b) MIN(c)
1233
3 2
1234
CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
1235
INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3);
1236
EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
1237
id select_type table type possible_keys key key_len ref rows Extra
1238
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
1239
SELECT MIN(a), MIN(b) FROM t3 where a = 2;
1240
MIN(a) MIN(b)
1241
2 NULL
1242
CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
1243
INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3);
1244
EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
1245
id select_type table type possible_keys key key_len ref rows Extra
1246
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
1247
SELECT MIN(a), MIN(b) FROM t4 where a = 2;
1248
MIN(a) MIN(b)
1249
2 NULL
1250
SELECT MIN(b), min(c) FROM t4 where a = 2;
1251
MIN(b) min(c)
1252
NULL 2
1253
CREATE TABLE t5( a INT, b INT, KEY( a, b) );
1254
INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
1255
EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
1256
id select_type table type possible_keys key key_len ref rows Extra
1257
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
1258
SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
1259
MIN(a) MIN(b)
1260
1 1
1261
SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
1262
MIN(a) MIN(b)
1263
1 2
1264
DROP TABLE t1, t2, t3, t4, t5;
1265
CREATE TABLE t1 (a int, b date NOT NULL, KEY k1 (a,b));
1266
SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01';
1267
MIN(b)
1268
NULL
1269
DROP TABLE t1;
1270
CREATE TABLE t1(a DOUBLE);
1271
INSERT INTO t1 VALUES (10), (20);
1272
SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1;
1273
AVG(a) CAST(AVG(a) AS DECIMAL)
1274
15 15
1275
DROP TABLE t1;
1276
End of 5.0 tests
Loggerhead is a web-based interface for Breezy
Version: 2.0.1