~drizzle-trunk/drizzle/development

explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);

select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;

select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from

(select * from t2 where a>1) as tt;

--replace_column 9 #

explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from

(select * from t2 where a>1) as tt;

select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);

select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);

select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1);

select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;

--replace_column 9 #

explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;

select * from t3 where exists (select * from t2 where t2.b=t3.a);

select * from t3 where not exists (select * from t2 where t2.b=t3.a);

select * from t3 where a = some (select b from t2);

select * from t3 where a <> any (select b from t2);

--echo # Rewrite: select * from t3 where not exists (select b from t2 where a <> b);

# Rewrite: select * from t3 where not exists (select b from t2 where a <> b);

select * from t3 where a = all (select b from t2);

select * from t3 where a <> all (select b from t2);

100

select * from t3 where a < any (select b from t2);

101

select * from t3 where a < all (select b from t2);

102

select * from t3 where a >= any (select b from t2);

103

--replace_column 9 #

104

100

explain extended select * from t3 where a >= any (select b from t2);

105

101

select * from t3 where a >= all (select b from t2);

106

102

delete from t2 where a=100;

109

105

-- error 1241

110

106

select * from t3 where a in (select * from t2);

111

107

insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);

112

--echo # empty set

108

# empty set

113

109

select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);

114

110

insert into t2 values (2,10);

115

111

select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);

116

112

delete from t2 where a=2 and b=10;

117

113

select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);

118

create temporary table t5 (a int) ENGINE=MyISAM;

114

create table t5 (a int);

119

115

select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;

120

116

insert into t5 values (5);

121

117

select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;

122

118

insert into t5 values (2);

123

119

select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;

124

--replace_column 9 #

125

120

explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;

126

121

-- error 1242

127

122

select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;

128

create temporary table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;

129

create temporary table t7( uq int primary key, name char(25)) ENGINE=MyISAM;

123

create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));

124

create table t7( uq int primary key, name char(25));

130

125

insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");

131

126

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

132

127

select * from t6 where exists (select * from t7 where uq = clinic_uq);

133

128

explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);

134

129

135

--echo # not unique fields

130

# not unique fields

136

131

-- error 1052

137

132

select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);

138

133

139

--echo # different tipes & group functions

134

# different tipes & group functions

140

135

drop table t1,t2,t3;

141

136

142

137

CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');

143

138

INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');

144

139

CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');

145

140

INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');

146

CREATE TABLE t1 (a varchar(20),b date NULL);

141

CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');

147

142

INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');

148

143

SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);

149

144

SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);

150

145

SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);

151

146

152

147

CREATE TABLE `t8` (

153

`pseudo` varchar(35) NOT NULL default '',

154

`email` varchar(60) NOT NULL default '',

148

`pseudo` varchar(35) character set latin1 NOT NULL default '',

149

`email` varchar(60) character set latin1 NOT NULL default '',

155

150

PRIMARY KEY (`pseudo`),

156

151

UNIQUE KEY `email` (`email`)

157

);

152

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

158

153

159

154

INSERT INTO t8 (pseudo,email) VALUES ('joce','test');

160

155

INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');

161

156

INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');

162

--replace_column 9 #

163

157

EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');

164

158

-- error 1241

165

159

SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM

173

167

174

168

drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;

175

169

176

--echo #searchconthardwarefr3 forumconthardwarefr7

177

CREATE TEMPORARY TABLE `t1` (

178

`topic` bigint NOT NULL default '0',

179

`date` date NULL,

180

`pseudo` varchar(35) NOT NULL default '',

170

#searchconthardwarefr3 forumconthardwarefr7

171

CREATE TABLE `t1` (

172

`topic` mediumint(8) unsigned NOT NULL default '0',

173

`date` date NOT NULL default '0000-00-00',

174

`pseudo` varchar(35) character set latin1 NOT NULL default '',

181

175

PRIMARY KEY (`pseudo`,`date`,`topic`),

182

176

KEY `topic` (`topic`)

183

177

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

193

187

EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);

194

188

drop table t1;

195

189

196

--echo #forumconthardwarefr7 searchconthardwarefr7

190

#forumconthardwarefr7 searchconthardwarefr7

197

191

CREATE TABLE `t1` (

198

`numeropost` bigint NOT NULL auto_increment,

199

`maxnumrep` int NOT NULL default '0',

192

`numeropost` mediumint(8) unsigned NOT NULL auto_increment,

193

`maxnumrep` int(10) unsigned NOT NULL default '0',

200

194

PRIMARY KEY (`numeropost`),

201

195

UNIQUE KEY `maxnumrep` (`maxnumrep`)

202

);

196

) ENGINE=MyISAM ROW_FORMAT=FIXED;

203

197

204

198

INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);

205

199

206

200

CREATE TABLE `t2` (

207

201

`mot` varchar(30) NOT NULL default '',

208

`topic` bigint NOT NULL default '0',

209

`date` date NULL,

202

`topic` mediumint(8) unsigned NOT NULL default '0',

203

`date` date NOT NULL default '0000-00-00',

210

204

`pseudo` varchar(35) NOT NULL default '',

211

205

PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)

212

);

206

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

213

207

214

208

INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');

215

209

select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);

236

230

SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;

237

231

drop table t1,t2;

238

232

239

--echo #forumconthardwarefr7

233

#forumconthardwarefr7

240

234

CREATE TABLE `t1` (

241

`numeropost` bigint NOT NULL auto_increment,

242

`maxnumrep` int NOT NULL default '0',

235

`numeropost` mediumint(8) unsigned NOT NULL auto_increment,

236

`maxnumrep` int(10) unsigned NOT NULL default '0',

243

237

PRIMARY KEY (`numeropost`),

244

238

UNIQUE KEY `maxnumrep` (`maxnumrep`)

245

);

239

) ENGINE=MyISAM ROW_FORMAT=FIXED;

246

240

247

241

INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);

248

242

-- error 1242

256

250

(select * from t1) union (select * from t1) order by (select a from t1 limit 1);

257

251

drop table t1;

258

252

259

--echo #iftest

260

CREATE TEMPORARY TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;

253

#iftest

254

CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');

261

255

INSERT INTO t1 VALUES ();

262

256

-- error 1242

263

257

SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');

264

258

drop table t1;

265

259

266

--echo # threadhardwarefr7

260

# threadhardwarefr7

267

261

CREATE TABLE `t1` (

268

`numeropost` bigint NOT NULL default '0',

269

`numreponse` int NOT NULL auto_increment,

262

`numeropost` mediumint(8) unsigned NOT NULL default '0',

263

`numreponse` int(10) unsigned NOT NULL auto_increment,

270

264

`pseudo` varchar(35) NOT NULL default '',

271

265

PRIMARY KEY (`numeropost`,`numreponse`),

272

266

UNIQUE KEY `numreponse` (`numreponse`),

273

267

KEY `pseudo` (`pseudo`,`numeropost`)

274

);

268

) ENGINE=MyISAM;

275

269

-- error 1247

276

270

SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;

277

271

-- error 1054

280

274

INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');

281

275

-- error 1242

282

276

EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');

283

--replace_column 9 #

284

277

EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';

285

--replace_column 9 #

286

278

EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');

287

279

drop table t1;

288

280

289

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

281

CREATE TABLE t1 (a int(1));

290

282

INSERT INTO t1 VALUES (1);

291

283

SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;

292

284

drop table t1;

293

285

294

--echo #update with subselects

286

#update with subselects

295

287

create table t1 (a int NOT NULL, b int, primary key (a));

296

288

create table t2 (a int NOT NULL, b int, primary key (a));

297

289

insert into t1 values (0, 10),(1, 11),(2, 12);

305

297

select * from t1;

306

298

drop table t1, t2;

307

299

308

--echo #delete with subselects

300

#delete with subselects

309

301

create table t1 (a int NOT NULL, b int, primary key (a));

310

302

create table t2 (a int NOT NULL, b int, primary key (a));

311

303

insert into t1 values (0, 10),(1, 11),(2, 12);

320

312

select * from t1;

321

313

drop table t1, t2;

322

314

323

--echo #insert with subselects

315

#multi-delete with subselects

316

317

create table t11 (a int NOT NULL, b int, primary key (a));

318

create table t12 (a int NOT NULL, b int, primary key (a));

319

create table t2 (a int NOT NULL, b int, primary key (a));

320

insert into t11 values (0, 10),(1, 11),(2, 12);

321

insert into t12 values (33, 10),(22, 11),(2, 12);

322

insert into t2 values (1, 21),(2, 12),(3, 23);

323

select * from t11;

324

select * from t12;

325

-- error 1093

326

delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);

327

-- error 1242

328

delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);

329

delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);

330

select * from t11;

331

select * from t12;

332

drop table t11, t12, t2;

333

334

#insert with subselects

324

335

CREATE TABLE t1 (x int);

325

336

create table t2 (a int);

326

337

create table t3 (b int);

333

344

INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));

334

345

select * from t1;

335

346

insert into t2 values (1);

336

INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));

347

INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));

348

-- sleep 1

337

349

select * from t1;

338

350

INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;

339

351

select * from t1;

340

--echo # After this, only data based on old t1 records should have been added.

352

# After this, only data based on old t1 records should have been added.

341

353

INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;

342

354

select * from t1;

343

355

-- error 1054

344

INSERT INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));

345

INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));

356

INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));

357

INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));

358

-- sleep 1

346

359

select * from t1;

347

--echo #

348

--echo #TODO: should be uncommented after bug 380 fix pushed

349

--echo #INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;

350

--echo #select * from t1;

360

361

#TODO: should be uncommented after bug 380 fix pushed

362

#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;

363

#select * from t1;

351

364

drop table t1, t2, t3;

352

365

353

--echo #replace with subselects

366

#replace with subselects

354

367

CREATE TABLE t1 (x int not null, y int, primary key (x));

355

368

create table t2 (a int);

356

create temporary table t3 (a int) ENGINE=MyISAM;

369

create table t3 (a int);

357

370

insert into t2 values (1);

358

371

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

359

372

select * from t1;

365

378

select * from t1;

366

379

replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));

367

380

select * from t1;

368

replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));

369

--sorted_result

370

select * from t1;

371

replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));

372

select * from t1;

373

replace into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));

374

--sorted_result

381

replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));

382

-- sleep 1

383

select * from t1;

384

replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));

385

