4
drop table if exists t1,t2,t3;
8
# Test different join syntaxes
11
CREATE TABLE t1 (S1 INT);
12
CREATE TABLE t2 (S1 INT);
13
INSERT INTO t1 VALUES (1);
14
INSERT INTO t2 VALUES (2);
15
SELECT * FROM t1 JOIN t2;
16
SELECT * FROM t1 INNER JOIN t2;
17
SELECT * from t1 JOIN t2 USING (S1);
18
SELECT * FROM t1 INNER JOIN t2 USING (S1);
19
SELECT * from t1 CROSS JOIN t2;
20
SELECT * from t1 LEFT JOIN t2 USING(S1);
21
SELECT * from t1 LEFT JOIN t2 ON(t2.S1=2);
22
SELECT * from t1 RIGHT JOIN t2 USING(S1);
23
SELECT * from t1 RIGHT JOIN t2 ON(t1.S1=1);
27
# This failed for lia Perminov
30
create table t1 (id int primary key);
31
create table t2 (id int);
32
insert into t1 values (75);
33
insert into t1 values (79);
34
insert into t1 values (78);
35
insert into t1 values (77);
36
replace into t1 values (76);
37
replace into t1 values (76);
38
insert into t1 values (104);
39
insert into t1 values (103);
40
insert into t1 values (102);
41
insert into t1 values (101);
42
insert into t1 values (105);
43
insert into t1 values (106);
44
insert into t1 values (107);
46
insert into t2 values (107),(75),(1000);
48
select t1.id, t2.id from t1, t2 where t2.id = t1.id;
49
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id;
50
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t2.id;
53
# Test problems with impossible ON or WHERE
55
select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
56
explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
57
explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0;
65
id int(11) NOT NULL auto_increment,
66
token varchar(100) DEFAULT '' NOT NULL,
67
count int(11) DEFAULT '0' NOT NULL,
69
phone char(1) DEFAULT '' NOT NULL,
70
timestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
72
KEY token (token(15)),
73
KEY timestamp (timestamp),
74
UNIQUE token_2 (token(75),count,phone)
77
INSERT INTO t1 VALUES (21,'e45703b64de71482360de8fec94c3ade',3,7800,'n','1999-12-23 17:22:21');
78
INSERT INTO t1 VALUES (22,'e45703b64de71482360de8fec94c3ade',4,5000,'y','1999-12-23 17:22:21');
79
INSERT INTO t1 VALUES (18,'346d1cb63c89285b2351f0ca4de40eda',3,13200,'b','1999-12-23 11:58:04');
80
INSERT INTO t1 VALUES (17,'ca6ddeb689e1b48a04146b1b5b6f936a',4,15000,'b','1999-12-23 11:36:53');
81
INSERT INTO t1 VALUES (16,'ca6ddeb689e1b48a04146b1b5b6f936a',3,13200,'b','1999-12-23 11:36:53');
82
INSERT INTO t1 VALUES (26,'a71250b7ed780f6ef3185bfffe027983',5,1500,'b','1999-12-27 09:44:24');
83
INSERT INTO t1 VALUES (24,'4d75906f3c37ecff478a1eb56637aa09',3,5400,'y','1999-12-23 17:29:12');
84
INSERT INTO t1 VALUES (25,'4d75906f3c37ecff478a1eb56637aa09',4,6500,'y','1999-12-23 17:29:12');
85
INSERT INTO t1 VALUES (27,'a71250b7ed780f6ef3185bfffe027983',3,6200,'b','1999-12-27 09:44:24');
86
INSERT INTO t1 VALUES (28,'a71250b7ed780f6ef3185bfffe027983',3,5400,'y','1999-12-27 09:44:36');
87
INSERT INTO t1 VALUES (29,'a71250b7ed780f6ef3185bfffe027983',4,17700,'b','1999-12-27 09:45:05');
90
id int(11) NOT NULL auto_increment,
91
category int(11) DEFAULT '0' NOT NULL,
92
county int(11) DEFAULT '0' NOT NULL,
93
state int(11) DEFAULT '0' NOT NULL,
94
phones int(11) DEFAULT '0' NOT NULL,
95
nophones int(11) DEFAULT '0' NOT NULL,
97
KEY category (category,county,state)
99
INSERT INTO t2 VALUES (3,2,11,12,5400,7800);
100
INSERT INTO t2 VALUES (4,2,25,12,6500,11200);
101
INSERT INTO t2 VALUES (5,1,37,6,10000,12000);
102
select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
103
select a.id, b.category as catid, b.state as stateid, b.county as
104
countyid from t1 a, t2 b where (a.token =
105
'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id) order by a.id;
110
# Test of join of many tables.
112
create table t1 (a int primary key);
113
insert into t1 values(1),(2);
114
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);
115
--replace_result "31 tables" "XX tables" "61 tables" "XX tables"
117
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
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);
119
--replace_result "31 tables" "XX tables" "61 tables" "XX tables"
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);
125
# Simple join test. This failed in 3.23.42, there should have been
126
# no matches, still three matches were found.
135
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,3);
138
a int(11) default NULL
140
INSERT INTO t2 VALUES (2),(3);
141
SELECT t1.a,t2.a,b FROM t1,t2 WHERE t1.a=t2.a AND (t1.a=1 OR t1.a=2) AND b>=1 AND b<=3;
145
# TEST LEFT JOIN with DATE columns
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');
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
SELECT * FROM t1 WHERE 1/0 IS NULL;
158
# Problem with reference from const tables
161
Document_ID varchar(50) NOT NULL default '',
162
Contractor_ID varchar(6) NOT NULL default '',
163
Language_ID char(3) NOT NULL default '',
164
Expiration_Date datetime default NULL,
165
Publishing_Date datetime default NULL,
167
Column_ID varchar(50) NOT NULL default '',
168
PRIMARY KEY (Language_ID,Document_ID,Contractor_ID)
171
INSERT INTO t1 VALUES ('xep80','1','ger','2001-12-31 20:00:00','2001-11-12 10:58:00','Kartenbestellung - jetzt auch online','anle'),('','999998','',NULL,NULL,NULL,'');
174
Contractor_ID char(6) NOT NULL default '',
175
Language_ID char(3) NOT NULL default '',
176
Document_ID char(50) NOT NULL default '',
177
CanRead char(1) default NULL,
178
Customer_ID int(11) NOT NULL default '0',
179
PRIMARY KEY (Contractor_ID,Language_ID,Document_ID,Customer_ID)
182
INSERT INTO t2 VALUES ('5','ger','xep80','1',999999),('1','ger','xep80','1',999999);
184
Language_ID char(3) NOT NULL default '',
185
Column_ID char(50) NOT NULL default '',
186
Contractor_ID char(6) NOT NULL default '',
187
CanRead char(1) default NULL,
188
Active char(1) default NULL,
189
PRIMARY KEY (Language_ID,Column_ID,Contractor_ID)
191
INSERT INTO t3 VALUES ('ger','home','1','1','1'),('ger','Test','1','0','0'),('ger','derclu','1','0','0'),('ger','clubne','1','0','0'),('ger','philos','1','0','0'),('ger','clubko','1','0','0'),('ger','clubim','1','1','1'),('ger','progra','1','0','0'),('ger','progvo','1','0','0'),('ger','progsp','1','0','0'),('ger','progau','1','0','0'),('ger','progku','1','0','0'),('ger','progss','1','0','0'),('ger','nachl','1','0','0'),('ger','mitgli','1','0','0'),('ger','mitsu','1','0','0'),('ger','mitbus','1','0','0'),('ger','ergmar','1','1','1'),('ger','home','4','1','1'),('ger','derclu','4','1','1'),('ger','clubne','4','0','0'),('ger','philos','4','1','1'),('ger','clubko','4','1','1'),('ger','clubim','4','1','1'),('ger','progra','4','1','1'),('ger','progvo','4','1','1'),('ger','progsp','4','1','1'),('ger','progau','4','0','0'),('ger','progku','4','1','1'),('ger','progss','4','1','1'),('ger','nachl','4','1','1'),('ger','mitgli','4','0','0'),('ger','mitsu','4','0','0'),('ger','mitbus','4','0','0'),('ger','ergmar','4','1','1'),('ger','progra2','1','0','0'),('ger','archiv','4','1','1'),('ger','anmeld','4','1','1'),('ger','thema','4','1','1'),('ger','edito','4','1','1'),('ger','madis','4','1','1'),('ger','enma','4','1','1'),('ger','madis','1','1','1'),('ger','enma','1','1','1'),('ger','vorsch','4','0','0'),('ger','veranst','4','0','0'),('ger','anle','4','1','1'),('ger','redak','4','1','1'),('ger','nele','4','1','1'),('ger','aukt','4','1','1'),('ger','callcenter','4','1','1'),('ger','anle','1','0','0');
192
delete from t1 where Contractor_ID='999998';
193
insert into t1 (Contractor_ID) Values ('999998');
194
SELECT DISTINCT COUNT(t1.Title) FROM t1,
196
t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND
197
t1.Language_ID='ger' AND '2001-12-21 23:14:24' >=
198
Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND
199
t1.Document_ID = t2.Document_ID AND
200
t1.Language_ID = t2.Language_ID AND
201
t1.Contractor_ID = t2.Contractor_ID AND (
202
t2.Customer_ID = '4' OR
203
t2.Customer_ID = '999999' OR
204
t2.Customer_ID = '1' )AND t2.CanRead
205
= '1' AND t1.Column_ID=t3.Column_ID AND
206
t1.Language_ID=t3.Language_ID AND (
207
t3.Contractor_ID = '4' OR
208
t3.Contractor_ID = '999999' OR
209
t3.Contractor_ID = '1') AND
210
t3.CanRead='1' AND t3.Active='1';
211
SELECT DISTINCT COUNT(t1.Title) FROM t1,
213
t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND
214
t1.Language_ID='ger' AND '2001-12-21 23:14:24' >=
215
Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND
216
t1.Document_ID = t2.Document_ID AND
217
t1.Language_ID = t2.Language_ID AND
218
t1.Contractor_ID = t2.Contractor_ID AND (
219
t2.Customer_ID = '4' OR
220
t2.Customer_ID = '999999' OR
221
t2.Customer_ID = '1' )AND t2.CanRead
222
= '1' AND t1.Column_ID=t3.Column_ID AND
223
t1.Language_ID=t3.Language_ID AND (
224
t3.Contractor_ID = '4' OR
225
t3.Contractor_ID = '999999' OR
226
t3.Contractor_ID = '1') AND
227
t3.CanRead='1' AND t3.Active='1';
231
# Bug when doing full join and NULL fields.
235
t1_id int(11) default NULL,
236
t2_id int(11) default NULL,
237
type enum('Cost','Percent') default NULL,
238
cost_unit enum('Cost','Unit') default NULL,
239
min_value double default NULL,
240
max_value double default NULL,
241
t3_id int(11) default NULL,
242
item_id int(11) default NULL
244
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);
246
id int(10) unsigned NOT NULL auto_increment,
247
name varchar(255) default NULL,
250
INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5');
251
select t1.*, t2.* from t1, t2 where t2.id=t1.t2_id limit 2;
255
# Bug in range optimiser with MAYBE_KEY
259
siteid varchar(25) NOT NULL default '',
260
emp_id varchar(30) NOT NULL default '',
261
rate_code varchar(10) default NULL,
262
UNIQUE KEY site_emp (siteid,emp_id),
265
INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust');
267
siteid varchar(25) NOT NULL default '',
268
rate_code varchar(10) NOT NULL default '',
269
base_rate float NOT NULL default '0',
270
PRIMARY KEY (siteid,rate_code)
272
INSERT INTO t2 VALUES ('rivercats','cust',20);
273
SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
274
SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
275
SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND siteid = 'rivercats';
276
SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE siteid = 'rivercats' AND emp.emp_id = 'psmith';
280
# Problem with internal list handling when reducing WHERE
283
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
284
CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
285
INSERT INTO t1 VALUES (1, 'A');
286
INSERT INTO t2 VALUES (1, 'B');
288
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B');
289
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B';
290
SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1;
294
# dummy natural join (no common columns) Bug #4807
297
CREATE TABLE t1 (a int);
298
CREATE TABLE t2 (b int);
299
CREATE TABLE t3 (c int);
300
SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
301
DROP TABLE t1, t2, t3;
304
# Test combination of join methods
307
create table t1 (i int);
308
create table t2 (i int);
309
create table t3 (i int);
310
insert into t1 values(1),(2);
311
insert into t2 values(2),(3);
312
insert into t3 values (2),(4);
314
select * from t1 natural left join t2;
315
select * from t1 left join t2 on (t1.i=t2.i);
316
select * from t1 natural left join t2 natural left join t3;
317
select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);
319
select * from t3 natural right join t2;
320
select * from t3 right join t2 on (t3.i=t2.i);
321
select * from t3 natural right join t2 natural right join t1;
322
select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
324
select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
325
select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
326
select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
327
select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
329
select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
330
select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
331
select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
332
select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
336
# Bug #27531: Query performance degredation in 4.1.22 and greater
338
CREATE TABLE t1 (a int, b int default 0, c int default 1);
340
INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
341
INSERT INTO t1 (a) SELECT a + 8 FROM t1;
342
INSERT INTO t1 (a) SELECT a + 16 FROM t1;
344
CREATE TABLE t2 (a int, d int, e int default 0);
346
INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4);
347
INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2;
348
INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2;
350
# should use join cache
352
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
354
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
362
# Tests for WL#2486 Natural/using join according to SQL:2003.
365
# - The tests are designed so that all statements, except MySQL
366
# extensions run on any SQL server. Please do no change.
367
# - Tests marked with TODO will be submitted as bugs.
370
create table t1 (c int, b int);
371
create table t2 (a int, b int);
372
create table t3 (b int, c int);
373
create table t4 (y int, c int);
374
create table t5 (y int, z int);
375
create table t6 (a int, c int);
377
insert into t1 values (10,1);
378
insert into t1 values (3 ,1);
379
insert into t1 values (3 ,2);
380
insert into t2 values (2, 1);
381
insert into t3 values (1, 3);
382
insert into t3 values (1,10);
383
insert into t4 values (11,3);
384
insert into t4 values (2, 3);
385
insert into t5 values (11,4);
386
insert into t6 values (2, 3);
388
select * from t1 natural join t2;
389
# as above, but column names are cross-renamed: a->c, c->b, b->a
390
select * from t1 natural join t2;
391
# as above, but column names are aliased: a->c, c->b, b->a
392
select b as a, c as b, a as c from t1 natural join t2;
393
# as above, but column names are cross-renamed, and aliased
394
# a->c->b, c->b->a, b->a->c
395
select a as c, c as b, b as a from t1 natural join t2;
397
# Views with JOIN ... ON
398
select t1.c, t1.b, t2.a from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
399
select t1.c as b, t1.b as a, t2.a as c
400
from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
402
# Views with bigger natural join
403
select * from t1 natural join (t2 natural join t3);
405
# Nested natural/using joins.
406
select * from (t1 natural join t2) natural join (t3 natural join t4);
407
select * from (t1 natural join t2) natural left join (t3 natural join t4);
408
select * from (t3 natural join t4) natural right join (t1 natural join t2);
409
select * from (t1 natural left join t2) natural left join (t3 natural left join t4);
410
select * from (t4 natural right join t3) natural right join (t2 natural right join t1);
411
select * from t1 natural join t2 natural join t3 natural join t4;
412
select * from ((t1 natural join t2) natural join t3) natural join t4;
413
select * from t1 natural join (t2 natural join (t3 natural join t4));
414
# BUG#15355: this query fails in 'prepared statements' mode
415
# select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5;
416
# select * from ((t3 natural left join (t1 natural left join t2)) natural left join t4) natural left join t5;
417
select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3));
418
select * from (t1 natural join t2), (t3 natural join t4);
419
# MySQL extension - nested comma ',' operator instead of cross join.
420
select * from t5 natural join ((t1 natural join t2), (t3 natural join t4));
421
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t5;
422
select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4));
423
select * from ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5;
425
select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c);
426
select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c));
429
# Other clauses refer to NJ columns.
430
select a,b,c from (t1 natural join t2) natural join (t3 natural join t4)
431
where b + 1 = y or b + 10 = y group by b,c,a having min(b) < max(y) order by a;
432
select * from (t1 natural join t2) natural left join (t3 natural join t4)
433
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;
434
select * from (t3 natural join t4) natural right join (t1 natural join t2)
435
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;
437
# Qualified column references to NJ columns.
438
select * from t1 natural join t2 where t1.c > t2.a;
439
select * from t1 natural join t2 where t1.b > t2.b;
440
select * from t1 natural left join (t4 natural join t5) where t5.z is not NULL;
442
# Nested 'join ... on' - name resolution of ON conditions
443
select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
444
select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c;
445
select * from t1 natural join (t2 join t4 on b + 1 = y);
446
select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c);
448
# MySQL extension - 'join ... on' over nested comma operator
449
select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c);
450
select * from (t1 natural join t2) join (t3 natural join t4) on a = y;
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;
453
# MySQL extension - refererence qualified coalesced columns
454
select * from t1 natural join t2 where t1.b > 0;
455
select * from t1 natural join (t4 natural join t5) where t4.y > 7;
456
select * from (t4 natural join t5) natural join t1 where t4.y > 7;
457
select * from t1 natural left join (t4 natural join t5) where t4.y > 7;
458
select * from (t4 natural join t5) natural right join t1 where t4.y > 7;
459
select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b;
461
# MySQL extension - select qualified columns of NJ columns
462
select t1.*, t2.* from t1 natural join t2;
463
select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4);
465
# Queries over subselects in the FROM clause
466
select * from (select * from t1 natural join t2) as t12
468
(select * from t3 natural join t4) as t34;
469
select * from (select * from t1 natural join t2) as t12
471
(select * from t3 natural join t4) as t34;
472
select * from (select * from t3 natural join t4) as t34
474
(select * from t1 natural join t2) as t12;
476
# TODO: add tests with correlated subqueries for natural join/join on.
477
# related to BUG#15269
480
#--------------------------------------------------------------------
481
# Negative tests (tests for errors)
482
#--------------------------------------------------------------------
483
# works in Oracle - bug
485
select * from t1 natural join (t3 cross join t4);
486
# works in Oracle - bug
488
select * from (t3 cross join t4) natural join t1;
490
select * from t1 join (t2, t3) using (b);
492
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
494
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
496
select * from t6 natural join ((t1 natural join t2), (t3 natural join t4));
498
select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4);
500
select * from (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b);
501
# this one is OK, the next equivalent one is incorrect (bug in Oracle)
503
select * from (t3 join (t4 natural join t5) on (b < z))
505
(t1 natural join t2);
507
select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z));
510
# Bug #17523 natural join and information_schema
512
# We mask out the Privileges column because it differs with embedded server
513
--replace_column 32 #
515
select * from information_schema.statistics join information_schema.columns
516
using(table_name,column_name) where table_name='user';
526
# BUG#15229 - columns of nested joins that are not natural joins incorrectly
529
create table t1 (a1 int, a2 int);
530
create table t2 (a1 int, b int);
531
create table t3 (c1 int, c2 int);
532
create table t4 (c2 int);
534
insert into t1 values (1,1);
535
insert into t2 values (1,1);
536
insert into t3 values (1,1);
537
insert into t4 values (1);
539
select * from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
540
select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
541
select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
542
select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
543
select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2);
544
select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4;
546
drop table t1,t2,t3,t4;
549
# BUG#15355: Common natural join column not resolved in prepared statement nested query
551
create table t1 (c int, b int);
552
create table t2 (a int, b int);
553
create table t3 (b int, c int);
554
create table t4 (y int, c int);
555
create table t5 (y int, z int);
557
insert into t1 values (3,2);
558
insert into t2 values (1,2);
559
insert into t3 values (2,3);
560
insert into t4 values (1,3);
561
insert into t5 values (1,4);
564
select * from ((t3 natural join (t1 natural join t2)) natural join t4)
566
drop table t1, t2, t3, t4, t5;
568
# End of tests for WL#2486 - natural/using join
570
# BUG#27939: Early NULLs filtering doesn't work for eq_ref access
571
create table t1 (a int, b int);
572
insert into t1 values
578
create table t2 (a int not null, primary key(a));
579
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
581
create table t3 (a int not null, primary key(a));
582
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
585
select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
586
explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
587
--echo We expect rnd_next=5, and read_key must be 0 because of short-cutting:
588
show status like 'Handler_read%';
589
drop table t1, t2, t3;
592
# BUG#14940: Make E(#rows) from "range" access be re-used by range optimizer
594
create table t1 (a int);
595
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
597
create table t2 (a int, b int, filler char(100), key(a), key(b));
598
create table t3 (a int, b int, filler char(100), key(a), key(b));
601
select @a:= A.a + 10*(B.a + 10*C.a), @a, 'filler' from t1 A, t1 B, t1 C;
602
insert into t3 select * from t2 where a < 800;
604
# The order of tables must be t2,t3:
605
explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b;
607
drop table t1, t2, t3;
609
# BUG#14940 {Wrong query plan is chosen because of odd results of
610
# prev_record_reads() function }
611
create table t1 (a int);
612
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
614
create table t2 (a int, b int, primary key(a));
615
insert into t2 select @v:=A.a+10*B.a, @v from t1 A, t1 B;
617
explain select * from t1;
618
show status like '%cost%';
619
select 'The cost of accessing t1 (dont care if it changes' '^';
621
select 'vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv' Z;
623
explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b;
624
show status like '%cost%';
625
select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z;
632
# Bug #31094: Forcing index-based sort doesn't work anymore if joins are
636
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
637
CREATE TABLE t2 (c INT PRIMARY KEY, d INT);
639
INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL);
640
INSERT INTO t1 SELECT a + 4, b FROM t1;
641
INSERT INTO t1 SELECT a + 8, b FROM t1;
642
INSERT INTO t1 SELECT a + 16, b FROM t1;
643
INSERT INTO t1 SELECT a + 32, b FROM t1;
644
INSERT INTO t1 SELECT a + 64, b FROM t1;
645
INSERT INTO t2 SELECT a, b FROM t1;
647
#expect indexed ORDER BY
648
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
649
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
650
SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
651
SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
654
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
655
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
656
SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
657
SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
659
DROP TABLE IF EXISTS t1,t2;
660
--echo End of 5.0 tests.
added
removed
mysql-test/t/join.test
