~drizzle-trunk/drizzle/development : revision 754.1.1

11

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

12

Warnings:

13

Note 1249 Select 2 was reduced during optimization

14

Note 1003 select 2 AS "(select 2)"

14

Note 1003 select 2 AS `(select 2)`

15

SELECT (SELECT 1) UNION SELECT (SELECT 2);

16

(SELECT 1)

17

1

24

Warnings:

25

Note 1249 Select 2 was reduced during optimization

26

Note 1249 Select 4 was reduced during optimization

27

Note 1003 select 1 AS "(SELECT 1)" union select 2 AS "(SELECT 2)"

27

Note 1003 select 1 AS `(SELECT 1)` union select 2 AS `(SELECT 2)`

28

SELECT (SELECT (SELECT 0 UNION SELECT 0));

29

(SELECT (SELECT 0 UNION SELECT 0))

30

0

36

NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL

37

Warnings:

38

Note 1249 Select 2 was reduced during optimization

39

Note 1003 select (select 0 AS "0" union select 0 AS "0") AS "(SELECT (SELECT 0 UNION SELECT 0))"

39

Note 1003 select (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))`

40

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

41

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

42

SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b;

54

Warnings:

55

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

56

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

57

Note 1003 select 1 AS "1" from (select 1 AS "a") "b" having ((select '1' AS "a") = 1)

57

Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)

58

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

59

1

60

1

76

SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));

77

1

78

1

79

select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));

80

ERROR HY000: Incorrect usage of PROCEDURE and subquery

81

SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));

82

ERROR HY000: Incorrect parameters to procedure 'ANALYSE'

83

79

SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;

84

80

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

85

81

SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;

139

135

SELECT 1 as a,(SELECT a+a) b,(SELECT b);

140

136

a b (SELECT b)

141

137

1 2 2

142

create table t1 (a int);

143

create table t2 (a int, b int);

144

create table t3 (a int);

145

create table t4 (a int not null, b int not null);

138

create table t1 (a int) ENGINE=MyISAM;

139

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

140

create table t3 (a int) ENGINE=MyISAM;

141

create table t4 (a int not null, b int not null) ENGINE=MyISAM;

146

142

insert into t1 values (2);

147

143

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

148

144

insert into t4 values (4,8),(3,8),(5,9);

191

187

4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

192

188

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

193

189

Warnings:

194

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

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

195

191

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

196

192

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

197

193

3 1

207

203

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

208

204

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

209

205

Warnings:

210

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

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`

211

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

212

208

a

213

209

2

228

224

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

229

225

Warnings:

230

226

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

231

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

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`

232

228

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

233

229

a

234

230

7

274

270

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

275

271

2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00

276

272

Warnings:

277

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

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

278

274

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

279

275

a

280

276

7

294

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

295

291

b ma

296

292

7 12

297

create table t5 (a int);

293

create table t5 (a int) ENGINE=MyISAM;

298

294

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

299

295

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

300

296

NULL 1

318

314

Warnings:

319

315

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

320

316

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

321

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

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`

322

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;

323

319

ERROR 21000: Subquery returns more than 1 row

324

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

325

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

320

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

321

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

326

322

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

327

323

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

328

324

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

336

332

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

337

333

Warnings:

338

334

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

339

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

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

340

336

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

341

337

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

342

338

drop table t1,t2,t3;

343

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

339

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

344

340

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

345

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

341

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

346

342

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

347

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

343

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

348

344

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

349

345

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

350

346

a b

356

352

a b

357

353

W a

358

354

CREATE TABLE `t8` (

359

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

360

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

355

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

356

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

361

357

PRIMARY KEY (`pseudo`),

362

358

UNIQUE KEY `email` (`email`)

363

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

359

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

364

360

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

365

361

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

366

362

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

367

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

368

364

id select_type table type possible_keys key key_len ref rows filtered Extra

369

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

370

4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

371

2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00

372

3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

365

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

366

4 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index

367

2 SUBQUERY t8 const PRIMARY PRIMARY 142 const 1 100.00

368

3 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index

373

369

Warnings:

374

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

370

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

375

371

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

376

372

t8 WHERE pseudo='joce');

377

373

ERROR 21000: Operand should contain 1 column(s)

385

381

ERROR 21000: Subquery returns more than 1 row

386

382

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

387

383

