← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/group_by.test

  • Committer: Mark Atwood
  • Date: 2008-10-03 01:39:40 UTC
  • mto: This revision was merged to the branch mainline in revision 437.
  • Revision ID: mark@fallenpegasus.com-20081003013940-mvefjo725dltz41h
rename logging_noop to logging_query

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 ER_WRONG_GROUP_FIELD
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 ER_WARN_DATA_TRUNCATED
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
 
--sorted_result
403
 
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
404
 
--sorted_result
405
 
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
406
 
drop table t1,t2;
407
 
 
408
 
#
409
 
# group function arguments in some functions
410
 
#
411
 
 
412
 
create table t1 (a int, b int);
413
 
insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
414
 
select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
415
 
select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
416
 
select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
417
 
select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
418
 
select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
419
 
select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
420
 
drop table t1;
421
 
 
422
 
#
423
 
# Problem with group by and alias
424
 
#
425
 
 
426
 
create table t1 (id int not null, qty int not null);
427
 
insert into t1 values (1,2),(1,3),(2,4),(2,5);
428
 
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
429
 
select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
430
 
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
431
 
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
432
 
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;
433
 
select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
434
 
drop table t1;
435
 
#
436
 
# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY
437
 
# NULL is used.
438
 
#
439
 
CREATE TABLE t1 (
440
 
  userid int,
441
 
  score int,
442
 
  key (score)
443
 
);
444
 
INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
445
 
# Here we select unordered GROUP BY into a temporary talbe, 
446
 
# and then sort it with filesort (GROUP BY in MySQL 
447
 
# implies sorted order of results)
448
 
SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
449
 
EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
450
 
DROP TABLE t1;
451
 
CREATE TABLE t1 (
452
 
  i int default NULL,
453
 
  j int default NULL
454
 
);
455
 
INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
456
 
SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
457
 
explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
458
 
DROP TABLE t1;
459
 
 
460
 
#Test for BUG#6976: Aggregate functions have incorrect NULL-ness
461
 
create table t1 (a int);
462
 
insert into t1 values(null);
463
 
select min(a) is null from t1;
464
 
select min(a) is null or null from t1;
465
 
select 1 and min(a) is null from t1;
466
 
drop table t1;
467
 
 
468
 
# Test for BUG#5400: GROUP_CONCAT returns everything twice.
469
 
create table t1 ( col1 int, col2 int );
470
 
insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
471
 
select group_concat( distinct col1 ) as alias from t1
472
 
  group by col2 having alias like '%';
473
 
 
474
 
drop table t1;
475
 
 
476
 
#
477
 
# Test BUG#8216 when referring in HAVING to n alias which is rand() function
478
 
#
479
 
 
480
 
create table t1 (a integer, b integer, c integer);
481
 
insert into t1 (a,b) values (1,2),(1,3),(2,5);
482
 
select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group  by a having r1>1 and r2=1;
483
 
# rand(100)*10 will be < 2 only for the first row (of 6)
484
 
select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group  by a having r1>1 and r2<=2;
485
 
select a,sum(b) from t1 where a=1 group by c;
486
 
select a*sum(b) from t1 where a=1 group by c;
487
 
select sum(a)*sum(b) from t1 where a=1 group by c;
488
 
select a,sum(b) from t1 where a=1 group by c having a=1;
489
 
select a as d,sum(b) from t1 where a=1 group by c having d=1;
490
 
select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
491
 
drop table t1;
492
 
 
493
 
# Test for BUG#9213 GROUP BY query on utf-8 key returns wrong results
494
 
create table t1(a int);
495
 
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
496
 
create table t2 (
497
 
  a int,
498
 
  b varchar(200) NOT NULL,
499
 
  c varchar(50) NOT NULL,
500
 
  d varchar(100) NOT NULL,
501
 
  primary key (a,b(132),c,d),
502
 
  key a (a,b)
503
 
);
504
 
 
505
 
insert into t2 select 
506
 
   x3.a,  -- 3
507
 
   concat('val-', x3.a + 3*x4.a), -- 12
508
 
   concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
509
 
   concat('val-', @a + 120*D.a)
510
 
