| blob | f745e953331e0ba79fbbbf05555a53c58e5d6138 |
1 drop table if exists t0,t1,t2,t3,t4,t5;
2 CREATE TABLE t1 (
3 grp int(11) default NULL,
4 a bigint(20) unsigned default NULL,
5 c char(10) NOT NULL default ''
6 ) ENGINE=MyISAM;
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;
11 grp a c id a c d
12 1 1 a 1 1 a 1
13 3 4 E 3 4 A 4
14 3 5 C 3 5 B 5
15 3 6 D 3 6 C 6
16 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
17 grp a c id a c d
18 NULL NULL NULL NULL NULL NULL
19 1 1 a 1 1 a 1
20 2 2 b NULL NULL NULL NULL
21 2 3 c NULL NULL NULL NULL
22 3 4 E 3 4 A 4
23 3 5 C 3 5 B 5
24 3 6 D 3 6 C 6
25 select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) };
26 grp a c id a c d
27 1 1 a 1 1 a 1
28 3 4 E 3 4 A 4
29 3 5 C 3 5 B 5
30 3 6 D 3 6 C 6
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;
33 grp a c id a c d
34 1 1 a 1 1 a 1
35 3 4 E 3 4 A 4
36 3 5 C 3 5 B 5
37 3 6 D 3 6 C 6
38 NULL NULL NULL 4 7 D 7
39 select t1.*,t2.* from t1 left join t2 using (a);
40 grp a c id a c d
41 1 1 a 1 1 a 1
42 2 2 b NULL NULL NULL NULL
43 2 3 c NULL NULL NULL NULL
44 3 4 E 3 4 A 4
45 3 5 C 3 5 B 5
46 3 6 D 3 6 C 6
47 NULL NULL NULL NULL NULL NULL
48 select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
49 grp a c id a c d
50 1 1 a 1 1 a 1
51 3 4 E 3 4 A 4
52 3 5 C 3 5 B 5
53 3 6 D 3 6 C 6
54 select t1.*,t2.* from t1 left join t2 using (a,c);
55 grp a c id a c d
56 1 1 a 1 1 a 1
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);
64 grp a c id a c d
65 1 1 a 1 1 a 1
66 1 1 a 3 4 A 4
67 2 2 b 3 5 B 5
68 2 3 c 3 6 C 6
69 3 4 E NULL NULL NULL NULL
70 3 5 C 3 6 C 6
71 3 6 D 4 7 D 7
72 NULL NULL NULL NULL NULL NULL
73 select t1.*,t2.* from t1 natural left outer join t2;
74 grp a c id a c d
75 1 1 a 1 1 a 1
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;
83 grp a c id a c d
84 3 4 E 3 4 A 4
85 3 5 C 3 5 B 5
86 3 6 D 3 6 C 6
87 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
88 grp a c id a c d
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);
100 grp a c id a c d a
101 1 1 a 1 1 a 1 1
102 2 2 b NULL NULL NULL NULL NULL
103 2 3 c NULL NULL NULL NULL NULL
104 3 4 E 3 4 A 4 4
105 3 5 C 3 5 B 5 5
106 3 6 D 3 6 C 6 6
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);
115 grp a c id a c d
116 1 1 a 1 1 a 1
117 3 4 E 3 4 A 4
118 3 5 C 3 5 B 5
119 3 6 D 3 6 C 6
120 select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
121 grp a c id a c d
122 1 1 a 1 1 a 1
123 3 4 E 3 4 A 4
124 3 5 C 3 5 B 5
125 3 6 D 3 6 C 6
126 select t1.*,t2.* from t1 natural join t2;
127 grp a c id a c d
128 1 1 a 1 1 a 1
129 drop table t1,t2;
130 CREATE TABLE t1 (
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)
138 );
139 CREATE TABLE t2 (
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),
150 INDEX id_idx (id),
151 INDEX usr2_idx (usr2_id)
152 );
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
156 FROM t1
157 LEFT JOIN t2 ON t2.id = t1.uniq_id
158 WHERE t1.uniq_id = 4
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
164 FROM t2
165 RIGHT JOIN t1 ON t2.id = t1.uniq_id
166 WHERE t1.uniq_id = 4
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
183 drop table t1,t2;
184 CREATE TABLE t1 (
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),
192 co_requisit int(11),
193 preco_requisit int(11),
194 PRIMARY KEY (cod_asig)
195 );
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);
204 CREATE TABLE t2 (
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)
210 );
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);
236 CREATE TABLE t3 (
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,
244 PRIMARY KEY (id),
245 UNIQUE dni_pasaporte (dni_pasaporte,idPla),
246 UNIQUE dni_pasaporte_2 (dni_pasaporte,idPla,cod_asig,any,quatrimestre)
247 );
248 INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');
249 CREATE TABLE t4 (
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,
254 PRIMARY KEY (id),
255 KEY papa (idPla,papa),
256 UNIQUE papa_2 (idPla,papa,fill)
257 );
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 ;
275 fill idPla
276 10360 NULL
277 10361 NULL
278 10362 NULL
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 ;
281 fill idPla
282 10360 1
283 10361 NULL
284 10362 NULL
285 drop table t1,t2,t3,test.t4;
286 CREATE TABLE t1 (
287 id smallint(5) unsigned NOT NULL auto_increment,
288 name char(60) DEFAULT '' NOT NULL,
289 PRIMARY KEY (id)
290 );
291 INSERT INTO t1 VALUES (1,'Antonio Paz');
292 INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
293 INSERT INTO t1 VALUES (3,'Thimble Smith');
294 CREATE TABLE t2 (
295 id smallint(5) unsigned NOT NULL auto_increment,
296 owner smallint(5) unsigned DEFAULT '0' NOT NULL,
297 name char(60),
298 PRIMARY KEY (id)
299 );
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);
304 name name id
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;
310 name name id
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);
321 count(*)
322 4
323 select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner);
324 name name id
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;
330 name name id
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);
341 count(*)
342 4
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;
344 name name id id
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;
350 name name id id
351 Antonio Paz El Gato 1 1
352 Antonio Paz Perrito 2 1
353 NULL NULL NULL 2
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
361 drop table t1,t2;
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;
365 id str
366 3 foo
367 4 bar
368 select * from t1 where str is null;
369 id str
370 1 NULL
371 2 NULL
372 drop table t1;
373 CREATE TABLE t1 (
374 t1_id bigint(21) NOT NULL auto_increment,
375 PRIMARY KEY (t1_id)
376 );
377 CREATE TABLE t2 (
378 t2_id bigint(21) NOT NULL auto_increment,
379 PRIMARY KEY (t2_id)
380 );
381 CREATE TABLE t3 (
382 t3_id bigint(21) NOT NULL auto_increment,
383 PRIMARY KEY (t3_id)
384 );
385 CREATE TABLE t4 (
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)
390 );
391 CREATE TABLE t5 (
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)
396 );
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;
411 n m o n m o
412 1 2 11 1 2 3
413 1 2 7 1 2 3
414 1 2 9 1 2 3
415 1 3 9 NULL NULL NULL
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;
418 n m o n m o
419 1 2 7 1 2 3
420 1 2 9 1 2 3
421 1 3 9 NULL NULL NULL
422 1 2 11 1 2 3
423 drop table t1,t2;
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;
433 id2
434 3
435 SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
436 id2
437 3
438 drop table t1,t2;
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');
450 select * from t1;
451 color name
452 red apple
453 yellow banana
454 green lime
455 black grape
456 blue blueberry
457 select * from t2;
458 count color
459 10 green
460 5 black
461 15 white
462 7 green
463 select * from t2 natural join t1;
464 color count name
465 green 10 lime
466 green 7 lime
467 black 5 grape
468 select t2.count, t1.name from t2 natural join t1;
469 count name
470 10 lime
471 7 lime
472 5 grape
473 select t2.count, t1.name from t2 inner join t1 using (color);
474 count name
475 10 lime
476 7 lime
477 5 grape
478 drop table t1;
479 drop table t2;
480 CREATE TABLE t1 (
481 pcode varchar(8) DEFAULT '' NOT NULL
482 );
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');
484 CREATE TABLE t2 (
485 pcode varchar(8) DEFAULT '' NOT NULL,
486 KEY pcode (pcode)
487 );
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;
491 pcode count
492 kld2000 1
493 klw1000 0
494 klw1020 0
495 klw1500 0
496 klw2000 0
497 klw2001 0
498 klw2002 0
499 klw2500 0
500 kmw1000 0
501 kmw1500 0
502 kmw2000 0
503 kmw2001 0
504 kmw2100 0
505 kmw3000 0
506 kmw3200 0
507 kvw2000 26
508 kvw2001 0
509 kvw3000 36
510 kvw3001 0
511 kvw3002 0
512 kvw3500 26
513 kvw3501 0
514 kvw3502 0
515 kvw3800 0
516 kvw3801 0
517 kvw3802 0
518 kvw3900 0
519 kvw3901 0
520 kvw3902 0
521 kvw4000 0
522 kvw4001 0
523 kvw4002 0
524 kvw4200 0
525 kvw4500 0
526 kvw5000 0
527 kvw5001 0
528 kvw5500 0
529 kvw5510 0
530 kvw5600 0
531 kvw5601 0
532 kvw6000 2
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;
534 pcode count
535 kld2000 1
536 klw1000 0
537 klw1020 0
538 klw1500 0
539 klw2000 0
540 klw2001 0
541 klw2002 0
542 klw2500 0
543 kmw1000 0
544 kmw1500 0
545 kmw2000 0
546 kmw2001 0
547 kmw2100 0
548 kmw3000 0
549 kmw3200 0
550 kvw2000 26
551 kvw2001 0
552 kvw3000 36
553 kvw3001 0
554 kvw3002 0
555 kvw3500 26
556 kvw3501 0
557 kvw3502 0
558 kvw3800 0
559 kvw3801 0
560 kvw3802 0
561 kvw3900 0
562 kvw3901 0
563 kvw3902 0
564 kvw4000 0
565 kvw4001 0
566 kvw4002 0
567 kvw4200 0
568 kvw4500 0
569 kvw5000 0
570 kvw5001 0
571 kvw5500 0
572 kvw5510 0
573 kvw5600 0
574 kvw5601 0
575 kvw6000 2
576 drop table t1,t2;
577 CREATE TABLE t1 (
578 id int(11),
579 pid int(11),
580 rep_del tinyint(4),
581 KEY id (id),
582 KEY pid (pid)
583 );
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
588 1 NULL NULL 2 1 NULL
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
593 1 NULL NULL 2 1 NULL
594 2 1 NULL NULL NULL NULL
595 drop table t1;
596 CREATE TABLE t1 (
597 id int(11) DEFAULT '0' NOT NULL,
598 name tinytext DEFAULT '' NOT NULL,
599 UNIQUE id (id)
600 );
601 Warnings:
602 Warning 1101 BLOB/TEXT column 'name' can't have a default value
603 INSERT INTO t1 VALUES (1,'yes'),(2,'no');
604 CREATE TABLE t2 (
605 id int(11) DEFAULT '0' NOT NULL,
606 idx int(11) DEFAULT '0' NOT NULL,
607 UNIQUE id (id,idx)
608 );
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;
615 id name id idx
616 2 no NULL NULL
617 drop table t1,t2;
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
624 1 1 1 2
625 drop table t1,t2;
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;
635 fooID barID fooID
636 10 1 NULL
637 20 2 NULL
638 30 3 30
639 select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;
640 fooID barID fooID
641 10 1 NULL
642 20 2 NULL
643 30 3 30
644 drop table t1,t2;
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;
652 i
653 1
654 2
655 select * from t1 natural left join t2 where (t2.i is not null)=0;
656 i
657 1
658 select * from t1 natural left join t2 where (t2.i is not null) is not null;
659 i
660 1
661 2
662 select * from t1 natural left join t2 where (i is not null)=0;
663 i
664 select * from t1 natural left join t2 where (i is not null) is not null;
665 i
666 1
667 2
668 drop table t1,t2,t3;
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);
672 select * from t1
673 left outer join t2 using (f2)
674 left outer join t3 using (f3);
675 f3 f2 f1 f4 f5
676 drop table t1,t2,t3;
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;
684 a1 a2 b1 b2 c1 c2
685 1 2 1 3 NULL NULL
686 2 2 2 3 NULL NULL
687 3 2 NULL NULL 3 4
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;
694 create table t1 (
695 a int(11),
696 b char(10),
697 key (a)
698 );
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);
702 a b a
703 1 NULL NULL
704 2 NULL NULL
705 3 NULL NULL
706 4 NULL NULL
707 select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a);
708 a b a
709 1 NULL NULL
710 2 NULL NULL
711 3 NULL NULL
712 4 NULL NULL
713 drop table t1,t2;
714 create table t1 (
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)
719 );
720 insert into t1 values("1", "2");
721 create table t2 (
722 player_id tinyint(3) unsigned default '0',
723 match_1_h tinyint(3) unsigned default '0',
724 key player_id (player_id)
725 );
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)
744 order by UUX desc;
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)
750 order by UUX desc;
751 player_id match_1_h * match_id home UUX
752 8 12 * 1 2 10
753 9 11 * 1 2 9
754 10 10 * 1 2 8
755 2 9 * 1 2 7
756 6 8 * 1 2 6
757 4 7 * 1 2 5
758 5 6 * 1 2 4
759 1 5 * 1 2 3
760 7 4 * 1 2 2
761 3 3 * 1 2 1
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
764 order by UUX desc;
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
770 order by UUX desc;
771 player_id match_1_h * match_id home UUX
772 8 12 * 1 2 10
773 9 11 * 1 2 9
774 10 10 * 1 2 8
775 2 9 * 1 2 7
776 6 8 * 1 2 6
777 4 7 * 1 2 5
778 5 6 * 1 2 4
779 1 5 * 1 2 3
780 7 4 * 1 2 2
781 3 3 * 1 2 1
782 drop table t1, t2;
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;
790 a b c
791 1 11 NULL
792 drop table t1, t2;
793 CREATE TABLE t1 (
794 ts_id bigint(20) default NULL,
795 inst_id tinyint(4) default NULL,
796 flag_name varchar(64) default NULL,
797 flag_value text,
798 UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
799 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
800 CREATE TABLE t2 (
801 ts_id bigint(20) default NULL,
802 inst_id tinyint(4) default NULL,
803 flag_name varchar(64) default NULL,
804 flag_value text,
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
814 FROM t1 LEFT JOIN t2
815 ON (t1.ts_id = t2.ts_id AND t1.flag_name = t2.flag_name AND
816 t2.inst_id = 3)
817 WHERE t1.inst_id = 0 AND t1.ts_id=111056548820001 AND
818 t2.flag_value IS NULL;
819 flag_name flag_value
820 flag2 NULL
821 DROP TABLE t1,t2;
822 CREATE TABLE t1 (
823 id int(11) unsigned NOT NULL auto_increment,
824 text_id int(10) unsigned default NULL,
825 PRIMARY KEY (id)
826 );
827 INSERT INTO t1 VALUES("1", "0");
828 INSERT INTO t1 VALUES("2", "10");
829 CREATE TABLE t2 (
830 text_id char(3) NOT NULL default '',
831 language_id char(3) NOT NULL default '',
832 text_data text,
833 PRIMARY KEY (text_id,language_id)
834 );
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
840 FROM t1 LEFT JOIN t2
841 ON t1.text_id = t2.text_id
842 AND t2.language_id = 'SV'
843 WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%');
844 id text_id text_data
845 1 0 0-SV
846 2 10 10-SV
847 DROP TABLE t1, t2;
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;
857 a1 a2
858 1 NULL
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;
864 a1 a2 a3
865 1 NULL NULL
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;
872 a0 a1 a2 a3
873 1 1 NULL NULL
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;
883 a0 a1 a2 a3
884 1 1 NULL NULL
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
891 drop table t1,t2;
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;
897 a b a b
898 1 1 1 1
899 2 2 2 2
900 NULL NULL 3 3
901 select straight_join * from t2 right join t1 on t2.a=t1.a;
902 a b a b
903 1 1 1 1
904 2 2 2 2
905 NULL NULL 3 3
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;
912 a b a b
913 1 1 1 2
914 2 1 2 2
915 3 1 NULL NULL
916 4 2 NULL NULL
917 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
918 a b a b
919 1 1 1 2
920 2 1 2 2
921 3 1 NULL NULL
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);
924 a b a b
925 1 1 1 2
926 2 1 2 2
927 3 1 NULL NULL
928 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
929 a b a b
930 1 1 1 2
931 2 1 2 2
932 3 1 NULL NULL
933 4 2 NULL NULL
934 DROP TABLE t1,t2;
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)
942 aaaaa
943 bbbbb
944 Warnings:
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)
948 aaaaa
949 bbbbb
950 Warnings:
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)
954 aaaaa
955 bbbbb
956 Warnings:
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)
960 aaaaa
961 bbbbb
962 Warnings:
963 Warning 1260 2 line(s) were cut by GROUP_CONCAT()
964 drop table t1, t2;
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');
986 CREATE VIEW v1 AS
987 SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP
988 FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;
989 SELECT * FROM v1;
990 EMPNUM NAME GRP
991 0 KERI 10
992 9 BARRY NULL
993 SELECT * FROM v1 WHERE EMPNUM < 10;
994 EMPNUM NAME GRP
995 0 KERI 10
996 9 BARRY NULL
997 DROP VIEW v1;
998 DROP TABLE t1,t2;
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;
1004 c11 c21
1005 40 NULL
1006 DROP TABLE t1, t2;
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;
1012 a b a b
1013 7 8 7 5
1014 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
1015 a b a b
1016 7 8 7 5
1017 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
1018 a b a b
1019 7 8 7 5
1020 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
1021 a b a b
1022 2 1 NULL NULL
1023 3 2 3 0
1024 4 3 4 1
1025 6 5 6 4
1026 8 7 NULL NULL
1027 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
1028 a b a b
1029 2 1 NULL NULL
1030 3 2 3 0
1031 4 3 4 1
1032 6 5 6 4
1033 8 7 NULL NULL
1034 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
1035 a b a b
1036 2 1 NULL NULL
1037 3 2 3 0
1038 4 3 4 1
1039 6 5 6 4
1040 8 7 NULL NULL
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;
1042 a b a b
1043 2 1 NULL NULL
1044 3 2 3 0
1045 4 3 4 1
1046 6 5 6 4
1047 7 8 7 5
1048 8 7 NULL NULL
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);
1050 a b a b
1051 2 1 NULL NULL
1052 3 2 3 0
1053 4 3 4 1
1054 6 5 6 4
1055 7 8 7 5
1056 8 7 NULL NULL
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);
1058 a b a b
1059 3 2 3 0
1060 4 3 4 1
1061 6 5 6 4
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);
1063 a b a b
1064 3 2 3 0
1065 4 3 4 1
1066 6 5 6 4
1067 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
1068 a b a b
1069 3 2 3 0
1070 4 3 4 1
1071 6 5 6 4
1072 7 8 7 5
1073 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
1074 a b a b
1075 3 2 3 0
1076 4 3 4 1
1077 6 5 6 4
1078 7 8 7 5
1079 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
1080 a b a b
1081 3 2 3 0
1082 4 3 4 1
1083 6 5 6 4
1084 7 8 7 5
1085 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
1086 a b a b
1087 3 2 3 0
1088 4 3 4 1
1089 6 5 6 4
1090 7 8 7 5
1091 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
1092 a b a b
1093 3 2 3 0
1094 4 3 4 1
1095 6 5 6 4
1096 7 8 7 5
1097 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
1098 a b a b
1099 3 2 3 0
1100 4 3 4 1
1101 6 5 6 4
1102 7 8 7 5
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);
1104 a b a b
1105 3 2 3 0
1106 4 3 4 1
1107 6 5 6 4
1108 7 8 7 5
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));
1110 a b a b
1111 3 2 3 0
1112 4 3 4 1
1113 6 5 6 4
1114 7 8 7 5
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;
1116 a b a b
1117 3 2 3 0
1118 4 3 4 1
1119 6 5 6 4
1120 7 8 7 5
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));
1122 a b a b
1123 3 2 3 0
1124 4 3 4 1
1125 6 5 6 4
1126 7 8 7 5
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
1139 DROP TABLE t1,t2;
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;
1148 SELECT v1.a, v2. b
1149 FROM v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3)
1150 GROUP BY v1.a;
1151 a b
1152 2 NULL
1153 3 3
1154 SELECT v1.a, v2. b
1155 FROM { OJ v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3) }
1156 GROUP BY v1.a;
1157 a b
1158 2 NULL
1159 3 3
1160 DROP VIEW v1,v2;
1161 DROP TABLE t1,t2;
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);
1167 a b
1168 1 NULL
1169 2 2
1170 3 3
1171 4 NULL
1172 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
1173 a b
1174 1 NULL
1175 2 2
1176 3 3
1177 4 NULL
1178 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
1179 a b
1180 1 NULL
1181 2 2
1182 3 3
1183 4 NULL
1184 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
1185 a b
1186 1 NULL
1187 2 2
1188 3 3
1189 4 NULL
1190 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
1191 a b
1192 1 NULL
1193 2 2
1194 3 3
1195 4 NULL
1196 DROP TABLE t1,t2;
1197 CREATE TABLE t1 (
1198 f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
1199 f2 varchar(16) collate latin1_swedish_ci
1200 );
1201 CREATE TABLE t2 (
1202 f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
1203 f3 varchar(16) collate latin1_swedish_ci
1204 );
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';
1208 f1 f2 f3
1209 bla blah sheep
1210 SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
1211 f1 f2 f3
1212 bla blah sheep
1213 SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
1214 f1 f2 f3
1215 bla blah sheep
1216 DROP TABLE t1,t2;
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);
1223 EXPLAIN
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
1228 flush status;
1229 SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
1230 id a
1231 1 aaaaaaa
1232 4 ddddddd
1233 show status like 'Handler_read%';
1234 Variable_name Value
1235 Handler_read_first 0
1236 Handler_read_key 5
1237 Handler_read_next 0
1238 Handler_read_prev 0
1239 Handler_read_rnd 0
1240 Handler_read_rnd_next 6
1241 DROP TABLE t1,t2;
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;
1251 c e d
1252 1 0 NULL
1253 SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
1254 c e d
1255 1 0 NULL
1256 DROP TABLE t1,t2;
1257 #
1258 # Bug#47650: using group by with rollup without indexes returns incorrect
1259 # results with where
1260 #
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);
1265 EXPLAIN
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 )
1276 1 3 6 3
1277 NULL 3 6 3
1278 EXPLAIN
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 )
1289 1 3 6 3
1290 NULL 3 6 3
1291 DROP TABLE t1, t2;
1292 #
1293 # Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison
1294 #
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
1304 Warnings:
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));
1308 f1 f2 f3 f1 f2
1309 1 NULL 3 NULL NULL
1310 DROP TABLE t1, t2;
1311 #
1312 # Bug#52357: Assertion failed: join->best_read in greedy_search
1313 # optimizer_search_depth=0
1314 #
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
1319 EXPLAIN
1320 SELECT 1
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;
1340 DROP TABLE t1;
1341 #
1342 # Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result
1343 #
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;
1351 COUNT(*)
1352 476
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
1359 DROP TABLE t1, t2;
1360 #
1361 # Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990
1362 #
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
1366 LEFT JOIN t1 AS jt2
1367 RIGHT JOIN t1 AS jt3
1368 JOIN t1 AS jt4 ON 1
1369 LEFT JOIN t1 AS jt5 ON 1
1370 ON 1
1371 RIGHT JOIN t1 AS jt6 ON jt6.f1
1372 ON 1;
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
1380 Warnings:
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
1385 JOIN t1 AS jt4 ON 1
1386 LEFT JOIN t1 AS jt5 ON 1
1387 ON 1
1388 RIGHT JOIN t1 AS jt6 ON jt6.f1
1389 ON 1;
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
1397 Warnings:
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
1399 DROP TABLE t1;
1400 #
1401 # Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field
1402 #
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
1409 WHERE t1.f1 = 4
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
1415 WHERE t1.f1 = 4
1416 GROUP BY t2.f1, t2.f2;
1417 f1 f1 f2
1418 4 NULL NULL
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;
1428 f1 f1 f2
1429 DROP TABLE t1,t2;
1431 # BUG#12567331 - INFINITE LOOP WHEN RESOLVING AN ALIASED COLUMN
1432 # USED IN GROUP BY
1434 CREATE TABLE t1 (
1435 col_varchar_1024_latin1_key varchar(1024),
1436 col_varchar_10_latin1 varchar(10),
1437 col_int int(11),
1438 pk int(11)
1439 );
1440 CREATE TABLE t2 (
1441 col_int_key int(11),
1442 col_int int(11),
1443 pk int(11)
1444 );
1445 PREPARE prep_stmt_9846 FROM '
1446 SELECT alias1.pk AS field1 FROM
1447 t1 AS alias1
1448 LEFT JOIN
1449 (
1450 t2 AS alias2
1451 RIGHT JOIN
1452 (
1453 t2 AS alias3
1454 JOIN t1 AS alias4
1455 ON 1
1456 )
1457 ON 1
1458 )
1459 ON 1
1460 GROUP BY field1';
1461 execute prep_stmt_9846;
1462 field1
1463 execute prep_stmt_9846;
1464 field1
1465 drop table t1,t2;
1466 #
1467 # Bug #11765810 58813: SERVER THREAD HANGS WHEN JOIN + WHERE + GROUP BY
1468 # IS EXECUTED TWICE FROM P
1469 #
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
1474 LEFT JOIN t1 t2
1475 RIGHT JOIN t1 t3
1476 JOIN t1 t4
1477 ON 1
1478 ON 1
1479 ON 1
1480 GROUP BY f';
1481 EXECUTE prep_stmt;
1482 f
1483 1
1484 EXECUTE prep_stmt;
1485 f
1486 1
1487 DROP TABLE t1;
1488 #
1489 # Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS
1490 # WRONG RESULT
1491 #
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);
1499 SELECT (
1500 SELECT MAX( t2.i2 )
1501 FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
1502 WHERE t2.i3 <> t1.i1
1503 ) AS field1
1504 FROM t1;;
1505 field1
1506 20
1507 20
1509 SELECT (
1510 SELECT MAX( t2.i2 )
1511 FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
1512 WHERE t2.i3 <> t1.i1
1513 ) AS field1
1514 FROM t1 GROUP BY field1;;
1515 field1
1516 20
1518 drop table t1,t2,t3;
1519 # End of test for Bug#13068506
1520 End of 5.1 tests