~stewart/drizzle/embedded-innodb-create-select-transaction-arrgh

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

187

id select_type table type possible_keys key key_len ref rows filtered Extra

188

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

189

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

190

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

191

4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

192

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

193

Warnings:

194

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

195

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

196

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

197

3 1

198

7 2

199

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

200

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

201

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

202

7 2

203

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

204

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

205

id select_type table type possible_keys key key_len ref rows filtered Extra

206

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

207

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

208

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

209

Warnings:

210

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"

211

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

212

213

214

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

215

216

217

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

218

219

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

220

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

221

8 7.5000

222

8 4.5000

223

9 7.5000

224

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

225

id select_type table type possible_keys key key_len ref rows filtered Extra

226

1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00

227

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

228

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

229

Warnings:

230

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

231

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"

232

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

233

234

235

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

236

237

238

239

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

240

241

242

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

243

244

245

246

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

247

248

249

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

250

251

252

253

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

254

255

256

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

257

258

259

260

insert into t2 values (100, 5);

261

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

262

263

264

265

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

266

267

268

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

269

270

271

272

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

273

id select_type table type possible_keys key key_len ref rows filtered Extra

274

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

275

2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00

276

Warnings:

277

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

278

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

279

280

281

delete from t2 where a=100;

282

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

283

ERROR 21000: Operand should contain 1 column(s)

284

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

285

ERROR 21000: Operand should contain 1 column(s)

286

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

287

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

288

b ma

289

insert into t2 values (2,10);

290

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

291

b ma

292

10 1

293

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

294

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

295

b ma

296

7 12

297

create table t5 (a int);

298

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

299

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

300

NULL 1

301

2 2

302

insert into t5 values (5);

303

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

304

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

305

NULL 1

306

2 2

307

insert into t5 values (2);

308

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

309

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

310

NULL 1

311

2 2

312

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;

313

id select_type table type possible_keys key key_len ref rows filtered Extra

314

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00

315

2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

316

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

317

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

318

Warnings:

319

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

320

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

321

