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

179

id select_type table type possible_keys key key_len ref rows filtered Extra

180

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

181

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

182

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

183

4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

184

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

185

Warnings:

186

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

187

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

188

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

189

3 1

190

7 2

191

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

192

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

193

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

194

7 2

195

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

196

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

197

id select_type table type possible_keys key key_len ref rows filtered Extra

198

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

199

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

200

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

201

Warnings:

202

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`

203

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

204

205

206

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

207

208

209

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

210

211

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

212

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

213

8 7.5000

214

8 4.5000

215

9 7.5000

216

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

217

id select_type table type possible_keys key key_len ref rows filtered Extra

218

1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00

219

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

220

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

221

Warnings:

222

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

223

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`

224

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

225

226

227

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

228

229

230

231

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

232

233

234

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

235

236

237

238

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

239

240

241

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

242

243

244

245

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

246

247

248

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

249

250

251

252

insert into t2 values (100, 5);

253

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

254

255

256

257

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

258

259

260

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

261

262

263

264

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

265

id select_type table type possible_keys key key_len ref rows filtered Extra

266

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

267

2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00

268

Warnings:

269

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

270

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

271

272

273

delete from t2 where a=100;

274

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

275

ERROR 21000: Operand should contain 1 column(s)

276

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

277

ERROR 21000: Operand should contain 1 column(s)

278

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

279

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

280

b ma

281

insert into t2 values (2,10);

282

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

283

b ma

284

10 1

285

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

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

7 12

289

create table t5 (a int);

290

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

291

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

292

NULL 1

293

2 2

294

insert into t5 values (5);

295

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

296

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

297

NULL 1

298

2 2

299

insert into t5 values (2);

300

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

301

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

302

NULL 1

303

2 2

304

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;

305

id select_type table type possible_keys key key_len ref rows filtered Extra

306

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00

307

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

308

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

309

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

310

Warnings:

311

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

312

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

313

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`

314

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

315

ERROR 21000: Subquery returns more than 1 row

316

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

317

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

318

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

319

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

320

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

321

patient_uq clinic_uq

322

1 1

323

1 2

324

2 2

325

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

326

id select_type table type possible_keys key key_len ref rows filtered Extra

327

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

328

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

329

Warnings:

330

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

331

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

332

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

333

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

334

drop table t1,t2,t3;

335

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

336

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

337

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

338

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

339

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

340

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

341

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

342

a b

343

W 1732-02-22

344

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

345

a b

346

W 1

347

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

348

a b

349

W a

350

CREATE TABLE `t8` (

351

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

352

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

353

PRIMARY KEY (`pseudo`),

354

UNIQUE KEY `email` (`email`)

355

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

356

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

357

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

358

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

359

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

360

id select_type table type possible_keys key key_len ref rows filtered Extra

361

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

362

4 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index

363

2 SUBQUERY t8 const PRIMARY PRIMARY 142 const 1 100.00

364

3 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index

365

Warnings:

366

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

367

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

368

t8 WHERE pseudo='joce');

369

ERROR 21000: Operand should contain 1 column(s)

370

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

371

pseudo='joce');

372

ERROR 21000: Operand should contain 1 column(s)

373

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

374

pseudo

375

joce

376

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

377

ERROR 21000: Subquery returns more than 1 row

378

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

379

CREATE TABLE `t1` (

380

`topic` bigint NOT NULL default '0',

381

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

382

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

383

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

384

KEY `topic` (`topic`)

385

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

386

INSERT INTO t1 (topic,date,pseudo) VALUES

387

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

388

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

389

id select_type table type possible_keys key key_len ref rows filtered Extra

390

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

391

Warnings:

392

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

393

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

394

id select_type table type possible_keys key key_len ref rows filtered Extra

395

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

396

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

397

Warnings:

398

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

399

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

400

date

401

2002-08-03

402

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

403

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

404

2002-08-03

405

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

406

407

408

409

410

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

411

ERROR 21000: Subquery returns more than 1 row

412

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

413

id select_type table type possible_keys key key_len ref rows filtered Extra

414

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

415

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

416

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

417

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

418

Warnings:

419

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

420

drop table t1;

421

CREATE TABLE `t1` (

422

`numeropost` bigint NOT NULL auto_increment,

423

`maxnumrep` int NOT NULL default '0',

424

PRIMARY KEY (`numeropost`),

425

UNIQUE KEY `maxnumrep` (`maxnumrep`)

426

) ENGINE=MyISAM ROW_FORMAT=FIXED;

427

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

428

CREATE TABLE `t2` (

429

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

430

`topic` bigint NOT NULL default '0',

431

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

432

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

433

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

434

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

435

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

436

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

437

438

40143

439

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;

440

numeropost maxnumrep

441

43506 2

442

40143 1

443

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

444

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

445

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

446

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

447

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

448

mot topic date pseudo

449

joce 40143 2002-10-22 joce

450

joce 43506 2002-10-22 joce

451

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

452

mot topic date pseudo

453

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

454

mot topic date pseudo

455

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

456

mot topic date pseudo

457

joce 40143 2002-10-22 joce

458

joce 43506 2002-10-22 joce

459

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

460

mot topic date pseudo

461

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

462

mot topic date pseudo

463

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

464

mot topic date pseudo

465

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

466

mot topic date pseudo

467

joce 40143 2002-10-22 joce

468

joce 43506 2002-10-22 joce

469

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

470

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

471

joce 40143 2002-10-22 joce 1

472

joce 43506 2002-10-22 joce 1

473

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

474

mot topic date pseudo

475

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

476

mot topic date pseudo

477

joce 40143 2002-10-22 joce

478

joce 43506 2002-10-22 joce

479

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

480

mot topic date pseudo

481

joce 40143 2002-10-22 joce

482

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

483

mot topic date pseudo

484

joce 40143 2002-10-22 joce

485

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

486

mot topic date pseudo

487

joce 40143 2002-10-22 joce

488

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

489

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

490

joce 40143 2002-10-22 joce 1

491

joce 43506 2002-10-22 joce 0

492

drop table t1,t2;

493

CREATE TABLE `t1` (

494

`numeropost` bigint NOT NULL auto_increment,

495

`maxnumrep` int NOT NULL default '0',

496

PRIMARY KEY (`numeropost`),

497

UNIQUE KEY `maxnumrep` (`maxnumrep`)

498

) ENGINE=MyISAM ROW_FORMAT=FIXED;

499

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

500

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

501

ERROR 21000: Subquery returns more than 1 row

502

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

503

ERROR 21000: Subquery returns more than 1 row

504

drop table t1;

505

create table t1 (a int);

506

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

507

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

508

509

510

511

512

drop table t1;

513

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

514

INSERT INTO t1 VALUES ();

515

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

516

ERROR 21000: Subquery returns more than 1 row

517

drop table t1;

518

CREATE TABLE `t1` (

519

`numeropost` bigint NOT NULL default '0',

520

`numreponse` int NOT NULL auto_increment,

521

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

522

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

523

UNIQUE KEY `numreponse` (`numreponse`),

524

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

525

) ENGINE=MyISAM;

526

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

527

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

528

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

529

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

530

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

531

numreponse (SELECT numeropost FROM t1 HAVING numreponse=1)

532

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

533

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

534

ERROR 21000: Subquery returns more than 1 row

535

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

536

id select_type table type possible_keys key key_len ref rows filtered Extra

537

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

538

Warnings:

539

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

540

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

541

id select_type table type possible_keys key key_len ref rows filtered Extra

542

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

543

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

544

Warnings:

545

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

546

drop table t1;

547

CREATE TABLE t1 (a int);

548

INSERT INTO t1 VALUES (1);

549

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

550

551

552

drop table t1;

553

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

554

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

555

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

556

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

557

select * from t1;

558

a b

559

0 10

560

1 11

561

2 12

562

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

563

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

564

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

565

ERROR 21000: Subquery returns more than 1 row

566

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

567

select * from t1;

568

a b

569

0 NULL

570

1 21

571

2 22

572

drop table t1, t2;

573

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

574

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

575

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

576

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

577

select * from t1;

578

a b

579

0 10

580

1 11

581

2 12

582

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

583

a b

584

2 12

585

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

586

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

587

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

588

ERROR 21000: Subquery returns more than 1 row

589

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

590

select * from t1;

591

a b

592

0 10

593

1 11

594

drop table t1, t2;

595

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

596

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

597

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

598

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

599

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

600

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

601

select * from t11;

602

a b

603

0 10

604

1 11

605

2 12

606

select * from t12;

607

a b

608

2 12

609

22 11

610

33 10

611

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

612

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

613

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

614

ERROR 21000: Subquery returns more than 1 row

615

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

616

select * from t11;

617

a b

618

0 10

619

1 11

620

select * from t12;

621

a b

622

22 11

623

33 10

624

drop table t11, t12, t2;

625

CREATE TABLE t1 (x int);

626

create table t2 (a int);

627

create table t3 (b int);

628

insert into t2 values (1);

629

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

630

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

631

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

632

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

633

ERROR 21000: Subquery returns more than 1 row

634

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

635

select * from t1;

636

637

638

insert into t2 values (1);

639

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

640

select * from t1;

641

642

643

644

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

645

select * from t1;

646

647

648

649

650

651

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

652

select * from t1;

653

654

655

656

657

658

659

660

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

661

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

662

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

663

select * from t1;

664

665

666

667

668

669

670

671

672

drop table t1, t2, t3;

673

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

674

create table t2 (a int);

675

create table t3 (a int);

676

insert into t2 values (1);

677

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

678

select * from t1;

679

x y

680

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

681

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

682

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

683

ERROR 21000: Subquery returns more than 1 row

684

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

685

select * from t1;

686

x y

687

1 2

688

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

689

select * from t1;

690

x y

691

1 3

692

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

693

select * from t1;

694

x y

695

1 3

696

4 1

697

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

698

select * from t1;

699

x y

700

1 3

701

4 2

702

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

703

select * from t1;

704

x y

705

1 3

706

2 1

707

4 2

708

drop table t1, t2, t3;

709

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

710

ERROR HY000: No tables used

711

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

712

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

713

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

714

715

716

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

717

id select_type table type possible_keys key key_len ref rows filtered Extra

718

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

719

Warnings:

720

Note 1249 Select 2 was reduced during optimization

721

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

722

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

723

724

725

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

726

727

728

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

729

id select_type table type possible_keys key key_len ref rows filtered Extra

730

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

731

Warnings:

732

Note 1249 Select 3 was reduced during optimization

733

Note 1249 Select 2 was reduced during optimization

734

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

735

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

736

id select_type table type possible_keys key key_len ref rows filtered Extra

737

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

738

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

739

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

740

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

741

Warnings:

742

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

743

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

744

745

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

746

747

748

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

749

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

750

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

751

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

752

SELECT * FROM t2;

753

754

755

756

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

757

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

758

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

759

ERROR 21000: Subquery returns more than 1 row

760

drop table t2, t1;

761

create table t1 (a int);

762

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

763

select 1 IN (SELECT * from t1);

764

1 IN (SELECT * from t1)

765

766

select 10 IN (SELECT * from t1);

767

10 IN (SELECT * from t1)

768

769

select NULL IN (SELECT * from t1);

770

NULL IN (SELECT * from t1)

771

NULL

772

update t1 set a=NULL where a=2;

773

select 1 IN (SELECT * from t1);

774

1 IN (SELECT * from t1)

775

776

select 3 IN (SELECT * from t1);

777

3 IN (SELECT * from t1)

778

779

select 10 IN (SELECT * from t1);

780

10 IN (SELECT * from t1)

781

NULL

782

select 1 > ALL (SELECT * from t1);

783

1 > ALL (SELECT * from t1)

784

785

select 10 > ALL (SELECT * from t1);

786

10 > ALL (SELECT * from t1)

787

NULL

788

select 1 > ANY (SELECT * from t1);

789

1 > ANY (SELECT * from t1)

790

NULL

791

select 10 > ANY (SELECT * from t1);

792

10 > ANY (SELECT * from t1)

793

794

drop table t1;

795

create table t1 (a varchar(20));

796

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

797

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

798

'A' IN (SELECT * from t1)

799

800

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

801

'XYZS' IN (SELECT * from t1)

802

803

select NULL IN (SELECT * from t1);

804

NULL IN (SELECT * from t1)

805

NULL

806

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

807

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

808

'A' IN (SELECT * from t1)

809

810

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

811

'DEF' IN (SELECT * from t1)

812

813

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

814

'XYZS' IN (SELECT * from t1)

815

NULL

816

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

817

'A' > ALL (SELECT * from t1)

818

819

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

820

'XYZS' > ALL (SELECT * from t1)

821

NULL

822

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

823

'A' > ANY (SELECT * from t1)

824

NULL

825

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

826

'XYZS' > ANY (SELECT * from t1)

827

828

drop table t1;

829

create table t1 (a float);

830

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

831

select 1.5 IN (SELECT * from t1);

832

1.5 IN (SELECT * from t1)

833

834

select 10.5 IN (SELECT * from t1);

835

10.5 IN (SELECT * from t1)

836

837

select NULL IN (SELECT * from t1);

838

NULL IN (SELECT * from t1)

839

NULL

840

update t1 set a=NULL where a=2.5;

841

select 1.5 IN (SELECT * from t1);

842

1.5 IN (SELECT * from t1)

843

844

select 3.5 IN (SELECT * from t1);

845

3.5 IN (SELECT * from t1)

846

847

select 10.5 IN (SELECT * from t1);

848

10.5 IN (SELECT * from t1)

849

NULL

850

select 1.5 > ALL (SELECT * from t1);

851

1.5 > ALL (SELECT * from t1)

852

853

select 10.5 > ALL (SELECT * from t1);

854

10.5 > ALL (SELECT * from t1)

855

NULL

856

select 1.5 > ANY (SELECT * from t1);

857

1.5 > ANY (SELECT * from t1)

858

NULL

859

select 10.5 > ANY (SELECT * from t1);

860

10.5 > ANY (SELECT * from t1)

861

862

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

863

id select_type table type possible_keys key key_len ref rows filtered Extra

864

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

865

Warnings:

866

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

867

Note 1249 Select 2 was reduced during optimization

868

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

869

select (select a+1) from t1;

870

(select a+1)

871

2.5

872

4.5

873

NULL

874

drop table t1;

875

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

876

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

877

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

878

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

879

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

880

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

881

1 1

882

2 1

883

3 1

884

4 0

885

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

886

id select_type table type possible_keys key key_len ref rows filtered Extra

887

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

888

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

889

Warnings:

890

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`

