~drizzle-trunk/drizzle/development : revision 642.1.53

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

187

4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

188

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

189

Warnings:

190

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

190

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

191

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

192

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

193

3 1

203

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

204

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

205

Warnings:

206

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

206

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

207

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

208

a

209

2

224

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

225

Warnings:

226

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

227

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

227

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

228

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

229

a

230

7

270

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

271

2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00

272

Warnings:

273

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

273

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

274

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

275

a

276

7

308

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

309

id select_type table type possible_keys key key_len ref rows filtered Extra

310

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00

311

2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

311

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

312

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

313

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

314

Warnings:

315

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

316

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

317

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

317

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

318

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

319

ERROR 21000: Subquery returns more than 1 row

320

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

332

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

333

Warnings:

334

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

335

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

335

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

336

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

337

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

338

drop table t1,t2,t3;

352

a b

353

W a

354

CREATE TABLE `t8` (

355

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

356

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

355

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

356

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

357

PRIMARY KEY (`pseudo`),

358

UNIQUE KEY `email` (`email`)

359

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

359

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

360

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

361

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

362

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

363

EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');

364

id select_type table type possible_keys key key_len ref rows filtered Extra

365

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

366

4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

367

2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00

368

3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

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

369

Warnings:

370

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

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

371

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

372

t8 WHERE pseudo='joce');

373

ERROR 21000: Operand should contain 1 column(s)

381

ERROR 21000: Subquery returns more than 1 row

382

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

383

