~drizzle-trunk/drizzle/development

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

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

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

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

--replace_column 9 #

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

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

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

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

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

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

--replace_column 9 #

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

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

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

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

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

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

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

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

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

100

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

101

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

102

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

103

--replace_column 9 #

104

100

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

105

101

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

106

102

delete from t2 where a=100;

107

--error ER_OPERAND_COLUMNS

103

-- error 1241

108

104

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

109

--error ER_OPERAND_COLUMNS

105

-- error 1241

110

106

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

111

107

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

112

--echo # empty set

108

# empty set

113

109

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

114

110

insert into t2 values (2,10);

115

111

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

116

112

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

117

113

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

118

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

114

create table t5 (a int);

119

115

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

120

116

insert into t5 values (5);

121

117

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

122

118

insert into t5 values (2);

123

119

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

124

--replace_column 9 #

125

120

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

126

--error ER_SUBQUERY_NO_1_ROW

121

-- error 1242

127

122

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

128

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

129

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

123

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

124

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

130

125

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

131

126

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

132

127

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

133

128

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

134

129

135

--echo # not unique fields

136

--error ER_NON_UNIQ_ERROR

130

# not unique fields

131

-- error 1052

137

132

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

138

133

139

--echo # different tipes & group functions

134

# different tipes & group functions

140

135

drop table t1,t2,t3;

141

136

142

137

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

143

138

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

144

139

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

145

140

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

146

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

141

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

147

142

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

148

143

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

149

144

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

154

149

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

155

150

PRIMARY KEY (`pseudo`),

156

151

UNIQUE KEY `email` (`email`)

157

);

152

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

158

153

159

154

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

160

155

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

161

156

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

162

--replace_column 9 #

163

157

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

164

--error ER_OPERAND_COLUMNS

158

-- error 1241

165

159

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

166

160

t8 WHERE pseudo='joce');

167

--error ER_OPERAND_COLUMNS

161

-- error 1241

168

162

SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE

169

163

pseudo='joce');

170

164

SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');

171

--error ER_SUBQUERY_NO_1_ROW

165

-- error 1242

172

166

SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');

173

167

174

168

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

175

169

176

--echo #searchconthardwarefr3 forumconthardwarefr7

177

CREATE TEMPORARY TABLE `t1` (

170

#searchconthardwarefr3 forumconthardwarefr7

171

CREATE TABLE `t1` (

178

172

`topic` bigint NOT NULL default '0',

179

`date` date NULL,

173

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

180

174

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

181

175

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

182

176

KEY `topic` (`topic`)

188

182

SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';

189

183

SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');

190

184

SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;

191

--error ER_SUBQUERY_NO_1_ROW

185

-- error 1242

192

186

SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;

193

187

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

194

188

drop table t1;

195

189

196

--echo #forumconthardwarefr7 searchconthardwarefr7

190

#forumconthardwarefr7 searchconthardwarefr7

197

191

CREATE TABLE `t1` (

198

192

`numeropost` bigint NOT NULL auto_increment,

199

193

`maxnumrep` int NOT NULL default '0',

200

194

PRIMARY KEY (`numeropost`),

201

195

UNIQUE KEY `maxnumrep` (`maxnumrep`)

202

);

196

) ENGINE=MyISAM ROW_FORMAT=FIXED;

203

197

204

198

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

205

199

206

200

CREATE TABLE `t2` (

207

201

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

208

202

`topic` bigint NOT NULL default '0',

209

`date` date NULL,

203

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

210

204

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

211

205

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

212

);

206

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

213

207

214

208

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

215

209

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

216

210

SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;

217

--error ER_BAD_FIELD_ERROR

211

-- error 1054

218

212

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

219

--error ER_BAD_FIELD_ERROR

213

-- error 1054

220

214

SELECT 1 IN (SELECT 1 FROM t2 HAVING a);

221

215

222

216

SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);

236

230

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

237

231

drop table t1,t2;

238

232

239

--echo #forumconthardwarefr7

233

#forumconthardwarefr7

240

234

CREATE TABLE `t1` (

241

235

`numeropost` bigint NOT NULL auto_increment,

242

236

`maxnumrep` int NOT NULL default '0',

243

237

PRIMARY KEY (`numeropost`),

244

238

UNIQUE KEY `maxnumrep` (`maxnumrep`)

245

);

239

) ENGINE=MyISAM ROW_FORMAT=FIXED;

246

240

247

241

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

248

--error ER_SUBQUERY_NO_1_ROW

242

-- error 1242

249

243

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

250

--error ER_SUBQUERY_NO_1_ROW

244

-- error 1242

251

245

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

252

246

drop table t1;

253

247

256

250

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

257

251

drop table t1;

258

252

259

--echo #iftest

260

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

253

#iftest

254

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

261

255

INSERT INTO t1 VALUES ();

262

--error ER_SUBQUERY_NO_1_ROW

256

-- error 1242

263

257

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

264

258

drop table t1;

265

259

266

--echo # threadhardwarefr7

260

# threadhardwarefr7

267

261

CREATE TABLE `t1` (

268

262

`numeropost` bigint NOT NULL default '0',

269

263

`numreponse` int NOT NULL auto_increment,

271

265

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

272

266

UNIQUE KEY `numreponse` (`numreponse`),

273

267

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

274

);

275

--error ER_ILLEGAL_REFERENCE

268

) ENGINE=MyISAM;

269

-- error 1247

276

270

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

277

--error ER_BAD_FIELD_ERROR

271

-- error 1054

278

272

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

279

273

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

280

274

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

281

--error ER_SUBQUERY_NO_1_ROW

275

-- error 1242

282

276

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

283

--replace_column 9 #

284

277

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

285

--replace_column 9 #

286

278

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

287

279

drop table t1;

288

280

289

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

281

CREATE TABLE t1 (a int);

290

282

INSERT INTO t1 VALUES (1);

291

283

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

292

284

drop table t1;

293

285

294

--echo #update with subselects

286

#update with subselects

295

287

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

296

288

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

297

289

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

298

290

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

299

291

select * from t1;

300

--error ER_UPDATE_TABLE_USED

292

-- error 1093

301

293

update t1 set b= (select b from t1);

302

--error ER_SUBQUERY_NO_1_ROW

294

-- error 1242

303

295

update t1 set b= (select b from t2);

304

296

update t1 set b= (select b from t2 where t1.a = t2.a);

305

297

select * from t1;

306

298

drop table t1, t2;

307

299

308

--echo #delete with subselects

300

#delete with subselects

309

301

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

310

302

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

311

303

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

312

304

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

313

305

select * from t1;

314

306

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

315

--error ER_UPDATE_TABLE_USED

307

-- error 1093

316

308

delete from t1 where b = (select b from t1);

317

--error ER_SUBQUERY_NO_1_ROW

309

-- error 1242

318

310

delete from t1 where b = (select b from t2);

319

311

delete from t1 where b = (select b from t2 where t1.a = t2.a);

320

312

select * from t1;

321

313

drop table t1, t2;

322

314

323

--echo #insert with subselects

315

#multi-delete with subselects

316

317

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

318

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

319

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

320

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

321

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

322

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

323

select * from t11;

324

--sorted_result

325

select * from t12;

326

-- error 1093

327

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

328

-- error 1242

329

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

330

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

331

select * from t11;

332

--sorted_result

333

select * from t12;

334

drop table t11, t12, t2;

335

336

#insert with subselects

324

337

CREATE TABLE t1 (x int);

325

338

create table t2 (a int);

326

339

create table t3 (b int);

327

340

insert into t2 values (1);

328

341

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

329

--error ER_UPDATE_TABLE_USED

342

-- error 1093

330

343

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

331

--error ER_SUBQUERY_NO_1_ROW

344

-- error 1242

332

345

INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));

333

346

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

334

347

select * from t1;

335

348

insert into t2 values (1);

336

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

349

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

350

-- sleep 1

337

351

select * from t1;

338

352

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

339

353

select * from t1;

340

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

354

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

341

355

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

342

356

select * from t1;

343

--error ER_BAD_FIELD_ERROR

344

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

345

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

357

-- error 1054

358

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

359

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

360

-- sleep 1

346

361

select * from t1;

347

--echo #

348

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

349

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

350

--echo #select * from t1;

362

363

#TODO: should be uncommented after bug 380 fix pushed

364

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

365

#select * from t1;

351

366

drop table t1, t2, t3;

352

367

353

--echo #replace with subselects

368

#replace with subselects

354

369

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

355

370

create table t2 (a int);

356

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

371

create table t3 (a int);

357

372

insert into t2 values (1);

358

373

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

359

374

select * from t1;

360

--error ER_UPDATE_TABLE_USED

375

-- error 1093

361

376

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

362

--error ER_SUBQUERY_NO_1_ROW

377

-- error 1242

363

378

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

364

379

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

365

380

select * from t1;

366

381

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

367

382

select * from t1;

368

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

383

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

384

-- sleep 1

369

385

--sorted_result

370

386

select * from t1;

371

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

387

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

388

-- sleep 1

372

389

select * from t1;

373

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

390

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

374

391

--sorted_result

375

392

select * from t1;

376

393

drop table t1, t2, t3;

377

394

378

--error ER_NO_TABLES_USED

395

-- error 1096

379

396

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

380

397

381

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

398

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

382

399

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

383

400

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

384

401

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

388

405

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

389

406

SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);

390

407

SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);

391

--error ER_UPDATE_TABLE_USED

408

-- error 1093

392

409

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

393

--error ER_UPDATE_TABLE_USED

410

-- error 1093

394

411

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

395

412

SELECT * FROM t2;

396

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

413

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

397

414

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

398

--error ER_SUBQUERY_NO_1_ROW

415

-- error 1242

399

416

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

400

417

drop table t2, t1;

401

418

402

--echo #NULL test

403

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

419

#NULL test

420

create table t1 (a int);

