~linuxjedi/drizzle/trunk-bug-667053

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

183

id select_type table type possible_keys key key_len ref rows filtered Extra

184

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

185

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

186

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

187

4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

188

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

189

Warnings:

190

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

191

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

192

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

193

3 1

194

7 2

195

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

196

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

197

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

198

7 2

199

explain extended 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

id select_type table type possible_keys key key_len ref rows filtered Extra

202

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

203

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

204

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

205

Warnings:

206

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"

207

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

208

209

210

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

211

212

213

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

214

215

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

216

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

217

8 7.5000

218

8 4.5000

219

9 7.5000

220

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

221

id select_type table type possible_keys key key_len ref rows filtered Extra

222

1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00

223

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

224

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

225

Warnings:

226

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

227

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"

228

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

229

230

231

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

232

233

234

235

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

236

237

238

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

239

240

241

242

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

243

244

245

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

246

247

248

249

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

250

251

252

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

253

254

255

256

insert into t2 values (100, 5);

257

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

258

259

260

261

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

262

263

264

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

265

266

267

268

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

269

id select_type table type possible_keys key key_len ref rows filtered Extra

270

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

271

2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00

272

Warnings:

273

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

274

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

275

276

277

delete from t2 where a=100;

278

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

279

ERROR 21000: Operand should contain 1 column(s)

280

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

281

ERROR 21000: Operand should contain 1 column(s)

282

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

283

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

284

b ma

285

insert into t2 values (2,10);

286

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

287

b ma

288

10 1

289

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

290

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

291

b ma

292

7 12

293

create table t5 (a int);

294

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

295

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

296

NULL 1

297

2 2

298

insert into t5 values (5);

299

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

300

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

301

NULL 1

302

2 2

303

insert into t5 values (2);

304

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

305

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

306

NULL 1

307

2 2

308

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;

309

id select_type table type possible_keys key key_len ref rows filtered Extra

310

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00

311

2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

312

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

313

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

314

Warnings:

315

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

316

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

317

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"

318

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

319

ERROR 21000: Subquery returns more than 1 row

320

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

321

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

322

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

323

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

324

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

325

patient_uq clinic_uq

326

1 1

327

1 2

328

2 2

329

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

330

id select_type table type possible_keys key key_len ref rows filtered Extra

331

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

332

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

333

Warnings:

334

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

335

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

336

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

337

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

338

drop table t1,t2,t3;

339

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

340

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

341

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

342

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

343

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

344

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

345

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

346

a b

347

W 1732-02-22

348

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

349

a b

350

W 1

351

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

352

a b

353

W a

354

CREATE TABLE `t8` (

355

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

356

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

357

PRIMARY KEY (`pseudo`),

358

UNIQUE KEY `email` (`email`)

359

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

360

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

361

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

362

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

363

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

364

id select_type table type possible_keys key key_len ref rows filtered Extra

365

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

366

4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

367

2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00

368

3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

369

Warnings:

370

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

371

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

372

t8 WHERE pseudo='joce');

373

ERROR 21000: Operand should contain 1 column(s)

374

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

375

pseudo='joce');

376

ERROR 21000: Operand should contain 1 column(s)

377

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

378

pseudo

379

joce

380

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

381

ERROR 21000: Subquery returns more than 1 row

382

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

383

