1 drop table if exists t0,t1,t2,t3,t4,t5;
3 grp int(11) default NULL,
4 a bigint(20) unsigned default NULL,
5 c char(10) NOT NULL default ''
7 INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,'');
8 create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a));
9 insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7);
10 select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a;
16 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
18 NULL NULL NULL NULL NULL NULL
20 2 2 b NULL NULL NULL NULL
21 2 3 c NULL NULL NULL NULL
25 select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) };
31 NULL NULL NULL 4 7 D 7
32 select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2;
38 NULL NULL NULL 4 7 D 7
39 select t1.*,t2.* from t1 left join t2 using (a);
42 2 2 b NULL NULL NULL NULL
43 2 3 c NULL NULL NULL NULL
47 NULL NULL NULL NULL NULL NULL
48 select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
54 select t1.*,t2.* from t1 left join t2 using (a,c);
57 2 2 b NULL NULL NULL NULL
58 2 3 c NULL NULL NULL NULL
59 3 4 E NULL NULL NULL NULL
60 3 5 C NULL NULL NULL NULL
61 3 6 D NULL NULL NULL NULL
62 NULL NULL NULL NULL NULL NULL
63 select t1.*,t2.* from t1 left join t2 using (c);
69 3 4 E NULL NULL NULL NULL
72 NULL NULL NULL NULL NULL NULL
73 select t1.*,t2.* from t1 natural left outer join t2;
76 2 2 b NULL NULL NULL NULL
77 2 3 c NULL NULL NULL NULL
78 3 4 E NULL NULL NULL NULL
79 3 5 C NULL NULL NULL NULL
80 3 6 D NULL NULL NULL NULL
81 NULL NULL NULL NULL NULL NULL
82 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
87 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
89 2 2 b NULL NULL NULL NULL
90 2 3 c NULL NULL NULL NULL
91 NULL NULL NULL NULL NULL NULL
92 explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1;
93 id select_type table type possible_keys key key_len ref rows Extra
94 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
95 explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
96 id select_type table type possible_keys key key_len ref rows Extra
97 1 SIMPLE t1 ALL NULL NULL NULL NULL 7
98 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.a 1 Using where
99 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);
102 2 2 b NULL NULL NULL NULL NULL
103 2 3 c NULL NULL NULL NULL NULL
107 NULL NULL NULL NULL NULL NULL NULL
108 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);
109 ERROR 42S22: Unknown column 't3.a' in 'on clause'
110 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);
111 ERROR 42S22: Unknown column 't3.a' in 'on clause'
112 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);
113 ERROR 42S22: Unknown column 't3.a' in 'on clause'
114 select t1.*,t2.* from t1 inner join t2 using (a);
120 select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
126 select t1.*,t2.* from t1 natural join t2;
131 usr_id INT unsigned NOT NULL,
132 uniq_id INT unsigned NOT NULL AUTO_INCREMENT,
133 start_num INT unsigned NOT NULL DEFAULT 1,
134 increment INT unsigned NOT NULL DEFAULT 1,
135 PRIMARY KEY (uniq_id),
136 INDEX usr_uniq_idx (usr_id, uniq_id),
137 INDEX uniq_usr_idx (uniq_id, usr_id)
140 id INT unsigned NOT NULL DEFAULT 0,
141 usr2_id INT unsigned NOT NULL DEFAULT 0,
142 max INT unsigned NOT NULL DEFAULT 0,
143 c_amount INT unsigned NOT NULL DEFAULT 0,
144 d_max INT unsigned NOT NULL DEFAULT 0,
145 d_num INT unsigned NOT NULL DEFAULT 0,
146 orig_time INT unsigned NOT NULL DEFAULT 0,
147 c_time INT unsigned NOT NULL DEFAULT 0,
148 active ENUM ("no","yes") NOT NULL,
149 PRIMARY KEY (id,usr2_id),
151 INDEX usr2_idx (usr2_id)
153 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);
154 SELECT t1.usr_id,t1.uniq_id,t1.increment,
155 t2.usr2_id,t2.c_amount,t2.max
157 LEFT JOIN t2 ON t2.id = t1.uniq_id
159 ORDER BY t2.c_amount;
160 usr_id uniq_id increment usr2_id c_amount max
161 3 4 84676 NULL NULL NULL
162 SELECT t1.usr_id,t1.uniq_id,t1.increment,
163 t2.usr2_id,t2.c_amount,t2.max
165 RIGHT JOIN t1 ON t2.id = t1.uniq_id
167 ORDER BY t2.c_amount;
168 usr_id uniq_id increment usr2_id c_amount max
169 3 4 84676 NULL NULL NULL
170 INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
171 INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
172 ERROR 23000: Duplicate entry '2-3' for key 'PRIMARY'
173 INSERT INTO t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');
174 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;
175 usr_id uniq_id increment usr2_id c_amount max
176 3 4 84676 NULL NULL NULL
177 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;
178 usr_id uniq_id increment usr2_id c_amount max
179 3 4 84676 NULL NULL NULL
180 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;
181 usr_id uniq_id increment usr2_id c_amount max
182 3 4 84676 NULL NULL NULL
185 cod_asig int(11) DEFAULT '0' NOT NULL,
186 desc_larga_cat varchar(80) DEFAULT '' NOT NULL,
187 desc_larga_cas varchar(80) DEFAULT '' NOT NULL,
188 desc_corta_cat varchar(40) DEFAULT '' NOT NULL,
189 desc_corta_cas varchar(40) DEFAULT '' NOT NULL,
190 cred_total double(3,1) DEFAULT '0.0' NOT NULL,
191 pre_requisit int(11),
193 preco_requisit int(11),
194 PRIMARY KEY (cod_asig)
196 INSERT INTO t1 VALUES (10360,'asdfggfg','Introduccion a los Ordenadores I','asdfggfg','Introduccio Ordinadors I',6.0,NULL,NULL,NULL);
197 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);
198 INSERT INTO t1 VALUES (10362,'Laboratori d`Ordinadors','Laboratorio de Ordenadores','Laboratori d`Ordinadors','Laboratori Ordinadors',4.5,NULL,NULL,NULL);
199 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);
200 INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);
201 INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);
202 INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);
203 INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
205 idAssignatura int(11) DEFAULT '0' NOT NULL,
206 Grup int(11) DEFAULT '0' NOT NULL,
207 Places smallint(6) DEFAULT '0' NOT NULL,
208 PlacesOcupades int(11) DEFAULT '0',
209 PRIMARY KEY (idAssignatura,Grup)
211 INSERT INTO t2 VALUES (10360,12,333,0);
212 INSERT INTO t2 VALUES (10361,30,2,0);
213 INSERT INTO t2 VALUES (10361,40,3,0);
214 INSERT INTO t2 VALUES (10360,45,10,0);
215 INSERT INTO t2 VALUES (10362,10,12,0);
216 INSERT INTO t2 VALUES (10360,55,2,0);
217 INSERT INTO t2 VALUES (10360,70,0,0);
218 INSERT INTO t2 VALUES (10360,565656,0,0);
219 INSERT INTO t2 VALUES (10360,32767,7,0);
220 INSERT INTO t2 VALUES (10360,33,8,0);
221 INSERT INTO t2 VALUES (10360,7887,85,0);
222 INSERT INTO t2 VALUES (11405,88,8,0);
223 INSERT INTO t2 VALUES (10360,0,55,0);
224 INSERT INTO t2 VALUES (10360,99,0,0);
225 INSERT INTO t2 VALUES (11411,30,10,0);
226 INSERT INTO t2 VALUES (11404,0,0,0);
227 INSERT INTO t2 VALUES (10362,11,111,0);
228 INSERT INTO t2 VALUES (10363,33,333,0);
229 INSERT INTO t2 VALUES (11412,55,0,0);
230 INSERT INTO t2 VALUES (50003,66,6,0);
231 INSERT INTO t2 VALUES (11403,5,0,0);
232 INSERT INTO t2 VALUES (11406,11,11,0);
233 INSERT INTO t2 VALUES (11410,11410,131,0);
234 INSERT INTO t2 VALUES (11416,11416,32767,0);
235 INSERT INTO t2 VALUES (11409,0,0,0);
237 id int(11) NOT NULL auto_increment,
238 dni_pasaporte char(16) DEFAULT '' NOT NULL,
239 idPla int(11) DEFAULT '0' NOT NULL,
240 cod_asig int(11) DEFAULT '0' NOT NULL,
241 any smallint(6) DEFAULT '0' NOT NULL,
242 quatrimestre smallint(6) DEFAULT '0' NOT NULL,
243 estat char(1) DEFAULT 'M' NOT NULL,
245 UNIQUE dni_pasaporte (dni_pasaporte,idPla),
246 UNIQUE dni_pasaporte_2 (dni_pasaporte,idPla,cod_asig,any,quatrimestre)
248 INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');
250 id int(11) NOT NULL auto_increment,
251 papa int(11) DEFAULT '0' NOT NULL,
252 fill int(11) DEFAULT '0' NOT NULL,
253 idPla int(11) DEFAULT '0' NOT NULL,
255 KEY papa (idPla,papa),
256 UNIQUE papa_2 (idPla,papa,fill)
258 INSERT INTO t4 VALUES (1,-1,10360,1);
259 INSERT INTO t4 VALUES (2,-1,10361,1);
260 INSERT INTO t4 VALUES (3,-1,10362,1);
261 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;
262 fill desc_larga_cat cred_total Grup Places PlacesOcupades
263 10360 asdfggfg 6.0 0 55 0
264 10360 asdfggfg 6.0 12 333 0
265 10360 asdfggfg 6.0 33 8 0
266 10360 asdfggfg 6.0 45 10 0
267 10360 asdfggfg 6.0 55 2 0
268 10360 asdfggfg 6.0 7887 85 0
269 10360 asdfggfg 6.0 32767 7 0
270 10361 Components i Circuits Electronics I 6.0 30 2 0
271 10361 Components i Circuits Electronics I 6.0 40 3 0
272 10362 Laboratori d`Ordinadors 4.5 10 12 0
273 10362 Laboratori d`Ordinadors 4.5 11 111 0
274 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 ;
279 INSERT INTO t3 VALUES (3,'1234',1,10360,98,1,'S');
280 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 ;
285 drop table t1,t2,t3,test.t4;
287 id smallint(5) unsigned NOT NULL auto_increment,
288 name char(60) DEFAULT '' NOT NULL,
291 INSERT INTO t1 VALUES (1,'Antonio Paz');
292 INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
293 INSERT INTO t1 VALUES (3,'Thimble Smith');
295 id smallint(5) unsigned NOT NULL auto_increment,
296 owner smallint(5) unsigned DEFAULT '0' NOT NULL,
300 INSERT INTO t2 VALUES (1,1,'El Gato');
301 INSERT INTO t2 VALUES (2,1,'Perrito');
302 INSERT INTO t2 VALUES (3,3,'Happy');
303 select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner);
305 Antonio Paz El Gato 1
306 Antonio Paz Perrito 2
307 Lilliana Angelovska NULL NULL
308 Thimble Smith Happy 3
309 select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
311 Lilliana Angelovska NULL NULL
312 explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
313 id select_type table type possible_keys key key_len ref rows Extra
314 1 SIMPLE t1 ALL NULL NULL NULL NULL 3
315 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Not exists
316 explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;
317 id select_type table type possible_keys key key_len ref rows Extra
318 1 SIMPLE t1 ALL NULL NULL NULL NULL 3
319 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
320 select count(*) from t1 left join t2 on (t1.id = t2.owner);
323 select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner);
325 Antonio Paz El Gato 1
326 Antonio Paz Perrito 2
327 Lilliana Angelovska NULL NULL
328 Thimble Smith Happy 3
329 select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
331 Lilliana Angelovska NULL NULL
332 explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
333 id select_type table type possible_keys key key_len ref rows Extra
334 1 SIMPLE t1 ALL NULL NULL NULL NULL 3
335 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Not exists
336 explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;
337 id select_type table type possible_keys key key_len ref rows Extra
338 1 SIMPLE t1 ALL NULL NULL NULL NULL 3
339 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
340 select count(*) from t2 right join t1 on (t1.id = t2.owner);
343 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;
345 Antonio Paz El Gato 1 1
346 Antonio Paz Perrito 2 1
347 Lilliana Angelovska NULL NULL NULL
348 Thimble Smith Happy 3 3
349 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;
351 Antonio Paz El Gato 1 1
352 Antonio Paz Perrito 2 1
354 Thimble Smith Happy 3 3
355 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;
356 name name id owner id
357 Antonio Paz El Gato 1 1 1
358 Antonio Paz Perrito 2 1 1
359 NULL NULL NULL NULL 2
360 Thimble Smith Happy 3 3 3
362 create table t1 (id int not null, str char(10), index(str));
363 insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");
364 select * from t1 where str is not null order by id;
368 select * from t1 where str is null;
374 t1_id bigint(21) NOT NULL auto_increment,
378 t2_id bigint(21) NOT NULL auto_increment,
382 t3_id bigint(21) NOT NULL auto_increment,
386 seq_0_id bigint(21) DEFAULT '0' NOT NULL,
387 seq_1_id bigint(21) DEFAULT '0' NOT NULL,
388 KEY seq_0_id (seq_0_id),
389 KEY seq_1_id (seq_1_id)
392 seq_0_id bigint(21) DEFAULT '0' NOT NULL,
393 seq_1_id bigint(21) DEFAULT '0' NOT NULL,
394 KEY seq_1_id (seq_1_id),
395 KEY seq_0_id (seq_0_id)
397 insert into t1 values (1);
398 insert into t2 values (1);
399 insert into t3 values (1);
400 insert into t4 values (1,1);
401 insert into t5 values (1,1);
402 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;
403 ERROR 42S22: Unknown column 't2.t2_id' in 'on clause'
404 drop table t1,t2,t3,t4,t5;
405 create table t1 (n int, m int, o int, key(n));
406 create table t2 (n int not null, m int, o int, primary key(n));
407 insert into t1 values (1, 2, 11), (1, 2, 7), (2, 2, 8), (1,2,9),(1,3,9);
408 insert into t2 values (1, 2, 3),(2, 2, 8), (4,3,9),(3,2,10);
409 select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
410 t1.m = t2.m where t1.n = 1;
416 select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
417 t1.m = t2.m where t1.n = 1 order by t1.o;
424 CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);
425 INSERT INTO t1 VALUES (1,'a',1);
426 INSERT INTO t1 VALUES (2,'b',1);
427 INSERT INTO t1 VALUES (3,'c',2);
428 CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));
429 INSERT INTO t2 VALUES (1,'x');
430 INSERT INTO t2 VALUES (2,'y');
431 INSERT INTO t2 VALUES (3,'z');
432 SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
435 SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
439 create table t1 ( color varchar(20), name varchar(20) );
440 insert into t1 values ( 'red', 'apple' );
441 insert into t1 values ( 'yellow', 'banana' );
442 insert into t1 values ( 'green', 'lime' );
443 insert into t1 values ( 'black', 'grape' );
444 insert into t1 values ( 'blue', 'blueberry' );
445 create table t2 ( count int, color varchar(20) );
446 insert into t2 values (10, 'green');
447 insert into t2 values (5, 'black');
448 insert into t2 values (15, 'white');
449 insert into t2 values (7, 'green');
463 select * from t2 natural join t1;
468 select t2.count, t1.name from t2 natural join t1;
473 select t2.count, t1.name from t2 inner join t1 using (color);
481 pcode varchar(8) DEFAULT '' NOT NULL
483 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');
485 pcode varchar(8) DEFAULT '' NOT NULL,
488 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');
489 SELECT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1
490 LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;
533 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;
584 INSERT INTO t1 VALUES (1,NULL,NULL);
585 INSERT INTO t1 VALUES (2,1,NULL);
586 select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
587 id pid rep_del id pid rep_del
589 2 1 NULL NULL NULL NULL
590 create index rep_del ON t1(rep_del);
591 select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
592 id pid rep_del id pid rep_del
594 2 1 NULL NULL NULL NULL
597 id int(11) DEFAULT '0' NOT NULL,
598 name tinytext DEFAULT '' NOT NULL,
602 Warning 1101 BLOB/TEXT column 'name' can't have a default value
603 INSERT INTO t1 VALUES (1,'yes'),(2,'no');
605 id int(11) DEFAULT '0' NOT NULL,
606 idx int(11) DEFAULT '0' NOT NULL,
609 INSERT INTO t2 VALUES (1,1);
610 explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
611 id select_type table type possible_keys key key_len ref rows Extra
612 1 SIMPLE t1 ALL NULL NULL NULL NULL 2
613 1 SIMPLE t2 ref id id 4 test.t1.id 1 Using where; Using index; Not exists
614 SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
618 create table t1 (bug_id mediumint, reporter mediumint);
619 create table t2 (bug_id mediumint, who mediumint, index(who));
620 insert into t2 values (1,1),(1,2);
621 insert into t1 values (1,1),(2,1);
622 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);
623 bug_id reporter bug_id who
626 create table t1 (fooID smallint unsigned auto_increment, primary key (fooID));
627 create table t2 (fooID smallint unsigned not null, barID smallint unsigned not null, primary key (fooID,barID));
628 insert into t1 (fooID) values (10),(20),(30);
629 insert into t2 values (10,1),(20,2),(30,3);
630 explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
631 id select_type table type possible_keys key key_len ref rows Extra
632 1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using index
633 1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using index
634 select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
639 select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;
645 create table t1 (i int);
646 create table t2 (i int);
647 create table t3 (i int);
648 insert into t1 values(1),(2);
649 insert into t2 values(2),(3);
650 insert into t3 values(2),(4);
651 select * from t1 natural left join t2 natural left join t3;
655 select * from t1 natural left join t2 where (t2.i is not null)=0;
658 select * from t1 natural left join t2 where (t2.i is not null) is not null;
662 select * from t1 natural left join t2 where (i is not null)=0;
664 select * from t1 natural left join t2 where (i is not null) is not null;
669 create table t1 (f1 integer,f2 integer,f3 integer);
670 create table t2 (f2 integer,f4 integer);
671 create table t3 (f3 integer,f5 integer);
673 left outer join t2 using (f2)
674 left outer join t3 using (f3);
677 create table t1 (a1 int, a2 int);
678 create table t2 (b1 int not null, b2 int);
679 create table t3 (c1 int, c2 int);
680 insert into t1 values (1,2), (2,2), (3,2);
681 insert into t2 values (1,3), (2,3);
682 insert into t3 values (2,4), (3,4);
683 select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;
688 explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;
689 id select_type table type possible_keys key key_len ref rows Extra
690 1 SIMPLE t1 ALL NULL NULL NULL NULL 3
691 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
692 1 SIMPLE t3 ALL NULL NULL NULL NULL 2
693 drop table t1, t2, t3;
699 insert into t1 (a) values (1),(2),(3),(4);
700 create table t2 (a int);
701 select * from t1 left join t2 on t1.a=t2.a where not (t2.a <=> t1.a);
707 select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a);
715 match_id tinyint(3) unsigned not null auto_increment,
716 home tinyint(3) unsigned default '0',
717 unique key match_id (match_id),
718 key match_id_2 (match_id)
720 insert into t1 values("1", "2");
722 player_id tinyint(3) unsigned default '0',
723 match_1_h tinyint(3) unsigned default '0',
724 key player_id (player_id)
726 insert into t2 values("1", "5");
727 insert into t2 values("2", "9");
728 insert into t2 values("3", "3");
729 insert into t2 values("4", "7");
730 insert into t2 values("5", "6");
731 insert into t2 values("6", "8");
732 insert into t2 values("7", "4");
733 insert into t2 values("8", "12");
734 insert into t2 values("9", "11");
735 insert into t2 values("10", "10");
736 explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
737 (t2 s left join t1 m on m.match_id = 1)
738 order by m.match_id desc;
739 id select_type table type possible_keys key key_len ref rows Extra
740 1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
741 1 SIMPLE m const match_id,match_id_2 match_id 1 const 1
742 explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
743 (t2 s left join t1 m on m.match_id = 1)
745 id select_type table type possible_keys key key_len ref rows Extra
746 1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
747 1 SIMPLE m const match_id,match_id_2 match_id 1 const 1
748 select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
749 (t2 s left join t1 m on m.match_id = 1)
751 player_id match_1_h * match_id home UUX
762 explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
763 t2 s straight_join t1 m where m.match_id = 1
765 id select_type table type possible_keys key key_len ref rows Extra
766 1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
767 1 SIMPLE m const match_id,match_id_2 match_id 1 const 1
768 select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
769 t2 s straight_join t1 m where m.match_id = 1
771 player_id match_1_h * match_id home UUX
783 create table t1 (a int, b int, unique index idx (a, b));
784 create table t2 (a int, b int, c int, unique index idx (a, b));
785 insert into t1 values (1, 10), (1,11), (2,10), (2,11);
786 insert into t2 values (1,10,3);
787 select t1.a, t1.b, t2.c from t1 left join t2
788 on t1.a=t2.a and t1.b=t2.b and t2.c=3
789 where t1.a=1 and t2.c is null;
794 ts_id bigint(20) default NULL,
795 inst_id tinyint(4) default NULL,
796 flag_name varchar(64) default NULL,
798 UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
799 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
801 ts_id bigint(20) default NULL,
802 inst_id tinyint(4) default NULL,
803 flag_name varchar(64) default NULL,
805 UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
806 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
807 INSERT INTO t1 VALUES
808 (111056548820001, 0, 'flag1', NULL),
809 (111056548820001, 0, 'flag2', NULL),
810 (2, 0, 'other_flag', NULL);
811 INSERT INTO t2 VALUES
812 (111056548820001, 3, 'flag1', 'sss');
813 SELECT t1.flag_name,t2.flag_value
815 ON (t1.ts_id = t2.ts_id AND t1.flag_name = t2.flag_name AND
817 WHERE t1.inst_id = 0 AND t1.ts_id=111056548820001 AND
818 t2.flag_value IS NULL;
823 id int(11) unsigned NOT NULL auto_increment,
824 text_id int(10) unsigned default NULL,
827 INSERT INTO t1 VALUES("1", "0");
828 INSERT INTO t1 VALUES("2", "10");
830 text_id char(3) NOT NULL default '',
831 language_id char(3) NOT NULL default '',
833 PRIMARY KEY (text_id,language_id)
835 INSERT INTO t2 VALUES("0", "EN", "0-EN");
836 INSERT INTO t2 VALUES("0", "SV", "0-SV");
837 INSERT INTO t2 VALUES("10", "EN", "10-EN");
838 INSERT INTO t2 VALUES("10", "SV", "10-SV");
839 SELECT t1.id, t1.text_id, t2.text_data
841 ON t1.text_id = t2.text_id
842 AND t2.language_id = 'SV'
843 WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%');
848 CREATE TABLE t0 (a0 int PRIMARY KEY);
849 CREATE TABLE t1 (a1 int PRIMARY KEY);
850 CREATE TABLE t2 (a2 int);
851 CREATE TABLE t3 (a3 int);
852 INSERT INTO t0 VALUES (1);
853 INSERT INTO t1 VALUES (1);
854 INSERT INTO t2 VALUES (1), (2);
855 INSERT INTO t3 VALUES (1), (2);
856 SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
859 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
860 id select_type table type possible_keys key key_len ref rows Extra
861 1 SIMPLE t1 system NULL NULL NULL NULL 1
862 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
863 SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
866 EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
867 id select_type table type possible_keys key key_len ref rows Extra
868 1 SIMPLE t1 system NULL NULL NULL NULL 1
869 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
870 1 SIMPLE t3 ALL NULL NULL NULL NULL 2
871 SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
874 EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
875 id select_type table type possible_keys key key_len ref rows Extra
876 1 SIMPLE t0 system PRIMARY NULL NULL NULL 1
877 1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
878 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
879 1 SIMPLE t3 ALL NULL NULL NULL NULL 2
880 INSERT INTO t0 VALUES (0);
881 INSERT INTO t1 VALUES (0);
882 SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
885 EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
886 id select_type table type possible_keys key key_len ref rows Extra
887 1 SIMPLE t0 const PRIMARY PRIMARY 4 const 1 Using index
888 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
889 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
890 1 SIMPLE t3 ALL NULL NULL NULL NULL 2
892 create table t1 (a int, b int);
893 insert into t1 values (1,1),(2,2),(3,3);
894 create table t2 (a int, b int);
895 insert into t2 values (1,1), (2,2);
896 select * from t2 right join t1 on t2.a=t1.a;
901 select straight_join * from t2 right join t1 on t2.a=t1.a;
906 DROP TABLE t0,t1,t2,t3;
907 CREATE TABLE t1 (a int PRIMARY KEY, b int);
908 CREATE TABLE t2 (a int PRIMARY KEY, b int);
909 INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
910 INSERT INTO t2 VALUES (1,2), (2,2);
911 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
917 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
922 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
923 WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
928 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
935 set group_concat_max_len=5;
936 create table t1 (a int, b varchar(20));
937 create table t2 (a int, c varchar(20));
938 insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
939 insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
940 select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
941 group_concat(t1.b,t2.c)
945 Warning 1260 2 line(s) were cut by GROUP_CONCAT()
946 select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
947 group_concat(t1.b,t2.c)
951 Warning 1260 2 line(s) were cut by GROUP_CONCAT()
952 select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
953 group_concat(t1.b,t2.c)
957 Warning 1260 2 line(s) were cut by GROUP_CONCAT()
958 select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
959 group_concat(t1.b,t2.c)
963 Warning 1260 2 line(s) were cut by GROUP_CONCAT()
965 set group_concat_max_len=default;
966 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));
967 insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
968 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));
969 insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
970 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));
971 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');
972 explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
973 left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
974 and t1.gid =1 and t2.gid =1 and t3.set_id =1;
975 id select_type table type possible_keys key key_len ref rows Extra
976 1 SIMPLE t1 const PRIMARY PRIMARY 10 const,const,const 1
977 1 SIMPLE t2 const PRIMARY,id PRIMARY 14 const,const,const,const 1 Using index
978 1 SIMPLE t3 const PRIMARY PRIMARY 3 const,const 1
979 drop tables t1,t2,t3;
980 CREATE TABLE t1 (EMPNUM INT, GRP INT);
981 INSERT INTO t1 VALUES (0, 10);
982 INSERT INTO t1 VALUES (2, 30);
983 CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));
984 INSERT INTO t2 VALUES (0, 'KERI');
985 INSERT INTO t2 VALUES (9, 'BARRY');
987 SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP
988 FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;
993 SELECT * FROM v1 WHERE EMPNUM < 10;
999 CREATE TABLE t1 (c11 int);
1000 CREATE TABLE t2 (c21 int);
1001 INSERT INTO t1 VALUES (30), (40), (50);
1002 INSERT INTO t2 VALUES (300), (400), (500);
1003 SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;
1007 CREATE TABLE t1 (a int PRIMARY KEY, b int);
1008 CREATE TABLE t2 (a int PRIMARY KEY, b int);
1009 INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
1010 INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
1011 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
1014 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
1017 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
1020 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
1027 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
1034 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
1041 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;
1049 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);
1057 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);
1062 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);
1067 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
1073 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
1079 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
1085 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
1091 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
1097 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
1103 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);
1109 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));
1115 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;
1121 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));
1127 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
1128 id select_type table type possible_keys key key_len ref rows Extra
1129 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4
1130 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
1131 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
1132 id select_type table type possible_keys key key_len ref rows Extra
1133 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where
1134 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
1135 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);
1136 id select_type table type possible_keys key key_len ref rows Extra
1137 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where
1138 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
1140 DROP VIEW IF EXISTS v1,v2;
1141 DROP TABLE IF EXISTS t1,t2;
1142 CREATE TABLE t1 (a int);
1143 CREATE table t2 (b int);
1144 INSERT INTO t1 VALUES (1), (2), (3), (4), (1), (1), (3);
1145 INSERT INTO t2 VALUES (2), (3);
1146 CREATE VIEW v1 AS SELECT a FROM t1 JOIN t2 ON t1.a=t2.b;
1147 CREATE VIEW v2 AS SELECT b FROM t2 JOIN t1 ON t2.b=t1.a;
1149 FROM v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3)
1155 FROM { OJ v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3) }
1162 CREATE TABLE t1 (a int);
1163 CREATE TABLE t2 (b int);
1164 INSERT INTO t1 VALUES (1), (2), (3), (4);
1165 INSERT INTO t2 VALUES (2), (3);
1166 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);
1172 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
1178 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
1184 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
1190 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
1198 f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
1199 f2 varchar(16) collate latin1_swedish_ci
1202 f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
1203 f3 varchar(16) collate latin1_swedish_ci
1205 INSERT INTO t1 VALUES ('bla','blah');
1206 INSERT INTO t2 VALUES ('bla','sheep');
1207 SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
1210 SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
1213 SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
1217 CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
1218 CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
1219 INSERT INTO t1 VALUES
1220 (1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
1221 INSERT INTO t2 VALUES
1222 (3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
1224 SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
1225 id select_type table type possible_keys key key_len ref rows Extra
1226 1 SIMPLE t1 ALL NULL NULL NULL NULL 5
1227 1 SIMPLE t2 ref idx idx 4 test.t1.id 2 Using where; Not exists
1229 SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
1233 show status like 'Handler_read%';
1235 Handler_read_first 0
1240 Handler_read_rnd_next 6
1242 CREATE TABLE t1 (c int PRIMARY KEY, e int NOT NULL);
1243 INSERT INTO t1 VALUES (1,0), (2,1);
1244 CREATE TABLE t2 (d int PRIMARY KEY);
1245 INSERT INTO t2 VALUES (1), (2), (3);
1246 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
1247 id select_type table type possible_keys key key_len ref rows Extra
1248 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1249 1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Not exists
1250 SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
1253 SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
1258 # Bug#47650: using group by with rollup without indexes returns incorrect
1259 # results with where
1261 CREATE TABLE t1 ( a INT );
1262 INSERT INTO t1 VALUES (1);
1263 CREATE TABLE t2 ( a INT, b INT );
1264 INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5);
1266 SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
1267 FROM t1 LEFT JOIN t2 USING( a )
1268 GROUP BY t1.a WITH ROLLUP;
1269 id select_type table type possible_keys key key_len ref rows Extra
1270 1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort
1271 1 SIMPLE t2 ALL NULL NULL NULL NULL 5
1272 SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
1273 FROM t1 LEFT JOIN t2 USING( a )
1274 GROUP BY t1.a WITH ROLLUP;
1275 a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b )
1279 SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
1280 FROM t1 JOIN t2 USING( a )
1281 GROUP BY t1.a WITH ROLLUP;
1282 id select_type table type possible_keys key key_len ref rows Extra
1283 1 SIMPLE t1 system NULL NULL NULL NULL 1 Using filesort
1284 1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where
1285 SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
1286 FROM t1 JOIN t2 USING( a )
1287 GROUP BY t1.a WITH ROLLUP;
1288 a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b )
1293 # Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison
1295 CREATE TABLE t1(f1 INT, f2 INT, f3 INT);
1296 INSERT INTO t1 VALUES (1, NULL, 3);
1297 CREATE TABLE t2(f1 INT, f2 INT);
1298 INSERT INTO t2 VALUES (2, 1);
1299 EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
1300 WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
1301 id select_type table type possible_keys key key_len ref rows filtered Extra
1302 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
1303 1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00
1305 Note 1003 select '1' AS `f1`,NULL AS `f2`,'3' AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t1` left join `test`.`t2` on(multiple equal(NULL)) where ((coalesce('1',NULL),'3') in ((1,3),(2,2)))
1306 SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
1307 WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
1312 # Bug#52357: Assertion failed: join->best_read in greedy_search
1313 # optimizer_search_depth=0
1315 CREATE TABLE t1( a INT );
1316 INSERT INTO t1 VALUES (1),(2);
1317 SET optimizer_search_depth = 0;
1318 # Should not core dump on query preparation
1321 FROM t1 tt3 LEFT OUTER JOIN t1 tt4 ON 1
1322 LEFT OUTER JOIN t1 tt5 ON 1
1323 LEFT OUTER JOIN t1 tt6 ON 1
1324 LEFT OUTER JOIN t1 tt7 ON 1
1325 LEFT OUTER JOIN t1 tt8 ON 1
1326 RIGHT OUTER JOIN t1 tt2 ON 1
1327 RIGHT OUTER JOIN t1 tt1 ON 1
1328 STRAIGHT_JOIN t1 tt9 ON 1;
1329 id select_type table type possible_keys key key_len ref rows Extra
1330 1 SIMPLE tt1 ALL NULL NULL NULL NULL 2
1331 1 SIMPLE tt2 ALL NULL NULL NULL NULL 2
1332 1 SIMPLE tt3 ALL NULL NULL NULL NULL 2
1333 1 SIMPLE tt4 ALL NULL NULL NULL NULL 2
1334 1 SIMPLE tt5 ALL NULL NULL NULL NULL 2
1335 1 SIMPLE tt6 ALL NULL NULL NULL NULL 2
1336 1 SIMPLE tt7 ALL NULL NULL NULL NULL 2
1337 1 SIMPLE tt8 ALL NULL NULL NULL NULL 2
1338 1 SIMPLE tt9 ALL NULL NULL NULL NULL 2 Using join buffer
1339 SET optimizer_search_depth = DEFAULT;
1342 # Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result
1344 CREATE TABLE t1 (f1 INT NOT NULL);
1345 INSERT INTO t1 VALUES (9),(0);
1346 CREATE TABLE t2 (f1 INT NOT NULL);
1347 INSERT INTO t2 VALUES
1348 (5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1);
1349 SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1
1350 RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;
1353 EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1
1354 RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;
1355 id select_type table type possible_keys key key_len ref rows Extra
1356 1 SIMPLE TA2 ALL NULL NULL NULL NULL 20 Using where
1357 1 SIMPLE TA3 ALL NULL NULL NULL NULL 20 Using join buffer
1358 1 SIMPLE TA1 ALL NULL NULL NULL NULL 2
1361 # Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990
1363 CREATE TABLE t1(f1 INT, PRIMARY KEY (f1));
1364 INSERT INTO t1 VALUES (1),(2);
1365 EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
1367 RIGHT JOIN t1 AS jt3
1369 LEFT JOIN t1 AS jt5 ON 1
1371 RIGHT JOIN t1 AS jt6 ON jt6.f1
1373 id select_type table type possible_keys key key_len ref rows filtered Extra
1374 1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index
1375 1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index
1376 1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using index
1377 1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index
1378 1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using index
1379 1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using index
1381 Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1))) on(1) where 1
1382 EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
1383 RIGHT JOIN t1 AS jt2
1384 RIGHT JOIN t1 AS jt3
1386 LEFT JOIN t1 AS jt5 ON 1
1388 RIGHT JOIN t1 AS jt6 ON jt6.f1
1390 id select_type table type possible_keys key key_len ref rows filtered Extra
1391 1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index
1392 1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using index
1393 1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index
1394 1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using index
1395 1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using index
1396 1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index
1398 Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1
1401 # Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field
1403 CREATE TABLE t1 (f1 INT NOT NULL, PRIMARY KEY (f1));
1404 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (f1, f2));
1405 INSERT INTO t1 VALUES (4);
1406 INSERT INTO t2 VALUES (3, 3);
1407 INSERT INTO t2 VALUES (7, 7);
1408 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
1410 GROUP BY t2.f1, t2.f2;
1411 id select_type table type possible_keys key key_len ref rows Extra
1412 1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using temporary; Using filesort
1413 1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index
1414 SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
1416 GROUP BY t2.f1, t2.f2;
1419 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
1420 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
1421 GROUP BY t2.f1, t2.f2;
1422 id select_type table type possible_keys key key_len ref rows Extra
1423 1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort
1424 1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index
1425 SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
1426 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
1427 GROUP BY t2.f1, t2.f2;
1431 # BUG#12567331 - INFINITE LOOP WHEN RESOLVING AN ALIASED COLUMN
1435 col_varchar_1024_latin1_key varchar(1024),
1436 col_varchar_10_latin1 varchar(10),
1441 col_int_key int(11),
1445 PREPARE prep_stmt_9846 FROM '
1446 SELECT alias1.pk AS field1 FROM
1461 execute prep_stmt_9846;
1463 execute prep_stmt_9846;
1467 # Bug #11765810 58813: SERVER THREAD HANGS WHEN JOIN + WHERE + GROUP BY
1468 # IS EXECUTED TWICE FROM P
1470 CREATE TABLE t1 ( a INT ) ENGINE = MYISAM;
1471 INSERT INTO t1 VALUES (1);
1472 PREPARE prep_stmt FROM '
1473 SELECT 1 AS f FROM t1
1489 # Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS
1492 CREATE TABLE t1 (i1 int);
1493 INSERT INTO t1 VALUES (100), (101);
1494 CREATE TABLE t2 (i2 int, i3 int);
1495 INSERT INTO t2 VALUES (20,1),(10,2);
1496 CREATE TABLE t3 (i4 int(11));
1497 INSERT INTO t3 VALUES (1),(2);
1501 FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
1502 WHERE t2.i3 <> t1.i1
1511 FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
1512 WHERE t2.i3 <> t1.i1
1514 FROM t1 GROUP BY field1;;
1518 drop table t1,t2,t3;
1519 # End of test for Bug#13068506