~drizzle-trunk/drizzle/development : revision 754.1.1

53

54

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

55

56

create table t1 (a int);

57

create table t2 (a int, b int);

58

create table t3 (a int);

59

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

56

create table t1 (a int) ENGINE=MyISAM;

57

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

58

create table t3 (a int) ENGINE=MyISAM;

59

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

60

insert into t1 values (2);

61

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

62

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

111

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

112

delete from t2 where a=2 and b=10;

113

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

114

create table t5 (a int);

114

create table t5 (a int) ENGINE=MyISAM;

115

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

116

insert into t5 values (5);

117

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

120

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;

121

-- error 1242

122

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

123

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

124

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

123

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

124

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

125

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

126

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

127

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

134

# different tipes & group functions

135

drop table t1,t2,t3;

136

137

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

137

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

138

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

139

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

139

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

140

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

141

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

141

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

142

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

143

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

144

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

245

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

246

drop table t1;

247

248

create table t1 (a int);

248

create table t1 (a int) ENGINE=MyISAM;

249

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

250

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

251

drop table t1;

252

253

#iftest

254

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

254

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

255

INSERT INTO t1 VALUES ();

256

-- error 1242

257

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

278

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

279

drop table t1;

280

281

CREATE TABLE t1 (a int);

281

CREATE TABLE t1 (a int) ENGINE=MyISAM;

282

INSERT INTO t1 VALUES (1);

283

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

284

drop table t1;

285

286

#update with subselects

287

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

288

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

287

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

288

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

289

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

290

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

291

select * from t1;

298

drop table t1, t2;

299

300

#delete with subselects

301

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

302

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

301

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

302

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

303

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

304

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

305

select * from t1;

314

315

#multi-delete with subselects

316

317

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

318

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

319

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

317

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

318

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

319

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

320

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

321

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

322

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

334

drop table t11, t12, t2;

335

336

#insert with subselects

337

CREATE TABLE t1 (x int);

338

create table t2 (a int);

339

create table t3 (b int);

337

CREATE TABLE t1 (x int) ENGINE=MyISAM;

338

create table t2 (a int) ENGINE=MyISAM;

339

create table t3 (b int) ENGINE=MyISAM;

340

insert into t2 values (1);

341

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

342

-- error 1093

366

drop table t1, t2, t3;

367

368

#replace with subselects

369

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

370

create table t2 (a int);

371

create table t3 (a int);

369

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

370

create table t2 (a int) ENGINE=MyISAM;

371

create table t3 (a int) ENGINE=MyISAM;

372

insert into t2 values (1);

373

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

374

select * from t1;

417

drop table t2, t1;

418

419

#NULL test

420

create table t1 (a int);

420

create table t1 (a int) ENGINE=MyISAM;

421

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

422

select 1 IN (SELECT * from t1);

423

select 10 IN (SELECT * from t1);

431

select 1 > ANY (SELECT * from t1);

432

select 10 > ANY (SELECT * from t1);

433

drop table t1;

434

create table t1 (a varchar(20));

434

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

435

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

436

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

437

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

445

select 'A' > ANY (SELECT * from t1);

446

select 'XYZS' > ANY (SELECT * from t1);

447

drop table t1;

448

create table t1 (a float);

448

create table t1 (a float) ENGINE=MyISAM;

449

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

450

select 1.5 IN (SELECT * from t1);

451

select 10.5 IN (SELECT * from t1);

467

# Null with keys

468

#

469

470

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

471

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

470

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

471

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

472

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

473

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

474

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

475

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

476

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

476

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

477

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

478

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

479

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

480

drop table t1,t2,t3;

481

482

#LIMIT is not supported now

483

#create table t1 (a float);

483

#create table t1 (a float) ENGINE=MyISAM;

484

#-- error 1235

485

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

486

#-- error 1235

487

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

488

#drop table t1;

489

#

490

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

491

#create table t2 (a int);

490

#create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;

491

#create table t2 (a int) ENGINE=MyISAM;

492

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

493

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

494

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

496

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

497

#drop table t1,t2;

498

#

499

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

499

#create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;

500

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

501

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

502

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

512

#drop table t1;

513

514

#test of uncacheable subqueries

515

CREATE TABLE t1 (a int);

515

CREATE TABLE t1 (a int) ENGINE=MyISAM;

516

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

517

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

518

drop table t1;

560

drop table t1, t2, t3;

561

562

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

563

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

564

SHOW CREATE TABLE t1;

565

drop table t1;

566

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

567

SHOW CREATE TABLE t1;

568

drop table t1;

569

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

570

SHOW CREATE TABLE t1;

571

drop table t1;

572

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

563

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

564

SHOW CREATE TABLE t1;

565

drop table t1;

566

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

567

SHOW CREATE TABLE t1;

568

drop table t1;

569

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

570

SHOW CREATE TABLE t1;

571

drop table t1;

572

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

573

select * from t1;

574

SHOW CREATE TABLE t1;

575

drop table t1;

576

577

create table t1 (a int);

577

