← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/t/func_group.test

Merged from trunk.

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/t/func_group.test
9
9
set @sav_dpi= @@div_precision_increment;
10
10
set div_precision_increment= 5;
11
11
show variables like 'div_precision_increment';
12
 
create table t1 (grp int, a bigint, c char(10) not null);
 
12
create table t1 (grp int, a bigint unsigned, c char(10) not null);
13
13
insert into t1 values (1,1,"a");
14
14
insert into t1 values (2,2,"b");
15
15
insert into t1 values (2,3,"c");
27
27
insert into t1 values (null,null,'');
28
28
select count(distinct a),count(distinct grp) from t1;
29
29
 
30
 
create table t2 (grp int, a bigint, c char(10));
 
30
select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;
 
31
select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp;
 
32
--disable_warnings
 
33
select grp, sum(a)+count(a)+avg(a)+std(a)+variance(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp;
 
34
--enable_warnings
 
35
 
 
36
create table t2 (grp int, a bigint unsigned, c char(10));
31
37
insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp;
32
38
 
33
39
# REPLACE ... SELECT doesn't yet work with PS
40
46
# Problem with std()
41
47
#
42
48
 
43
 
CREATE TABLE t1 (id int,value1 float(10,2));
 
49
CREATE TABLE t1 (id int(11),value1 float(10,2));
44
50
INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00); 
45
 
CREATE TABLE t2 (id int,name char(20)); 
 
51
CREATE TABLE t2 (id int(11),name char(20)); 
46
52
INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two'); 
47
53
select id, avg(value1), std(value1), variance(value1) from t1 group by id;
48
54
select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id;
67
73
#
68
74
# test of count
69
75
#
70
 
create table t1 (a int primary key, c char(10), b text);
 
76
create table t1 (a smallint(6) primary key, c char(10), b text);
71
77
INSERT INTO t1 VALUES (1,'1','1');
72
78
INSERT INTO t1 VALUES (2,'2','2');
73
79
INSERT INTO t1 VALUES (4,'4','4');
128
134
 
129
135
insert into t2 values('AAA', 10, 0.5);
130
136
insert into t2 values('BBB', 20, 1.0);
131
 
--sorted_result
132
137
select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;
133
138
 
134
139
select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
142
147
drop table t1,t2;
143
148
 
144
149
#
 
150
# Test of group function and NULL values
 
151
#
 
152
 
 
153
CREATE TABLE t1 (a int, b int);
 
154
select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1;
 
155
select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 
156
insert into t1 values (1,null);
 
157
select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 
158
insert into t1 values (1,null);
 
159
insert into t1 values (2,null);
 
160
select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 
161
select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 
162
insert into t1 values (2,1);
 
163
select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 
164
select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 
165
insert into t1 values (3,1);
 
166
select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 
167
select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a;
 
168
explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a;
 
169
drop table t1;
 
170
 
 
171
#
 
172
# Bug #1972: test for bit_and(), bit_or() and negative values
 
173
# 
 
174
create table t1 (col int);
 
175
insert into t1 values (-1), (-2), (-3);
 
176
select bit_and(col), bit_or(col) from t1;
 
177
select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col;
 
178
drop table t1;
 
179
 
 
180
#
145
181
# Bug #3376: avg() and an empty table
146
182
#
147
183
 
