~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/t/join.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
# Initialization
 
3
--disable_warnings
 
4
drop table if exists t1,t2,t3;
 
5
--enable_warnings
 
6
 
 
7
#
 
8
# Test different join syntaxes
 
9
#
 
10
 
 
11
CREATE TABLE t1 (S1 INT);
 
12
CREATE TABLE t2 (S1 INT);
 
13
INSERT INTO t1 VALUES (1);
 
14
INSERT INTO t2 VALUES (2);
 
15
SELECT * FROM t1 JOIN t2;
 
16
SELECT * FROM t1 INNER JOIN t2;
 
17
SELECT * from t1 JOIN t2 USING (S1);
 
18
SELECT * FROM t1 INNER JOIN t2 USING (S1);
 
19
SELECT * from t1 CROSS JOIN t2;
 
20
SELECT * from t1 LEFT JOIN t2 USING(S1);
 
21
SELECT * from t1 LEFT JOIN t2 ON(t2.S1=2);
 
22
SELECT * from t1 RIGHT JOIN t2 USING(S1);
 
23
SELECT * from t1 RIGHT JOIN t2 ON(t1.S1=1);
 
24
drop table t1,t2;
 
25
 
 
26
#
 
27
# This failed for lia Perminov
 
28
#
 
29
 
 
30
create table t1 (id int primary key);
 
31
create table t2 (id int);
 
32
insert into t1 values (75);
 
33
insert into t1 values (79);
 
34
insert into t1 values (78);
 
35
insert into t1 values (77);
 
36
replace into t1 values (76);
 
37
replace into t1 values (76);
 
38
insert into t1 values (104);
 
39
insert into t1 values (103);
 
40
insert into t1 values (102);
 
41
insert into t1 values (101);
 
42
insert into t1 values (105);
 
43
insert into t1 values (106);
 
44
insert into t1 values (107);
 
45
 
 
46
insert into t2 values (107),(75),(1000);
 
47
 
 
48
select t1.id, t2.id from t1, t2 where t2.id = t1.id;
 
49
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id;
 
50
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t2.id;
 
51
 
 
52
#
 
53
# Test problems with impossible ON or WHERE
 
54
#
 
55
select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
 
56
explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
 
57
explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0;
 
58
drop table t1,t2;
 
59
 
 
60
#
 
61
# problem with join
 
62
#
 
63
 
 
64
CREATE TABLE t1 (
 
65
  id int(11) NOT NULL auto_increment,
 
66
  token varchar(100) DEFAULT '' NOT NULL,
 
67
  count int(11) DEFAULT '0' NOT NULL,
 
68
  qty int(11),
 
69
  phone char(1) DEFAULT '' NOT NULL,
 
70
  timestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
 
71
  PRIMARY KEY (id),
 
72
  KEY token (token(15)),
 
73
  KEY timestamp (timestamp),
 
74
  UNIQUE token_2 (token(75),count,phone)
 
75
);
 
76
 
 
77
INSERT INTO t1 VALUES (21,'e45703b64de71482360de8fec94c3ade',3,7800,'n','1999-12-23 17:22:21');
 
78
INSERT INTO t1 VALUES (22,'e45703b64de71482360de8fec94c3ade',4,5000,'y','1999-12-23 17:22:21');
 
79
INSERT INTO t1 VALUES (18,'346d1cb63c89285b2351f0ca4de40eda',3,13200,'b','1999-12-23 11:58:04');
 
80
INSERT INTO t1 VALUES (17,'ca6ddeb689e1b48a04146b1b5b6f936a',4,15000,'b','1999-12-23 11:36:53');
 
81
INSERT INTO t1 VALUES (16,'ca6ddeb689e1b48a04146b1b5b6f936a',3,13200,'b','1999-12-23 11:36:53');
 
82
INSERT INTO t1 VALUES (26,'a71250b7ed780f6ef3185bfffe027983',5,1500,'b','1999-12-27 09:44:24');
 
