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

184

id select_type table type possible_keys key key_len ref rows filtered Extra

185

1 PRIMARY t2 ALL NULL NULL NULL NULL # 100.00 Using where

186

2 SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using filesort

187

3 UNION t4 ALL NULL NULL NULL NULL # 100.00 Using where

188

4 SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00

189

NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL # NULL

190

Warnings:

191

Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` AS `a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) AS `max(t2.a)*4` from `test`.`t2`)) order by `a`)

192

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

193

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

194

3 1

195

7 2

196

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

197

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

198

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

199

7 2

200

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

201

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

202

id select_type table type possible_keys key key_len ref rows filtered Extra

203

1 PRIMARY <derived3> system NULL NULL NULL NULL # 100.00

204

3 DERIVED t2 ALL NULL NULL NULL NULL # 100.00 Using where

205

2 SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using where; Using filesort

206

Warnings:

207

Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`

208

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

209

210

211

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

212

213

214

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

215

216

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

217

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

218

8 7.5000

219

8 4.5000

220

9 7.5000

221

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

222

id select_type table type possible_keys key key_len ref rows filtered Extra

223

1 PRIMARY t4 ALL NULL NULL NULL NULL # 100.00

224

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00

225

3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using where

226

Warnings:

227

Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1

228

Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`

229

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

230

231

232

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

233

234

235

236

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

237

238

239

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

240

241

242

243

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

244

245

246

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

247

248

249

250

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

251

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

252

253

254

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

255

256

257

258

insert into t2 values (100, 5);

259

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

260

261

262

263

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

264

265

266

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

267

268

269

270

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

271

id select_type table type possible_keys key key_len ref rows filtered Extra

272

1 PRIMARY t3 ALL NULL NULL NULL NULL # 100.00 Using where

273

2 SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00

274

Warnings:

275

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))

276

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

277

278

279

delete from t2 where a=100;

280

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

281

ERROR 21000: Operand should contain 1 column(s)

282

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

283

ERROR 21000: Operand should contain 1 column(s)

284

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

285

# empty set

286

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

287

b ma

288

insert into t2 values (2,10);

289

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

290

b ma

291

10 1

292

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

293

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

294

b ma

295

7 12

296

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

297

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

298

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

299

NULL 1

300

2 2

301

insert into t5 values (5);

302

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

303

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

304

NULL 1

305

2 2

306

insert into t5 values (2);

307

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

308

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

309

NULL 1

310

2 2

311

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;

312

id select_type table type possible_keys key key_len ref rows filtered Extra

313

1 PRIMARY t2 ALL NULL NULL NULL NULL # 100.00

314

2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00 Using where

315

3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL # 100.00 Using where

316

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL

317

Warnings:

318

Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1

319

Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1

320

Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`

321

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

322

ERROR 21000: Subquery returns more than 1 row

323

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

324

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

325

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

326

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

327

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

328

patient_uq clinic_uq

329

1 1

330

1 2

331

2 2

332

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

333

id select_type table type possible_keys key key_len ref rows filtered Extra

334

1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where

335

2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index

336

Warnings:

337

Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1

338

Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))

339

# not unique fields

340

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

341

ERROR 23000: Column 'a' in field list is ambiguous

342

# different tipes & group functions

343

drop table t1,t2,t3;

344

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

345

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

346

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

347

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

348

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

349

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

350

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

351

a b

352

W 1732-02-22

353

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

354

a b

355

W 1

356

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

357

a b

358

W a

359

CREATE TABLE `t8` (

360

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

361

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

362

PRIMARY KEY (`pseudo`),

363

UNIQUE KEY `email` (`email`)

364

);

365

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

366

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

367

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

368

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

369

id select_type table type possible_keys key key_len ref rows filtered Extra

370

1 PRIMARY t8 const PRIMARY PRIMARY 142 const # 100.00 Using index

371

4 SUBQUERY t8 const PRIMARY PRIMARY 142 # 100.00 Using index

372

2 SUBQUERY t8 const PRIMARY PRIMARY 142 const # 100.00

373

3 SUBQUERY t8 const PRIMARY PRIMARY 142 # 100.00 Using index

374

Warnings:

375

Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))

376

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

377

t8 WHERE pseudo='joce');

378

ERROR 21000: Operand should contain 1 column(s)

379

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

380

pseudo='joce');

381

ERROR 21000: Operand should contain 1 column(s)

382

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

383

pseudo

384

joce

385

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

386

ERROR 21000: Subquery returns more than 1 row

387

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

388

#searchconthardwarefr3 forumconthardwarefr7

389

CREATE TEMPORARY TABLE `t1` (

390

`topic` bigint NOT NULL default '0',

391

`date` date NULL,

392

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

393

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

394

KEY `topic` (`topic`)

395

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

396

INSERT INTO t1 (topic,date,pseudo) VALUES

397

('43506','2002-10-02','joce'),('40143','2002-08-03','joce');

398

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

399

id select_type table type possible_keys key key_len ref rows filtered Extra

400

1 SIMPLE t1 index NULL PRIMARY 153 NULL 2 100.00 Using where; Using index

401

Warnings:

402

Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')

403

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

404

id select_type table type possible_keys key key_len ref rows filtered Extra

405

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

406

2 SUBQUERY t1 index NULL PRIMARY 153 NULL 2 100.00 Using where; Using index

407

Warnings:

408

Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`

409

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

410

date

411

2002-08-03

412

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

413

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

414

2002-08-03

415

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

416

417

418

419

420

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

421

ERROR 21000: Subquery returns more than 1 row

422

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

423

id select_type table type possible_keys key key_len ref rows filtered Extra

424

1 PRIMARY t1 index NULL topic 8 NULL 2 100.00 Using index

425

2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

426

3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used

427

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL

428

Warnings:

429

Note 1003 select 1 AS `1` from `test`.`t1` where 1

430

drop table t1;

431

#forumconthardwarefr7 searchconthardwarefr7

432

CREATE TABLE `t1` (

433

`numeropost` bigint NOT NULL auto_increment,

434

`maxnumrep` int NOT NULL default '0',

435

PRIMARY KEY (`numeropost`),

436

UNIQUE KEY `maxnumrep` (`maxnumrep`)

437

);

438

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

439

CREATE TABLE `t2` (

440

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

441

`topic` bigint NOT NULL default '0',

442

`date` date NULL,

443

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

444

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

445

);

446

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

447

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

448

449

40143

450

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;

451

numeropost maxnumrep

452

43506 2

453

40143 1

454

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

455

ERROR 42S22: Unknown column 'a' in 'having clause'

456

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

457

ERROR 42S22: Unknown column 'a' in 'having clause'

458

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

459

mot topic date pseudo

460

joce 40143 2002-10-22 joce

461

joce 43506 2002-10-22 joce

462

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

463

mot topic date pseudo

464

SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);

465

mot topic date pseudo

466

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

467

mot topic date pseudo

468

joce 40143 2002-10-22 joce

469

joce 43506 2002-10-22 joce

470

SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);

471

mot topic date pseudo

472

SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);

473

mot topic date pseudo

474

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

475

mot topic date pseudo

476

SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);

477

mot topic date pseudo

478

joce 40143 2002-10-22 joce

479

joce 43506 2002-10-22 joce

480

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

481

mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100)

482

joce 40143 2002-10-22 joce 1

483

joce 43506 2002-10-22 joce 1

484

SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);

485

mot topic date pseudo

486

SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);

487

mot topic date pseudo

488

joce 40143 2002-10-22 joce

489

joce 43506 2002-10-22 joce

490

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

491

mot topic date pseudo

492

joce 40143 2002-10-22 joce

493

SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);

494

mot topic date pseudo

495

joce 40143 2002-10-22 joce

496

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

497

mot topic date pseudo

498

joce 40143 2002-10-22 joce

499

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

500

mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000)

501

joce 40143 2002-10-22 joce 1

502

joce 43506 2002-10-22 joce 0

503

drop table t1,t2;

504

#forumconthardwarefr7

505

CREATE TABLE `t1` (

506

`numeropost` bigint NOT NULL auto_increment,

507

`maxnumrep` int NOT NULL default '0',

508

PRIMARY KEY (`numeropost`),

509

UNIQUE KEY `maxnumrep` (`maxnumrep`)

510

);

511

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

512

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

513

ERROR 21000: Subquery returns more than 1 row

514

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

515

ERROR 21000: Subquery returns more than 1 row

516

drop table t1;

517

create table t1 (a int);

518

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

519

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

520

521

522

523

524

drop table t1;

525

#iftest

526

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

527

INSERT INTO t1 VALUES ();

528

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

529

ERROR 21000: Subquery returns more than 1 row

530

drop table t1;

531

# threadhardwarefr7

532

CREATE TABLE `t1` (

533

`numeropost` bigint NOT NULL default '0',

534

`numreponse` int NOT NULL auto_increment,

535

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

536

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

537

UNIQUE KEY `numreponse` (`numreponse`),

538

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

539

);

540

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

541

ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)

542

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

543

ERROR 42S22: Unknown column 'a' in 'having clause'

544

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

545

numreponse (SELECT numeropost FROM t1 HAVING numreponse=1)

546

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

547

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

548

ERROR 21000: Subquery returns more than 1 row

549

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

550

id select_type table type possible_keys key key_len ref rows filtered Extra

551

1 SIMPLE NULL NULL NULL NULL NULL NULL # NULL Select tables optimized away

552

Warnings:

553

Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)

554

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

555

id select_type table type possible_keys key key_len ref rows filtered Extra

556

1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 12 const,const # 100.00 Using index

557

2 SUBQUERY NULL NULL NULL NULL NULL NULL # NULL Select tables optimized away

558

Warnings:

559

Note 1003 select '3' AS `numreponse` from `test`.`t1` where 1

560

drop table t1;

561

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

562

INSERT INTO t1 VALUES (1);

563

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

564

565

566

drop table t1;

567

#update with subselects

568

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

569

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

570

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

571

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

572

select * from t1;

573

a b

574

0 10

575

1 11

576

2 12

577

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

578

ERROR HY000: You can't specify target table 't1' for update in FROM clause

579

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

580

ERROR 21000: Subquery returns more than 1 row

581

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

582

select * from t1;

583

a b

584

0 NULL

585

1 21

586

2 22

587

drop table t1, t2;

588

#delete with subselects

589

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

590

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

591

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

592

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

593

select * from t1;

594

a b

595

0 10

596

1 11

597

2 12

598

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

599

a b

600

2 12

601

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

602

ERROR HY000: You can't specify target table 't1' for update in FROM clause

603

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

604

ERROR 21000: Subquery returns more than 1 row

605

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

606

select * from t1;

607

a b

608

0 10

609

1 11

610

drop table t1, t2;

611

#insert with subselects

612

CREATE TABLE t1 (x int);

613

create table t2 (a int);

614

create table t3 (b int);

615

insert into t2 values (1);

616

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

617

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

618

ERROR HY000: You can't specify target table 't1' for update in FROM clause

619

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

620

ERROR 21000: Subquery returns more than 1 row

621

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

622

select * from t1;

623

624

625

insert into t2 values (1);

626

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

627

select * from t1;

628

629

630

631

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

632

select * from t1;

633

634

635

636

637

638

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

639

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

640

select * from t1;

641

642

643

644

645

646

647

648

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

649

ERROR 42S22: Unknown column 'x' in 'field list'

650

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

651

select * from t1;

652

653

654

655

656

657

658

659

660

661

#TODO: should be uncommented after bug 380 fix pushed

662

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

663

#select * from t1;

664

drop table t1, t2, t3;

665

#replace with subselects

666

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

667

create table t2 (a int);

668

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

669

insert into t2 values (1);

670

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

671

select * from t1;

672

x y

673

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

674

ERROR HY000: You can't specify target table 't1' for update in FROM clause

675

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

676

ERROR 21000: Subquery returns more than 1 row

677

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

678

select * from t1;

679

x y

680

1 2

681

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

682

select * from t1;

683

x y

684

1 3

685

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

686

select * from t1;

687

x y

688

1 3

689

4 1

690

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

691

select * from t1;

692

x y

693

1 3

694

4 2

695

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

696

select * from t1;

697

x y

698

1 3

699

2 1

700

4 2

701

drop table t1, t2, t3;

702

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

703

ERROR HY000: No tables used

704

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

705

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

706

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

707

708

709

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

710

id select_type table type possible_keys key key_len ref rows filtered Extra

711

1 PRIMARY t2 ref id id 5 const 1 100.00 Using index

712

Warnings:

713

Note 1249 Select 2 was reduced during optimization

714

Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)

715

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

716

717

718

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

719

720

721

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

722

id select_type table type possible_keys key key_len ref rows filtered Extra

723

1 PRIMARY t2 ref id id 5 const 1 100.00 Using index

724

Warnings:

725

Note 1249 Select 3 was reduced during optimization

726

Note 1249 Select 2 was reduced during optimization

727

Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))

728

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

729

id select_type table type possible_keys key key_len ref rows filtered Extra

730

1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index

731

2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

732

3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used

733

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL

734

Warnings:

735

Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))

736

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

737

738

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

739

740

741

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

742

ERROR HY000: You can't specify target table 't2' for update in FROM clause

743

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

744

ERROR HY000: You can't specify target table 't2' for update in FROM clause

745

SELECT * FROM t2;

746

747

748

749

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

750

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

751

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

752

ERROR 21000: Subquery returns more than 1 row

753

drop table t2, t1;

754

#NULL test

755

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

756

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

757

select 1 IN (SELECT * from t1);

758

1 IN (SELECT * from t1)

759

760

select 10 IN (SELECT * from t1);

761

10 IN (SELECT * from t1)

762

763

select NULL IN (SELECT * from t1);

764

NULL IN (SELECT * from t1)

765

NULL

766

update t1 set a=NULL where a=2;

767

select 1 IN (SELECT * from t1);

768

1 IN (SELECT * from t1)

769

770

select 3 IN (SELECT * from t1);

771

3 IN (SELECT * from t1)

772

773

select 10 IN (SELECT * from t1);

774

10 IN (SELECT * from t1)

775

NULL

