MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
explain_non_select.inc
1 # This file is a collection of regression and coverage tests
2 # for WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE.
3 
4 -- disable_query_log
5 -- disable_result_log
6 SET GLOBAL innodb_stats_persistent=0;
7 -- enable_result_log
8 -- enable_query_log
9 
10 set end_markers_in_json=on;
11 
12 --echo #1
13 CREATE TABLE t1 (a INT);
14 INSERT INTO t1 VALUES (1), (2), (3);
15 --let $query = UPDATE t1 SET a = 10 WHERE a < 10
16 --let $select = SELECT * FROM t1 WHERE a < 10
17 --source include/explain_utils.inc
18 DROP TABLE t1;
19 
20 --echo #2
21 CREATE TABLE t1 (a INT);
22 INSERT INTO t1 VALUES (1), (2), (3);
23 --let $query = DELETE FROM t1 WHERE a < 10
24 --let $select = SELECT * FROM t1 WHERE a < 10
25 --source include/explain_utils.inc
26 DROP TABLE t1;
27 
28 --echo #3
29 CREATE TABLE t1 (a INT);
30 INSERT INTO t1 VALUES (1), (2), (3);
31 --let $query = DELETE FROM t1 USING t1 WHERE a = 1
32 --let $select = SELECT * FROM t1 WHERE a = 1
33 --source include/explain_utils.inc
34 DROP TABLE t1;
35 
36 --echo #4
37 CREATE TABLE t1 (a INT);
38 INSERT INTO t1 VALUES (1), (2), (3);
39 CREATE TABLE t2 (b INT);
40 INSERT INTO t2 VALUES (1), (2), (3);
41 --let $query = UPDATE t1, t2 SET t1.a = 10 WHERE t1.a = 1
42 --let $select = SELECT * FROM t1, t2 WHERE t1.a = 1
43 --source include/explain_utils.inc
44 DROP TABLE t1, t2;
45 
46 --echo #5
47 CREATE TABLE t1 (a INT);
48 INSERT INTO t1 VALUES (1), (2), (3);
49 CREATE TABLE t2 (b INT);
50 INSERT INTO t2 VALUES (1), (2), (3);
51 --let $query = UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1
52 --let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a = 1
53 --source include/explain_utils.inc
54 DROP TABLE t1, t2;
55 
56 --echo #6
57 CREATE TABLE t1 (a INT);
58 INSERT INTO t1 VALUES (1), (2), (3);
59 CREATE TABLE t2 (b INT);
60 INSERT INTO t2 VALUES (1), (2), (3);
61 --let $query = UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3)
62 --let $select = SELECT * FROM t1 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3)
63 --source include/explain_utils.inc
64 DROP TABLE t1, t2;
65 
66 --echo #7
67 CREATE TABLE t1 (a INT);
68 INSERT INTO t1 VALUES (1), (2), (3);
69 CREATE TABLE t2 (b INT);
70 INSERT INTO t2 VALUES (1), (2), (3);
71 --let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3)
72 --let $select = SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3)
73 --source include/explain_utils.inc
74 DROP TABLE t1, t2;
75 
76 --echo #7
77 CREATE TABLE t1 (a INT);
78 INSERT INTO t1 VALUES (1), (2), (3);
79 CREATE TABLE t2 (b INT);
80 INSERT INTO t2 VALUES (1), (2), (3);
81 --let $query = UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
82 --let $select = SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
83 --source include/explain_utils.inc
84 DROP TABLE t1, t2;
85 
86 --echo #8
87 CREATE TABLE t1 (a INT);
88 INSERT INTO t1 VALUES (1), (2), (3);
89 CREATE TABLE t2 (b INT);
90 INSERT INTO t2 VALUES (1), (2), (3);
91 --let $query = UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10
92 --let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12
93 --source include/explain_utils.inc
94 DROP TABLE t1, t2;
95 
96 --echo #9
97 CREATE TABLE t1 (a INT);
98 INSERT INTO t1 VALUES (1), (2), (3);
99 CREATE TABLE t2 (b INT);
100 INSERT INTO t2 VALUES (1), (2), (3);
101 --let $query = UPDATE t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10
102 --let $select = SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12
103 --source include/explain_utils.inc
104 DROP TABLE t1, t2;
105 
106 --echo #10
107 CREATE TABLE t1 (a INT);
108 INSERT INTO t1 VALUES (1), (2), (3);
109 CREATE TABLE t2 (b INT);
110 INSERT INTO t2 VALUES (1), (2), (3);
111 --let $query = UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1
112 --let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a > 1
113 --source include/explain_utils.inc
114 DROP TABLE t1, t2;
115 
116 --echo #11
117 CREATE TABLE t1 (a INT);
118 INSERT INTO t1 VALUES (1), (2), (3);
119 --let $query = DELETE FROM t1 WHERE a > 1 LIMIT 1
120 --let $select = SELECT * FROM t1 WHERE a > 1 LIMIT 1
121 --source include/explain_utils.inc
122 DROP TABLE t1;
123 
124 --echo #12
125 CREATE TABLE t1 (a INT);
126 INSERT INTO t1 VALUES (1), (2), (3);
127 --let $query = DELETE FROM t1 WHERE 0
128 --let $select = SELECT * FROM t1 WHERE 0
129 --source include/explain_utils.inc
130 DROP TABLE t1;
131 
132 --echo #13
133 CREATE TABLE t1 (a INT);
134 INSERT INTO t1 VALUES (1), (2), (3);
135 --let $query = DELETE FROM t1 USING t1 WHERE 0
136 --let $select = SELECT * FROM t1 WHERE 0
137 --source include/explain_utils.inc
138 DROP TABLE t1;
139 
140 --echo #14
141 CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b));
142 INSERT INTO t1 VALUES (3, 3), (7, 7);
143 --let $query = DELETE FROM t1 WHERE a = 3
144 --let $select = SELECT * FROM t1 WHERE a = 3
145 --source include/explain_utils.inc
146 DROP TABLE t1;
147 
148 --echo #15
149 CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b));
150 INSERT INTO t1 VALUES (3, 3), (7, 7);
151 --let $query = DELETE FROM t1 WHERE a < 3
152 --let $select = SELECT * FROM t1 WHERE a < 3
153 --source include/explain_utils.inc
154 DROP TABLE t1;
155 
156 --echo #16
157 CREATE TABLE t1 ( a int PRIMARY KEY );
158 --let $query = DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a
159 --let $select = SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a
160 --source include/explain_utils.inc
161 INSERT INTO t1 VALUES (1), (2), (3);
162 --let $query = DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a
163 --let $select = SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a
164 --source include/explain_utils.inc
165 DROP TABLE t1;
166 
167 --echo #17
168 CREATE TABLE t1(a INT PRIMARY KEY);
169 INSERT INTO t1 VALUES (4),(3),(1),(2);
170 --let $query = DELETE FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1
171 --let $select = SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1
172 --source include/explain_utils.inc
173 DROP TABLE t1;
174 
175 --echo #18
176 CREATE TABLE t1 (a DATE, b TIME, c INT, KEY c(c), KEY b(b), KEY a(a));
177 INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), ();
178 UPDATE t1 SET a = c, b = c;
179 --let $query = DELETE FROM t1 ORDER BY a ASC, b ASC LIMIT 1
180 --let $select = SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1
181 --source include/explain_utils.inc
182 DROP TABLE t1;
183 
184 --echo #19
185 CREATE TABLE t1 (a1 INT NOT NULL, b1 INT NOT NULL);
186 CREATE TABLE t2 (a2 INT NOT NULL, b2 INT NOT NULL, PRIMARY KEY (a2,b2));
187 CREATE TABLE t3 (a3 INT NOT NULL, b3 INT NOT NULL, PRIMARY KEY (a3,b3));
188 INSERT INTO t1 VALUES (1,1), (2,1), (1,3);
189 INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
190 INSERT INTO t3 VALUES (1,1), (2,1), (1,3);
191 --let $query = DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3
192 --let $select = SELECT * FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3
193 --source include/explain_utils.inc
194 DROP TABLE t1, t2, t3;
195 
196 --echo #20
197 CREATE TABLE t1 (a INT);
198 INSERT INTO t1 VALUES (1), (2), (3);
199 CREATE TABLE t2 (a INT);
200 INSERT INTO t2 VALUES (1), (2), (3);
201 --let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2)
202 --let $select = SELECT * FROM t1 WHERE a IN (SELECT a FROM t2)
203 --source include/explain_utils.inc
204 DROP TABLE t1, t2;
205 
206 --echo #21
207 CREATE TABLE t1 (a1 INT);
208 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
209 CREATE TABLE t2 (a2 VARCHAR(10));
210 INSERT INTO t2 VALUES (1), (2), (3), (4), (5);
211 SET @save_optimizer_switch= @@optimizer_switch;
212 --disable_query_log
213 if (`select locate('semijoin', @@optimizer_switch) > 0`)
214 {
215  SET @@optimizer_switch= 'semijoin=off';
216 }
217 --enable_query_log
218 --let $query = DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
219 --let $select = SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
220 --source include/explain_utils.inc
221 SET @@optimizer_switch= @save_optimizer_switch;
222 TRUNCATE t1;
223 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
224 --let $query = DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
225 --let $select = SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
226 --source include/explain_utils.inc
227 DROP TABLE t1, t2;
228 
229 --echo #22
230 CREATE TABLE t1 (i INT, j INT);
231 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
232 --let $query = UPDATE t1 SET i = 10
233 --let $select = SELECT * FROM t1
234 --source include/explain_utils.inc
235 DROP TABLE t1;
236 
237 --echo #23
238 CREATE TABLE t1 (i INT, j INT);
239 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
240 --let $query = DELETE FROM t1
241 --let $select = SELECT * FROM t1
242 --source include/explain_utils.inc
243 DROP TABLE t1;
244 
245 --echo #24
246 CREATE TABLE t1 (i INT);
247 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
248  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
249  (30),(31),(32),(33),(34),(35);
250 CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
251 INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
252 INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
253 --let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
254 --let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
255 --let $no_rows = 1
256 --source include/explain_utils.inc
257 DROP TABLE t1, t2;
258 
259 --echo #25
260 CREATE TABLE t1 (i INT);
261 INSERT INTO t1 VALUES (1), (2), (3);
262 CREATE TABLE t2 (i INT);
263 --let $query = INSERT INTO t2 SELECT * FROM t1
264 --let $select = SELECT * FROM t1
265 --source include/explain_utils.inc
266 DROP TABLE t1, t2;
267 
268 --echo #26
269 CREATE TABLE t1 (i INT);
270 INSERT INTO t1 VALUES (1), (2), (3);
271 CREATE TABLE t2 (i INT);
272 --let $query = REPLACE INTO t2 SELECT * FROM t1
273 --let $select = SELECT * FROM t1
274 --source include/explain_utils.inc
275 DROP TABLE t1, t2;
276 
277 --echo #27
278 CREATE TABLE t1 (i INT);
279 --let $query = INSERT INTO t1 SET i = 10
280 --source include/explain_utils.inc
281 DROP TABLE t1;
282 
283 --echo #28
284 CREATE TABLE t1 (i INT);
285 --let $query = REPLACE INTO t1 SET i = 10
286 --source include/explain_utils.inc
287 DROP TABLE t1;
288 
289 --echo #29
290 CREATE TABLE t1 (a INT, i INT PRIMARY KEY);
291 INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
292  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
293  (30),(31),(32),(33),(34),(35);
294 --let $query = DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
295 --let $select = SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
296 --source include/explain_utils.inc
297 DROP TABLE t1;
298 
299 --echo #30
300 CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1)));
301 INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
302  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
303  (30),(31),(32),(33),(34),(35);
304 --let $query = DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
305 --let $select = SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
306 --source include/explain_utils.inc
307 DROP TABLE t1;
308 
309 --echo #31
310 CREATE TABLE t1 (i INT);
311 INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
312  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
313  (30),(31),(32),(33),(34),(35);
314 CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
315 INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
316 --let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
317 --let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
318 --source include/explain_utils.inc
319 DROP TABLE t1, t2;
320 
321 --echo #32
322 CREATE TABLE t1 (i INT);
323 INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
324  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
325  (30),(31),(32),(33),(34),(35);
326 CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
327 INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
328 INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
329 --let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
330 --let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
331 --let $no_rows = 1
332 --source include/explain_utils.inc
333 DROP TABLE t1, t2;
334 
335 --echo #33
336 CREATE TABLE t1 (i INT);
337 INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
338  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
339  (30),(31),(32),(33),(34),(35);
340 CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c));
341 INSERT INTO t2 SELECT i, i, i, i FROM t1;
342 --let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
343 --let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
344 --source include/explain_utils.inc
345 DROP TABLE t1, t2;
346 
347 --echo #34
348 CREATE TABLE t1 (i INT);
349 INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
350  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
351  (30),(31),(32),(33),(34),(35);
352 CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c))
353  ENGINE=HEAP;
354 INSERT INTO t2 SELECT i, i, i, i FROM t1;
355 --let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
356 --let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
357 --source include/explain_utils.inc
358 DROP TABLE t1, t2;
359 
360 --echo #35
361 CREATE TABLE t1 (i INT);
362 INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
363  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
364  (30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
365  (40),(41),(42);
366 CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
367 INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
368 --let $query = DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1
369 --let $select = SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1
370 --source include/explain_utils.inc
371 DROP TABLE t1, t2;
372 
373 --echo #36
374 CREATE TABLE t1 (i INT);
375 INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
376  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
377  (30),(31),(32),(33),(34),(35);
378 CREATE TABLE t2(a INT, i INT PRIMARY KEY);
379 INSERT INTO t2 (i) SELECT i FROM t1;
380 --let $query = DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
381 --let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
382 --source include/explain_utils.inc
383 DROP TABLE t1, t2;
384 
385 --echo #37
386 CREATE TABLE t1 (i INT);
387 INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
388  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
389  (30),(31),(32),(33),(34),(35);
390 CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
391 INSERT INTO t2 SELECT i, i, i FROM t1;
392 --let $query = DELETE FROM t2 ORDER BY a, b DESC LIMIT 5
393 --let $select = SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5
394 --source include/explain_utils.inc
395 DROP TABLE t1, t2;
396 
397 --echo #38
398 CREATE TABLE t1 (i INT);
399 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
400  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
401  (30),(31),(32),(33),(34),(35);
402 CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b));
403 INSERT INTO t2 (a, b) SELECT i, i FROM t1;
404 INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2;
405 --let $query = DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5
406 --let $select = SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5
407 --let $no_rows = 1
408 --source include/explain_utils.inc
409 DROP TABLE t1, t2;
410 
411 --echo #39
412 CREATE TABLE t1 (i INT);
413 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
414  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
415  (30),(31),(32),(33),(34),(35);
416 CREATE TABLE t2(a INT, i INT PRIMARY KEY);
417 INSERT INTO t2 (i) SELECT i FROM t1;
418 --let $query = UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
419 --let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
420 --let $no_rows = 1
421 --source include/explain_utils.inc
422 DROP TABLE t1, t2;
423 
424 --echo #40
425 CREATE TABLE t1 (i INT);
426 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
427  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
428  (30),(31),(32),(33),(34),(35);
429 CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1)));
430 INSERT INTO t2 (i) SELECT i FROM t1;
431 --let $query = UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
432 --let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
433 --source include/explain_utils.inc
434 DROP TABLE t1, t2;
435 
436 --echo #41
437 CREATE TABLE t1 (i INT);
438 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
439  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
440  (30),(31),(32),(33),(34),(35);
441 CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
442 INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
443 --let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
444 --let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
445 --source include/explain_utils.inc
446 DROP TABLE t1, t2;
447 
448 --echo #42
449 CREATE TABLE t1 (i INT);
450 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
451  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
452  (30),(31),(32),(33),(34),(35);
453 CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
454 INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
455 INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
456 --let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
457 --let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
458 --let $no_rows = 1
459 --source include/explain_utils.inc
460 DROP TABLE t1, t2;
461 
462 --echo #43
463 CREATE TABLE t1 (i INT);
464 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
465  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
466  (30),(31),(32),(33),(34),(35);
467 CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c));
468 INSERT INTO t2 SELECT i, i, i, i FROM t1;
469 --let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
470 --let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
471 --source include/explain_utils.inc
472 DROP TABLE t1, t2;
473 
474 --echo #44
475 CREATE TABLE t1 (i INT);
476 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
477  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
478  (30),(31),(32),(33),(34),(35);
479 CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c))
480  ENGINE=HEAP;
481 INSERT INTO t2 SELECT i, i, i, i FROM t1;
482 --let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
483 --let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
484 --source include/explain_utils.inc
485 DROP TABLE t1, t2;
486 
487 --echo #45
488 CREATE TABLE t1 (i INT);
489 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
490  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
491  (30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
492  (40),(41),(42);
493 CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
494 INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
495 --let $query = UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1
496 --let $select = SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1
497 --source include/explain_utils.inc
498 DROP TABLE t1, t2;
499 
500 --echo #46
501 CREATE TABLE t1 (i INT);
502 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
503  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
504  (30),(31),(32),(33),(34),(35);
505 CREATE TABLE t2(a INT, i INT PRIMARY KEY);
506 INSERT INTO t2 (i) SELECT i FROM t1;
507 --let $query = UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
508 --let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
509 --source include/explain_utils.inc
510 DROP TABLE t1, t2;
511 
512 --echo #47
513 CREATE TABLE t1 (i INT);
514 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
515  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
516  (30),(31),(32),(33),(34),(35);
517 CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
518 INSERT INTO t2 SELECT i, i, i FROM t1;
519 --let $query = UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5
520 --let $select = SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5
521 --source include/explain_utils.inc
522 DROP TABLE t1, t2;
523 
524 --echo #48
525 CREATE TABLE t1 (i INT);
526 INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
527  (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
528  (30),(31),(32),(33),(34),(35);
529 CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b));
530 INSERT INTO t2 (a, b) SELECT i, i FROM t1;
531 INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2;
532 --let $query = UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5
533 --let $select = SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5
534 --let $no_rows = 1
535 --source include/explain_utils.inc
536 DROP TABLE t1, t2;
537 
538 --echo #49
539 CREATE TABLE t1 (
540  pk INT NOT NULL AUTO_INCREMENT,
541  c1_idx CHAR(1) DEFAULT 'y',
542  c2 INT,
543  PRIMARY KEY (pk),
544  INDEX c1_idx (c1_idx)
545 );
546 INSERT INTO t1 VALUES (1,'y',1), (2,'n',2), (3,'y',3), (4,'n',4);
547 --let $query = UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
548 --let $select = SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
549 --source include/explain_utils.inc
550 --let $query = DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
551 --let $select = SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
552 --source include/explain_utils.inc
553 DROP TABLE t1;
554 
555 --echo #50
556 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
557 INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),();
558 --let $query = UPDATE t1 SET a=a+10 WHERE a > 34
559 --let $select = SELECT * FROM t1 WHERE a > 34
560 --source include/explain_utils.inc
561 DROP TABLE t1;
562 
563 --echo #51
564 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
565 CREATE TABLE t2 (c1 INT, c2 INT);
566 INSERT INTO t1 VALUES (1, 1, 10), (2, 2, 20);
567 --let $query = UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10
568 --let $select = SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1
569 --source include/explain_utils.inc
570 --let $query = UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10
571 --let $select = SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c3 = 10
572 --source include/explain_utils.inc
573 DROP TABLE t1, t2;
574 
575 --echo #52
576 CREATE TABLE t1(f1 INT, f2 INT);
577 CREATE TABLE t2(f3 INT, f4 INT);
578 CREATE INDEX IDX ON t2(f3);
579 INSERT INTO t1 VALUES(1,0),(2,0);
580 INSERT INTO t2 VALUES(1,1),(2,2);
581 --let $query = UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1)
582 --let $select = SELECT (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1
583 --source include/explain_utils.inc
584 DROP TABLE t1, t2;
585 
586 --echo #55
587 CREATE TABLE t1(a INT);
588 INSERT INTO t1 VALUES (1);
589 SET @a = NULL;
590 EXPLAIN DELETE FROM t1 WHERE (@a:= a);
591 if (`SELECT @a IS NOT NULL`) {
592  die Unexpectedly modified user variable;
593 }
594 DROP TABLE t1;
595 
596 --echo #56
597 CREATE TABLE t1 (a INT);
598 INSERT INTO t1 VALUES (1), (2), (3);
599 --error ER_BAD_FIELD_ERROR
600  DELETE FROM t1 USING t1 WHERE uknown_column = 12345;
601 --error ER_BAD_FIELD_ERROR
602 EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE uknown_column = 12345;
603 DROP TABLE t1;
604 
605 --echo #57
606 CREATE TABLE t1(f1 INT);
607 --error ER_BAD_FIELD_ERROR
608 EXPLAIN EXTENDED UPDATE t1 SET f2=1 ORDER BY f2;
609 --error ER_BAD_FIELD_ERROR
610 UPDATE t1 SET f2=1 ORDER BY f2;
611 DROP TABLE t1;
612 
613 --echo #59
614 CREATE TABLE t1 ( a INT, KEY( a ) );
615 INSERT INTO t1 VALUES (0), (1);
616 CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
617 SET SESSION sql_safe_updates = 1;
618 --error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
619 EXPLAIN EXTENDED UPDATE IGNORE v1 SET a = 1;
620 --error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
621 UPDATE IGNORE v1 SET a = 1;
622 SET SESSION sql_safe_updates = DEFAULT;
623 DROP TABLE t1;
624 DROP VIEW v1;
625 
626 --echo #62
627 CREATE TABLE t1 (a INT);
628 INSERT INTO t1 VALUES (0), (1);
629 CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
630 --let $query = UPDATE v1 SET a = 1 WHERE a > 0
631 --let $select = SELECT * FROM v1 WHERE a > 0
632 --source include/explain_utils.inc
633 --let $query = UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a
634 --let $select = SELECT * FROM t1, v1 WHERE t1.a = v1.a
635 --source include/explain_utils.inc
636 DROP TABLE t1;
637 DROP VIEW v1;
638 
639 --echo #63
640 CREATE TABLE t1 (a INT, PRIMARY KEY(a));
641 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
642 CREATE VIEW v1 (a) AS SELECT a FROM t1;
643 --let $query = DELETE FROM v1 WHERE a < 4
644 --let $select = SELECT * FROM v1 WHERE a < 4
645 --source include/explain_utils.inc
646 DROP TABLE t1;
647 DROP VIEW v1;
648 
649 --echo #64
650 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
651 INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10);
652 CREATE TABLE t2 (x INT);
653 INSERT INTO t2 VALUES (1), (2), (3), (4);
654 CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1;
655 --let $query = DELETE v1 FROM t2, v1 WHERE t2.x = v1.a
656 --let $select = SELECT * FROM t2, v1 WHERE t2.x = v1.a
657 --source include/explain_utils.inc
658 DROP TABLE t1,t2;
659 DROP VIEW v1;
660 
661 --echo #65
662 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
663 INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10);
664 CREATE TABLE t2 (x INT);
665 INSERT INTO t2 VALUES (1), (2), (3), (4);
666 CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1;
667 --let $query = DELETE v1 FROM t2, v1 WHERE t2.x = v1.a
668 --let $select = SELECT * FROM t2, v1 WHERE t2.x = v1.a
669 --source include/explain_utils.inc
670 DROP TABLE t1,t2;
671 DROP VIEW v1;
672 
673 --echo #66
674 CREATE TABLE t1 (a INT);
675 CREATE VIEW v1 (x) AS SELECT a FROM t1;
676 --let $query = INSERT INTO v1 VALUES (10)
677 --let $select = SELECT NULL
678 --source include/explain_utils.inc
679 DROP TABLE t1;
680 DROP VIEW v1;
681 
682 --echo #67
683 CREATE TABLE t1 (a INT);
684 CREATE TABLE t2 (b INT);
685 INSERT INTO t2 VALUES (1), (2), (3);
686 CREATE VIEW v1 (x) AS SELECT b FROM t2;
687 --let $query = INSERT INTO v1 SELECT * FROM t1
688 --let $select = SELECT * FROM t1
689 --source include/explain_utils.inc
690 DROP TABLE t1, t2;
691 DROP VIEW v1;
692 
693 --echo #68
694 CREATE TABLE t1 (i INT);
695 EXPLAIN INSERT DELAYED INTO t1 VALUES (1);
696 DROP TABLE t1;
697 
698 --echo #69
699 CREATE TABLE t1 (a INT);
700 INSERT INTO t1 VALUES (1), (2), (3);
701 CREATE TABLE t2 (b INT);
702 INSERT INTO t2 VALUES (1), (2), (3);
703 --let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
704 --let $select = SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
705 --source include/explain_utils.inc
706 --let $query = UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
707 --let $select = SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
708 --source include/explain_utils.inc
709 --let $query = UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
710 --let $select = SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
711 --source include/explain_utils.inc
712 DROP TABLE t1,t2;
713 
714 --echo #70
715 CREATE TABLE t1 (c1 INT KEY);
716 CREATE TABLE t2 (c2 INT);
717 CREATE TABLE t3 (c3 INT);
718 EXPLAIN EXTENDED UPDATE t3 SET c3 = (
719  SELECT COUNT(d1.c1)
720  FROM (
721  SELECT a11.c1 FROM t1 AS a11
722  STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1
723  JOIN t1 AS a12 ON a12.c1 = a11.c1
724  ) d1
725 );
726 
727 DROP TABLE t1, t2, t3;
728 
729 --echo #71
730 #
731 # Bug: after EXPLAIN bulk INSERT...SELECT and bulk INSERT...SELECT
732 # to a # MyISAM table the SELECT query may fail with the
733 # "1030: Got error 124 from storage engine" error message.
734 #
735 CREATE TABLE t1 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX i1(c1));
736 INSERT INTO t1 VALUES (1,0),(2,0),(3,0),(4,0),(5,0),(6,0),(7,0),(8,0);
737 --disable_query_log
738 let $1=7;
739 SET @d=8;
740 while ($1) {
741  eval INSERT INTO t1 SELECT c1 + @d, c2 + @d FROM t1;
742  eval SET @d = @d*2;
743  dec $1;
744 }
745 --enable_query_log
746 CREATE TABLE t2 LIKE t1;
747 
748 # replace "rows" column for InnoDB
749 --replace_column 9 X
750 EXPLAIN INSERT INTO t2 SELECT * FROM t1;
751 INSERT INTO t2 SELECT * FROM t1;
752 --disable_result_log
753 SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
754 --enable_result_log
755 
756 DROP TABLE t1, t2;
757 
758 --echo #73
759 
760 CREATE TABLE t1 (id INT);
761 CREATE TABLE t2 (id INT);
762 INSERT INTO t1 VALUES (1), (2);
763 
764 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id;
765 
766 DROP TABLE t1,t2;
767 
768 --echo #74
769 
770 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
771 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
772 
773 --echo # used key is modified & Using temporary
774 
775 --let $query = UPDATE t1 SET a=a+1 WHERE a>10
776 --let $select = SELECT a t1 FROM t1 WHERE a>10
777 --source include/explain_utils.inc
778 
779 --echo # used key is modified & Using filesort
780 
781 --let $query = UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20
782 --let $select = SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20
783 --source include/explain_utils.inc
784 
785 DROP TABLE t1;
786 
787 --echo #
788 --echo # Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH
789 --echo # EXPLAIN UPDATE/DEL/INS
790 --echo #
791 
792 CREATE TABLE t1 (i INT);
793 CREATE TABLE t2 (i INT);
794 
795 --delimiter |
796 CREATE PROCEDURE p1() BEGIN EXPLAIN INSERT INTO t1 VALUES (1);END|
797 CREATE PROCEDURE p2() BEGIN INSERT INTO t1 VALUES (1);END|
798 CREATE PROCEDURE p3() BEGIN EXPLAIN INSERT INTO t1 SELECT 1;END|
799 CREATE PROCEDURE p4() BEGIN INSERT INTO t1 SELECT 1;END|
800 CREATE PROCEDURE p5() BEGIN EXPLAIN REPLACE INTO t1 VALUES (1);END|
801 CREATE PROCEDURE p6() BEGIN REPLACE INTO t1 VALUES (1);END|
802 CREATE PROCEDURE p7() BEGIN EXPLAIN REPLACE INTO t1 SELECT 1;END|
803 CREATE PROCEDURE p8() BEGIN REPLACE INTO t1 SELECT 1;END|
804 CREATE PROCEDURE p9() BEGIN EXPLAIN UPDATE t1 SET i = 10;END|
805 CREATE PROCEDURE p10() BEGIN UPDATE t1 SET i = 10;END|
806 CREATE PROCEDURE p11() BEGIN EXPLAIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END|
807 CREATE PROCEDURE p12() BEGIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END|
808 CREATE PROCEDURE p13() BEGIN EXPLAIN DELETE FROM t1;END|
809 CREATE PROCEDURE p14() BEGIN DELETE FROM t1;END|
810 CREATE PROCEDURE p15() BEGIN EXPLAIN DELETE FROM t1 USING t1;END|
811 CREATE PROCEDURE p16() BEGIN DELETE FROM t1 USING t1;END|
812 --delimiter ;
813 
814 let $i=16;
815 while($i) {
816  eval CALL p$i();
817  eval DROP PROCEDURE p$i;
818  dec $i;
819 }
820 
821 DROP TABLE t1, t2;
822 
823 --echo #
824 
825 -- disable_query_log
826 -- disable_result_log
827 SET GLOBAL innodb_stats_persistent=default;
828 -- enable_result_log
829 -- enable_query_log