~drizzle-trunk/drizzle/development : revision 642.1.58

135

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

136

a b (SELECT b)

137

1 2 2

138

create table t1 (a int);

139

create table t2 (a int, b int);

140

create table t3 (a int);

141

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

138

create table t1 (a int) ENGINE=MyISAM;

139

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

140

create table t3 (a int) ENGINE=MyISAM;

141

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

142

insert into t1 values (2);

143

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

144

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

290

select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);

291

b ma

292

7 12

293

create table t5 (a int);

293

create table t5 (a int) ENGINE=MyISAM;

294

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

295

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

296

NULL 1

308

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

309

id select_type table type possible_keys key key_len ref rows filtered Extra

310

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00

311

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

311

2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

312

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

313

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

314

Warnings:

315

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

316

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

317

Note 1003 select (select `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`

317

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

318

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

319

ERROR 21000: Subquery returns more than 1 row

320

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

321

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

320

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

321

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

322

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

323

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

324

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

336

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

337

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

338

drop table t1,t2,t3;

339

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

339

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

340

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

341

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

341

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

342

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

343

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

343

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

344

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

345

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

346

a b

506

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

507

ERROR 21000: Subquery returns more than 1 row

508

drop table t1;

509

create table t1 (a int);

509

create table t1 (a int) ENGINE=MyISAM;

510

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

511

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

512

a

514

2

515

3

516

drop table t1;

517

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

517

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

518

INSERT INTO t1 VALUES ();

519

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

520

ERROR 21000: Subquery returns more than 1 row

548

Warnings:

549

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

550

drop table t1;

551

CREATE TABLE t1 (a int);

551

CREATE TABLE t1 (a int) ENGINE=MyISAM;

552

INSERT INTO t1 VALUES (1);

553

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

554

1

555

1

556

drop table t1;

557

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

558

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

557

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

558

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

559

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

560

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

561

select * from t1;

574

1 21

575

2 22

576

drop table t1, t2;

577

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

578

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

577

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

578

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

579

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

580

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

581

select * from t1;

596

0 10

597

1 11

598

drop table t1, t2;

599

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

600

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

601

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

599

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

600

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

601

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

602

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

603

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

604

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

626

22 11

627

33 10

628

drop table t11, t12, t2;

629

CREATE TABLE t1 (x int);

630

create table t2 (a int);

631

create table t3 (b int);

629

CREATE TABLE t1 (x int) ENGINE=MyISAM;

630

create table t2 (a int) ENGINE=MyISAM;

631

create table t3 (b int) ENGINE=MyISAM;

632

insert into t2 values (1);

633

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

634

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

674

11

675

2

676

drop table t1, t2, t3;

677

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

678

create table t2 (a int);

679

create table t3 (a int);

677

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

678

create table t2 (a int) ENGINE=MyISAM;

679

create table t3 (a int) ENGINE=MyISAM;

680

insert into t2 values (1);

681

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

682

select * from t1;

762

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

763

ERROR 21000: Subquery returns more than 1 row

764

drop table t2, t1;

765

create table t1 (a int);

765

create table t1 (a int) ENGINE=MyISAM;

766

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

767

select 1 IN (SELECT * from t1);

768

1 IN (SELECT * from t1)

796

10 > ANY (SELECT * from t1)

797

1

798

drop table t1;

799

create table t1 (a varchar(20));

799

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

800

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

801

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

802

'A' IN (SELECT * from t1)

830

'XYZS' > ANY (SELECT * from t1)

831

1

832

drop table t1;

833

create table t1 (a float);

833

create table t1 (a float) ENGINE=MyISAM;

834

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

835

select 1.5 IN (SELECT * from t1);

836

1.5 IN (SELECT * from t1)

876

4.5

877

NULL

878

drop table t1;

879

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

880

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

879

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

880

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

881

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

882

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

883

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

892

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

893

Warnings:

894

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

895

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

895

CREATE TABLE t3 (a int default '0') ENGINE=MyISAM;

896

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

897

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

898

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

908

Warnings:

909

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

910

drop table t1,t2,t3;

911

CREATE TABLE t1 (a int);

911

CREATE TABLE t1 (a int) ENGINE=MyISAM;

912

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

913

id select_type table type possible_keys key key_len ref rows filtered Extra

914

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

915

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

914

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

915

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

916

Warnings:

917

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

918

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

919

id select_type table type possible_keys key key_len ref rows filtered Extra

920

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

921

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

920

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

921

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

922

Warnings:

923

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

924

drop table t1;

966

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

967

a (SELECT a)

968

1 1

969

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

969

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

970

SHOW CREATE TABLE t1;

971

Table Create Table

972

t1 CREATE TABLE `t1` (

973

`a` int NOT NULL,

974

`(SELECT 1)` int NOT NULL

975

) ENGINE=InnoDB

975

) ENGINE=MyISAM

976

drop table t1;

977

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

977

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

978

SHOW CREATE TABLE t1;

979

Table Create Table

980

t1 CREATE TABLE `t1` (

981

`a` int NOT NULL,

982

`(SELECT a)` int NOT NULL

983

) ENGINE=InnoDB

983

) ENGINE=MyISAM

984

drop table t1;

985

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

985

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

986

SHOW CREATE TABLE t1;

987

Table Create Table

988

t1 CREATE TABLE `t1` (

989

`a` int NOT NULL,

990

`(SELECT a+0)` int NOT NULL

991

) ENGINE=InnoDB

991

) ENGINE=MyISAM

992

drop table t1;

993

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

993

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

994

select * from t1;

995

a

996

2

998

Table Create Table

999

t1 CREATE TABLE `t1` (

1000

`a` bigint NOT NULL

1001

) ENGINE=InnoDB

1001

) ENGINE=MyISAM

1002

drop table t1;

1003

create table t1 (a int);

1003

create table t1 (a int) ENGINE=MyISAM;

1004

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

1005

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

1006

from t1;

1105

2 lenka

1106

1 lenka

1107

drop table t1,t2;

1108

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

1108

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

1109

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

1110

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