-- sleep 1

386

select * from t1;

387

replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));

375

388

select * from t1;

376

389

drop table t1, t2, t3;

377

390

378

391

-- error 1096

379

392

SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);

380

393

381

CREATE TABLE t2 (id int default NULL, KEY id (id));

394

CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;

382

395

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

383

396

SELECT * FROM t2 WHERE id IN (SELECT 1);

384

397

EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);

393

406

-- error 1093

394

407

INSERT INTO t2 VALUES ((SELECT id FROM t2));

395

408

SELECT * FROM t2;

396

CREATE TEMPORARY TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;

409

CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;

397

410

INSERT INTO t1 values (1),(1);

398

411

-- error 1242

399

412

UPDATE t2 SET id=(SELECT * FROM t1);

400

413

drop table t2, t1;

401

414

402

--echo #NULL test

403

create temporary table t1 (a int) ENGINE=MyISAM;

415

#NULL test

416

create table t1 (a int);

404

417

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

405

418

select 1 IN (SELECT * from t1);

406

419

select 10 IN (SELECT * from t1);

414

427

select 1 > ANY (SELECT * from t1);

415

428

select 10 > ANY (SELECT * from t1);

416

429

drop table t1;

417

create temporary table t1 (a varchar(20)) ENGINE=MyISAM;

430

create table t1 (a varchar(20));

418

431

insert into t1 values ('A'),('BC'),('DEF');

419

432

select 'A' IN (SELECT * from t1);

420

433

select 'XYZS' IN (SELECT * from t1);

428

441

select 'A' > ANY (SELECT * from t1);

429

442

select 'XYZS' > ANY (SELECT * from t1);

430

443

drop table t1;

431

create temporary table t1 (a float) ENGINE=MyISAM;

444

create table t1 (a float);

432

445

insert into t1 values (1.5),(2.5),(3.5);

433

446

select 1.5 IN (SELECT * from t1);

434

447

select 10.5 IN (SELECT * from t1);

442

455

select 1.5 > ANY (SELECT * from t1);

443

456

select 10.5 > ANY (SELECT * from t1);

444

457

explain extended select (select a+1) from t1;

445

--sorted_result

446

458

select (select a+1) from t1;

447

459

drop table t1;

448

460

449

--echo #

450

--echo # Null with keys

451

--echo #

461

462

# Null with keys

463

452

464

453

CREATE TEMPORARY TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a)) ENGINE=MyISAM;

454

CREATE TEMPORARY TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;

465

CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a));

466

CREATE TABLE t2 (a int(11) default '0', INDEX (a));

455

467

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

456

468

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

457

469

SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;

458

470

explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;

459

CREATE TEMPORARY TABLE t3 (a int default '0') ENGINE=MyISAM;

471

CREATE TABLE t3 (a int(11) default '0');

460

472

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

461

473

SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;

462

474

explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;

463

475

drop table t1,t2,t3;

464

476

465

--echo #LIMIT is not supported now

466

--echo #create table t1 (a float) ENGINE=MyISAM;

467

--echo #-- error 1235

468

--echo #select 10.5 IN (SELECT * from t1 LIMIT 1);

469

--echo #-- error 1235

470

--echo #select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);

471

--echo #drop table t1;

472

--echo #

473

--echo #create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;

474

--echo #create table t2 (a int) ENGINE=MyISAM;

475

--echo #insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');

476

--echo #insert into t2 values (1),(2),(NULL);

477

--echo #select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2;

478

--echo #select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;

479

--echo #select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;

480

--echo #drop table t1,t2;

481

--echo #

482

--echo #create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;

483

--echo #insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');

484

--echo #select ROW(1, 1, 'a') IN (select a,b,c from t1);

485

--echo #select ROW(1, 2, 'a') IN (select a,b,c from t1);

486

--echo #select ROW(1, 1, 'a') IN (select b,a,c from t1);

487

--echo #select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);

488

--echo #select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);

489

--echo #select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);

490

--echo #select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');

491

--echo #select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');

492

--echo #select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');

493

--echo #-- error 1235

494

--echo #select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);

495

--echo #drop table t1;

496

497

--echo #test of uncacheable subqueries

498

CREATE TABLE t1 (a int);

477

#LIMIT is not supported now

478

create table t1 (a float);

479

-- error 1235

480

select 10.5 IN (SELECT * from t1 LIMIT 1);

481

-- error 1235

482

select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);

483

drop table t1;

484

485

create table t1 (a int, b int, c varchar(10));

486

create table t2 (a int);

487

insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');

488

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

489

select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2;

490

select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;

491

select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;

492

drop table t1,t2;

493

494

create table t1 (a int, b real, c varchar(10));

495

insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');

496

select ROW(1, 1, 'a') IN (select a,b,c from t1);

497

select ROW(1, 2, 'a') IN (select a,b,c from t1);

498

select ROW(1, 1, 'a') IN (select b,a,c from t1);

499

select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);

500

select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);

501

select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);

502

select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');

503

select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');

504

select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');

505

-- error 1235

506

select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);

507

drop table t1;

508

509

#test of uncacheable subqueries

510

CREATE TABLE t1 (a int(1));

499

511

EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;

512

EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;

500

513

EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;

501

514

drop table t1;

502

515

503

516

504

CREATE TEMPORARY TABLE `t1` (

505

`mot` varchar(30) NOT NULL default '',

506

`topic` bigint NOT NULL default '0',

507

`date` date NULL DEFAULT '2009-01-20',

508

`pseudo` varchar(35) NOT NULL default '',

509

PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),

510

KEY `pseudo` (`pseudo`,`date`,`topic`),

511

KEY `topic` (`topic`)

512

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

513

514

CREATE TEMPORARY TABLE `t2` (

515

`mot` varchar(30) NOT NULL default '',

516

`topic` bigint NOT NULL default '0',

517

`date` date NULL default '1997-08-29',

518

`pseudo` varchar(35) NOT NULL default '',

519

PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),

520

KEY `pseudo` (`pseudo`,`date`,`topic`),

521

KEY `topic` (`topic`)

522

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

523

524

CREATE TEMPORARY TABLE `t3` (

525

`numeropost` bigint NOT NULL auto_increment,

526

`maxnumrep` int NOT NULL default '0',

517

CREATE TABLE `t1` (

518

`mot` varchar(30) character set latin1 NOT NULL default '',

519

`topic` mediumint(8) unsigned NOT NULL default '0',

520

`date` date NOT NULL default '0000-00-00',

521

`pseudo` varchar(35) character set latin1 NOT NULL default '',

522

PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),

523

KEY `pseudo` (`pseudo`,`date`,`topic`),

524

KEY `topic` (`topic`)

525

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

526

527

CREATE TABLE `t2` (

528

`mot` varchar(30) character set latin1 NOT NULL default '',

529

`topic` mediumint(8) unsigned NOT NULL default '0',

530

`date` date NOT NULL default '0000-00-00',

531

`pseudo` varchar(35) character set latin1 NOT NULL default '',

532

PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),

533

KEY `pseudo` (`pseudo`,`date`,`topic`),

534

KEY `topic` (`topic`)

535

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

536

537

CREATE TABLE `t3` (

538

`numeropost` mediumint(8) unsigned NOT NULL auto_increment,

539

`maxnumrep` int(10) unsigned NOT NULL default '0',

527

540

PRIMARY KEY (`numeropost`),

528

541

UNIQUE KEY `maxnumrep` (`maxnumrep`)

529

) ENGINE=MyISAM;

542

) ENGINE=MyISAM CHARSET=latin1;

530

543

INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');

531

544

532

545

INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');

543

556

drop table t1, t2, t3;

544

557

545

558

SELECT * FROM (SELECT 1 as a,(SELECT a)) a;

546

CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;

547

SHOW CREATE TABLE t1;

548

drop table t1;

549

CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;

550

SHOW CREATE TABLE t1;

551

drop table t1;

552

CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;

553

SHOW CREATE TABLE t1;

554

drop table t1;

555

CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;

559

CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;

560

SHOW CREATE TABLE t1;

561

drop table t1;

562

CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;

563

SHOW CREATE TABLE t1;

564

drop table t1;

565

CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;

566

SHOW CREATE TABLE t1;

567

drop table t1;

568

CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;

556

569

select * from t1;

557

570

SHOW CREATE TABLE t1;

558

571

drop table t1;

559

572

560

573

create table t1 (a int);

561

574

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

562

--replace_column 9 #

563

575

explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)

564

576

from t1;

565

577

drop table t1;

566

578

567

--echo #

568

--echo # error in IN

569

--echo #

579

580

# error in IN

581

570

582

-- error 1146

571

583

select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent);

572

584

573

--echo #

574

--echo # complex subquery

575

--echo #

585

586

# complex subquery

587

576

588

577

589

CREATE TABLE t1 (

578

ID int NOT NULL auto_increment,

590

ID int(11) NOT NULL auto_increment,

579

591

name char(35) NOT NULL default '',

580

592

t2 char(3) NOT NULL default '',

581

593

District char(20) NOT NULL default '',

582

Population int NOT NULL default '0',

594

Population int(11) NOT NULL default '0',

583

595

PRIMARY KEY (ID)

584

);

596

) ENGINE=MyISAM;

585

597

586

598

INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);

587

599

INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);

593

605

Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',

594

606

Region char(26) NOT NULL default '',

595

607

SurfaceArea float(10,2) NOT NULL default '0.00',

596

IndepYear int default NULL,

597

Population int NOT NULL default '0',

608

IndepYear smallint(6) default NULL,

609

Population int(11) NOT NULL default '0',

598

610

LifeExpectancy float(3,1) default NULL,

599

611

GNP float(10,2) default NULL,

600

612

GNPOld float(10,2) default NULL,

601

613

LocalName char(45) NOT NULL default '',

602

614

GovernmentForm char(45) NOT NULL default '',

603

615

HeadOfState char(60) default NULL,

604

Capital int default NULL,

616

Capital int(11) default NULL,

605

617

Code2 char(2) NOT NULL default '',

606

618

PRIMARY KEY (Code)

607

);

619

) ENGINE=MyISAM;

608

620

609

621

INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU');

610

622

INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');

613

625

614

626

drop table t1, t2;

615

627

616

--echo #

617

--echo # constants in IN

618

--echo #

619