156
192
# Create database schema
157
193
create table t1(
158
194
  a1 char(3) primary key,
159
 
  a2 int,
 
195
  a2 smallint,
160
196
  a3 char(3),
161
197
  a4 real,
162
198
  a5 date,
200
236
insert into t2 values('AAA','AAA','AA','AME');
201
237
 
202
238
# Show the table contents
203
 
--sorted_result
204
239
select * from t1;
205
 
--sorted_result
206
240
select * from t2;
207
241
 
208
242
# Queries with min/max functions 
355
389
#select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;
356
390
#drop table t1;
357
391
 
 
392
 
 
393
create table t1 (a char(10));
 
394
insert into t1 values ('a'),('b'),('c');
 
395
select coercibility(max(a)) from t1;
 
396
drop table t1;
 
397
 
358
398
#
359
399
# Bug #6658 MAX(column) returns incorrect coercibility
360
400
#
361
 
create table t1 (a char);
 
401
create table t1 (a char character set latin2);
362
402
insert into t1 values ('a'),('b');
363
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
 
403
select charset(max(a)), coercibility(max(a)),
 
404
       charset(min(a)), coercibility(min(a)) from t1;
364
405
show create table t1;
365
406
create table t2 select max(a),min(a) from t1;
366
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
367
407
show create table t2;
368
408
drop table t2;
369
409
create table t2 select concat(a) from t1;
370
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
371
410
show create table t2;
372
411
drop table t2,t1;
373
412
 
423
462
# Bug #5555 GROUP BY enum_field" returns incorrect results
424
463
#
425
464
 
426
 
CREATE TEMPORARY TABLE t1 (
427
 
  id int NOT NULL auto_increment,
 
465
CREATE TABLE t1 (
 
466
  id int(10) unsigned NOT NULL auto_increment,
428
467
  val enum('one','two','three') NOT NULL default 'one',
429
468
  PRIMARY KEY  (id)
430
 
) ENGINE=MyISAM;
431
 
 
432
 
INSERT INTO t1 VALUES
433
 
(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
434
 
 
 
469
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
470
 
 
471
INSERT INTO t1 VALUES
 
472
(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
 
473
 
 
474
select val, count(*) from t1 group by val;
 
475
drop table t1;
 
476
 
 
477
CREATE TABLE t1 (
 
478
  id int(10) unsigned NOT NULL auto_increment,
 
479
  val set('one','two','three') NOT NULL default 'one',
 
480
  PRIMARY KEY  (id)
 
481
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
482
 
 
483
INSERT INTO t1 VALUES
 
484
(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
 
485
 
435
486
select val, count(*) from t1 group by val;
436
487
drop table t1;
437
488
 
442
493
create table t1(a int, b datetime);
443
494
insert into t1 values (1, NOW()), (2, NOW());
444
495
create table t2 select MAX(b) from t1 group by a;
445
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
446
496
show create table t2;
447
497
drop table t1, t2;
448
498
 
513
563
EXPLAIN SELECT MAX(b) FROM t1;
514
564
DROP TABLE t1;
515
565
 
 
566
CREATE TABLE t1 (id int , b varchar(512), INDEX(b(250))) COLLATE latin1_bin;
 
567
INSERT INTO t1 VALUES
 
568
  (1,CONCAT(REPEAT('_', 250), "qq")), (1,CONCAT(REPEAT('_', 250), "zz")),
 
569
  (1,CONCAT(REPEAT('_', 250), "aa")), (1,CONCAT(REPEAT('_', 250), "ff"));
 
570
 
 
571
SELECT MAX(b) FROM t1;
 
572
EXPLAIN SELECT MAX(b) FROM t1;
 
573
DROP TABLE t1;
516
574
#
517
575
# Bug #16792  query with subselect, join, and group not returning proper values
518
576
#
522
580
SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
523
581
SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;
524
582
# an attempt to test all aggregate function with no table.
525
 
SELECT AVG(2), COUNT(*), COUNT(12),
 
583
SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12),
526
584
       COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2),
527
585
       GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);
528
586
DROP TABLE t1;
535
593
create table t2 (ff double);
536
594
insert into t2 values (2.2);
537
595
select cast(sum(distinct ff) as decimal(5,2)) from t2;
538
 
select sum(distinct ff) from t2;
 
596
select cast(sum(distinct ff) as signed) from t2;
539
597
select cast(variance(ff) as decimal(10,3)) from t2;
540
598
select cast(min(ff) as decimal(5,2)) from t2;
541
599
 
542
600
create table t1 (df decimal(5,1));
543
601
insert into t1 values(1.1);
544
602
insert into t1 values(2.2);
545
 
select sum(distinct df) from t1;
546
 
select min(df) from t1;
 
603
select cast(sum(distinct df) as signed) from t1;
 
604
select cast(min(df) as signed) from t1;
547
605
select 1e8 * sum(distinct df) from t1;
548
606
select 1e8 * min(df) from t1;
549
607
 
558
616
# BUG#3190, WL#1639: Standard Deviation STDDEV - 2 different calculations
559
617
#
560
618
 
561
 
CREATE TABLE t1 (id int,value1 float(10,2));
 
619
CREATE TABLE t1 (id int(11),value1 float(10,2));
562
620
INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00), (2,13.00);
563
621
select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id;
564
622
DROP TABLE t1;
613
671
  (3,2,1), (3,2,2), (3,2,3),
614
672
  (3,3,1), (3,3,2), (3,3,3);
615
673
 
616
 
--sorted_result
617
674
SELECT b/c as v, a FROM t1 ORDER BY v;
618
675
SELECT b/c as v, SUM(a) FROM t1 GROUP BY v;
619
676
SELECT SUM(a) FROM t1 GROUP BY b/c;
630
687
CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
631
688
INSERT INTO t2 VALUES (1,1), (3,3);
632
689
 
633
 
--sorted_result
634
 
SELECT 
 
690
SELECT SQL_NO_CACHE 
635
691
  (SELECT SUM(c.a) FROM t1 ttt, t2 ccc 
636
692
   WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid   
637
693
FROM t1 t, t2 c WHERE t.a = c.b;
643
699
#
644
700
 
645
701
create table t1 select variance(0);                                               
646
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
647
702
show create table t1;                                                           
648
703
drop table t1;                                                                  
649
704
create table t1 select stddev(0);
650
 
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
651
705
show create table t1;
652
706
drop table t1;
653
707
 
656
710
# Bug#22555: STDDEV yields positive result for groups with only one row
657
711
#
658
712
 
659
 
create table bug22555 (i int primary key auto_increment, s1 int, s2 int, e decimal(30,10), o double);
 
713
create table bug22555 (i smallint primary key auto_increment, s1 smallint, s2 smallint, e decimal(30,10), o double);
660
714
insert into bug22555 (s1, s2, e, o) values (53, 78, 11.4276528, 6.828112), (17, 78, 5.916793, 1.8502951), (18, 76, 2.679231, 9.17975591), (31, 62, 6.07831, 0.1), (19, 41, 5.37463, 15.1), (83, 73, 14.567426, 7.959222), (92, 53, 6.10151, 13.1856852), (7, 12, 13.92272, 3.442007), (92, 35, 11.95358909, 6.01376678), (38, 84, 2.572, 7.904571);
661
715
select std(s1/s2) from bug22555 group by i;
662
716
select std(e) from bug22555 group by i;
663
717
select std(o) from bug22555 group by i;
664
718
drop table bug22555;
665
719
 
666
 
create table bug22555 (i int, s1 int, s2 int, o1 double, o2 double, e1 decimal, e2 decimal);
 
720
create table bug22555 (i smallint, s1 smallint, s2 smallint, o1 double, o2 double, e1 decimal, e2 decimal);
667
721
insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
668
722
select i, count(*) from bug22555 group by i;
669
723
select std(s1/s2) from bug22555 where i=1;
719
773
set @@div_precision_increment=@saved_div_precision_increment;
720
774
drop table bug22555;
721
775
 
722
 
create table bug22555 (s int, o double, e decimal);
 
776
create table bug22555 (s smallint, o double, e decimal);
723
777
insert into bug22555 values (1,1,1),(2,2,2),(3,3,3),(6,6,6),(7,7,7);
724
778
select var_samp(s), var_pop(s) from bug22555;
725
779
select var_samp(o), var_pop(o) from bug22555;
726
780
select var_samp(e), var_pop(e) from bug22555;
727
781
drop table bug22555;
728
782
 
729
 
create table bug22555 (s int, o double, e decimal);
 
783
create table bug22555 (s smallint, o double, e decimal);
730
784
insert into bug22555 values (null,null,null),(null,null,null);
731
785
select var_samp(s) as 'null', var_pop(s) as 'null' from bug22555;
732
786
select var_samp(o) as 'null', var_pop(o) as 'null' from bug22555;