from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
511
 
 
512
 
delete from t2  where a = 2 and b = 'val-2' order by a,b,c,d limit 30;
513
 
 
514
 
--replace_column 4 # 7 # 9 # 10 #
515
 
explain select c from t2 where a = 2 and b = 'val-2' group by c;
516
 
select c from t2 where a = 2 and b = 'val-2' group by c;
517
 
drop table t1,t2;
518
 
 
519
 
# Test for BUG#9298 "Wrong handling of int4 columns in GROUP functions"
520
 
# (the actual problem was with protocol code, not GROUP BY)
521
 
create table t1 (b int4 not null);
522
 
insert into t1 values(300000);
523
 
select * from t1;
524
 
select min(b) from t1;
525
 
drop table t1;
526
 
 
527
 
#
528
 
# Test for bug #11088: GROUP BY a BLOB column with COUNT(DISTINCT column1) 
529
 
#
530
 
 
531
 
CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);
532
 
 
533
 
INSERT INTO t1 VALUES
534
 
  (1, 7, 'cache-dtc-af05.proxy.aol.com'),
535
 
  (2, 3, 'what.ever.com'),
536
 
  (3, 7, 'cache-dtc-af05.proxy.aol.com'),
537
 
  (4, 7, 'cache-dtc-af05.proxy.aol.com');
538
 
 
539
 
SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
540
 
  WHERE hostname LIKE '%aol%'
541
 
    GROUP BY hostname;
542
 
 
543
 
DROP TABLE t1;
544
 
 
545
 
#
546
 
# Test for bug #8614: GROUP BY 'const' with DISTINCT  
547
 
#
548
 
 
549
 
CREATE TABLE t1 (a  int, b int);
550
 
INSERT INTO t1 VALUES (1,2), (1,3);
551
 
SELECT a, b FROM t1 GROUP BY 'const';
552
 
SELECT DISTINCT a, b FROM t1 GROUP BY 'const';
553
 
 
554
 
DROP TABLE t1;
555
 
 
556
 
#
557
 
# Test for bug #11385: GROUP BY for datetime converted to decimals  
558
 
#
559
 
 
560
 
CREATE TABLE t1 (id INT, dt DATETIME);
561
 
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
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
 
SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
566
 
 
567
 
DROP TABLE t1;
568
 
 
569
 
#
570
 
# Test for bug #11295: GROUP BY a BLOB column with COUNT(DISTINCT column1) 
571
 
#                      when the BLOB column takes NULL values
572
 
# 
573
 
 
574
 
CREATE TABLE t1 (id varchar(20) NOT NULL);
575
 
INSERT INTO t1 VALUES ('trans1'), ('trans2');
576
 
CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
577
 
INSERT INTO t2 VALUES ('trans1', 'a problem');
578
 
SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
579
 
  FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
580
 
 
581
 
DROP TABLE t1, t2;
582
 
 
583
 
#
584
 
# Bug #12266 GROUP BY expression on DATE column produces result with
585
 
#            reduced length
586
 
#
587
 
create table t1 (f1 date);
588
 
insert into t1 values('2005-06-06');
589
 
insert into t1 values('2005-06-06'); 
590
 
select date(left(f1+0,8)) from t1 group by 1;
591
 
drop table t1;
592
 
 
593
 
#
594
 
# Test for bug #11414: crash on Windows for a simple GROUP BY query 
595
 
#  
596
 
                    
597
 
CREATE TABLE t1 (n int);
598
 
INSERT INTO t1 VALUES (1);
599
 
SELECT n+1 AS n FROM t1 GROUP BY n;
600
 
DROP TABLE t1;
601
 
 
602
 
#
603
 
# BUG#12695: Item_func_isnull::update_used_tables
604
 
# did not update const_item_cache
605
 
#
606
 
create table t1(f1 varchar(5) key);
607
 
insert into t1 values (1),(2);
608
 
select sql_buffer_result max(f1) is null from t1;
609
 
select sql_buffer_result max(f1)+1 from t1;
610
 
drop table t1;
611
 
 
612
 
#
613
 
# BUG#14019-4.1-opt
614
 
#
615
 
CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2);
616
 
 
617
 