776

select 1 > ALL (SELECT * from t1);

777

1 > ALL (SELECT * from t1)

778

779

select 10 > ALL (SELECT * from t1);

780

10 > ALL (SELECT * from t1)

781

NULL

782

select 1 > ANY (SELECT * from t1);

783

1 > ANY (SELECT * from t1)

784

NULL

785

select 10 > ANY (SELECT * from t1);

786

10 > ANY (SELECT * from t1)

787

788

drop table t1;

789

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

790

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

791

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

792

'A' IN (SELECT * from t1)

793

794

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

795

'XYZS' IN (SELECT * from t1)

796

797

select NULL IN (SELECT * from t1);

798

NULL IN (SELECT * from t1)

799

NULL

800

update t1 set a=NULL where a='BC';

801

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

802

'A' IN (SELECT * from t1)

803

804

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

805

'DEF' IN (SELECT * from t1)

806

807

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

808

'XYZS' IN (SELECT * from t1)

809

NULL

810

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

811

'A' > ALL (SELECT * from t1)

812

813

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

814

'XYZS' > ALL (SELECT * from t1)

815

NULL

816

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

817

'A' > ANY (SELECT * from t1)

818

NULL

819

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

820

'XYZS' > ANY (SELECT * from t1)

821

822

drop table t1;

823

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

824

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

825

select 1.5 IN (SELECT * from t1);

826

1.5 IN (SELECT * from t1)

827

828

select 10.5 IN (SELECT * from t1);

829

10.5 IN (SELECT * from t1)

830

831

select NULL IN (SELECT * from t1);

832

NULL IN (SELECT * from t1)

833

NULL

834

update t1 set a=NULL where a=2.5;

835

select 1.5 IN (SELECT * from t1);

836

1.5 IN (SELECT * from t1)

837

838

select 3.5 IN (SELECT * from t1);

839

3.5 IN (SELECT * from t1)

840

841

select 10.5 IN (SELECT * from t1);

842

10.5 IN (SELECT * from t1)

843

NULL

844

select 1.5 > ALL (SELECT * from t1);

845

1.5 > ALL (SELECT * from t1)

846

847

select 10.5 > ALL (SELECT * from t1);

848

10.5 > ALL (SELECT * from t1)

849

NULL

850

select 1.5 > ANY (SELECT * from t1);

851

1.5 > ANY (SELECT * from t1)

852

NULL

853

select 10.5 > ANY (SELECT * from t1);

854

10.5 > ANY (SELECT * from t1)

855

856

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

857

id select_type table type possible_keys key key_len ref rows filtered Extra

858

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

859

Warnings:

860

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

861

Note 1249 Select 2 was reduced during optimization

862

Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`

863

select (select a+1) from t1;

864

(select a+1)

865

2.5

866

4.5

867

NULL

868

drop table t1;

869

870

# Null with keys

871

872

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

873

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

874

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

875

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

876

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

877

a t1.a in (select t2.a from t2)

878

1 1

879

2 1

880

3 1

881

4 0

882

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

883

id select_type table type possible_keys key key_len ref rows filtered Extra

884

1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index

885

2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index

886

Warnings:

887

Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`

888

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

889

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

890

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

891

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

892

1 1

893

2 1

894

3 1

895

4 0

896

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

897

id select_type table type possible_keys key key_len ref rows filtered Extra

898

1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index

899

2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index

900

2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

901

Warnings:

902

Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`

903

drop table t1,t2,t3;

904

#LIMIT is not supported now

905

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

906

#-- error 1235

907

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

908

#-- error 1235

909

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

910

#drop table t1;

911

912

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

913

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

914

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

915

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

916

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

917

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

918

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

919

#drop table t1,t2;

920

921

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

922

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

923

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

924

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

925

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

926

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

927

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

928

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

929

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

930

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

931

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

932

#-- error 1235

933

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

934

#drop table t1;

935

#test of uncacheable subqueries

936

CREATE TABLE t1 (a int);

937

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

938

id select_type table type possible_keys key key_len ref rows filtered Extra

939

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

940

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

941

Warnings:

942

Note 1003 select (select rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`

943

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

944

id select_type table type possible_keys key key_len ref rows filtered Extra

945

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

946

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

947

Warnings:

948

Note 1003 select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`

949

drop table t1;

950

CREATE TEMPORARY TABLE `t1` (

951

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

952

`topic` bigint NOT NULL default '0',

953

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

954

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

955

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

956

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

957

KEY `topic` (`topic`)

958

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

959

CREATE TEMPORARY TABLE `t2` (

960

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

961

`topic` bigint NOT NULL default '0',

962

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

963

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

964

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

965

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

966

KEY `topic` (`topic`)

967

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

968

CREATE TEMPORARY TABLE `t3` (

969

`numeropost` bigint NOT NULL auto_increment,

970

`maxnumrep` int NOT NULL default '0',

971

PRIMARY KEY (`numeropost`),

972

UNIQUE KEY `maxnumrep` (`maxnumrep`)

973

) ENGINE=MyISAM;

974

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

975

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

976

INSERT INTO t3 VALUES (1,1);

977

SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE

978

numeropost=topic);

979

topic

980

981

select * from t1;

982

mot topic date pseudo

983

joce 1 2009-01-20 joce

984

test 2 2009-01-20 test

985

DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT

986

EXISTS(SELECT * FROM t3 WHERE numeropost=topic));

987

select * from t1;

988

mot topic date pseudo

989

joce 1 2009-01-20 joce

990

drop table t1, t2, t3;

991

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

992

a (SELECT a)

993

1 1

994

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

995

SHOW CREATE TABLE t1;

996

Table Create Table

997

t1 CREATE TEMPORARY TABLE `t1` (

998

`a` int NOT NULL DEFAULT '0',

999

`(SELECT 1)` int NOT NULL DEFAULT '0'

1000

) ENGINE=MyISAM

1001

drop table t1;

1002

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

1003

SHOW CREATE TABLE t1;

1004

Table Create Table

1005

t1 CREATE TEMPORARY TABLE `t1` (

1006

`a` int NOT NULL DEFAULT '0',

1007

`(SELECT a)` int NOT NULL DEFAULT '0'

1008

) ENGINE=MyISAM

1009

drop table t1;

1010

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

1011

SHOW CREATE TABLE t1;

1012

Table Create Table

1013

t1 CREATE TEMPORARY TABLE `t1` (

1014

`a` int NOT NULL DEFAULT '0',

1015

`(SELECT a+0)` int NOT NULL DEFAULT '0'

1016

) ENGINE=MyISAM

1017

drop table t1;

1018

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

1019

select * from t1;

1020

1021

1022

SHOW CREATE TABLE t1;

1023

Table Create Table

1024

t1 CREATE TEMPORARY TABLE `t1` (

1025

`a` bigint NOT NULL

1026

) ENGINE=MyISAM

1027

drop table t1;

1028

create table t1 (a int);

1029

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

1030

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

1031

from t1;

1032

id select_type table type possible_keys key key_len ref rows filtered Extra

1033

1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00

1034

2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00

1035

3 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00

1036

Warnings:

1037

Note 1003 select `test`.`t1`.`a` AS `a`,(select (select rand() AS `rand()` from `test`.`t1` limit 1) AS `(select rand() from t1 limit 1)` from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1) from t1 limit 1)` from `test`.`t1`

1038

drop table t1;

1039

1040

# error in IN

1041

1042

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

1043

ERROR 42S02: Table 'test.t1' doesn't exist

1044

1045

# complex subquery

1046

1047

CREATE TABLE t1 (

1048

ID int NOT NULL auto_increment,

1049

name char(35) NOT NULL default '',

1050

t2 char(3) NOT NULL default '',

1051

District char(20) NOT NULL default '',

1052

Population int NOT NULL default '0',

1053

PRIMARY KEY (ID)

1054

);

1055

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

1056

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

1057

INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);

1058

CREATE TABLE t2 (

1059

Code char(3) NOT NULL default '',

1060

Name char(52) NOT NULL default '',

1061

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

1062

Region char(26) NOT NULL default '',

1063

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

1064

IndepYear int default NULL,

1065

Population int NOT NULL default '0',

1066

LifeExpectancy float(3,1) default NULL,

1067

GNP float(10,2) default NULL,

1068

GNPOld float(10,2) default NULL,

1069

LocalName char(45) NOT NULL default '',

1070

GovernmentForm char(45) NOT NULL default '',

1071

HeadOfState char(60) default NULL,

1072

Capital int default NULL,

1073

Code2 char(2) NOT NULL default '',

1074

PRIMARY KEY (Code)

1075

);

1076

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

1077

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

1078

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

1079

Continent Name Population

1080

Oceania Sydney 3276207

1081

drop table t1, t2;

1082

1083

# constants in IN

1084

1085

CREATE TEMPORARY TABLE `t1` (

1086

`id` bigint NOT NULL auto_increment,

1087

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

1088

PRIMARY KEY (`id`),

1089

UNIQUE KEY `pseudo` (`pseudo`)

1090

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

1091

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

1092

SELECT 0 IN (SELECT 1 FROM t1 a);

1093

0 IN (SELECT 1 FROM t1 a)

1094

1095

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

1096

id select_type table type possible_keys key key_len ref rows filtered Extra

1097

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

1098

2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1099

Warnings:

1100

Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`

1101

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

1102

SELECT 0 IN (SELECT 1 FROM t1 a);

1103

0 IN (SELECT 1 FROM t1 a)

1104

1105

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

1106

id select_type table type possible_keys key key_len ref rows filtered Extra

1107

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

1108

2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1109

Warnings:

1110

Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`

1111

drop table t1;

1112

CREATE TEMPORARY TABLE `t1` (

1113

`i` int NOT NULL default '0',

1114

PRIMARY KEY (`i`)

1115

) ENGINE=MyISAM;

1116

INSERT INTO t1 VALUES (1);

1117

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

1118

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

1119

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

1120

ERROR 42S22: Unknown column 't.i' in 'field list'

1121

select * from t1;

1122

1123

1124

drop table t1;

1125

1126

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

1127

1128

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

1129

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

1130

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

1131

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

1132

NULL

1133

drop table t1;

1134

1135

# Test optimization for sub selects

1136

1137

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

1138

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

1139

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

1140

id select_type table type possible_keys key key_len ref rows filtered Extra

1141

1 PRIMARY t1 ref salary salary 5 const # 100.00 Using where; Using index

1142

2 SUBQUERY NULL NULL NULL NULL NULL NULL # NULL Select tables optimized away

1143

Warnings:

1144

Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`))

1145

drop table t1;

1146

CREATE TABLE t1 (

1147

ID int NOT NULL auto_increment,

1148

SUB_ID int NOT NULL default '0',

1149

REF_ID int default NULL,

1150

REF_SUB int default '0',

1151

PRIMARY KEY (ID,SUB_ID),

1152

UNIQUE KEY t1_PK (ID,SUB_ID),

1153

KEY t1_FK (REF_ID,REF_SUB),

1154

KEY t1_REFID (REF_ID)

1155

);

1156

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

1157

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

1158

REF_ID

1159

DROP TABLE t1;

1160

1161

# uninterruptable update

1162

1163

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

1164

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

1165

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

1166

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

1167

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

1168

Warnings:

1169

Error 1242 Subquery returns more than 1 row

1170

select * from t1;

1171

a b

1172

1 1

1173

2 NULL

1174

3 1

1175

drop table t1, t2;

1176

1177

# reduced subselect in ORDER BY & GROUP BY clauses

1178

1179

CREATE TEMPORARY TABLE `t1` (

1180

`id` bigint NOT NULL auto_increment,

1181

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

1182

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

1183

PRIMARY KEY (`id`),

1184

UNIQUE KEY `email` (`email`),

1185

UNIQUE KEY `pseudo` (`pseudo`)

1186

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

1187

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

1188

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

1189

a b

1190

test test

1191

test1 test1

1192

drop table if exists t1;

1193

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

1194

1195

1196

1197

# IN subselect optimization test

1198

1199

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

1200

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

1201

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

1202

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

1203

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

1204

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

1205

select * from t2 where t2.a in (select a from t1);

1206

1207

1208

1209

1210

explain extended select * from t2 where t2.a in (select a from t1);

1211

id select_type table type possible_keys key key_len ref rows filtered Extra

1212

1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index

1213

2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index

1214

Warnings:

1215

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))

1216

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

1217

1218

1219

1220

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

1221

id select_type table type possible_keys key key_len ref rows filtered Extra

1222

1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index

1223

2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where

1224

Warnings:

1225

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))

1226

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

1227

1228

1229

1230

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

1231

id select_type table type possible_keys key key_len ref rows filtered Extra

1232

1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index

1233

2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00

1234

2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index

1235

Warnings:

1236

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))

1237

drop table t1, t2, t3;

1238

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

1239

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

1240

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

1241

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

1242

# making table large enough

1243

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

1244

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

1245

select * from t2 where t2.a in (select a from t1);

1246

1247

1248

1249

1250

explain extended select * from t2 where t2.a in (select a from t1);

1251

id select_type table type possible_keys key key_len ref rows filtered Extra

1252

1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index

1253

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index

1254

Warnings:

1255

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a)))

1256

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

1257

1258

1259

1260

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

1261

id select_type table type possible_keys key key_len ref rows filtered Extra

1262

1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index

1263

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where

1264

Warnings:

1265

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))

1266

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

1267

1268

1269

1270

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

1271

id select_type table type possible_keys key key_len ref rows filtered Extra

1272

1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index

1273

2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using index

1274

2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer

1275

Warnings:

1276

1277

insert into t1 values (3,31);

1278

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

1279

1280

1281

1282

1283

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

1284

1285

1286

1287

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

1288

id select_type table type possible_keys key key_len ref rows filtered Extra

1289

1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index

1290

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where

1291

Warnings:

1292

1293

drop table t1, t2, t3;

1294

1295

# alloc_group_fields() working

1296

1297

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

1298

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

1299

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

1300

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

1301

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

1302

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

1303

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;

1304

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)

1305

3 1

1306

2 2

1307

1 2

1308

drop table t1,t2,t3;

1309

1310

# aggregate functions in HAVING test

1311

1312

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

1313

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

1314

insert into t1 values (1);

1315

insert into t2 values (1);

1316

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

1317

1318

1319

drop table t1,t2;

1320

1321

# update subquery with wrong field (to force name resolving

1322

# in UPDATE name space)

1323

1324

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

1325

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

1326

insert into t1 values (1);

1327

insert into t2 values (1);

1328

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

1329

ERROR 42S22: Unknown column 'x.s1' in 'field list'

1330

DROP TABLE t1, t2;

1331

1332

# collation test

1333

1334

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

1335

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

1336

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

1337

#-- error 1267

1338

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

1339

#-- error 1267

1340

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

1341

#drop table t1;

1342

1343

# aggregate functions reinitialization

1344

1345

create table t1(toid int,rd int);

1346

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

1347

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

1348

insert into t1 values(1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2);

1349

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

1350

userid pmtotal pmnew calc_total calc_new

1351

1 0 0 9 3

1352

2 0 0 4 2

1353

drop table t1, t2;

1354

1355

# row union

1356

1357

create table t1 (s1 char(5));

1358

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

1359

ERROR 21000: Operand should contain 1 column(s)

1360

insert into t1 values ('tttt');

1361

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

1362

1363

tttt

1364

explain extended (select * from t1);

1365

id select_type table type possible_keys key key_len ref rows filtered Extra

1366

1 SIMPLE t1 ALL NULL NULL NULL NULL # 100.00

1367

Warnings:

1368

Note 1003 (select `test`.`t1`.`s1` AS `s1` from `test`.`t1`)

1369

(select * from t1);

1370

1371

tttt

1372

drop table t1;

1373

1374

# IN optimisation test results

1375

1376

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

1377

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

1378

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

1379

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

1380

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

1381

s1 s1 NOT IN (SELECT s1 FROM t2)

1382

a1 0

1383

a2 0

1384

a3 1

1385

select s1, s1 = ANY (SELECT s1 FROM t2) from t1;

1386

s1 s1 = ANY (SELECT s1 FROM t2)

1387

a1 1

1388

a2 1

1389

a3 0

1390

select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;

1391

s1 s1 <> ALL (SELECT s1 FROM t2)

1392

a1 0

1393

a2 0

1394

a3 1

1395

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

1396

s1 s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')

1397

a1 0

1398

a2 1

1399

a3 1

1400

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

1401

id select_type table type possible_keys key key_len ref rows filtered Extra

1402

1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index

1403

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key

1404

Warnings:

1405

Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`

1406

explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;

1407

id select_type table type possible_keys key key_len ref rows filtered Extra

1408

1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index

1409

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key

1410

Warnings:

1411

Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`

1412

explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;

1413

id select_type table type possible_keys key key_len ref rows filtered Extra

1414

1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index

1415

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key

1416

Warnings:

1417

1418

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

1419

id select_type table type possible_keys key key_len ref rows filtered Extra

1420

1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index

1421

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Using where; Full scan on NULL key

1422

Warnings:

1423

Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`

1424

drop table t1,t2;

1425

1426

# correct ALL optimisation

1427

1428

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

1429

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

1430

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

1431

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

1432

1433

1434

1435

1436

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

1437

id select_type table type possible_keys key key_len ref rows filtered Extra

1438

1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

1439

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1440

Warnings:

1441

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max('0') from `test`.`t2`)))