Note 1003 select (select '2' AS "a" from "test"."t1" where ('2' = "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"

322

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

323

ERROR 21000: Subquery returns more than 1 row

324

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

325

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

326

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

327

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

328

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

329

patient_uq clinic_uq

330

1 1

331

1 2

332

2 2

333

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

334

id select_type table type possible_keys key key_len ref rows filtered Extra

335

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

336

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

337

Warnings:

338

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

339

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

340

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

341

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

342

drop table t1,t2,t3;

343

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

344

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

345

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

346

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

347

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

348

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

349

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

350

a b

351

W 1732-02-22

352

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

353

a b

354

W 1

355

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

356

a b

357

W a

358

CREATE TABLE `t8` (

359

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

360

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

361

PRIMARY KEY (`pseudo`),

362

UNIQUE KEY `email` (`email`)

363

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

364

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

365

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

366

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

367

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

368

id select_type table type possible_keys key key_len ref rows filtered Extra

369

1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index

370

4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

371

2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00

372

3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

373

Warnings:

374

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

375

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

376

t8 WHERE pseudo='joce');

377

ERROR 21000: Operand should contain 1 column(s)

378

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

379

pseudo='joce');

380

ERROR 21000: Operand should contain 1 column(s)

381

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

382

pseudo

383

joce

384

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

385

ERROR 21000: Subquery returns more than 1 row

386

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

387

CREATE TABLE `t1` (

388

`topic` mediumint(8) unsigned NOT NULL default '0',

389

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

390

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

391

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

392

KEY `topic` (`topic`)

393

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

394

INSERT INTO t1 (topic,date,pseudo) VALUES

395

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

396

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

397

id select_type table type possible_keys key key_len ref rows filtered Extra

398

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

399

Warnings:

400

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

401

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

402

id select_type table type possible_keys key key_len ref rows filtered Extra

403

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

404

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

405

Warnings:

406

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

407

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

408

date

409

2002-08-03

410

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

411

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

412

2002-08-03

413

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

414

415

416

417

418

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

419

ERROR 21000: Subquery returns more than 1 row

420

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

421

id select_type table type possible_keys key key_len ref rows filtered Extra

422

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

423

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

424

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

425

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

426

Warnings:

427

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

428

drop table t1;

429

CREATE TABLE `t1` (

430

`numeropost` mediumint(8) unsigned NOT NULL auto_increment,

431

`maxnumrep` int(10) unsigned NOT NULL default '0',

432

PRIMARY KEY (`numeropost`),

433

UNIQUE KEY `maxnumrep` (`maxnumrep`)

434

) ENGINE=MyISAM ROW_FORMAT=FIXED;

435

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

436

CREATE TABLE `t2` (

437

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

438

`topic` mediumint(8) unsigned NOT NULL default '0',

439

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

440

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

441

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

442

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

443

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

444

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

445

446

40143

447

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;

448

numeropost maxnumrep

449

43506 2

450

40143 1

451

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

452

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

453

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

454

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

455

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

460

mot topic date pseudo

461

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

462

mot topic date pseudo

463

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

464

mot topic date pseudo

465

joce 40143 2002-10-22 joce

466

joce 43506 2002-10-22 joce

467

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

468

mot topic date pseudo

469

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

470

mot topic date pseudo

471

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

472

mot topic date pseudo

473

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

474

mot topic date pseudo

475

joce 40143 2002-10-22 joce

476

joce 43506 2002-10-22 joce

477

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

478

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

479

joce 40143 2002-10-22 joce 1

480

joce 43506 2002-10-22 joce 1

481

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

482

mot topic date pseudo

483

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

484

mot topic date pseudo

485

joce 40143 2002-10-22 joce

486

joce 43506 2002-10-22 joce

487

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

488

mot topic date pseudo

489

joce 40143 2002-10-22 joce

490

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

491

mot topic date pseudo

492

joce 40143 2002-10-22 joce

493

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

494

mot topic date pseudo

495

joce 40143 2002-10-22 joce

496

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

497

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

498

joce 40143 2002-10-22 joce 1

499

joce 43506 2002-10-22 joce 0

500

drop table t1,t2;

501

CREATE TABLE `t1` (

502

`numeropost` mediumint(8) unsigned NOT NULL auto_increment,

503

`maxnumrep` int(10) unsigned NOT NULL default '0',

504

PRIMARY KEY (`numeropost`),

505

UNIQUE KEY `maxnumrep` (`maxnumrep`)

506

) ENGINE=MyISAM ROW_FORMAT=FIXED;

507

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

508

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

509

ERROR 21000: Subquery returns more than 1 row

510

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

511

ERROR 21000: Subquery returns more than 1 row

512

drop table t1;

513

create table t1 (a int);

514

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

515

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

516

517

518

519

520

drop table t1;

521

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

522

INSERT INTO t1 VALUES ();

523

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

524

ERROR 21000: Subquery returns more than 1 row

525

drop table t1;

526

CREATE TABLE `t1` (

527

`numeropost` mediumint(8) unsigned NOT NULL default '0',

528

`numreponse` int(10) unsigned NOT NULL auto_increment,

529

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

530

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

531

UNIQUE KEY `numreponse` (`numreponse`),

532

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

533

) ENGINE=MyISAM;

534

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

535

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

536

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

537

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

538

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

539

numreponse (SELECT numeropost FROM t1 HAVING numreponse=1)

540

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

541

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

542

ERROR 21000: Subquery returns more than 1 row

543

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

544

id select_type table type possible_keys key key_len ref rows filtered Extra

545

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

546

Warnings:

547

Note 1003 select max("test"."t1"."numreponse") AS "MAX(numreponse)" from "test"."t1" where ("test"."t1"."numeropost" = '1')

548

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

549

id select_type table type possible_keys key key_len ref rows filtered Extra

550

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

551

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

552

Warnings:

553

Note 1003 select '3' AS "numreponse" from "test"."t1" where (('1' = '1'))

554

drop table t1;

555

CREATE TABLE t1 (a int(1));

556

INSERT INTO t1 VALUES (1);

557

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

558

559

560

drop table t1;

561

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

562

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

563

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

564

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

565

select * from t1;

566

a b

567

0 10

568

1 11

569

2 12

570

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

571

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

572

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

573

ERROR 21000: Subquery returns more than 1 row

574

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

575

select * from t1;

576

a b

577

0 NULL

578

1 21

579

2 22

580

drop table t1, t2;

581

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

582

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

583

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

584

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

585

select * from t1;

586

a b

587

0 10

588

1 11

589

2 12

590

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

591

a b

592

2 12

593

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

594

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

595

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

596

ERROR 21000: Subquery returns more than 1 row

597

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

598

select * from t1;

599

a b

600

0 10

601

1 11

602

drop table t1, t2;

603

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

604

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

605

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

606

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

607

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

608

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

609

select * from t11;

610

a b

611

0 10

612

1 11

613

2 12

614

select * from t12;

615

a b

616

33 10

617

22 11

618

2 12

619

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

620

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

621

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

622

ERROR 21000: Subquery returns more than 1 row

623

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

624

select * from t11;

625

a b

626

0 10

627

1 11

628

select * from t12;

629

a b

630

33 10

631

22 11

632

drop table t11, t12, t2;

633

CREATE TABLE t1 (x int);

634

create table t2 (a int);

635

create table t3 (b int);

636

insert into t2 values (1);

637

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

638

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

639

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

640

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

641

ERROR 21000: Subquery returns more than 1 row

642

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

643

select * from t1;

644

645

646

insert into t2 values (1);

647

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

648

select * from t1;

649

650

651

652

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

653

select * from t1;

654

655

656

657

658

659

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

660

select * from t1;

661

662

663

664

665

666

667

668

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

669

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

670

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

671

select * from t1;

672

673

674

675

676

677

678

679

680

drop table t1, t2, t3;

681

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

682

create table t2 (a int);

683

create table t3 (a int);

684

insert into t2 values (1);

685

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

686

select * from t1;

687

x y

688

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

689

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

690

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

691

ERROR 21000: Subquery returns more than 1 row

692

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

693

select * from t1;

694

x y

695

1 2

696

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

697

select * from t1;

698

x y

699

1 3

700

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

701

select * from t1;

702

x y

703

1 3

704

4 1

705

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

706

select * from t1;

707

x y

708

1 3

709

4 2

710

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

711

select * from t1;

712

x y

713

1 3

714

4 2

715

2 1

716

drop table t1, t2, t3;

717

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

718

ERROR HY000: No tables used

719

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

720

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

721

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

722

723

724

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

725

id select_type table type possible_keys key key_len ref rows filtered Extra

726

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

727

Warnings:

728

Note 1249 Select 2 was reduced during optimization

729

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

730

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

731

732

733

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

734

735

736

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

737

id select_type table type possible_keys key key_len ref rows filtered Extra

738

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

739

Warnings:

740

Note 1249 Select 3 was reduced during optimization

741

Note 1249 Select 2 was reduced during optimization

742

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

743

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

744

id select_type table type possible_keys key key_len ref rows filtered Extra

745

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

746

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

747

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

748

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

749

Warnings:

750

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

751

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

752

753

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

754

755

756

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

757

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

758

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

759

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

760

SELECT * FROM t2;

761

762

763

764

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

765

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

766

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

767

ERROR 21000: Subquery returns more than 1 row

768

drop table t2, t1;

769

create table t1 (a int);

770

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

771

select 1 IN (SELECT * from t1);

772

1 IN (SELECT * from t1)

773

774

select 10 IN (SELECT * from t1);

775

10 IN (SELECT * from t1)

776

777

select NULL IN (SELECT * from t1);

778

NULL IN (SELECT * from t1)

779

NULL

780

update t1 set a=NULL where a=2;

781

select 1 IN (SELECT * from t1);

782

1 IN (SELECT * from t1)

783

784

select 3 IN (SELECT * from t1);

785

3 IN (SELECT * from t1)

786

787

select 10 IN (SELECT * from t1);

788

10 IN (SELECT * from t1)

789

NULL

790

select 1 > ALL (SELECT * from t1);

791

1 > ALL (SELECT * from t1)

792

793

select 10 > ALL (SELECT * from t1);

794

10 > ALL (SELECT * from t1)

795

NULL

796

select 1 > ANY (SELECT * from t1);

797

1 > ANY (SELECT * from t1)

798

NULL

799

select 10 > ANY (SELECT * from t1);

800

10 > ANY (SELECT * from t1)

801

802

drop table t1;

803

create table t1 (a varchar(20));

804

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

805

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

806

'A' IN (SELECT * from t1)

807

808

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

809

'XYZS' IN (SELECT * from t1)

810

811

select NULL IN (SELECT * from t1);

812

NULL IN (SELECT * from t1)

813

NULL

814

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

815

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

816

'A' IN (SELECT * from t1)

817

818

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

819

'DEF' IN (SELECT * from t1)

820

821

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

822

'XYZS' IN (SELECT * from t1)

823

NULL

824

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

825

'A' > ALL (SELECT * from t1)

826

827

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

828

'XYZS' > ALL (SELECT * from t1)

829

NULL

830

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

831

'A' > ANY (SELECT * from t1)

832

NULL

833

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

834

'XYZS' > ANY (SELECT * from t1)

835

836

drop table t1;

837

create table t1 (a float);

838

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

839

select 1.5 IN (SELECT * from t1);

840

1.5 IN (SELECT * from t1)

841

842

select 10.5 IN (SELECT * from t1);

843

10.5 IN (SELECT * from t1)

844

845

select NULL IN (SELECT * from t1);

846

NULL IN (SELECT * from t1)

847

NULL

848

update t1 set a=NULL where a=2.5;

849

select 1.5 IN (SELECT * from t1);

850

1.5 IN (SELECT * from t1)

851

852

select 3.5 IN (SELECT * from t1);

853

3.5 IN (SELECT * from t1)

854

855

select 10.5 IN (SELECT * from t1);

856

10.5 IN (SELECT * from t1)

857

NULL

858

select 1.5 > ALL (SELECT * from t1);

859

1.5 > ALL (SELECT * from t1)

860

861

select 10.5 > ALL (SELECT * from t1);

862

10.5 > ALL (SELECT * from t1)

863

NULL

864

select 1.5 > ANY (SELECT * from t1);

865

1.5 > ANY (SELECT * from t1)

866

NULL

867

select 10.5 > ANY (SELECT * from t1);

868

10.5 > ANY (SELECT * from t1)

869

870

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

871

id select_type table type possible_keys key key_len ref rows filtered Extra

872

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

873

Warnings:

874

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

875

Note 1249 Select 2 was reduced during optimization

876

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

877

select (select a+1) from t1;

878

(select a+1)

879

2.5

880

NULL

881

4.5

882

drop table t1;

883

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

884

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

885

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

886

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

887

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

888

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

889

1 1

890

2 1

891

3 1

892

4 0

893

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

894

id select_type table type possible_keys key key_len ref rows filtered Extra

895

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

896

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

897

Warnings:

898

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"

899

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

900

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

901

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

902

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

903

1 1

904

2 1

905

3 1

906

4 0

907

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

908

id select_type table type possible_keys key key_len ref rows filtered Extra

909

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

910

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

911

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

912

Warnings:

913

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"

914

drop table t1,t2,t3;

915

create table t1 (a float);

916

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

917

ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

918

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

919

ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

920

drop table t1;

921

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

922

create table t2 (a int);

923

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

924

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

925

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

926

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

927

1 1 a

928

2 0 b

929

NULL 0 NULL

930

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

931

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

932

1 0 a

933

2 1 b

934

NULL NULL NULL

935

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

936

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

937

1 0 a

938

2 0 b

939

NULL 0 NULL

940

drop table t1,t2;

941

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

942

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

943

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

944

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

945

946

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

947

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

948

949

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

950

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

951

952

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

953

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

954

955

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

956

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

957

958

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

959

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

960

961

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

962

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

963

964

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

965

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

966

967

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

968

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

969

970

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

971

ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

972

drop table t1;

973

CREATE TABLE t1 (a int(1));

974

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

975

id select_type table type possible_keys key key_len ref rows filtered Extra

976

1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found

977

2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found

978

Warnings:

979

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

980

EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;

981

id select_type table type possible_keys key key_len ref rows filtered Extra

982

1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found

983

2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found

984

Warnings:

985

Note 1003 select (select encrypt('test') AS "ENCRYPT('test')" from "test"."t1") AS "(SELECT ENCRYPT('test') FROM t1)" from "test"."t1"

986

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

987

id select_type table type possible_keys key key_len ref rows filtered Extra

988

1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found

989

2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found

990

Warnings:

991

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

992

drop table t1;

993

CREATE TABLE `t1` (

994

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

995

`topic` mediumint(8) unsigned NOT NULL default '0',

996

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

997

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

998

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

999

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

1000

KEY `topic` (`topic`)

1001

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

1002

CREATE TABLE `t2` (

1003

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

1004

`topic` mediumint(8) unsigned NOT NULL default '0',

1005

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

1006

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

1007

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

1008

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

1009

KEY `topic` (`topic`)

1010

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

1011

CREATE TABLE `t3` (

1012

`numeropost` mediumint(8) unsigned NOT NULL auto_increment,

1013

`maxnumrep` int(10) unsigned NOT NULL default '0',

1014

PRIMARY KEY (`numeropost`),

1015

UNIQUE KEY `maxnumrep` (`maxnumrep`)

1016

) ENGINE=MyISAM CHARSET=latin1;

1017

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

1018

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

1019

INSERT INTO t3 VALUES (1,1);

1020

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

1021

numeropost=topic);

1022

topic

1023

1024

select * from t1;

1025

mot topic date pseudo

1026

joce 1 0000-00-00 joce

1027

test 2 0000-00-00 test

1028

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

1029

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

1030

select * from t1;

1031

mot topic date pseudo

1032

joce 1 0000-00-00 joce

1033

drop table t1, t2, t3;

1034

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

1035

a (SELECT a)

1036

1 1

1037

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

1038

SHOW CREATE TABLE t1;

1039

Table Create Table

1040

t1 CREATE TABLE "t1" (

1041

"a" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP,

1042

"(SELECT 1)" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP

1043

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1044

drop table t1;

1045

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

1046

SHOW CREATE TABLE t1;

1047

Table Create Table

1048

t1 CREATE TABLE "t1" (

1049

"a" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP,

1050

"(SELECT a)" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP

1051

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1052

drop table t1;

1053

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

1054

SHOW CREATE TABLE t1;

1055

Table Create Table

1056

t1 CREATE TABLE "t1" (

1057

"a" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP,

1058

"(SELECT a+0)" int(3) NOT NULL ON UPDATE CURRENT_TIMESTAMP

1059

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1060

drop table t1;

1061

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

1062

select * from t1;

1063

1064

1065

SHOW CREATE TABLE t1;

1066

Table Create Table

1067

t1 CREATE TABLE "t1" (

1068

"a" bigint(20) NOT NULL ON UPDATE CURRENT_TIMESTAMP

1069

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1070

drop table t1;

1071

create table t1 (a int);

1072

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

1073

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

1074

from t1;

1075

id select_type table type possible_keys key key_len ref rows filtered Extra

1076

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

1077

2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1078

3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1079

Warnings:

1080

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"

1081

drop table t1;

1082

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

1083

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

1084

CREATE TABLE t1 (

1085

ID int(11) NOT NULL auto_increment,

1086

name char(35) NOT NULL default '',

1087

t2 char(3) NOT NULL default '',

1088

District char(20) NOT NULL default '',

1089

Population int(11) NOT NULL default '0',

1090

PRIMARY KEY (ID)

1091

) ENGINE=MyISAM;

1092

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

1093

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

1094

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

1095

CREATE TABLE t2 (

1096

Code char(3) NOT NULL default '',

1097

Name char(52) NOT NULL default '',

1098

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

1099

Region char(26) NOT NULL default '',

1100

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

1101

IndepYear smallint(6) default NULL,

1102

Population int(11) NOT NULL default '0',

1103

LifeExpectancy float(3,1) default NULL,

1104

GNP float(10,2) default NULL,

1105

GNPOld float(10,2) default NULL,

1106

LocalName char(45) NOT NULL default '',

1107

GovernmentForm char(45) NOT NULL default '',

1108

HeadOfState char(60) default NULL,

1109

Capital int(11) default NULL,

1110

Code2 char(2) NOT NULL default '',

1111

PRIMARY KEY (Code)

1112

) ENGINE=MyISAM;

1113

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

1114

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

1115

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

1116

Continent Name Population

1117

Oceania Sydney 3276207

1118

drop table t1, t2;

1119

CREATE TABLE `t1` (

1120

`id` mediumint(8) unsigned NOT NULL auto_increment,

1121

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

1122

PRIMARY KEY (`id`),

1123

UNIQUE KEY `pseudo` (`pseudo`)

1124

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

1125

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

1126

SELECT 0 IN (SELECT 1 FROM t1 a);

1127

0 IN (SELECT 1 FROM t1 a)

1128

1129

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

1130

id select_type table type possible_keys key key_len ref rows filtered Extra

1131

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

1132

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

1133

Warnings:

1134

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

1135

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

1136

SELECT 0 IN (SELECT 1 FROM t1 a);

1137

0 IN (SELECT 1 FROM t1 a)

1138

1139

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

1140

id select_type table type possible_keys key key_len ref rows filtered Extra

1141

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

1142

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

1143

Warnings:

1144

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

1145

drop table t1;

1146

CREATE TABLE `t1` (

1147

`i` int(11) NOT NULL default '0',

1148

PRIMARY KEY (`i`)

1149

) ENGINE=MyISAM CHARSET=latin1;

1150

INSERT INTO t1 VALUES (1);

1151

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

1152

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

1153

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

1154

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

1155

select * from t1;

1156

1157

1158

drop table t1;

1159

CREATE TABLE t1 (

1160

id int(11) default NULL

1161

) ENGINE=MyISAM CHARSET=latin1;

1162

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

1163

CREATE TABLE t2 (

1164

id int(11) default NULL,

1165

name varchar(15) default NULL

1166

) ENGINE=MyISAM CHARSET=latin1;

1167

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

1168

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

1169

select * from t2;

1170

id name

1171

4 vita

1172

1 lenka

1173

2 lenka

1174

1 lenka

1175

drop table t1,t2;

1176

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

1177

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

1178

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

1179

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

1180

NULL

1181

drop table t1;

1182

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

1183

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

1184

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

1185

id select_type table type possible_keys key key_len ref rows filtered Extra

1186

1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition

1187

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

1188

Warnings:

1189

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

1190

drop table t1;

1191

CREATE TABLE t1 (

1192

ID int(10) unsigned NOT NULL auto_increment,

1193

SUB_ID int(3) unsigned NOT NULL default '0',

1194

REF_ID int(10) unsigned default NULL,

1195

REF_SUB int(3) unsigned default '0',

1196

PRIMARY KEY (ID,SUB_ID),

1197

UNIQUE KEY t1_PK (ID,SUB_ID),

1198

KEY t1_FK (REF_ID,REF_SUB),

1199

KEY t1_REFID (REF_ID)

1200

) ENGINE=MyISAM CHARSET=cp1251;

1201

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

1202

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

1203

REF_ID

1204

DROP TABLE t1;

1205

create table t1 (a int, b int);

1206

create table t2 (a int, b int);

1207

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

1208

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

1209

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

1210

Warnings:

1211

Error 1242 Subquery returns more than 1 row

1212

select * from t1;

1213

a b

1214

1 1

1215

2 NULL

1216

3 1

1217

drop table t1, t2;

1218

CREATE TABLE `t1` (

1219

`id` mediumint(8) unsigned NOT NULL auto_increment,

1220

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

1221

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

1222

PRIMARY KEY (`id`),

1223

UNIQUE KEY `email` (`email`),

1224

UNIQUE KEY `pseudo` (`pseudo`)

1225

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

1226

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

1227

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

1228

a b

1229

test test

1230

test1 test1

1231

drop table if exists t1;

1232

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

1233

1234

1235

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

1236

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

1237

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

1238

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

1239

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

1240

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

1241

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

1242

1243

1244

1245

1246

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

1247

id select_type table type possible_keys key key_len ref rows filtered Extra

1248

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

1249

2 SUBQUERY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index

1250

Warnings:

1251

Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a","test"."t2"."a" in ( <materialize> (select "test"."t1"."a" AS "a" from "test"."t1" ), <primary_index_lookup>("test"."t2"."a" in <temporary table> on distinct_key)))

1252

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

1253

1254

1255

1256

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

1257

id select_type table type possible_keys key key_len ref rows filtered Extra

1258

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

1259

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

1260

Warnings:

1261

Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a","test"."t2"."a" in ( <materialize> (select "test"."t1"."a" AS "a" from "test"."t1" where ("test"."t1"."b" <> 30) ), <primary_index_lookup>("test"."t2"."a" in <temporary table> on distinct_key)))

1262

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

1263

1264

1265

1266

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

1267

id select_type table type possible_keys key key_len ref rows filtered Extra

1268

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

1269

2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using index

1270

2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer

1271

Warnings:

1272

Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a","test"."t2"."a" in ( <materialize> (select "test"."t1"."a" AS "a" from "test"."t1" join "test"."t3" where ("test"."t1"."b" = "test"."t3"."a") ), <primary_index_lookup>("test"."t2"."a" in <temporary table> on distinct_key)))

1273

drop table t1, t2, t3;

1274

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

1275

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

1276

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

1277

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

1278

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

1279

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

1280

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

1281

1282

1283

1284

1285

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

1286

id select_type table type possible_keys key key_len ref rows filtered Extra

1287

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

1288

2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using index

1289

Warnings:

1290

1291

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

1292

1293

1294

1295

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

1296

id select_type table type possible_keys key key_len ref rows filtered Extra

1297

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

1298

2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index

1299

Warnings:

1300

Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a","test"."t2"."a" in ( <materialize> (select "test"."t1"."a" AS "a" from "test"."t1" where ("test"."t1"."b" <> 30) ), <primary_index_lookup>("test"."t2"."a" in <temporary table> on distinct_key)))

1301

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

1302

1303

1304

1305

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

1306

id select_type table type possible_keys key key_len ref rows filtered Extra

1307

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

1308

2 SUBQUERY t3 index a a 5 NULL 3 100.00 Using index

1309

2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index; Using join buffer

1310

Warnings:

1311

Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a","test"."t2"."a" in ( <materialize> (select "test"."t1"."a" AS "a" from "test"."t1" join "test"."t3" where ("test"."t1"."b" = "test"."t3"."a") ), <primary_index_lookup>("test"."t2"."a" in <temporary table> on distinct_key)))

1312

insert into t1 values (3,31);

1313

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

1314

1315

1316

1317

1318

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

1319

1320

1321

1322

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

1323

id select_type table type possible_keys key key_len ref rows filtered Extra

1324

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

1325

2 SUBQUERY t1 index NULL a 10 NULL 10005 100.00 Using where; Using index

1326

Warnings:

1327

Note 1003 select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a","test"."t2"."a" in ( <materialize> (select "test"."t1"."a" AS "a" from "test"."t1" where ("test"."t1"."b" <> 30) ), <primary_index_lookup>("test"."t2"."a" in <temporary table> on distinct_key)))

1328

drop table t1, t2, t3;

1329

create table t1 (a int, b int);

1330

create table t2 (a int, b int);

1331

create table t3 (a int, b int);

1332

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

1333

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

1334

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

1335

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;

1336

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)

1337

3 1

1338

2 2

1339

1 2

1340

drop table t1,t2,t3;

1341

create table t1 (s1 int);

1342

create table t2 (s1 int);

1343

insert into t1 values (1);

1344

insert into t2 values (1);

1345

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

1346

1347

1348

drop table t1,t2;

1349

create table t1 (s1 int);

1350

create table t2 (s1 int);

1351

insert into t1 values (1);

1352

insert into t2 values (1);

1353

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

1354

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

1355

DROP TABLE t1, t2;

1356

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

1357

s2 CHAR(5) COLLATE latin1_swedish_ci);

1358

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

1359

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

1360

ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'

1361

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

1362

ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'

1363

drop table t1;

1364

create table t1(toid int,rd int);

1365

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

1366

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

1367

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

1368

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

1369

userid pmtotal pmnew calc_total calc_new

1370

1 0 0 9 3

1371

2 0 0 4 2

1372

drop table t1, t2;

1373

create table t1 (s1 char(5));

1374

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

1375

ERROR 21000: Operand should contain 1 column(s)

1376

insert into t1 values ('tttt');

1377

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

1378

1379

tttt

1380

explain extended (select * from t1);

1381

id select_type table type possible_keys key key_len ref rows filtered Extra

1382

1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00

1383

Warnings:

1384

Note 1003 (select 'tttt' AS "s1" from "test"."t1")

1385

(select * from t1);

1386

1387

tttt

1388

drop table t1;

1389

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

1390

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

1391

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

1392

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

1393

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

1394

s1 s1 NOT IN (SELECT s1 FROM t2)

1395

a1 0

1396

a2 0

1397

a3 1

1398

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

1399

s1 s1 = ANY (SELECT s1 FROM t2)

1400

a1 1

1401

a2 1

1402

a3 0

1403

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

1404

s1 s1 <> ALL (SELECT s1 FROM t2)

1405

a1 0

1406

a2 0

1407

a3 1

1408

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

1409

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

1410

a1 0

1411

a2 1

1412

a3 1

1413

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

1414

id select_type table type possible_keys key key_len ref rows filtered Extra

1415

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

1416

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

1417

Warnings:

1418

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"

1419

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

1420

id select_type table type possible_keys key key_len ref rows filtered Extra

1421

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

1422

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

1423

Warnings:

1424

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"

1425

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

1426

id select_type table type possible_keys key key_len ref rows filtered Extra

1427

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

1428

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

1429

Warnings:

1430

1431

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

1432

id select_type table type possible_keys key key_len ref rows filtered Extra

1433

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

1434

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

1435

Warnings:

1436

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"

1437

drop table t1,t2;

1438

create table t2 (a int, b int);

1439

create table t3 (a int);

1440

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

1441

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

1442

1443

1444

1445

1446

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

1447

id select_type table type possible_keys key key_len ref rows filtered Extra

1448

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

1449

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

1450

Warnings:

1451

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

1452

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

1453

1454

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

1455

id select_type table type possible_keys key key_len ref rows filtered Extra

1456

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

1457

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

1458

Warnings:

1459

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

1460

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

1461

1462

1463

1464

1465

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

1466

id select_type table type possible_keys key key_len ref rows filtered Extra

1467

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

1468

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

1469

Warnings:

1470

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

1471

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

1472

1473

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

1474

id select_type table type possible_keys key key_len ref rows filtered Extra

1475

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

1476

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

1477

Warnings:

1478

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

1479

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

1480

1481

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

1482

id select_type table type possible_keys key key_len ref rows filtered Extra

1483

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

1484

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

1485

Warnings:

1486

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

1487

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

1488

1489

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

1490

id select_type table type possible_keys key key_len ref rows filtered Extra

1491

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

1492

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

1493

Warnings:

1494

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

1495

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

1496

1497

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

1498

id select_type table type possible_keys key key_len ref rows filtered Extra

1499

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

1500

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

1501

Warnings:

1502

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

1503

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

1504

1505

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

1506

id select_type table type possible_keys key key_len ref rows filtered Extra

1507

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

1508

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

1509

Warnings:

1510

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

1511

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

1512

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

1513

1514

1515

1516

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

1517

id select_type table type possible_keys key key_len ref rows filtered Extra

1518

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

1519

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

1520

Warnings:

1521

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

1522

drop table t2, t3;

1523

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

1524

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

1525

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

1526

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

1527

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

1528

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

1529

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

1530

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

1531

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;

1532

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

1533

-1 Valid 1

1534

-1 Valid 2 1

1535

-1 Should Not Return 0

1536

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;

1537

dbid name

1538

-1 Valid

1539

-1 Valid 2

1540

drop table t1,t2,t3,t4;

1541

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

1542

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

1543

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

1544

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

1545

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

1546

ERROR 21000: Operand should contain 3 column(s)

1547

DROP TABLE t1,t2;

1548

create table t1 (s1 char);

1549

insert into t1 values ('e');

1550

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

1551

1552

1553

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

1554

1555

1556

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

1557

id select_type table type possible_keys key key_len ref rows filtered Extra

1558

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00

1559

2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

1560

3 UNION t1 system NULL NULL NULL NULL 1 100.00

1561

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

1562

Warnings:

1563

Note 1003 select 'e' AS "s1" from "test"."t1" where 1

1564

drop table t1;

1565

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

1566

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

1567

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

1568

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

1569

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;

1570

phone code

1571

69294728265 6

1572

18621828126 1862

1573

89356874041 NULL

1574

95895001874 NULL

1575

drop table t1, t2;

1576

create table t1 (s1 int);

1577

create table t2 (s1 int);

1578

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

1579

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

1580

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

1581

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

1582

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

1583

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

1584

drop table t1, t2;

1585

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

1586

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

1587

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

1588

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

1589

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

1590

COLC

1591

DROP TABLE t1, t2;

1592

CREATE TABLE t1 (a int(1));

1593

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

1594

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

1595

(SELECT a)

1596

1597

1598

1599

1600

1601

DROP TABLE t1;

1602

CREATE TABLE `t1` (

1603

`id` int(11) NOT NULL auto_increment,

1604

`id_cns` tinyint(3) unsigned NOT NULL default '0',

1605

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

1606

`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',

1607

`particolare` mediumint(8) unsigned NOT NULL default '0',

1608

`generale` mediumint(8) unsigned NOT NULL default '0',

1609

`bis` tinyint(3) unsigned NOT NULL default '0',

1610

PRIMARY KEY (`id`),

1611

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

1612

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

1613

);

1614

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

1615

CREATE TABLE `t2` (

1616

`id` tinyint(3) unsigned NOT NULL auto_increment,

1617

`max_anno_dep` smallint(6) unsigned NOT NULL default '0',

1618

PRIMARY KEY (`id`)

1619

);

1620

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

1621

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;

1622

id max_anno_dep PIPPO

1623

16 1987 1

1624

50 1990 0

1625

51 1990 NULL

1626

DROP TABLE t1, t2;

1627

create table t1 (a int);

1628

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

1629

SET SQL_SELECT_LIMIT=1;

1630

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

1631

sum(a)

1632

1633

select 2 in (select * from t1);

1634

2 in (select * from t1)

1635

1636

SET SQL_SELECT_LIMIT=default;

1637

drop table t1;

1638

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

1639

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

1640

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

1641

a b

1642

1 1

1643

1 2

1644

1 3

1645

DROP TABLE t1;

1646

create table t1(val varchar(10));

1647

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

1648

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

1649

count(*)

1650

1651

drop table t1;

1652

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

1653

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

1654

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

1655

id text

1656

8 text8

1657

9 text9

1658

10 text10

1659

11 text11

1660

12 text12

1661

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

1662

id text

1663

8 text8

1664

9 text9

1665

10 text10

1666

11 text11

1667

12 text12

1668

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

1669

id select_type table type possible_keys key key_len ref rows filtered Extra

1670

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

1671

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

1672

Warnings:

1673

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

1674

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

1675

id select_type table type possible_keys key key_len ref rows filtered Extra

1676

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

1677

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

1678

Warnings:

1679

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

1680

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

1681

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

1682

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

1683

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

1684

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

1685

id text id text id text

1686

1 text1 1 text1 1 text1

1687

2 text2 2 text2 2 text2

1688

3 text3 3 text3 3 text3

1689

4 text4 4 text4 4 text4

1690

5 text5 5 text5 5 text5

1691

6 text6 6 text6 6 text6

1692

7 text7 7 text7 7 text7

1693

8 text8 8 text8 8 text8

1694

9 text9 9 text9 9 text9

1695

10 text10 10 text10 10 text10

1696

11 text11 11 text1 11 text11

1697

12 text12 12 text2 12 text12

1698

1000 text1000 NULL NULL 1000 text1000

1699

1001 text1001 NULL NULL 1000 text1000

1700

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

1701

id select_type table type possible_keys key key_len ref rows filtered Extra

1702

1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00

1703

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

1704

1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using index condition

1705

Warnings:

1706

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

1707

drop table t1,t2;

1708

create table t1 (a int);

1709

insert into t1 values (1);

1710

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

1711

id select_type table type possible_keys key key_len ref rows Extra

1712

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

1713

2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 1

1714

drop table t1;

1715

create table t1(id int);

1716

create table t2(id int);

1717

create table t3(flag int);

1718

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

1719

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

1720

drop table t1,t2,t3;

1721

CREATE TABLE t1 (id INT);

1722

CREATE TABLE t2 (id INT);

1723

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

1724

INSERT INTO t2 VALUES (1);

1725

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

1726

id c

1727

1 1

1728

2 0

1729

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

1730

id c

1731

1 1

1732

2 0

1733

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;

1734

id c

1735

1 1

1736

2 0

1737

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;

1738

id c

1739

1 1

1740

2 0

1741

DROP TABLE t1,t2;

1742

CREATE TABLE t1 ( a int, b int );

1743

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

1744

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

1745

1746

1747

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

1748

1749

1750

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

1751

1752

1753

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

1754

1755

1756

1757

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

1758

1759

1760

1761

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

1762

1763

1764

1765

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

1766

1767

1768

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

1769

1770

1771

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

1772

1773

1774

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

1775

1776

1777

1778

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

1779

1780

1781

1782

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

1783

1784

1785

1786

ALTER TABLE t1 ADD INDEX (a);

1787

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

1788

1789

1790

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

1791

1792

1793

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

1794

1795

1796

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

1797

1798

1799

1800

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

1805

1806

1807

1808

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

1809

1810

1811

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

1812

1813

1814

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

1815

1816

1817

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

1818

1819

1820

1821

SELECT a FROM t1 WHERE a <= ALL ( 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 );

1826

1827

1828

1829

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

1830

1831

1832

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

1833

1834

1835

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

1836

1837

1838

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

1839

1840

1841

1842

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

1847

1848

1849

1850

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

1851

1852

1853

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

1854

1855

1856

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

1857

1858

1859

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

1860

1861

1862

1863

SELECT a FROM t1 WHERE a <= ALL (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);

1868

1869

1870

1871

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

1872

1873

1874

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

1875

1876

1877

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

1878

1879

1880

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

1881

1882

1883

1884

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

1885

1886

1887

1888

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

1889

1890

1891

1892

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

1893

1894

1895

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

1896

1897

1898

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

1899

1900

1901

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

1902

1903

1904

1905

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

1906

1907

1908

1909

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

1910

1911

1912

1913

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

1914

1915

1916

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

1917

1918

1919

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

1920

1921

1922

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

1923

1924

1925

1926

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

1927

1928

1929

1930

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

1931

1932

1933

1934

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

1935

1936

1937

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

1938

1939

1940

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

1941

1942

1943

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

1944

1945

1946

1947

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

1948

1949

1950

1951

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

1952

1953

1954

1955

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

1956

ERROR 21000: Operand should contain 1 column(s)

1957

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

1958

ERROR 21000: Operand should contain 1 column(s)

1959

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

1960

ERROR 21000: Operand should contain 1 column(s)

1961

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

1962

ERROR 21000: Operand should contain 1 column(s)

1963

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

1964

ERROR 21000: Operand should contain 1 column(s)

1965

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

1966

ERROR 21000: Operand should contain 1 column(s)

1967

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

1968

ERROR 21000: Operand should contain 1 column(s)

1969

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

1970

ERROR 21000: Operand should contain 1 column(s)

1971

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

1972

ERROR 21000: Operand should contain 2 column(s)

1973

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

1974

ERROR 21000: Operand should contain 1 column(s)

1975

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

1976

1977

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

1978

ERROR 21000: Operand should contain 2 column(s)

1979

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

1980

ERROR 21000: Operand should contain 1 column(s)

1981

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

1982

1983

1984

1985

1986

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

1987

1988

1989

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

1990

1991

1992

1993

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

1994

1995

1996

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

1997

1998

1999

2000

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

2001

2002

2003

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

2004

2005

2006

2007

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

2008

2009

2010

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

2011

2012

2013

2014

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

2015

2016

2017

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

2018

2019

2020

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

2021

2022

2023

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

2024

2025

2026

2027

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

2028

2029

2030

2031

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

2032

2033

2034

2035

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

2036

2037

2038

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

2039

2040

2041

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

2042

2043

2044

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

2045

2046

2047

2048

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

2049

2050

2051

2052

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

2053

2054

2055

2056

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

2057

2058

2059

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

2060

2061

2062

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

2063

2064

2065

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

2066

2067

2068

2069

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

2070

2071

2072

2073

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

2074

2075

2076

2077

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

2078

2079

2080

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

2081

2082

2083

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

2084

2085

2086

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

2087

2088

2089

2090

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

2091

2092

2093

2094

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

2095

2096

2097

2098

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

2099

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

2100

2101

2102

2103

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

2104

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

2105

2106

2107

2108

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

2109

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

2110

2111

2112

2113

DROP TABLE t1;

2114

CREATE TABLE t1 ( a double, b double );

2115

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

2116

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

2117

2118

2119

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

2120

2121

2122

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

2123

2124

2125

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

2126

2127

2128

2129

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

2130

2131

2132

2133

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

2134

2135

2136

2137

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

2138

2139

2140

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

2141

2142

2143

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

2144

2145

2146

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

2147

2148

2149

2150

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

2151

2152

2153

2154

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

2155

2156

2157

2158

DROP TABLE t1;

2159

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

2160

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

2161

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

2162

2163

2164

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

2165

2166

2167

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

2168

2169

2170

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

2171

2172

2173

2174

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

2175

2176

2177

2178

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

2179

2180

2181

2182

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

2183

2184

2185

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

2186

2187

2188

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

2189

2190

2191

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

2192

2193

2194

2195

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

2196

2197

2198

2199

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

2200

2201

2202

2203

DROP TABLE t1;

2204

create table t1 (a int, b int);

2205

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

2206

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

2207

a b

2208

1 2

2209

3 4

2210

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

2211

id select_type table type possible_keys key key_len ref rows filtered Extra

2212

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

2213

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

2214

Warnings:

2215

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

2216

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

2217

drop table t1;

2218

CREATE TABLE t1 (t1_a int);

2219

INSERT INTO t1 VALUES (1);

2220

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

2221

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

2222

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

2223

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

2224

t1_a t2_a t2_b

2225

1 1 2

2226

DROP TABLE t1, t2;

2227

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

2228

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

2229

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

2230

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

2231

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

2232

id name id pet

2233

1 Tim 1 Fido

2234

2 Rebecca 2 Spot

2235

3 NULL 3 Felix

2236

drop table t1,t2;

2237

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

2238

insert into t1 values (1);

2239

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

2240

insert into t2 values (1,2);

2241

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

2242

a C

2243

1 1

2244

drop table t1,t2;

2245

create table t1 (a int not null auto_increment primary key, b varchar(40), fulltext(b));

2246

insert into t1 (b) values ('ball'),('ball games'), ('games'), ('foo'), ('foobar'), ('Serg'), ('Sergei'),('Georg'), ('Patrik'),('Hakan');

2247

create table t2 (a int);

2248

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

2249

select a,b from t1 where match(b) against ('Ball') > 0;

2250

a b

2251

1 ball

2252

2 ball games

2253

select a from t2 where a in (select a from t1 where match(b) against ('Ball') > 0);

2254

2255

2256

2257

drop table t1,t2;

2258

CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin);

2259

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

2260

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

2261

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

2262

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

2263

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

2264

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

2265

IZAVORGANG_ID

2266

D0000000001

2267

drop table t1;

2268

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

2269

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

2270

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

2271

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

2272

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

2273

aid bid

2274

1 1

2275

2 1

2276

alter table t2 drop primary key;

2277

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

2278

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

2279

aid bid

2280

1 1

2281

2 1

2282

alter table t2 drop key KEY1;

2283

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

2284

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

2285

aid bid

2286

1 1

2287

2 1

2288

drop table t1,t2;

2289

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

2290

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

2291

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

2292

howmanyvalues count(*)

2293

1 1

2294

2 2

2295

3 3

2296

4 4

2297

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

2298

howmanyvalues mycount

2299

1 1

2300

2 2

2301

3 3

2302

4 4

2303

CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);

2304

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

2305

howmanyvalues mycount

2306

1 1

2307

2 2

2308

3 3

2309

4 4

2310

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

2311

howmanyvalues mycount

2312

1 1

2313

2 2

2314

3 3

2315

4 4

2316

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

2317

howmanyvalues mycount

2318

1 1

2319

2 1

2320

3 1

2321

4 1

2322

drop table t1;

2323

create table t1 (x int);

2324

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;

2325

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

2326

drop table t1;

2327

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

2328

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

2329

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

2330

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

2331

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;

2332

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

2333

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;

2334

2335

700

2336

NULL

2337

drop tables t1,t2;

2338

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

2339

create table t1 (a int, b int);

2340

create table t2 (a int, b int);

2341

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

2342

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

2343

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

2344

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

2345

1 1 3

2346

1 2 1

2347

1 3 NULL

2348

2 4 NULL

2349

2 5 NULL

2350

drop table t1, t2;

2351

create table t1 (id int);

2352

create table t2 (id int, body text, fulltext (body));

2353

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

2354

insert into t2 values (1,'test'), (2,'mysql'), (3,'test'), (4,'test');

2355

select count(distinct id) from t1 where id in (select id from t2 where match(body) against ('mysql' in boolean mode));

2356

count(distinct id)

2357

2358

drop table t2,t1;

2359

create table t1 (s1 int,s2 int);

2360

insert into t1 values (20,15);

2361

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

2362

s1 s2

2363

drop table t1;

2364

create table t1 (s1 int);

2365

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

2366

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

2367

2368

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

2369

s1 s1 < all (select s1 from t1)

2370

1 0

2371

NULL NULL

2372

drop table t1;

2373

CREATE TABLE t1 (

2374

Code char(3) NOT NULL default '',

2375

Name char(52) NOT NULL default '',

2376

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

2377

Region char(26) NOT NULL default '',

2378

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

2379

IndepYear smallint(6) default NULL,

2380

Population int(11) NOT NULL default '0',

2381

LifeExpectancy float(3,1) default NULL,

2382

GNP float(10,2) default NULL,

2383

GNPOld float(10,2) default NULL,

2384

LocalName char(45) NOT NULL default '',

2385

GovernmentForm char(45) NOT NULL default '',

2386

HeadOfState char(60) default NULL,

2387

Capital int(11) default NULL,

2388

Code2 char(2) NOT NULL default ''

2389

) ENGINE=MyISAM;

2390

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

2391

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

2392

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

2393

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

2394

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

2395

SELECT DISTINCT Continent AS c FROM t1 outr WHERE

2396

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

2397

Population < 200);

2398

2399

Oceania

2400

drop table t1;

2401

create table t1 (a1 int);

2402

create table t2 (b1 int);

2403

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

2404

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

2405

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

2406

2407

drop table t1,t2;

2408

create table t1 (a integer, b integer);

2409

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

2410

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

2411

NULL

2412

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

2413

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

2414

NULL

2415

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

2416

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

2417

2418

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

2419

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

2420

2421

drop table t1;

2422

create table t1 (a integer, b integer);

2423

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

2424

ERROR 21000: Operand should contain 2 column(s)

2425

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

2426

ERROR 21000: Operand should contain 2 column(s)

2427

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

2428

ERROR 21000: Operand should contain 1 column(s)

2429

drop table t1;

2430

create table t1 (a integer);

2431

insert into t1 values (1);

2432

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

2433

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

2434

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

2435

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

2436

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

2437

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

2438

1 1

2439

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

2440

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

2441

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

2442

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

2443

drop table t1;

2444

CREATE TABLE t1 (

2445

categoryId int(11) NOT NULL,

2446

courseId int(11) NOT NULL,

2447

startDate datetime NOT NULL,

2448

endDate datetime NOT NULL,

2449

createDate datetime NOT NULL,

2450

modifyDate timestamp NOT NULL,

2451

attributes text NOT NULL

2452

);

2453

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

2454

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

2455

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

2456

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

2457

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

2458

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

2459

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

2460

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

2461

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

2462

CREATE TABLE t2 (

2463

userId int(11) NOT NULL,

2464

courseId int(11) NOT NULL,

2465

date datetime NOT NULL

2466

);

2467

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

2468

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

2469

(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),

2470

(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),

2471

(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),

2472

(5141,89,'2004-10-22'),(5141,51,'2004-10-26');

2473

CREATE TABLE t3 (

2474

groupId int(11) NOT NULL,

2475

parentId int(11) NOT NULL,

2476

startDate datetime NOT NULL,

2477

endDate datetime NOT NULL,

2478

createDate datetime NOT NULL,

2479

modifyDate timestamp NOT NULL,

2480

ordering int(11)

2481

);

2482

INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);

2483

CREATE TABLE t4 (

2484

id int(11) NOT NULL,

2485

groupTypeId int(11) NOT NULL,

2486

groupKey varchar(50) NOT NULL,

2487

name text,

2488

ordering int(11),

2489

description text,

2490

createDate datetime NOT NULL,

2491

modifyDate timestamp NOT NULL

2492

);

2493

INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),

2494

(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');

2495

CREATE TABLE t5 (

2496

userId int(11) NOT NULL,

2497

groupId int(11) NOT NULL,

2498

createDate datetime NOT NULL,

2499

modifyDate timestamp NOT NULL

2500

);

2501

INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');

2502

select

2503

count(distinct t2.userid) pass,

2504

groupstuff.*,

2505

count(t2.courseid) crse,

2506

t1.categoryid,

2507

t2.courseid,

2508

date_format(date, '%b%y') as colhead

2509

from t2

2510

join t1 on t2.courseid=t1.courseid

2511

join

2512

(

2513

select

2514

t5.userid,

2515

parentid,

2516

parentgroup,

2517

childid,

2518

groupname,

2519

grouptypeid

2520

from t5

2521

join

2522

(

2523

select t4.id as parentid,

2524

t4.name as parentgroup,

2525

t4.id as childid,

2526

t4.name as groupname,

2527

t4.grouptypeid

2528

from t4

2529

) as gin on t5.groupid=gin.childid

2530

) as groupstuff on t2.userid = groupstuff.userid

2531

group by

2532

groupstuff.groupname, colhead , t2.courseid;

2533

pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead

2534

1 5141 12 group2 12 group2 5 1 5 12 Aug04

2535

1 5141 12 group2 12 group2 5 1 1 41 Aug04

2536

1 5141 12 group2 12 group2 5 1 2 52 Aug04

2537

1 5141 12 group2 12 group2 5 1 2 53 Aug04

2538

1 5141 12 group2 12 group2 5 1 3 51 Oct04

2539

1 5141 12 group2 12 group2 5 1 1 86 Oct04

2540

1 5141 12 group2 12 group2 5 1 1 87 Oct04

2541

1 5141 12 group2 12 group2 5 1 2 88 Oct04

2542

1 5141 12 group2 12 group2 5 1 2 89 Oct04

2543

drop table t1, t2, t3, t4, t5;

2544

create table t1 (a int);

2545

insert into t1 values (1), (2), (3);

2546

SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);

2547

2548

2549

2550

2551

drop table t1;

2552

create table t1 (a int, b int);

2553

insert into t1 values (1,2);

2554

select 1 = (select * from t1);

2555

ERROR 21000: Operand should contain 1 column(s)

2556

select (select * from t1) = 1;

2557

ERROR 21000: Operand should contain 2 column(s)

2558

select (1,2) = (select a from t1);

2559

ERROR 21000: Operand should contain 2 column(s)

2560

select (select a from t1) = (1,2);

2561

ERROR 21000: Operand should contain 1 column(s)

2562

select (1,2,3) = (select * from t1);

2563

ERROR 21000: Operand should contain 3 column(s)

2564

select (select * from t1) = (1,2,3);

2565

ERROR 21000: Operand should contain 2 column(s)

2566

drop table t1;

2567

CREATE TABLE `t1` (

2568

`itemid` bigint(20) unsigned NOT NULL auto_increment,

2569

`sessionid` bigint(20) unsigned default NULL,

2570

`time` int(10) unsigned NOT NULL default '0',

2571

`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT

2572

NULL default '',

2573

`data` text collate latin1_general_ci NOT NULL,

2574

PRIMARY KEY (`itemid`)

2575

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2576

INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');

2577

CREATE TABLE `t2` (

2578

`sessionid` bigint(20) unsigned NOT NULL auto_increment,

2579

`pid` int(10) unsigned NOT NULL default '0',

2580

`date` int(10) unsigned NOT NULL default '0',

2581

`ip` varchar(15) collate latin1_general_ci NOT NULL default '',

2582

PRIMARY KEY (`sessionid`)

2583

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2584

INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');

2585

SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;

2586

ip count( e.itemid )

2587

10.10.10.1 1

2588

drop tables t1,t2;

2589

create table t1 (fld enum('0','1'));

2590

insert into t1 values ('1');

2591

select * from (select max(fld) from t1) as foo;

2592

max(fld)

2593

2594

drop table t1;

2595

CREATE TABLE t1 (one int, two int, flag char(1));

2596

CREATE TABLE t2 (one int, two int, flag char(1));

2597

INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');

2598

INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');

2599

SELECT * FROM t1

2600

WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');

2601

one two flag

2602

5 6 N

2603

7 8 N

2604

SELECT * FROM t1

2605

WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');

2606

one two flag

2607

5 6 N

2608

7 8 N

2609

insert into t2 values (null,null,'N');

2610

insert into t2 values (null,3,'0');

2611

insert into t2 values (null,5,'0');

2612

insert into t2 values (10,null,'0');

2613

insert into t1 values (10,3,'0');

2614

insert into t1 values (10,5,'0');

2615

insert into t1 values (10,10,'0');

2616

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;

2617

one two test

2618

1 2 NULL

2619

2 3 NULL

2620

3 4 NULL

2621

5 6 1

2622

7 8 1

2623

10 3 NULL

2624

10 5 NULL

2625

10 10 NULL

2626

SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');

2627

one two

2628

5 6

2629

7 8

2630

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;

2631

one two test

2632

1 2 NULL

2633

2 3 NULL

2634

3 4 NULL

2635

5 6 1

2636

7 8 1

2637

10 3 NULL

2638

10 5 NULL

2639

10 10 NULL

2640

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;

2641

one two test

2642

1 2 0

2643

2 3 NULL

2644

3 4 0

2645

5 6 0

2646

7 8 0

2647

10 3 NULL

2648

10 5 NULL

2649

10 10 NULL

2650

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;

2651

one two test

2652

1 2 0

2653

2 3 NULL

2654

3 4 0

2655

5 6 0

2656

7 8 0

2657

10 3 NULL

2658

10 5 NULL

2659

10 10 NULL

2660

explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;

2661

id select_type table type possible_keys key key_len ref rows filtered Extra

2662

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2663

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where

2664

Warnings:

2665

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"

2666

explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');

2667

id select_type table type possible_keys key key_len ref rows filtered Extra

2668

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary

2669

1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer

2670

Warnings:

2671

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'))

2672

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;

2673

id select_type table type possible_keys key key_len ref rows filtered Extra

2674

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2675

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort

2676

Warnings:

2677

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"

2678

DROP TABLE t1,t2;

2679

CREATE TABLE t1 (a char(5), b char(5));

2680

INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');

2681

SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));

2682

a b

2683

aaa aaa

2684

DROP TABLE t1;

2685

CREATE TABLE t1 (a int);

2686

CREATE TABLE t2 (a int, b int);

2687

CREATE TABLE t3 (b int NOT NULL);

2688

INSERT INTO t1 VALUES (1), (2), (3), (4);

2689

INSERT INTO t2 VALUES (1,10), (3,30);

2690

SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2691

WHERE t3.b IS NOT NULL OR t2.a > 10;

2692

a b b

2693

SELECT * FROM t1

2694

WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2695

WHERE t3.b IS NOT NULL OR t2.a > 10);

2696

2697

2698

2699

2700

2701

DROP TABLE t1,t2,t3;

2702

CREATE TABLE t1 (f1 INT);

2703

CREATE TABLE t2 (f2 INT);

2704

INSERT INTO t1 VALUES (1);

2705

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);

2706

2707

2708

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);

2709

2710

2711

INSERT INTO t2 VALUES (1);

2712

INSERT INTO t2 VALUES (2);

2713

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);

2714

2715

2716

DROP TABLE t1, t2;

2717

select 1 from dual where 1 < any (select 2);

2718

2719

2720

select 1 from dual where 1 < all (select 2);

2721

2722

2723

select 1 from dual where 2 > any (select 1);

2724

2725

2726

select 1 from dual where 2 > all (select 1);

2727

2728

2729

select 1 from dual where 1 < any (select 2 from dual);

2730

2731

2732

select 1 from dual where 1 < all (select 2 from dual where 1!=1);

2733

2734

2735

create table t1 (s1 char);

2736

insert into t1 values (1),(2);

2737

select * from t1 where (s1 < any (select s1 from t1));

2738

2739

2740

select * from t1 where not (s1 < any (select s1 from t1));

2741

2742

2743

select * from t1 where (s1 < ALL (select s1+1 from t1));

2744

2745

2746

select * from t1 where not(s1 < ALL (select s1+1 from t1));

2747

2748

2749

select * from t1 where (s1+1 = ANY (select s1 from t1));

2750

2751

2752

select * from t1 where NOT(s1+1 = ANY (select s1 from t1));

2753

2754

2755

select * from t1 where (s1 = ALL (select s1/s1 from t1));

2756

2757

2758

select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));

2759

2760

2761

drop table t1;

2762

create table t1 (

2763

retailerID varchar(8) NOT NULL,

2764

statusID int(10) unsigned NOT NULL,

2765

changed datetime NOT NULL,

2766

UNIQUE KEY retailerID (retailerID, statusID, changed)

2767

);

2768

INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");

2769

INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");

2770

INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");

2771

INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");

2772

INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");

2773

INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");

2774

select * from t1 r1

2775

where (r1.retailerID,(r1.changed)) in

2776

(SELECT r2.retailerId,(max(changed)) from t1 r2

2777

group by r2.retailerId);

2778

retailerID statusID changed

2779

0026 2 2006-01-06 12:25:53

2780

0037 2 2006-01-06 12:25:53

2781

0048 1 2006-01-06 12:37:50

2782

0059 1 2006-01-06 12:37:50

2783

drop table t1;

2784

create table t1(a int, primary key (a));

2785

insert into t1 values (10);

2786

create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));