404

421

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

405

422

select 1 IN (SELECT * from t1);

406

423

select 10 IN (SELECT * from t1);

414

431

select 1 > ANY (SELECT * from t1);

415

432

select 10 > ANY (SELECT * from t1);

416

433

drop table t1;

417

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

434

create table t1 (a varchar(20));

418

435

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

419

436

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

420

437

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

428

445

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

429

446

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

430

447

drop table t1;

431

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

448

create table t1 (a float);

432

449

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

433

450

select 1.5 IN (SELECT * from t1);

434

451

select 10.5 IN (SELECT * from t1);

446

463

select (select a+1) from t1;

447

464

drop table t1;

448

465

449

--echo #

450

--echo # Null with keys

451

--echo #

466

467

# Null with keys

468

452

469

453

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

454

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

470

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

471

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

455

472

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

456

473

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

457

474

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

458

475

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

459

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

476

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

460

477

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

461

478

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

462

479

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

463

480

drop table t1,t2,t3;

464

481

465

--echo #LIMIT is not supported now

466

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

467

--echo #--error ER_NOT_SUPPORTED_YET

468

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

469

--echo #--error ER_NOT_SUPPORTED_YET

470

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

471

--echo #drop table t1;

472

--echo #

473

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

474

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

475

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

476

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

477

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

478

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

479

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

480

--echo #drop table t1,t2;

481

--echo #

482

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

483

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

484

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

485

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

486

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

487

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

488

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

489

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

490

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

491

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

492

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

493

--echo #--error ER_NOT_SUPPORTED_YET

494

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

495

--echo #drop table t1;

482

#LIMIT is not supported now

483

#create table t1 (a float);

484

#-- error 1235

485

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

486

#-- error 1235

487

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

488

#drop table t1;

489

490

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

491

#create table t2 (a int);

492

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

493

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

494

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

495

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

496

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

497

#drop table t1,t2;

498

499

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

500

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

501

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

502

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

503

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

504

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

505

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

506

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

507

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

508

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

509

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

510

#-- error 1235

511

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

512

#drop table t1;

496

513

497

--echo #test of uncacheable subqueries

514

#test of uncacheable subqueries

498

515

CREATE TABLE t1 (a int);

499

516

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

500

517

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

501

518

drop table t1;

502

519

503

520

504