1442

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

1443

1444

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

1445

id select_type table type possible_keys key key_len ref rows filtered Extra

1446

1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

1447

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1448

Warnings:

1449

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min('0') from `test`.`t2`)))

1450

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

1451

1452

1453

1454

1455

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

1456

id select_type table type possible_keys key key_len ref rows filtered Extra

1457

1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

1458

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1459

Warnings:

1460

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select '0' AS `b` from `test`.`t2` group by 1)))

1461

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

1462

1463

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

1464

id select_type table type possible_keys key key_len ref rows filtered Extra

1465

1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

1466

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1467

Warnings:

1468

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select '0' AS `b` from `test`.`t2` group by 1)))

1469

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

1470

1471

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

1472

id select_type table type possible_keys key key_len ref rows filtered Extra

1473

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1474

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1475

Warnings:

1476

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0

1477

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

1478

1479

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

1480

id select_type table type possible_keys key key_len ref rows filtered Extra

1481

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1482

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1483

Warnings:

1484

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0

1485

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

1486

1487

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

1488

id select_type table type possible_keys key key_len ref rows filtered Extra

1489

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1490

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1491

Warnings:

1492

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0

1493

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

1494

1495

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

1496

id select_type table type possible_keys key key_len ref rows filtered Extra

1497

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1498

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1499

Warnings:

1500

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0

1501

1502

# optimized static ALL/ANY with grouping

1503

1504

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

1505

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

1506

1507

1508

1509

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

1510

id select_type table type possible_keys key key_len ref rows filtered Extra

1511

1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

1512

2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort

1513

Warnings:

1514

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) AS `max(b)` from `test`.`t2` group by `test`.`t2`.`a`)))

1515

drop table t2, t3;

1516

1517

# correct used_tables()

1518

1519

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 ;

1520

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

1521

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;

1522

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

1523

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 ;

1524

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

1525

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

1526

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

1527

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;

1528

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

1529

-1 Valid 1

1530

-1 Valid 2 1

1531

-1 Should Not Return 0

1532

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;

1533

dbid name

1534

-1 Valid

1535

-1 Valid 2

1536

drop table t1,t2,t3,t4;

1537

1538

# cardinality check

1539

1540

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

1541

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

1542

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

1543

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

1544

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

1545

ERROR 21000: Operand should contain 3 column(s)

1546

DROP TABLE t1,t2;

1547

1548

# optimized ALL/ANY with union

1549

1550

create table t1 (s1 char);

1551

insert into t1 values ('e');

1552

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

1553

1554

1555

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

1556

1557

1558

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

1559

id select_type table type possible_keys key key_len ref rows filtered Extra

1560

1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00

1561

2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00

1562

3 UNION t1 ALL NULL NULL NULL NULL # 100.00

1563

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL

1564

Warnings:

1565

Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1

1566

drop table t1;

1567

1568

# filesort in subquery (restoring join_tab)

1569

1570

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

1571

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

1572

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

1573

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

1574

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;

1575

phone code

1576

69294728265 6

1577

18621828126 1862

1578

89356874041 NULL

1579

95895001874 NULL

1580

drop table t1, t2;

1581

1582

# unresolved field error

1583

1584

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

1585

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

1586

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

1587

ERROR 42S22: Unknown column 't1.s2' in 'where clause'

1588

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

1589

ERROR 42S22: Unknown column 't1.s2' in 'group statement'

1590

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

1591

ERROR 42S22: Unknown column 't1.s2' in 'group statement'

1592

drop table t1, t2;

1593

1594

# fix_fields() in add_ref_to_table_cond()

1595

1596

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

1597

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

1598

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

1599

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

1600

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

1601

COLC

1602

DROP TABLE t1, t2;

1603

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

1604

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

1605

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

1606

(SELECT a)

1607

1608

1609

1610

1611

1612

DROP TABLE t1;

1613

1614

# Bug 2479

1615

1616

CREATE TEMPORARY TABLE `t1` (

1617

`id` int NOT NULL auto_increment,

1618

`id_cns` int NOT NULL default '0',

1619

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

1620

`anno_dep` int NOT NULL default '0',

1621

`particolare` bigint NOT NULL default '0',

1622

`generale` bigint NOT NULL default '0',

1623

`bis` int NOT NULL default '0',

1624

PRIMARY KEY (`id`),

1625

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

1626

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

1627

ENGINE=MyISAM;

1628

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

1629

CREATE TEMPORARY TABLE `t2` (

1630

`id` int NOT NULL auto_increment,

1631

`max_anno_dep` int NOT NULL default '0',

1632

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

1633

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

1634

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;

1635

id max_anno_dep PIPPO

1636

16 1987 1

1637

50 1990 0

1638

51 1990 NULL

1639

DROP TABLE t1, t2;

1640

1641

# GLOBAL LIMIT

1642

1643

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

1644

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

1645

SET SQL_SELECT_LIMIT=1;

1646

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

1647

sum(a)

1648

1649

select 2 in (select * from t1);

1650

2 in (select * from t1)

1651

1652

SET SQL_SELECT_LIMIT=default;

1653

drop table t1;

1654

1655

# Bug #3118: subselect + order by

1656

1657

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

1658

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

1659

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

1660

a b

1661

1 1

1662

1 2

1663

1 3

1664

DROP TABLE t1;

1665

# Item_cond fix field

1666

1667

create table t1(val varchar(10));

1668

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

1669

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

1670

count(*)

1671

1672

drop table t1;

1673

1674

# ref_or_null replacing with ref

1675

1676

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

1677

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

1678

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

1679

id text

1680

8 text8

1681

9 text9

1682

10 text10

1683

11 text11

1684

12 text12

1685

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

1686

id text

1687

8 text8

1688

9 text9

1689

10 text10

1690

11 text11

1691

12 text12

1692

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

1693

id select_type table type possible_keys key key_len ref rows filtered Extra

1694

1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00 Using where

1695

2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func # 100.00 Using index; Using where

1696

Warnings:

1697

Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))

1698

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

1699

id select_type table type possible_keys key key_len ref rows filtered Extra

1700

1 PRIMARY tt ALL NULL NULL NULL NULL # 100.00 Using where

1701

2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id # 100.00 Using where; Using index

1702

Warnings:

1703

Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1

1704

Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))

1705

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

1706

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

1707

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

1708

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

1709

id text id text id text

1710

1 text1 1 text1 1 text1

1711

2 text2 2 text2 2 text2

1712

3 text3 3 text3 3 text3

1713

4 text4 4 text4 4 text4

1714

5 text5 5 text5 5 text5

1715

6 text6 6 text6 6 text6

1716

7 text7 7 text7 7 text7

1717

8 text8 8 text8 8 text8

1718

9 text9 9 text9 9 text9

1719

10 text10 10 text10 10 text10

1720

11 text11 11 text1 11 text11

1721

12 text12 12 text2 12 text12

1722

1000 text1000 NULL NULL 1000 text1000

1723

1001 text1001 NULL NULL 1000 text1000

1724

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

1725

id select_type table type possible_keys key key_len ref rows filtered Extra

1726

1 SIMPLE a ALL NULL NULL NULL NULL # 100.00

1727

1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id # 100.00

1728

1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func # 100.00 Using where

1729

Warnings:

