~stewart/drizzle/embedded-innodb-create-select-transaction-arrgh

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;

drop table t1;

# Problem with GROUP BY + ORDER BY when no match

# Tested with locking

CREATE TABLE t1 (

cid mediumint(9) NOT NULL auto_increment,

firstname varchar(32) DEFAULT '' NOT NULL,

surname varchar(32) DEFAULT '' NOT NULL,

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 mediumint(8) NOT NULL auto_increment,

106

contact_id mediumint(8) DEFAULT '0' NOT NULL,

107

PRIMARY KEY (call_id),

108

KEY contact_id (contact_id)

109

);

110

111

lock tables t1 read,t2 write;

112

113

INSERT INTO t2 VALUES (10,2);

114

INSERT INTO t2 VALUES (18,2);

115

INSERT INTO t2 VALUES (62,2);

116

INSERT INTO t2 VALUES (91,2);

117

INSERT INTO t2 VALUES (92,2);

118

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;

120

SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY NULL;

121

SELECT HIGH_PRIORITY 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;

122

123

drop table t2;

124

unlock tables;

125

drop table t1;

126

127

128

# Test of group by bug in bugzilla

129

130

131

CREATE TABLE t1 (

132

bug_id mediumint(9) NOT NULL auto_increment,

133

groupset bigint(20) DEFAULT '0' NOT NULL,

134

assigned_to mediumint(9) DEFAULT '0' NOT NULL,

135

bug_file_loc text,

136

bug_severity enum('blocker','critical','major','normal','minor','trivial','enhancement') DEFAULT 'blocker' NOT NULL,

137

bug_status enum('','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED') DEFAULT 'NEW' NOT NULL,

138

creation_ts datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,

139

delta_ts timestamp,

140

short_desc mediumtext,

141

long_desc mediumtext,

142

op_sys enum('All','Windows 3.1','Windows 95','Windows 98','Windows NT','Windows 2000','Linux','other') DEFAULT 'All' NOT NULL,

143

priority enum('P1','P2','P3','P4','P5') DEFAULT 'P1' NOT NULL,

144

product varchar(64) DEFAULT '' NOT NULL,

145

rep_platform enum('All','PC','VTD-8','Other'),

146

reporter mediumint(9) DEFAULT '0' NOT NULL,

147

version varchar(16) DEFAULT '' NOT NULL,

148

component varchar(50) DEFAULT '' NOT NULL,

149

resolution enum('','FIXED','INVALID','WONTFIX','LATER','REMIND','DUPLICATE','WORKSFORME') DEFAULT '' NOT NULL,

150

target_milestone varchar(20) DEFAULT '' NOT NULL,

151

qa_contact mediumint(9) DEFAULT '0' NOT NULL,

152

status_whiteboard mediumtext NOT NULL,

153

votes mediumint(9) DEFAULT '0' NOT NULL,

154

PRIMARY KEY (bug_id),

155

KEY assigned_to (assigned_to),

156

KEY creation_ts (creation_ts),

157

KEY delta_ts (delta_ts),

158

KEY bug_severity (bug_severity),

159

KEY bug_status (bug_status),

160

KEY op_sys (op_sys),

161

KEY priority (priority),

162

KEY product (product),

163

KEY reporter (reporter),

164

KEY version (version),

165

KEY component (component),

166

KEY resolution (resolution),

167

KEY target_milestone (target_milestone),

168

KEY qa_contact (qa_contact),

169

KEY votes (votes)

170

);

171

172

INSERT INTO t1 VALUES (1,0,0,'','normal','','2000-02-10 09:25:12',20000321114747,'','','Linux','P1','TestProduct','PC',3,'other','TestComponent','','M1',0,'',0);

173

INSERT INTO t1 VALUES (9,0,0,'','enhancement','','2000-03-10 11:49:36',20000321114747,'','','All','P5','AAAAA','PC',3,'2.00 CD - Pre','BBBBBBBBBBBBB - conversion','','',0,'',0);

174

INSERT INTO t1 VALUES (10,0,0,'','enhancement','','2000-03-10 18:10:16',20000321114747,'','','All','P4','AAAAA','PC',3,'2.00 CD - Pre','BBBBBBBBBBBBB - conversion','','',0,'',0);

175

INSERT INTO t1 VALUES (7,0,0,'','critical','','2000-03-09 10:50:21',20000321114747,'','','All','P1','AAAAA','PC',3,'2.00 CD - Pre','BBBBBBBBBBBBB - generic','','',0,'',0);

176

INSERT INTO t1 VALUES (6,0,0,'','normal','','2000-03-09 10:42:44',20000321114747,'','','All','P2','AAAAA','PC',3,'2.00 CD - Pre','kkkkkkkkkkk lllllllllll','','',0,'',0);

