~drizzle-trunk/drizzle/development

select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);

118

120

119

121

--replace_result "31 tables" "XX tables" "61 tables" "XX tables"

120

--error 1116

122

--error ER_TOO_MANY_TABLES

121

123

select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);

122

124

drop table t1;

123

125

126

128

# no matches, still three matches were found.

127

129

128

130

129

CREATE TABLE t1 (

131

CREATE TEMPORARY TABLE t1 (

130

132

a int NOT NULL,

131

133

b int NOT NULL,

132

134

PRIMARY KEY (a,b)

134

136

135

137

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

136

138

137

CREATE TABLE t2 (

139

CREATE TEMPORARY TABLE t2 (

138

140

a int default NULL

139

141

) ENGINE=MyISAM;

140

142

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

145

147

# TEST LEFT JOIN with DATE columns

146

148

147

149

148

CREATE TABLE t1 (d DATE NOT NULL);

149

CREATE TABLE t2 (d DATE NOT NULL);

150

INSERT INTO t1 (d) VALUES ('2001-08-01'),('0000-00-00');

150

CREATE TABLE t1 (d DATE);

151

CREATE TABLE t2 (d DATE);

152

INSERT INTO t1 (d) VALUES ('2001-08-01'),(NULL);

151

153

SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL;

152

154

SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE d IS NULL;

153

155

SELECT * from t1 WHERE t1.d IS NULL;

156

--error ER_DIVISION_BY_ZERO

154

157

SELECT * FROM t1 WHERE 1/0 IS NULL;

155

158

DROP TABLE t1,t2;

156

159

231

234

# Bug when doing full join and NULL fields.

232

235

233

236

234

CREATE TABLE t1 (

237

CREATE TEMPORARY TABLE t1 (

235

238

t1_id int default NULL,

236

239

t2_id int default NULL,

237

240

type enum('Cost','Percent') default NULL,

242

245

item_id int default NULL

243

246

) ENGINE=MyISAM;

244

247

INSERT INTO t1 VALUES (12,5,'Percent','Cost',-1,0,-1,-1),(14,4,'Percent','Cost',-1,0,-1,-1),(18,5,'Percent','Cost',-1,0,-1,-1),(19,4,'Percent','Cost',-1,0,-1,-1),(20,5,'Percent','Cost',100,-1,22,291),(21,5,'Percent','Cost',100,-1,18,291),(22,1,'Percent','Cost',100,-1,6,291),(23,1,'Percent','Cost',100,-1,21,291),(24,1,'Percent','Cost',100,-1,9,291),(25,1,'Percent','Cost',100,-1,4,291),(26,1,'Percent','Cost',100,-1,20,291),(27,4,'Percent','Cost',100,-1,7,202),(28,1,'Percent','Cost',50,-1,-1,137),(29,2,'Percent','Cost',100,-1,4,354),(30,2,'Percent','Cost',100,-1,9,137),(93,2,'Cost','Cost',-1,10000000,-1,-1);

245

CREATE TABLE t2 (

248

CREATE TEMPORARY TABLE t2 (

246

249

id int NOT NULL auto_increment,

247

250

name varchar(255) default NULL,

248

251

PRIMARY KEY (id)

249

252

) ENGINE=MyISAM;

250

253

INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5');

254

--sorted_result

251

255

select t1.*, t2.* from t1, t2 where t2.id=t1.t2_id limit 2;

252

256

drop table t1,t2;

253

257

255

259

# Bug in range optimiser with MAYBE_KEY

256

260

257

261

258

CREATE TABLE t1 (

262

CREATE TEMPORARY TABLE t1 (

259

263

siteid varchar(25) NOT NULL default '',

260

264

emp_id varchar(30) NOT NULL default '',

261

265

rate_code varchar(10) default NULL,

263

267

KEY siteid (siteid)

264

268

) ENGINE=MyISAM;

265

269

INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust');

266

CREATE TABLE t2 (

270

CREATE TEMPORARY TABLE t2 (

267

271

siteid varchar(25) NOT NULL default '',

268

272

rate_code varchar(10) NOT NULL default '',

269

273

base_rate float NOT NULL default '0',

297

301

CREATE TABLE t1 (a int);

298

302

CREATE TABLE t2 (b int);

299

303

CREATE TABLE t3 (c int);

304

--error ER_CARTESIAN_JOIN_ATTEMPTED

300

305

SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;

301

306

DROP TABLE t1, t2, t3;

302

307

400

405

from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;

401

406

402

407

# Views with bigger natural join

408

--sorted_result

403

409

select * from t1 natural join (t2 natural join t3);

404

410

405

411

# Nested natural/using joins.

412

--sorted_result

406

413

select * from (t1 natural join t2) natural join (t3 natural join t4);

407

414

select * from (t1 natural join t2) natural left join (t3 natural join t4);

408

415

select * from (t3 natural join t4) natural right join (t1 natural join t2);

443

450

select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;

444

451

select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c;

445

452

select * from t1 natural join (t2 join t4 on b + 1 = y);

453

--sorted_result

446

454

select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c);

447

455

448

456

# MySQL extension - 'join ... on' over nested comma operator

457

--sorted_result

449

458

select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c);

450

459

select * from (t1 natural join t2) join (t3 natural join t4) on a = y;

460

--sorted_result

451

461

select * from ((t3 join (t1 join t2 on c > a) on t3.b < t2.a) join t4 on y > t1.c) join t5 on z = t1.b + 3;

452

462

453

463

# MySQL extension - refererence qualified coalesced columns

456

466

select * from (t4 natural join t5) natural join t1 where t4.y > 7;

457

467

select * from t1 natural left join (t4 natural join t5) where t4.y > 7;

458

468

select * from (t4 natural join t5) natural right join t1 where t4.y > 7;

469

--sorted_result

459

470

select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b;

460

471

461

472

# MySQL extension - select qualified columns of NJ columns

481

492

# Negative tests (tests for errors)

482

493

#--------------------------------------------------------------------

483

494

# works in Oracle - bug

495

--error ER_NON_UNIQ_ERROR

484

496

select * from t1 natural join (t3 cross join t4);

485

497

# works in Oracle - bug

498

--error ER_NON_UNIQ_ERROR

486

499

select * from (t3 cross join t4) natural join t1;

500

--error ER_NON_UNIQ_ERROR

487

501

select * from t1 join (t2, t3) using (b);

488

select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;

489

select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;

502

--error ER_NON_UNIQ_ERROR

503

select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;

504

--error ER_NON_UNIQ_ERROR

505

select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;

506

--error ER_NON_UNIQ_ERROR

490

507

select * from t6 natural join ((t1 natural join t2), (t3 natural join t4));

508

--error ER_NON_UNIQ_ERROR

491

509

select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4);

510

--error ER_NON_UNIQ_ERROR

492

511

select * from (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b);

493

512

# this one is OK, the next equivalent one is incorrect (bug in Oracle)

513

--error ER_NON_UNIQ_ERROR

494

514

select * from (t3 join (t4 natural join t5) on (b < z))

495

515

natural join

496

516

(t1 natural join t2);

517

--error ER_NON_UNIQ_ERROR

497

518

select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z));

498

519

499

520

drop table t1;

576

587

select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;

577

588

explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;

578

589

--echo We expect rnd_next=5, and read_key must be 0 because of short-cutting:

590

--replace_column 2 #

579

591

show status like 'Handler_read%';

580

592

drop table t1, t2, t3;

581

593

589

601

create table t3 (a int, b int, filler char(100), key(a), key(b));

590

602

591

603

insert into t2

592

select @a:= A.a + 10*(B.a + 10*C.a), @a, 'filler' from t1 A, t1 B, t1 C;

604

select @a:= A.a + 10*(B.a + 10*C.a), @a, 'filler' from t1 A, t1 B, t1 C where B.a >= 0;

593

605

insert into t3 select * from t2 where a < 800;

594

606

595

607

# The order of tables must be t2,t3:

603

615

insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

604

616

605

617

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

606

insert into t2 select @v:=A.a+10*B.a, @v from t1 A, t1 B;

618

insert into t2 select @v:=A.a+10*B.a, @v from t1 A, t1 B where B.a >= 0;

607

619

608

620

explain select * from t1;

621

--replace_column 2 #

609

622

show status like '%cost%';

610

623

select 'The cost of accessing t1 (dont care if it changes' '^';

611

624

612

625

select 'vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv' Z;

613

626

614

627

explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b;

628

--replace_column 2 #

615

629

show status like '%cost%';

616

630

select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z;

617

631

Older »