CREATE TABLE `t1` (

388

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

384

`topic` bigint NOT NULL default '0',

389

385

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

390

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

386

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

391

387

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

392

388

KEY `topic` (`topic`)

393

389

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

395

391

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

396

392

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

397

393

id select_type table type possible_keys key key_len ref rows filtered Extra

398

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

394

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

399

395

Warnings:

400

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

396

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

401

397

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

402

398

id select_type table type possible_keys key key_len ref rows filtered Extra

403

399

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

404

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

400

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

405

401

Warnings:

406

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

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

407

403

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

408

404

date

409

405

2002-08-03

419

415

ERROR 21000: Subquery returns more than 1 row

420

416

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

421

417

id select_type table type possible_keys key key_len ref rows filtered Extra

422

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

418

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

423

419

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

424

420

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

425

421

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

426

422

Warnings:

427

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

423

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

428

424

drop table t1;

429

425

CREATE TABLE `t1` (

430

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

431

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

426

`numeropost` bigint NOT NULL auto_increment,

427

`maxnumrep` int NOT NULL default '0',

432

428

PRIMARY KEY (`numeropost`),

433

429

UNIQUE KEY `maxnumrep` (`maxnumrep`)

434

430

) ENGINE=MyISAM ROW_FORMAT=FIXED;

435

431

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

436

432

CREATE TABLE `t2` (

437

433

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

438

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

434

`topic` bigint NOT NULL default '0',

439

435

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

440

436

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

441

437

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

499

495

joce 43506 2002-10-22 joce 0

500

496

drop table t1,t2;

501

497

CREATE TABLE `t1` (

502

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

503

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

498

`numeropost` bigint NOT NULL auto_increment,

499

`maxnumrep` int NOT NULL default '0',

504

500

PRIMARY KEY (`numeropost`),

505

501

UNIQUE KEY `maxnumrep` (`maxnumrep`)

506

502

) ENGINE=MyISAM ROW_FORMAT=FIXED;

510

506

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

511

507

ERROR 21000: Subquery returns more than 1 row

512

508

drop table t1;

513

create table t1 (a int);

509

create table t1 (a int) ENGINE=MyISAM;

514

510

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

515

511

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

516

512

a

518

514

2

519

515

3

520

516

drop table t1;

521

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

517

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

522

518

INSERT INTO t1 VALUES ();

523

519

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

524

520

ERROR 21000: Subquery returns more than 1 row

525

521

drop table t1;

526

522

CREATE TABLE `t1` (

527

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

528

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

523

`numeropost` bigint NOT NULL default '0',

524

`numreponse` int NOT NULL auto_increment,

529

525

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

530

526

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

531

527

UNIQUE KEY `numreponse` (`numreponse`),

544

540

id select_type table type possible_keys key key_len ref rows filtered Extra

545

541

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

546

542

Warnings:

547

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

543

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

548

544

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

549

545

id select_type table type possible_keys key key_len ref rows filtered Extra

550

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

546

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

551

547

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

552

548

Warnings:

553

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

549

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

554

550

drop table t1;

555

CREATE TABLE t1 (a int(1));

551

CREATE TABLE t1 (a int) ENGINE=MyISAM;

556

552

INSERT INTO t1 VALUES (1);

557

553

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

558

554

1

559

555

1

560

556

drop table t1;

561

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

562

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

557

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

558

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

563

559

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

564

560

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

565

561

select * from t1;

578

574

1 21

579

575

2 22

580

576

drop table t1, t2;

581

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

582

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

577

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

578

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

583

579

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

584

580

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

585

581

select * from t1;

600

596

0 10

601

597

1 11

602

598

drop table t1, t2;

603

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

604

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

605

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

599

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

600

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

601

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

606

602

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

607

603

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

608

604

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

613

609

2 12

614

610

select * from t12;

615

611

a b

612

2 12

613

22 11

616

614

33 10

617

22 11

618

2 12

619

615

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

620

616

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

621

617

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

627

623

1 11

628

624

select * from t12;

629

625

a b

626

22 11

630

627

33 10

631

22 11

632

628

drop table t11, t12, t2;

633

CREATE TABLE t1 (x int);

634

create table t2 (a int);

635

create table t3 (b int);

629

CREATE TABLE t1 (x int) ENGINE=MyISAM;

630

create table t2 (a int) ENGINE=MyISAM;

631

create table t3 (b int) ENGINE=MyISAM;

636

632

insert into t2 values (1);

637

633

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

638

634

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

678

674

11

679

675

2

680

676

drop table t1, t2, t3;

681

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

682

create table t2 (a int);

683

create table t3 (a int);

677

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

678

create table t2 (a int) ENGINE=MyISAM;

679

create table t3 (a int) ENGINE=MyISAM;

684

680

insert into t2 values (1);

685

681

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

686

682

select * from t1;

711

707

select * from t1;

712

708

x y

713

709

1 3

710

2 1

714

711

4 2

715

2 1

716

712

drop table t1, t2, t3;

717

713

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

718

714

ERROR HY000: No tables used

719

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

715

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

720

716

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

721

717

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

722

718

id

726

722

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

727

723

Warnings:

728

724

Note 1249 Select 2 was reduced during optimization

729

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

725

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

730

726

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

731

727

id

732

728

1

739

735

Warnings:

740

736

Note 1249 Select 3 was reduced during optimization

741

737

Note 1249 Select 2 was reduced during optimization

742

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

738

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

743

739

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

744

740

id select_type table type possible_keys key key_len ref rows filtered Extra

745

741

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

747

743

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

748

744

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

749

745

Warnings:

750

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

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

751

747

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

752

748

id

753

749

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

761

757

id

762

758

1

763

759

2

764

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

760

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

765

761

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

766

762

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

767

763

ERROR 21000: Subquery returns more than 1 row

768

764

drop table t2, t1;

769

create table t1 (a int);

765

create table t1 (a int) ENGINE=MyISAM;

770

766

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

771

767

select 1 IN (SELECT * from t1);

772

768

1 IN (SELECT * from t1)

800

796

10 > ANY (SELECT * from t1)

801

797

1

802

798

drop table t1;

803

create table t1 (a varchar(20));

799

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

804

800

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

805

801

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

806

802

'A' IN (SELECT * from t1)

834

830

'XYZS' > ANY (SELECT * from t1)

835

831

1

836

832

drop table t1;

837

create table t1 (a float);

833

create table t1 (a float) ENGINE=MyISAM;

838

834

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

839

835

select 1.5 IN (SELECT * from t1);

840

836

1.5 IN (SELECT * from t1)

873

869

Warnings:

874

870

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

875

871

Note 1249 Select 2 was reduced during optimization

876

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

872

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

877

873

select (select a+1) from t1;

878

874

(select a+1)

879

875

2.5

880

NULL

881

876

4.5

877

NULL

882

878

drop table t1;

883

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

884

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

879

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

880

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

885

881

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

886

882

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

887

883

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

895

891

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

896

892

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

897

893

Warnings:

898

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

899

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

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 default '0') ENGINE=MyISAM;

900

896

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

901

897

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

902

898

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

910

906

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

911

907

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

912

908

Warnings:

913

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

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`

914

910

drop table t1,t2,t3;

915

create table t1 (a float);

916

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

917

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

918

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

919

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

920

drop table t1;

921

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

922

create table t2 (a int);

923

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

924

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

925

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

926

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

927

1 1 a

928

2 0 b

929

NULL 0 NULL

930

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

931

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

932

1 0 a

933

2 1 b

934

NULL NULL NULL

935

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

936

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

937

1 0 a

938

2 0 b

939

NULL 0 NULL

940

drop table t1,t2;

941

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

942

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

943

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

944

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

945

1

946

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

947

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

948

0

949

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

950

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

951

1

952

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

953

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

954

1

955

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

956

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

957

0

958

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

959

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

960

1

961

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

962

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

963

1

964

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

965

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

966

0

967

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

968

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

969

1

970

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

971

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

972

drop table t1;

973

CREATE TABLE t1 (a int(1));

911

CREATE TABLE t1 (a int) ENGINE=MyISAM;

974

912

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

975

913

id select_type table type possible_keys key key_len ref rows filtered Extra

976

914

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

977

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

978

Warnings:

979

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

980

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

981

id select_type table type possible_keys key key_len ref rows filtered Extra

982

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

983

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

984

Warnings:

985

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

915

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

916

Warnings:

917

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

986

918

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

987

919

id select_type table type possible_keys key key_len ref rows filtered Extra

988

920

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

989

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

921

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

990

922

Warnings:

991

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

923

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

992

924

drop table t1;

993

925

CREATE TABLE `t1` (

994

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

995

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

926

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

927

`topic` bigint NOT NULL default '0',

996

928

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

997

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

929

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

998

930

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

999

931

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

1000

932

KEY `topic` (`topic`)

1001

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

933

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

1002

934

CREATE TABLE `t2` (

1003

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

1004

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

935

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

936

`topic` bigint NOT NULL default '0',

1005

937

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

1006

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

938

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

1007

939

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

1008

940

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

1009

941

KEY `topic` (`topic`)

1010

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

942

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

1011

943

CREATE TABLE `t3` (

1012

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

1013

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

944

`numeropost` bigint NOT NULL auto_increment,

945

`maxnumrep` int NOT NULL default '0',

1014

946

PRIMARY KEY (`numeropost`),

1015

947

UNIQUE KEY `maxnumrep` (`maxnumrep`)

1016

) ENGINE=MyISAM CHARSET=latin1;

948

) ENGINE=MyISAM;

1017

949

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

1018

950

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

1019

951

INSERT INTO t3 VALUES (1,1);

1034

966

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

1035

967

a (SELECT a)

1036

968

1 1

1037

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

1038

SHOW CREATE TABLE t1;

1039

Table Create Table

1040

t1 CREATE TABLE "t1" (

1041

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

1042

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

1043

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1044

drop table t1;

1045

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

1046

SHOW CREATE TABLE t1;

1047

Table Create Table

1048

t1 CREATE TABLE "t1" (

1049

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

1050

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

1051

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1052

drop table t1;

1053

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

1054

SHOW CREATE TABLE t1;

1055

Table Create Table

1056

t1 CREATE TABLE "t1" (

1057

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

1058

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

1059

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1060

drop table t1;

1061

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

969

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

970

SHOW CREATE TABLE t1;

971

Table Create Table

972

t1 CREATE TABLE `t1` (

973

`a` int NOT NULL,

974

`(SELECT 1)` int NOT NULL

975

) ENGINE=MyISAM

976

drop table t1;

977

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

978

SHOW CREATE TABLE t1;

979

Table Create Table

980

t1 CREATE TABLE `t1` (

981

`a` int NOT NULL,

982

`(SELECT a)` int NOT NULL

983

) ENGINE=MyISAM

984

drop table t1;

985

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

986

SHOW CREATE TABLE t1;

987

Table Create Table

988

t1 CREATE TABLE `t1` (

989

`a` int NOT NULL,

990

`(SELECT a+0)` int NOT NULL

991

) ENGINE=MyISAM

992

drop table t1;

993

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

1062

994

select * from t1;

1063

995

a

1064

996

2

1065

997

SHOW CREATE TABLE t1;

1066

998

Table Create Table

1067

t1 CREATE TABLE "t1" (

1068

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

1069

) ENGINE=MyISAM DEFAULT CHARSET=latin1

999

t1 CREATE TABLE `t1` (

1000

`a` bigint NOT NULL

1001

) ENGINE=MyISAM

1070

1002

drop table t1;

1071

create table t1 (a int);

1003

create table t1 (a int) ENGINE=MyISAM;

1072

1004

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

1073

1005

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

1074

1006

from t1;

1075

1007

id select_type table type possible_keys key key_len ref rows filtered Extra

1076

1008

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

1077

2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1078

3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1009

2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1010

3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1079

1011

Warnings:

1080

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

1012

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

1081

1013

drop table t1;

1082

1014

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

1083

1015

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

1084

1016

CREATE TABLE t1 (

1085

ID int(11) NOT NULL auto_increment,

1017

ID int NOT NULL auto_increment,

1086

1018

name char(35) NOT NULL default '',

1087

1019

t2 char(3) NOT NULL default '',

1088

1020

District char(20) NOT NULL default '',

1089

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

1021

Population int NOT NULL default '0',

1090

1022

PRIMARY KEY (ID)

1091

1023

) ENGINE=MyISAM;

1092

1024

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

1098

1030

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

1099

1031

Region char(26) NOT NULL default '',

1100

1032

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

1101

IndepYear smallint(6) default NULL,

1102

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

1033

IndepYear int default NULL,

1034

Population int NOT NULL default '0',

1103

1035

LifeExpectancy float(3,1) default NULL,

1104

1036

GNP float(10,2) default NULL,

1105

1037

GNPOld float(10,2) default NULL,

1106

1038

LocalName char(45) NOT NULL default '',

1107

1039

GovernmentForm char(45) NOT NULL default '',

1108

1040

HeadOfState char(60) default NULL,

1109

Capital int(11) default NULL,

1041

Capital int default NULL,

1110

1042

Code2 char(2) NOT NULL default '',

1111

1043

PRIMARY KEY (Code)

1112

1044

) ENGINE=MyISAM;

1117

1049

Oceania Sydney 3276207

1118

1050

drop table t1, t2;

1119

1051

CREATE TABLE `t1` (

1120

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

1121

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

1052

`id` bigint NOT NULL auto_increment,

1053

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

1122

1054

PRIMARY KEY (`id`),

1123

1055

UNIQUE KEY `pseudo` (`pseudo`)

1124

1056

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

1131

1063

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

1132

1064

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

1133

1065

Warnings:

1134

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

1066

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

1135

1067

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

1136

1068

SELECT 0 IN (SELECT 1 FROM t1 a);

1137

1069

0 IN (SELECT 1 FROM t1 a)

1141

1073

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

1142

1074

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

1143

1075

Warnings:

1144

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

1076

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

1145

1077

drop table t1;

1146

1078

CREATE TABLE `t1` (

1147

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

1079

`i` int NOT NULL default '0',

1148

1080

PRIMARY KEY (`i`)

1149

) ENGINE=MyISAM CHARSET=latin1;

1081

) ENGINE=MyISAM;

1150

1082

INSERT INTO t1 VALUES (1);

1151

1083

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

1152

1084

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

1157

1089

3

1158

1090

drop table t1;

1159

1091

CREATE TABLE t1 (

1160

id int(11) default NULL

1161

) ENGINE=MyISAM CHARSET=latin1;

1092

id int default NULL

1093

) ENGINE=MyISAM;

1162

1094

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

1163

1095

CREATE TABLE t2 (

1164

id int(11) default NULL,

1096

id int default NULL,

1165

1097

name varchar(15) default NULL

1166

) ENGINE=MyISAM CHARSET=latin1;

1098

) ENGINE=MyISAM;

1167

1099

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

1168

1100

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

1169

1101

select * from t2;

1173

1105

2 lenka

1174

1106

1 lenka

1175

1107

drop table t1,t2;

1176

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

1108

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

1177

1109

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

1178

1110

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

1179

1111

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

1180

1112

NULL

1181

1113

drop table t1;

1182

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

1114

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

1183

1115

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

1184

1116

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

1185

1117

id select_type table type possible_keys key key_len ref rows filtered Extra

1186

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

1118

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

1187

1119

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

1188

1120

Warnings:

1189

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

1121

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

1190

1122

drop table t1;

1191

1123

CREATE TABLE t1 (

1192

ID int(10) unsigned NOT NULL auto_increment,

1193

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

1194

REF_ID int(10) unsigned default NULL,

1195

REF_SUB int(3) unsigned default '0',

1124

ID int NOT NULL auto_increment,

1125

SUB_ID int NOT NULL default '0',

1126

REF_ID int default NULL,

1127

REF_SUB int default '0',

1196

1128

PRIMARY KEY (ID,SUB_ID),

1197

1129

UNIQUE KEY t1_PK (ID,SUB_ID),

1198

1130

KEY t1_FK (REF_ID,REF_SUB),

1199

1131

KEY t1_REFID (REF_ID)

1200

) ENGINE=MyISAM CHARSET=cp1251;

1132

) ENGINE=MyISAM;

1201

1133

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

1202

1134

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

1203

1135

REF_ID

1204

1136

DROP TABLE t1;

1205

create table t1 (a int, b int);

1206

create table t2 (a int, b int);

1137

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

1138

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

1207

1139

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

1208

1140

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

1209

1141

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

1216

1148

3 1

1217

1149

drop table t1, t2;

1218

1150

CREATE TABLE `t1` (

1219

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

1151

`id` bigint NOT NULL auto_increment,

1220

1152

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

1221

1153

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

1222

1154

PRIMARY KEY (`id`),

1223

1155

UNIQUE KEY `email` (`email`),

1224

1156

UNIQUE KEY `pseudo` (`pseudo`)

1225

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

1157

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

1226

1158

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

1227

1159

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

1228

1160

a b

1232

1164

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

1233

1165

a

1234

1166

1

1235

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

1236

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

1237

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

1167

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

1168

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

1169

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

1238

1170

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

1239

1171

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

1240

1172

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

1248

1180

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

1249

1181

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

1250

1182

Warnings:

1251

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

1183

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

1252

1184

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

1253

1185

a

1254

1186

2

1258

1190

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

1259

1191

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

1260

1192

Warnings:

1261

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

1193

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

1262

1194

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

1263

1195

a

1264

1196

2

1269

1201

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

1270

1202

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

1271

1203

Warnings:

1272

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

1204

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

1273

1205

drop table t1, t2, t3;

1274

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

1275

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

1276

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

1206

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

1207

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

1208

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

1277

1209

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

1278

1210

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

1279

1211

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

1287

1219

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

1288

1220

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

1289

1221

Warnings:

1290

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

1222

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

1291

1223

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

1292

1224

a

1293

1225

2

1297

1229

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

1298

1230

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

1299

1231

Warnings:

1300

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

1232

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

1301

1233

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

1302

1234

a

1303

1235

2

1308

1240

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

1309

1241

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

1310

1242

Warnings:

1311

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

1243

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

1312

1244

insert into t1 values (3,31);

1313

1245

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

1314

1246

a

1324

1256

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

1325

1257

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

1326

1258

Warnings:

1327

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

1259

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

1328

1260

drop table t1, t2, t3;

1329

create table t1 (a int, b int);

1330

create table t2 (a int, b int);

1331

create table t3 (a int, b int);

1261

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

1262

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

1263

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

1332

1264

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

1333

1265

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

1334

1266

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

1338

1270

2 2

1339

1271

1 2

1340

1272

drop table t1,t2,t3;

1341

create table t1 (s1 int);

1342

create table t2 (s1 int);

1273

create table t1 (s1 int) ENGINE=MyISAM;

1274

create table t2 (s1 int) ENGINE=MyISAM;

1343

1275

insert into t1 values (1);

1344

1276

insert into t2 values (1);

1345

1277

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

1346

1278

s1

1347

1279

1

1348

1280

drop table t1,t2;

1349

create table t1 (s1 int);

1350

create table t2 (s1 int);

1281

create table t1 (s1 int) ENGINE=MyISAM;

1282

create table t2 (s1 int) ENGINE=MyISAM;

1351

1283

insert into t1 values (1);

1352

1284

insert into t2 values (1);

1353

1285

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

1354

1286

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

1355

1287

DROP TABLE t1, t2;

1356

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

1357

s2 CHAR(5) COLLATE latin1_swedish_ci);

1358

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

1359

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

1360

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

1361

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

1362

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

1363

drop table t1;

1364

create table t1(toid int,rd int);

1365

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

1288

create table t1(toid int,rd int) ENGINE=MyISAM;

1289

create table t2(userid int,pmnew int,pmtotal int) ENGINE=MyISAM;

1366

1290

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

1367

1291

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

1368

1292

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

1370

1294

1 0 0 9 3

1371

1295

2 0 0 4 2

1372

1296

drop table t1, t2;

1373

create table t1 (s1 char(5));

1297

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

1374

1298

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

1375

1299

ERROR 21000: Operand should contain 1 column(s)

1376

1300

insert into t1 values ('tttt');

1381

1305

id select_type table type possible_keys key key_len ref rows filtered Extra

1382

1306

1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00

1383

1307

Warnings:

1384

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

1308

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

1385

1309

(select * from t1);

1386

1310

s1

1387

1311

tttt

1388

1312

drop table t1;

1389

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

1390

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

1313

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

1314

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

1391

1315

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

1392

1316

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

1393

1317

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

1412

1336

a3 1

1413

1337

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

1414

1338

id select_type table type possible_keys key key_len ref rows filtered Extra

1415

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

1416

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

1339

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

1340

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

1417

1341

Warnings:

1418

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

1342

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

1419

1343

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

1420

1344

id select_type table type possible_keys key key_len ref rows filtered Extra

1421

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

1422

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

1345

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

1346

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

1423

1347

Warnings:

1424

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

1348

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

1425

1349

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

1426

1350

id select_type table type possible_keys key key_len ref rows filtered Extra

1427

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

1428

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

1351

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

1352

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

1429

1353

Warnings:

1430

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 <> ALL (SELECT s1 FROM t2)" from "test"."t1"

1354

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 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`

1431

1355

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

1432

1356

id select_type table type possible_keys key key_len ref rows filtered Extra

1433

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

1434

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

1357

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

1358

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

1435

1359

Warnings:

1436

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

1360

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

1437

1361

drop table t1,t2;

1438

create table t2 (a int, b int);

1439

create table t3 (a int);

1362

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

1363

create table t3 (a int) ENGINE=MyISAM;

1440

1364

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

1441

1365

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

1442

1366

a

1448

1372

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

1449

1373

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

1450

1374

Warnings:

1451

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

1375

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

1452

1376

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

1453

1377

a

1454

1378

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

1456

1380

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

1457

1381

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

1458

1382

Warnings:

1459

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

1383

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

1460

1384

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

1461

1385

a

1462

1386

6

1467

1391

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

1468

1392

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

1469

1393

Warnings:

1470

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

1394

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

1471

1395

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

1472

1396

a

1473

1397

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

1475

1399

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

1476

1400

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

1477

1401

Warnings:

1478

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

1402

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

1479

1403

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

1480

1404

a

1481

1405

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

1483

1407

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

1484

1408

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

1485

1409

Warnings:

1486

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

1410

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

1487

1411

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

1488

1412

a

1489

1413

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

1491

1415

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

1492

1416

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

1493

1417

Warnings:

1494

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

1418

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

1495

1419

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

1496

1420

a

1497

1421

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

1499

1423

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

1500

1424

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

1501

1425

Warnings:

1502

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

1426

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

1503

1427

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

1504

1428

a

1505

1429

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

1507

1431

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

1508

1432

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

1509

1433

Warnings:

1510

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

1434

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

1511

1435

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

1512

1436

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

1513

1437

a

1518

1442

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

1519

1443

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

1520

1444

Warnings:

1521

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

1445

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

1522

1446

drop table t2, t3;

1523

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

1447

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

1524

1448

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

1525

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

1449

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

1526

1450

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

1527

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

1451

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

1528

1452

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

1529

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

1453

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

1530

1454

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

1531

1455

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

1532

1456

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

1538

1462

-1 Valid

1539

1463

-1 Valid 2

1540

1464

drop table t1,t2,t3,t4;

1541

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

1465

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

1542

1466

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

1543

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

1467

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

1544

1468

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

1545

1469

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

1546

1470

ERROR 21000: Operand should contain 3 column(s)

1547

1471

DROP TABLE t1,t2;

1548

create table t1 (s1 char);

1472

create table t1 (s1 char) ENGINE=MyISAM;

1549

1473

insert into t1 values ('e');

1550

1474

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

1551

1475

s1

1560

1484

3 UNION t1 system NULL NULL NULL NULL 1 100.00

1561

1485

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

1562

1486

Warnings:

1563

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

1487

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

1564

1488

drop table t1;

1565

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

1489

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

1566

1490

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

1567

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

1491

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

1568

1492

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

1569

1493

select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c;

1570

1494

phone code

1573

1497

89356874041 NULL

1574

1498

95895001874 NULL

1575

1499

drop table t1, t2;

1576

create table t1 (s1 int);

1577

create table t2 (s1 int);

1500

create table t1 (s1 int) ENGINE=MyISAM;

1501

create table t2 (s1 int) ENGINE=MyISAM;

1578

1502

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

1579

1503

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

1580

1504

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

1582

1506

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

1583

1507

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

1584

1508

drop table t1, t2;

1585

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

1586

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

1509

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

1510

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

1587

1511

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

1588

1512

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

1589

1513

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

1590

1514

COLC

1591

1515

DROP TABLE t1, t2;

1592

CREATE TABLE t1 (a int(1));

1516

CREATE TABLE t1 (a int) ENGINE=MyISAM;

1593

1517

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

1594

1518

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

1595

1519

(SELECT a)

1600

1524

5

1601

1525

DROP TABLE t1;

1602

1526

CREATE TABLE `t1` (

1603

`id` int(11) NOT NULL auto_increment,

1604

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

1527

`id` int NOT NULL auto_increment,

1528

`id_cns` int NOT NULL default '0',

1605

1529

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

1606

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

1607

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

1608

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

1609

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

1530

`anno_dep` int NOT NULL default '0',

1531

`particolare` bigint NOT NULL default '0',

1532

`generale` bigint NOT NULL default '0',

1533

`bis` int NOT NULL default '0',

1610

1534

PRIMARY KEY (`id`),

1611

1535

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

1612

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

1613

);

1536

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

1537

ENGINE=MyISAM;

1614

1538

INSERT INTO `t1` VALUES (1,16,'UNO',1987,2048,9681,0),(2,50,'UNO',1987,1536,13987,0),(3,16,'UNO',1987,2432,14594,0),(4,16,'UNO',1987,1792,13422,0),(5,16,'UNO',1987,1025,10240,0),(6,16,'UNO',1987,1026,7089,0);

1615

1539

CREATE TABLE `t2` (

1616

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

1617

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

1618

PRIMARY KEY (`id`)

1619

);

1540

`id` int NOT NULL auto_increment,

1541

`max_anno_dep` int NOT NULL default '0',

1542

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

1620

1543

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

1621

1544

SELECT cns.id, cns.max_anno_dep, cns.max_anno_dep = (SELECT s.anno_dep FROM t1 AS s WHERE s.id_cns = cns.id ORDER BY s.anno_dep DESC LIMIT 1) AS PIPPO FROM t2 AS cns;

1622

1545

id max_anno_dep PIPPO

1624

1547

50 1990 0

1625

1548

51 1990 NULL

1626

1549

DROP TABLE t1, t2;

1627

create table t1 (a int);

1550

create table t1 (a int) ENGINE=MyISAM;

1628

1551

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

1629

1552

SET SQL_SELECT_LIMIT=1;

1630

1553

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

1635

1558

1

1636

1559

SET SQL_SELECT_LIMIT=default;

1637

1560

drop table t1;

1638

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

1561

CREATE TABLE t1 (a int, b int, INDEX (a)) ENGINE=MyISAM;

1639

1562

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

1640

1563

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

1641

1564

a b

1643

1566

1 2

1644

1567

1 3

1645

1568

DROP TABLE t1;

1646

create table t1(val varchar(10));

1569

create table t1(val varchar(10)) ENGINE=MyISAM;

1647

1570

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

1648

1571

select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%');

1649

1572

count(*)

1650

1573

0

1651

1574

drop table t1;

1652

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

1575

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

1653

1576

insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');

1654

1577

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

1655

1578

id text

1670

1593

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

1671

1594

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

1672

1595

Warnings:

1673

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

1596

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

1674

1597

explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);

1675

1598

id select_type table type possible_keys key key_len ref rows filtered Extra

1676

1599

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

1677

1600

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

1678

1601

Warnings:

1679

1602

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

1680

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

1603

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

1681

1604

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

1682

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

1605

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

1683

1606

insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');

1684

1607

select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);