CREATE TABLE `t1` (

384

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

384

`topic` bigint NOT NULL default '0',

385

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

386

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

386

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

387

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

388

KEY `topic` (`topic`)

389

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

391

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

392

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

393

id select_type table type possible_keys key key_len ref rows filtered Extra

394

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

394

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

395

Warnings:

396

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

396

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

397

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

398

id select_type table type possible_keys key key_len ref rows filtered Extra

399

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

400

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

400

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

401

Warnings:

402

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

402

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

403

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

404

date

405

2002-08-03

415

ERROR 21000: Subquery returns more than 1 row

416

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

417

id select_type table type possible_keys key key_len ref rows filtered Extra

418

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

418

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

419

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

420

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

421

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

422

Warnings:

423

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

423

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

424

drop table t1;

425

CREATE TABLE `t1` (

426

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

427

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

426

`numeropost` bigint NOT NULL auto_increment,

427

`maxnumrep` int NOT NULL default '0',

428

PRIMARY KEY (`numeropost`),

429

UNIQUE KEY `maxnumrep` (`maxnumrep`)

430

) ENGINE=MyISAM ROW_FORMAT=FIXED;

431

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

432

CREATE TABLE `t2` (

433

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

434

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

434

`topic` bigint NOT NULL default '0',

435

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

436

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

437

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

495

joce 43506 2002-10-22 joce 0

496

drop table t1,t2;

497

CREATE TABLE `t1` (

498

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

499

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

498

`numeropost` bigint NOT NULL auto_increment,

499

`maxnumrep` int NOT NULL default '0',

500

PRIMARY KEY (`numeropost`),

501

UNIQUE KEY `maxnumrep` (`maxnumrep`)

502

) ENGINE=MyISAM ROW_FORMAT=FIXED;

520

ERROR 21000: Subquery returns more than 1 row

521

drop table t1;

522

CREATE TABLE `t1` (

523

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

524

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

523

`numeropost` bigint NOT NULL default '0',

524

`numreponse` int NOT NULL auto_increment,

525

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

526

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

527

UNIQUE KEY `numreponse` (`numreponse`),

540

id select_type table type possible_keys key key_len ref rows filtered Extra

541

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

542

Warnings:

543

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

543

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

544

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

545

id select_type table type possible_keys key key_len ref rows filtered Extra

546

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

546

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

547

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

548

Warnings:

549

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

549

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

550

drop table t1;

551

CREATE TABLE t1 (a int(1));

551

CREATE TABLE t1 (a int);

552

INSERT INTO t1 VALUES (1);

553

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

554

1

609

2 12

610

select * from t12;

611

a b

612

2 12

613

22 11

612

614

33 10

613

22 11

614

2 12

615

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

616

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

617

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

623

1 11

624

select * from t12;

625

a b

626

22 11

626

627

33 10

627

22 11

628

drop table t11, t12, t2;

629

CREATE TABLE t1 (x int);

630

create table t2 (a int);

707

select * from t1;

708

x y

709

1 3

710

2 1

710

711

4 2

711

2 1

712

drop table t1, t2, t3;

713

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

714

ERROR HY000: No tables used

715

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

715

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

716

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

717

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

718

id

722

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

723

Warnings:

724

Note 1249 Select 2 was reduced during optimization

725

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

725

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

726

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

727

id

728

1

735

Warnings:

736

Note 1249 Select 3 was reduced during optimization

737

Note 1249 Select 2 was reduced during optimization

738

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

738

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

739

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

740

id select_type table type possible_keys key key_len ref rows filtered Extra

741

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

743

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

744

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

745

Warnings:

746

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

746

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

747

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

748

id

749

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

757

id

758

1

759

2

760

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;

761

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

762

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

763

ERROR 21000: Subquery returns more than 1 row

869

Warnings:

870

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

871

Note 1249 Select 2 was reduced during optimization

872

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

872

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

873

select (select a+1) from t1;

874

(select a+1)

875

2.5

876

NULL

877

876

4.5

877

NULL

878

drop table t1;

879

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

880

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

879

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

880

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

881

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

882

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

883

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

891

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

892

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

893

Warnings:

894

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

895

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

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

896

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

897

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

898

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

906

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

907

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

908

Warnings:

909

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

909

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

910

drop table t1,t2,t3;

911

create table t1 (a float);

912

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

913

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

914

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

915

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

916

drop table t1;

917

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

918

create table t2 (a int);

919

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

920

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

921

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

922

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

923

1 1 a

924

2 0 b

925

NULL 0 NULL

926

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

927

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

928

1 0 a

929

2 1 b

930

NULL NULL NULL

931

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

932

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

933

1 0 a

934

2 0 b

935

NULL 0 NULL

936

drop table t1,t2;

937

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

938

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

939

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

940

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

941

1

942

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

943

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

944

0

945

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

946

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

947

1

948

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

949

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

950

1

951

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

952

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

953

0

954

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

955

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

956

1

957

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

958

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

959

1

960

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

961

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

962

0

963

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

964

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

965

1

966

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

967

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

968

drop table t1;

969

CREATE TABLE t1 (a int(1));

911

CREATE TABLE t1 (a int);

970

912

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

971

913

id select_type table type possible_keys key key_len ref rows filtered Extra

972

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

973

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

974

Warnings:

975

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

976

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

977

id select_type table type possible_keys key key_len ref rows filtered Extra

978

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

979

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

980

Warnings:

981

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

914

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

915

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

916

Warnings:

917

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

982

918

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

983

919

id select_type table type possible_keys key key_len ref rows filtered Extra

984

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

985

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

920

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

921

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

986

922

Warnings:

987

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

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`

988

924

drop table t1;

989

925

CREATE TABLE `t1` (

990

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

991

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

926

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

927

`topic` bigint NOT NULL default '0',

992

928

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

993

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

929

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

994

930

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

995

931

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

996

932

KEY `topic` (`topic`)

997

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

933

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

998

934

CREATE TABLE `t2` (

999

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

1000

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

935

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

936

`topic` bigint NOT NULL default '0',

1001

937

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

1002

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

938

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

1003

939

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

1004

940

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

1005

941

KEY `topic` (`topic`)

1006

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

942

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

1007

943

CREATE TABLE `t3` (

1008

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

1009

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

944

`numeropost` bigint NOT NULL auto_increment,

945

`maxnumrep` int NOT NULL default '0',

1010

946

PRIMARY KEY (`numeropost`),

1011

947

UNIQUE KEY `maxnumrep` (`maxnumrep`)

1012

) ENGINE=MyISAM CHARSET=latin1;

948

) ENGINE=MyISAM;

1013

949

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

1014

950

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

1015

951

INSERT INTO t3 VALUES (1,1);

1033

969

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

1034

970

SHOW CREATE TABLE t1;

1035

971

Table Create Table

1036

t1 CREATE TABLE "t1" (

1037

"a" int(1) NOT NULL,

1038

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

1039

) ENGINE=MyISAM DEFAULT CHARSET=latin1

972

t1 CREATE TABLE `t1` (

973

`a` int NOT NULL,

974

`(SELECT 1)` int NOT NULL

975

) ENGINE=InnoDB

1040

976

drop table t1;

1041

977

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

1042

978

SHOW CREATE TABLE t1;

1043

979

Table Create Table

1044

t1 CREATE TABLE "t1" (

1045

"a" int(1) NOT NULL,

1046

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

1047

) ENGINE=MyISAM DEFAULT CHARSET=latin1

980

t1 CREATE TABLE `t1` (

981

`a` int NOT NULL,

982

`(SELECT a)` int NOT NULL

983

) ENGINE=InnoDB

1048

984

drop table t1;

1049

985

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

1050

986

SHOW CREATE TABLE t1;

1051

987

Table Create Table

1052

t1 CREATE TABLE "t1" (

1053

"a" int(1) NOT NULL,

1054

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

1055

) ENGINE=MyISAM DEFAULT CHARSET=latin1

988

t1 CREATE TABLE `t1` (

989

`a` int NOT NULL,

990

`(SELECT a+0)` int NOT NULL

991

) ENGINE=InnoDB

1056

992

drop table t1;

1057

993

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

1058

994

select * from t1;

1060

996

2

1061

997

SHOW CREATE TABLE t1;

1062

998

Table Create Table

1063

t1 CREATE TABLE "t1" (

1064

"a" bigint(20) NOT NULL

1065

) ENGINE=MyISAM DEFAULT CHARSET=latin1

999

t1 CREATE TABLE `t1` (

1000

`a` bigint NOT NULL

1001

) ENGINE=InnoDB

1066

1002

drop table t1;

1067

1003

create table t1 (a int);

1068

1004

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

1070

1006

from t1;

1071

1007

id select_type table type possible_keys key key_len ref rows filtered Extra

1072

1008

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

1073

2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1074

3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1009

2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1010

3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1075

1011

Warnings:

1076

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

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`

1077

1013

drop table t1;

1078

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

1079

1015

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

1080

1016

CREATE TABLE t1 (

1081

ID int(11) NOT NULL auto_increment,

1017

ID int NOT NULL auto_increment,

1082

1018

name char(35) NOT NULL default '',

1083

1019

t2 char(3) NOT NULL default '',

1084

1020

District char(20) NOT NULL default '',

1085

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

1021

Population int NOT NULL default '0',

1086

1022

PRIMARY KEY (ID)

1087

1023

) ENGINE=MyISAM;

1088

1024

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

1094

1030

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

1095

1031

Region char(26) NOT NULL default '',

1096

1032

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

1097

IndepYear smallint(6) default NULL,

1098

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

1033

IndepYear int default NULL,

1034

Population int NOT NULL default '0',

1099

1035

LifeExpectancy float(3,1) default NULL,

1100

1036

GNP float(10,2) default NULL,

1101

1037

GNPOld float(10,2) default NULL,

1102

1038

LocalName char(45) NOT NULL default '',

1103

1039

GovernmentForm char(45) NOT NULL default '',

1104

1040

HeadOfState char(60) default NULL,

1105

Capital int(11) default NULL,

1041

Capital int default NULL,

1106

1042

Code2 char(2) NOT NULL default '',

1107

1043

PRIMARY KEY (Code)

1108

1044

) ENGINE=MyISAM;

1113

1049

Oceania Sydney 3276207

1114

1050

drop table t1, t2;

1115

1051

CREATE TABLE `t1` (

1116

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

1117

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

1052

`id` bigint NOT NULL auto_increment,

1053

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

1118

1054

PRIMARY KEY (`id`),

1119

1055

UNIQUE KEY `pseudo` (`pseudo`)

1120

1056

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

1127

1063

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

1128

1064

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

1129

1065

Warnings:

1130

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

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

1131

1067

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

1132

1068

SELECT 0 IN (SELECT 1 FROM t1 a);

1133

1069

0 IN (SELECT 1 FROM t1 a)

1137

1073

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

1138

1074

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

1139

1075

Warnings:

1140

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

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

1141

1077

drop table t1;

1142

1078

CREATE TABLE `t1` (

1143

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

1079

`i` int NOT NULL default '0',

1144

1080

PRIMARY KEY (`i`)

1145

) ENGINE=MyISAM CHARSET=latin1;

1081

) ENGINE=MyISAM;

1146

1082

INSERT INTO t1 VALUES (1);

1147

1083

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

1148

1084

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

1153

1089

3

1154

1090

drop table t1;

1155

1091

CREATE TABLE t1 (

1156

id int(11) default NULL

1157

) ENGINE=MyISAM CHARSET=latin1;

1092

id int default NULL

1093

) ENGINE=MyISAM;

1158

1094

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

1159

1095

CREATE TABLE t2 (

1160

id int(11) default NULL,

1096

id int default NULL,

1161

1097

name varchar(15) default NULL

1162

) ENGINE=MyISAM CHARSET=latin1;

1098

) ENGINE=MyISAM;

