~drizzle-trunk/drizzle/development

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

205

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

226

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

272

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;

352

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

353

PRIMARY KEY (`pseudo`),

354

UNIQUE KEY `email` (`email`)

355

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

355

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

356

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

357

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

358

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

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

708

drop table t1, t2, t3;

709

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

710

ERROR HY000: No tables used

711

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

711

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

712

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

713

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

714

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

724

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

745

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

753

754

755

756

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

756

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

757

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

758

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

759

ERROR 21000: Subquery returns more than 1 row

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

909

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

910

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

911

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

912

drop table t1;

913

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

914

create table t2 (a int);

915

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

916

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

917

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;

918

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

919

1 1 a

920

2 0 b

921

NULL 0 NULL

922

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;

923

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

924

1 0 a

925

2 1 b

926

NULL NULL NULL

927

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;

928

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

929

1 0 a

930

2 0 b

931

NULL 0 NULL

932

drop table t1,t2;

933

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

934

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

935

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

936

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

937

938

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

939

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

940

941

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

942

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

943

944

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

945

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

946

947

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

948

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

949

950

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

951

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

952

953

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

954

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

955

956

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

957

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

958

959

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

960

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

961

962

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

963

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

964

drop table t1;

965

907

CREATE TABLE t1 (a int);

966

908

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

967

909

id select_type table type possible_keys key key_len ref rows filtered Extra

968

910

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

969

911

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

970

912

Warnings:

971

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

913

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

972

914

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

973

915

id select_type table type possible_keys key key_len ref rows filtered Extra

974

916

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

975

917

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

976

918

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"

919

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

920

drop table t1;

979

921

CREATE TABLE `t1` (

980

922

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

984

926

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

985

927

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

986

928

KEY `topic` (`topic`)

987

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

929

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

988

930

CREATE TABLE `t2` (

989

931

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

990

932

`topic` bigint unsigned NOT NULL default '0',

993

935

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

994

936

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

995

937

KEY `topic` (`topic`)

996

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

938

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

997

939

CREATE TABLE `t3` (

998

940

`numeropost` bigint unsigned NOT NULL auto_increment,

999

941

`maxnumrep` int unsigned NOT NULL default '0',

1000

942

PRIMARY KEY (`numeropost`),

1001

943

UNIQUE KEY `maxnumrep` (`maxnumrep`)

1002

) ENGINE=MyISAM CHARSET=latin1;

944

) ENGINE=MyISAM;

1003

945

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

1004

946

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

1005

947

INSERT INTO t3 VALUES (1,1);

1023

965

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

1024

966

SHOW CREATE TABLE t1;

1025

967

Table Create Table

1026

t1 CREATE TABLE "t1" (

1027

"a" int NOT NULL,

1028

"(SELECT 1)" int NOT NULL

1029

) ENGINE=InnoDB DEFAULT CHARSET=latin1

968

t1 CREATE TABLE `t1` (

969

`a` int NOT NULL,

970

`(SELECT 1)` int NOT NULL

971

) ENGINE=InnoDB

1030

972

drop table t1;

1031

973

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

1032

974

SHOW CREATE TABLE t1;

1033

975

Table Create Table

1034

t1 CREATE TABLE "t1" (

1035

"a" int NOT NULL,

1036

"(SELECT a)" int NOT NULL

1037

) ENGINE=InnoDB DEFAULT CHARSET=latin1

976

t1 CREATE TABLE `t1` (

977

`a` int NOT NULL,

978

`(SELECT a)` int NOT NULL

979

) ENGINE=InnoDB

1038

980

drop table t1;

1039

981

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

1040

982

SHOW CREATE TABLE t1;

1041

983

Table Create Table

1042

t1 CREATE TABLE "t1" (

1043

"a" int NOT NULL,

1044

"(SELECT a+0)" int NOT NULL

1045

) ENGINE=InnoDB DEFAULT CHARSET=latin1

984

t1 CREATE TABLE `t1` (

985

`a` int NOT NULL,

986

`(SELECT a+0)` int NOT NULL

987

) ENGINE=InnoDB

1046

988

drop table t1;

1047

989

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

1048

990

select * from t1;

1050

992

1051

993

SHOW CREATE TABLE t1;

1052

994

Table Create Table

1053

t1 CREATE TABLE "t1" (

1054

"a" bigint NOT NULL

1055

) ENGINE=InnoDB DEFAULT CHARSET=latin1

995

t1 CREATE TABLE `t1` (

996

`a` bigint NOT NULL

997

) ENGINE=InnoDB

1056

998

drop table t1;

1057

999

create table t1 (a int);

1058

1000

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

1063

1005

2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1064

1006

3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00

1065

1007

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"

1008

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

1009

drop table t1;

1068

1010

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

1011

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

1117

1059

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

1118

1060

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

1119

1061

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

1062

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

1063

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

1122

1064

SELECT 0 IN (SELECT 1 FROM t1 a);

1123

1065

0 IN (SELECT 1 FROM t1 a)

1127

1069

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

1128

1070

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

1129

1071

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

1072

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

1073

drop table t1;

1132

1074

CREATE TABLE `t1` (

1133

1075

`i` int NOT NULL default '0',

1134

1076

PRIMARY KEY (`i`)

1135

) ENGINE=MyISAM CHARSET=latin1;

1077

) ENGINE=MyISAM;