1111

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

1112

NULL

1113

drop table t1;

1114

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

1114

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

1115

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

1116

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

1117

id select_type table type possible_keys key key_len ref rows filtered Extra

1118

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

1118

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

1119

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

1120

Warnings:

1121

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

1134

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

1135

REF_ID

1136

DROP TABLE t1;

1137

create table t1 (a int, b int);

1138

create table t2 (a int, b int);

1137

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

1138

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

1139

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

1140

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

1141

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

1164

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

1165

a

1166

1

1167

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

1168

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

1169

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

1167

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

1168

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

1169

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

1170

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

1171

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

1172

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

1203

Warnings:

1204

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

1205

drop table t1, t2, t3;

1206

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

1207

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

1208

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

1206

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

1207

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

1208

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

1209

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

1210

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

1211

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

1217

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

1218

id select_type table type possible_keys key key_len ref rows filtered Extra

1219

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

1220

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

1220

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

1221

Warnings:

1222

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

1223

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

1227

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

1228

id select_type table type possible_keys key key_len ref rows filtered Extra

1229

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

1230

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

1230

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

1231

Warnings:

1232

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

1233

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

1238

id select_type table type possible_keys key key_len ref rows filtered Extra

1239

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

1240

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

1241

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

1241

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

1242

Warnings:

1243

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

1244

insert into t1 values (3,31);

1254

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

1255

id select_type table type possible_keys key key_len ref rows filtered Extra

1256

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

1257

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

1257

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

1258

Warnings:

1259

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

1260

drop table t1, t2, t3;

1261

create table t1 (a int, b int);

1262

create table t2 (a int, b int);

1263

create table t3 (a int, b int);

1261

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

1262

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

1263

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

1264

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

1265

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

1266

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

1270

2 2

1271

1 2

1272

drop table t1,t2,t3;

1273

create table t1 (s1 int);

1274

create table t2 (s1 int);

1273

create table t1 (s1 int) ENGINE=MyISAM;

1274

create table t2 (s1 int) ENGINE=MyISAM;

1275

insert into t1 values (1);

1276

insert into t2 values (1);

1277

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

1278

s1

1279

1

1280

drop table t1,t2;

1281

create table t1 (s1 int);

1282

create table t2 (s1 int);

1281

create table t1 (s1 int) ENGINE=MyISAM;

1282

create table t2 (s1 int) ENGINE=MyISAM;

1283

insert into t1 values (1);

1284

insert into t2 values (1);

1285

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

1286

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

1287

DROP TABLE t1, t2;

1288

create table t1(toid int,rd int);

1289

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

1288

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

1289

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

1290

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

1291

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

1292

select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1);

1294

1 0 0 9 3

1295

2 0 0 4 2

1296

drop table t1, t2;

1297

create table t1 (s1 char(5));

1297

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

1298

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

1299

ERROR 21000: Operand should contain 1 column(s)

1300

insert into t1 values ('tttt');

1303

tttt

1304

explain extended (select * from t1);

1305

id select_type table type possible_keys key key_len ref rows filtered Extra

1306

1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00

1306

1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00

1307

Warnings:

1308

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

1308

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

1309

(select * from t1);

1310

s1

1311

tttt

1312

drop table t1;

1313

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

1314

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

1313

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

1314

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

1315

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

1316

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

1317

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

1359

Warnings:

1360

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

1361

drop table t1,t2;

1362

create table t2 (a int, b int);

1363

create table t3 (a int);

1362

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

1363

create table t3 (a int) ENGINE=MyISAM;

1364

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

1365

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

1366

a

1370

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

1371

id select_type table type possible_keys key key_len ref rows filtered Extra

1372

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

1373

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1373

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

1374

Warnings:

1375

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

1375

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

1376

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

1377

a

1378

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

1379

id select_type table type possible_keys key key_len ref rows filtered Extra

1380

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

1381

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1381

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

1382

Warnings:

1383

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

1383

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

1384

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

1385

a

1386

6

1389

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

1390

id select_type table type possible_keys key key_len ref rows filtered Extra

1391

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

1392

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

1392

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

1393

Warnings:

1394

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

1394

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

1395

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

1396

a

1397

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

1398

id select_type table type possible_keys key key_len ref rows filtered Extra

1399

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

1400

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

1400

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

1401

Warnings:

1402

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

1402

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

1403

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

1404

a

1405

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

1406

id select_type table type possible_keys key key_len ref rows filtered Extra

1407

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

1408

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1408

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

1409

Warnings:

1410

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

1411

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

1413

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

1414

id select_type table type possible_keys key key_len ref rows filtered Extra

1415

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

1416

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

1416

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

1417

Warnings:

1418

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

1419

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

1421

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

1422

id select_type table type possible_keys key key_len ref rows filtered Extra

1423

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

1424

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1424

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

1425

Warnings:

1426

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

1427

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

1429

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

1430

id select_type table type possible_keys key key_len ref rows filtered Extra

1431

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

1432

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

1432

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

1433

Warnings:

1434

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

1435

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

1469

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

1470

ERROR 21000: Operand should contain 3 column(s)

1471

DROP TABLE t1,t2;

1472

create table t1 (s1 char);

1472

create table t1 (s1 char) ENGINE=MyISAM;

1473

insert into t1 values ('e');

1474

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

1475

s1

1479

e

1480

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

1481

id select_type table type possible_keys key key_len ref rows filtered Extra

1482

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

1483

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

1484

3 UNION t1 ALL NULL NULL NULL NULL 1 100.00

1482

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00

1483

2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

1484

3 UNION t1 system NULL NULL NULL NULL 1 100.00

1485

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

1486

Warnings:

1487

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

1487

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

1488

drop table t1;

1489

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

1490

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

1497

89356874041 NULL

1498

95895001874 NULL

1499

drop table t1, t2;

1500

create table t1 (s1 int);

1501

create table t2 (s1 int);

1500

create table t1 (s1 int) ENGINE=MyISAM;

1501

create table t2 (s1 int) ENGINE=MyISAM;

1502

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

1503

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