CREATE TEMPORARY TABLE `t1` (

505

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

506

`topic` bigint NOT NULL default '0',

507

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

508

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

509

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

510

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

511

KEY `topic` (`topic`)

512

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

513

514

CREATE TEMPORARY TABLE `t2` (

515

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

516

`topic` bigint NOT NULL default '0',

517

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

518

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

519

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

520

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

521

KEY `topic` (`topic`)

522

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

523

524

CREATE TEMPORARY TABLE `t3` (

521

CREATE TABLE `t1` (

522

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

523

`topic` bigint NOT NULL default '0',

524

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

525

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

526

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

527

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

528

KEY `topic` (`topic`)

529

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

530

531

CREATE TABLE `t2` (

532

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

533

`topic` bigint NOT NULL default '0',

534

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

535

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

536

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

537

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

538

KEY `topic` (`topic`)

539

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

540

541

CREATE TABLE `t3` (

525

542

`numeropost` bigint NOT NULL auto_increment,

526

543

`maxnumrep` int NOT NULL default '0',

527

544

PRIMARY KEY (`numeropost`),

543

560

drop table t1, t2, t3;

544

561

545

562

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

546

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

547

SHOW CREATE TABLE t1;

548

drop table t1;

549

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

550

SHOW CREATE TABLE t1;

551

drop table t1;

552

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

553

SHOW CREATE TABLE t1;

554

drop table t1;

555

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

563

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

564

SHOW CREATE TABLE t1;

565

drop table t1;

566

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

567

SHOW CREATE TABLE t1;

568

drop table t1;

569

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

570

SHOW CREATE TABLE t1;

571

drop table t1;

572

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

556

573

select * from t1;

557

574

SHOW CREATE TABLE t1;

558

575

drop table t1;

559

576

560

577

create table t1 (a int);

561

578

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

562

--replace_column 9 #

563

579

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

564

580

from t1;

565

581

drop table t1;

566

582

567

--echo #

568

--echo # error in IN

569

--echo #

570

--error ER_TABLE_UNKNOWN

583

584

# error in IN

585

586

-- error 1146

571

587

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

572

588

573

--echo #

574

--echo # complex subquery

575

--echo #

589

590

# complex subquery

591

576

592

577

593

CREATE TABLE t1 (

578

594

ID int NOT NULL auto_increment,

581

597

District char(20) NOT NULL default '',

582

598

Population int NOT NULL default '0',

583

599

PRIMARY KEY (ID)

584

);

600

) ENGINE=MyISAM;

585

601

586

602

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

587

603

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

604

620

Capital int default NULL,

605

621

Code2 char(2) NOT NULL default '',

606

622

PRIMARY KEY (Code)

607

);

623

) ENGINE=MyISAM;

608

624

609

625

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

610

626

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

613

629

614

630

drop table t1, t2;

615

631

616

--echo #

617

--echo # constants in IN

618

--echo #

619

CREATE TEMPORARY TABLE `t1` (

632

633

# constants in IN

634

635

CREATE TABLE `t1` (

620

636

`id` bigint NOT NULL auto_increment,

621

637

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

622

638

PRIMARY KEY (`id`),

623

639

UNIQUE KEY `pseudo` (`pseudo`)

624

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

640

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

625

641

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

626

642

SELECT 0 IN (SELECT 1 FROM t1 a);

627

643

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

630

646

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

631

647

drop table t1;

632

648

633

CREATE TEMPORARY TABLE `t1` (

649

CREATE TABLE `t1` (

634

650

`i` int NOT NULL default '0',

635

651

PRIMARY KEY (`i`)

636

652

) ENGINE=MyISAM;

638

654

INSERT INTO t1 VALUES (1);

639

655

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

640

656

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

641

--error ER_BAD_FIELD_ERROR

657

-- error 1054

642

658

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

643

659

select * from t1;

644

660

drop table t1;

645

661

646

--echo #

647

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

648

--echo #

649

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

662

663

# Multi update test

664

665

CREATE TABLE t1 (

666

id int default NULL

667

) ENGINE=MyISAM;

668

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

669

CREATE TABLE t2 (

670

id int default NULL,

671

name varchar(15) default NULL

672

) ENGINE=MyISAM;

673

674

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

675

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

676

select * from t2;

677

drop table t1,t2;

678

679

680

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

681

682

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

650

683

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

651

684

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

652

685

drop table t1;

653

686

654

--echo #

655

--echo # Test optimization for sub selects

656

--echo #

687

688

# Test optimization for sub selects

689

657

690

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

658

691

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

659

--replace_column 9 #

660

692

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

661

693

drop table t1;

662

694

669

701

UNIQUE KEY t1_PK (ID,SUB_ID),

670

702

KEY t1_FK (REF_ID,REF_SUB),

671

703

KEY t1_REFID (REF_ID)

672

);

704

) ENGINE=MyISAM;

673

705

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

674

706

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

675

707

DROP TABLE t1;

676

708

677

--echo #

678

--echo # uninterruptable update

679

--echo #

680

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

681

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

709

710

# uninterruptable update

711

712

create table t1 (a int, b int);

713

create table t2 (a int, b int);

682

714

683

715

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

684

716

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

688

720

689

721

drop table t1, t2;

690

722

691

--echo #

692

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

693

--echo #

723

724

# reduced subselect in ORDER BY & GROUP BY clauses

725

694

726

695

CREATE TEMPORARY TABLE `t1` (

727

CREATE TABLE `t1` (

696

728

`id` bigint NOT NULL auto_increment,

697

729

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

698

730

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

699

731

PRIMARY KEY (`id`),

700

732

UNIQUE KEY `email` (`email`),

701

733

UNIQUE KEY `pseudo` (`pseudo`)

702

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

734

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

703

735

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

704

736

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

705

737

drop table if exists t1;

706

738

707

739

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

708

740

709

--echo #

710

--echo # IN subselect optimization test

711

--echo #

712

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

713

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

714

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

741

742

# IN subselect optimization test

743

744

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

745

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

746

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

715

747

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

716

748

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

717

749

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

722

754

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

723

755

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

724

756

drop table t1, t2, t3;

725

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

726

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

727

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

757

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

758

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

759

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

728

760

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

729

761

disable_query_log;

730

--echo # making table large enough

762

# making table large enough

731

763

set autocommit=0;

732

764

begin;

733

765

let $1 = 10000;

753

785

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

754

786

drop table t1, t2, t3;

755

787

756

--echo #

757

--echo # alloc_group_fields() working

758

--echo #

759

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

760

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

761

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

788

789

# alloc_group_fields() working

790

791

create table t1 (a int, b int);

792

create table t2 (a int, b int);

793

create table t3 (a int, b int);

762

794

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

763

795

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

764

796

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

765

797

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

766

798

drop table t1,t2,t3;

767

799

768

--echo #

769

--echo # aggregate functions in HAVING test

770

--echo #

771

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

772

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

800

801

# aggregate functions in HAVING test

802

803

create table t1 (s1 int);

804

create table t2 (s1 int);

773

805

insert into t1 values (1);

774

806

insert into t2 values (1);

775

807

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

776

808

drop table t1,t2;

777

809

778

--echo #

779

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

780

--echo # in UPDATE name space)

781

--echo #

782

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

783

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

810

811

# update subquery with wrong field (to force name resolving

812

# in UPDATE name space)

813

814

create table t1 (s1 int);

815

create table t2 (s1 int);

784

816

insert into t1 values (1);

785

817

insert into t2 values (1);

786

--error ER_BAD_FIELD_ERROR

818

-- error 1054

787

819

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

788

820

DROP TABLE t1, t2;

789

821

790

--echo #

791

--echo # collation test

792

--echo #

793

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

794

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

795

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

796

--echo #--error ER_CANT_AGGREGATE_2COLLATIONS

797

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

798

--echo #--error ER_CANT_AGGREGATE_2COLLATIONS

799

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

800

--echo #drop table t1;

822

823

# collation test

824

825

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

826

# s2 CHAR(5) COLLATE latin1_swedish_ci);

827

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

828

#-- error 1267

829

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

830

#-- error 1267

831

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

832

#drop table t1;

801

833

802

--echo #

803

--echo # aggregate functions reinitialization

804

--echo #

834

835

# aggregate functions reinitialization

836

805

837

create table t1(toid int,rd int);

806

838

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

807

839

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

809

841

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

810

842

drop table t1, t2;

811

843

812

--echo #

813

--echo # row union

814

--echo #

844

845

# row union

846

815

847

create table t1 (s1 char(5));

816

--error ER_OPERAND_COLUMNS

848

-- error 1241

817

849

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

818

850

insert into t1 values ('tttt');

819

851

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

820

--replace_column 9 #

821

852

explain extended (select * from t1);

822

853

(select * from t1);

823

854

drop table t1;

824

855

825

--echo #

826

--echo # IN optimisation test results

827

--echo #

828

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

829

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

856

857

# IN optimisation test results

858

859

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

860

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

830

861

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

831

862

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

832

863

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

839

870

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

840

871

drop table t1,t2;

841

872

842

--echo #

843

--echo # correct ALL optimisation

844

--echo #

845

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

846

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

873

874

# correct ALL optimisation

875

876

create table t2 (a int, b int);

877

create table t3 (a int);

847

878

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

848

879

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

849

880

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

861

892

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

862

893

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

863

894

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

864

--echo #

865

--echo # optimized static ALL/ANY with grouping

866

--echo #

895

896

# optimized static ALL/ANY with grouping

897

867

898

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

868

899

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

869

900

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

870

901

drop table t2, t3;

871

902

872

--echo #

873

--echo # correct used_tables()

874

--echo #

903

904

# correct used_tables()

905

875

906

876

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

907

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 AUTO_INCREMENT=3 ;

877

908

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

878

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

909

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

879

910

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

880

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

911

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

881

912

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

882

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

913

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

883

914

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

884

915

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

885

916

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

886

917

drop table t1,t2,t3,t4;

887

918

888

--echo #

889

--echo # cardinality check

890

--echo #

891

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

919

920

# cardinality check

921

922

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

892

923

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

893

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

924

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

894

925

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

895

--error ER_OPERAND_COLUMNS

926

-- error 1241

896

927

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

897

928

DROP TABLE t1,t2;

898

929

899

--echo #

900

--echo # optimized ALL/ANY with union

901

--echo #

930

931

# optimized ALL/ANY with union

932

902

933

create table t1 (s1 char);

903

934

insert into t1 values ('e');

904

935

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

905

936

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

906

--replace_column 9 #

907

937

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

908

938

drop table t1;

909

939

910

--echo #

911

--echo # filesort in subquery (restoring join_tab)

912

--echo #

913

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

940

941

# filesort in subquery (restoring join_tab)

942

943

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

914

944

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

915

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

945

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

916

946

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

917

947

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

918

948

drop table t1, t2;

919

949

920

--echo #

921

--echo # unresolved field error

922

--echo #

923

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

924

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

925

--error ER_BAD_FIELD_ERROR

950

951

# unresolved field error

952

953

create table t1 (s1 int);

954

create table t2 (s1 int);

955

-- error 1054

926

956

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

927

--error ER_BAD_FIELD_ERROR

957

-- error 1054

928

958

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

929

--error ER_BAD_FIELD_ERROR

959

-- error 1054

930

960

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

931

961

drop table t1, t2;

932

962

933

--echo #

934

--echo # fix_fields() in add_ref_to_table_cond()

935

--echo #

936

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

937

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

963

964

# fix_fields() in add_ref_to_table_cond()

965

966

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

967

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

938

968

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

939

969

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

940

970

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

941

971

DROP TABLE t1, t2;

942

972

943

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

973

CREATE TABLE t1 (a int);

944

974

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

945

975

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

946

976

DROP TABLE t1;

947

977

948

--echo #

949

--echo # Bug 2479

950

--echo #

978

979

# Bug 2479

980

951

981

952

CREATE TEMPORARY TABLE `t1` (

982

CREATE TABLE `t1` (

953

983

`id` int NOT NULL auto_increment,

954

984

`id_cns` int NOT NULL default '0',

955

985

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

959

989

`bis` int NOT NULL default '0',

960

990

PRIMARY KEY (`id`),

961

991

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

962

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

963

ENGINE=MyISAM;

992

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

993

);

964

994

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

965

CREATE TEMPORARY TABLE `t2` (

995

CREATE TABLE `t2` (

966

996

`id` int NOT NULL auto_increment,

967

997

`max_anno_dep` int NOT NULL default '0',

968

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

998

PRIMARY KEY (`id`)

999

);

969

1000

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

970

1001

971

1002

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

972

1003

973

1004

DROP TABLE t1, t2;

974

1005

975

--echo #

976

--echo # GLOBAL LIMIT

977

--echo #

978

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

1006

1007

# GLOBAL LIMIT

1008

1009

create table t1 (a int);

979

1010

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

980

1011

SET SQL_SELECT_LIMIT=1;

981

1012

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

983

1014

SET SQL_SELECT_LIMIT=default;

984

1015

drop table t1;

985

1016

986

--echo #

987

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

988

--echo #

1017

1018

# Bug #3118: subselect + order by

1019

989

1020

990

1021

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

991

1022

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

992

1023

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

993

1024

DROP TABLE t1;

994

1025

995

--echo # Item_cond fix field

996

--echo #

1026

# Item_cond fix field

1027

997

1028

create table t1(val varchar(10));

998

1029

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

999

1030

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

1000

1031

drop table t1;

1001

1032

1002

--echo #

1003

--echo # ref_or_null replacing with ref

1004

--echo #

1033

1034

# ref_or_null replacing with ref

1035

1005

1036

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

1006

1037

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

1007

1038

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

1008

1039

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

1009

--replace_column 9 #

1010

1040

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

1011

--replace_column 9 #

1012

1041

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

1013

1042

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

1014

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

1043

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

1015

1044

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

1016

1045

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

1017

--replace_column 9 #

1018

1046

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

1019

1047

drop table t1,t2;

1020

1048

1021

--echo #

1022

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

1023

--echo #

1024

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

1049

1050

# Static tables & rund() in subqueries

1051

1052

create table t1 (a int);

1025

1053

insert into t1 values (1);

1026

1054

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

1027

1055

drop table t1;

1028

1056

1029

--echo #

1030

--echo # bug 3188

1031

--echo #

1032

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

1033

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

1034

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

1035

--error ER_PARSE_ERROR

1057

1058

# bug 3188

1059

1060

create table t1(id int);

1061

create table t2(id int);

1062

create table t3(flag int);

1063

-- error 1064

1036

1064

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

1037

1065

drop table t1,t2,t3;

1038

1066

1039

--echo #

1040

--echo # aggregate functions (Bug #3505)

1041

--echo #

1067

1068

# aggregate functions (Bug #3505)

1069

1042

1070

CREATE TABLE t1 (id INT);

1043

1071

CREATE TABLE t2 (id INT);

1044

1072

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

1049

1077

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

1050

1078

DROP TABLE t1,t2;

1051

1079

1052

--echo #

1053

--echo # ALL/ANY test

1054

--echo #

1080

1081

# ALL/ANY test

1082

1055

1083

CREATE TABLE t1 ( a int, b int );

1056

1084

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

1057

1085

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

1066

1094

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

1067

1095

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

1068

1096

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

1069

--echo # with index

1097

# with index

1070

1098

ALTER TABLE t1 ADD INDEX (a);

1071

1099

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

1072

1100

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

1080

1108

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

1081

1109

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

1082

1110

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

1083

--echo # having clause test

1111

# having clause test

1084

1112

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

1085

1113

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

1086

1114

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

1093

1121

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

1094

1122

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

1095

1123

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

1096

--echo # union test

1124

# union test

1097

1125

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

1098

1126

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

1099

1127

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

1106

1134

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

1107

1135

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

1108

1136

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

1109

--echo # union + having test

1137

# union + having test

1110

1138

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

1111

1139

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

1112

1140

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

1119

1147

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

1120

1148

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

1121

1149

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

1122

--echo # row tests

1123

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

1124

--error ER_OPERAND_COLUMNS

1150

# row tests

1151

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

1152

-- error 1241

1125

1153

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

1126

--error ER_OPERAND_COLUMNS

1154

-- error 1241

1127

1155

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

1128

--error ER_OPERAND_COLUMNS

1156

-- error 1241

1129

1157

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

1130

--error ER_OPERAND_COLUMNS

1158

-- error 1241

1131

1159

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

1132

--error ER_OPERAND_COLUMNS

1160

-- error 1241

1133

1161

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

1134

--error ER_OPERAND_COLUMNS

1162

-- error 1241

1135

1163

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

1136

--error ER_OPERAND_COLUMNS

1164

-- error 1241

1137

1165

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

1138

--error ER_OPERAND_COLUMNS

1166

-- error 1241

1139

1167

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

1140

--echo # following should be converted to IN

1141

--error ER_OPERAND_COLUMNS

1168

# following should be converted to IN

1169

-- error 1241

1142

1170

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

1143

--error ER_OPERAND_COLUMNS

1171

-- error 1241

1144

1172

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

1145

1173

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

1146

--error ER_OPERAND_COLUMNS

1174

-- error 1241

1147

1175

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

1148

--error ER_OPERAND_COLUMNS

1176

-- error 1241

1149

1177

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

1150

1178

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

1151

1179

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

1156

1184

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

1157

1185

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

1158

1186

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

1159

--echo # without optimisation

1187

# without optimisation

1160

1188

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

1161

1189

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

1162

1190

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

1169

1197

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

1170

1198

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

1171

1199

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

1172

--echo # without optimisation + having

1200

# without optimisation + having

1173

1201

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

1174

1202

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

1175

1203

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

1182

1210

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

1183

1211

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

1184

1212

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

1185

--echo # EXISTS in string contence

1213

# EXISTS in string contence

1186

1214

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

1187

1215

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

1188

1216

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

1219

1247

DROP TABLE t1;

1220

1248

1221

1249

1222

--echo #

1223

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

1224

--echo #

1250

1251

# SELECT(EXISTS * ...)optimisation

1252

1225

1253

create table t1 (a int, b int);

1226

1254

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

1227

1255

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

1228

--replace_column 9 #

1229

1256

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

1230

1257

drop table t1;

1231

1258

1232

--echo #

1233

--echo # Bug #4102: subselect in HAVING

1234

--echo #

1259

1260

# Bug #4102: subselect in HAVING

1261

1235

1262

1236

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

1263

CREATE TABLE t1 (t1_a int);

1237

1264

INSERT INTO t1 VALUES (1);

1238

1265

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

1239

1266

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

1241

1268

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

1242

1269

DROP TABLE t1, t2;

1243

1270

1244

--echo #

1245

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

1246

--echo #

1271

1272

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

1273

1247

1274

1248

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

1275

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

1249

1276

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

1250

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

1277

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

1251

1278

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

1252

1279

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

1253

1280

drop table t1,t2;

1254

1281

1255

--echo #

1256

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

1257

--echo #

1258

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

1282

1283

# Aggregate function comparation with ALL/ANY/SOME subselect

1284

1285

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

1259

1286

insert into t1 values (1);

1260

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

1287

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

1261

1288

insert into t2 values (1,2);

1262

1289

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

1263

1290

drop table t1,t2;

1264

1291

1265

--echo #

1266

--echo # BUG#5003 - like in subselect

1267

--echo #

1292

1293

# BUG#5003 - like in subselect

1294

1268

1295

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

1269

1296

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

1270

1297

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

1274

1301

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

1275

1302

drop table t1;

1276

1303

1277

--echo #

1278

--echo # Optimized IN with compound index

1279

--echo #

1280

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

1281

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

1304

1305

# Optimized IN with compound index

1306

1307

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

1308

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

1282

1309

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

1283

1310

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

1284

1311

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

1290

1317

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

1291

1318

drop table t1,t2;

1292

1319

1293

--echo #

1294

--echo # resolving fields of grouped outer SELECT

1295

--echo #

1320

1321

# resolving fields of grouped outer SELECT

1322

1296

1323

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

1297

1324

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

1298

1325

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

1307

1334

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

1308

1335

drop table t1;

1309

1336

1310

--echo #

1311

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

1312

--echo # creation

1313

--echo #

1314

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

1337

1338

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

1339

# creation

1340

1341

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

1315

1342

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

1316

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

1343

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

1317

1344

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

1318

--error ER_BAD_FIELD_ERROR

1345

-- error 1054

1319

1346

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

1320

1347

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

1321

1348

drop tables t1,t2;

1322

1349

1323

--echo #

1324

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

1325

--echo #

1350

1351

# Subselect in non-select command just after connection

1352

1326

1353

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

1327

1354

connection root;

1328

1355

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

1329

1356

1330

--echo #

1331

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

1332

--echo #

1333

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

1334

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

1357

1358

# primary query with temporary table and subquery with groupping

1359

1360

create table t1 (a int, b int);

1361

create table t2 (a int, b int);

1335

1362

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

1336

1363

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

1337

1364

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

1338

1365

drop table t1, t2;

1339

1366

1340

--echo #

1341

--echo # Equal operation under row and empty subquery

1342

--echo #

1367

1368

# Equal operation under row and empty subquery

1369

1343

1370

create table t1 (s1 int,s2 int);

1344

1371

insert into t1 values (20,15);

1345

1372

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

1346

1373

drop table t1;

1347

1374

1348

--echo #

1349

--echo # ALL/ANY with NULL

1350

--echo #

1375

1376

# ALL/ANY with NULL

1377

1351

1378

create table t1 (s1 int);

1352

1379

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

1353

1380

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

1354

1381

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

1355

1382

drop table t1;

1356

1383

1357

--echo #

1358

--echo # reference on changable fields from subquery

1359

--echo #

1384

1385

# reference on changable fields from subquery

1386

1360

1387

CREATE TABLE t1 (

1361

1388

Code char(3) NOT NULL default '',

1362

1389

Name char(52) NOT NULL default '',

1373

1400

HeadOfState char(60) default NULL,

1374

1401

Capital int default NULL,

1375

1402

Code2 char(2) NOT NULL default ''

1376

);

1403

) ENGINE=MyISAM;

1377

1404

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

1378

1405

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

1379

1406

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

1384

1411

Population < 200);

1385

1412

drop table t1;

1386

1413

1387

--echo #

1388

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

1389

--echo # non-existant field.

1390

--echo #

1391

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

1392

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

1393

--error ER_BAD_FIELD_ERROR

1414

1415

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

1416

# non-existant field.

1417

1418

create table t1 (a1 int);

1419

create table t2 (b1 int);

1420

--error 1054

1394

1421

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

1395

1422

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

1396

1423

drop table t1,t2;

1397

1424

1398

1425

1399

--echo #

1400

--echo # Comparison subquery with * and row

1401

--echo #

1402

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

1426

1427

# Comparison subquery with * and row

1428

1429

create table t1 (a integer, b integer);

1403

1430

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

1404

1431

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

1405

--echo # queries whih can be converted to IN

1432

# queries whih can be converted to IN

1406

1433

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

1407

1434

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

1408

1435

drop table t1;

1409

1436

1410

--echo #

1411

--echo # Comparison subquery and row with nested rows

1412

--echo #

1413

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

1414

--error ER_OPERAND_COLUMNS

1437

1438

# Comparison subquery and row with nested rows

1439

1440

create table t1 (a integer, b integer);

1441

-- error 1241

1415

1442

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

1416

--error ER_OPERAND_COLUMNS

1443

-- error 1241

1417

1444

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

1418

--error ER_OPERAND_COLUMNS

1445

-- error 1241

1419

1446

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

1420

1447

drop table t1;

1421

1448

1422

--echo #

1423

--echo # Forward reference detection

1424

--echo #

1449

1450

# Forward reference detection

1451

1425

1452

create table t1 (a integer);

1426

1453

insert into t1 values (1);

1427

--error ER_ILLEGAL_REFERENCE

1454

-- error 1247

1428

1455

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

1429

--error ER_ILLEGAL_REFERENCE

1456

-- error 1247

1430

1457

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

1431

1458

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

1432

--error ER_ILLEGAL_REFERENCE

1459

-- error 1247

1433

1460

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

1434

1461

drop table t1;

1435

1462

1436

--echo #

1437

--echo # Test for BUG#8218

1438

--echo #

1439

CREATE TEMPORARY TABLE t1 (

1463

1464

# Test for BUG#8218

1465

1466

CREATE TABLE t1 (

1440

1467

categoryId int NOT NULL,

1441

1468

courseId int NOT NULL,

1442

1469

startDate datetime NOT NULL,

1443

1470

endDate datetime NOT NULL,

1444

1471

createDate datetime NOT NULL,

1445

1472

modifyDate timestamp NOT NULL,

1446

attributes text NOT NULL)

1447

engine=myisam;

1448

1473

attributes text NOT NULL

1474

);

1449

1475

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

1450

1476

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

1451

1477

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

1456

1482

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

1457

1483

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

1458

1484

1459

CREATE TEMPORARY TABLE t2 (

1485

CREATE TABLE t2 (

1460

1486

userId int NOT NULL,

1461

1487

courseId int NOT NULL,

1462

date datetime NOT NULL)

1463

engine=myisam;

1464

1488

date datetime NOT NULL

1489

);

1465

1490

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

1466

1491

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

1467

1492

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

1470

1495

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

1471

1496

1472

1497

1473

CREATE TEMPORARY TABLE t3 (

1498

CREATE TABLE t3 (

1474

1499

groupId int NOT NULL,

1475

1500

parentId int NOT NULL,

1476

1501

startDate datetime NOT NULL,

1477

1502

endDate datetime NOT NULL,

1478

1503

createDate datetime NOT NULL,

1479

1504

modifyDate timestamp NOT NULL,

1480

ordering int)

1481

engine=myisam;

1482

1505

ordering int

1506

);

1483

1507

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

1484

1508

1485

CREATE TEMPORARY TABLE t4 (

1509

CREATE TABLE t4 (

1486

1510

id int NOT NULL,

1487

1511

groupTypeId int NOT NULL,

1488

1512

groupKey varchar(50) NOT NULL,

1490

1514

ordering int,

1491

1515

description text,

1492

1516

createDate datetime NOT NULL,

1493

modifyDate timestamp NOT NULL)

1494

engine=myisam;

1495

1517

modifyDate timestamp NOT NULL

1518

);

1496

1519

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

1497

1520

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

1498

1521

1499

CREATE TEMPORARY TABLE t5 (

1522

CREATE TABLE t5 (

1500

1523

userId int NOT NULL,

1501

1524

groupId int NOT NULL,

1502

1525

createDate datetime NOT NULL,

1503

modifyDate timestamp NOT NULL) ENGINE=MyISAM;

1504

1526

modifyDate timestamp NOT NULL

1527

);

1505

1528

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

1506

1529

1507

1530

select

1538

1561

1539

1562

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

1540

1563

1541

--echo #

1542

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

1543

--echo #

1544

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

1564

1565

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

1566

1567

create table t1 (a int);

1545

1568

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

1546

1569

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

1547

1570

drop table t1;

1548

1571

1549

--echo #

1550

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

1551

--echo #

1552

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

1572

1573

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

1574

1575

create table t1 (a int, b int);

1553

1576

insert into t1 values (1,2);

1554

--error ER_OPERAND_COLUMNS

1577

-- error 1241

1555

1578

select 1 = (select * from t1);

1556

--error ER_OPERAND_COLUMNS

1579

-- error 1241

1557

1580

select (select * from t1) = 1;

1558

--error ER_OPERAND_COLUMNS

1581

-- error 1241

1559

1582

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

1560

--error ER_OPERAND_COLUMNS

1583

-- error 1241

1561

1584

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

1562

--error ER_OPERAND_COLUMNS

1585

-- error 1241

1563

1586

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

1564

--error ER_OPERAND_COLUMNS

1587

-- error 1241

1565

1588

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

1566

1589

drop table t1;

1567

1590

1568

--echo #

1569

--echo # Item_int_with_ref check (BUG#10020)

1570

--echo #

1571

--echo #CREATE TABLE `t1` (

1572

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

1573

--echo # `sessionid` bigint default NULL,

1574

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

1575

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

1576

--echo # PRIMARY KEY (`itemid`)

1577

--echo #);

1578

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

1579

--echo #CREATE TABLE `t2` (

1580

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

1581

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

1582

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

1583

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

1584

--echo # PRIMARY KEY (`sessionid`)

1585

--echo #);

1586

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

1587

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

1588

--echo #drop tables t1,t2;

1591

1592

# Item_int_with_ref check (BUG#10020)

1593

1594

#CREATE TABLE `t1` (

1595

# `itemid` bigint NOT NULL auto_increment,

1596

# `sessionid` bigint default NULL,

1597

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

1598

# `data` text collate latin1_general_ci NOT NULL,

1599

# PRIMARY KEY (`itemid`)

1600

#);

1601

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

1602

#CREATE TABLE `t2` (

1603

# `sessionid` bigint NOT NULL auto_increment,

1604

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

1605

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

1606

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

1607

# PRIMARY KEY (`sessionid`)

1608

#);

1609

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

1610

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

1611

#drop tables t1,t2;

1589

1612

1590

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

1591

--echo # segfaults:

1592

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

1613

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

1614

# segfaults:

1615

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

1593

1616

insert into t1 values ('1');

1594

1617

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

1595

1618

drop table t1;

1596

1619

1597

--echo #

1598

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

1599

--echo #

1620

1621

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

1622

1600

1623

1601

1624

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

1602

1625

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

1625

1648

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

1626

1649

DROP TABLE t1,t2;

1627

1650

1628

--echo #

1629

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

1630

--echo #

1651

1652

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

1653

1631

1654

1632

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

1655

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

1633

1656

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

1634

1657

1635

1658

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

1636

1659

1637

1660

DROP TABLE t1;

1638

1661

1639

--echo #

1640

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

1641

--echo #

1662

1663

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

1664

1642

1665

1643

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

1644

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

1645

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

1666

CREATE TABLE t1 (a int);

1667

CREATE TABLE t2 (a int, b int);

1668

CREATE TABLE t3 (b int NOT NULL);

1646

1669

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

1647

1670

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

1648

1671

1654

1677

1655

1678

DROP TABLE t1,t2,t3;

1656

1679

1657

--echo #

1658

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

1659

--echo # return a wrong result.

1660

--echo #

1661

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

1662

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

1680

1681

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

1682

# return a wrong result.

1683

1684

CREATE TABLE t1 (f1 INT);

1685

CREATE TABLE t2 (f2 INT);

1663

1686

INSERT INTO t1 VALUES (1);

1664

1687

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

1665

1688

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

1668

1691

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

1669

1692

DROP TABLE t1, t2;

1670

1693

1671

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

1694

# BUG#20975 Wrong query results for subqueries within NOT

1672

1695

create table t1 (s1 char);

1673

1696

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

1674

1697

1685

1708

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

1686

1709

drop table t1;

1687

1710

1688

--echo #

1689

--echo # Bug #16255: Subquery in where

1690

--echo #

1711

1712

# Bug #16255: Subquery in where

1713

1691

1714

create table t1 (

1692

1715

retailerID varchar(8) NOT NULL,

1693

1716

statusID int NOT NULL,

1694

1717

changed datetime NOT NULL,

1695

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

1718

UNIQUE KEY retailerID (retailerID, statusID, changed)

1719

);

1696

1720

1697

1721

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

1698

1722

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

1707

1731

group by r2.retailerId);

1708

1732

drop table t1;

1709

1733

1710

--echo #

1711

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

1712

--echo # produces empty result

1713

--echo #

1734

1735

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

1736

# produces empty result

1737

1714

1738

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

1715

1739

insert into t1 values (10);

1716

1740

1717

1741

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

1718

1742

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

1719

1743

1720

--replace_column 9 #

1721

1744

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

1722

1745

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

1723

1746

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

1734

1757

1735

1758

drop table t1,t2;

1736

1759

1737

--echo #

1738

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

1739

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

1740

--echo #

1760

1761

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

1762

# an ALL/ANY quantified subquery in HAVING

1763

1741

1764

1742

CREATE TEMPORARY TABLE t1 (

1765

CREATE TABLE t1 (

1743

1766

field1 int NOT NULL,

1744

1767

field2 int NOT NULL,

1745

1768

field3 int NOT NULL,

1746

PRIMARY KEY (field1,field2,field3))

1747

ENGINE=MyISAM;

1748

1749

CREATE TEMPORARY TABLE t2 (

1769

PRIMARY KEY (field1,field2,field3)

1770

);

1771

CREATE TABLE t2 (

1750

1772

fieldA int NOT NULL,

1751

1773

fieldB int NOT NULL,

1752

PRIMARY KEY (fieldA,fieldB))

1753

ENGINE=MyISAM;

1774

PRIMARY KEY (fieldA,fieldB)

1775

);

1754

1776

1755

1777

INSERT INTO t1 VALUES

1756

1778

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

1772

1794

1773

1795

DROP TABLE t1, t2;

1774

1796

1775

--echo #

1776

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

1777

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

1778

--echo #

1797

1798

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

1799

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

1800

1779

1801

1780

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

1802

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

1781

1803

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

1782

1804

INSERT INTO t1 VALUES (NULL);

1783

1805

1784

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

1806

CREATE TABLE t2(a int);

1785

1807

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

1786

1808

1787

1809

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

1789

1811

1790

1812

DROP TABLE t1,t2;

1791

1813

1792

--echo #

1793

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

1794

--echo #

1814

1815

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

1816

1795

1817

CREATE TABLE t1 (a DATETIME);

1796

1818

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

1797

1819

1798

CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT

1820

CREATE TABLE t2 AS SELECT

1799

1821

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

1800

1822

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

1801

1823

SHOW CREATE TABLE t2;

1802

1824

1803

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

1825

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

1804

1826

SHOW CREATE TABLE t3;

1805

1827

1806

1828

DROP TABLE t1,t2,t3;

1807

1829

1808

--echo #

1809

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

1810

--echo # that return more than one row

1811

--echo #

1830

1831

# Bug 24653: sorting by expressions containing subselects

1832

# that return more than one row

1833

1812

1834

1813

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

1835

CREATE TABLE t1 (a int);

1814

1836

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

1815

1837

1816

1838

CREATE TABLE t2 (b int, c int);

1818

1840

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

1819

1841

1820

1842

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

1821

--error ER_SUBQUERY_NO_1_ROW

1843

--error 1242

1822

1844

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

1823

1845

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

1824

--error ER_SUBQUERY_NO_1_ROW

1846

--error 1242

1825

1847

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

1826

1848

1827

1849

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

1828

--error ER_SUBQUERY_NO_1_ROW

1850

--error 1242

1829

1851

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

1830

1852

1831

1853

1832

1854

SELECT a FROM t1 GROUP BY a

1833

1855

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

1834

1856

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

1835

--error ER_SUBQUERY_NO_1_ROW

1857

--error 1242

1836

1858

SELECT a FROM t1 GROUP BY a

1837

1859

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

1838

1860

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

1840

1862

SELECT a FROM t1 GROUP BY a

1841

1863

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

1842

1864

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

1843

--error ER_SUBQUERY_NO_1_ROW

1865

--error 1242

1844

1866

SELECT a FROM t1 GROUP BY a

1845

1867

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

1846

1868

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

1848

1870

SELECT a FROM t1

1849

1871

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

1850

1872

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

1851

--error ER_SUBQUERY_NO_1_ROW

1873

--error 1242

1852

1874

SELECT a FROM t1

1853

1875

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

1854

1876

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

1856

1878

SELECT a FROM t1

1857

1879

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

1858

1880

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

1859

--error ER_SUBQUERY_NO_1_ROW

1881

--error 1242

1860

1882

SELECT a FROM t1

1861

1883

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

1862

1884

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

1863

1885

1864

1886

DROP TABLE t1,t2;

1865

1887

1866

--echo # End of 4.1 tests

1888

# End of 4.1 tests

1867

1889

1868

--echo #

1869

--echo #decimal-related tests

1870

--echo #

1890

1891

#decimal-related tests

1892

1871

1893

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

1872

1894

insert into t1 values(1.1);

1873

1895

insert into t1 values(2.2);

1876

1898

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

1877

1899

drop table t1;

1878

1900

1879

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

1901

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

1880

1902

insert into t1 values(1.1);

1881

1903

select 1.1 * exists(select * from t1);

1882

1904

drop table t1;

1883

1905

1884

CREATE TEMPORARY TABLE t1 (

1906

CREATE TABLE t1 (

1885

1907

grp int default NULL,

1886

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

1908

a decimal(10,2) default NULL);

1887

1909

1888

1910

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

1889

1911

select * from t1;

1890

1912

select min(a) from t1 group by grp;

1891

1913

drop table t1;

1892

1914

1893

--echo #

1894

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

1895

--echo #

1915

1916

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

1917

1896

1918

1897

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

1919

CREATE table t1 ( c1 integer );

1898

1920

INSERT INTO t1 VALUES ( 1 );

1899

1921

INSERT INTO t1 VALUES ( 2 );

1900

1922

INSERT INTO t1 VALUES ( 3 );

1911

1933

1912

1934

DROP TABLE t1,t2;

1913

1935

1914

--echo #

1915

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

1916

--echo #

1917

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

1936

1937

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

1938

1939

CREATE TABLE t1 ( c1 integer );

1918

1940

INSERT INTO t1 VALUES ( 1 );

1919

1941

INSERT INTO t1 VALUES ( 2 );

1920

1942

INSERT INTO t1 VALUES ( 3 );

1921

1943

INSERT INTO t1 VALUES ( 6 );

1922

1944

1923

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

1945

CREATE TABLE t2 ( c2 integer );

1924

1946

INSERT INTO t2 VALUES ( 1 );

1925

1947

INSERT INTO t2 VALUES ( 4 );

1926

1948

INSERT INTO t2 VALUES ( 5 );

1927

1949

INSERT INTO t2 VALUES ( 6 );

1928

1950

1929

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

1951

CREATE TABLE t3 ( c3 integer );

1930

1952

INSERT INTO t3 VALUES ( 7 );

1931

1953

INSERT INTO t3 VALUES ( 8 );

1932

1954

1935

1957

1936

1958

DROP TABLE t1,t2,t3;

1937

1959

1938

--echo #

1939

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

1940

--echo # fields) (BUG#6384)

1941

--echo #

1942

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

1943

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

1960

1961

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

1962

# fields) (BUG#6384)

1963

1964

CREATE TABLE t1 (EMPNUM CHAR(3));

1965

CREATE TABLE t2 (EMPNUM CHAR(3) );

1944

1966

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

1945

1967

INSERT INTO t2 VALUES ('E1');

1946

1968

DELETE FROM t1

1951

1973

select * from t1;

1952

1974

DROP TABLE t1,t2;

1953

1975

1954

--echo #

1955

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

1956

--echo #

1976

1977

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

1978

1957

1979

1958

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

1980

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);

1959

1981

INSERT INTO t1 VALUES (1, 1);

1960

CREATE TEMPORARY TABLE t2 (select_id BIGINT, values_id BIGINT,

1961

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

1982

CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,

1983

PRIMARY KEY(select_id,values_id));

1962

1984

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

1963

1985

1964

1986

SELECT values_id FROM t1

1973

1995

1974

1996

DROP TABLE t1, t2;

1975

1997

1976

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

1977

--echo # segfaults:

1978

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

1998

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

1999

# segfaults:

2000

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

1979

2001

insert into t1 values ('1');

1980

2002

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

1981

2003

drop table t1;

1982

2004

1983

--echo #

1984

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

1985

--echo #

2005

2006

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

2007

1986

2008

1987

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

1988

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

1989

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

2009

CREATE TABLE t1 (a int, b int);

2010

CREATE TABLE t2 (c int, d int);

2011

CREATE TABLE t3 (e int);

1990

2012

1991

2013

INSERT INTO t1 VALUES

1992

2014

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

2043

2065

HAVING t1.a > ALL(SELECT t2.c FROM t2

2044

2066

WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e

2045

2067

HAVING SUM(t1.a+t2.c) < t3.e/4));

2046

--error ER_INVALID_GROUP_FUNC_USE

2068

-- error 1111

2047

2069

SELECT t1.a FROM t1 GROUP BY t1.a

2048

2070

HAVING t1.a > ALL(SELECT t2.c FROM t2

2049

2071

WHERE EXISTS(SELECT t3.e FROM t3

2050

2072

WHERE SUM(t1.a+t2.c) < t3.e/4));

2051

--error ER_INVALID_GROUP_FUNC_USE

2073

-- error 1111

2052

2074

SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;

2053

2075

2054

2076

SELECT t1.a FROM t1 GROUP BY t1.a

2064

2086

2065

2087

DROP TABLE t1,t2,t3;

2066

2088

2067

--echo #

2068

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

2069

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

2089

2090

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

2091

# when an index is defined on the grouping field

2070

2092

2071

2093

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

2072

2094

INSERT INTO t1 VALUES

2074

2096

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

2075

2097

2076

2098

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

2077

--replace_column 9 #

2078

2099

EXPLAIN

2079

2100

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

2080

2101

2081

2102

ALTER TABLE t1 ADD INDEX(a);

2082

2103

2083

2104

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

2084

--replace_column 9 #

2085

2105

EXPLAIN

2086

2106

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

2087

2107

2088

2108

DROP TABLE t1;

2089

2109

2090

--echo #

2091

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

2092

--echo #

2110

2111

# Bug#17366: Unchecked Item_int results in server crash

2112

2093

2113

create table t1( f1 int,f2 int);

2094

2114

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

2095

2115

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

2096

2116

drop table t1;

2097

2117

2098

--echo #

2099

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

2100

--echo #

2118

2119

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

2120

2101

2121

2102

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

2122

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

2103

2123

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

2104

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

2124

create table t2 (a int, b int);

2105

2125

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

2106

2126

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

2107

2127

drop table t1, t2;

2108

2128

2109

--echo #

2110

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

2111

--echo #

2129

2130

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

2131

2112

2132

create table t1 (i int, j bigint);

2113

2133

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

2114

2134

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

2115

2135

drop table t1;

2116

2136

2117

--echo #

2118

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

2119

--echo #

2120

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

2137

2138

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

2139

2140

CREATE TABLE t1 (i BIGINT);

2121

2141

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

2122

2142

INSERT INTO t1 VALUES (1);

2123

2143

2124

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

2144

CREATE TABLE t2 (i BIGINT);

2125

2145

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

2126

2146

INSERT INTO t2 VALUES (1);

2127

2147

2137

2157

DROP TABLE t1;

2138

2158

DROP TABLE t2;

2139

2159

2140

--echo #

2141

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

2142

--echo #

2160

2161

# Bug#20519: subselect with LIMIT M, N

2162

2143

2163

2144

CREATE TEMPORARY TABLE t1 (

2164

CREATE TABLE t1 (

2145

2165

id bigint NOT NULL auto_increment,

2146

2166

name varchar(255) NOT NULL,

2147

PRIMARY KEY (id))

2148

ENGINE=MyISAM;

2149

2167

PRIMARY KEY (id)

2168

);

2150

2169

INSERT INTO t1 VALUES

2151

2170

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

2152

2171

2154

2173

id bigint NOT NULL auto_increment,

2155

2174

mid bigint NOT NULL,

2156

2175

date date NOT NULL,

2157

PRIMARY KEY (id));

2158

2176

PRIMARY KEY (id)

2177

);

2159

2178

INSERT INTO t2 VALUES

2160

2179

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

2161

2180

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

2178

2197

FROM t1;

2179

2198

DROP TABLE t1,t2;

2180

2199

2181

--echo #

2182

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

2183

--echo #

2200

2201

# Bug#20869: subselect with range access by DESC

2202

2184

2203

2185

2204

CREATE TABLE t1 (

2186

2205

i1 int NOT NULL default '0',

2187

2206

i2 int NOT NULL default '0',

2188

t datetime,

2189

PRIMARY KEY (i1,i2,t));

2190

2207

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

2208

PRIMARY KEY (i1,i2,t)

2209

);

2191

2210

INSERT INTO t1 VALUES

2192

2211

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

2193

2212

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

2200

2219

i1 int NOT NULL default '0',

2201

2220

i2 int NOT NULL default '0',

2202

2221

t datetime default NULL,

2203

PRIMARY KEY (i1));