1163

1099

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

1164

1100

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

1165

1101

select * from t2;

1179

1115

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

1180

1116

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

1181

1117

id select_type table type possible_keys key key_len ref rows filtered Extra

1182

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

1118

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

1183

1119

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

1184

1120

Warnings:

1185

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

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

1186

1122

drop table t1;

1187

1123

CREATE TABLE t1 (

1188

ID int(10) unsigned NOT NULL auto_increment,

1189

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

1190

REF_ID int(10) unsigned default NULL,

1191

REF_SUB int(3) unsigned default '0',

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

1192

1128

PRIMARY KEY (ID,SUB_ID),

1193

1129

UNIQUE KEY t1_PK (ID,SUB_ID),

1194

1130

KEY t1_FK (REF_ID,REF_SUB),

1195

1131

KEY t1_REFID (REF_ID)

1196

) ENGINE=MyISAM CHARSET=cp1251;

1132

) ENGINE=MyISAM;

1197

1133

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

1198

1134

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

1199

1135

REF_ID

1212

1148

3 1

1213

1149

drop table t1, t2;

1214

1150

CREATE TABLE `t1` (

1215

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

1151

`id` bigint NOT NULL auto_increment,

1216

1152

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

1217

1153

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

1218

1154

PRIMARY KEY (`id`),

1219

1155

UNIQUE KEY `email` (`email`),

1220

1156

UNIQUE KEY `pseudo` (`pseudo`)

1221

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

1157

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

1222

1158

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

1223

1159

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

1224

1160

a b

1242

1178

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

1243

1179

id select_type table type possible_keys key key_len ref rows filtered Extra

1244

1180

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

1245

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

1181

1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index

1246

1182

Warnings:

1247

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

1183

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

1248

1184

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

1249

1185

a

1250

1186

2

1254

1190

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

1255

1191

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

1256

1192

Warnings:

1257

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

1193

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

1258

1194

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

1259

1195

a

1260

1196

2

1265

1201

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

1266

1202

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

1267

1203

Warnings:

1268

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

1204

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

1269

1205

drop table t1, t2, t3;

1270

1206

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

1271

1207

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

1281

1217

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

1282

1218

id select_type table type possible_keys key key_len ref rows filtered Extra

1283

1219

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

1284

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

1220

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

1285

1221

Warnings:

1286

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

1222

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

1287

1223

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

1288

1224

a

1289

1225

2

1291

1227

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

1292

1228

id select_type table type possible_keys key key_len ref rows filtered Extra

1293

1229

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

1294

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

1230

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

1295

1231

Warnings:

1296

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

1232

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

1297

1233

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

1298

1234

a

1299

1235

2

1300

1236

3

1301

1237

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

1302

1238

id select_type table type possible_keys key key_len ref rows filtered Extra

1303

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

1304

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

1305

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

1239

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

1240

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

1241

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

1306

1242

Warnings:

1307

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

1243

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

1308

1244

insert into t1 values (3,31);

1309

1245

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

1310

1246

a

1318

1254

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

1319

1255

id select_type table type possible_keys key key_len ref rows filtered Extra

1320

1256

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

1321

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

1257

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

1322

1258

Warnings:

1323

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

1259

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

1324

1260

drop table t1, t2, t3;

1325

1261

create table t1 (a int, b int);

1326

1262

create table t2 (a int, b int);

1349

1285

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

1350

1286

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

1351

1287

DROP TABLE t1, t2;

1352

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

1353

s2 CHAR(5) COLLATE latin1_swedish_ci);

1354

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

1355

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

1356

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

1357

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

1358

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

1359

drop table t1;

1360

1288

create table t1(toid int,rd int);

1361

1289

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

1362

1290

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

1375

1303

tttt

1376

1304

explain extended (select * from t1);

1377

1305

id select_type table type possible_keys key key_len ref rows filtered Extra

1378

1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00

1306

1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00

1379

1307

Warnings:

1380

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

1308

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

1381

1309

(select * from t1);

1382

1310

s1

1383

1311

tttt

1408

1336

a3 1

1409

1337

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

1410

1338

id select_type table type possible_keys key key_len ref rows filtered Extra

1411

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

1412

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

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

1413

1341

Warnings:

1414

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

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`

1415

1343

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

1416

1344

id select_type table type possible_keys key key_len ref rows filtered Extra

1417

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

1418

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

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

1419

1347

Warnings:

1420

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

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`

1421

1349

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

1422

1350

id select_type table type possible_keys key key_len ref rows filtered Extra

1423

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

1424

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

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

1425

1353

Warnings:

1426

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`

1427

1355

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

1428

1356

id select_type table type possible_keys key key_len ref rows filtered Extra

1429

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

1430

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

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

1431

1359

Warnings:

1432

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

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`

1433

1361

drop table t1,t2;

1434

1362

create table t2 (a int, b int);

1435

1363

create table t3 (a int);

1442

1370

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

1443

1371

id select_type table type possible_keys key key_len ref rows filtered Extra

1444

1372

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

1445

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

1373

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1446

1374

Warnings:

1447

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(`test`.`t2`.`b`) from `test`.`t2`)))

1448

1376

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

1449

1377

a

1450

1378

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

1451

1379

id select_type table type possible_keys key key_len ref rows filtered Extra

1452

1380

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

1453

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

1381

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1454

1382

Warnings:

1455

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(`test`.`t2`.`b`) from `test`.`t2`)))

1456

1384

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

1457

1385

a

1458

1386

6

1461

1389

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

1462

1390

id select_type table type possible_keys key key_len ref rows filtered Extra

1463

1391

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

1464

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

1392

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

1465

1393

Warnings:

1466

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

1394

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

1467

1395

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

1468

1396

a

1469

1397

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

1470

1398

id select_type table type possible_keys key key_len ref rows filtered Extra

1471

1399

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

1472

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

1400

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

1473

1401

Warnings:

1474

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

1402

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

1475

1403

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

1476

1404

a

1477

1405

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

1478

1406

id select_type table type possible_keys key key_len ref rows filtered Extra

1479

1407

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

1480

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

1408

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1481

1409

Warnings:

1482

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

1483

1411

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

1484

1412

a

1485

1413

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

1486

1414

id select_type table type possible_keys key key_len ref rows filtered Extra

1487

1415

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

1488

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

1416

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

1489

1417

Warnings:

1490

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

1491

1419

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

1492

1420

a

1493

1421

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

1494

1422

id select_type table type possible_keys key key_len ref rows filtered Extra

1495

1423

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

1496

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

1424

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1497

1425

Warnings:

1498

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

1499

1427

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

1500

1428

a

1501

1429

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

1502

1430

id select_type table type possible_keys key key_len ref rows filtered Extra

