MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
explain.inc
1 #
2 # Test of different EXPLAINs
3 
4 --disable_warnings
5 drop table if exists t1;
6 --enable_warnings
7 create table t1 (id int not null, str char(10), unique(str));
8 eval explain $FORMAT select * from t1;
9 insert into t1 values (1, null),(2, null),(3, "foo"),(4, "bar");
10 select * from t1 where str is null;
11 select * from t1 where str="foo";
12 eval explain $FORMAT select * from t1 where str is null;
13 eval explain $FORMAT select * from t1 where str="foo";
14 eval explain $FORMAT select * from t1 ignore key (str) where str="foo";
15 eval explain $FORMAT select * from t1 use key (str,str) where str="foo";
16 
17 #The following should give errors
18 --error 1176
19 eval explain $FORMAT select * from t1 use key (str,str,foo) where str="foo";
20 --error 1176
21 eval explain $FORMAT select * from t1 ignore key (str,str,foo) where str="foo";
22 drop table t1;
23 
24 --eval explain $FORMAT select 1
25 
26 create table t1 (a int not null);
27 eval explain $FORMAT select count(*) from t1;
28 insert into t1 values(1);
29 eval explain $FORMAT select count(*) from t1;
30 insert into t1 values(1);
31 eval explain $FORMAT select count(*) from t1;
32 drop table t1;
33 
34 #
35 # Bug #3403 Wrong encoding in EXPLAIN SELECT output
36 #
37 set names koi8r;
38 create table таб (кол0 int, кол1 int, key инд0 (кол0), key инд01 (кол0,кол1));
39 insert into таб (кол0) values (1);
40 insert into таб (кол0) values (2);
41 eval explain $FORMAT select кол0 from таб where кол0=1;
42 drop table таб;
43 set names latin1;
44 
45 # End of 4.1 tests
46 
47 
48 #
49 # Bug#15463: EXPLAIN SELECT..INTO hangs the client (QB, command line)
50 #
51 select 3 into @v1;
52 eval explain $FORMAT select 3 into @v1;
53 
54 #
55 # Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were
56 # optimized away.
57 #
58 create table t1(f1 int, f2 int);
59 insert into t1 values (1,1);
60 create view v1 as select * from t1 where f1=1;
61 explain extended select * from v1 where f2=1;
62 explain extended select * from t1 where 0;
63 explain extended select * from t1 where 1;
64 explain extended select * from t1 having 0;
65 explain extended select * from t1 having 1;
66 drop view v1;
67 drop table t1;
68 
69 #
70 # Bug #32241: memory corruption due to large index map in 'Range checked for
71 # each record'
72 #
73 
74 CREATE TABLE t1(c INT);
75 INSERT INTO t1 VALUES (),();
76 
77 CREATE TABLE t2 (b INT,
78 KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
79 KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
80 KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
81 KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
82 KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
83 KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
84 KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
85 KEY(b),KEY(b),KEY(b),KEY(b),KEY(b));
86 
87 INSERT INTO t2 VALUES (),(),();
88 
89 # We only need to make sure that there is no buffer overrun and the index map
90 # is displayed correctly
91 #--replace_column 1 X 2 X 3 X 4 X 5 X 6 X 7 X 8 X 9 X
92 eval EXPLAIN $FORMAT SELECT 1 FROM (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
93 DROP TABLE t2;
94 DROP TABLE t1;
95 
96 #
97 # Bug #34773: query with explain extended and derived table / other table
98 # crashes server
99 #
100 
101 CREATE TABLE t1(a INT);
102 CREATE TABLE t2(a INT);
103 INSERT INTO t1 VALUES (1),(2);
104 INSERT INTO t2 VALUES (1),(2);
105 
106 EXPLAIN EXTENDED SELECT 1
107  FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
108 
109 EXPLAIN EXTENDED SELECT 1
110  FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
111 
112 prepare s1 from
113 'EXPLAIN EXTENDED SELECT 1
114  FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
115 execute s1;
116 
117 prepare s1 from
118 'EXPLAIN EXTENDED SELECT 1
119  FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
120 execute s1;
121 execute s1;
122 
123 DROP TABLE t1,t2;
124 
125 
126 #
127 # Bug #43354: Use key hint can crash server in explain extended query
128 #
129 
130 CREATE TABLE t1 (a INT PRIMARY KEY);
131 
132 --error ER_KEY_DOES_NOT_EXITS
133 EXPLAIN EXTENDED SELECT COUNT(a) FROM t1 USE KEY(a);
134 
135 DROP TABLE t1;
136 
137 #
138 # Bug#45989 memory leak after explain encounters an error in the query
139 #
140 CREATE TABLE t1(a LONGTEXT);
141 INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet));
142 INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet));
143 --error ER_BAD_FIELD_ERROR
144 eval EXPLAIN $FORMAT SELECT DISTINCT 1 FROM t1,
145  (SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) as d1
146  WHERE t1.a = d1.a;
147 DROP TABLE t1;
148 
149 --echo #
150 --echo # Bug#48295:
151 --echo # explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode
152 --echo #
153 
154 CREATE TABLE t1 (f1 INT);
155 
156 SELECT @@session.sql_mode INTO @old_sql_mode;
157 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
158 
159 # EXPLAIN EXTENDED (with subselect). used to crash.
160 # This is actually a valid query for this sql_mode,
161 # but it was transformed in such a way that it failed, see
162 # Bug#12329653 - EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY
163 EXPLAIN EXTENDED SELECT 1 FROM t1
164  WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
165 
166 SET SESSION sql_mode=@old_sql_mode;
167 
168 DROP TABLE t1;
169 
170 --echo End of 5.0 tests.
171 
172 --echo #
173 --echo # Bug#37870: Usage of uninitialized value caused failed assertion.
174 --echo #
175 set @opt_sw_save= @@optimizer_switch;
176 
177 --disable_query_log
178 if (`select locate('semijoin', @@optimizer_switch) > 0`)
179 {
180  set optimizer_switch='semijoin=off';
181 }
182 --enable_query_log
183 create table t1 (dt datetime not null, t time not null);
184 create table t2 (dt datetime not null);
185 insert into t1 values ('2001-01-01 1:1:1', '1:1:1'),
186 ('2001-01-01 1:1:1', '1:1:1');
187 insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1');
188 flush tables;
189 eval EXPLAIN $FORMAT SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
190 flush tables;
191 SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
192 flush tables;
193 eval EXPLAIN $FORMAT SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
194 flush tables;
195 SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
196 drop tables t1, t2;
197 set optimizer_switch= @opt_sw_save;
198 
199 --echo #
200 --echo # Bug#47669: Query showed by EXPLAIN EXTENDED gives different result from original query
201 --echo #
202 
203 CREATE TABLE t1 (c int);
204 INSERT INTO t1 VALUES (NULL);
205 CREATE TABLE t2 (d int);
206 INSERT INTO t2 VALUES (NULL), (0);
207 EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
208 DROP TABLE t1, t2;
209 
210 --echo #
211 --echo # Bug#30302: Tables that were optimized away are printed in the
212 --echo # EXPLAIN EXTENDED warning.
213 --echo #
214 create table t1(f1 int);
215 create table t2(f2 int);
216 insert into t1 values(1);
217 insert into t2 values(1),(2);
218 explain extended select * from t1 where f1=1;
219 explain extended select * from t1 join t2 on f1=f2 where f1=1;
220 drop table t1,t2;
221 
222 --echo #
223 --echo # Bug #48419: another explain crash..
224 --echo #
225 CREATE TABLE t1 (a INT);
226 CREATE TABLE t2 (b BLOB, KEY b(b(100)));
227 INSERT INTO t2 VALUES ('1'), ('2'), ('3');
228 
229 FLUSH TABLES;
230 
231 eval EXPLAIN $FORMAT SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t1 t JOIN t2 WHERE b <= 1 AND t.a);
232 
233 DROP TABLE t1, t2;
234 
235 --echo #
236 --echo # Bug #48573: difference of index selection between rpm binary and
237 --echo # .tar.gz, windows vs linux..
238 --echo #
239 
240 CREATE TABLE t1(c1 INT, c2 INT, c4 INT, c5 INT, KEY(c2, c5), KEY(c2, c4, c5));
241 INSERT INTO t1 VALUES(4, 1, 1, 1);
242 INSERT INTO t1 VALUES(3, 1, 1, 1);
243 INSERT INTO t1 VALUES(2, 1, 1, 1);
244 INSERT INTO t1 VALUES(1, 1, 1, 1);
245 
246 eval EXPLAIN $FORMAT SELECT c1 FROM t1 WHERE c2 = 1 AND c4 = 1 AND c5 = 1;
247 
248 DROP TABLE t1;
249 
250 --echo #
251 --echo # Bug#56814 Explain + subselect + fulltext crashes server
252 --echo #
253 
254 CREATE TABLE t1(f1 VARCHAR(6) NOT NULL,
255 FULLTEXT KEY(f1),UNIQUE(f1));
256 INSERT INTO t1 VALUES ('test');
257 
258 eval EXPLAIN $FORMAT SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1));
259 
260 eval PREPARE stmt FROM 'EXPLAIN $FORMAT SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1))';
261 
262 EXECUTE stmt;
263 EXECUTE stmt;
264 
265 DEALLOCATE PREPARE stmt;
266 
267 eval PREPARE stmt FROM 'EXPLAIN $FORMAT SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1))';
268 
269 EXECUTE stmt;
270 EXECUTE stmt;
271 
272 DEALLOCATE PREPARE stmt;
273 
274 DROP TABLE t1;
275 
276 --echo End of 5.1 tests.
277 
278 --echo #
279 --echo # Bug#46860:
280 --echo # Crash/segfault using EXPLAIN EXTENDED on query using UNION in subquery.
281 --echo #
282 --disable_warnings
283 drop table if exists t1;
284 --enable_warnings
285 create table `t1` (`a` int);
286 --error ER_NON_UNIQ_ERROR
287 explain extended select 1 from `t1`, `t1` as `t2`
288 where `t1`.`a` > all ( (select `a` from `t1` ) union (select `a`) );
289 drop table t1;
290 
291 --echo #
292 --echo # BUG#30597: Change EXPLAIN output to include extrema of
293 --echo # UNION components
294 --echo #
295 
296 eval EXPLAIN $FORMAT SELECT 1
297  UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
298  UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
299  UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
300  UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
301  UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
302  UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
303  ;
304 
305 --echo # End BUG#30597
306 
307 --echo #
308 --echo # BUG#53562: EXPLAIN statement should hint when
309 --echo # index is not used due to type conversion
310 --echo #
311 
312 CREATE TABLE t1 (url char(1) PRIMARY KEY);
313 INSERT INTO t1 VALUES ('1'),('2'),('3'),('4'),('5');
314 
315 --echo
316 --echo # Normally, lookup access on primary key is done
317 EXPLAIN EXTENDED SELECT * FROM t1 WHERE url='1';
318 --echo
319 --echo # Test that index can't be used for lookup due to type conversion
320 --echo # (comparing char and int)
321 SELECT * FROM t1 WHERE url=1;
322 EXPLAIN EXTENDED SELECT * FROM t1 WHERE url=1;
323 --echo
324 --echo # Test that index can't be used for lookup due to collation mismatch
325 SELECT * FROM t1 WHERE url='1' collate latin1_german2_ci;
326 EXPLAIN EXTENDED SELECT * FROM t1 WHERE url='1' collate latin1_german2_ci;
327 
328 --echo
329 --echo # Normally, range access on primary key is done
330 EXPLAIN EXTENDED SELECT * FROM t1 WHERE url>'3';
331 --echo
332 --echo # Test that range access on index can't be done due to type conversion
333 --echo # (comparing char and int)
334 SELECT * FROM t1 WHERE url>3;
335 EXPLAIN EXTENDED SELECT * FROM t1 WHERE url>3;
336 --echo
337 --echo # Test that range access on index can't be done due to collation mismatch
338 SELECT * FROM t1 WHERE url>'3' collate latin1_german2_ci;
339 EXPLAIN EXTENDED SELECT * FROM t1 WHERE url>'3' collate latin1_german2_ci;
340 
341 --echo
342 DROP TABLE t1;
343 
344 --echo # End BUG#53562
345 
346 --echo #
347 --echo # Bug#11829785 EXPLAIN EXTENDED CRASH WITH RIGHT OUTER JOIN, SUBQUERIES
348 --echo #
349 
350 CREATE TABLE t1(a INT);
351 
352 INSERT INTO t1 VALUES (0), (0);
353 
354 PREPARE s FROM
355 'EXPLAIN EXTENDED
356 SELECT SUBSTRING(1, (SELECT 1 FROM t1 a1 RIGHT OUTER JOIN t1 ON 0)) AS d
357 FROM t1 WHERE 0 > ANY (SELECT @a FROM t1)';
358 
359 --error ER_SUBQUERY_NO_1_ROW
360 EXECUTE s;
361 
362 DEALLOCATE PREPARE s;
363 DROP TABLE t1;
364 
365 --echo #
366 --echo # WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
367 --echo #
368 --echo # Coverage tests after code refactoring
369 --echo #
370 
371 CREATE TABLE t1 (a INT);
372 INSERT INTO t1 VALUES (1),(2),(3);
373 CREATE TABLE t2 (a INT);
374 INSERT INTO t2 VALUES (3),(4),(5);
375 
376 # LIMIT <offset> is for SELECT, not for EXPLAIN OUTPUT:
377 --echo # EXPLAIN must return 3 rows:
378 eval EXPLAIN $FORMAT SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
379 
380 DROP TABLE t1, t2;
381 
382 --echo # End WL#4897
383 
384 --echo End of 6.0 tests.