83
INSERT INTO t1 VALUES (24,'4d75906f3c37ecff478a1eb56637aa09',3,5400,'y','1999-12-23 17:29:12');
 
84
INSERT INTO t1 VALUES (25,'4d75906f3c37ecff478a1eb56637aa09',4,6500,'y','1999-12-23 17:29:12');
 
85
INSERT INTO t1 VALUES (27,'a71250b7ed780f6ef3185bfffe027983',3,6200,'b','1999-12-27 09:44:24');
 
86
INSERT INTO t1 VALUES (28,'a71250b7ed780f6ef3185bfffe027983',3,5400,'y','1999-12-27 09:44:36');
 
87
INSERT INTO t1 VALUES (29,'a71250b7ed780f6ef3185bfffe027983',4,17700,'b','1999-12-27 09:45:05');
 
88
 
 
89
CREATE TABLE t2 (
 
90
  id int(11) NOT NULL auto_increment,
 
91
  category int(11) DEFAULT '0' NOT NULL,
 
92
  county int(11) DEFAULT '0' NOT NULL,
 
93
  state int(11) DEFAULT '0' NOT NULL,
 
94
  phones int(11) DEFAULT '0' NOT NULL,
 
95
  nophones int(11) DEFAULT '0' NOT NULL,
 
96
  PRIMARY KEY (id),
 
97
  KEY category (category,county,state)
 
98
);
 
99
INSERT INTO t2 VALUES (3,2,11,12,5400,7800);
 
100
INSERT INTO t2 VALUES (4,2,25,12,6500,11200);
 
101
INSERT INTO t2 VALUES (5,1,37,6,10000,12000);
 
102
select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
 
103
select a.id, b.category as catid, b.state as stateid, b.county as
 
104
countyid from t1 a, t2 b where (a.token =
 
105
'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id) order by a.id;
 
106
 
 
107
drop table t1, t2;
 
108
 
 
109
#
 
110
# Test of join of many tables.
 
111
 
 
112
create table t1 (a int primary key);
 
113
insert into t1 values(1),(2);
 
114
select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
 
115
--replace_result "31 tables" "XX tables" "61 tables" "XX tables"
 
116
--error 1116
 
117
select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
 
118
select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
 
119
--replace_result "31 tables" "XX tables" "61 tables" "XX tables"
 
120
--error 1116
 
121
select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
 
122
drop table t1;
 
123
 
 
124
#
 
125
# Simple join test. This failed in 3.23.42, there should have been
 
126
# no matches, still three matches were found.
 
127
#
 
128
 
 
129
CREATE TABLE t1 (
 
130
  a int(11) NOT NULL,
 
131
  b int(11) NOT NULL,
 
132
  PRIMARY KEY  (a,b)
 
133
) ENGINE=MyISAM;
 
134
 
 
135
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,3);
 
136
 
 
137
CREATE TABLE t2 (
 
138
  a int(11) default NULL
 
139
) ENGINE=MyISAM;
 
140
INSERT INTO t2 VALUES (2),(3);
 
141
SELECT t1.a,t2.a,b FROM t1,t2 WHERE t1.a=t2.a AND (t1.a=1 OR t1.a=2) AND b>=1 AND b<=3;
 
142
DROP TABLE t1, t2;
 
143
 
 
144
#
 
145
# TEST LEFT JOIN with DATE columns
 
146
#
 
147
 
 
148
CREATE TABLE t1 (d DATE NOT NULL);
 
149
CREATE TABLE t2 (d DATE NOT NULL);
 
150
INSERT INTO t1 (d) VALUES ('2001-08-01'),('0000-00-00');
 
151
SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL;
 
152
SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE d IS NULL;
 
153
SELECT * from t1 WHERE t1.d IS NULL;
 
154
SELECT * FROM t1 WHERE 1/0 IS NULL;
 
155
DROP TABLE t1,t2;
 
156
 
 
157
#
 
158
# Problem with reference from const tables
 
159
#
 
