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

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

--echo # 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 #

100

104

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

101

105

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

102

106

delete from t2 where a=100;

105

109

-- error 1241

106

110

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

107

111

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

108

# empty set

112

--echo # empty set

109

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

110

114

insert into t2 values (2,10);

111

115

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

112

116

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

113

117

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

create table t5 (a int);

118

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

115

119

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

116

120

insert into t5 values (5);

117

121

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

118

122

insert into t5 values (2);

119

123

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 #

120

125

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;

121

126

-- error 1242

122

127

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

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

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;

125

130

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

126

131

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

127

132

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

128

133

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

129

134

130

# not unique fields

135

--echo # not unique fields

131

136

-- error 1052

132

137

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

133

138

134

# different tipes & group functions

139

--echo # different tipes & group functions

135

140

drop table t1,t2,t3;

136

141

137

142

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

138

143

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

139

144

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

140

145

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

141

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

146

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

142

147

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

143

148

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

144

149

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

145

150

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

146

151

147

152

CREATE TABLE `t8` (

148

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

149

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

153

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

154

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

150

155

PRIMARY KEY (`pseudo`),

151

156

UNIQUE KEY `email` (`email`)

152

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

157

);

153

158

154

159

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

155

160

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

156

161

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

162

--replace_column 9 #

157

163

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

158

164

-- error 1241

159

165

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

167

173

168

174

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

169

175

170

#searchconthardwarefr3 forumconthardwarefr7

171

CREATE TABLE `t1` (

172

`topic` bigint unsigned NOT NULL default '0',

173

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

174

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

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 '',

175

181

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

176

182

KEY `topic` (`topic`)

177

183

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

187

193

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

188

194

drop table t1;

189

195

190

#forumconthardwarefr7 searchconthardwarefr7

196

--echo #forumconthardwarefr7 searchconthardwarefr7

191

197

CREATE TABLE `t1` (

192

`numeropost` bigint unsigned NOT NULL auto_increment,

193

`maxnumrep` int unsigned NOT NULL default '0',

198

`numeropost` bigint NOT NULL auto_increment,

199

`maxnumrep` int NOT NULL default '0',

194

200

PRIMARY KEY (`numeropost`),

195

201

UNIQUE KEY `maxnumrep` (`maxnumrep`)

196

) ENGINE=MyISAM ROW_FORMAT=FIXED;

202

);

197

203

198

204

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

199

205

200

206

CREATE TABLE `t2` (

201

207

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

202

`topic` bigint unsigned NOT NULL default '0',

203

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

208

`topic` bigint NOT NULL default '0',

209

`date` date NULL,

204

210

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

205

211

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

206

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

212

);

207

213

208

214

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

209

215

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

230

236

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

231

237

drop table t1,t2;

232

238

233

#forumconthardwarefr7

239

--echo #forumconthardwarefr7

234

240

CREATE TABLE `t1` (

235

`numeropost` bigint unsigned NOT NULL auto_increment,

236

`maxnumrep` int unsigned NOT NULL default '0',

241

`numeropost` bigint NOT NULL auto_increment,

242

`maxnumrep` int NOT NULL default '0',

237

243

PRIMARY KEY (`numeropost`),

238

244

UNIQUE KEY `maxnumrep` (`maxnumrep`)

239

) ENGINE=MyISAM ROW_FORMAT=FIXED;

245

);

240

246

241

247

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

242

248

-- error 1242

250

256

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

251

257

drop table t1;

252

258

253

#iftest

254

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

259

--echo #iftest

260

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

255

261

INSERT INTO t1 VALUES ();

256

262

-- error 1242

257

263

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

258

264

drop table t1;

259

265

260

# threadhardwarefr7

266

--echo # threadhardwarefr7

261

267

CREATE TABLE `t1` (

262

`numeropost` bigint unsigned NOT NULL default '0',

263

`numreponse` int unsigned NOT NULL auto_increment,

268

`numeropost` bigint NOT NULL default '0',

269

`numreponse` int NOT NULL auto_increment,

264

270

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

265

271

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

266

272

UNIQUE KEY `numreponse` (`numreponse`),

267

273

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

268

) ENGINE=MyISAM;

274

);

269

275

-- error 1247

270

276

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

271

277

-- error 1054

274

280

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

275

281

-- error 1242

276

282

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

283

--replace_column 9 #

277

284

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

285

--replace_column 9 #

278

286

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

279

287

drop table t1;

280

288

281

CREATE TABLE t1 (a int);

289

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

282

290

INSERT INTO t1 VALUES (1);

283

291

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

284

292

drop table t1;

285

293

286

#update with subselects

294

--echo #update with subselects

287

295

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

288

296

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

289

297

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

297

305

select * from t1;

298

306

drop table t1, t2;

299

307

300

#delete with subselects

308

--echo #delete with subselects

301

309

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

302

310

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

303

311

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

312

320

select * from t1;

313

321

drop table t1, t2;

314

322

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

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

326

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

327

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

328

select * from t11;

329

select * from t12;

330

drop table t11, t12, t2;

331

332

#insert with subselects

323

--echo #insert with subselects

333

324

CREATE TABLE t1 (x int);

334

325

create table t2 (a int);

335

326

create table t3 (b int);

344

333

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

345

334

select * from t1;

346

335

insert into t2 values (1);

347

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

336

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

348

337

-- sleep 1

349

338

select * from t1;

350

339

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

351

340

select * from t1;

352

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

341

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

353

342

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

354

343

select * from t1;

355

344

-- error 1054

356

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

357

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

345

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

346

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

358

347

-- sleep 1

359

348

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;

349

--echo #

350

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

351

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

352

--echo #select * from t1;

364

353

drop table t1, t2, t3;

365

354

366

#replace with subselects

355

--echo #replace with subselects

367

356

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

368

357

create table t2 (a int);

369

create table t3 (a int);

358

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

370

359

insert into t2 values (1);

371

360

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

372

361

select * from t1;

378

367

select * from t1;

379

368

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

380

369

select * from t1;

381

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

382

select * from t1;

383

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

384

select * from t1;

385

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

370

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

371

-- sleep 1

372

--sorted_result

373

select * from t1;

374

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

375

-- sleep 1

376

select * from t1;

377

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

378

--sorted_result

386

379

select * from t1;

387

380

drop table t1, t2, t3;

388

381

389

382

-- error 1096

390

383

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

391

384

392

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

385

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

393

386

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

394

387

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

395

388

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

406

397

-- error 1093

407

398

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

408

399

SELECT * FROM t2;

409

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

400

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

410

401

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

411

402

-- error 1242

412

403

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

413

404

drop table t2, t1;

414

405

415

#NULL test

416

create table t1 (a int);

406

--echo #NULL test

407

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

417

408

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

418

409

select 1 IN (SELECT * from t1);

419

410

select 10 IN (SELECT * from t1);

427

418

select 1 > ANY (SELECT * from t1);

428

419

select 10 > ANY (SELECT * from t1);

429

420

drop table t1;

430

create table t1 (a varchar(20));

421

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

431

422

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

432

423

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

433

424

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

441

432

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

442

433

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

443

434

drop table t1;

444

create table t1 (a float);

435

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

445

436

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

446

437

select 1.5 IN (SELECT * from t1);

447

438

select 10.5 IN (SELECT * from t1);

455

446

select 1.5 > ANY (SELECT * from t1);

456

447

select 10.5 > ANY (SELECT * from t1);

457

448

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

449

--sorted_result

458

450

select (select a+1) from t1;

459

451

drop table t1;

460

452

461

462

# Null with keys

463

453

--echo #

454

--echo # Null with keys

455

--echo #

464

456

465

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

466

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

457

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

458

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

467

459

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

468

460

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

469

461

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

470

462

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

471

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

463

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

472

464

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

473

465

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

474

466

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

475

467

drop table t1,t2,t3;

476

468

477

#LIMIT is not supported now

478

create table t1 (a float);

479

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

480

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

481

drop table t1;

482

483

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

484

create table t2 (a int);

485

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

486

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

487

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;

488

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;

489

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;

490

drop table t1,t2;

491

492

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

493

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

494

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

495

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

496

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

497

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

498

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

499

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

500

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

501

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

502

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

503

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

504

drop table t1;

505

506

#test of uncacheable subqueries

469

--echo #LIMIT is not supported now

470

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

471

--echo #-- error 1235

472

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

473

--echo #-- error 1235

474

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

475

--echo #drop table t1;

476

--echo #

477

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

478

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

479

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

480

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

481

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

482

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

483

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

484

--echo #drop table t1,t2;

485

--echo #

486

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

487

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

488

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

489

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

490

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

491

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

492

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

493

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

494

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

495

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

496

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

497

--echo #-- error 1235

498

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

499

--echo #drop table t1;

500

501

--echo #test of uncacheable subqueries

507

502

CREATE TABLE t1 (a int);

508

503

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

509

504

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

510

505

drop table t1;

511

506

512

507

513