create table t1 (a int) ENGINE=MyISAM;

578

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

579

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

580

from t1;

679

#

680

# correct NULL in <CONSTANT> IN (SELECT ...)

681

#

682

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

682

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

683

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

684

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

685

drop table t1;

687

#

688

# Test optimization for sub selects

689

#

690

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

690

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

691

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

692

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

693

drop table t1;

709

#

710

# uninterruptable update

711

#

712

create table t1 (a int, b int);

713

create table t2 (a int, b int);

712

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

713

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

714

715

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

716

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

741

#

742

# IN subselect optimization test

743

#

744

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

745

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

746

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

744

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

745

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

746

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

747

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

748

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

749

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

754

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

755

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

756

drop table t1, t2, t3;

757

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

758

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

759

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

757

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

758

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

759

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

760

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

761

disable_query_log;

762

# making table large enough

788

#

789

# alloc_group_fields() working

790

#

791

create table t1 (a int, b int);

792

create table t2 (a int, b int);

793

create table t3 (a int, b int);

791

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

792

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

793

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

794

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

795

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

796

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

800

#

801

# aggregate functions in HAVING test

802

#

803

create table t1 (s1 int);

804

create table t2 (s1 int);

803

create table t1 (s1 int) ENGINE=MyISAM;

804

create table t2 (s1 int) ENGINE=MyISAM;

805

insert into t1 values (1);

806

insert into t2 values (1);

807

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

811

# update subquery with wrong field (to force name resolving

812

# in UPDATE name space)

813

#

814

create table t1 (s1 int);

815

create table t2 (s1 int);

814

create table t1 (s1 int) ENGINE=MyISAM;

815

create table t2 (s1 int) ENGINE=MyISAM;

816

insert into t1 values (1);

817

insert into t2 values (1);

818

-- error 1054

823

# collation test

824

#

825

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

826

# s2 CHAR(5) COLLATE latin1_swedish_ci);

826

# s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;

827

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

828

#-- error 1267

829

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

834

#

835

# aggregate functions reinitialization

836

#

837

create table t1(toid int,rd int);

838

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

837

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

838

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

839

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

840

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

841

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

844

#

845

# row union

846

#

847

create table t1 (s1 char(5));

847

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

848

-- error 1241

849

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

850

insert into t1 values ('tttt');

856

#

857

# IN optimisation test results

858

#

859

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

860

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

859

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

860

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

861

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

862

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

863

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

873

#

874

# correct ALL optimisation

875

#

876

create table t2 (a int, b int);

877

create table t3 (a int);

876

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

877

create table t3 (a int) ENGINE=MyISAM;

878

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

879

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

880

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

930

#

931

# optimized ALL/ANY with union

932

#

933

create table t1 (s1 char);

933

create table t1 (s1 char) ENGINE=MyISAM;

934

insert into t1 values ('e');

935

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

936

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

950

#

951

# unresolved field error

952

#

953

create table t1 (s1 int);

954

create table t2 (s1 int);

953

create table t1 (s1 int) ENGINE=MyISAM;

954

create table t2 (s1 int) ENGINE=MyISAM;

955

-- error 1054

956

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

957

-- error 1054

963

#

964

# fix_fields() in add_ref_to_table_cond()

965

#

966

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

967

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

966

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

967

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

968

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

969

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

970

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

971

DROP TABLE t1, t2;

972

973

CREATE TABLE t1 (a int);

973

CREATE TABLE t1 (a int) ENGINE=MyISAM;

974

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

975

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

976

DROP TABLE t1;

989

`bis` int NOT NULL default '0',

990

PRIMARY KEY (`id`),

991

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

992

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

993

);

992

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

993

ENGINE=MyISAM;

994

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

995

CREATE TABLE `t2` (

996

`id` int NOT NULL auto_increment,

997

`max_anno_dep` int NOT NULL default '0',

998

PRIMARY KEY (`id`)

999

);

998

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

1000

999

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

1001

1000

1002

1001

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;

1006

1005

#

1007

1006

# GLOBAL LIMIT

1008

1007

#

1009

create table t1 (a int);

1008

create table t1 (a int) ENGINE=MyISAM;

1010

1009

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

1011

1010

SET SQL_SELECT_LIMIT=1;

1012

1011

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

1018

1017

# Bug #3118: subselect + order by

1019

1018

#

1020

1019

1021

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

1020

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

1022

1021

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

1023

1022

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

1024

1023

DROP TABLE t1;

1025

1024

1026

1025

# Item_cond fix field

1027

1026

#

1028

create table t1(val varchar(10));

1027

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

1029

1028

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

1030

1029

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

1031

1030

drop table t1;

1033

1032

#

1034

1033

# ref_or_null replacing with ref

1035

1034

#

1036

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

1035

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

1037

1036

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

1038

1037

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

1039

1038

select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);

1040

1039

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

1041

1040

explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);

1042

1041

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

1043

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

1042

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

1044

1043

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

1045

1044

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

1046

1045

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

1049

1048

#

1050

1049

