~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

) ROW_FORMAT=DYNAMIC;

365

Warnings:

366

Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.

367

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

368

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

369

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

370

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

371

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

372

id select_type table type possible_keys key key_len ref rows filtered Extra

373

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

374

4 SUBQUERY t8 const PRIMARY PRIMARY 142 # 100.00 Using index

375

2 SUBQUERY t8 const PRIMARY PRIMARY 142 const # 100.00

376

3 SUBQUERY t8 const PRIMARY PRIMARY 142 # 100.00 Using index

377

Warnings:

378

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

379

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

380

t8 WHERE pseudo='joce');

381

ERROR 21000: Operand should contain 1 column(s)

382

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

383

pseudo='joce');

384

ERROR 21000: Operand should contain 1 column(s)

385

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

386

pseudo

387

joce

388

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

389

ERROR 21000: Subquery returns more than 1 row

390

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

391

#searchconthardwarefr3 forumconthardwarefr7

392

CREATE TEMPORARY TABLE `t1` (

393

`topic` bigint NOT NULL default '0',

394

`date` date NULL,

395

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

396

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

397

KEY `topic` (`topic`)

398

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

399

INSERT INTO t1 (topic,date,pseudo) VALUES

400

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

401

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

402

id select_type table type possible_keys key key_len ref rows filtered Extra

403

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

404

Warnings:

405

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

406

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

407

id select_type table type possible_keys key key_len ref rows filtered Extra

408

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

409

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

410

Warnings:

411

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

412

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

413

date

414

2002-08-03

415

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

416

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

417

2002-08-03

418

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

419

420

421

422

423

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

424

ERROR 21000: Subquery returns more than 1 row

425

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

426

id select_type table type possible_keys key key_len ref rows filtered Extra

427

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

428

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

429

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

430

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

431

Warnings:

432

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

433

drop table t1;

434

#forumconthardwarefr7 searchconthardwarefr7

435

CREATE TABLE `t1` (

436

`numeropost` bigint NOT NULL auto_increment,

437

`maxnumrep` int NOT NULL default '0',

438

PRIMARY KEY (`numeropost`),

439

UNIQUE KEY `maxnumrep` (`maxnumrep`)

440

) ROW_FORMAT=FIXED;

441

Warnings:

442

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

443

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

444

CREATE TABLE `t2` (

445

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

446

`topic` bigint NOT NULL default '0',

447

`date` date NULL,

448

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

449

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

450

) ROW_FORMAT=DYNAMIC;

451

Warnings:

452

Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.

453

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

454

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

455

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

456

457

40143

458

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;

459

numeropost maxnumrep

460

43506 2

461

40143 1

462

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

463

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

464

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

465

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

466

SELECT * from t2 where topic IN (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 IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);

471

mot topic date pseudo

472

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

473

mot topic date pseudo

474

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

475

mot topic date pseudo

476

joce 40143 2002-10-22 joce

477

joce 43506 2002-10-22 joce

478

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

479

mot topic date pseudo

480

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

481

mot topic date pseudo

482

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

483

mot topic date pseudo

484

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

485

mot topic date pseudo

486

joce 40143 2002-10-22 joce

487

joce 43506 2002-10-22 joce

488

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

489

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

490

joce 40143 2002-10-22 joce 1

491

joce 43506 2002-10-22 joce 1

492

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

493

mot topic date pseudo

494

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

495

mot topic date pseudo

496

joce 40143 2002-10-22 joce

497

joce 43506 2002-10-22 joce

498

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

499

mot topic date pseudo

500

joce 40143 2002-10-22 joce

501

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

502

mot topic date pseudo

503

joce 40143 2002-10-22 joce

504

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

505

mot topic date pseudo

506

joce 40143 2002-10-22 joce

507

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

508

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

509

joce 40143 2002-10-22 joce 1

510

joce 43506 2002-10-22 joce 0

511

drop table t1,t2;

512

#forumconthardwarefr7

513

CREATE TABLE `t1` (

514

`numeropost` bigint NOT NULL auto_increment,

515

`maxnumrep` int NOT NULL default '0',

516

PRIMARY KEY (`numeropost`),

517

UNIQUE KEY `maxnumrep` (`maxnumrep`)

518

) ROW_FORMAT=FIXED;

519

Warnings:

520

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

521

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

522

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

523

ERROR 21000: Subquery returns more than 1 row

524

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

525

ERROR 21000: Subquery returns more than 1 row

526

drop table t1;

527

create table t1 (a int);

528

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

529

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

530

531

532

533

534

drop table t1;

535

#iftest

536

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

537

INSERT INTO t1 VALUES ();

538

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

539

ERROR 21000: Subquery returns more than 1 row

540

drop table t1;

541

# threadhardwarefr7

542

CREATE TABLE `t1` (

543

`numeropost` bigint NOT NULL default '0',

544

`numreponse` int NOT NULL auto_increment,

545

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

546

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

547

UNIQUE KEY `numreponse` (`numreponse`),

548

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

549

);

550

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

551

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

552

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

553

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

554

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

555

numreponse (SELECT numeropost FROM t1 HAVING numreponse=1)

556

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

557

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

558

ERROR 21000: Subquery returns more than 1 row

559

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

560

id select_type table type possible_keys key key_len ref rows filtered Extra

561

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

562

Warnings:

563

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

564

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

565

id select_type table type possible_keys key key_len ref rows filtered Extra

566

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

567

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

568

Warnings:

569

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

570

drop table t1;

571

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

572

INSERT INTO t1 VALUES (1);

573

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

574

575

576

drop table t1;

577

#update with subselects

578

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

579

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

580

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

581

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

582

select * from t1;

583

a b

584

0 10

585

1 11

586

2 12

587

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

588

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

589

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

590

ERROR 21000: Subquery returns more than 1 row

591

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

592

select * from t1;

593

a b

594

0 NULL

595

1 21

596

2 22

597

drop table t1, t2;

598

#delete with subselects

599

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

600

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

601

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

602

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

603

select * from t1;

604

a b

605

0 10

606

1 11

607

2 12

608

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

609

a b

610

2 12

611

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

612

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

613

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

614

ERROR 21000: Subquery returns more than 1 row

615

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

616

select * from t1;

617

a b

618

0 10

619

1 11

620

drop table t1, t2;

621

#insert with subselects

622

CREATE TABLE t1 (x int);

623

create table t2 (a int);

624

create table t3 (b int);

625

insert into t2 values (1);

626

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

627

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

628

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

629

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

630

ERROR 21000: Subquery returns more than 1 row

631

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

632

select * from t1;

633

634

635

insert into t2 values (1);

636

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

637

select * from t1;

638

639

640

641

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

642

select * from t1;

643

644

645

646

647

648

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

649

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

650

select * from t1;

651

652

653

654

655

656

657

658

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

659

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

660

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

661

select * from t1;

662

663

664

665

666

667

668

669

670

671

#TODO: should be uncommented after bug 380 fix pushed

672

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

673

#select * from t1;

674

drop table t1, t2, t3;

675

#replace with subselects

676

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

677

create table t2 (a int);

678

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

679

insert into t2 values (1);

680

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

681

select * from t1;

682

x y

683

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

684

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

685

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

686

ERROR 21000: Subquery returns more than 1 row

687

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

688

select * from t1;

689

x y

690

1 2

691

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

692

select * from t1;

693

x y

694

1 3

695

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

696

select * from t1;

697

x y

698

1 3

699

4 1

700

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

701

select * from t1;

702

x y

703

1 3

704

4 2

705

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

706

select * from t1;

707

x y

708

1 3

709

2 1

710

4 2

711

drop table t1, t2, t3;

712

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

713

ERROR HY000: No tables used

714

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

715

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

716

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

717

718

719

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

720

id select_type table type possible_keys key key_len ref rows filtered Extra

721

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

722

Warnings:

723

Note 1249 Select 2 was reduced during optimization

724

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

725

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

726

727

728

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

729

730

731

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

732

id select_type table type possible_keys key key_len ref rows filtered Extra

733

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

734

Warnings:

735

Note 1249 Select 3 was reduced during optimization

736

Note 1249 Select 2 was reduced during optimization

737

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

738

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

739

id select_type table type possible_keys key key_len ref rows filtered Extra

740

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

741

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

742

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

743

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

744

Warnings:

745

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

746

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

747

748

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

749

750

751

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

752

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

753

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

754

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

755

SELECT * FROM t2;

756

757

758

759

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

760

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

761

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

762

ERROR 21000: Subquery returns more than 1 row

763

drop table t2, t1;

764

#NULL test

765

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

766

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

767

select 1 IN (SELECT * from t1);

768

1 IN (SELECT * from t1)

769

770

select 10 IN (SELECT * from t1);

771

10 IN (SELECT * from t1)

772

773

select NULL IN (SELECT * from t1);

774

NULL IN (SELECT * from t1)

775

NULL

776

update t1 set a=NULL where a=2;

777

select 1 IN (SELECT * from t1);

778

1 IN (SELECT * from t1)

779

780

select 3 IN (SELECT * from t1);

781

3 IN (SELECT * from t1)

782

783

select 10 IN (SELECT * from t1);

784

10 IN (SELECT * from t1)

785

NULL

786

select 1 > ALL (SELECT * from t1);

787

1 > ALL (SELECT * from t1)

788

789

select 10 > ALL (SELECT * from t1);

790

10 > ALL (SELECT * from t1)

791

NULL

792

select 1 > ANY (SELECT * from t1);

793

1 > ANY (SELECT * from t1)

794

NULL

795

select 10 > ANY (SELECT * from t1);

796

10 > ANY (SELECT * from t1)

797

798

drop table t1;

799

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

800

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

801

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

802

'A' IN (SELECT * from t1)

803

804

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

805

'XYZS' IN (SELECT * from t1)

806

807

select NULL IN (SELECT * from t1);

808

NULL IN (SELECT * from t1)

809

NULL

810

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

811

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

812

'A' IN (SELECT * from t1)

813

814

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

815

'DEF' IN (SELECT * from t1)

816

817

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

818

'XYZS' IN (SELECT * from t1)

819

NULL

820

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

821

'A' > ALL (SELECT * from t1)

822

823

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

824

'XYZS' > ALL (SELECT * from t1)

825

NULL

826

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

827

'A' > ANY (SELECT * from t1)

828

NULL

829

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

830

'XYZS' > ANY (SELECT * from t1)

831

832

drop table t1;

833

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

834

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

835

select 1.5 IN (SELECT * from t1);

836

1.5 IN (SELECT * from t1)

837

838

select 10.5 IN (SELECT * from t1);

839

10.5 IN (SELECT * from t1)

840

841

select NULL IN (SELECT * from t1);

842

NULL IN (SELECT * from t1)

843

NULL

844

update t1 set a=NULL where a=2.5;

845

select 1.5 IN (SELECT * from t1);

846

1.5 IN (SELECT * from t1)

847

848

select 3.5 IN (SELECT * from t1);

849

3.5 IN (SELECT * from t1)

850

851

select 10.5 IN (SELECT * from t1);

852

10.5 IN (SELECT * from t1)

853

NULL

854

select 1.5 > ALL (SELECT * from t1);

855

1.5 > ALL (SELECT * from t1)

856

857

select 10.5 > ALL (SELECT * from t1);

858

10.5 > ALL (SELECT * from t1)

859

NULL

860

select 1.5 > ANY (SELECT * from t1);

861

1.5 > ANY (SELECT * from t1)

862

NULL

863

select 10.5 > ANY (SELECT * from t1);

864

10.5 > ANY (SELECT * from t1)

865

866

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

867

id select_type table type possible_keys key key_len ref rows filtered Extra

868

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

869

Warnings:

870

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

871

Note 1249 Select 2 was reduced during optimization

872

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

873

select (select a+1) from t1;

874

(select a+1)

875

2.5

876

4.5

877

NULL

878

drop table t1;

879

880

# Null with keys

881

882

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

883

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

884

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

885

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

886

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

887

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

888

1 1

889

2 1

890

3 1

891

4 0

892

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

893

id select_type table type possible_keys key key_len ref rows filtered Extra

894

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

895

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

896

Warnings:

897

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`

898

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

899

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

900

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

901

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

902

1 1

903

2 1

904

3 1

905

4 0

906

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

907

id select_type table type possible_keys key key_len ref rows filtered Extra

908

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

909

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

910

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

911

Warnings:

912

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`

913

drop table t1,t2,t3;

914

#LIMIT is not supported now

915

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

916

#-- error 1235

917

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

918

#-- error 1235

919

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

920

#drop table t1;

921

922

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

923

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

924

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

925

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

926

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

927

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

928

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

929

#drop table t1,t2;

930

931

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

932

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

933

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

934

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

935

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

936

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

937

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

938

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

939

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

940

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

941

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

942

#-- error 1235

943

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

944

#drop table t1;

945

#test of uncacheable subqueries

946

CREATE TABLE t1 (a int);

947

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

948

id select_type table type possible_keys key key_len ref rows filtered Extra

949

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

950

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

951

Warnings:

952

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

953

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

954

id select_type table type possible_keys key key_len ref rows filtered Extra

955

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

956

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

957

Warnings:

958

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

959

drop table t1;

960

CREATE TEMPORARY TABLE `t1` (

961

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

962

`topic` bigint NOT NULL default '0',

963

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

964

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

965

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

966

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

967

KEY `topic` (`topic`)

968

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

969

CREATE TEMPORARY TABLE `t2` (

970

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

971

`topic` bigint NOT NULL default '0',

972

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

973

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

974

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

975

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

976

KEY `topic` (`topic`)

977

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

978

CREATE TEMPORARY TABLE `t3` (

979

`numeropost` bigint NOT NULL auto_increment,

980

`maxnumrep` int NOT NULL default '0',

981

PRIMARY KEY (`numeropost`),

982

UNIQUE KEY `maxnumrep` (`maxnumrep`)

983

) ENGINE=MyISAM;

984

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

985

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

986

INSERT INTO t3 VALUES (1,1);

987

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

988

numeropost=topic);

989

topic

990

991

select * from t1;

992

mot topic date pseudo

993

joce 1 2009-01-20 joce

994

test 2 2009-01-20 test

995

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

996

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

997

select * from t1;

998

mot topic date pseudo

999

joce 1 2009-01-20 joce

1000

drop table t1, t2, t3;

1001

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

1002

a (SELECT a)

1003

1 1

1004

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

1005

SHOW CREATE TABLE t1;

1006

Table Create Table

1007

t1 CREATE TEMPORARY TABLE `t1` (

1008

`a` int NOT NULL DEFAULT '0',

1009

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

1010

) ENGINE=MyISAM

1011

drop table t1;

1012

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

1013

SHOW CREATE TABLE t1;

1014

Table Create Table

1015

t1 CREATE TEMPORARY TABLE `t1` (

1016

`a` int NOT NULL DEFAULT '0',

1017

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

1018

) ENGINE=MyISAM

1019

drop table t1;

1020

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

1021

SHOW CREATE TABLE t1;

1022

Table Create Table

1023

t1 CREATE TEMPORARY TABLE `t1` (

1024

`a` int NOT NULL DEFAULT '0',

1025

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

1026

) ENGINE=MyISAM

1027

drop table t1;

1028

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

1029

select * from t1;

1030

1031

1032

SHOW CREATE TABLE t1;

1033

Table Create Table

1034

t1 CREATE TEMPORARY TABLE `t1` (

1035

`a` bigint NOT NULL

1036

) ENGINE=MyISAM

1037

drop table t1;

1038

create table t1 (a int);

1039

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

1040

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

1041

from t1;

1042

id select_type table type possible_keys key key_len ref rows filtered Extra

1043

1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00

1044

2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00

1045

3 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00

1046

Warnings:

1047

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`

1048

drop table t1;

1049

1050

# error in IN

1051

1052

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

1053

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

1054

1055

# complex subquery

1056

1057

CREATE TABLE t1 (

1058

ID int NOT NULL auto_increment,

1059

name char(35) NOT NULL default '',

1060

t2 char(3) NOT NULL default '',

1061

District char(20) NOT NULL default '',

1062

Population int NOT NULL default '0',

1063

PRIMARY KEY (ID)

1064

);

1065

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

1066

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

1067

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

1068

CREATE TABLE t2 (

1069

Code char(3) NOT NULL default '',

1070

Name char(52) NOT NULL default '',

1071

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

1072

Region char(26) NOT NULL default '',

1073

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

1074

IndepYear int default NULL,

1075

Population int NOT NULL default '0',

1076

LifeExpectancy float(3,1) default NULL,

1077

GNP float(10,2) default NULL,

1078

GNPOld float(10,2) default NULL,

1079

LocalName char(45) NOT NULL default '',

1080

GovernmentForm char(45) NOT NULL default '',

1081

HeadOfState char(60) default NULL,

1082

Capital int default NULL,

1083

Code2 char(2) NOT NULL default '',

1084

PRIMARY KEY (Code)

1085

);

1086

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

1087

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

1088

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

1089

Continent Name Population

1090

Oceania Sydney 3276207

1091

drop table t1, t2;

1092

1093

# constants in IN

1094

1095

CREATE TEMPORARY TABLE `t1` (

1096

`id` bigint NOT NULL auto_increment,

1097

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

1098

PRIMARY KEY (`id`),

1099

UNIQUE KEY `pseudo` (`pseudo`)

1100

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

1101

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

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

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

1112

SELECT 0 IN (SELECT 1 FROM t1 a);

1113

0 IN (SELECT 1 FROM t1 a)

1114

1115

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

1116

id select_type table type possible_keys key key_len ref rows filtered Extra

1117

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

1118

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

1119

Warnings:

1120

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

1121

drop table t1;

1122

CREATE TEMPORARY TABLE `t1` (

1123

`i` int NOT NULL default '0',

1124

PRIMARY KEY (`i`)

1125

) ENGINE=MyISAM;

1126

INSERT INTO t1 VALUES (1);

1127

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

1128

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

1129

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

1130

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

1131

select * from t1;

1132

1133

1134

drop table t1;

1135

1136

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

1137

1138

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

1139

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

1140

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

1141

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

1142

NULL

1143

drop table t1;

1144

1145

# Test optimization for sub selects

1146

1147

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

1148

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

1149

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

1150

id select_type table type possible_keys key key_len ref rows filtered Extra

1151

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

1152

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

1153

Warnings:

1154

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

1155

drop table t1;

1156

CREATE TABLE t1 (

1157

ID int NOT NULL auto_increment,

1158

SUB_ID int NOT NULL default '0',

1159

REF_ID int default NULL,

1160

REF_SUB int default '0',

1161

PRIMARY KEY (ID,SUB_ID),

1162

UNIQUE KEY t1_PK (ID,SUB_ID),

1163

KEY t1_FK (REF_ID,REF_SUB),

1164

KEY t1_REFID (REF_ID)

1165

);

1166

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

1167

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

1168

REF_ID

1169

DROP TABLE t1;

1170

1171

# uninterruptable update

1172

1173

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

1174

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

1175

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

1176

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

1177

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

1178

Warnings:

1179

Error 1242 Subquery returns more than 1 row

1180

select * from t1;

1181

a b

1182

1 1

1183

2 NULL

1184

3 1

1185

drop table t1, t2;

1186

1187

# reduced subselect in ORDER BY & GROUP BY clauses

1188

1189

CREATE TEMPORARY TABLE `t1` (

1190

`id` bigint NOT NULL auto_increment,

1191

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

1192

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

1193

PRIMARY KEY (`id`),

1194

UNIQUE KEY `email` (`email`),

1195

UNIQUE KEY `pseudo` (`pseudo`)

1196

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

1197

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

1198

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

1199

a b

1200

test test

1201

test1 test1

1202

drop table if exists t1;

1203

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

1204

1205

1206

1207

# IN subselect optimization test

1208

1209

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

1210

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

1211

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

1212

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

1213

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

1214

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

1215

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

1216

1217

1218

1219

1220

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

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 index

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

1226

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

1227

1228

1229

1230

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

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 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where

1234

Warnings:

1235

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

1236

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

1237

1238

1239

1240

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

1241

id select_type table type possible_keys key key_len ref rows filtered Extra

1242

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

1243

2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00

1244

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

1245

Warnings:

1246

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

1247

drop table t1, t2, t3;

1248

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

1249

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

1250

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

1251

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

1252

# making table large enough

1253

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

1254

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

1255

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

1256

1257

1258

1259

1260

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

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

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

1266

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

1267

1268

1269

1270

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

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 index_subquery a a 5 func 1001 100.00 Using index; Using where

1274

Warnings:

1275

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

1276

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

1277

1278

1279

1280

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

1281

id select_type table type possible_keys key key_len ref rows filtered Extra

1282

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

1283

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

1284

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

1285

Warnings:

1286

1287

insert into t1 values (3,31);

1288

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

1289

1290

1291

1292

1293

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

1294

1295

1296

1297

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

1298

id select_type table type possible_keys key key_len ref rows filtered Extra

1299

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

1300

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

1301

Warnings:

1302

1303

drop table t1, t2, t3;

1304

1305

# alloc_group_fields() working

1306

1307

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

1308

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

1309

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

1310

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

1311

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

1312

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

1313

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;

1314

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)

1315

3 1

1316

2 2

1317

1 2

1318

drop table t1,t2,t3;

1319

1320

# aggregate functions in HAVING test

1321

1322

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

1323

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

1324

insert into t1 values (1);

1325

insert into t2 values (1);

1326

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

1327

1328

1329

drop table t1,t2;

1330

1331

# update subquery with wrong field (to force name resolving

1332

# in UPDATE name space)

1333

1334

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

1335

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

1336

insert into t1 values (1);

1337

insert into t2 values (1);

1338

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

1339

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

1340

DROP TABLE t1, t2;

1341

1342

# collation test

1343

1344

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

1345

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

1346

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

1347

#-- error 1267

1348

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

1349

#-- error 1267

1350

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

1351

#drop table t1;

1352

1353

# aggregate functions reinitialization

1354

1355

create table t1(toid int,rd int);

1356

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

1357

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

1358

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

1359

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

1360

userid pmtotal pmnew calc_total calc_new

1361

1 0 0 9 3

1362

2 0 0 4 2

1363

drop table t1, t2;

1364

1365

# row union

1366

1367

create table t1 (s1 char(5));

1368

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

1369

ERROR 21000: Operand should contain 1 column(s)

1370

insert into t1 values ('tttt');

1371

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

1372

1373

tttt

1374

explain extended (select * from t1);

1375

id select_type table type possible_keys key key_len ref rows filtered Extra

1376

1 SIMPLE t1 ALL NULL NULL NULL NULL # 100.00

1377

Warnings:

1378

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

1379

(select * from t1);

1380

1381

tttt

1382

drop table t1;

1383

1384

# IN optimisation test results

1385

1386

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

1387

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

1388

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

1389

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

1390

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

1391

s1 s1 NOT IN (SELECT s1 FROM t2)

1392

a1 0

1393

a2 0

1394

a3 1

1395

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

1396

s1 s1 = ANY (SELECT s1 FROM t2)

1397

a1 1

1398

a2 1

1399

a3 0

1400

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

1401

s1 s1 <> ALL (SELECT s1 FROM t2)

1402

a1 0

1403

a2 0

1404

a3 1

1405

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

1406

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

1407

a1 0

1408

a2 1

1409

a3 1

1410

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

1411

id select_type table type possible_keys key key_len ref rows filtered Extra

1412

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

1413

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

1414

Warnings:

1415

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`

1416

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

1417

id select_type table type possible_keys key key_len ref rows filtered Extra

1418

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

1419

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

1420

Warnings:

1421

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`

1422

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

1423

id select_type table type possible_keys key key_len ref rows filtered Extra

1424

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

1425

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

1426

Warnings:

1427

1428

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

1429

id select_type table type possible_keys key key_len ref rows filtered Extra

1430

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

1431

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

1432

Warnings:

1433

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`

1434

drop table t1,t2;

1435

1436

# correct ALL optimisation

1437

1438

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

1439

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

1440

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

1441

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

1442

1443

1444

1445

1446

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

1447

id select_type table type possible_keys key key_len ref rows filtered Extra

1448

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

1449

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

1450

Warnings:

1451

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

1452

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

1453

1454

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

1455

id select_type table type possible_keys key key_len ref rows filtered Extra

1456

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

1457

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

1458

Warnings:

1459

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

1460

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

1461

1462

1463

1464

1465

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

1466

id select_type table type possible_keys key key_len ref rows filtered Extra

1467

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

1468

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

1469

Warnings:

1470

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

1471

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

1472

1473

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

1474

id select_type table type possible_keys key key_len ref rows filtered Extra

1475

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

1476

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

1477

Warnings:

1478

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

1479

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

1480

1481

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

1482

id select_type table type possible_keys key key_len ref rows filtered Extra

1483

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

1484

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

1485

Warnings:

1486

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

1487

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

1488

1489

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

1490

id select_type table type possible_keys key key_len ref rows filtered Extra

1491

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

1492

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

1493

Warnings:

1494

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

1495

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

1496

1497

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

1498

id select_type table type possible_keys key key_len ref rows filtered Extra

1499

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

1500

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

1501

Warnings:

1502

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

1503

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

1504

1505

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

1506

id select_type table type possible_keys key key_len ref rows filtered Extra

1507

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

1508

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

1509

Warnings:

1510

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

1511

1512

# optimized static ALL/ANY with grouping

1513

1514

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

1515

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

1516

1517

1518

1519

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

1520

id select_type table type possible_keys key key_len ref rows filtered Extra

1521

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

1522

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

1523

Warnings:

1524

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

1525

drop table t2, t3;

1526

1527

# correct used_tables()

1528

1529

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 ;

1530

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

1531

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;

1532

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

1533

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 ;

1534

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

1535

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

1536

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

1537

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;

1538

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

1539

-1 Valid 1

1540

-1 Valid 2 1

1541

-1 Should Not Return 0

1542

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;

1543

dbid name

1544

-1 Valid

1545

-1 Valid 2

1546

drop table t1,t2,t3,t4;

1547

1548

# cardinality check

1549

1550

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

1551

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

1552

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

1553

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

1554

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

1555

ERROR 21000: Operand should contain 3 column(s)

1556

DROP TABLE t1,t2;

1557

1558

# optimized ALL/ANY with union

1559

1560

create table t1 (s1 char);

1561

insert into t1 values ('e');

1562

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

1563

1564

1565

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

1566

1567

1568

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

1569

id select_type table type possible_keys key key_len ref rows filtered Extra

1570

1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00

1571

2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00

1572

3 UNION t1 ALL NULL NULL NULL NULL # 100.00

1573

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

1574

Warnings:

1575

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

1576

drop table t1;

1577

1578

# filesort in subquery (restoring join_tab)

1579

1580

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

1581

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

1582

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

1583

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

1584

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;

1585

phone code

1586

69294728265 6

1587

18621828126 1862

1588

89356874041 NULL

1589

95895001874 NULL

1590

drop table t1, t2;

1591

1592

# unresolved field error

1593

1594

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

1595

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

1596

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

1597

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

1598

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

1599

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

1600

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

1601

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

1602

drop table t1, t2;

1603

1604

# fix_fields() in add_ref_to_table_cond()

1605

1606

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

1607

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

1608

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

1609

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

1610

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

1611

COLC

1612

DROP TABLE t1, t2;

1613

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

1614

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

1615

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

1616

(SELECT a)

1617

1618

1619

1620

1621

1622

DROP TABLE t1;

1623

1624

# Bug 2479

1625

1626

CREATE TEMPORARY TABLE `t1` (

1627

`id` int NOT NULL auto_increment,

1628

`id_cns` int NOT NULL default '0',

1629

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

1630

`anno_dep` int NOT NULL default '0',

1631

`particolare` bigint NOT NULL default '0',

1632

`generale` bigint NOT NULL default '0',

1633

`bis` int NOT NULL default '0',

1634

PRIMARY KEY (`id`),

1635

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

1636

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

1637

ENGINE=MyISAM;

1638

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

1639

CREATE TEMPORARY TABLE `t2` (

1640

`id` int NOT NULL auto_increment,

1641

`max_anno_dep` int NOT NULL default '0',

1642

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

1643

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

1644

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;

1645

id max_anno_dep PIPPO

1646

16 1987 1

1647

50 1990 0

1648

51 1990 NULL

1649

DROP TABLE t1, t2;

1650

1651

# GLOBAL LIMIT

1652

1653

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

1654

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

1655

SET SQL_SELECT_LIMIT=1;

1656

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

1657

sum(a)

1658

1659

select 2 in (select * from t1);

1660

2 in (select * from t1)

1661

1662

SET SQL_SELECT_LIMIT=default;

1663

drop table t1;

1664

1665

# Bug #3118: subselect + order by

1666

1667

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

1668

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

1669

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

1670

a b

1671

1 1

1672

1 2

1673

1 3

1674

DROP TABLE t1;

1675

# Item_cond fix field

1676

1677

create table t1(val varchar(10));

1678

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

1679

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

1680

count(*)

1681

1682

drop table t1;

1683

1684

# ref_or_null replacing with ref

1685

1686

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

1687

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

1688

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

1689

id text

1690

8 text8

1691

9 text9

1692

10 text10

1693

11 text11

1694

12 text12

1695

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

1696

id text

1697

8 text8

1698

9 text9

1699

10 text10

1700

11 text11

1701

12 text12

1702

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

1703

id select_type table type possible_keys key key_len ref rows filtered Extra

1704

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

1705

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

1706

Warnings:

1707

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

1708

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

1709

id select_type table type possible_keys key key_len ref rows filtered Extra

1710

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

1711

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

1712

Warnings:

1713

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

1714

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

1715

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

1716

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

1717

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

1718

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

1719

id text id text id text

1720

1 text1 1 text1 1 text1

1721

2 text2 2 text2 2 text2

1722

3 text3 3 text3 3 text3

1723

4 text4 4 text4 4 text4

1724

5 text5 5 text5 5 text5

1725

6 text6 6 text6 6 text6

1726

7 text7 7 text7 7 text7

1727

8 text8 8 text8 8 text8

1728

9 text9 9 text9 9 text9

1729

10 text10 10 text10 10 text10

1730

11 text11 11 text1 11 text11

1731

12 text12 12 text2 12 text12

1732

1000 text1000 NULL NULL 1000 text1000

1733

1001 text1001 NULL NULL 1000 text1000

1734

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

1735

id select_type table type possible_keys key key_len ref rows filtered Extra

1736

1 SIMPLE a ALL NULL NULL NULL NULL # 100.00

1737

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

1738

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

1739

Warnings:

1740

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

1741

drop table t1,t2;

1742

1743

# Static tables & rund() in subqueries

1744

1745

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

1746

insert into t1 values (1);

1747

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

1748

id select_type table type possible_keys key key_len ref rows Extra

1749

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

1750

2 SUBQUERY t1 system NULL NULL NULL NULL 1

1751

drop table t1;

1752

1753

# bug 3188

1754

1755

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

1756

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

1757

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

1758

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

1759

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

1760

drop table t1,t2,t3;

1761

1762

# aggregate functions (Bug #3505)

1763

1764

CREATE TABLE t1 (id INT);

1765

CREATE TABLE t2 (id INT);

1766

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

1767

INSERT INTO t2 VALUES (1);

1768

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

1769

id c

1770

1 1

1771

2 0

1772

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

1773

id c

1774

1 1

1775

2 0

1776

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;

1777

id c

1778

1 1

1779

2 0

1780

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;

1781

id c

1782

1 1

1783

2 0

1784

DROP TABLE t1,t2;

1785

1786

# ALL/ANY test

1787

1788

CREATE TABLE t1 ( a int, b int );

1789

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

1790

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

1791

1792

1793

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

1794

1795

1796

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

1797

1798

1799

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

1800

1801

1802

1803

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

1804

1805

1806

1807

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

1808

1809

1810

1811

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

1812

1813

1814

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

1815

1816

1817

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

1818

1819

1820

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

1821

1822

1823

1824

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

1825

1826

1827

1828

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

1829

1830

1831

1832

# with index

1833

ALTER TABLE t1 ADD INDEX (a);

1834

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

1835

1836

1837

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

1838

1839

1840

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

1841

1842

1843

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

1844

1845

1846

1847

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

1848

1849

1850

1851

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

1852

1853

1854

1855

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

1856

1857

1858

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

1859

1860

1861

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

1862

1863

1864

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

1865

1866

1867

1868

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

1869

1870

1871

1872

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

1873

1874

1875

1876

# having clause test

1877

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

1878

1879

1880

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

1881

1882

1883

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

1884

1885

1886

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

1887

1888

1889

1890

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

1891

1892

1893

1894

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

1895

1896

1897

1898

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

1899

1900

1901

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

1902

1903

1904

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

1905

1906

1907

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

1908

1909

1910

1911

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

1912

1913

1914

1915

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

1916

1917

1918

1919

# union test

1920

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

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

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

1927

1928

1929

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

1930

1931

1932

1933

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

1934

1935

1936

1937

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

1938

1939

1940

1941

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

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

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

1948

1949

1950

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

1951

1952

1953

1954

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

1955

1956

1957

1958

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

1959

1960

1961

1962

# union + having test

1963

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

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

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

1970

1971

1972

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

1973

1974

1975

1976

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

1977

1978

1979

1980

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

1981

1982

1983

1984

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

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

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

1991

1992

1993

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

1994

1995

1996

1997

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

1998

1999

2000

2001

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

2002

2003

2004

2005

# row tests

2006

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

2007

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

2008

ERROR 21000: Operand should contain 1 column(s)

2009

SELECT a FROM t1 WHERE a > ANY (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

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

2014

ERROR 21000: Operand should contain 1 column(s)

2015

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

2016

ERROR 21000: Operand should contain 1 column(s)

2017

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

2018

ERROR 21000: Operand should contain 1 column(s)

2019

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

2020

ERROR 21000: Operand should contain 1 column(s)

2021

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

2022

ERROR 21000: Operand should contain 1 column(s)

2023

# following should be converted to IN

2024

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

2025

ERROR 21000: Operand should contain 2 column(s)

2026

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

2027

ERROR 21000: Operand should contain 1 column(s)

2028

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

2029

2030

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

2031

ERROR 21000: Operand should contain 2 column(s)

2032

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

2033

ERROR 21000: Operand should contain 1 column(s)

2034

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

2035

2036

2037

2038

2039

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

2040

2041

2042

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

2043

2044

2045

2046

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

2047

2048

2049

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

2050

2051

2052

2053

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

2054

2055

2056

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

2057

2058

2059

2060

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

2061

2062

2063

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

2064

2065

2066

2067

# without optimisation

2068

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

2069

2070

2071

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

2072

2073

2074

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

2075

2076

2077

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

2078

2079

2080

2081

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

2082

2083

2084

2085

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

2086

2087

2088

2089

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

2090

2091

2092

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

2093

2094

2095

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

2096

2097

2098

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

2099

2100

2101

2102

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

2103

2104

2105

2106

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

2107

2108

2109

2110

# without optimisation + having

2111

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

2112

2113

2114

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

2115

2116

2117

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

2118

2119

2120

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

2121

2122

2123

2124

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

2125

2126

2127

2128

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

2129

2130

2131

2132

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

2133

2134

2135

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

2136

2137

2138

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

2139

2140

2141

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

2142

2143

2144

2145

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

2146

2147

2148

2149

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

2150

2151

2152

2153

# EXISTS in string contence

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

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

2160

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

2161

2162

2163

2164

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

2165

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

2166

2167

2168

2169

DROP TABLE t1;

2170

CREATE TABLE t1 ( a double, b double );

2171

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

2172

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

2173

2174

2175

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

2176

2177

2178

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

2179

2180

2181

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

2182

2183

2184

2185

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

2186

2187

2188

2189

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

2190

2191

2192

2193

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

2194

2195

2196

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

2197

2198

2199

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

2200

2201

2202

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

2203

2204

2205

2206

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

2207

2208

2209

2210

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

2211

2212

2213

2214

DROP TABLE t1;

2215

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

2216

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

2217

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

2218

2219

2220

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

2221

2222

2223

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

2224

2225

2226

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

2227

2228

2229

2230

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

2231

2232

2233

2234

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

2235

2236

2237

2238

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

2239

2240

2241

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

2242

2243

2244

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

2245

2246

2247

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

2248

2249

2250

2251

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

2252

2253

2254

2255

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

2256

2257

2258

2259

DROP TABLE t1;

2260

2261

# SELECT(EXISTS * ...)optimisation

2262

2263

create table t1 (a int, b int);

2264

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

2265

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

2266

a b

2267

1 2

2268

3 4

2269

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

2270

id select_type table type possible_keys key key_len ref rows filtered Extra

2271

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

2272

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

2273

Warnings:

2274

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

2275

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

2276

drop table t1;

2277

2278

# Bug #4102: subselect in HAVING

2279

2280

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

2281

INSERT INTO t1 VALUES (1);

2282

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

2283

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

2284

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

2285

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

2286

t1_a t2_a t2_b

2287

1 1 2

2288

DROP TABLE t1, t2;

2289

2290

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

2291

2292

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

2293

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

2294

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

2295

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

2296

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

2297

id name id pet

2298

1 Tim 1 Fido

2299

2 Rebecca 2 Spot

2300

3 NULL 3 Felix

2301

drop table t1,t2;

2302

2303

# Aggregate function comparation with ALL/ANY/SOME subselect

2304

2305

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

2306

insert into t1 values (1);

2307

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

2308

insert into t2 values (1,2);

2309

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

2310

a C

2311

1 1

2312

drop table t1,t2;

2313

2314

# BUG#5003 - like in subselect

2315

2316

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

2317

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

2318

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

2319

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

2320

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

2321

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

2322

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

2323

IZAVORGANG_ID

2324

D0000000001

2325

drop table t1;

2326

2327

# Optimized IN with compound index

2328

2329

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

2330

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

2331

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

2332

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

2333

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

2334

aid bid

2335

1 1

2336

2 1

2337

alter table t2 drop primary key;

2338

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

2339

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

2340

aid bid

2341

1 1

2342

2 1

2343

alter table t2 drop key KEY1;

2344

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

2345

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

2346

aid bid

2347

1 1

2348

2 1

2349

drop table t1,t2;

2350

2351

# resolving fields of grouped outer SELECT

2352

2353

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

2354

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

2355

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

2356

howmanyvalues count(*)

2357

1 1

2358

2 2

2359

3 3

2360

4 4

2361

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

2362

howmanyvalues mycount

2363

1 1

2364

2 2

2365

3 3

2366

4 4

2367

CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);

2368

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

2369

howmanyvalues mycount

2370

1 1

2371

2 2

2372

3 3

2373

4 4

2374

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

2375

howmanyvalues mycount

2376

1 1

2377

2 2

2378

3 3

2379

4 4

2380

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

2381

howmanyvalues mycount

2382

1 1

2383

2 1

2384

3 1

2385

4 1

2386

drop table t1;

2387

create table t1 (x int);

2388

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;

2389

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

2390

drop table t1;

2391

2392

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

2393

# creation

2394

2395

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;

2396

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

2397

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 ;

2398

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

2399

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;

2400

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

2401

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;

2402

2403

700

2404

NULL

2405

drop tables t1,t2;

2406

2407

# Subselect in non-select command just after connection

2408

2409

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

2410

2411

# primary query with temporary table and subquery with groupping

2412

2413

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

2414

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

2415

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

2416

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

2417

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

2418

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

2419

1 1 3

2420

1 2 1

2421

1 3 NULL

2422

2 4 NULL

2423

2 5 NULL

2424

drop table t1, t2;

2425

2426

# Equal operation under row and empty subquery

2427

2428

create table t1 (s1 int,s2 int);

2429

insert into t1 values (20,15);

2430

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

2431

s1 s2

2432

drop table t1;

2433

2434

# ALL/ANY with NULL

2435

2436

create table t1 (s1 int);

2437

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

2438

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

2439

2440

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

2441

s1 s1 < all (select s1 from t1)

2442

1 0

2443

NULL NULL

2444

drop table t1;

2445

2446

# reference on changable fields from subquery

2447

2448

CREATE TABLE t1 (

2449

Code char(3) NOT NULL default '',

2450

Name char(52) NOT NULL default '',

2451

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

2452

Region char(26) NOT NULL default '',

2453

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

2454

IndepYear int default NULL,

2455

Population int NOT NULL default '0',

2456

LifeExpectancy float(3,1) default NULL,

2457

GNP float(10,2) default NULL,

2458

GNPOld float(10,2) default NULL,

2459

LocalName char(45) NOT NULL default '',

2460

GovernmentForm char(45) NOT NULL default '',

2461

HeadOfState char(60) default NULL,

2462

Capital int default NULL,

2463

Code2 char(2) NOT NULL default ''

2464

);

2465

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

2466

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

2467

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

2468

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

2469

/*!40000 ALTER TABLE t1 ENABLE KEYS */;

2470

Warnings:

2471

Note 1031 Table storage engine for 't1' doesn't have this option

2472

SELECT DISTINCT Continent AS c FROM t1 outr WHERE

2473

Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND

2474

Population < 200);

2475

2476

Oceania

2477

drop table t1;

2478

2479

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

2480

# non-existant field.

2481

2482

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

2483

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

2484

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

2485

ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'

2486

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

2487

2488

drop table t1,t2;

2489

2490

# Comparison subquery with * and row

2491

2492

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

2493

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

2494

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

2495

NULL

2496

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

2497

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

2498

NULL

2499

# queries whih can be converted to IN

2500

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

2501

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

2502

2503

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

2504

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

2505

2506

drop table t1;

2507

2508

# Comparison subquery and row with nested rows

2509

2510

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

2511

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

2512

ERROR 21000: Operand should contain 2 column(s)

2513

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

2514

ERROR 21000: Operand should contain 2 column(s)

2515

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

2516

ERROR 21000: Operand should contain 1 column(s)

2517

drop table t1;

2518

2519

# Forward reference detection

2520

2521

create table t1 (a integer);

2522

insert into t1 values (1);

2523

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

2524

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

2525

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

2526

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

2527

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

2528

xx 1 = ALL ( select 1 from t1 where 1 = xx )

2529

1 1

2530

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

2531

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

2532

drop table t1;

2533

2534

# Test for BUG#8218

2535

2536

CREATE TEMPORARY TABLE t1 (

2537

categoryId int NOT NULL,

2538

courseId int NOT NULL,

2539

startDate datetime NOT NULL,

2540

endDate datetime NOT NULL,

2541

createDate datetime NOT NULL,

2542

modifyDate timestamp NOT NULL,

2543

attributes text NOT NULL)

2544

engine=myisam;

2545

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

2546

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

2547

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

2548

(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),

2549

(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),

2550

(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),

2551

(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),

2552

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

2553

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

2554

CREATE TEMPORARY TABLE t2 (

2555

userId int NOT NULL,

2556

courseId int NOT NULL,

2557

date datetime NOT NULL)

2558

engine=myisam;

2559

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

2560

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

2561

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

2562

(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),

2563

(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),

2564

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

2565

CREATE TEMPORARY TABLE t3 (

2566

groupId int NOT NULL,

2567

parentId int NOT NULL,

2568

startDate datetime NOT NULL,

2569

endDate datetime NOT NULL,

2570

createDate datetime NOT NULL,

2571

modifyDate timestamp NOT NULL,

2572

ordering int)

2573

engine=myisam;

2574

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

2575

CREATE TEMPORARY TABLE t4 (

2576

id int NOT NULL,

2577

groupTypeId int NOT NULL,

2578

groupKey varchar(50) NOT NULL,

2579

name text,

2580

ordering int,

2581

description text,

2582

createDate datetime NOT NULL,

2583

modifyDate timestamp NOT NULL)

2584

engine=myisam;

2585

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

2586

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

2587

CREATE TEMPORARY TABLE t5 (

2588

userId int NOT NULL,

2589

groupId int NOT NULL,

2590

createDate datetime NOT NULL,

2591

modifyDate timestamp NOT NULL) ENGINE=MyISAM;

2592

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

2593

select

2594

count(distinct t2.userid) pass,

2595

groupstuff.*,

2596

count(t2.courseid) crse,

2597

t1.categoryid,

2598

t2.courseid,

2599

date_format(date, '%b%y') as colhead

2600

from t2

2601

join t1 on t2.courseid=t1.courseid

2602

join

2603

(

2604

select

2605

t5.userid,

2606

parentid,

2607

parentgroup,

2608

childid,

2609

groupname,

2610

grouptypeid

2611

from t5

2612

join

2613

(

2614

select t4.id as parentid,

2615

t4.name as parentgroup,

2616

t4.id as childid,

2617

t4.name as groupname,

2618

t4.grouptypeid

2619

from t4

2620

) as gin on t5.groupid=gin.childid

2621

) as groupstuff on t2.userid = groupstuff.userid

2622

group by

2623

groupstuff.groupname, colhead , t2.courseid;

2624

pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead

2625

1 5141 12 group2 12 group2 5 1 5 12 Aug04

2626

1 5141 12 group2 12 group2 5 1 1 41 Aug04

2627

1 5141 12 group2 12 group2 5 1 2 52 Aug04

2628

1 5141 12 group2 12 group2 5 1 2 53 Aug04

2629

1 5141 12 group2 12 group2 5 1 3 51 Oct04

2630

1 5141 12 group2 12 group2 5 1 1 86 Oct04

2631

1 5141 12 group2 12 group2 5 1 1 87 Oct04

2632

1 5141 12 group2 12 group2 5 1 2 88 Oct04

2633

1 5141 12 group2 12 group2 5 1 2 89 Oct04

2634

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

2635

2636

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

2637

2638

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

2639

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

2640

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

2641

2642

2643

2644

2645

drop table t1;

2646

2647

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

2648

2649

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

2650

insert into t1 values (1,2);

2651

select 1 = (select * from t1);

2652

ERROR 21000: Operand should contain 1 column(s)

2653

select (select * from t1) = 1;

2654

ERROR 21000: Operand should contain 2 column(s)

2655

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

2656

ERROR 21000: Operand should contain 2 column(s)

2657

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

2658

ERROR 21000: Operand should contain 1 column(s)

2659

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

2660

ERROR 21000: Operand should contain 3 column(s)

2661

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

2662

ERROR 21000: Operand should contain 2 column(s)

2663

drop table t1;

2664

2665

# Item_int_with_ref check (BUG#10020)

2666

2667

#CREATE TABLE `t1` (

2668

# `itemid` bigint NOT NULL auto_increment,

2669

# `sessionid` bigint default NULL,

2670

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

2671

# `data` text collate latin1_general_ci NOT NULL,

2672

# PRIMARY KEY (`itemid`)

2673

#);

2674

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

2675

#CREATE TABLE `t2` (

2676

# `sessionid` bigint NOT NULL auto_increment,

2677

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

2678

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

2679

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

2680

# PRIMARY KEY (`sessionid`)

2681

#);

2682

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

2683

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

2684

#drop tables t1,t2;

2685

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

2686

# segfaults:

2687

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

2688

insert into t1 values ('1');

2689

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

2690

max(fld)

2691

2692

drop table t1;

2693

2694

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

2695

2696

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

2697

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

2698

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

2699

INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');

2700

SELECT * FROM t1

2701

WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');

2702

one two flag

2703

5 6 N

2704

7 8 N

2705

SELECT * FROM t1

2706

WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');

2707

one two flag

2708

5 6 N

2709

7 8 N

2710

insert into t2 values (null,null,'N');

2711

insert into t2 values (null,3,'0');

2712

insert into t2 values (null,5,'0');

2713

insert into t2 values (10,null,'0');

2714

insert into t1 values (10,3,'0');

2715

insert into t1 values (10,5,'0');

2716

insert into t1 values (10,10,'0');

2717

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;

2718

one two test

2719

1 2 NULL

2720

2 3 NULL

2721

3 4 NULL

2722

5 6 1

2723

7 8 1

2724

10 3 NULL

2725

10 5 NULL

2726

10 10 NULL

2727

SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');

2728

one two

2729

5 6

2730

7 8

2731

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

2732

one two test

2733

1 2 NULL

2734

2 3 NULL

2735

3 4 NULL

2736

5 6 1

2737

7 8 1

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

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

2752

one two test

2753

1 2 0

2754

2 3 NULL

2755

3 4 0

2756

5 6 0

2757

7 8 0

2758

10 3 NULL

2759

10 5 NULL

2760

10 10 NULL

2761

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

2762

id select_type table type possible_keys key key_len ref rows filtered Extra

2763

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2764

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where

2765

Warnings:

2766

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`

2767

explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');

2768

id select_type table type possible_keys key key_len ref rows filtered Extra

2769

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

2770

2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where

2771

Warnings:

2772

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

2773

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;

2774

id select_type table type possible_keys key key_len ref rows filtered Extra

2775

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2776

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort

2777

Warnings:

2778

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`

2779

DROP TABLE t1,t2;

2780

2781

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

2782

2783

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

2784

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

2785

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

2786

a b

2787

aaa aaa

2788

DROP TABLE t1;

2789

2790

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

2791

2792

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

2793

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

2794

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

2795

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

2796

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

2797

SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2798

WHERE t3.b IS NOT NULL OR t2.a > 10;

2799

a b b

2800

SELECT * FROM t1

2801

WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2802

WHERE t3.b IS NOT NULL OR t2.a > 10);

2803

2804

2805

2806

2807

2808

DROP TABLE t1,t2,t3;

2809

2810

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

2811

# return a wrong result.

2812

2813

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

2814

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

2815

INSERT INTO t1 VALUES (1);

2816

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

2817

2818

2819

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

2820

2821

2822

INSERT INTO t2 VALUES (1);

2823

INSERT INTO t2 VALUES (2);

2824

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

2825

2826

2827

DROP TABLE t1, t2;

2828

# BUG#20975 Wrong query results for subqueries within NOT

2829

create table t1 (s1 char);

2830

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

2831

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

2832

2833

2834

select * from t1 where not (s1 < any (select s1 from t1));

2835

2836

2837

select * from t1 where (s1 < ALL (select s1+1 from t1));

2838

2839

2840

select * from t1 where not(s1 < ALL (select s1+1 from t1));

2841

2842

2843

select * from t1 where (s1+1 = ANY (select s1 from t1));

2844

2845

2846

select * from t1 where NOT(s1+1 = ANY (select s1 from t1));

2847

2848

2849

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

2850

2851

2852

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

2853

2854

2855

drop table t1;

2856

2857

# Bug #16255: Subquery in where

2858

2859

create table t1 (

2860

retailerID varchar(8) NOT NULL,

2861

statusID int NOT NULL,

2862

changed datetime NOT NULL,

2863

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

2864

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

2865

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

2866

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

2867

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

2868

INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");

2869

INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");

2870

select * from t1 r1

2871

where (r1.retailerID,(r1.changed)) in

2872

(SELECT r2.retailerId,(max(changed)) from t1 r2

2873

group by r2.retailerId);

2874

retailerID statusID changed

2875

0026 2 2006-01-06 12:25:53

2876

0037 2 2006-01-06 12:25:53

2877

0048 1 2006-01-06 12:37:50

2878

0059 1 2006-01-06 12:37:50

2879

drop table t1;

2880

2881

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

2882

# produces empty result

2883

2884

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

2885

insert into t1 values (10);

2886

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

2887

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

2888

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

2889

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

2890

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

2891

id select_type table type possible_keys key key_len ref rows Extra

2892

1 PRIMARY t1 const PRIMARY PRIMARY 4 const # Using index

2893

1 PRIMARY r const PRIMARY PRIMARY 4 const #

2894

2 DEPENDENT SUBQUERY t2 ref b b 5 # Using where; Using index

2895

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

2896

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

2897

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

2898

a a b

2899

10 3 35989

2900

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

2901

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

2902

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

2903

id select_type table type possible_keys key key_len ref rows Extra

2904

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2905

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2906

2 DEPENDENT SUBQUERY t2 ref b b 5 1 Using where; Using index

2907

SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2908

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2909

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2910

a a b

2911

10 1 359

2912

drop table t1,t2;

2913

2914

# Bug #21853: assert failure for a grouping query with

2915

# an ALL/ANY quantified subquery in HAVING

2916

2917

CREATE TEMPORARY TABLE t1 (

2918

field1 int NOT NULL,

2919

field2 int NOT NULL,

2920

field3 int NOT NULL,

2921

PRIMARY KEY (field1,field2,field3))

2922

ENGINE=MyISAM;

2923

CREATE TEMPORARY TABLE t2 (

2924

fieldA int NOT NULL,

2925

fieldB int NOT NULL,

2926

PRIMARY KEY (fieldA,fieldB))

2927

ENGINE=MyISAM;

2928

INSERT INTO t1 VALUES

2929

(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);

2930

INSERT INTO t2 VALUES (1,1), (1,2), (1,3);

2931

SELECT field1, field2, COUNT(*)

2932

FROM t1 GROUP BY field1, field2;

2933

field1 field2 COUNT(*)

2934

1 1 2

2935

1 2 3

2936

1 3 1

2937

SELECT field1, field2

2938

FROM t1

2939

GROUP BY field1, field2

2940

HAVING COUNT(*) >= ALL (SELECT fieldB

2941

FROM t2 WHERE fieldA = field1);

2942

field1 field2

2943

1 2

2944

SELECT field1, field2

2945

FROM t1

2946

GROUP BY field1, field2

2947

HAVING COUNT(*) < ANY (SELECT fieldB

2948

FROM t2 WHERE fieldA = field1);

2949

field1 field2

2950

1 1

2951

1 3

2952

DROP TABLE t1, t2;

2953

2954

# Bug #23478: not top-level IN subquery returning a non-empty result set

2955

# with possible NULL values by index access from the outer query

2956

2957

CREATE TEMPORARY TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;

2958

INSERT INTO t1 VALUES (1), (3), (5), (7);

2959

INSERT INTO t1 VALUES (NULL);

2960

CREATE TEMPORARY TABLE t2(a int) ENGINE=MyISAM;

2961

INSERT INTO t2 VALUES (1),(2),(3);

2962

EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;

2963

id select_type table type possible_keys key key_len ref rows Extra

2964

1 PRIMARY t2 ALL NULL NULL NULL NULL 3

2965

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key

2966

SELECT a, a IN (SELECT a FROM t1) FROM t2;

2967

a a IN (SELECT a FROM t1)

2968

1 1

2969

2 NULL

2970

3 1

2971

DROP TABLE t1,t2;

2972

2973

# Bug #11302: getObject() returns a String for a sub-query of type datetime

2974

2975

CREATE TABLE t1 (a DATETIME);

2976

INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');

2977

CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT

2978

(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a

2979

FROM t1 WHERE a > '2000-01-01';

2980

SHOW CREATE TABLE t2;

2981

Table Create Table

2982

t2 CREATE TEMPORARY TABLE `t2` (

2983

`sub_a` datetime DEFAULT NULL

2984

) ENGINE=MyISAM

2985

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');

2986

SHOW CREATE TABLE t3;

2987

Table Create Table

2988

t3 CREATE TEMPORARY TABLE `t3` (

2989

`a` datetime DEFAULT NULL

2990

) ENGINE=MyISAM

2991

DROP TABLE t1,t2,t3;

2992

2993

# Bug 24653: sorting by expressions containing subselects

2994

# that return more than one row

2995

2996

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

2997

INSERT INTO t1 VALUES (2), (4), (1), (3);

2998

CREATE TABLE t2 (b int, c int);

2999

INSERT INTO t2 VALUES

3000

(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);

3001

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );

3002

3003

3004

3005

3006

3007

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);

3008

ERROR 21000: Subquery returns more than 1 row

3009

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;

3010

3011

3012

3013

3014

3015

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;

3016

ERROR 21000: Subquery returns more than 1 row

3017

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);

3018

b MAX(c)

3019

1 4

3020

2 2

3021

4 4

3022

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);