1503

1431

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

1504

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

1432

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

1505

1433

Warnings:

1506

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

1507

1435

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

1508

1436

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

1509

1437

a

1514

1442

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

1515

1443

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

1516

1444

Warnings:

1517

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

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

1518

1446

drop table t2, t3;

1519

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

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 ;

1520

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

1521

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

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;

1522

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

1523

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

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 ;

1524

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

1525

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;

1526

1454

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

1527

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;

1528

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

1534

1462

-1 Valid

1535

1463

-1 Valid 2

1536

1464

drop table t1,t2,t3,t4;

1537

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

1465

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

1538

1466

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

1539

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

1467

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

1540

1468

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

1541

1469

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

1542

1470

ERROR 21000: Operand should contain 3 column(s)

1551

1479

e

1552

1480

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

1553

1481

id select_type table type possible_keys key key_len ref rows filtered Extra

1554

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00

1555

2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

1556

3 UNION t1 system NULL NULL NULL NULL 1 100.00

1482

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

1483

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

1484

3 UNION t1 ALL NULL NULL NULL NULL 1 100.00

1557

1485

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

1558

1486

Warnings:

1559

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

1487

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

1560

1488

drop table t1;

1561

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;

1562

1490

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

1563

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

1491

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

1564

1492

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

1565

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;

1566

1494

phone code

1585

1513

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

1586

1514

COLC

1587

1515

DROP TABLE t1, t2;

1588

CREATE TABLE t1 (a int(1));

1516

CREATE TABLE t1 (a int);

1589

1517

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

1590

1518

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

1591

1519

(SELECT a)

1596

1524

5

1597

1525

DROP TABLE t1;

1598

1526

CREATE TABLE `t1` (

1599

`id` int(11) NOT NULL auto_increment,

1600

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

1527

`id` int NOT NULL auto_increment,

1528

`id_cns` int NOT NULL default '0',

1601

1529

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

1602

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

1603

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

1604

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

1605

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

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

1606

1534

PRIMARY KEY (`id`),

1607

1535

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

1608

1536

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

1609

1537

);

1610

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

1611

1539

CREATE TABLE `t2` (

1612

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

1613

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

1540

`id` int NOT NULL auto_increment,

1541

`max_anno_dep` int NOT NULL default '0',

1614

1542

PRIMARY KEY (`id`)

1615

1543

);

1616

1544

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

1666

1594

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

1667

1595

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

1668

1596

Warnings:

1669

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

1597

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

1670

1598

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

1671

1599

id select_type table type possible_keys key key_len ref rows filtered Extra

1672

1600

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

1673

1601

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

1674

1602

Warnings:

1675

1603

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

1676

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

1604

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

1677

1605

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

1678

1606

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

1679

1607

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

1697

1625

id select_type table type possible_keys key key_len ref rows filtered Extra

1698

1626

1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00

1699

1627

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

1700

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

1628

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

1701

1629

Warnings:

1702

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

1630

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

1703

1631

drop table t1,t2;

1704

1632

create table t1 (a int);

1705

1633

insert into t1 values (1);

1706

1634

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

1707

1635

id select_type table type possible_keys key key_len ref rows Extra

1708

1636

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

1709

2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 1

1637

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where

1710

1638

drop table t1;

1711

1639

create table t1(id int);

1712

1640

create table t2(id int);

1713

1641

create table t3(flag int);

1714

1642

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

1715

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

1643

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

1716

1644

drop table t1,t2,t3;

1717

1645

CREATE TABLE t1 (id INT);

1718

1646

CREATE TABLE t2 (id INT);

2209

2137

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

2210

2138

Warnings:

2211

2139

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

2212

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

2140

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

2213

2141

drop table t1;

2214

2142

CREATE TABLE t1 (t1_a int);

2215

2143

INSERT INTO t1 VALUES (1);

2220

2148

t1_a t2_a t2_b

2221

2149

1 1 2

2222

2150

DROP TABLE t1, t2;

2223

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

2151

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

2224

2152

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

2225

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

2153

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

2226

2154

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

2227

2155

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

2228

2156

id name id pet

2230

2158

2 Rebecca 2 Spot

2231

2159

3 NULL 3 Felix

2232

2160

drop table t1,t2;

2233

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

2161

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

2234

2162

insert into t1 values (1);

2235

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

2163

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

2236

2164

insert into t2 values (1,2);

2237

2165

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

2238

2166

a C

2239

2167

1 1

2240

2168

drop table t1,t2;

2241

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

2242

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

2243

create table t2 (a int);

2244

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

2245

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

2246

a b

2247

1 ball

2248

2 ball games

2249

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

2250

a

2251

1

2252

2

2253

drop table t1,t2;

2254

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

2169

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

2255

2170

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

2256

2171

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

2257

2172

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

2261

2176

IZAVORGANG_ID

2262

2177

D0000000001

2263

2178

drop table t1;

2264

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

2265

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

2179

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

2180

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

2266

2181

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

2267

2182

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

2268

2183

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

2320

2235

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

2321

2236

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

2322

2237

drop table t1;

2323

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

2238

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

2324

2239

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

2325

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

2240

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

2326

2241

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

2327

2242

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

2328

2243

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

2344

2259

2 4 NULL

2345

2260

2 5 NULL

2346

2261

drop table t1, t2;

2347

create table t1 (id int);

2348

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

2349

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

2350

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

2351

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

2352

count(distinct id)

2353

1

2354

drop table t2,t1;

2355

2262

create table t1 (s1 int,s2 int);

2356

2263

insert into t1 values (20,15);

2357

2264

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

2372

2279

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

2373

2280

Region char(26) NOT NULL default '',

2374

2281

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

2375

IndepYear smallint(6) default NULL,

2376

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

2282

IndepYear int default NULL,

2283

Population int NOT NULL default '0',

2377

2284

LifeExpectancy float(3,1) default NULL,

2378

2285

GNP float(10,2) default NULL,

2379

2286

GNPOld float(10,2) default NULL,

2380

2287

LocalName char(45) NOT NULL default '',

2381

2288

GovernmentForm char(45) NOT NULL default '',

2382

2289

HeadOfState char(60) default NULL,

2383

Capital int(11) default NULL,

2290

Capital int default NULL,

2384

2291

Code2 char(2) NOT NULL default ''

2385

2292

) ENGINE=MyISAM;

2386

2293

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

2434

2341

1 1

2435

2342

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

2436

2343

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

2437

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

2438

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

2439

2344

drop table t1;

2440

2345

