← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/group_by.test

  • Committer: Jay Pipes
  • Date: 2008-07-17 17:54:00 UTC
  • mto: This revision was merged to the branch mainline in revision 182.
  • Revision ID: jay@mysql.com-20080717175400-xm2aazihjra8mdzq
Removal of DBUG from libdrizzle/ - Round 2

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/group_by.test
1
 
 
2
 
# Initialise
3
 
--disable_warnings
4
 
drop table if exists t1,t2,t3;
5
 
--enable_warnings
6
 
 
7
 
#
8
 
# Simple test without tables
9
 
 
10
 
--error ER_INVALID_GROUP_FUNC_USE
11
 
SELECT 1 FROM (SELECT 1) as a  GROUP BY SUM(1);
12
 
 
13
 
#
14
 
# Test of group (Failed for Lars Hoss <lh@pbm.de>)
15
 
#
16
 
 
17
 
CREATE TABLE t1 (
18
 
  spID int,
19
 
  userID int,
20
 
  score int,
21
 
  lsg char(40),
22
 
  date date
23
 
);
24
 
 
25
 
INSERT INTO t1 VALUES (1,1,1,'',NULL);
26
 
INSERT INTO t1 VALUES (2,2,2,'',NULL);
27
 
INSERT INTO t1 VALUES (2,1,1,'',NULL);
28
 
INSERT INTO t1 VALUES (3,3,3,'',NULL);
29
 
 
30
 
CREATE TABLE t2 (
31
 
  userID int NOT NULL auto_increment,
32
 
  niName char(15),
33
 
  passwd char(8),
34
 
  mail char(50),
35
 
  isAukt enum('N','Y') DEFAULT 'N',
36
 
  vName char(30),
37
 
  nName char(40),
38
 
  adr char(60),
39
 
  plz char(5),
40
 
  ort char(35),
41
 
  land char(20),
42
 
  PRIMARY KEY (userID)
43
 
);
44
 
 
45
 
INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1');
46
 
INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1');
47
 
INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1');
48
 
INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1');
49
 
INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1');
50
 
 
51
 
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid;
52
 
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL;
53
 
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid;
54
 
SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid;
55
 
SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid ORDER BY NULL;
56
 
EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid ORDER BY NULL;
57
 
drop table t1,t2;
58
 
 
59
 
#
60
 
# Bug in GROUP BY, by Nikki Chumakov <nikki@saddam.cityline.ru>
61
 
#
62
 
 
63
 
CREATE TABLE t1 (
64
 
  PID int NOT NULL auto_increment,
65
 
  payDate date,
66
 
  recDate datetime,
67
 
  URID int DEFAULT '0' NOT NULL,
68
 
  CRID int DEFAULT '0' NOT NULL,
69
 
  amount int DEFAULT '0' NOT NULL,
70
 
  operator int,
71
 
  method enum('unknown','cash','dealer','check','card','lazy','delayed','test') DEFAULT 'unknown' NOT NULL,
72
 
  DIID int,
73
 
  reason char(1) DEFAULT '' NOT NULL,
74
 
  code_id int,
75
 
  qty int DEFAULT '0' NOT NULL,
76
 
  PRIMARY KEY (PID),
77
 
  KEY URID (URID),
78
 
  KEY reason (reason),
79
 
  KEY method (method),
80
 
  KEY payDate (payDate)
81
 
);
82
 
 
83
 
INSERT INTO t1 VALUES (1,'1970-01-01','1997-10-17 00:00:00',2529,1,21000,11886,'check',0,'F',16200,6);
84
 
 
85
 
--error ER_WRONG_GROUP_FIELD
86
 
SELECT COUNT(P.URID),SUM(P.amount),P.method, MIN(PP.recdate+0) > 19980501000000   AS IsNew FROM t1 AS P JOIN t1 as PP WHERE P.URID = PP.URID GROUP BY method,IsNew;
87
 
 
88
 
drop table t1;
89
 
 
90
 
#
91
 
# Problem with GROUP BY + ORDER BY when no match
92
 
# Tested with locking
93
 
#  NOTE: LOCK TABLE was removed, so now just testing normal syntax.
94
 
#
95
 
 
96
 
CREATE TABLE t1 (
97
 
  cid int NOT NULL auto_increment,
98
 
  firstname varchar(32) DEFAULT '' NOT NULL,
99
 
  surname varchar(32) DEFAULT '' NOT NULL,
100
 
  PRIMARY KEY (cid)
101
 
);
102
 
INSERT INTO t1 VALUES (1,'That','Guy');
103
 
INSERT INTO t1 VALUES (2,'Another','Gent');
104
 
 
105
 
CREATE TABLE t2 (
106
 
  call_id int NOT NULL auto_increment,
107
 
  contact_id int DEFAULT '0' NOT NULL,
108
 
  PRIMARY KEY (call_id),
109
 
  KEY contact_id (contact_id)
110
 
);
111
 
 
112
 
INSERT INTO t2 VALUES (10,2);
113
 
INSERT INTO t2 VALUES (18,2);
114
 
INSERT INTO t2 VALUES (62,2);
115
 
INSERT INTO t2 VALUES (91,2);
116
 
INSERT INTO t2 VALUES (92,2);
117
 
 
118
 
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid;
119
 
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY NULL;
120
 
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY surname, firstname;
121
 
 
122
 
drop table t2;
123
 
drop table t1;
124
 
 
125
 
# Test needs to be rewritten
126
 
##
127
 
## Test of group by bug in bugzilla
128
 
##
129
 
#
130
 
#CREATE TABLE t1 (
131
 
#  bug_id int NOT NULL auto_increment,
132
 
#  groupset bigint DEFAULT '0' NOT NULL,
133
 
#  assigned_to int DEFAULT '0' NOT NULL,
134
 
#  bug_file_loc text,
135
 
#  bug_severity enum('blocker','critical','major','normal','minor','trivial','enhancement') DEFAULT 'blocker' NOT NULL,
136
 