891

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

892

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

893

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

894

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

895

1 1

896

2 1

897

3 1

898

4 0

899

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

900

id select_type table type possible_keys key key_len ref rows filtered Extra

901

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

902

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

903

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

904

Warnings:

905

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`

906

drop table t1,t2,t3;

907

CREATE TABLE t1 (a int);

908

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

909

id select_type table type possible_keys key key_len ref rows filtered Extra

910

1 PRIMARY t1 ALL NULL NULL NULL NULL 0 0.00

911

2 SUBQUERY t1 ALL NULL NULL NULL NULL 0 0.00

912

Warnings:

913

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

914

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

915

id select_type table type possible_keys key key_len ref rows filtered Extra

916

1 PRIMARY t1 ALL NULL NULL NULL NULL 0 0.00

917

2 SUBQUERY t1 ALL NULL NULL NULL NULL 0 0.00

918

Warnings:

919

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

920

drop table t1;

921

CREATE TABLE `t1` (

922

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

923

`topic` bigint NOT NULL default '0',

924

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

925

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

926

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

927

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

928

KEY `topic` (`topic`)

929

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

930

CREATE TABLE `t2` (

931

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

932

`topic` bigint NOT NULL default '0',

933

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

934

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

935

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

936

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

937

KEY `topic` (`topic`)

938

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

939

CREATE TABLE `t3` (

940

`numeropost` bigint NOT NULL auto_increment,

941

`maxnumrep` int NOT NULL default '0',

942

PRIMARY KEY (`numeropost`),

943

UNIQUE KEY `maxnumrep` (`maxnumrep`)

944

) ENGINE=MyISAM;

945

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

946

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

947

INSERT INTO t3 VALUES (1,1);

948

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

949

numeropost=topic);

950

topic

951

952

select * from t1;

953

mot topic date pseudo

954

joce 1 0000-00-00 joce

955

test 2 0000-00-00 test

956

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

957

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

958

select * from t1;

959

mot topic date pseudo

960

joce 1 0000-00-00 joce

961

drop table t1, t2, t3;

962

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

963

a (SELECT a)

964

1 1

965

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

966

SHOW CREATE TABLE t1;

967

Table Create Table

968

t1 CREATE TABLE `t1` (

969

`a` int NOT NULL,

970

`(SELECT 1)` int NOT NULL

971

) ENGINE=PBXT

972

drop table t1;

973

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

974

SHOW CREATE TABLE t1;

975

Table Create Table

976

t1 CREATE TABLE `t1` (

977

`a` int NOT NULL,

978

`(SELECT a)` int NOT NULL

979

) ENGINE=PBXT

980

drop table t1;

981

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

982

SHOW CREATE TABLE t1;

983

Table Create Table

984

t1 CREATE TABLE `t1` (

985

`a` int NOT NULL,

986

`(SELECT a+0)` int NOT NULL

987

) ENGINE=PBXT

988

drop table t1;

989

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

990

select * from t1;

991

992

993

SHOW CREATE TABLE t1;

994

Table Create Table

995

t1 CREATE TABLE `t1` (

996

`a` bigint NOT NULL

997

) ENGINE=PBXT

998

drop table t1;

999

create table t1 (a int);

1000

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

1001

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

1002

from t1;

1003

id select_type table type possible_keys key key_len ref rows filtered Extra

1004

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

1005

2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1006

3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1007

Warnings:

1008

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`

1009

drop table t1;

1010

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

1011

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

1012

CREATE TABLE t1 (

1013

ID int NOT NULL auto_increment,

1014

name char(35) NOT NULL default '',

1015

t2 char(3) NOT NULL default '',

1016

District char(20) NOT NULL default '',

1017

Population int NOT NULL default '0',

1018

PRIMARY KEY (ID)

1019

) ENGINE=MyISAM;

1020

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

1021

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

1022

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

1023

CREATE TABLE t2 (

1024

Code char(3) NOT NULL default '',

1025

Name char(52) NOT NULL default '',

1026

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

1027

Region char(26) NOT NULL default '',

1028

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

1029

IndepYear int default NULL,

1030

Population int NOT NULL default '0',

1031

LifeExpectancy float(3,1) default NULL,

1032

GNP float(10,2) default NULL,

1033

GNPOld float(10,2) default NULL,

1034

LocalName char(45) NOT NULL default '',

1035

GovernmentForm char(45) NOT NULL default '',

1036

HeadOfState char(60) default NULL,

1037

Capital int default NULL,

1038

Code2 char(2) NOT NULL default '',

1039

PRIMARY KEY (Code)

1040

) ENGINE=MyISAM;

1041

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

1042

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

1043

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

1044

Continent Name Population

1045

Oceania Sydney 3276207

1046

drop table t1, t2;

1047

CREATE TABLE `t1` (

1048

`id` bigint NOT NULL auto_increment,

1049

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

1050

PRIMARY KEY (`id`),

1051

UNIQUE KEY `pseudo` (`pseudo`)

1052

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

1053

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

1054

SELECT 0 IN (SELECT 1 FROM t1 a);

1055

0 IN (SELECT 1 FROM t1 a)

1056

1057

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

1058

id select_type table type possible_keys key key_len ref rows filtered Extra

1059

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

1060

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

1061

Warnings:

1062

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

1063

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

1064

SELECT 0 IN (SELECT 1 FROM t1 a);

1065

0 IN (SELECT 1 FROM t1 a)

1066

1067

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

1068

id select_type table type possible_keys key key_len ref rows filtered Extra

1069

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

1070

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

1071

Warnings:

1072

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

1073

drop table t1;

1074

CREATE TABLE `t1` (

1075

`i` int NOT NULL default '0',

1076

PRIMARY KEY (`i`)

1077

) ENGINE=MyISAM;

1078

INSERT INTO t1 VALUES (1);

1079

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

1080

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

1081

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

1082

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

1083

select * from t1;

1084

1085

1086

drop table t1;

1087

CREATE TABLE t1 (

1088

id int default NULL

1089

) ENGINE=MyISAM;

1090

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

1091

CREATE TABLE t2 (

1092

id int default NULL,

1093

name varchar(15) default NULL

1094

) ENGINE=MyISAM;

1095

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

1096

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

1097

select * from t2;

1098

id name

1099

4 vita

1100

1 lenka

1101

2 lenka

1102

1 lenka

1103

drop table t1,t2;

1104

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

1105

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

1106

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

1107

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

1108

NULL

1109

drop table t1;

1110

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

1111

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

1112

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

1113

id select_type table type possible_keys key key_len ref rows filtered Extra

1114

1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where

1115

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

1116

Warnings:

1117

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

1118

drop table t1;

1119

CREATE TABLE t1 (

1120

ID int NOT NULL auto_increment,

1121

SUB_ID int NOT NULL default '0',

1122

REF_ID int default NULL,

1123

REF_SUB int default '0',

1124

PRIMARY KEY (ID,SUB_ID),

1125

UNIQUE KEY t1_PK (ID,SUB_ID),

1126

KEY t1_FK (REF_ID,REF_SUB),

1127

KEY t1_REFID (REF_ID)

1128

) ENGINE=MyISAM;

1129

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

1130

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

1131

REF_ID

1132

DROP TABLE t1;

1133

create table t1 (a int, b int);

1134

create table t2 (a int, b int);

1135

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

1136

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

1137

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

1138

Warnings:

1139

Error 1242 Subquery returns more than 1 row

1140

select * from t1;

1141

a b

1142

1 1

1143

2 NULL

1144

3 1

1145

drop table t1, t2;

1146

CREATE TABLE `t1` (

1147

`id` bigint NOT NULL auto_increment,

1148

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

1149

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

1150

PRIMARY KEY (`id`),

1151

UNIQUE KEY `email` (`email`),

1152

UNIQUE KEY `pseudo` (`pseudo`)

1153

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

1154

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

1155

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

1156

a b

1157

test test

1158

test1 test1

1159

drop table if exists t1;

1160

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

1161

1162

1163

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

1164

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

1165

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

1166

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

1167

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

1168

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

1169

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

1170

1171

1172

1173

1174

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

1175

id select_type table type possible_keys key key_len ref rows filtered Extra

1176

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

1177

1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer

1178

Warnings:

1179

Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)

1180

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

1181

1182

1183

1184

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

1185

id select_type table type possible_keys key key_len ref rows filtered Extra

1186

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

1187

1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer

1188

Warnings:

1189

Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))

1190

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

1191

1192

1193

1194

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

1195

id select_type table type possible_keys key key_len ref rows filtered Extra

1196

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

1197

1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer

1198

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

1199

Warnings:

1200

Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1` join `test`.`t3`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))

1201

drop table t1, t2, t3;

1202

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

1203

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

1204

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

1205

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

1206

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

1207

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

1208

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

1209

1210

1211

1212

1213

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

1214

id select_type table type possible_keys key key_len ref rows filtered Extra

1215

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

1216

1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)

1217

Warnings:

1218

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)

1219

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

1220

1221

1222

1223

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

1224

id select_type table type possible_keys key key_len ref rows filtered Extra

1225

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

1226

1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)

1227

Warnings:

1228

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))

1229

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

1230

1231

1232

1233

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

1234

id select_type table type possible_keys key key_len ref rows filtered Extra

1235

1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index; Start temporary

1236

1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)

1237

1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary; Using join buffer

1238

Warnings:

1239

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))

1240

insert into t1 values (3,31);

1241

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

1242

1243

1244

1245

1246

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

1247

1248

1249

1250

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

1251

id select_type table type possible_keys key key_len ref rows filtered Extra

1252

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

1253

1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)

1254

Warnings:

1255

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))

1256

drop table t1, t2, t3;

1257

create table t1 (a int, b int);

1258

create table t2 (a int, b int);

1259

create table t3 (a int, b int);

1260

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

1261

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

1262

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

1263

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;

1264

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)

1265

3 1

1266

2 2

1267

1 2

1268

drop table t1,t2,t3;

1269

create table t1 (s1 int);

1270

create table t2 (s1 int);

1271

insert into t1 values (1);

1272

insert into t2 values (1);

1273

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

1274

1275

1276

drop table t1,t2;

1277

create table t1 (s1 int);

1278

create table t2 (s1 int);

1279

insert into t1 values (1);

1280

insert into t2 values (1);

1281

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

1282

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

1283

DROP TABLE t1, t2;

1284

create table t1(toid int,rd int);

1285

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

1286

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

1287

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

1288

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

1289

userid pmtotal pmnew calc_total calc_new

1290

1 0 0 9 3

1291

2 0 0 4 2

1292

drop table t1, t2;

1293

create table t1 (s1 char(5));

1294

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

1295

ERROR 21000: Operand should contain 1 column(s)

1296

insert into t1 values ('tttt');

1297

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

1298

1299

tttt

1300

explain extended (select * from t1);

1301

id select_type table type possible_keys key key_len ref rows filtered Extra

1302

1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00

1303

Warnings:

1304

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

1305

(select * from t1);

1306

1307

tttt

1308

drop table t1;

1309

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

1310

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

1311

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

1312

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

1313

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

1314

s1 s1 NOT IN (SELECT s1 FROM t2)

1315

a1 0

1316

a2 0

1317

a3 1

1318

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

1319

s1 s1 = ANY (SELECT s1 FROM t2)

1320

a1 1

1321

a2 1

1322

a3 0

1323

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

1324

s1 s1 <> ALL (SELECT s1 FROM t2)

1325

a1 0

1326

a2 0

1327

a3 1

1328

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

1329

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

1330

a1 0

1331

a2 1

1332

a3 1

1333

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

1334

id select_type table type possible_keys key key_len ref rows filtered Extra

1335

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

1336

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

1337

Warnings:

1338

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`