177

INSERT INTO t1 VALUES (8,0,0,'','major','','2000-03-09 11:32:14',20000321114747,'','','All','P3','AAAAA','PC',3,'2.00 CD - Pre','kkkkkkkkkkk lllllllllll','','',0,'',0);

178

INSERT INTO t1 VALUES (5,0,0,'','enhancement','','2000-03-09 10:38:59',20000321114747,'','','All','P5','CCC/CCCCCC','PC',5,'7.00','Administration','','',0,'',0);

179

INSERT INTO t1 VALUES (4,0,0,'','normal','','2000-03-08 18:32:14',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent2','','',0,'',0);

180

INSERT INTO t1 VALUES (3,0,0,'','normal','','2000-03-08 18:30:52',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0);

181

INSERT INTO t1 VALUES (2,0,0,'','enhancement','','2000-03-08 18:24:51',20000321114747,'','','All','P2','TestProduct','Other',4,'other','TestComponent2','','',0,'',0);

182

INSERT INTO t1 VALUES (11,0,0,'','blocker','','2000-03-13 09:43:41',20000321114747,'','','All','P2','CCC/CCCCCC','PC',5,'7.00','DDDDDDDDD','','',0,'',0);

183

INSERT INTO t1 VALUES (12,0,0,'','normal','','2000-03-13 16:14:31',20000321114747,'','','All','P2','AAAAA','PC',3,'2.00 CD - Pre','kkkkkkkkkkk lllllllllll','','',0,'',0);

184

INSERT INTO t1 VALUES (13,0,0,'','normal','','2000-03-15 16:20:44',20000321114747,'','','other','P2','TestProduct','Other',3,'other','TestComponent','','',0,'',0);

185

INSERT INTO t1 VALUES (14,0,0,'','blocker','','2000-03-15 18:13:47',20000321114747,'','','All','P1','AAAAA','PC',3,'2.00 CD - Pre','BBBBBBBBBBBBB - generic','','',0,'',0);

186

INSERT INTO t1 VALUES (15,0,0,'','minor','','2000-03-16 18:03:28',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','DDDDDDDDD','','',0,'',0);

187

INSERT INTO t1 VALUES (16,0,0,'','normal','','2000-03-16 18:33:41',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);

188

INSERT INTO t1 VALUES (17,0,0,'','normal','','2000-03-16 18:34:18',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);

189

INSERT INTO t1 VALUES (18,0,0,'','normal','','2000-03-16 18:34:56',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);

190

INSERT INTO t1 VALUES (19,0,0,'','enhancement','','2000-03-16 18:35:34',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);

191

INSERT INTO t1 VALUES (20,0,0,'','enhancement','','2000-03-16 18:36:23',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);

192

INSERT INTO t1 VALUES (21,0,0,'','enhancement','','2000-03-16 18:37:23',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);

193

INSERT INTO t1 VALUES (22,0,0,'','enhancement','','2000-03-16 18:38:16',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','Administration','','',0,'',0);

194

INSERT INTO t1 VALUES (23,0,0,'','normal','','2000-03-16 18:58:12',20000321114747,'','','All','P2','CCC/CCCCCC','Other',5,'7.00','DDDDDDDDD','','',0,'',0);

195

INSERT INTO t1 VALUES (24,0,0,'','normal','','2000-03-17 11:08:10',20000321114747,'','','All','P2','AAAAAAAA-AAA','PC',3,'2.8','Web Interface','','',0,'',0);

196

INSERT INTO t1 VALUES (25,0,0,'','normal','','2000-03-17 11:10:45',20000321114747,'','','All','P2','AAAAAAAA-AAA','PC',3,'2.8','Web Interface','','',0,'',0);

197

INSERT INTO t1 VALUES (26,0,0,'','normal','','2000-03-17 11:15:47',20000321114747,'','','All','P2','AAAAAAAA-AAA','PC',3,'2.8','Web Interface','','',0,'',0);

198

INSERT INTO t1 VALUES (27,0,0,'','normal','','2000-03-17 17:45:41',20000321114747,'','','All','P2','CCC/CCCCCC','PC',5,'7.00','DDDDDDDDD','','',0,'',0);

199

INSERT INTO t1 VALUES (28,0,0,'','normal','','2000-03-20 09:51:45',20000321114747,'','','Windows NT','P2','TestProduct','PC',8,'other','TestComponent','','',0,'',0);

200

INSERT INTO t1 VALUES (29,0,0,'','normal','','2000-03-20 11:15:09',20000321114747,'','','All','P5','AAAAAAAA-AAA','PC',3,'2.8','Web Interface','','',0,'',0);

201

CREATE TABLE t2 (

202

value tinytext,

203

program varchar(64),

204

initialowner tinytext NOT NULL,

205

initialqacontact tinytext NOT NULL,

206

description mediumtext NOT NULL

207

);

208

209

INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','','');

210

INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','','');

211

INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','','');

212

INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','','');

213

INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - eeeeeeeee','AAAAA','id0001','','');

214

INSERT INTO t2 VALUES ('kkkkkkkkkkk lllllllllll','AAAAA','id0001','','');

215

INSERT INTO t2 VALUES ('Test Procedures','AAAAA','id0001','','');

216

INSERT INTO t2 VALUES ('Documentation','AAAAA','id0003','','');

217

INSERT INTO t2 VALUES ('DDDDDDDDD','CCC/CCCCCC','id0002','','');

218

INSERT INTO t2 VALUES ('Eeeeeeee Lite','CCC/CCCCCC','id0002','','');

219

INSERT INTO t2 VALUES ('Eeeeeeee Full','CCC/CCCCCC','id0002','','');

220

INSERT INTO t2 VALUES ('Administration','CCC/CCCCCC','id0002','','');

221

INSERT INTO t2 VALUES ('Distribution','CCC/CCCCCC','id0002','','');

222

INSERT INTO t2 VALUES ('Setup','CCC/CCCCCC','id0002','','');

223

INSERT INTO t2 VALUES ('Unspecified','CCC/CCCCCC','id0002','','');

224

INSERT INTO t2 VALUES ('Web Interface','AAAAAAAA-AAA','id0001','','');

225

INSERT INTO t2 VALUES ('Host communication','AAAAA','id0001','','');

226

select value,description,bug_id from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA";

227

select value,description,COUNT(bug_id) from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA" group by value;

228

select value,description,COUNT(bug_id) from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA" group by value having COUNT(bug_id) IN (0,2);

229

230

drop table t1,t2;

231

232

233

# Problem with functions and group functions when no matching rows

234

235

236

create table t1 (foo int);

237

insert into t1 values (1);

238

select 1+1, "a",count(*) from t1 where foo in (2);

239

insert into t1 values (1);

240

select 1+1,"a",count(*) from t1 where foo in (2);

241

drop table t1;

242

243

244

# Test GROUP BY DESC

245

246

CREATE TABLE t1 (

247

spID int(10) unsigned,

248

userID int(10) unsigned,

249

score smallint(5) unsigned,

250

key (spid),

251

key (score)

252

);

253

254

INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3);

255

explain select userid,count(*) from t1 group by userid desc;

256

explain select userid,count(*) from t1 group by userid desc order by null;

257

select userid,count(*) from t1 group by userid desc;

258

select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);

259

select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*));