CREATE TABLE t1 (

2441

categoryId int(11) NOT NULL,

2442

courseId int(11) NOT NULL,

2346

categoryId int NOT NULL,

2347

courseId int NOT NULL,

2443

2348

startDate datetime NOT NULL,

2444

2349

endDate datetime NOT NULL,

2445

2350

createDate datetime NOT NULL,

2456

2361

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

2457

2362

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

2458

2363

CREATE TABLE t2 (

2459

userId int(11) NOT NULL,

2460

courseId int(11) NOT NULL,

2364

userId int NOT NULL,

2365

courseId int NOT NULL,

2461

2366

date datetime NOT NULL

2462

2367

);

2463

2368

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

2467

2372

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

2468

2373

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

2469

2374

CREATE TABLE t3 (

2470

groupId int(11) NOT NULL,

2471

parentId int(11) NOT NULL,

2375

groupId int NOT NULL,

2376

parentId int NOT NULL,

2472

2377

startDate datetime NOT NULL,

2473

2378

endDate datetime NOT NULL,

2474

2379

createDate datetime NOT NULL,

2475

2380

modifyDate timestamp NOT NULL,

2476

ordering int(11)

2381

ordering int

2477

2382

);

2478

2383

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

2479

2384

CREATE TABLE t4 (

2480

id int(11) NOT NULL,

2481

groupTypeId int(11) NOT NULL,

2385

id int NOT NULL,

2386

groupTypeId int NOT NULL,

2482

2387

groupKey varchar(50) NOT NULL,

2483

2388

name text,

2484

ordering int(11),

2389

ordering int,

2485

2390

description text,

2486

2391

createDate datetime NOT NULL,

2487

2392

modifyDate timestamp NOT NULL

2489

2394

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

2490

2395

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

2491

2396

CREATE TABLE t5 (

2492

userId int(11) NOT NULL,

2493

groupId int(11) NOT NULL,

2397

userId int NOT NULL,

2398

groupId int NOT NULL,

2494

2399

createDate datetime NOT NULL,

2495

2400

modifyDate timestamp NOT NULL

2496

2401

);

2560

2465

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

2561

2466

ERROR 21000: Operand should contain 2 column(s)

2562

2467

drop table t1;

2563

CREATE TABLE `t1` (

2564

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

2565

`sessionid` bigint(20) unsigned default NULL,

2566

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

2567

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

2568

NULL default '',

2569

`data` text collate latin1_general_ci NOT NULL,

2570

PRIMARY KEY (`itemid`)

2571

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2572

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

2573

CREATE TABLE `t2` (

2574

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

2575

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

2576

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

2577

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

2578

PRIMARY KEY (`sessionid`)

2579

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2580

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

2581

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

2582

ip count( e.itemid )

2583

10.10.10.1 1

2584

drop tables t1,t2;

2585

2468

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

2586

2469

insert into t1 values ('1');

2587

2470

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

2658

2541

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2659

2542

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

2660

2543

Warnings:

2661

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

2544

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

2662

2545

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

2663

2546

id select_type table type possible_keys key key_len ref rows filtered Extra

2664

2547

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

2665

2548

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

2666

2549

Warnings:

2667

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

2550

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

2668

2551

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

2669

2552

id select_type table type possible_keys key key_len ref rows filtered Extra

2670

2553

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2671

2554

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

2672

2555

Warnings:

2673

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

2556

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

2674

2557

DROP TABLE t1,t2;

2675

2558

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

2676

2559

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

2710

2593

f1

2711

2594

1

2712

2595

DROP TABLE t1, t2;

2713

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

2714

1

2715

1

2716

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

2717

1

2718

1

2719

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

2720

1

2721

1

2722

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

2723

1

2724

1

2725

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

2726

1

2727

1

2728

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

2729

1

2730

1

2731

2596

create table t1 (s1 char);

2732

2597

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

2733

2598

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

2757

2622

drop table t1;

2758

2623

create table t1 (

2759

2624

retailerID varchar(8) NOT NULL,

2760

statusID int(10) unsigned NOT NULL,

2625

statusID int NOT NULL,

2761

2626

changed datetime NOT NULL,

2762

2627

UNIQUE KEY retailerID (retailerID, statusID, changed)

2763

2628

);

2781

2646

insert into t1 values (10);

2782

2647

create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));

2783

2648

insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');

2784

explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2649

explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2785

2650

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2786

2651

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

2787

2652

id select_type table type possible_keys key key_len ref rows Extra

2788

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2653

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2789

2654

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2790

2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition

2791

SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2655

2 DEPENDENT SUBQUERY t2 ref b b 5 1 Using where; Using index

2656

SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2792

2657

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2793

2658

ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;

2794

2659

a a b

2795

2660

10 3 35989

2796

explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2661

explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2797

2662

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2798

2663

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2799

2664

id select_type table type possible_keys key key_len ref rows Extra

2800

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2665

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2801

2666

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2802

2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition; Using MRR

2803

SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2667

2 DEPENDENT SUBQUERY t2 ref b b 5 1 Using where; Using index

2668

SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2804

2669

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2805

2670

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2806

2671

a a b

2864

2729

FROM t1 WHERE a > '2000-01-01';

2865

2730

SHOW CREATE TABLE t2;

2866

2731

Table Create Table

2867

t2 CREATE TABLE "t2" (

2868

"sub_a" datetime

2869

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2732

t2 CREATE TABLE `t2` (

2733

`sub_a` datetime

2734

) ENGINE=InnoDB

2870

2735

CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');

2871

2736

SHOW CREATE TABLE t3;

2872

2737

Table Create Table

2873

t3 CREATE TABLE "t3" (

2874

"a" datetime

2875

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2738

t3 CREATE TABLE `t3` (

2739

`a` datetime

2740

) ENGINE=InnoDB

2876

2741

DROP TABLE t1,t2,t3;

2877

2742

CREATE TABLE t1 (a int);

2878

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

2879

SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;

2880

a

2881

SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;

2882

a

2883

1

2884

2

2885

EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;

2886

id select_type table type possible_keys key key_len ref rows Extra

2887

1 PRIMARY t1 ALL NULL NULL NULL NULL 2

2888

2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

2889

DROP TABLE t1;

2890

CREATE TABLE t1 (a int);

2891

2743

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

2892

2744

CREATE TABLE t2 (b int, c int);

2893

2745

INSERT INTO t2 VALUES

2978

2830

1.1

2979

2831

drop table t1;

2980

2832

CREATE TABLE t1 (

2981

grp int(11) default NULL,

2833

grp int default NULL,

2982

2834

a decimal(10,2) default NULL);

2983

2835

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

2984

2836

select * from t1;

3032

2884

2 NULL

3033

2885

3 NULL

3034

2886

DROP TABLE t1,t2,t3;

3035

CREATE TABLE `t1` (

3036

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

3037

`sessionid` bigint(20) unsigned default NULL,

3038

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

3039

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

3040

NULL default '',

3041

`data` text collate latin1_general_ci NOT NULL,

3042

PRIMARY KEY (`itemid`)

3043

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

3044

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

3045

CREATE TABLE `t2` (

3046

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

3047

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

3048

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

3049

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

3050

PRIMARY KEY (`sessionid`)

3051

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

3052

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

3053

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;

3054

ip count( e.itemid )

3055

10.10.10.1 1

3056

drop tables t1,t2;

3057

2887

CREATE TABLE t1 (EMPNUM CHAR(3));

3058

2888

CREATE TABLE t2 (EMPNUM CHAR(3) );

3059

2889

INSERT INTO t1 VALUES ('E1'),('E2');

3261

3091

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3262

3092

id select_type table type possible_keys key key_len ref rows Extra

3263

3093

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3264

2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort

3094

2 SUBQUERY t1 index NULL a 23 NULL 9

3265

3095

DROP TABLE t1;

3266

3096

create table t1( f1 int,f2 int);

3267

3097

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

3282

3112

min(i)

3283

3113

1

3284

3114

drop table t1;

3285

CREATE TABLE t1 (i BIGINT UNSIGNED);

3286

INSERT INTO t1 VALUES (10000000000000000000);

3115

CREATE TABLE t1 (i BIGINT);

3116

INSERT INTO t1 VALUES (10000000000000000);

3287

3117

INSERT INTO t1 VALUES (1);

3288

CREATE TABLE t2 (i BIGINT UNSIGNED);

3289

INSERT INTO t2 VALUES (10000000000000000000);

3118

CREATE TABLE t2 (i BIGINT);

3119

INSERT INTO t2 VALUES (10000000000000000);

3290

3120

INSERT INTO t2 VALUES (1);

3291

3121

/* simple test */

3292

3122

SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;

3293

3123

i

3294

10000000000000000000

3124

10000000000000000

3295

3125

1

3296

3126

/* subquery test */

3297

3127

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3298

3128

i

3299

10000000000000000000

3129

10000000000000000

3300

3130

/* subquery test with cast*/

3301

SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);

3131

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

3302

3132

i

3303

10000000000000000000

3133

10000000000000000

3304

3134

DROP TABLE t1;

3305

3135

DROP TABLE t2;

3306

3136

CREATE TABLE t1 (

3307

id bigint(20) unsigned NOT NULL auto_increment,

3137

id bigint NOT NULL auto_increment,

3308

3138

name varchar(255) NOT NULL,

3309

3139

PRIMARY KEY (id)

3310

3140

);

3311

3141

INSERT INTO t1 VALUES

3312

3142

(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

3313

3143

CREATE TABLE t2 (

3314

id bigint(20) unsigned NOT NULL auto_increment,

3315

mid bigint(20) unsigned NOT NULL,

3144

id bigint NOT NULL auto_increment,

3145

mid bigint NOT NULL,

3316

3146

date date NOT NULL,

3317

3147

PRIMARY KEY (id)

3318

3148

);

3349

3179

3 Frank 2006-04-13 NULL

3350

3180

DROP TABLE t1,t2;

3351

3181

CREATE TABLE t1 (

3352

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

3353

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

3182

i1 int NOT NULL default '0',

3183

i2 int NOT NULL default '0',

3354

3184

t datetime NOT NULL default '0000-00-00 00:00:00',

3355

3185

PRIMARY KEY (i1,i2,t)

3356

3186

);

3362

3192

(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),

3363

3193

(24,2,'2005-05-27 12:40:06');

3364

3194

CREATE TABLE t2 (

3365

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

3366

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

3195

i1 int NOT NULL default '0',

3196

i2 int NOT NULL default '0',

3367

3197

t datetime default NULL,

3368

3198

PRIMARY KEY (i1)

3369

3199

);

3374

3204

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3375

3205

ORDER BY t1.t DESC LIMIT 1);

3376

3206

id select_type table type possible_keys key key_len ref rows Extra

3377

1 PRIMARY t2 system NULL NULL NULL NULL 1

3378

1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index

3379

2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index

3207

1 PRIMARY t2 ALL NULL NULL NULL NULL 1

3208

1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index; Using join buffer

3209

2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 5 Using where; Using index; Using filesort

3380

3210

SELECT * FROM t1,t2

3381

3211

WHERE t1.t = (SELECT t1.t FROM t1

3382

3212

WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1

3387

3217

CREATE TABLE t1 (i INT);

3388

3218

(SELECT i FROM t1) UNION (SELECT i FROM t1);

3389

3219

i

3390

SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS

3220

SELECT * FROM t1 WHERE NOT EXISTS

3391

3221

(

3392

3222

(SELECT i FROM t1) UNION

3393

3223

(SELECT i FROM t1)

3395

3225

i

3396

3226

SELECT * FROM t1

3397

3227

WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));

3398

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1

3228

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

3399

3229

explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))

3400

3230

from t1;

3401

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))

3231

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

3402

3232

from t1' at line 1

3403

3233

explain select * from t1 where not exists

3404

3234

((select t11.i from t1 t11) union (select t12.i from t1 t12));

3405

3235

id select_type table type possible_keys key key_len ref rows Extra

3406

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

3407

2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table

3408

3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table

3236

1 PRIMARY t1 ALL NULL NULL NULL NULL 1

3237

2 SUBQUERY t11 ALL NULL NULL NULL NULL 1

3238

3 UNION t12 ALL NULL NULL NULL NULL 1

3409

3239

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

3410

3240

DROP TABLE t1;

3411

CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));