CREATE TEMPORARY TABLE `t1` (

620

`id` bigint NOT NULL auto_increment,

621

`pseudo` varchar(35) NOT NULL default '',

628

629

# constants in IN

630

631

CREATE TABLE `t1` (

632

`id` mediumint(8) unsigned NOT NULL auto_increment,

633

`pseudo` varchar(35) character set latin1 NOT NULL default '',

622

634

PRIMARY KEY (`id`),

623

635

UNIQUE KEY `pseudo` (`pseudo`)

624

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

636

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

625

637

INSERT INTO t1 (pseudo) VALUES ('test');

626

638

SELECT 0 IN (SELECT 1 FROM t1 a);

627

639

EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);

630

642

EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);

631

643

drop table t1;

632

644

633

CREATE TEMPORARY TABLE `t1` (

634

`i` int NOT NULL default '0',

645

CREATE TABLE `t1` (

646

`i` int(11) NOT NULL default '0',

635

647

PRIMARY KEY (`i`)

636

) ENGINE=MyISAM;

648

) ENGINE=MyISAM CHARSET=latin1;

637

649

638

650

INSERT INTO t1 VALUES (1);

639

651

UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));

643

655

select * from t1;

644

656

drop table t1;

645

657

646

--echo #

647

--echo # correct NULL in <CONSTANT> IN (SELECT ...)

648

--echo #

649

create temporary table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;

658

659

# Multi update test

660

661

CREATE TABLE t1 (

662

id int(11) default NULL

663

) ENGINE=MyISAM CHARSET=latin1;

664

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

665

CREATE TABLE t2 (

666

id int(11) default NULL,

667

name varchar(15) default NULL

668

) ENGINE=MyISAM CHARSET=latin1;

669

670

INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');

671

update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);

672

select * from t2;

673

drop table t1,t2;

674

675

676

# correct NULL in <CONSTANT> IN (SELECT ...)

677

678

create table t1 (a int, unique index indexa (a));

650

679

insert into t1 values (-1), (-4), (-2), (NULL);

651

680

select -10 IN (select a from t1 FORCE INDEX (indexa));

652

681

drop table t1;

653

682

654

--echo #

655

--echo # Test optimization for sub selects

656

--echo #

683

684

# Test optimization for sub selects

685

657

686

create table t1 (id int not null auto_increment primary key, salary int, key(salary));

658

687

insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);

659

--replace_column 9 #

660

688

explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);

661

689

drop table t1;

662

690

663

691

CREATE TABLE t1 (

664

ID int NOT NULL auto_increment,

665

SUB_ID int NOT NULL default '0',

666

REF_ID int default NULL,

667

REF_SUB int default '0',

692

ID int(10) unsigned NOT NULL auto_increment,

693

SUB_ID int(3) unsigned NOT NULL default '0',

694

REF_ID int(10) unsigned default NULL,

695

REF_SUB int(3) unsigned default '0',

668

696

PRIMARY KEY (ID,SUB_ID),

669

697

UNIQUE KEY t1_PK (ID,SUB_ID),

670

698

KEY t1_FK (REF_ID,REF_SUB),

671

699

KEY t1_REFID (REF_ID)

672

);

700

) ENGINE=MyISAM CHARSET=cp1251;

673

701

INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);

674

702

SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);

675

703

DROP TABLE t1;

676

704

677

--echo #

678

--echo # uninterruptable update

679

--echo #

680

create temporary table t1 (a int, b int) ENGINE=MyISAM;

681

create temporary table t2 (a int, b int) ENGINE=MyISAM;

705

706

# uninterruptable update

707

708

create table t1 (a int, b int);

709

create table t2 (a int, b int);

682

710

683

711

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

684

712

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

688

716

689

717

drop table t1, t2;

690

718

691

--echo #

692

--echo # reduced subselect in ORDER BY & GROUP BY clauses

693

--echo #

719

720

# reduced subselect in ORDER BY & GROUP BY clauses

721

694

722

695

CREATE TEMPORARY TABLE `t1` (

696

`id` bigint NOT NULL auto_increment,

723

CREATE TABLE `t1` (

724

`id` mediumint(8) unsigned NOT NULL auto_increment,

697

725

`pseudo` varchar(35) NOT NULL default '',

698

726

`email` varchar(60) NOT NULL default '',

699

727

PRIMARY KEY (`id`),

700

728

UNIQUE KEY `email` (`email`),

701

729

UNIQUE KEY `pseudo` (`pseudo`)

702

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

730

) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

703

731

INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');

704

732

SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);

705

733

drop table if exists t1;

706

734

707

735

(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);

708

736

709

--echo #

710

--echo # IN subselect optimization test

711

--echo #

712

create temporary table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;

713

create temporary table t2 (a int not null, primary key (a)) ENGINE=MyISAM;

714

create temporary table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;

737

738

# IN subselect optimization test

739

740

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

741

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

742

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

715

743

insert into t1 values (1,10), (2,20), (3,30), (4,40);

716

744

insert into t2 values (2), (3), (4), (5);

717

745

insert into t3 values (10,3), (20,4), (30,5);

722

750

select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);

723

751

explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);

724

752

drop table t1, t2, t3;

725

create temporary table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;

726

create temporary table t2 (a int, index a (a)) ENGINE=MyISAM;

727

create temporary table t3 (a int, b int, index a (a)) ENGINE=MyISAM;

753

create table t1 (a int, b int, index a (a,b));

754

create table t2 (a int, index a (a));

755

create table t3 (a int, b int, index a (a));

728

756

insert into t1 values (1,10), (2,20), (3,30), (4,40);

729

757

disable_query_log;

730

--echo # making table large enough

731

set autocommit=0;

732

begin;

758

# making table large enough

733

759

let $1 = 10000;

734

760

while ($1)

735

761

{

736

762

eval insert into t1 values (rand()*100000+200,rand()*100000);

737

763

dec $1;

738

764

}

739

commit;

740

set autocommit=1;

741

765

enable_query_log;

742

766

insert into t2 values (2), (3), (4), (5);

743

767

insert into t3 values (10,3), (20,4), (30,5);

753

777

explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

754

778

drop table t1, t2, t3;

755

779

756

--echo #

757

--echo # alloc_group_fields() working

758

--echo #

759

create temporary table t1 (a int, b int) ENGINE=MyISAM;

760

create temporary table t2 (a int, b int) ENGINE=MyISAM;

761

create temporary table t3 (a int, b int) ENGINE=MyISAM;

780

781

# alloc_group_fields() working

782

783

create table t1 (a int, b int);

784

create table t2 (a int, b int);

785

create table t3 (a int, b int);

762

786

insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);

763

787

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

764

788

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

765

789

select a,(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3;

766

790

drop table t1,t2,t3;

767

791

768

--echo #

769

--echo # aggregate functions in HAVING test

770

--echo #

771

create temporary table t1 (s1 int) ENGINE=MyISAM;

772

create temporary table t2 (s1 int) ENGINE=MyISAM;

792

793

# aggregate functions in HAVING test

794

795

create table t1 (s1 int);

796

create table t2 (s1 int);

773

797

insert into t1 values (1);

774

798

insert into t2 values (1);

775

799

select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);

776

800

drop table t1,t2;

777

801

778

--echo #

779

--echo # update subquery with wrong field (to force name resolving

780

--echo # in UPDATE name space)

781

--echo #

782

create temporary table t1 (s1 int) ENGINE=MyISAM;

783

create temporary table t2 (s1 int) ENGINE=MyISAM;

802

803

# update subquery with wrong field (to force name resolving

804

# in UPDATE name space)

805

806

create table t1 (s1 int);

807

create table t2 (s1 int);

784

808

insert into t1 values (1);

785

809

insert into t2 values (1);

786

810

-- error 1054

787

811

update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);

788

812

DROP TABLE t1, t2;

789

813

790

--echo #

791

--echo # collation test

792

--echo #

793

--echo #CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,

794

--echo # s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;

795

--echo #INSERT INTO t1 VALUES ('z','?');

796

--echo #-- error 1267

797

--echo #select * from t1 where s1 > (select max(s2) from t1);

798

--echo #-- error 1267

799

--echo #select * from t1 where s1 > any (select max(s2) from t1);

800

--echo #drop table t1;

814

815

# collation test

816

817

CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,

818

s2 CHAR(5) COLLATE latin1_swedish_ci);

819

INSERT INTO t1 VALUES ('z','?');

820

-- error 1267

821

select * from t1 where s1 > (select max(s2) from t1);

822

-- error 1267

823

select * from t1 where s1 > any (select max(s2) from t1);

824

drop table t1;

801

825

802

--echo #

803

--echo # aggregate functions reinitialization

804

--echo #

826

827

# aggregate functions reinitialization

828

805

829

create table t1(toid int,rd int);

806

830

create table t2(userid int,pmnew int,pmtotal int);

807

831

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

809

833

select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1);

810

834

drop table t1, t2;

811

835

812

--echo #

813

--echo # row union

814

--echo #

836

837

# row union

838

815

839

create table t1 (s1 char(5));

816

840

-- error 1241

817

841

select (select 'a','b' from t1 union select 'a','b' from t1) from t1;

818

842

insert into t1 values ('tttt');

819

843

select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);

820

--replace_column 9 #

821

844

explain extended (select * from t1);

822

845

(select * from t1);

823

846

drop table t1;

824

847

825

--echo #

826

--echo # IN optimisation test results

827

--echo #

828

create temporary table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;

829

create temporary table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;

848

849

# IN optimisation test results

850

851

create table t1 (s1 char(5), index s1(s1));

852

create table t2 (s1 char(5), index s1(s1));

830

853

insert into t1 values ('a1'),('a2'),('a3');

831

854

insert into t2 values ('a1'),('a2');

832

855

select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;

839

862

explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;

840

863

drop table t1,t2;

841

864

842

--echo #

843

--echo # correct ALL optimisation

844

--echo #

845

create temporary table t2 (a int, b int) ENGINE=MyISAM;

846

create temporary table t3 (a int) ENGINE=MyISAM;

865

866

# correct ALL optimisation

867

868

create table t2 (a int, b int);

869

create table t3 (a int);

847

870

insert into t3 values (6),(7),(3);

848

871

select * from t3 where a >= all (select b from t2);

849

872

explain extended select * from t3 where a >= all (select b from t2);

861

884

explain extended select * from t3 where NULL >= some (select b from t2);

862

885

select * from t3 where NULL >= some (select b from t2 group by 1);

863

886

explain extended select * from t3 where NULL >= some (select b from t2 group by 1);