1730

Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`b`.`id` = `test`.`a`.`id`) or isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`)

1731

drop table t1,t2;

1732

1733

# Static tables & rund() in subqueries

1734

1735

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

1736

insert into t1 values (1);

1737

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

1738

id select_type table type possible_keys key key_len ref rows Extra

1739

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used

1740

2 SUBQUERY t1 system NULL NULL NULL NULL 1

1741

drop table t1;

1742

1743

# bug 3188

1744

1745

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

1746

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

1747

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

1748

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

1749

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'null) from t1, t2' at line 1

1750

drop table t1,t2,t3;

1751

1752

# aggregate functions (Bug #3505)

1753

1754

CREATE TABLE t1 (id INT);

1755

CREATE TABLE t2 (id INT);

1756

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

1757

INSERT INTO t2 VALUES (1);

1758

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

1759

id c

1760

1 1

1761

2 0

1762

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

1763

id c

1764

1 1

1765

2 0

1766

SELECT t1.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 t1.id;

1767

id c

1768

1 1

1769

2 0

1770

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;

1771

id c

1772

1 1

1773

2 0

1774

DROP TABLE t1,t2;

1775

1776

# ALL/ANY test

1777

1778

CREATE TABLE t1 ( a int, b int );

1779

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

1780

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

1781

1782

1783

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

1784

1785

1786

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

1787

1788

1789

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

1790

1791

1792

1793

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

1794

1795

1796

1797

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

1798

1799

1800

1801

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

1802

1803

1804

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

1805

1806

1807

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

1808

1809

1810

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

1811

1812

1813

1814

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

1815

1816

1817

1818

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

1819

1820

1821

1822

# with index

1823

ALTER TABLE t1 ADD INDEX (a);

1824

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

1825

1826

1827

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

1828

1829

1830

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

1831

1832

1833

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

1834

1835

1836

1837

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

1838

1839

1840

1841

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

1842

1843

1844

1845

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

1846

1847

1848

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

1849

1850

1851

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

1852

1853

1854

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

1855

1856

1857

1858

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

1859

1860

1861

1862

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

1863

1864

1865

1866

# having clause test

1867

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

1868

1869

1870

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

1871

1872

1873

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

1874

1875

1876

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

1877

1878

1879

1880

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

1881

1882

1883

1884

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

1885

1886

1887

1888

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

1889

1890

1891

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

1892

1893

1894

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

1895

1896

1897

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

1898

1899

1900

1901

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

1902

1903

1904

1905

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

1906

1907

1908

1909

# union test

1910

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

1911

1912

1913

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

1914

1915

1916

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

1917

1918

1919

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

1920

1921

1922

1923

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

1924

1925

1926

1927

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

1928

1929

1930

1931

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

1932

1933

1934

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

1935

1936

1937

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

1938

1939

1940

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

1941

1942

1943

1944

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

1945

1946

1947

1948

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

1949

1950

1951

1952

# union + having test

1953

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

1954

1955

1956

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

1957

1958

1959

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

1960

1961

1962

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

1963

1964

1965

1966

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

1967

1968

1969

1970

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

1971

1972

1973

1974

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

1975

1976

1977

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

1978

1979

1980

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

1981

1982

1983

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

1984

1985

1986

1987

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

1988

1989

1990

1991

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

1992

1993

1994

1995

# row tests

1996

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

1997

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

1998

ERROR 21000: Operand should contain 1 column(s)

1999

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

2000

ERROR 21000: Operand should contain 1 column(s)

2001

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

2002

ERROR 21000: Operand should contain 1 column(s)

2003

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

2004

ERROR 21000: Operand should contain 1 column(s)

2005

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

2006

ERROR 21000: Operand should contain 1 column(s)

2007

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

2008

ERROR 21000: Operand should contain 1 column(s)

2009

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

2010

ERROR 21000: Operand should contain 1 column(s)

2011

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

2012

ERROR 21000: Operand should contain 1 column(s)

2013

# following should be converted to IN

2014

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

2015

ERROR 21000: Operand should contain 2 column(s)

2016

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

2017

ERROR 21000: Operand should contain 1 column(s)

2018

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

2019

2020

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

2021

ERROR 21000: Operand should contain 2 column(s)

2022

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

2023

ERROR 21000: Operand should contain 1 column(s)

2024

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

2025

2026

2027

2028

2029

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

2030

2031

2032

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

2033

2034

2035

2036

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

2037

2038

2039

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

2040

2041

2042

2043

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

2044

2045

2046

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

2047

2048

2049

2050

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

2051

2052

2053

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

2054

2055

2056

2057

# without optimisation

2058

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

2059

2060

2061

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

2062

2063

2064

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

2065

2066

2067

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

2068

2069

2070

2071

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

2072

2073

2074

2075

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

2076

2077

2078

2079

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

2080

2081

2082

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

2083

2084

2085

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

2086

2087

2088

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

2089

2090

2091

2092

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

2093

2094

2095

2096

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

2097

2098

2099

2100

# without optimisation + having

2101

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

2102

2103

2104

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

2105

2106

2107

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

2108

2109

2110

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

2111

2112

2113

2114

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

2115

2116

2117

2118

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

2119

2120

2121

2122

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

2123

2124

2125

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

2126

2127

2128

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

2129

2130

2131

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

2132

2133

2134

2135

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

2136

2137

2138

2139

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

2140

2141

2142

2143

# EXISTS in string contence

2144

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

2145

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

2146

2147

2148

2149

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

2150

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

2151

2152

2153

2154

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

2155

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

2156

2157

2158

2159

DROP TABLE t1;

2160

CREATE TABLE t1 ( a double, b double );

2161

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

2162

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

2163

2164

2165

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

2166

2167

2168

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

2169

2170

2171

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

2172

2173

2174

2175

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

2176

2177

2178

2179

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

2180

2181

2182

2183

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

2184

2185

2186

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

2187

2188

2189

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

2190

2191

2192

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

2193

2194

2195

2196

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

2197

2198

2199

2200

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

2201

2202

2203

2204

DROP TABLE t1;

2205

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

2206

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

2207

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

2208

2209

2210

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

2211

2212

2213

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

2214

2215

2216

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

2217

2218

2219

2220

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

2221

2222

2223

2224

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

2225

2226

2227

2228

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

2229

2230

2231

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

2232

2233

2234

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

2235

2236

2237

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

2238

2239

2240

2241

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

2242

2243

2244

2245

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

2246

2247

2248

2249

DROP TABLE t1;

2250

2251

# SELECT(EXISTS * ...)optimisation

2252

2253

create table t1 (a int, b int);

2254

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

2255

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

2256

a b

2257

1 2

2258

3 4

2259

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

2260

id select_type table type possible_keys key key_len ref rows filtered Extra

2261

1 PRIMARY up ALL NULL NULL NULL NULL # 100.00 Using where

2262

2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00 Using where

2263

Warnings:

2264

Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1

2265

Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))

2266

drop table t1;

2267

2268

# Bug #4102: subselect in HAVING

2269

2270

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

2271

INSERT INTO t1 VALUES (1);

2272

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

2273

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

2274

SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1

2275

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

2276

t1_a t2_a t2_b

2277

1 1 2

2278

DROP TABLE t1, t2;

2279

2280

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

2281

2282

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

2283

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

2284

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

2285

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

2286

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

2287

id name id pet

2288

1 Tim 1 Fido

2289

2 Rebecca 2 Spot

2290

3 NULL 3 Felix

2291

drop table t1,t2;

2292

2293

# Aggregate function comparation with ALL/ANY/SOME subselect

2294

2295

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

2296

insert into t1 values (1);

2297

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

2298

insert into t2 values (1,2);

2299

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

2300

a C

2301

1 1

2302

drop table t1,t2;

2303

2304

# BUG#5003 - like in subselect

2305

2306

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

2307

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

2308

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

2309

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

2310

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

2311

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

2312

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

2313

IZAVORGANG_ID

2314

D0000000001

2315

drop table t1;

2316

2317

# Optimized IN with compound index

2318

2319

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

2320

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

2321

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

2322

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

2323

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

2324

aid bid

2325

1 1

2326

2 1

2327

alter table t2 drop primary key;

2328

alter table t2 add key KEY1 (aid, bid);

2329

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

2330

aid bid

2331

1 1

2332

2 1

2333

alter table t2 drop key KEY1;

2334

alter table t2 add primary key (bid, aid);

2335

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

2336

aid bid

2337

1 1

2338

2 1

2339

drop table t1,t2;

2340

2341

# resolving fields of grouped outer SELECT

2342

2343

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

2344

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

2345

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

2346

howmanyvalues count(*)

2347

1 1

2348

2 2

2349

3 3

2350

4 4

2351

SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;

2352

howmanyvalues mycount

2353

1 1

2354

2 2

2355

3 3

2356

4 4

2357

CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);

2358

SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;

2359

howmanyvalues mycount

2360

1 1

2361

2 2

2362

3 3

2363

4 4

2364

SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;

2365

howmanyvalues mycount

2366

1 1

2367

2 2

2368

3 3

2369

4 4

2370

SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;

2371

howmanyvalues mycount

2372

1 1

2373

2 1

2374

3 1

2375

4 1

2376

drop table t1;

2377

create table t1 (x int);

2378

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;

2379

(select b.x from t1 as b where b.x=a.x)

2380

drop table t1;

2381

2382

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

2383

# creation

2384

2385

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;

2386

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

2387

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 ;

2388

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

2389

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;

2390

ERROR 42S22: Unknown column 'b.sc' in 'field list'

2391

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;

2392

2393

700

2394

NULL

2395

drop tables t1,t2;

2396

2397

# Subselect in non-select command just after connection

2398

2399

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

2400

2401

# primary query with temporary table and subquery with groupping

2402

2403

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

2404

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

2405

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

2406

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

2407

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

2408

a b (select max(b) from t2 where t1.b=t2.a)

2409

1 1 3

2410

1 2 1

2411

1 3 NULL

2412

2 4 NULL

2413

2 5 NULL

2414

drop table t1, t2;

2415

2416

# Equal operation under row and empty subquery

2417

2418

create table t1 (s1 int,s2 int);

2419

insert into t1 values (20,15);

2420

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

2421

s1 s2

2422

drop table t1;

2423

2424

# ALL/ANY with NULL

2425

2426

create table t1 (s1 int);

2427

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

2428

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

2429

2430

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

2431

s1 s1 < all (select s1 from t1)

2432

1 0

2433

NULL NULL

2434

drop table t1;

2435

2436

# reference on changable fields from subquery

2437

2438

CREATE TABLE t1 (

2439

Code char(3) NOT NULL default '',

2440

Name char(52) NOT NULL default '',

2441

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

2442

Region char(26) NOT NULL default '',

2443

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

2444

IndepYear int default NULL,

2445

Population int NOT NULL default '0',

2446

LifeExpectancy float(3,1) default NULL,

2447

GNP float(10,2) default NULL,

2448

GNPOld float(10,2) default NULL,

2449

LocalName char(45) NOT NULL default '',

2450

GovernmentForm char(45) NOT NULL default '',

2451

HeadOfState char(60) default NULL,

2452

Capital int default NULL,

2453

Code2 char(2) NOT NULL default ''

2454

);

2455

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

2456

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

2457

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

2458

INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');

2459

/*!40000 ALTER TABLE t1 ENABLE KEYS */;

2460

Warnings:

2461

Note 1031 Table storage engine for 't1' doesn't have this option

2462

SELECT DISTINCT Continent AS c FROM t1 outr WHERE

2463

Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND

2464

Population < 200);

2465

2466

Oceania

2467

drop table t1;

2468

2469

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

2470

# non-existant field.

2471

2472

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

2473

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

2474

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

2475

ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'

2476

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

2477

2478

drop table t1,t2;

2479

2480

# Comparison subquery with * and row

2481

2482

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

2483

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

2484

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

2485

NULL

2486

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

2487

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

2488

NULL

2489

# queries whih can be converted to IN

2490

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

2491

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

2492

2493

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

2494

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

2495

2496

drop table t1;

2497

2498

# Comparison subquery and row with nested rows

2499

2500

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

2501

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

2502

ERROR 21000: Operand should contain 2 column(s)

2503

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

2504

ERROR 21000: Operand should contain 2 column(s)

2505

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

2506

ERROR 21000: Operand should contain 1 column(s)

2507

drop table t1;

2508

2509

# Forward reference detection

2510

2511

create table t1 (a integer);

2512

insert into t1 values (1);

2513

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

2514

ERROR 42S22: Reference 'xx' not supported (forward reference in item list)

2515

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

2516

ERROR 42S22: Reference 'xx' not supported (forward reference in item list)

2517

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

2518

xx 1 = ALL ( select 1 from t1 where 1 = xx )

2519

1 1

2520

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

2521

ERROR 42S22: Reference 'xx' not supported (forward reference in item list)

2522

drop table t1;

2523

2524

# Test for BUG#8218

2525

2526

CREATE TEMPORARY TABLE t1 (

2527

categoryId int NOT NULL,

2528

courseId int NOT NULL,

2529

startDate datetime NOT NULL,

2530

endDate datetime NOT NULL,

2531

createDate datetime NOT NULL,

2532

modifyDate timestamp NOT NULL,

2533

attributes text NOT NULL)

2534

engine=myisam;

2535

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

2536

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

2537

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

2538

(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),

2539

(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),

2540

(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),

2541

(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),

2542

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

2543

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

2544

CREATE TEMPORARY TABLE t2 (

2545

userId int NOT NULL,

2546

courseId int NOT NULL,

2547

date datetime NOT NULL)

2548

engine=myisam;

2549

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

2550

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

2551

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

2552

(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),

2553

(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),

2554

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

2555

CREATE TEMPORARY TABLE t3 (

2556

groupId int NOT NULL,

2557

parentId int NOT NULL,

2558

startDate datetime NOT NULL,

2559

endDate datetime NOT NULL,

2560

createDate datetime NOT NULL,

2561

modifyDate timestamp NOT NULL,

2562

ordering int)

2563

engine=myisam;

2564

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

2565

CREATE TEMPORARY TABLE t4 (

2566

id int NOT NULL,

2567

groupTypeId int NOT NULL,

2568

groupKey varchar(50) NOT NULL,

2569

name text,

2570

ordering int,

2571

description text,

2572

createDate datetime NOT NULL,

2573

modifyDate timestamp NOT NULL)

2574

engine=myisam;

2575

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

2576

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

2577

CREATE TEMPORARY TABLE t5 (

2578

userId int NOT NULL,

2579

groupId int NOT NULL,

2580

createDate datetime NOT NULL,

2581

modifyDate timestamp NOT NULL) ENGINE=MyISAM;

2582

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

2583

select

2584

count(distinct t2.userid) pass,

2585

groupstuff.*,

2586

count(t2.courseid) crse,

2587

t1.categoryid,

2588

t2.courseid,

2589

date_format(date, '%b%y') as colhead

2590

from t2

2591

join t1 on t2.courseid=t1.courseid

2592

join

2593

(

2594

select

2595

t5.userid,

2596

parentid,

2597

parentgroup,

2598

childid,

2599

groupname,

2600

grouptypeid

2601

from t5

2602

join

2603

(

2604

select t4.id as parentid,

2605

t4.name as parentgroup,

2606

t4.id as childid,

2607

t4.name as groupname,

2608

t4.grouptypeid

2609

from t4

2610

) as gin on t5.groupid=gin.childid

2611

) as groupstuff on t2.userid = groupstuff.userid

2612

group by

2613

groupstuff.groupname, colhead , t2.courseid;

2614

pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead

2615

1 5141 12 group2 12 group2 5 1 5 12 Aug04

2616

1 5141 12 group2 12 group2 5 1 1 41 Aug04

2617

1 5141 12 group2 12 group2 5 1 2 52 Aug04

2618

1 5141 12 group2 12 group2 5 1 2 53 Aug04

2619

1 5141 12 group2 12 group2 5 1 3 51 Oct04

2620

1 5141 12 group2 12 group2 5 1 1 86 Oct04

2621

1 5141 12 group2 12 group2 5 1 1 87 Oct04

2622

1 5141 12 group2 12 group2 5 1 2 88 Oct04

2623

1 5141 12 group2 12 group2 5 1 2 89 Oct04

2624

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

2625

2626

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

2627

2628

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

2629

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

2630

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

2631

2632

2633

2634

2635

drop table t1;

2636

2637

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

2638

2639

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

2640

insert into t1 values (1,2);

2641

select 1 = (select * from t1);

2642

ERROR 21000: Operand should contain 1 column(s)

2643

select (select * from t1) = 1;

2644

ERROR 21000: Operand should contain 2 column(s)

2645

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

2646

ERROR 21000: Operand should contain 2 column(s)

2647

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

2648

ERROR 21000: Operand should contain 1 column(s)

2649

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

2650

ERROR 21000: Operand should contain 3 column(s)

2651

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

2652

ERROR 21000: Operand should contain 2 column(s)

2653

drop table t1;

2654

2655

# Item_int_with_ref check (BUG#10020)

2656

2657

#CREATE TABLE `t1` (

2658

# `itemid` bigint NOT NULL auto_increment,

2659

# `sessionid` bigint default NULL,

2660

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

2661

# `data` text collate latin1_general_ci NOT NULL,

2662

# PRIMARY KEY (`itemid`)

2663

#);

2664

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

2665

#CREATE TABLE `t2` (

2666

# `sessionid` bigint NOT NULL auto_increment,

2667

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

2668

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

2669

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

2670

# PRIMARY KEY (`sessionid`)

2671

#);

2672

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

2673

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

2674

#drop tables t1,t2;

2675

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

2676

# segfaults:

2677

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

2678

insert into t1 values ('1');

2679

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

2680

max(fld)

2681

2682

drop table t1;

2683

2684

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

2685

2686

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

2687

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

2688

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

2689

INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');

2690

SELECT * FROM t1

2691

WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');

2692

one two flag

2693

5 6 N

2694

7 8 N

2695

SELECT * FROM t1

2696

WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');

2697

one two flag

2698

5 6 N

2699

7 8 N

2700

insert into t2 values (null,null,'N');

2701

insert into t2 values (null,3,'0');

2702

insert into t2 values (null,5,'0');

2703

insert into t2 values (10,null,'0');

2704

insert into t1 values (10,3,'0');

2705

insert into t1 values (10,5,'0');

2706

insert into t1 values (10,10,'0');

2707

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;

2708

one two test

2709

1 2 NULL

2710

2 3 NULL

2711

3 4 NULL

2712

5 6 1

2713

7 8 1

2714

10 3 NULL

2715

10 5 NULL

2716

10 10 NULL

2717

SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');

2718

one two

2719

5 6

2720

7 8

2721

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

2722

one two test

2723

1 2 NULL

2724

2 3 NULL

2725

3 4 NULL

2726

5 6 1

2727

7 8 1

2728

10 3 NULL

2729

10 5 NULL

2730

10 10 NULL

2731

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

2732

one two test

2733

1 2 0

2734

2 3 NULL

2735

3 4 0

2736

5 6 0

2737

7 8 0

2738

10 3 NULL

2739

10 5 NULL

2740

10 10 NULL

2741

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

2742

one two test

2743

1 2 0

2744

2 3 NULL

2745

3 4 0

2746

5 6 0

2747

7 8 0

2748

10 3 NULL

2749

10 5 NULL

2750

10 10 NULL

2751

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

2752

id select_type table type possible_keys key key_len ref rows filtered Extra

2753

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2754

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where

2755

Warnings:

2756

Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`