CREATE TABLE `t1` (

384

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

385

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

386

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

387

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

388

KEY `topic` (`topic`)

389

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

390

INSERT INTO t1 (topic,date,pseudo) VALUES

391

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

392

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

393

id select_type table type possible_keys key key_len ref rows filtered Extra

394

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

395

Warnings:

396

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

397

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

398

id select_type table type possible_keys key key_len ref rows filtered Extra

399

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

400

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

401

Warnings:

402

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

403

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

404

date

405

2002-08-03

406

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

407

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

408

2002-08-03

409

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

410

411

412

413

414

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

415

ERROR 21000: Subquery returns more than 1 row

416

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

417

id select_type table type possible_keys key key_len ref rows filtered Extra

418

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

419

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

420

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

421

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

422

Warnings:

423

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

424

drop table t1;

425

CREATE TABLE `t1` (

426

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

427

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

428

PRIMARY KEY (`numeropost`),

429

UNIQUE KEY `maxnumrep` (`maxnumrep`)

430

) ENGINE=MyISAM ROW_FORMAT=FIXED;

431

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

432

CREATE TABLE `t2` (

433

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

434

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

435

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

436

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

437

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

438

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

439

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

440

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

441

442

40143

443

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;

444

numeropost maxnumrep

445

43506 2

446

40143 1

447

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

448

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

449

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

450

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

451

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

452

mot topic date pseudo

453

joce 40143 2002-10-22 joce

454

joce 43506 2002-10-22 joce

455

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

456

mot topic date pseudo

457

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

458

mot topic date pseudo

459

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

460

mot topic date pseudo

461

joce 40143 2002-10-22 joce

462

joce 43506 2002-10-22 joce

463

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

464

mot topic date pseudo

465

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

466

mot topic date pseudo

467

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

468

mot topic date pseudo

469

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

470

mot topic date pseudo

471

joce 40143 2002-10-22 joce

472

joce 43506 2002-10-22 joce

473

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

474

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

475

joce 40143 2002-10-22 joce 1

476

joce 43506 2002-10-22 joce 1

477

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

478

mot topic date pseudo

479

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

480

mot topic date pseudo

481

joce 40143 2002-10-22 joce

482

joce 43506 2002-10-22 joce

483

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

484

mot topic date pseudo

485

joce 40143 2002-10-22 joce

486

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

487

mot topic date pseudo

488

joce 40143 2002-10-22 joce

489

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

490

mot topic date pseudo

491

joce 40143 2002-10-22 joce

492

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

493

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

494

joce 40143 2002-10-22 joce 1

495

joce 43506 2002-10-22 joce 0

496

drop table t1,t2;

497

CREATE TABLE `t1` (

498

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

499

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

500

PRIMARY KEY (`numeropost`),

501

UNIQUE KEY `maxnumrep` (`maxnumrep`)

502

) ENGINE=MyISAM ROW_FORMAT=FIXED;

503

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

504

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

505

ERROR 21000: Subquery returns more than 1 row

506

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

507

ERROR 21000: Subquery returns more than 1 row

508

drop table t1;

509

create table t1 (a int);

510

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

511

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

512

513

514

515

516

drop table t1;

517

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

518

INSERT INTO t1 VALUES ();

519

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

520

ERROR 21000: Subquery returns more than 1 row

521

drop table t1;

522

CREATE TABLE `t1` (

523

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

524

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

525

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

526

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

527

UNIQUE KEY `numreponse` (`numreponse`),

528

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

529

) ENGINE=MyISAM;

530

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

531

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

532

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

533

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

534

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

535

numreponse (SELECT numeropost FROM t1 HAVING numreponse=1)

536

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

537

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

538

ERROR 21000: Subquery returns more than 1 row

539

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

540

id select_type table type possible_keys key key_len ref rows filtered Extra

541

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

542

Warnings:

543

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

544

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

545

id select_type table type possible_keys key key_len ref rows filtered Extra

546

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

547

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

548

Warnings:

549

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

550

drop table t1;

551

CREATE TABLE t1 (a int(1));

552

INSERT INTO t1 VALUES (1);

553

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

554

555

556

drop table t1;

557

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

558

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

559

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

560

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

561

select * from t1;

562

a b

563

0 10

564

1 11

565

2 12

566

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

567

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

568

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

569

ERROR 21000: Subquery returns more than 1 row

570

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

571

select * from t1;

572

a b

573

0 NULL

574

1 21

575

2 22

576

drop table t1, t2;

577

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

578

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

579

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

580

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

581

select * from t1;

582

a b

583

0 10

584

1 11

585

2 12

586

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

587

a b

588

2 12

589

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

590

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

591

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

592

ERROR 21000: Subquery returns more than 1 row

593

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

594

select * from t1;

595

a b

596

0 10

597

1 11

598

drop table t1, t2;

599

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

600

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

601

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

602

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

603

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

604

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

605

select * from t11;

606

a b

607

0 10

608

1 11

609

2 12

610

select * from t12;

611

a b

612

33 10

613

22 11

614

2 12

615

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

616

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

617

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

618

ERROR 21000: Subquery returns more than 1 row

619

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

620

select * from t11;

621

a b

622

0 10

623

1 11

624

select * from t12;

625

a b

626

33 10

627

22 11

628

drop table t11, t12, t2;

629

CREATE TABLE t1 (x int);

630

create table t2 (a int);

631

create table t3 (b int);

632

insert into t2 values (1);

633

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

634

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

635

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

636

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

637

ERROR 21000: Subquery returns more than 1 row

638

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

639

select * from t1;

640

641

642

insert into t2 values (1);

643

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

644

select * from t1;

645

646

647

648

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

649

select * from t1;

650

651

652

653

654

655

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

656

select * from t1;

657

658

659

660

661

662

663

664

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

665

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

666

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

667

select * from t1;

668

669

670

671

672

673

674

675

676

drop table t1, t2, t3;

677

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

678

create table t2 (a int);

679

create table t3 (a int);

680

insert into t2 values (1);

681

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

682

select * from t1;

683

x y

684

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

685

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

686

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

687

ERROR 21000: Subquery returns more than 1 row

688

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

689

select * from t1;

690

x y

691

1 2

692

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

693

select * from t1;

694

x y

695

1 3

696

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

697

select * from t1;

698

x y

699

1 3

700

4 1

701

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

702

select * from t1;

703

x y

704

1 3

705

4 2

706

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

707

select * from t1;

708

x y

709

1 3

710

4 2

711

2 1

712

drop table t1, t2, t3;

713

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

714

ERROR HY000: No tables used

715

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

716

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

717

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

718

719

720

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

721

id select_type table type possible_keys key key_len ref rows filtered Extra

722

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

723

Warnings:

724

Note 1249 Select 2 was reduced during optimization

725

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

726

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

727

728

729

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

730

731

732

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

733

id select_type table type possible_keys key key_len ref rows filtered Extra

734

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

735

Warnings:

736

Note 1249 Select 3 was reduced during optimization

737

Note 1249 Select 2 was reduced during optimization

738

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

739

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

740

id select_type table type possible_keys key key_len ref rows filtered Extra

741

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

742

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

743

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

744

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

745

Warnings:

746

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

747

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

748

749

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

750

751

752

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

753

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

754

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

755

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

756

SELECT * FROM t2;

757

758

759

760

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

761

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

762

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

763

ERROR 21000: Subquery returns more than 1 row

764

drop table t2, t1;

765

create table t1 (a int);

766

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

767

select 1 IN (SELECT * from t1);

768

1 IN (SELECT * from t1)

769

770

select 10 IN (SELECT * from t1);

771

10 IN (SELECT * from t1)

772

773

select NULL IN (SELECT * from t1);

774

NULL IN (SELECT * from t1)

775

NULL

776

update t1 set a=NULL where a=2;

777

select 1 IN (SELECT * from t1);

778

1 IN (SELECT * from t1)

779

780

select 3 IN (SELECT * from t1);

781

3 IN (SELECT * from t1)

782

783

select 10 IN (SELECT * from t1);

784

10 IN (SELECT * from t1)

785

NULL

786

select 1 > ALL (SELECT * from t1);

787

1 > ALL (SELECT * from t1)

788

789

select 10 > ALL (SELECT * from t1);

790

10 > ALL (SELECT * from t1)

791

NULL

792

select 1 > ANY (SELECT * from t1);

793

1 > ANY (SELECT * from t1)

794

NULL

795

select 10 > ANY (SELECT * from t1);

796

10 > ANY (SELECT * from t1)

797

798

drop table t1;

799

create table t1 (a varchar(20));

800

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

801

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

802

'A' IN (SELECT * from t1)

803

804

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

805

'XYZS' IN (SELECT * from t1)

806

807

select NULL IN (SELECT * from t1);

808

NULL IN (SELECT * from t1)

809

NULL

810

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

811

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

812

'A' IN (SELECT * from t1)

813

814

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

815

'DEF' IN (SELECT * from t1)

816

817

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

818

'XYZS' IN (SELECT * from t1)

819

NULL

820

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

821

'A' > ALL (SELECT * from t1)

822

823

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

824

'XYZS' > ALL (SELECT * from t1)

825

NULL

826

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

827

'A' > ANY (SELECT * from t1)

828

NULL

829

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

830

'XYZS' > ANY (SELECT * from t1)

831

832

drop table t1;

833

create table t1 (a float);

834

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

835

select 1.5 IN (SELECT * from t1);

836

1.5 IN (SELECT * from t1)

837

838

select 10.5 IN (SELECT * from t1);

839

10.5 IN (SELECT * from t1)

840

841

select NULL IN (SELECT * from t1);

842

NULL IN (SELECT * from t1)

843

NULL

844

update t1 set a=NULL where a=2.5;

845

select 1.5 IN (SELECT * from t1);

846

1.5 IN (SELECT * from t1)

847

848

select 3.5 IN (SELECT * from t1);

849

3.5 IN (SELECT * from t1)

850

851

select 10.5 IN (SELECT * from t1);

852

10.5 IN (SELECT * from t1)

853

NULL

854

select 1.5 > ALL (SELECT * from t1);

855

1.5 > ALL (SELECT * from t1)

856

857

select 10.5 > ALL (SELECT * from t1);

858

10.5 > ALL (SELECT * from t1)

859

NULL

860

select 1.5 > ANY (SELECT * from t1);

861

1.5 > ANY (SELECT * from t1)

862

NULL

863

select 10.5 > ANY (SELECT * from t1);

864

10.5 > ANY (SELECT * from t1)

865

866

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

867

id select_type table type possible_keys key key_len ref rows filtered Extra

868

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

869

Warnings:

870

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

871

Note 1249 Select 2 was reduced during optimization

872

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

873

select (select a+1) from t1;

874

(select a+1)

875

2.5

876

NULL

877

4.5

878

drop table t1;

879

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

880

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

881

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

882

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

883

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

884

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

885

1 1

886

2 1

887

3 1

888

4 0

889

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

890

id select_type table type possible_keys key key_len ref rows filtered Extra

891

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

892

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

893

Warnings:

894

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"

895

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

896

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

897

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

898

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

899

1 1

900

2 1

901

3 1

902

4 0

903

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

904

id select_type table type possible_keys key key_len ref rows filtered Extra

905

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

906

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

907

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

908

Warnings:

909

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"

910

drop table t1,t2,t3;

911

create table t1 (a float);

912

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

913

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

914

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

915

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

916

drop table t1;

917

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

918

create table t2 (a int);

919

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

920

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

921

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;

922

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

923

1 1 a

924

2 0 b

925

NULL 0 NULL

926

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;

927

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

928

1 0 a

929

2 1 b

930

NULL NULL NULL

931

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;

932

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

933

1 0 a

934

2 0 b

935

NULL 0 NULL

936

drop table t1,t2;

937

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

938

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

939

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

940

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

941

942

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

943

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

944

945

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

946

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

947

948

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

949

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

950

951

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

952

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

953

954

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

955

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

956

957

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

958

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

959

960

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

961

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

962

963

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

964

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

965

966

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

967

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

968

drop table t1;

969

CREATE TABLE t1 (a int(1));

970

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

971

id select_type table type possible_keys key key_len ref rows filtered Extra

972

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

973

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

974

Warnings:

975

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

976

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

977

id select_type table type possible_keys key key_len ref rows filtered Extra

978

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

979

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

980

Warnings:

981

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

982

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

983

id select_type table type possible_keys key key_len ref rows filtered Extra

984

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

985

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

986

Warnings:

987

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

988

drop table t1;

989

CREATE TABLE `t1` (

990

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

991

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

992

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

993

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

994

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

995

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

996

KEY `topic` (`topic`)

997

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

998

CREATE TABLE `t2` (

999

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

1000

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

1001

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

1002

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

1003

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

1004

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

1005

KEY `topic` (`topic`)

1006

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

1007

CREATE TABLE `t3` (

1008

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

1009

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

1010

PRIMARY KEY (`numeropost`),

1011

UNIQUE KEY `maxnumrep` (`maxnumrep`)

1012

) ENGINE=MyISAM CHARSET=latin1;

1013

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

1014

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

1015

INSERT INTO t3 VALUES (1,1);

1016

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

1017

numeropost=topic);

1018

topic

1019

1020

select * from t1;

1021

mot topic date pseudo

1022

joce 1 0000-00-00 joce

1023

test 2 0000-00-00 test

1024

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

1025

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

1026

select * from t1;

1027

mot topic date pseudo

1028

joce 1 0000-00-00 joce

1029

drop table t1, t2, t3;

1030

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

1031

a (SELECT a)

1032

1 1

1033

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

1034

SHOW CREATE TABLE t1;

1035

Table Create Table

1036

t1 CREATE TABLE "t1" (

1037

"a" int(1) NOT NULL,

1038

"(SELECT 1)" int(1) NOT NULL

1039

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1040

drop table t1;

1041

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

1042

SHOW CREATE TABLE t1;

1043

Table Create Table

1044

t1 CREATE TABLE "t1" (

1045

"a" int(1) NOT NULL,

1046

"(SELECT a)" int(1) NOT NULL

1047

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1048

drop table t1;

1049

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

1050

SHOW CREATE TABLE t1;

1051

Table Create Table

1052

t1 CREATE TABLE "t1" (

1053

"a" int(1) NOT NULL,

1054

"(SELECT a+0)" int(3) NOT NULL

1055

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1056

drop table t1;

1057

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

1058

select * from t1;

1059

1060

1061

SHOW CREATE TABLE t1;

1062

Table Create Table

1063

t1 CREATE TABLE "t1" (

1064

"a" bigint(20) NOT NULL

1065

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1066

drop table t1;

1067

create table t1 (a int);

1068

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

1069

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

1070

from t1;

1071

id select_type table type possible_keys key key_len ref rows filtered Extra

1072

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

1073

2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1074

3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1075

Warnings:

1076

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"

1077

drop table t1;

1078

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

1079

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

1080

CREATE TABLE t1 (

1081

ID int(11) NOT NULL auto_increment,

1082

name char(35) NOT NULL default '',

1083

t2 char(3) NOT NULL default '',

1084

District char(20) NOT NULL default '',

1085

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

1086

PRIMARY KEY (ID)

1087

) ENGINE=MyISAM;

1088

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

1089

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

1090

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

1091

CREATE TABLE t2 (

1092

Code char(3) NOT NULL default '',

1093

Name char(52) NOT NULL default '',

1094

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

1095

Region char(26) NOT NULL default '',

1096

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

1097

IndepYear smallint(6) default NULL,

1098

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

1099

LifeExpectancy float(3,1) default NULL,

1100

GNP float(10,2) default NULL,

1101

GNPOld float(10,2) default NULL,

1102

LocalName char(45) NOT NULL default '',

1103

GovernmentForm char(45) NOT NULL default '',

1104

HeadOfState char(60) default NULL,

1105

Capital int(11) default NULL,

1106

Code2 char(2) NOT NULL default '',

1107

PRIMARY KEY (Code)

1108

) ENGINE=MyISAM;

1109

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

1110

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

1111

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

1112

Continent Name Population

1113

Oceania Sydney 3276207

1114

drop table t1, t2;

1115

CREATE TABLE `t1` (

1116

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

1117

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

1118

PRIMARY KEY (`id`),

1119

UNIQUE KEY `pseudo` (`pseudo`)

1120

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

1121

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

1122

SELECT 0 IN (SELECT 1 FROM t1 a);

1123

0 IN (SELECT 1 FROM t1 a)

1124

1125

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

1126

id select_type table type possible_keys key key_len ref rows filtered Extra

1127

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

1128

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

1129

Warnings:

1130

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

1131

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

1132

SELECT 0 IN (SELECT 1 FROM t1 a);

1133

0 IN (SELECT 1 FROM t1 a)

1134

1135

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

1136

id select_type table type possible_keys key key_len ref rows filtered Extra

1137

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

1138

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

1139

Warnings:

1140

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

1141

drop table t1;

1142

CREATE TABLE `t1` (

1143

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

1144

PRIMARY KEY (`i`)

1145

) ENGINE=MyISAM CHARSET=latin1;

1146

INSERT INTO t1 VALUES (1);

1147

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

1148

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

1149

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

1150

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

1151

select * from t1;

1152

1153

1154

drop table t1;

1155

CREATE TABLE t1 (

1156

id int(11) default NULL

1157

) ENGINE=MyISAM CHARSET=latin1;

1158

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

1159

CREATE TABLE t2 (

1160

id int(11) default NULL,

1161

name varchar(15) default NULL

1162

) ENGINE=MyISAM CHARSET=latin1;

1163

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

1164

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

1165

select * from t2;

1166

id name

1167

4 vita

1168

1 lenka

1169

2 lenka

1170

1 lenka

1171

drop table t1,t2;

1172

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

1173

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

1174

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

1175

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

1176

NULL

1177

drop table t1;

1178

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

1179

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

1180

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

1181

id select_type table type possible_keys key key_len ref rows filtered Extra

1182

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

1183

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

1184

Warnings:

1185

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

1186

drop table t1;

1187

CREATE TABLE t1 (

1188

ID int(10) unsigned NOT NULL auto_increment,

1189

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

1190

REF_ID int(10) unsigned default NULL,

1191

REF_SUB int(3) unsigned default '0',

1192

PRIMARY KEY (ID,SUB_ID),

1193

UNIQUE KEY t1_PK (ID,SUB_ID),

1194

KEY t1_FK (REF_ID,REF_SUB),

1195

KEY t1_REFID (REF_ID)

1196

) ENGINE=MyISAM CHARSET=cp1251;

1197

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

1198

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

1199

REF_ID

1200

DROP TABLE t1;

1201

create table t1 (a int, b int);

1202

create table t2 (a int, b int);

1203

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

1204

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

1205

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

1206

Warnings:

1207

Error 1242 Subquery returns more than 1 row

1208

select * from t1;

1209

a b

1210

1 1

1211

2 NULL

1212

3 1

1213

drop table t1, t2;

1214

CREATE TABLE `t1` (

1215

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

1216

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

1217

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

1218

PRIMARY KEY (`id`),

1219

UNIQUE KEY `email` (`email`),

1220

UNIQUE KEY `pseudo` (`pseudo`)

1221

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

1222

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

1223

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

1224

a b

1225

test test

1226

test1 test1

1227

drop table if exists t1;

1228

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

1229

1230

1231

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

1232

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

1233

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

1234

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

1235

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

1236

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

1237

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

1238

1239

1240

1241

1242

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

1243

id select_type table type possible_keys key key_len ref rows filtered Extra

1244

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

1245

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

1246

Warnings:

1247

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

1248

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

1249

1250

1251

1252

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

1253

id select_type table type possible_keys key key_len ref rows filtered Extra

1254

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

1255

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

1256

Warnings:

1257

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

1258

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

1259

1260

1261

1262

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

1263

id select_type table type possible_keys key key_len ref rows filtered Extra

1264

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

1265

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

1266

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

1267

Warnings:

1268

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

1269

drop table t1, t2, t3;

1270

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

1271

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

1272

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

1273

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

1274

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

1275

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

1276

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

1277

1278

1279

1280

1281

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

1282

id select_type table type possible_keys key key_len ref rows filtered Extra

1283

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

1284

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

1285

Warnings:

1286

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

1287

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

1288

1289

1290

1291

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

1292

id select_type table type possible_keys key key_len ref rows filtered Extra

1293

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

1294

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

1295

Warnings:

1296

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

1297

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

1298

1299

1300

1301

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

1302

id select_type table type possible_keys key key_len ref rows filtered Extra

1303

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

1304

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

1305

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

1306

Warnings:

1307

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

1308

insert into t1 values (3,31);

1309

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

1310

1311

1312

1313

1314

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

1315

1316

1317

1318

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

1319

id select_type table type possible_keys key key_len ref rows filtered Extra

1320

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

1321

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

1322

Warnings:

1323

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

1324

drop table t1, t2, t3;

1325

create table t1 (a int, b int);

1326

create table t2 (a int, b int);

1327

create table t3 (a int, b int);

1328

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

1329

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

1330

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

1331

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;

1332

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)

1333

3 1

1334

2 2

1335

1 2

1336

drop table t1,t2,t3;

1337

create table t1 (s1 int);

1338

create table t2 (s1 int);

1339

insert into t1 values (1);

1340

insert into t2 values (1);

1341

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

1342

1343

1344

drop table t1,t2;

1345

create table t1 (s1 int);

1346

create table t2 (s1 int);

1347

insert into t1 values (1);

1348

insert into t2 values (1);

1349

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

1350

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

1351

DROP TABLE t1, t2;

1352

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

1353

s2 CHAR(5) COLLATE latin1_swedish_ci);

1354

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

1355

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

1356

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

1357

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

1358

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

1359

drop table t1;

1360

create table t1(toid int,rd int);

1361

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

1362

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

1363

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

1364

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

1365

userid pmtotal pmnew calc_total calc_new

1366

1 0 0 9 3

1367

2 0 0 4 2

1368

drop table t1, t2;

1369

create table t1 (s1 char(5));

1370

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

1371

ERROR 21000: Operand should contain 1 column(s)

1372

insert into t1 values ('tttt');

1373

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

1374

1375

tttt

1376

explain extended (select * from t1);

1377

id select_type table type possible_keys key key_len ref rows filtered Extra

1378

1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00

1379

Warnings:

1380

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

1381

(select * from t1);

1382

1383

tttt

1384

drop table t1;

1385

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

1386

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

1387

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

1388

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

1389

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

1390

s1 s1 NOT IN (SELECT s1 FROM t2)

1391

a1 0

1392

a2 0

1393

a3 1

1394

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

1395

s1 s1 = ANY (SELECT s1 FROM t2)

1396

a1 1

1397

a2 1

1398

a3 0

1399

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

1400

s1 s1 <> ALL (SELECT s1 FROM t2)

1401

a1 0

1402

a2 0

1403

a3 1

1404

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

1405

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

1406

a1 0

1407

a2 1

1408

a3 1

1409

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

1410

id select_type table type possible_keys key key_len ref rows filtered Extra

1411

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

1412

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

1413

Warnings:

1414

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"

1415

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

1416

id select_type table type possible_keys key key_len ref rows filtered Extra

1417

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

1418

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

1419

Warnings:

1420

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"

1421

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

1422

id select_type table type possible_keys key key_len ref rows filtered Extra

1423

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

1424

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

1425

Warnings:

1426

1427

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

1428

id select_type table type possible_keys key key_len ref rows filtered Extra

1429

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

1430

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

1431

Warnings:

1432

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"

1433

drop table t1,t2;

1434

create table t2 (a int, b int);

1435

create table t3 (a int);

1436

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

1437

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

1438

1439

1440

1441

1442

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

1443

id select_type table type possible_keys key key_len ref rows filtered Extra

1444

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

1445

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

1446

Warnings:

1447

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

1448

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

1449

1450

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

1451

id select_type table type possible_keys key key_len ref rows filtered Extra

1452

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

1453

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

1454

Warnings:

1455

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

1456

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

1457

1458

1459

1460

1461

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

1462

id select_type table type possible_keys key key_len ref rows filtered Extra

1463

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

1464

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

1465

Warnings:

1466

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

1467

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

1468

1469

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

1470

id select_type table type possible_keys key key_len ref rows filtered Extra

1471

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

1472

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

1473

Warnings:

1474

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

1475

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

1476

1477

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

1478

id select_type table type possible_keys key key_len ref rows filtered Extra

1479

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

1480

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

1481

Warnings:

1482

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

1483

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

1484

1485

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

1486

id select_type table type possible_keys key key_len ref rows filtered Extra

1487

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

1488

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

1489

Warnings:

1490

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

1491

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

1492

1493

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

1494

id select_type table type possible_keys key key_len ref rows filtered Extra

1495

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

1496

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

1497

Warnings:

1498

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

1499

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

1500

1501

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

1502

id select_type table type possible_keys key key_len ref rows filtered Extra

1503

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

1504

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

1505

Warnings:

1506

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

1507

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

1508

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

1509

1510

1511

1512

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

1513

id select_type table type possible_keys key key_len ref rows filtered Extra

1514

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

1515

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

1516

Warnings:

1517

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

1518

drop table t2, t3;

1519

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 ;

1520

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

1521

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

1522

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

1523

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

1524

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

1525

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

1526

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

1527

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

1528

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

1529

-1 Valid 1

1530

-1 Valid 2 1

1531

-1 Should Not Return 0

1532

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

1533

dbid name

1534

-1 Valid

1535

-1 Valid 2

1536

drop table t1,t2,t3,t4;

1537

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

1538

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

1539

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

1540

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

1541

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

1542

ERROR 21000: Operand should contain 3 column(s)

1543

DROP TABLE t1,t2;

1544

create table t1 (s1 char);

1545

insert into t1 values ('e');

1546

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

1547

1548

1549

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

1550

1551

1552

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

1553

id select_type table type possible_keys key key_len ref rows filtered Extra

1554

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00

1555

2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

1556

3 UNION t1 system NULL NULL NULL NULL 1 100.00

1557

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

1558

Warnings:

1559

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

1560

drop table t1;

1561

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

1562

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

1563

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

1564

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

1565

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;

1566

phone code

1567

69294728265 6

1568

18621828126 1862

1569

89356874041 NULL

1570

95895001874 NULL

1571

drop table t1, t2;

1572

create table t1 (s1 int);

1573

create table t2 (s1 int);

1574

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

1575

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

1576

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

1577

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

1578

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

1579

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

1580

drop table t1, t2;

1581

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

1582

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

1583

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

1584

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

1585

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

1586

COLC

1587

DROP TABLE t1, t2;

1588

CREATE TABLE t1 (a int(1));

1589

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

1590

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

1591

(SELECT a)

1592

1593

1594

1595

1596

1597

DROP TABLE t1;

1598

CREATE TABLE `t1` (

1599

`id` int(11) NOT NULL auto_increment,

1600

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

1601

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

1602

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

1603

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

1604

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

1605

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

1606

PRIMARY KEY (`id`),

1607

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

1608

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

1609

);

1610

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

1611

CREATE TABLE `t2` (

1612

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

1613

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

1614

PRIMARY KEY (`id`)

1615

);

1616

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

1617

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;

1618

id max_anno_dep PIPPO

1619

16 1987 1

1620

50 1990 0

1621

51 1990 NULL

1622

DROP TABLE t1, t2;

1623

create table t1 (a int);

1624

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

1625

SET SQL_SELECT_LIMIT=1;

1626

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

1627

sum(a)

1628

1629

select 2 in (select * from t1);

1630

2 in (select * from t1)

1631

1632

SET SQL_SELECT_LIMIT=default;

1633

drop table t1;

1634

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

1635

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

1636

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

1637

a b

1638

1 1

1639

1 2

1640

1 3

1641

DROP TABLE t1;

1642

create table t1(val varchar(10));

1643

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

1644

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

1645

count(*)

1646

1647

drop table t1;

1648

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

1649

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

1650

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

1651

id text

1652

8 text8

1653

9 text9

1654

10 text10

1655

11 text11

1656

12 text12

1657

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

1658

id text

1659

8 text8

1660

9 text9

1661

10 text10

1662

11 text11

1663

12 text12

1664

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

1665

id select_type table type possible_keys key key_len ref rows filtered Extra

1666

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

1667

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

1668

Warnings:

1669

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

1670

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

1671

id select_type table type possible_keys key key_len ref rows filtered Extra

1672

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

1673

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

1674

Warnings:

1675

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

1676

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

1677

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

1678

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

1679

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

1680

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

1681

id text id text id text

1682

1 text1 1 text1 1 text1

1683

2 text2 2 text2 2 text2

1684

3 text3 3 text3 3 text3

1685

4 text4 4 text4 4 text4

1686

5 text5 5 text5 5 text5

1687

6 text6 6 text6 6 text6

1688

7 text7 7 text7 7 text7

1689

8 text8 8 text8 8 text8

1690

9 text9 9 text9 9 text9

1691

10 text10 10 text10 10 text10

1692

11 text11 11 text1 11 text11

1693

12 text12 12 text2 12 text12

1694

1000 text1000 NULL NULL 1000 text1000

1695

1001 text1001 NULL NULL 1000 text1000

1696

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

1697

id select_type table type possible_keys key key_len ref rows filtered Extra

1698

1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00

1699

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

1700

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

1701

Warnings:

1702

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

1703

drop table t1,t2;

1704

create table t1 (a int);

1705

insert into t1 values (1);

1706

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

1707

id select_type table type possible_keys key key_len ref rows Extra

1708

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

1709

2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 1

1710

drop table t1;

1711

create table t1(id int);

1712

create table t2(id int);

1713

create table t3(flag int);

1714

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

1715

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

1716

drop table t1,t2,t3;

1717

CREATE TABLE t1 (id INT);

1718

CREATE TABLE t2 (id INT);

1719

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

1720

INSERT INTO t2 VALUES (1);

1721

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

1722

id c

1723

1 1

1724

2 0

1725

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

1730

id c

1731

1 1

1732

2 0

1733

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;

1734

id c

1735

1 1

1736

2 0

1737

DROP TABLE t1,t2;

1738

CREATE TABLE t1 ( a int, b int );

1739

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

1740

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

1741

1742

1743

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

1744

1745

1746

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

1747

1748

1749

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

1750

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

1762

1763

1764

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

1765

1766

1767

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

1768

1769

1770

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

1771

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

ALTER TABLE t1 ADD INDEX (a);

1783

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

1784

1785

1786

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

1787

1788

1789

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

1790

1791

1792

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

1793

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

1805

1806

1807

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

1808

1809

1810

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

1811

1812

1813

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

1814

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

1826

1827

1828

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

1829

1830

1831

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

1832

1833

1834

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

1835

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

1847

1848

1849

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

1850

1851

1852

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

1853

1854

1855

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

1856

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

1868

1869

1870

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

1871

1872

1873

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

1874

1875

1876

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

1877

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

1889

1890

1891

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

1892

1893

1894

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

1895

1896

1897

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

1898

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

1910

1911

1912

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

1913

1914

1915

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

1916

1917

1918

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

1919

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

1931

1932

1933

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

1934

1935

1936

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

1937

1938

1939

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

1940

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

1952

ERROR 21000: Operand should contain 1 column(s)

1953

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

1954

ERROR 21000: Operand should contain 1 column(s)

1955

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

1956

ERROR 21000: Operand should contain 1 column(s)

1957

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

1958

ERROR 21000: Operand should contain 1 column(s)

1959

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

1962

ERROR 21000: Operand should contain 1 column(s)

1963

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

1968

ERROR 21000: Operand should contain 2 column(s)

1969

SELECT a FROM t1 WHERE a = 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,2 FROM t1 WHERE b = 2);

1972

1973

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

1974

ERROR 21000: Operand should contain 2 column(s)

1975

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

1976

ERROR 21000: Operand should contain 1 column(s)

1977

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

1978

1979

1980

1981

1982

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

1983

1984

1985

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

1986

1987

1988

1989

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

1990

1991

1992

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

1993

1994

1995

1996

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

1997

1998

1999

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

2000

2001

2002

2003

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

2004

2005

2006

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

2007

2008

2009

2010

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

2011

2012

2013

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

2014

2015

2016

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

2017

2018

2019

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

2020

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

2032

2033

2034

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

2035

2036

2037

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

2038

2039

2040

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

2041

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 > ANY (SELECT a FROM t1 group by a HAVING a = 2);

2053

2054

2055

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

2056

2057

2058

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

2059

2060

2061

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

2062

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 > ALL (SELECT a FROM t1 group by a HAVING a = 2);

2074

2075

2076

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

2077

2078

2079

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

2080

2081

2082

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

2083

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 concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;

2095

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

2096

2097

2098

2099

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

2100

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

2101

2102

2103

2104

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

2105

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

2106

2107

2108

2109

DROP TABLE t1;

2110

CREATE TABLE t1 ( a double, b double );

2111

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

2112

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

2113

2114

2115

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

2116

2117

2118

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

2119

2120

2121

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

2122

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

2134

2135

2136

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

2137

2138

2139

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

2140

2141

2142

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

2143

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

DROP TABLE t1;

2155

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

2156

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

2157

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

2158

2159

2160

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

2161

2162

2163

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

2164

2165

2166

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

2167

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

2179

2180

2181

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

2182

2183

2184

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

2185

2186

2187

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

2188

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

DROP TABLE t1;

2200

create table t1 (a int, b int);

2201

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

2202

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

2203

a b

2204

1 2

2205

3 4

2206

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

2207

id select_type table type possible_keys key key_len ref rows filtered Extra

2208

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

2209

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

2210

Warnings:

2211

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

2212

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

2213

drop table t1;

2214

CREATE TABLE t1 (t1_a int);

2215

INSERT INTO t1 VALUES (1);

2216

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

2217

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

2218

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

2219

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

2220

t1_a t2_a t2_b

2221

1 1 2

2222

DROP TABLE t1, t2;

2223

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

2224

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

2225

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

2226

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

2227

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

2228

id name id pet

2229

1 Tim 1 Fido

2230

2 Rebecca 2 Spot

2231

3 NULL 3 Felix

2232

drop table t1,t2;

2233

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

2234

insert into t1 values (1);

2235

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

2236

insert into t2 values (1,2);

2237

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

2238

a C

2239

1 1

2240

drop table t1,t2;

2241

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

2242

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

2243

create table t2 (a int);

2244

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

2245

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

2246

a b

2247

1 ball

2248

2 ball games

2249

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

2250

2251

2252

2253

drop table t1,t2;

2254

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

2255

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

2256

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

2257

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

2258

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

2259

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

2260

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

2261

IZAVORGANG_ID

2262

D0000000001

2263

drop table t1;

2264

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

2265

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

2266

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

2267

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

2268

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

2269

aid bid

2270

1 1

2271

2 1

2272

alter table t2 drop primary key;

2273

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

2274

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

2275

aid bid

2276

1 1

2277

2 1

2278

alter table t2 drop key KEY1;

2279

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

2280

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

2281

aid bid

2282

1 1

2283

2 1

2284

drop table t1,t2;

2285

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

2286

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

2287

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

2288

howmanyvalues count(*)

2289

1 1

2290

2 2

2291

3 3

2292

4 4

2293

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

2294

howmanyvalues mycount

2295

1 1

2296

2 2

2297

3 3

2298

4 4

2299

CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);

2300

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

2301

howmanyvalues mycount

2302

1 1

2303

2 2

2304

3 3

2305

4 4

2306

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

2307

howmanyvalues mycount

2308

1 1

2309

2 2

2310

3 3

2311

4 4

2312

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

2313

howmanyvalues mycount

2314

1 1

2315

2 1

2316

3 1

2317

4 1

2318

drop table t1;

2319

create table t1 (x int);

2320

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;

2321

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

2322

drop table t1;

2323

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

2324

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

2325

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

2326

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

2327

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;

2328

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

2329

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;

2330

2331

700

2332

NULL

2333

drop tables t1,t2;

2334

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

2335

create table t1 (a int, b int);

2336

create table t2 (a int, b int);

2337

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

2338

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

2339

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

2340

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

2341

1 1 3

2342

1 2 1

2343

1 3 NULL

2344

2 4 NULL

2345

2 5 NULL

2346

drop table t1, t2;

2347

create table t1 (id int);

2348

create table t2 (id int, body text, fulltext (body));

2349

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

2350

insert into t2 values (1,'test'), (2,'mysql'), (3,'test'), (4,'test');

2351

select count(distinct id) from t1 where id in (select id from t2 where match(body) against ('mysql' in boolean mode));

2352

count(distinct id)

2353

2354

drop table t2,t1;

2355

create table t1 (s1 int,s2 int);

2356

insert into t1 values (20,15);

2357

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

2358

s1 s2

2359

drop table t1;

2360

create table t1 (s1 int);

2361

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

2362

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

2363

2364

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

2365

s1 s1 < all (select s1 from t1)

2366

1 0

2367

NULL NULL

2368

drop table t1;

2369

CREATE TABLE t1 (

2370

Code char(3) NOT NULL default '',

2371

Name char(52) NOT NULL default '',

2372

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

2373

Region char(26) NOT NULL default '',

2374

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

2375

IndepYear smallint(6) default NULL,

2376

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

2377

LifeExpectancy float(3,1) default NULL,

2378

GNP float(10,2) default NULL,

2379

GNPOld float(10,2) default NULL,

2380

LocalName char(45) NOT NULL default '',

2381

GovernmentForm char(45) NOT NULL default '',

2382

HeadOfState char(60) default NULL,

2383

Capital int(11) default NULL,

2384

Code2 char(2) NOT NULL default ''

2385

) ENGINE=MyISAM;

2386

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

2387

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

2388

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

2389

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

2390

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

2391

SELECT DISTINCT Continent AS c FROM t1 outr WHERE

2392

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

2393

Population < 200);

2394

2395

Oceania

2396

drop table t1;

2397

create table t1 (a1 int);

2398

create table t2 (b1 int);

2399

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

2400

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

2401

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

2402

2403

drop table t1,t2;

2404

create table t1 (a integer, b integer);

2405

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

2406

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

2407

NULL

2408

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

2409

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

2410

NULL

2411

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

2412

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

2413

2414

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

2415

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

2416

2417

drop table t1;

2418

create table t1 (a integer, b integer);

2419

select row(1,(2,2)) in (select * from t1 );

2420

ERROR 21000: Operand should contain 2 column(s)

2421

select row(1,(2,2)) = (select * from t1 );

2422

ERROR 21000: Operand should contain 2 column(s)

2423

select (select * from t1) = row(1,(2,2));

2424

ERROR 21000: Operand should contain 1 column(s)

2425

drop table t1;

2426

create table t1 (a integer);

2427

insert into t1 values (1);

2428

select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;

2429

ERROR 42S22: Reference 'xx' not supported (forward reference in item list)

2430

select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;

2431

ERROR 42S22: Reference 'xx' not supported (forward reference in item list)

2432

select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx );

2433

xx 1 = ALL ( select 1 from t1 where 1 = xx )

2434

1 1

2435

select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;

2436

ERROR 42S22: Reference 'xx' not supported (forward reference in item list)

2437

select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx from DUAL;

2438

ERROR 42S22: Reference 'xx' not supported (forward reference in item list)

2439

drop table t1;

2440

CREATE TABLE t1 (

2441

categoryId int(11) NOT NULL,

2442

courseId int(11) NOT NULL,

2443

startDate datetime NOT NULL,

2444

endDate datetime NOT NULL,

2445

createDate datetime NOT NULL,

2446

modifyDate timestamp NOT NULL,

2447

attributes text NOT NULL

2448

);

2449

INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),

2450

(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),

2451

(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),

2452

(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),

2453

(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),

2454

(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),

2455

(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),

2456

(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),

2457

(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');

2458

CREATE TABLE t2 (

2459

userId int(11) NOT NULL,

2460

courseId int(11) NOT NULL,

2461

date datetime NOT NULL

2462

);

2463

INSERT INTO t2 VALUES (5141,71,'2003-11-18'),

2464

(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),

2465

(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),

2466

(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),

2467

(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),

2468

(5141,89,'2004-10-22'),(5141,51,'2004-10-26');

2469

CREATE TABLE t3 (

2470

groupId int(11) NOT NULL,

2471

parentId int(11) NOT NULL,

2472

startDate datetime NOT NULL,

2473

endDate datetime NOT NULL,

2474

createDate datetime NOT NULL,

2475

modifyDate timestamp NOT NULL,

2476

ordering int(11)

2477

);

2478

INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);

2479

CREATE TABLE t4 (

2480

id int(11) NOT NULL,

2481

groupTypeId int(11) NOT NULL,

2482

groupKey varchar(50) NOT NULL,

2483

name text,

2484

ordering int(11),

2485

description text,

2486

createDate datetime NOT NULL,

2487

modifyDate timestamp NOT NULL

2488

);

2489

INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),

2490

(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');

2491

CREATE TABLE t5 (

2492

userId int(11) NOT NULL,

2493

groupId int(11) NOT NULL,

2494

createDate datetime NOT NULL,

2495

modifyDate timestamp NOT NULL

2496

);

2497

INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');

2498

select

2499

count(distinct t2.userid) pass,

2500

groupstuff.*,

2501

count(t2.courseid) crse,

2502

t1.categoryid,

2503

t2.courseid,

2504

date_format(date, '%b%y') as colhead

2505

from t2

2506

join t1 on t2.courseid=t1.courseid

2507

join

2508

(

2509

select

2510

t5.userid,

2511

parentid,

2512

parentgroup,

2513

childid,

2514

groupname,

2515

grouptypeid

2516

from t5

2517

join

2518

(

2519

select t4.id as parentid,

2520

t4.name as parentgroup,

2521

t4.id as childid,

2522

t4.name as groupname,

2523

t4.grouptypeid

2524

from t4

2525

) as gin on t5.groupid=gin.childid

2526

) as groupstuff on t2.userid = groupstuff.userid

2527

group by

2528

groupstuff.groupname, colhead , t2.courseid;

2529

pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead

2530

1 5141 12 group2 12 group2 5 1 5 12 Aug04

2531

1 5141 12 group2 12 group2 5 1 1 41 Aug04

2532

1 5141 12 group2 12 group2 5 1 2 52 Aug04

2533

1 5141 12 group2 12 group2 5 1 2 53 Aug04

2534

1 5141 12 group2 12 group2 5 1 3 51 Oct04

2535

1 5141 12 group2 12 group2 5 1 1 86 Oct04

2536

1 5141 12 group2 12 group2 5 1 1 87 Oct04

2537

1 5141 12 group2 12 group2 5 1 2 88 Oct04

2538

1 5141 12 group2 12 group2 5 1 2 89 Oct04

2539

drop table t1, t2, t3, t4, t5;

2540

create table t1 (a int);

2541

insert into t1 values (1), (2), (3);

2542

SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);

2543

2544

2545

2546

2547

drop table t1;

2548

create table t1 (a int, b int);

2549

insert into t1 values (1,2);

2550

select 1 = (select * from t1);

2551

ERROR 21000: Operand should contain 1 column(s)

2552

select (select * from t1) = 1;

2553

ERROR 21000: Operand should contain 2 column(s)

2554

select (1,2) = (select a from t1);

2555

ERROR 21000: Operand should contain 2 column(s)

2556

select (select a from t1) = (1,2);

2557

ERROR 21000: Operand should contain 1 column(s)

2558

select (1,2,3) = (select * from t1);

2559

ERROR 21000: Operand should contain 3 column(s)

2560

select (select * from t1) = (1,2,3);

2561

ERROR 21000: Operand should contain 2 column(s)

2562

drop table t1;

2563

CREATE TABLE `t1` (

2564

`itemid` bigint(20) unsigned NOT NULL auto_increment,

2565

`sessionid` bigint(20) unsigned default NULL,

2566

`time` int(10) unsigned NOT NULL default '0',

2567

`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT

2568

NULL default '',

2569

`data` text collate latin1_general_ci NOT NULL,

2570

PRIMARY KEY (`itemid`)

2571

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2572

INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');

2573

CREATE TABLE `t2` (

2574

`sessionid` bigint(20) unsigned NOT NULL auto_increment,

2575

`pid` int(10) unsigned NOT NULL default '0',

2576

`date` int(10) unsigned NOT NULL default '0',

2577

`ip` varchar(15) collate latin1_general_ci NOT NULL default '',

2578

PRIMARY KEY (`sessionid`)

2579

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2580

INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');

2581

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;

2582

ip count( e.itemid )

2583

10.10.10.1 1

2584

drop tables t1,t2;

2585

create table t1 (fld enum('0','1'));

2586

insert into t1 values ('1');

2587

select * from (select max(fld) from t1) as foo;

2588

max(fld)

2589

2590

drop table t1;

2591

CREATE TABLE t1 (one int, two int, flag char(1));

2592

CREATE TABLE t2 (one int, two int, flag char(1));

2593

INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');

2594

INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');

2595

SELECT * FROM t1

2596

WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');

2597

one two flag

2598

5 6 N

2599

7 8 N

2600

SELECT * FROM t1

2601

WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');

2602

one two flag

2603

5 6 N

2604

7 8 N

2605

insert into t2 values (null,null,'N');

2606

insert into t2 values (null,3,'0');

2607

insert into t2 values (null,5,'0');

2608

insert into t2 values (10,null,'0');

2609

insert into t1 values (10,3,'0');

2610

insert into t1 values (10,5,'0');

2611

insert into t1 values (10,10,'0');

2612

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;

2613

one two test

2614

1 2 NULL

2615

2 3 NULL

2616

3 4 NULL

2617

5 6 1

2618

7 8 1

2619

10 3 NULL

2620

10 5 NULL

2621

10 10 NULL

2622

SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');

2623

one two

2624

5 6

2625

7 8

2626

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;

2627

one two test

2628

1 2 NULL

2629

2 3 NULL

2630

3 4 NULL

2631

5 6 1

2632

7 8 1

2633

10 3 NULL

2634

10 5 NULL

2635

10 10 NULL

2636

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;

2637

one two test

2638

1 2 0

2639

2 3 NULL

2640

3 4 0

2641

5 6 0

2642

7 8 0

2643

10 3 NULL

2644

10 5 NULL

2645

10 10 NULL

2646

SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;

2647

one two test

2648

1 2 0

2649

2 3 NULL

2650

3 4 0

2651

5 6 0

2652

7 8 0

2653

10 3 NULL

2654

10 5 NULL

2655

10 10 NULL

2656

explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;

2657

id select_type table type possible_keys key key_len ref rows filtered Extra

2658

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2659

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where

2660

Warnings:

2661

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"

2662

explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');

2663

id select_type table type possible_keys key key_len ref rows filtered Extra

2664

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary

2665

1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer

2666

Warnings:

2667

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'))

2668

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;

2669

id select_type table type possible_keys key key_len ref rows filtered Extra

2670

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2671

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort

2672

Warnings:

2673

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"

2674

DROP TABLE t1,t2;

2675

CREATE TABLE t1 (a char(5), b char(5));

2676

INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');

2677

SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));

2678

a b

2679

aaa aaa

2680

DROP TABLE t1;

2681

CREATE TABLE t1 (a int);

2682

CREATE TABLE t2 (a int, b int);

2683

CREATE TABLE t3 (b int NOT NULL);

2684

INSERT INTO t1 VALUES (1), (2), (3), (4);

2685

INSERT INTO t2 VALUES (1,10), (3,30);

2686

SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2687

WHERE t3.b IS NOT NULL OR t2.a > 10;

2688

a b b

2689

SELECT * FROM t1

2690

WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2691

WHERE t3.b IS NOT NULL OR t2.a > 10);

2692

2693

2694

2695

2696

2697

DROP TABLE t1,t2,t3;

2698

CREATE TABLE t1 (f1 INT);

2699

CREATE TABLE t2 (f2 INT);

2700

INSERT INTO t1 VALUES (1);

2701

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);

2702

2703

2704

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);

2705

2706

2707

INSERT INTO t2 VALUES (1);

2708

INSERT INTO t2 VALUES (2);

2709

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);

2710

2711

2712

DROP TABLE t1, t2;

2713

select 1 from dual where 1 < any (select 2);

2714

2715

2716

select 1 from dual where 1 < all (select 2);

2717

2718

2719

select 1 from dual where 2 > any (select 1);

2720

2721

2722

select 1 from dual where 2 > all (select 1);

2723

2724

2725

select 1 from dual where 1 < any (select 2 from dual);

2726

2727

2728

select 1 from dual where 1 < all (select 2 from dual where 1!=1);

2729

2730

2731

create table t1 (s1 char);

2732

insert into t1 values (1),(2);

2733

select * from t1 where (s1 < any (select s1 from t1));

2734

2735

2736

select * from t1 where not (s1 < any (select s1 from t1));

2737

2738

2739

select * from t1 where (s1 < ALL (select s1+1 from t1));

2740

2741

2742

select * from t1 where not(s1 < ALL (select s1+1 from t1));

2743

2744

2745

select * from t1 where (s1+1 = ANY (select s1 from t1));

2746

2747

2748

select * from t1 where NOT(s1+1 = ANY (select s1 from t1));

2749

2750

2751

select * from t1 where (s1 = ALL (select s1/s1 from t1));

2752

2753

2754

select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));

2755

2756

2757

drop table t1;

2758

create table t1 (

2759

retailerID varchar(8) NOT NULL,

2760

statusID int(10) unsigned NOT NULL,

2761

changed datetime NOT NULL,

2762

UNIQUE KEY retailerID (retailerID, statusID, changed)

2763

);

2764

INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");

2765

INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");

2766

INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");

2767

INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");

2768

INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");

2769

INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");

2770

select * from t1 r1

2771

where (r1.retailerID,(r1.changed)) in

2772

(SELECT r2.retailerId,(max(changed)) from t1 r2

2773

group by r2.retailerId);

2774

retailerID statusID changed

2775

0026 2 2006-01-06 12:25:53

2776

0037 2 2006-01-06 12:25:53

2777

0048 1 2006-01-06 12:37:50

2778

0059 1 2006-01-06 12:37:50

2779

drop table t1;

2780

create table t1(a int, primary key (a));

2781

insert into t1 values (10);

2782

create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));

2783

insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');

2784

explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2785

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2786

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

2787

id select_type table type possible_keys key key_len ref rows Extra

2788

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2789

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2790

2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition

2791

SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2792

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2793

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

2794

a a b

2795

10 3 35989

2796

explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2797

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2798

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2799

id select_type table type possible_keys key key_len ref rows Extra

2800

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2801

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2802

2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition; Using MRR

2803

SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2804

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2805

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2806

a a b

2807

10 1 359

2808

drop table t1,t2;

2809

CREATE TABLE t1 (

2810

field1 int NOT NULL,

2811

field2 int NOT NULL,

2812

field3 int NOT NULL,

2813

PRIMARY KEY (field1,field2,field3)

2814

);

2815

CREATE TABLE t2 (

2816

fieldA int NOT NULL,

2817

fieldB int NOT NULL,

2818

PRIMARY KEY (fieldA,fieldB)

2819

);

2820

INSERT INTO t1 VALUES

2821

(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);

2822

INSERT INTO t2 VALUES (1,1), (1,2), (1,3);

2823

SELECT field1, field2, COUNT(*)

2824

FROM t1 GROUP BY field1, field2;

2825

field1 field2 COUNT(*)

2826

1 1 2

2827

1 2 3

2828

1 3 1

2829

SELECT field1, field2

2830

FROM t1

2831

GROUP BY field1, field2

2832

HAVING COUNT(*) >= ALL (SELECT fieldB

2833

FROM t2 WHERE fieldA = field1);

2834

field1 field2

2835

1 2

2836

SELECT field1, field2

2837

FROM t1

2838

GROUP BY field1, field2

2839

HAVING COUNT(*) < ANY (SELECT fieldB

2840

FROM t2 WHERE fieldA = field1);

2841

field1 field2

2842

1 1

2843

1 3

2844

DROP TABLE t1, t2;

2845

CREATE TABLE t1(a int, INDEX (a));

2846

INSERT INTO t1 VALUES (1), (3), (5), (7);

2847

INSERT INTO t1 VALUES (NULL);

2848

CREATE TABLE t2(a int);

2849

INSERT INTO t2 VALUES (1),(2),(3);

2850

EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;

2851

id select_type table type possible_keys key key_len ref rows Extra

2852

1 PRIMARY t2 ALL NULL NULL NULL NULL 3

2853

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key

2854

SELECT a, a IN (SELECT a FROM t1) FROM t2;

2855

a a IN (SELECT a FROM t1)

2856

1 1

2857

2 NULL

2858

3 1

2859

DROP TABLE t1,t2;

2860

CREATE TABLE t1 (a DATETIME);

2861

INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');

2862

CREATE TABLE t2 AS SELECT

2863

(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a

2864

FROM t1 WHERE a > '2000-01-01';

2865

SHOW CREATE TABLE t2;

2866

Table Create Table

2867

t2 CREATE TABLE "t2" (

2868

"sub_a" datetime

2869

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2870

CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');

2871

SHOW CREATE TABLE t3;

2872

Table Create Table

2873

t3 CREATE TABLE "t3" (

2874

"a" datetime

2875

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2876

DROP TABLE t1,t2,t3;

2877

CREATE TABLE t1 (a int);

2878

INSERT INTO t1 VALUES (1), (2);

2879

SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;

2880

2881

SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;

2882

2883

2884

2885

EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;

2886

id select_type table type possible_keys key key_len ref rows Extra

2887

1 PRIMARY t1 ALL NULL NULL NULL NULL 2

2888

2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

2889

DROP TABLE t1;

2890

CREATE TABLE t1 (a int);

2891

INSERT INTO t1 VALUES (2), (4), (1), (3);

2892

CREATE TABLE t2 (b int, c int);

2893

INSERT INTO t2 VALUES

2894

(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);

2895

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );

2896

2897

2898

2899

2900

2901

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);

2902

ERROR 21000: Subquery returns more than 1 row

2903

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;

2904

2905

2906

2907

2908

2909

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;

2910

ERROR 21000: Subquery returns more than 1 row

2911

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);

2912

b MAX(c)

2913

1 4

2914

2 2

2915

4 4

2916

SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);

2917

ERROR 21000: Subquery returns more than 1 row

2918

SELECT a FROM t1 GROUP BY a

2919

HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),

2920

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

2921

2922

2923

2924

2925

2926

SELECT a FROM t1 GROUP BY a

2927

HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),

2928

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

2929

ERROR 21000: Subquery returns more than 1 row

2930

SELECT a FROM t1 GROUP BY a

2931

HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),

2932

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;

2933

2934

2935

SELECT a FROM t1 GROUP BY a

2936

HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),

2937

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;

2938

ERROR 21000: Subquery returns more than 1 row

2939

SELECT a FROM t1

2940

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),

2941

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));

2942

2943

2944

2945

2946

2947

SELECT a FROM t1

2948

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),

2949

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

2950

ERROR 21000: Subquery returns more than 1 row

2951

SELECT a FROM t1

2952

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),

2953

(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));

2954

2955

2956

2957

2958

2959

SELECT a FROM t1

2960

ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),

2961

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

2962

ERROR 21000: Subquery returns more than 1 row

2963

DROP TABLE t1,t2;

2964

create table t1 (df decimal(5,1));

2965

insert into t1 values(1.1);

2966

insert into t1 values(2.2);

2967

select * from t1 where df <= all (select avg(df) from t1 group by df);

2968

2969

1.1

2970

select * from t1 where df >= all (select avg(df) from t1 group by df);

2971

2972

2.2

2973

drop table t1;

2974

create table t1 (df decimal(5,1));

2975

insert into t1 values(1.1);

2976

select 1.1 * exists(select * from t1);

2977

1.1 * exists(select * from t1)

2978

1.1

2979

drop table t1;

2980

CREATE TABLE t1 (

2981

grp int(11) default NULL,

2982

a decimal(10,2) default NULL);

2983

insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);

2984

select * from t1;

2985

grp a

2986

1 1.00

2987

2 2.00

2988

2 3.00

2989

3 4.00

2990

3 5.00

2991

3 6.00

2992

NULL NULL

2993

select min(a) from t1 group by grp;

2994

min(a)

2995

NULL

2996

1.00

2997

2.00

2998

4.00

2999

drop table t1;

3000

CREATE table t1 ( c1 integer );

3001

INSERT INTO t1 VALUES ( 1 );

3002

INSERT INTO t1 VALUES ( 2 );

3003

INSERT INTO t1 VALUES ( 3 );

3004

CREATE TABLE t2 ( c2 integer );

3005

INSERT INTO t2 VALUES ( 1 );

3006

INSERT INTO t2 VALUES ( 4 );

3007

INSERT INTO t2 VALUES ( 5 );

3008

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);

3009

c1 c2

3010

1 1

3011

SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2

3012

WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );

3013

c1 c2

3014

1 1

3015

DROP TABLE t1,t2;

3016

CREATE TABLE t1 ( c1 integer );

3017

INSERT INTO t1 VALUES ( 1 );

3018

INSERT INTO t1 VALUES ( 2 );

3019

INSERT INTO t1 VALUES ( 3 );

3020

INSERT INTO t1 VALUES ( 6 );

3021

CREATE TABLE t2 ( c2 integer );

3022

INSERT INTO t2 VALUES ( 1 );

3023

INSERT INTO t2 VALUES ( 4 );

3024

INSERT INTO t2 VALUES ( 5 );

3025

INSERT INTO t2 VALUES ( 6 );

3026

CREATE TABLE t3 ( c3 integer );

3027

INSERT INTO t3 VALUES ( 7 );

3028

INSERT INTO t3 VALUES ( 8 );

3029

SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2

3030

WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );

3031

c1 c2

3032

2 NULL

3033

3 NULL

3034

DROP TABLE t1,t2,t3;

3035

CREATE TABLE `t1` (

3036

`itemid` bigint(20) unsigned NOT NULL auto_increment,

3037

`sessionid` bigint(20) unsigned default NULL,

3038

`time` int(10) unsigned NOT NULL default '0',

3039

`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT

3040

NULL default '',

3041

`data` text collate latin1_general_ci NOT NULL,

3042

PRIMARY KEY (`itemid`)

3043

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

3044

INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');

3045

CREATE TABLE `t2` (

3046

`sessionid` bigint(20) unsigned NOT NULL auto_increment,

3047

`pid` int(10) unsigned NOT NULL default '0',

3048

`date` int(10) unsigned NOT NULL default '0',

3049

`ip` varchar(15) collate latin1_general_ci NOT NULL default '',

3050

PRIMARY KEY (`sessionid`)

3051

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

3052

INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');

3053

3054

ip count( e.itemid )

3055

10.10.10.1 1

3056

drop tables t1,t2;

3057

CREATE TABLE t1 (EMPNUM CHAR(3));

3058

CREATE TABLE t2 (EMPNUM CHAR(3) );

3059

INSERT INTO t1 VALUES ('E1'),('E2');

3060

INSERT INTO t2 VALUES ('E1');

3061

DELETE FROM t1

3062

WHERE t1.EMPNUM NOT IN

3063

(SELECT t2.EMPNUM

3064

FROM t2

3065

WHERE t1.EMPNUM = t2.EMPNUM);

3066

select * from t1;

3067

EMPNUM

3068

3069

DROP TABLE t1,t2;

3070

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);

3071

INSERT INTO t1 VALUES (1, 1);

3072

CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,

3073

PRIMARY KEY(select_id,values_id));

3074

INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);