864

--echo #

865

--echo # optimized static ALL/ANY with grouping

866

--echo #

887

888

# optimized static ALL/ANY with grouping

889

867

890

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

868

891

select * from t3 where a > all (select max(b) from t2 group by a);

869

892

explain extended select * from t3 where a > all (select max(b) from t2 group by a);

870

893

drop table t2, t3;

871

894

872

--echo #

873

--echo # correct used_tables()

874

--echo #

895

896

# correct used_tables()

897

875

898

876

CREATE TEMPORARY TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime, `last_update` datetime, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;

899

CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;

877

900

INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());

878

CREATE TEMPORARY TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` int NOT NULL default '0',`secondary_uid` int NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;

901

CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;

879

902

INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);

880

CREATE TEMPORARY TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` int NOT NULL default '1',`tues` int NOT NULL default '1',`wed` int NOT NULL default '1',`thur` int NOT NULL default '1',`fri` int NOT NULL default '1',`sat` int NOT NULL default '0',`sun` int NOT NULL default '0',`how_often` int NOT NULL default '1',`userid` int NOT NULL default '0',`active` int NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;

903

CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;

881

904

INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);

882

CREATE TEMPORARY TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;

905

CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;

883

906

INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');

884

907

select dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;

885

908

SELECT dbid, name FROM t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')) AND t4.task_id = taskid;

886

909

drop table t1,t2,t3,t4;

887

910

888

--echo #

889

--echo # cardinality check

890

--echo #

891

CREATE TEMPORARY TABLE t1 (id int default NULL) ENGINE=MyISAM;

911

912

# cardinality check

913

914

CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;

892

915

INSERT INTO t1 VALUES (1),(5);

893

CREATE TEMPORARY TABLE t2 (id int default NULL) ENGINE=MyISAM;

916

CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;

894

917

INSERT INTO t2 VALUES (2),(6);

895

918

-- error 1241

896

919

select * from t1 where (1,2,6) in (select * from t2);

897

920

DROP TABLE t1,t2;

898

921

899

--echo #

900

--echo # optimized ALL/ANY with union

901

--echo #

922

923

# optimized ALL/ANY with union

924

902

925

create table t1 (s1 char);

903

926

insert into t1 values ('e');

904

927

select * from t1 where 'f' > any (select s1 from t1);

905

928

select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);

906

--replace_column 9 #

907

929

explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);

908

930

drop table t1;

909

931

910

--echo #

911

--echo # filesort in subquery (restoring join_tab)

912

--echo #

913

CREATE TEMPORARY TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;

932

933

# filesort in subquery (restoring join_tab)

934

935

CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;

914

936

INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');

915

CREATE TEMPORARY TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;

937

CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;

916

938

INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');

917

939

select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c;

918

940

drop table t1, t2;

919

941

920

--echo #

921

--echo # unresolved field error

922

--echo #

923

create temporary table t1 (s1 int) ENGINE=MyISAM;

924

create temporary table t2 (s1 int) ENGINE=MyISAM;

942

943

# unresolved field error

944

945

create table t1 (s1 int);

946

create table t2 (s1 int);

925

947

-- error 1054

926

948

select * from t1 where (select count(*) from t2 where t1.s2) = 1;

927

949

-- error 1054

930

952

select count(*) from t2 group by t1.s2;

931

953

drop table t1, t2;

932

954

933

--echo #

934

--echo # fix_fields() in add_ref_to_table_cond()

935

--echo #

936

CREATE TEMPORARY TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;

937

CREATE TEMPORARY TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;

955

956

# fix_fields() in add_ref_to_table_cond()

957

958

CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));

959

CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));

938

960

INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');

939

961

INSERT INTO t2 VALUES (100, 200, 'C');

940

962

SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);

941

963

DROP TABLE t1, t2;

942

964

943

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

965

CREATE TABLE t1 (a int(1));

944

966

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

945

967

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

946

968

DROP TABLE t1;

947

969

948

--echo #

949

--echo # Bug 2479

950

--echo #

970

971

# Bug 2479

972

951

973

952

CREATE TEMPORARY TABLE `t1` (

953

`id` int NOT NULL auto_increment,

954

`id_cns` int NOT NULL default '0',

974

CREATE TABLE `t1` (

975

`id` int(11) NOT NULL auto_increment,

976

`id_cns` tinyint(3) unsigned NOT NULL default '0',

955

977

`tipo` enum('','UNO','DUE') NOT NULL default '',

956

`anno_dep` int NOT NULL default '0',

957

`particolare` bigint NOT NULL default '0',

958

`generale` bigint NOT NULL default '0',

959

`bis` int NOT NULL default '0',

978

`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',

979

`particolare` mediumint(8) unsigned NOT NULL default '0',

980

`generale` mediumint(8) unsigned NOT NULL default '0',

981

`bis` tinyint(3) unsigned NOT NULL default '0',

960

982

PRIMARY KEY (`id`),

961

983

UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),

962

UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`))

963

ENGINE=MyISAM;

984

UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)

985

);

964

986

INSERT INTO `t1` VALUES (1,16,'UNO',1987,2048,9681,0),(2,50,'UNO',1987,1536,13987,0),(3,16,'UNO',1987,2432,14594,0),(4,16,'UNO',1987,1792,13422,0),(5,16,'UNO',1987,1025,10240,0),(6,16,'UNO',1987,1026,7089,0);

965

CREATE TEMPORARY TABLE `t2` (

966

`id` int NOT NULL auto_increment,

967

`max_anno_dep` int NOT NULL default '0',

968

PRIMARY KEY (`id`)) ENGINE=MyISAM;

987

CREATE TABLE `t2` (

988

`id` tinyint(3) unsigned NOT NULL auto_increment,

989

`max_anno_dep` smallint(6) unsigned NOT NULL default '0',

990

PRIMARY KEY (`id`)

991

);

969

992

INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);

970

993

971

994

SELECT cns.id, cns.max_anno_dep, cns.max_anno_dep = (SELECT s.anno_dep FROM t1 AS s WHERE s.id_cns = cns.id ORDER BY s.anno_dep DESC LIMIT 1) AS PIPPO FROM t2 AS cns;

972

995

973

996

DROP TABLE t1, t2;

974

997

975

--echo #

976

--echo # GLOBAL LIMIT

977

--echo #

978

create temporary table t1 (a int) ENGINE=MyISAM;

998

999

# GLOBAL LIMIT

1000

1001

create table t1 (a int);

979

1002

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

980

1003

SET SQL_SELECT_LIMIT=1;

981

1004

select sum(a) from (select * from t1) as a;

983

1006

SET SQL_SELECT_LIMIT=default;

984

1007

drop table t1;

985

1008

986

--echo #

987

--echo # Bug #3118: subselect + order by

988

--echo #

1009

1010

# Bug #3118: subselect + order by

1011

989

1012

990

1013

CREATE TABLE t1 (a int, b int, INDEX (a));

991

1014

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

992

1015

SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;

993

1016

DROP TABLE t1;

994

1017

995

--echo # Item_cond fix field

996

--echo #

1018

# Item_cond fix field

1019

997

1020

create table t1(val varchar(10));

998

1021

insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');

999

1022

select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%');

1000

1023

drop table t1;

1001

1024

1002

--echo #

1003

--echo # ref_or_null replacing with ref

1004

--echo #

1025

1026

# ref_or_null replacing with ref

1027

1005

1028

create table t1 (id int not null, text varchar(20) not null default '', primary key (id));

1006

1029

insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');

1007

1030

select * from t1 where id not in (select id from t1 where id < 8);

1008

1031

select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);

1009

--replace_column 9 #

1010

1032

explain extended select * from t1 where id not in (select id from t1 where id < 8);

1011

--replace_column 9 #

1012

1033

explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);

1013

1034

insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');

1014

create temporary table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;

1035

create table t2 (id int not null, text varchar(20) not null default '', primary key (id));

1015

1036

insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');

1016

1037

select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);

1017

--replace_column 9 #

1018

1038

explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);

1019

1039

drop table t1,t2;

1020

1040

1021

--echo #

1022

--echo # Static tables & rund() in subqueries

1023

--echo #

1024

create temporary table t1 (a int) ENGINE=MyISAM;

1041

1042

# Static tables & rund() in subqueries

1043

1044

create table t1 (a int);

1025

1045

insert into t1 values (1);

1026

1046

explain select benchmark(1000, (select a from t1 where a=rand()));

1027

1047

drop table t1;

1028

1048

1029

--echo #

1030

--echo # bug 3188

1031

--echo #

1032

create temporary table t1(id int) ENGINE=MyISAM;

1033

create temporary table t2(id int) ENGINE=MyISAM;

1034

create temporary table t3(flag int) ENGINE=MyISAM;

1049

1050

# bug 3188

1051

1052

create table t1(id int);

1053

create table t2(id int);

1054

create table t3(flag int);

1035

1055

-- error 1064

1036

1056

select (select * from t3 where id not null) from t1, t2;

1037

1057

drop table t1,t2,t3;

1038

1058

1039

--echo #

1040

--echo # aggregate functions (Bug #3505)

1041

--echo #

1059

1060

# aggregate functions (Bug #3505)

1061

1042

1062

CREATE TABLE t1 (id INT);

1043

1063

CREATE TABLE t2 (id INT);

1044

1064

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

1049

1069

SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY id;

1050

1070

DROP TABLE t1,t2;

1051

1071

1052

--echo #

1053

--echo # ALL/ANY test

1054

--echo #

1072

1073

# ALL/ANY test

1074

1055

1075

CREATE TABLE t1 ( a int, b int );

1056

1076

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

1057

1077

SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );

1066

1086

SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );

1067

1087

SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );

1068

1088

SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );

1069

--echo # with index

1089

# with index

1070

1090

ALTER TABLE t1 ADD INDEX (a);

1071

1091

SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );

1072

1092

SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );

1080

1100

SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );

1081

1101

SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );

1082

1102

SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );

1083

--echo # having clause test

1103

# having clause test

1084

1104

SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);

1085

1105

SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);

1086

1106

SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);

1093

1113

SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);

1094

1114

SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);

1095

1115

SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);

1096

--echo # union test

1116

# union test

1097

1117

SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);

1098

1118

SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);

1099

1119

SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);

1106

1126

SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);

1107

1127

SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);

1108

1128

SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);

1109

--echo # union + having test

1129