2757

explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');

2758

id select_type table type possible_keys key key_len ref rows filtered Extra

2759

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where

2760

2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where

2761

Warnings:

2762

Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key)))

2763

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;

2764

id select_type table type possible_keys key key_len ref rows filtered Extra

2765

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2766

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort

2767

Warnings:

2768

Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`

2769

DROP TABLE t1,t2;

2770

2771

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

2772

2773

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

2774

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

2775

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

2776

a b

2777

aaa aaa

2778

DROP TABLE t1;

2779

2780

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

2781

2782

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

2783

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

2784

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

2785

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

2786

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

2787

SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2788

WHERE t3.b IS NOT NULL OR t2.a > 10;

2789

a b b

2790

SELECT * FROM t1

2791

WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2792

WHERE t3.b IS NOT NULL OR t2.a > 10);

2793

2794

2795

2796

2797

2798

DROP TABLE t1,t2,t3;

2799

2800

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

2801

# return a wrong result.

2802

2803

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

2804

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

2805

INSERT INTO t1 VALUES (1);

2806

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

2807

2808

2809

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

2810

2811

2812

INSERT INTO t2 VALUES (1);

2813

INSERT INTO t2 VALUES (2);

2814

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

2815

2816

2817

DROP TABLE t1, t2;

2818

# BUG#20975 Wrong query results for subqueries within NOT

2819

create table t1 (s1 char);

2820

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

2821

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

2822

2823

2824

select * from t1 where not (s1 < any (select s1 from t1));

2825

2826

2827

select * from t1 where (s1 < ALL (select s1+1 from t1));

2828

2829

2830

select * from t1 where not(s1 < ALL (select s1+1 from t1));

2831

2832

2833

select * from t1 where (s1+1 = ANY (select s1 from t1));

2834

2835

2836

select * from t1 where NOT(s1+1 = ANY (select s1 from t1));

2837

2838

2839

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

2840

2841

2842

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

2843

2844

2845

drop table t1;

2846

2847

# Bug #16255: Subquery in where

2848

2849

create table t1 (

2850

retailerID varchar(8) NOT NULL,

2851

statusID int NOT NULL,

2852

changed datetime NOT NULL,

2853

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

2854

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

2855

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

2856

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

2857

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

2858

INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");

2859

INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");

2860

select * from t1 r1

2861

where (r1.retailerID,(r1.changed)) in

2862

(SELECT r2.retailerId,(max(changed)) from t1 r2

2863

group by r2.retailerId);

2864

retailerID statusID changed

2865

0026 2 2006-01-06 12:25:53

2866

0037 2 2006-01-06 12:25:53

2867

0048 1 2006-01-06 12:37:50

2868

0059 1 2006-01-06 12:37:50

2869

drop table t1;

2870

2871

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

2872

# produces empty result

2873

2874

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

2875

insert into t1 values (10);

2876

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

2877

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

2878

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

2879

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

2880

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

2881

id select_type table type possible_keys key key_len ref rows Extra

2882

1 PRIMARY t1 const PRIMARY PRIMARY 4 const # Using index

2883

1 PRIMARY r const PRIMARY PRIMARY 4 const #

2884

2 DEPENDENT SUBQUERY t2 ref b b 5 # Using where; Using index

2885

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

2886

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

2887

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

2888

a a b

2889

10 3 35989

2890

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

2891

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

2892

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

2893

id select_type table type possible_keys key key_len ref rows Extra

2894

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2895

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2896

2 DEPENDENT SUBQUERY t2 ref b b 5 1 Using where; Using index

2897

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

2898

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

2899

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

2900

a a b

2901

10 1 359

2902

drop table t1,t2;

2903

2904

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

2905

# an ALL/ANY quantified subquery in HAVING

2906

2907

CREATE TEMPORARY TABLE t1 (

2908

field1 int NOT NULL,

2909

field2 int NOT NULL,

2910

field3 int NOT NULL,

2911

PRIMARY KEY (field1,field2,field3))

2912

ENGINE=MyISAM;

2913

CREATE TEMPORARY TABLE t2 (

2914

fieldA int NOT NULL,

2915

fieldB int NOT NULL,

2916

PRIMARY KEY (fieldA,fieldB))

2917

ENGINE=MyISAM;

2918

INSERT INTO t1 VALUES

2919

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

2920

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

2921

SELECT field1, field2, COUNT(*)

2922

FROM t1 GROUP BY field1, field2;

2923

field1 field2 COUNT(*)

2924

1 1 2

2925

1 2 3

2926

1 3 1

2927

SELECT field1, field2

2928

FROM t1

2929

GROUP BY field1, field2

2930

HAVING COUNT(*) >= ALL (SELECT fieldB

2931

FROM t2 WHERE fieldA = field1);

2932

field1 field2

2933

1 2

2934

SELECT field1, field2

2935

FROM t1

2936

GROUP BY field1, field2

2937

HAVING COUNT(*) < ANY (SELECT fieldB

2938

FROM t2 WHERE fieldA = field1);

2939

field1 field2

2940

1 1

2941

1 3

2942

DROP TABLE t1, t2;

2943

2944

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

2945

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

2946

2947

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

2948

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

2949

INSERT INTO t1 VALUES (NULL);

2950

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

2951

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

2952

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

2953

id select_type table type possible_keys key key_len ref rows Extra

2954

1 PRIMARY t2 ALL NULL NULL NULL NULL 3

2955

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key

2956

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

2957

a a IN (SELECT a FROM t1)

2958

1 1

2959

2 NULL

2960

3 1

2961

DROP TABLE t1,t2;

2962

2963

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

2964

2965

CREATE TABLE t1 (a DATETIME);

2966

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

2967

CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT

2968

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

2969

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

2970

SHOW CREATE TABLE t2;

2971

Table Create Table

2972

t2 CREATE TEMPORARY TABLE `t2` (

2973

`sub_a` datetime DEFAULT NULL

2974

) ENGINE=MyISAM

2975

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

2976

SHOW CREATE TABLE t3;

2977

Table Create Table

2978

t3 CREATE TEMPORARY TABLE `t3` (

2979

`a` datetime DEFAULT NULL

2980

) ENGINE=MyISAM

2981

DROP TABLE t1,t2,t3;

2982

2983

# Bug 24653: sorting by expressions containing subselects

2984

# that return more than one row

2985

2986

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

2987

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

2988

CREATE TABLE t2 (b int, c int);

2989

INSERT INTO t2 VALUES

2990

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

2991

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

2992

2993

2994

2995

2996

2997

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

2998

ERROR 21000: Subquery returns more than 1 row

2999

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

3000

3001

3002

3003

3004

3005

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

3006

ERROR 21000: Subquery returns more than 1 row

3007

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

3008

b MAX(c)

3009

1 4

3010

2 2

3011

4 4

3012

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

3013

ERROR 21000: Subquery returns more than 1 row

3014

SELECT a FROM t1 GROUP BY a

3015

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

3016

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

3017

3018

3019

3020

3021

3022

SELECT a FROM t1 GROUP BY a

3023

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

3024

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

3025

ERROR 21000: Subquery returns more than 1 row

3026

SELECT a FROM t1 GROUP BY a

3027

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

3028

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

3029

3030

3031

SELECT a FROM t1 GROUP BY a

3032

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

3033

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

3034

ERROR 21000: Subquery returns more than 1 row

3035

SELECT a FROM t1

3036

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

3037

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

3038

3039

3040

3041

3042

3043

SELECT a FROM t1

3044

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

3045

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

3046

ERROR 21000: Subquery returns more than 1 row

3047

SELECT a FROM t1

3048

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

3049

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

3050

3051

3052

3053

3054

3055

SELECT a FROM t1

3056

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

3057

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

3058

ERROR 21000: Subquery returns more than 1 row

3059

DROP TABLE t1,t2;

3060

# End of 4.1 tests

3061

3062

#decimal-related tests

3063

3064

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

3065

insert into t1 values(1.1);

3066

insert into t1 values(2.2);

3067

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

3068

3069

1.1

3070

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

3071

3072

2.2

3073

drop table t1;

3074

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

3075

insert into t1 values(1.1);

3076

select 1.1 * exists(select * from t1);

3077

1.1 * exists(select * from t1)

3078

1.1

3079

drop table t1;

3080

CREATE TEMPORARY TABLE t1 (

3081

grp int default NULL,

3082

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

3083

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

3084

select * from t1;

3085

grp a

3086

1 1.00

3087

2 2.00

3088

2 3.00

3089

3 4.00

3090

3 5.00

3091

3 6.00

3092

NULL NULL

3093

select min(a) from t1 group by grp;

3094

min(a)

3095

NULL

3096

1.00

3097

2.00

3098

4.00

3099

drop table t1;

3100

3101

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

3102

3103

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

3104

INSERT INTO t1 VALUES ( 1 );

3105

INSERT INTO t1 VALUES ( 2 );

3106

INSERT INTO t1 VALUES ( 3 );

3107

CREATE TABLE t2 ( c2 integer );

3108

INSERT INTO t2 VALUES ( 1 );

3109

INSERT INTO t2 VALUES ( 4 );

3110

INSERT INTO t2 VALUES ( 5 );

3111

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);

3112

c1 c2

3113

1 1

3114

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2

3115

WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );

3116

c1 c2

3117

1 1

3118

DROP TABLE t1,t2;

3119

3120

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

3121

3122

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

3123

INSERT INTO t1 VALUES ( 1 );

3124

INSERT INTO t1 VALUES ( 2 );

3125

INSERT INTO t1 VALUES ( 3 );

3126

INSERT INTO t1 VALUES ( 6 );

3127

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

3128

INSERT INTO t2 VALUES ( 1 );

3129

INSERT INTO t2 VALUES ( 4 );

3130

INSERT INTO t2 VALUES ( 5 );

3131

INSERT INTO t2 VALUES ( 6 );

3132

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

3133

INSERT INTO t3 VALUES ( 7 );

3134

INSERT INTO t3 VALUES ( 8 );

3135

SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2

3136

WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );

3137

c1 c2

3138

2 NULL

3139

3 NULL

3140

DROP TABLE t1,t2,t3;

3141

3142

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

3143

# fields) (BUG#6384)

3144

3145

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

3146

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

3147

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

3148

INSERT INTO t2 VALUES ('E1');

3149

DELETE FROM t1

3150

WHERE t1.EMPNUM NOT IN

3151

(SELECT t2.EMPNUM

3152

FROM t2

3153

WHERE t1.EMPNUM = t2.EMPNUM);

3154

select * from t1;

3155

EMPNUM

3156

3157

DROP TABLE t1,t2;

3158

3159

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

3160

3161

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

3162

INSERT INTO t1 VALUES (1, 1);

3163

CREATE TEMPORARY TABLE t2 (select_id BIGINT, values_id BIGINT,

3164

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

3165

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

3166

SELECT values_id FROM t1

3167

WHERE values_id IN (SELECT values_id FROM t2

3168

WHERE select_id IN (1, 0));

3169

values_id

3170

3171

SELECT values_id FROM t1

3172

WHERE values_id IN (SELECT values_id FROM t2

3173

WHERE select_id BETWEEN 0 AND 1);

3174

values_id

3175

3176

SELECT values_id FROM t1

3177

WHERE values_id IN (SELECT values_id FROM t2

3178

WHERE select_id = 0 OR select_id = 1);

3179

values_id

3180

3181

DROP TABLE t1, t2;

3182

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

3183

# segfaults:

3184

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

3185

insert into t1 values ('1');

3186

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

3187

max(fld)

3188

3189

drop table t1;

3190

3191

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

3192

3193

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

3194

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

3195

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

3196

INSERT INTO t1 VALUES

3197

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

3198

INSERT INTO t2 VALUES

3199

(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);

3200

INSERT INTO t3 VALUES (10), (30), (10), (20) ;

3201

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

3202

a MAX(b) MIN(b)

3203

1 20 10

3204

2 30 10

3205

3 20 20

3206

4 40 40

3207

SELECT * FROM t2;

3208

c d

3209

2 10

3210

2 20

3211

4 10

3212

5 10

3213

3 20

3214

2 40

3215

SELECT * FROM t3;

3216

3217

3218

3219

3220

3221

SELECT a FROM t1 GROUP BY a

3222

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);

3223

3224

3225

3226

SELECT a FROM t1 GROUP BY a

3227

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);

3228

3229

3230

SELECT a FROM t1 GROUP BY a

3231

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);

3232

3233

3234

3235

SELECT a FROM t1 GROUP BY a

3236

HAVING a IN (SELECT c FROM t2

3237

WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));

3238

3239

3240

3241

SELECT a FROM t1 GROUP BY a

3242

HAVING a IN (SELECT c FROM t2

3243

WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

3244

3245

3246

3247

SELECT a FROM t1 GROUP BY a

3248

HAVING a IN (SELECT c FROM t2

3249

WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));

3250

3251

3252

SELECT a FROM t1 GROUP BY a

3253

HAVING a IN (SELECT c FROM t2

3254

WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));

3255

3256

3257

SELECT a FROM t1 GROUP BY a

3258

HAVING a IN (SELECT c FROM t2

3259

WHERE MIN(b) < d AND

3260

EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

3261

3262

3263

SELECT a, SUM(a) FROM t1 GROUP BY a;

3264

a SUM(a)

3265

1 2

3266

2 6

3267

3 3

3268

4 4

3269

SELECT a FROM t1

3270

WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;

3271

3272

3273

3274

SELECT a FROM t1 GROUP BY a

3275

HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);

3276

3277

3278

3279

3280

SELECT a FROM t1

3281

WHERE a < 3 AND

3282

EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;

3283

3284

3285

3286

SELECT a FROM t1

3287

WHERE a < 3 AND

3288

EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);

3289

3290

3291

3292

3293

3294

3295

SELECT t1.a FROM t1 GROUP BY t1.a

3296

HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c

3297

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

3298

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

3299

3300

3301

3302

SELECT t1.a FROM t1 GROUP BY t1.a

3303

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

3304

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

3305

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

3306

3307

3308

SELECT t1.a FROM t1 GROUP BY t1.a

3309

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

3310

WHERE EXISTS(SELECT t3.e FROM t3

3311

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

3312

ERROR HY000: Invalid use of group function

3313

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

3314

ERROR HY000: Invalid use of group function

3315

SELECT t1.a FROM t1 GROUP BY t1.a

3316

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3317

HAVING AVG(t2.c+SUM(t1.b)) > 20);

3318

3319

3320

3321

3322

SELECT t1.a FROM t1 GROUP BY t1.a

3323

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3324

HAVING AVG(SUM(t1.b)) > 20);

3325

3326

3327

3328

SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a

3329

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3330

HAVING t2.c+sum > 20);

3331

a sum

3332

2 60

3333

3 20

3334

4 40

3335

DROP TABLE t1,t2,t3;

3336

3337

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

3338

# when an index is defined on the grouping field

3339

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

3340

INSERT INTO t1 VALUES

3341

('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),

3342

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

3343

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

3344

a b

3345

BBB 4

3346

CCC 7

3347

AAA 8

3348

EXPLAIN

3349

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

3350

id select_type table type possible_keys key key_len ref rows Extra

3351

1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where

3352

2 SUBQUERY t1 ALL NULL NULL NULL NULL # Using temporary; Using filesort

3353

ALTER TABLE t1 ADD INDEX(a);

3354

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

3355

a b

3356

BBB 4

3357

CCC 7

3358

AAA 8

3359

EXPLAIN

3360

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

3361

id select_type table type possible_keys key key_len ref rows Extra

3362

1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where

3363

2 SUBQUERY t1 index NULL a 23 NULL #

3364

DROP TABLE t1;

3365

3366

# Bug#17366: Unchecked Item_int results in server crash

3367

3368

create table t1( f1 int,f2 int);

3369

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

3370

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

3371

3372

crash1

3373

crash1

3374

drop table t1;

3375

3376

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

3377

3378

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

3379

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

3380

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

3381

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

3382

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

3383

drop table t1, t2;

3384

3385

# Bug#19077: A nested materialized derived table is used before being populated.

3386

3387

create table t1 (i int, j bigint);

3388

insert into t1 values (1, 2), (2, 2), (3, 2);

3389

select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;

3390

min(i)

3391

3392

drop table t1;

3393

3394

# Bug#19700: subselect returning BIGINT always returned it as SIGNED

3395

3396

CREATE TEMPORARY TABLE t1 (i BIGINT) ENGINE=MyISAM;

3397

INSERT INTO t1 VALUES (10000000000000000);

3398

INSERT INTO t1 VALUES (1);

3399

CREATE TEMPORARY TABLE t2 (i BIGINT) ENGINE=MyISAM;

3400

INSERT INTO t2 VALUES (10000000000000000);

3401

INSERT INTO t2 VALUES (1);

3402

/* simple test */

3403

SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;

3404

3405

10000000000000000

3406

3407

/* subquery test */

3408

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3409

3410

10000000000000000

3411

/* subquery test with cast*/

3412

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3413

3414

10000000000000000

3415

DROP TABLE t1;

3416

DROP TABLE t2;

3417

3418

# Bug#20519: subselect with LIMIT M, N

3419

3420

CREATE TEMPORARY TABLE t1 (

3421

id bigint NOT NULL auto_increment,

3422

name varchar(255) NOT NULL,

3423

PRIMARY KEY (id))

3424

ENGINE=MyISAM;

3425

INSERT INTO t1 VALUES

3426

(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

3427

CREATE TABLE t2 (

3428

id bigint NOT NULL auto_increment,

3429

mid bigint NOT NULL,

3430

date date NOT NULL,

3431

PRIMARY KEY (id));

3432

INSERT INTO t2 VALUES

3433

(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),

3434

(4, 2, '2006-04-20'), (5, 1, '2006-05-01');

3435

SELECT *,

3436

(SELECT date FROM t2 WHERE mid = t1.id

3437

ORDER BY date DESC LIMIT 0, 1) AS date_last,

3438

(SELECT date FROM t2 WHERE mid = t1.id

3439

ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last

3440

FROM t1;

3441

id name date_last date_next_to_last

3442

1 Balazs 2006-05-01 NULL

3443

2 Joe 2006-04-20 NULL

3444

3 Frank 2006-04-13 NULL

3445

SELECT *,

3446

(SELECT COUNT(*) FROM t2 WHERE mid = t1.id

3447

ORDER BY date DESC LIMIT 1, 1) AS date_count

3448

FROM t1;

3449

id name date_count

3450

1 Balazs NULL

3451

2 Joe NULL

3452

3 Frank NULL

3453

SELECT *,

3454

(SELECT date FROM t2 WHERE mid = t1.id

3455

ORDER BY date DESC LIMIT 0, 1) AS date_last,

3456

(SELECT date FROM t2 WHERE mid = t1.id

3457

ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last

3458

FROM t1;

3459

id name date_last date_next_to_last

3460

1 Balazs 2006-05-01 2006-03-30

3461

2 Joe 2006-04-20 2006-04-06

3462

3 Frank 2006-04-13 NULL

3463

DROP TABLE t1,t2;

3464

3465

# Bug#20869: subselect with range access by DESC

3466

3467

CREATE TABLE t1 (

3468

i1 int NOT NULL default '0',

3469

i2 int NOT NULL default '0',

3470

t datetime,

3471

PRIMARY KEY (i1,i2,t));

3472

INSERT INTO t1 VALUES

3473

(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),

3474

(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),

3475

(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),

3476

(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),

3477

(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),

3478

(24,2,'2005-05-27 12:40:06');

3479

CREATE TABLE t2 (

3480

i1 int NOT NULL default '0',

3481

i2 int NOT NULL default '0',

3482

t datetime default NULL,

3483

PRIMARY KEY (i1));

3484

INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');

3485

EXPLAIN

3486

SELECT * FROM t1,t2

3487

WHERE t1.t = (SELECT t1.t FROM t1

3488

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3489

ORDER BY t1.t DESC LIMIT 1);

3490

id select_type table type possible_keys key key_len ref rows Extra

3491

1 PRIMARY t2 ALL NULL NULL NULL NULL 1

3492

1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index; Using join buffer

3493

2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 5 Using where; Using index; Using filesort

3494

SELECT * FROM t1,t2

3495

WHERE t1.t = (SELECT t1.t FROM t1

3496

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3497

ORDER BY t1.t DESC LIMIT 1);

3498

i1 i2 t i1 i2 t

3499

24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40

3500

DROP TABLE t1, t2;

3501

3502

# Bug#14654 : Cannot select from the same table twice within a UNION

3503

# statement

3504

3505

CREATE TABLE t1 (i INT);

3506

(SELECT i FROM t1) UNION (SELECT i FROM t1);

3507

3508

SELECT * FROM t1 WHERE NOT EXISTS

3509

(

3510

(SELECT i FROM t1) UNION

3511

(SELECT i FROM t1)

3512

);

3513

3514

#TODO:not supported

3515

SELECT * FROM t1

3516

WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));

3517

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1

3518

#TODO:not supported

3519

explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))

3520

from t1;

3521

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'union (select t12.i from t1 t12))

3522

from t1' at line 1

3523

explain select * from t1 where not exists

3524

((select t11.i from t1 t11) union (select t12.i from t1 t12));

3525

id select_type table type possible_keys key key_len ref rows Extra

3526

1 PRIMARY t1 ALL NULL NULL NULL NULL #

3527

2 SUBQUERY t11 ALL NULL NULL NULL NULL #

3528

3 UNION t12 ALL NULL NULL NULL NULL #

3529

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL #

3530

DROP TABLE t1;

3531

3532

# Bug #21540: Subqueries with no from and aggregate functions return

3533

# wrong results

3534

CREATE TABLE t1 (a INT, b INT);

3535

CREATE TABLE t2 (a INT);

3536

INSERT INTO t2 values (1);

3537

INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);

3538

SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;

3539

(SELECT COUNT(DISTINCT t1.b) from t2)

3540

3541

3542

3543

SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)

3544

FROM t1 GROUP BY t1.a;

3545

(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)

3546

3547

3548

3549

SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;

3550

COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))

3551

2 2

3552

1 1

3553

1 1

3554

SELECT (

3555

SELECT (

3556

SELECT (

3557

SELECT COUNT(DISTINCT t1.b)

3558

)

3559

)

3560

FROM t1 GROUP BY t1.a LIMIT 1)

3561

FROM t1 t2

3562

GROUP BY t2.a;

3563

(

3564

SELECT (

3565

SELECT (

3566

SELECT COUNT(DISTINCT t1.b)

3567

)

3568

)

3569

FROM t1 GROUP BY t1.a LIMIT 1)

3570

3571

3572

3573

DROP TABLE t1,t2;

3574

3575

# Bug #21727: Correlated subquery that requires filesort:

3576

# slow with big sort_buffer_size

3577

3578

CREATE TEMPORARY TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;

3579

CREATE TEMPORARY TABLE t2 (x int auto_increment, y int, z int,

3580

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;

3581

SET SESSION sort_buffer_size = 32 * 1024;

3582

Warnings:

3583

Error 1292 Truncated incorrect sort_buffer_size value: '32768'

3584

SELECT COUNT(*)

3585

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3586

FROM t1) t;

3587

COUNT(*)

3588

3000

3589

SET SESSION sort_buffer_size = 8 * 1024 * 1024;

3590

SELECT COUNT(*)

3591

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3592

FROM t1) t;

3593

COUNT(*)

3594

3000

3595

DROP TABLE t2,t1;

3596

3597

# Bug #25219: EXIST subquery with UNION over a mix of

3598

# correlated and uncorrelated selects

3599

3600

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);

3601

CREATE TABLE t2 (c int);

3602

INSERT INTO t1 VALUES ('aa', 1);

3603

INSERT INTO t2 VALUES (1);

3604

SELECT * FROM t1

3605

WHERE EXISTS (SELECT c FROM t2 WHERE c=1

3606

UNION

3607

SELECT c from t2 WHERE c=t1.c);

3608

id c

3609

aa 1

3610

INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);

3611

SELECT * FROM t1

3612

WHERE EXISTS (SELECT c FROM t2 WHERE c=1

3613

UNION

3614

SELECT c from t2 WHERE c=t1.c);

3615

id c

3616

aa 1

3617

bb 2

3618

cc 3

3619

dd 1

3620

INSERT INTO t2 VALUES (2);

3621

CREATE TEMPORARY TABLE t3 (c int) ENGINE=MyISAM;

3622

INSERT INTO t3 VALUES (1);

3623

SELECT * FROM t1

3624

WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1

3625

UNION

3626

SELECT c from t2 WHERE c=t1.c);

3627

id c

3628

aa 1

3629

bb 2

3630

cc 3

3631

dd 1

3632

DROP TABLE t1,t2,t3;

3633

3634

# Bug#21904 (parser problem when using IN with a double "(())")

3635

3636

DROP TABLE IF EXISTS t1;

3637

DROP TABLE IF EXISTS t2;

3638

DROP TABLE IF EXISTS t1xt2;

3639

CREATE TEMPORARY TABLE t1 (

3640

id_1 int NOT NULL,

3641

t varchar(4) DEFAULT NULL)

3642

ENGINE=MyISAM;

3643

CREATE TEMPORARY TABLE t2 (

3644

id_2 int NOT NULL,

3645

t varchar(4) DEFAULT NULL)

3646

ENGINE=MyISAM;

3647

CREATE TEMPORARY TABLE t1xt2 (

3648

id_1 int NOT NULL,

3649

id_2 int NOT NULL)

3650

ENGINE=MyISAM;

3651

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

3652

INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');

3653

INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);

3654

# subselect returns 0 rows

3655

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3656

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3657

id_1

3658

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3659

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3660

id_1

3661

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3662

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3663

id_1

3664

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3665

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3666

id_1

3667

3668

3669

3670

3671

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3672

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));

3673

id_1

3674

3675

3676

3677

3678

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3679

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));

3680

id_1

3681

3682

3683

3684

3685

insert INTO t1xt2 VALUES (1, 12);

3686

# subselect returns 1 row

3687

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3688

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3689

id_1

3690

3691

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3692

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3693

id_1

3694

3695

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3696

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3697

id_1

3698

3699

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3700

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3701

id_1

3702

3703

3704

3705

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3706

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3707

id_1

3708

3709

3710

3711

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3712

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3713

id_1

3714

3715

3716

3717

insert INTO t1xt2 VALUES (2, 12);

3718

# subselect returns more than 1 row

3719

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3720

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3721

id_1

3722

3723

3724

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3725

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3726

id_1

3727

3728

3729

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3730

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3731

id_1

3732

3733

3734

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3735

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3736

id_1

3737

3738

3739

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3740

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3741

id_1

3742

3743

3744

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3745

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3746

id_1

3747

3748

3749

DROP TABLE t1;

3750

DROP TABLE t2;

3751

DROP TABLE t1xt2;

3752

3753

# Bug #26728: derived table with concatanation of literals in select list

3754

3755

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

3756

INSERT INTO t1 VALUES (3), (1), (2);

3757

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;

3758

col1 col2

3759

this is a test. 3

3760

this is a test. 1

3761

this is a test. 2

3762

SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;

3763

col1 t2

3764

this is a test. 3

3765

this is a test. 1

3766

this is a test. 2

3767

DROP table t1;

3768

3769

# Bug #27257: COUNT(*) aggregated in outer query

3770

3771

CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;

3772

CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;

3773

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

3774

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3775

SELECT COUNT(*), a,

3776

(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3777

FROM t1 GROUP BY a;

3778

COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3779

2 2 2

3780

3 3 3

3781

1 4 1

3782

SELECT COUNT(*), a,

3783

(SELECT MIN(m) FROM t2 WHERE m = count(*))

3784

FROM t1 GROUP BY a;

3785

COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))

3786

2 2 2

3787

3 3 3

3788

1 4 1

3789

SELECT COUNT(*), a

3790

FROM t1 GROUP BY a

3791

HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;

3792

COUNT(*) a

3793

2 2

3794

3 3

3795

DROP TABLE t1,t2;

3796

3797

# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument

3798

3799

CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;

3800

CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;

3801

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

3802

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3803

SELECT COUNT(*) c, a,

3804

(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3805

FROM t1 GROUP BY a;

3806

c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3807

2 2 2

3808

3 3 3

3809

1 4 1,1

3810

SELECT COUNT(*) c, a,

3811

(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3812

FROM t1 GROUP BY a;

3813

c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3814

2 2 3

3815

3 3 4

3816

1 4 2,2

3817

DROP table t1,t2;

3818

3819

# Bug#27321: Wrong subquery result in a grouping select

3820

3821

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));

3822

INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),

3823

(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),

3824

(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');

3825

SELECT a, MAX(b),

3826

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test

3827

FROM t1 GROUP BY a;

3828

a MAX(b) test

3829

1 9 m

3830

2 3 h

3831

3 4 i

3832

SELECT a x, MAX(b),

3833

(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test

3834

FROM t1 GROUP BY a;

3835

x MAX(b) test

3836

1 9 m

3837

2 3 h

3838

3 4 i

3839

SELECT a, AVG(b),

3840

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test

3841

FROM t1 WHERE t1.d=0 GROUP BY a;

3842

a AVG(b) test

3843

1 4.0000 d

3844

2 2.0000 g

3845

3 2.5000 NULL

3846

SELECT tt.a,

3847

(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3848

LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3849

FROM t1 as tt;

3850

a test

3851

1 n

3852

1 n

3853

1 n

3854

1 n

3855

1 n

3856

1 n

3857

1 n

3858

2 o

3859

2 o

3860

2 o

3861

2 o

3862

3 p

3863

3 p

3864

3 p

3865

3 p

3866

3 p

3867

SELECT tt.a,

3868

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3869

LIMIT 1)

3870

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3871

FROM t1 as tt GROUP BY tt.a;

3872

a test

3873

1 n

3874

2 o

3875

3 p

3876

SELECT tt.a, MAX(

3877

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3878

LIMIT 1)

3879

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test

3880

FROM t1 as tt GROUP BY tt.a;

3881

a test

3882

1 n

3883

2 o

3884

3 p

3885

DROP TABLE t1;

3886

3887

# Bug #27363: nested aggregates in outer, subquery / sum(select

3888

# count(outer))

3889

3890

CREATE TABLE t1 (a INT);

3891

INSERT INTO t1 values (1),(1),(1),(1);

3892

CREATE TEMPORARY TABLE t2 (x INT) ENGINE=MyISAM;

3893

INSERT INTO t1 values (1000),(1001),(1002);

3894

SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;

3895

ERROR HY000: Invalid use of group function

3896

SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;

3897

ERROR HY000: Invalid use of group function

3898

SELECT

3899

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )

3900

FROM t1;

3901

ERROR HY000: Invalid use of group function

3902

SELECT t1.a as XXA,

3903

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )

3904

FROM t1;

3905

ERROR HY000: Invalid use of group function

3906

DROP TABLE t1,t2;

3907

3908

# Bug #27807: Server crash when executing subquery with EXPLAIN

3909

3910

CREATE TABLE t1 (a int, b int, KEY (a));

3911

INSERT INTO t1 VALUES (1,1),(2,1);

3912

EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);

3913

id select_type table type possible_keys key key_len ref rows Extra

3914

1 PRIMARY t1 ref a a 5 const 1 Using where; Using index

3915

2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort

3916

DROP TABLE t1;

3917

3918

# Bug #28377: grouping query with a correlated subquery in WHERE condition

3919

3920

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));

3921

INSERT INTO t1 VALUES

3922

(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');

3923

CREATE TEMPORARY TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;

3924

INSERT INTO t2 VALUES (7), (5), (1), (3);

3925

SELECT id, st FROM t1

3926

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3927

id st

3928

3 FL

3929

1 GA

3930

7 FL

3931

SELECT id, st FROM t1

3932

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)

3933

GROUP BY id;

3934

id st

3935

1 GA

3936

3 FL

3937

7 FL

3938

SELECT id, st FROM t1

3939

WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3940

id st

3941

2 GA

3942

4 FL

3943

SELECT id, st FROM t1

3944

WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)

3945

GROUP BY id;

3946

id st

3947

2 GA

3948

4 FL

3949

DROP TABLE t1,t2;

3950

3951

# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table

3952

# over a grouping subselect

3953

3954

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

3955

INSERT INTO t1 VALUES (1), (2);

3956

EXPLAIN EXTENDED

3957

SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;

3958

id select_type table type possible_keys key key_len ref rows filtered Extra

3959

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00

3960

2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort

3961

Warnings:

3962

Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`