260

explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;

261

explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;

262

explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;

263

select spid,count(*) from t1 where spid between 1 and 2 group by spid;

264

select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;

265

explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc;

266

explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;

267

select sql_big_result spid,sum(userid) from t1 group by spid desc;

268

explain select sql_big_result score,count(*) from t1 group by score desc;

269

explain select sql_big_result score,count(*) from t1 group by score desc order by null;

270

select sql_big_result score,count(*) from t1 group by score desc;

271

drop table t1;

272

273

# not purely group_by bug, but group_by is involved...

274

275

create table t1 (a date default null, b date default null);

276

insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01');

277

select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day;

278

drop table t1;

279

280

# Compare with hash keys

281

282

CREATE TABLE t1 (a char(1));

283

INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);

284

SELECT a FROM t1 GROUP BY a;

285

SELECT a,count(*) FROM t1 GROUP BY a;

286

SELECT a FROM t1 GROUP BY binary a;

287

SELECT a,count(*) FROM t1 GROUP BY binary a;

288

SELECT binary a FROM t1 GROUP BY 1;

289

SELECT binary a,count(*) FROM t1 GROUP BY 1;

290

# Do the same tests with MyISAM temporary tables

291

SET SQL_BIG_TABLES=1;

292

SELECT a FROM t1 GROUP BY a;

293

SELECT a,count(*) FROM t1 GROUP BY a;

294

SELECT a FROM t1 GROUP BY binary a;

295

SELECT a,count(*) FROM t1 GROUP BY binary a;

296

SELECT binary a FROM t1 GROUP BY 1;

297

SELECT binary a,count(*) FROM t1 GROUP BY 1;

298

SET SQL_BIG_TABLES=0;

299

drop table t1;

300

301

302

# Test of key >= 256 bytes

303

304

305

CREATE TABLE t1 (

306

`a` char(193) default NULL,

307

`b` char(63) default NULL

308

);

309

INSERT INTO t1 VALUES ('abc','def'),('hij','klm');

310