# union + having test

1110

1130

SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);

1111

1131

SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);

1112

1132

SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);

1119

1139

SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);

1120

1140

SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);

1121

1141

SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);

1122

--echo # row tests

1123

--echo # < > >= <= and = ALL/ <> ANY do not support row operation

1142

# row tests

1143

# < > >= <= and = ALL/ <> ANY do not support row operation

1124

1144

-- error 1241

1125

1145

SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);

1126

1146

-- error 1241

1137

1157

SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);

1138

1158

-- error 1241

1139

1159

SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);

1140

--echo # following should be converted to IN

1160

# following should be converted to IN

1141

1161

-- error 1241

1142

1162

SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);

1143

1163

-- error 1241

1156

1176

SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);

1157

1177

SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);

1158

1178

SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);

1159

--echo # without optimisation

1179

# without optimisation

1160

1180

SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);

1161

1181

SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);

1162

1182

SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);

1169

1189

SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);

1170

1190

SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);

1171

1191

SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);

1172

--echo # without optimisation + having

1192

# without optimisation + having

1173

1193

SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);

1174

1194

SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);

1175

1195

SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);

1182

1202

SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);

1183

1203

SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);

1184

1204

SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);

1185

--echo # EXISTS in string contence

1205

# EXISTS in string contence

1186

1206

SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;

1187

1207

SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;

1188

1208

SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;

1219

1239

DROP TABLE t1;

1220

1240

1221

1241

1222

--echo #

1223

--echo # SELECT(EXISTS * ...)optimisation

1224

--echo #

1242

1243

# SELECT(EXISTS * ...)optimisation

1244

1225

1245

create table t1 (a int, b int);

1226

1246

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

1227

1247

select * from t1 up where exists (select * from t1 where t1.a=up.a);

1228

--replace_column 9 #

1229

1248

explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);

1230

1249

drop table t1;

1231

1250

1232

--echo #

1233

--echo # Bug #4102: subselect in HAVING

1234

--echo #

1251

1252

# Bug #4102: subselect in HAVING

1253

1235

1254

1236

CREATE TEMPORARY TABLE t1 (t1_a int) ENGINE=MyISAM;

1255

CREATE TABLE t1 (t1_a int);

1237

1256

INSERT INTO t1 VALUES (1);

1238

1257

CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));

1239

1258

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

1241

1260

HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);

1242

1261

DROP TABLE t1, t2;

1243

1262

1244

--echo #

1245

--echo # Test problem with NULL and derived tables (Bug #4097)

1246

--echo #

1263

1264

# Test problem with NULL and derived tables (Bug #4097)

1265

1247

1266

1248

CREATE TEMPORARY TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;

1267

CREATE TABLE t1 (id int(11) default NULL,name varchar(10) default NULL);

1249

1268

INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);

1250

CREATE TEMPORARY TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;

1269

CREATE TABLE t2 (id int(11) default NULL, pet varchar(10) default NULL);

1251

1270

INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');

1252

1271

SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;

1253

1272

drop table t1,t2;

1254

1273

1255

--echo #

1256

--echo # Aggregate function comparation with ALL/ANY/SOME subselect

1257

--echo #

1258

CREATE TEMPORARY TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;

1274

1275

# Aggregate function comparation with ALL/ANY/SOME subselect

1276

1277

CREATE TABLE `t1` ( `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

1259

1278

insert into t1 values (1);

1260

CREATE TEMPORARY TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;

1279

CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

1261

1280

insert into t2 values (1,2);

1262

1281

select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);

1263

1282

drop table t1,t2;

1264

1283

1265

--echo #

1266

--echo # BUG#5003 - like in subselect

1267

--echo #

1268

CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) COLLATE utf8_bin,`KUERZEL` VARCHAR(10) COLLATE utf8_bin,`IZAANALYSEART_ID` VARCHAR(11) COLLATE utf8_bin,`IZAPMKZ_ID` VARCHAR(11) COLLATE utf8_bin);

1284

1285

# BUG#5003 - like in subselect

1286

1287

CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin);

1269

1288

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

1270

1289

INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');

1271

1290

INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');

1274

1293

SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');

1275

1294

drop table t1;

1276

1295

1277

--echo #

1278

--echo # Optimized IN with compound index

1279

--echo #

1280

CREATE TEMPORARY TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;

1281

CREATE TEMPORARY TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;

1296

1297

# Optimized IN with compound index

1298

1299

CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));

1300

CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));

1282

1301

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

1283

1302

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

1284

1303

select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);

1290

1309

select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);

1291

1310

drop table t1,t2;

1292

1311

1293

--echo #

1294

--echo # resolving fields of grouped outer SELECT

1295

--echo #

1312

1313

# resolving fields of grouped outer SELECT

1314

1296

1315

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

1297

1316

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

1298

1317

SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;

1307

1326

select (select b.x from t1 as b where b.x=a.x) from t1 as a where a.x=2 group by a.x;

1308

1327

drop table t1;

1309

1328

1310

--echo #

1311

--echo # Test of correct maybe_null flag returning by subquwery for temporary table

1312

--echo # creation

1313

--echo #

1314

CREATE TEMPORARY TABLE `t1` ( `master` int NOT NULL default '0', `map` int NOT NULL default '0', `slave` int NOT NULL default '0', `access` int NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`)) ENGINE=MyISAM;

1329

1330

# Test of correct maybe_null flag returning by subquwery for temporary table

1331

# creation

1332

1333

CREATE TABLE `t1` ( `master` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `slave` int(10) unsigned NOT NULL default '0', `access` int(10) unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));

1315

1334

INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400);

1316

CREATE TEMPORARY TABLE `t2` ( `id` int NOT NULL default '0', `pid` int NOT NULL default '0', `map` int NOT NULL default '0', `level` int NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ENGINE=MyISAM ;

1335

CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL default '0', `pid` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `level` tinyint(4) unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;

1317

1336

INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a');

1318

1337

-- error 1054

1319

1338

SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;

1320

1339

SELECT b.ac FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;

1321

1340

drop tables t1,t2;

1322

1341

1323

--echo #

1324

--echo # Subselect in non-select command just after connection

1325

--echo #

1342

1343

# Subselect in non-select command just after connection

1344

1326

1345

connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);

1327

1346

connection root;

1328

1347

set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;

1329

1348

1330

--echo #

1331

--echo # primary query with temporary table and subquery with groupping

1332

--echo #

1333

create temporary table t1 (a int, b int) ENGINE=MyISAM;

1334

create temporary table t2 (a int, b int) ENGINE=MyISAM;

1349

1350

# primary query with temporary table and subquery with groupping

1351

1352

create table t1 (a int, b int);

1353

create table t2 (a int, b int);

1335

1354

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

1336

1355

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

1337

1356

select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;

1338

1357

drop table t1, t2;

1339

1358

1340

--echo #

1341

--echo # Equal operation under row and empty subquery

1342

--echo #

1359

1360

# Equal operation under row and empty subquery

1361

1343

1362

create table t1 (s1 int,s2 int);

1344

1363

insert into t1 values (20,15);

1345

1364

select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));

1346

1365

drop table t1;

1347

1366

1348

--echo #

1349

--echo # ALL/ANY with NULL

1350

--echo #

1367

1368

# ALL/ANY with NULL

1369

1351

1370

create table t1 (s1 int);

1352

1371

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

1353

1372

select * from t1 where s1 < all (select s1 from t1);

1354

1373

select s1, s1 < all (select s1 from t1) from t1;

1355

1374

drop table t1;

1356

1375

1357

--echo #

1358

--echo # reference on changable fields from subquery

1359

--echo #

1376

1377

# reference on changable fields from subquery

1378

1360

1379

CREATE TABLE t1 (

1361

1380

Code char(3) NOT NULL default '',

1362

1381

Name char(52) NOT NULL default '',

1363

1382

Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',

1364

1383

Region char(26) NOT NULL default '',

1365

1384

SurfaceArea float(10,2) NOT NULL default '0.00',

1366

IndepYear int default NULL,

1367

Population int NOT NULL default '0',

1385

IndepYear smallint(6) default NULL,

1386

Population int(11) NOT NULL default '0',

1368

1387

LifeExpectancy float(3,1) default NULL,

1369

1388

GNP float(10,2) default NULL,

1370

1389

GNPOld float(10,2) default NULL,

1371

1390

LocalName char(45) NOT NULL default '',

1372

1391

GovernmentForm char(45) NOT NULL default '',

1373

1392

HeadOfState char(60) default NULL,

1374

Capital int default NULL,

1393

Capital int(11) default NULL,

1375

1394

Code2 char(2) NOT NULL default ''

1376

);

1395

) ENGINE=MyISAM;

1377

1396

INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');

1378

1397

INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,68000,75.1,334.00,NULL,'Amerika Samoa','US Territory','George W. Bush',54,'AS');

1379

1398

INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');

1384

1403

Population < 200);

1385

1404

drop table t1;

1386

1405

1387

--echo #

1388

--echo # Test for BUG#7885: Server crash when 'any' subselect compared to

1389

--echo # non-existant field.

1390

--echo #

1391

create temporary table t1 (a1 int) ENGINE=MyISAM;

1392

create temporary table t2 (b1 int) ENGINE=MyISAM;

1393

--error ER_BAD_FIELD_ERROR

1406

1407

# Test for BUG#7885: Server crash when 'any' subselect compared to

1408

# non-existant field.

1409

1410

create table t1 (a1 int);

1411

create table t2 (b1 int);

1412

--error 1054

1394

1413

select * from t1 where a2 > any(select b1 from t2);

1395

1414

select * from t1 where a1 > any(select b1 from t2);

1396

1415

drop table t1,t2;

1397

1416

1398

1417

1399

--echo #

1400

--echo # Comparison subquery with * and row

1401

--echo #

1402

create temporary table t1 (a integer, b integer) ENGINE=MyISAM;

1418

1419

# Comparison subquery with * and row

1420

1421

create table t1 (a integer, b integer);

1403

1422

select (select * from t1) = (select 1,2);

1404

1423

select (select 1,2) = (select * from t1);

1405

--echo # queries whih can be converted to IN

1424

# queries whih can be converted to IN

1406

1425

select row(1,2) = ANY (select * from t1);

1407

1426

select row(1,2) != ALL (select * from t1);

1408

1427

drop table t1;

1409

1428

1410

--echo #

1411

--echo # Comparison subquery and row with nested rows

