~drizzle-trunk/drizzle/development : revision 201

304

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;

305

id select_type table type possible_keys key key_len ref rows filtered Extra

306

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00

307

2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

307

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

308

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

309

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

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

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

605

2 12

606

select * from t12;

607

a b

608

2 12

609

22 11

608

610

33 10

609

22 11

610

2 12

611

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

612

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

613

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

619

1 11

620

select * from t12;

621

a b

622

22 11

622

623

33 10

623

22 11

624

drop table t11, t12, t2;

625

CREATE TABLE t1 (x int);

626

create table t2 (a int);

703

select * from t1;

704

x y

705

1 3

706

2 1

706

707

4 2

707

2 1

708

drop table t1, t2, t3;

709

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

710

ERROR HY000: No tables used

965

CREATE TABLE t1 (a int(1));

966

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

967

id select_type table type possible_keys key key_len ref rows filtered Extra

968

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

969

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

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"

972

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

973

id select_type table type possible_keys key key_len ref rows filtered Extra

974

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

975

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

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 encrypt('test') AS "ENCRYPT('test')" from "test"."t1") AS "(SELECT ENCRYPT('test') FROM t1)" from "test"."t1"

978

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

979

id select_type table type possible_keys key key_len ref rows filtered Extra

980

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

981

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

980

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

981

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

982

Warnings:

983

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

984

drop table t1;

1032