SELECT a FROM t1 GROUP BY 'a';
618
 
SELECT a FROM t1 GROUP BY "a";
619
 
SELECT a FROM t1 GROUP BY `a`;
620
 
 
621
 
SELECT a FROM t1 GROUP BY "a";
622
 
SELECT a FROM t1 GROUP BY 'a';
623
 
SELECT a FROM t1 GROUP BY `a`;
624
 
 
625
 
SELECT a FROM t1 HAVING 'a' > 1;
626
 
SELECT a FROM t1 HAVING "a" > 1;
627
 
SELECT a FROM t1 HAVING `a` > 1;
628
 
 
629
 
SELECT a FROM t1 ORDER BY 'a' DESC;
630
 
SELECT a FROM t1 ORDER BY "a" DESC;
631
 
SELECT a FROM t1 ORDER BY `a` DESC;
632
 
DROP TABLE t1;
633
 
 
634
 
#
635
 
# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself
636
 
# returns empty
637
 
# 
638
 
CREATE TABLE t1 (
639
 
    f1 int NOT NULL auto_increment primary key,
640
 
    f2 varchar(100) NOT NULL default ''
641
 
);
642
 
CREATE TABLE t2 (
643
 
    f1 varchar(10) NOT NULL default '',
644
 
    f2 char(3) NOT NULL default '',
645
 
    PRIMARY KEY  (`f1`),
646
 
    KEY `k1` (`f2`,`f1`)
647
 
);
648
 
 
649
 
INSERT INTO t1 values(NULL, '');
650
 
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
651
 
SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
652
 
SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
653
 
DROP TABLE t1, t2;
654
 
 
655
 
 
656
 
# End of 4.1 tests
657
 
 
658
 
#
659
 
# Bug#11211: Ambiguous column reference in GROUP BY.
660
 
#
661
 
 
662
 
create table t1 (c1 char(3), c2 char(3));
663
 
create table t2 (c3 char(3), c4 char(3));
664
 
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
665
 
insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
666
 
 
667
 
# query with ambiguous column reference 'c2'
668
 
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
669
 
group by c2;
670
 
show warnings;
671
 
 
672
 
# this query has no ambiguity
673
 
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
674
 
group by t1.c1;
675
 
 
676
 
show warnings;
677
 
drop table t1, t2;
678
 
 
679
 
#
680
 
# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
681
 
#
682
 
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
683
 
 
684
 
INSERT INTO t1 VALUES (1,      1);
685
 
INSERT INTO t1 SELECT  a + 1 , MOD(a + 1 , 20) FROM t1;
686
 
INSERT INTO t1 SELECT  a + 2 , MOD(a + 2 , 20) FROM t1;
687
 
INSERT INTO t1 SELECT  a + 4 , MOD(a + 4 , 20) FROM t1;
688
 
INSERT INTO t1 SELECT  a + 8 , MOD(a + 8 , 20) FROM t1;
689
 
INSERT INTO t1 SELECT  a + 16, MOD(a + 16, 20) FROM t1;
690
 
INSERT INTO t1 SELECT  a + 32, MOD(a + 32, 20) FROM t1;
691
 
INSERT INTO t1 SELECT  a + 64, MOD(a + 64, 20) FROM t1;
692
 
 
693
 
SELECT MIN(b), MAX(b) from t1;
694
 
 
695
 
EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
696
 
EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
697
 
SELECT b, sum(1) FROM t1 GROUP BY b;
698
 
SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
699
 
DROP TABLE t1;
700
 
 
701
 
#
702
 
# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
703
 
#
704
 
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
705
 
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
706
 
 
707
 
SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
708
 
SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
709
 
SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 
710
 
 GROUP BY b;
711
 
SELECT a + 1 FROM t1 GROUP BY a;
712
 
SELECT a + b FROM t1 GROUP BY b;
713
 
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) 
714
 
  FROM t1 AS t1_outer;
715
 
SELECT 1 FROM t1 as t1_outer GROUP BY a 
716
 
  HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
717
 
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1) 
718
 
  FROM t1 AS t1_outer GROUP BY t1_outer.b;
719
 