CREATE TABLE `t1` (

514

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

515

`topic` bigint unsigned NOT NULL default '0',

516

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

517

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

518

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

519

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

520

KEY `topic` (`topic`)

521

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

522

523

CREATE TABLE `t2` (

524

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

525

`topic` bigint unsigned NOT NULL default '0',

526

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

527

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

528

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

529

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

530

KEY `topic` (`topic`)

531

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

532

533

CREATE TABLE `t3` (

534

`numeropost` bigint unsigned NOT NULL auto_increment,

535

`maxnumrep` int unsigned NOT NULL default '0',

508

CREATE TEMPORARY TABLE `t1` (

509

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

510

`topic` bigint NOT NULL default '0',

511

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

512

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

513

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

514

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

515

KEY `topic` (`topic`)

516

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

517

518

CREATE TEMPORARY TABLE `t2` (

519

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

520

`topic` bigint NOT NULL default '0',

521

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

522

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

523

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

524

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

525

KEY `topic` (`topic`)

526

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

527

528

CREATE TEMPORARY TABLE `t3` (

529

`numeropost` bigint NOT NULL auto_increment,

530

`maxnumrep` int NOT NULL default '0',

536

531

PRIMARY KEY (`numeropost`),

537

532

UNIQUE KEY `maxnumrep` (`maxnumrep`)

538

) ENGINE=MyISAM CHARSET=latin1;

533

) ENGINE=MyISAM;

539

534

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

540

535

541

536

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

555

547

drop table t1, t2, t3;

556

548

557

549

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

558

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

559

SHOW CREATE TABLE t1;

560

drop table t1;

561

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

562

SHOW CREATE TABLE t1;

563

drop table t1;

564

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

565

SHOW CREATE TABLE t1;

566

drop table t1;

567

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

550

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

551

SHOW CREATE TABLE t1;

552

drop table t1;

553

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

554

SHOW CREATE TABLE t1;

555

drop table t1;

556

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

557

SHOW CREATE TABLE t1;

558

drop table t1;

559

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

568

560

select * from t1;

569

561

SHOW CREATE TABLE t1;

570

562

drop table t1;

571

563

572

564

create table t1 (a int);

573

565

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

566

--replace_column 9 #

574

567

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

575

568

from t1;

576

569

drop table t1;

577

570

578

579

# error in IN

580

571

--echo #

572

--echo # error in IN

573

--echo #

581

574

-- error 1146

582

575

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

583

576

584

585

# complex subquery

586

577

--echo #

578

--echo # complex subquery

579

--echo #

587

580

588

581

CREATE TABLE t1 (

589

582

ID int NOT NULL auto_increment,

592

585

District char(20) NOT NULL default '',

593

586

Population int NOT NULL default '0',

594

587

PRIMARY KEY (ID)

595

) ENGINE=MyISAM;

588

);

596

589

597

590

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

598

591

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

604

597

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

605

598

Region char(26) NOT NULL default '',

606

599

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

607

IndepYear smallint default NULL,

600

IndepYear int default NULL,

608

601

Population int NOT NULL default '0',

609

602

LifeExpectancy float(3,1) default NULL,

610

603

GNP float(10,2) default NULL,

615

608

Capital int default NULL,

616

609

Code2 char(2) NOT NULL default '',

617

610

PRIMARY KEY (Code)

618

) ENGINE=MyISAM;

611

);

619

612

620

613

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

621

614

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

624

617

625

618

drop table t1, t2;

626

619

627

628

# constants in IN

629

630

CREATE TABLE `t1` (

631

`id` bigint unsigned NOT NULL auto_increment,

632

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

620

--echo #

621

--echo # constants in IN

622

--echo #

623

CREATE TEMPORARY TABLE `t1` (

624

`id` bigint NOT NULL auto_increment,

625

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

633

626

PRIMARY KEY (`id`),

634

627

UNIQUE KEY `pseudo` (`pseudo`)

635

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

628

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

636

629

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

637

630

SELECT 0 IN (SELECT 1 FROM t1 a);

638

631

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

641

634

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

642

635

drop table t1;

643

636

644

CREATE TABLE `t1` (

637

CREATE TEMPORARY TABLE `t1` (

645

638

`i` int NOT NULL default '0',

646

639

PRIMARY KEY (`i`)

647

) ENGINE=MyISAM CHARSET=latin1;

640

) ENGINE=MyISAM;

648

641

649

642

INSERT INTO t1 VALUES (1);

650

643

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

654

647

select * from t1;

655

648

drop table t1;

656

649

657

658

# Multi update test

659

660

CREATE TABLE t1 (

661

id int default NULL

662

) ENGINE=MyISAM CHARSET=latin1;

663

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

664

CREATE TABLE t2 (

665

id int default NULL,

666

name varchar(15) default NULL

667

) ENGINE=MyISAM CHARSET=latin1;

668

669

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

670

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

671

select * from t2;

672

drop table t1,t2;

673

674

675

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

676

677

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

650

--echo #

651

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

652

--echo #

653

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

678

654

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

679

655

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

680

656

drop table t1;

681

657

682

683

# Test optimization for sub selects

684

658

--echo #

659

--echo # Test optimization for sub selects

660

--echo #

685

661

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

686

662

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

663

--replace_column 9 #

687

664

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

688

665

drop table t1;

689

666

690

667

CREATE TABLE t1 (

691

ID int unsigned NOT NULL auto_increment,

692

SUB_ID int unsigned NOT NULL default '0',

693

REF_ID int unsigned default NULL,

694

REF_SUB int unsigned default '0',

668

ID int NOT NULL auto_increment,

669

SUB_ID int NOT NULL default '0',

670

REF_ID int default NULL,

671

REF_SUB int default '0',

695

672

PRIMARY KEY (ID,SUB_ID),

696

673

UNIQUE KEY t1_PK (ID,SUB_ID),

697

674

KEY t1_FK (REF_ID,REF_SUB),

698

675

KEY t1_REFID (REF_ID)

699

) ENGINE=MyISAM CHARSET=cp1251;

676

);

700

677

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

701

678

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

702

679

DROP TABLE t1;

703

680

704

705

# uninterruptable update

706

707

create table t1 (a int, b int);

708

create table t2 (a int, b int);

681

--echo #

682

--echo # uninterruptable update

683

--echo #

684

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

685

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

709

686

710

687

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

711

688

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

715

692

716

693

drop table t1, t2;

717

694

718

719

# reduced subselect in ORDER BY & GROUP BY clauses

720

695

--echo #

696

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

697

--echo #

721

698

722

CREATE TABLE `t1` (

723

`id` bigint unsigned NOT NULL auto_increment,

699

CREATE TEMPORARY TABLE `t1` (

700

`id` bigint NOT NULL auto_increment,

724

701

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

725

702

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

726

703

PRIMARY KEY (`id`),

727

704

UNIQUE KEY `email` (`email`),

728

705

UNIQUE KEY `pseudo` (`pseudo`)

729

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

706

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

730

707

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

731

708

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

732

709

drop table if exists t1;

733

710

734

711

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

735

712

736

737

# IN subselect optimization test

738

739

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

740

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

741

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

713

--echo #

714

--echo # IN subselect optimization test

715

--echo #

716

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

717

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

718

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

742

719

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

743

720

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

744

721

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

749

726

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

750

727

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

751

728

drop table t1, t2, t3;

752

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

753

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

754

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

729

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

730

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

731

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

755

732

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

756

733

disable_query_log;

757

# making table large enough

734

--echo # making table large enough

758

735

set autocommit=0;

759

736

begin;

760

737

let $1 = 10000;

780

757

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

781

758

drop table t1, t2, t3;

782

759

783

784

# alloc_group_fields() working

785

786

create table t1 (a int, b int);

787

create table t2 (a int, b int);

788

create table t3 (a int, b int);

760

--echo #

761

--echo # alloc_group_fields() working

762

--echo #

763

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

764

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

765

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

789

766

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

790

767

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

791

768

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

792

769

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;

793

770

drop table t1,t2,t3;

794

771

795

796

# aggregate functions in HAVING test

797

798

create table t1 (s1 int);

799

create table t2 (s1 int);

772

--echo #

773

--echo # aggregate functions in HAVING test

774

--echo #

775

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

776

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

800

777

insert into t1 values (1);

801

778

insert into t2 values (1);

802

779

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

803

780

drop table t1,t2;

804

781

805

806

# update subquery with wrong field (to force name resolving

807

# in UPDATE name space)

808

809

create table t1 (s1 int);

810

create table t2 (s1 int);

782

--echo #

783

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

784

--echo # in UPDATE name space)

785

--echo #

786

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

787

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

811

788

insert into t1 values (1);

812

789

insert into t2 values (1);

813

790

-- error 1054

814

791

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

815

792

DROP TABLE t1, t2;

816

793

817

818

# collation test

819

820

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

821

s2 CHAR(5) COLLATE latin1_swedish_ci);

822

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

823

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

824

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

825

drop table t1;

794

--echo #

795

--echo # collation test

796

--echo #

797

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

798

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

799

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

800

--echo #-- error 1267

801

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

802

--echo #-- error 1267

803

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

804

--echo #drop table t1;

826

805

827

828

# aggregate functions reinitialization

829

806

--echo #

807

--echo # aggregate functions reinitialization

808

--echo #

830

809

create table t1(toid int,rd int);

831

810

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

832

811

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

836

813

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

837

814

drop table t1, t2;

838

815

839

840

# row union

841

816

--echo #

817

--echo # row union

818

--echo #

842

819

create table t1 (s1 char(5));

843

820

-- error 1241

844

821

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

845

822

insert into t1 values ('tttt');

846

823

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

824

--replace_column 9 #

847

825

explain extended (select * from t1);

848

826

(select * from t1);

849

827

drop table t1;

850

828

851

852

# IN optimisation test results

853

854

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

855

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

829

--echo #

830

--echo # IN optimisation test results

831

--echo #

832

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

833

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

856

834

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

857

835

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

858

836

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

865

843

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

866

844

drop table t1,t2;

867

845

868

869

# correct ALL optimisation

870

871

create table t2 (a int, b int);

872

create table t3 (a int);

846

--echo #

847

--echo # correct ALL optimisation

848

--echo #

849

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

850

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

873

851

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

874

852

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

875

853

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

887

865

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

888

866

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

889

867

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

890

891

# optimized static ALL/ANY with grouping

892

868

--echo #

869

--echo # optimized static ALL/ANY with grouping

870

--echo #

893

871

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

894

872

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

895

873

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

896

874

drop table t2, t3;

897

875

898

899

# correct used_tables()

900

876

--echo #

877

--echo # correct used_tables()

878

--echo #

901

879

902

CREATE TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int 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 ;

880

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 ;

903

881

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

904

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

882

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;

905

883

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

906

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

884

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 ;

907

885

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

908

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

886

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

909

887

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

910

888

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;

911

889

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;

912

890

drop table t1,t2,t3,t4;

913

891

914

915

# cardinality check

916

917

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

892

--echo #

893

--echo # cardinality check

894

--echo #

895

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

918

896

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

919

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

897

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

920

898

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

921

899

-- error 1241

922

900

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

923

901

DROP TABLE t1,t2;

924

902

925

926

# optimized ALL/ANY with union

927

903

--echo #

904

--echo # optimized ALL/ANY with union

905

--echo #

928

906

create table t1 (s1 char);

929

907

insert into t1 values ('e');

930

908

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

931

909

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

910

--replace_column 9 #

932

911

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

933

912

drop table t1;

934

913

935

936

# filesort in subquery (restoring join_tab)

937

938

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

914

--echo #

915

--echo # filesort in subquery (restoring join_tab)

916

--echo #

917

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

939

918

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

940

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

919

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

941

920

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

942

921

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;

943

922

drop table t1, t2;

944

923

945

946

# unresolved field error

947

948

create table t1 (s1 int);

949

create table t2 (s1 int);

924

--echo #

925

--echo # unresolved field error

926

--echo #

927

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

928

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

950

929

-- error 1054

951

930

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

952

931

-- error 1054

955

934

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

956

935

drop table t1, t2;

957

936

958

959

# fix_fields() in add_ref_to_table_cond()

960

961

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

962

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

937

--echo #

938

--echo # fix_fields() in add_ref_to_table_cond()

939

--echo #

940

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

941

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

963

942

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

964

943

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

965

944

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

966

945

DROP TABLE t1, t2;

967

946

968

CREATE TABLE t1 (a int);

947

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

969

948

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

970

949

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

971

950

DROP TABLE t1;

972

951

973

974

# Bug 2479

975

952

--echo #

953

--echo # Bug 2479

954

--echo #

976

955

977

CREATE TABLE `t1` (

956

CREATE TEMPORARY TABLE `t1` (

978

957

`id` int NOT NULL auto_increment,

979

`id_cns` tinyint unsigned NOT NULL default '0',

958

`id_cns` int NOT NULL default '0',

980

959

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

981

`anno_dep` smallint unsigned NOT NULL default '0',

982

`particolare` bigint unsigned NOT NULL default '0',

983

`generale` bigint unsigned NOT NULL default '0',

984

`bis` tinyint unsigned NOT NULL default '0',

960

`anno_dep` int NOT NULL default '0',

961

`particolare` bigint NOT NULL default '0',

962

`generale` bigint NOT NULL default '0',

963

`bis` int NOT NULL default '0',

985

964

PRIMARY KEY (`id`),

986

965

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

987

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

988

);

966

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

967

ENGINE=MyISAM;

989

968

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

990

CREATE TABLE `t2` (

991

`id` tinyint unsigned NOT NULL auto_increment,

992

`max_anno_dep` smallint unsigned NOT NULL default '0',

993

PRIMARY KEY (`id`)

994

);

969

CREATE TEMPORARY TABLE `t2` (

970

`id` int NOT NULL auto_increment,

971

`max_anno_dep` int NOT NULL default '0',

972

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

995

973

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

996

974

997

975

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;

998

976

999

977

DROP TABLE t1, t2;

1000

978

1001

1002

# GLOBAL LIMIT

1003

1004

create table t1 (a int);

979

--echo #

980

--echo # GLOBAL LIMIT

981

--echo #

982

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

1005

983

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

1006

984

SET SQL_SELECT_LIMIT=1;

1007

985

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

1009

987

SET SQL_SELECT_LIMIT=default;

1010

988

drop table t1;

1011

989

1012

1013

# Bug #3118: subselect + order by

1014

990

--echo #

991

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

992

--echo #

1015

993

1016

994

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

1017

995

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

1018

996

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

1019

997

DROP TABLE t1;

1020

998

1021

# Item_cond fix field

1022

999

--echo # Item_cond fix field

1000

--echo #

1023

1001

create table t1(val varchar(10));

1024

1002

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

1025

1003

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

1026

1004

drop table t1;

1027

1005

1028

1029

# ref_or_null replacing with ref

1030

1006

--echo #

1007

--echo # ref_or_null replacing with ref

1008

--echo #

1031

1009

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

1032

1010

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

1033

1011

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

1034

1012

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

--replace_column 9 #

1035

1014

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

1015

--replace_column 9 #

1036

1016

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

1037

1017

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

1038

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

1018

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

1039

1019

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

1040

1020

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

1021

--replace_column 9 #

1041

1022

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

1042

1023

drop table t1,t2;

1043

1024

1044

1045

# Static tables & rund() in subqueries

1046

1047

create table t1 (a int);

1025

--echo #

1026

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

1027

--echo #

1028

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

1048

1029

insert into t1 values (1);

1049

1030

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

1050

1031

drop table t1;

1051

1032

1052

1053

# bug 3188

1054

1055

create table t1(id int);

1056

create table t2(id int);

1057

create table t3(flag int);

1033

--echo #

1034

--echo # bug 3188

1035

--echo #

1036

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

1037

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

1038

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

1058

1039

-- error 1064

1059

1040

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

1060

1041

drop table t1,t2,t3;

1061

1042

1062

1063

# aggregate functions (Bug #3505)

1064

1043

--echo #

1044

--echo # aggregate functions (Bug #3505)

1045

--echo #

1065

1046

CREATE TABLE t1 (id INT);

1066

1047

CREATE TABLE t2 (id INT);

1067

1048

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

1072

1053

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;

1073

1054

DROP TABLE t1,t2;

1074

1055

1075

1076

# ALL/ANY test

1077

1056

--echo #

1057

--echo # ALL/ANY test

1058

--echo #

1078

1059

CREATE TABLE t1 ( a int, b int );

1079

1060

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

1080

1061

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

1089

1070

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

1090

1071

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

1091

1072

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

1092

# with index

1073

--echo # with index

1093

1074

ALTER TABLE t1 ADD INDEX (a);

1094

1075

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

1095

1076

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

1103

1084

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

1104

1085

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

1105

1086

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

1106

# having clause test

1087

--echo # having clause test

1107

1088

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

1108

1089

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

1109

1090

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

1116

1097

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

1117

1098

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

1118

1099

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

1119

# union test

1100

--echo # union test

1120

1101

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

1121

1102

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

1122

1103

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

1129

1110

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

1130

1111

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

1131

1112

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

1132

# union + having test

1113

--echo # union + having test

1133

1114

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

1134

1115

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

1135

1116

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

1142

1123

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

1143

1124

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

1144

1125

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

1145

# row tests

1146

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

1126

--echo # row tests

1127

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

1147

1128

-- error 1241

1148

1129

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

1149

1130

-- error 1241

1160

1141

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

1161

1142

-- error 1241

1162

1143

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

1163

# following should be converted to IN

1144

--echo # following should be converted to IN

1164

1145

-- error 1241

1165

1146

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

1166

1147

-- error 1241

1179

1160

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

1180

1161

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

1181

1162

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

1182

# without optimisation

1163

--echo # without optimisation

1183

1164

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

1184

1165

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

1185

1166

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

1192

1173

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

1193

1174

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

1194

1175

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

1195

# without optimisation + having

1176

--echo # without optimisation + having

1196

1177

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

1197

1178

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

1198

1179

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

1205

1186

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

1206

1187

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

1207

1188

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

1208

# EXISTS in string contence

1189

--echo # EXISTS in string contence

1209

1190

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

1210

1191

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

1211

1192

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

1242

1223

DROP TABLE t1;

1243

1224

1244

1225

1245

1246

# SELECT(EXISTS * ...)optimisation

1247

1226

--echo #

1227

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

1228

--echo #

1248

1229

create table t1 (a int, b int);

1249

1230

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

1250

1231

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

1232

--replace_column 9 #

1251

1233

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

1252

1234

drop table t1;

1253

1235

1254

1255

# Bug #4102: subselect in HAVING

1256

1236

--echo #

1237

--echo # Bug #4102: subselect in HAVING

1238

--echo #

1257

1239

1258

CREATE TABLE t1 (t1_a int);

1240

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

1259

1241

INSERT INTO t1 VALUES (1);

1260

1242

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

1261

1243

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

1263

1245

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

1264

1246

DROP TABLE t1, t2;

1265

1247

1266

1267

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

1268

1248

--echo #

1249

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

1250

--echo #

1269

1251

1270

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

1252

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

1271

1253

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

1272

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

1254

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

1273

1255

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

1274

1256

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

1275

1257

drop table t1,t2;

1276

1258

1277

1278

# Aggregate function comparation with ALL/ANY/SOME subselect

1279

1280

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

1259

--echo #

1260

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

1261

--echo #

1262

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

1281

1263

insert into t1 values (1);

1282

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

1264

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

1283

1265

insert into t2 values (1,2);

1284

1266

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

1285

1267

drop table t1,t2;

1286

1268

1287

1288

# BUG#5003 - like in subselect

1289

1290

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

--echo #

1270

--echo # BUG#5003 - like in subselect

1271

--echo #

1272

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

1291

1273

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

1292

1274

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

1293

1275

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

1296

1278

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

1297

1279

drop table t1;

1298

1280

1299

1300

# Optimized IN with compound index

1301

1302

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

1303

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

1281

--echo #

1282

--echo # Optimized IN with compound index

1283

--echo #

1284

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

1285

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

1304

1286

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

1305

1287

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

1306

1288

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

1312

1294

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

1313

1295

drop table t1,t2;

1314

1296

1315

1316

# resolving fields of grouped outer SELECT

1317

1297

--echo #

1298

--echo # resolving fields of grouped outer SELECT

1299

--echo #

1318

1300

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

1319

1301

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

1320

1302

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

1329

1311

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;

1330

1312

drop table t1;

1331

1313

1332

1333

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

1334

# creation

1335

1336

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

1314

--echo #

1315

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

1316

--echo # creation

1317

--echo #

1318

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;

1337

1319

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

1338

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

1320

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 ;

1339

1321

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

1340

1322

-- error 1054

1341

1323

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;

1342

1324

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;

1343

1325

drop tables t1,t2;

1344

1326

1345

1346

# Subselect in non-select command just after connection

1347

1327

--echo #

1328

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

1329

--echo #

1348

1330

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

1349

1331

connection root;

1350

1332

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

1351

1333

1352

1353

# primary query with temporary table and subquery with groupping

1354

1355

create table t1 (a int, b int);

1356

create table t2 (a int, b int);

1334

--echo #

1335

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

1336

--echo #

1337

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

1338

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

1357

1339

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

1358

1340

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

1359

1341

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

1360

1342

drop table t1, t2;

1361

1343

1362

1363

# Equal operation under row and empty subquery

1364

1344

--echo #

1345

--echo # Equal operation under row and empty subquery

1346

--echo #

1365

1347

create table t1 (s1 int,s2 int);

1366

1348

insert into t1 values (20,15);

1367

1349

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

1368

1350

drop table t1;

1369

1351

1370

1371

# ALL/ANY with NULL

1372

1352

--echo #

1353

--echo # ALL/ANY with NULL

1354

--echo #

1373

1355

create table t1 (s1 int);

1374

1356

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

1375

1357

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

1376

1358

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

1377

1359

drop table t1;

1378

1360

1379

1380

# reference on changable fields from subquery

1381

1361

--echo #

1362

--echo # reference on changable fields from subquery

1363

--echo #

1382

1364

CREATE TABLE t1 (

1383

1365

Code char(3) NOT NULL default '',

1384

1366

Name char(52) NOT NULL default '',

1385

1367

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

1386

1368

Region char(26) NOT NULL default '',

1387

1369

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

1388

IndepYear smallint default NULL,

1370

IndepYear int default NULL,

1389

1371

Population int NOT NULL default '0',

1390

1372

LifeExpectancy float(3,1) default NULL,

1391

1373

GNP float(10,2) default NULL,

1395

1377

HeadOfState char(60) default NULL,

1396

1378

Capital int default NULL,

1397

1379

Code2 char(2) NOT NULL default ''

1398

) ENGINE=MyISAM;

1380

);

1399

1381

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

1400

1382

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

1401

1383

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

1406

1388

Population < 200);

1407

1389

drop table t1;

1408

1390

1409

1410

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

1411

# non-existant field.

1412

1413

create table t1 (a1 int);

1414

create table t2 (b1 int);

1391

--echo #

1392

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

1393

--echo # non-existant field.

1394

--echo #

1395

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

1396

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

1415

1397

--error 1054

1416

1398

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

1417

1399

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

1418

1400

drop table t1,t2;

1419

1401

1420

1402

1421

1422

# Comparison subquery with * and row

1423

1424

create table t1 (a integer, b integer);

1403

--echo #

1404

--echo # Comparison subquery with * and row

1405

--echo #

1406

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

1425

1407

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

1426

1408

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

1427

# queries whih can be converted to IN

1409

--echo # queries whih can be converted to IN

1428

1410

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

1429

1411

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

1430

1412

drop table t1;

1431

1413

1432

1433

# Comparison subquery and row with nested rows

1434

1435

create table t1 (a integer, b integer);

1414

--echo #

1415

--echo # Comparison subquery and row with nested rows

1416

--echo #

1417

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

1436

1418

-- error 1241

1437

1419

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

1438

1420

-- error 1241

1441

1423

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

1442

1424

drop table t1;

1443

1425

1444

1445

# Forward reference detection

1446

1426

--echo #

1427

--echo # Forward reference detection

1428

--echo #

1447

1429

create table t1 (a integer);

1448

1430

insert into t1 values (1);

1449

1431

-- error 1247

1455

1437

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

1456

1438

drop table t1;

1457

1439

1458

1459

# Test for BUG#8218

1460

1461

CREATE TABLE t1 (

1440

--echo #

1441

--echo # Test for BUG#8218

1442

--echo #

1443

CREATE TEMPORARY TABLE t1 (

1462

1444

categoryId int NOT NULL,

1463

1445

courseId int NOT NULL,

1464

1446

startDate datetime NOT NULL,

1465

1447

endDate datetime NOT NULL,

1466

1448

createDate datetime NOT NULL,

1467

1449

modifyDate timestamp NOT NULL,

1468

attributes text NOT NULL

1469

);

1450

attributes text NOT NULL)

1451

engine=myisam;

1452

1470

1453

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

1471

1454

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

1472

1455

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

1477

1460

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

1478

1461

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

1479

1462

1480

CREATE TABLE t2 (

1463

CREATE TEMPORARY TABLE t2 (

1481

1464

userId int NOT NULL,

1482

1465

courseId int NOT NULL,

1483

date datetime NOT NULL

1484

);

1466

date datetime NOT NULL)

1467

engine=myisam;

1468

1485

1469

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

1486

1470

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

1487

1471

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

1490

1474

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

1491

1475

1492

1476

1493

CREATE TABLE t3 (

1477

CREATE TEMPORARY TABLE t3 (

1494

1478

groupId int NOT NULL,

1495

1479

parentId int NOT NULL,

1496

1480

startDate datetime NOT NULL,

1497

1481

endDate datetime NOT NULL,

1498

1482

createDate datetime NOT NULL,

1499

1483

modifyDate timestamp NOT NULL,

1500

ordering int

1501

);

1484

ordering int)

1485

engine=myisam;

1486

1502

1487

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

1503

1488

1504

CREATE TABLE t4 (

1489

CREATE TEMPORARY TABLE t4 (

1505

1490

id int NOT NULL,

1506

1491

groupTypeId int NOT NULL,

1507

1492

groupKey varchar(50) NOT NULL,

1509

1494

ordering int,

1510

1495

description text,

1511

1496

createDate datetime NOT NULL,

1512

modifyDate timestamp NOT NULL

1513

);

1497

modifyDate timestamp NOT NULL)

1498

engine=myisam;

1499

1514

1500

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

1515

1501

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

1516

1502

1517

CREATE TABLE t5 (

1503

CREATE TEMPORARY TABLE t5 (

1518

1504

userId int NOT NULL,

1519

1505

groupId int NOT NULL,

1520

1506

createDate datetime NOT NULL,

1521

modifyDate timestamp NOT NULL

1522

);

1507

modifyDate timestamp NOT NULL) ENGINE=MyISAM;

1508

1523

1509

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

1524

1510

1525

1511

select

1556

1542

1557

1543

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

1558

1544

1559

1560

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

1561

1562

create table t1 (a int);

1545

--echo #

1546

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

1547

--echo #

1548

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

1563

1549

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

1564

1550

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

1565

1551

drop table t1;

1566

1552

1567

1568

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

1569

1570

create table t1 (a int, b int);

1553

--echo #

1554

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

1555

--echo #

1556

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

1571

1557

insert into t1 values (1,2);

1572

1558

-- error 1241

1573

1559

select 1 = (select * from t1);

1583

1569

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

1584

1570

drop table t1;

1585

1571

1586

1587

# Item_int_with_ref check (BUG#10020)

1588

1589

CREATE TABLE `t1` (

1590

`itemid` bigint unsigned NOT NULL auto_increment,

1591

`sessionid` bigint unsigned default NULL,

1592

`time` int unsigned NOT NULL default '0',

1593

`data` text collate latin1_general_ci NOT NULL,

1594

PRIMARY KEY (`itemid`)

1595

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1596

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

1597

CREATE TABLE `t2` (

1598

`sessionid` bigint unsigned NOT NULL auto_increment,

1599

`pid` int unsigned NOT NULL default '0',

1600

`date` int unsigned NOT NULL default '0',

1601

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

1602

PRIMARY KEY (`sessionid`)

1603

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1604

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

1605

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;

1606

drop tables t1,t2;

1572

--echo #

1573

--echo # Item_int_with_ref check (BUG#10020)

1574

--echo #

1575

--echo #CREATE TABLE `t1` (

1576

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

1577

--echo # `sessionid` bigint default NULL,

1578

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

1579

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

1580

--echo # PRIMARY KEY (`itemid`)

1581

--echo #);

1582

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

1583

--echo #CREATE TABLE `t2` (

1584

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

1585

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

1586

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

1587

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

1588

--echo # PRIMARY KEY (`sessionid`)

1589

--echo #);

1590

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

1591

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

1592

--echo #drop tables t1,t2;

1607

1593

1608

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

1609

# segfaults:

1610

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

1594

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

1595

--echo # segfaults:

1596

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

1611

1597

insert into t1 values ('1');

1612

1598

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

1613

1599

drop table t1;

1614

1600

1615

1616

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

1617

1601

--echo #

1602

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

1603

--echo #

1618

1604

1619

1605

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

1620

1606

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

1643

1629

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;

1644

1630

DROP TABLE t1,t2;

1645

1631

1646

1647

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

1648

1632

--echo #

1633

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

1634

--echo #

1649

1635

1650

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

1636

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

1651

1637

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

1652

1638

1653

1639

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

1654

1640

1655

1641

DROP TABLE t1;

1656

1642

1657

1658

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

1659

1643

--echo #

1644

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

1645

--echo #

1660

1646

1661

CREATE TABLE t1 (a int);

1662

CREATE TABLE t2 (a int, b int);

1663

CREATE TABLE t3 (b int NOT NULL);

1647

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

1648

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

1649

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

1664

1650

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

1665

1651

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

1666

1652

1672

1658

1673

1659

DROP TABLE t1,t2,t3;

1674

1660

1675

1676

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

1677

# return a wrong result.

1678

1679

CREATE TABLE t1 (f1 INT);

1680

CREATE TABLE t2 (f2 INT);

1661

--echo #

1662

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

1663

--echo # return a wrong result.

1664

--echo #

1665

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

1666

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

1681

1667

INSERT INTO t1 VALUES (1);

1682

1668

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

1683

1669

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

1686

1672

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

1687

1673

DROP TABLE t1, t2;

1688

1674

1689

# BUG#20975 Wrong query results for subqueries within NOT

1675

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

1690

1676

create table t1 (s1 char);

1691

1677

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

1692

1678

1703

1689

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

1704

1690

drop table t1;

1705

1691

1706

1707

# Bug #16255: Subquery in where

1708

1692

--echo #

1693

--echo # Bug #16255: Subquery in where

1694

--echo #

1709

1695

create table t1 (

1710

1696

retailerID varchar(8) NOT NULL,

1711

statusID int unsigned NOT NULL,

1697

statusID int NOT NULL,

1712

1698

changed datetime NOT NULL,

1713

UNIQUE KEY retailerID (retailerID, statusID, changed)

1714

);

1699

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

1715

1700

1716

1701

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

1717

1702

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

1726

1711

group by r2.retailerId);

1727

1712

drop table t1;

1728

1713

1729

1730

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

1731

# produces empty result

1732

1714

--echo #

1715

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

1716

--echo # produces empty result

1717

--echo #

1733

1718

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

1734

1719

insert into t1 values (10);

1735

1720

1736

1721

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

1737

1722

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

1738

1723

1724

--replace_column 9 #

1739

1725

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

1740

1726

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

1741

1727

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

1752

1738

1753

1739

drop table t1,t2;

1754

1740

1755

1756

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

1757

# an ALL/ANY quantified subquery in HAVING

1758

1741

--echo #

1742

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

1743

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

1744

--echo #

1759

1745

1760

CREATE TABLE t1 (

1746

CREATE TEMPORARY TABLE t1 (

1761

1747

field1 int NOT NULL,

1762

1748

field2 int NOT NULL,

1763

1749

field3 int NOT NULL,

1764

PRIMARY KEY (field1,field2,field3)

1765

);

1766

CREATE TABLE t2 (

1750

PRIMARY KEY (field1,field2,field3))

1751

ENGINE=MyISAM;

1752

1753

CREATE TEMPORARY TABLE t2 (

1767

1754

fieldA int NOT NULL,

1768

1755

fieldB int NOT NULL,

1769

PRIMARY KEY (fieldA,fieldB)

1770

);

1756

PRIMARY KEY (fieldA,fieldB))

1757

ENGINE=MyISAM;

1771

1758

1772

1759

INSERT INTO t1 VALUES

1773

1760

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

1789

1776

1790

1777

DROP TABLE t1, t2;

1791

1778

1792

1793

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

1794

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

1795

1779

--echo #

1780

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

1781

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

1782

--echo #

1796

1783

1797

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

1784

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

1798

1785

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

1799

1786

INSERT INTO t1 VALUES (NULL);

1800

1787

1801

CREATE TABLE t2(a int);

1788

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

1802

1789

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

1803

1790

1804

1791

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

1806

1793

1807

1794

DROP TABLE t1,t2;

1808

1795

1809

1810

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

1811

1796

--echo #

1797

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

1798

--echo #

1812

1799

CREATE TABLE t1 (a DATETIME);

1813

1800

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

1814

1801

1815

CREATE TABLE t2 AS SELECT

1802

CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT

1816

1803

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

1817

1804

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

1818

1805

SHOW CREATE TABLE t2;

1819

1806

1820

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

1807

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

1821

1808

SHOW CREATE TABLE t3;

1822

1809

1823

1810

DROP TABLE t1,t2,t3;

1824

1811

1825

1826

# Bug 24653: sorting by expressions containing subselects

1827

# that return more than one row

1828

1812

--echo #

1813

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

1814

--echo # that return more than one row

1815

--echo #

1829

1816

1830

CREATE TABLE t1 (a int);

1817

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

1831

1818

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

1832

1819

1833

1820

CREATE TABLE t2 (b int, c int);

1880

1867

1881

1868

DROP TABLE t1,t2;

1882

1869

1883

# End of 4.1 tests

1870

--echo # End of 4.1 tests

1884

1871

1885

1886

#decimal-related tests

1887

1872

--echo #

1873

--echo #decimal-related tests

1874

--echo #

1888

1875

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

1889

1876

insert into t1 values(1.1);

1890

1877

insert into t1 values(2.2);

1893

1880

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

1894

1881

drop table t1;

1895

1882

1896

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

1883

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

1897

1884

insert into t1 values(1.1);

1898

1885

select 1.1 * exists(select * from t1);

1899

1886

drop table t1;

1900

1887

1901

CREATE TABLE t1 (

1888

CREATE TEMPORARY TABLE t1 (

1902

1889

grp int default NULL,

1903

a decimal(10,2) default NULL);

1890

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

1904

1891

1905

1892

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

1906

1893

select * from t1;

1907

1894

select min(a) from t1 group by grp;

1908

1895

drop table t1;

1909

1896

1910

1911

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

1912

1897

--echo #

1898

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

1899

--echo #

1913

1900

1914

CREATE table t1 ( c1 integer );

1901

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

1915

1902

INSERT INTO t1 VALUES ( 1 );

1916

1903

INSERT INTO t1 VALUES ( 2 );

1917

1904

INSERT INTO t1 VALUES ( 3 );

1928

1915

1929

1916

DROP TABLE t1,t2;

1930

1917

1931

1932

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

1933

1934

CREATE TABLE t1 ( c1 integer );

1918

--echo #

1919

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

1920

--echo #

1921

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

1935

1922

INSERT INTO t1 VALUES ( 1 );

1936

1923

INSERT INTO t1 VALUES ( 2 );

1937

1924

INSERT INTO t1 VALUES ( 3 );

1938

1925

INSERT INTO t1 VALUES ( 6 );

1939

1926

1940

CREATE TABLE t2 ( c2 integer );

1927

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

1941

1928

INSERT INTO t2 VALUES ( 1 );

1942

1929

INSERT INTO t2 VALUES ( 4 );

1943

1930

INSERT INTO t2 VALUES ( 5 );

1944

1931

INSERT INTO t2 VALUES ( 6 );

1945

1932

1946

CREATE TABLE t3 ( c3 integer );

1933

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

1947

1934

INSERT INTO t3 VALUES ( 7 );

1948

1935

INSERT INTO t3 VALUES ( 8 );

1949

1936

1952

1939

1953

1940

DROP TABLE t1,t2,t3;

1954

1941

1955

1956

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

1957

# fields) (BUG#6384)

1958

1959

CREATE TABLE t1 (EMPNUM CHAR(3));

1960

CREATE TABLE t2 (EMPNUM CHAR(3) );

1942

--echo #

1943

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

1944

--echo # fields) (BUG#6384)

1945

--echo #

1946

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

1947

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

1961

1948

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

1962

1949

INSERT INTO t2 VALUES ('E1');

1963

1950

DELETE FROM t1

1968

1955

select * from t1;

1969

1956

DROP TABLE t1,t2;

1970

1957

1971

1972

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

1973

1958

--echo #

1959

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

1960

--echo #

1974

1961

1975

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);

1962

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

1976

1963

INSERT INTO t1 VALUES (1, 1);

1977

CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,

1978

PRIMARY KEY(select_id,values_id));

1964

CREATE TEMPORARY TABLE t2 (select_id BIGINT, values_id BIGINT,

1965

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

1979

1966

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

1980

1967

1981

1968

SELECT values_id FROM t1

1990

1977

1991

1978

DROP TABLE t1, t2;

1992

1979

1993

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

1994

# segfaults:

1995

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

1980

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

1981

--echo # segfaults:

1982

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

1996

1983

insert into t1 values ('1');

1997

1984

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

1998

1985

drop table t1;

1999

1986

2000

2001

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

2002

1987

--echo #

1988

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

1989

--echo #

2003

1990

2004

CREATE TABLE t1 (a int, b int);

2005

CREATE TABLE t2 (c int, d int);

2006

CREATE TABLE t3 (e int);

1991

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

1992

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

1993

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

2007

1994

2008

1995

INSERT INTO t1 VALUES

2009

1996

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

2081

2068

2082

2069

DROP TABLE t1,t2,t3;

2083

2070

2084

2085

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

2086

# when an index is defined on the grouping field

2071

--echo #

2072

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

2073

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

2087

2074

2088

2075

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

2089

2076

INSERT INTO t1 VALUES

2091

2078

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

2092

2079

2093

2080

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

2081

--replace_column 9 #

2094

2082

EXPLAIN

2095

2083

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

2096

2084

2097

2085

ALTER TABLE t1 ADD INDEX(a);

2098

2086

2099

2087

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

2088

--replace_column 9 #

2100

2089

EXPLAIN

2101

2090

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

2102

2091

2103

2092

DROP TABLE t1;

2104

2093

2105

2106

# Bug#17366: Unchecked Item_int results in server crash

2107

2094

--echo #

2095

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

2096

--echo #

2108

2097

create table t1( f1 int,f2 int);

2109

2098

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

2110

2099

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

2111

2100

drop table t1;

2112

2101

2113

2114

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

2115

2102

--echo #

2103

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

2104

--echo #

2116

2105

2117

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

2106

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

2118

2107

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

2119

create table t2 (a int, b int);

2108

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

2120

2109

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

2121

2110

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

2122

2111

drop table t1, t2;

2123

2112

2124

2125

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

2126

2113

--echo #

2114

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

2115

--echo #

2127

2116

create table t1 (i int, j bigint);

2128

2117

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

2129

2118

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

2130

2119

drop table t1;

2131

2120

2132

2133

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

2134

2135

CREATE TABLE t1 (i BIGINT UNSIGNED);

2136

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

2121

--echo #

2122

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

2123

--echo #

2124

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

2125

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

2137

2126

INSERT INTO t1 VALUES (1);

2138

2127

2139

CREATE TABLE t2 (i BIGINT UNSIGNED);

2140

INSERT INTO t2 VALUES (10000000000000000000); # same as first table

2128

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

2129

INSERT INTO t2 VALUES (10000000000000000); # same as first table

2141

2130

INSERT INTO t2 VALUES (1);

2142

2131

2143

2132

/* simple test */

2147

2136

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

2148

2137

2149

2138

/* subquery test with cast*/

2150

SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);

2139

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

2151

2140

2152

2141

DROP TABLE t1;

2153

2142

DROP TABLE t2;

2154

2143

2155

2156

# Bug#20519: subselect with LIMIT M, N

2157

2144

--echo #

2145

--echo # Bug#20519: subselect with LIMIT M, N

2146

--echo #

2158

2147

2159

CREATE TABLE t1 (

2160

id bigint unsigned NOT NULL auto_increment,

2148

CREATE TEMPORARY TABLE t1 (

2149

id bigint NOT NULL auto_increment,

2161

2150

name varchar(255) NOT NULL,

2162

PRIMARY KEY (id)

2163

);

2151

PRIMARY KEY (id))

2152

ENGINE=MyISAM;

2153

2164

2154

INSERT INTO t1 VALUES

2165

2155

(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

2166

2156

2167

2157

CREATE TABLE t2 (

2168

id bigint unsigned NOT NULL auto_increment,

2169

mid bigint unsigned NOT NULL,

2158

id bigint NOT NULL auto_increment,

2159

mid bigint NOT NULL,

2170

2160

date date NOT NULL,

2171

PRIMARY KEY (id)

2172

);

2161

PRIMARY KEY (id));

2162

2173

2163

INSERT INTO t2 VALUES

2174

2164

(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),

2175

2165

(4, 2, '2006-04-20'), (5, 1, '2006-05-01');

2192

2182

FROM t1;

2193

2183

DROP TABLE t1,t2;

2194

2184

2195

2196

# Bug#20869: subselect with range access by DESC

2197

2185

--echo #

2186

--echo # Bug#20869: subselect with range access by DESC

2187

--echo #

2198

2188

2199

2189

CREATE TABLE t1 (

2200

2190

i1 int NOT NULL default '0',

2201

2191

i2 int NOT NULL default '0',

2202

t datetime NOT NULL default '0000-00-00 00:00:00',

2203

PRIMARY KEY (i1,i2,t)

2204

);

2192

t datetime,

2193

PRIMARY KEY (i1,i2,t));

2194

2205

2195

INSERT INTO t1 VALUES

2206

2196

(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),

2207

2197

(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),

2214

2204

i1 int NOT NULL default '0',

2215

2205

i2 int NOT NULL default '0',

2216

2206

t datetime default NULL,

2217

PRIMARY KEY (i1)

2218

);

2207

PRIMARY KEY (i1));