1136

1078

INSERT INTO t1 VALUES (1);

1137

1079

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

1138

1080

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

1144

1086

drop table t1;

1145

1087

CREATE TABLE t1 (

1146

1088

id int default NULL

1147

) ENGINE=MyISAM CHARSET=latin1;

1089

) ENGINE=MyISAM;

1148

1090

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

1149

1091

CREATE TABLE t2 (

1150

1092

id int default NULL,

1151

1093

name varchar(15) default NULL

1152

) ENGINE=MyISAM CHARSET=latin1;

1094

) ENGINE=MyISAM;

1153

1095

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

1154

1096

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

1155

1097

select * from t2;

1172

1114

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

1173

1115

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

1174

1116

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

1117

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

1118

drop table t1;

1177

1119

CREATE TABLE t1 (

1178

1120

ID int unsigned NOT NULL auto_increment,

1183

1125

UNIQUE KEY t1_PK (ID,SUB_ID),

1184

1126

KEY t1_FK (REF_ID,REF_SUB),

1185

1127

KEY t1_REFID (REF_ID)

1186

) ENGINE=MyISAM CHARSET=cp1251;

1128

) ENGINE=MyISAM;

1187

1129

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

1188

1130

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

1189

1131

REF_ID

1208

1150

PRIMARY KEY (`id`),

1209

1151

UNIQUE KEY `email` (`email`),

1210

1152

UNIQUE KEY `pseudo` (`pseudo`)

1211

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

1153

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

1212

1154

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

1213

1155

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

1214

1156

a b

1234

1176

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

1235

1177

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

1236

1178

Warnings:

1237

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

1179

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

1238

1180

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

1239

1181

1240

1182

1244

1186

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

1245

1187

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

1246

1188

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

1189

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

1190

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

1249

1191

1250

1192

1255

1197

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

1256

1198

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

1257

1199

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

1200

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

1201

drop table t1, t2, t3;

1260

1202

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

1261

1203

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

1273

1215

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

1274

1216

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

1275

1217

Warnings:

1276

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

1218

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

1277

1219

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

1278

1220

1279

1221

1283

1225

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

1284

1226

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

1285

1227

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

1228

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

1229

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

1288

1230

1289

1231

1294

1236

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

1295

1237

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

1296

1238

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

1239

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

1240

insert into t1 values (3,31);

1299

1241

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

1300

1242

1310

1252

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

1311

1253

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

1312

1254

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

1255

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

1256

drop table t1, t2, t3;

1315

1257

create table t1 (a int, b int);

1316

1258

create table t2 (a int, b int);

1339

1281

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

1340

1282

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

1341

1283

DROP TABLE t1, t2;

1342

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

1343

s2 CHAR(5) COLLATE latin1_swedish_ci);

1344

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

1345

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

1346

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

1347

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

1348

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

1349

drop table t1;

1350

1284

create table t1(toid int,rd int);

1351

1285

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

1352

1286

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

1367

1301

id select_type table type possible_keys key key_len ref rows filtered Extra

1368

1302

1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00

1369

1303

Warnings:

1370

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

1304

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

1371

1305

(select * from t1);

1372

1306

1373

1307

tttt

1398

1332

a3 1

1399

1333

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

1400

1334

id select_type table type possible_keys key key_len ref rows filtered Extra

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

1335

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

1336

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

1403

1337

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"

1338

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

1339

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

1406

1340

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

1341

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

1342

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

1409

1343

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"

1344

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

1345

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

1412

1346

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

1347

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

1348

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

1415

1349

Warnings:

1416

1350

1417

1351

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

1418

1352

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