3023

ERROR 21000: Subquery returns more than 1 row

3024

SELECT a FROM t1 GROUP BY a

3025

HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),

3026

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

3027

3028

3029

3030

3031

3032

SELECT a FROM t1 GROUP BY a

3033

HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),

3034

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

3035

ERROR 21000: Subquery returns more than 1 row

3036

SELECT a FROM t1 GROUP BY a

3037

HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),

3038

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

3039

3040

3041

SELECT a FROM t1 GROUP BY a

3042

HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),

3043

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;

3044

ERROR 21000: Subquery returns more than 1 row

3045

SELECT a FROM t1

3046

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),

3047

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));

3048

3049

3050

3051

3052

3053

SELECT a FROM t1

3054

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),

3055

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

3056

ERROR 21000: Subquery returns more than 1 row

3057

SELECT a FROM t1

3058

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),

3059

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));

3060

3061

3062

3063

3064

3065

SELECT a FROM t1

3066

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),

3067

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

3068

ERROR 21000: Subquery returns more than 1 row

3069

DROP TABLE t1,t2;

3070

# End of 4.1 tests

3071

3072

#decimal-related tests

3073

3074

create table t1 (df decimal(5,1));

3075

insert into t1 values(1.1);

3076

insert into t1 values(2.2);

3077

