~drizzle-trunk/drizzle/development : revision 352.2.1

7

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

8

Warnings:

9

Note 1249 Select 2 was reduced during optimization

10

Note 1003 select 2 AS "(select 2)"

10

Note 1003 select 2 AS `(select 2)`

11

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

12

(SELECT 1)

13

1

20

Warnings:

21

Note 1249 Select 2 was reduced during optimization

22

Note 1249 Select 4 was reduced during optimization

23

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

23

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

24

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

25

(SELECT (SELECT 0 UNION SELECT 0))

26

0

32

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

33

Warnings:

34

Note 1249 Select 2 was reduced during optimization

35

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

35

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

36

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

37

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

38

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;

50

Warnings:

51

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

52

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

53

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

53

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

54

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

55

1

56

1

183

4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

184

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

185

Warnings:

186

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

186

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

187

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

188

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

189

3 1

199

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

200

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

201

Warnings:

202

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

202

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

203

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

204

a

205

2

220

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

221

Warnings:

222

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

223

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

223

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

224

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

225

a

226

7

266

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

267

2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00

268

Warnings:

269

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

269

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

270

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

271

a

272

7

310

Warnings:

311

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

312

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

313

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

313

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

314

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

315

ERROR 21000: Subquery returns more than 1 row

316

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

328

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

329

Warnings:

330

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

331

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

331

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

332

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

333

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

334

drop table t1,t2,t3;

363

2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00

364

3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

365

Warnings:

366

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

366

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

367

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

368

t8 WHERE pseudo='joce');

369

ERROR 21000: Operand should contain 1 column(s)

389

id select_type table type possible_keys key key_len ref rows filtered Extra

390

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

391

Warnings:

392

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

392

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

393

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

394

id select_type table type possible_keys key key_len ref rows filtered Extra

395

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

396

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

397

Warnings:

398

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

398

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

399

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

400

date

401

2002-08-03

416

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

417

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

418

Warnings:

419

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

419

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

420

drop table t1;

421

