← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/join.test

  • Committer: Brian Aker
  • Date: 2008-07-20 05:41:52 UTC
  • Revision ID: brian@tangent.org-20080720054152-5laf6plsb0o7h6ss
Documentation cleanup

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/join.test
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 NOT NULL auto_increment,
66
 
  token varchar(100) DEFAULT '' NOT NULL,
67
 
  count int DEFAULT '0' NOT NULL,
68
 
  qty int,
69
 
  phone char(1) DEFAULT '' NOT NULL,
70
 
  timestamp datetime,
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 NOT NULL auto_increment,
91
 
  category int DEFAULT '0' NOT NULL,
92
 
  county int DEFAULT '0' NOT NULL,
93
 
  state int DEFAULT '0' NOT NULL,
94
 
  phones int DEFAULT '0' NOT NULL,
95
 
  nophones int 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 ER_TOO_MANY_TABLES
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 ER_TOO_MANY_TABLES
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 TEMPORARY TABLE t1 (
130
 
  a int NOT NULL,
131
 
  b int 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 TEMPORARY TABLE t2 (
138
 
  a int 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);
149
 
CREATE TABLE t2 (d DATE);
150
 
INSERT INTO t1 (d) VALUES ('2001-08-01'),(NULL);
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
 
--error ER_DIVISION_BY_ZERO
155
 
SELECT * FROM t1 WHERE 1/0 IS NULL;
156
 
DROP TABLE t1,t2;
157
 
 
158
 
#
159
 
# Problem with reference from const tables
160
 
#
161
 
CREATE TABLE t1 (
162
 
  Document_ID varchar(50) NOT NULL default '',
163
 
  Contractor_ID varchar(6) NOT NULL default '',
164
 
  Language_ID char(3) NOT NULL default '',
165
 
  Expiration_Date datetime default NULL,
166
 
  Publishing_Date datetime default NULL,
167
 
  Title text,
168
 
  Column_ID varchar(50) NOT NULL default '',
169
 
  PRIMARY KEY  (Language_ID,Document_ID,Contractor_ID)
170
 
);
171
 
 
172
 
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,'');
173
 
 
174
 
CREATE TABLE t2 (
175
 
  Contractor_ID char(6) NOT NULL default '',
176
 
  Language_ID char(3) NOT NULL default '',
177
 
  Document_ID char(50) NOT NULL default '',
178
 
  CanRead char(1) default NULL,
179
 
  Customer_ID int NOT NULL default '0',
180
 
  PRIMARY KEY  (Contractor_ID,Language_ID,Document_ID,Customer_ID)
181
 
);
182
 
 
183
 
INSERT INTO t2 VALUES ('5','ger','xep80','1',999999),('1','ger','xep80','1',999999);
184
 
CREATE TABLE t3 (
185
 
  Language_ID char(3) NOT NULL default '',
186
 
  Column_ID char(50) NOT NULL default '',
187
 
  Contractor_ID char(6) NOT NULL default '',
188
 
  CanRead char(1) default NULL,
189
 
  Active char(1) default NULL,
190
 
  PRIMARY KEY  (Language_ID,Column_ID,Contractor_ID)
191
 
);
192
 
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');
193
 
delete from t1 where Contractor_ID='999998';
194
 
insert into t1 (Contractor_ID) Values ('999998');
195
 
SELECT DISTINCT COUNT(t1.Title) FROM t1,
196
 
t2, t3 WHERE 
197
 
t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND 
198
 
t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= 
199
 
Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND 
200
 
t1.Document_ID = t2.Document_ID AND 
201
 
t1.Language_ID = t2.Language_ID AND 
202
 
t1.Contractor_ID = t2.Contractor_ID AND ( 
203
 
t2.Customer_ID = '4'  OR 
204
 
t2.Customer_ID = '999999'  OR 
205
 
t2.Customer_ID = '1' )AND t2.CanRead 
206
 
= '1'  AND t1.Column_ID=t3.Column_ID AND 
207
 
t1.Language_ID=t3.Language_ID AND ( 
208
 
t3.Contractor_ID = '4'  OR 
209
 
t3.Contractor_ID = '999999'  OR 
210
 
t3.Contractor_ID = '1') AND 
211
 
t3.CanRead='1' AND t3.Active='1';
212
 
SELECT DISTINCT COUNT(t1.Title) FROM t1,
213
 
