← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/join_outer.test

  • Committer: Brian Aker
  • Date: 2008-07-14 22:18:37 UTC
  • mfrom: (77.1.96 codestyle)
  • Revision ID: brian@tangent.org-20080714221837-oceoshx7fjkla9u3
Merge from Monty

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/join_outer.test
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 default NULL,
11
 
  a bigint 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 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,
126
 
  co_requisit int,
127
 
  preco_requisit int,
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 DEFAULT '0' NOT NULL,
142
 
  Grup int DEFAULT '0' NOT NULL,
143
 
  Places smallint DEFAULT '0' NOT NULL,
144
 
  PlacesOcupades int 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 NOT NULL auto_increment,
177
 
  dni_pasaporte char(16) DEFAULT '' NOT NULL,
178
 
  idPla int DEFAULT '0' NOT NULL,
179
 
  cod_asig int DEFAULT '0' NOT NULL,
180
 
  any smallint DEFAULT '0' NOT NULL,
181
 
  quatrimestre smallint 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 NOT NULL auto_increment,
192
 
  papa int DEFAULT '0' NOT NULL,
193
 
  fill int DEFAULT '0' NOT NULL,
194
 
  idPla int 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 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 unsigned NOT NULL auto_increment,
228
 
  owner smallint 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 NOT NULL auto_increment,
266
 
  PRIMARY KEY (t1_id)
267
 
);
268
 
CREATE TABLE t2 (
269
 
  t2_id bigint NOT NULL auto_increment,
270
 
  PRIMARY KEY (t2_id)
271
 
);
272
 
CREATE TABLE t3 (
273
 
  t3_id bigint NOT NULL auto_increment,
274
 
  PRIMARY KEY (t3_id)
275
 
);
276
 
CREATE TABLE t4 (
277
 
  seq_0_id bigint DEFAULT '0' NOT NULL,
278
 
  seq_1_id bigint 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 DEFAULT '0' NOT NULL,
284
 
  seq_1_id bigint 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,
376
 
  pid int,
377
 
  rep_del tinyint,
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 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 DEFAULT '0' NOT NULL,
396
 
  idx int 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 bigint, reporter bigint);
408
 
create table t2 (bug_id bigint, who bigint, 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,
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 unsigned not null auto_increment,
480
 
  home tinyint 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 unsigned default '0',
489
 
  match_1_h tinyint 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 default NULL,
542
 
  inst_id tinyint 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 default NULL,
550
 
  inst_id tinyint 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 unsigned NOT NULL auto_increment,
575
 
  text_id int 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 unsigned not null, x int not null, y int not null, art int 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 unsigned not null, x int not null, y int not null, id int 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 unsigned not null, id tinyint 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