3075

SELECT values_id FROM t1

3076

WHERE values_id IN (SELECT values_id FROM t2

3077

WHERE select_id IN (1, 0));

3078

values_id

3079

3080

SELECT values_id FROM t1

3081

WHERE values_id IN (SELECT values_id FROM t2

3082

WHERE select_id BETWEEN 0 AND 1);

3083

values_id

3084

3085

SELECT values_id FROM t1

3086

WHERE values_id IN (SELECT values_id FROM t2

3087

WHERE select_id = 0 OR select_id = 1);

3088

values_id

3089

3090

DROP TABLE t1, t2;

3091

create table t1 (fld enum('0','1'));

3092

insert into t1 values ('1');

3093

select * from (select max(fld) from t1) as foo;

3094

max(fld)

3095

3096

drop table t1;

3097

CREATE TABLE t1 (a int, b int);

3098

CREATE TABLE t2 (c int, d int);

3099

CREATE TABLE t3 (e int);

3100

INSERT INTO t1 VALUES

3101

(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);

3102

INSERT INTO t2 VALUES

3103

(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);

3104

INSERT INTO t3 VALUES (10), (30), (10), (20) ;

3105

SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;

3106

a MAX(b) MIN(b)

3107

1 20 10

3108

2 30 10

3109

3 20 20