t2, t3 WHERE 
214
 
t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND 
215
 
t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= 
216
 
Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND 
217
 
t1.Document_ID = t2.Document_ID AND 
218
 
t1.Language_ID = t2.Language_ID AND 
219
 
t1.Contractor_ID = t2.Contractor_ID AND ( 
220
 
t2.Customer_ID = '4'  OR 
221
 
t2.Customer_ID = '999999'  OR 
222
 
t2.Customer_ID = '1' )AND t2.CanRead 
223
 
= '1'  AND t1.Column_ID=t3.Column_ID AND 
224
 
t1.Language_ID=t3.Language_ID AND ( 
225
 
t3.Contractor_ID = '4'  OR 
226
 
t3.Contractor_ID = '999999'  OR 
227
 
t3.Contractor_ID = '1') AND 
228
 
t3.CanRead='1' AND t3.Active='1';
229
 
drop table t1,t2,t3;
230
 
 
231
 
#
232
 
# Bug when doing full join and NULL fields.
233
 
#
234
 
 
235
 
CREATE TEMPORARY TABLE t1 (
236
 
  t1_id int default NULL,
237
 
  t2_id int default NULL,
238
 
  type enum('Cost','Percent') default NULL,
239
 
  cost_unit enum('Cost','Unit') default NULL,
240
 
  min_value double default NULL,
241
 
  max_value double default NULL,
242
 
  t3_id int default NULL,
243
 
  item_id int default NULL
244
 
) ENGINE=MyISAM;
245
 
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);
246
 
CREATE TEMPORARY TABLE t2 (
247
 
  id int NOT NULL auto_increment,
248
 
  name varchar(255) default NULL,
249
 
  PRIMARY KEY  (id)
250
 
) ENGINE=MyISAM;
251
 
INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5');
252
 
--sorted_result
253
 
select t1.*, t2.*  from t1, t2 where t2.id=t1.t2_id limit 2;
254
 
drop table t1,t2;
255
 
 
256
 
#
257
 
# Bug in range optimiser with MAYBE_KEY
258
 
#
259
 
 
260
 
CREATE TEMPORARY TABLE t1 (
261
 
  siteid varchar(25) NOT NULL default '',
262
 
  emp_id varchar(30) NOT NULL default '',
263
 
  rate_code varchar(10) default NULL,
264
 
  UNIQUE KEY site_emp (siteid,emp_id),
265
 
  KEY siteid (siteid)
266
 
) ENGINE=MyISAM;
267
 
INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust');
268
 
CREATE TEMPORARY TABLE t2 (
269
 
  siteid varchar(25) NOT NULL default '',
270
 
  rate_code varchar(10) NOT NULL default '',
271
 
  base_rate float NOT NULL default '0',
272
 
  PRIMARY KEY  (siteid,rate_code)
273
 
) ENGINE=MyISAM;
274
 
INSERT INTO t2 VALUES ('rivercats','cust',20);
275
 
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';
276
 
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';
277
 
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';
278
 
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';
279
 
drop table t1,t2;
280
 
 
281
 
#
282
 
# Problem with internal list handling when reducing WHERE
283
 
#
284
 
 
285
 
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
286
 
CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
287
 
INSERT INTO t1 VALUES (1, 'A');
288
 
INSERT INTO t2 VALUES (1, 'B');
289
 
 
290
 
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B');
291
 
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B';
292
 
SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1;
293
 
drop table t1,t2;
294
 
 
295
 
#
296
 
# dummy natural join (no common columns) Bug #4807
297
 
#
298
 
 
299
 
CREATE TABLE t1 (a int);
300
 
CREATE TABLE t2 (b int);
301
 
CREATE TABLE t3 (c int);
302
 
SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
303
 
DROP TABLE t1, t2, t3;
304
 
 
305
 
#
306
 
# Test combination of join methods
307
 
#
308
 
 
309
 
create table t1 (i int);
310
 
create table t2 (i int);
311
 
create table t3 (i int);
312
 
insert into t1 values(1),(2);
313
 
insert into t2 values(2),(3);
314
 
insert into t3 values (2),(4);
315
 
 
316
 
select * from t1 natural left join t2;
317
 
select * from t1 left join t2 on (t1.i=t2.i);
318
 
select * from t1 natural left join t2 natural left join t3;
319
 
select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);
320
 
 
321
 
select * from t3 natural right join t2;
322
 