1339

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

1340

id select_type table type possible_keys key key_len ref rows filtered Extra

1341

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

1342

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

1343

Warnings:

1344

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`

1345

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

1346

id select_type table type possible_keys key key_len ref rows filtered Extra

1347

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

1348

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

1349

Warnings:

1350

1351

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

1352

id select_type table type possible_keys key key_len ref rows filtered Extra

1353

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

1354

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

1355

Warnings:

1356

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`

1357

drop table t1,t2;

1358

create table t2 (a int, b int);

1359

create table t3 (a int);

1360

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

1361

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

1362

1363

1364

1365

1366

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

1367

id select_type table type possible_keys key key_len ref rows filtered Extra

1368

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

1369

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00

1370

Warnings:

1371

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

1372

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

1373

1374

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

1375

id select_type table type possible_keys key key_len ref rows filtered Extra

1376

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

1377

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00

1378

Warnings:

1379

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

1380

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

1381

1382

1383

1384

1385

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

1386

id select_type table type possible_keys key key_len ref rows filtered Extra

1387

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

1388

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary; Using filesort

1389

Warnings:

1390

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

1391

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

1392

1393

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

1394

id select_type table type possible_keys key key_len ref rows filtered Extra

1395

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

1396

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary; Using filesort

1397

Warnings:

1398

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

1399

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

1400

1401

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

1402

id select_type table type possible_keys key key_len ref rows filtered Extra

1403

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

1404

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00

1405

Warnings:

1406

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

1407

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

1408

1409

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

1410

id select_type table type possible_keys key key_len ref rows filtered Extra

1411

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

1412

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary; Using filesort

1413

Warnings:

1414

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

1415

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

1416

1417

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

1418

id select_type table type possible_keys key key_len ref rows filtered Extra

1419

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

1420

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00

1421

Warnings:

1422

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

1423

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

1424

1425

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

1426

id select_type table type possible_keys key key_len ref rows filtered Extra

1427

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

1428

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary; Using filesort

1429

Warnings:

1430

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

1431

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

1432

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

1433

1434

1435

1436

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

1437

id select_type table type possible_keys key key_len ref rows filtered Extra

1438

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

1439

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

1440

Warnings:

1441

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

1442

drop table t2, t3;

1443

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

1444

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

1445

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

1446

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

1447

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

1448

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

1449

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

1450

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

1451

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;

1452

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

1453

-1 Valid 1

1454

-1 Valid 2 1

1455

-1 Should Not Return 0

1456

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;

1457

dbid name

1458

-1 Valid

1459

-1 Valid 2

1460

drop table t1,t2,t3,t4;

1461

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

1462

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

1463

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

1464

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

1465

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

1466

ERROR 21000: Operand should contain 3 column(s)

1467

DROP TABLE t1,t2;

1468

create table t1 (s1 char);

1469

insert into t1 values ('e');

1470

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

1471

1472

1473

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

1474

1475

1476

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

1477

id select_type table type possible_keys key key_len ref rows filtered Extra

1478

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

1479

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

1480

3 UNION t1 ALL NULL NULL NULL NULL 1 100.00

1481

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

1482

Warnings:

1483

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

1484

drop table t1;

1485

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

1486

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

1487

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

1488

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

1489

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;

1490

phone code

1491

69294728265 6

1492

18621828126 1862

1493

89356874041 NULL

1494

95895001874 NULL

1495

drop table t1, t2;

1496

create table t1 (s1 int);

1497

create table t2 (s1 int);

1498

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

1499

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

1500

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

1501

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

1502

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

1503

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

1504

drop table t1, t2;

1505

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

1506

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

1507

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

1508

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

1509

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

1510

COLC

1511

DROP TABLE t1, t2;

1512

CREATE TABLE t1 (a int);

1513

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

1514

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

1515

(SELECT a)

1516

1517

1518

1519

1520

1521

DROP TABLE t1;

1522

CREATE TABLE `t1` (

1523

`id` int NOT NULL auto_increment,

1524

`id_cns` int NOT NULL default '0',

1525

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

1526

`anno_dep` int NOT NULL default '0',

1527

`particolare` bigint NOT NULL default '0',

1528

`generale` bigint NOT NULL default '0',

1529

`bis` int NOT NULL default '0',

1530

PRIMARY KEY (`id`),

1531

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

1532

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

1533

);

1534

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

1535

CREATE TABLE `t2` (

1536

`id` int NOT NULL auto_increment,

1537

`max_anno_dep` int NOT NULL default '0',

1538

PRIMARY KEY (`id`)

1539

);

1540

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

1541

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;

1542

id max_anno_dep PIPPO

1543

16 1987 1

1544

50 1990 0

1545

51 1990 NULL

1546

DROP TABLE t1, t2;

1547

create table t1 (a int);

1548

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

1549

SET SQL_SELECT_LIMIT=1;

1550

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

1551

sum(a)

1552

1553

select 2 in (select * from t1);

1554

2 in (select * from t1)

1555

1556

SET SQL_SELECT_LIMIT=default;

1557

drop table t1;

1558

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

1559

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

1560

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

1561

a b

1562

1 1

1563

1 2

1564

1 3

1565

DROP TABLE t1;

1566

create table t1(val varchar(10));

1567

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

1568

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

1569

count(*)

1570

1571

drop table t1;

1572

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

1573

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

1574

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

1575

id text

1576

8 text8

1577

9 text9

1578

10 text10

1579

11 text11

1580

12 text12

1581

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

1582

id text

1583

8 text8

1584

9 text9

1585

10 text10

1586

11 text11

1587

12 text12

1588

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

1589

id select_type table type possible_keys key key_len ref rows filtered Extra

1590

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

1591

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

1592

Warnings:

1593

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

1594

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

1595

id select_type table type possible_keys key key_len ref rows filtered Extra

1596

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

1597

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

1598

Warnings:

1599

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

1600

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

1601

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

1602

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

1603

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

1604

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

1605

id text id text id text

1606

1 text1 1 text1 1 text1

1607

2 text2 2 text2 2 text2

1608

3 text3 3 text3 3 text3

1609

4 text4 4 text4 4 text4

1610

5 text5 5 text5 5 text5

1611

6 text6 6 text6 6 text6

1612

7 text7 7 text7 7 text7

1613

8 text8 8 text8 8 text8

1614

9 text9 9 text9 9 text9

1615

10 text10 10 text10 10 text10

1616

11 text11 11 text1 11 text11

1617

12 text12 12 text2 12 text12

1618

1000 text1000 NULL NULL 1000 text1000

1619

1001 text1001 NULL NULL 1000 text1000

1620

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

1621

id select_type table type possible_keys key key_len ref rows filtered Extra

1622

1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00

1623

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

1624

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

1625

Warnings:

1626

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

1627

drop table t1,t2;

1628

create table t1 (a int);

1629

insert into t1 values (1);

1630

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

1631

id select_type table type possible_keys key key_len ref rows Extra

1632

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

1633

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where

1634

drop table t1;

1635

create table t1(id int);

1636

create table t2(id int);

1637

create table t3(flag int);

1638

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

1639

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

1640

drop table t1,t2,t3;

1641

CREATE TABLE t1 (id INT);

1642

CREATE TABLE t2 (id INT);

1643

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

1644

INSERT INTO t2 VALUES (1);

1645

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

1646

id c

1647

1 1

1648

2 0

1649

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

1650

id c

1651

1 1

1652

2 0

1653

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;

1654

id c

1655

1 1

1656

2 0

1657

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;

1658

id c

1659

1 1

1660

2 0

1661

DROP TABLE t1,t2;

1662

CREATE TABLE t1 ( a int, b int );

1663

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

1664

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

1665

1666

1667

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

1668

1669

1670

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

1671

1672

1673

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

1674

1675

1676

1677

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

1678

1679

1680

1681

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

1682

1683

1684

1685

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

1686

1687

1688

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

1689

1690

1691

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

1692

1693

1694

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

1695

1696

1697

1698

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

1699

1700

1701

1702

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

1703

1704

1705

1706

ALTER TABLE t1 ADD INDEX (a);

1707

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

1708

1709

1710

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

1711

1712

1713

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

1714

1715

1716

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

1717

1718

1719

1720

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

1721

1722

1723

1724

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

1725

1726

1727

1728

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

1729

1730

1731

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

1732

1733

1734

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

1735

1736

1737

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

1738

1739

1740

1741

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

1742

1743

1744

1745

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

1746

1747

1748

1749

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

1750

1751

1752

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

1753

1754

1755

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

1756

1757

1758

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

1759

1760

1761

1762

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

1763

1764

1765

1766

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

1767

1768

1769

1770

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

1771

1772

1773

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

1774

1775

1776

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

1777

1778

1779

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

1780

1781

1782

1783

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

1784

1785

1786

1787

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

1788

1789

1790

1791

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

1792

1793

1794

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

1795

1796

1797

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

1798

1799

1800

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

1801

1802

1803

1804

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

1805

1806

1807

1808

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

1809

1810

1811

1812

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

1813

1814

1815

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

1816

1817

1818

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

1819

1820

1821

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

1822

1823

1824

1825

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

1826

1827

1828

1829

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

1830

1831

1832

1833

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

1834

1835

1836

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

1837

1838

1839

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

1840

1841

1842

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

1843

1844

1845

1846

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

1847

1848

1849

1850

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

1851

1852

1853

1854

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

1855

1856

1857

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

1858

1859

1860

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

1861

1862

1863

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

1864

1865

1866

1867

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

1868

1869

1870

1871

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

1872

1873

1874

1875

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

1876

ERROR 21000: Operand should contain 1 column(s)

1877

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

1878

ERROR 21000: Operand should contain 1 column(s)

1879

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

1880

ERROR 21000: Operand should contain 1 column(s)

1881

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

1882

ERROR 21000: Operand should contain 1 column(s)

1883

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

1884

ERROR 21000: Operand should contain 1 column(s)

1885

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

1886

ERROR 21000: Operand should contain 1 column(s)

1887

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

1888

ERROR 21000: Operand should contain 1 column(s)

1889

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

1890

ERROR 21000: Operand should contain 1 column(s)

1891

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

1892

ERROR 21000: Operand should contain 2 column(s)

1893

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

1894

ERROR 21000: Operand should contain 1 column(s)

1895

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

1896

1897

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

1898

ERROR 21000: Operand should contain 2 column(s)

1899

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

1900

ERROR 21000: Operand should contain 1 column(s)

1901

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

1902

1903

1904

1905

1906

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

1907

1908

1909

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

1910

1911

1912

1913

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

1914

1915

1916

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

1917

1918

1919

1920

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

1921

1922

1923

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

1924

1925

1926

1927

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

1928

1929

1930

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

1931

1932

1933

1934

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

1935

1936

1937

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

1938

1939

1940

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

1941

1942

1943

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

1944

1945

1946

1947

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

1948

1949

1950

1951

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

1952

1953

1954

1955

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

1956

1957

1958

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

1959

1960

1961

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

1962

1963

1964

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

1965

1966

1967

1968

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

1969

1970

1971

1972

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

1973

1974

1975

1976

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

1977

1978

1979

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

1980

1981

1982

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

1983

1984

1985

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

1986

1987

1988

1989

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

1990

1991

1992

1993

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

1994

1995

1996

1997

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

1998

1999

2000

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

2001

2002

2003

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

2004

2005

2006

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

2007

2008

2009

2010

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

2011

2012

2013

2014

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

2015

2016

2017

2018

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

2019

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

2020

2021

2022

2023

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

2024

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

2025

2026