select * from t1 where df <= all (select avg(df) from t1 group by df);

3078

3079

1.1

3080

select * from t1 where df >= all (select avg(df) from t1 group by df);

3081

3082

2.2

3083

drop table t1;

3084

create temporary table t1 (df decimal(5,1)) ENGINE=MyISAM;

3085

insert into t1 values(1.1);

3086

select 1.1 * exists(select * from t1);

3087

1.1 * exists(select * from t1)

3088

1.1

3089

drop table t1;

3090

CREATE TEMPORARY TABLE t1 (

3091

grp int default NULL,

3092

a decimal(10,2) default NULL) ENGINE=MyISAM;

3093

insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);

3094

select * from t1;

3095

grp a

3096

1 1.00

3097

2 2.00

3098

2 3.00

3099

3 4.00

3100

3 5.00

3101

3 6.00

3102

NULL NULL

3103

select min(a) from t1 group by grp;

3104

min(a)

3105

NULL

3106

1.00

3107

2.00

3108

4.00

3109

drop table t1;

3110

3111

# Test for bug #9338: lame substitution of c1 instead of c2

3112

3113

CREATE temporary table t1 ( c1 integer ) ENGINE=MyISAM;

3114

INSERT INTO t1 VALUES ( 1 );

3115

INSERT INTO t1 VALUES ( 2 );