#  bug_status enum('','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED') DEFAULT 'NEW' NOT NULL,
137
 
#  creation_ts datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
138
 
#  delta_ts timestamp,
139
 
#  short_desc mediumtext,
140
 
#  long_desc mediumtext,
141
 
#  op_sys enum('All','Windows 3.1','Windows 95','Windows 98','Windows NT','Windows 2000','Linux','other') DEFAULT 'All' NOT NULL,
142
 
#  priority enum('P1','P2','P3','P4','P5') DEFAULT 'P1' NOT NULL,
143
 
#  product varchar(64) DEFAULT '' NOT NULL,
144
 
#  rep_platform enum('All','PC','VTD-8','Other'),
145
 
#  reporter int DEFAULT '0' NOT NULL,
146
 
#  version varchar(16) DEFAULT '' NOT NULL,
147
 
#  component varchar(50) DEFAULT '' NOT NULL,
148
 
#  resolution enum('','FIXED','INVALID','WONTFIX','LATER','REMIND','DUPLICATE','WORKSFORME') DEFAULT '' NOT NULL,
149
 
#  target_milestone varchar(20) DEFAULT '' NOT NULL,
150
 
#  qa_contact int DEFAULT '0' NOT NULL,
151
 
#  status_whiteboard mediumtext NOT NULL,
152
 
#  votes int DEFAULT '0' NOT NULL,
153
 
#  PRIMARY KEY (bug_id),
154
 
#  KEY assigned_to (assigned_to),
155
 
#  KEY creation_ts (creation_ts),
156
 
#  KEY delta_ts (delta_ts),
157
 
#  KEY bug_severity (bug_severity),
158
 
#  KEY bug_status (bug_status),
159
 
#  KEY op_sys (op_sys),
160
 
#  KEY priority (priority),
161
 
#  KEY product (product),
162
 
#  KEY reporter (reporter),
163
 
#  KEY version (version),
164
 
#  KEY component (component),
165
 
#  KEY resolution (resolution),
166
 
#  KEY target_milestone (target_milestone),
167
 
#  KEY qa_contact (qa_contact),
168
 
#  KEY votes (votes)
169
 
#);
170
 
#
171
 
#--error ER_WARN_DATA_TRUNCATED
172
 
#INSERT INTO t1 VALUES (1,0,0,'','normal','','2000-02-10 09:25:12',20000321114747,'','','Linux','P1','TestProduct','PC',3,'other','TestComponent','','M1',0,'',0);
173
 
#INSERT INTO t1 VALUES (9,0,0,'','enhancement','','2000-03-10 11:49:36',20000321114747,'','','All','P5','AAAAA','PC',3,'2.00 CD - Pre','BBBBBBBBBBBBB - conversion','','',0,'',0);
174
 
#INSERT INTO t1 VALUES (10,0,0,'','enhancement','','2000-03-10 18:10:16',20000321114747,'','','All','P4','AAAAA','PC',3,'2.00 CD - Pre','BBBBBBBBBBBBB - conversion','','',0,'',0);
175
 
#INSERT INTO t1 VALUES (7,0,0,'','critical','','2000-03-09 10:50:21',20000321114747,'','','All','P1','AAAAA','PC',3,'2.00 CD - Pre','BBBBBBBBBBBBB - generic','','',0,'',0);
176
 
#INSERT INTO t1 VALUES (6,0,0,'','normal','','2000-03-09 10:42:44',20000321114747,'','','All','P2','AAAAA','PC',3,'2.00 CD - Pre','kkkkkkkkkkk lllllllllll','','',0,'',0);
177
 
#INSERT INTO t1 VALUES (8,0,0,'','major','','2000-03-09 11:32:14',20000321114747,'','','All','P3','AAAAA','PC',3,'2.00 CD - Pre','kkkkkkkkkkk lllllllllll','','',0,'',0);
178
 
#INSERT INTO t1 VALUES (5,0,0,'','enhancement','','2000-03-09 10:38:59',20000321114747,'','','All','P5','CCC/CCCCCC','PC',5,'7.00','Administration','','',0,'',0);
179
 
#INSERT INTO t1 VALUES (4,0,0,'','normal','','2000-03-08 18:32:14',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent2','','',0,'',0);
180
 
#INSERT INTO t1 VALUES (3,0,0,'','normal','','2000-03-08 18:30:52',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0);
181
 
#INSERT INTO t1 VALUES (2,0,0,'','enhancement','','2000-03-08 18:24:51',20000321114747,'','','All','P2','TestProduct','Other',4,'other','TestComponent2','','',0,'',0);
182
 
#INSERT INTO t1 VALUES (11,0,0,'','blocker','','2000-03-13 09:43:41',20000321114747,'','','All','P2','CCC/CCCCCC','PC',5,'7.00','DDDDDDDDD','','',0,'',0);
183
 
#INSERT INTO t1 VALUES (12,0,0,'','normal','','2000-03-13 16:14:31',20000321114747,'','','All','P2','AAAAA','PC',3,'2.00 CD - Pre','kkkkkkkkkkk lllllllllll','','',0,'',0);
184
 
#INSERT INTO t1 VALUES (13,0,0,'','normal','','2000-03-15 16:20:44',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0);
185
 
#INSERT INTO t1 VALUES (14,0,0,'','blocker','','2000-03-15 18:13:47',20000321114747,'','','All','P1','AAAAA','PC',3,'2.00 CD - Pre','BBBBBBBBBBBBB - generic','','',0,'',0);
186
 
#INSERT INTO t1 VALUES (15,0,0,'','minor','','2000-03-16 18:03:28',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','DDDDDDDDD','','',0,'',0);
187
 
#INSERT INTO t1 VALUES (16,0,0,'','normal','','2000-03-16 18:33:41',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);
188
 
#INSERT INTO t1 VALUES (17,0,0,'','normal','','2000-03-16 18:34:18',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);
189
 
#INSERT INTO t1 VALUES (18,0,0,'','normal','','2000-03-16 18:34:56',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);
190
 