2208

2219

2209

INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');

2220

2210

2221

2211

EXPLAIN

2230

2220

2231

2221

DROP TABLE t1, t2;

2232

2222

2233

2234

# Bug#14654 : Cannot select from the same table twice within a UNION

2235

# statement

2236

2223

--echo #

2224

--echo # Bug#14654 : Cannot select from the same table twice within a UNION

2225

--echo # statement

2226

--echo #

2237

2227

CREATE TABLE t1 (i INT);

2238

2228

2239

2229

(SELECT i FROM t1) UNION (SELECT i FROM t1);

2243

2233

(SELECT i FROM t1)

2244

2234

);

2245

2235

2246

#TODO:not supported

2236

--echo #TODO:not supported

2247

2237

--error ER_PARSE_ERROR

2248

2238

SELECT * FROM t1

2249

2239

WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));

2250

2240

2251

#TODO:not supported

2241

--echo #TODO:not supported

2252

2242

--error 1064

2253

2243

explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))

2254

2244

from t1;

2255

2245

2246

--replace_column 9 #

2256

2247

explain select * from t1 where not exists

2257

2248

((select t11.i from t1 t11) union (select t12.i from t1 t12));

2258

2249

2259

2250

DROP TABLE t1;

2260

2251

2261

2262

# Bug #21540: Subqueries with no from and aggregate functions return