1504

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

1506

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

1507

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

1508

drop table t1, t2;

1509

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

1510

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

1509

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

1510

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

1511

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

1512

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

1513

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

1514

COLC

1515

DROP TABLE t1, t2;

1516

CREATE TABLE t1 (a int);

1516

CREATE TABLE t1 (a int) ENGINE=MyISAM;

1517

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

1518

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

1519

(SELECT a)

1533

`bis` int NOT NULL default '0',

1534

PRIMARY KEY (`id`),

1535

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

1536

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

1537

);

1536

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

1537

ENGINE=MyISAM;

1538

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

1539

CREATE TABLE `t2` (

1540

`id` int NOT NULL auto_increment,

1541

`max_anno_dep` int NOT NULL default '0',

1542

PRIMARY KEY (`id`)

1543

);

1542

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

1544

1543

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

1545

1544

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

1546

1545

id max_anno_dep PIPPO

1548

1547

50 1990 0

1549

1548

51 1990 NULL

1550

1549

DROP TABLE t1, t2;

1551

create table t1 (a int);

1550

create table t1 (a int) ENGINE=MyISAM;

1552

1551

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

1553

1552

SET SQL_SELECT_LIMIT=1;

1554

1553

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

1559

1558

1

1560

1559

SET SQL_SELECT_LIMIT=default;

1561

1560

drop table t1;

1562

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

1561

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

1563

1562

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

1564

1563

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

1565

1564

a b

1567

1566

1 2

1568

1567

1 3

1569

1568

DROP TABLE t1;

1570

create table t1(val varchar(10));

1569

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

1571

1570

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

1572

1571

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

1573

1572

count(*)

1574

1573

0

1575

1574

drop table t1;

1576

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

1575

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

1577

1576

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

1578

1577

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

1579

1578

id text

1603

1602

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

1604

1603

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

1605

1604

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

1606

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

1605

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

1607

1606

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

1608

1607

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

1609

1608

id text id text id text

1629

1628

Warnings:

1630

1629

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

1631

1630

drop table t1,t2;

1632

create table t1 (a int);

1631

create table t1 (a int) ENGINE=MyISAM;

1633

1632

insert into t1 values (1);

1634

1633

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

1635

1634

id select_type table type possible_keys key key_len ref rows Extra

1636

1635

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

1637

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where

1636

2 SUBQUERY t1 system NULL NULL NULL NULL 1

1638

1637

drop table t1;

1639

create table t1(id int);

1640

create table t2(id int);

1641

create table t3(flag int);

1638

create table t1(id int) ENGINE=MyISAM;

1639

create table t2(id int) ENGINE=MyISAM;

1640

create table t3(flag int) ENGINE=MyISAM;

1642

1641

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

1643

1642

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

1644

1643

drop table t1,t2,t3;

1645

CREATE TABLE t1 (id INT);

1646

CREATE TABLE t2 (id INT);

1644

CREATE TABLE t1 (id INT) ENGINE=MyISAM;

1645

CREATE TABLE t2 (id INT) ENGINE=MyISAM;

1647

1646

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

1648

1647

INSERT INTO t2 VALUES (1);

1649

1648

SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);

1663

1662

1 1

1664

1663

2 0

1665

1664

DROP TABLE t1,t2;

1666

CREATE TABLE t1 ( a int, b int );

1665

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

1667

1666

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

1668

1667

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

1669

1668

a

2035

2034

1-

2036

2035

0-

2037

2036

DROP TABLE t1;

2038

CREATE TABLE t1 ( a double, b double );

2037

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

2039

2038

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

2040

2039

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

2041

2040

a

2080

2079

1

2081

2080

3

2082

2081

DROP TABLE t1;

2083

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

2082

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

2084

2083

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

2085

2084

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

2086

2085

a

2125

2124

1

2126

2125

3

2127

2126

DROP TABLE t1;

2128

create table t1 (a int, b int);

2127

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

2129

2128

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

2130

2129

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

2131

2130

a b

2139

2138

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

2140

2139

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

2141

2140

drop table t1;

2142

CREATE TABLE t1 (t1_a int);

2141

CREATE TABLE t1 (t1_a int) ENGINE=MyISAM;

2143

2142

INSERT INTO t1 VALUES (1);

2144

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

2143

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

2145

2144

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

2146

2145

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

2147

2146

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

2148

2147

t1_a t2_a t2_b

2149

2148

1 1 2

2150

2149

DROP TABLE t1, t2;

2151

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

2150

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

2152

2151

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

2153

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

2152

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

2154

2153

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

2155

2154

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

2156

2155

id name id pet

2166

2165

a C

2167

2166

1 1

2168

2167

drop table t1,t2;

2169

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

2168

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

2170

2169

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

2171

2170

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

2172

2171

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

2176

2175

IZAVORGANG_ID

2177

2176

D0000000001

2178

2177

drop table t1;

2179

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

2180

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

2178

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

2179

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

2181

2180

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

2182

2181

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

2183

2182

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

2197

2196

1 1

2198

2197

2 1

2199

2198

drop table t1,t2;

2200

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

2199

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

2201

2200

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

2202

2201

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

2203

2202

howmanyvalues count(*)

2231

2230

3 1

2232

2231

4 1

2233

2232

drop table t1;

2234

create table t1 (x int);

2233

create table t1 (x int) ENGINE=MyISAM;

2235

2234

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

2236

2235

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

2237

2236

drop table t1;

2238

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

2237

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

2239

2238

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

2240

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

2239

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

2241

2240

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

2242

2241

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

2243

2242

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

2247

2246

NULL

2248

2247

drop tables t1,t2;

2249

2248

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

2250

create table t1 (a int, b int);

2251

create table t2 (a int, b int);

2249

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

2250

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

2252

2251

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

2253

2252

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

2254

2253

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

2259

2258

2 4 NULL

2260

2259

2 5 NULL

2261

2260

drop table t1, t2;

2262

create table t1 (s1 int,s2 int);

2261

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

2263

2262

insert into t1 values (20,15);

2264

2263

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