3963

DROP TABLE t1;

3964

3965

# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1

3966

3967

CREATE TABLE t1 (

3968

a varchar(255) default NULL,

3969

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3970

INDEX idx(a,b));

3971

Warnings:

3972

Warning 1071 Specified key was too long; max key length is 767 bytes

3973

CREATE TABLE t2 (

3974

a varchar(255) default NULL);

3975

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');

3976

INSERT INTO t1 SELECT * FROM t1;

3977

INSERT INTO t1 SELECT * FROM t1;

3978

INSERT INTO t1 SELECT * FROM t1;

3979

INSERT INTO t1 SELECT * FROM t1;

3980

INSERT INTO t1 SELECT * FROM t1;

3981

INSERT INTO t1 SELECT * FROM t1;

3982

INSERT INTO t1 SELECT * FROM t1;

3983

INSERT INTO t1 SELECT * FROM t1;

3984

INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');

3985

INSERT INTO `t2` VALUES ('abcdefghijk');

3986

INSERT INTO `t2` VALUES ('asdf');

3987

SET session sort_buffer_size=8192;

3988

Warnings:

3989

Error 1292 Truncated incorrect sort_buffer_size value: '8192'

3990

SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;

3991

3992

3993

3994

DROP TABLE t1,t2;

3995