2027

2028

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

2029

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

2030

2031

2032

2033

DROP TABLE t1;

2034

CREATE TABLE t1 ( a double, b double );

2035

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

2036

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

2037

2038

2039

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

2040

2041

2042

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

2043

2044

2045

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

2046

2047

2048

2049

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

2050

2051

2052

2053

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

2054

2055

2056

2057

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

2058

2059

2060

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

2061

2062

2063

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

2064

2065

2066

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

2067

2068

2069

2070

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

2071

2072

2073

2074

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

2075

2076

2077

2078

DROP TABLE t1;

2079

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

2080

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

2081

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

2082

2083

2084

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

2085

2086

2087

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

2088

2089

2090

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

2091

2092

2093

2094

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

2095

2096

2097

2098

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

2099

2100

2101

2102

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

2103

2104

2105

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

2106

2107

2108

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

2109

2110

2111

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

2112

2113

2114

2115

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

2116

2117

2118

2119

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

2120

2121

2122

2123

DROP TABLE t1;

2124

create table t1 (a int, b int);

2125

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

2126

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

2127

a b

2128

1 2

2129

3 4

2130

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

2131

id select_type table type possible_keys key key_len ref rows filtered Extra

2132

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

2133

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

2134

Warnings:

2135

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

2136

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

2137

drop table t1;

2138

CREATE TABLE t1 (t1_a int);

2139

INSERT INTO t1 VALUES (1);

2140

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

2141

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

2142

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

2143

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

2144

t1_a t2_a t2_b

2145

1 1 2

2146

DROP TABLE t1, t2;

2147

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

2148

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

2149

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

2150

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

2151

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

2152

id name id pet

2153

1 Tim 1 Fido

2154

2 Rebecca 2 Spot

2155

3 NULL 3 Felix

2156

drop table t1,t2;

2157

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

2158

insert into t1 values (1);

2159

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

2160

insert into t2 values (1,2);

2161

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

2162

a C

2163

1 1

2164

drop table t1,t2;

2165

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

2166

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

2167

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

2168

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

2169

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

2170

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

2171

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

2172

IZAVORGANG_ID

2173

D0000000001

2174

drop table t1;

2175

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

2176

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

2177

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

2178

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

2179

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

2180

aid bid

2181

1 1

2182

2 1

2183

alter table t2 drop primary key;

2184

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

2185

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

2186

aid bid

2187

1 1

2188

2 1

2189

alter table t2 drop key KEY1;

2190

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

2191

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

2192

aid bid

2193

1 1

2194

2 1

2195

drop table t1,t2;

2196

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

2197

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

2198

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

2199

howmanyvalues count(*)

2200

1 1

2201

2 2

2202

3 3

2203

4 4

2204

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

2205

howmanyvalues mycount

2206

1 1

2207

2 2

2208

3 3

2209

4 4

2210

CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);

2211

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

2212

howmanyvalues mycount

2213

1 1

2214

2 2

2215

3 3

2216

4 4

2217

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

2218

howmanyvalues mycount

2219

1 1

2220

2 2

2221

3 3

2222

4 4

2223

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

2224

howmanyvalues mycount

2225

1 1

2226

2 1

2227

3 1

2228

4 1

2229

drop table t1;

2230

create table t1 (x int);

2231

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;

2232

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

2233

drop table t1;

2234

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

2235

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

2236

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

2237

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

2238

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;

2239

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

2240

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;

2241

2242

700

2243

NULL

2244

drop tables t1,t2;

2245

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

2246

create table t1 (a int, b int);

2247

create table t2 (a int, b int);

2248

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

2249

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

2250

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

2251

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

2252

1 1 3

2253

1 2 1

2254

1 3 NULL

2255

2 4 NULL

2256

2 5 NULL

2257

drop table t1, t2;

2258

create table t1 (s1 int,s2 int);

2259

insert into t1 values (20,15);

2260

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

2261

s1 s2

2262

drop table t1;

2263

create table t1 (s1 int);

2264

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

2265

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

2266

2267

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

2268

s1 s1 < all (select s1 from t1)

2269

1 0

2270

NULL NULL

2271

drop table t1;

2272

CREATE TABLE t1 (

2273

Code char(3) NOT NULL default '',

2274

Name char(52) NOT NULL default '',

2275

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

2276

Region char(26) NOT NULL default '',

2277

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

2278

IndepYear int default NULL,

2279

Population int NOT NULL default '0',

2280

LifeExpectancy float(3,1) default NULL,

2281

GNP float(10,2) default NULL,

2282

GNPOld float(10,2) default NULL,

2283

LocalName char(45) NOT NULL default '',

2284

GovernmentForm char(45) NOT NULL default '',

2285

HeadOfState char(60) default NULL,

2286

Capital int default NULL,

2287

Code2 char(2) NOT NULL default ''

2288

) ENGINE=MyISAM;

2289

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

2290

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

2291

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

2292

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

2293

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

2294

SELECT DISTINCT Continent AS c FROM t1 outr WHERE

2295

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

2296

Population < 200);

2297

2298

Oceania

2299

drop table t1;

2300

create table t1 (a1 int);

2301

create table t2 (b1 int);

2302

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

2303

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

2304

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

2305

2306

drop table t1,t2;

2307

create table t1 (a integer, b integer);

2308

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

2309

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

2310

NULL

2311

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

2312

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

2313

NULL

2314

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

2315

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

2316

2317

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

2318

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

2319

2320

drop table t1;

2321

create table t1 (a integer, b integer);

2322

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

2323

ERROR 21000: Operand should contain 2 column(s)

2324

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

2325

ERROR 21000: Operand should contain 2 column(s)

2326

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

2327

ERROR 21000: Operand should contain 1 column(s)

2328

drop table t1;

2329

create table t1 (a integer);

2330

insert into t1 values (1);

2331

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

2332

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

2333

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

2334

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

2335

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

2336

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

2337

1 1

2338

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

2339

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

2340

drop table t1;

2341

CREATE TABLE t1 (

2342

categoryId int NOT NULL,

2343

courseId int NOT NULL,

2344

startDate datetime NOT NULL,

2345

endDate datetime NOT NULL,

2346

createDate datetime NOT NULL,

2347

modifyDate timestamp NOT NULL,

2348

attributes text NOT NULL

2349

);

2350

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

2351

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

2352

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

2353

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

2354

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

2355

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

2356

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

2357

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

2358

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

2359

CREATE TABLE t2 (

2360

userId int NOT NULL,

2361

courseId int NOT NULL,

2362

date datetime NOT NULL

2363

);

2364

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

2365

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

2366

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

2367

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

2368

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

2369

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

2370

CREATE TABLE t3 (

2371

groupId int NOT NULL,

2372

parentId int NOT NULL,

2373

startDate datetime NOT NULL,

2374

endDate datetime NOT NULL,

2375

createDate datetime NOT NULL,

2376

modifyDate timestamp NOT NULL,

2377

ordering int

2378

);

2379

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

2380

CREATE TABLE t4 (

2381

id int NOT NULL,

2382

groupTypeId int NOT NULL,

2383

groupKey varchar(50) NOT NULL,

2384

name text,

2385

ordering int,

2386

description text,

2387

createDate datetime NOT NULL,

2388

modifyDate timestamp NOT NULL

2389

);

2390

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

2391

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

2392

CREATE TABLE t5 (

2393

userId int NOT NULL,

2394

groupId int NOT NULL,

2395

createDate datetime NOT NULL,

2396

modifyDate timestamp NOT NULL

2397

);

2398

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

2399

select

2400

count(distinct t2.userid) pass,

2401

groupstuff.*,

2402

count(t2.courseid) crse,

2403

t1.categoryid,

2404

t2.courseid,

2405

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

2406

from t2

2407

join t1 on t2.courseid=t1.courseid

2408

join

2409

(

2410

select

2411

t5.userid,

2412

parentid,

2413

parentgroup,

2414

childid,

2415

groupname,

2416

grouptypeid

2417

from t5

2418

join

2419

(

2420

select t4.id as parentid,

2421

t4.name as parentgroup,

2422

t4.id as childid,

2423

t4.name as groupname,

2424

t4.grouptypeid

2425

from t4

2426

) as gin on t5.groupid=gin.childid

2427

) as groupstuff on t2.userid = groupstuff.userid

2428

group by

2429

groupstuff.groupname, colhead , t2.courseid;

2430

pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead

2431

1 5141 12 group2 12 group2 5 1 5 12 Aug04

2432

1 5141 12 group2 12 group2 5 1 1 41 Aug04

2433

1 5141 12 group2 12 group2 5 1 2 52 Aug04

2434

1 5141 12 group2 12 group2 5 1 2 53 Aug04

2435

1 5141 12 group2 12 group2 5 1 3 51 Oct04

2436

1 5141 12 group2 12 group2 5 1 1 86 Oct04

2437

1 5141 12 group2 12 group2 5 1 1 87 Oct04

2438

1 5141 12 group2 12 group2 5 1 2 88 Oct04

2439

1 5141 12 group2 12 group2 5 1 2 89 Oct04

2440

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

2441

create table t1 (a int);

2442

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

2443

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

2444

2445

2446

2447

2448

drop table t1;

2449

create table t1 (a int, b int);

2450

insert into t1 values (1,2);

2451

select 1 = (select * from t1);

2452

ERROR 21000: Operand should contain 1 column(s)

2453

select (select * from t1) = 1;

2454

ERROR 21000: Operand should contain 2 column(s)

2455

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

2456

ERROR 21000: Operand should contain 2 column(s)

2457

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

2458

ERROR 21000: Operand should contain 1 column(s)

2459

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

2460

ERROR 21000: Operand should contain 3 column(s)

2461

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

2462

ERROR 21000: Operand should contain 2 column(s)

2463

drop table t1;

2464

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

2465

insert into t1 values ('1');

2466

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

2467

max(fld)

2468

2469

drop table t1;

2470

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

2471

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

2472

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

2473

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

2474

SELECT * FROM t1

2475

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

2476

one two flag

2477

5 6 N

2478

7 8 N

2479

SELECT * FROM t1

2480

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

2481

one two flag

2482

5 6 N

2483

7 8 N

2484

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

2485

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

2486

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

2487

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

2488

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

2489

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

2490

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

2491

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

2492

one two test

2493

1 2 NULL

2494

2 3 NULL

2495

3 4 NULL

2496

5 6 1

2497

7 8 1

2498

10 3 NULL

2499

10 5 NULL

2500

10 10 NULL

2501

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

2502

one two

2503

5 6

2504

7 8

2505

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

2506

one two test

2507

1 2 NULL

2508

2 3 NULL

2509

3 4 NULL

2510

5 6 1

2511

7 8 1

2512

10 3 NULL

2513

10 5 NULL

2514

10 10 NULL

2515

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

2516

one two test

2517

1 2 0

2518

2 3 NULL

2519

3 4 0

2520

5 6 0

2521

7 8 0

2522

10 3 NULL

2523

10 5 NULL

2524

10 10 NULL

2525

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;

2526

one two test

2527

1 2 0

2528

2 3 NULL

2529

3 4 0

2530

5 6 0

2531

7 8 0

2532

10 3 NULL

2533

10 5 NULL

2534

10 10 NULL

2535

explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;

2536

id select_type table type possible_keys key key_len ref rows filtered Extra

2537

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2538

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where

2539

Warnings:

2540

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`

2541

explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');

2542

id select_type table type possible_keys key key_len ref rows filtered Extra

2543

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary

2544

1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer

2545

Warnings:

2546

Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))

2547

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;

2548

id select_type table type possible_keys key key_len ref rows filtered Extra

2549

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2550

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort

2551

Warnings:

2552

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`

2553

DROP TABLE t1,t2;

2554

CREATE TABLE t1 (a char(5), b char(5));

2555

INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');

2556

SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));

2557

a b

2558

aaa aaa

2559

DROP TABLE t1;

2560

CREATE TABLE t1 (a int);

2561

CREATE TABLE t2 (a int, b int);

2562

CREATE TABLE t3 (b int NOT NULL);

2563

INSERT INTO t1 VALUES (1), (2), (3), (4);

2564

INSERT INTO t2 VALUES (1,10), (3,30);

2565

SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2566

WHERE t3.b IS NOT NULL OR t2.a > 10;

2567

a b b

2568

SELECT * FROM t1

2569

WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2570

WHERE t3.b IS NOT NULL OR t2.a > 10);

2571

2572

2573

2574

2575

2576

DROP TABLE t1,t2,t3;

2577

CREATE TABLE t1 (f1 INT);

2578

CREATE TABLE t2 (f2 INT);