#INSERT INTO t1 VALUES (19,0,0,'','enhancement','','2000-03-16 18:35:34',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);
191
 
#INSERT INTO t1 VALUES (20,0,0,'','enhancement','','2000-03-16 18:36:23',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);
192
 
#INSERT INTO t1 VALUES (21,0,0,'','enhancement','','2000-03-16 18:37:23',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);
193
 
#INSERT INTO t1 VALUES (22,0,0,'','enhancement','','2000-03-16 18:38:16',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);
194
 
#INSERT INTO t1 VALUES (23,0,0,'','normal','','2000-03-16 18:58:12',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','DDDDDDDDD','','',0,'',0);
195
 
#INSERT INTO t1 VALUES (24,0,0,'','normal','','2000-03-17 11:08:10',20000321114747,'','','All','P2','AAAAAAAA-AAA','PC',3,'2.8','Web Interface','','',0,'',0);
196
 
#INSERT INTO t1 VALUES (25,0,0,'','normal','','2000-03-17 11:10:45',20000321114747,'','','All','P2','AAAAAAAA-AAA','PC',3,'2.8','Web Interface','','',0,'',0);
197
 
#INSERT INTO t1 VALUES (26,0,0,'','normal','','2000-03-17 11:15:47',20000321114747,'','','All','P2','AAAAAAAA-AAA','PC',3,'2.8','Web Interface','','',0,'',0);
198
 
#INSERT INTO t1 VALUES (27,0,0,'','normal','','2000-03-17 17:45:41',20000321114747,'','','All','P2','CCC/CCCCCC','PC',5,'7.00','DDDDDDDDD','','',0,'',0);
199
 
#INSERT INTO t1 VALUES (28,0,0,'','normal','','2000-03-20 09:51:45',20000321114747,'','','Windows NT','P2','TestProduct','PC',8,'other','TestComponent','','',0,'',0);
200
 
#INSERT INTO t1 VALUES (29,0,0,'','normal','','2000-03-20 11:15:09',20000321114747,'','','All','P5','AAAAAAAA-AAA','PC',3,'2.8','Web Interface','','',0,'',0);
201
 
#CREATE TABLE t2 (
202
 
#  value text,
203
 
#  program varchar(64),
204
 
#  initialowner text NOT NULL,
205
 
#  initialqacontact text NOT NULL,
206
 
#  description text NOT NULL
207
 
#);
208
 
#
209
 
#INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','','');
210
 
#INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','','');
211
 
#INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','','');
212
 
#INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','','');
213
 
#INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - eeeeeeeee','AAAAA','id0001','','');
214
 
#INSERT INTO t2 VALUES ('kkkkkkkkkkk lllllllllll','AAAAA','id0001','','');
215
 
#INSERT INTO t2 VALUES ('Test Procedures','AAAAA','id0001','','');
216
 
#INSERT INTO t2 VALUES ('Documentation','AAAAA','id0003','','');
217
 
#INSERT INTO t2 VALUES ('DDDDDDDDD','CCC/CCCCCC','id0002','','');
218
 
#INSERT INTO t2 VALUES ('Eeeeeeee Lite','CCC/CCCCCC','id0002','','');
219
 
#INSERT INTO t2 VALUES ('Eeeeeeee Full','CCC/CCCCCC','id0002','','');
220
 
#INSERT INTO t2 VALUES ('Administration','CCC/CCCCCC','id0002','','');
221
 
#INSERT INTO t2 VALUES ('Distribution','CCC/CCCCCC','id0002','','');
222
 
#INSERT INTO t2 VALUES ('Setup','CCC/CCCCCC','id0002','','');
223
 
#INSERT INTO t2 VALUES ('Unspecified','CCC/CCCCCC','id0002','','');
224
 
#INSERT INTO t2 VALUES ('Web Interface','AAAAAAAA-AAA','id0001','','');
225
 
#INSERT INTO t2 VALUES ('Host communication','AAAAA','id0001','','');
226
 
#select value,description,bug_id from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA";
227
 
#select value,description,COUNT(bug_id) from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA" group by value;
228
 
#select value,description,COUNT(bug_id) from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA" group by value having COUNT(bug_id) IN (0,2);
229
 
#
230
 
#drop table t1,t2;
231
 
 
232
 
#
233
 
# Problem with functions and group functions when no matching rows
234
 
#
235
 
 
236
 
create table t1 (foo int);
237
 
insert into t1 values (1);
238
 
select 1+1, "a",count(*) from t1 where foo in (2);
239
 
insert into t1 values (1);
240
 
select 1+1,"a",count(*) from t1 where foo in (2);
241
 
drop table t1;
242
 
 
243
 
#
244
 
# Test GROUP BY DESC
245
 
 
246
 
CREATE TABLE t1 (
247
 
  spID int,
248
 
  userID int,
249
 
  score int,
250
 
  key (spid),
251
 
  key (score)
252
 
);
253
 
 
254
 
INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3);
255
 
explain select userid,count(*) from t1 group by userid desc;
256
 
explain select userid,count(*) from t1 group by userid desc order by null;
257
 
select userid,count(*) from t1 group by userid desc;
258
 
select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);
259
 
select userid,count(*) from t1 group by userid desc having 3  IN (1,COUNT(*));
260
 
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
261
 
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
262
 
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;
263
 
select spid,count(*) from t1 where spid between 1 and 2 group by spid;
264
 
select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
265
 
explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc;
266
 
explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
267
 
select sql_big_result spid,sum(userid) from t1 group by spid desc;
268
 
explain select sql_big_result score,count(*) from t1 group by score desc;
269
 
explain select sql_big_result score,count(*) from t1 group by score desc order by null;
270
 
select sql_big_result score,count(*) from t1 group by score desc;
271
 
drop table t1;
272
 
 
273
 
# not purely group_by bug, but group_by is involved...
274
 
 
275
 
create table t1 (a date default null, b date default null);
276
 
insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01');
277
 
select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day;
278
 
drop table t1;
279
 
 
280
 