1353

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

1354

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

1421

1355

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"

1356

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

1357

drop table t1,t2;

1424

1358

create table t2 (a int, b int);

1425

1359

create table t3 (a int);

1434

1368

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

1435

1369

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1436

1370

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

1371

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

1372

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

1439

1373

1440

1374

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

1442

1376

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

1443

1377

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1444

1378

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

1379

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

1380

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

1447

1381

1448

1382

1453

1387

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

1454

1388

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

1455

1389

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

1390

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

1391

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

1458

1392

1459

1393

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

1461

1395

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

1462

1396

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

1463

1397

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

1398

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

1399

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

1466

1400

1467

1401

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

1469

1403

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

1470

1404

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1471

1405

Warnings:

1472

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

1406

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

1473

1407

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

1474

1408

1475

1409

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

1477

1411

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

1478

1412

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

1479

1413

Warnings:

1480

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

1414

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

1481

1415

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

1482

1416

1483

1417

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

1485

1419

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

1486

1420

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1487

1421

Warnings:

1488

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

1422

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

1489

1423

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

1490

1424

1491

1425

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

1493

1427

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

1494

1428

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

1495

1429

Warnings:

1496

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

1430

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

1497

1431

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

1498

1432

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

1499

1433

1504

1438

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

1505

1439

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

1506

1440

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

1441

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

1442

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 ;

1443

1510

1444

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

1511

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

1445

1512

1446

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

1513

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

1447

1514

1448

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

1515

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

1449

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

1516

1450

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

1517

1451

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;

1518

1452

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

1524

1458

-1 Valid

1525

1459

-1 Valid 2

1526

1460

drop table t1,t2,t3,t4;

1527

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

1461

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

1528

1462

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

1529

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

1463

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

1530

1464

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

1531

1465

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

1532

1466

ERROR 21000: Operand should contain 3 column(s)

1546

1480

3 UNION t1 ALL NULL NULL NULL NULL 1 100.00

1547

1481

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

1548

1482

Warnings:

1549

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

1483

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

1550

1484

drop table t1;

1551

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

1485

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

1552

1486

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

1553

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

1487

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

1554

1488

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

1555

1489

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;

1556

1490

phone code

1656

1590

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

1657

1591

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

1658

1592

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

1593

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

1594

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

1595

id select_type table type possible_keys key key_len ref rows filtered Extra

1662

1596

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

1663

1597

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

1664

1598

Warnings:

1665

1599

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

1600

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

1601

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

1668

1602

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

1669

1603

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

1623

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

1690

1624

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

1691

1625

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

1626

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

1627

drop table t1,t2;

1694

1628

create table t1 (a int);

1695

1629

insert into t1 values (1);

2199

2133

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

2200

2134

Warnings:

2201

2135

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

2136

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

2137

drop table t1;

2204

2138

CREATE TABLE t1 (t1_a int);

2205

2139

INSERT INTO t1 VALUES (1);

2220

2154

2 Rebecca 2 Spot

2221

2155

3 NULL 3 Felix

2222

2156

drop table t1,t2;

2223

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

2157

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

2224

2158

insert into t1 values (1);

2225

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

2159

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

2226

2160

insert into t2 values (1,2);

2227

2161

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

2228

2162

a C

2527

2461

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

2528

2462

ERROR 21000: Operand should contain 2 column(s)

2529

2463

drop table t1;

2530