2787

insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');

2788

explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2789

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2790

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

2791

id select_type table type possible_keys key key_len ref rows Extra

2792

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2793

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2794

2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition

2795

SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2796

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2797

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

2798

a a b

2799

10 3 35989

2800

explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2801

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2802

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2803

id select_type table type possible_keys key key_len ref rows Extra

2804

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2805

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2806

2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition; Using MRR

2807

SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2808

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2809

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2810

a a b

2811

10 1 359

2812

drop table t1,t2;

2813

CREATE TABLE t1 (

2814

field1 int NOT NULL,

2815

field2 int NOT NULL,

2816

field3 int NOT NULL,

2817

PRIMARY KEY (field1,field2,field3)

2818

);

2819

CREATE TABLE t2 (

2820

fieldA int NOT NULL,

2821

fieldB int NOT NULL,

2822

PRIMARY KEY (fieldA,fieldB)

2823

);

2824

INSERT INTO t1 VALUES

2825

(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);

2826

INSERT INTO t2 VALUES (1,1), (1,2), (1,3);

2827

SELECT field1, field2, COUNT(*)

2828

FROM t1 GROUP BY field1, field2;

2829

field1 field2 COUNT(*)

2830

1 1 2

2831

1 2 3

2832

1 3 1

2833