# Static tables & rund() in subqueries

1051

1050

#

1052

create table t1 (a int);

1051

create table t1 (a int) ENGINE=MyISAM;

1053

1052

insert into t1 values (1);

1054

1053

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

1055

1054

drop table t1;

1057

1056

#

1058

1057

# bug 3188

1059

1058

#

1060

create table t1(id int);

1061

create table t2(id int);

1062

create table t3(flag int);

1059

create table t1(id int) ENGINE=MyISAM;

1060

create table t2(id int) ENGINE=MyISAM;

1061

create table t3(flag int) ENGINE=MyISAM;

1063

1062

-- error 1064

1064

1063

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

1065

1064

drop table t1,t2,t3;

1067

1066

#

1068

1067

# aggregate functions (Bug #3505)

1069

1068

#

1070

CREATE TABLE t1 (id INT);

1071

CREATE TABLE t2 (id INT);

1069

CREATE TABLE t1 (id INT) ENGINE=MyISAM;

1070

CREATE TABLE t2 (id INT) ENGINE=MyISAM;

1072

1071

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

1073

1072

INSERT INTO t2 VALUES (1);

1074

1073

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

1080

1079

#

1081

1080

# ALL/ANY test

1082

1081

#

1083

CREATE TABLE t1 ( a int, b int );

1082

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

1084

1083

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

1085

1084

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

1086

1085

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

1215

1214

SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;

1216

1215

SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;

1217

1216

DROP TABLE t1;

1218

CREATE TABLE t1 ( a double, b double );

1217

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

1219

1218

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

1220

1219

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

1221

1220

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

1230

1229

SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);

1231

1230

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

1232

1231

DROP TABLE t1;

1233

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

1232

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

1234

1233

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

1235

1234

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

1236

1235

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

1250

1249

#

1251

1250

# SELECT(EXISTS * ...)optimisation

1252

1251

#

1253

create table t1 (a int, b int);

1252

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

1254

1253

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

1255

1254

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

1256

1255

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

1260

1259

# Bug #4102: subselect in HAVING

1261

1260

#

1262

1261

1263

CREATE TABLE t1 (t1_a int);

1262

CREATE TABLE t1 (t1_a int) ENGINE=MyISAM;

1264

1263

INSERT INTO t1 VALUES (1);

1265

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

1264

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

1266

1265

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

1267

1266

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

1268

1267

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

1272

1271

# Test problem with NULL and derived tables (Bug #4097)

1273

1272

#

1274

1273

1275

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

1274

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

1276

1275

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

1277

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

1276

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

1278

1277

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

1279

1278

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

1280

1279

drop table t1,t2;

1292

1291

#

1293

1292

# BUG#5003 - like in subselect

1294

1293

#

1295

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

1294

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;

1296

1295

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

1297

1296

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

1298

1297

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

1304

1303

#

1305

1304

# Optimized IN with compound index

1306

1305

#

1307

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

1308

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

1306

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

1307

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

1309

1308

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

1310

1309

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

1311

1310

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

1320

1319

#

1321

1320

# resolving fields of grouped outer SELECT

1322

1321

#

1323

CREATE TABLE t1 (howmanyvalues bigint, avalue int);

1322

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

1324

1323

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

1325

1324

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

1326

1325

SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;

1330

1329

SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;

1331

1330

drop table t1;

1332

1331

1333

create table t1 (x int);

1332

create table t1 (x int) ENGINE=MyISAM;

1334

1333

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;

1335

1334

drop table t1;

1336

1335

1338

1337

# Test of correct maybe_null flag returning by subquwery for temporary table

1339

1338

# creation

1340

1339

#

1341

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

1340

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;

1342

1341

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

1343

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

1342

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 ;

1344

1343

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

1345

1344

-- error 1054

1346

1345

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;

1357

1356

#

1358

1357

# primary query with temporary table and subquery with groupping

1359

1358

#

1360

create table t1 (a int, b int);

1361

create table t2 (a int, b int);

1359

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

1360

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

1362

1361

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

1363

1362

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

1364

1363

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

1367

1366

#

1368

1367

# Equal operation under row and empty subquery

1369

1368

#

1370

create table t1 (s1 int,s2 int);

1369

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

1371

1370

insert into t1 values (20,15);

1372

1371

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

1373

1372

drop table t1;

1375

1374

#

1376

1375

# ALL/ANY with NULL

1377

1376

#

1378

create table t1 (s1 int);

1377

create table t1 (s1 int) ENGINE=MyISAM;

1379

1378

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

1380

1379

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

1381

1380

select s1, s1 < all (select s1 from t1) from t1;

1415

1414

# Test for BUG#7885: Server crash when 'any' subselect compared to

1416

1415

# non-existant field.

1417

1416

#

1418

create table t1 (a1 int);

1419

create table t2 (b1 int);

1417

create table t1 (a1 int) ENGINE=MyISAM;

1418

create table t2 (b1 int) ENGINE=MyISAM;

1420

1419

--error 1054

1421

1420

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

1422

1421

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

1426