3116

INSERT INTO t1 VALUES ( 3 );

3117

CREATE TABLE t2 ( c2 integer );

3118

INSERT INTO t2 VALUES ( 1 );

3119

INSERT INTO t2 VALUES ( 4 );

3120

INSERT INTO t2 VALUES ( 5 );

3121

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);

3122

c1 c2

3123

1 1

3124

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2

3125

WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );

3126

c1 c2

3127

1 1

3128

DROP TABLE t1,t2;

3129

3130

# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ

3131

3132

CREATE TEMPORARY TABLE t1 ( c1 integer ) ENGINE=MyISAM;

3133

INSERT INTO t1 VALUES ( 1 );

3134

INSERT INTO t1 VALUES ( 2 );

3135

INSERT INTO t1 VALUES ( 3 );

3136

INSERT INTO t1 VALUES ( 6 );

3137

CREATE TEMPORARY TABLE t2 ( c2 integer ) ENGINE=MyISAM;

3138

INSERT INTO t2 VALUES ( 1 );

3139

INSERT INTO t2 VALUES ( 4 );

3140

INSERT INTO t2 VALUES ( 5 );

3141

INSERT INTO t2 VALUES ( 6 );

3142

CREATE TEMPORARY TABLE t3 ( c3 integer ) ENGINE=MyISAM;

