~drizzle-trunk/drizzle/development : revision 754.1.1

131

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

132

a b (SELECT b)

133

1 2 2

134

create table t1 (a int);

135

create table t2 (a int, b int);

136

create table t3 (a int);

137

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

134

create table t1 (a int) ENGINE=MyISAM;

135

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

136

create table t3 (a int) ENGINE=MyISAM;

137

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

138

insert into t1 values (2);

139

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

140

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

286

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

287

b ma

288

7 12

289

create table t5 (a int);

289

create table t5 (a int) ENGINE=MyISAM;

290

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

291

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

292

NULL 1

304

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

305

id select_type table type possible_keys key key_len ref rows filtered Extra

306

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00

307

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

307

2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

308

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

309

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

310

Warnings:

311

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

312

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

313

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

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

317

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

316

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

317

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

318

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

319

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

320

select * from t6 where exists (select * from t7 where uq = 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;

335

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

335

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

336

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

337

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

337

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

338

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

339

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

339

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

340

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

341

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

342

a b

502

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

503

ERROR 21000: Subquery returns more than 1 row

504

drop table t1;

505

create table t1 (a int);

505

create table t1 (a int) ENGINE=MyISAM;

506

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

507

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

508

a

510

2

511

3

512

drop table t1;

513

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

513

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

514

INSERT INTO t1 VALUES ();

515

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

516

ERROR 21000: Subquery returns more than 1 row

544

Warnings:

545

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

546

drop table t1;

547

CREATE TABLE t1 (a int);

547

CREATE TABLE t1 (a int) ENGINE=MyISAM;

548

INSERT INTO t1 VALUES (1);

549

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

550

1

551

1

552

drop table t1;

553

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

554

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

553

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

554

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

555

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

556

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

557

select * from t1;

570

1 21

571

2 22

572

drop table t1, t2;

573

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

574

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

573

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

574

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

575

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

576

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

577

select * from t1;

592

0 10

593

1 11

594

drop table t1, t2;

595

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

596

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

597

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

595

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

596

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

597

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

598

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

599

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

600

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

622

22 11

623

33 10

624

drop table t11, t12, t2;

625

CREATE TABLE t1 (x int);

626

create table t2 (a int);

627

create table t3 (b int);

625

CREATE TABLE t1 (x int) ENGINE=MyISAM;

626

create table t2 (a int) ENGINE=MyISAM;

627

create table t3 (b int) ENGINE=MyISAM;

628

insert into t2 values (1);

629

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

630

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

670

11

671

2

672

drop table t1, t2, t3;

673

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

674

create table t2 (a int);

675

create table t3 (a int);

673

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

674

create table t2 (a int) ENGINE=MyISAM;

675

create table t3 (a int) ENGINE=MyISAM;

676

insert into t2 values (1);

677

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

678

select * from t1;

758

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

759

ERROR 21000: Subquery returns more than 1 row

760

drop table t2, t1;

761

create table t1 (a int);

761

create table t1 (a int) ENGINE=MyISAM;

762

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

763

select 1 IN (SELECT * from t1);

764

1 IN (SELECT * from t1)

792

10 > ANY (SELECT * from t1)

793

1

794

drop table t1;

795

create table t1 (a varchar(20));

795

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

796

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

797

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

798

'A' IN (SELECT * from t1)

826

'XYZS' > ANY (SELECT * from t1)

827

1

828

drop table t1;

829

create table t1 (a float);

829

create table t1 (a float) ENGINE=MyISAM;

830

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

831

select 1.5 IN (SELECT * from t1);

832

1.5 IN (SELECT * from t1)

872

4.5

873

NULL

874

drop table t1;

875

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

876

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

875

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

876

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

877

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

878

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

879

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

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`

891

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

891

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

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;

894

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

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`

906

drop table t1,t2,t3;

907

CREATE TABLE t1 (a int);

907

CREATE TABLE t1 (a int) ENGINE=MyISAM;

908

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

909

id select_type table type possible_keys key key_len ref rows filtered Extra

910

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

911

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

910

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

911

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

912

Warnings:

913

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

914

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

915

id select_type table type possible_keys key key_len ref rows filtered Extra

916

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

917

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

916

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

917

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

918

Warnings:

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`

920

drop table t1;

962

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

963

a (SELECT a)

964

1 1

965

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

965

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

966

SHOW CREATE TABLE t1;

967

Table Create Table

968

t1 CREATE TABLE `t1` (

969

`a` int NOT NULL,

970

`(SELECT 1)` int NOT NULL

971

) ENGINE=InnoDB

971

) ENGINE=MyISAM

972

drop table t1;

973

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

973

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

974

SHOW CREATE TABLE t1;

975

Table Create Table

976

t1 CREATE TABLE `t1` (

977

`a` int NOT NULL,

978

`(SELECT a)` int NOT NULL

979

) ENGINE=InnoDB

979

) ENGINE=MyISAM

980

drop table t1;

981

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

981

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

982

SHOW CREATE TABLE t1;

983

Table Create Table

984

t1 CREATE TABLE `t1` (

985

`a` int NOT NULL,

986

`(SELECT a+0)` int NOT NULL

987

) ENGINE=InnoDB

987

) ENGINE=MyISAM

988

drop table t1;

989

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

989

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

990

select * from t1;

991

a

992

2

994

Table Create Table

995

t1 CREATE TABLE `t1` (

996

`a` bigint NOT NULL

997

) ENGINE=InnoDB

997

) ENGINE=MyISAM

998

drop table t1;

999

create table t1 (a int);

999

create table t1 (a int) ENGINE=MyISAM;

1000

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

1001

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

1002

from t1;

1101

2 lenka

1102

1 lenka

1103

drop table t1,t2;

1104

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

1104

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

1105

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

1106

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

1107

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

1108

NULL

1109

drop table t1;

1110

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

1110

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

1111

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

1112

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

1113

id select_type table type possible_keys key key_len ref rows filtered Extra

1114

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

1114

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

1115

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

1116

Warnings:

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

1130

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

1131

REF_ID

1132

DROP TABLE t1;

1133

create table t1 (a int, b int);

1134

create table t2 (a int, b int);

1133

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

1134

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

1135

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

1136

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

1137

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

1160

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

1161

a

1162

1

1163

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

1164

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

1165

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

1163

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

1164

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

1165

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

1166

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

1167

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

1168

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

1174

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

1175

id select_type table type possible_keys key key_len ref rows filtered Extra

1176

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

1177

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

1177

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

1178

Warnings:

1179

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

1180

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

1199

Warnings:

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

1201

drop table t1, t2, t3;

1202

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

1203

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

1204

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

1202

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

1203

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

1204

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

1205

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

1206

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

1207

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

1213

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

1214

id select_type table type possible_keys key key_len ref rows filtered Extra

1215

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

1216

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

1216

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

1217

Warnings:

1218

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

1219

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

1223

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

1224

id select_type table type possible_keys key key_len ref rows filtered Extra

1225

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

1226

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

1226

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

1227

Warnings:

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

1229

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

1232

3

1233

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

1234

id select_type table type possible_keys key key_len ref rows filtered Extra

1235

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

1236

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

1237

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

1235

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

1236

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

1237

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

1238

Warnings:

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

1240

insert into t1 values (3,31);

1250

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

1251

id select_type table type possible_keys key key_len ref rows filtered Extra

1252

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

1253

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

1253

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

1254

Warnings:

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

1256

drop table t1, t2, t3;

1257

create table t1 (a int, b int);

1258

create table t2 (a int, b int);

1259

create table t3 (a int, b int);

1257

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

1258

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

1259

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

1260

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

1261

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

1262

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

1266

2 2

1267

1 2

1268

drop table t1,t2,t3;

1269

create table t1 (s1 int);

1270

create table t2 (s1 int);

1269

create table t1 (s1 int) ENGINE=MyISAM;

1270

create table t2 (s1 int) ENGINE=MyISAM;

1271

insert into t1 values (1);

1272

insert into t2 values (1);

1273

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

1274

s1

1275

1

1276

drop table t1,t2;

1277

create table t1 (s1 int);

1278

create table t2 (s1 int);

1277

create table t1 (s1 int) ENGINE=MyISAM;

1278

create table t2 (s1 int) ENGINE=MyISAM;

1279

insert into t1 values (1);

1280

insert into t2 values (1);

1281

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

1282

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

1283

DROP TABLE t1, t2;

1284

create table t1(toid int,rd int);

1285

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

1284

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

1285

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

1286

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

1287

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

1288

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

1290

1 0 0 9 3

1291

2 0 0 4 2

1292

drop table t1, t2;

1293

create table t1 (s1 char(5));

1293

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

1294

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

1295

ERROR 21000: Operand should contain 1 column(s)

1296

insert into t1 values ('tttt');

1299

tttt

1300

explain extended (select * from t1);

1301

id select_type table type possible_keys key key_len ref rows filtered Extra

1302

1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00

1302

1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00

1303

Warnings:

1304

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

1304

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

1305

(select * from t1);

1306

s1

1307

tttt

1308

drop table t1;

1309

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

1310

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

1309

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

1310

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

1311

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

1312

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

1313

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

1355

Warnings:

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`

1357

drop table t1,t2;

1358

create table t2 (a int, b int);

1359

create table t3 (a int);

1358

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

1359

create table t3 (a int) ENGINE=MyISAM;

1360

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

1361

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

1362

a

1366

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

1367

id select_type table type possible_keys key key_len ref rows filtered Extra

1368

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

1369

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1369

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

1370

Warnings:

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

1371

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

1372

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

1373

a

1374

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

1375

id select_type table type possible_keys key key_len ref rows filtered Extra

1376

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

1377

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1377

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

1378

Warnings:

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

1379

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

1380

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

1381

a

1382

6

1385

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

1386

id select_type table type possible_keys key key_len ref rows filtered Extra

1387

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

1388

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

1388

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

1389

Warnings:

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

1390

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

1391

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

1392

a

1393

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

1394

id select_type table type possible_keys key key_len ref rows filtered Extra

1395

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

1396

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

1396

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

1397

Warnings:

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

1398

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

1399

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

1400

a

1401

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

1402

id select_type table type possible_keys key key_len ref rows filtered Extra

1403

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

1404

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1404

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

1405

Warnings:

1406

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

1407

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

1409

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

1410

id select_type table type possible_keys key key_len ref rows filtered Extra

1411

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

1412

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

1412

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

1413

Warnings:

1414

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

1415

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

1417

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

1418

id select_type table type possible_keys key key_len ref rows filtered Extra

1419

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

1420

2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00

1420

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

1421

Warnings:

1422

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

1423

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

1425

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

1426

id select_type table type possible_keys key key_len ref rows filtered Extra

1427

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

1428

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

1428

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

1429

Warnings:

1430

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

1431

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

1465

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

1466

ERROR 21000: Operand should contain 3 column(s)

1467

DROP TABLE t1,t2;

1468

create table t1 (s1 char);

1468

create table t1 (s1 char) ENGINE=MyISAM;

1469

insert into t1 values ('e');

1470

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

1471

s1

1475

e

1476

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

1477

id select_type table type possible_keys key key_len ref rows filtered Extra

1478

1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00

1479

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00

1480

3 UNION t1 ALL NULL NULL NULL NULL 1 100.00

1478

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00

1479

2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00

1480

3 UNION t1 system NULL NULL NULL NULL 1 100.00

1481

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

1482

Warnings:

1483

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

1483

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

1484

drop table t1;

1485

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

1486

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

1493

89356874041 NULL

1494

95895001874 NULL

1495

drop table t1, t2;

1496

create table t1 (s1 int);

1497

create table t2 (s1 int);

1496

create table t1 (s1 int) ENGINE=MyISAM;

1497

create table t2 (s1 int) ENGINE=MyISAM;

1498

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

1499

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

1500

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

1502

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

1503

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

1504

drop table t1, t2;

1505

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

1506

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

1505

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

1506

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

1507

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

1508

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

1509

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

1510

COLC

1511

DROP TABLE t1, t2;

1512

CREATE TABLE t1 (a int);

1512

CREATE TABLE t1 (a int) ENGINE=MyISAM;

1513

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

1514

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

1515

(SELECT a)

1529

`bis` int NOT NULL default '0',

1530

PRIMARY KEY (`id`),

1531

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

1532

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

1533

);