160
CREATE TABLE t1 (
 
161
  Document_ID varchar(50) NOT NULL default '',
 
162
  Contractor_ID varchar(6) NOT NULL default '',
 
163
  Language_ID char(3) NOT NULL default '',
 
164
  Expiration_Date datetime default NULL,
 
165
  Publishing_Date datetime default NULL,
 
166
  Title text,
 
167
  Column_ID varchar(50) NOT NULL default '',
 
168
  PRIMARY KEY  (Language_ID,Document_ID,Contractor_ID)
 
169
);
 
170
 
 
171
INSERT INTO t1 VALUES ('xep80','1','ger','2001-12-31 20:00:00','2001-11-12 10:58:00','Kartenbestellung - jetzt auch online','anle'),('','999998','',NULL,NULL,NULL,'');
 
172
 
 
173
CREATE TABLE t2 (
 
174
  Contractor_ID char(6) NOT NULL default '',
 
175
  Language_ID char(3) NOT NULL default '',
 
176
  Document_ID char(50) NOT NULL default '',
 
177
  CanRead char(1) default NULL,
 
178
  Customer_ID int(11) NOT NULL default '0',
 
179
  PRIMARY KEY  (Contractor_ID,Language_ID,Document_ID,Customer_ID)
 
180
);
 
181
 
 
182
INSERT INTO t2 VALUES ('5','ger','xep80','1',999999),('1','ger','xep80','1',999999);
 
183
CREATE TABLE t3 (
 
184
  Language_ID char(3) NOT NULL default '',
 
185
  Column_ID char(50) NOT NULL default '',
 
186
  Contractor_ID char(6) NOT NULL default '',
 
187
  CanRead char(1) default NULL,
 
188
  Active char(1) default NULL,
 
189
  PRIMARY KEY  (Language_ID,Column_ID,Contractor_ID)
 
190
);
 
191
INSERT INTO t3 VALUES ('ger','home','1','1','1'),('ger','Test','1','0','0'),('ger','derclu','1','0','0'),('ger','clubne','1','0','0'),('ger','philos','1','0','0'),('ger','clubko','1','0','0'),('ger','clubim','1','1','1'),('ger','progra','1','0','0'),('ger','progvo','1','0','0'),('ger','progsp','1','0','0'),('ger','progau','1','0','0'),('ger','progku','1','0','0'),('ger','progss','1','0','0'),('ger','nachl','1','0','0'),('ger','mitgli','1','0','0'),('ger','mitsu','1','0','0'),('ger','mitbus','1','0','0'),('ger','ergmar','1','1','1'),('ger','home','4','1','1'),('ger','derclu','4','1','1'),('ger','clubne','4','0','0'),('ger','philos','4','1','1'),('ger','clubko','4','1','1'),('ger','clubim','4','1','1'),('ger','progra','4','1','1'),('ger','progvo','4','1','1'),('ger','progsp','4','1','1'),('ger','progau','4','0','0'),('ger','progku','4','1','1'),('ger','progss','4','1','1'),('ger','nachl','4','1','1'),('ger','mitgli','4','0','0'),('ger','mitsu','4','0','0'),('ger','mitbus','4','0','0'),('ger','ergmar','4','1','1'),('ger','progra2','1','0','0'),('ger','archiv','4','1','1'),('ger','anmeld','4','1','1'),('ger','thema','4','1','1'),('ger','edito','4','1','1'),('ger','madis','4','1','1'),('ger','enma','4','1','1'),('ger','madis','1','1','1'),('ger','enma','1','1','1'),('ger','vorsch','4','0','0'),('ger','veranst','4','0','0'),('ger','anle','4','1','1'),('ger','redak','4','1','1'),('ger','nele','4','1','1'),('ger','aukt','4','1','1'),('ger','callcenter','4','1','1'),('ger','anle','1','0','0');
 
192
delete from t1 where Contractor_ID='999998';
 
193
insert into t1 (Contractor_ID) Values ('999998');
 
194
SELECT DISTINCT COUNT(t1.Title) FROM t1,
 
195
t2, t3 WHERE 
 
196
t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND 
 
197
t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= 
 