3143

INSERT INTO t3 VALUES ( 7 );

3144

INSERT INTO t3 VALUES ( 8 );

3145

SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2

3146

WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );

3147

c1 c2

3148

2 NULL

3149

3 NULL

3150

DROP TABLE t1,t2,t3;

3151

3152

# Correct building of equal fields list (do not include outer

3153

# fields) (BUG#6384)

3154

3155

CREATE TEMPORARY TABLE t1 (EMPNUM CHAR(3)) ENGINE=MyISAM;

3156

CREATE TEMPORARY TABLE t2 (EMPNUM CHAR(3) ) ENGINE=MyISAM;

3157

INSERT INTO t1 VALUES ('E1'),('E2');

3158

INSERT INTO t2 VALUES ('E1');

3159

DELETE FROM t1

3160

WHERE t1.EMPNUM NOT IN

3161

(SELECT t2.EMPNUM

3162

FROM t2

3163

WHERE t1.EMPNUM = t2.EMPNUM);

3164

select * from t1;

3165

EMPNUM

3166

3167

DROP TABLE t1,t2;

3168

3169

# Test for bug #11487: range access in a subquery

3170

3171

CREATE TEMPORARY TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;

3172

INSERT INTO t1 VALUES (1, 1);

3173

CREATE TEMPORARY TABLE t2 (select_id BIGINT, values_id BIGINT,

3174

PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;

3175

INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);

3176

SELECT values_id FROM t1

3177

WHERE values_id IN (SELECT values_id FROM t2

3178

WHERE select_id IN (1, 0));

3179

values_id

3180

3181

SELECT values_id FROM t1

3182

WHERE values_id IN (SELECT values_id FROM t2

3183

WHERE select_id BETWEEN 0 AND 1);

3184

values_id

3185

3186

SELECT values_id FROM t1

3187

WHERE values_id IN (SELECT values_id FROM t2

3188

WHERE select_id = 0 OR select_id = 1);

3189

values_id

3190

3191

DROP TABLE t1, t2;

3192

# BUG#11821 : Select from subselect using aggregate function on an enum

3193

# segfaults:

3194

create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;

3195

insert into t1 values ('1');

3196

select * from (select max(fld) from t1) as foo;

3197

max(fld)

3198

3199

drop table t1;

3200

3201

# Test for bug #11762: subquery with an aggregate function in HAVING

3202

3203

CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;

3204

CREATE TEMPORARY TABLE t2 (c int, d int) ENGINE=MyISAM;

3205

CREATE TEMPORARY TABLE t3 (e int) ENGINE=MyISAM;

3206

INSERT INTO t1 VALUES

3207

(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);

3208

INSERT INTO t2 VALUES

3209

(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);

3210

INSERT INTO t3 VALUES (10), (30), (10), (20) ;

3211

SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;

3212

a MAX(b) MIN(b)

3213

1 20 10

3214

2 30 10

3215

3 20 20

3216

4 40 40

3217

SELECT * FROM t2;

3218

c d

3219

2 10

3220

2 20

3221

4 10

3222

5 10

3223

3 20

3224

2 40

3225

SELECT * FROM t3;

3226

3227

3228

3229

3230

3231

SELECT a FROM t1 GROUP BY a

3232

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);

3233

3234

3235

3236

SELECT a FROM t1 GROUP BY a

3237

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);

3238

3239

3240

SELECT a FROM t1 GROUP BY a

3241

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);

3242

3243

3244

3245

SELECT a FROM t1 GROUP BY a

3246

HAVING a IN (SELECT c FROM t2

3247

WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));

3248

3249

3250

3251

SELECT a FROM t1 GROUP BY a

3252

HAVING a IN (SELECT c FROM t2

3253

WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

3254

3255

3256

3257

SELECT a FROM t1 GROUP BY a

3258

HAVING a IN (SELECT c FROM t2

3259

WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));

3260

3261

3262

SELECT a FROM t1 GROUP BY a

3263

HAVING a IN (SELECT c FROM t2

3264

WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));

3265

3266

3267

SELECT a FROM t1 GROUP BY a

3268

HAVING a IN (SELECT c FROM t2

3269

WHERE MIN(b) < d AND

3270

EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

3271

3272

3273

SELECT a, SUM(a) FROM t1 GROUP BY a;

3274

a SUM(a)

3275

1 2

3276

2 6

3277

3 3

3278

4 4

3279

SELECT a FROM t1

3280

WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;

3281

3282

3283

3284

SELECT a FROM t1 GROUP BY a

3285

HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);

3286

3287

3288

3289

3290

SELECT a FROM t1

3291

WHERE a < 3 AND

3292

EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;

3293

3294

3295

3296

SELECT a FROM t1

3297

WHERE a < 3 AND

3298

EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);

3299

3300

3301

3302

3303

3304

3305

SELECT t1.a FROM t1 GROUP BY t1.a

3306

HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c

3307

HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e

3308

HAVING SUM(t1.a+t2.c) < t3.e/4));

3309

3310

3311

3312

SELECT t1.a FROM t1 GROUP BY t1.a

3313

HAVING t1.a > ALL(SELECT t2.c FROM t2

3314

WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e

3315

HAVING SUM(t1.a+t2.c) < t3.e/4));

3316

3317

3318

SELECT t1.a FROM t1 GROUP BY t1.a

3319

HAVING t1.a > ALL(SELECT t2.c FROM t2

3320

WHERE EXISTS(SELECT t3.e FROM t3

3321

WHERE SUM(t1.a+t2.c) < t3.e/4));

3322

ERROR HY000: Invalid use of group function

3323

SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;

3324

ERROR HY000: Invalid use of group function

3325

SELECT t1.a FROM t1 GROUP BY t1.a

3326

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3327

HAVING AVG(t2.c+SUM(t1.b)) > 20);

3328

3329

3330

3331

3332

SELECT t1.a FROM t1 GROUP BY t1.a

3333

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3334

HAVING AVG(SUM(t1.b)) > 20);

3335

3336

3337

3338

SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a

3339

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3340

HAVING t2.c+sum > 20);

3341

a sum

3342

2 60

3343

3 20

3344

4 40

3345