2263

# wrong results

2252

--echo #

2253

--echo # Bug #21540: Subqueries with no from and aggregate functions return

2254

--echo # wrong results

2264

2255

CREATE TABLE t1 (a INT, b INT);

2265

2256

CREATE TABLE t2 (a INT);

2266

2257

INSERT INTO t2 values (1);

2280

2271

GROUP BY t2.a;

2281

2272

DROP TABLE t1,t2;

2282

2273

2283

2284

# Bug #21727: Correlated subquery that requires filesort:

2285

# slow with big sort_buffer_size

2286

2274

--echo #

2275

--echo # Bug #21727: Correlated subquery that requires filesort:

2276

--echo # slow with big sort_buffer_size

2277

--echo #

2287

2278

2288

CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));

2289

CREATE TABLE t2 (x int auto_increment, y int, z int,

2290

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));

2279

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

2280

CREATE TEMPORARY TABLE t2 (x int auto_increment, y int, z int,

2281

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;

2291

2282

disable_query_log;

2292

2283

set autocommit=0;

2293

2284

begin;

2320

2311

2321

2312

DROP TABLE t2,t1;

2322

2313

2323

2324

# Bug #25219: EXIST subquery with UNION over a mix of

2325

# correlated and uncorrelated selects

2326

2314

--echo #

2315

--echo # Bug #25219: EXIST subquery with UNION over a mix of

2316

--echo # correlated and uncorrelated selects

2317

--echo #

2327

2318

2328

2319

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);