CREATE TABLE `t1` (

422

`numeropost` bigint unsigned NOT NULL auto_increment,

536

id select_type table type possible_keys key key_len ref rows filtered Extra

537

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

538

Warnings:

539

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

539

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

540

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

541

id select_type table type possible_keys key key_len ref rows filtered Extra

542

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

543

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

544

Warnings:

545

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

545

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

546

drop table t1;

547

CREATE TABLE t1 (a int);

548

INSERT INTO t1 VALUES (1);

718

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

719

Warnings:

720

Note 1249 Select 2 was reduced during optimization

721

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

721

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

722

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

723

id

724

1

731

Warnings:

732

Note 1249 Select 3 was reduced during optimization

733

Note 1249 Select 2 was reduced during optimization

734

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

734

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

735

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

736

id select_type table type possible_keys key key_len ref rows filtered Extra

737

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

739

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

740

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

741

Warnings:

742

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

742

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

743

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

744

id

745

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

865

Warnings:

866

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

867

Note 1249 Select 2 was reduced during optimization

868

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

868

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

869

select (select a+1) from t1;

870

(select a+1)

871

2.5

887

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

888

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

889

Warnings:

890

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

890

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

891

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

892

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

893

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

902

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

903

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

904

Warnings:

905

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

905

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

906

drop table t1,t2,t3;

907

create table t1 (a float);

908

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

968

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

969

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

970

Warnings:

971

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

971

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

972

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

973

id select_type table type possible_keys key key_len ref rows filtered Extra

974

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

975

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

976

Warnings:

977

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

977

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

978

drop table t1;

979

CREATE TABLE `t1` (

980

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

1023

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

1024

SHOW CREATE TABLE t1;

1025

Table Create Table

1026

t1 CREATE TABLE "t1" (

1027

"a" int NOT NULL,

1028

"(SELECT 1)" int NOT NULL

1026

t1 CREATE TABLE `t1` (

1027

`a` int NOT NULL,

1028

`(SELECT 1)` int NOT NULL

1029

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1030

drop table t1;

1031

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

1032

SHOW CREATE TABLE t1;

1033

Table Create Table

1034

t1 CREATE TABLE "t1" (

1035

"a" int NOT NULL,

1036

"(SELECT a)" int NOT NULL

1034

t1 CREATE TABLE `t1` (

1035

`a` int NOT NULL,

1036

`(SELECT a)` int NOT NULL

1037

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1038

drop table t1;

1039

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

1040

SHOW CREATE TABLE t1;

1041

Table Create Table

1042

t1 CREATE TABLE "t1" (

1043

"a" int NOT NULL,

1044

"(SELECT a+0)" int NOT NULL

1042

t1 CREATE TABLE `t1` (

1043

`a` int NOT NULL,

1044

`(SELECT a+0)` int NOT NULL

1045

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1046

drop table t1;

1047

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

1050

2

1051

SHOW CREATE TABLE t1;

1052

Table Create Table

1053

t1 CREATE TABLE "t1" (

1054

"a" bigint NOT NULL

1053

t1 CREATE TABLE `t1` (

1054

`a` bigint NOT NULL

1055

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1056

drop table t1;

1057

create table t1 (a int);

1063

2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1064

3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1065

Warnings:

1066

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"

1066

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`

1067

drop table t1;

1068

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

1069

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

1117

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

1118

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

1119

Warnings:

1120

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

1120

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

1121

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

1122

SELECT 0 IN (SELECT 1 FROM t1 a);

1123

0 IN (SELECT 1 FROM t1 a)

1127

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

1128

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

1129

Warnings:

1130

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

1130

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

1131

drop table t1;

1132

CREATE TABLE `t1` (

1133

`i` int NOT NULL default '0',

1172

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

1173

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

1174

Warnings:

1175

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

1175

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

1176

drop table t1;

1177

CREATE TABLE t1 (

1178

ID int unsigned NOT NULL auto_increment,

1234

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

1235

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

1236

Warnings:

1237

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

1237

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

1238

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

1239

a

1240

2

1244

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

1245

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

1246

Warnings:

1247

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

1247

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

1248

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

1249

a

1250

2

1255

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

1256

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

1257

Warnings:

1258

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

1258

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

1259

drop table t1, t2, t3;

1260

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

1261

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

1273

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

1274

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

1275

Warnings:

1276

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

1276

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

1277

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

1278

a

1279

2

1283

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

1284

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

1285

Warnings:

1286

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

1286

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

1287

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

1288

a

1289

2

1294

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

1295

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

1296

Warnings:

1297

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

1297

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

1298

insert into t1 values (3,31);

1299

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

1300

a

1310

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

1311

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

1312

Warnings:

1313

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

1313

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

1314

drop table t1, t2, t3;

1315

create table t1 (a int, b int);

1316

create table t2 (a int, b int);

1367

id select_type table type possible_keys key key_len ref rows filtered Extra

1368

1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00

1369

Warnings:

1370

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

1370

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

1371

(select * from t1);

1372

s1

1373

tttt

1401

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

1402

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

1403

Warnings:

1404

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"

1404

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`

1405

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

1406

id select_type table type possible_keys key key_len ref rows filtered Extra

1407

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

1408

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

1409

Warnings:

1410

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"

1410

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`

1411

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

1412

id select_type table type possible_keys key key_len ref rows filtered Extra

1413

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

1414

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

1415

Warnings:

1416

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"

1416

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`

1417

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

1418

id select_type table type possible_keys key key_len ref rows filtered Extra

1419

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

1420

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

1421

Warnings:

1422

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"

1422

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`

1423

drop table t1,t2;

1424

create table t2 (a int, b int);

1425

create table t3 (a int);

1434

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

1435

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1436

Warnings:

1437

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

1437

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

1438

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

1439

a

1440

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

1442

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

1443

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1444

Warnings:

1445

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

1445

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

1446

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

1447

a

1448

6

1453

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

1454

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

1455

Warnings:

1456

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

1456

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

1457

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

1458

a

1459

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

1461

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

1462

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

1463

Warnings:

1464

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

1464

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

1465

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

1466

a

1467

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

1469

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

1470

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1471

Warnings:

1472

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

1472

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

1473

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

1474

a

1475

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

1477

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

1478

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

1479

Warnings:

1480

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

1480

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

1481

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

1482

a

1483

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

1485

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

1486

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1487

Warnings:

1488

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

1488

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

1489

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

1490

a

1491

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

1493

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

1494

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

1495

Warnings:

1496

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

1496

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

1497

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

1498

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

1499

a

1504

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

1505

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

1506

Warnings:

1507

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

1507

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

1508

drop table t2, t3;

1509

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 CHARSET=latin1 AUTO_INCREMENT=3 ;

1510

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

1546

3 UNION t1 ALL NULL NULL NULL NULL 1 100.00

1547

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

1548

Warnings:

1549

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

1549

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

1550

drop table t1;

1551

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

1552

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

1656

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

1657

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

1658

Warnings:

1659

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

1659

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

1660

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

1661

id select_type table type possible_keys key key_len ref rows filtered Extra

1662

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

1663

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

1664

Warnings:

1665

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

1666

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

1666

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

1667

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

1668

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

1669

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

1689

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

1690

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

1691

Warnings:

1692

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

1692

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

1693

drop table t1,t2;

1694

create table t1 (a int);

1695

insert into t1 values (1);

2199

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

2200

Warnings:

2201

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

2202

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

2202

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

2203

drop table t1;

2204

CREATE TABLE t1 (t1_a int);

2205

INSERT INTO t1 VALUES (1);

2623

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2624

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

2625

Warnings:

2626

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"

2626

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`

2627

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

2628

id select_type table type possible_keys key key_len ref rows filtered Extra

2629

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

2630

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

2631

Warnings:

2632

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

2632

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

2633

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;

2634

id select_type table type possible_keys key key_len ref rows filtered Extra

2635

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2636

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

2637

Warnings:

2638

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"

2638

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`

2639

DROP TABLE t1,t2;

2640

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

2641

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

2811

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

2812

SHOW CREATE TABLE t2;

2813

Table Create Table

2814

t2 CREATE TABLE "t2" (

2815

"sub_a" datetime

2814

t2 CREATE TABLE `t2` (

2815

`sub_a` datetime

2816

) ENGINE=InnoDB DEFAULT CHARSET=latin1

2817

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

2818

SHOW CREATE TABLE t3;

2819

Table Create Table

2820

t3 CREATE TABLE "t3" (

2821

"a" datetime

2820

t3 CREATE TABLE `t3` (

2821

`a` datetime

2822

) ENGINE=InnoDB DEFAULT CHARSET=latin1

2823

DROP TABLE t1,t2,t3;

2824

CREATE TABLE t1 (a int);

3708

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

3709

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

3710

Warnings:

3711

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

3711

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

3712

DROP TABLE t1;

3713

CREATE TABLE t1 (

3714

a varchar(255) default NULL,

3853

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

3854

Warnings:

3855

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

3856

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

3856

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

3857

EXPLAIN EXTENDED

3858

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

3859

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

3865

Warnings:

3866

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

3867

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

3868

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

3868

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

3869

DROP TABLE t1,t2;

3870

CREATE TABLE t4 (

3871

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