~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/t/join_outer.test

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

Show diffs side-by-side

added added

removed removed

Lines of Context:
 
1
#
 
2
# test of left outer join
 
3
#
 
4
 
 
5
--disable_warnings
 
6
drop table if exists t0,t1,t2,t3,t4,t5;
 
7
--enable_warnings
 
8
 
 
9
CREATE TABLE t1 (
 
10
  grp int(11) default NULL,
 
11
  a bigint(20) unsigned default NULL,
 
12
  c char(10) NOT NULL default ''
 
13
) ENGINE=MyISAM;
 
14
INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,'');
 
15
create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a));
 
16
insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7);
 
17
 
 
18
select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a;
 
19
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
 
20
select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) };
 
21
select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2;
 
22
select t1.*,t2.* from t1 left join t2 using (a);
 
23
select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
 
24
select t1.*,t2.* from t1 left join t2 using (a,c);
 
25
select t1.*,t2.* from t1 left join t2 using (c);
 
26
select t1.*,t2.* from t1 natural left outer join t2;
 
27
 
 
28
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
 
29
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
 
30
 
 
31
explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1;
 
32
explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
 
33
 
 
34
select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 
35
 
 
36
# The next query should rearange the left joins to get this to work
 
37
--error 1054
 
38
explain select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
 
39
--error 1054
 
40
select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
 
41
 
 
42
# The next query should give an error in MySQL
 
43
--error 1054
 
44
select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 
45
 
 
46
# Test of inner join
 
47
select t1.*,t2.* from t1 inner join t2 using (a);
 
48
select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
 
49
select t1.*,t2.* from t1 natural join t2;
 
50
 
 
51
drop table t1,t2;
 
52
 
 
53
#
 
54
# Test of left join bug
 
55
#
 
56
 
 
57
CREATE TABLE t1 (
 
58
 usr_id INT unsigned NOT NULL,
 
59
 uniq_id INT unsigned NOT NULL AUTO_INCREMENT,
 
60
        start_num INT unsigned NOT NULL DEFAULT 1,
 
61
        increment INT unsigned NOT NULL DEFAULT 1,
 
62
 PRIMARY KEY (uniq_id),
 
63
 INDEX usr_uniq_idx (usr_id, uniq_id),
 
64
 INDEX uniq_usr_idx (uniq_id, usr_id)
 
65
);
 
66
CREATE TABLE t2 (
 
67
 id INT unsigned NOT NULL DEFAULT 0,
 
68
 usr2_id INT unsigned NOT NULL DEFAULT 0,
 
69
 max INT unsigned NOT NULL DEFAULT 0,
 
70
 c_amount INT unsigned NOT NULL DEFAULT 0,
 
71
 d_max INT unsigned NOT NULL DEFAULT 0,
 
72
 d_num INT unsigned NOT NULL DEFAULT 0,
 
73
 orig_time INT unsigned NOT NULL DEFAULT 0,
 
74
 c_time INT unsigned NOT NULL DEFAULT 0,
 
75
 active ENUM ("no","yes") NOT NULL,
 
76
 PRIMARY KEY (id,usr2_id),
 
77
 INDEX id_idx (id),
 
78
 INDEX usr2_idx (usr2_id)
 
79
);
 
80
INSERT INTO t1 VALUES (3,NULL,0,50),(3,NULL,0,200),(3,NULL,0,25),(3,NULL,0,84676),(3,NULL,0,235),(3,NULL,0,10),(3,NULL,0,3098),(3,NULL,0,2947),(3,NULL,0,8987),(3,NULL,0,8347654),(3,NULL,0,20398),(3,NULL,0,8976),(3,NULL,0,500),(3,NULL,0,198);
 
81
 
 
82
#1st select shows that one record is returned with null entries for the right
 
83
#table, when selecting on an id that does not exist in the right table t2
 
84
SELECT t1.usr_id,t1.uniq_id,t1.increment,
 
85
t2.usr2_id,t2.c_amount,t2.max
 
86
FROM t1
 
87
LEFT JOIN t2 ON t2.id = t1.uniq_id
 
88
WHERE t1.uniq_id = 4
 
89
ORDER BY t2.c_amount;
 
90
 
 
91
# The same with RIGHT JOIN
 
92
SELECT t1.usr_id,t1.uniq_id,t1.increment,
 
93
t2.usr2_id,t2.c_amount,t2.max
 
94
FROM t2
 
95
RIGHT JOIN t1 ON t2.id = t1.uniq_id
 
96
WHERE t1.uniq_id = 4
 