--error ER_BAD_FIELD_ERROR 
720
 
SELECT 1 FROM t1 as t1_outer GROUP BY a 
721
 
  HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
722
 
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) 
723
 
  FROM t1 AS t1_outer GROUP BY t1_outer.b;
724
 
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
725
 
  FROM t1 AS t1_outer;
726
 
SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1) 
727
 
  FROM t1 AS t1_outer GROUP BY t1_outer.b;
728
 
 
729
 
SELECT 1 FROM t1 as t1_outer 
730
 
  WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
731
 
 
732
 
SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
733
 
 
734
 
SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
735
 
SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
736
 
 
737
 
--error ER_BAD_FIELD_ERROR
738
 
SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
739
 
--error ER_INVALID_GROUP_FUNC_USE
740
 
SELECT 1 FROM t1 GROUP BY SUM(b);
741
 
SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN 
742
 
  (SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
743
 
   HAVING SUM(t1_inner.b)+t1_outer.b > 5);
744
 
DROP TABLE t1;
745
 
#
746
 
# Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
747
 
#
748
 
create table t1(f1 int, f2 int);
749
 
select * from t1 group by f1;
750
 
select * from t1 group by f2;
751
 
select * from t1 group by f1, f2;
752
 
select t1.f1,t.* from t1, t1 t group by 1;
753
 
drop table t1;
754
 
 
755
 
#
756
 
# Bug #32202: ORDER BY not working with GROUP BY
757
 
#
758
 
 
759
 
CREATE TABLE t1(
760
 
  id INT AUTO_INCREMENT PRIMARY KEY, 
761
 
  c1 INT NOT NULL, 
762
 
  c2 INT NOT NULL,
763
 
  UNIQUE KEY (c2,c1));
764
 
 
765
 
INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
766
 
 
767
 
# Show that the test cases from the bug report pass
768
 
SELECT * FROM t1 ORDER BY c1;
769
 
SELECT * FROM t1 GROUP BY id ORDER BY c1;
770
 
 
771
 
# Show that DESC is handled correctly
772
 
SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
773
 
 
774
 
# Show that results are correctly ordered when ORDER BY fields
775
 
# are a subset of GROUP BY ones
776
 
SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
777
 
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
778
 
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
779
 
 
780
 
# Show that results are correctly ordered when GROUP BY fields
781
 
# are a subset of ORDER BY ones
782
 
SELECT * FROM t1 GROUP BY c2  ORDER BY c2, c1;
783
 
SELECT * FROM t1 GROUP BY c2  ORDER BY c2 DESC, c1;
784
 
SELECT * FROM t1 GROUP BY c2  ORDER BY c2 DESC, c1 DESC;
785
 
 
786
 
DROP TABLE t1;
787
 
 
788
 
 
789
 
--echo #
790
 
--echo # Bug#27219: Aggregate functions in ORDER BY.  
791
 
--echo #
792
 
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
793
 
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
794
 
CREATE TABLE t2 SELECT * FROM t1;
795
 
 
796
 
SELECT 1 FROM t1 ORDER BY COUNT(*);
797
 
SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
798
 
SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
799
 
SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
800
 
SELECT 1 FROM t1 ORDER BY COUNT(*), a;
801
 
 
802
 
SELECT 1 FROM t1 ORDER BY SUM(a);
803
 
SELECT 1 FROM t1 ORDER BY SUM(a + 1);
804
 
SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
805
 
SELECT 1 FROM t1 ORDER BY SUM(a), b;
806
 
 
807
 
SELECT a FROM t1 ORDER BY COUNT(b);
808
 
 
809
 
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
810
 
 
811
 
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
812
 
SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
813
 
 
814
 
SELECT t1.a FROM t1
815
 
  WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
816
 
SELECT t1.a FROM t1 GROUP BY t1.a
817
 
  HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
818
 
 
819
 
SELECT t1.a FROM t1 GROUP BY t1.a
820
 
  HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
821
 
SELECT t1.a FROM t1 GROUP BY t1.a
822
 
  HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
823
 
SELECT t1.a FROM t1 GROUP BY t1.a
824
 
  HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
825
 
 
826
 
SELECT t1.a FROM t1
827
 
  WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