2579

INSERT INTO t1 VALUES (1);

2580

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);

2581

2582

2583

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);

2584

2585

2586

INSERT INTO t2 VALUES (1);

2587

INSERT INTO t2 VALUES (2);

2588

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);

2589

2590

2591

DROP TABLE t1, t2;

2592

create table t1 (s1 char);

2593

insert into t1 values (1),(2);

2594

select * from t1 where (s1 < any (select s1 from t1));

2595

2596

2597

select * from t1 where not (s1 < any (select s1 from t1));

2598

2599

2600

select * from t1 where (s1 < ALL (select s1+1 from t1));

2601

2602

2603

select * from t1 where not(s1 < ALL (select s1+1 from t1));

2604

2605

2606

select * from t1 where (s1+1 = ANY (select s1 from t1));

2607

2608

2609

select * from t1 where NOT(s1+1 = ANY (select s1 from t1));

2610

2611

2612

select * from t1 where (s1 = ALL (select s1/s1 from t1));

2613

2614

2615

select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));

2616

2617

2618

drop table t1;

2619

create table t1 (

2620

retailerID varchar(8) NOT NULL,

2621

statusID int NOT NULL,

2622

changed datetime NOT NULL,

2623

UNIQUE KEY retailerID (retailerID, statusID, changed)

2624

);

2625

INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");

2626

INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");

2627

INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");

2628

INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");

2629

INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");

2630

INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");

2631

select * from t1 r1

2632

where (r1.retailerID,(r1.changed)) in

2633

(SELECT r2.retailerId,(max(changed)) from t1 r2

2634

group by r2.retailerId);

2635

retailerID statusID changed

2636

0026 2 2006-01-06 12:25:53

2637

0037 2 2006-01-06 12:25:53

2638

0048 1 2006-01-06 12:37:50

2639

0059 1 2006-01-06 12:37:50

2640

drop table t1;

2641

create table t1(a int, primary key (a));

2642

insert into t1 values (10);

2643

create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));

2644

insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');

2645

explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2646

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2647

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

2648

id select_type table type possible_keys key key_len ref rows Extra

2649

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2650

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2651

2 DEPENDENT SUBQUERY t2 range b b 136 NULL 1 Using where

2652

SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2653

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2654

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

2655

a a b

2656

10 3 35989

2657

explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2658

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2659

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2660

id select_type table type possible_keys key key_len ref rows Extra

2661

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2662

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2663

2 DEPENDENT SUBQUERY t2 range b b 136 NULL 1 Using where

2664

SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2665

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2666

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2667

a a b

2668

10 1 359

2669

drop table t1,t2;

2670

CREATE TABLE t1 (

2671

field1 int NOT NULL,

2672

field2 int NOT NULL,

2673

field3 int NOT NULL,

2674

PRIMARY KEY (field1,field2,field3)

2675

);

2676

CREATE TABLE t2 (

2677

fieldA int NOT NULL,

2678

fieldB int NOT NULL,

2679

PRIMARY KEY (fieldA,fieldB)

2680

);

2681

INSERT INTO t1 VALUES

2682

(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);

2683

INSERT INTO t2 VALUES (1,1), (1,2), (1,3);

2684

SELECT field1, field2, COUNT(*)

2685

FROM t1 GROUP BY field1, field2;

2686

field1 field2 COUNT(*)

2687

1 1 2

2688

1 2 3

2689

1 3 1

2690

SELECT field1, field2

2691

FROM t1

2692

GROUP BY field1, field2

2693

HAVING COUNT(*) >= ALL (SELECT fieldB

2694

FROM t2 WHERE fieldA = field1);

2695

field1 field2

2696

1 2

2697

SELECT field1, field2

2698

FROM t1

2699

GROUP BY field1, field2

2700

HAVING COUNT(*) < ANY (SELECT fieldB

2701

FROM t2 WHERE fieldA = field1);

2702

field1 field2

2703

1 1

2704

1 3

2705

DROP TABLE t1, t2;

2706

CREATE TABLE t1(a int, INDEX (a));

2707

INSERT INTO t1 VALUES (1), (3), (5), (7);

2708

INSERT INTO t1 VALUES (NULL);

2709

CREATE TABLE t2(a int);

2710

INSERT INTO t2 VALUES (1),(2),(3);

2711

EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;

2712

id select_type table type possible_keys key key_len ref rows Extra

2713

1 PRIMARY t2 ALL NULL NULL NULL NULL 3

2714

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key

2715

SELECT a, a IN (SELECT a FROM t1) FROM t2;

2716

a a IN (SELECT a FROM t1)

2717

1 1

2718

2 NULL

2719

3 1

2720

DROP TABLE t1,t2;

2721

CREATE TABLE t1 (a DATETIME);

2722

INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');

2723

CREATE TABLE t2 AS SELECT

2724

(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a

2725

FROM t1 WHERE a > '2000-01-01';

2726

SHOW CREATE TABLE t2;

2727

Table Create Table

2728

t2 CREATE TABLE `t2` (

2729

`sub_a` datetime

2730

) ENGINE=PBXT

2731

CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');

2732

SHOW CREATE TABLE t3;

2733

Table Create Table

2734

t3 CREATE TABLE `t3` (

2735

`a` datetime

2736

) ENGINE=PBXT

2737

DROP TABLE t1,t2,t3;

2738

CREATE TABLE t1 (a int);

2739

INSERT INTO t1 VALUES (2), (4), (1), (3);

2740

CREATE TABLE t2 (b int, c int);

2741

INSERT INTO t2 VALUES

2742

(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);

2743

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );

2744

2745

2746

2747

2748

2749

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);

2750

ERROR 21000: Subquery returns more than 1 row

2751

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;

2752

2753

2754

2755

2756

2757

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;

2758

ERROR 21000: Subquery returns more than 1 row

2759

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);

2760

b MAX(c)

2761

1 4

2762

2 2

2763

4 4

2764

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);

2765

ERROR 21000: Subquery returns more than 1 row

2766

SELECT a FROM t1 GROUP BY a

2767

HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),

2768

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

2769

2770

2771

2772

2773

2774

SELECT a FROM t1 GROUP BY a

2775

HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),

2776

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

2777

ERROR 21000: Subquery returns more than 1 row

2778

SELECT a FROM t1 GROUP BY a

2779

HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),

2780

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

2781

2782

2783

SELECT a FROM t1 GROUP BY a

2784

HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),

2785

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;

2786

ERROR 21000: Subquery returns more than 1 row

2787

SELECT a FROM t1

2788

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),

2789

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));

2790

2791

2792

2793

2794

2795

SELECT a FROM t1

2796

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),

2797

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

2798

ERROR 21000: Subquery returns more than 1 row

2799

SELECT a FROM t1

2800

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),

2801

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));

2802

2803

2804

2805

2806

2807

SELECT a FROM t1

2808

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),

2809

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

2810

ERROR 21000: Subquery returns more than 1 row

2811

DROP TABLE t1,t2;

2812

create table t1 (df decimal(5,1));

2813

insert into t1 values(1.1);

2814

insert into t1 values(2.2);

2815

select * from t1 where df <= all (select avg(df) from t1 group by df);

2816

2817

1.1

2818

select * from t1 where df >= all (select avg(df) from t1 group by df);

2819

2820

2.2

2821

drop table t1;

2822

create table t1 (df decimal(5,1));

2823

insert into t1 values(1.1);

2824

select 1.1 * exists(select * from t1);

2825

1.1 * exists(select * from t1)

2826

1.1

2827

drop table t1;

2828

CREATE TABLE t1 (

2829

grp int default NULL,

2830

a decimal(10,2) default NULL);

2831

insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);

2832

select * from t1;

2833

grp a

2834

1 1.00

2835

2 2.00

2836

2 3.00

2837

3 4.00

2838

3 5.00

2839

3 6.00

2840

NULL NULL

2841

select min(a) from t1 group by grp;

2842

min(a)

2843

NULL

2844

1.00

2845

2.00

2846

4.00

2847

drop table t1;

2848

CREATE table t1 ( c1 integer );

2849

INSERT INTO t1 VALUES ( 1 );

2850

INSERT INTO t1 VALUES ( 2 );

2851

INSERT INTO t1 VALUES ( 3 );

2852

CREATE TABLE t2 ( c2 integer );

2853

INSERT INTO t2 VALUES ( 1 );

2854

INSERT INTO t2 VALUES ( 4 );

2855

INSERT INTO t2 VALUES ( 5 );

2856

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);

2857

c1 c2

2858

1 1

2859

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2

2860

WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );

2861

c1 c2

2862

1 1

2863

DROP TABLE t1,t2;

2864

CREATE TABLE t1 ( c1 integer );

2865

INSERT INTO t1 VALUES ( 1 );

2866

INSERT INTO t1 VALUES ( 2 );

2867

INSERT INTO t1 VALUES ( 3 );

2868

INSERT INTO t1 VALUES ( 6 );

2869

CREATE TABLE t2 ( c2 integer );

2870

INSERT INTO t2 VALUES ( 1 );

2871

INSERT INTO t2 VALUES ( 4 );

2872

INSERT INTO t2 VALUES ( 5 );

2873

INSERT INTO t2 VALUES ( 6 );

2874

CREATE TABLE t3 ( c3 integer );

2875

INSERT INTO t3 VALUES ( 7 );

2876

INSERT INTO t3 VALUES ( 8 );

2877

SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2

2878

WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );

2879

c1 c2

2880

2 NULL

2881

3 NULL

2882

DROP TABLE t1,t2,t3;

2883

CREATE TABLE t1 (EMPNUM CHAR(3));

2884

CREATE TABLE t2 (EMPNUM CHAR(3) );

2885

INSERT INTO t1 VALUES ('E1'),('E2');

2886

INSERT INTO t2 VALUES ('E1');

2887

DELETE FROM t1

2888

WHERE t1.EMPNUM NOT IN

2889

(SELECT t2.EMPNUM

2890

FROM t2

2891

WHERE t1.EMPNUM = t2.EMPNUM);

2892

select * from t1;

2893

EMPNUM

2894

2895

DROP TABLE t1,t2;

2896

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);

2897

INSERT INTO t1 VALUES (1, 1);

2898

CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,

2899

PRIMARY KEY(select_id,values_id));

2900

INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);

2901

SELECT values_id FROM t1

2902

WHERE values_id IN (SELECT values_id FROM t2

2903

WHERE select_id IN (1, 0));

2904

values_id

2905

2906

SELECT values_id FROM t1

2907

WHERE values_id IN (SELECT values_id FROM t2

2908

WHERE select_id BETWEEN 0 AND 1);

2909

values_id

2910

2911

SELECT values_id FROM t1

2912

WHERE values_id IN (SELECT values_id FROM t2

2913

WHERE select_id = 0 OR select_id = 1);

2914

values_id

2915

2916

DROP TABLE t1, t2;

2917

create table t1 (fld enum('0','1'));

2918

insert into t1 values ('1');

2919

select * from (select max(fld) from t1) as foo;

2920

max(fld)

2921

2922

drop table t1;

2923

CREATE TABLE t1 (a int, b int);

2924

CREATE TABLE t2 (c int, d int);

2925

CREATE TABLE t3 (e int);

2926

INSERT INTO t1 VALUES

2927

(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);

2928

INSERT INTO t2 VALUES

2929

(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);

2930

INSERT INTO t3 VALUES (10), (30), (10), (20) ;

2931

SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;

2932

a MAX(b) MIN(b)

2933

1 20 10

2934

2 30 10

2935

3 20 20

2936

4 40 40

2937

SELECT * FROM t2;

2938

c d

2939

2 10

2940

2 20

2941

4 10

2942

5 10

2943

3 20

2944

2 40

2945

SELECT * FROM t3;

2946

2947

2948

2949

2950

2951

SELECT a FROM t1 GROUP BY a

2952

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);

2953

2954

2955

2956

SELECT a FROM t1 GROUP BY a

2957

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);

2958

2959

2960

SELECT a FROM t1 GROUP BY a

2961

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);

2962

2963

2964

2965

SELECT a FROM t1 GROUP BY a

2966

HAVING a IN (SELECT c FROM t2

2967

WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));

2968

2969

2970

2971

SELECT a FROM t1 GROUP BY a

2972

HAVING a IN (SELECT c FROM t2

2973

WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

2974

2975

2976

2977

SELECT a FROM t1 GROUP BY a

2978

HAVING a IN (SELECT c FROM t2

2979

WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));

2980

2981

2982

SELECT a FROM t1 GROUP BY a

2983

HAVING a IN (SELECT c FROM t2

2984

WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));

