~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

119

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

120

--error ER_TOO_MANY_TABLES

120

--error 1116

121

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

drop table t1;

123

151

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

152

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

153

SELECT * from t1 WHERE t1.d IS NULL;

154

--error ER_DIVISION_BY_ZERO

155

154

SELECT * FROM t1 WHERE 1/0 IS NULL;

156

155

DROP TABLE t1,t2;

157

156

249

248

PRIMARY KEY (id)

250

249

) ENGINE=MyISAM;

251

250

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

252

--sorted_result

253

251

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

254

252

drop table t1,t2;

255

253

402

400

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

403

401

404

402

# Views with bigger natural join

405

--sorted_result

406

403

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

407

404

408

405

# Nested natural/using joins.

409

--sorted_result

410

406

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

411

407

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

412

408

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

447

443

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

448

444

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

449

445

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

450

--sorted_result

451

446

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

452

447

453

448

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

454

--sorted_result

455

449

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

456

450

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

457

--sorted_result

458

451

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;

459

452

460

453

# MySQL extension - refererence qualified coalesced columns

463

456

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

464

457

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

465

458

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

466

--sorted_result

467

459

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

468

460

469

461

# MySQL extension - select qualified columns of NJ columns

Older »