# Compare with hash keys
281
 
 
282
 
CREATE TABLE t1 (a char(1));
283
 
INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);
284
 
SELECT a FROM t1 GROUP BY a;
285
 
SELECT a,count(*) FROM t1 GROUP BY a;
286
 
SELECT a FROM t1 GROUP BY a;
287
 
SELECT a,count(*) FROM t1 GROUP BY a;
288
 
SELECT a FROM t1 GROUP BY 1;
289
 
SELECT a,count(*) FROM t1 GROUP BY 1;
290
 
# Do the same tests with MyISAM temporary tables
291
 
SELECT a FROM t1 GROUP BY a;
292
 
SELECT a,count(*) FROM t1 GROUP BY a;
293
 
SELECT a FROM t1 GROUP BY a;
294
 
SELECT a,count(*) FROM t1 GROUP BY a;
295
 
SELECT a FROM t1 GROUP BY 1;
296
 
SELECT a,count(*) FROM t1 GROUP BY 1;
297
 
drop table t1;
298
 
 
299
 
#
300
 
# Test of key >= 256 bytes
301
 
#
302
 
 
303
 
CREATE TABLE t1 (
304
 
  `a` char(193) default NULL,
305
 
  `b` char(63) default NULL
306
 
);
307
 
INSERT INTO t1 VALUES ('abc','def'),('hij','klm');
308
 
SELECT CONCAT(a, b) FROM t1 GROUP BY 1;
309
 
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
310
 
SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1;
311
 
SELECT 1 FROM t1 GROUP BY CONCAT(a, b);
312
 
INSERT INTO t1 values ('hij','klm');
313
 
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
314
 
DROP TABLE t1;
315
 
 
316
 
#
317
 
# Test problem with ORDER BY on a SUM() column
318
 
#
319
 
 
320
 
create table t1 (One int, Two int, Three int, Four int);
321
 
insert into t1 values (1,2,1,4),(1,2,2,4),(1,2,3,4),(1,2,4,4),(1,1,1,4),(1,1,2,4),(1,1,3,4),(1,1,4,4),(1,3,1,4),(1,3,2,4),(1,3,3,4),(1,3,4,4);
322
 
select One, Two, sum(Four) from t1 group by One,Two;
323
 
drop table t1;
324
 
 
325
 
create table t1 (id integer primary key not null auto_increment, gender char(1));
326
 
insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M');
327
 
create table t2 (user_id integer not null, date date);
328
 
insert into t2 values (1, '2002-06-09'),(2, '2002-06-09'),(1, '2002-06-09'),(3, '2002-06-09'),(4, '2002-06-09'),(4, '2002-06-09');
329
 
select u.gender as gender, count(distinct  u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender;
330
 
select u.gender as  gender, count(distinct  u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender  order by percentage;
331
 
drop table t1,t2;
332
 
 
333
 
#
334
 
# The GROUP BY returned rows in wrong order in 3.23.51
335
 
#
336
 
 
337
 
CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
338
 
));
339
 
insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
340
 
select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS  on S.ID1 between yS.ID1 and yS.ID2;
341
 
select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS  on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1;
342
 
drop table t1;
343
 
 
344
 
#
345
 
# Problem with MAX and LEFT JOIN
346
 
#
347
 
 
348
 
CREATE TEMPORARY TABLE t1 (
349
 
  pid int NOT NULL default '0',
350
 
  c1id int default NULL,
351
 
  c2id int default NULL,
352
 
  value int NOT NULL default '0',
353
 
  UNIQUE KEY pid2 (pid,c1id,c2id),
354
 
  UNIQUE KEY pid (pid,value)
355
 
) ENGINE=MyISAM;
356
 
 
357
 
INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5);
358
 
 
359
 
CREATE TEMPORARY TABLE t2 (
360
 
  id int NOT NULL default '0',
361
 
  active enum('Yes','No') NOT NULL default 'Yes',
362
 
  PRIMARY KEY  (id)
363
 
) ENGINE=MyISAM;
364
 
 
365
 
INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No');
366
 
 
367
 
CREATE TABLE t3 (
368
 
  id int NOT NULL default '0',
369
 
  active enum('Yes','No') NOT NULL default 'Yes',
370
 
  PRIMARY KEY  (id)
371
 
);
372
 
INSERT INTO t3 VALUES (3, 'Yes');
373
 
 
374
 
select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id = 
375
 
c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND 
376
 
c2.active = 'Yes' WHERE m.pid=1  AND (c1.id IS NOT NULL OR c2.id IS NOT NULL);
377
 
select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON 
378
 
m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = 
379
 
c2.id AND c2.active = 'Yes' WHERE m.pid=1  AND (c1.id IS NOT NULL OR c2.id IS 
380
 
NOT NULL);
381
 
drop table t1,t2,t3;
382
 
 
383
 
#
384
 
# Test bug in GROUP BY on BLOB that is NULL or empty
385
 
#
386
 
 
387
 
create table t1 (a blob null);
388
 
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b");
389
 
select a,count(*) from t1 group by a;
390
 
select a,count(*) from t1 group by a;
391
 
drop table t1;
392
 
 
393
 
#
394
 
# Test of GROUP BY ... ORDER BY NULL optimization
395
 
#
396
 
 
397
 
create table t1 (a int not null, b int not null);
398
 
insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1);
399
 
create table t2 (a int not null, b int not null, key(a));
400
 
insert into t2 values (1,3),(3,1),(2,2),(1,1);
401
 
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
402
 
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
403
 
--sorted_result
404
 
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
405
 
--sorted_result
406
 
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
407
 
drop table t1,t2;
408
 
 
409
 
#
410
 
# group function arguments in some functions
411
 
#
412
 
 
413
 
create table t1 (a int, b int);
414
 
insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
415
 
select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
416
 
select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
417
 
select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
418
 
select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
419
 
select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
420
 
select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
421
 
drop table t1;
422
 
 
423
 
#
424
 
# Problem with group by and alias
425
 
#
426
 
 
427
 