198
Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND 
 
199
t1.Document_ID = t2.Document_ID AND 
 
200
t1.Language_ID = t2.Language_ID AND 
 
201
t1.Contractor_ID = t2.Contractor_ID AND ( 
 
202
t2.Customer_ID = '4'  OR 
 
203
t2.Customer_ID = '999999'  OR 
 
204
t2.Customer_ID = '1' )AND t2.CanRead 
 
205
= '1'  AND t1.Column_ID=t3.Column_ID AND 
 
206
t1.Language_ID=t3.Language_ID AND ( 
 
207
t3.Contractor_ID = '4'  OR 
 
208
t3.Contractor_ID = '999999'  OR 
 
209
t3.Contractor_ID = '1') AND 
 
210
t3.CanRead='1' AND t3.Active='1';
 
211
SELECT DISTINCT COUNT(t1.Title) FROM t1,
 
212
t2, t3 WHERE 
 
213
t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND 
 
214
t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= 
 
215
Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND 
 
216
t1.Document_ID = t2.Document_ID AND 
 
217
t1.Language_ID = t2.Language_ID AND 
 
218
t1.Contractor_ID = t2.Contractor_ID AND ( 
 
219
t2.Customer_ID = '4'  OR 
 
220
t2.Customer_ID = '999999'  OR 
 
221
t2.Customer_ID = '1' )AND t2.CanRead 
 
222
= '1'  AND t1.Column_ID=t3.Column_ID AND 
 
223
t1.Language_ID=t3.Language_ID AND ( 
 
224
t3.Contractor_ID = '4'  OR 
 
225
t3.Contractor_ID = '999999'  OR 
 
226
t3.Contractor_ID = '1') AND 
 
227
t3.CanRead='1' AND t3.Active='1';
 
228
drop table t1,t2,t3;
 
229
 
 
230
#
 
231
# Bug when doing full join and NULL fields.
 
232
#
 
233
 
 
234
CREATE TABLE t1 (
 
235
  t1_id int(11) default NULL,
 
236
  t2_id int(11) default NULL,
 
237
  type enum('Cost','Percent') default NULL,
 
238
  cost_unit enum('Cost','Unit') default NULL,
 
239
  min_value double default NULL,
 
240
  max_value double default NULL,
 
241
  t3_id int(11) default NULL,
 
242
  item_id int(11) default NULL
 
243
) ENGINE=MyISAM;
 
244
INSERT INTO t1 VALUES (12,5,'Percent','Cost',-1,0,-1,-1),(14,4,'Percent','Cost',-1,0,-1,-1),(18,5,'Percent','Cost',-1,0,-1,-1),(19,4,'Percent','Cost',-1,0,-1,-1),(20,5,'Percent','Cost',100,-1,22,291),(21,5,'Percent','Cost',100,-1,18,291),(22,1,'Percent','Cost',100,-1,6,291),(23,1,'Percent','Cost',100,-1,21,291),(24,1,'Percent','Cost',100,-1,9,291),(25,1,'Percent','Cost',100,-1,4,291),(26,1,'Percent','Cost',100,-1,20,291),(27,4,'Percent','Cost',100,-1,7,202),(28,1,'Percent','Cost',50,-1,-1,137),(29,2,'Percent','Cost',100,-1,4,354),(30,2,'Percent','Cost',100,-1,9,137),(93,2,'Cost','Cost',-1,10000000,-1,-1);
 
245
CREATE TABLE t2 (
 
246
  id int(10) unsigned NOT NULL auto_increment,
 
247
  name varchar(255) default NULL,
 
248
  PRIMARY KEY  (id)
 
249
) ENGINE=MyISAM;
 
250
INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5');
 
251
select t1.*, t2.*  from t1, t2 where t2.id=t1.t2_id limit 2;
 
252
drop table t1,t2;
 
253
 
 
254
#
 
255
# Bug in range optimiser with MAYBE_KEY
 
256
#
 