2985

2986

2987

SELECT a FROM t1 GROUP BY a

2988

HAVING a IN (SELECT c FROM t2

2989

WHERE MIN(b) < d AND

2990

EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

2991

2992

2993

SELECT a, SUM(a) FROM t1 GROUP BY a;

2994

a SUM(a)

2995

1 2

2996

2 6

2997

3 3

2998

4 4

2999

SELECT a FROM t1

3000

WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;

3001

3002

3003

3004

SELECT a FROM t1 GROUP BY a

3005

HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);

3006

3007

3008

3009

3010

SELECT a FROM t1

3011

WHERE a < 3 AND

3012

EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;

3013

3014

3015

3016

SELECT a FROM t1

3017

WHERE a < 3 AND

3018

EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);

3019

3020

3021

3022

3023

3024

3025

SELECT t1.a FROM t1 GROUP BY t1.a

3026

HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c

3027

HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e

3028

HAVING SUM(t1.a+t2.c) < t3.e/4));

3029

3030

3031

3032

SELECT t1.a FROM t1 GROUP BY t1.a

3033

HAVING t1.a > ALL(SELECT t2.c FROM t2

3034

WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e

3035

HAVING SUM(t1.a+t2.c) < t3.e/4));

3036

3037

3038

SELECT t1.a FROM t1 GROUP BY t1.a

3039

HAVING t1.a > ALL(SELECT t2.c FROM t2

3040

WHERE EXISTS(SELECT t3.e FROM t3

3041

WHERE SUM(t1.a+t2.c) < t3.e/4));

3042

ERROR HY000: Invalid use of group function

3043

SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;

3044

ERROR HY000: Invalid use of group function

3045

SELECT t1.a FROM t1 GROUP BY t1.a

3046

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3047

HAVING AVG(t2.c+SUM(t1.b)) > 20);

3048

3049

3050

3051

3052

SELECT t1.a FROM t1 GROUP BY t1.a

3053

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3054

HAVING AVG(SUM(t1.b)) > 20);

3055

3056

3057

3058

SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a

3059

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3060

HAVING t2.c+sum > 20);

3061

a sum

3062

2 60

3063

3 20

3064

4 40

3065

DROP TABLE t1,t2,t3;

3066

CREATE TABLE t1 (a varchar(5), b varchar(10));

3067

INSERT INTO t1 VALUES

3068

('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),

3069

('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);

3070

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3071

a b

3072

BBB 4

3073

CCC 7

3074

AAA 8

3075

EXPLAIN

3076

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3077

id select_type table type possible_keys key key_len ref rows Extra

3078

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3079

2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort

3080

ALTER TABLE t1 ADD INDEX(a);

3081

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3082

a b

3083

BBB 4

3084

CCC 7

3085

AAA 8

3086

EXPLAIN

3087

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3088

id select_type table type possible_keys key key_len ref rows Extra

3089

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3090

2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort

3091

DROP TABLE t1;

3092

create table t1( f1 int,f2 int);

3093

insert into t1 values (1,1),(2,2);

3094

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';

3095

3096

crash1

3097

crash1

3098

drop table t1;

3099

create table t1 (c int, key(c));

3100

insert into t1 values (1142477582), (1142455969);

3101

create table t2 (a int, b int);

3102

insert into t2 values (2, 1), (1, 0);

3103

delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;

3104

drop table t1, t2;

3105

create table t1 (i int, j bigint);

3106

insert into t1 values (1, 2), (2, 2), (3, 2);

3107

select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;

3108

min(i)

3109

3110

drop table t1;

3111

CREATE TABLE t1 (i BIGINT);

3112

INSERT INTO t1 VALUES (10000000000000000);

3113

INSERT INTO t1 VALUES (1);

3114

CREATE TABLE t2 (i BIGINT);

3115

INSERT INTO t2 VALUES (10000000000000000);

3116

INSERT INTO t2 VALUES (1);

3117

/* simple test */

3118

SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;

3119

3120

10000000000000000

3121

3122

/* subquery test */

3123

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3124

3125

10000000000000000

3126

/* subquery test with cast*/

3127

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3128

3129

10000000000000000

3130

DROP TABLE t1;

3131

DROP TABLE t2;

3132

CREATE TABLE t1 (

3133

id bigint NOT NULL auto_increment,

3134

name varchar(255) NOT NULL,

3135

PRIMARY KEY (id)

3136

);

3137

INSERT INTO t1 VALUES

3138

(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

3139

CREATE TABLE t2 (

3140

id bigint NOT NULL auto_increment,

3141

mid bigint NOT NULL,

3142

date date NOT NULL,

3143

PRIMARY KEY (id)

3144

);

3145

INSERT INTO t2 VALUES

3146

(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),

3147

(4, 2, '2006-04-20'), (5, 1, '2006-05-01');

3148

SELECT *,

3149

(SELECT date FROM t2 WHERE mid = t1.id

3150

ORDER BY date DESC LIMIT 0, 1) AS date_last,

3151

(SELECT date FROM t2 WHERE mid = t1.id

3152

ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last

3153

FROM t1;

3154

id name date_last date_next_to_last

3155

1 Balazs 2006-05-01 NULL

3156

2 Joe 2006-04-20 NULL

3157

3 Frank 2006-04-13 NULL

3158

SELECT *,

3159

(SELECT COUNT(*) FROM t2 WHERE mid = t1.id

3160

ORDER BY date DESC LIMIT 1, 1) AS date_count

3161

FROM t1;

3162

id name date_count

3163

1 Balazs NULL

3164

2 Joe NULL

3165

3 Frank NULL

3166

SELECT *,

3167

(SELECT date FROM t2 WHERE mid = t1.id

3168

ORDER BY date DESC LIMIT 0, 1) AS date_last,

3169

(SELECT date FROM t2 WHERE mid = t1.id

3170

ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last

3171

FROM t1;

3172

id name date_last date_next_to_last

3173

1 Balazs 2006-05-01 2006-03-30

3174

2 Joe 2006-04-20 2006-04-06

3175

3 Frank 2006-04-13 NULL

3176

DROP TABLE t1,t2;

3177

CREATE TABLE t1 (

3178

i1 int NOT NULL default '0',

3179

i2 int NOT NULL default '0',

3180

t datetime NOT NULL default '0000-00-00 00:00:00',

3181

PRIMARY KEY (i1,i2,t)

3182

);

3183

INSERT INTO t1 VALUES

3184

(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),

3185

(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),

3186

(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),

3187

(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),

3188

(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),

3189

(24,2,'2005-05-27 12:40:06');

3190

CREATE TABLE t2 (

3191

i1 int NOT NULL default '0',

3192

i2 int NOT NULL default '0',

3193

t datetime default NULL,

3194

PRIMARY KEY (i1)

3195

);

3196

INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');

3197

EXPLAIN

3198

SELECT * FROM t1,t2

3199

WHERE t1.t = (SELECT t1.t FROM t1

3200

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3201

ORDER BY t1.t DESC LIMIT 1);

3202

id select_type table type possible_keys key key_len ref rows Extra

3203

1 PRIMARY t2 ALL NULL NULL NULL NULL 1

3204

1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index; Using join buffer

3205

2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 2 Using where; Using index; Using filesort

3206

SELECT * FROM t1,t2

3207

WHERE t1.t = (SELECT t1.t FROM t1

3208

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3209

ORDER BY t1.t DESC LIMIT 1);

3210

i1 i2 t i1 i2 t

3211

24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40

3212

DROP TABLE t1, t2;

3213

CREATE TABLE t1 (i INT);

3214

(SELECT i FROM t1) UNION (SELECT i FROM t1);

3215

3216

SELECT * FROM t1 WHERE NOT EXISTS

3217

(

3218

(SELECT i FROM t1) UNION

3219

(SELECT i FROM t1)

3220

);

3221

3222

SELECT * FROM t1

3223

WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));

3224

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1

3225

explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))

3226

from t1;

3227

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))

3228

from t1' at line 1

3229

explain select * from t1 where not exists

3230

((select t11.i from t1 t11) union (select t12.i from t1 t12));

3231

id select_type table type possible_keys key key_len ref rows Extra

3232

1 PRIMARY t1 ALL NULL NULL NULL NULL 0

3233

2 SUBQUERY t11 ALL NULL NULL NULL NULL 0

3234

3 UNION t12 ALL NULL NULL NULL NULL 0

3235

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL

3236

DROP TABLE t1;

3237

CREATE TABLE t1 (a INT, b INT);

3238

CREATE TABLE t2 (a INT);

3239

INSERT INTO t2 values (1);

3240

INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);

3241

SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;

3242

(SELECT COUNT(DISTINCT t1.b) from t2)

3243

3244

3245

3246

SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)

3247

FROM t1 GROUP BY t1.a;

3248

(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)

3249

3250

3251

3252

SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;

3253

COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))

3254

2 2

3255

1 1

3256

1 1

3257

SELECT (

3258

SELECT (

3259

SELECT (

3260

SELECT COUNT(DISTINCT t1.b)

3261

)

3262

)

3263

FROM t1 GROUP BY t1.a LIMIT 1)

3264

FROM t1 t2

3265

GROUP BY t2.a;

3266

(

3267

SELECT (

3268

SELECT (

3269

SELECT COUNT(DISTINCT t1.b)

3270

)

3271

)

3272

FROM t1 GROUP BY t1.a LIMIT 1)

3273

3274

3275

3276

DROP TABLE t1,t2;

3277

CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));

3278

CREATE TABLE t2 (x int auto_increment, y int, z int,

3279

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));

3280

SET SESSION sort_buffer_size = 32 * 1024;

3281

Warnings:

3282

Warning 1292 Truncated incorrect sort_buffer_size value: '32768'

3283

SELECT COUNT(*)

3284

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3285

FROM t1) t;

3286

COUNT(*)

3287

3000

3288

SET SESSION sort_buffer_size = 8 * 1024 * 1024;

3289

SELECT COUNT(*)

3290

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3291

FROM t1) t;

3292

COUNT(*)

3293

3000

3294

DROP TABLE t2,t1;

3295

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);

3296

CREATE TABLE t2 (c int);

3297

INSERT INTO t1 VALUES ('aa', 1);

3298

INSERT INTO t2 VALUES (1);

3299

SELECT * FROM t1

3300

WHERE EXISTS (SELECT c FROM t2 WHERE c=1

3301

UNION

3302

SELECT c from t2 WHERE c=t1.c);

3303

id c

3304

aa 1

3305

INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);

3306

SELECT * FROM t1

3307

WHERE EXISTS (SELECT c FROM t2 WHERE c=1

3308

UNION

3309

SELECT c from t2 WHERE c=t1.c);

3310

id c

3311

aa 1

3312

bb 2

3313

cc 3

3314

dd 1

3315

INSERT INTO t2 VALUES (2);

3316

CREATE TABLE t3 (c int);

3317

INSERT INTO t3 VALUES (1);

3318

SELECT * FROM t1

3319

WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1

3320

UNION

3321

SELECT c from t2 WHERE c=t1.c);

3322

id c

3323

aa 1

3324

bb 2

3325

cc 3

3326

dd 1

3327

DROP TABLE t1,t2,t3;

3328

DROP TABLE IF EXISTS t1;

3329

DROP TABLE IF EXISTS t2;

3330

DROP TABLE IF EXISTS t1xt2;

3331

CREATE TABLE t1 (

3332

id_1 int NOT NULL,

3333

t varchar(4) DEFAULT NULL

3334

);

3335

CREATE TABLE t2 (

3336

id_2 int NOT NULL,

3337

t varchar(4) DEFAULT NULL

3338

);

3339

CREATE TABLE t1xt2 (

3340

id_1 int NOT NULL,

3341

id_2 int NOT NULL

3342

);

3343

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

3344

INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');

3345

INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);

3346

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3347

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3348

id_1

3349

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3350

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3351

id_1

3352

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3353

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3354

id_1

3355

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3356

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3357

id_1

3358

3359

3360

3361

3362

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3363

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));

3364

id_1

3365

3366

3367

3368

3369

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3370

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));

3371

id_1

3372

3373

3374

3375

3376

insert INTO t1xt2 VALUES (1, 12);

3377

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3378

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3379

id_1

3380

3381

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3382

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3383

id_1

3384

3385

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3386

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3387

id_1

3388

3389

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3390

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3391

id_1

3392

3393

3394

3395

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3396

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3397

id_1

3398

3399

3400

3401

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3402

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3403

id_1

3404

3405

3406

3407

insert INTO t1xt2 VALUES (2, 12);

3408

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3409

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3410