SELECT CONCAT(a, b) FROM t1 GROUP BY 1;

311

SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;

312

SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1;

313

SELECT 1 FROM t1 GROUP BY CONCAT(a, b);

314

INSERT INTO t1 values ('hij','klm');

315

SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;

316

DROP TABLE t1;

317

318

319

# Test problem with ORDER BY on a SUM() column

320

321

322

create table t1 (One int unsigned, Two int unsigned, Three int unsigned, Four int unsigned);

323

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);

324

select One, Two, sum(Four) from t1 group by One,Two;

325

drop table t1;

326

327

create table t1 (id integer primary key not null auto_increment, gender char(1));

328

insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M');

329

create table t2 (user_id integer not null, date date);

330

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');

331

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;

332

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;

333

drop table t1,t2;

334

335

336

# The GROUP BY returned rows in wrong order in 3.23.51

337

338

339

CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID

340

));

341

insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);

342

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;

343

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;

344

drop table t1;

345

346

347

# Problem with MAX and LEFT JOIN

348

349

350

CREATE TABLE t1 (

351

pid int(11) unsigned NOT NULL default '0',

352

c1id int(11) unsigned default NULL,

353

c2id int(11) unsigned default NULL,

354

value int(11) unsigned NOT NULL default '0',

355

UNIQUE KEY pid2 (pid,c1id,c2id),

356

UNIQUE KEY pid (pid,value)

357

) ENGINE=MyISAM;

358

359

INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5);

360

361

CREATE TABLE t2 (

362

id int(11) unsigned NOT NULL default '0',

363

active enum('Yes','No') NOT NULL default 'Yes',

364

PRIMARY KEY (id)

365

) ENGINE=MyISAM;

366

367

INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No');

368

369

CREATE TABLE t3 (

370

id int(11) unsigned NOT NULL default '0',

371

active enum('Yes','No') NOT NULL default 'Yes',

372

PRIMARY KEY (id)

373

);

374

INSERT INTO t3 VALUES (3, 'Yes');

375

376

select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id =

377

c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND

378

c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS NOT NULL);

379

select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON

380

m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id =

381

c2.id AND c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS

382

NOT NULL);

383

drop table t1,t2,t3;

384

385

386

# Test bug in GROUP BY on BLOB that is NULL or empty

387

388

389

create table t1 (a blob null);

390

insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b");

391

select a,count(*) from t1 group by a;

392

set option sql_big_tables=1;

393

select a,count(*) from t1 group by a;

394

drop table t1;

395

396

397

# Test of GROUP BY ... ORDER BY NULL optimization

398

399

400

create table t1 (a int not null, b int not null);

401

insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1);

402

create table t2 (a int not null, b int not null, key(a));

403

insert into t2 values (1,3),(3,1),(2,2),(1,1);

404

select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;

405

select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;

406

explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;

407

explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;

408

drop table t1,t2;

409

410

411

# group function arguments in some functions

412

413

414

create table t1 (a int, b int);

415

insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);

416

select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;

417

select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;

418

select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;

419

select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;

420

select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;

421

select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;

422

drop table t1;

423

424

425

# Problem with group by and alias

426

427

428

create table t1 (id int not null, qty int not null);

429

insert into t1 values (1,2),(1,3),(2,4),(2,5);

430

select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;

431

select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;

432

select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;

433

select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;

434

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;

435

select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;

436

drop table t1;

437

438

# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY

439

# NULL is used.

440

441

CREATE TABLE t1 (

442

userid int(10) unsigned,

443

score smallint(5) unsigned,

444

key (score)

445

);

446

INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);

447

# Here we select unordered GROUP BY into a temporary talbe,

448

# and then sort it with filesort (GROUP BY in MySQL

449

# implies sorted order of results)

450

SELECT userid,count(*) FROM t1 GROUP BY userid DESC;

451

EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;

452

DROP TABLE t1;

453

CREATE TABLE t1 (

454

i int(11) default NULL,

455

j int(11) default NULL

456

);

457

INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);

458

SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;

459

explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;

460

DROP TABLE t1;

461

462

#Test for BUG#6976: Aggregate functions have incorrect NULL-ness

463

create table t1 (a int);

464

insert into t1 values(null);

465

select min(a) is null from t1;

466

select min(a) is null or null from t1;

467

select 1 and min(a) is null from t1;

468

drop table t1;

469

470

# Test for BUG#5400: GROUP_CONCAT returns everything twice.

471

create table t1 ( col1 int, col2 int );

472

insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);

473

select group_concat( distinct col1 ) as alias from t1

474

group by col2 having alias like '%';

475

476

drop table t1;

477

478

479

# Test BUG#8216 when referring in HAVING to n alias which is rand() function