1532

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

1533

ENGINE=MyISAM;

1534

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

1535

CREATE TABLE `t2` (

1536

`id` int NOT NULL auto_increment,

1537

`max_anno_dep` int NOT NULL default '0',

1538

PRIMARY KEY (`id`)

1539

);

1538

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

1540

1539

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

1541

1540

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;

1542

1541

id max_anno_dep PIPPO

1544

1543

50 1990 0

1545

1544

51 1990 NULL

1546

1545

DROP TABLE t1, t2;

1547

create table t1 (a int);

1546

create table t1 (a int) ENGINE=MyISAM;

1548

1547

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

1549

1548

SET SQL_SELECT_LIMIT=1;

1550

1549

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

1555

1554

1

1556

1555

SET SQL_SELECT_LIMIT=default;

1557

1556

drop table t1;

1558

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

1557

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

1559

1558

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

1560

1559

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

1561

1560

a b

1563

1562

1 2

1564

1563

1 3

1565

1564

DROP TABLE t1;

1566

create table t1(val varchar(10));

1565

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

1567

1566

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

1568

1567

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

1569

1568

count(*)

1570

1569

0

1571

1570

drop table t1;

1572

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

1571

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

1573

1572

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

1574

1573

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

1575

1574

id text

1599

1598

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

1600