2329

2320

CREATE TABLE t2 (c int);

2344

2335

SELECT c from t2 WHERE c=t1.c);

2345

2336

2346

2337

INSERT INTO t2 VALUES (2);

2347

CREATE TABLE t3 (c int);

2338

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

2348

2339

INSERT INTO t3 VALUES (1);

2349

2340

2350

2341

SELECT * FROM t1

2354

2345

2355

2346

DROP TABLE t1,t2,t3;

2356

2347

2357

2358

# Bug#21904 (parser problem when using IN with a double "(())")

2359

2348

--echo #

2349

--echo # Bug#21904 (parser problem when using IN with a double "(())")

2350

--echo #

2360

2351

2361

2352

--disable_warnings

2362

2353

DROP TABLE IF EXISTS t1;

2364

2355

DROP TABLE IF EXISTS t1xt2;

2365

2356

--enable_warnings

2366

2357

2367

CREATE TABLE t1 (

2358

CREATE TEMPORARY TABLE t1 (

2368

2359

id_1 int NOT NULL,

2369

t varchar(4) DEFAULT NULL

2370

);

2360

t varchar(4) DEFAULT NULL)

2361

ENGINE=MyISAM;

2371

2362

2372

CREATE TABLE t2 (

2363

CREATE TEMPORARY TABLE t2 (

2373

2364

id_2 int NOT NULL,

2374

t varchar(4) DEFAULT NULL

2375

);

2365

t varchar(4) DEFAULT NULL)