97
ORDER BY t2.c_amount;
 
98
 
 
99
INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
 
100
--error ER_DUP_ENTRY
 
101
INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
 
102
INSERT INTO t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');
 
103
 
 
104
#3rd select should show that one record is returned with null entries for the
 
105
# right table, when selecting on an id that does not exist in the right table
 
106
# t2 but this select returns an empty set!!!!
 
107
SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4 ORDER BY t2.c_amount;
 
108
SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4 GROUP BY t2.c_amount;
 
109
# Removing the ORDER BY works:
 
110
SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4;
 
111
 
 
112
drop table t1,t2;
 
113
 
 
114
#
 
115
# Test of LEFT JOIN with const tables (failed for frankie@etsetb.upc.es)
 
116
#
 
117
 
 
118
CREATE TABLE t1 (
 
119
  cod_asig int(11) DEFAULT '0' NOT NULL,
 
120
  desc_larga_cat varchar(80) DEFAULT '' NOT NULL,
 
121
  desc_larga_cas varchar(80) DEFAULT '' NOT NULL,
 
122
  desc_corta_cat varchar(40) DEFAULT '' NOT NULL,
 
123
  desc_corta_cas varchar(40) DEFAULT '' NOT NULL,
 
124
  cred_total double(3,1) DEFAULT '0.0' NOT NULL,
 
125
  pre_requisit int(11),
 
126
  co_requisit int(11),
 
127
  preco_requisit int(11),
 
128
  PRIMARY KEY (cod_asig)
 
129
);
 
130
 
 
131
INSERT INTO t1 VALUES (10360,'asdfggfg','Introduccion a los  Ordenadores I','asdfggfg','Introduccio Ordinadors I',6.0,NULL,NULL,NULL);
 
132
INSERT INTO t1 VALUES (10361,'Components i Circuits Electronics I','Componentes y Circuitos Electronicos I','Components i Circuits Electronics I','Comp. i Circ. Electr. I',6.0,NULL,NULL,NULL);
 
133
INSERT INTO t1 VALUES (10362,'Laboratori d`Ordinadors','Laboratorio de Ordenadores','Laboratori d`Ordinadors','Laboratori Ordinadors',4.5,NULL,NULL,NULL);
 
134
INSERT INTO t1 VALUES (10363,'Tecniques de Comunicacio Oral i Escrita','Tecnicas de Comunicacion Oral y Escrita','Tecniques de Comunicacio Oral i Escrita','Tec. Com. Oral i Escrita',4.5,NULL,NULL,NULL);
 
135
INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);
 
136
INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);
 
137
INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);
 
138
INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','CĂźlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
 
139
 
 
140
CREATE TABLE t2 (
 
141
  idAssignatura int(11) DEFAULT '0' NOT NULL,
 
142
  Grup int(11) DEFAULT '0' NOT NULL,
 
143
  Places smallint(6) DEFAULT '0' NOT NULL,
 
144
  PlacesOcupades int(11) DEFAULT '0',
 
145
  PRIMARY KEY (idAssignatura,Grup)
 
146
);
 
147
 
 
148
 
 
149
INSERT INTO t2 VALUES (10360,12,333,0);
 
150
INSERT INTO t2 VALUES (10361,30,2,0);
 
151
INSERT INTO t2 VALUES (10361,40,3,0);
 
152
INSERT INTO t2 VALUES (10360,45,10,0);
 
153
INSERT INTO t2 VALUES (10362,10,12,0);
 
154
INSERT INTO t2 VALUES (10360,55,2,0);
 
155
INSERT INTO t2 VALUES (10360,70,0,0);
 
156
INSERT INTO t2 VALUES (10360,565656,0,0);
 
157
INSERT INTO t2 VALUES (10360,32767,7,0);
 
158
INSERT INTO t2 VALUES (10360,33,8,0);
 
159
INSERT INTO t2 VALUES (10360,7887,85,0);
 
160
INSERT INTO t2 VALUES (11405,88,8,0);
 
161
INSERT INTO t2 VALUES (10360,0,55,0);
 
162
INSERT INTO t2 VALUES (10360,99,0,0);
 
163
INSERT INTO t2 VALUES (11411,30,10,0);
 
164
INSERT INTO t2 VALUES (11404,0,0,0);
 
165
INSERT INTO t2 VALUES (10362,11,111,0);
 
166
INSERT INTO t2 VALUES (10363,33,333,0);
 
167
INSERT INTO t2 VALUES (11412,55,0,0);
 
168
INSERT INTO t2 VALUES (50003,66,6,0);
 