1425

#

1427

1426

# Comparison subquery with * and row

1428

1427

#

1429

create table t1 (a integer, b integer);

1428

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

1430

1429

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

1431

1430

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

1432

1431

# queries whih can be converted to IN

1437

1436

#

1438

1437

# Comparison subquery and row with nested rows

1439

1438

#

1440

create table t1 (a integer, b integer);

1439

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

1441

1440

-- error 1241

1442

1441

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

1443

1442

-- error 1241

1470

1469

endDate datetime NOT NULL,

1471

1470

createDate datetime NOT NULL,

1472

1471

modifyDate timestamp NOT NULL,

1473

attributes text NOT NULL

1474

);

1472

attributes text NOT NULL)

1473

engine=myisam;

1474

1475

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

1476

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

1477

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

1485

CREATE TABLE t2 (

1486

userId int NOT NULL,

1487

courseId int NOT NULL,

1488

date datetime NOT NULL

1489

);

1488

date datetime NOT NULL)

1489

engine=myisam;

1490

1491

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

1491

1492

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

1492

1493

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

1502

1503

endDate datetime NOT NULL,

1503

1504

createDate datetime NOT NULL,

1504

1505

modifyDate timestamp NOT NULL,

1505

ordering int

1506

);

1506

ordering int)

1507

engine=myisam;

1508

1507

1509

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

1508

1510

1509

1511

CREATE TABLE t4 (

1514

1516

ordering int,

1515

1517

description text,

1516

1518

createDate datetime NOT NULL,

1517

modifyDate timestamp NOT NULL

1518

);

1519

modifyDate timestamp NOT NULL)

1520

engine=myisam;

1521

1519

1522

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

1520

1523

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

1521

1524

1523

1526

userId int NOT NULL,

1524

1527

groupId int NOT NULL,

1525

1528

createDate datetime NOT NULL,

1526

modifyDate timestamp NOT NULL

1527

);

1529

modifyDate timestamp NOT NULL) ENGINE=MyISAM;

1530

1528

1531

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

1529

1532

1530

1533

select

1564

1567

#

1565

1568

# Transformation in left expression of subquery (BUG#8888)

1566

1569

#

1567

create table t1 (a int);

1570

create table t1 (a int) ENGINE=MyISAM;

1568

1571

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

1569

1572

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

1570

1573

drop table t1;

1572

1575

#

1573

1576

# single row subqueries and row operations (code covarage improvement)

1574

1577

#

1575

create table t1 (a int, b int);

1578

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

1576

1579

insert into t1 values (1,2);

1577

1580

-- error 1241

1578

1581

select 1 = (select * from t1);

1612

1615

1613

1616

# BUG#11821 : Select from subselect using aggregate function on an enum

1614

1617

# segfaults:

1615

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

1618

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

1616

1619

insert into t1 values ('1');

1617

1620

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

1618

1621

drop table t1;

1621

1624

# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)

1622

1625

#

1623

1626

1624

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

1625

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

1627

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

1628

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

1626

1629

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

1627

1630

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

1628

1631

1652

1655

# Bug #12392: where cond with IN predicate for rows and NULL values in table

1653

1656

#

1654

1657

1655

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

1658

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

1656

1659

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

1657

1660

1658

1661

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

1663

1666

# Bug #11479: subquery over left join with an empty inner table

1664

1667

#

1665

1668

1666

CREATE TABLE t1 (a int);

1667

CREATE TABLE t2 (a int, b int);

1668

CREATE TABLE t3 (b int NOT NULL);

1669

CREATE TABLE t1 (a int) ENGINE=MyISAM;

1670

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

1671

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

1669

1672

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

1670

1673

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

1671

1674

1681

1684

# Bug#18503: Queries with a quantified subquery returning empty set may

1682

1685

# return a wrong result.

1683

1686

#

1684

CREATE TABLE t1 (f1 INT);

1685

CREATE TABLE t2 (f2 INT);

1687

CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;

1688

CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;

1686

1689

INSERT INTO t1 VALUES (1);

1687

1690

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

1688

1691

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

1692

1695

DROP TABLE t1, t2;

1693

1696

1694

1697

# BUG#20975 Wrong query results for subqueries within NOT

1695

create table t1 (s1 char);

1698

create table t1 (s1 char) ENGINE=MyISAM;

1696

1699

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

1697

1700

1698

1701

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

1715

1718

retailerID varchar(8) NOT NULL,

1716

1719

statusID int NOT NULL,

1717

1720

changed datetime NOT NULL,

1718

UNIQUE KEY retailerID (retailerID, statusID, changed)

1719

);

1721

UNIQUE KEY retailerID (retailerID, statusID, changed))

1722

ENGINE=MyISAM;

1720

1723

1721

1724

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

1722

1725

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

1735

1738

# Bug #21180: Subselect with index for both WHERE and ORDER BY

1736

1739

# produces empty result

1737

1740

#

1738

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

1741

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

1739

1742

insert into t1 values (10);

1740

1743

1741

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

1744

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

1742