2366

ENGINE=MyISAM;

2376

2367

2377

CREATE TABLE t1xt2 (

2368

CREATE TEMPORARY TABLE t1xt2 (

2378

2369

id_1 int NOT NULL,

2379

id_2 int NOT NULL

2380

);

2370

id_2 int NOT NULL)

2371

ENGINE=MyISAM;

2381

2372

2382

2373

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

2383

2374

2385

2376

2386

2377

INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);

2387

2378

2388

# subselect returns 0 rows

2379

--echo # subselect returns 0 rows

2389

2380

2390

2381

SELECT DISTINCT t1.id_1 FROM t1 WHERE

2391

2382

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

2407

2398

2408

2399

insert INTO t1xt2 VALUES (1, 12);

2409

2400

2410

# subselect returns 1 row

2401

--echo # subselect returns 1 row

2411

2402

2412

2403

SELECT DISTINCT t1.id_1 FROM t1 WHERE

2413

2404

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

2429

2420

2430

2421

insert INTO t1xt2 VALUES (2, 12);

2431

2422

2432

# subselect returns more than 1 row

2423

--echo # subselect returns more than 1 row

2433

2424

2434

2425

SELECT DISTINCT t1.id_1 FROM t1 WHERE

2435

2426

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

2453

2444

DROP TABLE t2;