3996

# Bug #27333: subquery grouped for aggregate of outer query / no aggregate

3997

# of subquery

3998

3999

CREATE TEMPORARY TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;

4000

CREATE TEMPORARY TABLE t2 (x INTEGER) ENGINE=MyISAM;

4001

INSERT INTO t1 VALUES (1,11), (2,22), (2,22);

4002

INSERT INTO t2 VALUES (1), (2);

4003

# wasn't failing, but should

4004

SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;

4005

ERROR 21000: Subquery returns more than 1 row

4006

# fails as it should

4007

SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;

4008

ERROR 21000: Subquery returns more than 1 row

4009

SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;

4010

(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)

4011

3.3333

4012

DROP TABLE t1,t2;

4013

# second test case from 27333

4014

CREATE TABLE t1 (a INT, b INT);

4015

INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);

4016

# returns no rows, when it should

4017

SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1

4018

AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)

4019

GROUP BY a1.a;

4020

a COUNT(*)

4021

1 3

4022

DROP TABLE t1;

4023

#test cases from 29297

4024

CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;

4025

CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MyISAM;

4026

INSERT INTO t1 VALUES (1),(2);

4027

INSERT INTO t2 VALUES (1),(2);

4028

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;

4029

(SELECT SUM(t1.a) FROM t2 WHERE a=0)

4030

NULL

4031

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;

4032

ERROR 21000: Subquery returns more than 1 row

4033

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;

4034

(SELECT SUM(t1.a) FROM t2 WHERE a=1)

4035

4036

DROP TABLE t1,t2;

4037

4038

# Bug #31884: Assertion + crash in subquery in the SELECT clause.

4039

4040

CREATE TEMPORARY TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;

4041

CREATE TEMPORARY TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;

4042

INSERT INTO t1 VALUES (100, 200);

4043

INSERT INTO t1 VALUES (101, 201);

4044

INSERT INTO t2 VALUES (101, 201);

4045

INSERT INTO t2 VALUES (103, 203);

4046

SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;

4047

((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL

4048

4049

4050

DROP TABLE t1, t2;

4051

4052

# Bug #30788: Inconsistent retrieval of char/varchar

4053

4054

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));

4055

INSERT INTO t1 VALUES ('a', 'aa');

4056

INSERT INTO t1 VALUES ('a', 'aaa');

4057

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

4058

a b

4059

a aa

4060

a aaa

4061

CREATE INDEX I1 ON t1 (a);

4062

CREATE INDEX I2 ON t1 (b);

4063

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

4064

id select_type table type possible_keys key key_len ref rows Extra

4065

1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where

4066

2 SUBQUERY t1 index NULL I1 7 NULL # Using index

4067

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

4068

a b

4069

a aa

4070

a aaa

4071

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));

4072

INSERT INTO t2 SELECT * FROM t1;

4073

CREATE INDEX I1 ON t2 (a);

4074

CREATE INDEX I2 ON t2 (b);

4075

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

4076

id select_type table type possible_keys key key_len ref rows Extra

4077

1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where

4078

2 SUBQUERY t2 index NULL I1 7 NULL # Using index

4079

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

4080

a b

4081

a aa

4082

a aaa

4083

EXPLAIN

4084

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4085

id select_type table type possible_keys key key_len ref rows Extra

4086

1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where

4087

2 SUBQUERY t1 index NULL I1 7 NULL # Using where; Using index

4088

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4089

a b

4090

a aa

4091

a aaa

4092

DROP TABLE t1,t2;

4093

4094

# Bug #32400: Complex SELECT query returns correct result only on some

4095

# occasions

4096

4097

CREATE TABLE t1(a INT, b INT);

4098

INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);

4099

EXPLAIN

4100

SELECT a AS out_a, MIN(b) FROM t1

4101

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

4102

GROUP BY a;

4103

ERROR 42S22: Unknown column 'out_a' in 'where clause'

4104

SELECT a AS out_a, MIN(b) FROM t1

4105

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

4106

GROUP BY a;

4107

ERROR 42S22: Unknown column 'out_a' in 'where clause'

4108

EXPLAIN

4109

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

4110

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

4111

GROUP BY a;

4112

id select_type table type possible_keys key key_len ref rows Extra

4113

1 PRIMARY t1_outer ALL NULL NULL NULL NULL # Using where; Using temporary; Using filesort

4114

2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where

4115

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

4116

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

4117

GROUP BY a;

4118

out_a MIN(b)

4119

1 2

4120

2 4

4121

DROP TABLE t1;

4122

4123

# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122

4124

4125

CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;

4126

CREATE TABLE t2 (a INT);

4127

INSERT INTO t1 VALUES (1),(2);

4128

INSERT INTO t2 VALUES (1),(2);

4129

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

4130

4131

4132

4133

EXPLAIN EXTENDED

4134

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

4135

id select_type table type possible_keys key key_len ref rows filtered Extra

4136

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where

4137

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where

4138

Warnings:

4139

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

4140

Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))

4141

EXPLAIN EXTENDED

4142

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION

4143

(SELECT 1 FROM t2 WHERE t1.a = t2.a));

4144

id select_type table type possible_keys key key_len ref rows filtered Extra

4145

1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00 Using where

4146

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00 Using where

4147

3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL # 100.00 Using where

4148

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL

4149

Warnings:

4150

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

4151

Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1

4152

Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))

4153

DROP TABLE t1,t2;

4154

4155

# BUG#33794 "MySQL crashes executing specific query on specific dump"

4156

4157

CREATE TEMPORARY TABLE t4 (

4158

f7 varchar(32) collate utf8_bin NOT NULL default '',

4159

f10 varchar(32) collate utf8_bin default NULL,

4160

PRIMARY KEY (f7))

4161

ENGINE=MyISAM;

4162

INSERT INTO t4 VALUES(1,1), (2,null);

4163

CREATE TABLE t2 (

4164

f4 varchar(32) collate utf8_bin NOT NULL default '',

4165

f2 varchar(50) collate utf8_bin default NULL,

4166

f3 varchar(10) collate utf8_bin default NULL,

4167

PRIMARY KEY (f4),

4168

UNIQUE KEY uk1 (f2));

4169