1745

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

1743

1746

1744

1747

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

1766

1769

field1 int NOT NULL,

1767

1770

field2 int NOT NULL,

1768

1771

field3 int NOT NULL,

1769

PRIMARY KEY (field1,field2,field3)

1770

);

1772

PRIMARY KEY (field1,field2,field3))

1773

ENGINE=MyISAM;

1774

1771

1775

CREATE TABLE t2 (

1772

1776

fieldA int NOT NULL,

1773

1777

fieldB int NOT NULL,

1774

PRIMARY KEY (fieldA,fieldB)

1775

);

1778

PRIMARY KEY (fieldA,fieldB))

1779

ENGINE=MyISAM;

1776

1780

1777

1781

INSERT INTO t1 VALUES

1778

1782

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

1799

1803

# with possible NULL values by index access from the outer query

1800

1804

#

1801

1805

1802

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

1806

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

1803

1807

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

1804

1808

INSERT INTO t1 VALUES (NULL);

1805

1809

1806

CREATE TABLE t2(a int);

1810

CREATE TABLE t2(a int) ENGINE=MyISAM;

1807

1811

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

1808

1812

1809

1813

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

1814

1818

#

1815

1819

# Bug #11302: getObject() returns a String for a sub-query of type datetime

1816

1820

#

1817

CREATE TABLE t1 (a DATETIME);

1821

CREATE TABLE t1 (a DATETIME) ENGINE=MyISAM;

1818

1822

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

1819

1823

1820

CREATE TABLE t2 AS SELECT

1824

CREATE TABLE t2 ENGINE=MyISAM AS SELECT

1821

1825

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

1822

1826

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

1823

1827

SHOW CREATE TABLE t2;

1824

1828

1825

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

1829

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

1826

1830

SHOW CREATE TABLE t3;

1827

1831

1828

1832

DROP TABLE t1,t2,t3;

1832

1836

# that return more than one row

1833

1837

#

1834

1838

1835

CREATE TABLE t1 (a int);

1839

CREATE TABLE t1 (a int) ENGINE=MyISAM;

1836

1840

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

1837

1841

1838

CREATE TABLE t2 (b int, c int);

1842

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

1839

1843

INSERT INTO t2 VALUES

1840

1844

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

1841

1845

1890

1894

#

1891

1895

#decimal-related tests

1892

1896

#

1893

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

1897

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

1894

1898

insert into t1 values(1.1);

1895

1899

insert into t1 values(2.2);

1896

1900

1898

1902

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

1899

1903

drop table t1;

1900

1904

1901

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

1905

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

1902

1906

insert into t1 values(1.1);

1903

1907

select 1.1 * exists(select * from t1);

1904

1908

drop table t1;

1905

1909

1906

1910

CREATE TABLE t1 (

1907

1911

grp int default NULL,

1908

a decimal(10,2) default NULL);

1912

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

1909

1913

1910

1914

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

1911

1915

select * from t1;

1916

1920

# Test for bug #9338: lame substitution of c1 instead of c2

1917

1921

#

1918

1922

1919

CREATE table t1 ( c1 integer );

1923

CREATE table t1 ( c1 integer ) ENGINE=MyISAM;

1920

1924

INSERT INTO t1 VALUES ( 1 );

1921

1925

INSERT INTO t1 VALUES ( 2 );

1922

1926

INSERT INTO t1 VALUES ( 3 );

1923

1927

1924

CREATE TABLE t2 ( c2 integer );

1928

CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;

1925

1929

INSERT INTO t2 VALUES ( 1 );

1926

1930

INSERT INTO t2 VALUES ( 4 );

1927

1931

INSERT INTO t2 VALUES ( 5 );

1936

1940

#

1937

1941

# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ

1938

1942

#

1939

CREATE TABLE t1 ( c1 integer );

1943

CREATE TABLE t1 ( c1 integer ) ENGINE=MyISAM;

1940

1944

INSERT INTO t1 VALUES ( 1 );

1941

1945

INSERT INTO t1 VALUES ( 2 );

1942

1946

INSERT INTO t1 VALUES ( 3 );

1943

1947

INSERT INTO t1 VALUES ( 6 );

1944

1948

1945

CREATE TABLE t2 ( c2 integer );

1949

CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;

1946

1950

INSERT INTO t2 VALUES ( 1 );

1947

1951

INSERT INTO t2 VALUES ( 4 );

1948

1952

INSERT INTO t2 VALUES ( 5 );

1949

1953

INSERT INTO t2 VALUES ( 6 );

1950

1954

1951

CREATE TABLE t3 ( c3 integer );

1955

CREATE TABLE t3 ( c3 integer ) ENGINE=MyISAM;

1952

1956

INSERT INTO t3 VALUES ( 7 );

1953

1957

INSERT INTO t3 VALUES ( 8 );

1954

1958

1961

1965

# Correct building of equal fields list (do not include outer

1962

1966

# fields) (BUG#6384)

1963

1967

#

1964

CREATE TABLE t1 (EMPNUM CHAR(3));