1599

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

1601

1600

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

1602

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

1601

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

1603

1602

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

1604

1603

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

1605

1604

id text id text id text

1625

1624

Warnings:

1626

1625

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

1627

1626

drop table t1,t2;

1628

create table t1 (a int);

1627

create table t1 (a int) ENGINE=MyISAM;

1629

1628

insert into t1 values (1);

1630

1629

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

1631

1630

id select_type table type possible_keys key key_len ref rows Extra

1632

1631

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

1633

2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where

1632

2 SUBQUERY t1 system NULL NULL NULL NULL 1

1634

1633

drop table t1;

1635

create table t1(id int);

1636

create table t2(id int);

1637

create table t3(flag int);

1634

create table t1(id int) ENGINE=MyISAM;

1635

create table t2(id int) ENGINE=MyISAM;

1636

create table t3(flag int) ENGINE=MyISAM;

1638

1637

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

1639

1638

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

1640

1639

drop table t1,t2,t3;

1641

CREATE TABLE t1 (id INT);

1642

CREATE TABLE t2 (id INT);

1640

CREATE TABLE t1 (id INT) ENGINE=MyISAM;

1641

CREATE TABLE t2 (id INT) ENGINE=MyISAM;

1643

1642

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

1644

1643

INSERT INTO t2 VALUES (1);

1645

1644

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

1659

1658

1 1

1660

1659

2 0

1661

1660

DROP TABLE t1,t2;

1662

CREATE TABLE t1 ( a int, b int );

1661

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

1663

1662

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

1664

1663

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

1665

1664

a

2031

2030

1-

2032

2031

0-

2033

2032

DROP TABLE t1;

2034

CREATE TABLE t1 ( a double, b double );

2033

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

2035

2034

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

2036

2035

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

2037

2036

a

2076

2075

1

2077

2076

3

2078

2077

DROP TABLE t1;

2079

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

2078

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

2080

2079

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

2081

2080

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

2082

2081

a

2121

2120

1

2122

2121

3

2123

2122

DROP TABLE t1;

2124

create table t1 (a int, b int);

2123

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

2125

2124

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

2126

2125

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

2127

2126

a b

2135

2134

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

2136

2135

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

2137

2136

drop table t1;

2138

CREATE TABLE t1 (t1_a int);

2137

CREATE TABLE t1 (t1_a int) ENGINE=MyISAM;

2139

2138

INSERT INTO t1 VALUES (1);

2140

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

2139

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

2141

2140

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

2142

2141

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

2143

2142

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

2144

2143

t1_a t2_a t2_b

2145

2144

1 1 2

2146

2145

DROP TABLE t1, t2;

2147

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

2146

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

2148

2147

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

2149

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

2148

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

2150

2149

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

2151

2150

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

2152

2151

id name id pet

2162

2161

a C

2163

2162

1 1

2164

2163

drop table t1,t2;

2165

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

2164

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;

2166

2165

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

2167

2166

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

2168

2167

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

2172

2171

IZAVORGANG_ID

2173

2172

D0000000001

2174

2173

drop table t1;

2175

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

2176

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

2174

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

2175

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

2177

2176

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

2178

2177

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

2179

2178

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

2193

2192

1 1

2194

2193

2 1

2195

2194

drop table t1,t2;

2196

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

2195

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

2197

2196

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

2198

2197

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

2199

2198

howmanyvalues count(*)

2227

2226

3 1

2228

