2
# test of left outer join
6
drop table if exists t0,t1,t2,t3,t4,t5;
10
grp int(11) default NULL,
11
a bigint(20) unsigned default NULL,
12
c char(10) NOT NULL default ''
14
INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,'');
15
create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a));
16
insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7);
18
select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a;
19
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
20
select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) };
21
select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2;
22
select t1.*,t2.* from t1 left join t2 using (a);
23
select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
24
select t1.*,t2.* from t1 left join t2 using (a,c);
25
select t1.*,t2.* from t1 left join t2 using (c);
26
select t1.*,t2.* from t1 natural left outer join t2;
28
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
29
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
31
explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1;
32
explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
34
select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
36
# The next query should rearange the left joins to get this to work
38
explain select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
40
select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
42
# The next query should give an error in MySQL
44
select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
47
select t1.*,t2.* from t1 inner join t2 using (a);
48
select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
49
select t1.*,t2.* from t1 natural join t2;
54
# Test of left join bug
58
usr_id INT unsigned NOT NULL,
59
uniq_id INT unsigned NOT NULL AUTO_INCREMENT,
60
start_num INT unsigned NOT NULL DEFAULT 1,
61
increment INT unsigned NOT NULL DEFAULT 1,
62
PRIMARY KEY (uniq_id),
63
INDEX usr_uniq_idx (usr_id, uniq_id),
64
INDEX uniq_usr_idx (uniq_id, usr_id)
67
id INT unsigned NOT NULL DEFAULT 0,
68
usr2_id INT unsigned NOT NULL DEFAULT 0,
69
max INT unsigned NOT NULL DEFAULT 0,
70
c_amount INT unsigned NOT NULL DEFAULT 0,
71
d_max INT unsigned NOT NULL DEFAULT 0,
72
d_num INT unsigned NOT NULL DEFAULT 0,
73
orig_time INT unsigned NOT NULL DEFAULT 0,
74
c_time INT unsigned NOT NULL DEFAULT 0,
75
active ENUM ("no","yes") NOT NULL,
76
PRIMARY KEY (id,usr2_id),
78
INDEX usr2_idx (usr2_id)
80
INSERT INTO t1 VALUES (3,NULL,0,50),(3,NULL,0,200),(3,NULL,0,25),(3,NULL,0,84676),(3,NULL,0,235),(3,NULL,0,10),(3,NULL,0,3098),(3,NULL,0,2947),(3,NULL,0,8987),(3,NULL,0,8347654),(3,NULL,0,20398),(3,NULL,0,8976),(3,NULL,0,500),(3,NULL,0,198);
82
#1st select shows that one record is returned with null entries for the right
83
#table, when selecting on an id that does not exist in the right table t2
84
SELECT t1.usr_id,t1.uniq_id,t1.increment,
85
t2.usr2_id,t2.c_amount,t2.max
87
LEFT JOIN t2 ON t2.id = t1.uniq_id
91
# The same with RIGHT JOIN
92
SELECT t1.usr_id,t1.uniq_id,t1.increment,
93
t2.usr2_id,t2.c_amount,t2.max
95
RIGHT JOIN t1 ON t2.id = t1.uniq_id
99
INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
101
INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
102
INSERT INTO t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');
104
#3rd select should show that one record is returned with null entries for the
105
# right table, when selecting on an id that does not exist in the right table
106
# t2 but this select returns an empty set!!!!
107
SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4 ORDER BY t2.c_amount;
108
SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4 GROUP BY t2.c_amount;
109
# Removing the ORDER BY works:
110
SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4;
115
# Test of LEFT JOIN with const tables (failed for frankie@etsetb.upc.es)
119
cod_asig int(11) DEFAULT '0' NOT NULL,
120
desc_larga_cat varchar(80) DEFAULT '' NOT NULL,
121
desc_larga_cas varchar(80) DEFAULT '' NOT NULL,
122
desc_corta_cat varchar(40) DEFAULT '' NOT NULL,
123
desc_corta_cas varchar(40) DEFAULT '' NOT NULL,
124
cred_total double(3,1) DEFAULT '0.0' NOT NULL,
125
pre_requisit int(11),
127
preco_requisit int(11),
128
PRIMARY KEY (cod_asig)
131
INSERT INTO t1 VALUES (10360,'asdfggfg','Introduccion a los Ordenadores I','asdfggfg','Introduccio Ordinadors I',6.0,NULL,NULL,NULL);
132
INSERT INTO t1 VALUES (10361,'Components i Circuits Electronics I','Componentes y Circuitos Electronicos I','Components i Circuits Electronics I','Comp. i Circ. Electr. I',6.0,NULL,NULL,NULL);
133
INSERT INTO t1 VALUES (10362,'Laboratori d`Ordinadors','Laboratorio de Ordenadores','Laboratori d`Ordinadors','Laboratori Ordinadors',4.5,NULL,NULL,NULL);
134
INSERT INTO t1 VALUES (10363,'Tecniques de Comunicacio Oral i Escrita','Tecnicas de Comunicacion Oral y Escrita','Tecniques de Comunicacio Oral i Escrita','Tec. Com. Oral i Escrita',4.5,NULL,NULL,NULL);
135
INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);
136
INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);
137
INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);
138
INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
141
idAssignatura int(11) DEFAULT '0' NOT NULL,
142
Grup int(11) DEFAULT '0' NOT NULL,
143
Places smallint(6) DEFAULT '0' NOT NULL,
144
PlacesOcupades int(11) DEFAULT '0',
145
PRIMARY KEY (idAssignatura,Grup)
149
INSERT INTO t2 VALUES (10360,12,333,0);
150
INSERT INTO t2 VALUES (10361,30,2,0);
151
INSERT INTO t2 VALUES (10361,40,3,0);
152
INSERT INTO t2 VALUES (10360,45,10,0);
153
INSERT INTO t2 VALUES (10362,10,12,0);
154
INSERT INTO t2 VALUES (10360,55,2,0);
155
INSERT INTO t2 VALUES (10360,70,0,0);
156
INSERT INTO t2 VALUES (10360,565656,0,0);
157
INSERT INTO t2 VALUES (10360,32767,7,0);
158
INSERT INTO t2 VALUES (10360,33,8,0);
159
INSERT INTO t2 VALUES (10360,7887,85,0);
160
INSERT INTO t2 VALUES (11405,88,8,0);
161
INSERT INTO t2 VALUES (10360,0,55,0);
162
INSERT INTO t2 VALUES (10360,99,0,0);
163
INSERT INTO t2 VALUES (11411,30,10,0);
164
INSERT INTO t2 VALUES (11404,0,0,0);
165
INSERT INTO t2 VALUES (10362,11,111,0);
166
INSERT INTO t2 VALUES (10363,33,333,0);
167
INSERT INTO t2 VALUES (11412,55,0,0);
168
INSERT INTO t2 VALUES (50003,66,6,0);
169
INSERT INTO t2 VALUES (11403,5,0,0);
170
INSERT INTO t2 VALUES (11406,11,11,0);
171
INSERT INTO t2 VALUES (11410,11410,131,0);
172
INSERT INTO t2 VALUES (11416,11416,32767,0);
173
INSERT INTO t2 VALUES (11409,0,0,0);
176
id int(11) NOT NULL auto_increment,
177
dni_pasaporte char(16) DEFAULT '' NOT NULL,
178
idPla int(11) DEFAULT '0' NOT NULL,
179
cod_asig int(11) DEFAULT '0' NOT NULL,
180
any smallint(6) DEFAULT '0' NOT NULL,
181
quatrimestre smallint(6) DEFAULT '0' NOT NULL,
182
estat char(1) DEFAULT 'M' NOT NULL,
184
UNIQUE dni_pasaporte (dni_pasaporte,idPla),
185
UNIQUE dni_pasaporte_2 (dni_pasaporte,idPla,cod_asig,any,quatrimestre)
188
INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');
191
id int(11) NOT NULL auto_increment,
192
papa int(11) DEFAULT '0' NOT NULL,
193
fill int(11) DEFAULT '0' NOT NULL,
194
idPla int(11) DEFAULT '0' NOT NULL,
196
KEY papa (idPla,papa),
197
UNIQUE papa_2 (idPla,papa,fill)
200
INSERT INTO t4 VALUES (1,-1,10360,1);
201
INSERT INTO t4 VALUES (2,-1,10361,1);
202
INSERT INTO t4 VALUES (3,-1,10362,1);
204
SELECT DISTINCT fill,desc_larga_cat,cred_total,Grup,Places,PlacesOcupades FROM t4 LEFT JOIN t3 ON t3.cod_asig=fill AND estat='S' AND dni_pasaporte='11111111' AND t3.idPla=1 , t2,t1 WHERE fill=t1.cod_asig AND Places>PlacesOcupades AND fill=idAssignatura AND t4.idPla=1 AND papa=-1;
206
SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ;
208
INSERT INTO t3 VALUES (3,'1234',1,10360,98,1,'S');
209
SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ;
211
drop table t1,t2,t3,test.t4;
214
# Test of IS NULL on AUTO_INCREMENT with LEFT JOIN
218
id smallint(5) unsigned NOT NULL auto_increment,
219
name char(60) DEFAULT '' NOT NULL,
222
INSERT INTO t1 VALUES (1,'Antonio Paz');
223
INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
224
INSERT INTO t1 VALUES (3,'Thimble Smith');
227
id smallint(5) unsigned NOT NULL auto_increment,
228
owner smallint(5) unsigned DEFAULT '0' NOT NULL,
232
INSERT INTO t2 VALUES (1,1,'El Gato');
233
INSERT INTO t2 VALUES (2,1,'Perrito');
234
INSERT INTO t2 VALUES (3,3,'Happy');
236
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner);
237
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
238
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
239
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;
240
select count(*) from t1 left join t2 on (t1.id = t2.owner);
242
select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner);
243
select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
244
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
245
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;
246
select count(*) from t2 right join t1 on (t1.id = t2.owner);
248
select t1.name, t2.name, t2.id,t3.id from t2 right join t1 on (t1.id = t2.owner) left join t1 as t3 on t3.id=t2.owner;
249
select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;
250
select t1.name, t2.name, t2.id, t2.owner, t3.id from t1 left join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;
254
create table t1 (id int not null, str char(10), index(str));
255
insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");
256
select * from t1 where str is not null order by id;
257
select * from t1 where str is null;
261
# Test wrong LEFT JOIN query
265
t1_id bigint(21) NOT NULL auto_increment,
269
t2_id bigint(21) NOT NULL auto_increment,
273
t3_id bigint(21) NOT NULL auto_increment,
277
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
278
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
279
KEY seq_0_id (seq_0_id),
280
KEY seq_1_id (seq_1_id)
283
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
284
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
285
KEY seq_1_id (seq_1_id),
286
KEY seq_0_id (seq_0_id)
289
insert into t1 values (1);
290
insert into t2 values (1);
291
insert into t3 values (1);
292
insert into t4 values (1,1);
293
insert into t5 values (1,1);
296
explain select * from t3 left join t4 on t4.seq_1_id = t2.t2_id left join t1 on t1.t1_id = t4.seq_0_id left join t5 on t5.seq_0_id = t1.t1_id left join t2 on t2.t2_id = t5.seq_1_id where t3.t3_id = 23;
298
drop table t1,t2,t3,t4,t5;
301
# Another LEFT JOIN problem
302
# (The problem was that the result changed when we added ORDER BY)
305
create table t1 (n int, m int, o int, key(n));
306
create table t2 (n int not null, m int, o int, primary key(n));
307
insert into t1 values (1, 2, 11), (1, 2, 7), (2, 2, 8), (1,2,9),(1,3,9);
308
insert into t2 values (1, 2, 3),(2, 2, 8), (4,3,9),(3,2,10);
309
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
310
t1.m = t2.m where t1.n = 1;
311
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
312
t1.m = t2.m where t1.n = 1 order by t1.o;
315
# Test bug with NATURAL join:
317
CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);
318
INSERT INTO t1 VALUES (1,'a',1);
319
INSERT INTO t1 VALUES (2,'b',1);
320
INSERT INTO t1 VALUES (3,'c',2);
322
CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));
323
INSERT INTO t2 VALUES (1,'x');
324
INSERT INTO t2 VALUES (2,'y');
325
INSERT INTO t2 VALUES (3,'z');
327
SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
328
SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
332
create table t1 ( color varchar(20), name varchar(20) );
333
insert into t1 values ( 'red', 'apple' );
334
insert into t1 values ( 'yellow', 'banana' );
335
insert into t1 values ( 'green', 'lime' );
336
insert into t1 values ( 'black', 'grape' );
337
insert into t1 values ( 'blue', 'blueberry' );
338
create table t2 ( count int, color varchar(20) );
339
insert into t2 values (10, 'green');
340
insert into t2 values (5, 'black');
341
insert into t2 values (15, 'white');
342
insert into t2 values (7, 'green');
345
select * from t2 natural join t1;
346
select t2.count, t1.name from t2 natural join t1;
347
select t2.count, t1.name from t2 inner join t1 using (color);
352
# Test of LEFT JOIN + GROUP FUNCTIONS within functions:
356
pcode varchar(8) DEFAULT '' NOT NULL
358
INSERT INTO t1 VALUES ('kvw2000'),('kvw2001'),('kvw3000'),('kvw3001'),('kvw3002'),('kvw3500'),('kvw3501'),('kvw3502'),('kvw3800'),('kvw3801'),('kvw3802'),('kvw3900'),('kvw3901'),('kvw3902'),('kvw4000'),('kvw4001'),('kvw4002'),('kvw4200'),('kvw4500'),('kvw5000'),('kvw5001'),('kvw5500'),('kvw5510'),('kvw5600'),('kvw5601'),('kvw6000'),('klw1000'),('klw1020'),('klw1500'),('klw2000'),('klw2001'),('klw2002'),('kld2000'),('klw2500'),('kmw1000'),('kmw1500'),('kmw2000'),('kmw2001'),('kmw2100'),('kmw3000'),('kmw3200');
360
pcode varchar(8) DEFAULT '' NOT NULL,
363
INSERT INTO t2 VALUES ('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw6000'),('kvw6000'),('kld2000');
365
SELECT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1
366
LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;
367
SELECT SQL_BIG_RESULT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1 LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;
371
# Another left join problem
381
INSERT INTO t1 VALUES (1,NULL,NULL);
382
INSERT INTO t1 VALUES (2,1,NULL);
383
select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
384
create index rep_del ON t1(rep_del);
385
select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
389
id int(11) DEFAULT '0' NOT NULL,
390
name tinytext DEFAULT '' NOT NULL,
393
INSERT INTO t1 VALUES (1,'yes'),(2,'no');
395
id int(11) DEFAULT '0' NOT NULL,
396
idx int(11) DEFAULT '0' NOT NULL,
399
INSERT INTO t2 VALUES (1,1);
400
explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
401
SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
405
# Test problem with using key_column= constant in ON and WHERE
407
create table t1 (bug_id mediumint, reporter mediumint);
408
create table t2 (bug_id mediumint, who mediumint, index(who));
409
insert into t2 values (1,1),(1,2);
410
insert into t1 values (1,1),(2,1);
411
SELECT * FROM t1 LEFT JOIN t2 ON (t1.bug_id = t2.bug_id AND t2.who = 2) WHERE (t1.reporter = 2 OR t2.who = 2);
415
# Test problem with LEFT JOIN
417
create table t1 (fooID smallint unsigned auto_increment, primary key (fooID));
418
create table t2 (fooID smallint unsigned not null, barID smallint unsigned not null, primary key (fooID,barID));
419
insert into t1 (fooID) values (10),(20),(30);
420
insert into t2 values (10,1),(20,2),(30,3);
421
explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
422
select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
423
select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;
426
create table t1 (i int);
427
create table t2 (i int);
428
create table t3 (i int);
429
insert into t1 values(1),(2);
430
insert into t2 values(2),(3);
431
insert into t3 values(2),(4);
432
select * from t1 natural left join t2 natural left join t3;
433
select * from t1 natural left join t2 where (t2.i is not null)=0;
434
select * from t1 natural left join t2 where (t2.i is not null) is not null;
435
select * from t1 natural left join t2 where (i is not null)=0;
436
select * from t1 natural left join t2 where (i is not null) is not null;
442
create table t1 (f1 integer,f2 integer,f3 integer);
443
create table t2 (f2 integer,f4 integer);
444
create table t3 (f3 integer,f5 integer);
446
left outer join t2 using (f2)
447
left outer join t3 using (f3);
450
create table t1 (a1 int, a2 int);
451
create table t2 (b1 int not null, b2 int);
452
create table t3 (c1 int, c2 int);
454
insert into t1 values (1,2), (2,2), (3,2);
455
insert into t2 values (1,3), (2,3);
456
insert into t3 values (2,4), (3,4);
458
select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;
459
explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;
461
drop table t1, t2, t3;
463
# Test for BUG#8711 '<=>' was considered to be a NULL-rejecting predicate.
469
insert into t1 (a) values (1),(2),(3),(4);
470
create table t2 (a int);
472
select * from t1 left join t2 on t1.a=t2.a where not (t2.a <=> t1.a);
473
select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a);
479
match_id tinyint(3) unsigned not null auto_increment,
480
home tinyint(3) unsigned default '0',
481
unique key match_id (match_id),
482
key match_id_2 (match_id)
485
insert into t1 values("1", "2");
488
player_id tinyint(3) unsigned default '0',
489
match_1_h tinyint(3) unsigned default '0',
490
key player_id (player_id)
493
insert into t2 values("1", "5");
494
insert into t2 values("2", "9");
495
insert into t2 values("3", "3");
496
insert into t2 values("4", "7");
497
insert into t2 values("5", "6");
498
insert into t2 values("6", "8");
499
insert into t2 values("7", "4");
500
insert into t2 values("8", "12");
501
insert into t2 values("9", "11");
502
insert into t2 values("10", "10");
504
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
505
(t2 s left join t1 m on m.match_id = 1)
506
order by m.match_id desc;
508
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
509
(t2 s left join t1 m on m.match_id = 1)
512
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
513
(t2 s left join t1 m on m.match_id = 1)
516
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
517
t2 s straight_join t1 m where m.match_id = 1
520
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
521
t2 s straight_join t1 m where m.match_id = 1
526
# Tests for bugs #6307 and 6460
528
create table t1 (a int, b int, unique index idx (a, b));
529
create table t2 (a int, b int, c int, unique index idx (a, b));
531
insert into t1 values (1, 10), (1,11), (2,10), (2,11);
532
insert into t2 values (1,10,3);
534
select t1.a, t1.b, t2.c from t1 left join t2
535
on t1.a=t2.a and t1.b=t2.b and t2.c=3
536
where t1.a=1 and t2.c is null;
541
ts_id bigint(20) default NULL,
542
inst_id tinyint(4) default NULL,
543
flag_name varchar(64) default NULL,
545
UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
546
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
549
ts_id bigint(20) default NULL,
550
inst_id tinyint(4) default NULL,
551
flag_name varchar(64) default NULL,
553
UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
554
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
556
INSERT INTO t1 VALUES
557
(111056548820001, 0, 'flag1', NULL),
558
(111056548820001, 0, 'flag2', NULL),
559
(2, 0, 'other_flag', NULL);
561
INSERT INTO t2 VALUES
562
(111056548820001, 3, 'flag1', 'sss');
564
SELECT t1.flag_name,t2.flag_value
566
ON (t1.ts_id = t2.ts_id AND t1.flag_name = t2.flag_name AND
568
WHERE t1.inst_id = 0 AND t1.ts_id=111056548820001 AND
569
t2.flag_value IS NULL;
574
id int(11) unsigned NOT NULL auto_increment,
575
text_id int(10) unsigned default NULL,
579
INSERT INTO t1 VALUES("1", "0");
580
INSERT INTO t1 VALUES("2", "10");
583
text_id char(3) NOT NULL default '',
584
language_id char(3) NOT NULL default '',
586
PRIMARY KEY (text_id,language_id)
589
INSERT INTO t2 VALUES("0", "EN", "0-EN");
590
INSERT INTO t2 VALUES("0", "SV", "0-SV");
591
INSERT INTO t2 VALUES("10", "EN", "10-EN");
592
INSERT INTO t2 VALUES("10", "SV", "10-SV");
593
SELECT t1.id, t1.text_id, t2.text_data
595
ON t1.text_id = t2.text_id
596
AND t2.language_id = 'SV'
597
WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%');
603
CREATE TABLE t0 (a0 int PRIMARY KEY);
604
CREATE TABLE t1 (a1 int PRIMARY KEY);
605
CREATE TABLE t2 (a2 int);
606
CREATE TABLE t3 (a3 int);
607
INSERT INTO t0 VALUES (1);
608
INSERT INTO t1 VALUES (1);
609
INSERT INTO t2 VALUES (1), (2);
610
INSERT INTO t3 VALUES (1), (2);
612
SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
613
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
614
SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
615
EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
616
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
617
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
619
INSERT INTO t0 VALUES (0);
620
INSERT INTO t1 VALUES (0);
621
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
622
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
626
create table t1 (a int, b int);
627
insert into t1 values (1,1),(2,2),(3,3);
628
create table t2 (a int, b int);
629
insert into t2 values (1,1), (2,2);
631
select * from t2 right join t1 on t2.a=t1.a;
632
select straight_join * from t2 right join t1 on t2.a=t1.a;
634
DROP TABLE t0,t1,t2,t3;
637
# Test for bug #9017: left join mistakingly converted to inner join
640
CREATE TABLE t1 (a int PRIMARY KEY, b int);
641
CREATE TABLE t2 (a int PRIMARY KEY, b int);
643
INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
644
INSERT INTO t2 VALUES (1,2), (2,2);
646
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
647
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
648
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
649
WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
650
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
654
# Bug #8681: Bad warning message when group_concat() exceeds max length
655
set group_concat_max_len=5;
656
create table t1 (a int, b varchar(20));
657
create table t2 (a int, c varchar(20));
658
insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
659
insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
660
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
661
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
662
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
663
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
665
set group_concat_max_len=default;
670
# BUG#10162 - ON is merged with WHERE, left join is convered to a regular join
672
create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, art int(11) not null, primary key (gid,x,y));
673
insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
674
create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, id int(11) not null, primary key (gid,id,x,y), key id (id));
675
insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
676
create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null, name char(12) not null, primary key (id,set_id));
677
insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4, 'f'), (1, 5, 'g'), (1, 6, 'h');
678
explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
679
left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
680
and t1.gid =1 and t2.gid =1 and t3.set_id =1;
681
drop tables t1,t2,t3;
684
# Test for bug #9938: invalid conversion from outer join to inner join
685
# for queries containing indirect reference in WHERE clause
688
CREATE TABLE t1 (EMPNUM INT, GRP INT);
689
INSERT INTO t1 VALUES (0, 10);
690
INSERT INTO t1 VALUES (2, 30);
692
CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));
693
INSERT INTO t2 VALUES (0, 'KERI');
694
INSERT INTO t2 VALUES (9, 'BARRY');
697
SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP
698
FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;
701
SELECT * FROM v1 WHERE EMPNUM < 10;
707
# Test for bug #11285: false Item_equal on expression in outer join
710
CREATE TABLE t1 (c11 int);
711
CREATE TABLE t2 (c21 int);
712
INSERT INTO t1 VALUES (30), (40), (50);
713
INSERT INTO t2 VALUES (300), (400), (500);
714
SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;
718
# #12101: erroneously applied outer join elimination in case of WHERE NOT BETWEEN
719
# #12102: erroneously missing outer join elimination in case of WHERE IN/IF
722
CREATE TABLE t1 (a int PRIMARY KEY, b int);
723
CREATE TABLE t2 (a int PRIMARY KEY, b int);
725
INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
726
INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
728
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
729
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
730
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
732
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
733
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
734
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
736
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b;
737
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b);
739
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b);
740
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b);
742
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
743
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
744
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
746
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
747
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
748
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
750
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b);
751
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b));
753
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b;
754
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b));
756
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
757
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
758
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1);
763
# Test for bug #17164: ORed FALSE blocked conversion of outer join into join
766
# Test case moved to join_outer_innodb
769
# Bug 19396: LEFT OUTER JOIN over views in curly braces
772
DROP VIEW IF EXISTS v1,v2;
773
DROP TABLE IF EXISTS t1,t2;
776
CREATE TABLE t1 (a int);
777
CREATE table t2 (b int);
778
INSERT INTO t1 VALUES (1), (2), (3), (4), (1), (1), (3);
779
INSERT INTO t2 VALUES (2), (3);
781
CREATE VIEW v1 AS SELECT a FROM t1 JOIN t2 ON t1.a=t2.b;
782
CREATE VIEW v2 AS SELECT b FROM t2 JOIN t1 ON t2.b=t1.a;
785
FROM v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3)
788
FROM { OJ v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3) }
795
# Bug 19816: LEFT OUTER JOIN with constant ORed predicates in WHERE clause
798
CREATE TABLE t1 (a int);
799
CREATE TABLE t2 (b int);
800
INSERT INTO t1 VALUES (1), (2), (3), (4);
801
INSERT INTO t2 VALUES (2), (3);
803
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);
805
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
806
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
807
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
808
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
813
# Bug 26017: LEFT OUTER JOIN over two constant tables and
814
# a case-insensitive comparison predicate field=const
818
f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
819
f2 varchar(16) collate latin1_swedish_ci
822
f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
823
f3 varchar(16) collate latin1_swedish_ci
826
INSERT INTO t1 VALUES ('bla','blah');
827
INSERT INTO t2 VALUES ('bla','sheep');
829
SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
830
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
831
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
836
# Bug 28188: 'not exists' optimization for outer joins
839
CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
840
CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
841
INSERT INTO t1 VALUES
842
(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
843
INSERT INTO t2 VALUES
844
(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
847
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
850
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
851
show status like 'Handler_read%';
856
# Bug 28571: outer join with false on condition over constant tables
859
CREATE TABLE t1 (c int PRIMARY KEY, e int NOT NULL);
860
INSERT INTO t1 VALUES (1,0), (2,1);
861
CREATE TABLE t2 (d int PRIMARY KEY);
862
INSERT INTO t2 VALUES (1), (2), (3);
864
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
865
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
866
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
871
--echo # Bug#47650: using group by with rollup without indexes returns incorrect
872
--echo # results with where
874
CREATE TABLE t1 ( a INT );
875
INSERT INTO t1 VALUES (1);
877
CREATE TABLE t2 ( a INT, b INT );
878
INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5);
881
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
882
FROM t1 LEFT JOIN t2 USING( a )
883
GROUP BY t1.a WITH ROLLUP;
885
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
886
FROM t1 LEFT JOIN t2 USING( a )
887
GROUP BY t1.a WITH ROLLUP;
890
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
891
FROM t1 JOIN t2 USING( a )
892
GROUP BY t1.a WITH ROLLUP;
894
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
895
FROM t1 JOIN t2 USING( a )
896
GROUP BY t1.a WITH ROLLUP;
added
removed
mysql-test/t/join_outer.test