3110

4 40 40

3111

SELECT * FROM t2;

3112

c d

3113

2 10

3114

2 20

3115

4 10

3116

5 10

3117

3 20

3118

2 40

3119

SELECT * FROM t3;

3120

3121

3122

3123

3124

3125

SELECT a FROM t1 GROUP BY a

3126

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);

3127

3128

3129

3130

SELECT a FROM t1 GROUP BY a

3131

HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);

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

3139

SELECT a FROM t1 GROUP BY a

3140

HAVING a IN (SELECT c FROM t2

3141

WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));

3142

3143

3144

3145

SELECT a FROM t1 GROUP BY a

3146

HAVING a IN (SELECT c FROM t2

3147

WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

3148

3149

3150

3151

SELECT a FROM t1 GROUP BY a

3152

HAVING a IN (SELECT c FROM t2

3153

WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));

3154

3155

3156

SELECT a FROM t1 GROUP BY a

3157

HAVING a IN (SELECT c FROM t2

3158

WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));

3159

3160

3161

SELECT a FROM t1 GROUP BY a

3162

HAVING a IN (SELECT c FROM t2

3163

WHERE MIN(b) < d AND

3164

EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));

3165

3166

3167

SELECT a, SUM(a) FROM t1 GROUP BY a;

3168

a SUM(a)