2204

2222

PRIMARY KEY (i1)

2223

);

2205

2224

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

2206

2225

2207

2226

EXPLAIN

2216

2235

2217

2236

DROP TABLE t1, t2;

2218

2237

2219

--echo #

2220

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

2221

--echo # statement

2222

--echo #

2238

2239

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

2240

# statement

2241

2223

2242

CREATE TABLE t1 (i INT);

2224

2243

2225

2244

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

2229

2248

(SELECT i FROM t1)

2230

2249

);

2231

2250

2232

--echo #TODO:not supported

2251

#TODO:not supported

2233

2252

--error ER_PARSE_ERROR

2234

2253

SELECT * FROM t1

2235

2254

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

2236

2255

2237

--echo #TODO:not supported

2238

--error ER_PARSE_ERROR

2256

#TODO:not supported

2257

--error 1064

2239

2258

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

2240

2259

from t1;

2241

2260

2242

--replace_column 9 #

2243

2261

explain select * from t1 where not exists

2244

2262

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

2245

2263

2246

2264

DROP TABLE t1;

2247

2265

2248

--echo #

2249

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

2250

--echo # wrong results

2266

2267

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

2268

# wrong results

2251

2269

CREATE TABLE t1 (a INT, b INT);

2252

2270

CREATE TABLE t2 (a INT);