2454

2445

DROP TABLE t1xt2;

2455

2446

2456

2457

# Bug #26728: derived table with concatanation of literals in select list

2458

2447

--echo #

2448

--echo # Bug #26728: derived table with concatanation of literals in select list

2449

--echo #

2459

2450

2460

CREATE TABLE t1 (a int);

2451

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

2461

2452

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

2462

2453

2463

2454

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;

2465

2456

2466

2457

DROP table t1;

2467

2458

2468

2469

# Bug #27257: COUNT(*) aggregated in outer query

2470

2459

--echo #

2460

--echo # Bug #27257: COUNT(*) aggregated in outer query

2461

--echo #

2471

2462

2472

CREATE TABLE t1 (a int, b int);

2473

CREATE TABLE t2 (m int, n int);

2463

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

2464

CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;

2474

2465

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

2475

2466

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

2476

2467

2488

2479

2489

2480

DROP TABLE t1,t2;

2490

2481

2491

2492

# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument

2493

2482

--echo #

2483

--echo # Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument

2484

--echo #

2494

2485

2495

CREATE TABLE t1 (a int, b int);

2496

CREATE TABLE t2 (m int, n int);

2486

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

2487

CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;

2497

2488

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

2498

2489

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

2499

2490

2507

2498

2508

2499

DROP table t1,t2;

2509

2500

2510

2511

# Bug#27321: Wrong subquery result in a grouping select

2512

2501

--echo #

2502

--echo # Bug#27321: Wrong subquery result in a grouping select

2503

--echo #

2513

2504

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));

2514

2505

INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),

2515

2506

(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),

2544

2535

2545

2536

DROP TABLE t1;

2546

2537

2547

2548

# Bug #27363: nested aggregates in outer, subquery / sum(select

2549

# count(outer))

2550

2538

--echo #

2539

--echo # Bug #27363: nested aggregates in outer, subquery / sum(select

2540

--echo # count(outer))

2541

--echo #

2551

2542

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

2552

CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);

2543

CREATE TEMPORARY TABLE t2 (x INT) ENGINE=MyISAM; INSERT INTO t1 values (1000),(1001),(1002);

2553

2544

2554

2545

--error ER_INVALID_GROUP_FUNC_USE

2555

2546

SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;

2568

2559

2569

2560

DROP TABLE t1,t2;

2570

2561

2571

2572

# Bug #27807: Server crash when executing subquery with EXPLAIN

2573

2562

--echo #

2563

--echo # Bug #27807: Server crash when executing subquery with EXPLAIN

2564

--echo #

2574

2565

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

2575

2566

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

2576

2567

EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);

2577

2568

DROP TABLE t1;

2578

2569

2579

2580

# Bug #28377: grouping query with a correlated subquery in WHERE condition

2581

2570

--echo #

2571

--echo # Bug #28377: grouping query with a correlated subquery in WHERE condition

2572

--echo #

2582

2573

2583

2574

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));

2584

2575

INSERT INTO t1 VALUES

2585

2576

(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');

2586

CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));

2577

CREATE TEMPORARY TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;

2587

2578

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

2588

2579

2589

2580

SELECT id, st FROM t1

2600

2591

2601

2592

DROP TABLE t1,t2;

2602

2593

2603

2604

# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table

2605

# over a grouping subselect

2606

2594

--echo #

2595

--echo # Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table

2596

--echo # over a grouping subselect

2597

--echo #

2607

2598

2608

CREATE TABLE t1 (a int);

2599

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

2609

2600

2610

2601

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

2611

2602

2614

2605

2615

2606

DROP TABLE t1;

2616

2607

2617

2618

# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1

2619

2608

--echo #

2609

--echo # Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1

2610

--echo #

2620

2611

2621

2612

CREATE TABLE t1 (

2622

2613

a varchar(255) default NULL,

2623

2614

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

2624

INDEX idx(a,b)

2625

);

2615

INDEX idx(a,b));

2616

2626

2617

CREATE TABLE t2 (

2627

a varchar(255) default NULL

2628

);

2618

a varchar(255) default NULL);

2629

2619

2630

2620

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');

2631

2621

INSERT INTO t1 SELECT * FROM t1;

2647

2637

DROP TABLE t1,t2;

2648

2638

2649

2639

2650

2651

# Bug #27333: subquery grouped for aggregate of outer query / no aggregate

2652

# of subquery

2653

2654

CREATE TABLE t1 (a INTEGER, b INTEGER);

2655

CREATE TABLE t2 (x INTEGER);

2640

--echo #

2641

--echo # Bug #27333: subquery grouped for aggregate of outer query / no aggregate

2642

--echo # of subquery

2643

--echo #

2644

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

2645

CREATE TEMPORARY TABLE t2 (x INTEGER) ENGINE=MyISAM;

2656

2646

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

2657

2647

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

2658

2648

2659

# wasn't failing, but should

2649

--echo # wasn't failing, but should

2660

2650

--error ER_SUBQUERY_NO_1_ROW

2661

2651

SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;

2662

2652

2663

# fails as it should

2653

--echo # fails as it should

2664

2654

--error ER_SUBQUERY_NO_1_ROW

2665

2655

SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;

2666

2656

2667

2657

SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;

2668

2658

DROP TABLE t1,t2;

2669

2659

2670

# second test case from 27333

2660

--echo # second test case from 27333

2671

2661

CREATE TABLE t1 (a INT, b INT);

2672

2662

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

2673

2663

2674

# returns no rows, when it should

2664

--echo # returns no rows, when it should

2675

2665

SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1

2676

2666

AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)

2677

2667

GROUP BY a1.a;

2678

2668

DROP TABLE t1;

2679

2669

2680

#test cases from 29297

2681

CREATE TABLE t1 (a INT);

2682

CREATE TABLE t2 (a INT);

2670

--echo #test cases from 29297

2671

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

2672

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

2683

2673

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

2684

2674

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

2685

2675

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;

2688

2678

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;

2689

2679

DROP TABLE t1,t2;

2690

2680

2691

2692

# Bug #31884: Assertion + crash in subquery in the SELECT clause.

2693

2681

--echo #

2682

--echo # Bug #31884: Assertion + crash in subquery in the SELECT clause.

2683

--echo #

2694

2684

2695

CREATE TABLE t1 (a1 INT, a2 INT);

2696