3169

1 2

3170

2 6

3171

3 3

3172

4 4

3173

SELECT a FROM t1

3174

WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;

3175

3176

3177

3178

SELECT a FROM t1 GROUP BY a

3179

HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);

3180

3181

3182

3183

3184

SELECT a FROM t1

3185

WHERE a < 3 AND

3186

EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;

3187

3188

3189

3190

SELECT a FROM t1

3191

WHERE a < 3 AND

3192

EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);

3193

3194

3195

3196

3197

3198

3199

SELECT t1.a FROM t1 GROUP BY t1.a

3200

HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c

3201

HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e

3202

HAVING SUM(t1.a+t2.c) < t3.e/4));

3203

3204

3205

3206

SELECT t1.a FROM t1 GROUP BY t1.a

3207

HAVING t1.a > ALL(SELECT t2.c FROM t2

3208

WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e

3209

HAVING SUM(t1.a+t2.c) < t3.e/4));

3210

3211

3212

SELECT t1.a FROM t1 GROUP BY t1.a

3213

HAVING t1.a > ALL(SELECT t2.c FROM t2

3214

WHERE EXISTS(SELECT t3.e FROM t3

3215

WHERE SUM(t1.a+t2.c) < t3.e/4));

3216

ERROR HY000: Invalid use of group function