2253

2271

INSERT INTO t2 values (1);

2267

2285

GROUP BY t2.a;

2268

2286

DROP TABLE t1,t2;

2269

2287

2270

--echo #

2271

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

2272

--echo # slow with big sort_buffer_size

2273

--echo #

2288

2289

# Bug #21727: Correlated subquery that requires filesort:

2290

# slow with big sort_buffer_size

2291

2274

2292

2275

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

2276

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

2277

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

2293

CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));

2294

CREATE TABLE t2 (x int auto_increment, y int, z int,

2295

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));

2278

2296

disable_query_log;

2279

2297

set autocommit=0;

2280

2298

begin;

2307

2325

2308

2326

DROP TABLE t2,t1;

2309

2327

2310

--echo #

2311

--echo # Bug #25219: EXIST subquery with UNION over a mix of

2312

--echo # correlated and uncorrelated selects

2313

--echo #

2328

2329

# Bug #25219: EXIST subquery with UNION over a mix of

2330

# correlated and uncorrelated selects

2331

2314

2332

2315

2333

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);

2316

2334

CREATE TABLE t2 (c int);

2331

2349

SELECT c from t2 WHERE c=t1.c);

2332

2350

2333

2351

INSERT INTO t2 VALUES (2);

2334

CREATE TEMPORARY TABLE t3 (c int) ENGINE=MyISAM;