1965

CREATE TABLE t2 (EMPNUM CHAR(3) );

1968

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

1969

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

1966

1970

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

1967

1971

INSERT INTO t2 VALUES ('E1');

1968

1972

DELETE FROM t1

1977

1981

# Test for bug #11487: range access in a subquery

1978

1982

#

1979

1983

1980

CREATE TABLE t1(select_id BIGINT, values_id BIGINT);

1984

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

1981

1985

INSERT INTO t1 VALUES (1, 1);

1982

1986

CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,

1983

PRIMARY KEY(select_id,values_id));

1987

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

1984

1988

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

1985

1989

1986

1990

SELECT values_id FROM t1

1997

2001

1998

2002

# BUG#11821 : Select from subselect using aggregate function on an enum

1999

2003

# segfaults:

2000

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

2004

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

2001

2005

insert into t1 values ('1');

2002

2006

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

2003

2007

drop table t1;

2006

2010

# Test for bug #11762: subquery with an aggregate function in HAVING

2007

2011

#

2008

2012

2009

CREATE TABLE t1 (a int, b int);

2010

CREATE TABLE t2 (c int, d int);

2011

CREATE TABLE t3 (e int);

2013

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

2014

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

2015

CREATE TABLE t3 (e int) ENGINE=MyISAM;

2012

2016

2013

2017

INSERT INTO t1 VALUES

2014

2018

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

2090

2094

# Test for bug #16603: GROUP BY in a row subquery with a quantifier

2091

2095

# when an index is defined on the grouping field

2092

2096

2093

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

2097

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

2094

2098

INSERT INTO t1 VALUES

2095

2099

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

2096

2100

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

2110

2114

#

2111

2115

# Bug#17366: Unchecked Item_int results in server crash

2112

2116

#

2113

create table t1( f1 int,f2 int);

2117

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

2114

2118

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

2115

2119

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

2116

2120

drop table t1;

2119

2123

# Bug #18306: server crash on delete using subquery.

2120

2124

#

2121

2125

2122

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

2126

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

2123

2127

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

2124

create table t2 (a int, b int);

2128

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

2125

2129

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

2126

2130

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

2127

2131

drop table t1, t2;

2129

2133

#

2130

2134

# Bug#19077: A nested materialized derived table is used before being populated.

2131

2135

#

2132

create table t1 (i int, j bigint);

2136

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

2133

2137

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

2134

2138

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

2135

2139

drop table t1;

2137

2141

#

2138

2142

# Bug#19700: subselect returning BIGINT always returned it as SIGNED

2139

2143

#

2140

CREATE TABLE t1 (i BIGINT);

2144

CREATE TABLE t1 (i BIGINT) ENGINE=MyISAM;

2141

2145

INSERT INTO t1 VALUES (10000000000000000); # > MAX SIGNED BIGINT 9323372036854775807

2142

2146

INSERT INTO t1 VALUES (1);

2143

2147

2144

CREATE TABLE t2 (i BIGINT);

2148

CREATE TABLE t2 (i BIGINT) ENGINE=MyISAM;

2145

2149

INSERT INTO t2 VALUES (10000000000000000); # same as first table

2146

2150

INSERT INTO t2 VALUES (1);

2147

2151

2164

2168

CREATE TABLE t1 (

2165

2169

id bigint NOT NULL auto_increment,

2166

2170

name varchar(255) NOT NULL,

2167

PRIMARY KEY (id)

2168

);

2171

PRIMARY KEY (id))

2172

ENGINE=MyISAM;

2173

2169

2174

INSERT INTO t1 VALUES

2170

2175

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

2171

2176

2173

2178

id bigint NOT NULL auto_increment,

2174

2179

mid bigint NOT NULL,

2175

2180

date date NOT NULL,

2176

PRIMARY KEY (id)

2177

);

2181

PRIMARY KEY (id))

2182

ENGINE=MyISAM;

2183

2178

2184

INSERT INTO t2 VALUES

2179

2185

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

2180

2186

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

2205

2211

i1 int NOT NULL default '0',

2206

2212

i2 int NOT NULL default '0',

2207

2213

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

2208

PRIMARY KEY (i1,i2,t)

2209

);

2214

PRIMARY KEY (i1,i2,t))

2215

ENGINE=MyISAM;

2216

2210

2217

INSERT INTO t1 VALUES

2211

2218

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

2212

2219

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

2219

2226

i1 int NOT NULL default '0',

2220

2227

i2 int NOT NULL default '0',

2221

2228

t datetime default NULL,

2222

PRIMARY KEY (i1)

2223

);

2229

PRIMARY KEY (i1))

2230

ENGINE=MyISAM;

2231

2224

2232

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

2225

2233

2226

2234

EXPLAIN

2239

2247

# Bug#14654 : Cannot select from the same table twice within a UNION

2240

2248

# statement

2241

2249

#

2242

CREATE TABLE t1 (i INT);

2250

CREATE TABLE t1 (i INT) ENGINE=MyISAM;

2243

2251

2244

2252

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