169
INSERT INTO t2 VALUES (11403,5,0,0);
 
170
INSERT INTO t2 VALUES (11406,11,11,0);
 
171
INSERT INTO t2 VALUES (11410,11410,131,0);
 
172
INSERT INTO t2 VALUES (11416,11416,32767,0);
 
173
INSERT INTO t2 VALUES (11409,0,0,0);
 
174
 
 
175
CREATE TABLE t3 (
 
176
  id int(11) NOT NULL auto_increment,
 
177
  dni_pasaporte char(16) DEFAULT '' NOT NULL,
 
178
  idPla int(11) DEFAULT '0' NOT NULL,
 
179
  cod_asig int(11) DEFAULT '0' NOT NULL,
 
180
  any smallint(6) DEFAULT '0' NOT NULL,
 
181
  quatrimestre smallint(6) DEFAULT '0' NOT NULL,
 
182
  estat char(1) DEFAULT 'M' NOT NULL,
 
183
  PRIMARY KEY (id),
 
184
  UNIQUE dni_pasaporte (dni_pasaporte,idPla),
 
185
  UNIQUE dni_pasaporte_2 (dni_pasaporte,idPla,cod_asig,any,quatrimestre)
 
186
);
 
187
 
 
188
INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');
 
189
 
 
190
CREATE TABLE t4 (
 
191
  id int(11) NOT NULL auto_increment,
 
192
  papa int(11) DEFAULT '0' NOT NULL,
 
193
  fill int(11) DEFAULT '0' NOT NULL,
 
194
  idPla int(11) DEFAULT '0' NOT NULL,
 
195
  PRIMARY KEY (id),
 
196
  KEY papa (idPla,papa),
 
197
  UNIQUE papa_2 (idPla,papa,fill)
 
198
);
 
199
 
 
200
INSERT INTO t4 VALUES (1,-1,10360,1);
 
201
INSERT INTO t4 VALUES (2,-1,10361,1);
 
202
INSERT INTO t4 VALUES (3,-1,10362,1);
 
203
 
 
204
SELECT DISTINCT fill,desc_larga_cat,cred_total,Grup,Places,PlacesOcupades FROM t4 LEFT JOIN t3 ON t3.cod_asig=fill AND estat='S'   AND dni_pasaporte='11111111'   AND t3.idPla=1 , t2,t1 WHERE fill=t1.cod_asig   AND Places>PlacesOcupades   AND fill=idAssignatura   AND t4.idPla=1   AND papa=-1;
 
205
 
 
206
SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ;
 
207
 
 
208
INSERT INTO t3 VALUES (3,'1234',1,10360,98,1,'S');
 
209
SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ;
 
210
 
 
211
drop table t1,t2,t3,test.t4;
 
212
 
 
213
#
 
214
# Test of IS NULL on AUTO_INCREMENT with LEFT JOIN
 
215
#
 
216
 
 
217
CREATE TABLE t1 (
 
218
  id smallint(5) unsigned NOT NULL auto_increment,
 
219
  name char(60) DEFAULT '' NOT NULL,
 
220
  PRIMARY KEY (id)
 
221
);
 
222
INSERT INTO t1 VALUES (1,'Antonio Paz');
 
223
INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
 
224
INSERT INTO t1 VALUES (3,'Thimble Smith');
 
225
 
 
226
CREATE TABLE t2 (
 
227
  id smallint(5) unsigned NOT NULL auto_increment,
 
228
  owner smallint(5) unsigned DEFAULT '0' NOT NULL,
 
229
  name char(60),
 
230
  PRIMARY KEY (id)
 
231
);
 
232
INSERT INTO t2 VALUES (1,1,'El Gato');
 
233
INSERT INTO t2 VALUES (2,1,'Perrito');
 
234
INSERT INTO t2 VALUES (3,3,'Happy');
 
235
 
 
236
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner);
 
237
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
 
238
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
 
239
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;
 
240
select count(*) from t1 left join t2 on (t1.id = t2.owner);
 
241
 
 
242
select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner);
 
243
select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
 
244
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
 
245
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;
 
246
select count(*) from t2 right join t1 on (t1.id = t2.owner);
 
247
 
 
248
select t1.name, t2.name, t2.id,t3.id from t2 right join t1 on (t1.id = t2.owner) left join t1 as t3 on t3.id=t2.owner;
 
249
select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;
 
250
select t1.name, t2.name, t2.id, t2.owner, t3.id from t1 left join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;
 
251
 
 
252
drop table t1,t2;
 