INSERT INTO t2 VALUES(1,1,null), (2,2,null);

4170

CREATE TEMPORARY TABLE t1 (

4171

f8 varchar(32) collate utf8_bin NOT NULL default '',

4172

f1 varchar(10) collate utf8_bin default NULL,

4173

f9 varchar(32) collate utf8_bin default NULL,

4174

PRIMARY KEY (f8))

4175

ENGINE=MyISAM;

4176

INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);

4177

CREATE TEMPORARY TABLE t3 (

4178

f6 varchar(32) collate utf8_bin NOT NULL default '',

4179

f5 varchar(50) collate utf8_bin default NULL,

4180

PRIMARY KEY (f6))

4181

ENGINE=MyISAM;

4182

INSERT INTO t3 VALUES (1,null), (2,null);

4183

SELECT

4184

IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,

4185

IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,

4186

SUM(

4187

IF(

4188

(SELECT VPC.f2

4189

FROM t2 VPC, t4 a2, t2 a3

4190

WHERE

4191

VPC.f4 = a2.f10 AND a3.f2 = a4

4192

LIMIT 1) IS NULL,

4193

4194

t3.f5

4195

)

4196

) AS a6

4197

FROM

4198

t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4

4199

GROUP BY a4;

4200

a4 f3 a6

4201

1 NULL NULL

4202

2 NULL NULL

4203

DROP TABLE t1, t2, t3, t4;

4204

End of 5.0 tests.

4205

4206

# Test [NOT] IN truth table (both as top-level and general predicate).

4207

4208

create temporary table t_out (subcase char(3),

4209

a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;

4210

create table t_in (a2 char(2), b2 char(2), c2 char(2));

4211

insert into t_out values ('A.1','2a', NULL, '2a');

4212

#------------------------- A.2 - impossible

4213

insert into t_out values ('A.3', '2a', NULL, '2a');

4214

insert into t_out values ('A.4', '2a', NULL, 'xx');

4215

insert into t_out values ('B.1', '2a', '2a', '2a');

4216

insert into t_out values ('B.2', '2a', '2a', '2a');

4217

insert into t_out values ('B.3', '3a', 'xx', '3a');

4218

insert into t_out values ('B.4', 'xx', '3a', '3a');

4219

insert into t_in values ('1a', '1a', '1a');

4220

insert into t_in values ('2a', '2a', '2a');

4221

insert into t_in values (NULL, '2a', '2a');

4222

insert into t_in values ('3a', NULL, '3a');

4223

4224

Test general IN semantics (not top-level)

4225

4226

case A.1

4227

select subcase,

4228

(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,

4229

(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in

4230

from t_out where subcase = 'A.1';

4231

subcase pred_in pred_not_in

4232

A.1 0 1

4233

case A.2 - impossible

4234

case A.3

4235

select subcase,

4236

(a1, b1, c1) IN (select * from t_in) pred_in,

4237

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4238

from t_out where subcase = 'A.3';

4239

subcase pred_in pred_not_in

4240

A.3 NULL NULL

4241

case A.4

4242

select subcase,

4243

(a1, b1, c1) IN (select * from t_in) pred_in,

4244

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4245

from t_out where subcase = 'A.4';

4246

subcase pred_in pred_not_in

4247

A.4 0 1

4248

case B.1

4249

select subcase,

4250

(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,

4251

(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in

4252

from t_out where subcase = 'B.1';

4253

subcase pred_in pred_not_in

4254

B.1 0 1

4255

case B.2

4256

select subcase,

4257

(a1, b1, c1) IN (select * from t_in) pred_in,

4258

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4259

from t_out where subcase = 'B.2';

4260

subcase pred_in pred_not_in

4261

B.2 1 0

4262

case B.3

4263

select subcase,

4264

(a1, b1, c1) IN (select * from t_in) pred_in,

4265

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4266

from t_out where subcase = 'B.3';

4267

subcase pred_in pred_not_in

4268

B.3 NULL NULL

4269

case B.4

4270

select subcase,

4271

(a1, b1, c1) IN (select * from t_in) pred_in,

4272

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4273

from t_out where subcase = 'B.4';

4274

subcase pred_in pred_not_in

4275

B.4 0 1

4276

4277

Test IN as top-level predicate, and

4278

as non-top level for cases A.3, B.3 (the only cases with NULL result).

4279

4280

case A.1

4281

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4282

where subcase = 'A.1' and

4283

(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');

4284

pred_in

4285

4286

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4287

where subcase = 'A.1' and

4288

(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');

4289

pred_not_in

4290

4291

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4292

where subcase = 'A.1' and

4293

NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));

4294

not_pred_in

4295

4296

case A.3

4297

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4298

where subcase = 'A.3' and

4299

(a1, b1, c1) IN (select * from t_in);

4300

pred_in

4301

4302

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4303

where subcase = 'A.3' and

4304

(a1, b1, c1) NOT IN (select * from t_in);

4305

pred_not_in

4306

4307

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4308

where subcase = 'A.3' and

4309

NOT((a1, b1, c1) IN (select * from t_in));

4310

not_pred_in

4311

4312

# test non-top level result indirectly

4313

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

4314

where subcase = 'A.3' and

4315

((a1, b1, c1) IN (select * from t_in)) is NULL and

4316

((a1, b1, c1) NOT IN (select * from t_in)) is NULL;

4317

pred_in

4318

4319

case A.4

4320

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4321

where subcase = 'A.4' and

4322

(a1, b1, c1) IN (select * from t_in);

4323

pred_in

4324

4325

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4326

where subcase = 'A.4' and

4327

(a1, b1, c1) NOT IN (select * from t_in);

4328

pred_not_in

4329

4330

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4331

where subcase = 'A.4' and

4332

NOT((a1, b1, c1) IN (select * from t_in));

4333

not_pred_in

4334

4335

case B.1

4336

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4337

where subcase = 'B.1' and

4338

(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');

4339

pred_in

4340

4341

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4342

where subcase = 'B.1' and

4343

(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');

4344

pred_not_in

4345

4346

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4347

where subcase = 'B.1' and

4348

NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));

4349

not_pred_in

4350

4351

case B.2

4352

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4353

where subcase = 'B.2' and

4354

(a1, b1, c1) IN (select * from t_in);

4355

pred_in

4356

4357

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4358

where subcase = 'B.2' and

4359

(a1, b1, c1) NOT IN (select * from t_in);

4360

pred_not_in

4361

4362

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4363

where subcase = 'B.2' and

4364

NOT((a1, b1, c1) IN (select * from t_in));

4365

not_pred_in

4366

4367

case B.3

4368

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4369

where subcase = 'B.3' and

4370

(a1, b1, c1) IN (select * from t_in);

4371

pred_in

4372

4373

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4374

where subcase = 'B.3' and

4375

(a1, b1, c1) NOT IN (select * from t_in);

4376

pred_not_in

4377

4378

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4379

where subcase = 'B.3' and

4380

NOT((a1, b1, c1) IN (select * from t_in));

4381

not_pred_in

4382

4383

# test non-top level result indirectly

4384

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

4385

where subcase = 'B.3' and

4386

((a1, b1, c1) IN (select * from t_in)) is NULL and

4387

((a1, b1, c1) NOT IN (select * from t_in)) is NULL;

4388

pred_in

4389

4390

case B.4

4391

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4392

where subcase = 'B.4' and

4393

(a1, b1, c1) IN (select * from t_in);

4394

pred_in

4395

4396

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4397

where subcase = 'B.4' and

4398

(a1, b1, c1) NOT IN (select * from t_in);

4399

pred_not_in

4400

4401

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4402

where subcase = 'B.4' and

4403

NOT((a1, b1, c1) IN (select * from t_in));

4404

not_pred_in

4405

4406

drop table t_out;

4407

drop table t_in;

4408

4409

# Bug#20835 (literal string with =any values)

4410

4411

CREATE TABLE t1 (s1 char(1));

4412

INSERT INTO t1 VALUES ('a');

4413

SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);

4414

4415

4416

DROP TABLE t1;

4417

4418

# Bug#33204: INTO is allowed in subselect, causing inconsistent results

4419

4420

CREATE TABLE t1( a INT );

4421

INSERT INTO t1 VALUES (1),(2);

4422

CREATE TABLE t2( a INT, b INT );

4423

SELECT *

4424

FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;

4425

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2

4426

SELECT *

4427

FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;

4428

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2

4429

SELECT *

4430

FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;

4431

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2

4432

SELECT * FROM (

4433

SELECT 1 a

4434

UNION

4435

SELECT a INTO @var FROM t1 WHERE a = 2

4436

) t1a;

4437

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2

4438

) t1a' at line 2

4439

SELECT * FROM (

4440

SELECT 1 a

4441

UNION

4442

SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2

4443

) t1a;

4444

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2

4445

) t1a' at line 2

4446

SELECT * FROM (

4447

SELECT 1 a

4448

UNION

4449

SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4450

) t1a;

4451

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4452

) t1a' at line 2

4453

SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;

4454

4455

4456

SELECT * FROM (

4457

SELECT a FROM t1 WHERE a = 2

4458

UNION

4459

SELECT a FROM t1 WHERE a = 2

4460

) t1a;

4461

4462

4463

SELECT * FROM (

4464

SELECT 1 a

4465

UNION

4466

SELECT a FROM t1 WHERE a = 2

4467

UNION

4468

SELECT a FROM t1 WHERE a = 2

4469

) t1a;

4470

4471

4472

4473

# This was not allowed previously. Possibly, it should be allowed on the future.

4474

# For now, the intent is to keep the fix as non-intrusive as possible.

4475

SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);

4476

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1

4477

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;

4478

4479

4480

SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;

4481

4482

4483

SELECT * FROM ((SELECT 1 a INTO @a)) t1a;

4484

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)) t1a' at line 1

4485

SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;

4486

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1

4487

SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4488

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1

4489

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;

4490

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)) t1a' at line 1

4491

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4492

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1

4493

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;

4494

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1

4495

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;

4496

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a))) t1a' at line 1

4497

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;

4498

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1

4499

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;

4500

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1

4501

SELECT * FROM (SELECT 1 a ORDER BY a) t1a;

4502

4503

4504

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;

4505

4506

4507

SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;

4508

4509

4510

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;

4511

4512

4513

# Test of rule

4514

# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias

4515

# UNION should not be allowed inside the parentheses, nor should

4516

# aliases after.

4517

4518

SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;

4519

a 1

4520

1 1

4521

2 1

4522

SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;

4523

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')) ON 1' at line 1

4524

SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;

4525

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'ON 1' at line 1

4526

SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;

4527

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ') ON 1' at line 1

4528

SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;

4529

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a ON 1' at line 1

4530

SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;

4531

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a ON 1' at line 1

4532

SELECT * FROM t1 JOIN (t1 t1a) ON 1;

4533

a a

4534

1 1

4535

2 1

4536

1 2

4537

2 2

4538

SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;

4539

a a

4540

1 1

4541

2 1

4542

1 2

4543

2 2

4544

SELECT * FROM (t1 t1a);

4545

4546

4547

4548

SELECT * FROM ((t1 t1a));

4549

4550

4551

4552

SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;

4553

a t1a

4554

1 1

4555

2 1

4556

SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;

4557

a t1a

4558

1 1

4559

2 1

4560

SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;

4561

a a

4562

1 1

4563

2 1

4564

SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;

4565

a a

4566

1 1

4567

2 1

4568

# For the join, TABLE_LIST::select_lex == NULL

4569

# Check that we handle this.

4570

SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;

4571

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a2' at line 1

4572

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );

4573

4574

4575

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );

4576

4577

4578

SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );

4579

4580

4581

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);

4582

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1

4583

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4584

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4585

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4586

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4587

SELECT * FROM t1 WHERE a = ( SELECT 1 );

4588

4589

4590

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );

4591

4592

4593

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);

4594

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1

4595

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );

4596

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4597

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );

4598

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4599

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);

4600

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1

4601

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4602

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4603

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4604

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4605

SELECT ( SELECT 1 INTO @v );

4606

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4607

SELECT ( SELECT 1 INTO OUTFILE 'file' );

4608

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4609

SELECT ( SELECT 1 INTO DUMPFILE 'file' );

4610

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4611

SELECT ( SELECT 1 UNION SELECT 1 INTO @v );

4612

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4613

SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4614

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4615

SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4616

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4617

# Make sure context is popped when we leave the nested select

4618

SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;

4619

( SELECT a FROM t1 WHERE a = 1 ) a

4620

1 1

4621

1 2

4622

SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;

4623

( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a

4624

1 1

4625

1 2

4626

# Make sure we have feature F561 (see .yy file)

4627

SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);

4628

a b

4629

# Make sure the parser does not allow nested UNIONs anywhere

4630

SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );

4631

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1

4632

( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4633

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1' at line 1

4634

SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4635

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4636

SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4637

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1' at line 1

4638

SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4639

( SELECT 1 UNION SELECT 1 UNION SELECT 1 )

4640

4641

SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));

4642

((SELECT 1 UNION SELECT 1 UNION SELECT 1))

4643

4644

SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4645

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4646

SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4647

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ') UNION SELECT 1 )' at line 1

4648

SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;

4649

4650

4651

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4652

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4653

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4654

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4655

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4656

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4657

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4658

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4659

SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4660

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1

4661

SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4662

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1

4663

SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4664

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1

4665

SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4666

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1

4667

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4668

4669

4670

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4671

4672

4673

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4674

4675

4676

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4677

4678

4679

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );

4680

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4681

SELECT EXISTS(SELECT 1+1);

4682

EXISTS(SELECT 1+1)

4683

4684

SELECT EXISTS(SELECT 1+1 INTO @test);

4685

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @test)' at line 1

4686

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );

4687

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4688

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );

4689

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4690

SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );

4691

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4692

DROP TABLE t1, t2;

4693

set optimizer_switch='';

4694

ERROR 42000: Variable 'optimizer_switch' can't be set to the value of ''

4695

set optimizer_switch=0;

4696

show variables like 'optimizer_switch';

4697

Variable_name Value

4698

optimizer_switch

Older »