1685

1608

id text id text id text

1701

1624

id select_type table type possible_keys key key_len ref rows filtered Extra

1702

1625

1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00

1703

1626

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

1704

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

1627

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

1705

1628

Warnings:

1706

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

1629

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

1707

1630

drop table t1,t2;

1708

create table t1 (a int);

1631

create table t1 (a int) ENGINE=MyISAM;

1709

1632

insert into t1 values (1);

1710

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

1633

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

1711

1634

id select_type table type possible_keys key key_len ref rows Extra

1712

1635

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

1713

2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 1

1636

2 SUBQUERY t1 system NULL NULL NULL NULL 1

1714

1637

drop table t1;

1715

create table t1(id int);

1716

create table t2(id int);

1717

create table t3(flag int);

1638

create table t1(id int) ENGINE=MyISAM;

1639

create table t2(id int) ENGINE=MyISAM;

1640

create table t3(flag int) ENGINE=MyISAM;

1718

1641

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

1719

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

1642

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

1720

1643

drop table t1,t2,t3;

1721

CREATE TABLE t1 (id INT);

1722

CREATE TABLE t2 (id INT);

1644

CREATE TABLE t1 (id INT) ENGINE=MyISAM;

1645

CREATE TABLE t2 (id INT) ENGINE=MyISAM;