253
 
 
254
create table t1 (id int not null, str char(10), index(str));
 
255
insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");
 
256
select * from t1 where str is not null order by id;
 
257
select * from t1 where str is null;
 
258
drop table t1;
 
259
 
 
260
#
 
261
# Test wrong LEFT JOIN query
 
262
#
 
263
 
 
264
CREATE TABLE t1 (
 
265
  t1_id bigint(21) NOT NULL auto_increment,
 
266
  PRIMARY KEY (t1_id)
 
267
);
 
268
CREATE TABLE t2 (
 
269
  t2_id bigint(21) NOT NULL auto_increment,
 
270
  PRIMARY KEY (t2_id)
 
271
);
 
272
CREATE TABLE t3 (
 
273
  t3_id bigint(21) NOT NULL auto_increment,
 
274
  PRIMARY KEY (t3_id)
 
275
);
 
276
CREATE TABLE t4 (
 
277
  seq_0_id bigint(21) DEFAULT '0' NOT NULL,
 
278
  seq_1_id bigint(21) DEFAULT '0' NOT NULL,
 
279
  KEY seq_0_id (seq_0_id),
 
280
  KEY seq_1_id (seq_1_id)
 
281
);
 
282
CREATE TABLE t5 (
 
283
  seq_0_id bigint(21) DEFAULT '0' NOT NULL,
 
284
  seq_1_id bigint(21) DEFAULT '0' NOT NULL,
 
285
  KEY seq_1_id (seq_1_id),
 
286
  KEY seq_0_id (seq_0_id)
 
287
);
 
288
 
 
289
insert into t1 values (1);
 
290
insert into t2 values (1);
 
291
insert into t3 values (1);
 
292
insert into t4 values (1,1);
 
293
insert into t5 values (1,1);
 
294
 
 
295
--error 1054
 
296
explain select * from t3 left join t4 on t4.seq_1_id = t2.t2_id left join t1 on t1.t1_id = t4.seq_0_id left join t5 on t5.seq_0_id = t1.t1_id left join t2 on t2.t2_id = t5.seq_1_id where t3.t3_id = 23;
 
297
 
 
298
drop table t1,t2,t3,t4,t5;
 
299
 
 
300
#
 
301
# Another LEFT JOIN problem
 
302
# (The problem was that the result changed when we added ORDER BY)
 
303
#
 
304
 
 
305
create table t1 (n int, m int, o int, key(n));
 
306
create table t2 (n int not null, m int, o int, primary key(n));
 
307
insert into t1 values (1, 2, 11), (1, 2, 7), (2, 2, 8), (1,2,9),(1,3,9);
 
308
insert into t2 values (1, 2, 3),(2, 2, 8), (4,3,9),(3,2,10);
 
309
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
 
310
t1.m = t2.m where t1.n = 1;
 
311
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
 
312
t1.m = t2.m where t1.n = 1 order by t1.o;
 
313
drop table t1,t2;
 
314
 
 
315
# Test bug with NATURAL join:
 
316
 
 
317
CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);   
 
318
INSERT INTO t1 VALUES (1,'a',1);
 
319
INSERT INTO t1 VALUES (2,'b',1);
 
320
INSERT INTO t1 VALUES (3,'c',2);
 
321
 
 
322
CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));   
 
323
INSERT INTO t2 VALUES (1,'x');
 
324
INSERT INTO t2 VALUES (2,'y');
 
325
INSERT INTO t2 VALUES (3,'z');
 
326
 
 
327
SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
 
328
SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
 
329
 
 
330
drop table t1,t2;
 
331
 
 
332
create table t1 ( color varchar(20), name varchar(20) );
 
333
insert into t1 values ( 'red', 'apple' );
 
334
insert into t1 values ( 'yellow', 'banana' );
 
335
insert into t1 values ( 'green', 'lime' );
 
336
insert into t1 values ( 'black', 'grape' );
 
337
insert into t1 values ( 'blue', 'blueberry' );
 
338
create table t2 ( count int, color varchar(20) );
 
339
insert into t2 values (10, 'green');
 
340
insert into t2 values (5, 'black');
 
341
insert into t2 values (15, 'white');
 
342
insert into t2 values (7, 'green');
 
343
select * from t1;
 
344
select * from t2;
 
345
select * from t2 natural join t1;
 
346
select t2.count, t1.name from t2 natural join t1;
 
347
select t2.count, t1.name from t2 inner join t1 using (color);
 
348
drop table t1;
 
349
drop table t2;
 
350
 
 
351
#
 
352
# Test of LEFT JOIN + GROUP FUNCTIONS within functions:
 