1412

--echo #

1413

create temporary table t1 (a integer, b integer) ENGINE=MyISAM;

1429

1430

# Comparison subquery and row with nested rows

1431

1432

create table t1 (a integer, b integer);

1414

1433

-- error 1241

1415

1434

select row(1,(2,2)) in (select * from t1 );

1416

1435

-- error 1241

1419

1438

select (select * from t1) = row(1,(2,2));

1420

1439

drop table t1;

1421

1440

1422

--echo #

1423

--echo # Forward reference detection

1424

--echo #

1441

1442

# Forward reference detection

1443

1425

1444

create table t1 (a integer);

1426

1445

insert into t1 values (1);

1427

1446

-- error 1247

1433

1452

select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;

1434

1453

drop table t1;

1435

1454

1436

--echo #

1437

--echo # Test for BUG#8218

1438

--echo #

1439

CREATE TEMPORARY TABLE t1 (

1440

categoryId int NOT NULL,

1441

courseId int NOT NULL,

1455

1456

# Test for BUG#8218

1457

1458

CREATE TABLE t1 (

1459

categoryId int(11) NOT NULL,

1460

courseId int(11) NOT NULL,

1442

1461

startDate datetime NOT NULL,

1443

1462

endDate datetime NOT NULL,

1444

1463

createDate datetime NOT NULL,

1445

1464

modifyDate timestamp NOT NULL,

1446

attributes text NOT NULL)

1447

engine=myisam;

1448

1465

attributes text NOT NULL

1466

);

1449

1467

INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),

1450

1468

(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),

1451

1469

(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),

1456

1474

(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),

1457

1475

(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');

1458

1476

1459

CREATE TEMPORARY TABLE t2 (

1460

userId int NOT NULL,

1461

courseId int NOT NULL,

1462

date datetime NOT NULL)

1463

engine=myisam;

1464

1477

CREATE TABLE t2 (

1478

userId int(11) NOT NULL,

1479

courseId int(11) NOT NULL,

1480

date datetime NOT NULL

1481

);

1465

1482

INSERT INTO t2 VALUES (5141,71,'2003-11-18'),

1466

1483

(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),

1467

1484

(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),

1470

1487

(5141,89,'2004-10-22'),(5141,51,'2004-10-26');

1471

1488

1472

1489

1473

CREATE TEMPORARY TABLE t3 (

1474

groupId int NOT NULL,

1475

parentId int NOT NULL,

1490

CREATE TABLE t3 (

1491

groupId int(11) NOT NULL,

1492

parentId int(11) NOT NULL,

1476

1493

startDate datetime NOT NULL,

1477

1494

endDate datetime NOT NULL,

1478

1495

createDate datetime NOT NULL,

1479

1496

modifyDate timestamp NOT NULL,

1480

ordering int)

1481

engine=myisam;

1482

1497

ordering int(11)

1498

);

1483

1499

INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);

1484

1500

1485

CREATE TEMPORARY TABLE t4 (

1486

id int NOT NULL,

1487

groupTypeId int NOT NULL,

1501

CREATE TABLE t4 (

1502

id int(11) NOT NULL,

1503

groupTypeId int(11) NOT NULL,

1488

1504

groupKey varchar(50) NOT NULL,

1489

1505

name text,

1490

ordering int,

1506

ordering int(11),

1491

1507

description text,

1492

1508

createDate datetime NOT NULL,

1493

modifyDate timestamp NOT NULL)

1494

engine=myisam;

1495

1509

modifyDate timestamp NOT NULL

1510

);

1496

1511

INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),

1497

1512

(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');

1498

1513

1499

CREATE TEMPORARY TABLE t5 (

1500

userId int NOT NULL,

1501

groupId int NOT NULL,

1514

CREATE TABLE t5 (

1515

userId int(11) NOT NULL,

1516

groupId int(11) NOT NULL,

1502

1517

createDate datetime NOT NULL,

1503

modifyDate timestamp NOT NULL) ENGINE=MyISAM;

1504

1518

modifyDate timestamp NOT NULL

1519

);

1505

1520

INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');

1506

1521

1507

1522

select

1538

1553

1539

1554

drop table t1, t2, t3, t4, t5;

1540

1555

1541

--echo #

1542

--echo # Transformation in left expression of subquery (BUG#8888)

1543

--echo #

1544

create temporary table t1 (a int) ENGINE=MyISAM;

1556

1557

# Transformation in left expression of subquery (BUG#8888)

1558

1559

create table t1 (a int);

1545

1560

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

1546

1561

SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);

1547

1562

drop table t1;

1548

1563

1549

--echo #

1550

--echo # single row subqueries and row operations (code covarage improvement)

1551

--echo #

1552

create temporary table t1 (a int, b int) ENGINE=MyISAM;

1564

1565

# single row subqueries and row operations (code covarage improvement)

1566

1567

create table t1 (a int, b int);

1553

1568

insert into t1 values (1,2);

1554

1569

-- error 1241

1555

1570

select 1 = (select * from t1);

1565

1580

select (select * from t1) = (1,2,3);

1566

1581

drop table t1;

1567

1582

1568

--echo #

1569

--echo # Item_int_with_ref check (BUG#10020)

1570

--echo #

1571

--echo #CREATE TABLE `t1` (

1572

--echo # `itemid` bigint NOT NULL auto_increment,

1573

--echo # `sessionid` bigint default NULL,

1574

--echo # `time` int NOT NULL default '0',

1575

--echo # `data` text collate latin1_general_ci NOT NULL,

1576

--echo # PRIMARY KEY (`itemid`)

1577

--echo #);

1578

--echo #INSERT INTO `t1` VALUES (1, 1, 1, '');

1579

--echo #CREATE TABLE `t2` (

1580

--echo # `sessionid` bigint NOT NULL auto_increment,

1581

--echo # `pid` int NOT NULL default '0',

1582

--echo # `date` int NOT NULL default '0',

1583

--echo # `ip` varchar(15) collate latin1_general_ci NOT NULL default '',

1584

--echo # PRIMARY KEY (`sessionid`)

1585

--echo #);

1586

--echo #INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');

1587

--echo #SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;

1588

--echo #drop tables t1,t2;

1583

1584

# Item_int_with_ref check (BUG#10020)

1585

1586