create table t1 (id int not null, qty int not null);
428
 
insert into t1 values (1,2),(1,3),(2,4),(2,5);
429
 
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
430
 
select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
431
 
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
432
 
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
433
 
select count(*), case interval(qty,2,3,4,5,6,7,8) when -1 then NULL when 0 then "zero" when 1 then "one" when 2 then "two" end as category from t1 group by category;
434
 
select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
435
 
drop table t1;
436
 
#
437
 
# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY
438
 
# NULL is used.
439
 
#
440
 
CREATE TABLE t1 (
441
 
  userid int,
442
 
  score int,
443
 
  key (score)
444
 
);
445
 
INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
446
 
# Here we select unordered GROUP BY into a temporary talbe, 
447
 
# and then sort it with filesort (GROUP BY in MySQL 
448
 
# implies sorted order of results)
449
 
SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
450
 
EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
451
 
DROP TABLE t1;
452
 
CREATE TABLE t1 (
453
 
  i int default NULL,
454
 
  j int default NULL
455
 
);
456
 
INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
457
 
SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
458
 
explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
459
 
DROP TABLE t1;
460
 
 
461
 
#Test for BUG#6976: Aggregate functions have incorrect NULL-ness
462
 
create table t1 (a int);
463
 
insert into t1 values(null);
464
 
select min(a) is null from t1;
465
 
select min(a) is null or null from t1;
466
 
select 1 and min(a) is null from t1;
467
 
drop table t1;
468
 
 
469
 
# Test for BUG#5400: GROUP_CONCAT returns everything twice.
470
 
create table t1 ( col1 int, col2 int );
471
 
insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
472
 
select group_concat( distinct col1 ) as alias from t1
473
 
  group by col2 having alias like '%';
474
 
 
475
 
drop table t1;
476
 
 
477
 
#
478
 
# Test BUG#8216 when referring in HAVING to n alias which is rand() function
479
 
#
480
 
 
481
 
create table t1 (a integer, b integer, c integer);
482
 
insert into t1 (a,b) values (1,2),(1,3),(2,5);
483
 
select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group  by a having r1>1 and r2=1;
484
 
# rand(100)*10 will be < 2 only for the first row (of 6)
485
 
select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group  by a having r1>1 and r2<=2;
486
 
select a,sum(b) from t1 where a=1 group by c;
487
 
select a*sum(b) from t1 where a=1 group by c;
488
 
select sum(a)*sum(b) from t1 where a=1 group by c;
489
 
select a,sum(b) from t1 where a=1 group by c having a=1;
490
 
select a as d,sum(b) from t1 where a=1 group by c having d=1;
491
 
select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
492
 
drop table t1;
493
 
 
494
 
# Test for BUG#9213 GROUP BY query on utf-8 key returns wrong results
495
 
create table t1(a int);
496
 
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
497
 
create table t2 (
498
 
  a int,
499
 
  b varchar(200) NOT NULL,
500
 
  c varchar(50) NOT NULL,
501
 
  d varchar(100) NOT NULL,
502
 
  primary key (a,b(132),c,d),
503
 
  key a (a,b)
504
 
);
505
 
 
506
 
insert into t2 select 
507
 
   x3.a,  -- 3
508
 
   concat('val-', x3.a + 3*x4.a), -- 12
509
 
   concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
510
 
   concat('val-', @a + 120*D.a)
511
 
from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
512
 
 
513
 
delete from t2  where a = 2 and b = 'val-2' order by a,b,c,d limit 30;
514
 
 
515
 
--replace_column 4 # 7 # 9 # 10 #
516
 
explain select c from t2 where a = 2 and b = 'val-2' group by c;
517
 
select c from t2 where a = 2 and b = 'val-2' group by c;
518
 
drop table t1,t2;
519
 
 
520
 
# Test for BUG#9298 "Wrong handling of int4 columns in GROUP functions"
521
 
# (the actual problem was with protocol code, not GROUP BY)
522
 
create table t1 (b int4 not null);
523
 
insert into t1 values(300000);
524
 
select * from t1;
525
 
select min(b) from t1;
526
 
drop table t1;
527
 
 
528
 
#
529
 
# Test for bug #11088: GROUP BY a BLOB column with COUNT(DISTINCT column1) 
530
 
#
531
 
 
532
 
CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);
533
 
 
534
 
INSERT INTO t1 VALUES
535
 
  (1, 7, 'cache-dtc-af05.proxy.aol.com'),
536
 
  (2, 3, 'what.ever.com'),
537
 
  (3, 7, 'cache-dtc-af05.proxy.aol.com'),
538
 
  (4, 7, 'cache-dtc-af05.proxy.aol.com');
539
 
 
540
 
SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
541
 
  WHERE hostname LIKE '%aol%'
542
 
    GROUP BY hostname;
543
 
 
544
 
DROP TABLE t1;
545
 
 
546
 
#
547
 
# Test for bug #8614: GROUP BY 'const' with DISTINCT  
548
 
#
549
 
 
550
 
CREATE TABLE t1 (a  int, b int);
551
 
INSERT INTO t1 VALUES (1,2), (1,3);
552
 
SELECT a, b FROM t1 GROUP BY 'const';
553
 
SELECT DISTINCT a, b FROM t1 GROUP BY 'const';
554
 
 
555
 
DROP TABLE t1;
556
 
 
557
 
#
558
 
# Test for bug #11385: GROUP BY for datetime converted to decimals  
559
 
#
560
 
 
561
 
CREATE TABLE t1 (id INT, dt DATETIME);
562
 
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
563
 
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
564
 
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
565
 
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
566
 
SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
567
 
 
568
 
DROP TABLE t1;
569
 
 
570
 
#
571
 
# Test for bug #11295: GROUP BY a BLOB column with COUNT(DISTINCT column1) 
572
 
#                      when the BLOB column takes NULL values
573
 
# 
574
 
 
575
 
CREATE TABLE t1 (id varchar(20) NOT NULL);
576
 