353
#
 
354
 
 
355
CREATE TABLE t1 (
 
356
  pcode varchar(8) DEFAULT '' NOT NULL
 
357
);
 
358
INSERT INTO t1 VALUES ('kvw2000'),('kvw2001'),('kvw3000'),('kvw3001'),('kvw3002'),('kvw3500'),('kvw3501'),('kvw3502'),('kvw3800'),('kvw3801'),('kvw3802'),('kvw3900'),('kvw3901'),('kvw3902'),('kvw4000'),('kvw4001'),('kvw4002'),('kvw4200'),('kvw4500'),('kvw5000'),('kvw5001'),('kvw5500'),('kvw5510'),('kvw5600'),('kvw5601'),('kvw6000'),('klw1000'),('klw1020'),('klw1500'),('klw2000'),('klw2001'),('klw2002'),('kld2000'),('klw2500'),('kmw1000'),('kmw1500'),('kmw2000'),('kmw2001'),('kmw2100'),('kmw3000'),('kmw3200');
 
359
CREATE TABLE t2 (
 
360
  pcode varchar(8) DEFAULT '' NOT NULL,
 
361
  KEY pcode (pcode)
 
362
);
 
363
INSERT INTO t2 VALUES ('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw6000'),('kvw6000'),('kld2000');
 
364
 
 
365
SELECT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1
 
366
LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;
 
367
SELECT SQL_BIG_RESULT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1 LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;
 
368
drop table t1,t2;
 
369
 
 
370
#
 
371
# Another left join problem
 
372
#
 
373
 
 
374
CREATE TABLE t1 (
 
375
  id int(11),
 
376
  pid int(11),
 
377
  rep_del tinyint(4),
 
378
  KEY id (id),
 
379
  KEY pid (pid)
 
380
);
 
381
INSERT INTO t1 VALUES (1,NULL,NULL);
 
382
INSERT INTO t1 VALUES (2,1,NULL);
 
383
select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
 
384
create index rep_del ON t1(rep_del);
 
385
select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
 
386
drop table t1;
 
387
 
 
388
CREATE TABLE t1 (
 
389
  id int(11) DEFAULT '0' NOT NULL,
 
390
  name tinytext DEFAULT '' NOT NULL,
 
391
  UNIQUE id (id)
 
392
);
 
393
INSERT INTO t1 VALUES (1,'yes'),(2,'no');
 
394
CREATE TABLE t2 (
 
395
  id int(11) DEFAULT '0' NOT NULL,
 
396
  idx int(11) DEFAULT '0' NOT NULL,
 
397
  UNIQUE id (id,idx)
 
398
);
 
399
INSERT INTO t2 VALUES (1,1);
 
400
explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
 
401
SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
 
402
drop table t1,t2;
 
403
 
 
404
#
 
405
# Test problem with using key_column= constant in ON and WHERE
 
406
#
 
407
create table t1 (bug_id mediumint, reporter mediumint);
 
408
create table t2 (bug_id mediumint, who mediumint, index(who));
 
409
insert into t2 values (1,1),(1,2);
 
410
insert into t1 values (1,1),(2,1);
 
411
SELECT * FROM t1 LEFT JOIN t2 ON (t1.bug_id =  t2.bug_id AND  t2.who = 2) WHERE  (t1.reporter = 2 OR t2.who = 2);
 
412
drop table t1,t2;
 
413
 
 
414
#
 
415
# Test problem with LEFT JOIN
 
416
 
 
417
create table t1 (fooID smallint unsigned auto_increment, primary key (fooID));
 
418
create table t2 (fooID smallint unsigned not null, barID smallint unsigned not null, primary key (fooID,barID));
 
419
insert into t1 (fooID) values (10),(20),(30);
 
420
insert into t2 values (10,1),(20,2),(30,3);
 
421
explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
 
422
select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
 
423
select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;
 
424
drop table t1,t2;
 
425
 
 
426
create table t1 (i int);
 
427
create table t2 (i int);
 
428
create table t3 (i int);
 
429
insert into t1 values(1),(2);
 
430
insert into t2 values(2),(3);
 
431
insert into t3 values(2),(4);
 
432
select * from t1 natural left join t2 natural left join t3;
 
433
select * from t1 natural left join t2 where (t2.i is not null)=0;
 
434
select * from t1 natural left join t2 where (t2.i is not null) is not null;
 
435
select * from t1 natural left join t2 where (i is not null)=0;
 
436
select * from t1 natural left join t2 where (i is not null) is not null;
 