828
 
 
829
 
SELECT 1 FROM t1 GROUP BY t1.a
830
 
  HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
831
 
SELECT 1 FROM t1 GROUP BY t1.a
832
 
  HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
833
 
SELECT 1 FROM t1 GROUP BY t1.a
834
 
  HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
835
 
 
836
 
SELECT 1 FROM t1 GROUP BY t1.a
837
 
  HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
838
 
SELECT 1 FROM t1 GROUP BY t1.a
839
 
  HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
840
 
SELECT 1 FROM t1 GROUP BY t1.a
841
 
  HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
842
 
 
843
 
# Both SUMs are aggregated in the subquery, no mixture:
844
 
SELECT t1.a FROM t1 
845
 
  WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
846
 
                  ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
847
 
 
848
 
# SUM(t1.b) is aggregated in the subquery, no mixture:
849
 
SELECT t1.a, SUM(t1.b) FROM t1 
850
 
  WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
851
 
                  ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
852
 
  GROUP BY t1.a;
853
 
 
854
 
# 2nd SUM(t1.b) is aggregated in the subquery, no mixture:
855
 
SELECT t1.a, SUM(t1.b) FROM t1 
856
 
  WHERE t1.a = (SELECT SUM(t2.b) FROM t2
857
 
                  ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
858
 
  GROUP BY t1.a;
859
 
 
860
 
# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture:
861
 
SELECT t1.a, SUM(t1.b) FROM t1 
862
 
  WHERE t1.a = (SELECT SUM(t2.b) FROM t2
863
 
                  ORDER BY SUM(t2.b + t1.a) LIMIT 1)
864
 
  GROUP BY t1.a;
865
 
 
866
 
SELECT t1.a FROM t1 GROUP BY t1.a
867
 
    HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
868
 
 
869
 
select avg (
870
 
  (select
871
 
    (select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
872
 
   from t1 as outr order by outr.a limit 1))
873
 
from t1 as most_outer;
874
 
 
875
 
select avg (
876
 
  (select (
877
 
    (select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
878
 
   from t1 as outr order by count(outr.a) limit 1)) as tt
879
 
from t1 as most_outer;
880
 
 
881
 
select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
882
 
 
883
 
DROP TABLE t1, t2;
884
 
 
885
 
--echo End of 5.0 tests
886
 
# Bug #21174: Index degrades sort performance and 
887
 
#             optimizer does not honor IGNORE INDEX.
888
 
#             a.k.a WL3527.
889
 
#
890
 
CREATE TABLE t1 (a INT, b INT,
891
 
                 PRIMARY KEY (a),
892
 
                 KEY i2(a,b));
893
 
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
894
 
INSERT INTO t1 SELECT a + 8,b FROM t1;
895
 
INSERT INTO t1 SELECT a + 16,b FROM t1;
896
 
INSERT INTO t1 SELECT a + 32,b FROM t1;
897
 
INSERT INTO t1 SELECT a + 64,b FROM t1;
898
 
INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;
899
 
ANALYZE TABLE t1;
900
 
EXPLAIN SELECT a FROM t1 WHERE a < 2;
901
 
EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
902
 
EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
903
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
904
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
905
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
906
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
907
 
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
908
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
909
 
  IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
910
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
911
 
EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
912
 
EXPLAIN SELECT a FROM t1 USE INDEX ();
913
 
EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
914
 
--error ER_WRONG_USAGE
915
 
EXPLAIN SELECT a FROM t1 
916
 
  FORCE INDEX (PRIMARY) 
917
 
  IGNORE INDEX FOR GROUP BY (i2)
918
 
  IGNORE INDEX FOR ORDER BY (i2)
919
 
  USE INDEX (i2);
920
 
EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
921
 
--error ER_PARSE_ERROR
922
 
EXPLAIN SELECT a FROM t1 FORCE INDEX ();
923
 
--error ER_PARSE_ERROR
924
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
925
 
# disable the columns irrelevant to this test here. On some systems 
926
 
# without support for large files the rowid is shorter and its size affects 
927
 
# the cost calculations. This causes the optimizer to choose loose index
928
 
# scan over normal index access.
929
 
--replace_column 4 # 7 # 9 # 10 #
930
 
EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2) 
931
 
  USE INDEX FOR GROUP BY (i2) GROUP BY a;
932
 
EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) 
933
 
  FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
934
 
EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
935
 
EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
936
 
 
937
 
EXPLAIN SELECT a FROM t1 
938
 
  USE INDEX FOR GROUP BY (i2) 
939
 
  USE INDEX FOR ORDER BY (i2)
940
 
  USE INDEX FOR JOIN (i2);
941
 
 
942
 
EXPLAIN SELECT a FROM t1 
943
 
  USE INDEX FOR JOIN (i2) 
944
 
  USE INDEX FOR JOIN (i2) 
945
 
  USE INDEX FOR JOIN (i2,i2);
946
 
 
947
 
EXPLAIN SELECT 1 FROM t1 WHERE a IN
948
 
  (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
949
 
 
950
 
CREATE TABLE t2 (a INT, b INT, KEY(a));
951
 
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
952
 
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; 
953
 
EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
954
 
 
955
 
EXPLAIN SELECT 1 FROM t2 WHERE a IN
956
 
  (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
957
 
 
958
 
DROP TABLE t1, t2;
959
 
 
960
 
#
961
 
# Bug#30596: GROUP BY optimization gives wrong result order
962
 
#
963
 
CREATE TABLE t1(
964
 
  a INT, 
965
 
  b INT NOT NULL, 
966
 
  c INT NOT NULL, 
967
 
  d INT, 
968
 
  UNIQUE KEY (c,b)
969
 
);
970
 
 
971
 
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
972
 
 
973
 
CREATE TABLE t2(
974
 
  a INT,
975
 
  b INT,
976
 
  UNIQUE KEY(a,b)
977
 
);
978
 
 
979
 
INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
980
 
 
981
 
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
982
 
SELECT c,b,d FROM t1 GROUP BY c,b,d;
983
 
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
984
 
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
985
 
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
986
 
SELECT c,b,d FROM t1 ORDER BY c,b,d;
987
 
 
988
 
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
989
 
SELECT c,b,d FROM t1 GROUP BY c,b;
990
 
EXPLAIN SELECT c,b   FROM t1 GROUP BY c,b;
991
 
SELECT c,b   FROM t1 GROUP BY c,b;
992
 
 
993
 
EXPLAIN SELECT a,b from t2 ORDER BY a,b;
994
 
SELECT a,b from t2 ORDER BY a,b;
995
 
EXPLAIN SELECT a,b from t2 GROUP BY a,b;
996
 
SELECT a,b from t2 GROUP BY a,b;
997
 
EXPLAIN SELECT a from t2 GROUP BY a;
998
 
SELECT a from t2 GROUP BY a;
999
 
EXPLAIN SELECT b from t2 GROUP BY b;
1000
 
SELECT b from t2 GROUP BY b;
1001
 
 
1002
 
DROP TABLE t1,t2;
1003
 
 
1004
 
#
1005
 
# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING
1006
 
#
1007
 
CREATE TABLE t1 ( a INT, b INT );
1008
 
 
1009
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1010
 
FROM t1;
1011
 
 
1012
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1013
 
FROM t1 
1014
 
HAVING b = 10;
1015
 
 
1016
 
--error ER_ILLEGAL_REFERENCE
1017
 
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1018
 
FROM t1 
1019
 
HAVING b = 10;
1020
 
 
1021
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1022
 
FROM t1;
1023
 
 
1024
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1025
 
FROM t1 
1026
 
HAVING b = 10;
1027
 
 
1028
 
--error ER_ILLEGAL_REFERENCE
1029
 
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1030
 
FROM t1 
1031
 
HAVING b = 10;
1032
 
 
1033
 
INSERT INTO t1 VALUES (1, 1);
1034
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1035
 
FROM t1;
1036
 
 
1037
 
INSERT INTO t1 VALUES (2, 1);
1038
 
--error ER_SUBQUERY_NO_1_ROW
1039
 
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1040
 
FROM t1;
1041
 
 
1042
 
DROP TABLE t1;
1043
 
 
1044
 
 
1045