3217

SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;

3218

ERROR HY000: Invalid use of group function

3219

SELECT t1.a FROM t1 GROUP BY t1.a

3220

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3221

HAVING AVG(t2.c+SUM(t1.b)) > 20);

3222

3223

3224

3225

3226

SELECT t1.a FROM t1 GROUP BY t1.a

3227

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3228

HAVING AVG(SUM(t1.b)) > 20);

3229

3230

3231

3232

SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a

3233

HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c

3234

HAVING t2.c+sum > 20);

3235

a sum

3236

2 60

3237

3 20

3238

4 40

3239

DROP TABLE t1,t2,t3;

3240

CREATE TABLE t1 (a varchar(5), b varchar(10));

3241

INSERT INTO t1 VALUES

3242

('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),

3243

('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);

3244

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3245

a b

3246

BBB 4

3247

CCC 7

3248

AAA 8

3249

EXPLAIN

3250

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3251

id select_type table type possible_keys key key_len ref rows Extra

3252

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3253

2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort

3254

ALTER TABLE t1 ADD INDEX(a);

3255

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3256

a b

3257

BBB 4

3258

CCC 7

3259

AAA 8

3260

EXPLAIN

3261

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3262

id select_type table type possible_keys key key_len ref rows Extra

3263

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3264

2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort

3265

DROP TABLE t1;

3266

create table t1( f1 int,f2 int);

3267

insert into t1 values (1,1),(2,2);

3268

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';

3269

3270

crash1

3271

crash1

3272

drop table t1;

3273

create table t1 (c int, key(c));

3274

insert into t1 values (1142477582), (1142455969);

3275

create table t2 (a int, b int);

3276

insert into t2 values (2, 1), (1, 0);

3277

delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;

3278

drop table t1, t2;

3279

create table t1 (i int, j bigint);

3280

insert into t1 values (1, 2), (2, 2), (3, 2);

3281

select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;

3282

min(i)

3283

3284

drop table t1;

3285

CREATE TABLE t1 (i BIGINT UNSIGNED);

3286

INSERT INTO t1 VALUES (10000000000000000000);

3287

INSERT INTO t1 VALUES (1);

3288

CREATE TABLE t2 (i BIGINT UNSIGNED);

3289

INSERT INTO t2 VALUES (10000000000000000000);

3290

INSERT INTO t2 VALUES (1);

3291

/* simple test */

3292

SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;

3293

3294

10000000000000000000

3295

3296

/* subquery test */

3297

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3298

3299

10000000000000000000

3300

/* subquery test with cast*/

3301

SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);

3302

3303

10000000000000000000

3304

DROP TABLE t1;

3305

DROP TABLE t2;

3306

CREATE TABLE t1 (

3307

id bigint(20) unsigned NOT NULL auto_increment,

3308

name varchar(255) NOT NULL,

3309

PRIMARY KEY (id)

3310

);

3311

INSERT INTO t1 VALUES

3312

(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

3313

CREATE TABLE t2 (

3314

id bigint(20) unsigned NOT NULL auto_increment,

3315

mid bigint(20) unsigned NOT NULL,

3316

date date NOT NULL,

3317

PRIMARY KEY (id)

3318

);

3319

INSERT INTO t2 VALUES

3320

(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),

3321

(4, 2, '2006-04-20'), (5, 1, '2006-05-01');

3322

SELECT *,

3323

(SELECT date FROM t2 WHERE mid = t1.id

3324

ORDER BY date DESC LIMIT 0, 1) AS date_last,

3325

(SELECT date FROM t2 WHERE mid = t1.id

3326

ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last

3327

FROM t1;

3328

id name date_last date_next_to_last

3329

1 Balazs 2006-05-01 NULL

3330

2 Joe 2006-04-20 NULL

3331

3 Frank 2006-04-13 NULL

3332

SELECT *,

3333

(SELECT COUNT(*) FROM t2 WHERE mid = t1.id

3334

ORDER BY date DESC LIMIT 1, 1) AS date_count

3335

FROM t1;

3336

id name date_count

3337

1 Balazs NULL

3338

2 Joe NULL

3339

3 Frank NULL

3340

SELECT *,

3341

(SELECT date FROM t2 WHERE mid = t1.id

3342

ORDER BY date DESC LIMIT 0, 1) AS date_last,

3343

(SELECT date FROM t2 WHERE mid = t1.id

3344

ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last

3345

FROM t1;

3346

id name date_last date_next_to_last

3347

1 Balazs 2006-05-01 2006-03-30

3348

2 Joe 2006-04-20 2006-04-06

3349

3 Frank 2006-04-13 NULL

3350

DROP TABLE t1,t2;

3351

CREATE TABLE t1 (

3352

i1 int(11) NOT NULL default '0',

3353

i2 int(11) NOT NULL default '0',

3354

t datetime NOT NULL default '0000-00-00 00:00:00',

3355

PRIMARY KEY (i1,i2,t)

3356

);

3357

INSERT INTO t1 VALUES

3358

(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),

3359

(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),

3360

(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),

3361

(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),

3362

(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),

3363

(24,2,'2005-05-27 12:40:06');

3364

CREATE TABLE t2 (

3365

i1 int(11) NOT NULL default '0',

3366

i2 int(11) NOT NULL default '0',

3367

t datetime default NULL,

3368

PRIMARY KEY (i1)

3369

);

3370

INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');

3371

EXPLAIN

3372

SELECT * FROM t1,t2

3373

WHERE t1.t = (SELECT t1.t FROM t1

3374

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3375

ORDER BY t1.t DESC LIMIT 1);

3376

id select_type table type possible_keys key key_len ref rows Extra

3377

1 PRIMARY t2 system NULL NULL NULL NULL 1

3378

1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index

3379

2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index

3380

SELECT * FROM t1,t2

3381

WHERE t1.t = (SELECT t1.t FROM t1

3382

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3383

ORDER BY t1.t DESC LIMIT 1);

3384

i1 i2 t i1 i2 t

3385

24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40

3386

DROP TABLE t1, t2;

3387

CREATE TABLE t1 (i INT);

3388

(SELECT i FROM t1) UNION (SELECT i FROM t1);

3389

3390

SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS

3391

(

3392

(SELECT i FROM t1) UNION

3393

(SELECT i FROM t1)

3394

);

3395

3396

SELECT * FROM t1

3397

WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));

3398

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

3399

explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))

3400

from t1;

3401

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))

3402

from t1' at line 1

3403

explain select * from t1 where not exists

3404

((select t11.i from t1 t11) union (select t12.i from t1 t12));

3405

id select_type table type possible_keys key key_len ref rows Extra

3406

1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found

3407

2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table

3408

3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table

3409

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL

3410

DROP TABLE t1;

3411

CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));

3412

insert into t1 (a) values (FLOOR(rand() * 100));

3413

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3414

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3415

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3416

insert into t1 (a) select FLOOR(rand() * 100) from t1;

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

SELECT a,

3427

(SELECT REPEAT(' ',250) FROM t1 i1

3428

WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a

3429

FROM t1 ORDER BY a LIMIT 5;

3430

a a

3431

0 NULL

3432

0 NULL

3433

0 NULL

3434

0 NULL

3435

0 NULL

3436

DROP TABLE t1;

3437

CREATE TABLE t1 (a INT, b INT);

3438

CREATE TABLE t2 (a INT);

3439

INSERT INTO t2 values (1);

3440

INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);

3441

SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;

3442

(SELECT COUNT(DISTINCT t1.b) from t2)

3443

3444

3445

3446

SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)

3447

FROM t1 GROUP BY t1.a;

3448

(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)

3449

3450

3451

3452

SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;

3453

COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))

3454

2 2

3455

1 1

3456

1 1

3457

SELECT COUNT(DISTINCT t1.b),

3458

(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)

3459

FROM t1 GROUP BY t1.a;

3460

COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)

3461

2 2

3462

1 1

3463

1 1

3464

SELECT (

3465

SELECT (

3466

SELECT COUNT(DISTINCT t1.b)

3467

)

3468

)

3469

FROM t1 GROUP BY t1.a;

3470

(

3471

SELECT (

3472

SELECT COUNT(DISTINCT t1.b)

3473

)

3474

)

3475

3476

3477

3478

SELECT (

3479

SELECT (

3480

SELECT (

3481

SELECT COUNT(DISTINCT t1.b)

3482

)

3483

)

3484

FROM t1 GROUP BY t1.a LIMIT 1)

3485

FROM t1 t2

3486

GROUP BY t2.a;

3487

(

3488

SELECT (

3489

SELECT (

3490

SELECT COUNT(DISTINCT t1.b)

3491

)

3492

)

3493

FROM t1 GROUP BY t1.a LIMIT 1)

3494

3495

3496

3497

DROP TABLE t1,t2;

3498

CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));

3499

CREATE TABLE t2 (x int auto_increment, y int, z int,

3500

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));

3501

SET SESSION sort_buffer_size = 32 * 1024;

3502

Warnings:

3503

Warning 1292 Truncated incorrect sort_buffer_size value: '32768'

3504

SELECT SQL_NO_CACHE COUNT(*)

3505

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3506

FROM t1) t;

3507

COUNT(*)

3508

3000

3509

SET SESSION sort_buffer_size = 8 * 1024 * 1024;

3510

SELECT SQL_NO_CACHE COUNT(*)

3511

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3512

FROM t1) t;

3513

COUNT(*)

3514

3000

3515

DROP TABLE t1,t2;

3516

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);

3517

CREATE TABLE t2 (c int);

3518

INSERT INTO t1 VALUES ('aa', 1);

3519

INSERT INTO t2 VALUES (1);

3520

SELECT * FROM t1

3521

WHERE EXISTS (SELECT c FROM t2 WHERE c=1

3522

UNION

3523

SELECT c from t2 WHERE c=t1.c);

3524

id c

3525

aa 1

3526

INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);

3527

SELECT * FROM t1

3528

WHERE EXISTS (SELECT c FROM t2 WHERE c=1

3529

UNION

3530

SELECT c from t2 WHERE c=t1.c);

3531

id c

3532

aa 1

3533

bb 2

3534

cc 3

3535

dd 1

3536

INSERT INTO t2 VALUES (2);

3537

CREATE TABLE t3 (c int);

3538

INSERT INTO t3 VALUES (1);

3539

SELECT * FROM t1

3540

WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1

3541

UNION

3542

SELECT c from t2 WHERE c=t1.c);

3543

id c

3544

aa 1

3545

bb 2

3546

cc 3

3547

dd 1

3548

DROP TABLE t1,t2,t3;

3549

DROP TABLE IF EXISTS t1;

3550

DROP TABLE IF EXISTS t2;

3551

DROP TABLE IF EXISTS t1xt2;

3552

CREATE TABLE t1 (

3553

id_1 int(5) NOT NULL,

3554

t varchar(4) DEFAULT NULL

3555

);

3556

CREATE TABLE t2 (

3557

id_2 int(5) NOT NULL,

3558

t varchar(4) DEFAULT NULL

3559

);

3560

CREATE TABLE t1xt2 (

3561

id_1 int(5) NOT NULL,

3562

id_2 int(5) NOT NULL

3563

);

3564

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

3565

INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');

3566

INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);

3567

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3568

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3569

id_1

3570

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3571

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3572

id_1

3573

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3574

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3575

id_1

3576

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3577

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3578

id_1

3579

3580

3581

3582

3583

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3584

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));

3585

id_1

3586

3587

3588

3589

3590

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3591

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));

3592

id_1

3593

3594

3595

3596

3597

insert INTO t1xt2 VALUES (1, 12);

3598

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3599

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3600

id_1

3601

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 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3612

id_1

3613

3614

3615

3616

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3617

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3618

id_1

3619

3620

3621

3622

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3623

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3624

id_1

3625

3626

3627

3628

insert INTO t1xt2 VALUES (2, 12);

3629

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3630

(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3631

id_1

3632

3633

3634

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3635

(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3636

id_1

3637

3638

3639

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3640

(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3641

id_1

3642

3643

3644

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3645

(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));

3646

id_1

3647

3648

3649

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3650

(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));

3651

id_1

3652

3653

3654

SELECT DISTINCT t1.id_1 FROM t1 WHERE

3655

(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));

3656

id_1

3657

3658

3659

DROP TABLE t1;

3660

DROP TABLE t2;

3661

DROP TABLE t1xt2;

3662

CREATE TABLE t1 (a int);