1723

1646

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

1724

1647

INSERT INTO t2 VALUES (1);

1725

1648

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

1739

1662

1 1

1740

1663

2 0

1741

1664

DROP TABLE t1,t2;

1742

CREATE TABLE t1 ( a int, b int );

1665

CREATE TABLE t1 ( a int, b int ) ENGINE=MyISAM;

1743

1666

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

1744

1667

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

1745

1668

a

2111

2034

1-

2112

2035

0-

2113

2036

DROP TABLE t1;

2114

CREATE TABLE t1 ( a double, b double );

2037

CREATE TABLE t1 ( a double, b double ) ENGINE=MyISAM;

2115

2038

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

2116

2039

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

2117

2040

a

2156

2079

1

2157

2080

3

2158

2081

DROP TABLE t1;

2159

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

2082

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

2160

2083

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

2161

2084

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

2162

2085

a

2201

2124

1

2202

2125

3

2203

2126

DROP TABLE t1;

2204

create table t1 (a int, b int);

2127

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

2205

2128

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

2206

2129

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

2207

2130

a b

2213

2136

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

2214

2137

Warnings:

2215

2138

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

2216

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

2139

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

2217

2140

drop table t1;

2218

CREATE TABLE t1 (t1_a int);

2141

CREATE TABLE t1 (t1_a int) ENGINE=MyISAM;

2219

2142

INSERT INTO t1 VALUES (1);

2220

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

2143

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

2221

2144

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

2222

2145

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

2223

2146

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

2224

2147

t1_a t2_a t2_b

2225

2148

1 1 2

2226

2149

DROP TABLE t1, t2;

2227

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

2150

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

2228

2151

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

2229

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

2152

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

2230

2153

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

2231

2154

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

2232

2155

id name id pet

2234

2157

2 Rebecca 2 Spot

2235

2158

3 NULL 3 Felix

2236

2159

drop table t1,t2;

2237

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

2160

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

2238

2161

insert into t1 values (1);

2239

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

2162

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

2240

2163

insert into t2 values (1,2);

2241

2164

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

2242

2165

a C

2243

2166

1 1

2244

2167

drop table t1,t2;

2245

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

2246

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

2247

create table t2 (a int);

2248

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

2249

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

2250

a b

2251

1 ball

2252

2 ball games

2253

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

2254

a

2255

1

2256

2

2257

drop table t1,t2;

2258

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

2168

CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) COLLATE utf8_bin,`KUERZEL` VARCHAR(10) COLLATE utf8_bin,`IZAANALYSEART_ID` VARCHAR(11) COLLATE utf8_bin,`IZAPMKZ_ID` VARCHAR(11) COLLATE utf8_bin) ENGINE=MyISAM;

2259

2169

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

2260

2170

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

2261

2171

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

2265

2175

IZAVORGANG_ID

2266

2176

D0000000001

2267

2177

drop table t1;

2268

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

2269

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

2178

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

2179

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

2270

2180

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

2271

2181

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

2272

2182

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

2286

2196

1 1

2287

2197

2 1

2288

2198

drop table t1,t2;

2289

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

2199

CREATE TABLE t1 (howmanyvalues bigint, avalue int) ENGINE=MyISAM;

2290

2200

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

2291

2201

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

2292

2202

howmanyvalues count(*)

2320

2230

3 1

2321

2231

4 1

2322

2232

drop table t1;

2323

create table t1 (x int);

2233

create table t1 (x int) ENGINE=MyISAM;

2324

2234

select (select b.x from t1 as b where b.x=a.x) from t1 as a where a.x=2 group by a.x;

2325

2235

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

2326

2236

drop table t1;

2327

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

2237

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

2328

2238

INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400);

2329

CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL default '0', `pid` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `level` tinyint(4) unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;

2239

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

2330

2240

INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a');

2331

2241

SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;

2332

2242

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

2336

2246

NULL

2337

2247

drop tables t1,t2;

2338

2248

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

2339

create table t1 (a int, b int);

2340

create table t2 (a int, b int);

2249

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

2250

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

2341

2251

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

2342

2252

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

2343

2253

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

2348

2258

2 4 NULL

2349

2259

2 5 NULL

2350

2260

drop table t1, t2;

2351

create table t1 (id int);

2352

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

2353

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

2354

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

2355

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

2356

count(distinct id)

2357

1

2358

drop table t2,t1;

2359

create table t1 (s1 int,s2 int);

2261

create table t1 (s1 int,s2 int) ENGINE=MyISAM;

2360

2262

insert into t1 values (20,15);

2361

2263

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

2362

2264

s1 s2

2363

2265

drop table t1;

2364

create table t1 (s1 int);

2266

create table t1 (s1 int) ENGINE=MyISAM;

2365

2267

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

2366

2268

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

2367

2269

s1

2376

2278

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

2377

2279

Region char(26) NOT NULL default '',

2378

2280

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

2379

IndepYear smallint(6) default NULL,

2380

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

2281

IndepYear int default NULL,

2282

Population int NOT NULL default '0',

2381

2283

LifeExpectancy float(3,1) default NULL,

2382

2284

GNP float(10,2) default NULL,

2383

2285

GNPOld float(10,2) default NULL,

2384

2286

LocalName char(45) NOT NULL default '',

2385

2287

GovernmentForm char(45) NOT NULL default '',

2386

2288

HeadOfState char(60) default NULL,

2387

Capital int(11) default NULL,

2289

Capital int default NULL,

2388

2290

Code2 char(2) NOT NULL default ''

2389

2291

) ENGINE=MyISAM;

2390

2292

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

2398

2300

c

2399

2301

Oceania

2400

2302

drop table t1;

2401

create table t1 (a1 int);

2402

create table t2 (b1 int);

2303

create table t1 (a1 int) ENGINE=MyISAM;

2304

create table t2 (b1 int) ENGINE=MyISAM;

2403

2305

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

2404

2306

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

2405

2307

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

2406

2308

a1

2407

2309

drop table t1,t2;

2408

create table t1 (a integer, b integer);

2310

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

2409

2311

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

2410

2312

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

2411

2313

NULL

2419

2321

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

2420

2322

1

2421

2323

drop table t1;

2422

create table t1 (a integer, b integer);

2324

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

2423

2325

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

2424

2326

ERROR 21000: Operand should contain 2 column(s)

2425

2327

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

2438

2340

1 1

2439

2341

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

2440

2342

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

2441

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

2442

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

2443

2343

drop table t1;

2444

2344

CREATE TABLE t1 (

2445

categoryId int(11) NOT NULL,

2446

courseId int(11) NOT NULL,

2345

categoryId int NOT NULL,

2346

courseId int NOT NULL,

2447

2347

startDate datetime NOT NULL,

2448

2348

endDate datetime NOT NULL,

2449

2349

createDate datetime NOT NULL,

2450

2350

modifyDate timestamp NOT NULL,

2451

attributes text NOT NULL

2452

);

2351

attributes text NOT NULL)

2352

engine=myisam;

2453

2353

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

2454

2354

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

2455

2355

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

2460

2360

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

2461

2361

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

2462

2362

CREATE TABLE t2 (

2463

userId int(11) NOT NULL,

2464

courseId int(11) NOT NULL,

2465

date datetime NOT NULL

2466

);

2363

userId int NOT NULL,

2364

courseId int NOT NULL,

2365

date datetime NOT NULL)

2366

engine=myisam;

2467

2367

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

2468

2368

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

2469

2369

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

2471

2371

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

2472

2372

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

2473

2373

CREATE TABLE t3 (

2474

groupId int(11) NOT NULL,

2475

parentId int(11) NOT NULL,

2374

groupId int NOT NULL,

2375

parentId int NOT NULL,

2476

2376

startDate datetime NOT NULL,

2477

2377

endDate datetime NOT NULL,

2478

2378

createDate datetime NOT NULL,

2479

2379

modifyDate timestamp NOT NULL,

2480

ordering int(11)

2481

);

2380

ordering int)

2381

engine=myisam;

2482

2382

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

2483

2383

CREATE TABLE t4 (

2484

id int(11) NOT NULL,

2485

groupTypeId int(11) NOT NULL,

2384

id int NOT NULL,

2385

groupTypeId int NOT NULL,

2486

2386

groupKey varchar(50) NOT NULL,

2487

2387

name text,

2488

ordering int(11),

2388

ordering int,

2489

2389

description text,

2490

2390

createDate datetime NOT NULL,

2491

modifyDate timestamp NOT NULL

2492

);

2391

modifyDate timestamp NOT NULL)

2392

engine=myisam;

2493

2393

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

2494

2394

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

2495

2395

CREATE TABLE t5 (

2496

userId int(11) NOT NULL,

2497

groupId int(11) NOT NULL,

2396

userId int NOT NULL,

2397

groupId int NOT NULL,

2498

2398

createDate datetime NOT NULL,

2499

modifyDate timestamp NOT NULL

2500

);

2399

modifyDate timestamp NOT NULL) ENGINE=MyISAM;

2501

2400

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

2502

2401

select

2503

2402

count(distinct t2.userid) pass,

2541

2440

1 5141 12 group2 12 group2 5 1 2 88 Oct04

2542

2441

1 5141 12 group2 12 group2 5 1 2 89 Oct04

2543

2442

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

2544

create table t1 (a int);

2443

create table t1 (a int) ENGINE=MyISAM;

2545

2444

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

2546

2445

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

2547

2446

1

2549

2448

1

2550

2449

1

2551

2450

drop table t1;

2552

create table t1 (a int, b int);

2451

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

2553

2452

insert into t1 values (1,2);

2554

2453

select 1 = (select * from t1);

2555

2454

ERROR 21000: Operand should contain 1 column(s)

2564

2463

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

2565

2464

ERROR 21000: Operand should contain 2 column(s)

2566

2465

drop table t1;

2567