480

481

482

create table t1 (a integer, b integer, c integer);

483

insert into t1 (a,b) values (1,2),(1,3),(2,5);

484

select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;

485

# rand(100)*10 will be < 2 only for the first row (of 6)

486

select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2;

487

select a,sum(b) from t1 where a=1 group by c;

488

select a*sum(b) from t1 where a=1 group by c;

489

select sum(a)*sum(b) from t1 where a=1 group by c;

490

select a,sum(b) from t1 where a=1 group by c having a=1;

491

select a as d,sum(b) from t1 where a=1 group by c having d=1;

492

select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;

493

drop table t1;

494

495

# Test for BUG#9213 GROUP BY query on utf-8 key returns wrong results

496

create table t1(a int);

497

insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);

498

create table t2 (

499

a int,

500

b varchar(200) NOT NULL,

501

c varchar(50) NOT NULL,

502

d varchar(100) NOT NULL,

503

primary key (a,b(132),c,d),

504

key a (a,b)

505

) charset=utf8;

506

507

insert into t2 select

508

x3.a, -- 3

509

concat('val-', x3.a + 3*x4.a), -- 12

510

concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120

511

concat('val-', @a + 120*D.a)

512

from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;

513

514

delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30;

515

516

explain select c from t2 where a = 2 and b = 'val-2' group by c;

517

select c from t2 where a = 2 and b = 'val-2' group by c;

518

drop table t1,t2;

519

520

# Test for BUG#9298 "Wrong handling of int4 unsigned columns in GROUP functions"

521

# (the actual problem was with protocol code, not GROUP BY)

522

create table t1 (b int4 unsigned not null);

523

insert into t1 values(3000000000);

524

select * from t1;

525

select min(b) from t1;

526

drop table t1;

527

528

529

# Test for bug #11088: GROUP BY a BLOB column with COUNT(DISTINCT column1)

530

531

532

CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);

533

534

INSERT INTO t1 VALUES

535

(1, 7, 'cache-dtc-af05.proxy.aol.com'),

536

(2, 3, 'what.ever.com'),

537

(3, 7, 'cache-dtc-af05.proxy.aol.com'),

538

(4, 7, 'cache-dtc-af05.proxy.aol.com');

539

540

SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1

541

WHERE hostname LIKE '%aol%'

542

GROUP BY hostname;

543

544

DROP TABLE t1;

545

546

547

# Test for bug #8614: GROUP BY 'const' with DISTINCT

548

549

550

CREATE TABLE t1 (a int, b int);

551

INSERT INTO t1 VALUES (1,2), (1,3);

552

SELECT a, b FROM t1 GROUP BY 'const';

553

SELECT DISTINCT a, b FROM t1 GROUP BY 'const';

554

555

DROP TABLE t1;

556

557

558

# Test for bug #11385: GROUP BY for datetime converted to decimals

559

560

561

CREATE TABLE t1 (id INT, dt DATETIME);

562

INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );

563

INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );

564

INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );

565

INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );

566

SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;

567

568

DROP TABLE t1;

569

570

571

# Test for bug #11295: GROUP BY a BLOB column with COUNT(DISTINCT column1)

572

# when the BLOB column takes NULL values

573

574

575

CREATE TABLE t1 (id varchar(20) NOT NULL);

576

INSERT INTO t1 VALUES ('trans1'), ('trans2');

577

CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);

578

INSERT INTO t2 VALUES ('trans1', 'a problem');

579

SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment

580

FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;

581

582

DROP TABLE t1, t2;

583

584

585

# Bug #12266 GROUP BY expression on DATE column produces result with

586

# reduced length

587

588

create table t1 (f1 date);

589

insert into t1 values('2005-06-06');

590

insert into t1 values('2005-06-06');

591

select date(left(f1+0,8)) from t1 group by 1;

592

drop table t1;

593

594

595

# Test for bug #11414: crash on Windows for a simple GROUP BY query

596

597

598

CREATE TABLE t1 (n int);

599

INSERT INTO t1 VALUES (1);

600

SELECT n+1 AS n FROM t1 GROUP BY n;

601

DROP TABLE t1;

602

603

604

# BUG#12695: Item_func_isnull::update_used_tables

605

# did not update const_item_cache

606

607

create table t1(f1 varchar(5) key);

608

insert into t1 values (1),(2);

609

select sql_buffer_result max(f1) is null from t1;

610

select sql_buffer_result max(f1)+1 from t1;

611

drop table t1;

612

613

614

# BUG#14019-4.1-opt

615

616

CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2);

617

618

SELECT a FROM t1 GROUP BY 'a';

619

SELECT a FROM t1 GROUP BY "a";

620