3663

INSERT INTO t1 VALUES (3), (1), (2);

3664

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;

3665

col1 col2

3666

this is a test. 3

3667

this is a test. 1

3668

this is a test. 2

3669

SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;

3670

col1 t2

3671

this is a test. 3

3672

this is a test. 1

3673

this is a test. 2

3674

DROP table t1;

3675

CREATE TABLE t1 (a int, b int);

3676

CREATE TABLE t2 (m int, n int);

3677

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

3678

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3679

SELECT COUNT(*), a,

3680

(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3681

FROM t1 GROUP BY a;

3682

COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3683

2 2 2

3684

3 3 3

3685

1 4 1

3686

SELECT COUNT(*), a,

3687

(SELECT MIN(m) FROM t2 WHERE m = count(*))

3688

FROM t1 GROUP BY a;

3689

COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))

3690

2 2 2

3691

3 3 3

3692

1 4 1

3693

SELECT COUNT(*), a

3694

FROM t1 GROUP BY a

3695

HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;

3696

COUNT(*) a

3697

2 2

3698

3 3

3699

DROP TABLE t1,t2;

3700

CREATE TABLE t1 (a int, b int);

3701

CREATE TABLE t2 (m int, n int);

3702

INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

3703

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3704

SELECT COUNT(*) c, a,

3705

(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3706

FROM t1 GROUP BY a;

3707

c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3708

2 2 2

3709

3 3 3

3710

1 4 1,1

3711

SELECT COUNT(*) c, a,

3712

(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3713

FROM t1 GROUP BY a;

3714

c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3715

2 2 3

3716

3 3 4

3717

1 4 2,2

3718

DROP table t1,t2;

3719

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));

3720

INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),

3721

(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),

3722

(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');

3723

SELECT a, MAX(b),

3724

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test

3725

FROM t1 GROUP BY a;

3726

a MAX(b) test

3727

1 9 m

3728

2 3 h

3729

3 4 i

3730

SELECT a x, MAX(b),

3731

(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test

3732

FROM t1 GROUP BY a;

3733

x MAX(b) test

3734

1 9 m

3735

2 3 h

3736

3 4 i

3737

SELECT a, AVG(b),

3738

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test

3739

FROM t1 WHERE t1.d=0 GROUP BY a;

3740

a AVG(b) test

3741

1 4.0000 d

3742

2 2.0000 g

3743

3 2.5000 NULL

3744

SELECT tt.a,

3745

(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3746

LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3747

FROM t1 as tt;

3748

a test

3749

1 n

3750

1 n

3751

1 n

3752

1 n

3753

1 n

3754

1 n

3755

1 n

3756

2 o

3757

2 o

3758

2 o

3759

2 o

3760

3 p

3761

3 p

3762

3 p

3763

3 p

3764

3 p

3765

SELECT tt.a,

3766

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3767

LIMIT 1)

3768

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3769

FROM t1 as tt GROUP BY tt.a;

3770

a test

3771

1 n

3772

2 o

3773

3 p

3774

SELECT tt.a, MAX(

3775

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3776

LIMIT 1)

3777

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test

3778

FROM t1 as tt GROUP BY tt.a;

3779

a test

3780

1 n

3781

2 o

3782

3 p

3783

DROP TABLE t1;

3784

CREATE TABLE t1 (a INT);

3785

INSERT INTO t1 values (1),(1),(1),(1);

3786

CREATE TABLE t2 (x INT);

3787

INSERT INTO t1 values (1000),(1001),(1002);

3788

SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;

3789

ERROR HY000: Invalid use of group function

3790

SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;

3791

ERROR HY000: Invalid use of group function

3792

SELECT COUNT(1) FROM DUAL;

3793

COUNT(1)

3794

3795

SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;

3796

ERROR HY000: Invalid use of group function

3797

SELECT

3798

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )

3799

FROM t1;

3800

ERROR HY000: Invalid use of group function

3801

SELECT t1.a as XXA,

3802

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )

3803

FROM t1;

3804

ERROR HY000: Invalid use of group function

3805

DROP TABLE t1,t2;

3806

CREATE TABLE t1 (a int, b int, KEY (a));

3807

INSERT INTO t1 VALUES (1,1),(2,1);

3808

EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);

3809

id select_type table type possible_keys key key_len ref rows Extra

3810

1 PRIMARY t1 ref a a 5 const 1 Using where; Using index

3811

2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort

3812

DROP TABLE t1;

3813

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));

3814

INSERT INTO t1 VALUES

3815

(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');

3816

CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));

3817

INSERT INTO t2 VALUES (7), (5), (1), (3);

3818

SELECT id, st FROM t1

3819

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3820

id st

3821

3 FL

3822

1 GA

3823

7 FL

3824

SELECT id, st FROM t1

3825

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)

3826

GROUP BY id;

3827

id st

3828

1 GA

3829

3 FL

3830

7 FL

3831

SELECT id, st FROM t1

3832

WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3833

id st

3834

2 GA

3835

4 FL

3836

SELECT id, st FROM t1

3837

WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)

3838

GROUP BY id;

3839

id st

3840

2 GA

3841

4 FL

3842

DROP TABLE t1,t2;

3843

CREATE TABLE t1 (a int);

3844

INSERT INTO t1 VALUES (1), (2);

3845

EXPLAIN EXTENDED

3846

SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;

3847

id select_type table type possible_keys key key_len ref rows filtered Extra

3848

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00

3849

2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort

3850

Warnings:

3851

Note 1003 select "res"."count(*)" AS "count(*)" from (select count(0) AS "count(*)" from "test"."t1" group by "test"."t1"."a") "res"

3852

DROP TABLE t1;

3853

CREATE TABLE t1 (

3854

a varchar(255) default NULL,

3855

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3856

INDEX idx(a,b)

3857

);

3858

CREATE TABLE t2 (

3859

a varchar(255) default NULL

3860

);

3861

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');

3862

INSERT INTO t1 SELECT * FROM t1;

3863

INSERT INTO t1 SELECT * FROM t1;

3864

INSERT INTO t1 SELECT * FROM t1;

3865