select * from t3 right join t2 on (t3.i=t2.i);
323
 
select * from t3 natural right join t2 natural right join t1;
324
 
select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
325
 
 
326
 
select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
327
 
select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
328
 
select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
329
 
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;
330
 
 
331
 
select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
332
 
select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
333
 
select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
334
 
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;
335
 
drop table t1,t2,t3;
336
 
 
337
 
#
338
 
# Bug #27531: Query performance degredation in 4.1.22 and greater
339
 
#
340
 
CREATE TABLE t1 (a int, b int default 0, c int default 1);
341
 
 
342
 
INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
343
 
INSERT INTO t1 (a) SELECT a + 8 FROM t1;
344
 
INSERT INTO t1 (a) SELECT a + 16 FROM t1;
345
 
 
346
 
CREATE TABLE t2 (a int, d int, e int default 0);
347
 
 
348
 
INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4);
349
 
INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2;
350
 
INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2;
351
 
 
352
 
# should use join cache
353
 
EXPLAIN
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
 
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
357
 
  ORDER BY t1.b, t1.c;
358
 
 
359
 
DROP TABLE t1,t2;
360
 
 
361
 
# End of 4.1 tests
362
 
 
363
 
#
364
 
#  Tests for WL#2486 Natural/using join according to SQL:2003.
365
 
#
366
 
#  NOTICE:
367
 
#  - The tests are designed so that all statements, except MySQL
368
 
#    extensions run on any SQL server. Please do no change.
369
 
#  - Tests marked with TODO will be submitted as bugs.
370
 
#
371
 
 
372
 
create table t1 (c int, b int);
373
 
create table t2 (a int, b int);
374
 
create table t3 (b int, c int);
375
 
create table t4 (y int, c int);
376
 
create table t5 (y int, z int);
377
 
create table t6 (a int, c int);
378
 
 
379
 
insert into t1 values (10,1);
380
 
insert into t1 values (3 ,1);
381
 
insert into t1 values (3 ,2);
382
 
insert into t2 values (2, 1);
383
 
insert into t3 values (1, 3);
384
 
insert into t3 values (1,10);
385
 
insert into t4 values (11,3);
386
 
insert into t4 values (2, 3);
387
 
insert into t5 values (11,4);
388
 
insert into t6 values (2, 3);
389
 
 
390
 
select * from t1 natural join t2;
391
 
# as above, but column names are cross-renamed: a->c, c->b, b->a
392
 
select * from t1 natural join t2;
393
 
# as above, but column names are aliased: a->c, c->b, b->a
394
 
select b as a, c as b, a as c from t1 natural join t2;
395
 
#  as above, but column names are cross-renamed, and aliased
396
 
#  a->c->b, c->b->a, b->a->c
397
 
select a as c, c as b, b as a from t1 natural join t2;
398
 
 
399
 
# Views with JOIN ... ON
400
 
select t1.c, t1.b, t2.a from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
401
 
select t1.c as b, t1.b as a, t2.a as c
402
 
from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
403
 
 
404
 
# Views with bigger natural join
405
 
--sorted_result
406
 
select * from t1 natural join (t2 natural join t3);
407
 
 
408
 
# Nested natural/using joins.
409
 
--sorted_result
410
 
select * from (t1 natural join t2) natural join (t3 natural join t4);
411
 
select * from (t1 natural join t2) natural left join (t3 natural join t4);
412
 
select * from (t3 natural join t4) natural right join (t1 natural join t2);
413
 
select * from (t1 natural left join t2) natural left join (t3 natural left join t4);
414
 
select * from (t4 natural right join t3) natural right join (t2 natural right join t1);
415
 
select * from t1 natural join t2 natural join t3 natural join t4;
416
 
select * from ((t1 natural join t2) natural join t3) natural join t4;
417
 
select * from t1 natural join (t2 natural join (t3 natural join t4));
418
 
# BUG#15355: this query fails in 'prepared statements' mode
419
 
# select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5;
420
 
# select * from ((t3 natural left join (t1 natural left join t2)) natural left join t4) natural left join t5;
421
 
select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3));
422
 
select * from (t1 natural join t2), (t3 natural join t4);
423
 
# MySQL extension - nested comma ',' operator instead of cross join.
424
 
select * from t5 natural join ((t1 natural join t2), (t3 natural join t4));
425
 