SELECT a FROM t1 GROUP BY `a`;

621

622

SELECT a FROM t1 GROUP BY "a";

623

SELECT a FROM t1 GROUP BY 'a';

624

SELECT a FROM t1 GROUP BY `a`;

625

626

SELECT a FROM t1 HAVING 'a' > 1;

627

SELECT a FROM t1 HAVING "a" > 1;

628

SELECT a FROM t1 HAVING `a` > 1;

629

630

SELECT a FROM t1 ORDER BY 'a' DESC;

631

SELECT a FROM t1 ORDER BY "a" DESC;

632

SELECT a FROM t1 ORDER BY `a` DESC;

633

DROP TABLE t1;

634

635

636

# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself

637

# returns empty

638

639

CREATE TABLE t1 (

640

f1 int(10) unsigned NOT NULL auto_increment primary key,

641

f2 varchar(100) NOT NULL default ''

642

);

643

CREATE TABLE t2 (

644

f1 varchar(10) NOT NULL default '',

645

f2 char(3) NOT NULL default '',

646

PRIMARY KEY (`f1`),

647

KEY `k1` (`f2`,`f1`)

648

);

649

650

INSERT INTO t1 values(NULL, '');

651

INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');

652

SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;

653

SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;

654

DROP TABLE t1, t2;

655

656

657

# End of 4.1 tests

658

659

660

# Bug#11211: Ambiguous column reference in GROUP BY.

661

662

663

create table t1 (c1 char(3), c2 char(3));

664

create table t2 (c3 char(3), c4 char(3));

665

insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');

666

insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');

667

668

# query with ambiguous column reference 'c2'

669

select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4

670

group by c2;

671

show warnings;

672

673

# this query has no ambiguity

674

select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4

675

group by t1.c1;

676

677

show warnings;

678

drop table t1, t2;

679

680

681

# Bug#22781: SQL_BIG_RESULT fails to influence sort plan

682

683

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));

684

685

INSERT INTO t1 VALUES (1, 1);

686

INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;

687

INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;

688

INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;

689

INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;

690

INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;

691

INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;

692

INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;

693

694

SELECT MIN(b), MAX(b) from t1;

695

696

EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;

697

EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;

698

SELECT b, sum(1) FROM t1 GROUP BY b;

699

SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;

700

DROP TABLE t1;

701

702

703

# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode

704

705

CREATE TABLE t1 (a INT PRIMARY KEY, b INT);

706

INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);

707

708

SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;

709

SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;

710

SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1

711

GROUP BY b;

712

SELECT a + 1 FROM t1 GROUP BY a;

713

SELECT a + b FROM t1 GROUP BY b;

714

SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)

715

FROM t1 AS t1_outer;

716

SELECT 1 FROM t1 as t1_outer GROUP BY a

717

HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);

718

SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)

719

FROM t1 AS t1_outer GROUP BY t1_outer.b;

720

--error ER_BAD_FIELD_ERROR

721

SELECT 1 FROM t1 as t1_outer GROUP BY a

722

HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);

723

SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)

724

FROM t1 AS t1_outer GROUP BY t1_outer.b;

725

SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)

726

FROM t1 AS t1_outer;

727

SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)

728

FROM t1 AS t1_outer GROUP BY t1_outer.b;

729

730

SELECT 1 FROM t1 as t1_outer

731

WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);

732

733

SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;

734

735

SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;

736

SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);

737

738

--error ER_BAD_FIELD_ERROR

739

SELECT 1 FROM t1 GROUP BY b HAVING a = 2;

740

--error ER_INVALID_GROUP_FUNC_USE

741

SELECT 1 FROM t1 GROUP BY SUM(b);

742

SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN

743

(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a

744

HAVING SUM(t1_inner.b)+t1_outer.b > 5);

745

DROP TABLE t1;

746

747

# Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.

748

749

create table t1(f1 int, f2 int);

750

select * from t1 group by f1;

751

select * from t1 group by f2;

752

select * from t1 group by f1, f2;

753

select t1.f1,t.* from t1, t1 t group by 1;

754

drop table t1;

755

756

757

# Bug #32202: ORDER BY not working with GROUP BY

758

759

760

CREATE TABLE t1(

761

id INT AUTO_INCREMENT PRIMARY KEY,

762

c1 INT NOT NULL,

763

c2 INT NOT NULL,

764

UNIQUE KEY (c2,c1));

765

766

INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);

767

768

# Show that the test cases from the bug report pass

769

SELECT * FROM t1 ORDER BY c1;

770

SELECT * FROM t1 GROUP BY id ORDER BY c1;

771

772

# Show that DESC is handled correctly

773

SELECT * FROM t1 GROUP BY id ORDER BY id DESC;