3412

insert into t1 (a) values (FLOOR(rand() * 100));

3413

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3414

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3415

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3416

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3417

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3418

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3419

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3420

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3421

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3422

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3423

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3424

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3425

insert into t1 (a) select FLOOR(rand() * 100) from t1;

3426

SELECT a,

3427

(SELECT REPEAT(' ',250) FROM t1 i1

3428

WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a

3429

FROM t1 ORDER BY a LIMIT 5;

3430

a a

3431

0 NULL

3432

0 NULL

3433

0 NULL

3434

0 NULL

3435

0 NULL

3436

DROP TABLE t1;

3437

3241

CREATE TABLE t1 (a INT, b INT);

3438

3242

CREATE TABLE t2 (a INT);

3439

3243

INSERT INTO t2 values (1);

3454

3258

2 2

3455

3259

1 1

3456

3260

1 1

3457

SELECT COUNT(DISTINCT t1.b),

3458

(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)

3459

FROM t1 GROUP BY t1.a;

3460

COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)

3461

2 2

3462

1 1

3463

1 1

3464

SELECT (

3465

SELECT (

3466

SELECT COUNT(DISTINCT t1.b)

3467

)

3468

)

3469

FROM t1 GROUP BY t1.a;

3470

(

3471

SELECT (

3472

SELECT COUNT(DISTINCT t1.b)

3473

)

3474

)