INSERT INTO t1 VALUES ('trans1'), ('trans2');
577
 
CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
578
 
INSERT INTO t2 VALUES ('trans1', 'a problem');
579
 
SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
580
 
  FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
581
 
 
582
 
DROP TABLE t1, t2;
583
 
 
584
 
#
585
 
# Bug #12266 GROUP BY expression on DATE column produces result with
586
 
#            reduced length
587
 
#
588
 
create table t1 (f1 date);
589
 
insert into t1 values('2005-06-06');
590
 
insert into t1 values('2005-06-06'); 
591
 
select date(left(f1+0,8)) from t1 group by 1;
592
 
drop table t1;
593
 
 
594
 
#
595
 
# Test for bug #11414: crash on Windows for a simple GROUP BY query 
596
 
#  
597
 
                    
598
 
CREATE TABLE t1 (n int);
599
 
INSERT INTO t1 VALUES (1);
600
 
SELECT n+1 AS n FROM t1 GROUP BY n;
601
 
DROP TABLE t1;
602
 
 
603
 
#
604
 
# BUG#12695: Item_func_isnull::update_used_tables
605
 
# did not update const_item_cache
606
 
#
607
 
create table t1(f1 varchar(5) key);
608
 
insert into t1 values (1),(2);
609
 
select sql_buffer_result max(f1) is null from t1;
610
 
select sql_buffer_result max(f1)+1 from t1;
611
 
drop table t1;
612
 
 
613
 
#
614
 
# BUG#14019-4.1-opt
615
 
#
616
 
CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2);
617
 
 
618
 
SELECT a FROM t1 GROUP BY 'a';
619
 
SELECT a FROM t1 GROUP BY "a";
620
 
SELECT a FROM t1 GROUP BY `a`;
621
 
 
622
 
SELECT a FROM t1 GROUP BY "a";
623
 
SELECT a FROM t1 GROUP BY 'a';
624
 
SELECT a FROM t1 GROUP BY `a`;
625
 
 
626
 
SELECT a FROM t1 HAVING 'a' > 1;
627
 
SELECT a FROM t1 HAVING "a" > 1;
628
 
SELECT a FROM t1 HAVING `a` > 1;
629
 
 
630
 
SELECT a FROM t1 ORDER BY 'a' DESC;
631
 
SELECT a FROM t1 ORDER BY "a" DESC;
632
 
SELECT a FROM t1 ORDER BY `a` DESC;
633
 
DROP TABLE t1;
634
 
 
635
 
#
636
 
# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself
637
 
# returns empty
638
 
# 
639
 
CREATE TABLE t1 (
640
 
    f1 int NOT NULL auto_increment primary key,
641
 
    f2 varchar(100) NOT NULL default ''
642
 
);
643
 
CREATE TABLE t2 (
644
 
    f1 varchar(10) NOT NULL default '',
645
 
    f2 char(3) NOT NULL default '',
646
 
    PRIMARY KEY  (`f1`),
647
 
    KEY `k1` (`f2`,`f1`)
648
 
);
649
 
 
650
 
INSERT INTO t1 values(NULL, '');
651
 
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
652
 
SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
653
 
SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
654
 
DROP TABLE t1, t2;
655
 
 
656
 
 
657
 
# End of 4.1 tests
658
 
 
659
 
#
660
 
# Bug#11211: Ambiguous column reference in GROUP BY.
661
 
#
662
 
 
663
 
create table t1 (c1 char(3), c2 char(3));
664
 
create table t2 (c3 char(3), c4 char(3));
665
 
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
666
 
insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
667
 
 
668
 
# query with ambiguous column reference 'c2'
669
 
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
670
 
group by c2;
671
 
show warnings;
672
 
 
673
 
# this query has no ambiguity
674
 
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
675
 
group by t1.c1;
676
 
 
677
 
show warnings;
678
 
drop table t1, t2;
679
 
 
680
 
#
681
 
# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
682
 
#
683
 
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
684
 
 
685
 
INSERT INTO t1 VALUES (1,      1);
686
 
INSERT INTO t1 SELECT  a + 1 , MOD(a + 1 , 20) FROM t1;
687
 
INSERT INTO t1 SELECT  a + 2 , MOD(a + 2 , 20) FROM t1;
688
 
INSERT INTO t1 SELECT  a + 4 , MOD(a + 4 , 20) FROM t1;
689
 
INSERT INTO t1 SELECT  a + 8 , MOD(a + 8 , 20) FROM t1;
690
 
INSERT INTO t1 SELECT  a + 16, MOD(a + 16, 20) FROM t1;
691
 
INSERT INTO t1 SELECT  a + 32, MOD(a + 32, 20) FROM t1;
692
 
INSERT INTO t1 SELECT  a + 64, MOD(a + 64, 20) FROM t1;
693
 
 
694
 
SELECT MIN(b), MAX(b) from t1;
695
 
 
696
 
EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
697
 
EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
698
 
SELECT b, sum(1) FROM t1 GROUP BY b;
699
 
SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
700
 
DROP TABLE t1;
701
 
 
702
 
#
703
 
# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
704
 
#
705
 
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
706
 
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
707
 
 
708
 
SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
709
 
SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
710
 
SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 
711
 
 GROUP BY b;
712
 
SELECT a + 1 FROM t1 GROUP BY a;
713
 
SELECT a + b FROM t1 GROUP BY b;
714
 
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) 
715
 
  FROM t1 AS t1_outer;
716
 
SELECT 1 FROM t1 as t1_outer GROUP BY a 
717
 
  HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
718
 
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1) 
719
 
  FROM t1 AS t1_outer GROUP BY t1_outer.b;
720
 
--error ER_BAD_FIELD_ERROR 
721
 
SELECT 1 FROM t1 as t1_outer GROUP BY a 
722
 
  HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
723
 
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) 
724
 
  FROM t1 AS t1_outer GROUP BY t1_outer.b;
725
 
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
726
 
  FROM t1 AS t1_outer;
727
 
SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1) 
728
 
  FROM t1 AS t1_outer GROUP BY t1_outer.b;
