← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/join.test

  • Committer: Jim Winstead
  • Date: 2008-07-19 02:56:45 UTC
  • mto: (202.1.8 codestyle)
  • mto: This revision was merged to the branch mainline in revision 207.
  • Revision ID: jimw@mysql.com-20080719025645-w2pwytebgzusjzjb
Various fixes to enable compilation on Mac OS X, and remove the glib dependency.
Temporarily disables tab-completion in the drizzle client until an appropriate
autoconf check can be added/enabled.

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