437
drop table t1,t2,t3;
 
438
 
 
439
#
 
440
# Test of USING
 
441
#
 
442
create table t1 (f1 integer,f2 integer,f3 integer);
 
443
create table t2 (f2 integer,f4 integer);
 
444
create table t3 (f3 integer,f5 integer);
 
445
select * from t1
 
446
         left outer join t2 using (f2)
 
447
         left outer join t3 using (f3);
 
448
drop table t1,t2,t3;
 
449
 
 
450
create table t1 (a1 int, a2 int);
 
451
create table t2 (b1 int not null, b2 int);
 
452
create table t3 (c1 int, c2 int);
 
453
 
 
454
insert into t1 values (1,2), (2,2), (3,2);
 
455
insert into t2 values (1,3), (2,3);
 
456
insert into t3 values (2,4),        (3,4);
 
457
 
 
458
select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
 
459
explain select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
 
460
 
 
461
drop table t1, t2, t3;
 
462
 
 
463
# Test for BUG#8711 '<=>' was considered to be a NULL-rejecting predicate.
 
464
create table t1 (
 
465
  a int(11),
 
466
  b char(10),
 
467
  key (a)
 
468
);
 
469
insert into t1 (a) values (1),(2),(3),(4);
 
470
create table t2 (a int);
 
471
 
 
472
select * from t1 left join t2 on t1.a=t2.a where not (t2.a <=> t1.a);
 
473
select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a);
 
474
drop table t1,t2;
 
475
 
 
476
# Test for BUG#5088
 
477
 
 
478
create table t1 (
 
479
  match_id tinyint(3) unsigned not null auto_increment,
 
480
  home tinyint(3) unsigned default '0',
 
481
  unique key match_id (match_id),
 
482
  key match_id_2 (match_id)
 
483
);
 
484
 
 
485
insert into t1 values("1", "2");
 
486
 
 
487
create table t2 (
 
488
  player_id tinyint(3) unsigned default '0',
 
489
  match_1_h tinyint(3) unsigned default '0',
 
490
  key player_id (player_id)
 
491
);
 
492
 
 
493
insert into t2 values("1", "5");
 
494
insert into t2 values("2", "9");
 
495
insert into t2 values("3", "3");
 
496
insert into t2 values("4", "7");
 
497
insert into t2 values("5", "6");
 
498
insert into t2 values("6", "8");
 
499
insert into t2 values("7", "4");
 
500
insert into t2 values("8", "12");
 
501
insert into t2 values("9", "11");
 
502
insert into t2 values("10", "10");
 
503
 
 
504
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
 
505
  (t2 s left join t1 m on m.match_id = 1) 
 
506
  order by m.match_id desc;
 
507
  
 
508
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
 
509
  (t2 s left join t1 m on m.match_id = 1) 
 
510
  order by UUX desc;
 
511
 
 
512
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
 
513
  (t2 s left join t1 m on m.match_id = 1) 
 
514
  order by UUX desc;
 
515
 
 
516
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
 
517
  t2 s straight_join t1 m where m.match_id = 1 
 
518
  order by UUX desc;
 
519
 
 
520
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
 
521
  t2 s straight_join t1 m where m.match_id = 1 
 
522
  order by UUX desc;
 
523
 
 
524
drop table t1, t2;
 
525
 
 
526
# Tests for bugs #6307 and 6460
 
527
 
 
528
create table t1 (a int, b int, unique index idx (a, b));
 
529
create table t2 (a int, b int, c int, unique index idx (a, b));
 
530
 
 
531
insert into t1 values (1, 10), (1,11), (2,10), (2,11);
 
532
insert into t2 values (1,10,3);
 
533
 
 
534
select t1.a, t1.b, t2.c from t1 left join t2
 
535
                                on t1.a=t2.a and t1.b=t2.b and t2.c=3
 
536
   where t1.a=1 and t2.c is null;
 
537
 
 
538
drop table t1, t2;
 
