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
13CREATE TABLE t1 (a INT);
14INSERT 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
18DROP TABLE t1;
19
20--echo #2
21CREATE TABLE t1 (a INT);
22INSERT 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
26DROP TABLE t1;
27
28--echo #3
29CREATE TABLE t1 (a INT);
30INSERT 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
34DROP TABLE t1;
35
36--echo #4
37CREATE TABLE t1 (a INT);
38INSERT INTO t1 VALUES (1), (2), (3);
39CREATE TABLE t2 (b INT);
40INSERT 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
44DROP TABLE t1, t2;
45
46--echo #5
47CREATE TABLE t1 (a INT);
48INSERT INTO t1 VALUES (1), (2), (3);
49CREATE TABLE t2 (b INT);
50INSERT 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
54DROP TABLE t1, t2;
55
56--echo #6
57CREATE TABLE t1 (a INT);
58INSERT INTO t1 VALUES (1), (2), (3);
59CREATE TABLE t2 (b INT);
60INSERT 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
64DROP TABLE t1, t2;
65
66--echo #7
67CREATE TABLE t1 (a INT);
68INSERT INTO t1 VALUES (1), (2), (3);
69CREATE TABLE t2 (b INT);
70INSERT 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
74DROP TABLE t1, t2;
75
76--echo #7
77CREATE TABLE t1 (a INT);
78INSERT INTO t1 VALUES (1), (2), (3);
79CREATE TABLE t2 (b INT);
80INSERT 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
84DROP TABLE t1, t2;
85
86--echo #8
87CREATE TABLE t1 (a INT);
88INSERT INTO t1 VALUES (1), (2), (3);
89CREATE TABLE t2 (b INT);
90INSERT 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
94DROP TABLE t1, t2;
95
96--echo #9
97CREATE TABLE t1 (a INT);
98INSERT INTO t1 VALUES (1), (2), (3);
99CREATE TABLE t2 (b INT);
100INSERT 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
104DROP TABLE t1, t2;
105
106--echo #10
107CREATE TABLE t1 (a INT);
108INSERT INTO t1 VALUES (1), (2), (3);
109CREATE TABLE t2 (b INT);
110INSERT 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
114DROP TABLE t1, t2;
115
116--echo #11
117CREATE TABLE t1 (a INT);
118INSERT 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
122DROP TABLE t1;
123
124--echo #12
125CREATE TABLE t1 (a INT);
126INSERT 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
130DROP TABLE t1;
131
132--echo #13
133CREATE TABLE t1 (a INT);
134INSERT 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
138DROP TABLE t1;
139
140--echo #14
141CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b));
142INSERT 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
146DROP TABLE t1;
147
148--echo #15
149CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b));
150INSERT 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
154DROP TABLE t1;
155
156--echo #16
157CREATE 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
161INSERT INTO t1 VALUES (1), (2), (3), (-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
165DROP TABLE t1;
166
167--echo #17
168CREATE TABLE t1(a INT PRIMARY KEY);
169INSERT 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
173DROP TABLE t1;
174
175--echo #18
176CREATE TABLE t1 (a DATE, b TIME, c INT, KEY c(c), KEY b(b), KEY a(a));
177INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), ();
178UPDATE 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
182DROP TABLE t1;
183
184--echo #19
185CREATE TABLE t1 (a1 INT NOT NULL, b1 INT NOT NULL);
186CREATE TABLE t2 (a2 INT NOT NULL, b2 INT NOT NULL, PRIMARY KEY (a2,b2));
187CREATE TABLE t3 (a3 INT NOT NULL, b3 INT NOT NULL, PRIMARY KEY (a3,b3));
188INSERT INTO t1 VALUES (1,1), (2,1), (1,3);
189INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
190INSERT 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
194DROP TABLE t1, t2, t3;
195
196--echo #20
197CREATE TABLE t1 (a INT);
198INSERT INTO t1 VALUES (1), (2), (3);
199CREATE TABLE t2 (a INT);
200INSERT 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
204DROP TABLE t1, t2;
205
206--echo #21
207CREATE TABLE t1 (a1 INT);
208INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
209CREATE TABLE t2 (a2 VARCHAR(10));
210INSERT INTO t2 VALUES (1), (2), (3), (4), (5);
211SET @save_optimizer_switch= @@optimizer_switch;
212--disable_query_log
213if (`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
221SET @@optimizer_switch= @save_optimizer_switch;
222TRUNCATE t1;
223INSERT 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
227DROP TABLE t1, t2;
228
229--echo #22
230CREATE TABLE t1 (i INT, j INT);
231INSERT 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
235DROP TABLE t1;
236
237--echo #23
238CREATE TABLE t1 (i INT, j INT);
239INSERT 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
243DROP TABLE t1;
244
245--echo #24
246CREATE TABLE t1 (i INT);
247INSERT 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);
250CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
251INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
252INSERT 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
257DROP TABLE t1, t2;
258
259--echo #25
260CREATE TABLE t1 (i INT);
261INSERT INTO t1 VALUES (1), (2), (3);
262CREATE 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
266DROP TABLE t1, t2;
267
268--echo #26
269CREATE TABLE t1 (i INT);
270INSERT INTO t1 VALUES (1), (2), (3);
271CREATE 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
275DROP TABLE t1, t2;
276
277--echo #27
278CREATE TABLE t1 (i INT);
279--let $query = INSERT INTO t1 SET i = 10
280--source include/explain_utils.inc
281DROP TABLE t1;
282
283--echo #28
284CREATE TABLE t1 (i INT);
285--let $query = REPLACE INTO t1 SET i = 10
286--source include/explain_utils.inc
287DROP TABLE t1;
288
289--echo #29
290CREATE TABLE t1 (a INT, i INT PRIMARY KEY);
291INSERT 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
297DROP TABLE t1;
298
299--echo #30
300CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1)));
301INSERT 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
307DROP TABLE t1;
308
309--echo #31
310CREATE TABLE t1 (i INT);
311INSERT 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);
314CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
315INSERT 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
319DROP TABLE t1, t2;
320
321--echo #32
322CREATE TABLE t1 (i INT);
323INSERT 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);
326CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
327INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
328INSERT 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
333DROP TABLE t1, t2;
334
335--echo #33
336CREATE TABLE t1 (i INT);
337INSERT 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);
340CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c));
341INSERT 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
345DROP TABLE t1, t2;
346
347--echo #34
348CREATE TABLE t1 (i INT);
349INSERT 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);
352CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c))
353  ENGINE=HEAP;
354INSERT 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
358DROP TABLE t1, t2;
359
360--echo #35
361CREATE TABLE t1 (i INT);
362INSERT 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);
366CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
367INSERT 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
371DROP TABLE t1, t2;
372
373--echo #36
374CREATE TABLE t1 (i INT);
375INSERT 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);
378CREATE TABLE t2(a INT, i INT PRIMARY KEY);
379INSERT 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
383DROP TABLE t1, t2;
384
385--echo #37
386CREATE TABLE t1 (i INT);
387INSERT 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);
390CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
391INSERT 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
395DROP TABLE t1, t2;
396
397--echo #38
398CREATE TABLE t1 (i INT);
399INSERT 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);
402CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b));
403INSERT INTO t2 (a, b) SELECT i, i FROM t1;
404INSERT 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
409DROP TABLE t1, t2;
410
411--echo #39
412CREATE TABLE t1 (i INT);
413INSERT 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);
416CREATE TABLE t2(a INT, i INT PRIMARY KEY);
417INSERT 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
422DROP TABLE t1, t2;
423
424--echo #40
425CREATE TABLE t1 (i INT);
426INSERT 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);
429CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1)));
430INSERT 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
434DROP TABLE t1, t2;
435
436--echo #41
437CREATE TABLE t1 (i INT);
438INSERT 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);
441CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
442INSERT 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
446DROP TABLE t1, t2;
447
448--echo #42
449CREATE TABLE t1 (i INT);
450INSERT 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);
453CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
454INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
455INSERT 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
460DROP TABLE t1, t2;
461
462--echo #43
463CREATE TABLE t1 (i INT);
464INSERT 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);
467CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c));
468INSERT 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
472DROP TABLE t1, t2;
473
474--echo #44
475CREATE TABLE t1 (i INT);
476INSERT 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);
479CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c))
480  ENGINE=HEAP;
481INSERT 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
485DROP TABLE t1, t2;
486
487--echo #45
488CREATE TABLE t1 (i INT);
489INSERT 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);
493CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
494INSERT 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
498DROP TABLE t1, t2;
499
500--echo #46
501CREATE TABLE t1 (i INT);
502INSERT 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);
505CREATE TABLE t2(a INT, i INT PRIMARY KEY);
506INSERT 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
510DROP TABLE t1, t2;
511
512--echo #47
513CREATE TABLE t1 (i INT);
514INSERT 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);
517CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
518INSERT 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
522DROP TABLE t1, t2;
523
524--echo #48
525CREATE TABLE t1 (i INT);
526INSERT 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);
529CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b));
530INSERT INTO t2 (a, b) SELECT i, i FROM t1;
531INSERT 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
536DROP TABLE t1, t2;
537
538--echo #49
539CREATE 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);
546INSERT 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
553DROP TABLE t1;
554
555--echo #50
556CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
557INSERT 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
561DROP TABLE t1;
562
563--echo #51
564CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
565CREATE TABLE t2 (c1 INT, c2 INT);
566INSERT 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
573DROP TABLE t1, t2;
574
575--echo #52
576CREATE TABLE t1(f1 INT, f2 INT);
577CREATE TABLE t2(f3 INT, f4 INT);
578CREATE INDEX IDX ON t2(f3);
579INSERT INTO t1 VALUES(1,0),(2,0);
580INSERT 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
584DROP TABLE t1, t2;
585
586--echo #55
587CREATE TABLE t1(a INT);
588INSERT INTO t1 VALUES (1);
589SET @a = NULL;
590EXPLAIN DELETE FROM t1 WHERE (@a:= a);
591if (`SELECT @a IS NOT NULL`) {
592  die Unexpectedly modified user variable;
593}
594DROP TABLE t1;
595
596--echo #56
597CREATE TABLE t1 (a INT);
598INSERT 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
602EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE uknown_column = 12345;
603DROP TABLE t1;
604
605--echo #57
606CREATE TABLE t1(f1 INT);
607--error ER_BAD_FIELD_ERROR
608EXPLAIN EXTENDED UPDATE t1 SET f2=1 ORDER BY f2;
609--error ER_BAD_FIELD_ERROR
610UPDATE t1 SET f2=1 ORDER BY f2;
611DROP TABLE t1;
612
613--disable_parsing
614--echo #59
615CREATE TABLE t1 ( a INT, KEY( a ) );
616INSERT INTO t1 VALUES (0), (1);
617CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
618SET SESSION sql_safe_updates = 1;
619--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
620EXPLAIN EXTENDED UPDATE IGNORE v1 SET a = 1;
621--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
622UPDATE IGNORE v1 SET a = 1;
623SET SESSION sql_safe_updates = DEFAULT;
624DROP TABLE t1;
625DROP VIEW v1;
626--enable_parsing
627
628--echo #62
629CREATE TABLE t1 (a INT);
630INSERT INTO t1 VALUES (0), (1);
631CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
632--let $query =  UPDATE        v1 SET a = 1 WHERE a > 0
633--let $select = SELECT * FROM v1           WHERE a > 0
634--source include/explain_utils.inc
635--let $query =  UPDATE        t1, v1 SET v1.a = 1 WHERE t1.a = v1.a
636--let $select = SELECT * FROM t1, v1              WHERE t1.a = v1.a
637--source include/explain_utils.inc
638DROP TABLE t1;
639DROP VIEW v1;
640
641--echo #63
642CREATE TABLE t1 (a INT, PRIMARY KEY(a));
643INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);
644CREATE VIEW v1 (a) AS SELECT a FROM t1;
645--let $query =  DELETE   FROM v1 WHERE a < 4
646--let $select = SELECT * FROM v1 WHERE a < 4
647--source include/explain_utils.inc
648DROP TABLE t1;
649DROP VIEW v1;
650
651--echo #64
652CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
653INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10);
654CREATE TABLE t2 (x INT);
655INSERT INTO t2 VALUES (1), (2), (3), (4);
656CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1;
657--let $query =  DELETE v1 FROM t2, v1 WHERE t2.x = v1.a
658--let $select = SELECT *  FROM t2, v1 WHERE t2.x = v1.a
659--source include/explain_utils.inc
660DROP TABLE t1,t2;
661DROP VIEW v1;
662
663--echo #65
664CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
665INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10);
666CREATE TABLE t2 (x INT);
667INSERT INTO t2 VALUES (1), (2), (3), (4);
668CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1;
669--let $query =  DELETE v1 FROM t2, v1 WHERE t2.x = v1.a
670--let $select = SELECT *  FROM t2, v1 WHERE t2.x = v1.a
671--source include/explain_utils.inc
672DROP TABLE t1,t2;
673DROP VIEW v1;
674
675--echo #66
676CREATE TABLE t1 (a INT);
677CREATE VIEW v1 (x) AS SELECT a FROM t1;
678--let $query =  INSERT INTO v1 VALUES (10)
679--let $select = SELECT NULL
680--source include/explain_utils.inc
681DROP TABLE t1;
682DROP VIEW v1;
683
684--echo #67
685CREATE TABLE t1 (a INT);
686CREATE TABLE t2 (b INT);
687INSERT INTO t2 VALUES (1), (2), (3);
688CREATE VIEW v1 (x) AS SELECT b FROM t2;
689--let $query =  INSERT INTO v1 SELECT * FROM t1
690--let $select =                SELECT * FROM t1
691--source include/explain_utils.inc
692DROP TABLE t1, t2;
693DROP VIEW v1;
694
695--echo #68
696CREATE TABLE t1 (i INT);
697EXPLAIN INSERT DELAYED INTO t1 VALUES (1);
698DROP TABLE t1;
699
700--echo #69
701CREATE TABLE t1 (a INT);
702INSERT INTO t1 VALUES (1), (2), (3);
703CREATE TABLE t2 (b INT);
704INSERT INTO t2 VALUES (1), (2), (3);
705--let $query =  UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
706--let $select = SELECT * FROM t1            WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
707--source include/explain_utils.inc
708--let $query =  UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
709--let $select = SELECT * FROM t1, t2            WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
710--source include/explain_utils.inc
711--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)
712--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)
713--source include/explain_utils.inc
714DROP TABLE t1,t2;
715
716--echo #70
717CREATE TABLE t1 (c1 INT KEY);
718CREATE TABLE t2 (c2 INT);
719CREATE TABLE t3 (c3 INT);
720EXPLAIN EXTENDED UPDATE t3 SET c3 = (
721  SELECT COUNT(d1.c1)
722    FROM (
723      SELECT a11.c1 FROM t1 AS a11
724        STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1
725        JOIN t1 AS a12 ON a12.c1 = a11.c1
726    ) d1
727);
728
729DROP TABLE t1, t2, t3;
730
731--disable_parsing
732--echo #71
733#
734# Bug: after EXPLAIN bulk INSERT...SELECT and bulk INSERT...SELECT
735# to a # MyISAM table the SELECT query may fail with the
736# "1030: Got error 124 from storage engine" error message.
737#
738CREATE TABLE t1 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX i1(c1));
739INSERT INTO t1 VALUES (1,0),(2,0),(3,0),(4,0),(5,0),(6,0),(7,0),(8,0);
740--disable_query_log
741let $1=7;
742SET @d=8;
743while ($1) {
744  eval INSERT INTO t1 SELECT c1 + @d, c2 + @d FROM t1;
745  eval SET @d = @d*2;
746  dec $1;
747}
748--enable_query_log
749CREATE TABLE t2 LIKE t1;
750
751# replace "rows" column for InnoDB
752--replace_column 9 X
753EXPLAIN INSERT INTO t2 SELECT * FROM t1;
754INSERT INTO t2 SELECT * FROM t1;
755--disable_result_log
756SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
757--enable_result_log
758
759DROP TABLE t1, t2;
760--enable_parsing
761
762--echo #73
763
764CREATE TABLE t1 (id INT);
765CREATE TABLE t2 (id INT);
766INSERT INTO t1 VALUES (1), (2);
767
768EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id;
769
770DROP TABLE t1,t2;
771
772--echo #74
773
774CREATE TABLE t1(a INT PRIMARY KEY);
775INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
776
777--echo # used key is modified & Using temporary
778
779--let $query = UPDATE t1 SET a=a+1 WHERE a>10
780--let $select = SELECT a t1 FROM t1 WHERE a>10
781--source include/explain_utils.inc
782
783--echo # used key is modified & Using filesort
784
785--let $query = UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20
786--let $select = SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20
787--source include/explain_utils.inc
788
789DROP TABLE t1;
790
791--echo #
792--echo # Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH
793--echo #                EXPLAIN UPDATE/DEL/INS
794--echo #
795
796CREATE TABLE t1 (i INT);
797CREATE TABLE t2 (i INT);
798
799--delimiter |
800CREATE PROCEDURE p1() BEGIN EXPLAIN INSERT INTO t1 VALUES (1);END|
801CREATE PROCEDURE p2() BEGIN         INSERT INTO t1 VALUES (1);END|
802CREATE PROCEDURE p3() BEGIN EXPLAIN INSERT INTO t1 SELECT 1;END|
803CREATE PROCEDURE p4() BEGIN         INSERT INTO t1 SELECT 1;END|
804CREATE PROCEDURE p5() BEGIN EXPLAIN REPLACE INTO t1 VALUES (1);END|
805CREATE PROCEDURE p6() BEGIN         REPLACE INTO t1 VALUES (1);END|
806CREATE PROCEDURE p7() BEGIN EXPLAIN REPLACE INTO t1 SELECT 1;END|
807CREATE PROCEDURE p8() BEGIN         REPLACE INTO t1 SELECT 1;END|
808CREATE PROCEDURE p9() BEGIN EXPLAIN UPDATE t1 SET i = 10;END|
809CREATE PROCEDURE p10() BEGIN         UPDATE t1 SET i = 10;END|
810CREATE PROCEDURE p11() BEGIN EXPLAIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END|
811CREATE PROCEDURE p12() BEGIN         UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END|
812CREATE PROCEDURE p13() BEGIN EXPLAIN DELETE FROM t1;END|
813CREATE PROCEDURE p14() BEGIN         DELETE FROM t1;END|
814CREATE PROCEDURE p15() BEGIN EXPLAIN DELETE FROM t1 USING t1;END|
815CREATE PROCEDURE p16() BEGIN         DELETE FROM t1 USING t1;END|
816--delimiter ;
817
818let $i=16;
819while($i) {
820	eval CALL p$i();
821	eval DROP PROCEDURE p$i;
822	dec $i;
823}
824
825DROP TABLE t1, t2;
826
827--echo #
828
829-- disable_query_log
830-- disable_result_log
831# SET GLOBAL innodb_stats_persistent=default;
832-- enable_result_log
833-- enable_query_log
834