CREATE TABLE `t1` (

2531

`itemid` bigint unsigned NOT NULL auto_increment,

2532

`sessionid` bigint unsigned default NULL,

2533

`time` int unsigned NOT NULL default '0',

2534

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

2535

NULL default '',

2536

`data` text collate latin1_general_ci NOT NULL,

2537

PRIMARY KEY (`itemid`)

2538

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2539

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

2540

CREATE TABLE `t2` (

2541

`sessionid` bigint unsigned NOT NULL auto_increment,

2542

`pid` int unsigned NOT NULL default '0',

2543

`date` int unsigned NOT NULL default '0',

2544

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

2545

PRIMARY KEY (`sessionid`)

2546

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2547

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

2548

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;

2549

ip count( e.itemid )

2550

10.10.10.1 1

2551

drop tables t1,t2;

2552

2464

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

2553

2465

insert into t1 values ('1');

2554

2466

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

2625

2537

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2626

2538

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

2627

2539

Warnings:

2628

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"

2540

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`

2629

2541

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

2630

2542

id select_type table type possible_keys key key_len ref rows filtered Extra

2631

2543

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

2632

2544

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

2633

2545

Warnings:

2634

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

2546

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

2635

2547

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;

2636

2548

id select_type table type possible_keys key key_len ref rows filtered Extra

2637

2549

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

2638

2550

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

2639

2551

Warnings:

2640

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"

2552

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`

2641

2553

DROP TABLE t1,t2;

2642

2554

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

2643

2555

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

2813

2725

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

2814

2726

SHOW CREATE TABLE t2;

2815

2727

Table Create Table

2816

t2 CREATE TABLE "t2" (

2817

"sub_a" datetime

2818

) ENGINE=InnoDB DEFAULT CHARSET=latin1

2728

t2 CREATE TABLE `t2` (

2729

`sub_a` datetime

2730

) ENGINE=InnoDB

2819

2731

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

2820

2732

SHOW CREATE TABLE t3;

2821

2733

Table Create Table

2822

t3 CREATE TABLE "t3" (

2823

"a" datetime

2824

) ENGINE=InnoDB DEFAULT CHARSET=latin1

2734

t3 CREATE TABLE `t3` (

2735

`a` datetime

2736

) ENGINE=InnoDB

2825

2737

DROP TABLE t1,t2,t3;

2826

2738

CREATE TABLE t1 (a int);

2827

2739

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

2968

2880

2 NULL

2969

2881

3 NULL

2970

2882

DROP TABLE t1,t2,t3;

2971

CREATE TABLE `t1` (

2972

`itemid` bigint unsigned NOT NULL auto_increment,

2973

`sessionid` bigint unsigned default NULL,

2974

`time` int unsigned NOT NULL default '0',

2975

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

2976

NULL default '',

2977

`data` text collate latin1_general_ci NOT NULL,

2978

PRIMARY KEY (`itemid`)

2979

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2980

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

2981

CREATE TABLE `t2` (

2982

`sessionid` bigint unsigned NOT NULL auto_increment,

2983

`pid` int unsigned NOT NULL default '0',

2984

`date` int unsigned NOT NULL default '0',

2985

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

2986

PRIMARY KEY (`sessionid`)

2987

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2988

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

2989

2990

ip count( e.itemid )

2991

10.10.10.1 1

2992

drop tables t1,t2;

2993

2883

CREATE TABLE t1 (EMPNUM CHAR(3));

2994

2884

CREATE TABLE t2 (EMPNUM CHAR(3) );

2995

2885

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

3197

3087

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

3198

3088

id select_type table type possible_keys key key_len ref rows Extra

3199

3089

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3200

2 SUBQUERY t1 index NULL a 8 NULL 9

3090

2 SUBQUERY t1 index NULL a 23 NULL 9

3201

3091

DROP TABLE t1;

3202

3092

create table t1( f1 int,f2 int);

3203

3093

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

3732

3622

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

3733

3623

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

3734

3624

Warnings:

3735

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

3625

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

3736

3626

DROP TABLE t1;

3737

3627

CREATE TABLE t1 (

3738

3628

a varchar(255) default NULL,

3739

3629

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3740

3630

INDEX idx(a,b)

3741

3631

);

3632

Warnings:

3633

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

3742

3634

CREATE TABLE t2 (

3743

3635

a varchar(255) default NULL

3744

3636

);

3815

3707

CREATE INDEX I2 ON t1 (b);

3816

3708

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

3817

3709

id select_type table type possible_keys key key_len ref rows Extra

3818

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

3819

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

3710

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

3711

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

3820

3712

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

3821

3713

a b

3822

3714

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

3825

3717

CREATE INDEX I2 ON t2 (b);

3826

3718

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

3827

3719

id select_type table type possible_keys key key_len ref rows Extra

3828

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

3829

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

3720

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

3721

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

3830

3722

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

3831

3723

a b

3832

3724

EXPLAIN

3833

3725

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

3834

3726

id select_type table type possible_keys key key_len ref rows Extra

3835

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

3836

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

3727

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

3728

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

3837

3729

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

3838

3730

a b

3839

3731

DROP TABLE t1,t2;

3877

3769

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

3878

3770

Warnings:

3879

3771

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

3880

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

3772

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

3881

3773

EXPLAIN EXTENDED

3882

3774

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

3883

3775

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

3889

3781

Warnings:

3890

3782

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

3891

3783

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

3892

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

3784

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

3893

3785

DROP TABLE t1,t2;

3894

3786

CREATE TABLE t4 (

3895

3787

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

Older »