CREATE TABLE t2 (b1 INT, b2 INT);

2685

CREATE TEMPORARY TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;

2686

CREATE TEMPORARY TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;

2697

2687

2698

2688

INSERT INTO t1 VALUES (100, 200);

2699

2689

INSERT INTO t1 VALUES (101, 201);

2703

2693

SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;

2704

2694

DROP TABLE t1, t2;

2705

2695

2706

2707

# Bug #30788: Inconsistent retrieval of char/varchar

2708

2696

--echo #

2697

--echo # Bug #30788: Inconsistent retrieval of char/varchar

2698

--echo #

2709

2699

2710

2700

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));

2711

2701

INSERT INTO t1 VALUES ('a', 'aa');

2713

2703

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

2714

2704

CREATE INDEX I1 ON t1 (a);

2715

2705

CREATE INDEX I2 ON t1 (b);

2706

--replace_column 9 #

2716

2707

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

2717

2708

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

2718

2709

2720

2711

INSERT INTO t2 SELECT * FROM t1;

2721

2712

CREATE INDEX I1 ON t2 (a);

2722

2713

CREATE INDEX I2 ON t2 (b);

2714

--replace_column 9 #

2723

2715

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

2724

2716

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

2717

--replace_column 9 #

2725

2718

EXPLAIN

2726

2719

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

2727

2720

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

2728

2721

2729

2722

DROP TABLE t1,t2;

2730

2723

2731

2732

# Bug #32400: Complex SELECT query returns correct result only on some

2733

# occasions

2734

2724

--echo #

2725

--echo # Bug #32400: Complex SELECT query returns correct result only on some

2726

--echo # occasions

2727

--echo #

2735

2728

2736

2729

CREATE TABLE t1(a INT, b INT);

2737

2730

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

2747

2740

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

2748

2741

GROUP BY a;

2749

2742

2743

--replace_column 9 #

2750

2744

EXPLAIN

2751

2745

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

2752

2746

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

2759

2753

DROP TABLE t1;

2760

2754

2761

2755

2762

2763

# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122

2764

2756

--echo #

2757

--echo # Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122

2758

--echo #

2765

2759

2766

CREATE TABLE t1 (a INT);

2760

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

2767

2761

CREATE TABLE t2 (a INT);

2768

2762

2769

2763

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

2774

2768

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

2775

2769

2776

2770

2771

--replace_column 9 #

2777

2772

EXPLAIN EXTENDED

2778

2773

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION

2779

2774

(SELECT 1 FROM t2 WHERE t1.a = t2.a));

2780

2775

2781

2776

DROP TABLE t1,t2;

2782

2777

2783

2784

# BUG#33794 "MySQL crashes executing specific query on specific dump"

2785

2786

CREATE TABLE t4 (

2778

--echo #

2779

--echo # BUG#33794 "MySQL crashes executing specific query on specific dump"

2780

--echo #

2781

CREATE TEMPORARY TABLE t4 (

2787

2782

f7 varchar(32) collate utf8_bin NOT NULL default '',

2788

2783

f10 varchar(32) collate utf8_bin default NULL,

2789

PRIMARY KEY (f7)

2790

);

2784

PRIMARY KEY (f7))

2785

ENGINE=MyISAM;

2786

2791

2787

INSERT INTO t4 VALUES(1,1), (2,null);

2792

2788

2793

2789

CREATE TABLE t2 (

2795

2791

f2 varchar(50) collate utf8_bin default NULL,

2796

2792

f3 varchar(10) collate utf8_bin default NULL,

2797

2793

PRIMARY KEY (f4),

2798

UNIQUE KEY uk1 (f2)

2799

);

2794

UNIQUE KEY uk1 (f2));

2795

2800

2796

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

2801

2797

2802

CREATE TABLE t1 (

2798

CREATE TEMPORARY TABLE t1 (

2803

2799

f8 varchar(32) collate utf8_bin NOT NULL default '',

2804

2800

f1 varchar(10) collate utf8_bin default NULL,

2805

2801

f9 varchar(32) collate utf8_bin default NULL,

2806

PRIMARY KEY (f8)

2807

);

2802

PRIMARY KEY (f8))

2803

ENGINE=MyISAM;

2804

2808

2805

INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);

2809

2806

2810

CREATE TABLE t3 (

2807

CREATE TEMPORARY TABLE t3 (

2811

2808

f6 varchar(32) collate utf8_bin NOT NULL default '',

2812

2809

f5 varchar(50) collate utf8_bin default NULL,

2813

PRIMARY KEY (f6)

2814

);

2810

PRIMARY KEY (f6))

2811

ENGINE=MyISAM;

2812

2815

2813

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

2816

2814

2817

2815

SELECT

2836

2834

2837

2835

--echo End of 5.0 tests.

2838

2836

2839

2840

# Test [NOT] IN truth table (both as top-level and general predicate).

2841

2837

--echo #

2838

--echo # Test [NOT] IN truth table (both as top-level and general predicate).

2839

--echo #

2842

2840

2843

create table t_out (subcase char(3),

2844

a1 char(2), b1 char(2), c1 char(2));

2841

create temporary table t_out (subcase char(3),

2842

a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;

2845

2843

create table t_in (a2 char(2), b2 char(2), c2 char(2));

2846

2844

2847

2845

insert into t_out values ('A.1','2a', NULL, '2a');

2848

#------------------------- A.2 - impossible

2846

--echo #------------------------- A.2 - impossible

2849

2847

insert into t_out values ('A.3', '2a', NULL, '2a');

2850

2848

insert into t_out values ('A.4', '2a', NULL, 'xx');

2851

2849

insert into t_out values ('B.1', '2a', '2a', '2a');

2933

2931

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

2934

2932

where subcase = 'A.3' and

2935

2933

NOT((a1, b1, c1) IN (select * from t_in));

2936

# test non-top level result indirectly

2934

--echo # test non-top level result indirectly

2937

2935

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

2938

2936

where subcase = 'A.3' and

2939

2937

((a1, b1, c1) IN (select * from t_in)) is NULL and

2990

2988

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

2991

2989

where subcase = 'B.3' and

2992

2990

NOT((a1, b1, c1) IN (select * from t_in));

2993

# test non-top level result indirectly

2991

--echo # test non-top level result indirectly

2994

2992

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

2995

2993

where subcase = 'B.3' and

2996

2994

((a1, b1, c1) IN (select * from t_in)) is NULL and

3013

3011

drop table t_in;

3014

3012

3015

3013

3016

3017

# Bug#20835 (literal string with =any values)

3018

3014

--echo #

3015

--echo # Bug#20835 (literal string with =any values)

3016

--echo #

3019

3017

CREATE TABLE t1 (s1 char(1));

3020

3018

INSERT INTO t1 VALUES ('a');

3021

SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);

3019

SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);

3022

3020

DROP TABLE t1;

3023

3021

3024

3025

# Bug#33204: INTO is allowed in subselect, causing inconsistent results

3026

3022

--echo #

3023

--echo # Bug#33204: INTO is allowed in subselect, causing inconsistent results

3024

--echo #

3027

3025

CREATE TABLE t1( a INT );

3028

3026

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

3029

3027

3076

3074

SELECT a FROM t1 WHERE a = 2

3077

3075

) t1a;

3078

3076

3079

# This was not allowed previously. Possibly, it should be allowed on the future.

3080

# For now, the intent is to keep the fix as non-intrusive as possible.

3077

--echo # This was not allowed previously. Possibly, it should be allowed on the future.

3078

--echo # For now, the intent is to keep the fix as non-intrusive as possible.

3081

3079

--error ER_PARSE_ERROR

3082

3080

SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);

3083

3081

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;

3108

3106

SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;

3109

3107

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;

3110

3108

3111

# Test of rule

3112

# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias

3113

# UNION should not be allowed inside the parentheses, nor should

3114

# aliases after.

3115

3109

--echo # Test of rule

3110

--echo # table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias

3111

--echo # UNION should not be allowed inside the parentheses, nor should

3112

--echo # aliases after.

3113

--echo #

3116

3114

SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;

3117

3115

--error ER_PARSE_ERROR

3118

3116

SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;

3137

3135

SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;

3138

3136

SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;

3139

3137

3140

# For the join, TABLE_LIST::select_lex == NULL

3141

# Check that we handle this.

3138

--echo # For the join, TABLE_LIST::select_lex == NULL

3139

--echo # Check that we handle this.

3142

3140

--error ER_PARSE_ERROR

3143

3141

SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;

3144

3142

3183

3181

--error ER_PARSE_ERROR

3184

3182

SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

3185

3183

3186

# Make sure context is popped when we leave the nested select

3184

--echo # Make sure context is popped when we leave the nested select

3187

3185

SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;

3188

3186

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

3189

3187

3190

# Make sure we have feature F561 (see .yy file)

3188

--echo # Make sure we have feature F561 (see .yy file)

3191

3189

SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);

3192

3190

3193

# Make sure the parser does not allow nested UNIONs anywhere

3191

--echo # Make sure the parser does not allow nested UNIONs anywhere

3194

3192

3195

3193

--error ER_PARSE_ERROR

3196

3194

SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );

Older »