2265

2264

s1 s2

2266

2265

drop table t1;

2267

create table t1 (s1 int);

2266

create table t1 (s1 int) ENGINE=MyISAM;

2268

2267

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

2269

2268

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

2270

2269

s1

2301

2300

c

2302

2301

Oceania

2303

2302

drop table t1;

2304

create table t1 (a1 int);

2305

create table t2 (b1 int);

2303

create table t1 (a1 int) ENGINE=MyISAM;

2304

create table t2 (b1 int) ENGINE=MyISAM;

2306

2305

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

2307

2306

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

2308

2307

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

2309

2308

a1

2310

2309

drop table t1,t2;

2311

create table t1 (a integer, b integer);

2310

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

2312

2311

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

2313

2312

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

2314

2313

NULL

2322

2321

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

2323

2322

1

2324

2323

drop table t1;

2325

create table t1 (a integer, b integer);

2324

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

2326

2325

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

2327

2326

ERROR 21000: Operand should contain 2 column(s)

2328

2327

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

2349

2348

endDate datetime NOT NULL,

2350

2349

createDate datetime NOT NULL,

2351

2350

modifyDate timestamp NOT NULL,

2352

attributes text NOT NULL

2353

);

2351

attributes text NOT NULL)

2352

engine=myisam;

2354

2353

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

2355

2354

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

2356

2355

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

2363

2362

CREATE TABLE t2 (

2364

2363

userId int NOT NULL,

2365

2364

courseId int NOT NULL,

2366

date datetime NOT NULL

2367

);

2365

date datetime NOT NULL)

2366

engine=myisam;

2368

2367

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

2369

2368

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

2370

2369

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

2378

2377

endDate datetime NOT NULL,

2379

2378

createDate datetime NOT NULL,

2380

2379

modifyDate timestamp NOT NULL,

2381

ordering int

2382

);

2380

ordering int)

2381

engine=myisam;

2383

2382

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

2384

2383

CREATE TABLE t4 (

2385

2384

id int NOT NULL,

2389

2388

ordering int,

2390

2389

description text,

2391

2390

createDate datetime NOT NULL,

2392

modifyDate timestamp NOT NULL

2393

);

2391

modifyDate timestamp NOT NULL)

2392

engine=myisam;

2394

2393

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

2395

2394

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

2396

2395

CREATE TABLE t5 (

2397

2396

userId int NOT NULL,

2398

2397

groupId int NOT NULL,

2399

2398

createDate datetime NOT NULL,

2400

modifyDate timestamp NOT NULL

2401

);

2399

modifyDate timestamp NOT NULL) ENGINE=MyISAM;

2402

2400

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

2403

2401

select

2404

2402

count(distinct t2.userid) pass,

2442

2440

1 5141 12 group2 12 group2 5 1 2 88 Oct04

2443

2441

1 5141 12 group2 12 group2 5 1 2 89 Oct04

2444

2442

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

2445

create table t1 (a int);

2443

create table t1 (a int) ENGINE=MyISAM;

2446

2444

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

2447

2445

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

2448

2446

1

2450

2448

1

2451

2449

1

2452

2450

drop table t1;

2453

create table t1 (a int, b int);

2451

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

2454

2452

insert into t1 values (1,2);

2455

2453

select 1 = (select * from t1);

2456

2454

ERROR 21000: Operand should contain 1 column(s)

2465

2463

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

2466

2464

ERROR 21000: Operand should contain 2 column(s)

2467

2465

drop table t1;

2468

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

2466

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

2469

2467

insert into t1 values ('1');

2470

2468

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

2471

2469

max(fld)

2472

2470

1

2473

2471

drop table t1;

2474

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

2475

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

2472

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

2473

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

2476

2474

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

2477

2475

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

2478

2476

SELECT * FROM t1

2555

2553

Warnings:

2556

2554

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

2557

2555

DROP TABLE t1,t2;

2558

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

2556

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

2559

2557

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

2560

2558

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

2561

2559

a b

2562

2560

aaa aaa

2563

2561

DROP TABLE t1;

2564

CREATE TABLE t1 (a int);

2565

CREATE TABLE t2 (a int, b int);

2566

CREATE TABLE t3 (b int NOT NULL);

2562

CREATE TABLE t1 (a int) ENGINE=MyISAM;

2563

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

2564

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

2567

2565

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

2568

2566

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

2569

2567

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

2578

2576

3

2579

2577

4

2580

2578

DROP TABLE t1,t2,t3;

2581

CREATE TABLE t1 (f1 INT);

2582

CREATE TABLE t2 (f2 INT);

2579

CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;

2580

CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;

2583

2581

INSERT INTO t1 VALUES (1);

2584

2582

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

2585

2583

f1

2593

2591

f1

2594

2592

1

2595

2593

DROP TABLE t1, t2;

2596

create table t1 (s1 char);

2594

create table t1 (s1 char) ENGINE=MyISAM;

2597

2595

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

2598

2596

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

2599

2597

s1

2624

2622

retailerID varchar(8) NOT NULL,

2625

2623

statusID int NOT NULL,

2626

2624

changed datetime NOT NULL,

2627

UNIQUE KEY retailerID (retailerID, statusID, changed)

2628

);

2625

UNIQUE KEY retailerID (retailerID, statusID, changed))

2626

ENGINE=MyISAM;

2629

2627

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

2630

2628

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

2631

2629

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

2642

2640

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

2643

2641

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

2644

2642

drop table t1;

2645

create table t1(a int, primary key (a));

2643

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

2646

2644

insert into t1 values (10);

2647

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

2645

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

2648

2646

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

2649

2647

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

2650

2648

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

2651

2649

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

2652

2650

id select_type table type possible_keys key key_len ref rows Extra

2653

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2651

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2654

2652

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2655

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

2653

2 DEPENDENT SUBQUERY t2 range b b 136 NULL 2 Using where

2656

2654

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

2657

2655

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

2658

2656

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

2662

2660

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

2663

2661

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

2664

2662

id select_type table type possible_keys key key_len ref rows Extra