2227

4 1

2229

2228

drop table t1;

2230

create table t1 (x int);

2229

create table t1 (x int) ENGINE=MyISAM;

2231

2230

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;

2232

2231

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

2233

2232

drop table t1;

2234

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

2233

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;

2235

2234

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

2236

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

2235

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 ;

2237

2236

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

2238

2237

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;

2239

2238

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

2243

2242

NULL

2244

2243

drop tables t1,t2;

2245

2244

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

2246

create table t1 (a int, b int);

2247

create table t2 (a int, b int);

2245

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

2246

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

2248

2247

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

2249

2248

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

2250

2249

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

2255

2254

2 4 NULL

2256

2255

2 5 NULL

2257

2256

drop table t1, t2;

2258

create table t1 (s1 int,s2 int);

2257

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

2259

2258

insert into t1 values (20,15);

2260

2259

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

2261

2260

s1 s2

2262

2261

drop table t1;

2263

create table t1 (s1 int);

2262

create table t1 (s1 int) ENGINE=MyISAM;

2264

2263

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

2265

2264

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

2266

2265

s1

2297

2296

c

2298

2297

Oceania

2299

2298

drop table t1;

2300

create table t1 (a1 int);

2301

create table t2 (b1 int);

2299

create table t1 (a1 int) ENGINE=MyISAM;

2300

create table t2 (b1 int) ENGINE=MyISAM;

2302

2301

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

2303

2302

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

2304

2303

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

2305

2304

a1

2306

2305

drop table t1,t2;

2307

create table t1 (a integer, b integer);

2306

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

2308

2307

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

2309

2308

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

2310

2309

NULL

2318

2317

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

2319

2318

1

2320

2319

drop table t1;

2321

create table t1 (a integer, b integer);

2320

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

2322

2321

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

2323

2322

ERROR 21000: Operand should contain 2 column(s)

2324

2323

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

2345

2344

endDate datetime NOT NULL,

2346

2345

createDate datetime NOT NULL,

2347

2346

modifyDate timestamp NOT NULL,

2348

attributes text NOT NULL

2349

);

2347

attributes text NOT NULL)

2348

engine=myisam;

2350

2349

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

2351

2350

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

2352

2351

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

2359

2358

CREATE TABLE t2 (

2360

2359

userId int NOT NULL,

2361

2360

courseId int NOT NULL,

2362

date datetime NOT NULL

2363

);

2361

date datetime NOT NULL)

2362

engine=myisam;

2364

2363

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

2365

2364

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

2366

2365

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

2374

2373

endDate datetime NOT NULL,

2375

2374

createDate datetime NOT NULL,

2376

2375

modifyDate timestamp NOT NULL,

2377

ordering int

2378

);

2376

ordering int)

2377

engine=myisam;

2379

2378

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

2380

2379

CREATE TABLE t4 (

2381

2380

id int NOT NULL,

2385

2384

ordering int,

2386

2385

description text,

2387

2386

createDate datetime NOT NULL,

2388

modifyDate timestamp NOT NULL

2389

);

2387

modifyDate timestamp NOT NULL)

2388

engine=myisam;

2390

2389

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

2391

2390

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

2392

2391

CREATE TABLE t5 (

2393

2392

userId int NOT NULL,

2394

2393

groupId int NOT NULL,

2395

2394

createDate datetime NOT NULL,

2396

modifyDate timestamp NOT NULL

2397

);

2395

modifyDate timestamp NOT NULL) ENGINE=MyISAM;

2398

2396

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

2399

2397

select

2400

2398

count(distinct t2.userid) pass,

2438

2436

1 5141 12 group2 12 group2 5 1 2 88 Oct04

2439

2437

1 5141 12 group2 12 group2 5 1 2 89 Oct04

2440

2438

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

2441

create table t1 (a int);

2439

create table t1 (a int) ENGINE=MyISAM;

2442

2440

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

2443

2441

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

2444

2442

1

2446

2444

1

2447

2445

1

2448

2446

drop table t1;

2449

create table t1 (a int, b int);

2447

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

2450

2448

insert into t1 values (1,2);

2451

2449

select 1 = (select * from t1);

2452

2450

ERROR 21000: Operand should contain 1 column(s)

2461

2459

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

2462

2460

ERROR 21000: Operand should contain 2 column(s)

2463

2461

drop table t1;

2464

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

2462

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

2465

2463

insert into t1 values ('1');

2466

2464

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

2467

2465

max(fld)

2468

2466

1

2469

2467

drop table t1;

2470

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

2471

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

2468

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

2469

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

2472

2470

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

2473

2471

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

2474

2472

SELECT * FROM t1

2551

2549

Warnings:

2552