INSERT INTO t1 SELECT * FROM t1;

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` VALUES ('asdf','2007-02-08 01:11:26');

3871

INSERT INTO `t2` VALUES ('abcdefghijk');

3872

INSERT INTO `t2` VALUES ('asdf');

3873

SET session sort_buffer_size=8192;

3874

Warnings:

3875

Warning 1292 Truncated incorrect sort_buffer_size value: '8192'

3876

SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;

3877

3878

3879

3880

DROP TABLE t1,t2;

3881

CREATE TABLE t1 (a INTEGER, b INTEGER);

3882

CREATE TABLE t2 (x INTEGER);

3883

INSERT INTO t1 VALUES (1,11), (2,22), (2,22);

3884

INSERT INTO t2 VALUES (1), (2);

3885

SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;

3886

ERROR 21000: Subquery returns more than 1 row

3887

SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;

3888

ERROR 21000: Subquery returns more than 1 row

3889

SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;

3890

(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)

3891

3.3333

3892

DROP TABLE t1,t2;

3893

CREATE TABLE t1 (a INT, b INT);

3894

INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);

3895

SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1

3896

AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)

3897

GROUP BY a1.a;

3898

a COUNT(*)

3899

1 3

3900

DROP TABLE t1;

3901

CREATE TABLE t1 (a INT);

3902

CREATE TABLE t2 (a INT);

3903

INSERT INTO t1 VALUES (1),(2);

3904

INSERT INTO t2 VALUES (1),(2);

3905

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;

3906

(SELECT SUM(t1.a) FROM t2 WHERE a=0)

3907

NULL

3908

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;

3909

ERROR 21000: Subquery returns more than 1 row

3910

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;

3911

(SELECT SUM(t1.a) FROM t2 WHERE a=1)

3912

3913

DROP TABLE t1,t2;

3914

CREATE TABLE t1 (a1 INT, a2 INT);

3915

CREATE TABLE t2 (b1 INT, b2 INT);

3916

INSERT INTO t1 VALUES (100, 200);

3917

INSERT INTO t1 VALUES (101, 201);

3918

INSERT INTO t2 VALUES (101, 201);

3919

INSERT INTO t2 VALUES (103, 203);

3920

SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;

3921

((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL

3922

3923

3924

DROP TABLE t1, t2;

3925

CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));

3926

INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);

3927

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);

3928

s1 s2

3929

SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

3930

s1 s2

3931

CREATE INDEX I1 ON t1 (s1);

3932

CREATE INDEX I2 ON t1 (s2);

3933

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);

3934

s1 s2

3935

SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

3936

s1 s2

3937

TRUNCATE t1;

3938

INSERT INTO t1 VALUES (0x41,0x41);

3939

SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);

3940

s1 s2

3941

DROP TABLE t1;

3942

CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));

3943

CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));

3944

CREATE TABLE t3 (a3 BINARY(2) default '0');

3945

INSERT INTO t1 VALUES (1),(2),(3),(4);

3946

INSERT INTO t2 VALUES (1),(2),(3);

3947

INSERT INTO t3 VALUES (1),(2),(3);

3948

SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;

3949

LEFT(t2.a2, 1)

3950

3951

3952

3953

SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;

3954

a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)

3955

1 0

3956

2 0

3957

3 0

3958

4 0

3959

DROP TABLE t1,t2,t3;

3960

CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));

3961

CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);

3962

CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);

3963

INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);

3964

INSERT INTO t2 VALUES (2), (3), (4), (5);

3965

INSERT INTO t3 VALUES (10), (20), (30);

3966

SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;

3967

LEFT(t1.a1,1)

3968

3969

3970

3971

SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);

3972

3973

DROP TABLE t1, t2, t3;

3974

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));

3975

INSERT INTO t1 VALUES ('a', 'aa');

3976

INSERT INTO t1 VALUES ('a', 'aaa');

3977

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3978

a b

3979

CREATE INDEX I1 ON t1 (a);

3980

CREATE INDEX I2 ON t1 (b);

3981

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3982

id select_type table type possible_keys key key_len ref rows Extra

3983

1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan

3984

1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition

3985

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3986

a b

3987

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));

3988

INSERT INTO t2 SELECT * FROM t1;

3989

CREATE INDEX I1 ON t2 (a);

3990

CREATE INDEX I2 ON t2 (b);

3991

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

3992

id select_type table type possible_keys key key_len ref rows Extra

3993

1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan

3994

1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition

3995

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

3996

a b

3997

EXPLAIN

3998

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

3999

id select_type table type possible_keys key key_len ref rows Extra

4000

1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan

4001

1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition

4002

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4003

a b

4004

DROP TABLE t1,t2;

4005

CREATE TABLE t1(a INT, b INT);

4006

INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);

4007

EXPLAIN

4008

SELECT a AS out_a, MIN(b) FROM t1

4009

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)

4010

GROUP BY a;

4011

ERROR 42S22: Unknown column 'out_a' in 'where clause'

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

EXPLAIN

4017

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

4018

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

4019

GROUP BY a;

4020

id select_type table type possible_keys key key_len ref rows Extra

4021

1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort

4022

2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where

4023

SELECT a AS out_a, MIN(b) FROM t1 t1_outer

4024

WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)

4025

GROUP BY a;

4026

out_a MIN(b)

4027

1 2

4028

2 4

4029

DROP TABLE t1;

4030

CREATE TABLE t1 (a INT);

4031

CREATE TABLE t2 (a INT);

4032

INSERT INTO t1 VALUES (1),(2);

4033

INSERT INTO t2 VALUES (1),(2);

4034

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

4035

4036

4037

4038

EXPLAIN EXTENDED

4039

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

4040

id select_type table type possible_keys key key_len ref rows filtered Extra

4041

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where

4042

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where

4043

Warnings:

4044

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

4045

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"))

4046

EXPLAIN EXTENDED

4047

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION

4048

(SELECT 1 FROM t2 WHERE t1.a = t2.a));

4049

id select_type table type possible_keys key key_len ref rows filtered Extra

4050

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where

4051

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where

4052

3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where

4053

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL

4054

Warnings:

4055

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

4056

Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1

4057

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")))

4058

DROP TABLE t1,t2;

4059

CREATE TABLE t4 (

4060

f7 varchar(32) collate utf8_bin NOT NULL default '',

4061

f10 varchar(32) collate utf8_bin default NULL,

4062

PRIMARY KEY (f7)

4063

);

4064

INSERT INTO t4 VALUES(1,1), (2,null);

4065

CREATE TABLE t2 (

4066

f4 varchar(32) collate utf8_bin NOT NULL default '',

4067

f2 varchar(50) collate utf8_bin default NULL,

4068

f3 varchar(10) collate utf8_bin default NULL,

4069

PRIMARY KEY (f4),

4070

UNIQUE KEY uk1 (f2)

4071

);

4072

INSERT INTO t2 VALUES(1,1,null), (2,2,null);

4073

CREATE TABLE t1 (

4074

f8 varchar(32) collate utf8_bin NOT NULL default '',

4075

f1 varchar(10) collate utf8_bin default NULL,

4076

f9 varchar(32) collate utf8_bin default NULL,

4077

PRIMARY KEY (f8)

4078

);

4079

INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);

4080

CREATE TABLE t3 (

4081

f6 varchar(32) collate utf8_bin NOT NULL default '',

4082

f5 varchar(50) collate utf8_bin default NULL,

4083

PRIMARY KEY (f6)

4084

);

4085

INSERT INTO t3 VALUES (1,null), (2,null);

4086

SELECT

4087

IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,

4088

IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,

4089

SUM(

4090

IF(

4091

(SELECT VPC.f2

4092

FROM t2 VPC, t4 a2, t2 a3

4093

WHERE

4094

VPC.f4 = a2.f10 AND a3.f2 = a4

4095

LIMIT 1) IS NULL,

4096

4097

t3.f5

4098

)

4099

) AS a6

4100

FROM

4101

t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4

4102

GROUP BY a4;

4103

a4 f3 a6

4104

1 NULL NULL

4105

2 NULL NULL

4106

DROP TABLE t1, t2, t3, t4;

4107

End of 5.0 tests.

4108

create table t_out (subcase char(3),

4109

a1 char(2), b1 char(2), c1 char(2));

4110

create table t_in (a2 char(2), b2 char(2), c2 char(2));

4111

insert into t_out values ('A.1','2a', NULL, '2a');

4112

insert into t_out values ('A.3', '2a', NULL, '2a');

4113

insert into t_out values ('A.4', '2a', NULL, 'xx');

4114

insert into t_out values ('B.1', '2a', '2a', '2a');

4115

insert into t_out values ('B.2', '2a', '2a', '2a');

4116

insert into t_out values ('B.3', '3a', 'xx', '3a');

4117

insert into t_out values ('B.4', 'xx', '3a', '3a');

4118

insert into t_in values ('1a', '1a', '1a');

4119

insert into t_in values ('2a', '2a', '2a');

4120

insert into t_in values (NULL, '2a', '2a');

4121

insert into t_in values ('3a', NULL, '3a');

4122

4123

Test general IN semantics (not top-level)

4124

4125

case A.1

4126

select subcase,

4127

(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,

4128

(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in

4129

from t_out where subcase = 'A.1';

4130

subcase pred_in pred_not_in

4131

A.1 0 1

4132

case A.2 - impossible

4133

case A.3

4134

select subcase,

4135

(a1, b1, c1) IN (select * from t_in) pred_in,

4136

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4137

from t_out where subcase = 'A.3';

4138

subcase pred_in pred_not_in

4139

A.3 NULL NULL

4140

case A.4

4141

select subcase,

4142

(a1, b1, c1) IN (select * from t_in) pred_in,

4143

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4144

from t_out where subcase = 'A.4';

4145

subcase pred_in pred_not_in

4146

A.4 0 1

4147

case B.1

4148

select subcase,

4149

(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,

4150

(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in

4151

from t_out where subcase = 'B.1';

4152

subcase pred_in pred_not_in

4153

B.1 0 1

4154

case B.2

4155

select subcase,

4156

(a1, b1, c1) IN (select * from t_in) pred_in,

4157

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4158

from t_out where subcase = 'B.2';

4159

subcase pred_in pred_not_in

4160

B.2 1 0

4161

case B.3

4162

select subcase,

4163

(a1, b1, c1) IN (select * from t_in) pred_in,

4164

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4165

from t_out where subcase = 'B.3';

4166

subcase pred_in pred_not_in

4167

B.3 NULL NULL

4168

case B.4

4169

select subcase,

4170

(a1, b1, c1) IN (select * from t_in) pred_in,

4171

(a1, b1, c1) NOT IN (select * from t_in) pred_not_in

4172

from t_out where subcase = 'B.4';

4173

subcase pred_in pred_not_in

4174

B.4 0 1

4175

4176

Test IN as top-level predicate, and

4177

as non-top level for cases A.3, B.3 (the only cases with NULL result).

4178

4179

case A.1

4180

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4181

where subcase = 'A.1' and

4182

(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');

4183

pred_in

4184

4185

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4186

where subcase = 'A.1' and

4187

(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');

4188

pred_not_in

4189

4190

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4191

where subcase = 'A.1' and

4192

NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));

4193

not_pred_in

4194

4195

case A.3

4196

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4197

where subcase = 'A.3' and

4198

(a1, b1, c1) IN (select * from t_in);

4199

pred_in

4200

4201

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4202

where subcase = 'A.3' and

4203

(a1, b1, c1) NOT IN (select * from t_in);

4204

pred_not_in

4205

4206

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4207

where subcase = 'A.3' and

4208

NOT((a1, b1, c1) IN (select * from t_in));

4209

not_pred_in

4210

4211

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

4212

where subcase = 'A.3' and

4213

((a1, b1, c1) IN (select * from t_in)) is NULL and

4214

((a1, b1, c1) NOT IN (select * from t_in)) is NULL;

4215

pred_in

4216

4217

case A.4

4218

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4219

where subcase = 'A.4' and

4220

(a1, b1, c1) IN (select * from t_in);

4221

pred_in

4222

4223

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4224

where subcase = 'A.4' and

4225

(a1, b1, c1) NOT IN (select * from t_in);

4226

pred_not_in

4227

4228

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4229

where subcase = 'A.4' and

4230

NOT((a1, b1, c1) IN (select * from t_in));

4231

not_pred_in

4232

4233

case B.1

4234

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4235

where subcase = 'B.1' and

4236

(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');

4237

pred_in

4238

4239

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4240

where subcase = 'B.1' and

4241

(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');

4242

pred_not_in

4243

4244

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4245

where subcase = 'B.1' and

4246

NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));

4247

not_pred_in

4248

4249

case B.2

4250

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4251

where subcase = 'B.2' and

4252

(a1, b1, c1) IN (select * from t_in);

4253

pred_in

4254

4255

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4256

where subcase = 'B.2' and

4257

(a1, b1, c1) NOT IN (select * from t_in);

4258

pred_not_in

4259

4260

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4261

where subcase = 'B.2' and

4262

NOT((a1, b1, c1) IN (select * from t_in));

4263

not_pred_in

4264

4265

case B.3

4266

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4267

where subcase = 'B.3' and

4268

(a1, b1, c1) IN (select * from t_in);

4269

pred_in

4270

4271

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4272

where subcase = 'B.3' and

4273

(a1, b1, c1) NOT IN (select * from t_in);

4274

pred_not_in

4275

4276

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4277

where subcase = 'B.3' and

4278

NOT((a1, b1, c1) IN (select * from t_in));

4279

not_pred_in

4280

4281

select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out

4282

where subcase = 'B.3' and

4283

((a1, b1, c1) IN (select * from t_in)) is NULL and

4284

((a1, b1, c1) NOT IN (select * from t_in)) is NULL;

4285

pred_in

4286

4287

case B.4

4288

select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out

4289

where subcase = 'B.4' and

4290

(a1, b1, c1) IN (select * from t_in);

4291

pred_in

4292

4293

select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out

4294

where subcase = 'B.4' and

4295

(a1, b1, c1) NOT IN (select * from t_in);

4296

pred_not_in

4297

4298

select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out

4299

where subcase = 'B.4' and

4300

NOT((a1, b1, c1) IN (select * from t_in));

4301

not_pred_in

4302

4303

drop table t_out;

4304

drop table t_in;

4305

CREATE TABLE t1 (s1 char(1));

4306

INSERT INTO t1 VALUES ('a');

4307

SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);

4308

4309

4310

DROP TABLE t1;

4311

CREATE TABLE t1( a INT );

4312

INSERT INTO t1 VALUES (1),(2);

4313

CREATE TABLE t2( a INT, b INT );

4314

SELECT *

4315

FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;

4316

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

4317

SELECT *

4318

FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;

4319

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

4320

SELECT *

4321

FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;

4322

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

4323

SELECT * FROM (

4324

SELECT 1 a

4325

UNION

4326

SELECT a INTO @var FROM t1 WHERE a = 2

4327

) t1a;

4328

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

4329

) t1a' at line 2

4330

SELECT * FROM (

4331

SELECT 1 a

4332

UNION

4333

SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2

4334

) t1a;

4335

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

4336

) t1a' at line 2

4337

SELECT * FROM (

4338

SELECT 1 a

4339

UNION

4340

SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4341

) t1a;

4342

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

4343

) t1a' at line 2

4344

SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;

4345

4346

4347

SELECT * FROM (

4348

SELECT a FROM t1 WHERE a = 2

4349

UNION

4350

SELECT a FROM t1 WHERE a = 2

4351

) t1a;

4352

4353

4354

SELECT * FROM (

4355

SELECT 1 a

4356

UNION

4357

SELECT a FROM t1 WHERE a = 2

4358

UNION

4359

SELECT a FROM t1 WHERE a = 2

4360

) t1a;

4361

4362

4363

4364

SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);

4365

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

4366

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;

4367

4368

4369

SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;

4370

4371

4372

SELECT * FROM ((SELECT 1 a INTO @a)) t1a;

4373

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

4374

SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;

4375

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

4376

SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) 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 DUMPFILE 'file' )) t1a' at line 1

4378

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) 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 @a)) t1a' at line 1

4380

SELECT * FROM (SELECT 1 a UNION (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 OUTFILE 'file' )) 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 OUTFILE 'file' )) t1a' at line 1

4384

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) 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 @a))) t1a' at line 1

4386

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE '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 DUMPFILE 'file' ))) t1a' at line 1

4388

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) 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 OUTFILE 'file' ))) t1a' at line 1

4390

SELECT * FROM (SELECT 1 a ORDER BY a) t1a;

4391

4392

4393

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;

4394

4395

4396

SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;

4397

4398

4399

SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;

4400

4401

4402

SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;

4403

a 1

4404

1 1

4405

2 1

4406

SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;

4407

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

4408

SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;

4409

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

4410

SELECT * FROM t1 JOIN ((t1 t1a 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) t1a 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 't1a ON 1' at line 1

4414

SELECT * FROM t1 JOIN ((t1 t1a)) t1a 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 't1a ON 1' at line 1

4416

SELECT * FROM t1 JOIN (t1 t1a) ON 1;

4417

a a

4418

1 1

4419

2 1

4420

1 2

4421

2 2

4422

SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;

4423

a a

4424

1 1

4425

2 1

4426

1 2

4427

2 2

4428

SELECT * FROM (t1 t1a);

4429

4430

4431

4432

SELECT * FROM ((t1 t1a));

4433

4434

4435

4436

SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;

4437

a t1a

4438

1 1

4439

2 1

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 a) a ON 1;

4445

a a

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) t1a1 ON 1) t1a2;

4453

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

4454

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );

4455

4456

4457

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );

4458

4459

4460

SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );

4461

4462

4463

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);

4464

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

4465

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4466

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

4467

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

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 DUMPFILE 'file' )' at line 1

4469

SELECT * FROM t1 WHERE a = ( SELECT 1 );

4470

4471

4472

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );

4473

4474

4475

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);

4476

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

4477

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );

4478

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

4479

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );

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 DUMPFILE 'file' )' at line 1

4481

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);

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 @a)' at line 1

4483

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE '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 OUTFILE 'file' )' at line 1

4485

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

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 DUMPFILE 'file' )' at line 1

4487

SELECT ( SELECT 1 INTO @v );

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 @v )' at line 1

4489

SELECT ( SELECT 1 INTO OUTFILE '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 OUTFILE 'file' )' at line 1

4491

SELECT ( SELECT 1 INTO DUMPFILE 'file' );

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 DUMPFILE 'file' )' at line 1

4493

SELECT ( SELECT 1 UNION SELECT 1 INTO @v );

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 @v )' at line 1

4495

SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE '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 OUTFILE 'file' )' at line 1

4497

SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

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 DUMPFILE 'file' )' at line 1

4499

SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;

4500

( SELECT a FROM t1 WHERE a = 1 ) a

4501

1 1

4502

1 2

4503

SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;

4504

( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a

4505

1 1

4506

1 2

4507

SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);

4508

a b

4509

SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );

4510

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

4511

( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4512

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

4513

SELECT ( 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 ( ( 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' at line 1

4517

SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4518

( SELECT 1 UNION SELECT 1 UNION SELECT 1 )

4519

4520

SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));

4521

((SELECT 1 UNION SELECT 1 UNION SELECT 1))

4522

4523

SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4524

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

4525

SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4526

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

4527

SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;

4528

4529

4530

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4531

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

4532

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4533

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

4534

SELECT * FROM t1 WHERE a = ANY ( 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 IN ( 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 = ( ( 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 = ALL ( ( 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 ) UNION SELECT 1 )' at line 1

4542

SELECT * FROM t1 WHERE a = ANY ( ( 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 ) UNION SELECT 1 )' at line 1

4544

SELECT * FROM t1 WHERE a IN ( ( 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 )' at line 1

4546

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4547

4548

4549

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4550

4551

4552

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4553

4554

4555

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4556

4557

4558

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );

4559

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

4560

SELECT EXISTS(SELECT 1+1);

4561

EXISTS(SELECT 1+1)

4562

4563

SELECT EXISTS(SELECT 1+1 INTO @test);

4564

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

4565

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );

4566

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

4567

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );

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 @v )' at line 1

4569

SELECT * FROM t1 WHERE a IN ( 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

DROP TABLE t1, t2;

4572

set optimizer_switch='';

4573

show variables like 'optimizer_switch';

4574

Variable_name Value

4575

optimizer_switch

Older »