2665

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2663

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2666

2664

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2667

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

2665

2 DEPENDENT SUBQUERY t2 range b b 136 NULL 2 Using where; Using MRR

2668

2666

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

2669

2667

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

2670

2668

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

2675

2673

field1 int NOT NULL,

2676

2674

field2 int NOT NULL,

2677

2675

field3 int NOT NULL,

2678

PRIMARY KEY (field1,field2,field3)

2679

);

2676

PRIMARY KEY (field1,field2,field3))

2677

ENGINE=MyISAM;

2680

2678

CREATE TABLE t2 (

2681

2679

fieldA int NOT NULL,

2682

2680

fieldB int NOT NULL,

2683

PRIMARY KEY (fieldA,fieldB)

2684

);

2681

PRIMARY KEY (fieldA,fieldB))

2682

ENGINE=MyISAM;

2685

2683

INSERT INTO t1 VALUES

2686

2684

(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);

2687

2685

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

2707

2705

1 1

2708

2706

1 3

2709

2707

DROP TABLE t1, t2;

2710

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

2708

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

2711

2709

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

2712

2710

INSERT INTO t1 VALUES (NULL);

2713

CREATE TABLE t2(a int);

2711

CREATE TABLE t2(a int) ENGINE=MyISAM;

2714

2712

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

2715

2713

EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;

2716

2714

id select_type table type possible_keys key key_len ref rows Extra

2722

2720

2 NULL

2723

2721

3 1

2724

2722

DROP TABLE t1,t2;

2725

CREATE TABLE t1 (a DATETIME);

2723

CREATE TABLE t1 (a DATETIME) ENGINE=MyISAM;

2726

2724

INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');

2727

CREATE TABLE t2 AS SELECT

2725

CREATE TABLE t2 ENGINE=MyISAM AS SELECT

2728

2726

(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a

2729

2727

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

2730

2728

SHOW CREATE TABLE t2;

2731

2729

Table Create Table

2732

2730

t2 CREATE TABLE `t2` (

2733

2731

`sub_a` datetime

2734

) ENGINE=InnoDB

2735

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

2732

) ENGINE=MyISAM

2733

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

2736

2734

SHOW CREATE TABLE t3;

2737

2735

Table Create Table

2738

2736

t3 CREATE TABLE `t3` (

2739

2737

`a` datetime

2740

) ENGINE=InnoDB

2738

) ENGINE=MyISAM

2741

2739

DROP TABLE t1,t2,t3;

2742

CREATE TABLE t1 (a int);

2740

CREATE TABLE t1 (a int) ENGINE=MyISAM;

2743

2741

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

2744

CREATE TABLE t2 (b int, c int);

2742

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

2745

2743

INSERT INTO t2 VALUES

2746

2744

(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);

2747

2745

SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );

2813

2811

(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));

2814

2812

ERROR 21000: Subquery returns more than 1 row

2815

2813

DROP TABLE t1,t2;

2816

create table t1 (df decimal(5,1));

2814

create table t1 (df decimal(5,1)) ENGINE=MyISAM;

2817

2815

insert into t1 values(1.1);

2818

2816

insert into t1 values(2.2);

2819

2817

select * from t1 where df <= all (select avg(df) from t1 group by df);

2823

2821

df

2824

2822

2.2

2825

2823

drop table t1;

2826

create table t1 (df decimal(5,1));

2824

create table t1 (df decimal(5,1)) ENGINE=MyISAM;

2827

2825

insert into t1 values(1.1);

2828

2826

select 1.1 * exists(select * from t1);

2829

2827

1.1 * exists(select * from t1)

2831

2829

drop table t1;

2832

2830

CREATE TABLE t1 (

2833

2831

grp int default NULL,

2834

a decimal(10,2) default NULL);

2832

a decimal(10,2) default NULL) ENGINE=MyISAM;

2835

2833

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

2836

2834

select * from t1;

2837

2835

grp a

2849

2847

2.00

2850

2848

4.00

2851

2849

drop table t1;

2852

CREATE table t1 ( c1 integer );

2850

CREATE table t1 ( c1 integer ) ENGINE=MyISAM;

2853

2851

INSERT INTO t1 VALUES ( 1 );

2854

2852

INSERT INTO t1 VALUES ( 2 );

2855

2853

INSERT INTO t1 VALUES ( 3 );

2856

CREATE TABLE t2 ( c2 integer );

2854

CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;

2857

2855

INSERT INTO t2 VALUES ( 1 );

2858

2856

INSERT INTO t2 VALUES ( 4 );

2859

2857

INSERT INTO t2 VALUES ( 5 );

2865

2863

c1 c2

2866

2864

1 1

2867

2865

DROP TABLE t1,t2;

2868

CREATE TABLE t1 ( c1 integer );

2866

CREATE TABLE t1 ( c1 integer ) ENGINE=MyISAM;

2869

2867

INSERT INTO t1 VALUES ( 1 );

2870

2868

INSERT INTO t1 VALUES ( 2 );

2871

2869

INSERT INTO t1 VALUES ( 3 );

2872

2870

INSERT INTO t1 VALUES ( 6 );

2873

CREATE TABLE t2 ( c2 integer );

2871

CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;

2874

2872

INSERT INTO t2 VALUES ( 1 );

2875

2873

INSERT INTO t2 VALUES ( 4 );

2876

2874

INSERT INTO t2 VALUES ( 5 );

2877

2875

INSERT INTO t2 VALUES ( 6 );

2878

CREATE TABLE t3 ( c3 integer );

2876

CREATE TABLE t3 ( c3 integer ) ENGINE=MyISAM;

2879

2877

INSERT INTO t3 VALUES ( 7 );

2880

2878

INSERT INTO t3 VALUES ( 8 );

2881

2879

SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2

2884

2882

2 NULL

2885

2883

3 NULL

2886

2884

DROP TABLE t1,t2,t3;

2887

CREATE TABLE t1 (EMPNUM CHAR(3));

2888

CREATE TABLE t2 (EMPNUM CHAR(3) );

2885