2550

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`

2553

2551

DROP TABLE t1,t2;

2554

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

2552

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

2555

2553

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

2556

2554

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

2557

2555

a b

2558

2556

aaa aaa

2559

2557

DROP TABLE t1;

2560

CREATE TABLE t1 (a int);

2561

CREATE TABLE t2 (a int, b int);

2562

CREATE TABLE t3 (b int NOT NULL);

2558

CREATE TABLE t1 (a int) ENGINE=MyISAM;

2559

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

2560

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

2563

2561

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

2564

2562

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

2565

2563

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

2574

2572

3

2575

2573

4

2576

2574

DROP TABLE t1,t2,t3;

2577

CREATE TABLE t1 (f1 INT);

2578

CREATE TABLE t2 (f2 INT);

2575

CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;

2576

CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;

2579

2577

INSERT INTO t1 VALUES (1);

2580

2578

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

2581

2579

f1

2589

2587

f1

2590

2588

1

2591

2589

DROP TABLE t1, t2;

2592

create table t1 (s1 char);

2590

create table t1 (s1 char) ENGINE=MyISAM;

2593

2591

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

2594

2592

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

2595

2593

s1

2620

2618

retailerID varchar(8) NOT NULL,

2621

2619

statusID int NOT NULL,

2622

2620

changed datetime NOT NULL,

2623

UNIQUE KEY retailerID (retailerID, statusID, changed)

2624

);

2621

UNIQUE KEY retailerID (retailerID, statusID, changed))

2622

ENGINE=MyISAM;

2625

2623

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

2626

2624

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

2627

2625

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

2638

2636

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

2639

2637

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

2640

2638

drop table t1;

2641

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

2639

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

2642

2640

insert into t1 values (10);

2643

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

2641

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

2644

2642

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

2645

2643

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

2646

2644

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

2647

2645

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

2648

2646

id select_type table type possible_keys key key_len ref rows Extra

2649

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2647

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2650

2648

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2651

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

2649

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

2652

2650

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

2653

2651

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

2654

2652

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

2658

2656

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

2659

2657

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

2660

2658

id select_type table type possible_keys key key_len ref rows Extra

2661

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2659

1 PRIMARY t1 system PRIMARY NULL NULL NULL 1

2662

2660

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2663

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

2661

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

2664

2662

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

2665

2663

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

2666

2664

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

2671

2669

field1 int NOT NULL,

2672

2670

field2 int NOT NULL,

2673

2671

field3 int NOT NULL,

2674

PRIMARY KEY (field1,field2,field3)

2675

);

2672

PRIMARY KEY (field1,field2,field3))

2673

ENGINE=MyISAM;

2676

2674

CREATE TABLE t2 (

2677

2675

fieldA int NOT NULL,

2678

2676

fieldB int NOT NULL,

2679

PRIMARY KEY (fieldA,fieldB)

2680

);

2677

PRIMARY KEY (fieldA,fieldB))

2678

ENGINE=MyISAM;

2681

2679

INSERT INTO t1 VALUES

2682

2680

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

2683

2681

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

2703

2701

1 1

2704

2702

1 3

2705

2703

DROP TABLE t1, t2;

2706

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

2704

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

2707

2705

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

2708

2706

INSERT INTO t1 VALUES (NULL);

2709

CREATE TABLE t2(a int);

2707

CREATE TABLE t2(a int) ENGINE=MyISAM;

2710

2708

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

2711

2709

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

2712

2710

id select_type table type possible_keys key key_len ref rows Extra

2718

2716

2 NULL

2719

2717

3 1

2720

2718

DROP TABLE t1,t2;

2721

CREATE TABLE t1 (a DATETIME);

2719

CREATE TABLE t1 (a DATETIME) ENGINE=MyISAM;

2722

2720

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

2723

CREATE TABLE t2 AS SELECT

2721

CREATE TABLE t2 ENGINE=MyISAM AS SELECT

2724

2722

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

2725

2723

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

2726

2724

SHOW CREATE TABLE t2;

2727

2725

Table Create Table

2728

2726

t2 CREATE TABLE `t2` (

2729

2727

`sub_a` datetime

2730

) ENGINE=InnoDB

2731

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

2728

) ENGINE=MyISAM

2729

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

2732

2730

SHOW CREATE TABLE t3;

2733

2731

Table Create Table

2734

2732

t3 CREATE TABLE `t3` (

2735

2733

`a` datetime

2736

) ENGINE=InnoDB

2734

) ENGINE=MyISAM

2737

2735

DROP TABLE t1,t2,t3;

2738

CREATE TABLE t1 (a int);

2736

CREATE TABLE t1 (a int) ENGINE=MyISAM;

2739

2737

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

2740

CREATE TABLE t2 (b int, c int);

2738

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

2741

2739

INSERT INTO t2 VALUES

2742

2740

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

2743

2741

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

2809

2807

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

2810

2808

ERROR 21000: Subquery returns more than 1 row

2811

2809

DROP TABLE t1,t2;

2812

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

2810

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

2813

2811

insert into t1 values(1.1);

2814

2812

insert into t1 values(2.2);

2815

2813

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

2819

2817

df

2820

2818

2.2

2821

2819

drop table t1;

2822

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

2820

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

2823

2821

insert into t1 values(1.1);

2824

2822

select 1.1 * exists(select * from t1);

2825

2823

1.1 * exists(select * from t1)

2827

2825

drop table t1;

2828

2826

CREATE TABLE t1 (

2829

2827

grp int default NULL,

2830

a decimal(10,2) default NULL);

2828

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

2831

2829

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

2832

2830

select * from t1;

2833

2831

grp a

2845

2843

2.00

2846

2844

4.00

2847

2845

drop table t1;

2848

CREATE table t1 ( c1 integer );

2846

CREATE table t1 ( c1 integer ) ENGINE=MyISAM;

2849

2847

INSERT INTO t1 VALUES ( 1 );

2850

2848

INSERT INTO t1 VALUES ( 2 );

2851

2849

INSERT INTO t1 VALUES ( 3 );

2852

CREATE TABLE t2 ( c2 integer );

2850

CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;

2853

2851

INSERT INTO t2 VALUES ( 1 );

2854

2852

INSERT INTO t2 VALUES ( 4 );

2855

2853

INSERT INTO t2 VALUES ( 5 );

2861

2859

c1 c2

2862

2860

1 1

2863

2861

DROP TABLE t1,t2;

2864

CREATE TABLE t1 ( c1 integer );

2862

CREATE TABLE t1 ( c1 integer ) ENGINE=MyISAM;

2865

2863

INSERT INTO t1 VALUES ( 1 );

2866

2864

INSERT INTO t1 VALUES ( 2 );

2867

2865

INSERT INTO t1 VALUES ( 3 );

2868

2866

INSERT INTO t1 VALUES ( 6 );

2869

CREATE TABLE t2 ( c2 integer );

2867

CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;

2870

2868

INSERT INTO t2 VALUES ( 1 );

2871

2869

INSERT INTO t2 VALUES ( 4 );

2872

2870

INSERT INTO t2 VALUES ( 5 );

2873

2871

INSERT INTO t2 VALUES ( 6 );

2874

CREATE TABLE t3 ( c3 integer );

2872

CREATE TABLE t3 ( c3 integer ) ENGINE=MyISAM;

2875

2873

INSERT INTO t3 VALUES ( 7 );

2876

2874

INSERT INTO t3 VALUES ( 8 );

2877

2875

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

2880

2878

2 NULL

2881

2879

3 NULL

2882

2880

DROP TABLE t1,t2,t3;

2883

CREATE TABLE t1 (EMPNUM CHAR(3));

2884

CREATE TABLE t2 (EMPNUM CHAR(3) );

2881

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

2882

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

2885

2883

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

2886

2884

INSERT INTO t2 VALUES ('E1');

2887

2885

DELETE FROM t1

2893

2891

EMPNUM

2894

2892

E1

2895

2893

DROP TABLE t1,t2;

2896

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);

2894

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

2897

2895

INSERT INTO t1 VALUES (1, 1);

2898

2896

CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,

2899

PRIMARY KEY(select_id,values_id));

2897

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

2900

2898

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

2901

2899

SELECT values_id FROM t1

2902

2900

WHERE values_id IN (SELECT values_id FROM t2

2914

2912

values_id

2915

2913

1

2916

2914

DROP TABLE t1, t2;

2917

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

2915

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

2918

2916

insert into t1 values ('1');

2919

2917

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

2920

2918

max(fld)

2921

2919

1

2922

2920

drop table t1;

2923

CREATE TABLE t1 (a int, b int);

2924

CREATE TABLE t2 (c int, d int);

2925

CREATE TABLE t3 (e int);

2921

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

2922

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

2923

CREATE TABLE t3 (e int) ENGINE=MyISAM;

2926

2924

INSERT INTO t1 VALUES

2927

2925

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

2928

2926

INSERT INTO t2 VALUES

3063

3061

3 20

3064

3062

4 40

3065

3063

DROP TABLE t1,t2,t3;

3066

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

3064

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

3067

3065

INSERT INTO t1 VALUES

3068

3066

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

3069

3067

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

3087

3085

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

3088

3086

id select_type table type possible_keys key key_len ref rows Extra

3089

3087

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3090

2 SUBQUERY t1 index NULL a 23 NULL 9

3088

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

3091

3089

DROP TABLE t1;

3092

create table t1( f1 int,f2 int);

3090

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

3093

3091

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

3094

3092

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

3095

3093

t

3096

3094

crash1

3097

3095

crash1

3098

3096

drop table t1;

3099

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

3097

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

3100

3098

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

3101

create table t2 (a int, b int);

3099

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

3102

3100

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

3103

3101

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

3104

3102

drop table t1, t2;

3105

create table t1 (i int, j bigint);

3103

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

3106

3104

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

3107

3105

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

3108

3106

min(i)

3109

3107

1

3110

3108

drop table t1;

3111

CREATE TABLE t1 (i BIGINT);

3109

CREATE TABLE t1 (i BIGINT) ENGINE=MyISAM;

3112

3110

INSERT INTO t1 VALUES (10000000000000000);

3113

3111

INSERT INTO t1 VALUES (1);

3114

CREATE TABLE t2 (i BIGINT);

3112

CREATE TABLE t2 (i BIGINT) ENGINE=MyISAM;

3115

3113

INSERT INTO t2 VALUES (10000000000000000);

3116

3114

INSERT INTO t2 VALUES (1);

3117

3115

/* simple test */

3132

3130

CREATE TABLE t1 (

3133

3131

id bigint NOT NULL auto_increment,

3134

3132

name varchar(255) NOT NULL,

3135

PRIMARY KEY (id)

3136

);

3133

PRIMARY KEY (id))

3134

ENGINE=MyISAM;

3137

3135

INSERT INTO t1 VALUES

3138

3136

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

3139

3137

CREATE TABLE t2 (

3140

3138

id bigint NOT NULL auto_increment,

3141

3139

mid bigint NOT NULL,

3142

3140

date date NOT NULL,

3143

PRIMARY KEY (id)

3144

);

3141

PRIMARY KEY (id))

3142

ENGINE=MyISAM;

3145

3143

INSERT INTO t2 VALUES

3146

3144

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

3147

3145

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

3178

3176

i1 int NOT NULL default '0',

3179

3177

i2 int NOT NULL default '0',

3180

3178

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

3181

PRIMARY KEY (i1,i2,t)

3182

);

3179

PRIMARY KEY (i1,i2,t))

3180

ENGINE=MyISAM;

3183

3181

INSERT INTO t1 VALUES

3184

3182

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

3185

3183

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

3191

3189

i1 int NOT NULL default '0',

3192

3190

i2 int NOT NULL default '0',

3193

3191

t datetime default NULL,

3194

PRIMARY KEY (i1)

3195

);

3192

PRIMARY KEY (i1))

3193

ENGINE=MyISAM;

3196

3194

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

3197

3195

EXPLAIN

3198

3196

SELECT * FROM t1,t2

3200

3198

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

3201

3199

ORDER BY t1.t DESC LIMIT 1);

3202

3200

id select_type table type possible_keys key key_len ref rows Extra

3203

1 PRIMARY t2 ALL NULL NULL NULL NULL 1

3204

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

3205

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

3201

1 PRIMARY t2 system NULL NULL NULL NULL 1

3202

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

3203

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

3206

3204

SELECT * FROM t1,t2

3207

3205

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

3208

3206

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

3210

3208

i1 i2 t i1 i2 t

3211

3209

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

3212

3210

DROP TABLE t1, t2;

3213

CREATE TABLE t1 (i INT);

3211

CREATE TABLE t1 (i INT) ENGINE=MyISAM;

3214

3212

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

3215

3213

i

3216

3214

SELECT * FROM t1 WHERE NOT EXISTS

3229

3227

explain select * from t1 where not exists

3230

3228

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

3231

3229

id select_type table type possible_keys key key_len ref rows Extra

3232

1 PRIMARY t1 ALL NULL NULL NULL NULL 1

3233

2 SUBQUERY t11 ALL NULL NULL NULL NULL 1

3234

3 UNION t12 ALL NULL NULL NULL NULL 1

3230

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

3231

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

3232

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

3235

3233

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

3236

3234

DROP TABLE t1;

3237

CREATE TABLE t1 (a INT, b INT);

3238

CREATE TABLE t2 (a INT);

3235

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

3236

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

3239

3237

INSERT INTO t2 values (1);

3240

3238

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

3241

3239

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

3274

3272

2

3275

3273

2

3276

3274

DROP TABLE t1,t2;

3277

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

3275

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

3278

3276

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

3279

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

3277

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

3280

3278

SET SESSION sort_buffer_size = 32 * 1024;

3281

3279

Warnings:

3282

3280

Warning 1292 Truncated incorrect sort_buffer_size value: '32768'

3292

3290

COUNT(*)

3293

3291

3000

3294

3292

DROP TABLE t2,t1;

3295

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

3296

CREATE TABLE t2 (c int);

3293

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

3294

CREATE TABLE t2 (c int) ENGINE=MyISAM;

3297

3295

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

3298

3296

INSERT INTO t2 VALUES (1);

3299

3297

SELECT * FROM t1

3313

3311

cc 3

3314

3312

dd 1

3315

3313

INSERT INTO t2 VALUES (2);

3316

CREATE TABLE t3 (c int);

3314

CREATE TABLE t3 (c int) ENGINE=MyISAM;

3317

3315

INSERT INTO t3 VALUES (1);

3318

3316

SELECT * FROM t1

3319

3317

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

3330

3328

DROP TABLE IF EXISTS t1xt2;

3331

3329

CREATE TABLE t1 (

3332

3330

id_1 int NOT NULL,

3333

t varchar(4) DEFAULT NULL

3334

);

3331

t varchar(4) DEFAULT NULL)

3332

ENGINE=MyISAM;

3335

3333

CREATE TABLE t2 (

3336

3334

id_2 int NOT NULL,

3337

t varchar(4) DEFAULT NULL

3338

);

3335

t varchar(4) DEFAULT NULL)

3336

ENGINE=MyISAM;

3339

3337

CREATE TABLE t1xt2 (

3340

3338

id_1 int NOT NULL,

3341

id_2 int NOT NULL

3342

);

3339

id_2 int NOT NULL)

3340

ENGINE=MyISAM;

3343

3341

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

3344

3342

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

3345

3343

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

3438

3436

DROP TABLE t1;

3439

3437

DROP TABLE t2;

3440

3438

DROP TABLE t1xt2;

3441

CREATE TABLE t1 (a int);

3439

CREATE TABLE t1 (a int) ENGINE=MyISAM;

3442

3440

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

3443

3441

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

3444

3442

col1 col2

3451

3449

this is a test. 1

3452

3450

this is a test. 2

3453

3451

DROP table t1;

3454

CREATE TABLE t1 (a int, b int);

3455

CREATE TABLE t2 (m int, n int);

3452

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

3453

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

3456

3454

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

3457

3455

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

3458

3456

SELECT COUNT(*), a,

3476

3474

2 2

3477

3475

3 3

3478

3476

DROP TABLE t1,t2;

3479

CREATE TABLE t1 (a int, b int);

3480

CREATE TABLE t2 (m int, n int);

3477

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

3478

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

3481

3479

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

3482

3480

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

3483

3481

SELECT COUNT(*) c, a,

3495

3493

3 3 4

3496

3494

1 4 2,2

3497

3495

DROP table t1,t2;

3498

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

3496

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

3499

3497

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

3500

3498

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

3501

3499

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

3560

3558

2 o

3561

3559

3 p

3562

3560

DROP TABLE t1;

3563

CREATE TABLE t1 (a INT);

3561

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

3564

3562

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

3565

CREATE TABLE t2 (x INT);

3563

CREATE TABLE t2 (x INT) ENGINE=MyISAM;

3566

3564

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

3567

3565

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

3568

3566

ERROR HY000: Invalid use of group function

3577

3575

FROM t1;

3578

3576

ERROR HY000: Invalid use of group function

3579

3577

DROP TABLE t1,t2;

3580

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

3578

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

3581

3579

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

3582

3580

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

3583

3581

id select_type table type possible_keys key key_len ref rows Extra

3584

3582

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

3585

3583

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

3586

3584

DROP TABLE t1;

3587

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

3585

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

3588

3586

INSERT INTO t1 VALUES

3589

3587

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

3590

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

3588

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

3591

3589

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

3592

3590

SELECT id, st FROM t1

3593

3591

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

3614

3612

2 GA

3615

3613

4 FL

3616

3614

DROP TABLE t1,t2;

3617

CREATE TABLE t1 (a int);

3615

CREATE TABLE t1 (a int) ENGINE=MyISAM;

3618

3616

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

3619

3617

EXPLAIN EXTENDED

3620

3618

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

3627

3625

CREATE TABLE t1 (

3628

3626

a varchar(255) default NULL,

3629

3627

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3630

INDEX idx(a,b)

3631

);

3632

Warnings:

3633

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

3628

INDEX idx(a,b))

3629

ENGINE=MyISAM;

3634

3630

CREATE TABLE t2 (

3635

a varchar(255) default NULL

3636

);

3631

a varchar(255) default NULL)

3632

ENGINE=MyISAM;

3637

3633

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

3638

3634

INSERT INTO t1 SELECT * FROM t1;

3639

3635

INSERT INTO t1 SELECT * FROM t1;

3654

3650

1

3655

3651

1

3656

3652

DROP TABLE t1,t2;

3657

CREATE TABLE t1 (a INTEGER, b INTEGER);

3658

CREATE TABLE t2 (x INTEGER);

3653

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

3654

CREATE TABLE t2 (x INTEGER) ENGINE=MyISAM;

3659

3655

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

3660

3656

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

3661

3657

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

3666

3662

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

3667

3663

3.3333

3668

3664

DROP TABLE t1,t2;

3669

CREATE TABLE t1 (a INT, b INT);

3665

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

3670

3666

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

3671

3667

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

3672

3668

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

3674

3670

a COUNT(*)

3675

3671

1 3

3676

3672

DROP TABLE t1;

3677

CREATE TABLE t1 (a INT);

3678

CREATE TABLE t2 (a INT);

3673

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

3674

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

3679

3675

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

3680

3676

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

3681

3677

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

3687

3683

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

3688

3684

3

3689

3685

DROP TABLE t1,t2;

3690

CREATE TABLE t1 (a1 INT, a2 INT);

3691

CREATE TABLE t2 (b1 INT, b2 INT);

3686

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

3687

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

3692

3688

INSERT INTO t1 VALUES (100, 200);

3693

3689

INSERT INTO t1 VALUES (101, 201);

3694

3690

INSERT INTO t2 VALUES (101, 201);

3698

3694

0

3699

3695

0

3700

3696

DROP TABLE t1, t2;

3701

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

3697

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

3702

3698

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

3703

3699

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

3704

3700

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

3708

3704

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

3709

3705

id select_type table type possible_keys key key_len ref rows Extra

3710

3706

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 where

3707

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

3712

3708

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

3713

3709

a b

3714

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

3710

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

3715

3711

INSERT INTO t2 SELECT * FROM t1;

3716

3712

CREATE INDEX I1 ON t2 (a);

3717

3713

CREATE INDEX I2 ON t2 (b);

3718

3714

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

3719

3715

id select_type table type possible_keys key key_len ref rows Extra

3720

3716

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 where

3717

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

3722

3718

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

3723

3719

a b

3724

3720

EXPLAIN

3725

3721

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

3726

3722

id select_type table type possible_keys key key_len ref rows Extra

3727

3723

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 where

3724

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

3729

3725

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

3730

3726

a b

3731

3727

DROP TABLE t1,t2;

3732

CREATE TABLE t1(a INT, b INT);

3728

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

3733

3729

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

3734

3730

EXPLAIN

3735

3731

SELECT a AS out_a, MIN(b) FROM t1

3754

3750

1 2

3755

3751

2 4

3756

3752

DROP TABLE t1;

3757

CREATE TABLE t1 (a INT);

3758

CREATE TABLE t2 (a INT);

3753

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

3754

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

3759

3755

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

3760

3756

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

3761

3757

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

3786

3782

CREATE TABLE t4 (

3787

3783

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

3788

3784

f10 varchar(32) collate utf8_bin default NULL,

3789

PRIMARY KEY (f7)

3790

);

3785

PRIMARY KEY (f7))

3786

ENGINE=MyISAM;

3791

3787

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

3792

3788

CREATE TABLE t2 (

3793

3789

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

3794

3790

f2 varchar(50) collate utf8_bin default NULL,

3795

3791

f3 varchar(10) collate utf8_bin default NULL,

3796

3792

PRIMARY KEY (f4),

3797

UNIQUE KEY uk1 (f2)

3798

);

3793

UNIQUE KEY uk1 (f2))

3794

ENGINE=MyISAM;

3799

3795

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

3800

CREATE TABLE t1 (

3796

CREATE TABLE t1 (

3801

3797

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

3802

3798

f1 varchar(10) collate utf8_bin default NULL,

3803

3799

f9 varchar(32) collate utf8_bin default NULL,

3804

PRIMARY KEY (f8)

3805

);

3800

PRIMARY KEY (f8))

3801

ENGINE=MyISAM;

3806

3802

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

3807

3803

CREATE TABLE t3 (

3808

3804

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

3809

3805

f5 varchar(50) collate utf8_bin default NULL,

3810

PRIMARY KEY (f6)

3811

);

3806

PRIMARY KEY (f6))

3807

ENGINE=MyISAM;

3812

3808

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

3813

3809

SELECT

3814

3810

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

3833

3829

DROP TABLE t1, t2, t3, t4;

3834

3830

End of 5.0 tests.

3835

3831

create table t_out (subcase char(3),

3836

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

3837

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

3832

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

3833

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

3838

3834

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

3839

3835

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

3840

3836

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

4029

4025

T

4030

4026

drop table t_out;

4031

4027

drop table t_in;

4032

CREATE TABLE t1 (s1 char(1));

4028

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

4033

4029

INSERT INTO t1 VALUES ('a');

4034

4030

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

4035

4031

s1

4036

4032

a

4037

4033

DROP TABLE t1;

4038

CREATE TABLE t1( a INT );

4034

CREATE TABLE t1( a INT ) ENGINE=MyISAM;

4039

4035

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

4040

CREATE TABLE t2( a INT, b INT );

4036

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

4041

4037

SELECT *

4042

4038

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

4043

4039

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