select * from  ((t1 natural join t2),  (t3 natural join t4)) natural join t5;
426
 
select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4));
427
 
select * from  ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5;
428
 
 
429
 
select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c);
430
 
select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c));
431
 
 
432
 
 
433
 
# Other clauses refer to NJ columns.
434
 
select a,b,c from (t1 natural join t2) natural join (t3 natural join t4)
435
 
where b + 1 = y or b + 10 = y group by b,c,a having min(b) < max(y) order by a;
436
 
select * from (t1 natural join t2) natural left join (t3 natural join t4)
437
 
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;
438
 
select * from (t3 natural join t4) natural right join (t1 natural join t2)
439
 
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;
440
 
 
441
 
# Qualified column references to NJ columns.
442
 
select * from t1 natural join t2 where t1.c > t2.a;
443
 
select * from t1 natural join t2 where t1.b > t2.b;
444
 
select * from t1 natural left join (t4 natural join t5) where t5.z is not NULL;
445
 
 
446
 
# Nested 'join ... on' - name resolution of ON conditions
447
 
select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
448
 
select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c;
449
 
select * from t1 natural join (t2 join t4 on b + 1 = y);
450
 
--sorted_result
451
 
select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c);
452
 
 
453
 
# MySQL extension - 'join ... on' over nested comma operator
454
 
--sorted_result
455
 
select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c);
456
 
select * from (t1 natural join t2) join (t3 natural join t4) on a = y;
457
 
--sorted_result
458
 
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;
459
 
 
460
 
# MySQL extension - refererence qualified coalesced columns
461
 
select * from t1 natural join t2 where t1.b > 0;
462
 
select * from t1 natural join (t4 natural join t5) where t4.y > 7;
463
 
select * from (t4 natural join t5) natural join t1 where t4.y > 7;
464
 
select * from t1 natural left join (t4 natural join t5) where t4.y > 7;
465
 
select * from (t4 natural join t5) natural right join t1 where t4.y > 7;
466
 
--sorted_result
467
 
select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b;
468
 
 
469
 
# MySQL extension - select qualified columns of NJ columns
470
 
select t1.*, t2.* from t1 natural join t2;
471
 
select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4);
472
 
 
473
 
# Queries over subselects in the FROM clause
474
 
select * from (select * from t1 natural join t2) as t12
475
 
              natural join
476
 
              (select * from t3 natural join t4) as t34;
477
 
select * from (select * from t1 natural join t2) as t12
478
 
              natural left join
479
 
              (select * from t3 natural join t4) as t34;
480
 
select * from (select * from t3 natural join t4) as t34
481
 
              natural right join
482
 
              (select * from t1 natural join t2) as t12;
483
 
 
484
 
# TODO: add tests with correlated subqueries for natural join/join on.
485
 
# related to BUG#15269
486
 
 
487
 
 
488
 
#--------------------------------------------------------------------
489
 
# Negative tests (tests for errors)
490
 
#--------------------------------------------------------------------
491
 
# works in Oracle - bug
492
 
select * from t1 natural join (t3 cross join t4);
493
 
# works in Oracle - bug
494
 
select * from (t3 cross join t4) natural join t1;
495
 
select * from t1 join (t2, t3) using (b);
496
 
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
497
 
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
498
 
select * from t6 natural join ((t1 natural join t2),  (t3 natural join t4));
499
 
select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4);
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
 
select * from (t3 join (t4 natural join t5) on (b < z))
503
 
              natural join
504
 
              (t1 natural join t2);
505
 
select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z));
506
 
 
507
 
drop table t1;
508
 
drop table t2;
509
 
drop table t3;
510
 
drop table t4;
511
 
drop table t5;
512
 
drop table t6;
513
 
 
514
 
#
515
 
# BUG#15229 - columns of nested joins that are not natural joins incorrectly
516
 
# materialized
517
 
#
518
 
create table t1 (a1 int, a2 int);
519
 
create table t2 (a1 int, b int);
520
 
create table t3 (c1 int, c2 int);
521
 
create table t4 (c2 int);
522
 
 
523
 
insert into t1 values (1,1);
524
 
insert into t2 values (1,1);
525
 
insert into t3 values (1,1);
526
 
insert into t4 values (1);
527
 
 
528
 
select * from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
529
 
select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
530
 
select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
531
 
select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
532
 
select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2);
533
 
select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4;
534
 
 
535
 