774

775

# Show that results are correctly ordered when ORDER BY fields

776

# are a subset of GROUP BY ones

777

SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;

778

SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;

779

SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;

780

781

# Show that results are correctly ordered when GROUP BY fields

782

# are a subset of ORDER BY ones

783

SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;

784

SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;

785

SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;

786

787

DROP TABLE t1;

788

789

790

--echo #

791

--echo # Bug#27219: Aggregate functions in ORDER BY.

792

--echo #

793

CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);

794

INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);

795

CREATE TABLE t2 SELECT * FROM t1;

796

797

SELECT 1 FROM t1 ORDER BY COUNT(*);

798

SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;

799

SELECT 1 FROM t1 ORDER BY COUNT(*) + a;

800

SELECT 1 FROM t1 ORDER BY COUNT(*), 1;

801

SELECT 1 FROM t1 ORDER BY COUNT(*), a;

802

803

SELECT 1 FROM t1 ORDER BY SUM(a);

804

SELECT 1 FROM t1 ORDER BY SUM(a + 1);

805

SELECT 1 FROM t1 ORDER BY SUM(a) + 1;

806

SELECT 1 FROM t1 ORDER BY SUM(a), b;

807

808

SELECT a FROM t1 ORDER BY COUNT(b);

809

810

SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);

811

812

SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);

813

SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);

814

815

SELECT t1.a FROM t1

816

WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);

817

SELECT t1.a FROM t1 GROUP BY t1.a

818

HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);

819

820

SELECT t1.a FROM t1 GROUP BY t1.a

821

HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));

822

SELECT t1.a FROM t1 GROUP BY t1.a

823

HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));

824

SELECT t1.a FROM t1 GROUP BY t1.a

825

HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));

826

827

SELECT t1.a FROM t1

828

WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));

829

830

SELECT 1 FROM t1 GROUP BY t1.a

831

HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);

832

SELECT 1 FROM t1 GROUP BY t1.a

833

HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);

834

SELECT 1 FROM t1 GROUP BY t1.a

835

HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);

836

837

SELECT 1 FROM t1 GROUP BY t1.a

838

HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);

839

SELECT 1 FROM t1 GROUP BY t1.a

840

HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);

841

SELECT 1 FROM t1 GROUP BY t1.a

842

HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);

843

844

# Both SUMs are aggregated in the subquery, no mixture:

845

SELECT t1.a FROM t1

846

WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a

847

ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);

848

849

# SUM(t1.b) is aggregated in the subquery, no mixture:

850

SELECT t1.a, SUM(t1.b) FROM t1

851

WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a

852

ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)

853

GROUP BY t1.a;

854

855

# 2nd SUM(t1.b) is aggregated in the subquery, no mixture:

856

SELECT t1.a, SUM(t1.b) FROM t1

857

WHERE t1.a = (SELECT SUM(t2.b) FROM t2

858

ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)

859

GROUP BY t1.a;

860

861

# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture:

862

SELECT t1.a, SUM(t1.b) FROM t1

863

WHERE t1.a = (SELECT SUM(t2.b) FROM t2

864

ORDER BY SUM(t2.b + t1.a) LIMIT 1)

865

GROUP BY t1.a;

866

867

SELECT t1.a FROM t1 GROUP BY t1.a

868

HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);

869

870

select avg (

871

(select

872

(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt

873

from t1 as outr order by outr.a limit 1))

874

from t1 as most_outer;

875

876

select avg (

877

(select (

878

(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt

879

from t1 as outr order by count(outr.a) limit 1)) as tt

880

from t1 as most_outer;

881

882

select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;

883

884

DROP TABLE t1, t2;

885

886

--echo End of 5.0 tests

887

# Bug #21174: Index degrades sort performance and

888

# optimizer does not honor IGNORE INDEX.

889

# a.k.a WL3527.

890

891

CREATE TABLE t1 (a INT, b INT,

892

PRIMARY KEY (a),

893

KEY i2(a,b));

894

INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

895

INSERT INTO t1 SELECT a + 8,b FROM t1;

896

INSERT INTO t1 SELECT a + 16,b FROM t1;

897

INSERT INTO t1 SELECT a + 32,b FROM t1;

898

INSERT INTO t1 SELECT a + 64,b FROM t1;

899

INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;

900

ANALYZE TABLE t1;

901

EXPLAIN SELECT a FROM t1 WHERE a < 2;

902

EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;

903

EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;

904

EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);

905

EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);

906

EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;

907

EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;

908

SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;

909

EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)

910

IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;

911

EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);

912

EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);

913

EXPLAIN SELECT a FROM t1 USE INDEX ();

914

EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);

915

--error ER_WRONG_USAGE

916