DROP TABLE t1,t2,t3;

3346

3347

# Test for bug #16603: GROUP BY in a row subquery with a quantifier

3348

# when an index is defined on the grouping field

3349

CREATE TABLE t1 (a varchar(5), b varchar(10));

3350

INSERT INTO t1 VALUES

3351

('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),

3352

('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);

3353

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3354

a b

3355

BBB 4

3356

CCC 7

3357

AAA 8

3358

EXPLAIN

3359

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3360

id select_type table type possible_keys key key_len ref rows Extra

3361

1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where

3362

2 SUBQUERY t1 ALL NULL NULL NULL NULL # Using temporary; Using filesort

3363

ALTER TABLE t1 ADD INDEX(a);

3364

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3365

a b

3366

BBB 4

3367

CCC 7

3368

AAA 8

3369

EXPLAIN

3370

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3371

id select_type table type possible_keys key key_len ref rows Extra

3372

1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where

3373

2 SUBQUERY t1 index NULL a 23 NULL #

3374

DROP TABLE t1;

3375

3376

# Bug#17366: Unchecked Item_int results in server crash

3377

3378

create table t1( f1 int,f2 int);

3379

insert into t1 values (1,1),(2,2);

3380

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';

3381

3382

crash1

3383

crash1

3384

drop table t1;

3385

3386

# Bug #18306: server crash on delete using subquery.

3387

3388

create temporary table t1 (c int, key(c)) ENGINE=MyISAM;

3389

insert into t1 values (1142477582), (1142455969);

3390

create temporary table t2 (a int, b int) ENGINE=MyISAM;

3391

insert into t2 values (2, 1), (1, 0);

3392

delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;

3393

drop table t1, t2;

3394

3395

# Bug#19077: A nested materialized derived table is used before being populated.

3396

3397

create table t1 (i int, j bigint);

3398

insert into t1 values (1, 2), (2, 2), (3, 2);

3399

select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;

3400

min(i)

3401

3402

drop table t1;

3403

3404

# Bug#19700: subselect returning BIGINT always returned it as SIGNED

3405

3406

CREATE TEMPORARY TABLE t1 (i BIGINT) ENGINE=MyISAM;

3407

INSERT INTO t1 VALUES (10000000000000000);

3408

INSERT INTO t1 VALUES (1);

3409

CREATE TEMPORARY TABLE t2 (i BIGINT) ENGINE=MyISAM;

3410

INSERT INTO t2 VALUES (10000000000000000);

3411

INSERT INTO t2 VALUES (1);

3412

/* simple test */

3413

SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;

3414

3415

10000000000000000

3416

3417

/* subquery test */

3418

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3419

3420

10000000000000000

3421

/* subquery test with cast*/

3422

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3423

3424

10000000000000000

3425

DROP TABLE t1;

3426

DROP TABLE t2;

3427

3428

# Bug#20519: subselect with LIMIT M, N

3429

3430

CREATE TEMPORARY TABLE t1 (

3431

id bigint NOT NULL auto_increment,

3432

name varchar(255) NOT NULL,

3433

PRIMARY KEY (id))

3434

ENGINE=MyISAM;

3435

INSERT INTO t1 VALUES

3436

(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

3437

CREATE TABLE t2 (

3438

id bigint NOT NULL auto_increment,

3439

mid bigint NOT NULL,

3440

date date NOT NULL,

3441

PRIMARY KEY (id));

3442

INSERT INTO t2 VALUES

3443

(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),

3444

(4, 2, '2006-04-20'), (5, 1, '2006-05-01');

3445

SELECT *,

3446

(SELECT date FROM t2 WHERE mid = t1.id

3447

ORDER BY date DESC LIMIT 0, 1) AS date_last,

3448

(SELECT date FROM t2 WHERE mid = t1.id

3449

ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last

3450

FROM t1;

3451

id name date_last date_next_to_last

3452

1 Balazs 2006-05-01 NULL

3453

2 Joe 2006-04-20 NULL

3454

3 Frank 2006-04-13 NULL

3455

SELECT *,

3456

(SELECT COUNT(*) FROM t2 WHERE mid = t1.id

3457

ORDER BY date DESC LIMIT 1, 1) AS date_count

3458

FROM t1;

3459

id name date_count

3460

1 Balazs NULL

3461

2 Joe NULL

3462

3 Frank NULL

3463

SELECT *,

3464

(SELECT date FROM t2 WHERE mid = t1.id

3465

ORDER BY date DESC LIMIT 0, 1) AS date_last,

3466

(SELECT date FROM t2 WHERE mid = t1.id

3467

ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last

3468

FROM t1;

3469

id name date_last date_next_to_last

3470

1 Balazs 2006-05-01 2006-03-30

3471

2 Joe 2006-04-20 2006-04-06

3472

3 Frank 2006-04-13 NULL

3473

DROP TABLE t1,t2;

3474

3475

# Bug#20869: subselect with range access by DESC

3476

3477

CREATE TABLE t1 (

3478

i1 int NOT NULL default '0',

3479

i2 int NOT NULL default '0',

3480

t datetime,

3481

PRIMARY KEY (i1,i2,t));

3482

INSERT INTO t1 VALUES

3483

(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),

3484

(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),

3485

(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),

3486

(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),

3487

(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),

3488

(24,2,'2005-05-27 12:40:06');

3489

CREATE TABLE t2 (

3490

i1 int NOT NULL default '0',

3491

i2 int NOT NULL default '0',

3492

t datetime default NULL,

3493

PRIMARY KEY (i1));

3494

INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');

3495

EXPLAIN

3496

SELECT * FROM t1,t2

3497

WHERE t1.t = (SELECT t1.t FROM t1

3498

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3499

ORDER BY t1.t DESC LIMIT 1);

3500

id select_type table type possible_keys key key_len ref rows Extra

3501

1 PRIMARY t2 ALL NULL NULL NULL NULL 1

3502

1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index; Using join buffer

3503

2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 5 Using where; Using index; Using filesort

3504

SELECT * FROM t1,t2

3505

WHERE t1.t = (SELECT t1.t FROM t1

3506

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3507

ORDER BY t1.t DESC LIMIT 1);

3508

i1 i2 t i1 i2 t

3509

24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40

3510

DROP TABLE t1, t2;

3511

3512

# Bug#14654 : Cannot select from the same table twice within a UNION

3513

# statement

3514

3515

CREATE TABLE t1 (i INT);

3516

(SELECT i FROM t1) UNION (SELECT i FROM t1);

3517

3518

SELECT * FROM t1 WHERE NOT EXISTS

3519

(

3520

(SELECT i FROM t1) UNION

3521

(SELECT i FROM t1)

3522

);

3523

3524

#TODO:not supported

3525

SELECT * FROM t1

3526

WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));

3527

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

3528

#TODO:not supported

3529

explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))

3530

from t1;

3531

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))

3532

from t1' at line 1

3533

explain select * from t1 where not exists

3534

((select t11.i from t1 t11) union (select t12.i from t1 t12));

3535

id select_type table type possible_keys key key_len ref rows Extra

3536

1 PRIMARY t1 ALL NULL NULL NULL NULL #

3537

2 SUBQUERY t11 ALL NULL NULL NULL NULL #

3538

3 UNION t12 ALL NULL NULL NULL NULL #

3539

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL #

3540

DROP TABLE t1;

3541

3542

# Bug #21540: Subqueries with no from and aggregate functions return

3543

# wrong results

3544

CREATE TABLE t1 (a INT, b INT);

3545

CREATE TABLE t2 (a INT);

3546

INSERT INTO t2 values (1);

3547

INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);

3548

SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;

3549

(SELECT COUNT(DISTINCT t1.b) from t2)

3550

3551

3552

3553

SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)

3554

FROM t1 GROUP BY t1.a;

3555

(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)

3556

3557

3558

3559

SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;

3560

COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))

3561

2 2

3562

1 1

3563

1 1

3564

SELECT (

3565

SELECT (

3566

SELECT (

3567

SELECT COUNT(DISTINCT t1.b)

3568

)

3569

)

3570

FROM t1 GROUP BY t1.a LIMIT 1)

3571

FROM t1 t2

3572

GROUP BY t2.a;

3573

(

3574

SELECT (

3575

SELECT (

3576

SELECT COUNT(DISTINCT t1.b)

3577

)

3578

)

3579

FROM t1 GROUP BY t1.a LIMIT 1)

3580

3581

3582

3583

DROP TABLE t1,t2;

3584

3585

# Bug #21727: Correlated subquery that requires filesort:

3586

# slow with big sort_buffer_size

3587

3588

CREATE TEMPORARY TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;

3589

CREATE TEMPORARY TABLE t2 (x int auto_increment, y int, z int,

3590

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;

3591

SET SESSION sort_buffer_size = 32 * 1024;

3592

Warnings:

3593

Error 1292 Truncated incorrect sort_buffer_size value: '32768'

3594

SELECT COUNT(*)

3595

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3596

FROM t1) t;

3597

COUNT(*)

3598

3000

3599

SET SESSION sort_buffer_size = 8 * 1024 * 1024;

3600

SELECT COUNT(*)

3601

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3602

FROM t1) t;

3603

COUNT(*)

3604

3000

3605

DROP TABLE t2,t1;

3606

3607

# Bug #25219: EXIST subquery with UNION over a mix of

3608

# correlated and uncorrelated selects

3609

3610

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);

3611

CREATE TABLE t2 (c int);

3612

INSERT INTO t1 VALUES ('aa', 1);

3613

INSERT INTO t2 VALUES (1);

3614

SELECT * FROM t1

3615

WHERE EXISTS (SELECT c FROM t2 WHERE c=1

3616

UNION

3617

SELECT c from t2 WHERE c=t1.c);

3618

id c

3619

aa 1

3620

INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);

3621

SELECT * FROM t1

3622

WHERE EXISTS (SELECT c FROM t2 WHERE c=1

3623

UNION

3624

SELECT c from t2 WHERE c=t1.c);

3625

id c

3626

aa 1

3627

bb 2

3628

cc 3

3629

dd 1

3630

INSERT INTO t2 VALUES (2);

3631

CREATE TEMPORARY TABLE t3 (c int) ENGINE=MyISAM;

3632

INSERT INTO t3 VALUES (1);

3633

SELECT * FROM t1

3634

WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1

3635

UNION

3636

SELECT c from t2 WHERE c=t1.c);

3637

id c

3638

aa 1

3639

bb 2

3640

cc 3

3641

dd 1

3642

DROP TABLE t1,t2,t3;

3643

3644

# Bug#21904 (parser problem when using IN with a double "(())")

3645

3646

DROP TABLE IF EXISTS t1;

3647

DROP TABLE IF EXISTS t2;

3648

DROP TABLE IF EXISTS t1xt2;

3649

CREATE TEMPORARY TABLE t1 (

3650

id_1 int NOT NULL,

3651

t varchar(4) DEFAULT NULL)

3652

ENGINE=MyISAM;

3653

CREATE TEMPORARY TABLE t2 (

3654

id_2 int NOT NULL,

3655

t varchar(4) DEFAULT NULL)

3656

ENGINE=MyISAM;

3657

CREATE TEMPORARY TABLE t1xt2 (

3658

id_1 int NOT NULL,

3659

id_2 int NOT NULL)

3660

ENGINE=MyISAM;

3661

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

3662

INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');

3663

INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);

3664

# subselect returns 0 rows

3665

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3666

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3667

id_1

3668

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3669

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3670

id_1

3671

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3672

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3673

id_1

3674

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3675

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3676

id_1

3677

3678

3679

3680

3681

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3682

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));

3683

id_1

3684

3685

3686

3687

3688

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3689

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));

3690

id_1

3691

3692

3693

3694

3695

insert INTO t1xt2 VALUES (1, 12);

3696

# subselect returns 1 row

3697

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3698

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3699

id_1

3700

3701

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3702

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3703

id_1

3704

3705

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3706

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3707

id_1

3708

3709

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3710

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3711

id_1

3712

3713

3714

3715

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3716

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3717

id_1

3718

3719

3720

3721

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3722

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3723

id_1

3724

3725

3726

3727

insert INTO t1xt2 VALUES (2, 12);

3728

# subselect returns more than 1 row

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 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 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

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3750

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3751

id_1

3752

3753

3754

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3755

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3756

id_1

3757

3758

3759

DROP TABLE t1;

3760

DROP TABLE t2;

3761

DROP TABLE t1xt2;

3762

3763

# Bug #26728: derived table with concatanation of literals in select list

3764

3765

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

3766

INSERT INTO t1 VALUES (3), (1), (2);

3767

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;

3768

col1 col2

3769

this is a test. 3

3770

this is a test. 1

3771

this is a test. 2

3772

SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;

3773

col1 t2

3774

this is a test. 3

3775

this is a test. 1

3776

this is a test. 2

3777

DROP table t1;

3778

3779

# Bug #27257: COUNT(*) aggregated in outer query

3780

3781

CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;

3782

CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;

3783

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

3784

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3785

SELECT COUNT(*), a,

3786

(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3787

FROM t1 GROUP BY a;

3788

COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3789

2 2 2

3790

3 3 3

3791

1 4 1

3792

SELECT COUNT(*), a,

3793

(SELECT MIN(m) FROM t2 WHERE m = count(*))

3794

FROM t1 GROUP BY a;

3795

COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))