257
 
 
258
CREATE TABLE t1 (
 
259
  siteid varchar(25) NOT NULL default '',
 
260
  emp_id varchar(30) NOT NULL default '',
 
261
  rate_code varchar(10) default NULL,
 
262
  UNIQUE KEY site_emp (siteid,emp_id),
 
263
  KEY siteid (siteid)
 
264
) ENGINE=MyISAM;
 
265
INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust');
 
266
CREATE TABLE t2 (
 
267
  siteid varchar(25) NOT NULL default '',
 
268
  rate_code varchar(10) NOT NULL default '',
 
269
  base_rate float NOT NULL default '0',
 
270
  PRIMARY KEY  (siteid,rate_code)
 
271
) ENGINE=MyISAM;
 
272
INSERT INTO t2 VALUES ('rivercats','cust',20);
 
273
SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
 
274
SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
 
275
SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND siteid = 'rivercats';
 
276
SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE siteid = 'rivercats' AND emp.emp_id = 'psmith';
 
277
drop table t1,t2;
 
278
 
 
279
#
 
280
# Problem with internal list handling when reducing WHERE
 
281
#
 
282
 
 
283
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
 
284
CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
 
285
INSERT INTO t1 VALUES (1, 'A');
 
286
INSERT INTO t2 VALUES (1, 'B');
 
287
 
 
288
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B');
 
289
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B';
 
290
SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1;
 
291
drop table t1,t2;
 
292
 
 
293
#
 
294
# dummy natural join (no common columns) Bug #4807
 
295
#
 
296
 
 
297
CREATE TABLE t1 (a int);
 
298
CREATE TABLE t2 (b int);
 
299
CREATE TABLE t3 (c int);
 
300
SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
 
301
DROP TABLE t1, t2, t3;
 
302
 
 
303
#
 
304
# Test combination of join methods
 
305
#
 
306
 
 
307
create table t1 (i int);
 
308
create table t2 (i int);
 
309
create table t3 (i int);
 
310
insert into t1 values(1),(2);
 
311
insert into t2 values(2),(3);
 
312
insert into t3 values (2),(4);
 
313
 
 
314
select * from t1 natural left join t2;
 
315
select * from t1 left join t2 on (t1.i=t2.i);
 
316
select * from t1 natural left join t2 natural left join t3;
 
317
select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);
 
318
 
 
319
select * from t3 natural right join t2;
 
320
select * from t3 right join t2 on (t3.i=t2.i);
 
321
select * from t3 natural right join t2 natural right join t1;
 
322
select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
 
323
 
 
324
select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
 
325
select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
 
326
select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
 
327
select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
 
328
 
 
329
select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
 
330
select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
 
331
select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
 
332
select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
 
333
drop table t1,t2,t3;
 
334
 
 
335
#
 
336
# Bug #27531: Query performance degredation in 4.1.22 and greater
 
337
#
 
338
CREATE TABLE t1 (a int, b int default 0, c int default 1);
 
339
 
 
340
INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
 
341
INSERT INTO t1 (a) SELECT a + 8 FROM t1;
 
342
INSERT INTO t1 (a) SELECT a + 16 FROM t1;
 
343
 
 
344
CREATE TABLE t2 (a int, d int, e int default 0);
 
345
 
 
346
INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4);
 
347
INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2;
 
348
INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2;
 
349
 
 
350
# should use join cache
 
351
EXPLAIN
 
352
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
 
353
  ORDER BY t1.b, t1.c;
 
354
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
 
355
  ORDER BY t1.b, t1.c;
 
356
 
 
357
DROP TABLE t1,t2;
 
358
 
 
359
# End of 4.1 tests
 
360
 
 
361
#
 
362
#  Tests for WL#2486 Natural/using join according to SQL:2003.
 
363
#
 
364
#  NOTICE:
 
365
#  - The tests are designed so that all statements, except MySQL
 
366
#    extensions run on any SQL server. Please do no change.
 
367
#  - Tests marked with TODO will be submitted as bugs.
 
368
#
 
369
 
 
370
create table t1 (c int, b int);
 