SELECT field1, field2

2834

FROM t1

2835

GROUP BY field1, field2

2836

HAVING COUNT(*) >= ALL (SELECT fieldB

2837

FROM t2 WHERE fieldA = field1);

2838

field1 field2

2839

1 2

2840

SELECT field1, field2

2841

FROM t1

2842

GROUP BY field1, field2

2843

HAVING COUNT(*) < ANY (SELECT fieldB

2844

FROM t2 WHERE fieldA = field1);

2845

field1 field2

2846

1 1

2847

1 3

2848

DROP TABLE t1, t2;

2849

CREATE TABLE t1(a int, INDEX (a));

2850

INSERT INTO t1 VALUES (1), (3), (5), (7);

2851

INSERT INTO t1 VALUES (NULL);

2852

CREATE TABLE t2(a int);

2853

INSERT INTO t2 VALUES (1),(2),(3);

2854

EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;

2855

id select_type table type possible_keys key key_len ref rows Extra

2856

1 PRIMARY t2 ALL NULL NULL NULL NULL 3

2857

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key

2858

SELECT a, a IN (SELECT a FROM t1) FROM t2;

2859

a a IN (SELECT a FROM t1)

2860

1 1

2861

2 NULL

2862

3 1

2863

DROP TABLE t1,t2;