3475

2

3476

1

3477

1

3478

3261

SELECT (

3479

3262

SELECT (

3480

3263

SELECT (

3501

3284

SET SESSION sort_buffer_size = 32 * 1024;

3502

3285

Warnings:

3503

3286

Warning 1292 Truncated incorrect sort_buffer_size value: '32768'

3504

SELECT SQL_NO_CACHE COUNT(*)

3287

SELECT COUNT(*)

3505

3288

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3506

3289

FROM t1) t;

3507

3290

COUNT(*)

3508

3291

3000

3509

3292

SET SESSION sort_buffer_size = 8 * 1024 * 1024;

3510

SELECT SQL_NO_CACHE COUNT(*)

3293

SELECT COUNT(*)

3511

3294

FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c

3512

3295

FROM t1) t;

3513

3296

COUNT(*)

3514

3297

3000

3515

DROP TABLE t1,t2;

3298

DROP TABLE t2,t1;

3516

3299

CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);

3517

3300

CREATE TABLE t2 (c int);

3518

3301

INSERT INTO t1 VALUES ('aa', 1);

3550

3333

DROP TABLE IF EXISTS t2;

3551

3334

DROP TABLE IF EXISTS t1xt2;

3552

3335

CREATE TABLE t1 (

3553

id_1 int(5) NOT NULL,

3336

id_1 int NOT NULL,

3554

3337

t varchar(4) DEFAULT NULL

3555

3338

);

3556

3339

CREATE TABLE t2 (

3557

id_2 int(5) NOT NULL,

3340

id_2 int NOT NULL,

3558

3341

t varchar(4) DEFAULT NULL

3559

3342

);

3560

3343

CREATE TABLE t1xt2 (

3561

id_1 int(5) NOT NULL,

3562

id_2 int(5) NOT NULL

3344

id_1 int NOT NULL,

3345

id_2 int NOT NULL

3563

3346

);

3564

3347

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

3565

3348

INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');

3789

3572

ERROR HY000: Invalid use of group function

3790

3573

SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;

3791

3574

ERROR HY000: Invalid use of group function

3792

SELECT COUNT(1) FROM DUAL;

3793

COUNT(1)

3794

1

3795

SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;

3796

ERROR HY000: Invalid use of group function

3797

3575

SELECT

3798

3576

SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )

3799

3577

FROM t1;

3848

3626

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00

3849

3627

2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort

3850

3628

Warnings:

3851

Note 1003 select "res"."count(*)" AS "count(*)" from (select count(0) AS "count(*)" from "test"."t1" group by "test"."t1"."a") "res"