CREATE TABLE t1 (EMPNUM CHAR(3)) ENGINE=MyISAM;

2886

CREATE TABLE t2 (EMPNUM CHAR(3) ) ENGINE=MyISAM;

2889

2887

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

2890

2888

INSERT INTO t2 VALUES ('E1');

2891

2889

DELETE FROM t1

2897

2895

EMPNUM

2898

2896

E1

2899

2897

DROP TABLE t1,t2;

2900

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);

2898

CREATE TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;

2901

2899

INSERT INTO t1 VALUES (1, 1);

2902

2900

CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,

2903

PRIMARY KEY(select_id,values_id));

2901

PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;

2904

2902

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

2905

2903

SELECT values_id FROM t1

2906

2904

WHERE values_id IN (SELECT values_id FROM t2

2918

2916

values_id

2919

2917

1

2920

2918

DROP TABLE t1, t2;

2921

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

2919

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

2922

2920

insert into t1 values ('1');

2923

2921

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

2924

2922

max(fld)

2925

2923

1

2926

2924

drop table t1;

2927

CREATE TABLE t1 (a int, b int);

2928

CREATE TABLE t2 (c int, d int);

2929

CREATE TABLE t3 (e int);

2925

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

2926

CREATE TABLE t2 (c int, d int) ENGINE=MyISAM;

2927

CREATE TABLE t3 (e int) ENGINE=MyISAM;

2930

2928

INSERT INTO t1 VALUES

2931

2929

(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);

2932

2930

INSERT INTO t2 VALUES

3067

3065

3 20

3068

3066

4 40

3069

3067

DROP TABLE t1,t2,t3;

3070

CREATE TABLE t1 (a varchar(5), b varchar(10));

3068

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

3071

3069

INSERT INTO t1 VALUES

3072

3070

('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),

3073

3071

('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);

3091

3089

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

3092

3090

id select_type table type possible_keys key key_len ref rows Extra

3093

3091

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3094

2 SUBQUERY t1 index NULL a 23 NULL 9

3092

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

3095

3093

DROP TABLE t1;

3096

create table t1( f1 int,f2 int);

3094

create table t1( f1 int,f2 int) ENGINE=MyISAM;

3097

3095

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

3098

3096

select tt.t from (select 'crash1' as t, f2 from t1) as tt left join t1 on tt.t = 'crash2' and tt.f2 = t1.f2 where tt.t = 'crash1';

3099

3097

t

3100

3098

crash1

3101

3099

crash1

3102

3100

drop table t1;

3103

create table t1 (c int, key(c));

3101

create table t1 (c int, key(c)) ENGINE=MyISAM;

3104

3102

insert into t1 values (1142477582), (1142455969);

3105

create table t2 (a int, b int);

3103

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

3106

3104

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

3107

3105

delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;

3108

3106

drop table t1, t2;

3109

create table t1 (i int, j bigint);

3107

create table t1 (i int, j bigint) ENGINE=MyISAM;

3110

3108

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

3111

3109

select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;

3112

3110

min(i)

3113

3111

1

3114

3112

drop table t1;

3115

CREATE TABLE t1 (i BIGINT);

3113

CREATE TABLE t1 (i BIGINT) ENGINE=MyISAM;

3116

3114

INSERT INTO t1 VALUES (10000000000000000);

3117

3115

INSERT INTO t1 VALUES (1);

3118

CREATE TABLE t2 (i BIGINT);

3116

CREATE TABLE t2 (i BIGINT) ENGINE=MyISAM;

3119

3117

INSERT INTO t2 VALUES (10000000000000000);

3120

3118

INSERT INTO t2 VALUES (1);

3121

3119

/* simple test */

3136

3134

CREATE TABLE t1 (

3137

3135

id bigint NOT NULL auto_increment,

3138

3136

name varchar(255) NOT NULL,

3139

PRIMARY KEY (id)

3140

);

3137

PRIMARY KEY (id))

3138

ENGINE=MyISAM;

3141

3139

INSERT INTO t1 VALUES

3142

3140

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

3143

3141

CREATE TABLE t2 (

3144

3142

id bigint NOT NULL auto_increment,

3145

3143

mid bigint NOT NULL,

3146

3144

date date NOT NULL,

3147

PRIMARY KEY (id)

3148

);

3145

PRIMARY KEY (id))

3146

ENGINE=MyISAM;

3149

3147

INSERT INTO t2 VALUES

3150

3148

(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),

3151

3149

(4, 2, '2006-04-20'), (5, 1, '2006-05-01');

3182

3180

i1 int NOT NULL default '0',

3183

3181

i2 int NOT NULL default '0',

3184

3182

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

3185

PRIMARY KEY (i1,i2,t)

3186

);

3183

PRIMARY KEY (i1,i2,t))

3184

ENGINE=MyISAM;

3187

3185

INSERT INTO t1 VALUES

3188

3186

(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),

3189

3187

(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),

3195

3193

i1 int NOT NULL default '0',

3196

3194

i2 int NOT NULL default '0',

3197

3195

t datetime default NULL,

3198

PRIMARY KEY (i1)

3199

);

3196

PRIMARY KEY (i1))

3197

ENGINE=MyISAM;

3200

3198

INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');

3201

3199

EXPLAIN

3202

3200

SELECT * FROM t1,t2

3204

3202

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

3205

3203

ORDER BY t1.t DESC LIMIT 1);

3206

3204

id select_type table type possible_keys key key_len ref rows Extra

3207

1 PRIMARY t2 ALL NULL NULL NULL NULL 1

3208

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

3209

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

3205

1 PRIMARY t2 system NULL NULL NULL NULL 1

3206

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

3207

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

3210

3208

SELECT * FROM t1,t2

3211

3209

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

3212

3210

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

3214

3212

i1 i2 t i1 i2 t

3215

3213

24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40

3216

3214

DROP TABLE t1, t2;

3217

CREATE TABLE t1 (i INT);

3215

CREATE TABLE t1 (i INT) ENGINE=MyISAM;

3218

3216

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

3219

3217