CREATE TABLE `t1` (

2568

`itemid` bigint(20) unsigned NOT NULL auto_increment,

2569

`sessionid` bigint(20) unsigned default NULL,

2570

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

2571

`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT

2572

NULL default '',

2573

`data` text collate latin1_general_ci NOT NULL,

2574

PRIMARY KEY (`itemid`)

2575

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2576

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

2577

CREATE TABLE `t2` (

2578

`sessionid` bigint(20) unsigned NOT NULL auto_increment,

2579

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

2580

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

2581

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

2582

PRIMARY KEY (`sessionid`)

2583

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2584

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

2585

SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;

2586

ip count( e.itemid )

2587

10.10.10.1 1

2588

drop tables t1,t2;

2589

create table t1 (fld enum('0','1'));

2466

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

2590

2467

insert into t1 values ('1');

2591

2468

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

2592

2469

max(fld)

2593

2470

1

2594

2471

drop table t1;

2595

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

2596

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

2472

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

2473

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

2597

2474

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

2598

2475

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

2599

2476

SELECT * FROM t1

2662

2539

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2663

2540

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

2664

2541

Warnings:

2665

Note 1003 select "test"."t1"."one" AS "one","test"."t1"."two" AS "two",<in_optimizer>(("test"."t1"."one","test"."t1"."two"),<exists>(select "test"."t2"."one" AS "one","test"."t2"."two" AS "two" from "test"."t2" where (("test"."t2"."flag" = '0') and trigcond(((<cache>("test"."t1"."one") = "test"."t2"."one") or isnull("test"."t2"."one"))) and trigcond(((<cache>("test"."t1"."two") = "test"."t2"."two") or isnull("test"."t2"."two")))) having (trigcond(<is_not_null_test>("test"."t2"."one")) and trigcond(<is_not_null_test>("test"."t2"."two"))))) AS "test" from "test"."t1"

2542

Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`

2666

2543

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

2667

2544

id select_type table type possible_keys key key_len ref rows filtered Extra

2668

2545

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary

2669

2546

1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer

2670

2547

Warnings:

2671

Note 1003 select "test"."t1"."one" AS "one","test"."t1"."two" AS "two" from "test"."t1" semi join ("test"."t2") where (("test"."t2"."two" = "test"."t1"."two") and ("test"."t2"."one" = "test"."t1"."one") and ("test"."t2"."flag" = 'N'))

2548

Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))

2672

2549

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

2673

2550

id select_type table type possible_keys key key_len ref rows filtered Extra

2674

2551

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2675

2552

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

2676

2553

Warnings:

2677

Note 1003 select "test"."t1"."one" AS "one","test"."t1"."two" AS "two",<in_optimizer>(("test"."t1"."one","test"."t1"."two"),<exists>(select "test"."t2"."one" AS "one","test"."t2"."two" AS "two" from "test"."t2" where ("test"."t2"."flag" = '0') group by "test"."t2"."one","test"."t2"."two" having (trigcond(((<cache>("test"."t1"."one") = "test"."t2"."one") or isnull("test"."t2"."one"))) and trigcond(((<cache>("test"."t1"."two") = "test"."t2"."two") or isnull("test"."t2"."two"))) and trigcond(<is_not_null_test>("test"."t2"."one")) and trigcond(<is_not_null_test>("test"."t2"."two"))))) AS "test" from "test"."t1"

2554

Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`

2678

2555

DROP TABLE t1,t2;

2679

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

2556

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

2680

2557

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

2681

2558

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

2682

2559

a b

2683

2560

aaa aaa

2684

2561

DROP TABLE t1;

2685

CREATE TABLE t1 (a int);

2686

CREATE TABLE t2 (a int, b int);

2687

CREATE TABLE t3 (b int NOT NULL);

2562

CREATE TABLE t1 (a int) ENGINE=MyISAM;

2563

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

2564

CREATE TABLE t3 (b int NOT NULL) ENGINE=MyISAM;

2688

2565

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

2689

2566

INSERT INTO t2 VALUES (1,10), (3,30);

2690

2567

SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b

2699

2576

3

2700

2577

4

2701

2578

DROP TABLE t1,t2,t3;

2702

CREATE TABLE t1 (f1 INT);

2703

CREATE TABLE t2 (f2 INT);

2579

CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;

2580

CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;

2704

2581

INSERT INTO t1 VALUES (1);

2705

2582

SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);

2706

2583

f1

2714

2591

f1

2715

2592

1

2716

2593

DROP TABLE t1, t2;

2717

select 1 from dual where 1 < any (select 2);

2718

1

2719

1

2720

select 1 from dual where 1 < all (select 2);

2721

1

2722

1

2723

select 1 from dual where 2 > any (select 1);

2724

1

2725

1

2726

select 1 from dual where 2 > all (select 1);

2727

1

2728

1

2729

select 1 from dual where 1 < any (select 2 from dual);

2730

1

2731

1

2732

select 1 from dual where 1 < all (select 2 from dual where 1!=1);

2733

1

2734

1

2735

create table t1 (s1 char);

2594

create table t1 (s1 char) ENGINE=MyISAM;

2736

2595

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

2737

2596

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

2738

2597

s1

2761

2620

drop table t1;

2762

2621

create table t1 (

2763

2622

retailerID varchar(8) NOT NULL,

2764

statusID int(10) unsigned NOT NULL,

2623

statusID int NOT NULL,

2765

2624

changed datetime NOT NULL,

2766

UNIQUE KEY retailerID (retailerID, statusID, changed)

2767

);

2625

UNIQUE KEY retailerID (retailerID, statusID, changed))

2626

ENGINE=MyISAM;

2768

2627

INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");

2769

2628

INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");

2770

2629

INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");

2781

2640

0048 1 2006-01-06 12:37:50

2782

2641

0059 1 2006-01-06 12:37:50

2783

2642

drop table t1;

2784

create table t1(a int, primary key (a));

2643

create table t1(a int, primary key (a)) ENGINE=MyISAM;

2785

2644

insert into t1 values (10);

2786

create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));

2645

create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)) ENGINE=MyISAM;

2787

2646

insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');

2788

explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2647

explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2789

2648

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2790

2649

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

2791

2650

id select_type table type possible_keys key key_len ref rows Extra

2792

2651

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2793

2652

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2794

2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition

2795

SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2653

2 DEPENDENT SUBQUERY t2 range b b 136 NULL 2 Using where

2654

SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2796

2655

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2797

2656

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

2798

2657

a a b

2799

2658

10 3 35989

2800

explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2659

explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2801

2660

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2802

2661

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2803

2662

id select_type table type possible_keys key key_len ref rows Extra

2804

2663

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2805

2664

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2806

2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition; Using MRR

2807

SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2665

2 DEPENDENT SUBQUERY t2 range b b 136 NULL 2 Using where; Using MRR

2666

SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2808

2667

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2809

2668

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2810

2669

a a b

2814

2673

field1 int NOT NULL,

2815

2674

field2 int NOT NULL,

2816

2675

field3 int NOT NULL,

2817

PRIMARY KEY (field1,field2,field3)

2818

);

2676

PRIMARY KEY (field1,field2,field3))

2677

ENGINE=MyISAM;

2819

2678

CREATE TABLE t2 (

2820

2679

fieldA int NOT NULL,

2821

2680

fieldB int NOT NULL,

2822

PRIMARY KEY (fieldA,fieldB)

2823

);

2681

PRIMARY KEY (fieldA,fieldB))

2682

ENGINE=MyISAM;

2824

2683

INSERT INTO t1 VALUES

2825

2684

(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);

2826

2685

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

2846

2705

1 1

2847

2706

1 3

2848

2707

DROP TABLE t1, t2;

2849

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

2708

CREATE TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;

2850

2709

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

2851

2710

INSERT INTO t1 VALUES (NULL);

2852

CREATE TABLE t2(a int);

2711

CREATE TABLE t2(a int) ENGINE=MyISAM;

2853

2712

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

2854

2713

EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;

2855

2714

id select_type table type possible_keys key key_len ref rows Extra

2861

2720

2 NULL

2862

2721

3 1

2863

2722

DROP TABLE t1,t2;

2864

CREATE TABLE t1 (a DATETIME);

2723

CREATE TABLE t1 (a DATETIME) ENGINE=MyISAM;

2865

2724

INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');

2866

CREATE TABLE t2 AS SELECT

2725

CREATE TABLE t2 ENGINE=MyISAM AS SELECT

2867

2726

(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a

2868

2727

FROM t1 WHERE a > '2000-01-01';

2869

2728

SHOW CREATE TABLE t2;

2870

2729

Table Create Table

2871

t2 CREATE TABLE "t2" (

2872

"sub_a" datetime ON UPDATE CURRENT_TIMESTAMP

2873

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2874

CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');

2730

t2 CREATE TABLE `t2` (

2731

`sub_a` datetime

2732

) ENGINE=MyISAM

2733

CREATE TABLE t3 ENGINE=MyISAM AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');

2875

2734

SHOW CREATE TABLE t3;

2876

2735

Table Create Table

2877

t3 CREATE TABLE "t3" (

2878

"a" datetime ON UPDATE CURRENT_TIMESTAMP

2879

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2736

t3 CREATE TABLE `t3` (

2737

`a` datetime

2738

) ENGINE=MyISAM

2880

2739

DROP TABLE t1,t2,t3;

2881

CREATE TABLE t1 (a int);

2882

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

2883

SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;

2884

a

2885

SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;

2886

a

2887

1

2888

2

2889

EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;

2890

id select_type table type possible_keys key key_len ref rows Extra

2891

1 PRIMARY t1 ALL NULL NULL NULL NULL 2

2892

2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

2893

DROP TABLE t1;

2894

CREATE TABLE t1 (a int);

2740

CREATE TABLE t1 (a int) ENGINE=MyISAM;

2895

2741

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

2896

CREATE TABLE t2 (b int, c int);

2742

CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;

2897

2743

INSERT INTO t2 VALUES

2898

2744

(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);

2899

2745

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );

2965

2811

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

2966

2812

ERROR 21000: Subquery returns more than 1 row

2967

2813

DROP TABLE t1,t2;

2968

create table t1 (df decimal(5,1));

2814

create table t1 (df decimal(5,1)) ENGINE=MyISAM;

2969

2815

insert into t1 values(1.1);

2970

2816

insert into t1 values(2.2);

2971

2817

select * from t1 where df <= all (select avg(df) from t1 group by df);

2975

2821

df

2976

2822

2.2

2977

2823

drop table t1;

2978

create table t1 (df decimal(5,1));

2824

create table t1 (df decimal(5,1)) ENGINE=MyISAM;

2979

2825

insert into t1 values(1.1);

2980

2826

select 1.1 * exists(select * from t1);

2981

2827

1.1 * exists(select * from t1)

2982

2828

1.1

2983

2829

drop table t1;

2984

2830

CREATE TABLE t1 (

2985

grp int(11) default NULL,

2986

a decimal(10,2) default NULL);

2831

grp int default NULL,

2832

a decimal(10,2) default NULL) ENGINE=MyISAM;

2987

2833

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

2988

2834

select * from t1;

2989

2835

grp a

3001

2847

2.00

3002

2848

4.00

3003

2849

drop table t1;

3004

CREATE table t1 ( c1 integer );

2850

CREATE table t1 ( c1 integer ) ENGINE=MyISAM;

3005

2851

INSERT INTO t1 VALUES ( 1 );

3006

2852

INSERT INTO t1 VALUES ( 2 );

3007

2853

INSERT INTO t1 VALUES ( 3 );

3008

CREATE TABLE t2 ( c2 integer );

2854

CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;

3009

2855

INSERT INTO t2 VALUES ( 1 );

3010

2856

INSERT INTO t2 VALUES ( 4 );

3011

2857

INSERT INTO t2 VALUES ( 5 );

3017

2863

c1 c2

3018

2864

1 1

3019

2865

DROP TABLE t1,t2;

3020

CREATE TABLE t1 ( c1 integer );

2866

CREATE TABLE t1 ( c1 integer ) ENGINE=MyISAM;

3021

2867

INSERT INTO t1 VALUES ( 1 );

3022

2868

INSERT INTO t1 VALUES ( 2 );

3023

2869

INSERT INTO t1 VALUES ( 3 );

3024

2870

INSERT INTO t1 VALUES ( 6 );

3025

CREATE TABLE t2 ( c2 integer );

2871

CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;

3026

2872

INSERT INTO t2 VALUES ( 1 );

3027

2873

INSERT INTO t2 VALUES ( 4 );

3028

2874

INSERT INTO t2 VALUES ( 5 );

3029

2875

INSERT INTO t2 VALUES ( 6 );

3030

CREATE TABLE t3 ( c3 integer );

2876

CREATE TABLE t3 ( c3 integer ) ENGINE=MyISAM;

3031

2877

INSERT INTO t3 VALUES ( 7 );

3032

2878

INSERT INTO t3 VALUES ( 8 );

3033

2879

SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2

3036

2882

2 NULL

3037

2883

3 NULL

3038

2884

DROP TABLE t1,t2,t3;

3039

CREATE TABLE `t1` (

3040

`itemid` bigint(20) unsigned NOT NULL auto_increment,

3041

`sessionid` bigint(20) unsigned default NULL,

3042

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

3043

`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT

3044

NULL default '',

3045

`data` text collate latin1_general_ci NOT NULL,

3046

PRIMARY KEY (`itemid`)

3047

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

3048

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

3049

CREATE TABLE `t2` (

3050

`sessionid` bigint(20) unsigned NOT NULL auto_increment,

3051

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

3052

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

3053

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

3054

PRIMARY KEY (`sessionid`)

3055

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

3056

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

3057

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;

3058

ip count( e.itemid )

3059

10.10.10.1 1

3060

drop tables t1,t2;

3061

CREATE TABLE t1 (EMPNUM CHAR(3));

3062

CREATE TABLE t2 (EMPNUM CHAR(3) );

2885

CREATE TABLE t1 (EMPNUM CHAR(3)) ENGINE=MyISAM;

2886

CREATE TABLE t2 (EMPNUM CHAR(3) ) ENGINE=MyISAM;

3063

2887

INSERT INTO t1 VALUES ('E1'),('E2');

3064

2888

INSERT INTO t2 VALUES ('E1');

3065

2889

DELETE FROM t1

3071

2895

EMPNUM

3072

2896

E1

3073

2897

DROP TABLE t1,t2;

3074

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);

2898

CREATE TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;

3075

2899

INSERT INTO t1 VALUES (1, 1);

3076

2900

CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,

3077

PRIMARY KEY(select_id,values_id));

2901

PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;

3078

2902

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

3079

2903

SELECT values_id FROM t1

3080

2904

WHERE values_id IN (SELECT values_id FROM t2

3092

2916

values_id

3093

2917

1

3094

2918

DROP TABLE t1, t2;

3095

create table t1 (fld enum('0','1'));

2919

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

3096

2920

insert into t1 values ('1');

3097

2921

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

3098

2922

max(fld)

3099

2923

1

3100

2924

drop table t1;

3101

CREATE TABLE t1 (a int, b int);

3102

CREATE TABLE t2 (c int, d int);

3103

CREATE TABLE t3 (e int);

2925

CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;

2926

CREATE TABLE t2 (c int, d int) ENGINE=MyISAM;

2927

CREATE TABLE t3 (e int) ENGINE=MyISAM;

3104

2928

INSERT INTO t1 VALUES

3105

2929

(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);

3106

2930

INSERT INTO t2 VALUES

3241

3065

3 20

3242

3066

4 40

3243

3067

DROP TABLE t1,t2,t3;

3244

CREATE TABLE t1 (a varchar(5), b varchar(10));

3068

CREATE TABLE t1 (a varchar(5), b varchar(10)) ENGINE=MyISAM;

3245

3069

INSERT INTO t1 VALUES

3246

3070

('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),

3247

3071

('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);

3267

3091

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3268

3092

2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort

3269

3093

DROP TABLE t1;

3270

create table t1( f1 int,f2 int);

3094

create table t1( f1 int,f2 int) ENGINE=MyISAM;

3271

3095

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

3272

3096

select tt.t from (select 'crash1' as t, f2 from t1) as tt left join t1 on tt.t = 'crash2' and tt.f2 = t1.f2 where tt.t = 'crash1';

3273

3097

t

3274

3098

crash1

3275

3099

crash1

3276

3100

drop table t1;

3277

create table t1 (c int, key(c));

3101

create table t1 (c int, key(c)) ENGINE=MyISAM;

3278

3102

insert into t1 values (1142477582), (1142455969);

3279

create table t2 (a int, b int);

3103

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

3280

3104

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

3281

3105

delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;

3282

3106

drop table t1, t2;

3283

create table t1 (i int, j bigint);

3107

create table t1 (i int, j bigint) ENGINE=MyISAM;

3284

3108

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

3285

3109

select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;

3286

3110

min(i)

3287

3111

1

3288

3112

drop table t1;

3289

CREATE TABLE t1 (i BIGINT UNSIGNED);

3290

INSERT INTO t1 VALUES (10000000000000000000);

3113

CREATE TABLE t1 (i BIGINT) ENGINE=MyISAM;

3114

INSERT INTO t1 VALUES (10000000000000000);

3291

3115

INSERT INTO t1 VALUES (1);

3292

CREATE TABLE t2 (i BIGINT UNSIGNED);

3293

INSERT INTO t2 VALUES (10000000000000000000);

3116

CREATE TABLE t2 (i BIGINT) ENGINE=MyISAM;

3117

INSERT INTO t2 VALUES (10000000000000000);

3294

3118

INSERT INTO t2 VALUES (1);

3295

3119

/* simple test */

3296

3120

SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;

3297

3121

i

3298

10000000000000000000

3122

10000000000000000

3299

3123

1

3300

3124

/* subquery test */

3301

3125

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3302

3126

i

3303

10000000000000000000

3127

10000000000000000

3304

3128

/* subquery test with cast*/

3305

SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);

3129

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3306

3130

i

3307

10000000000000000000

3131

10000000000000000

3308

3132

DROP TABLE t1;

3309

3133

DROP TABLE t2;

3310

3134

CREATE TABLE t1 (

3311

id bigint(20) unsigned NOT NULL auto_increment,

3135

id bigint NOT NULL auto_increment,

3312

3136

name varchar(255) NOT NULL,

3313

PRIMARY KEY (id)

3314

);

3137

PRIMARY KEY (id))

3138

ENGINE=MyISAM;

3315

3139

INSERT INTO t1 VALUES

3316

3140

(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

3317

3141

CREATE TABLE t2 (

3318

id bigint(20) unsigned NOT NULL auto_increment,

3319

mid bigint(20) unsigned NOT NULL,

3142

id bigint NOT NULL auto_increment,

3143

mid bigint NOT NULL,

3320

3144

date date NOT NULL,

3321

PRIMARY KEY (id)

3322

);

3145

PRIMARY KEY (id))

3146

ENGINE=MyISAM;

3323

3147

INSERT INTO t2 VALUES

3324

3148

(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),

3325

3149

(4, 2, '2006-04-20'), (5, 1, '2006-05-01');

3353

3177

3 Frank 2006-04-13 NULL

3354

3178

DROP TABLE t1,t2;

3355

3179

CREATE TABLE t1 (

3356

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

3357

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

3180

i1 int NOT NULL default '0',

3181

i2 int NOT NULL default '0',

3358

3182

t datetime NOT NULL default '0000-00-00 00:00:00',

3359

PRIMARY KEY (i1,i2,t)

3360

);

3183

PRIMARY KEY (i1,i2,t))

3184

ENGINE=MyISAM;

3361

3185

INSERT INTO t1 VALUES

3362

3186

(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),

3363

3187

(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),

3366

3190

(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),

3367

3191

(24,2,'2005-05-27 12:40:06');

3368

3192

CREATE TABLE t2 (

3369

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

3370

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

3193

i1 int NOT NULL default '0',

3194

i2 int NOT NULL default '0',

3371

3195

t datetime default NULL,

3372

PRIMARY KEY (i1)

3373

);

3196

PRIMARY KEY (i1))

3197

ENGINE=MyISAM;

3374

3198

INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');

3375

3199

EXPLAIN

3376

3200

SELECT * FROM t1,t2

3388

3212

i1 i2 t i1 i2 t

3389

3213

24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40

3390

3214

DROP TABLE t1, t2;

3391

CREATE TABLE t1 (i INT);

3215

CREATE TABLE t1 (i INT) ENGINE=MyISAM;

3392

3216

(SELECT i FROM t1) UNION (SELECT i FROM t1);

3393

3217

i

3394

SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS

3218

SELECT * FROM t1 WHERE NOT EXISTS

3395

3219

(

3396

3220

(SELECT i FROM t1) UNION

3397

3221

(SELECT i FROM t1)

3399

3223

i

3400

3224

SELECT * FROM t1

3401

3225

WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));

3402

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1

3226

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1

3403

3227

explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))

3404

3228

from t1;

3405

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))

3229

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'union (select t12.i from t1 t12))

3406

3230

from t1' at line 1

3407

3231

explain select * from t1 where not exists

3408

3232

((select t11.i from t1 t11) union (select t12.i from t1 t12));

3412

3236

3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table

3413

3237

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

3414

3238

DROP TABLE t1;

3415

CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));

3416

insert into t1 (a) values (FLOOR(rand() * 100));

3417

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3418

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3419

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3420

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3421

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3422

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3423

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3424

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3425

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3426

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3427

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3428

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3429

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3430

SELECT a,

3431

(SELECT REPEAT(' ',250) FROM t1 i1

3432

WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a

3433

FROM t1 ORDER BY a LIMIT 5;

3434

a a

3435

0 NULL

3436

0 NULL

3437

0 NULL

3438

0 NULL

3439

0 NULL

3440

DROP TABLE t1;

3441

CREATE TABLE t1 (a INT, b INT);

3442

CREATE TABLE t2 (a INT);

3239

CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;

3240

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

3443

3241

INSERT INTO t2 values (1);

3444

3242

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

3445

3243

SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;

3458

3256

2 2

3459

3257

1 1

3460

3258

1 1

3461

SELECT COUNT(DISTINCT t1.b),

3462

(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)

3463

FROM t1 GROUP BY t1.a;

3464

COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)