id_1

3411

3412

3413

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3414

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3415

id_1

3416

3417

3418

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3419

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3420

id_1

3421

3422

3423

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3424

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3425

id_1

3426

3427

3428

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3429

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3430

id_1

3431

3432

3433

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3434

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3435

id_1

3436

3437

3438

DROP TABLE t1;

3439

DROP TABLE t2;

3440

DROP TABLE t1xt2;

3441

CREATE TABLE t1 (a int);

3442

INSERT INTO t1 VALUES (3), (1), (2);

3443

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;

3444

col1 col2

3445

this is a test. 3

3446

this is a test. 1

3447

this is a test. 2

3448

SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;

3449

col1 t2

3450

this is a test. 3

3451

this is a test. 1

3452

this is a test. 2

3453

DROP table t1;

3454

CREATE TABLE t1 (a int, b int);

3455

CREATE TABLE t2 (m int, n int);

3456

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

3457

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3458

SELECT COUNT(*), a,

3459

(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3460

FROM t1 GROUP BY a;

3461

COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3462

2 2 2

3463

3 3 3

3464

1 4 1

3465

SELECT COUNT(*), a,

3466

(SELECT MIN(m) FROM t2 WHERE m = count(*))

3467

FROM t1 GROUP BY a;

3468

COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))

3469

2 2 2

3470

3 3 3

3471

1 4 1

3472

SELECT COUNT(*), a

3473

FROM t1 GROUP BY a

3474

HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;

3475

COUNT(*) a

3476

2 2

3477

3 3

3478

DROP TABLE t1,t2;

3479

CREATE TABLE t1 (a int, b int);

3480

CREATE TABLE t2 (m int, n int);

3481

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

3482

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3483

SELECT COUNT(*) c, a,

3484

(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3485

FROM t1 GROUP BY a;

3486

c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3487

2 2 2

3488

3 3 3

3489

1 4 1,1

3490

SELECT COUNT(*) c, a,

3491

(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3492

FROM t1 GROUP BY a;

3493

c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3494

2 2 3

3495

3 3 4

3496

1 4 2,2

3497

DROP table t1,t2;

3498

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));

3499

INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),

3500

(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),

3501

(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');

3502

SELECT a, MAX(b),

3503

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test

3504

FROM t1 GROUP BY a;

3505

a MAX(b) test

3506

1 9 m

3507

2 3 h

3508

3 4 i

3509

SELECT a x, MAX(b),

3510

(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test

3511

FROM t1 GROUP BY a;

3512

x MAX(b) test

3513

1 9 m

3514

2 3 h

3515

3 4 i

3516

SELECT a, AVG(b),

3517

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test

3518

FROM t1 WHERE t1.d=0 GROUP BY a;

3519

a AVG(b) test

3520

1 4.0000 d

3521

2 2.0000 g

3522

3 2.5000 NULL

3523

SELECT tt.a,

3524

(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3525

LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3526

FROM t1 as tt;

3527

a test

3528

1 n

3529

1 n

3530

1 n

3531

1 n

3532

1 n

3533

1 n

3534

1 n

3535

2 o

3536

2 o

3537

2 o

3538

2 o

3539

3 p

3540

3 p

3541

3 p

3542

3 p

3543

3 p

3544

SELECT tt.a,

3545

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3546

LIMIT 1)

3547

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3548

FROM t1 as tt GROUP BY tt.a;

3549

a test

3550

1 n

3551

2 o

3552

3 p

3553

SELECT tt.a, MAX(

3554

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3555

LIMIT 1)

3556

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test

3557

FROM t1 as tt GROUP BY tt.a;

3558

a test

3559

1 n

3560

2 o

3561

3 p

3562

DROP TABLE t1;

3563

CREATE TABLE t1 (a INT);

3564

INSERT INTO t1 values (1),(1),(1),(1);

3565

CREATE TABLE t2 (x INT);

3566

INSERT INTO t1 values (1000),(1001),(1002);

3567

SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;

3568

ERROR HY000: Invalid use of group function

3569

SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;

3570

ERROR HY000: Invalid use of group function

3571

SELECT

3572

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )

3573

FROM t1;

3574

ERROR HY000: Invalid use of group function

3575

SELECT t1.a as XXA,

3576

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )

3577

FROM t1;

3578

ERROR HY000: Invalid use of group function

3579

DROP TABLE t1,t2;

3580

CREATE TABLE t1 (a int, b int, KEY (a));

3581

INSERT INTO t1 VALUES (1,1),(2,1);

3582

EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);

3583

id select_type table type possible_keys key key_len ref rows Extra

3584

1 PRIMARY t1 ref a a 5 const 1 Using where; Using index

3585

2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort

3586

DROP TABLE t1;

3587

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));

3588

INSERT INTO t1 VALUES

3589

(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');

3590

CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));

3591

INSERT INTO t2 VALUES (7), (5), (1), (3);

3592

SELECT id, st FROM t1

3593

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3594

id st

3595

3 FL

3596

1 GA

3597

7 FL

3598

SELECT id, st FROM t1

3599

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)

3600

GROUP BY id;

3601

id st

3602

1 GA

3603

3 FL

3604

7 FL

3605

SELECT id, st FROM t1

3606

WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3607

id st

3608

2 GA

3609

4 FL

3610

SELECT id, st FROM t1

3611

WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)

3612

GROUP BY id;

3613

id st

3614

2 GA

3615

4 FL

3616

DROP TABLE t1,t2;

3617

CREATE TABLE t1 (a int);

3618

INSERT INTO t1 VALUES (1), (2);

3619

EXPLAIN EXTENDED

3620

SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;

3621

id select_type table type possible_keys key key_len ref rows filtered Extra

3622

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00

3623

2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort

3624

Warnings:

3625

Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`

3626

DROP TABLE t1;

3627

CREATE TABLE t1 (

3628

a varchar(255) default NULL,

3629

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3630

INDEX idx(a,b)

3631

);

3632

CREATE TABLE t2 (

3633

a varchar(255) default NULL

3634

);

3635

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');

3636

INSERT INTO t1 SELECT * FROM t1;

3637

INSERT INTO t1 SELECT * FROM t1;

3638

INSERT INTO t1 SELECT * FROM t1;

3639

INSERT INTO t1 SELECT * FROM t1;

3640

INSERT INTO t1 SELECT * FROM t1;

3641

INSERT INTO t1 SELECT * FROM t1;

3642

INSERT INTO t1 SELECT * FROM t1;

3643

INSERT INTO t1 SELECT * FROM t1;

3644

INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');

3645

INSERT INTO `t2` VALUES ('abcdefghijk');

3646

INSERT INTO `t2` VALUES ('asdf');

3647

SET session sort_buffer_size=8192;

3648

Warnings:

3649

Warning 1292 Truncated incorrect sort_buffer_size value: '8192'

3650

SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;

3651

3652

3653

3654

DROP TABLE t1,t2;

3655

CREATE TABLE t1 (a INTEGER, b INTEGER);

3656

CREATE TABLE t2 (x INTEGER);

3657

INSERT INTO t1 VALUES (1,11), (2,22), (2,22);

3658

INSERT INTO t2 VALUES (1), (2);

3659

SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;

3660

ERROR 21000: Subquery returns more than 1 row

3661

SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;

3662

ERROR 21000: Subquery returns more than 1 row

3663

SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;

3664

(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)

3665

3.3333

3666

DROP TABLE t1,t2;

3667

CREATE TABLE t1 (a INT, b INT);

3668

INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);

3669

SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1

3670

AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)

3671

GROUP BY a1.a;

3672

a COUNT(*)

3673

1 3

3674

DROP TABLE t1;

3675

CREATE TABLE t1 (a INT);

3676

CREATE TABLE t2 (a INT);

3677

INSERT INTO t1 VALUES (1),(2);

3678

INSERT INTO t2 VALUES (1),(2);

3679

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;

3680

(SELECT SUM(t1.a) FROM t2 WHERE a=0)

3681

NULL

3682

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;

3683

ERROR 21000: Subquery returns more than 1 row

3684

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;

3685

(SELECT SUM(t1.a) FROM t2 WHERE a=1)

3686

3687

DROP TABLE t1,t2;

3688

CREATE TABLE t1 (a1 INT, a2 INT);

3689

CREATE TABLE t2 (b1 INT, b2 INT);

3690

INSERT INTO t1 VALUES (100, 200);

3691

INSERT INTO t1 VALUES (101, 201);

3692

INSERT INTO t2 VALUES (101, 201);

3693

INSERT INTO t2 VALUES (103, 203);

3694

SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;

3695

((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL

3696

3697

3698

DROP TABLE t1, t2;

3699

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));

3700

INSERT INTO t1 VALUES ('a', 'aa');

3701

INSERT INTO t1 VALUES ('a', 'aaa');

3702

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3703

a b

3704

CREATE INDEX I1 ON t1 (a);

3705

CREATE INDEX I2 ON t1 (b);

3706

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3707

id select_type table type possible_keys key key_len ref rows Extra

3708

1 PRIMARY t1 index I1 I1 7 NULL 2 Using index; LooseScan

3709

1 PRIMARY t1 ref I2 I2 43 test.t1.a 2 Using where

3710

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3711

a b

3712

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));

3713

INSERT INTO t2 SELECT * FROM t1;

3714

CREATE INDEX I1 ON t2 (a);

3715

CREATE INDEX I2 ON t2 (b);

3716

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

3717

id select_type table type possible_keys key key_len ref rows Extra

3718

1 PRIMARY t2 index I1 I1 7 NULL 2 Using index; LooseScan

3719

1 PRIMARY t2 ref I2 I2 43 test.t2.a 2 Using where

3720

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

3721

a b

3722

EXPLAIN

3723

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

3724

id select_type table type possible_keys key key_len ref rows Extra

3725

1 PRIMARY t1 index I1 I1 7 NULL 2 Using where; Using index; LooseScan

3726

1 PRIMARY t1 ref I2 I2 43 test.t1.a 2 Using where

3727

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

3728

a b

3729

DROP TABLE t1,t2;

3730

CREATE TABLE t1(a INT, b INT);

3731

INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);

3732

EXPLAIN

3733

SELECT a AS out_a, MIN(b) FROM t1

3734

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

3735

GROUP BY a;

3736

ERROR 42S22: Unknown column 'out_a' in 'where clause'

3737

SELECT a AS out_a, MIN(b) FROM t1

3738

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

3739

GROUP BY a;

3740

ERROR 42S22: Unknown column 'out_a' in 'where clause'

3741

EXPLAIN

3742

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

3743

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

3744

GROUP BY a;

3745

id select_type table type possible_keys key key_len ref rows Extra

3746

1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort

3747

2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where

3748

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

3749

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

3750

GROUP BY a;

3751

out_a MIN(b)

3752

1 2

3753

2 4

3754

DROP TABLE t1;

3755

CREATE TABLE t1 (a INT);

3756

CREATE TABLE t2 (a INT);

3757

INSERT INTO t1 VALUES (1),(2);

3758

INSERT INTO t2 VALUES (1),(2);

3759

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

3760

3761

3762

3763

EXPLAIN EXTENDED

3764

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

3765

id select_type table type possible_keys key key_len ref rows filtered Extra

3766

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where

3767

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where

3768

Warnings:

3769

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

3770

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`))

3771

EXPLAIN EXTENDED

3772

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION

3773

(SELECT 1 FROM t2 WHERE t1.a = t2.a));

3774

id select_type table type possible_keys key key_len ref rows filtered Extra

3775

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where

3776

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where

3777

3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where

3778

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL

3779

Warnings:

3780

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

3781

Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1