2245

2253

SELECT * FROM t1 WHERE NOT EXISTS

2266

2274

#

2267

2275

# Bug #21540: Subqueries with no from and aggregate functions return

2268

2276

# wrong results

2269

CREATE TABLE t1 (a INT, b INT);

2270

CREATE TABLE t2 (a INT);

2277

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

2278

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

2271

2279

INSERT INTO t2 values (1);

2272

2280

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

2273

2281

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

2290

2298

# slow with big sort_buffer_size

2291

2299

#

2292

2300

2293

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

2301

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

2294

2302

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

2295

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

2303

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

2296

2304

disable_query_log;

2297

2305

set autocommit=0;

2298

2306

begin;

2330

2338

# correlated and uncorrelated selects

2331

2339

#

2332

2340

2333

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

2334

CREATE TABLE t2 (c int);

2341

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

2342

CREATE TABLE t2 (c int) ENGINE=MyISAM;

2335

2343

2336

2344

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

2337

2345

INSERT INTO t2 VALUES (1);

2349

2357

SELECT c from t2 WHERE c=t1.c);

2350

2358

2351

2359

INSERT INTO t2 VALUES (2);

2352

CREATE TABLE t3 (c int);

2360

CREATE TABLE t3 (c int) ENGINE=MyISAM;

2353

2361

INSERT INTO t3 VALUES (1);

2354

2362

2355

2363

SELECT * FROM t1

2371

2379

2372

2380

CREATE TABLE t1 (

2373

2381

id_1 int NOT NULL,

2374

t varchar(4) DEFAULT NULL

2375

);

2382

t varchar(4) DEFAULT NULL)

2383

ENGINE=MyISAM;

2376

2384

2377

2385

CREATE TABLE t2 (

2378

2386

id_2 int NOT NULL,

2379

t varchar(4) DEFAULT NULL

2380

);

2387

t varchar(4) DEFAULT NULL)

2388

ENGINE=MyISAM;

2381

2389

2382

2390

CREATE TABLE t1xt2 (

2383

2391

id_1 int NOT NULL,

2384

id_2 int NOT NULL

2385

);

2392

id_2 int NOT NULL)

2393

ENGINE=MyISAM;

2386

2394

2387

2395

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

2388

2396

2462

2470

# Bug #26728: derived table with concatanation of literals in select list

2463

2471

#

2464

2472

2465

CREATE TABLE t1 (a int);

2473

CREATE TABLE t1 (a int) ENGINE=MyISAM;

2466

2474

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

2467

2475

2468

2476

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

2474

2482

# Bug #27257: COUNT(*) aggregated in outer query

2475

2483

#

2476

2484

2477

CREATE TABLE t1 (a int, b int);

2478

CREATE TABLE t2 (m int, n int);

2485

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

2486

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

2479

2487

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

2480

2488

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

2481

2489

2497

2505

# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument

2498

2506

#

2499

2507

2500

CREATE TABLE t1 (a int, b int);

2501

CREATE TABLE t2 (m int, n int);

2508

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

2509

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

2502

2510

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

2503

2511

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

2504

2512

2515

2523

#

2516

2524

# Bug#27321: Wrong subquery result in a grouping select

2517

2525

#

2518

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

2526

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

2519

2527

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

2520

2528

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

2521

2529

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

2553

2561

# Bug #27363: nested aggregates in outer, subquery / sum(select

2554

2562

# count(outer))

2555

2563

#

2556

CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);

2557

CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);

2564

CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 values (1),(1),(1),(1);

2565

CREATE TABLE t2 (x INT) ENGINE=MyISAM; INSERT INTO t1 values (1000),(1001),(1002);

2558

2566

2559

2567

--error ER_INVALID_GROUP_FUNC_USE

2560

2568

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

2576

2584

#

2577

2585

# Bug #27807: Server crash when executing subquery with EXPLAIN

2578

2586

#

2579

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

2587

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

2580

2588

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

2581

2589

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

2582

2590

DROP TABLE t1;

2585

2593

# Bug #28377: grouping query with a correlated subquery in WHERE condition

2586

2594

#

2587

2595

2588

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

2596

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

2589

2597

INSERT INTO t1 VALUES

2590

2598

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

2591

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

2599

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

2592

2600

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

2593

2601

2594

2602

SELECT id, st FROM t1

2610

2618

# over a grouping subselect

2611

2619

#

2612

2620

2613

CREATE TABLE t1 (a int);

2621

CREATE TABLE t1 (a int) ENGINE=MyISAM;

2614

2622

2615

2623

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

2616

2624

2626

2634

CREATE TABLE t1 (

2627

2635

a varchar(255) default NULL,

2628

2636

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

2629

INDEX idx(a,b)

2630

);

2637

INDEX idx(a,b))

2638

ENGINE=MyISAM;

2639

2631

2640

CREATE TABLE t2 (

2632

a varchar(255) default NULL

2633

);

2641

a varchar(255) default NULL)

2642

ENGINE=MyISAM;

2634

2643

2635

2644

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