729
 
 
730
 
SELECT 1 FROM t1 as t1_outer 
731
 
  WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
732
 
 
733
 
SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
734
 
 
735
 
SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
736
 
SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
737
 
 
738
 
--error ER_BAD_FIELD_ERROR
739
 
SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
740
 
--error ER_INVALID_GROUP_FUNC_USE
741
 
SELECT 1 FROM t1 GROUP BY SUM(b);
742
 
SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN 
743
 
  (SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
744
 
   HAVING SUM(t1_inner.b)+t1_outer.b > 5);
745
 
DROP TABLE t1;
746
 
#
747
 
# Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
748
 
#
749
 
create table t1(f1 int, f2 int);
750
 
select * from t1 group by f1;
751
 
select * from t1 group by f2;
752
 
select * from t1 group by f1, f2;
753
 
select t1.f1,t.* from t1, t1 t where t1.f1 >= 0 group by 1;
754
 
drop table t1;
755
 
 
756
 
#
757
 
# Bug #32202: ORDER BY not working with GROUP BY
758
 
#
759
 
 
760
 
CREATE TABLE t1(
761
 
  id INT AUTO_INCREMENT PRIMARY KEY, 
762
 
  c1 INT NOT NULL, 
763
 
  c2 INT NOT NULL,
764
 
  UNIQUE KEY (c2,c1));
765
 
 
766
 
INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
767
 
 
768
 
# Show that the test cases from the bug report pass
769
 
SELECT * FROM t1 ORDER BY c1;
770
 
SELECT * FROM t1 GROUP BY id ORDER BY c1;
771
 
 
772
 
# Show that DESC is handled correctly
773
 
SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
774
 
 
775
 
# Show that results are correctly ordered when ORDER BY fields
776
 
# are a subset of GROUP BY ones
777
 
SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
778
 
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
779
 
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
780
 
 
781
 
# Show that results are correctly ordered when GROUP BY fields
782
 
# are a subset of ORDER BY ones
783
 
SELECT * FROM t1 GROUP BY c2  ORDER BY c2, c1;
784
 
SELECT * FROM t1 GROUP BY c2  ORDER BY c2 DESC, c1;
785
 
SELECT * FROM t1 GROUP BY c2  ORDER BY c2 DESC, c1 DESC;
786
 
 
787
 
DROP TABLE t1;
788
 
 
789
 
 
790
 
--echo #
791
 
--echo # Bug#27219: Aggregate functions in ORDER BY.  
792
 
--echo #
793
 
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
794
 
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
795
 
CREATE TABLE t2 SELECT * FROM t1;
796
 
 
797
 
SELECT 1 FROM t1 ORDER BY COUNT(*);
798
 
SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
799
 
SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
800
 
SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
801
 
SELECT 1 FROM t1 ORDER BY COUNT(*), a;
802
 
 
803
 
SELECT 1 FROM t1 ORDER BY SUM(a);
804
 
SELECT 1 FROM t1 ORDER BY SUM(a + 1);
805
 
SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
806
 
SELECT 1 FROM t1 ORDER BY SUM(a), b;
807
 
 
808
 
SELECT a FROM t1 ORDER BY COUNT(b);
809
 
 
810
 
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
811
 
 
812
 
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
813
 
SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
814
 
 
815
 
SELECT t1.a FROM t1
816
 
  WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
817
 
SELECT t1.a FROM t1 GROUP BY t1.a
818
 
  HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
819
 
 
820
 
SELECT t1.a FROM t1 GROUP BY t1.a
821
 
  HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
822
 
SELECT t1.a FROM t1 GROUP BY t1.a
823
 
  HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
824
 
SELECT t1.a FROM t1 GROUP BY t1.a
825
 
  HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
826
 
 
827
 
SELECT t1.a FROM t1
828
 
  WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
829
 
 
830
 
SELECT 1 FROM t1 GROUP BY t1.a
831
 
  HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
832
 
SELECT 1 FROM t1 GROUP BY t1.a
833
 
  HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
834
 
SELECT 1 FROM t1 GROUP BY t1.a
835
 
  HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
836
 
 
837
 
SELECT 1 FROM t1 GROUP BY t1.a
838
 
  HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
839
 
SELECT 1 FROM t1 GROUP BY t1.a
840
 
  HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
841
 
SELECT 1 FROM t1 GROUP BY t1.a
842
 
  HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
843
 
 
844
 
# Both SUMs are aggregated in the subquery, no mixture:
845
 
SELECT t1.a FROM t1 
846
 
  WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
847
 
                  ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
848
 
 
849
 
# SUM(t1.b) is aggregated in the subquery, no mixture:
850
 
SELECT t1.a, SUM(t1.b) FROM t1 
851
 
  WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
852
 
                  ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
853
 
  GROUP BY t1.a;
854
 
 
855
 
# 2nd SUM(t1.b) is aggregated in the subquery, no mixture:
856
 