539
 
 
540
CREATE TABLE t1 (
 
541
  ts_id bigint(20) default NULL,
 
542
  inst_id tinyint(4) default NULL,
 
543
  flag_name varchar(64) default NULL,
 
544
  flag_value text,
 
545
  UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
 
546
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
547
 
 
548
CREATE TABLE t2 (
 
549
  ts_id bigint(20) default NULL,
 
550
  inst_id tinyint(4) default NULL,
 
551
  flag_name varchar(64) default NULL,
 
552
  flag_value text,
 
553
  UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
 
554
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
555
 
 
556
INSERT INTO t1 VALUES
 
557
  (111056548820001, 0, 'flag1', NULL),
 
558
  (111056548820001, 0, 'flag2', NULL),
 
559
  (2, 0, 'other_flag', NULL);
 
560
 
 
561
INSERT INTO t2 VALUES
 
562
  (111056548820001, 3, 'flag1', 'sss');
 
563
 
 
564
SELECT t1.flag_name,t2.flag_value 
 
565
  FROM t1 LEFT JOIN t2 
 
566
          ON (t1.ts_id = t2.ts_id AND t1.flag_name = t2.flag_name AND
 
567
              t2.inst_id = 3) 
 
568
  WHERE t1.inst_id = 0 AND t1.ts_id=111056548820001 AND
 
569
        t2.flag_value IS  NULL;
 
570
 
 
571
DROP TABLE t1,t2;
 
572
 
 
573
CREATE TABLE t1 (
 
574
  id int(11) unsigned NOT NULL auto_increment,
 
575
  text_id int(10) unsigned default NULL,
 
576
  PRIMARY KEY  (id)
 
577
);
 
578
 
 
579
INSERT INTO t1 VALUES("1", "0");
 
580
INSERT INTO t1 VALUES("2", "10");
 
581
 
 
582
CREATE TABLE t2 (
 
583
  text_id char(3) NOT NULL default '',
 
584
  language_id char(3) NOT NULL default '',
 
585
  text_data text,
 
586
  PRIMARY KEY  (text_id,language_id)
 
587
);
 
588
 
 
589
INSERT INTO t2 VALUES("0", "EN", "0-EN");
 
590
INSERT INTO t2 VALUES("0", "SV", "0-SV");
 
591
INSERT INTO t2 VALUES("10", "EN", "10-EN");
 
592
INSERT INTO t2 VALUES("10", "SV", "10-SV");
 
593
SELECT t1.id, t1.text_id, t2.text_data
 
594
  FROM t1 LEFT JOIN t2
 
595
               ON t1.text_id = t2.text_id
 
596
                  AND t2.language_id = 'SV'
 
597
  WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%');
 
598
 
 
599
DROP TABLE t1, t2;
 
600
 
 
601
# Test for bug #5896  
 
602
 
 
603
CREATE TABLE t0 (a0 int PRIMARY KEY);
 
604
CREATE TABLE t1 (a1 int PRIMARY KEY);
 
605
CREATE TABLE t2 (a2 int);
 
606
CREATE TABLE t3 (a3 int);
 
607
INSERT INTO t0 VALUES (1);
 
608
INSERT INTO t1 VALUES (1);
 
609
INSERT INTO t2 VALUES (1), (2);
 
610
INSERT INTO t3 VALUES (1), (2);
 
611
 
 
612
SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
 
613
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
 
614
SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
 
615
EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
 
616
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
 
617
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
 
618
 
 
619
INSERT INTO t0 VALUES (0);
 
620
INSERT INTO t1 VALUES (0);
 
621
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
 
622
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
 
623
 
 
624
# Test for BUG#4480
 
625
drop table t1,t2;
 
626
create table t1 (a int, b int);
 
627
insert into t1 values (1,1),(2,2),(3,3);
 
628
create table t2 (a int, b int);
 
629
insert into t2 values (1,1), (2,2);
 
630
 
 
631
select * from t2 right join t1 on t2.a=t1.a;
 
632
select straight_join * from t2 right join t1 on t2.a=t1.a;
 
633
 
 
634
DROP TABLE t0,t1,t2,t3;
 
635
 
 
636
#
 
637
# Test for bug #9017: left join mistakingly converted to inner join
 
638
#
 
639
 
 
640
CREATE TABLE t1 (a int PRIMARY KEY, b int);
 
641
CREATE TABLE t2 (a int PRIMARY KEY, b int);
 
642
 
 
643
INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
 
644
INSERT INTO t2 VALUES (1,2), (2,2);
 
645
 
 
646
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
 
647
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
 
648
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
 
649
  WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
 
650
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
 
651
 
 
652
DROP TABLE t1,t2;
 
653
 
 
654
# Bug #8681: Bad warning message when group_concat() exceeds max length
 
655
set group_concat_max_len=5;
 
656
create table t1 (a int, b varchar(20));
 
657
create table t2 (a int, c varchar(20));
 
658
insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
 
659
insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
 
660
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
 
661
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
 
662
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
 
663
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
 
664
drop table t1, t2;
 
665
set group_concat_max_len=default;
 
666
 
 
667
# End of 4.1 tests
 
668
 
 
669
#
 
670
# BUG#10162 - ON is merged with WHERE, left join is convered to a regular join
 
671
#
 
672
create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, art int(11) not null, primary key  (gid,x,y));
 
673
insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
 
674
create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, id int(11) not null, primary key  (gid,id,x,y), key id (id));
 