2636

2645

INSERT INTO t1 SELECT * FROM t1;

2656

2665

# Bug #27333: subquery grouped for aggregate of outer query / no aggregate

2657

2666

# of subquery

2658

2667

#

2659

CREATE TABLE t1 (a INTEGER, b INTEGER);

2660

CREATE TABLE t2 (x INTEGER);

2668

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

2669

CREATE TABLE t2 (x INTEGER) ENGINE=MyISAM;

2661

2670

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

2662

2671

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

2663

2672

2673

2682

DROP TABLE t1,t2;

2674

2683

2675

2684

# second test case from 27333

2676

CREATE TABLE t1 (a INT, b INT);

2685

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

2677

2686

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

2678

2687

2679

2688

# returns no rows, when it should

2683

2692

DROP TABLE t1;

2684

2693

2685

2694

#test cases from 29297

2686

CREATE TABLE t1 (a INT);

2687

CREATE TABLE t2 (a INT);

2695

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

2696

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

2688

2697

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

2689

2698

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

2690

2699

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

2697

2706

# Bug #31884: Assertion + crash in subquery in the SELECT clause.

2698

2707

#

2699

2708

2700

CREATE TABLE t1 (a1 INT, a2 INT);

2701

CREATE TABLE t2 (b1 INT, b2 INT);

2709

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

2710

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

2702

2711

2703

2712

INSERT INTO t1 VALUES (100, 200);

2704

2713

INSERT INTO t1 VALUES (101, 201);

2712

2721

# Bug #30788: Inconsistent retrieval of char/varchar

2713

2722

#

2714

2723

2715

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

2724

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

2716

2725

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

2717

2726

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

2718

2727

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

2721

2730

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

2722

2731

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

2723

2732

2724

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

2733

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

2725

2734

INSERT INTO t2 SELECT * FROM t1;

2726

2735

CREATE INDEX I1 ON t2 (a);

2727

2736

CREATE INDEX I2 ON t2 (b);

2738

2747

# occasions

2739

2748

#

2740

2749

2741

CREATE TABLE t1(a INT, b INT);

2750

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

2742

2751

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

2743

2752

2744

2753

--error ER_BAD_FIELD_ERROR

2768

2777

# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122

2769

2778

#

2770

2779

2771

CREATE TABLE t1 (a INT);

2772

CREATE TABLE t2 (a INT);

2780

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

2781

CREATE TABLE t2 (a INT) ENGINE=MyISAM;

2773

2782

2774

2783

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

2775

2784

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

2791

2800

CREATE TABLE t4 (

2792

2801

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

2793

2802

f10 varchar(32) collate utf8_bin default NULL,

2794

PRIMARY KEY (f7)

2795

);

2803

PRIMARY KEY (f7))

2804

ENGINE=MyISAM;

2805

2796

2806

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

2797

2807

2798

2808

CREATE TABLE t2 (

2800

2810

f2 varchar(50) collate utf8_bin default NULL,

2801

2811

f3 varchar(10) collate utf8_bin default NULL,

2802

2812

PRIMARY KEY (f4),

2803

UNIQUE KEY uk1 (f2)

2804

);

2813

UNIQUE KEY uk1 (f2))

2814

ENGINE=MyISAM;

2815

2805

2816

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

2806

2817

2807

CREATE TABLE t1 (

2818

CREATE TABLE t1 (

2808

2819

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

2809

2820

f1 varchar(10) collate utf8_bin default NULL,

2810

2821

f9 varchar(32) collate utf8_bin default NULL,

2811

PRIMARY KEY (f8)

2812

);

2822

PRIMARY KEY (f8))

2823

ENGINE=MyISAM;

2824

2813

2825

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

2814

2826

2815

2827

CREATE TABLE t3 (

2816

2828

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

2817

2829

f5 varchar(50) collate utf8_bin default NULL,

2818

PRIMARY KEY (f6)

2819

);

2830

PRIMARY KEY (f6))

2831

ENGINE=MyISAM;

2832

2820

2833

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

2821

2834

2822

2835

SELECT

2846

2859

#

2847

2860

2848

2861

create table t_out (subcase char(3),

2849

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

2850

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

2862

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

2863

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

2851

2864

2852

2865

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

2853

2866

#------------------------- A.2 - impossible

3021

3034

#

3022

3035

# Bug#20835 (literal string with =any values)

3023

3036

#

3024

CREATE TABLE t1 (s1 char(1));

3037

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

3025

3038

INSERT INTO t1 VALUES ('a');

3026

3039

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

3027

3040

DROP TABLE t1;

3029

3042

#

3030

3043

# Bug#33204: INTO is allowed in subselect, causing inconsistent results

3031

3044

#

3032

CREATE TABLE t1( a INT );

3045

CREATE TABLE t1( a INT ) ENGINE=MyISAM;

3033

3046

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

3034

3047

3035

CREATE TABLE t2( a INT, b INT );

3048

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

3036

3049

3037

3050

--error ER_PARSE_ERROR

3038

3051

SELECT *