CREATE TABLE `t1` (

1587

`itemid` bigint(20) unsigned NOT NULL auto_increment,

1588

`sessionid` bigint(20) unsigned default NULL,

1589

`time` int(10) unsigned NOT NULL default '0',

1590

`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT

1591

NULL default '',

1592

`data` text collate latin1_general_ci NOT NULL,

1593

PRIMARY KEY (`itemid`)

1594

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1595

INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');

1596

CREATE TABLE `t2` (

1597

`sessionid` bigint(20) unsigned NOT NULL auto_increment,

1598

`pid` int(10) unsigned NOT NULL default '0',

1599

`date` int(10) unsigned NOT NULL default '0',

1600

`ip` varchar(15) collate latin1_general_ci NOT NULL default '',

1601

PRIMARY KEY (`sessionid`)

1602

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1603

INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');

1604

SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;

1605

drop tables t1,t2;

1589

1606

1590

--echo # BUG#11821 : Select from subselect using aggregate function on an enum

1591

--echo # segfaults:

1592

create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;

1607

# BUG#11821 : Select from subselect using aggregate function on an enum

1608

# segfaults:

1609

create table t1 (fld enum('0','1'));

1593

1610

insert into t1 values ('1');

1594

1611

select * from (select max(fld) from t1) as foo;

1595

1612

drop table t1;

1596

1613

1597

--echo #

1598

--echo # Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)

1599

--echo #

1614

1615

# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)

1616

1600

1617

1601

1618

CREATE TABLE t1 (one int, two int, flag char(1));

1602

1619

CREATE TABLE t2 (one int, two int, flag char(1));

1625

1642

explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;

1626

1643

DROP TABLE t1,t2;

1627

1644

1628

--echo #

1629

--echo # Bug #12392: where cond with IN predicate for rows and NULL values in table

1630

--echo #

1645

1646

# Bug #12392: where cond with IN predicate for rows and NULL values in table

1647

1631

1648

1632

CREATE TEMPORARY TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;

1649

CREATE TABLE t1 (a char(5), b char(5));

1633

1650

INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');

1634

1651

1635

1652

SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));

1636

1653

1637

1654

DROP TABLE t1;

1638

1655

1639

--echo #

1640

--echo # Bug #11479: subquery over left join with an empty inner table

1641

--echo #

1656

1657

# Bug #11479: subquery over left join with an empty inner table

1658

1642

1659

1643

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

1644

CREATE TEMPORARY TABLE t2 (a int, b int) ENGINE=MyISAM;

1645

CREATE TEMPORARY TABLE t3 (b int NOT NULL) ENGINE=MyISAM;

1660

CREATE TABLE t1 (a int);

1661

CREATE TABLE t2 (a int, b int);

1662

CREATE TABLE t3 (b int NOT NULL);

1646

1663

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

1647

1664

INSERT INTO t2 VALUES (1,10), (3,30);

1648

1665

1654

1671

1655

1672

DROP TABLE t1,t2,t3;

1656

1673

1657

--echo #

1658

--echo # Bug#18503: Queries with a quantified subquery returning empty set may

1659

--echo # return a wrong result.

1660

--echo #

1661

CREATE TEMPORARY TABLE t1 (f1 INT) ENGINE=MyISAM;

1662

CREATE TEMPORARY TABLE t2 (f2 INT) ENGINE=MyISAM;

1674

1675

# Bug#18503: Queries with a quantified subquery returning empty set may

1676

# return a wrong result.

1677

1678

CREATE TABLE t1 (f1 INT);

1679

CREATE TABLE t2 (f2 INT);

1663

1680

INSERT INTO t1 VALUES (1);

1664

1681

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);

1665

1682

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);

1668

1685

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);

1669

1686

DROP TABLE t1, t2;

1670

1687

1671

--echo # BUG#20975 Wrong query results for subqueries within NOT

1688

# BUG#20975 Wrong query results for subqueries within NOT

1672

1689

create table t1 (s1 char);

1673

1690

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

1674

1691

1685

1702

select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));

1686

1703

drop table t1;

1687

1704

1688

--echo #

1689

--echo # Bug #16255: Subquery in where

1690

--echo #

1705

1706

# Bug #16255: Subquery in where

1707

1691

1708

create table t1 (

1692

1709

retailerID varchar(8) NOT NULL,

1693

statusID int NOT NULL,

1710

statusID int(10) unsigned NOT NULL,

1694

1711

changed datetime NOT NULL,

1695

UNIQUE KEY retailerID (retailerID, statusID, changed));

1712

UNIQUE KEY retailerID (retailerID, statusID, changed)

1713

);

1696

1714

1697

1715

INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");

1698

1716

INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");

1707

1725

group by r2.retailerId);

1708

1726

drop table t1;

1709

1727

1710

--echo #

1711

--echo # Bug #21180: Subselect with index for both WHERE and ORDER BY

1712

--echo # produces empty result

1713

--echo #

1728

1729

# Bug #21180: Subselect with index for both WHERE and ORDER BY

1730

# produces empty result

1731

1714

1732

create table t1(a int, primary key (a));

1715

1733

insert into t1 values (10);

1716

1734

1717

1735

create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));

1718

1736

insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');

1719

1737

1720

--replace_column 9 #

1721

1738

explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

1722

1739

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

1723

1740

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

1734

1751

1735

1752

drop table t1,t2;

1736

1753

1737

--echo #

1738

--echo # Bug #21853: assert failure for a grouping query with

1739

--echo # an ALL/ANY quantified subquery in HAVING

1740

--echo #

1754

1755

# Bug #21853: assert failure for a grouping query with

1756

# an ALL/ANY quantified subquery in HAVING

1757

1741

1758

1742

CREATE TEMPORARY TABLE t1 (

1759

CREATE TABLE t1 (

1743

1760

field1 int NOT NULL,

1744

1761

field2 int NOT NULL,

1745

1762

field3 int NOT NULL,

1746

PRIMARY KEY (field1,field2,field3))

1747

ENGINE=MyISAM;

1748

1749

CREATE TEMPORARY TABLE t2 (

1763

PRIMARY KEY (field1,field2,field3)

1764

);

1765

CREATE TABLE t2 (

1750

1766

fieldA int NOT NULL,

1751

1767

fieldB int NOT NULL,

1752

PRIMARY KEY (fieldA,fieldB))

1753

ENGINE=MyISAM;

1768

PRIMARY KEY (fieldA,fieldB)

1769

);

1754

1770

1755

1771

INSERT INTO t1 VALUES

1756

1772

(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);

1772

1788

1773

1789

DROP TABLE t1, t2;

1774

1790

1775

--echo #

1776

--echo # Bug #23478: not top-level IN subquery returning a non-empty result set

1777

--echo # with possible NULL values by index access from the outer query

1778

--echo #

1791

1792

# Bug #23478: not top-level IN subquery returning a non-empty result set

1793

# with possible NULL values by index access from the outer query

1794

1779

1795

1780

CREATE TEMPORARY TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;

1796

CREATE TABLE t1(a int, INDEX (a));

1781

1797

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

1782

1798

INSERT INTO t1 VALUES (NULL);

1783

1799

1784

CREATE TEMPORARY TABLE t2(a int) ENGINE=MyISAM;

1800

CREATE TABLE t2(a int);

1785

1801

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

1786

1802

1787

1803

EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;

1789

1805

1790

1806

DROP TABLE t1,t2;

1791

1807

1792

--echo #

1793

--echo # Bug #11302: getObject() returns a String for a sub-query of type datetime

1794

--echo #

1808

1809

# Bug #11302: getObject() returns a String for a sub-query of type datetime

1810

1795

1811

CREATE TABLE t1 (a DATETIME);

1796

1812

INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');

1797

1813

1798

CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT

1814

CREATE TABLE t2 AS SELECT

1799

1815

(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a

1800

1816

FROM t1 WHERE a > '2000-01-01';

1801

1817

SHOW CREATE TABLE t2;

1802

1818

1803

CREATE TEMPORARY TABLE t3 ENGINE=MyISAM AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');

1819

CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');

1804

1820

SHOW CREATE TABLE t3;

1805

1821

1806

1822

DROP TABLE t1,t2,t3;

1807

1823

1808

--echo #

1809

--echo # Bug 24653: sorting by expressions containing subselects

1810

--echo # that return more than one row

1811

--echo #

1824

1825

# Bug 24653: sorting by expressions containing subselects

1826

# that return more than one row

1827

1812

1828

1813

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

1829

CREATE TABLE t1 (a int);

1814

1830

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

1815

1831

1816

1832

CREATE TABLE t2 (b int, c int);

1818

1834

(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);

1819

1835

1820

1836

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );

1821

--error ER_SUBQUERY_NO_1_ROW

1837

--error 1242

1822

1838

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);

1823

1839

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;

1824

--error ER_SUBQUERY_NO_1_ROW

1840

--error 1242

1825

1841

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;

1826

1842

1827

1843

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);

1828

--error ER_SUBQUERY_NO_1_ROW

1844

--error 1242

1829

1845

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);

1830

1846

1831

1847

1832

1848

SELECT a FROM t1 GROUP BY a

1833

1849

HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),

1834

1850

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

1835

--error ER_SUBQUERY_NO_1_ROW

1851

--error 1242

1836

1852

SELECT a FROM t1 GROUP BY a

1837

1853

HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),

1838

1854

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

1840

1856

SELECT a FROM t1 GROUP BY a

1841

1857

HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),

1842

1858

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

1843

--error ER_SUBQUERY_NO_1_ROW

1859

--error 1242

1844

1860

SELECT a FROM t1 GROUP BY a

1845

1861

HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),

1846

1862

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;

1848

1864

SELECT a FROM t1

1849

1865

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),

1850

1866

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));

1851

--error ER_SUBQUERY_NO_1_ROW

1867

--error 1242

1852

1868

SELECT a FROM t1

1853

1869

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),

1854

1870

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

1856

1872

SELECT a FROM t1

1857

1873

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),

1858

1874

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));

1859

--error ER_SUBQUERY_NO_1_ROW

1875

--error 1242

1860

1876

SELECT a FROM t1

1861

1877

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),

1862

1878

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

1863

1879

1864

1880

DROP TABLE t1,t2;

1865

1881

1866

--echo # End of 4.1 tests

1882

# End of 4.1 tests

1867

1883

1868

--echo #

1869

--echo #decimal-related tests

1870

--echo #

1884

1885

#decimal-related tests

1886

1871

1887

create table t1 (df decimal(5,1));

1872

1888

insert into t1 values(1.1);

1873

1889

insert into t1 values(2.2);

1876

1892

select * from t1 where df >= all (select avg(df) from t1 group by df);

1877

1893

drop table t1;

1878

1894

1879

create temporary table t1 (df decimal(5,1)) ENGINE=MyISAM;

1895

create table t1 (df decimal(5,1));

1880

1896

insert into t1 values(1.1);

1881

1897

select 1.1 * exists(select * from t1);

1882

1898

drop table t1;

1883

1899

1884

CREATE TEMPORARY TABLE t1 (

1885

grp int default NULL,

1886

a decimal(10,2) default NULL) ENGINE=MyISAM;

1900

CREATE TABLE t1 (

1901

grp int(11) default NULL,

1902

a decimal(10,2) default NULL);

1887

1903

1888

1904

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

1889

1905

select * from t1;

1890

1906

select min(a) from t1 group by grp;

1891

1907

drop table t1;

1892

1908

1893

--echo #

1894

--echo # Test for bug #9338: lame substitution of c1 instead of c2

1895

--echo #

1909

1910

# Test for bug #9338: lame substitution of c1 instead of c2

1911

1896

1912

1897

CREATE temporary table t1 ( c1 integer ) ENGINE=MyISAM;

1913

CREATE table t1 ( c1 integer );

1898

1914

INSERT INTO t1 VALUES ( 1 );

1899

1915

INSERT INTO t1 VALUES ( 2 );

1900

1916

INSERT INTO t1 VALUES ( 3 );

1911

1927

1912

1928

DROP TABLE t1,t2;

1913

1929

1914

--echo #

1915

--echo # Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ

1916

--echo #

1917

CREATE TEMPORARY TABLE t1 ( c1 integer ) ENGINE=MyISAM;

1930

1931

# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ

1932

1933

CREATE TABLE t1 ( c1 integer );

1918

1934

INSERT INTO t1 VALUES ( 1 );

1919

1935

INSERT INTO t1 VALUES ( 2 );

1920

1936

INSERT INTO t1 VALUES ( 3 );

1921

1937

INSERT INTO t1 VALUES ( 6 );

1922

1938

1923

CREATE TEMPORARY TABLE t2 ( c2 integer ) ENGINE=MyISAM;

1939

CREATE TABLE t2 ( c2 integer );

1924

1940

INSERT INTO t2 VALUES ( 1 );

1925

1941

INSERT INTO t2 VALUES ( 4 );

1926

1942

INSERT INTO t2 VALUES ( 5 );

1927

1943

INSERT INTO t2 VALUES ( 6 );

1928

1944

1929

CREATE TEMPORARY TABLE t3 ( c3 integer ) ENGINE=MyISAM;

1945

CREATE TABLE t3 ( c3 integer );

1930

1946

INSERT INTO t3 VALUES ( 7 );

1931

1947

INSERT INTO t3 VALUES ( 8 );

1932

1948

1935

1951

1936

1952

DROP TABLE t1,t2,t3;

1937

1953

1938

--echo #

1939

--echo # Correct building of equal fields list (do not include outer

1940

--echo # fields) (BUG#6384)

1941

--echo #

1942

CREATE TEMPORARY TABLE t1 (EMPNUM CHAR(3)) ENGINE=MyISAM;

1943

CREATE TEMPORARY TABLE t2 (EMPNUM CHAR(3) ) ENGINE=MyISAM;

1954

1955

# Item_int_with_ref check (BUG#10020)

1956

1957

CREATE TABLE `t1` (

1958

`itemid` bigint(20) unsigned NOT NULL auto_increment,

1959

`sessionid` bigint(20) unsigned default NULL,

1960

`time` int(10) unsigned NOT NULL default '0',

1961

`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT

1962

NULL default '',

1963

`data` text collate latin1_general_ci NOT NULL,

1964

PRIMARY KEY (`itemid`)

1965

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1966

INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');

1967

CREATE TABLE `t2` (

1968

`sessionid` bigint(20) unsigned NOT NULL auto_increment,

1969

`pid` int(10) unsigned NOT NULL default '0',

1970

`date` int(10) unsigned NOT NULL default '0',

1971

`ip` varchar(15) collate latin1_general_ci NOT NULL default '',

1972

PRIMARY KEY (`sessionid`)

1973

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1974

INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');

1975

1976

drop tables t1,t2;

1977

1978

1979

# Correct building of equal fields list (do not include outer

1980

# fields) (BUG#6384)

1981

1982

CREATE TABLE t1 (EMPNUM CHAR(3));

1983

CREATE TABLE t2 (EMPNUM CHAR(3) );

1944

1984

INSERT INTO t1 VALUES ('E1'),('E2');

1945

1985

INSERT INTO t2 VALUES ('E1');

1946

1986

DELETE FROM t1

1951

1991

select * from t1;

1952

1992

DROP TABLE t1,t2;

1953

1993

1954

--echo #

1955

--echo # Test for bug #11487: range access in a subquery

1956

--echo #

1994

1995

# Test for bug #11487: range access in a subquery

1996

1957

1997

1958

CREATE TEMPORARY TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;

1998

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);

1959

1999

INSERT INTO t1 VALUES (1, 1);

1960

CREATE TEMPORARY TABLE t2 (select_id BIGINT, values_id BIGINT,

1961

PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;

2000

CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,

2001

PRIMARY KEY(select_id,values_id));

1962

2002

INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);