SELECT t1.a, SUM(t1.b) FROM t1 
857
 
  WHERE t1.a = (SELECT SUM(t2.b) FROM t2
858
 
                  ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
859
 
  GROUP BY t1.a;
860
 
 
861
 
# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture:
862
 
SELECT t1.a, SUM(t1.b) FROM t1 
863
 
  WHERE t1.a = (SELECT SUM(t2.b) FROM t2
864
 
                  ORDER BY SUM(t2.b + t1.a) LIMIT 1)
865
 
  GROUP BY t1.a;
866
 
 
867
 
SELECT t1.a FROM t1 GROUP BY t1.a
868
 
    HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
869
 
 
870
 
select avg (
871
 
  (select
872
 
    (select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
873
 
   from t1 as outr order by outr.a limit 1))
874
 
from t1 as most_outer;
875
 
 
876
 
select avg (
877
 
  (select (
878
 
    (select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
879
 
   from t1 as outr order by count(outr.a) limit 1)) as tt
880
 
from t1 as most_outer;
881
 
 
882
 
select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
883
 
 
884
 
DROP TABLE t1, t2;
885
 
 
886
 
--echo End of 5.0 tests
887
 
# Bug #21174: Index degrades sort performance and 
888
 
#             optimizer does not honor IGNORE INDEX.
889
 
#             a.k.a WL3527.
890
 
#
891
 
CREATE TABLE t1 (a INT, b INT,
892
 
                 PRIMARY KEY (a),
893
 
                 KEY i2(a,b));
894
 
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
895
 
INSERT INTO t1 SELECT a + 8,b FROM t1;
896
 
INSERT INTO t1 SELECT a + 16,b FROM t1;
897
 
INSERT INTO t1 SELECT a + 32,b FROM t1;
898
 
INSERT INTO t1 SELECT a + 64,b FROM t1;
899
 
INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;
900
 
ANALYZE TABLE t1;
901
 
EXPLAIN SELECT a FROM t1 WHERE a < 2;
902
 
EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
903
 
EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
904
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
905
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
906
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
907
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
908
 
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
909
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
910
 
  IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
911
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
912
 
EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
913
 
EXPLAIN SELECT a FROM t1 USE INDEX ();
914
 
EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
915
 
--error ER_WRONG_USAGE
916
 
EXPLAIN SELECT a FROM t1 
917
 
  FORCE INDEX (PRIMARY) 
918
 
  IGNORE INDEX FOR GROUP BY (i2)
919
 
  IGNORE INDEX FOR ORDER BY (i2)
920
 
  USE INDEX (i2);
921
 
EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
922
 
--error ER_PARSE_ERROR
923
 
EXPLAIN SELECT a FROM t1 FORCE INDEX ();
924
 
--error ER_PARSE_ERROR
925
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
926
 
# disable the columns irrelevant to this test here. On some systems 
927
 
# without support for large files the rowid is shorter and its size affects 
928
 
# the cost calculations. This causes the optimizer to choose loose index
929
 
# scan over normal index access.
930
 
--replace_column 4 # 7 # 9 # 10 #
931
 
EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2) 
932
 
  USE INDEX FOR GROUP BY (i2) GROUP BY a;
933
 
EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) 
934
 
  FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
935
 
EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
936
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
937
 
 
938
 
EXPLAIN SELECT a FROM t1 
939
 
  USE INDEX FOR GROUP BY (i2) 
940
 
  USE INDEX FOR ORDER BY (i2)
941
 
  USE INDEX FOR JOIN (i2);
942
 
 
943
 
EXPLAIN SELECT a FROM t1 
944
 
  USE INDEX FOR JOIN (i2) 
945
 
  USE INDEX FOR JOIN (i2) 
946
 
  USE INDEX FOR JOIN (i2,i2);
947
 
 
948
 
EXPLAIN SELECT 1 FROM t1 WHERE a IN
949
 
  (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
950
 
 
951
 
CREATE TABLE t2 (a INT, b INT, KEY(a));
952
 
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
953
 
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; 
954
 
EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
955
 
 
956
 
EXPLAIN SELECT 1 FROM t2 WHERE a IN
957
 
  (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
958
 
 
959
 
DROP TABLE t1, t2;
960
 
 
961
 
#
962
 
# Bug#30596: GROUP BY optimization gives wrong result order
963
 
#
964
 
CREATE TABLE t1(
965
 
  a INT, 
966
 
  b INT NOT NULL, 
967
 
  c INT NOT NULL, 
968
 
  d INT, 
969
 
  UNIQUE KEY (c,b)
970
 
);
971
 
 
972
 
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
973
 
 
974
 
CREATE TABLE t2(
975
 
  a INT,
976
 
  b INT,
977
 
  UNIQUE KEY(a,b)
978
 
);
979
 
 
980
 
INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
981
 
 
982
 
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
983
 
SELECT c,b,d FROM t1 GROUP BY c,b,d;
984
 
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
985
 
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
986
 
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
987
 
SELECT c,b,d FROM t1 ORDER BY c,b,d;
988
 
 
989
 
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
990
 
SELECT c,b,d FROM t1 GROUP BY c,b;
991
 
EXPLAIN SELECT c,b   FROM t1 GROUP BY c,b;
992
 
SELECT c,b   FROM t1 GROUP BY c,b;
993
 
 
994
 
EXPLAIN SELECT a,b from t2 ORDER BY a,b;
995
 
SELECT a,b from t2 ORDER BY a,b;
996
 
EXPLAIN SELECT a,b from t2 GROUP BY a,b;
997
 
SELECT a,b from t2 GROUP BY a,b;
998
 
EXPLAIN SELECT a from t2 GROUP BY a;
999
 
SELECT a from t2 GROUP BY a;
1000
 
EXPLAIN SELECT b from t2 GROUP BY b;
1001
 
SELECT b from t2 GROUP BY b;
1002
 
 
1003
 
DROP TABLE t1,t2;
1004
 
 
1005
 
#
1006
 
# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING
1007
 
#
1008
 
CREATE TABLE t1 ( a INT, b INT );
1009
 
 
1010
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1011
 
FROM t1;
1012
 
 
1013
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1014
 
FROM t1 
1015
 
HAVING b = 10;
1016
 
 
1017
 
--error ER_ILLEGAL_REFERENCE
1018
 
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1019
 
FROM t1 
1020
 
HAVING b = 10;
1021
 
 
1022
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1023
 
FROM t1;
1024
 
 
1025
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1026
 
FROM t1 
1027
 
HAVING b = 10;
1028
 
 
1029
 
--error ER_ILLEGAL_REFERENCE
1030
 
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1031
 
FROM t1 
1032
 
HAVING b = 10;
1033
 
 
1034
 
INSERT INTO t1 VALUES (1, 1);
1035
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1036
 
FROM t1;
1037
 
 
1038
 
INSERT INTO t1 VALUES (2, 1);
1039
 
--error ER_SUBQUERY_NO_1_ROW
1040
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1041
 
FROM t1;
1042
 
 
1043
 
DROP TABLE t1;
1044
 
 
1045
 
 
1046