675
insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
 
676
create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null, name char(12) not null, primary key  (id,set_id));
 
677
insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4, 'f'), (1, 5, 'g'), (1, 6, 'h');
 
678
explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
 
679
left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
 
680
and t1.gid =1 and t2.gid =1 and t3.set_id =1;
 
681
drop tables t1,t2,t3;
 
682
 
 
683
#
 
684
# Test for bug #11285: false Item_equal on expression in outer join
 
685
 
686
 
 
687
CREATE TABLE t1 (c11 int);
 
688
CREATE TABLE t2 (c21 int);
 
689
INSERT INTO t1 VALUES (30), (40), (50);
 
690
INSERT INTO t2 VALUES (300), (400), (500);
 
691
SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;
 
692
DROP TABLE t1, t2;
 
693
#
 
694
# Test for bugs
 
695
# #12101: erroneously applied outer join elimination in case of WHERE NOT BETWEEN
 
696
# #12102: erroneously missing outer join elimination in case of WHERE IN/IF
 
697
#
 
698
 
 
699
CREATE TABLE t1 (a int PRIMARY KEY, b int);
 
700
CREATE TABLE t2 (a int PRIMARY KEY, b int);
 
701
 
 
702
INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
 
703
INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
 
704
 
 
705
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
 
706
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
 
707
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
 
708
 
 
709
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
 
710
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
 
711
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
 
712
 
 
713
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;
 
714
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);
 
715
 
 
716
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);
 
717
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);
 
718
 
 
719
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
 
720
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
 
721
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
 
722
 
 
723
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
 
724
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
 
725
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
 
726
 
 
727
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);
 
728
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));
 
729
 
 
730
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;
 
731
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));
 
732
 
 
733
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
 
734
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
 
735
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);
 
736
 
 
737
DROP TABLE t1,t2;
 
738
 
 
739
#
 
740
# Bug 19816: LEFT OUTER JOIN with constant ORed predicates in WHERE clause
 
741
 
742
 
 
743
CREATE TABLE t1 (a int);
 
744
CREATE TABLE t2 (b int);
 
745
INSERT INTO t1 VALUES (1), (2), (3), (4);
 
746
INSERT INTO t2 VALUES (2), (3);
 
747
 
 
748
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);
 
749
 
 
750
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
 
751
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
 
752
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
 
753
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
 
754
 
 
755
DROP TABLE t1,t2;
 
756
 
 
757
#
 
758
# Bug 26017: LEFT OUTER JOIN over two constant tables and 
 
759
#            a case-insensitive comparison predicate field=const 
 
760
 
761
 
 
762
CREATE TABLE t1 (
 
763
  f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
 
764
  f2 varchar(16) collate latin1_swedish_ci
 
765
);
 
766
CREATE TABLE t2 (
 
767
  f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
 
768
  f3 varchar(16) collate latin1_swedish_ci
 
769
);
 
770
 
 
771
INSERT INTO t1 VALUES ('bla','blah');
 
772
INSERT INTO t2 VALUES ('bla','sheep');
 
773
 
 
774
SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
 
775
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
 
776
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
 
777
 
 
778
DROP TABLE t1,t2;
 
779
 
 
780
#
 
781
# Bug 28188: 'not exists' optimization for outer joins 
 
782
#
 
783
 
 
784
CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
 
785
CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
 
786
INSERT INTO t1 VALUES
 
787
  (1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
 
788
INSERT INTO t2 VALUES
 
789
  (3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
 
790
 
 
791
EXPLAIN
 
792
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
 
793
 
 
794
flush status;
 
795
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
 
796
show status like 'Handler_read%';
 
797
 
 
798
DROP TABLE t1,t2;
 
799
 
 
800
#
 
801
# Bug 28571: outer join with false on condition over constant tables 
 
802
#
 
803
 
 
804
CREATE TABLE t1 (c int  PRIMARY KEY, e int NOT NULL);
 
805
INSERT INTO t1 VALUES (1,0), (2,1);
 
806
CREATE TABLE t2 (d int PRIMARY KEY);
 
807
INSERT INTO t2 VALUES (1), (2), (3);
 
808
 
 
809
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
 
810
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
 
811
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
 
812
 
 
813
DROP TABLE t1,t2;
 
814