2352

CREATE TABLE t3 (c int);

2335

2353

INSERT INTO t3 VALUES (1);

2336

2354

2337

2355

SELECT * FROM t1

2341

2359

2342

2360

DROP TABLE t1,t2,t3;

2343

2361

2344

--echo #

2345

--echo # Bug#21904 (parser problem when using IN with a double "(())")

2346

--echo #

2362

2363

# Bug#21904 (parser problem when using IN with a double "(())")

2364

2347

2365

2348

2366

--disable_warnings

2349

2367

DROP TABLE IF EXISTS t1;

2351

2369

DROP TABLE IF EXISTS t1xt2;

2352

2370

--enable_warnings

2353

2371

2354

CREATE TEMPORARY TABLE t1 (

2372

CREATE TABLE t1 (

2355

2373

id_1 int NOT NULL,

2356

t varchar(4) DEFAULT NULL)

2357

ENGINE=MyISAM;

2374

t varchar(4) DEFAULT NULL

2375

);

2358

2376

2359

CREATE TEMPORARY TABLE t2 (

2377

CREATE TABLE t2 (

2360

2378

id_2 int NOT NULL,

2361

t varchar(4) DEFAULT NULL)

2362

ENGINE=MyISAM;

2379

t varchar(4) DEFAULT NULL

2380

);