3629

Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`

3852

3630

DROP TABLE t1;

3853

3631

CREATE TABLE t1 (

3854

3632

a varchar(255) default NULL,

3855

3633

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3856

3634

INDEX idx(a,b)

3857

3635

);

3636

Warnings:

3637

Warning 1071 Specified key was too long; max key length is 767 bytes

3858

3638

CREATE TABLE t2 (

3859

3639

a varchar(255) default NULL

3860

3640

);

3922

3702

0

3923

3703

0

3924

3704

DROP TABLE t1, t2;

3925

CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));

3926

INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);

3927

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);

3928

s1 s2

3929

SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

3930

s1 s2

3931

CREATE INDEX I1 ON t1 (s1);

3932

CREATE INDEX I2 ON t1 (s2);

3933

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);

3934

s1 s2

3935

SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

3936

s1 s2

3937

TRUNCATE t1;

3938

INSERT INTO t1 VALUES (0x41,0x41);

3939

SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);

3940

s1 s2

3941

DROP TABLE t1;

3942

CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));

3943

CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));

3944

CREATE TABLE t3 (a3 BINARY(2) default '0');

3945

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

3946

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

3947

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

3948

SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;

3949

LEFT(t2.a2, 1)

3950

1

3951

2

3952

3

3953

SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;

3954

a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)

3955

1 0

3956

2 0

3957

3 0

3958

4 0

3959

DROP TABLE t1,t2,t3;

3960

CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));

3961

CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);

3962

CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);

3963

INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);

3964

INSERT INTO t2 VALUES (2), (3), (4), (5);

3965

INSERT INTO t3 VALUES (10), (20), (30);

3966

SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;

3967

LEFT(t1.a1,1)

3968

1

3969

2

3970

3

3971

SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);

3972

a2

3973

DROP TABLE t1, t2, t3;

3974

3705

CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));

3975

3706

INSERT INTO t1 VALUES ('a', 'aa');

3976

3707

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

3980

3711

CREATE INDEX I2 ON t1 (b);

3981

3712

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3982

3713

id select_type table type possible_keys key key_len ref rows Extra

3983

1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan

3984

1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition

3714

1 PRIMARY t1 index I1 I1 7 NULL 2 Using index; LooseScan

3715

1 PRIMARY t1 ref I2 I2 43 test.t1.a 1 Using where

3985

3716

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3986

3717

a b

3987

3718

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));

3990

3721

CREATE INDEX I2 ON t2 (b);

3991

3722

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

3992

3723

id select_type table type possible_keys key key_len ref rows Extra

3993

1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan

3994

1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition

3724

1 PRIMARY t2 index I1 I1 7 NULL 2 Using index; LooseScan

3725

1 PRIMARY t2 ref I2 I2 43 test.t2.a 1 Using where

3995

3726

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

3996

3727

a b

3997

3728

EXPLAIN

3998

3729

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

3999

3730

id select_type table type possible_keys key key_len ref rows Extra

4000

1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan

4001

1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition

3731

1 PRIMARY t1 index I1 I1 7 NULL 2 Using where; Using index; LooseScan

3732

1 PRIMARY t1 ref I2 I2 43 test.t1.a 1 Using where

4002

3733

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4003

3734

a b

4004

3735

DROP TABLE t1,t2;

4042

3773

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

4043

3774

Warnings:

4044

3775

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

4045

Note 1003 select 2 AS "2" from "test"."t1" where exists(select 1 AS "1" from "test"."t2" where ("test"."t1"."a" = "test"."t2"."a"))

3776

Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))

4046

3777

EXPLAIN EXTENDED

4047

3778

SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION

4048

3779

(SELECT 1 FROM t2 WHERE t1.a = t2.a));

4054

3785

Warnings:

4055

3786

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

4056

3787

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

4057

Note 1003 select 2 AS "2" from "test"."t1" where exists((select 1 AS "1" from "test"."t2" where ("test"."t1"."a" = "test"."t2"."a")) union (select 1 AS "1" from "test"."t2" where ("test"."t1"."a" = "test"."t2"."a")))

3788

Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))

4058

3789

DROP TABLE t1,t2;

4059

3790

CREATE TABLE t4 (

4060

3791

f7 varchar(32) collate utf8_bin NOT NULL default '',

4313

4044

CREATE TABLE t2( a INT, b INT );

4314

4045

SELECT *

4315

4046

FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;

4316

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2

4047

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

4317

4048

SELECT *

4318

4049

FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;

4319

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2

4050

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

4320

4051

SELECT *

4321

4052

FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;

4322

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2

4053

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

4323

4054

SELECT * FROM (

4324

4055

SELECT 1 a

4325

4056

UNION

4326

4057

SELECT a INTO @var FROM t1 WHERE a = 2

4327

4058

) t1a;

4328

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2

4059

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

4329

4060

) t1a' at line 2

4330

4061

SELECT * FROM (

4331

4062

SELECT 1 a

4332

4063

UNION

4333

4064

SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2

4334

4065

) t1a;

4335

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2

4066

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

4336

4067

) t1a' at line 2

4337

4068

SELECT * FROM (

4338

4069

SELECT 1 a

4339

4070

UNION

4340

4071

SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4341

4072

) t1a;

4342

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2

4073

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

4343

4074

) t1a' at line 2

4344

4075

SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;

4345

4076

a

4362

4093

1

4363

4094

2

4364

4095

SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);

4365

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

4096

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

4366

4097

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;

4367

4098

a

4368

4099

1

4370

4101

1

4371

4102

1

4372

4103

SELECT * FROM ((SELECT 1 a INTO @a)) t1a;

4373

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1

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 @a)) t1a' at line 1

4374

4105

SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;

4375

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1

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 OUTFILE 'file' )) t1a' at line 1

4376

4107

SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4377

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1

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

4378

4109

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;

4379

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1

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 @a)) t1a' at line 1

4380

4111

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;

4381

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1

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 DUMPFILE 'file' )) t1a' at line 1

4382

4113

SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;

4383

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1

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 OUTFILE 'file' )) t1a' at line 1

4384

4115

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;

4385

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a))) t1a' at line 1

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 @a))) t1a' at line 1

4386

4117

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;

4387

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1

4118

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

4388

4119

SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;

4389

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1

4120

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

4390

4121

SELECT * FROM (SELECT 1 a ORDER BY a) t1a;

4391

4122

a

4392

4123

1

4404

4135

1 1

4405

4136

2 1

4406

4137

SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;

4407

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

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

4408

4139

SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;

4409

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

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 'ON 1' at line 1

4410

4141

SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;

4411

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

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 ') ON 1' at line 1

4412

4143

SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;

4413

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a ON 1' at line 1

4144

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

4414

4145

SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;

4415

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a ON 1' at line 1

4146

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

4416

4147

SELECT * FROM t1 JOIN (t1 t1a) ON 1;

4417

4148

a a

4418

4149

1 1

4450

4181

1 1

4451

4182

2 1

4452

4183

SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;

4453

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

4184

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

4454

4185

SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );

4455

4186

a

4456

4187

1

4461

4192

a

4462

4193

1

4463

4194

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);

4464

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1

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 @a)' at line 1

4465

4196

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4466

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4197

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

4467

4198

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4468

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4199

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

4469

4200

SELECT * FROM t1 WHERE a = ( SELECT 1 );

4470

4201

a

4471

4202

1

4473

4204

a

4474

4205

1

4475

4206

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);

4476

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1

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 @a)' at line 1

4477

4208

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );

4478

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

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 OUTFILE 'file' )' at line 1

4479

4210

SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );

4480

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

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 DUMPFILE 'file' )' at line 1

4481

4212

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);

4482

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1

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 @a)' at line 1

4483

4214

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4484

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

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 OUTFILE 'file' )' at line 1

4485

4216

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4486

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

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 DUMPFILE 'file' )' at line 1

4487

4218

SELECT ( SELECT 1 INTO @v );

4488

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

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 @v )' at line 1

4489

4220

SELECT ( SELECT 1 INTO OUTFILE 'file' );

4490

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

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 OUTFILE 'file' )' at line 1

4491

4222

SELECT ( SELECT 1 INTO DUMPFILE 'file' );

4492

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

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 DUMPFILE 'file' )' at line 1

4493

4224

SELECT ( SELECT 1 UNION SELECT 1 INTO @v );

4494

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

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 @v )' at line 1

4495

4226

SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );

4496

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1

4227

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

4497

4228

SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );

4498

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1

4229

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

4499

4230

SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;

4500

4231

( SELECT a FROM t1 WHERE a = 1 ) a

4501

4232

1 1

4507

4238

SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);

4508

4239

a b

4509

4240

SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );

4510

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

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

4511

4242

( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

4512

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

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 ) UNION SELECT 1' at line 1

4513

4244

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

4514

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

4245

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

4246

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

4516

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1' at line 1

4247

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

4517

4248

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

4518

4249

( SELECT 1 UNION SELECT 1 UNION SELECT 1 )

4519

4250

1

4521

4252

((SELECT 1 UNION SELECT 1 UNION SELECT 1))

4522

4253

1

4523

4254

SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4524

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

4255

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

4525

4256

SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4526

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

4257

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

4527

4258

SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;

4528

4259

1

4529

4260

1

4530

4261

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4531

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

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

4532

4263

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

4533

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

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

4534

4265

SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4535

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

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

4536

4267

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );

4537

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

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 ) )' at line 1

4538

4269

SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4539

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

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 )' at line 1

4540

4271

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

4541

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

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 ) UNION SELECT 1 )' at line 1

4542

4273

SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4543

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

4274

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

4544

4275

SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

4545

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

4276

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

4546

4277

SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );

4547

4278

a

4548

4279

1

4556

4287

a

4557

4288

1

4558

4289

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );

4559

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4290

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

4560

4291

SELECT EXISTS(SELECT 1+1);

4561

4292

EXISTS(SELECT 1+1)

4562

4293

1

4563

4294

SELECT EXISTS(SELECT 1+1 INTO @test);

4564

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @test)' at line 1

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 @test)' at line 1

4565

4296

SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );

4566

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

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

4567

4298

SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );

4568

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4299

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

4569

4300

SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );

4570

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1

4301

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

4302

DROP TABLE t1, t2;

4572

4303

set optimizer_switch='';

4304

ERROR 42000: Variable 'optimizer_switch' can't be set to the value of ''

4305

set optimizer_switch=0;

4573

4306

show variables like 'optimizer_switch';

4574

4307

Variable_name Value

4575

4308

optimizer_switch