371
create table t2 (a int, b int);
 
372
create table t3 (b int, c int);
 
373
create table t4 (y int, c int);
 
374
create table t5 (y int, z int);
 
375
create table t6 (a int, c int);
 
376
 
 
377
insert into t1 values (10,1);
 
378
insert into t1 values (3 ,1);
 
379
insert into t1 values (3 ,2);
 
380
insert into t2 values (2, 1);
 
381
insert into t3 values (1, 3);
 
382
insert into t3 values (1,10);
 
383
insert into t4 values (11,3);
 
384
insert into t4 values (2, 3);
 
385
insert into t5 values (11,4);
 
386
insert into t6 values (2, 3);
 
387
 
 
388
select * from t1 natural join t2;
 
389
# as above, but column names are cross-renamed: a->c, c->b, b->a
 
390
select * from t1 natural join t2;
 
391
# as above, but column names are aliased: a->c, c->b, b->a
 
392
select b as a, c as b, a as c from t1 natural join t2;
 
393
#  as above, but column names are cross-renamed, and aliased
 
394
#  a->c->b, c->b->a, b->a->c
 
395
select a as c, c as b, b as a from t1 natural join t2;
 
396
 
 
397
# Views with JOIN ... ON
 
398
select t1.c, t1.b, t2.a from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
 
399
select t1.c as b, t1.b as a, t2.a as c
 
400
from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
 
401
 
 
402
# Views with bigger natural join
 
403
select * from t1 natural join (t2 natural join t3);
 
404
 
 
405
# Nested natural/using joins.
 
406
select * from (t1 natural join t2) natural join (t3 natural join t4);
 
407
select * from (t1 natural join t2) natural left join (t3 natural join t4);
 
408
select * from (t3 natural join t4) natural right join (t1 natural join t2);
 
409
select * from (t1 natural left join t2) natural left join (t3 natural left join t4);
 
410
select * from (t4 natural right join t3) natural right join (t2 natural right join t1);
 
411
select * from t1 natural join t2 natural join t3 natural join t4;
 
412
select * from ((t1 natural join t2) natural join t3) natural join t4;
 
413
select * from t1 natural join (t2 natural join (t3 natural join t4));
 
414
# BUG#15355: this query fails in 'prepared statements' mode
 
415
# select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5;
 
416
# select * from ((t3 natural left join (t1 natural left join t2)) natural left join t4) natural left join t5;
 
417
select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3));
 
418
select * from (t1 natural join t2), (t3 natural join t4);
 
419
# MySQL extension - nested comma ',' operator instead of cross join.
 
420
select * from t5 natural join ((t1 natural join t2), (t3 natural join t4));
 
421
select * from  ((t1 natural join t2),  (t3 natural join t4)) natural join t5;
 
422
select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4));
 
423
select * from  ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5;
 
424
 
 
425
select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c);
 
426
select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c));
 
427
 
 
428
 
 
429
# Other clauses refer to NJ columns.
 
430
select a,b,c from (t1 natural join t2) natural join (t3 natural join t4)
 
431
where b + 1 = y or b + 10 = y group by b,c,a having min(b) < max(y) order by a;
 
432
select * from (t1 natural join t2) natural left join (t3 natural join t4)
 
433
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;
 
434
select * from (t3 natural join t4) natural right join (t1 natural join t2)
 
435
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;
 
436
 
 
437
# Qualified column references to NJ columns.
 
438
select * from t1 natural join t2 where t1.c > t2.a;
 
439
select * from t1 natural join t2 where t1.b > t2.b;
 
440
select * from t1 natural left join (t4 natural join t5) where t5.z is not NULL;
 
441
 
 
442
# Nested 'join ... on' - name resolution of ON conditions
 
443
select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
 
444
select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c;
 
445
select * from t1 natural join (t2 join t4 on b + 1 = y);
 
446
select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c);
 
447
 
 
448
# MySQL extension - 'join ... on' over nested comma operator
 
449
select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c);
 
450
select * from (t1 natural join t2) join (t3 natural join t4) on a = y;
 
