1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
#
# simple test of all group functions
#
drop table if exists t1,t2;
create table t1 (grp int, a bigint unsigned, c char(10) not null);
insert into t1 values (1,1,"a");
insert into t1 values (2,2,"b");
insert into t1 values (2,3,"c");
insert into t1 values (3,4,"E");
insert into t1 values (3,5,"C");
insert into t1 values (3,6,"D");
# Test of MySQL field extension with and without matching records.
select a,c,sum(a) from t1 group by a;
select a,c,sum(a) from t1 where a > 10 group by a;
select sum(a) from t1 where a > 10;
select a from t1 order by rand(10);
select distinct a from t1 order by rand(10);
select count(distinct a),count(distinct grp) from t1;
insert into t1 values (null,null,'');
select count(distinct a),count(distinct grp) from t1;
select sum(all a),count(all a),avg(all a),std(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;
select grp, sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp;
select grp, sum(a)+count(a)+avg(a)+std(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp;
create table t2 (grp int, a bigint unsigned, c char(10));
insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp;
replace into t2 select grp, a, c from t1 limit 2,1;
select * from t2;
drop table t1,t2;
#
# Problem with std()
#
CREATE TABLE t1 (id int(11),value1 float(10,2));
INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00);
CREATE TABLE t2 (id int(11),name char(20));
INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two');
select id, avg(value1), std(value1) from t1 group by id;
select name, avg(value1), std(value1) from t1, t2 where t1.id = t2.id group by t1.id;
drop table t1,t2;
#
# Test of bug in left join & avg
#
create table t1 (id int not null);
create table t2 (id int not null,rating int null);
insert into t1 values(1),(2),(3);
insert into t2 values(1, 3),(2, NULL),(2, NULL),(3, 2),(3, NULL);
select t1.id, avg(rating) from t1 left join t2 on ( t1.id = t2.id ) group by t1.id;
drop table t1,t2;
#
# test of count
#
create table t1 (a smallint(6) primary key, c char(10), b text);
INSERT INTO t1 VALUES (1,'1','1');
INSERT INTO t1 VALUES (2,'2','2');
INSERT INTO t1 VALUES (4,'4','4');
select count(*) from t1;
select count(*) from t1 where a = 1;
select count(*) from t1 where a = 100;
select count(*) from t1 where a >= 10;
select count(a) from t1 where a = 1;
select count(a) from t1 where a = 100;
select count(a) from t1 where a >= 10;
select count(b) from t1 where b >= 2;
select count(b) from t1 where b >= 10;
select count(c) from t1 where c = 10;
drop table t1;
#
# Test of bug in COUNT(i)*(i+0)
#
CREATE TABLE t1 (d DATETIME, i INT);
INSERT INTO t1 VALUES (NOW(), 1);
SELECT COUNT(i), i, COUNT(i)*i FROM t1 GROUP BY i;
SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i;
DROP TABLE t1;
#
# Another SUM() problem with 3.23.2
#
create table t1 (
num float(5,2),
user char(20)
);
insert into t1 values (10.3,'nem'),(20.53,'monty'),(30.23,'sinisa');
insert into t1 values (30.13,'nem'),(20.98,'monty'),(10.45,'sinisa');
insert into t1 values (5.2,'nem'),(8.64,'monty'),(11.12,'sinisa');
select sum(num) from t1;
select sum(num) from t1 group by user;
drop table t1;
#
# Test problem with MIN() optimization in case of null values
#
create table t1 (a1 int, a2 char(3), key k1(a1), key k2(a2));
insert into t1 values(10,'aaa'), (10,null), (10,'bbb'), (20,'zzz');
create table t2(a1 char(3), a2 int, a3 real, key k1(a1), key k2(a2, a1));
select * from t1;
# The following returned NULL in 4.0.10
select min(a2) from t1;
select max(t1.a1), max(t2.a2) from t1, t2;
select max(t1.a1) from t1, t2;
select max(t2.a2), max(t1.a1) from t1, t2;
explain select min(a2) from t1;
explain select max(t1.a1), max(t2.a2) from t1, t2;
insert into t2 values('AAA', 10, 0.5);
select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9;
select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
select max(t1.a2) from t1 left outer join t2 on t1.a1=10;
select max(t1.a2) from t1 left outer join t2 on t1.a1=10 where t1.a1=20;
select max(t1.a2) from t1 left outer join t2 on t1.a1=10 where t1.a1=10;
select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA';
drop table t1,t2;
#
# Test of group function and NULL values
#
CREATE TABLE t1 (a int, b int);
select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1;
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;
insert into t1 values (1,null);
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;
insert into t1 values (1,null);
insert into t1 values (2,null);
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;
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;
insert into t1 values (2,1);
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;
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;
insert into t1 values (3,1);
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;
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;
drop table t1;
|