3465

2 2

3466

1 1

3467

1 1

3468

SELECT (

3469

SELECT (

3470

SELECT COUNT(DISTINCT t1.b)

3471

)

3472

)

3473

FROM t1 GROUP BY t1.a;

3474

(

3475

SELECT (

3476

SELECT COUNT(DISTINCT t1.b)

3477

)

3478

)

3479

2

3480

1

3481

1

3482

3259

SELECT (

3483

3260

SELECT (

3484

3261

SELECT (

3499

3276

2

3500

3277

2

3501

3278

DROP TABLE t1,t2;

3502

CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));

3279

CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;

3503

3280

CREATE TABLE t2 (x int auto_increment, y int, z int,

3504

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));

3281

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;

3505

3282

SET SESSION sort_buffer_size = 32 * 1024;

3506

3283

Warnings:

3507

3284

Warning 1292 Truncated incorrect sort_buffer_size value: '32768'

3508

SELECT SQL_NO_CACHE COUNT(*)

3285

SELECT COUNT(*)

3509

3286

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3510

3287

FROM t1) t;

3511

3288

COUNT(*)

3512

3289

3000

3513

3290

SET SESSION sort_buffer_size = 8 * 1024 * 1024;

3514

SELECT SQL_NO_CACHE COUNT(*)

3291

SELECT COUNT(*)

3515

3292

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3516

3293

FROM t1) t;

3517

3294

COUNT(*)

3518

3295

3000

3519

DROP TABLE t1,t2;

3520

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);

3521

CREATE TABLE t2 (c int);

3296

DROP TABLE t2,t1;

3297

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int) ENGINE=MyISAM;

3298

CREATE TABLE t2 (c int) ENGINE=MyISAM;

3522

3299

INSERT INTO t1 VALUES ('aa', 1);

3523

3300

INSERT INTO t2 VALUES (1);

3524

3301

SELECT * FROM t1

3538

3315

cc 3

3539

3316

dd 1

3540

3317

INSERT INTO t2 VALUES (2);

3541

CREATE TABLE t3 (c int);

3318

CREATE TABLE t3 (c int) ENGINE=MyISAM;

3542

3319

INSERT INTO t3 VALUES (1);

3543

3320

SELECT * FROM t1

3544

3321

WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1

3554

3331

DROP TABLE IF EXISTS t2;

3555

3332

DROP TABLE IF EXISTS t1xt2;

3556

3333

CREATE TABLE t1 (

3557

id_1 int(5) NOT NULL,

3558

t varchar(4) DEFAULT NULL

3559

);

3334

id_1 int NOT NULL,

3335

t varchar(4) DEFAULT NULL)

3336

ENGINE=MyISAM;

3560

3337

CREATE TABLE t2 (

3561

id_2 int(5) NOT NULL,

3562

t varchar(4) DEFAULT NULL

3563

);

3338

id_2 int NOT NULL,

3339

t varchar(4) DEFAULT NULL)

3340

ENGINE=MyISAM;

3564

3341

CREATE TABLE t1xt2 (

3565

id_1 int(5) NOT NULL,

3566

id_2 int(5) NOT NULL

3567

);

3342

id_1 int NOT NULL,

3343

id_2 int NOT NULL)

3344

ENGINE=MyISAM;

3568

3345

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

3569

3346

INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');

3570

3347

INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);

3663

3440

DROP TABLE t1;

3664

3441

DROP TABLE t2;

3665

3442

DROP TABLE t1xt2;

3666

CREATE TABLE t1 (a int);

3443

CREATE TABLE t1 (a int) ENGINE=MyISAM;

3667

3444

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

3668

3445

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;

3669

3446

col1 col2

3676

3453

this is a test. 1

3677

3454

this is a test. 2

3678

3455

DROP table t1;

3679

CREATE TABLE t1 (a int, b int);

3680

CREATE TABLE t2 (m int, n int);

3456

CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;

3457

CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;

3681

3458

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

3682

3459

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3683

3460

SELECT COUNT(*), a,

3701

3478

2 2

3702

3479

3 3

3703

3480

DROP TABLE t1,t2;

3704

CREATE TABLE t1 (a int, b int);

3705

CREATE TABLE t2 (m int, n int);

3481

CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;

3482

CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;

3706

3483

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

3707

3484

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3708

3485

SELECT COUNT(*) c, a,

3720

3497

3 3 4

3721

3498

1 4 2,2

3722

3499

DROP table t1,t2;

3723

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));

3500

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)) ENGINE=MyISAM;

3724

3501

INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),

3725

3502

(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),

3726

3503

(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');

3785

3562

2 o

3786

3563

3 p

3787

3564

DROP TABLE t1;

3788

CREATE TABLE t1 (a INT);

3565

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

3789

3566

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

3790

CREATE TABLE t2 (x INT);

3567

CREATE TABLE t2 (x INT) ENGINE=MyISAM;

3791

3568

INSERT INTO t1 values (1000),(1001),(1002);

3792

3569

SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;

3793

3570

ERROR HY000: Invalid use of group function

3794

3571

SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;

3795

3572

ERROR HY000: Invalid use of group function

3796

SELECT COUNT(1) FROM DUAL;

3797

COUNT(1)

3798

1

3799

SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;

3800

ERROR HY000: Invalid use of group function

3801

3573

SELECT

3802

3574

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )

3803

3575

FROM t1;

3807

3579

FROM t1;

3808

3580

ERROR HY000: Invalid use of group function

3809

3581

DROP TABLE t1,t2;

3810

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

3582

CREATE TABLE t1 (a int, b int, KEY (a)) ENGINE=MyISAM;

3811

3583

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

3812

3584

EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);

3813

3585

id select_type table type possible_keys key key_len ref rows Extra

3814

3586

1 PRIMARY t1 ref a a 5 const 1 Using where; Using index

3815

3587

2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort

3816

3588

DROP TABLE t1;

3817

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));

3589

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)) ENGINE=MyISAM;

3818

3590

INSERT INTO t1 VALUES

3819

3591

(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');

3820

CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));

3592

CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;

3821

3593

INSERT INTO t2 VALUES (7), (5), (1), (3);

3822

3594

SELECT id, st FROM t1

3823

3595

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3844

3616

2 GA

3845

3617

4 FL

3846

3618

DROP TABLE t1,t2;

3847

CREATE TABLE t1 (a int);

3619

CREATE TABLE t1 (a int) ENGINE=MyISAM;

3848

3620

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

3849

3621

EXPLAIN EXTENDED

3850

3622

SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;

3852

3624

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00

3853

3625

2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort

3854

3626

Warnings:

3855

Note 1003 select "res"."count(*)" AS "count(*)" from (select count(0) AS "count(*)" from "test"."t1" group by "test"."t1"."a") "res"