451
select * from ((t3 join (t1 join t2 on c > a) on t3.b < t2.a) join t4 on y > t1.c) join t5 on z = t1.b + 3;
 
452
 
 
453
# MySQL extension - refererence qualified coalesced columns
 
454
select * from t1 natural join t2 where t1.b > 0;
 
455
select * from t1 natural join (t4 natural join t5) where t4.y > 7;
 
456
select * from (t4 natural join t5) natural join t1 where t4.y > 7;
 
457
select * from t1 natural left join (t4 natural join t5) where t4.y > 7;
 
458
select * from (t4 natural join t5) natural right join t1 where t4.y > 7;
 
459
select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b;
 
460
 
 
461
# MySQL extension - select qualified columns of NJ columns
 
462
select t1.*, t2.* from t1 natural join t2;
 
463
select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4);
 
464
 
 
465
# Queries over subselects in the FROM clause
 
466
select * from (select * from t1 natural join t2) as t12
 
467
              natural join
 
468
              (select * from t3 natural join t4) as t34;
 
469
select * from (select * from t1 natural join t2) as t12
 
470
              natural left join
 
471
              (select * from t3 natural join t4) as t34;
 
472
select * from (select * from t3 natural join t4) as t34
 
473
              natural right join
 
474
              (select * from t1 natural join t2) as t12;
 
475
 
 
476
# TODO: add tests with correlated subqueries for natural join/join on.
 
477
# related to BUG#15269
 
478
 
 
479
 
 
480
#--------------------------------------------------------------------
 
481
# Negative tests (tests for errors)
 
482
#--------------------------------------------------------------------
 
483
# works in Oracle - bug
 
484
-- error 1052
 
485
select * from t1 natural join (t3 cross join t4);
 
486
# works in Oracle - bug
 
487
-- error 1052
 
488
select * from (t3 cross join t4) natural join t1;
 
489
-- error 1052
 
490
select * from t1 join (t2, t3) using (b);
 
491
-- error 1052
 
492
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
 
493
-- error 1052
 
494
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
 
495
-- error 1052
 
496
select * from t6 natural join ((t1 natural join t2),  (t3 natural join t4));
 
497
-- error 1052
 
498
select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4);
 
499
-- error 1052
 
500
select * from  (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b);
 
501
# this one is OK, the next equivalent one is incorrect (bug in Oracle)
 
502
-- error 1052
 
503
select * from (t3 join (t4 natural join t5) on (b < z))
 
504
              natural join
 
505
              (t1 natural join t2);
 
506
-- error 1052
 
507
select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z));
 
508
 
 
509
#
 
510
# Bug #17523 natural join and information_schema
 
511
#
 
512
# We mask out the Privileges column because it differs with embedded server
 
513
--replace_column 32 #
 
514
query_vertical 
 
515
select * from information_schema.statistics join information_schema.columns
 
516
              using(table_name,column_name) where table_name='user';
 
517
 
 
518
drop table t1;
 
519
drop table t2;
 
520
drop table t3;
 
521
drop table t4;
 
522
drop table t5;
 
523
drop table t6;
 
524
 
 
525
#
 
526
# BUG#15229 - columns of nested joins that are not natural joins incorrectly
 
527
# materialized
 
528
#
 
529
create table t1 (a1 int, a2 int);
 
530
create table t2 (a1 int, b int);
 
531
create table t3 (c1 int, c2 int);
 
532
create table t4 (c2 int);
 
533
 
 
534
insert into t1 values (1,1);
 
535
insert into t2 values (1,1);
 
536
insert into t3 values (1,1);
 
537
insert into t4 values (1);
 
538
 
 
539
select * from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
 
540
select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
 
541
select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
 
542
select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
 
543
select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2);
 
544
select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4;
 
545
 
 
546
drop table t1,t2,t3,t4;
 
547
 
 
548
#
 
549
# BUG#15355: Common natural join column not resolved in prepared statement nested query
 
550
#
 
551
create table t1 (c int, b int);
 