t1 CREATE TABLE "t1" (

1033

"a" int(1) NOT NULL,

1034

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

1035

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1035

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1036

drop table t1;

1037

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

1038

SHOW CREATE TABLE t1;

1040

t1 CREATE TABLE "t1" (

1041

"a" int(1) NOT NULL,

1042

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

1043

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1043

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1044

drop table t1;

1045

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

1046

SHOW CREATE TABLE t1;

1048

t1 CREATE TABLE "t1" (

1049

"a" int(1) NOT NULL,

1050

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

1051

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1051

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1052

drop table t1;

1053

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

1054

select * from t1;

1058

Table Create Table

1059

t1 CREATE TABLE "t1" (

1060

"a" bigint(20) NOT NULL

1061

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1061

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1062

drop table t1;

1063

create table t1 (a int);

1064

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

1175

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

1176

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

1177

id select_type table type possible_keys key key_len ref rows filtered Extra

1178

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

1178

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

1179

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

1180

Warnings:

1181

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

1238

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

1239

id select_type table type possible_keys key key_len ref rows filtered Extra

1240

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

1241

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

1241

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

1242

Warnings:

1243

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

1244

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

1277

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

1278

id select_type table type possible_keys key key_len ref rows filtered Extra

1279

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

1280

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

1280

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

1281

Warnings:

1282

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

1283

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

1287

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

1288

id select_type table type possible_keys key key_len ref rows filtered Extra

1289

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

1290

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

1290

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

1291

Warnings:

1292

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

1293

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

1296

3

1297

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

1298

id select_type table type possible_keys key key_len ref rows filtered Extra

1299

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

1300

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

1301

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

1299

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

1300

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

1301

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

1302

Warnings:

1303

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

1304

insert into t1 values (3,31);

1314

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

1315

id select_type table type possible_keys key key_len ref rows filtered Extra

1316

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

1317

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

1317

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

1318

Warnings:

1319

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

1320

drop table t1, t2, t3;

1371

tttt

1372

explain extended (select * from t1);

1373

id select_type table type possible_keys key key_len ref rows filtered Extra

1374

1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00

1374

1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00

1375

Warnings:

1376

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

1376

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

1377

(select * from t1);

1378

s1

1379

tttt

1438

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

1439

id select_type table type possible_keys key key_len ref rows filtered Extra

1440

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

1441

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

1441

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1442

Warnings:

1443

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

1443

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

1444

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

1445

a

1446

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

1447

id select_type table type possible_keys key key_len ref rows filtered Extra

1448

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

1449

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

1449

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1450

Warnings:

1451

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

1451

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

1452

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

1453

a

1454

6

1457

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

1458

id select_type table type possible_keys key key_len ref rows filtered Extra

1459

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

1460

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

1460

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

1461

Warnings:

1462

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

1462

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

1463

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

1464

a

1465

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

1466

id select_type table type possible_keys key key_len ref rows filtered Extra

1467

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

1468

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

1468

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

1469

Warnings:

1470

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

1470

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

1471

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

1472

a

1473

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

1474

id select_type table type possible_keys key key_len ref rows filtered Extra

1475

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

1476

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

1476

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1477

Warnings:

1478

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

1479

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

1481

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

1482

id select_type table type possible_keys key key_len ref rows filtered Extra

1483

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

1484

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

1484

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

1485

Warnings:

1486

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

1487

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

1489

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

1490

id select_type table type possible_keys key key_len ref rows filtered Extra

1491

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

1492

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

1492

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1493

Warnings:

1494

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

1495

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

1497

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

1498

id select_type table type possible_keys key key_len ref rows filtered Extra

1499

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

1500

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

1500

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

1501

Warnings:

1502

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

1503

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

1547

e

1548

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

1549

id select_type table type possible_keys key key_len ref rows filtered Extra

1550

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00

1551

2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

1552

3 UNION t1 system NULL NULL NULL NULL 1 100.00

1550

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

1551

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

1552

3 UNION t1 ALL NULL NULL NULL NULL 1 100.00

1553

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

1554

Warnings:

1555

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

1555

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

1556

drop table t1;

1557

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

1558

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

1693

id select_type table type possible_keys key key_len ref rows filtered Extra

1694

1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00

1695

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

1696

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

1696

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

1697

Warnings:

1698

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

1699

drop table t1,t2;

1702

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

1703

id select_type table type possible_keys key key_len ref rows Extra

1704

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

1705

2 SUBQUERY t1 system NULL NULL NULL NULL 1

1705

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where

1706

drop table t1;

1707

create table t1(id int);

1708

create table t2(id int);

2740

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

2741

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

2742

id select_type table type possible_keys key key_len ref rows Extra

2743

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2743

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2744

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2745

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

2745

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

2746

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

2747

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

2748

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

2752

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

2753

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

2754

id select_type table type possible_keys key key_len ref rows Extra

2755

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2755

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2756

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2757

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

2757

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

2758

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

2759

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

2760

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

2821

Table Create Table

2822

t2 CREATE TABLE "t2" (

2823

"sub_a" datetime

2824

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2824

) ENGINE=InnoDB DEFAULT CHARSET=latin1

2825

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

2826

SHOW CREATE TABLE t3;

2827

Table Create Table

2828

t3 CREATE TABLE "t3" (

2829

"a" datetime

2830

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2830

) ENGINE=InnoDB DEFAULT CHARSET=latin1

2831

DROP TABLE t1,t2,t3;

2832

CREATE TABLE t1 (a int);

2833

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

3203

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

3204

id select_type table type possible_keys key key_len ref rows Extra

3205

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3206

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

3206

2 SUBQUERY t1 index NULL a 8 NULL 9

3207

DROP TABLE t1;

3208

create table t1( f1 int,f2 int);

3209

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

3316

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

3317

ORDER BY t1.t DESC LIMIT 1);

3318

id select_type table type possible_keys key key_len ref rows Extra

3319

1 PRIMARY t2 system NULL NULL NULL NULL 1

3320

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

3321

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

3319

1 PRIMARY t2 ALL NULL NULL NULL NULL 1

3320

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

3321

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

3322

SELECT * FROM t1,t2

3323

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

3324

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

3345

explain select * from t1 where not exists

3346

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

3347

id select_type table type possible_keys key key_len ref rows Extra

3348

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

3349

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

3350

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

3348

1 PRIMARY t1 ALL NULL NULL NULL NULL 1

3349

2 SUBQUERY t11 ALL NULL NULL NULL NULL 1

3350

3 UNION t12 ALL NULL NULL NULL NULL 1

3351

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

3352

DROP TABLE t1;

3353

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

3354

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

3355

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

3356

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

3357

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

3358

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

3359

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

3360

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

3361

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

3362

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

3363

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

3364

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

3365

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

3366

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

3367

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

3368

SELECT a,

3369

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

3370

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

3371

FROM t1 ORDER BY a LIMIT 5;

3372

a a

3373

0 NULL

3374

0 NULL

3375

0 NULL

3376

0 NULL

3377

0 NULL

3378

DROP TABLE t1;

3379

3353

CREATE TABLE t1 (a INT, b INT);

3380

3354

CREATE TABLE t2 (a INT);

3381

3355

INSERT INTO t2 values (1);

3433

3407

FROM t1) t;

3434

3408

COUNT(*)

3435

3409

3000

3436

DROP TABLE t1,t2;

3410

DROP TABLE t2,t1;

3437

3411

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

3438

3412

CREATE TABLE t2 (c int);

3439

3413

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

3897

3871

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

3898

3872

id select_type table type possible_keys key key_len ref rows Extra

3899

3873

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

3900

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

3874

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

3901

3875

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

3902

3876

a b

3903

3877

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

3907

3881

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

3908

3882

id select_type table type possible_keys key key_len ref rows Extra

3909

3883

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

3910

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

3884

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

3911

3885

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

3912

3886

a b

3913

3887

EXPLAIN

3914

3888

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

3915

3889

id select_type table type possible_keys key key_len ref rows Extra

3916

3890

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

3917

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

3891

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

3918

3892

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

3919

3893

a b

3920

3894

DROP TABLE t1,t2;