2363

2381

2364

CREATE TEMPORARY TABLE t1xt2 (

2382

CREATE TABLE t1xt2 (

2365

2383

id_1 int NOT NULL,

2366

id_2 int NOT NULL)

2367

ENGINE=MyISAM;

2384

id_2 int NOT NULL

2385

);

2368

2386

2369

2387

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

2370

2388

2372

2390

2373

2391

INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);

2374

2392

2375

--echo # subselect returns 0 rows

2393

# subselect returns 0 rows

2376

2394

2377

2395

SELECT DISTINCT t1.id_1 FROM t1 WHERE

2378

2396

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

2394

2412

2395

2413

insert INTO t1xt2 VALUES (1, 12);

2396

2414

2397

--echo # subselect returns 1 row

2415

# subselect returns 1 row

2398

2416

2399

2417

SELECT DISTINCT t1.id_1 FROM t1 WHERE

2400

2418

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

2416

2434

2417

2435

insert INTO t1xt2 VALUES (2, 12);

2418

2436

2419

--echo # subselect returns more than 1 row

2437

# subselect returns more than 1 row

2420

2438

2421

2439

SELECT DISTINCT t1.id_1 FROM t1 WHERE

2422

2440

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

2440

2458

DROP TABLE t2;

2441

2459

DROP TABLE t1xt2;

2442

2460

2443

--echo #

2444

--echo # Bug #26728: derived table with concatanation of literals in select list

2445

--echo #

2461

2462

# Bug #26728: derived table with concatanation of literals in select list

2463

2446

2464

2447

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

2465

CREATE TABLE t1 (a int);

2448

2466

INSERT INTO t1 VALUES (3), (1), (2);

2449

2467

2450

2468

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;

2452

2470

2453

2471

DROP table t1;

2454

2472

2455

--echo #

2456

--echo # Bug #27257: COUNT(*) aggregated in outer query

2457

--echo #

2473

2474

# Bug #27257: COUNT(*) aggregated in outer query

2475

2458

2476

2459

CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;

2460

CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;

2477

CREATE TABLE t1 (a int, b int);

2478

CREATE TABLE t2 (m int, n int);

2461

2479

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

2462

2480

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

2463

2481

2475

2493

2476

2494

DROP TABLE t1,t2;

2477

2495

2478

--echo #

2479

--echo # Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument

2480

--echo #

2496

2497

# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument

2498

2481

2499

2482

CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;

2483

CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;

2500

CREATE TABLE t1 (a int, b int);

2501

CREATE TABLE t2 (m int, n int);

2484

2502

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

2485

2503

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

2486

2504

2494

2512

2495

2513

DROP table t1,t2;

2496

2514

2497

--echo #

2498

--echo # Bug#27321: Wrong subquery result in a grouping select

2499

--echo #

2515

2516

# Bug#27321: Wrong subquery result in a grouping select

2517

2500

2518

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));

2501

2519

INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),

2502

2520

(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),

2531

2549

2532

2550

DROP TABLE t1;

2533

2551

2534

--echo #

2535

--echo # Bug #27363: nested aggregates in outer, subquery / sum(select

2536

--echo # count(outer))

2537

--echo #

2552

2553

# Bug #27363: nested aggregates in outer, subquery / sum(select

2554

# count(outer))

2555

2538

2556

CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);

2539

CREATE TEMPORARY TABLE t2 (x INT) ENGINE=MyISAM; INSERT INTO t1 values (1000),(1001),(1002);

2557

CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);

2540

2558

2541

2559

--error ER_INVALID_GROUP_FUNC_USE

2542

2560

SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;

2555

2573

2556

2574

DROP TABLE t1,t2;

2557

2575

2558

--echo #

2559

--echo # Bug #27807: Server crash when executing subquery with EXPLAIN

2560

--echo #

2576

2577

# Bug #27807: Server crash when executing subquery with EXPLAIN

2578

2561

2579

CREATE TABLE t1 (a int, b int, KEY (a));

2562

2580

INSERT INTO t1 VALUES (1,1),(2,1);

2563

2581

EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);

2564

2582

DROP TABLE t1;

2565

2583

2566

--echo #

2567

--echo # Bug #28377: grouping query with a correlated subquery in WHERE condition

2568

--echo #

2584

2585

# Bug #28377: grouping query with a correlated subquery in WHERE condition

2586

2569

2587

2570

2588

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));

2571

2589

INSERT INTO t1 VALUES

2572

2590

(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');

2573

CREATE TEMPORARY TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;

2591

CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));

2574

2592

INSERT INTO t2 VALUES (7), (5), (1), (3);

2575

2593

2576

2594

SELECT id, st FROM t1

2587

2605

2588

2606

DROP TABLE t1,t2;

2589

2607

2590

--echo #

2591

--echo # Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table

2592

--echo # over a grouping subselect

2593

--echo #

2608

2609

# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table

2610

# over a grouping subselect

2611

2594

2612

2595

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

2613

CREATE TABLE t1 (a int);

2596

2614

2597

2615

INSERT INTO t1 VALUES (1), (2);

2598

2616

2601

2619

2602

2620

DROP TABLE t1;

2603

2621

2604

--echo #

2605

--echo # Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1

2606

--echo #

2622

2623

# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1

2624

2607

2625

2608

2626

CREATE TABLE t1 (

2609

2627

a varchar(255) default NULL,

2610

2628

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

2611

INDEX idx(a,b));

2612

2629

INDEX idx(a,b)

2630

);

2613

2631

CREATE TABLE t2 (

2614

a varchar(255) default NULL);

2632

a varchar(255) default NULL

2633

);

2615

2634

2616

2635

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');

2617

2636

INSERT INTO t1 SELECT * FROM t1;

2633

2652

DROP TABLE t1,t2;

2634

2653

2635

2654

2636

--echo #

2637

--echo # Bug #27333: subquery grouped for aggregate of outer query / no aggregate

2638

--echo # of subquery

2639

--echo #

2640

CREATE TEMPORARY TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;

2641

CREATE TEMPORARY TABLE t2 (x INTEGER) ENGINE=MyISAM;

2655

2656

# Bug #27333: subquery grouped for aggregate of outer query / no aggregate

2657

# of subquery

2658

2659

CREATE TABLE t1 (a INTEGER, b INTEGER);

2660

CREATE TABLE t2 (x INTEGER);

2642

2661

INSERT INTO t1 VALUES (1,11), (2,22), (2,22);

2643

2662

INSERT INTO t2 VALUES (1), (2);

2644

2663

2645

--echo # wasn't failing, but should

2664

# wasn't failing, but should

2646

2665

--error ER_SUBQUERY_NO_1_ROW

2647

2666

SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;

2648

2667

2649

--echo # fails as it should

2668

# fails as it should

2650

2669

--error ER_SUBQUERY_NO_1_ROW

2651

2670

SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;

2652

2671

2653

2672

SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;

2654

2673

DROP TABLE t1,t2;

2655

2674

2656

--echo # second test case from 27333

2675

# second test case from 27333

2657

2676

CREATE TABLE t1 (a INT, b INT);

2658

2677

INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);

2659

2678

2660

--echo # returns no rows, when it should

2679

# returns no rows, when it should

2661

2680

SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1

2662

2681

AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)

2663

2682

GROUP BY a1.a;

2664

2683

DROP TABLE t1;

2665

2684

2666

--echo #test cases from 29297

2667

CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;

2668

CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MyISAM;

2685

#test cases from 29297

2686

CREATE TABLE t1 (a INT);

2687

CREATE TABLE t2 (a INT);

2669

2688

INSERT INTO t1 VALUES (1),(2);

2670

2689

INSERT INTO t2 VALUES (1),(2);

2671

2690

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;

2674

2693

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;

2675

2694

DROP TABLE t1,t2;

2676

2695

2677

--echo #

2678

--echo # Bug #31884: Assertion + crash in subquery in the SELECT clause.

2679

--echo #

2696

2697

# Bug #31884: Assertion + crash in subquery in the SELECT clause.

2698

2680

2699

2681