1963

2003

1964

2004

SELECT values_id FROM t1

1973

2013

1974

2014

DROP TABLE t1, t2;

1975

2015

1976

--echo # BUG#11821 : Select from subselect using aggregate function on an enum

1977

--echo # segfaults:

1978

create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;

2016

# BUG#11821 : Select from subselect using aggregate function on an enum

2017

# segfaults:

2018

create table t1 (fld enum('0','1'));

1979

2019

insert into t1 values ('1');

1980

2020

select * from (select max(fld) from t1) as foo;

1981

2021

drop table t1;

1982

2022

1983

--echo #

1984

--echo # Test for bug #11762: subquery with an aggregate function in HAVING

1985

--echo #

2023

2024

# Test for bug #11762: subquery with an aggregate function in HAVING

2025

1986

2026

1987

CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;

1988

CREATE TEMPORARY TABLE t2 (c int, d int) ENGINE=MyISAM;

1989

CREATE TEMPORARY TABLE t3 (e int) ENGINE=MyISAM;

2027

CREATE TABLE t1 (a int, b int);

2028

CREATE TABLE t2 (c int, d int);

2029

CREATE TABLE t3 (e int);

1990

2030

1991

2031

INSERT INTO t1 VALUES

1992

2032

(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);

2064

2104

2065

2105

DROP TABLE t1,t2,t3;

2066

2106

2067

--echo #

2068

--echo # Test for bug #16603: GROUP BY in a row subquery with a quantifier

2069

--echo # when an index is defined on the grouping field

2107

2108

# Test for bug #16603: GROUP BY in a row subquery with a quantifier

2109

# when an index is defined on the grouping field

2070

2110

2071

2111

CREATE TABLE t1 (a varchar(5), b varchar(10));

2072

2112

INSERT INTO t1 VALUES

2074

2114

('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);

2075

2115

2076

2116

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

2077

--replace_column 9 #

2078

2117

EXPLAIN

2079

2118

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

2080

2119

2081

2120

ALTER TABLE t1 ADD INDEX(a);

2082

2121

2083

2122

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

2084

--replace_column 9 #

2085

2123

EXPLAIN

2086

2124

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

2087

2125

2088

2126

DROP TABLE t1;

2089

2127

2090

--echo #

2091

--echo # Bug#17366: Unchecked Item_int results in server crash

2092

--echo #

2128

2129

# Bug#17366: Unchecked Item_int results in server crash

2130

2093

2131

create table t1( f1 int,f2 int);

2094

2132

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

2095

2133

select tt.t from (select 'crash1' as t, f2 from t1) as tt left join t1 on tt.t = 'crash2' and tt.f2 = t1.f2 where tt.t = 'crash1';

2096

2134

drop table t1;

2097

2135

2098

--echo #

2099

--echo # Bug #18306: server crash on delete using subquery.

2100

--echo #

2136

2137

# Bug #18306: server crash on delete using subquery.

2138

2101

2139

2102

create temporary table t1 (c int, key(c)) ENGINE=MyISAM;

2140

create table t1 (c int, key(c));

2103

2141

insert into t1 values (1142477582), (1142455969);

2104

create temporary table t2 (a int, b int) ENGINE=MyISAM;

2142

create table t2 (a int, b int);

2105

2143

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

2106

2144

delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;

2107

2145

drop table t1, t2;

2108

2146

2109

--echo #

2110

--echo # Bug#19077: A nested materialized derived table is used before being populated.

2111

--echo #

2147

2148

# Bug#19077: A nested materialized derived table is used before being populated.

2149

2112

2150

create table t1 (i int, j bigint);

2113

2151

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

2114

2152

select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;

2115

2153

drop table t1;

2116

2154

2117

--echo #

2118

--echo # Bug#19700: subselect returning BIGINT always returned it as SIGNED

2119

--echo #

2120

CREATE TEMPORARY TABLE t1 (i BIGINT) ENGINE=MyISAM;

2121

INSERT INTO t1 VALUES (10000000000000000); # > MAX SIGNED BIGINT 9323372036854775807

2155

2156

# Bug#19700: subselect returning BIGINT always returned it as SIGNED

2157

2158

CREATE TABLE t1 (i BIGINT UNSIGNED);

2159

INSERT INTO t1 VALUES (10000000000000000000); # > MAX SIGNED BIGINT 9323372036854775807

2122

2160

INSERT INTO t1 VALUES (1);

2123

2161

2124

CREATE TEMPORARY TABLE t2 (i BIGINT) ENGINE=MyISAM;

2125

INSERT INTO t2 VALUES (10000000000000000); # same as first table

2162

CREATE TABLE t2 (i BIGINT UNSIGNED);

2163

INSERT INTO t2 VALUES (10000000000000000000); # same as first table

2126

2164

INSERT INTO t2 VALUES (1);

2127

2165

2128

2166

/* simple test */

2132

2170

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

2133

2171

2134

2172

/* subquery test with cast*/

2135

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

2173

SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);

2136

2174

2137

2175

DROP TABLE t1;

2138

2176

DROP TABLE t2;

2139

2177

2140

--echo #

2141

--echo # Bug#20519: subselect with LIMIT M, N

2142

--echo #

2178

2179

# Bug#20519: subselect with LIMIT M, N

2180

2143

2181

2144

CREATE TEMPORARY TABLE t1 (

2145

id bigint NOT NULL auto_increment,

2182

CREATE TABLE t1 (

2183

id bigint(20) unsigned NOT NULL auto_increment,

2146

2184

name varchar(255) NOT NULL,

2147

PRIMARY KEY (id))

2148

ENGINE=MyISAM;

2149

2185

PRIMARY KEY (id)

2186

);

2150

2187

INSERT INTO t1 VALUES

2151

2188

(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

2152

2189

2153

2190

CREATE TABLE t2 (

2154

id bigint NOT NULL auto_increment,

2155

mid bigint NOT NULL,

2191

id bigint(20) unsigned NOT NULL auto_increment,

2192

mid bigint(20) unsigned NOT NULL,

2156

2193

date date NOT NULL,

2157

PRIMARY KEY (id));

2158

2194

PRIMARY KEY (id)

2195

);

2159

2196

INSERT INTO t2 VALUES

2160

2197

(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),

2161

2198

(4, 2, '2006-04-20'), (5, 1, '2006-05-01');

2178

2215

FROM t1;

2179

2216

DROP TABLE t1,t2;

2180

2217

2181

--echo #

2182

--echo # Bug#20869: subselect with range access by DESC

2183

--echo #

2218

2219

# Bug#20869: subselect with range access by DESC

2220

2184

2221

2185

2222

CREATE TABLE t1 (

2186

i1 int NOT NULL default '0',

2187

i2 int NOT NULL default '0',

2188

t datetime,

2189

PRIMARY KEY (i1,i2,t));

2190

2223

i1 int(11) NOT NULL default '0',

2224

i2 int(11) NOT NULL default '0',

2225

t datetime NOT NULL default '0000-00-00 00:00:00',

2226

PRIMARY KEY (i1,i2,t)

2227

);

2191

2228

INSERT INTO t1 VALUES

2192

2229

(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),

2193

2230

(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),

2197

2234

(24,2,'2005-05-27 12:40:06');

2198

2235

2199

2236

CREATE TABLE t2 (

2200

i1 int NOT NULL default '0',

2201

i2 int NOT NULL default '0',

2237

i1 int(11) NOT NULL default '0',

2238

i2 int(11) NOT NULL default '0',

2202

2239

t datetime default NULL,

2203

PRIMARY KEY (i1));

2204

2240

PRIMARY KEY (i1)

2241

);

2205

2242

INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');

2206

2243

2207

2244

EXPLAIN

2216

2253

2217

2254

DROP TABLE t1, t2;

2218

2255

2219

--echo #

2220

--echo # Bug#14654 : Cannot select from the same table twice within a UNION

2221

--echo # statement

2222

--echo #

2256

2257

# Bug#14654 : Cannot select from the same table twice within a UNION

2258

# statement

2259

2223

2260

CREATE TABLE t1 (i INT);

2224

2261

2225

2262

(SELECT i FROM t1) UNION (SELECT i FROM t1);

2229

2266

(SELECT i FROM t1)

2230

2267

);

2231

2268

2232

--echo #TODO:not supported

2269

#TODO:not supported

2233

2270

--error ER_PARSE_ERROR

2234

2271

SELECT * FROM t1

2235

2272

WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));

2236

2273

2237

--echo #TODO:not supported

2238

--error ER_PARSE_ERROR

2274

#TODO:not supported

2275

--error 1064

2239

2276

explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))

2240

2277

from t1;

2241

2278

2242

--replace_column 9 #

2243

2279

explain select * from t1 where not exists

2244

2280

((select t11.i from t1 t11) union (select t12.i from t1 t12));

2245

2281

2246

2282

DROP TABLE t1;

2247

2283

2248

--echo #

2249

--echo # Bug #21540: Subqueries with no from and aggregate functions return

2250

--echo # wrong results

2284

2285

# Bug#21798: memory leak during query execution with subquery in column

2286

# list using a function

2287

2288

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

2289

insert into t1 (a) values (FLOOR(rand() * 100));

2290