3782

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`)))

3783

DROP TABLE t1,t2;

3784

CREATE TABLE t4 (

3785

f7 varchar(32) collate utf8_bin NOT NULL default '',

3786

f10 varchar(32) collate utf8_bin default NULL,

3787

PRIMARY KEY (f7)

3788

);

3789

INSERT INTO t4 VALUES(1,1), (2,null);

3790

CREATE TABLE t2 (

3791

f4 varchar(32) collate utf8_bin NOT NULL default '',

3792

f2 varchar(50) collate utf8_bin default NULL,

3793

f3 varchar(10) collate utf8_bin default NULL,

3794

PRIMARY KEY (f4),

3795

UNIQUE KEY uk1 (f2)

3796

);

3797

INSERT INTO t2 VALUES(1,1,null), (2,2,null);

3798

CREATE TABLE t1 (

3799

f8 varchar(32) collate utf8_bin NOT NULL default '',

3800

f1 varchar(10) collate utf8_bin default NULL,

3801

f9 varchar(32) collate utf8_bin default NULL,

3802

PRIMARY KEY (f8)

3803

);

3804

INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);

3805

CREATE TABLE t3 (

3806

f6 varchar(32) collate utf8_bin NOT NULL default '',

3807

f5 varchar(50) collate utf8_bin default NULL,

3808

PRIMARY KEY (f6)

3809

);

3810

INSERT INTO t3 VALUES (1,null), (2,null);

3811

SELECT

3812

IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,

3813

IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,

3814

SUM(

3815

IF(

3816

(SELECT VPC.f2

3817

FROM t2 VPC, t4 a2, t2 a3

3818

WHERE

3819

VPC.f4 = a2.f10 AND a3.f2 = a4

3820

LIMIT 1) IS NULL,

3821

3822

t3.f5

3823

)

3824

) AS a6

3825

FROM

3826

t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4

3827

GROUP BY a4;

3828

a4 f3 a6

3829

1 NULL NULL

3830

2 NULL NULL

3831

DROP TABLE t1, t2, t3, t4;

3832

End of 5.0 tests.

3833

create table t_out (subcase char(3),

3834

a1 char(2), b1 char(2), c1 char(2));

3835

create table t_in (a2 char(2), b2 char(2), c2 char(2));

3836

insert into t_out values ('A.1','2a', NULL, '2a');

3837

insert into t_out values ('A.3', '2a', NULL, '2a');

3838

insert into t_out values ('A.4', '2a', NULL, 'xx');

3839

insert into t_out values ('B.1', '2a', '2a', '2a');

3840

insert into t_out values ('B.2', '2a', '2a', '2a');

3841

insert into t_out values ('B.3', '3a', 'xx', '3a');

3842

insert into t_out values ('B.4', 'xx', '3a', '3a');

3843

insert into t_in values ('1a', '1a', '1a');

3844

insert into t_in values ('2a', '2a', '2a');

3845

insert into t_in values (NULL, '2a', '2a');

3846

insert into t_in values ('3a', NULL, '3a');

3847

3848

Test general IN semantics (not top-level)

3849

3850

case A.1

3851

select subcase,

3852

(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,

3853

(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in

3854

from t_out where subcase = 'A.1';

3855

subcase pred_in pred_not_in

3856

A.1 0 1

3857

case A.2 - impossible

3858

case A.3

3859

select subcase,

3860

(a1, b1, c1) IN (select * from t_in) pred_in,

3861

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

3862

from t_out where subcase = 'A.3';

3863

subcase pred_in pred_not_in

3864

A.3 NULL NULL

3865

case A.4

3866

select subcase,

3867

(a1, b1, c1) IN (select * from t_in) pred_in,

3868

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

3869

from t_out where subcase = 'A.4';

3870

subcase pred_in pred_not_in

3871

A.4 0 1

3872

case B.1

3873

select subcase,

3874

(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,

3875

(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in

3876

from t_out where subcase = 'B.1';

3877

subcase pred_in pred_not_in

3878

B.1 0 1

3879

case B.2

3880

select subcase,

3881

(a1, b1, c1) IN (select * from t_in) pred_in,

3882

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

3883

from t_out where subcase = 'B.2';

3884

subcase pred_in pred_not_in

3885

B.2 1 0

3886

case B.3

3887

select subcase,

3888

(a1, b1, c1) IN (select * from t_in) pred_in,

3889

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

3890

from t_out where subcase = 'B.3';

3891

subcase pred_in pred_not_in

3892

B.3 NULL NULL

3893

case B.4

3894

select subcase,

3895

(a1, b1, c1) IN (select * from t_in) pred_in,

3896

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

3897

from t_out where subcase = 'B.4';

3898

subcase pred_in pred_not_in

3899

B.4 0 1

3900

3901

Test IN as top-level predicate, and

3902

as non-top level for cases A.3, B.3 (the only cases with NULL result).

3903

3904

case A.1

3905

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

3906

where subcase = 'A.1' and

3907

(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');

3908

pred_in

3909

3910

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

3911

where subcase = 'A.1' and

3912

(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');

3913

pred_not_in

3914

3915

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

3916

where subcase = 'A.1' and

3917

NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));

3918

not_pred_in

3919

3920

case A.3

3921

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

3922

where subcase = 'A.3' and

3923

(a1, b1, c1) IN (select * from t_in);

3924

pred_in

3925

3926

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

3927

where subcase = 'A.3' and

3928

(a1, b1, c1) NOT IN (select * from t_in);

3929

pred_not_in

3930

3931

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

3932

where subcase = 'A.3' and

3933

NOT((a1, b1, c1) IN (select * from t_in));

3934

not_pred_in

3935

3936

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

3937

where subcase = 'A.3' and

3938

((a1, b1, c1) IN (select * from t_in)) is NULL and

3939

((a1, b1, c1) NOT IN (select * from t_in)) is NULL;

3940

pred_in

3941

3942

case A.4

3943

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

3944

where subcase = 'A.4' and

3945

(a1, b1, c1) IN (select * from t_in);

3946

pred_in

3947

3948

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

3949

where subcase = 'A.4' and

3950

(a1, b1, c1) NOT IN (select * from t_in);

3951

pred_not_in

3952

3953

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

3954

where subcase = 'A.4' and

3955

NOT((a1, b1, c1) IN (select * from t_in));

3956

not_pred_in

3957

3958

case B.1

3959

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

3960

where subcase = 'B.1' and

3961

(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');

3962

pred_in

3963

3964

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

3965

where subcase = 'B.1' and

3966

(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');

3967

pred_not_in

3968

3969

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

3970

where subcase = 'B.1' and

3971

NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));

3972

not_pred_in

3973

3974

case B.2

3975

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

3976

where subcase = 'B.2' and

3977

(a1, b1, c1) IN (select * from t_in);

3978

pred_in

3979

3980

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

3981

where subcase = 'B.2' and

3982

(a1, b1, c1) NOT IN (select * from t_in);

3983

pred_not_in

3984

3985

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

3986

where subcase = 'B.2' and

3987

NOT((a1, b1, c1) IN (select * from t_in));

3988

not_pred_in

3989

3990

case B.3

3991

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

3992

where subcase = 'B.3' and

3993

(a1, b1, c1) IN (select * from t_in);

3994

pred_in

3995

3996

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

3997

where subcase = 'B.3' and

3998

(a1, b1, c1) NOT IN (select * from t_in);

3999

pred_not_in

4000

4001

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4002

where subcase = 'B.3' and

4003

NOT((a1, b1, c1) IN (select * from t_in));

4004

not_pred_in

4005

4006

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

4007

where subcase = 'B.3' and

4008

((a1, b1, c1) IN (select * from t_in)) is NULL and

4009

((a1, b1, c1) NOT IN (select * from t_in)) is NULL;

4010

pred_in

4011

4012

case B.4

4013

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4014

where subcase = 'B.4' and

4015

(a1, b1, c1) IN (select * from t_in);

4016

pred_in

4017

4018

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4019

where subcase = 'B.4' and

4020

(a1, b1, c1) NOT IN (select * from t_in);

4021

pred_not_in

4022

4023

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4024

where subcase = 'B.4' and

4025

NOT((a1, b1, c1) IN (select * from t_in));

4026

not_pred_in

4027

4028

drop table t_out;

4029

drop table t_in;

4030

CREATE TABLE t1 (s1 char(1));

4031

INSERT INTO t1 VALUES ('a');

4032

SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);

4033

4034

4035

DROP TABLE t1;

4036

CREATE TABLE t1( a INT );

4037

INSERT INTO t1 VALUES (1),(2);

4038

CREATE TABLE t2( a INT, b INT );

4039

SELECT *

4040

FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;

4041

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2

4042

SELECT *

4043

FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;

4044

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2

4045

SELECT *

4046

FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;

4047

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2

4048

SELECT * FROM (

4049

SELECT 1 a

4050

UNION

4051

SELECT a INTO @var FROM t1 WHERE a = 2

4052

) t1a;

4053

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2

4054

) t1a' at line 2

4055

SELECT * FROM (

4056

SELECT 1 a

4057

UNION

4058

SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2

4059

) t1a;

4060

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2

4061

) t1a' at line 2

4062

SELECT * FROM (

4063

SELECT 1 a

4064

UNION

4065

SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4066

) t1a;

4067

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4068

) t1a' at line 2

4069

SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;

4070

4071

4072

SELECT * FROM (

4073

SELECT a FROM t1 WHERE a = 2

4074

UNION

4075

SELECT a FROM t1 WHERE a = 2

4076

) t1a;

4077

4078

4079

SELECT * FROM (

4080

SELECT 1 a

4081

UNION

4082

SELECT a FROM t1 WHERE a = 2

4083

UNION

4084

SELECT a FROM t1 WHERE a = 2

4085

) t1a;

4086

4087

4088

4089

SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);

4090

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

4091

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;

4092

4093

4094

SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;

4095

4096

4097

SELECT * FROM ((SELECT 1 a INTO @a)) t1a;

4098

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1

4099

SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;

4100

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1

4101

SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4102

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1

4103

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;

4104

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1

4105

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4106

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1

4107

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;

4108

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1

4109

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;

4110

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a))) t1a' at line 1

4111

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;

4112

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1

4113

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;

4114

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1

4115

SELECT * FROM (SELECT 1 a ORDER BY a) t1a;

4116

4117

4118

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;

4119

4120

4121

SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;

4122

4123

4124

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;

4125

4126

4127

SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;

4128

a 1

4129

1 1

4130

2 1

4131

SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;

4132

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ON 1' at line 1

4133

SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;

4134

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON 1' at line 1

4135

SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;

4136

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ON 1' at line 1

4137

SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;

4138

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a ON 1' at line 1

4139

SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;

4140

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a ON 1' at line 1

4141

SELECT * FROM t1 JOIN (t1 t1a) ON 1;

4142

a a

4143

1 1

4144

2 1

4145

1 2

4146

2 2

4147

SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;

4148

a a

4149

1 1

4150

2 1

4151

1 2

4152

2 2

4153

SELECT * FROM (t1 t1a);

4154

4155

4156

4157

SELECT * FROM ((t1 t1a));

4158

4159

4160

4161

SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;

4162

a t1a

4163

1 1

4164

2 1

4165

SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;

4166

a t1a

4167

1 1

4168

2 1

4169

SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;

4170

a a

4171

1 1

4172

2 1

4173

SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;

4174

a a

4175

1 1

4176

2 1

4177

SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;

4178

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a2' at line 1

4179

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );

4180

4181

4182

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );

4183

4184

4185

SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );

4186

4187

4188

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);

4189

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1

4190

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4191

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4192

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4193

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4194

SELECT * FROM t1 WHERE a = ( SELECT 1 );

4195

4196

4197

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );

4198

4199

4200

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);

4201

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1

4202

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );

4203

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4204

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );

4205

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4206

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);

4207

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1

4208

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4209

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4210

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4211

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4212

SELECT ( SELECT 1 INTO @v );

4213

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4214

SELECT ( SELECT 1 INTO OUTFILE 'file' );

4215

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4216

SELECT ( SELECT 1 INTO DUMPFILE 'file' );

4217

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4218

SELECT ( SELECT 1 UNION SELECT 1 INTO @v );

4219

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4220

SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4221

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4222

SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4223

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4224

SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;

4225

( SELECT a FROM t1 WHERE a = 1 ) a

4226

1 1

4227

1 2

4228

SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;

4229

( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a

4230

1 1

4231

1 2

4232

SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);

4233

a b

4234

SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );

4235

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1

4236

( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4237

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1' at line 1

4238

SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4239

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4240

SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4241

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1' at line 1

4242

SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4243

( SELECT 1 UNION SELECT 1 UNION SELECT 1 )

4244

4245

SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));

4246

((SELECT 1 UNION SELECT 1 UNION SELECT 1))

4247

4248

SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4249

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4250

SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4251

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') UNION SELECT 1 )' at line 1

4252

SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;

4253

4254

4255

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4256

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4257

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4258

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4259

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4260

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4261

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4262

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4263

SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4264

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1

4265

SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4266

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1

4267

SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4268

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1

4269

SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4270

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1

4271

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4272

4273

4274

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4275

4276

4277

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4278

4279

4280

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4281

4282

4283

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );

4284

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4285

SELECT EXISTS(SELECT 1+1);

4286

EXISTS(SELECT 1+1)

4287

4288

SELECT EXISTS(SELECT 1+1 INTO @test);

4289

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @test)' at line 1

4290

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );

4291

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4292

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );

4293

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4294

SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );

4295

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4296

DROP TABLE t1, t2;

Older »