← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/group_by.test

  • Committer: Brian Aker
  • Date: 2008-07-28 18:01:38 UTC
  • Revision ID: brian@tangent.org-20080728180138-q2pxlq0qiapvqsdn
Remove YEAR field type

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