3796

2 2 2

3797

3 3 3

3798

1 4 1

3799

SELECT COUNT(*), a

3800

FROM t1 GROUP BY a

3801

HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;

3802

COUNT(*) a

3803

2 2

3804

3 3

3805

DROP TABLE t1,t2;

3806

3807

# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument

3808

3809

CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;

3810

CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;

3811

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

3812

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3813

SELECT COUNT(*) c, a,

3814

(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3815

FROM t1 GROUP BY a;

3816

c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3817

2 2 2

3818

3 3 3

3819

1 4 1,1

3820

SELECT COUNT(*) c, a,

3821

(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3822

FROM t1 GROUP BY a;

3823

c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3824

2 2 3

3825

3 3 4

3826

1 4 2,2

3827

DROP table t1,t2;

3828

3829

# Bug#27321: Wrong subquery result in a grouping select

3830

3831

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));

3832

INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),

3833

(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),

3834

(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');

3835

SELECT a, MAX(b),

3836

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test

3837

FROM t1 GROUP BY a;

3838

a MAX(b) test

3839

1 9 m

3840

2 3 h

3841

3 4 i

3842

SELECT a x, MAX(b),

3843

(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test

3844

FROM t1 GROUP BY a;

3845

x MAX(b) test

3846

1 9 m

3847

2 3 h

3848

3 4 i

3849

SELECT a, AVG(b),

3850

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test

3851

FROM t1 WHERE t1.d=0 GROUP BY a;

3852

a AVG(b) test

3853

1 4.0000 d

3854

2 2.0000 g

3855

3 2.5000 NULL

3856

SELECT tt.a,

3857

(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3858

LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3859

FROM t1 as tt;

3860

a test

3861

1 n

3862

1 n

3863

1 n

3864

1 n

3865

1 n

3866

1 n

3867

1 n

3868

2 o

3869

2 o

3870

2 o

3871

2 o

3872

3 p

3873

3 p

3874

3 p

3875

3 p

3876

3 p

3877

SELECT tt.a,

3878

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3879

LIMIT 1)

3880

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3881

FROM t1 as tt GROUP BY tt.a;

3882

a test

3883

1 n

3884

2 o

3885

3 p

3886

SELECT tt.a, MAX(

3887

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3888

LIMIT 1)

3889

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test

3890

FROM t1 as tt GROUP BY tt.a;

3891

a test

3892

1 n

3893

2 o

3894

3 p

3895

DROP TABLE t1;

3896

3897

# Bug #27363: nested aggregates in outer, subquery / sum(select

3898

# count(outer))

3899

3900

CREATE TABLE t1 (a INT);

3901

INSERT INTO t1 values (1),(1),(1),(1);

3902

CREATE TEMPORARY TABLE t2 (x INT) ENGINE=MyISAM;

3903

INSERT INTO t1 values (1000),(1001),(1002);

3904

SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;

3905

ERROR HY000: Invalid use of group function

3906

SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;

3907

ERROR HY000: Invalid use of group function

3908

SELECT

3909

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )

3910

FROM t1;

3911

ERROR HY000: Invalid use of group function

3912

SELECT t1.a as XXA,

3913

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )

3914

FROM t1;

3915

ERROR HY000: Invalid use of group function

3916

DROP TABLE t1,t2;

3917

3918

# Bug #27807: Server crash when executing subquery with EXPLAIN

3919

3920

CREATE TABLE t1 (a int, b int, KEY (a));

3921

INSERT INTO t1 VALUES (1,1),(2,1);

3922

EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);

3923

id select_type table type possible_keys key key_len ref rows Extra

3924

1 PRIMARY t1 ref a a 5 const 1 Using where; Using index

3925

2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort

3926

DROP TABLE t1;

3927

3928

# Bug #28377: grouping query with a correlated subquery in WHERE condition

3929

3930

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));

3931

INSERT INTO t1 VALUES

3932

(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');

3933

CREATE TEMPORARY TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;

3934

INSERT INTO t2 VALUES (7), (5), (1), (3);

3935

SELECT id, st FROM t1

3936

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3937

id st

3938

3 FL

3939

1 GA

3940

7 FL

3941

SELECT id, st FROM t1

3942

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)

3943

GROUP BY id;

3944

id st

3945

1 GA

3946

3 FL

3947

7 FL

3948

SELECT id, st FROM t1

3949

WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3950

id st

3951

2 GA

3952

4 FL

3953

SELECT id, st FROM t1

3954

WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)

3955

GROUP BY id;

3956

id st

3957

2 GA

3958

4 FL

3959

DROP TABLE t1,t2;

3960

3961

# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table

3962

# over a grouping subselect

3963

3964

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

3965

INSERT INTO t1 VALUES (1), (2);

3966

EXPLAIN EXTENDED

3967

SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;

3968

id select_type table type possible_keys key key_len ref rows filtered Extra

3969

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00

3970

2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort

3971

Warnings:

3972

Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`

3973

DROP TABLE t1;

3974

3975

# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1

3976

3977

CREATE TABLE t1 (

3978

a varchar(255) default NULL,

3979

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3980

INDEX idx(a,b));

3981

Warnings:

3982

Warning 1071 Specified key was too long; max key length is 767 bytes

3983

CREATE TABLE t2 (

3984

a varchar(255) default NULL);

3985

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');

3986

INSERT INTO t1 SELECT * FROM t1;

3987

INSERT INTO t1 SELECT * FROM t1;

3988

INSERT INTO t1 SELECT * FROM t1;

3989

INSERT INTO t1 SELECT * FROM t1;

3990

INSERT INTO t1 SELECT * FROM t1;

3991

INSERT INTO t1 SELECT * FROM t1;

3992

INSERT INTO t1 SELECT * FROM t1;

3993

INSERT INTO t1 SELECT * FROM t1;

3994

INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');

3995

INSERT INTO `t2` VALUES ('abcdefghijk');

3996

INSERT INTO `t2` VALUES ('asdf');

3997

SET session sort_buffer_size=8192;

3998

Warnings:

3999

Error 1292 Truncated incorrect sort_buffer_size value: '8192'

4000

SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;

4001

4002

4003

4004

DROP TABLE t1,t2;

4005

4006

# Bug #27333: subquery grouped for aggregate of outer query / no aggregate

4007

# of subquery

4008

4009

CREATE TEMPORARY TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;

4010

CREATE TEMPORARY TABLE t2 (x INTEGER) ENGINE=MyISAM;

4011

INSERT INTO t1 VALUES (1,11), (2,22), (2,22);

4012

INSERT INTO t2 VALUES (1), (2);

4013

# wasn't failing, but should

4014

SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;

4015

ERROR 21000: Subquery returns more than 1 row

4016

# fails as it should

4017

SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;

4018

ERROR 21000: Subquery returns more than 1 row

4019

SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;

4020

(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)

4021

3.3333

4022

DROP TABLE t1,t2;

4023

# second test case from 27333

4024

CREATE TABLE t1 (a INT, b INT);

4025

INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);

4026

# returns no rows, when it should

4027

SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1

4028

AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)

4029

GROUP BY a1.a;

4030

a COUNT(*)

4031

1 3

4032

DROP TABLE t1;

4033

#test cases from 29297

4034

CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;

4035

CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MyISAM;

4036

INSERT INTO t1 VALUES (1),(2);

4037

INSERT INTO t2 VALUES (1),(2);

4038

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;

4039

(SELECT SUM(t1.a) FROM t2 WHERE a=0)

4040

NULL

4041

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;

4042

ERROR 21000: Subquery returns more than 1 row

4043

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;

4044

(SELECT SUM(t1.a) FROM t2 WHERE a=1)

4045

4046

DROP TABLE t1,t2;

4047

4048

# Bug #31884: Assertion + crash in subquery in the SELECT clause.

4049

4050

CREATE TEMPORARY TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;

4051

CREATE TEMPORARY TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;

4052

INSERT INTO t1 VALUES (100, 200);

4053

INSERT INTO t1 VALUES (101, 201);

4054

INSERT INTO t2 VALUES (101, 201);

4055

INSERT INTO t2 VALUES (103, 203);

4056

SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;

4057

((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL

4058

4059

4060

DROP TABLE t1, t2;

4061

4062

# Bug #30788: Inconsistent retrieval of char/varchar

4063

4064

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));

4065

INSERT INTO t1 VALUES ('a', 'aa');

4066

INSERT INTO t1 VALUES ('a', 'aaa');

4067

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

4068

a b

4069

a aa

4070

a aaa

4071

CREATE INDEX I1 ON t1 (a);

4072

CREATE INDEX I2 ON t1 (b);

4073

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

4074

id select_type table type possible_keys key key_len ref rows Extra

4075

1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where

4076

2 SUBQUERY t1 index NULL I1 7 NULL # Using index

4077

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

4078

a b

4079

a aa

4080

a aaa

4081

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));

4082

INSERT INTO t2 SELECT * FROM t1;

4083

CREATE INDEX I1 ON t2 (a);

4084

CREATE INDEX I2 ON t2 (b);

4085

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

4086

id select_type table type possible_keys key key_len ref rows Extra

4087

1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where

4088

2 SUBQUERY t2 index NULL I1 7 NULL # Using index

4089

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

4090

a b

4091

a aa

4092

a aaa

4093

EXPLAIN

4094

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4095

id select_type table type possible_keys key key_len ref rows Extra

4096

1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where

4097

2 SUBQUERY t1 index NULL I1 7 NULL # Using where; Using index

4098

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4099

a b

4100

a aa

4101

a aaa

4102

DROP TABLE t1,t2;

4103

4104

# Bug #32400: Complex SELECT query returns correct result only on some

4105

# occasions

4106

4107

CREATE TABLE t1(a INT, b INT);

4108

INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);

4109

EXPLAIN

4110

SELECT a AS out_a, MIN(b) FROM t1

4111

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

4112

GROUP BY a;

4113

ERROR 42S22: Unknown column 'out_a' in 'where clause'

4114

SELECT a AS out_a, MIN(b) FROM t1

4115

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

4116

GROUP BY a;

4117

ERROR 42S22: Unknown column 'out_a' in 'where clause'

4118

EXPLAIN

4119

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

4120

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

4121

GROUP BY a;

4122

id select_type table type possible_keys key key_len ref rows Extra

4123

1 PRIMARY t1_outer ALL NULL NULL NULL NULL # Using where; Using temporary; Using filesort

4124

2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where

4125

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

4126

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

4127

GROUP BY a;

4128

out_a MIN(b)

4129

1 2

4130

2 4

4131

DROP TABLE t1;

4132

4133

# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122

4134

4135

CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;

4136

CREATE TABLE t2 (a INT);

4137

INSERT INTO t1 VALUES (1),(2);

4138

INSERT INTO t2 VALUES (1),(2);

4139

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

4140

4141

4142

4143

EXPLAIN EXTENDED

4144

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

4145

id select_type table type possible_keys key key_len ref rows filtered Extra

4146

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where

4147

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where

4148

Warnings:

4149

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

4150

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`))

4151

EXPLAIN EXTENDED

4152

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION

4153

(SELECT 1 FROM t2 WHERE t1.a = t2.a));

4154

id select_type table type possible_keys key key_len ref rows filtered Extra

4155

1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00 Using where

4156

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00 Using where

4157

3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL # 100.00 Using where

4158

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL

4159

Warnings:

4160

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

4161

Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1

