Debian Codegraph @

mysql-test/suite/optimizer_unfixed_bugs/t/bug41996-extra1.test

raw
1 set @bug41996_tmp=@@optimizer_switch;
2 set optimizer_switch='mrr=off';
3 --disable_warnings
4 DROP DATABASE IF EXISTS d1;
5 DROP DATABASE IF EXISTS d2;
6 DROP DATABASE IF EXISTS d3;
7 --enable_warnings
8 CREATE DATABASE d1;
9 CREATE DATABASE d2;
10 CREATE DATABASE d3;
11 CREATE TABLE d1.t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
12 CREATE TABLE d2.t2 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
13 CREATE TABLE d3.t3 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
14 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
15 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
16 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
17 DELETE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
18 SELECT * FROM d1.t1 ORDER BY c1;
19 SELECT * FROM d2.t2 ORDER BY c1;
20 SELECT * FROM d3.t3 ORDER BY c1;
21 DELETE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
22 SELECT * FROM d1.t1 ORDER BY c1;
23 SELECT * FROM d2.t2 ORDER BY c1;
24 SELECT * FROM d3.t3 ORDER BY c1;
25 DROP DATABASE d1;
26 DROP DATABASE d2;
27 DROP DATABASE d3;
28 CREATE DATABASE d1;
29 CREATE DATABASE d2;
30 CREATE DATABASE d3;
31 CREATE TABLE d1.t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
32 CREATE TABLE d2.t2 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
33 CREATE TABLE d3.t3 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
34 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
35 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
36 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
37 DELETE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
38 SELECT * FROM d1.t1 ORDER BY c1;
39 SELECT * FROM d2.t2 ORDER BY c1;
40 SELECT * FROM d3.t3 ORDER BY c1;
41 DELETE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
42 SELECT * FROM d1.t1 ORDER BY c1;
43 SELECT * FROM d2.t2 ORDER BY c1;
44 SELECT * FROM d3.t3 ORDER BY c1;
45 DROP DATABASE d1;
46 DROP DATABASE d2;
47 DROP DATABASE d3;
48 CREATE DATABASE d1;
49 CREATE DATABASE d2;
50 CREATE DATABASE d3;
51 CREATE TABLE d1.t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
52 CREATE TABLE d2.t2 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
53 CREATE TABLE d3.t3 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
54 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
55 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
56 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
57 DELETE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
58 SELECT * FROM d1.t1 ORDER BY c1;
59 SELECT * FROM d2.t2 ORDER BY c1;
60 SELECT * FROM d3.t3 ORDER BY c1;
61 DELETE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
62 SELECT * FROM d1.t1 ORDER BY c1;
63 SELECT * FROM d2.t2 ORDER BY c1;
64 SELECT * FROM d3.t3 ORDER BY c1;
65 DROP DATABASE d1;
66 DROP DATABASE d2;
67 DROP DATABASE d3;
68 CREATE DATABASE d1;
69 CREATE DATABASE d2;
70 CREATE DATABASE d3;
71 CREATE TABLE d1.t1 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
72 CREATE TABLE d2.t2 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
73 CREATE TABLE d3.t3 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
74 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
75 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
76 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
77 DELETE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
78 SELECT * FROM d1.t1 ORDER BY c1;
79 SELECT * FROM d2.t2 ORDER BY c1;
80 SELECT * FROM d3.t3 ORDER BY c1;
81 DELETE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
82 SELECT * FROM d1.t1 ORDER BY c1;
83 SELECT * FROM d2.t2 ORDER BY c1;
84 SELECT * FROM d3.t3 ORDER BY c1;
85 DROP DATABASE d1;
86 DROP DATABASE d2;
87 DROP DATABASE d3;
88 CREATE DATABASE d1;
89 CREATE DATABASE d2;
90 CREATE DATABASE d3;
91 CREATE TABLE d1.t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
92 CREATE TABLE d2.t2 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
93 CREATE TABLE d3.t3 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
94 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
95 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
96 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
97 DELETE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
98 SELECT * FROM d1.t1 ORDER BY c1;
99 SELECT * FROM d2.t2 ORDER BY c1;
100 SELECT * FROM d3.t3 ORDER BY c1;
101 DELETE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
102 SELECT * FROM d1.t1 ORDER BY c1;
103 SELECT * FROM d2.t2 ORDER BY c1;
104 SELECT * FROM d3.t3 ORDER BY c1;
105 DROP DATABASE d1;
106 DROP DATABASE d2;
107 DROP DATABASE d3;
108 CREATE DATABASE d1;
109 CREATE DATABASE d2;
110 CREATE DATABASE d3;
111 CREATE TABLE d1.t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
112 CREATE TABLE d2.t2 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
113 CREATE TABLE d3.t3 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
114 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
115 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
116 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
117 DELETE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
118 SELECT * FROM d1.t1 ORDER BY c1;
119 SELECT * FROM d2.t2 ORDER BY c1;
120 SELECT * FROM d3.t3 ORDER BY c1;
121 DELETE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
122 SELECT * FROM d1.t1 ORDER BY c1;
123 SELECT * FROM d2.t2 ORDER BY c1;
124 SELECT * FROM d3.t3 ORDER BY c1;
125 DROP DATABASE d1;
126 DROP DATABASE d2;
127 DROP DATABASE d3;
128 CREATE DATABASE d1;
129 CREATE DATABASE d2;
130 CREATE DATABASE d3;
131 CREATE TABLE d1.t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
132 CREATE TABLE d2.t2 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
133 CREATE TABLE d3.t3 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
134 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
135 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
136 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
137 DELETE QUICK d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
138 SELECT * FROM d1.t1 ORDER BY c1;
139 SELECT * FROM d2.t2 ORDER BY c1;
140 SELECT * FROM d3.t3 ORDER BY c1;
141 DELETE QUICK d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
142 SELECT * FROM d1.t1 ORDER BY c1;
143 SELECT * FROM d2.t2 ORDER BY c1;
144 SELECT * FROM d3.t3 ORDER BY c1;
145 DROP DATABASE d1;
146 DROP DATABASE d2;
147 DROP DATABASE d3;
148 CREATE DATABASE d1;
149 CREATE DATABASE d2;
150 CREATE DATABASE d3;
151 CREATE TABLE d1.t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
152 CREATE TABLE d2.t2 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
153 CREATE TABLE d3.t3 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
154 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
155 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
156 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
157 DELETE QUICK d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
158 SELECT * FROM d1.t1 ORDER BY c1;
159 SELECT * FROM d2.t2 ORDER BY c1;
160 SELECT * FROM d3.t3 ORDER BY c1;
161 DELETE QUICK d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
162 SELECT * FROM d1.t1 ORDER BY c1;
163 SELECT * FROM d2.t2 ORDER BY c1;
164 SELECT * FROM d3.t3 ORDER BY c1;
165 DROP DATABASE d1;
166 DROP DATABASE d2;
167 DROP DATABASE d3;
168 CREATE DATABASE d1;
169 CREATE DATABASE d2;
170 CREATE DATABASE d3;
171 CREATE TABLE d1.t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
172 CREATE TABLE d2.t2 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
173 CREATE TABLE d3.t3 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
174 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
175 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
176 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
177 DELETE QUICK d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
178 SELECT * FROM d1.t1 ORDER BY c1;
179 SELECT * FROM d2.t2 ORDER BY c1;
180 SELECT * FROM d3.t3 ORDER BY c1;
181 DELETE QUICK d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
182 SELECT * FROM d1.t1 ORDER BY c1;
183 SELECT * FROM d2.t2 ORDER BY c1;
184 SELECT * FROM d3.t3 ORDER BY c1;
185 DROP DATABASE d1;
186 DROP DATABASE d2;
187 DROP DATABASE d3;
188 CREATE DATABASE d1;
189 CREATE DATABASE d2;
190 CREATE DATABASE d3;
191 CREATE TABLE d1.t1 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
192 CREATE TABLE d2.t2 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
193 CREATE TABLE d3.t3 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
194 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
195 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
196 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
197 DELETE QUICK d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
198 SELECT * FROM d1.t1 ORDER BY c1;
199 SELECT * FROM d2.t2 ORDER BY c1;
200 SELECT * FROM d3.t3 ORDER BY c1;
201 DELETE QUICK d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
202 SELECT * FROM d1.t1 ORDER BY c1;
203 SELECT * FROM d2.t2 ORDER BY c1;
204 SELECT * FROM d3.t3 ORDER BY c1;
205 DROP DATABASE d1;
206 DROP DATABASE d2;
207 DROP DATABASE d3;
208 CREATE DATABASE d1;
209 CREATE DATABASE d2;
210 CREATE DATABASE d3;
211 CREATE TABLE d1.t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
212 CREATE TABLE d2.t2 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
213 CREATE TABLE d3.t3 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
214 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
215 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
216 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
217 DELETE QUICK d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
218 SELECT * FROM d1.t1 ORDER BY c1;
219 SELECT * FROM d2.t2 ORDER BY c1;
220 SELECT * FROM d3.t3 ORDER BY c1;
221 DELETE QUICK d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
222 SELECT * FROM d1.t1 ORDER BY c1;
223 SELECT * FROM d2.t2 ORDER BY c1;
224 SELECT * FROM d3.t3 ORDER BY c1;
225 DROP DATABASE d1;
226 DROP DATABASE d2;
227 DROP DATABASE d3;
228 CREATE DATABASE d1;
229 CREATE DATABASE d2;
230 CREATE DATABASE d3;
231 CREATE TABLE d1.t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
232 CREATE TABLE d2.t2 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
233 CREATE TABLE d3.t3 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
234 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
235 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
236 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
237 DELETE QUICK d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
238 SELECT * FROM d1.t1 ORDER BY c1;
239 SELECT * FROM d2.t2 ORDER BY c1;
240 SELECT * FROM d3.t3 ORDER BY c1;
241 DELETE QUICK d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
242 SELECT * FROM d1.t1 ORDER BY c1;
243 SELECT * FROM d2.t2 ORDER BY c1;
244 SELECT * FROM d3.t3 ORDER BY c1;
245 DROP DATABASE d1;
246 DROP DATABASE d2;
247 DROP DATABASE d3;
248 CREATE DATABASE d1;
249 CREATE DATABASE d2;
250 CREATE DATABASE d3;
251 CREATE TABLE d1.t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
252 CREATE TABLE d2.t2 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
253 CREATE TABLE d3.t3 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
254 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
255 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
256 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
257 DELETE IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
258 SELECT * FROM d1.t1 ORDER BY c1;
259 SELECT * FROM d2.t2 ORDER BY c1;
260 SELECT * FROM d3.t3 ORDER BY c1;
261 DELETE IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
262 SELECT * FROM d1.t1 ORDER BY c1;
263 SELECT * FROM d2.t2 ORDER BY c1;
264 SELECT * FROM d3.t3 ORDER BY c1;
265 DROP DATABASE d1;
266 DROP DATABASE d2;
267 DROP DATABASE d3;
268 CREATE DATABASE d1;
269 CREATE DATABASE d2;
270 CREATE DATABASE d3;
271 CREATE TABLE d1.t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
272 CREATE TABLE d2.t2 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
273 CREATE TABLE d3.t3 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
274 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
275 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
276 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
277 DELETE IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
278 SELECT * FROM d1.t1 ORDER BY c1;
279 SELECT * FROM d2.t2 ORDER BY c1;
280 SELECT * FROM d3.t3 ORDER BY c1;
281 DELETE IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
282 SELECT * FROM d1.t1 ORDER BY c1;
283 SELECT * FROM d2.t2 ORDER BY c1;
284 SELECT * FROM d3.t3 ORDER BY c1;
285 DROP DATABASE d1;
286 DROP DATABASE d2;
287 DROP DATABASE d3;
288 CREATE DATABASE d1;
289 CREATE DATABASE d2;
290 CREATE DATABASE d3;
291 CREATE TABLE d1.t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
292 CREATE TABLE d2.t2 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
293 CREATE TABLE d3.t3 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
294 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
295 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
296 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
297 DELETE IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
298 SELECT * FROM d1.t1 ORDER BY c1;
299 SELECT * FROM d2.t2 ORDER BY c1;
300 SELECT * FROM d3.t3 ORDER BY c1;
301 DELETE IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
302 SELECT * FROM d1.t1 ORDER BY c1;
303 SELECT * FROM d2.t2 ORDER BY c1;
304 SELECT * FROM d3.t3 ORDER BY c1;
305 DROP DATABASE d1;
306 DROP DATABASE d2;
307 DROP DATABASE d3;
308 CREATE DATABASE d1;
309 CREATE DATABASE d2;
310 CREATE DATABASE d3;
311 CREATE TABLE d1.t1 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
312 CREATE TABLE d2.t2 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
313 CREATE TABLE d3.t3 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
314 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
315 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
316 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
317 DELETE IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
318 SELECT * FROM d1.t1 ORDER BY c1;
319 SELECT * FROM d2.t2 ORDER BY c1;
320 SELECT * FROM d3.t3 ORDER BY c1;
321 DELETE IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
322 SELECT * FROM d1.t1 ORDER BY c1;
323 SELECT * FROM d2.t2 ORDER BY c1;
324 SELECT * FROM d3.t3 ORDER BY c1;
325 DROP DATABASE d1;
326 DROP DATABASE d2;
327 DROP DATABASE d3;
328 CREATE DATABASE d1;
329 CREATE DATABASE d2;
330 CREATE DATABASE d3;
331 CREATE TABLE d1.t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
332 CREATE TABLE d2.t2 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
333 CREATE TABLE d3.t3 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
334 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
335 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
336 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
337 DELETE IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
338 SELECT * FROM d1.t1 ORDER BY c1;
339 SELECT * FROM d2.t2 ORDER BY c1;
340 SELECT * FROM d3.t3 ORDER BY c1;
341 DELETE IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
342 SELECT * FROM d1.t1 ORDER BY c1;
343 SELECT * FROM d2.t2 ORDER BY c1;
344 SELECT * FROM d3.t3 ORDER BY c1;
345 DROP DATABASE d1;
346 DROP DATABASE d2;
347 DROP DATABASE d3;
348 CREATE DATABASE d1;
349 CREATE DATABASE d2;
350 CREATE DATABASE d3;
351 CREATE TABLE d1.t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
352 CREATE TABLE d2.t2 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
353 CREATE TABLE d3.t3 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
354 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
355 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
356 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
357 DELETE IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
358 SELECT * FROM d1.t1 ORDER BY c1;
359 SELECT * FROM d2.t2 ORDER BY c1;
360 SELECT * FROM d3.t3 ORDER BY c1;
361 DELETE IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
362 SELECT * FROM d1.t1 ORDER BY c1;
363 SELECT * FROM d2.t2 ORDER BY c1;
364 SELECT * FROM d3.t3 ORDER BY c1;
365 DROP DATABASE d1;
366 DROP DATABASE d2;
367 DROP DATABASE d3;
368 CREATE DATABASE d1;
369 CREATE DATABASE d2;
370 CREATE DATABASE d3;
371 CREATE TABLE d1.t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
372 CREATE TABLE d2.t2 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
373 CREATE TABLE d3.t3 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
374 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
375 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
376 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
377 DELETE QUICK IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
378 SELECT * FROM d1.t1 ORDER BY c1;
379 SELECT * FROM d2.t2 ORDER BY c1;
380 SELECT * FROM d3.t3 ORDER BY c1;
381 DELETE QUICK IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
382 SELECT * FROM d1.t1 ORDER BY c1;
383 SELECT * FROM d2.t2 ORDER BY c1;
384 SELECT * FROM d3.t3 ORDER BY c1;
385 DROP DATABASE d1;
386 DROP DATABASE d2;
387 DROP DATABASE d3;
388 CREATE DATABASE d1;
389 CREATE DATABASE d2;
390 CREATE DATABASE d3;
391 CREATE TABLE d1.t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
392 CREATE TABLE d2.t2 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
393 CREATE TABLE d3.t3 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
394 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
395 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
396 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
397 DELETE QUICK IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
398 SELECT * FROM d1.t1 ORDER BY c1;
399 SELECT * FROM d2.t2 ORDER BY c1;
400 SELECT * FROM d3.t3 ORDER BY c1;
401 DELETE QUICK IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
402 SELECT * FROM d1.t1 ORDER BY c1;
403 SELECT * FROM d2.t2 ORDER BY c1;
404 SELECT * FROM d3.t3 ORDER BY c1;
405 DROP DATABASE d1;
406 DROP DATABASE d2;
407 DROP DATABASE d3;
408 CREATE DATABASE d1;
409 CREATE DATABASE d2;
410 CREATE DATABASE d3;
411 CREATE TABLE d1.t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
412 CREATE TABLE d2.t2 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
413 CREATE TABLE d3.t3 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
414 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
415 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
416 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
417 DELETE QUICK IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
418 SELECT * FROM d1.t1 ORDER BY c1;
419 SELECT * FROM d2.t2 ORDER BY c1;
420 SELECT * FROM d3.t3 ORDER BY c1;
421 DELETE QUICK IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
422 SELECT * FROM d1.t1 ORDER BY c1;
423 SELECT * FROM d2.t2 ORDER BY c1;
424 SELECT * FROM d3.t3 ORDER BY c1;
425 DROP DATABASE d1;
426 DROP DATABASE d2;
427 DROP DATABASE d3;
428 CREATE DATABASE d1;
429 CREATE DATABASE d2;
430 CREATE DATABASE d3;
431 CREATE TABLE d1.t1 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
432 CREATE TABLE d2.t2 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
433 CREATE TABLE d3.t3 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
434 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
435 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
436 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
437 DELETE QUICK IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
438 SELECT * FROM d1.t1 ORDER BY c1;
439 SELECT * FROM d2.t2 ORDER BY c1;
440 SELECT * FROM d3.t3 ORDER BY c1;
441 DELETE QUICK IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
442 SELECT * FROM d1.t1 ORDER BY c1;
443 SELECT * FROM d2.t2 ORDER BY c1;
444 SELECT * FROM d3.t3 ORDER BY c1;
445 DROP DATABASE d1;
446 DROP DATABASE d2;
447 DROP DATABASE d3;
448 CREATE DATABASE d1;
449 CREATE DATABASE d2;
450 CREATE DATABASE d3;
451 CREATE TABLE d1.t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
452 CREATE TABLE d2.t2 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
453 CREATE TABLE d3.t3 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
454 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
455 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
456 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
457 DELETE QUICK IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
458 SELECT * FROM d1.t1 ORDER BY c1;
459 SELECT * FROM d2.t2 ORDER BY c1;
460 SELECT * FROM d3.t3 ORDER BY c1;
461 DELETE QUICK IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
462 SELECT * FROM d1.t1 ORDER BY c1;
463 SELECT * FROM d2.t2 ORDER BY c1;
464 SELECT * FROM d3.t3 ORDER BY c1;
465 DROP DATABASE d1;
466 DROP DATABASE d2;
467 DROP DATABASE d3;
468 CREATE DATABASE d1;
469 CREATE DATABASE d2;
470 CREATE DATABASE d3;
471 CREATE TABLE d1.t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
472 CREATE TABLE d2.t2 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
473 CREATE TABLE d3.t3 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
474 INSERT INTO d1.t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
475 INSERT INTO d2.t2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
476 INSERT INTO d3.t3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
477 DELETE QUICK IGNORE d1.t1, d2.t2 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
478 SELECT * FROM d1.t1 ORDER BY c1;
479 SELECT * FROM d2.t2 ORDER BY c1;
480 SELECT * FROM d3.t3 ORDER BY c1;
481 DELETE QUICK IGNORE d1.t1.*, d2.t2.*, d3.t3 FROM d1.t1, d2.t2, d3.t3 WHERE d1.t1.c1=d2.t2.c2 AND d2.t2.c1=d3.t3.c2;
482 SELECT * FROM d1.t1 ORDER BY c1;
483 SELECT * FROM d2.t2 ORDER BY c1;
484 SELECT * FROM d3.t3 ORDER BY c1;
485 DROP DATABASE d1;
486 DROP DATABASE d2;
487 DROP DATABASE d3;
488 set optimizer_switch=@bug41996_tmp;