552
create table t2 (a int, b int);
 
553
create table t3 (b int, c int);
 
554
create table t4 (y int, c int);
 
555
create table t5 (y int, z int);
 
556
 
 
557
insert into t1 values (3,2);
 
558
insert into t2 values (1,2);
 
559
insert into t3 values (2,3);
 
560
insert into t4 values (1,3);
 
561
insert into t5 values (1,4);
 
562
 
 
563
# this works
 
564
select * from ((t3 natural join (t1 natural join t2)) natural join t4)
 
565
  natural join t5;
 
566
drop table t1, t2, t3, t4, t5;
 
567
 
 
568
# End of tests for WL#2486 - natural/using join
 
569
 
 
570
# BUG#27939: Early NULLs filtering doesn't work for eq_ref access
 
571
create table t1 (a int, b int);
 
572
insert into t1 values 
 
573
  (NULL, 1),
 
574
  (NULL, 2),
 
575
  (NULL, 3),
 
576
  (NULL, 4);
 
577
 
 
578
create table t2 (a int not null, primary key(a));
 
579
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
580
 
 
581
create table t3 (a int not null, primary key(a));
 
582
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
583
 
 
584
flush status;
 
585
select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
 
586
explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
 
587
--echo We expect rnd_next=5, and read_key must be 0 because of short-cutting:
 
588
show status like 'Handler_read%'; 
 
589
drop table t1, t2, t3;
 
590
 
 
591
#
 
592
# BUG#14940: Make E(#rows) from "range" access be re-used by range optimizer
 
593
#
 
594
create table t1 (a int); 
 
595
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
596
 
 
597
create table t2 (a int, b int, filler char(100), key(a), key(b));
 
598
create table t3 (a int, b int, filler char(100), key(a), key(b));
 
599
 
 
600
insert into t2 
 
601
  select @a:= A.a + 10*(B.a + 10*C.a), @a, 'filler' from t1 A, t1 B, t1 C;
 
602
insert into t3 select * from t2 where a < 800;
 
603
 
 
604
# The order of tables must be t2,t3:
 
605
explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b;
 
606
 
 
607
drop table t1, t2, t3;
 
608
 
 
609
# BUG#14940 {Wrong query plan is chosen because of odd results of
 
610
# prev_record_reads() function }
 
611
create table t1 (a int); 
 
612
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
613
 
 
614
create table t2 (a int, b int, primary key(a));
 
615
insert into t2 select @v:=A.a+10*B.a, @v  from t1 A, t1 B;
 
616
 
 
617
explain select * from t1;
 
618
show status like '%cost%';
 
619
select 'The cost of accessing t1 (dont care if it changes' '^';
 
620
 
 
621
select 'vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv' Z;
 
622
 
 
623
explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b;
 
624
show status like '%cost%';
 
625
select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z;
 
626
 
 
627
 
 
628
 
 
629
drop table t1, t2;
 
630
 
 
631
#
 
632
# Bug #31094: Forcing index-based sort doesn't work anymore if joins are
 
633
# done
 
634
#
 
635
 
 
636
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
 
637
CREATE TABLE t2 (c INT PRIMARY KEY, d INT);
 
638
 
 
639
INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL);
 
640
INSERT INTO t1 SELECT a + 4, b FROM t1;
 
641
INSERT INTO t1 SELECT a + 8, b FROM t1;
 
642
INSERT INTO t1 SELECT a + 16, b FROM t1;
 
643
INSERT INTO t1 SELECT a + 32, b FROM t1;
 
644
INSERT INTO t1 SELECT a + 64, b FROM t1;
 
645
INSERT INTO t2 SELECT a, b FROM t1;
 
646
 
 
647
#expect indexed ORDER BY
 
648
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
 
649
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
 
650
SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
 
651
SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
 
652
 
 
653
#expect filesort
 
654
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
 
655
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
 
656
SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
 
657
SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
 
658
 
 
659
DROP TABLE IF EXISTS t1,t2;
 
660
--echo End of 5.0 tests.