i

3220

3218

SELECT * FROM t1 WHERE NOT EXISTS

3233

3231

explain select * from t1 where not exists

3234

3232

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

3235

3233

id select_type table type possible_keys key key_len ref rows Extra

3236

1 PRIMARY t1 ALL NULL NULL NULL NULL 1

3237

2 SUBQUERY t11 ALL NULL NULL NULL NULL 1

3238

3 UNION t12 ALL NULL NULL NULL NULL 1

3234

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

3235

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

3236

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

3239

3237

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

3240

3238

DROP TABLE t1;

3241

CREATE TABLE t1 (a INT, b INT);

3242

CREATE TABLE t2 (a INT);

3239

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

3240

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

3243

3241

INSERT INTO t2 values (1);

3244

3242

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

3245

3243

SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;

3278

3276

2

3279

3277

2

3280

3278

DROP TABLE t1,t2;

3281

CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));

3279

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

3282

3280

CREATE TABLE t2 (x int auto_increment, y int, z int,

3283

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));

3281

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;

3284

3282

SET SESSION sort_buffer_size = 32 * 1024;

3285

3283

Warnings:

3286

3284

Warning 1292 Truncated incorrect sort_buffer_size value: '32768'

3296

3294

COUNT(*)

3297

3295

3000

3298

3296

DROP TABLE t2,t1;

3299

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

3300

CREATE TABLE t2 (c int);

3297

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

3298

CREATE TABLE t2 (c int) ENGINE=MyISAM;

3301

3299

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

3302

3300

INSERT INTO t2 VALUES (1);

3303

3301

SELECT * FROM t1

3317

3315

cc 3

3318

3316

dd 1

3319

3317

INSERT INTO t2 VALUES (2);

3320

CREATE TABLE t3 (c int);

3318

CREATE TABLE t3 (c int) ENGINE=MyISAM;

3321

3319

INSERT INTO t3 VALUES (1);

3322

3320

SELECT * FROM t1

3323

3321

WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1

3334

3332

DROP TABLE IF EXISTS t1xt2;

3335

3333

CREATE TABLE t1 (

3336

3334

id_1 int NOT NULL,

3337

t varchar(4) DEFAULT NULL

3338

);

3335

t varchar(4) DEFAULT NULL)

3336

ENGINE=MyISAM;

3339

3337

CREATE TABLE t2 (

3340

3338

id_2 int NOT NULL,

3341

t varchar(4) DEFAULT NULL

3342

);

3339

t varchar(4) DEFAULT NULL)

3340

ENGINE=MyISAM;

3343

3341

CREATE TABLE t1xt2 (

3344

3342

id_1 int NOT NULL,

3345

id_2 int NOT NULL

3346

);

3343

id_2 int NOT NULL)

3344

ENGINE=MyISAM;

3347

3345

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

3348

3346

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

3349

3347

INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);

3442

3440

DROP TABLE t1;

3443

3441

DROP TABLE t2;

3444

3442

DROP TABLE t1xt2;

3445

CREATE TABLE t1 (a int);

3443

CREATE TABLE t1 (a int) ENGINE=MyISAM;

3446

3444

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

3447

3445

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;

3448

3446

col1 col2

3455

3453

this is a test. 1

3456

3454

this is a test. 2

3457

3455

DROP table t1;

3458

CREATE TABLE t1 (a int, b int);

3459

CREATE TABLE t2 (m int, n int);

3456

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

3457

CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;

3460

3458

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

3461

3459

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3462

3460

SELECT COUNT(*), a,

3480

3478

2 2

3481

3479

3 3

3482

3480

DROP TABLE t1,t2;

3483

CREATE TABLE t1 (a int, b int);

3484

CREATE TABLE t2 (m int, n int);

3481

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

3482

CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;

3485

3483

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

3486

3484

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3487

3485

SELECT COUNT(*) c, a,

3499

3497

3 3 4

3500

3498

1 4 2,2

3501

3499

DROP table t1,t2;

3502

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));

3500

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)) ENGINE=MyISAM;

3503

3501

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

3504

3502

(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),

3505

3503

(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');

3564

3562

2 o

3565

3563

3 p

3566

3564

DROP TABLE t1;

3567

CREATE TABLE t1 (a INT);

3565

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

3568

3566

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

3569

CREATE TABLE t2 (x INT);

3567

CREATE TABLE t2 (x INT) ENGINE=MyISAM;

3570

3568

INSERT INTO t1 values (1000),(1001),(1002);

3571

3569

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

3572

3570

ERROR HY000: Invalid use of group function

3581

3579

FROM t1;

3582

3580

ERROR HY000: Invalid use of group function

3583

3581

DROP TABLE t1,t2;

3584

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

3582

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

3585

3583

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

3586

3584

EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);

3587

3585

id select_type table type possible_keys key key_len ref rows Extra

3588

3586

1 PRIMARY t1 ref a a 5 const 1 Using where; Using index

3589

3587

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

3590

3588

DROP TABLE t1;

3591

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));

3589

CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)) ENGINE=MyISAM;

3592

3590

INSERT INTO t1 VALUES

3593

3591

(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');

3594

CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));

3592

CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;

3595

3593

INSERT INTO t2 VALUES (7), (5), (1), (3);

3596

3594

SELECT id, st FROM t1

3597

3595

WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);

3618

3616

2 GA

3619

3617

4 FL

3620

3618

DROP TABLE t1,t2;

3621

CREATE TABLE t1 (a int);

3619

CREATE TABLE t1 (a int) ENGINE=MyISAM;

3622

3620

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

3623

3621

EXPLAIN EXTENDED

3624

3622

SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;

3631

3629

CREATE TABLE t1 (

3632

3630

a varchar(255) default NULL,

3633

3631

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3634

INDEX idx(a,b)

3635

);

3636

Warnings:

3637

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

3632

INDEX idx(a,b))

3633

ENGINE=MyISAM;

3638

3634

CREATE TABLE t2 (

3639

a varchar(255) default NULL

3640

);

3635

a varchar(255) default NULL)