2864

CREATE TABLE t1 (a DATETIME);

2865

INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');

2866

CREATE TABLE t2 AS SELECT

2867

(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a

2868

FROM t1 WHERE a > '2000-01-01';

2869

SHOW CREATE TABLE t2;

2870

Table Create Table

2871

t2 CREATE TABLE "t2" (

2872

"sub_a" datetime ON UPDATE CURRENT_TIMESTAMP

2873

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2874

CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');

2875

SHOW CREATE TABLE t3;

2876

Table Create Table

2877

t3 CREATE TABLE "t3" (

2878

"a" datetime ON UPDATE CURRENT_TIMESTAMP

2879

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2880

DROP TABLE t1,t2,t3;

2881

CREATE TABLE t1 (a int);

2882

INSERT INTO t1 VALUES (1), (2);

2883

SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;

2884

2885

SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;

2886

2887

2888

2889

EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;

2890

id select_type table type possible_keys key key_len ref rows Extra

2891

1 PRIMARY t1 ALL NULL NULL NULL NULL 2

2892

2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

2893

DROP TABLE t1;

2894

CREATE TABLE t1 (a int);

2895

INSERT INTO t1 VALUES (2), (4), (1), (3);

2896

CREATE TABLE t2 (b int, c int);

2897

INSERT INTO t2 VALUES

2898

(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);

2899

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );

2900

2901

2902

2903

2904

2905

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);

2906

ERROR 21000: Subquery returns more than 1 row

2907

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;

2908

2909

2910

2911

2912

2913

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;

2914

ERROR 21000: Subquery returns more than 1 row

2915

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);

2916

b MAX(c)

2917

1 4

2918

2 2

2919

4 4

2920

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);

2921

ERROR 21000: Subquery returns more than 1 row

2922

SELECT a FROM t1 GROUP BY a

2923

HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),

2924

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

2925

2926

2927

2928

2929

2930

SELECT a FROM t1 GROUP BY a

2931

HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),

2932

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

2933

ERROR 21000: Subquery returns more than 1 row

2934

SELECT a FROM t1 GROUP BY a

2935

HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),

2936

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

2937

2938

2939

SELECT a FROM t1 GROUP BY a

2940

HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),

2941

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;

2942

ERROR 21000: Subquery returns more than 1 row

2943

SELECT a FROM t1

2944

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),

2945

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));

2946

2947

2948

2949

2950

2951

SELECT a FROM t1

2952

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),

2953

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

2954

ERROR 21000: Subquery returns more than 1 row

2955

SELECT a FROM t1

2956

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),

2957

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));

2958

2959

2960

2961

2962

2963

SELECT a FROM t1

2964

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),

2965

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

2966

ERROR 21000: Subquery returns more than 1 row

2967

DROP TABLE t1,t2;

2968

create table t1 (df decimal(5,1));

2969

insert into t1 values(1.1);

2970

insert into t1 values(2.2);

2971

select * from t1 where df <= all (select avg(df) from t1 group by df);

2972

2973

1.1

2974

select * from t1 where df >= all (select avg(df) from t1 group by df);

2975

2976

2.2

2977

drop table t1;

2978

create table t1 (df decimal(5,1));

2979

insert into t1 values(1.1);

2980

select 1.1 * exists(select * from t1);

2981

1.1 * exists(select * from t1)

2982

1.1

2983

drop table t1;

2984

CREATE TABLE t1 (

2985

grp int(11) default NULL,

2986

a decimal(10,2) default NULL);

2987

insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);

2988

select * from t1;

2989

grp a

2990

1 1.00

2991

2 2.00

2992

2 3.00

2993

3 4.00

2994

3 5.00

2995

3 6.00

2996

NULL NULL

2997

select min(a) from t1 group by grp;

2998

min(a)

2999

NULL

3000

1.00

3001

2.00

3002

4.00

3003

drop table t1;

3004

CREATE table t1 ( c1 integer );

3005

INSERT INTO t1 VALUES ( 1 );

3006

INSERT INTO t1 VALUES ( 2 );

3007

INSERT INTO t1 VALUES ( 3 );

3008

CREATE TABLE t2 ( c2 integer );

3009

INSERT INTO t2 VALUES ( 1 );

3010

INSERT INTO t2 VALUES ( 4 );

3011

INSERT INTO t2 VALUES ( 5 );

3012

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);

3013

c1 c2

3014

1 1

3015

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2

3016

WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );

3017

c1 c2

3018

1 1

3019

DROP TABLE t1,t2;

3020

CREATE TABLE t1 ( c1 integer );

3021

INSERT INTO t1 VALUES ( 1 );

3022

INSERT INTO t1 VALUES ( 2 );

3023

INSERT INTO t1 VALUES ( 3 );

3024

INSERT INTO t1 VALUES ( 6 );

3025

CREATE TABLE t2 ( c2 integer );

3026

INSERT INTO t2 VALUES ( 1 );

3027

INSERT INTO t2 VALUES ( 4 );

3028

INSERT INTO t2 VALUES ( 5 );

3029

INSERT INTO t2 VALUES ( 6 );

3030

CREATE TABLE t3 ( c3 integer );

3031

INSERT INTO t3 VALUES ( 7 );

3032

INSERT INTO t3 VALUES ( 8 );

3033

SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2

3034

WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );

3035

c1 c2

3036

2 NULL

3037

3 NULL

3038

DROP TABLE t1,t2,t3;

3039