drop table t1,t2,t3,t4;
536
 
 
537
 
#
538
 
# BUG#15355: Common natural join column not resolved in prepared statement nested query
539
 
#
540
 
create table t1 (c int, b int);
541
 
create table t2 (a int, b int);
542
 
create table t3 (b int, c int);
543
 
create table t4 (y int, c int);
544
 
create table t5 (y int, z int);
545
 
 
546
 
insert into t1 values (3,2);
547
 
insert into t2 values (1,2);
548
 
insert into t3 values (2,3);
549
 
insert into t4 values (1,3);
550
 
insert into t5 values (1,4);
551
 
 
552
 
# this works
553
 
select * from ((t3 natural join (t1 natural join t2)) natural join t4)
554
 
  natural join t5;
555
 
drop table t1, t2, t3, t4, t5;
556
 
 
557
 
# End of tests for WL#2486 - natural/using join
558
 
 
559
 
# BUG#27939: Early NULLs filtering doesn't work for eq_ref access
560
 
create table t1 (a int, b int);
561
 
insert into t1 values 
562
 
  (NULL, 1),
563
 
  (NULL, 2),
564
 
  (NULL, 3),
565
 
  (NULL, 4);
566
 
 
567
 
create table t2 (a int not null, primary key(a));
568
 
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
569
 
 
570
 
create table t3 (a int not null, primary key(a));
571
 
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
572
 
 
573
 
flush status;
574
 
select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
575
 
explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
576
 
--echo We expect rnd_next=5, and read_key must be 0 because of short-cutting:
577
 
--replace_column 2 #
578
 
show status like 'Handler_read%'; 
579
 
drop table t1, t2, t3;
580
 
 
581
 
#
582
 
# BUG#14940: Make E(#rows) from "range" access be re-used by range optimizer
583
 
#
584
 
create table t1 (a int); 
585
 
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
586
 
 
587
 
create table t2 (a int, b int, filler char(100), key(a), key(b));
588
 
create table t3 (a int, b int, filler char(100), key(a), key(b));
589
 
 
590
 
insert into t2 
591
 
  select @a:= A.a + 10*(B.a + 10*C.a), @a, 'filler' from t1 A, t1 B, t1 C;
592
 
insert into t3 select * from t2 where a < 800;
593
 
 
594
 
# The order of tables must be t2,t3:
595
 
#explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b;
596
 
 
597
 
drop table t1, t2, t3;
598
 
 
599
 
# BUG#14940 {Wrong query plan is chosen because of odd results of
600
 
# prev_record_reads() function }
601
 
create table t1 (a int); 
602
 
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
603
 
 
604
 
create table t2 (a int, b int, primary key(a));
605
 
insert into t2 select @v:=A.a+10*B.a, @v  from t1 A, t1 B;
606
 
 
607
 
explain select * from t1;
608
 
--replace_column 2 #
609
 
show status like '%cost%';
610
 
select 'The cost of accessing t1 (dont care if it changes' '^';
611
 
 
612
 
select 'vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv' Z;
613
 
 
614
 
explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b;
615
 
--replace_column 2 #
616
 
show status like '%cost%';
617
 
select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z;
618
 
 
619
 
 
620
 
 
621
 
drop table t1, t2;
622
 
 
623
 
#
624
 
# Bug #31094: Forcing index-based sort doesn't work anymore if joins are
625
 
# done
626
 
#
627
 
 
628
 
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
629
 
CREATE TABLE t2 (c INT PRIMARY KEY, d INT);
630
 
 
631
 
INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL);
632
 
INSERT INTO t1 SELECT a + 4, b FROM t1;
633
 
INSERT INTO t1 SELECT a + 8, b FROM t1;
634
 
INSERT INTO t1 SELECT a + 16, b FROM t1;
635
 
INSERT INTO t1 SELECT a + 32, b FROM t1;
636
 
INSERT INTO t1 SELECT a + 64, b FROM t1;
637
 
INSERT INTO t2 SELECT a, b FROM t1;
638
 
 
639
 
#expect indexed ORDER BY
640
 
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
641
 
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
642
 
SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
643
 
SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
644
 
 
645
 
#expect filesort
646
 
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
647
 
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
648
 
SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
649
 
SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
650
 
 
651
 
DROP TABLE IF EXISTS t1,t2;
652
 
--echo End of 5.0 tests.