EXPLAIN SELECT a FROM t1

917

FORCE INDEX (PRIMARY)

918

IGNORE INDEX FOR GROUP BY (i2)

919

IGNORE INDEX FOR ORDER BY (i2)

920

USE INDEX (i2);

921

EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();

922

--error ER_PARSE_ERROR

923

EXPLAIN SELECT a FROM t1 FORCE INDEX ();

924

--error ER_PARSE_ERROR

925

EXPLAIN SELECT a FROM t1 IGNORE INDEX ();

926

# disable the columns irrelevant to this test here. On some systems

927

# without support for large files the rowid is shorter and its size affects

928

# the cost calculations. This causes the optimizer to choose loose index

929

# scan over normal index access.

930

--replace_column 4 # 7 # 9 # 10 #

931

EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)

932

USE INDEX FOR GROUP BY (i2) GROUP BY a;

933

EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)

934

FORCE INDEX FOR GROUP BY (i2) GROUP BY a;

935

EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);

936

EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();

937

938

EXPLAIN SELECT a FROM t1

939

USE INDEX FOR GROUP BY (i2)

940

USE INDEX FOR ORDER BY (i2)

941

USE INDEX FOR JOIN (i2);

942

943

EXPLAIN SELECT a FROM t1

944

USE INDEX FOR JOIN (i2)

945

USE INDEX FOR JOIN (i2)

946

USE INDEX FOR JOIN (i2,i2);

947

948

EXPLAIN SELECT 1 FROM t1 WHERE a IN

949

(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));

950

951

CREATE TABLE t2 (a INT, b INT, KEY(a));

952

INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);

953

EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;

954

EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;

955

956

EXPLAIN SELECT 1 FROM t2 WHERE a IN

957

(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));

958

959

SHOW VARIABLES LIKE 'old';

960

--error ER_INCORRECT_GLOBAL_LOCAL_VAR

961

SET @@old = off;

962

963

DROP TABLE t1, t2;

964

965

966

# Bug#30596: GROUP BY optimization gives wrong result order

967

968

CREATE TABLE t1(

969

a INT,

970

b INT NOT NULL,

971

c INT NOT NULL,

972

d INT,

973

UNIQUE KEY (c,b)

974

);

975

976

INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);

977

978

CREATE TABLE t2(

979

a INT,

980

b INT,

981

UNIQUE KEY(a,b)

982

);

983

984

INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);

985

986

EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;

987

SELECT c,b,d FROM t1 GROUP BY c,b,d;

988

EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;

989

SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;

990

EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;

991

SELECT c,b,d FROM t1 ORDER BY c,b,d;

992

993

EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;

994

SELECT c,b,d FROM t1 GROUP BY c,b;

995

EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;

996

SELECT c,b FROM t1 GROUP BY c,b;

997

998

EXPLAIN SELECT a,b from t2 ORDER BY a,b;

999

SELECT a,b from t2 ORDER BY a,b;

1000

EXPLAIN SELECT a,b from t2 GROUP BY a,b;

1001

SELECT a,b from t2 GROUP BY a,b;

1002

EXPLAIN SELECT a from t2 GROUP BY a;

1003

SELECT a from t2 GROUP BY a;

1004

EXPLAIN SELECT b from t2 GROUP BY b;

1005

SELECT b from t2 GROUP BY b;

1006

1007

DROP TABLE t1;

1008

1009

1010

# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING

1011

1012

CREATE TABLE t1 ( a INT, b INT );

1013

1014

SELECT b c, (SELECT a FROM t1 WHERE b = c)

1015

FROM t1;

1016

1017

SELECT b c, (SELECT a FROM t1 WHERE b = c)

1018

FROM t1

1019

HAVING b = 10;

1020

1021

--error ER_ILLEGAL_REFERENCE

1022

SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)

1023

FROM t1

1024

HAVING b = 10;

1025

1026

SELECT b c, (SELECT a FROM t1 WHERE b = c)

1027

FROM t1;

1028

1029

SELECT b c, (SELECT a FROM t1 WHERE b = c)

1030

FROM t1

1031

HAVING b = 10;

1032

1033

--error ER_ILLEGAL_REFERENCE

1034

SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)

1035

FROM t1

1036

HAVING b = 10;

1037

1038

INSERT INTO t1 VALUES (1, 1);

1039

SELECT b c, (SELECT a FROM t1 WHERE b = c)

1040

FROM t1;

1041

1042

INSERT INTO t1 VALUES (2, 1);

1043

--error ER_SUBQUERY_NO_1_ROW

1044

SELECT b c, (SELECT a FROM t1 WHERE b = c)

1045

FROM t1;

1046

1047

DROP TABLE t1;

1048

1049

1050

Older »