4162

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`)))

4163

DROP TABLE t1,t2;

4164

4165

# BUG#33794 "MySQL crashes executing specific query on specific dump"

4166

4167

CREATE TEMPORARY TABLE t4 (

4168

f7 varchar(32) collate utf8_bin NOT NULL default '',

4169

f10 varchar(32) collate utf8_bin default NULL,

4170

PRIMARY KEY (f7))

4171

ENGINE=MyISAM;

4172

INSERT INTO t4 VALUES(1,1), (2,null);

4173

CREATE TABLE t2 (

4174

f4 varchar(32) collate utf8_bin NOT NULL default '',

4175

f2 varchar(50) collate utf8_bin default NULL,

4176

f3 varchar(10) collate utf8_bin default NULL,

4177

PRIMARY KEY (f4),

4178

UNIQUE KEY uk1 (f2));

4179

INSERT INTO t2 VALUES(1,1,null), (2,2,null);

4180

CREATE TEMPORARY TABLE t1 (

4181

f8 varchar(32) collate utf8_bin NOT NULL default '',

4182

f1 varchar(10) collate utf8_bin default NULL,

4183

f9 varchar(32) collate utf8_bin default NULL,

4184

PRIMARY KEY (f8))

4185

ENGINE=MyISAM;

4186

INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);

4187

CREATE TEMPORARY TABLE t3 (

4188

f6 varchar(32) collate utf8_bin NOT NULL default '',

4189

f5 varchar(50) collate utf8_bin default NULL,

4190

PRIMARY KEY (f6))

4191

ENGINE=MyISAM;

4192

INSERT INTO t3 VALUES (1,null), (2,null);

4193

SELECT

4194

IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,

4195

IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,

4196

SUM(

4197

IF(

4198

(SELECT VPC.f2

4199

FROM t2 VPC, t4 a2, t2 a3

4200

WHERE

4201

VPC.f4 = a2.f10 AND a3.f2 = a4

4202

LIMIT 1) IS NULL,

4203

4204

t3.f5

4205

)

4206

) AS a6

4207

FROM

4208

t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4

4209

GROUP BY a4;

4210

a4 f3 a6

4211

1 NULL NULL

4212

2 NULL NULL

4213

DROP TABLE t1, t2, t3, t4;

4214

End of 5.0 tests.

4215

4216

# Test [NOT] IN truth table (both as top-level and general predicate).

4217

4218

create temporary table t_out (subcase char(3),

4219

a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;

4220

create table t_in (a2 char(2), b2 char(2), c2 char(2));

4221

insert into t_out values ('A.1','2a', NULL, '2a');

4222

#------------------------- A.2 - impossible

4223

insert into t_out values ('A.3', '2a', NULL, '2a');

4224

insert into t_out values ('A.4', '2a', NULL, 'xx');

4225

insert into t_out values ('B.1', '2a', '2a', '2a');

4226

insert into t_out values ('B.2', '2a', '2a', '2a');

4227

insert into t_out values ('B.3', '3a', 'xx', '3a');

4228

insert into t_out values ('B.4', 'xx', '3a', '3a');

4229

insert into t_in values ('1a', '1a', '1a');

4230

insert into t_in values ('2a', '2a', '2a');

4231

insert into t_in values (NULL, '2a', '2a');

4232

insert into t_in values ('3a', NULL, '3a');

4233

4234

Test general IN semantics (not top-level)

4235

4236

case A.1

4237

select subcase,

4238

(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,

4239

(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in

4240

from t_out where subcase = 'A.1';

4241

subcase pred_in pred_not_in

4242

A.1 0 1

4243

case A.2 - impossible

4244

case A.3

4245

select subcase,

4246

(a1, b1, c1) IN (select * from t_in) pred_in,

4247

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4248

from t_out where subcase = 'A.3';

4249

subcase pred_in pred_not_in

4250

A.3 NULL NULL

4251

case A.4

4252

select subcase,

4253

(a1, b1, c1) IN (select * from t_in) pred_in,

4254

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4255

from t_out where subcase = 'A.4';

4256

subcase pred_in pred_not_in

4257

A.4 0 1

4258

case B.1

4259

select subcase,

4260

(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,

4261

(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in

4262

from t_out where subcase = 'B.1';

4263

subcase pred_in pred_not_in

4264

B.1 0 1

4265

case B.2

4266

select subcase,

4267

(a1, b1, c1) IN (select * from t_in) pred_in,

4268

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4269

from t_out where subcase = 'B.2';

4270

subcase pred_in pred_not_in

4271

B.2 1 0

4272

case B.3

4273

select subcase,

4274

(a1, b1, c1) IN (select * from t_in) pred_in,

4275

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4276

from t_out where subcase = 'B.3';

4277

subcase pred_in pred_not_in

4278

B.3 NULL NULL

4279

case B.4

4280

select subcase,

4281

(a1, b1, c1) IN (select * from t_in) pred_in,

4282

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4283

from t_out where subcase = 'B.4';

4284

subcase pred_in pred_not_in

4285

B.4 0 1

4286

4287

Test IN as top-level predicate, and

4288

as non-top level for cases A.3, B.3 (the only cases with NULL result).

4289

4290

case A.1

4291

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4292

where subcase = 'A.1' and

4293

(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');

4294

pred_in

4295

4296

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4297

where subcase = 'A.1' and

4298

(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');

4299

pred_not_in

4300

4301

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4302

where subcase = 'A.1' and

4303

NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));

4304

not_pred_in

4305

4306

case A.3

4307

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4308

where subcase = 'A.3' and

4309

(a1, b1, c1) IN (select * from t_in);

4310

pred_in

4311

4312

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4313

where subcase = 'A.3' and

4314

(a1, b1, c1) NOT IN (select * from t_in);

4315

pred_not_in

4316

4317

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4318

where subcase = 'A.3' and

4319

NOT((a1, b1, c1) IN (select * from t_in));

4320

not_pred_in

4321

4322

# test non-top level result indirectly

4323

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

4324

where subcase = 'A.3' and

4325

((a1, b1, c1) IN (select * from t_in)) is NULL and

4326

((a1, b1, c1) NOT IN (select * from t_in)) is NULL;

4327

pred_in

4328

4329

case A.4

4330

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4331

where subcase = 'A.4' and

4332

(a1, b1, c1) IN (select * from t_in);

4333

pred_in

4334

4335

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4336

where subcase = 'A.4' and

4337

(a1, b1, c1) NOT IN (select * from t_in);

4338

pred_not_in

4339

4340

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4341

where subcase = 'A.4' and

4342

NOT((a1, b1, c1) IN (select * from t_in));

4343

not_pred_in

4344

4345

case B.1

4346

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4347

where subcase = 'B.1' and

4348

(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');

4349

pred_in

4350

4351

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4352

where subcase = 'B.1' and

4353

(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');

4354

pred_not_in

4355

4356

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4357

where subcase = 'B.1' and

4358

NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));

4359

not_pred_in

4360

4361

case B.2

4362

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4363

where subcase = 'B.2' and

4364

(a1, b1, c1) IN (select * from t_in);

4365

pred_in

4366

4367

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4368

where subcase = 'B.2' and

4369

(a1, b1, c1) NOT IN (select * from t_in);

4370

pred_not_in

4371

4372

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4373

where subcase = 'B.2' and

4374

NOT((a1, b1, c1) IN (select * from t_in));

4375

not_pred_in

4376

4377

case B.3

4378

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4379

where subcase = 'B.3' and

4380

(a1, b1, c1) IN (select * from t_in);

4381

pred_in

4382

4383

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4384

where subcase = 'B.3' and

4385

(a1, b1, c1) NOT IN (select * from t_in);

4386

pred_not_in

4387

4388

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4389

where subcase = 'B.3' and

4390

NOT((a1, b1, c1) IN (select * from t_in));

4391

not_pred_in

4392

4393

# test non-top level result indirectly

4394

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

4395

where subcase = 'B.3' and

4396

((a1, b1, c1) IN (select * from t_in)) is NULL and

4397

((a1, b1, c1) NOT IN (select * from t_in)) is NULL;

4398

pred_in

4399

4400

case B.4

4401

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4402

where subcase = 'B.4' and

4403

(a1, b1, c1) IN (select * from t_in);

4404

pred_in

4405

4406

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4407

where subcase = 'B.4' and

4408

(a1, b1, c1) NOT IN (select * from t_in);

4409

pred_not_in

4410

4411

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4412

where subcase = 'B.4' and

4413

NOT((a1, b1, c1) IN (select * from t_in));

4414

not_pred_in

4415

4416

drop table t_out;

4417

drop table t_in;

4418

4419

# Bug#20835 (literal string with =any values)

4420

4421

CREATE TABLE t1 (s1 char(1));

4422

INSERT INTO t1 VALUES ('a');

4423

SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);

4424

4425

4426

DROP TABLE t1;

4427

4428

# Bug#33204: INTO is allowed in subselect, causing inconsistent results

4429

4430

CREATE TABLE t1( a INT );

4431

INSERT INTO t1 VALUES (1),(2);

4432

CREATE TABLE t2( a INT, b INT );

4433

SELECT *

4434

FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;

4435

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

4436

SELECT *

4437

FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;

4438

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

4439

SELECT *

4440

FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;

4441

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

4442

SELECT * FROM (

4443

SELECT 1 a

4444

UNION

4445

SELECT a INTO @var FROM t1 WHERE a = 2

4446

) t1a;

4447

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

4448

) t1a' at line 2

4449

SELECT * FROM (

4450

SELECT 1 a

4451

UNION

4452

SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2

4453

) t1a;

4454

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

4455

) t1a' at line 2

4456

SELECT * FROM (

4457

SELECT 1 a

4458

UNION

4459

SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4460

) t1a;

4461

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

4462

) t1a' at line 2

4463

SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;

4464

4465

4466

SELECT * FROM (

4467

SELECT a FROM t1 WHERE a = 2

4468

UNION

4469

SELECT a FROM t1 WHERE a = 2

4470

) t1a;

4471

4472

4473

SELECT * FROM (

4474

SELECT 1 a

4475

UNION

4476

SELECT a FROM t1 WHERE a = 2

4477

UNION

4478

SELECT a FROM t1 WHERE a = 2

4479

) t1a;

4480

4481

4482

4483

# This was not allowed previously. Possibly, it should be allowed on the future.

4484

# For now, the intent is to keep the fix as non-intrusive as possible.

4485

SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);

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 ')' at line 1

4487

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;

4488

4489

4490

SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;

4491

4492

4493

SELECT * FROM ((SELECT 1 a INTO @a)) 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 @a)) t1a' at line 1

4495

SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) 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 OUTFILE 'file' )) t1a' at line 1

4497

SELECT * FROM ((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 @a)) 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 @a)) t1a' at line 1

4501

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4502

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

4503

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;

4504

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

4505

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;

4506

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

4507

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;

4508

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

4509

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;

4510

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

4511

SELECT * FROM (SELECT 1 a ORDER BY a) t1a;

4512

4513

4514

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;

4515

4516

4517

SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;

4518

4519

4520

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;

4521

4522

4523

# Test of rule

4524

# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias

4525

# UNION should not be allowed inside the parentheses, nor should

4526

# aliases after.

4527

4528

SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;

4529

a 1

4530

1 1

4531

2 1

4532

SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;

4533

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

4534

SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;

4535

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

4536

SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;

4537

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

4538

SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;

4539

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

4540

SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;

4541

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

4542

SELECT * FROM t1 JOIN (t1 t1a) ON 1;

4543

a a

4544

1 1

4545

2 1

4546

1 2

4547

2 2

4548

SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;

4549

a a

4550

1 1

4551

2 1

4552

1 2

4553

2 2

4554

SELECT * FROM (t1 t1a);

4555

4556

4557

4558

SELECT * FROM ((t1 t1a));

4559

4560

4561

4562

SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;

4563

a t1a

4564

1 1

4565

2 1

4566

SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;

4567

a t1a

4568

1 1

4569

2 1

4570

SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;

4571

a a

4572

1 1

4573

2 1

4574

SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;

4575

a a

4576

1 1

4577

2 1

4578

# For the join, TABLE_LIST::select_lex == NULL

4579

# Check that we handle this.

4580

SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;

4581

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

4582

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );

4583

4584

4585

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );

4586

4587

4588

SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );

4589

4590

4591

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);

4592

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

4593

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

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 OUTFILE 'file' )' at line 1

4595

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE '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 DUMPFILE 'file' )' at line 1

4597

SELECT * FROM t1 WHERE a = ( SELECT 1 );

4598

4599

4600

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );

4601

4602

4603

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);

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 @a)' at line 1

4605

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );

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 OUTFILE 'file' )' at line 1

4607

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE '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 DUMPFILE 'file' )' at line 1

4609

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);

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 @a)' at line 1

4611

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

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 OUTFILE 'file' )' at line 1

4613

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE '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 DUMPFILE 'file' )' at line 1

4615

SELECT ( SELECT 1 INTO @v );

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 @v )' at line 1

4617

SELECT ( SELECT 1 INTO OUTFILE 'file' );

4618

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

4619

SELECT ( SELECT 1 INTO DUMPFILE 'file' );

4620

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

4621

SELECT ( SELECT 1 UNION SELECT 1 INTO @v );

4622

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

4623

SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4624

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

4625

SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4626

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

4627

# Make sure context is popped when we leave the nested select

4628

SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;

4629

( SELECT a FROM t1 WHERE a = 1 ) a

4630

1 1

4631

1 2

4632

SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;

4633

( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a

4634

1 1

4635

1 2

4636

# Make sure we have feature F561 (see .yy file)

4637

SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);

4638

a b

4639

# Make sure the parser does not allow nested UNIONs anywhere

4640

SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );

4641

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

4642

( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4643

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

4644

SELECT ( 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 ( ( 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 ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4649

( SELECT 1 UNION SELECT 1 UNION SELECT 1 )

4650

4651

SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));

4652

((SELECT 1 UNION SELECT 1 UNION SELECT 1))

4653

4654

SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4655

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

4656

SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4657

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

4658

SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;

4659

4660

4661

SELECT * FROM t1 WHERE a = ( 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 ) )' at line 1

4663

SELECT * FROM t1 WHERE a = ALL ( 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 ) )' at line 1

4665

SELECT * FROM t1 WHERE a = ANY ( 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 IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4668

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

4669

SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4670

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

4671

SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4672

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

4673

SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4674

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

4675

SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4676

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

4677

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4678

4679

4680

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4681

4682

4683

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4684

4685

4686

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4687

4688

4689

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );

4690

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

4691

SELECT EXISTS(SELECT 1+1);

4692

EXISTS(SELECT 1+1)

4693

4694

SELECT EXISTS(SELECT 1+1 INTO @test);

4695

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

4696

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );

4697

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

4698

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );

4699

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

4700

SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );

4701

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

4702

DROP TABLE t1, t2;

4703

set optimizer_switch='';

4704

ERROR 42000: Variable 'optimizer_switch' can't be set to the value of ''

4705

set optimizer_switch=0;

4706

show variables like 'optimizer_switch';

4707

Variable_name Value

4708

optimizer_switch

Older »