3636

ENGINE=MyISAM;

3641

3637

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');

3642

3638

INSERT INTO t1 SELECT * FROM t1;

3643

3639

INSERT INTO t1 SELECT * FROM t1;

3658

3654

1

3659

3655

1

3660

3656

DROP TABLE t1,t2;

3661

CREATE TABLE t1 (a INTEGER, b INTEGER);

3662

CREATE TABLE t2 (x INTEGER);

3657

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

3658

CREATE TABLE t2 (x INTEGER) ENGINE=MyISAM;

3663

3659

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

3664

3660

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

3665

3661

SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;

3670

3666

(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)

3671

3667

3.3333

3672

3668

DROP TABLE t1,t2;

3673

CREATE TABLE t1 (a INT, b INT);

3669

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

3674

3670

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

3675

3671

SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1

3676

3672

AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)

3678

3674

a COUNT(*)

3679

3675

1 3

3680

3676

DROP TABLE t1;

3681

CREATE TABLE t1 (a INT);

3682

CREATE TABLE t2 (a INT);

3677

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

3678

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

3683

3679

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

3684

3680

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

3685

3681

SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;

3691

3687

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

3692

3688

3

3693

3689

DROP TABLE t1,t2;

3694

CREATE TABLE t1 (a1 INT, a2 INT);

3695

CREATE TABLE t2 (b1 INT, b2 INT);

3690

CREATE TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;

3691

CREATE TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;

3696

3692

INSERT INTO t1 VALUES (100, 200);

3697

3693

INSERT INTO t1 VALUES (101, 201);

3698

3694

INSERT INTO t2 VALUES (101, 201);

3702

3698

0

3703

3699

0

3704

3700

DROP TABLE t1, t2;

3705

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

3701

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

3706

3702

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

3707

3703

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

3708

3704

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

3712

3708

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

3713

3709

id select_type table type possible_keys key key_len ref rows Extra

3714

3710

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

3715

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

3711

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

3716

3712

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

3717

3713

a b

3718

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

3714

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

3719

3715

INSERT INTO t2 SELECT * FROM t1;

3720

3716

CREATE INDEX I1 ON t2 (a);

3721

3717

CREATE INDEX I2 ON t2 (b);

3722

3718

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

3723

3719

id select_type table type possible_keys key key_len ref rows Extra

3724

3720

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

3725

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

3721

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

3726

3722

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

3727

3723

a b

3728

3724

EXPLAIN

3729

3725

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

3730

3726

id select_type table type possible_keys key key_len ref rows Extra

3731

3727

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

3732

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

3728

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

3733

3729

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

3734

3730

a b

3735

3731

DROP TABLE t1,t2;

3736

CREATE TABLE t1(a INT, b INT);

3732

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

3737

3733

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

3738

3734

EXPLAIN

3739

3735

SELECT a AS out_a, MIN(b) FROM t1

3758

3754

1 2

3759

3755

2 4

3760

3756

DROP TABLE t1;

3761

CREATE TABLE t1 (a INT);

3762

CREATE TABLE t2 (a INT);

3757

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

3758

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

3763

3759

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

3764

3760

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

3765

3761

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

3790

3786

CREATE TABLE t4 (

3791

3787

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

3792

3788

f10 varchar(32) collate utf8_bin default NULL,

3793

PRIMARY KEY (f7)

3794

);

3789

PRIMARY KEY (f7))

3790

ENGINE=MyISAM;

3795

3791

INSERT INTO t4 VALUES(1,1), (2,null);

3796

3792

CREATE TABLE t2 (

3797

3793

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

3798

3794

f2 varchar(50) collate utf8_bin default NULL,

3799

3795

f3 varchar(10) collate utf8_bin default NULL,

3800

3796

PRIMARY KEY (f4),

3801

UNIQUE KEY uk1 (f2)

3802

);

3797

UNIQUE KEY uk1 (f2))

3798

ENGINE=MyISAM;

3803

3799

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

3804

CREATE TABLE t1 (

3800

CREATE TABLE t1 (

3805

3801

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

3806

3802

f1 varchar(10) collate utf8_bin default NULL,

3807

3803

f9 varchar(32) collate utf8_bin default NULL,

3808

PRIMARY KEY (f8)

3809

);

3804

PRIMARY KEY (f8))

3805

ENGINE=MyISAM;

3810

3806

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

3811

3807

CREATE TABLE t3 (

3812

3808

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

3813

3809

f5 varchar(50) collate utf8_bin default NULL,

3814

PRIMARY KEY (f6)

3815

);

3810

PRIMARY KEY (f6))

3811

ENGINE=MyISAM;

3816

3812

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

3817

3813

SELECT

3818

3814

IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,

3837

3833

DROP TABLE t1, t2, t3, t4;

3838

3834

End of 5.0 tests.

3839

3835

create table t_out (subcase char(3),

3840

a1 char(2), b1 char(2), c1 char(2));

3841

create table t_in (a2 char(2), b2 char(2), c2 char(2));

3836

a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;

3837

create table t_in (a2 char(2), b2 char(2), c2 char(2)) ENGINE=MyISAM;

3842

3838

insert into t_out values ('A.1','2a', NULL, '2a');

3843

3839

insert into t_out values ('A.3', '2a', NULL, '2a');

3844

3840

insert into t_out values ('A.4', '2a', NULL, 'xx');

4033

4029

T

4034

4030

drop table t_out;

4035

4031

drop table t_in;

4036

CREATE TABLE t1 (s1 char(1));

4032

CREATE TABLE t1 (s1 char(1)) ENGINE=MyISAM;

4037

4033

INSERT INTO t1 VALUES ('a');

4038

4034

SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);

4039

4035

s1

4040

4036

a

4041

4037

DROP TABLE t1;

4042

CREATE TABLE t1( a INT );

4038

CREATE TABLE t1( a INT ) ENGINE=MyISAM;

4043

4039

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

4044

CREATE TABLE t2( a INT, b INT );

4040

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

4045

4041

SELECT *

4046

4042

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

4047

4043

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