3627

Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`

3856

3628

DROP TABLE t1;

3857

3629

CREATE TABLE t1 (

3858

3630

a varchar(255) default NULL,

3859

3631

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3860

INDEX idx(a,b)

3861

);

3632

INDEX idx(a,b))

3633

ENGINE=MyISAM;

3862

3634

CREATE TABLE t2 (

3863

a varchar(255) default NULL

3864

);

3635

a varchar(255) default NULL)

3636

ENGINE=MyISAM;

3865

3637

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');

3866

3638

INSERT INTO t1 SELECT * FROM t1;

3867

3639

INSERT INTO t1 SELECT * FROM t1;

3882

3654

1

3883

3655

1

3884

3656

DROP TABLE t1,t2;

3885

CREATE TABLE t1 (a INTEGER, b INTEGER);

3886

CREATE TABLE t2 (x INTEGER);

3657

CREATE TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;

3658

CREATE TABLE t2 (x INTEGER) ENGINE=MyISAM;

3887

3659

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

3888

3660

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

3889

3661

SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;

3894

3666

(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)

3895

3667

3.3333

3896

3668

DROP TABLE t1,t2;

3897

CREATE TABLE t1 (a INT, b INT);

3669

CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;

3898

3670

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

3899

3671

SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1

3900

3672

AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)

3902

3674

a COUNT(*)

3903

3675

1 3

3904

3676

DROP TABLE t1;

3905

CREATE TABLE t1 (a INT);

3906

CREATE TABLE t2 (a INT);

3677

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

3678

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

3907

3679

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

3908

3680

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

3909

3681

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;

3915

3687

(SELECT SUM(t1.a) FROM t2 WHERE a=1)

3916

3688

3

3917

3689

DROP TABLE t1,t2;

3918

CREATE TABLE t1 (a1 INT, a2 INT);

3919

CREATE TABLE t2 (b1 INT, b2 INT);

3690

CREATE TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;

3691

CREATE TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;

3920

3692

INSERT INTO t1 VALUES (100, 200);

3921

3693

INSERT INTO t1 VALUES (101, 201);

3922

3694

INSERT INTO t2 VALUES (101, 201);

3926

3698

0

3927

3699

0

3928

3700

DROP TABLE t1, t2;

3929

CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));

3930

INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);

3931

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);

3932

s1 s2

3933

SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

3934

s1 s2

3935

CREATE INDEX I1 ON t1 (s1);

3936

CREATE INDEX I2 ON t1 (s2);

3937

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);

3938

s1 s2

3939

SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

3940

s1 s2

3941

TRUNCATE t1;

3942

INSERT INTO t1 VALUES (0x41,0x41);

3943

SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);

3944

s1 s2

3945

DROP TABLE t1;

3946

CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));

3947

CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));

3948

CREATE TABLE t3 (a3 BINARY(2) default '0');

3949

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

3950

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

3951

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

3952

SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;

3953

LEFT(t2.a2, 1)

3954

1

3955

2

3956

3

3957

SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;

3958

a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)

3959

1 0

3960

2 0

3961

3 0

3962

4 0

3963

DROP TABLE t1,t2,t3;

3964

CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));

3965

CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);

3966

CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);

3967

INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);

3968

INSERT INTO t2 VALUES (2), (3), (4), (5);

3969

INSERT INTO t3 VALUES (10), (20), (30);

3970

SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;

3971

LEFT(t1.a1,1)

3972

1

3973

2

3974

3

3975

SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);

3976

a2

3977

DROP TABLE t1, t2, t3;

3978

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));

3701

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)) ENGINE=MyISAM;

3979

3702

INSERT INTO t1 VALUES ('a', 'aa');

3980

3703

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

3981

3704

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3984

3707

CREATE INDEX I2 ON t1 (b);

3985

3708

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3986

3709

id select_type table type possible_keys key key_len ref rows Extra

3987

1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan

3988

1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition

3710

1 PRIMARY t1 index I1 I1 7 NULL 2 Using index; LooseScan

3711

1 PRIMARY t1 ref I2 I2 43 test.t1.a 2 Using where

3989

3712

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3990

3713

a b

3991

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));

3714

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)) ENGINE=MyISAM;

3992

3715

INSERT INTO t2 SELECT * FROM t1;

3993

3716

CREATE INDEX I1 ON t2 (a);

3994

3717

CREATE INDEX I2 ON t2 (b);

3995

3718

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

3996

3719

id select_type table type possible_keys key key_len ref rows Extra

3997

1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan

3998

1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition

3720

1 PRIMARY t2 index I1 I1 7 NULL 2 Using index; LooseScan

3721

1 PRIMARY t2 ref I2 I2 43 test.t2.a 2 Using where

3999

3722

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

4000

3723

a b

4001

3724

EXPLAIN

4002

3725

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4003

3726

id select_type table type possible_keys key key_len ref rows Extra

4004

1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan

4005

1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition

3727

1 PRIMARY t1 index I1 I1 7 NULL 2 Using where; Using index; LooseScan

3728

1 PRIMARY t1 ref I2 I2 43 test.t1.a 2 Using where

4006

3729

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4007

3730

a b

4008

3731

DROP TABLE t1,t2;

4009

CREATE TABLE t1(a INT, b INT);

3732

CREATE TABLE t1(a INT, b INT) ENGINE=MyISAM;

4010

3733

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

4011

3734

EXPLAIN

4012

3735

SELECT a AS out_a, MIN(b) FROM t1

4031

3754

1 2

4032

3755

2 4

4033

3756

DROP TABLE t1;

4034

CREATE TABLE t1 (a INT);

4035

CREATE TABLE t2 (a INT);

3757

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

3758

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

4036

3759

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

4037

3760

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

4038

3761

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));

4046

3769

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

4047

3770

Warnings:

4048

3771

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

4049

Note 1003 select 2 AS "2" from "test"."t1" where exists(select 1 AS "1" from "test"."t2" where ("test"."t1"."a" = "test"."t2"."a"))

3772

Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))

4050

3773

EXPLAIN EXTENDED

4051

3774

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION

4052

3775

(SELECT 1 FROM t2 WHERE t1.a = t2.a));

4058

3781

Warnings:

4059

3782

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

4060

3783

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

4061

Note 1003 select 2 AS "2" from "test"."t1" where exists((select 1 AS "1" from "test"."t2" where ("test"."t1"."a" = "test"."t2"."a")) union (select 1 AS "1" from "test"."t2" where ("test"."t1"."a" = "test"."t2"."a")))

3784

Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))

4062

3785

DROP TABLE t1,t2;

4063

3786

CREATE TABLE t4 (

4064

3787

f7 varchar(32) collate utf8_bin NOT NULL default '',

4065

3788

f10 varchar(32) collate utf8_bin default NULL,

4066

PRIMARY KEY (f7)

4067

);

3789

PRIMARY KEY (f7))

3790

ENGINE=MyISAM;

4068

3791

INSERT INTO t4 VALUES(1,1), (2,null);

4069

3792

CREATE TABLE t2 (

4070

3793

f4 varchar(32) collate utf8_bin NOT NULL default '',

4071

3794

f2 varchar(50) collate utf8_bin default NULL,

4072

3795

f3 varchar(10) collate utf8_bin default NULL,

4073

3796

PRIMARY KEY (f4),

4074

UNIQUE KEY uk1 (f2)

4075

);

3797

UNIQUE KEY uk1 (f2))

3798

ENGINE=MyISAM;

4076

3799

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

4077

CREATE TABLE t1 (

3800

CREATE TABLE t1 (

4078

3801

f8 varchar(32) collate utf8_bin NOT NULL default '',

4079

3802

f1 varchar(10) collate utf8_bin default NULL,

4080

3803

f9 varchar(32) collate utf8_bin default NULL,

4081

PRIMARY KEY (f8)

4082

);

3804

PRIMARY KEY (f8))

3805

ENGINE=MyISAM;

4083

3806

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

4084

3807

CREATE TABLE t3 (

4085

3808

f6 varchar(32) collate utf8_bin NOT NULL default '',

4086

3809

f5 varchar(50) collate utf8_bin default NULL,

4087

PRIMARY KEY (f6)

4088

);

3810

PRIMARY KEY (f6))

3811

ENGINE=MyISAM;

4089

3812

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

4090

3813

SELECT

4091

3814

IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,

4110

3833

DROP TABLE t1, t2, t3, t4;

4111

3834

End of 5.0 tests.

4112

3835

create table t_out (subcase char(3),

4113

a1 char(2), b1 char(2), c1 char(2));

4114

create table t_in (a2 char(2), b2 char(2), c2 char(2));

3836

a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;

3837

create table t_in (a2 char(2), b2 char(2), c2 char(2)) ENGINE=MyISAM;

4115

3838

insert into t_out values ('A.1','2a', NULL, '2a');

4116

3839

insert into t_out values ('A.3', '2a', NULL, '2a');

4117

3840

insert into t_out values ('A.4', '2a', NULL, 'xx');

4306

4029

T

4307

4030

drop table t_out;

4308

4031

drop table t_in;

4309

CREATE TABLE t1 (s1 char(1));

4032

CREATE TABLE t1 (s1 char(1)) ENGINE=MyISAM;

4310

4033

INSERT INTO t1 VALUES ('a');

4311

4034

SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);

4312

4035

s1

4313

4036

a

4314

4037

DROP TABLE t1;

4315

CREATE TABLE t1( a INT );

4038

CREATE TABLE t1( a INT ) ENGINE=MyISAM;

4316

4039

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

4317

CREATE TABLE t2( a INT, b INT );

4040

CREATE TABLE t2( a INT, b INT ) ENGINE=MyISAM;

4318

4041

SELECT *

4319

4042

FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;

4320

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2

4043

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2

4321

4044

SELECT *

4322

4045

FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;

4323

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2

4046

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2

4324

4047

SELECT *

4325

4048

FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;

4326

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2

4049

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2

4327

4050

SELECT * FROM (

4328

4051

SELECT 1 a

4329

4052

UNION

4330

4053

SELECT a INTO @var FROM t1 WHERE a = 2

4331

4054

) t1a;

4332

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2

4055

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2

4333

4056

) t1a' at line 2

4334

4057

SELECT * FROM (

4335

4058

SELECT 1 a

4336

4059

UNION

4337

4060

SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2

4338

4061

) t1a;

4339

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2

4062

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2

4340

4063

) t1a' at line 2

4341

4064

SELECT * FROM (

4342

4065

SELECT 1 a

4343

4066

UNION

4344

4067

SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4345

4068

) t1a;

4346

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4069

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4347

4070

) t1a' at line 2

4348

4071

SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;

4349

4072

a

4366

4089

1

4367

4090

2

4368

4091

SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);

4369

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

4092

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

4370

4093

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;

4371

4094

a

4372

4095

1

4374

4097

1

4375

4098

1

4376

4099

SELECT * FROM ((SELECT 1 a INTO @a)) t1a;

4377

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1

4100

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)) t1a' at line 1

4378

4101

SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;

4379

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1

4102

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1

4380

4103

SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4381

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1

4104

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1

4382

4105

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;

4383

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1

4106

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)) t1a' at line 1

4384

4107

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4385

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1

4108

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1

4386

4109

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;

4387

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1

4110

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1

4388

4111

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;

4389

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a))) t1a' at line 1

4112

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a))) t1a' at line 1

4390

4113

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;

4391

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1

4114

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1

4392

4115

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;

4393

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1

4116

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1

4394

4117

SELECT * FROM (SELECT 1 a ORDER BY a) t1a;

4395

4118

a

4396

4119

1

4408

4131

1 1

4409

4132

2 1

4410

4133

SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;

4411

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

4134

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

4412

4135

SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;

4413

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

4136

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

4414

4137

SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;

4415

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

4138

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

4416

4139

SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;

4417

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a ON 1' at line 1

4140

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a ON 1' at line 1

4418

4141

SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;

4419

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a ON 1' at line 1

4142

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a ON 1' at line 1

4420

4143

SELECT * FROM t1 JOIN (t1 t1a) ON 1;

4421

4144

a a

4422

4145

1 1

4454

4177

1 1

4455

4178

2 1

4456

4179

SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;

4457

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

4180

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

4458

4181

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );

4459

4182

a

4460

4183

1

4465

4188

a

4466

4189

1

4467

4190

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);

4468

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1

4191

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1

4469

4192

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4470

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4193

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4471

4194

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4472

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4195

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4473

4196

SELECT * FROM t1 WHERE a = ( SELECT 1 );

4474

4197

a

4475

4198

1

4477

4200

a

4478

4201

1

4479

4202

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);

4480

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1

4203

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1

4481

4204

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );

4482

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4205

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4483

4206

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );

4484

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4207

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4485

4208

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);

4486

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1

4209

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1

4487

4210

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4488

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4211

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4489

4212

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4490

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4213

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4491

4214

SELECT ( SELECT 1 INTO @v );

4492

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4215

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4493

4216

SELECT ( SELECT 1 INTO OUTFILE 'file' );

4494

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4217

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4495

4218

SELECT ( SELECT 1 INTO DUMPFILE 'file' );

4496

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4219

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4497

4220

SELECT ( SELECT 1 UNION SELECT 1 INTO @v );

4498

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4221

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4499

4222

SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4500

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4223

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4501

4224

SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4502

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4225

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4503

4226

SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;

4504

4227

( SELECT a FROM t1 WHERE a = 1 ) a

4505

4228

1 1

4511

4234

SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);

4512

4235

a b

4513

4236

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

4237

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

4515

4238

( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4516

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

4239

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

4517

4240

SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4518

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

4241

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

4519

4242

SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4520

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1' at line 1

4243

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1' at line 1

4521

4244

SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4522

4245

( SELECT 1 UNION SELECT 1 UNION SELECT 1 )

4523

4246

1

4525

4248

((SELECT 1 UNION SELECT 1 UNION SELECT 1))

4526

4249

1

4527

4250

SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4528

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

4251

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

4529

4252

SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4530

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

4253

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ') UNION SELECT 1 )' at line 1

4531

4254

SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;

4532

4255

1

4533

4256

1

4534

4257

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4535

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4258

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4536

4259

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4537

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4260

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4538

4261

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4539

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4262

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4540

4263

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4541

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4264

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1

4542

4265

SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4543

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1

4266

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1

4544

4267

SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4545

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1

4268

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1

4546

4269

SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4547

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1

4270

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1

4548

4271

SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4549

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1

4272

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1

4550

4273

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4551

4274

a

4552

4275

1

4560

4283

a

4561

4284

1

4562

4285

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );

4563

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4286

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4564

4287

SELECT EXISTS(SELECT 1+1);

4565

4288

EXISTS(SELECT 1+1)

4566

4289

1

4567

4290

SELECT EXISTS(SELECT 1+1 INTO @test);

4568

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @test)' at line 1

4291

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @test)' at line 1

4569

4292

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );

4570

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4293

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4571

4294

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );

4572

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4295

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4573

4296

SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );

4574

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4297

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1

4575

4298

DROP TABLE t1, t2;

4576

set optimizer_switch='';

4299

set optimizer_switch=0;

4577

4300

show variables like 'optimizer_switch';

4578

4301

Variable_name Value

4579

4302

optimizer_switch