CREATE TABLE `t1` (

3040

`itemid` bigint(20) unsigned NOT NULL auto_increment,

3041

`sessionid` bigint(20) unsigned default NULL,

3042

`time` int(10) unsigned NOT NULL default '0',

3043

`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT

3044

NULL default '',

3045

`data` text collate latin1_general_ci NOT NULL,

3046

PRIMARY KEY (`itemid`)

3047

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

3048

INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');

3049

CREATE TABLE `t2` (

3050

`sessionid` bigint(20) unsigned NOT NULL auto_increment,

3051

`pid` int(10) unsigned NOT NULL default '0',

3052

`date` int(10) unsigned NOT NULL default '0',

3053

`ip` varchar(15) collate latin1_general_ci NOT NULL default '',

3054

PRIMARY KEY (`sessionid`)

3055

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

3056

INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');

3057

3058

ip count( e.itemid )

3059

10.10.10.1 1

3060

drop tables t1,t2;

3061

CREATE TABLE t1 (EMPNUM CHAR(3));

3062

CREATE TABLE t2 (EMPNUM CHAR(3) );

3063

INSERT INTO t1 VALUES ('E1'),('E2');

3064

INSERT INTO t2 VALUES ('E1');

3065

DELETE FROM t1

3066

WHERE t1.EMPNUM NOT IN

3067

(SELECT t2.EMPNUM

3068

FROM t2

3069

WHERE t1.EMPNUM = t2.EMPNUM);

3070

select * from t1;

3071

EMPNUM

3072

3073

DROP TABLE t1,t2;

3074

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);

3075

INSERT INTO t1 VALUES (1, 1);

3076

CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,

3077

PRIMARY KEY(select_id,values_id));

3078

INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);

3079

SELECT values_id FROM t1

3080

WHERE values_id IN (SELECT values_id FROM t2

3081

WHERE select_id IN (1, 0));

3082

values_id

3083

3084

SELECT values_id FROM t1

3085

WHERE values_id IN (SELECT values_id FROM t2

3086

WHERE select_id BETWEEN 0 AND 1);

3087

values_id

3088

3089

SELECT values_id FROM t1

3090

WHERE values_id IN (SELECT values_id FROM t2

3091

WHERE select_id = 0 OR select_id = 1);

3092

values_id

3093

3094

DROP TABLE t1, t2;

3095

create table t1 (fld enum('0','1'));

3096

insert into t1 values ('1');

3097

select * from (select max(fld) from t1) as foo;

3098

max(fld)

3099

3100

drop table t1;

3101

CREATE TABLE t1 (a int, b int);

3102

CREATE TABLE t2 (c int, d int);

3103

CREATE TABLE t3 (e int);

3104

INSERT INTO t1 VALUES

3105

(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);

3106

INSERT INTO t2 VALUES

3107

(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);

3108

INSERT INTO t3 VALUES (10), (30), (10), (20) ;

3109

SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;

3110

a MAX(b) MIN(b)

3111

1 20 10

3112

2 30 10

3113

3 20 20

3114

4 40 40

3115

SELECT * FROM t2;

3116

c d

3117

2 10

3118

2 20

3119

4 10

3120

5 10

3121

3 20

3122

2 40

3123

SELECT * FROM t3;

3124

3125

3126

3127

3128

3129

SELECT a FROM t1 GROUP BY a

3130

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);

3131

3132

3133

3134

SELECT a FROM t1 GROUP BY a

3135

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);

3136

3137

3138

SELECT a FROM t1 GROUP BY a

3139

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);

3140

3141

3142

3143

SELECT a FROM t1 GROUP BY a

3144

HAVING a IN (SELECT c FROM t2

3145

WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));

3146

3147

3148

3149

SELECT a FROM t1 GROUP BY a

3150

HAVING a IN (SELECT c FROM t2

3151

WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

3152

3153

3154

3155

SELECT a FROM t1 GROUP BY a

3156

HAVING a IN (SELECT c FROM t2

3157

WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));

3158

3159

3160

SELECT a FROM t1 GROUP BY a

3161

HAVING a IN (SELECT c FROM t2

3162

WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));

3163

3164

3165

SELECT a FROM t1 GROUP BY a

3166

HAVING a IN (SELECT c FROM t2

3167

WHERE MIN(b) < d AND

3168

EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

3169

3170

3171

SELECT a, SUM(a) FROM t1 GROUP BY a;

3172

a SUM(a)

3173

1 2

3174

2 6

3175

3 3

3176

4 4

3177

SELECT a FROM t1

3178

WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;

3179

3180

3181

3182

SELECT a FROM t1 GROUP BY a

3183

HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);

3184

3185

3186

3187

3188

SELECT a FROM t1

3189

WHERE a < 3 AND

3190

EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;

3191

3192

3193

3194

SELECT a FROM t1

3195

WHERE a < 3 AND

3196

EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);

3197

3198

3199

3200

3201

3202

3203

SELECT t1.a FROM t1 GROUP BY t1.a

3204

HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c

3205

HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e

3206

HAVING SUM(t1.a+t2.c) < t3.e/4));

3207

3208

3209

3210

SELECT t1.a FROM t1 GROUP BY t1.a

3211

HAVING t1.a > ALL(SELECT t2.c FROM t2

3212

WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e

3213

HAVING SUM(t1.a+t2.c) < t3.e/4));

3214

3215

3216

SELECT t1.a FROM t1 GROUP BY t1.a

3217

HAVING t1.a > ALL(SELECT t2.c FROM t2

3218

WHERE EXISTS(SELECT t3.e FROM t3

3219

WHERE SUM(t1.a+t2.c) < t3.e/4));

3220

ERROR HY000: Invalid use of group function

3221

SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;

3222

ERROR HY000: Invalid use of group function

3223

SELECT t1.a FROM t1 GROUP BY t1.a

3224

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3225

HAVING AVG(t2.c+SUM(t1.b)) > 20);

3226

3227

3228

3229

3230

SELECT t1.a FROM t1 GROUP BY t1.a

3231

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3232

HAVING AVG(SUM(t1.b)) > 20);

3233

3234

3235

3236

SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a

3237

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3238

HAVING t2.c+sum > 20);

3239

a sum

3240

2 60

3241

3 20

3242

4 40

3243

DROP TABLE t1,t2,t3;

3244

CREATE TABLE t1 (a varchar(5), b varchar(10));

3245

INSERT INTO t1 VALUES

3246

('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),

3247

('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);

3248

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3249

a b

3250

BBB 4

3251

CCC 7

3252

AAA 8

3253

EXPLAIN

3254

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3255

id select_type table type possible_keys key key_len ref rows Extra

3256

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3257

2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort

3258

ALTER TABLE t1 ADD INDEX(a);

3259

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3260

a b

3261

BBB 4

3262

CCC 7

3263

AAA 8

3264

EXPLAIN

3265

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3266

id select_type table type possible_keys key key_len ref rows Extra

3267

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3268

2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort

3269

DROP TABLE t1;

3270

create table t1( f1 int,f2 int);

3271

insert into t1 values (1,1),(2,2);

3272

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';

3273

3274

crash1

3275

crash1

3276

drop table t1;

3277

create table t1 (c int, key(c));

3278

insert into t1 values (1142477582), (1142455969);

3279

create table t2 (a int, b int);

3280

insert into t2 values (2, 1), (1, 0);

3281

delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;

3282

drop table t1, t2;

3283

create table t1 (i int, j bigint);

3284

insert into t1 values (1, 2), (2, 2), (3, 2);

3285

select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;

3286

min(i)

3287

3288

drop table t1;

3289

CREATE TABLE t1 (i BIGINT UNSIGNED);

3290

INSERT INTO t1 VALUES (10000000000000000000);

3291

INSERT INTO t1 VALUES (1);

3292

CREATE TABLE t2 (i BIGINT UNSIGNED);

3293

INSERT INTO t2 VALUES (10000000000000000000);

3294

INSERT INTO t2 VALUES (1);

3295

/* simple test */

3296

SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;

3297

3298

10000000000000000000

3299

3300

/* subquery test */

3301

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3302

3303

10000000000000000000

3304

/* subquery test with cast*/

3305

SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);

3306

3307

10000000000000000000

3308

DROP TABLE t1;

3309

DROP TABLE t2;

3310

CREATE TABLE t1 (

3311

id bigint(20) unsigned NOT NULL auto_increment,

3312

name varchar(255) NOT NULL,

3313

PRIMARY KEY (id)

3314

);

3315

INSERT INTO t1 VALUES

3316

(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

3317

CREATE TABLE t2 (

3318

id bigint(20) unsigned NOT NULL auto_increment,

3319

mid bigint(20) unsigned NOT NULL,

3320

date date NOT NULL,

3321

PRIMARY KEY (id)

3322

);

3323

INSERT INTO t2 VALUES

3324

(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),

3325

(4, 2, '2006-04-20'), (5, 1, '2006-05-01');

3326

SELECT *,

3327

(SELECT date FROM t2 WHERE mid = t1.id

3328

ORDER BY date DESC LIMIT 0, 1) AS date_last,

3329

(SELECT date FROM t2 WHERE mid = t1.id

3330

ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last

3331

FROM t1;

3332

id name date_last date_next_to_last

3333

1 Balazs 2006-05-01 NULL

3334

2 Joe 2006-04-20 NULL

3335

3 Frank 2006-04-13 NULL

3336

SELECT *,

3337

(SELECT COUNT(*) FROM t2 WHERE mid = t1.id

3338

ORDER BY date DESC LIMIT 1, 1) AS date_count

3339

FROM t1;

3340

id name date_count

3341

1 Balazs NULL

3342

2 Joe NULL

3343

3 Frank NULL

3344

SELECT *,

3345

(SELECT date FROM t2 WHERE mid = t1.id

3346

ORDER BY date DESC LIMIT 0, 1) AS date_last,

3347

(SELECT date FROM t2 WHERE mid = t1.id

3348

ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last

3349

FROM t1;

3350

id name date_last date_next_to_last

3351

1 Balazs 2006-05-01 2006-03-30

3352

2 Joe 2006-04-20 2006-04-06

3353

3 Frank 2006-04-13 NULL

3354

DROP TABLE t1,t2;

3355

CREATE TABLE t1 (

3356

i1 int(11) NOT NULL default '0',

3357

i2 int(11) NOT NULL default '0',

3358

t datetime NOT NULL default '0000-00-00 00:00:00',

3359

PRIMARY KEY (i1,i2,t)

3360

);

3361

INSERT INTO t1 VALUES

3362

(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),

3363

(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),

3364

(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),

3365

(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),

3366

(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),

3367

(24,2,'2005-05-27 12:40:06');

3368

CREATE TABLE t2 (

3369

i1 int(11) NOT NULL default '0',

3370

i2 int(11) NOT NULL default '0',

3371

t datetime default NULL,

3372

PRIMARY KEY (i1)

3373

);

3374

INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');

3375

EXPLAIN

3376

SELECT * FROM t1,t2

3377

WHERE t1.t = (SELECT t1.t FROM t1

3378

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3379

ORDER BY t1.t DESC LIMIT 1);

3380

id select_type table type possible_keys key key_len ref rows Extra

3381

1 PRIMARY t2 system NULL NULL NULL NULL 1

3382

1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index

3383

2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index

3384

SELECT * FROM t1,t2

3385

WHERE t1.t = (SELECT t1.t FROM t1

3386

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3387

ORDER BY t1.t DESC LIMIT 1);

3388

i1 i2 t i1 i2 t

3389

24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40

3390

DROP TABLE t1, t2;

3391

CREATE TABLE t1 (i INT);

3392

(SELECT i FROM t1) UNION (SELECT i FROM t1);

3393

3394

SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS

3395

(

3396

(SELECT i FROM t1) UNION

3397

(SELECT i FROM t1)

3398

);

3399

3400

SELECT * FROM t1

3401

WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));

3402

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

3403

explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))

3404

from t1;

3405

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))

3406

from t1' at line 1

3407

explain select * from t1 where not exists

3408

((select t11.i from t1 t11) union (select t12.i from t1 t12));

3409

id select_type table type possible_keys key key_len ref rows Extra

3410

1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found

3411

2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table

3412

3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table

3413

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL

3414

DROP TABLE t1;

3415

CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));

3416

insert into t1 (a) values (FLOOR(rand() * 100));

3417

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3418

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3419

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3420

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3421

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3422

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3423

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3424

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3425

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3426

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3427

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3428

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3429

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3430

SELECT a,

3431

(SELECT REPEAT(' ',250) FROM t1 i1

3432

WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a

3433

FROM t1 ORDER BY a LIMIT 5;

3434

a a

3435

0 NULL

3436

0 NULL

3437

0 NULL

3438

0 NULL

3439

0 NULL

3440

DROP TABLE t1;

3441

CREATE TABLE t1 (a INT, b INT);

3442

CREATE TABLE t2 (a INT);

3443

INSERT INTO t2 values (1);

3444

INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);

3445

SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;

3446

(SELECT COUNT(DISTINCT t1.b) from t2)

3447

3448

3449

3450

SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)

3451

FROM t1 GROUP BY t1.a;

3452

(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)

3453

3454

3455

3456

SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;

3457

COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))

3458

2 2

3459

1 1

3460

1 1

3461

SELECT COUNT(DISTINCT t1.b),

3462

(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)

3463

FROM t1 GROUP BY t1.a;

3464

COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)

3465

2 2

3466

1 1

3467

1 1

3468

SELECT (

3469

SELECT (

3470

SELECT COUNT(DISTINCT t1.b)

3471

)

3472

)

3473

FROM t1 GROUP BY t1.a;

3474

(

3475

SELECT (

3476

SELECT COUNT(DISTINCT t1.b)

3477

)

3478

)

3479

3480

3481

3482

SELECT (

3483

SELECT (

3484

SELECT (

3485

SELECT COUNT(DISTINCT t1.b)

3486

)

3487

)

3488

FROM t1 GROUP BY t1.a LIMIT 1)

3489

FROM t1 t2

3490

GROUP BY t2.a;

3491

(

3492

SELECT (

3493

SELECT (

3494

SELECT COUNT(DISTINCT t1.b)

3495

)

3496

)

3497

FROM t1 GROUP BY t1.a LIMIT 1)

3498

3499

3500

3501

DROP TABLE t1,t2;

3502

CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));

3503

CREATE TABLE t2 (x int auto_increment, y int, z int,

3504

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));

3505

SET SESSION sort_buffer_size = 32 * 1024;

3506

Warnings:

3507

Warning 1292 Truncated incorrect sort_buffer_size value: '32768'

3508

SELECT SQL_NO_CACHE COUNT(*)

3509

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3510

FROM t1) t;

3511

COUNT(*)

3512

3000

3513

SET SESSION sort_buffer_size = 8 * 1024 * 1024;

3514

SELECT SQL_NO_CACHE COUNT(*)

3515

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3516

FROM t1) t;

3517

COUNT(*)

3518

3000

3519

DROP TABLE t1,t2;

3520

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);

3521

CREATE TABLE t2 (c int);

3522

INSERT INTO t1 VALUES ('aa', 1);

3523

INSERT INTO t2 VALUES (1);

3524

SELECT * FROM t1

3525

WHERE EXISTS (SELECT c FROM t2 WHERE c=1

3526

UNION

3527

SELECT c from t2 WHERE c=t1.c);

3528

id c

3529

aa 1

3530

INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);

3531

SELECT * FROM t1

3532

WHERE EXISTS (SELECT c FROM t2 WHERE c=1

3533

UNION

3534

SELECT c from t2 WHERE c=t1.c);

3535

id c

3536

aa 1

3537

bb 2

3538

cc 3

3539

dd 1

3540

INSERT INTO t2 VALUES (2);

3541

CREATE TABLE t3 (c int);

3542

INSERT INTO t3 VALUES (1);

3543

SELECT * FROM t1

3544

WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1

3545

UNION

3546

SELECT c from t2 WHERE c=t1.c);

3547

id c

3548

aa 1

3549

bb 2

3550

cc 3

3551

dd 1

3552

DROP TABLE t1,t2,t3;

3553

DROP TABLE IF EXISTS t1;

3554

DROP TABLE IF EXISTS t2;

3555

DROP TABLE IF EXISTS t1xt2;

3556

CREATE TABLE t1 (

3557

id_1 int(5) NOT NULL,

3558

t varchar(4) DEFAULT NULL

3559

);

3560

CREATE TABLE t2 (

3561

id_2 int(5) NOT NULL,

3562

t varchar(4) DEFAULT NULL

3563

);

3564

CREATE TABLE t1xt2 (

3565

id_1 int(5) NOT NULL,

3566

id_2 int(5) NOT NULL

3567

);

3568

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

3569

INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');

3570

INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);

3571

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3572

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3573

id_1

3574

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3575

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3576

id_1

3577

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3578

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3579

id_1

3580

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3581

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3582

id_1

3583

3584

3585

3586

3587

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3588

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));

3589

id_1

3590

3591

3592

3593

3594

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3595

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));

3596

id_1

3597

3598

3599

3600

3601

insert INTO t1xt2 VALUES (1, 12);

3602

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3603

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3604

id_1

3605

3606

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3607

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3608

id_1

3609

3610

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3611

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3612

id_1

3613

3614

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3615

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3616

id_1

3617

3618

3619

3620

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3621

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3622

id_1

3623

3624

3625

3626

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3627

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3628

id_1

3629

3630

3631

3632

insert INTO t1xt2 VALUES (2, 12);

3633

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3634

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3635

id_1

3636

3637

3638

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3639

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3640

id_1

3641

3642

3643

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3644

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3645

id_1

3646

3647

3648

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3649

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3650

id_1

3651

3652

3653

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3654

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3655

id_1

3656

3657

3658

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3659

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3660

id_1

3661

3662

3663

DROP TABLE t1;

3664

DROP TABLE t2;

3665

DROP TABLE t1xt2;

3666

CREATE TABLE t1 (a int);

3667

INSERT INTO t1 VALUES (3), (1), (2);

3668

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;

3669

col1 col2

3670

this is a test. 3

3671

this is a test. 1

3672

this is a test. 2

3673

SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;

3674

col1 t2

3675

this is a test. 3

3676

this is a test. 1

3677

this is a test. 2

3678

DROP table t1;

3679

CREATE TABLE t1 (a int, b int);

3680

CREATE TABLE t2 (m int, n int);

3681

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

3682

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3683

SELECT COUNT(*), a,

3684

(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3685

FROM t1 GROUP BY a;

3686

COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3687

2 2 2

3688

3 3 3

3689

1 4 1

3690

SELECT COUNT(*), a,

3691

(SELECT MIN(m) FROM t2 WHERE m = count(*))

3692

FROM t1 GROUP BY a;

3693

COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))

3694

2 2 2

3695

3 3 3

3696

1 4 1

3697

SELECT COUNT(*), a

3698

FROM t1 GROUP BY a

3699

HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;

3700

COUNT(*) a

3701

2 2

3702

3 3

3703

DROP TABLE t1,t2;

3704

CREATE TABLE t1 (a int, b int);

3705

CREATE TABLE t2 (m int, n int);

3706

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

3707

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3708

SELECT COUNT(*) c, a,

3709

(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3710

FROM t1 GROUP BY a;

3711

c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3712

2 2 2

3713

3 3 3

3714

1 4 1,1

3715

SELECT COUNT(*) c, a,

3716

(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3717

FROM t1 GROUP BY a;

3718

c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3719

2 2 3

3720

3 3 4

3721

1 4 2,2

3722

DROP table t1,t2;

3723

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));

3724

INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),

3725

(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),

3726

(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');

3727

SELECT a, MAX(b),

3728

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test

3729

FROM t1 GROUP BY a;

3730

a MAX(b) test

3731

1 9 m

3732

2 3 h

3733

3 4 i

3734

SELECT a x, MAX(b),

3735

(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test

3736

FROM t1 GROUP BY a;

3737

x MAX(b) test

3738

1 9 m

3739

2 3 h

3740

3 4 i

3741

SELECT a, AVG(b),

3742

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test

3743

FROM t1 WHERE t1.d=0 GROUP BY a;

3744

a AVG(b) test

3745

1 4.0000 d

3746

2 2.0000 g

3747

3 2.5000 NULL

3748

SELECT tt.a,

3749

(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3750

LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3751

FROM t1 as tt;

3752

a test

3753

1 n

3754

1 n

3755

1 n

3756

1 n

3757

1 n

3758

1 n

3759

1 n

3760

2 o

3761

2 o

3762

2 o

3763

2 o

3764

3 p

3765

3 p

3766

3 p

3767

3 p

3768

3 p

3769

SELECT tt.a,

3770

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3771

LIMIT 1)

3772

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3773

FROM t1 as tt GROUP BY tt.a;

3774

a test

3775

1 n

3776

2 o

3777

3 p

3778

SELECT tt.a, MAX(

3779

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3780

LIMIT 1)

3781

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test

3782

FROM t1 as tt GROUP BY tt.a;

3783

a test

3784

1 n

3785

2 o

3786

3 p

3787

DROP TABLE t1;

3788

CREATE TABLE t1 (a INT);

3789

INSERT INTO t1 values (1),(1),(1),(1);

3790

CREATE TABLE t2 (x INT);

3791

INSERT INTO t1 values (1000),(1001),(1002);

3792

SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;

3793

ERROR HY000: Invalid use of group function

3794

SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;

3795

ERROR HY000: Invalid use of group function

3796

SELECT COUNT(1) FROM DUAL;

3797

COUNT(1)

3798

3799

SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;

3800

ERROR HY000: Invalid use of group function

3801

SELECT

3802

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )

3803

FROM t1;

3804

ERROR HY000: Invalid use of group function

3805

SELECT t1.a as XXA,

3806

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )

3807

FROM t1;

3808

ERROR HY000: Invalid use of group function

3809

DROP TABLE t1,t2;

3810

CREATE TABLE t1 (a int, b int, KEY (a));

3811

INSERT INTO t1 VALUES (1,1),(2,1);

3812

EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);

3813

id select_type table type possible_keys key key_len ref rows Extra

3814

1 PRIMARY t1 ref a a 5 const 1 Using where; Using index

3815

2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort

3816

DROP TABLE t1;

3817

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));

3818

INSERT INTO t1 VALUES

3819

(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');

3820

CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));

3821

INSERT INTO t2 VALUES (7), (5), (1), (3);

3822

SELECT id, st FROM t1

3823

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3824

id st

3825

3 FL

3826

1 GA

3827

7 FL

3828

SELECT id, st FROM t1

3829

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)

3830

GROUP BY id;

3831

id st

3832

1 GA

3833

3 FL

3834

7 FL

3835

SELECT id, st FROM t1

3836

WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3837

id st

3838

2 GA

3839

4 FL

3840

SELECT id, st FROM t1

3841

WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)

3842

GROUP BY id;

3843

id st

3844

2 GA

3845

4 FL

3846

DROP TABLE t1,t2;

3847

CREATE TABLE t1 (a int);

3848

INSERT INTO t1 VALUES (1), (2);

3849

EXPLAIN EXTENDED

3850

SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;

3851

id select_type table type possible_keys key key_len ref rows filtered Extra

3852

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00

3853

2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort

3854

Warnings:

3855

Note 1003 select "res"."count(*)" AS "count(*)" from (select count(0) AS "count(*)" from "test"."t1" group by "test"."t1"."a") "res"

3856

DROP TABLE t1;

3857

CREATE TABLE t1 (

3858

a varchar(255) default NULL,

3859

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3860

INDEX idx(a,b)

3861

);

3862

CREATE TABLE t2 (

3863

a varchar(255) default NULL

3864

);

3865

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');

3866

INSERT INTO t1 SELECT * FROM t1;

3867

INSERT INTO t1 SELECT * FROM t1;

3868

INSERT INTO t1 SELECT * FROM t1;

3869

INSERT INTO t1 SELECT * FROM t1;

3870

INSERT INTO t1 SELECT * FROM t1;

3871

INSERT INTO t1 SELECT * FROM t1;

3872

INSERT INTO t1 SELECT * FROM t1;

3873

INSERT INTO t1 SELECT * FROM t1;

3874

INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');

3875

INSERT INTO `t2` VALUES ('abcdefghijk');

3876

INSERT INTO `t2` VALUES ('asdf');

3877

SET session sort_buffer_size=8192;

3878

Warnings:

3879

Warning 1292 Truncated incorrect sort_buffer_size value: '8192'

3880

SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;

3881

3882

3883

3884

DROP TABLE t1,t2;

3885

CREATE TABLE t1 (a INTEGER, b INTEGER);

3886

CREATE TABLE t2 (x INTEGER);

3887

INSERT INTO t1 VALUES (1,11), (2,22), (2,22);

3888

INSERT INTO t2 VALUES (1), (2);

3889

SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;

3890

ERROR 21000: Subquery returns more than 1 row

3891

SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;

3892

ERROR 21000: Subquery returns more than 1 row

3893

SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;

3894

(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)

3895

3.3333

3896

DROP TABLE t1,t2;

3897

CREATE TABLE t1 (a INT, b INT);

3898

INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);

3899

SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1

3900

AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)

3901

GROUP BY a1.a;

3902

a COUNT(*)

3903

1 3

3904

DROP TABLE t1;

3905

CREATE TABLE t1 (a INT);

3906

CREATE TABLE t2 (a INT);

3907

INSERT INTO t1 VALUES (1),(2);

3908

INSERT INTO t2 VALUES (1),(2);

3909

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;

3910

(SELECT SUM(t1.a) FROM t2 WHERE a=0)

3911

NULL

3912

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;

3913

ERROR 21000: Subquery returns more than 1 row

3914

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;

3915

(SELECT SUM(t1.a) FROM t2 WHERE a=1)

3916

3917

DROP TABLE t1,t2;

3918

CREATE TABLE t1 (a1 INT, a2 INT);

3919

CREATE TABLE t2 (b1 INT, b2 INT);

3920

INSERT INTO t1 VALUES (100, 200);

3921

INSERT INTO t1 VALUES (101, 201);

3922

INSERT INTO t2 VALUES (101, 201);

3923

INSERT INTO t2 VALUES (103, 203);

3924

SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;

3925

((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL

3926

3927

3928

DROP TABLE t1, t2;

3929

CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));

3930

INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);

3931

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);

3932

s1 s2

3933

SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

3934

s1 s2

3935

CREATE INDEX I1 ON t1 (s1);

3936

CREATE INDEX I2 ON t1 (s2);

3937

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);

3938

s1 s2

3939

SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

3940

s1 s2

3941

TRUNCATE t1;

3942

INSERT INTO t1 VALUES (0x41,0x41);

3943

SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);

3944

s1 s2

3945

DROP TABLE t1;

3946

CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));

3947

CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));

3948

CREATE TABLE t3 (a3 BINARY(2) default '0');

3949

INSERT INTO t1 VALUES (1),(2),(3),(4);

3950

INSERT INTO t2 VALUES (1),(2),(3);

3951

INSERT INTO t3 VALUES (1),(2),(3);

3952

SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;

3953

LEFT(t2.a2, 1)

3954

3955

3956

3957

SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;

3958

a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)

3959

1 0

3960

2 0

3961

3 0

3962

4 0

3963

DROP TABLE t1,t2,t3;

3964

CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));

3965

CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);

3966

CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);

3967

INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);

3968

INSERT INTO t2 VALUES (2), (3), (4), (5);

3969

INSERT INTO t3 VALUES (10), (20), (30);

3970

SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;

3971

LEFT(t1.a1,1)

3972

3973

3974

3975

SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);

3976

3977

DROP TABLE t1, t2, t3;

3978

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));

3979

INSERT INTO t1 VALUES ('a', 'aa');

3980

INSERT INTO t1 VALUES ('a', 'aaa');

3981

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3982

a b

3983

CREATE INDEX I1 ON t1 (a);

3984

CREATE INDEX I2 ON t1 (b);

3985

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3986

id select_type table type possible_keys key key_len ref rows Extra

3987

1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan

3988

1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition

3989

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3990

a b

3991

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));

3992

INSERT INTO t2 SELECT * FROM t1;

3993

CREATE INDEX I1 ON t2 (a);

3994

CREATE INDEX I2 ON t2 (b);

3995

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

3996

id select_type table type possible_keys key key_len ref rows Extra

3997

1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan

3998

1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition

3999

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

4000

a b

4001

EXPLAIN

4002

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4003

id select_type table type possible_keys key key_len ref rows Extra

4004

1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan

4005

1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition

4006

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4007

a b

4008

DROP TABLE t1,t2;

4009

CREATE TABLE t1(a INT, b INT);

4010

INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);

4011

EXPLAIN

4012

SELECT a AS out_a, MIN(b) FROM t1

4013

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

4014

GROUP BY a;

4015

ERROR 42S22: Unknown column 'out_a' in 'where clause'

4016

SELECT a AS out_a, MIN(b) FROM t1

4017

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

4018

GROUP BY a;

4019

ERROR 42S22: Unknown column 'out_a' in 'where clause'

4020

EXPLAIN

4021

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

4022

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

4023

GROUP BY a;

4024

id select_type table type possible_keys key key_len ref rows Extra

4025

1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort

4026

2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where

4027

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

4028

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

4029

GROUP BY a;

4030

out_a MIN(b)

4031

1 2

4032

2 4

4033

DROP TABLE t1;

4034

CREATE TABLE t1 (a INT);

4035

CREATE TABLE t2 (a INT);

4036

INSERT INTO t1 VALUES (1),(2);

4037

INSERT INTO t2 VALUES (1),(2);

4038

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

4039

4040

4041

4042

EXPLAIN EXTENDED

4043

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

4044

id select_type table type possible_keys key key_len ref rows filtered Extra

4045

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where

4046

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where

4047

Warnings:

4048

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

4049

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"))

4050

EXPLAIN EXTENDED

4051

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION

4052

(SELECT 1 FROM t2 WHERE t1.a = t2.a));

4053

id select_type table type possible_keys key key_len ref rows filtered Extra

4054

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where

4055

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where

4056

3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where

4057

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL

4058

Warnings:

4059

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

4060

Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1

4061

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")))

4062

DROP TABLE t1,t2;

4063

CREATE TABLE t4 (

4064

f7 varchar(32) collate utf8_bin NOT NULL default '',

4065

f10 varchar(32) collate utf8_bin default NULL,

4066

PRIMARY KEY (f7)

4067

);

4068

INSERT INTO t4 VALUES(1,1), (2,null);

4069

CREATE TABLE t2 (

4070

f4 varchar(32) collate utf8_bin NOT NULL default '',

4071

f2 varchar(50) collate utf8_bin default NULL,

4072

f3 varchar(10) collate utf8_bin default NULL,

4073

PRIMARY KEY (f4),

4074

UNIQUE KEY uk1 (f2)

4075

);

4076

INSERT INTO t2 VALUES(1,1,null), (2,2,null);

4077

CREATE TABLE t1 (

4078

f8 varchar(32) collate utf8_bin NOT NULL default '',

4079

f1 varchar(10) collate utf8_bin default NULL,

4080

f9 varchar(32) collate utf8_bin default NULL,

4081

PRIMARY KEY (f8)

4082

);

4083

INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);

4084

CREATE TABLE t3 (

4085

f6 varchar(32) collate utf8_bin NOT NULL default '',

4086

f5 varchar(50) collate utf8_bin default NULL,

4087

PRIMARY KEY (f6)

4088

);

4089

INSERT INTO t3 VALUES (1,null), (2,null);

4090

SELECT

4091

IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,

4092

IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,

4093

SUM(

4094

IF(

4095

(SELECT VPC.f2

4096

FROM t2 VPC, t4 a2, t2 a3

4097

WHERE

4098

VPC.f4 = a2.f10 AND a3.f2 = a4

4099

LIMIT 1) IS NULL,

4100

4101

t3.f5

4102

)

4103

) AS a6

4104

FROM

4105

t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4

4106

GROUP BY a4;

4107

a4 f3 a6

4108

1 NULL NULL

4109

2 NULL NULL

4110

DROP TABLE t1, t2, t3, t4;

4111

End of 5.0 tests.

4112

create table t_out (subcase char(3),

4113

a1 char(2), b1 char(2), c1 char(2));

4114

create table t_in (a2 char(2), b2 char(2), c2 char(2));

4115

insert into t_out values ('A.1','2a', NULL, '2a');

4116

insert into t_out values ('A.3', '2a', NULL, '2a');

4117

insert into t_out values ('A.4', '2a', NULL, 'xx');

4118

insert into t_out values ('B.1', '2a', '2a', '2a');

4119

insert into t_out values ('B.2', '2a', '2a', '2a');

4120

insert into t_out values ('B.3', '3a', 'xx', '3a');

4121

insert into t_out values ('B.4', 'xx', '3a', '3a');

4122

insert into t_in values ('1a', '1a', '1a');

4123

insert into t_in values ('2a', '2a', '2a');

4124

insert into t_in values (NULL, '2a', '2a');

4125

insert into t_in values ('3a', NULL, '3a');

4126

4127

Test general IN semantics (not top-level)

4128

4129

case A.1

4130

select subcase,

4131

(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,

4132

(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in

4133

from t_out where subcase = 'A.1';

4134

subcase pred_in pred_not_in

4135

A.1 0 1

4136

case A.2 - impossible

4137

case A.3

4138

select subcase,

4139

(a1, b1, c1) IN (select * from t_in) pred_in,

4140

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4141

from t_out where subcase = 'A.3';

4142

subcase pred_in pred_not_in

4143

A.3 NULL NULL

4144

case A.4

4145

select subcase,

4146

(a1, b1, c1) IN (select * from t_in) pred_in,

4147

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4148

from t_out where subcase = 'A.4';

4149

subcase pred_in pred_not_in

4150

A.4 0 1

4151

case B.1

4152

select subcase,

4153

(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,

4154

(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in

4155

from t_out where subcase = 'B.1';

4156

subcase pred_in pred_not_in

4157

B.1 0 1

4158

case B.2

4159

select subcase,

4160

(a1, b1, c1) IN (select * from t_in) pred_in,

4161

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4162

from t_out where subcase = 'B.2';

4163

subcase pred_in pred_not_in

4164

B.2 1 0

4165

case B.3

4166

select subcase,

4167

(a1, b1, c1) IN (select * from t_in) pred_in,

4168

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4169

from t_out where subcase = 'B.3';

4170

subcase pred_in pred_not_in

4171

B.3 NULL NULL

4172

case B.4

4173

select subcase,

4174

(a1, b1, c1) IN (select * from t_in) pred_in,

4175

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4176

from t_out where subcase = 'B.4';

4177

subcase pred_in pred_not_in

4178

B.4 0 1

4179

4180

Test IN as top-level predicate, and

4181

as non-top level for cases A.3, B.3 (the only cases with NULL result).

4182

4183

case A.1

4184

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4185

where subcase = 'A.1' and

4186

(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');

4187

pred_in

4188

4189

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4190

where subcase = 'A.1' and

4191

(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');

4192

pred_not_in

4193

4194

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4195

where subcase = 'A.1' and

4196

NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));

4197

not_pred_in

4198

4199

case A.3

4200

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4201

where subcase = 'A.3' and

4202

(a1, b1, c1) IN (select * from t_in);

4203

pred_in

4204

4205

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4206

where subcase = 'A.3' and

4207

(a1, b1, c1) NOT IN (select * from t_in);

4208

pred_not_in

4209

4210

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4211

where subcase = 'A.3' and

4212

NOT((a1, b1, c1) IN (select * from t_in));

4213

not_pred_in

4214

4215

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

4216

where subcase = 'A.3' and

4217

((a1, b1, c1) IN (select * from t_in)) is NULL and

4218

((a1, b1, c1) NOT IN (select * from t_in)) is NULL;

4219

pred_in

4220

4221

case A.4

4222

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4223

where subcase = 'A.4' and

4224

(a1, b1, c1) IN (select * from t_in);

4225

pred_in

4226

4227

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4228

where subcase = 'A.4' and

4229

(a1, b1, c1) NOT IN (select * from t_in);

4230

pred_not_in

4231

4232

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4233

where subcase = 'A.4' and

4234

NOT((a1, b1, c1) IN (select * from t_in));

4235

not_pred_in

4236

4237

case B.1

4238

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4239

where subcase = 'B.1' and

4240

(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');

4241

pred_in

4242

4243

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4244

where subcase = 'B.1' and

4245

(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');

4246

pred_not_in

4247

4248

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4249

where subcase = 'B.1' and

4250

NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));

4251

not_pred_in

4252

4253

case B.2

4254

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4255

where subcase = 'B.2' and

4256

(a1, b1, c1) IN (select * from t_in);

4257

pred_in

4258

4259

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4260

where subcase = 'B.2' and

4261

(a1, b1, c1) NOT IN (select * from t_in);

4262

pred_not_in

4263

4264

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4265

where subcase = 'B.2' and

4266

NOT((a1, b1, c1) IN (select * from t_in));

4267

not_pred_in

4268

4269

case B.3

4270

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4271

where subcase = 'B.3' and

4272

(a1, b1, c1) IN (select * from t_in);

4273

pred_in

4274

4275

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4276

where subcase = 'B.3' and

4277

(a1, b1, c1) NOT IN (select * from t_in);

4278

pred_not_in

4279

4280

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4281

where subcase = 'B.3' and

4282

NOT((a1, b1, c1) IN (select * from t_in));

4283

not_pred_in

4284

4285

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

4286

where subcase = 'B.3' and

4287

((a1, b1, c1) IN (select * from t_in)) is NULL and

4288

((a1, b1, c1) NOT IN (select * from t_in)) is NULL;

4289

pred_in

4290

4291

case B.4

4292

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4293

where subcase = 'B.4' and

4294

(a1, b1, c1) IN (select * from t_in);

4295

pred_in

4296

4297

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4298

where subcase = 'B.4' and

4299

(a1, b1, c1) NOT IN (select * from t_in);

4300

pred_not_in

4301

4302

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4303

where subcase = 'B.4' and

4304

NOT((a1, b1, c1) IN (select * from t_in));

4305

not_pred_in

4306

4307

drop table t_out;

4308

drop table t_in;

4309

CREATE TABLE t1 (s1 char(1));

4310

INSERT INTO t1 VALUES ('a');

4311

SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);

4312

4313

4314

DROP TABLE t1;

4315

CREATE TABLE t1( a INT );

4316

INSERT INTO t1 VALUES (1),(2);

4317

CREATE TABLE t2( a INT, b INT );

4318

SELECT *

4319

FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;

4320

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

4321

SELECT *

4322

FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;

4323

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

4324

SELECT *

4325

FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;

4326

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

4327

SELECT * FROM (

4328

SELECT 1 a

4329

UNION

4330

SELECT a INTO @var FROM t1 WHERE a = 2

4331

) t1a;

4332

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

4333

) t1a' at line 2

4334

SELECT * FROM (

4335

SELECT 1 a

4336

UNION

4337

SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2

4338

) t1a;

4339

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

4340

) t1a' at line 2

4341

SELECT * FROM (

4342

SELECT 1 a

4343

UNION

4344

SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4345

) t1a;

4346

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

4347

) t1a' at line 2

4348

SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;

4349

4350

4351

SELECT * FROM (

4352

SELECT a FROM t1 WHERE a = 2

4353

UNION

4354

SELECT a FROM t1 WHERE a = 2

4355

) t1a;

4356

4357

4358

SELECT * FROM (

4359

SELECT 1 a

4360

UNION

4361

SELECT a FROM t1 WHERE a = 2

4362

UNION

4363

SELECT a FROM t1 WHERE a = 2

4364

) t1a;

4365

4366

4367

4368

SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);

4369

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

4370

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;

4371

4372

4373

SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;

4374

4375

4376

SELECT * FROM ((SELECT 1 a INTO @a)) t1a;

4377

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

4378

SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;

4379

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

4380

SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4381

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

4382

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;

4383

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

4384

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4385

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

4386

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;

4387

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

4388

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;

4389

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

4390

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;

4391

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

4392

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;

4393

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

4394

SELECT * FROM (SELECT 1 a ORDER BY a) t1a;

4395

4396

4397

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;

4398

4399

4400

SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;

4401

4402

4403

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;

4404

4405

4406

SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;

4407

a 1

4408

1 1

4409

2 1

4410

SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;

4411

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

4412

SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;

4413

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

4414

SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;

4415

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

4416

SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;

4417

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

4418

SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;

4419

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

4420

SELECT * FROM t1 JOIN (t1 t1a) ON 1;

4421

a a

4422

1 1

4423

2 1

4424

1 2

4425

2 2

4426

SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;

4427

a a

4428

1 1

4429

2 1

4430

1 2

4431

2 2

4432

SELECT * FROM (t1 t1a);

4433

4434

4435

4436

SELECT * FROM ((t1 t1a));

4437

4438

4439

4440

SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;

4441

a t1a

4442

1 1

4443

2 1

4444

SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;

4445

a t1a

4446

1 1

4447

2 1

4448

SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;

4449

a a

4450

1 1

4451

2 1

4452

SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;

4453

a a

4454

1 1

4455

2 1

4456

SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;

4457

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

4458

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );

4459

4460

4461

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );

4462

4463

4464

SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );

4465

4466

4467

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);

4468

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

4469

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4470

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

4471

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4472

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

4473

SELECT * FROM t1 WHERE a = ( SELECT 1 );

4474

4475

4476

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );

4477

4478

4479

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);

4480

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

4481

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );

4482

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

4483

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );

4484

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

4485

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);

4486

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

4487

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4488

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

4489

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4490

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

4491

SELECT ( SELECT 1 INTO @v );

4492

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

4493

SELECT ( SELECT 1 INTO OUTFILE 'file' );

4494

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

4495

SELECT ( SELECT 1 INTO DUMPFILE 'file' );

4496

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

4497

SELECT ( SELECT 1 UNION SELECT 1 INTO @v );

4498

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

4499

SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4500

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

4501

SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4502

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

4503

SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;

4504

( SELECT a FROM t1 WHERE a = 1 ) a

4505

1 1

4506

1 2

4507

SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;

4508

( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a

4509

1 1

4510

1 2

4511

SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);

4512

a b

4513

SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );

4514

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

4515

( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4516

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

4517

SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4518

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

4519

SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4520

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

4521

SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4522

( SELECT 1 UNION SELECT 1 UNION SELECT 1 )

4523

4524

SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));

4525

((SELECT 1 UNION SELECT 1 UNION SELECT 1))

4526

4527

SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4528

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

4529

SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4530

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

4531

SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;

4532

4533

4534

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4535

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

4536

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4537

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

4538

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4539

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

4540

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4541

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

4542

SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4543

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

4544

SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4545

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

4546

SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4547

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

4548

SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4549

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

4550

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4551

4552

4553

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4554

4555

4556

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4557

4558

4559

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4560

4561

4562

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );

4563

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

4564

SELECT EXISTS(SELECT 1+1);

4565

EXISTS(SELECT 1+1)

4566

4567

SELECT EXISTS(SELECT 1+1 INTO @test);

4568

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

4569

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );

4570

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

4571

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );

4572

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

4573

SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );

4574

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

4575

DROP TABLE t1, t2;

4576

set optimizer_switch='';

4577

show variables like 'optimizer_switch';

4578

Variable_name Value

4579

optimizer_switch

Older »