CREATE TEMPORARY TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;

2682

CREATE TEMPORARY TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;

2700

CREATE TABLE t1 (a1 INT, a2 INT);

2701

CREATE TABLE t2 (b1 INT, b2 INT);

2683

2702

2684

2703

INSERT INTO t1 VALUES (100, 200);

2685

2704

INSERT INTO t1 VALUES (101, 201);

2689

2708

SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;

2690

2709

DROP TABLE t1, t2;

2691

2710

2692

--echo #

2693

--echo # Bug #30788: Inconsistent retrieval of char/varchar

2694

--echo #

2711

2712

# Bug #30788: Inconsistent retrieval of char/varchar

2713

2695

2714

2696

2715

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));

2697

2716

INSERT INTO t1 VALUES ('a', 'aa');

2699

2718

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

2700

2719

CREATE INDEX I1 ON t1 (a);

2701

2720

CREATE INDEX I2 ON t1 (b);

2702

--replace_column 9 #

2703

2721

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

2704

2722

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

2705

2723

2707

2725

INSERT INTO t2 SELECT * FROM t1;

2708

2726

CREATE INDEX I1 ON t2 (a);

2709

2727

CREATE INDEX I2 ON t2 (b);

2710

--replace_column 9 #

2711

2728

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

2712

2729

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

2713

--replace_column 9 #

2714

2730

EXPLAIN

2715

2731

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

2716

2732

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

2717

2733

2718

2734

DROP TABLE t1,t2;

2719

2735

2720

--echo #

2721

--echo # Bug #32400: Complex SELECT query returns correct result only on some

2722

--echo # occasions

2723

--echo #

2736

2737

# Bug #32400: Complex SELECT query returns correct result only on some

2738

# occasions

2739

2724

2740

2725

2741

CREATE TABLE t1(a INT, b INT);

2726

2742

INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);

2736

2752

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

2737

2753

GROUP BY a;

2738

2754

2739

--replace_column 9 #

2740

2755

EXPLAIN

2741

2756

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

2742

2757

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

2749

2764

DROP TABLE t1;

2750

2765

2751

2766

2752

--echo #

2753

--echo # Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122

2754

--echo #

2767

2768

# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122

2769

2755

2770

2756

CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;

2771

CREATE TABLE t1 (a INT);

2757

2772

CREATE TABLE t2 (a INT);

2758

2773

2759

2774

INSERT INTO t1 VALUES (1),(2);

2764

2779

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

2765

2780

2766

2781

2767

--replace_column 9 #

2768

2782

EXPLAIN EXTENDED

2769

2783

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION

2770

2784

(SELECT 1 FROM t2 WHERE t1.a = t2.a));

2771

2785

2772

2786

DROP TABLE t1,t2;

2773

2787

2774

--echo #

2775

--echo # BUG#33794 "MySQL crashes executing specific query on specific dump"

2776

--echo #

2777

CREATE TEMPORARY TABLE t4 (

2788

2789

# BUG#33794 "MySQL crashes executing specific query on specific dump"

2790

2791

CREATE TABLE t4 (

2778

2792

f7 varchar(32) collate utf8_bin NOT NULL default '',

2779

2793

f10 varchar(32) collate utf8_bin default NULL,

2780

PRIMARY KEY (f7))

2781

ENGINE=MyISAM;

2782

2794

PRIMARY KEY (f7)

2795

);

2783

2796

INSERT INTO t4 VALUES(1,1), (2,null);

2784

2797

2785

2798

CREATE TABLE t2 (

2787

2800

f2 varchar(50) collate utf8_bin default NULL,

2788

2801

f3 varchar(10) collate utf8_bin default NULL,

2789

2802

PRIMARY KEY (f4),

2790

UNIQUE KEY uk1 (f2));

2791

2803

UNIQUE KEY uk1 (f2)

2804

);

2792

2805

INSERT INTO t2 VALUES(1,1,null), (2,2,null);

2793

2806

2794

CREATE TEMPORARY TABLE t1 (

2807

CREATE TABLE t1 (

2795

2808

f8 varchar(32) collate utf8_bin NOT NULL default '',

2796

2809

f1 varchar(10) collate utf8_bin default NULL,

2797

2810

f9 varchar(32) collate utf8_bin default NULL,

2798

PRIMARY KEY (f8))

2799

ENGINE=MyISAM;

2800

2811

PRIMARY KEY (f8)

2812

);

2801

2813

INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);

2802

2814

2803

CREATE TEMPORARY TABLE t3 (

2815

CREATE TABLE t3 (

2804

2816

f6 varchar(32) collate utf8_bin NOT NULL default '',

2805

2817

f5 varchar(50) collate utf8_bin default NULL,

2806

PRIMARY KEY (f6))

2807

ENGINE=MyISAM;

2808

2818

PRIMARY KEY (f6)

2819

);

2809

2820

INSERT INTO t3 VALUES (1,null), (2,null);

2810

2821

2811

2822

SELECT

2830

2841

2831

2842

--echo End of 5.0 tests.

2832

2843

2833

--echo #

2834

--echo # Test [NOT] IN truth table (both as top-level and general predicate).

2835

--echo #

2844

2845

# Test [NOT] IN truth table (both as top-level and general predicate).

2846

2836

2847

2837

create temporary table t_out (subcase char(3),

2838

a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;

2848

create table t_out (subcase char(3),

2849

a1 char(2), b1 char(2), c1 char(2));

2839

2850

create table t_in (a2 char(2), b2 char(2), c2 char(2));

2840

2851

2841

2852

insert into t_out values ('A.1','2a', NULL, '2a');

2842

--echo #------------------------- A.2 - impossible

2853

#------------------------- A.2 - impossible

2843

2854

insert into t_out values ('A.3', '2a', NULL, '2a');

2844

2855

insert into t_out values ('A.4', '2a', NULL, 'xx');

2845

2856

insert into t_out values ('B.1', '2a', '2a', '2a');

2927

2938

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

2928

2939

where subcase = 'A.3' and

2929

2940

NOT((a1, b1, c1) IN (select * from t_in));

2930

--echo # test non-top level result indirectly

2941

# test non-top level result indirectly

2931

2942

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

2932

2943

where subcase = 'A.3' and

2933

2944

((a1, b1, c1) IN (select * from t_in)) is NULL and

2984

2995

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

2985

2996

where subcase = 'B.3' and

2986

2997

NOT((a1, b1, c1) IN (select * from t_in));

2987

--echo # test non-top level result indirectly

2998

# test non-top level result indirectly

2988

2999

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

2989

3000

where subcase = 'B.3' and

2990

3001

((a1, b1, c1) IN (select * from t_in)) is NULL and

3007

3018

drop table t_in;

3008

3019

3009

3020

3010

--echo #

3011

--echo # Bug#20835 (literal string with =any values)

3012

--echo #

3021

3022

# Bug#20835 (literal string with =any values)

3023

3013

3024

CREATE TABLE t1 (s1 char(1));

3014

3025

INSERT INTO t1 VALUES ('a');

3015

SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);

3026

SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);

3016

3027

DROP TABLE t1;

3017

3028

3018

--echo #

3019

--echo # Bug#33204: INTO is allowed in subselect, causing inconsistent results

3020

--echo #

3029

3030

# Bug#33204: INTO is allowed in subselect, causing inconsistent results

3031

3021

3032

CREATE TABLE t1( a INT );

3022

3033

INSERT INTO t1 VALUES (1),(2);

3023

3034

3070

3081

SELECT a FROM t1 WHERE a = 2

3071

3082

) t1a;

3072

3083

3073

--echo # This was not allowed previously. Possibly, it should be allowed on the future.

3074

--echo # For now, the intent is to keep the fix as non-intrusive as possible.

3084

# This was not allowed previously. Possibly, it should be allowed on the future.

3085

# For now, the intent is to keep the fix as non-intrusive as possible.

3075

3086

--error ER_PARSE_ERROR

3076

3087

SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);

3077

3088

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;

3102

3113

SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;

3103

3114

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;

3104

3115

3105

--echo # Test of rule

3106

--echo # table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias

3107

--echo # UNION should not be allowed inside the parentheses, nor should

3108

--echo # aliases after.

3109

--echo #

3116

# Test of rule

3117

# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias

3118

# UNION should not be allowed inside the parentheses, nor should

3119

# aliases after.

3120

3110

3121

SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;

3111

3122

--error ER_PARSE_ERROR

3112

3123

SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;

3119

3130

--error ER_PARSE_ERROR

3120

3131

SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;

3121

3132

3122

--error ER_CARTESIAN_JOIN_ATTEMPTED

3123

3133

SELECT * FROM t1 JOIN (t1 t1a) ON 1;

3124

--error ER_CARTESIAN_JOIN_ATTEMPTED

3125

3134

SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;

3126

3135

3127

3136

SELECT * FROM (t1 t1a);

3133

3142

SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;

3134

3143

SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;

3135

3144

3136

--echo # For the join, TABLE_LIST::select_lex == NULL

3137

--echo # Check that we handle this.

3145

# For the join, TABLE_LIST::select_lex == NULL

3146

# Check that we handle this.

3138

3147

--error ER_PARSE_ERROR

3139

3148

SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;

3140

3149

3179

3188

--error ER_PARSE_ERROR

3180

3189

SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

3181

3190

3182

--echo # Make sure context is popped when we leave the nested select

3191

# Make sure context is popped when we leave the nested select

3183

3192

SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;

3184

3193

SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;

3185

3194

3186

--echo # Make sure we have feature F561 (see .yy file)

3195

# Make sure we have feature F561 (see .yy file)

3187

3196

SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);

3188

3197

3189

--echo # Make sure the parser does not allow nested UNIONs anywhere

3198

# Make sure the parser does not allow nested UNIONs anywhere

3190

3199

3191

3200

--error ER_PARSE_ERROR

3192

3201

SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );

Older »