Debian Codegraph @

mysql-test/suite/opt_trace/r/bugs_ps_prot_none.result

raw
1 set optimizer_trace_max_mem_size=1048576;
2 SET end_markers_in_json=on;
3 SET optimizer_trace="enabled=on,one_line=off";
4 #
5 # BUG#12430646 - SEL_ARG::LEFT AND RIGHT POINTERS INCORRECTLY
6 # USED. CRASHES OPTIMIZER TRACING
7 #
8 CREATE TABLE t1 (
9 a INT,
10 b CHAR(2),
11 c INT,
12 d INT,
13 KEY (c),
14 KEY (d,a,b(2)),
15 KEY (b(1))
16 );
17 INSERT INTO t1 VALUES (NULL, 'a', 1, 2), (NULL, 'a', 1, 2),
18 (1, 'a', 1, 2), (1, 'a', 1, 2);
19 CREATE TABLE t2 (
20 a INT,
21 c INT,
22 e INT,
23 KEY (e)
24 );
25 INSERT INTO t2 VALUES (1, 1, NULL), (1, 1, NULL);
26 SELECT 1
27 FROM t1, t2
28 WHERE t1.d <> '1' AND t1.b > '1'
29 AND t1.a = t2.a AND t1.c = t2.c;
30 1
31 1
32 1
33 1
34 1
35 DROP TABLE t1, t2;
36 #
37 # BUG#12595210 - JSON SYNTAX ERROR ASSERT ON WHERE FIELD NOT IN SUBQUERY
38 #
39 CREATE TABLE t1 (
40 pk INT NOT NULL AUTO_INCREMENT,
41 col_int_key INT DEFAULT NULL,
42 col_varchar_key VARCHAR(1) DEFAULT NULL,
43 PRIMARY KEY (pk)
44 ) ENGINE=InnoDB, CHARSET utf8mb4;
45 CREATE TABLE t2 (
46 pk INT NOT NULL AUTO_INCREMENT,
47 col_int_key INT DEFAULT NULL,
48 col_varchar_key VARCHAR(1) DEFAULT NULL,
49 col_varchar_nokey VARCHAR(1) DEFAULT NULL,
50 PRIMARY KEY (pk)
51 ) ENGINE=InnoDB, CHARSET utf8mb4;
52 CREATE TABLE t3 (
53 pk INT NOT NULL AUTO_INCREMENT,
54 col_int_key INT DEFAULT NULL,
55 col_varchar_key VARCHAR(1) DEFAULT NULL,
56 col_varchar_nokey VARCHAR(1) DEFAULT NULL,
57 PRIMARY KEY (pk)
58 ) ENGINE=InnoDB, CHARSET utf8mb4;
59 CREATE TABLE t4 (
60 pk INT NOT NULL AUTO_INCREMENT,
61 col_int_key INT DEFAULT NULL,
62 col_varchar_key VARCHAR(1) DEFAULT NULL,
63 PRIMARY KEY (pk)
64 ) ENGINE=InnoDB, CHARSET utf8mb4;
65 CREATE VIEW view_t4 AS SELECT * FROM t4;
66 CREATE TABLE where_subselect_19379 SELECT
67 (
68 SELECT SUM(sq1_alias1.pk) AS sq1_field1
69 FROM view_t4 AS sq1_alias1
70 INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
71 sq1_alias1.col_varchar_key )
72 ) AS field1,
73 alias1.col_varchar_nokey AS field2
74 FROM (t2 AS alias1
75 LEFT JOIN ( t2 AS alias2
76 LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
77 alias2.col_varchar_key )
78 ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
79 )
80 WHERE ( alias2.col_varchar_key IN (
81 SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
82 FROM t3 AS sq2_alias1
83 WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
84 )
85 );
86 SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
87 (SELECT
88 (
89 SELECT SUM(sq1_alias1.pk) AS sq1_field1
90 FROM view_t4 AS sq1_alias1
91 INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
92 sq1_alias1.col_varchar_key )
93 ) AS field1,
94 alias1.col_varchar_nokey AS field2
95 FROM (t2 AS alias1
96 LEFT JOIN ( t2 AS alias2
97 LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
98 alias2.col_varchar_key )
99 ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
100 )
101 WHERE ( alias2.col_varchar_key IN (
102 SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
103 FROM t3 AS sq2_alias1
104 WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
105 )
106 ));
107 field1 field2
108 select * from information_schema.OPTIMIZER_TRACE;
109 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
110 SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
111 (SELECT
112 (
113 SELECT SUM(sq1_alias1.pk) AS sq1_field1
114 FROM view_t4 AS sq1_alias1
115 INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
116 sq1_alias1.col_varchar_key )
117 ) AS field1,
118 alias1.col_varchar_nokey AS field2
119 FROM (t2 AS alias1
120 LEFT JOIN ( t2 AS alias2
121 LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
122 alias2.col_varchar_key )
123 ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
124 )
125 WHERE ( alias2.col_varchar_key IN (
126 SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
127 FROM t3 AS sq2_alias1
128 WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
129 )
130 )) {
131 "steps": [
132 {
133 "join_optimization": {
134 "select#": 1,
135 "steps": [
136 {
137 "condition_processing": {
138 "condition": "WHERE",
139 "original_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` where (<in_optimizer>(`alias2`.`col_varchar_key`,<exists>(/* select#4 */ select `sq2_alias1`.`col_varchar_nokey` AS `sq2_field1` from `t3` `sq2_alias1` where ((`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (<cache>(`alias2`.`col_varchar_key`) = `sq2_alias1`.`col_varchar_nokey`)))) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)",
140 "steps": [
141 {
142 "transformation": "equality_propagation",
143 "subselect_evaluation": [
144 ] /* subselect_evaluation */,
145 "resulting_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` where (<in_optimizer>(`alias2`.`col_varchar_key`,<exists>(/* select#4 */ select `sq2_alias1`.`col_varchar_nokey` AS `sq2_field1` from `t3` `sq2_alias1` where ((`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (<cache>(`alias2`.`col_varchar_key`) = `sq2_alias1`.`col_varchar_nokey`)))) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)"
146 },
147 {
148 "transformation": "constant_propagation",
149 "subselect_evaluation": [
150 ] /* subselect_evaluation */,
151 "resulting_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` where (<in_optimizer>(`alias2`.`col_varchar_key`,<exists>(/* select#4 */ select `sq2_alias1`.`col_varchar_nokey` AS `sq2_field1` from `t3` `sq2_alias1` where ((`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (<cache>(`alias2`.`col_varchar_key`) = `sq2_alias1`.`col_varchar_nokey`)))) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)"
152 },
153 {
154 "transformation": "trivial_condition_removal",
155 "subselect_evaluation": [
156 ] /* subselect_evaluation */,
157 "resulting_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` where (<in_optimizer>(`alias2`.`col_varchar_key`,<exists>(/* select#4 */ select `sq2_alias1`.`col_varchar_nokey` AS `sq2_field1` from `t3` `sq2_alias1` where ((`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (<cache>(`alias2`.`col_varchar_key`) = `sq2_alias1`.`col_varchar_nokey`)))) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)"
158 }
159 ] /* steps */
160 } /* condition_processing */
161 },
162 {
163 "substitute_generated_columns": {
164 } /* substitute_generated_columns */
165 },
166 {
167 "table_dependencies": [
168 {
169 "table": "`where_subselect_19379`",
170 "row_may_be_null": false,
171 "map_bit": 0,
172 "depends_on_map_bits": [
173 ] /* depends_on_map_bits */
174 }
175 ] /* table_dependencies */
176 },
177 {
178 "ref_optimizer_key_uses": [
179 ] /* ref_optimizer_key_uses */
180 },
181 {
182 "rows_estimation": [
183 {
184 "table": "`where_subselect_19379`",
185 "rows": 1,
186 "cost": 1,
187 "table_type": "system",
188 "empty": true
189 }
190 ] /* rows_estimation */
191 }
192 ] /* steps */,
193 "empty_result": {
194 "cause": "no matching row in const table"
195 } /* empty_result */
196 } /* join_optimization */
197 },
198 {
199 "join_execution": {
200 "select#": 1,
201 "steps": [
202 ] /* steps */
203 } /* join_execution */
204 }
205 ] /* steps */
206 } 0 0
207 drop table t1,t2,t3,t4,where_subselect_19379;
208 drop view view_t4;
209 #
210 # BUG#12607524 JSON PARSE ERROR ON SELECT ... FROM ... WHERE .. IN (SUBQUERY)
211 #
212 CREATE TABLE t1 (
213 col_int_key int(11) DEFAULT NULL,
214 col_varchar_key varchar(1) DEFAULT NULL,
215 KEY col_int_key (col_int_key),
216 KEY col_varchar_key (col_varchar_key,col_int_key)
217 ) ENGINE=MyISAM;
218 Warnings:
219 Warning 1681 Integer display width is deprecated and will be removed in a future release.
220 INSERT INTO t1 VALUES (8,'g');
221 CREATE TABLE t2 (
222 col_int_key int(11) DEFAULT NULL,
223 col_varchar_key varchar(1) DEFAULT NULL,
224 KEY col_int_key (col_int_key),
225 KEY col_varchar_key (col_varchar_key,col_int_key)
226 ) ENGINE=MyISAM;
227 Warnings:
228 Warning 1681 Integer display width is deprecated and will be removed in a future release.
229 INSERT INTO t2 VALUES (7,'x');
230 CREATE TABLE where_subselect_19033
231 SELECT
232 ( SELECT col_int_key FROM t2 ) as field1
233 FROM t1
234 ;
235 SELECT * FROM where_subselect_19033;
236 field1
237 7
238 SELECT field1
239 FROM where_subselect_19033
240 WHERE field1 IN
241 ( SELECT
242 ( SELECT col_int_key FROM t2 )
243 FROM t1
244 )
245 ;
246 field1
247 7
248 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
249 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
250 SELECT field1
251 FROM where_subselect_19033
252 WHERE field1 IN
253 ( SELECT
254 ( SELECT col_int_key FROM t2 )
255 FROM t1
256 ) {
257 "steps": [
258 {
259 "join_optimization": {
260 "select#": 1,
261 "steps": [
262 {
263 "condition_processing": {
264 "condition": "WHERE",
265 "original_condition": "<in_optimizer>(`where_subselect_19033`.`field1`,<exists>(/* select#2 */ select (/* select#3 */ select `t2`.`col_int_key` from `t2`) from `t1` where (<cache>(`where_subselect_19033`.`field1`) = (/* select#3 */ select `t2`.`col_int_key` from `t2`))))",
266 "steps": [
267 {
268 "transformation": "equality_propagation",
269 "subselect_evaluation": [
270 ] /* subselect_evaluation */,
271 "resulting_condition": "<in_optimizer>(`where_subselect_19033`.`field1`,<exists>(/* select#2 */ select (/* select#3 */ select `t2`.`col_int_key` from `t2`) from `t1` where (<cache>(`where_subselect_19033`.`field1`) = (/* select#3 */ select `t2`.`col_int_key` from `t2`))))"
272 },
273 {
274 "transformation": "constant_propagation",
275 "subselect_evaluation": [
276 ] /* subselect_evaluation */,
277 "resulting_condition": "<in_optimizer>(`where_subselect_19033`.`field1`,<exists>(/* select#2 */ select (/* select#3 */ select `t2`.`col_int_key` from `t2`) from `t1` where (<cache>(`where_subselect_19033`.`field1`) = (/* select#3 */ select `t2`.`col_int_key` from `t2`))))"
278 },
279 {
280 "transformation": "trivial_condition_removal",
281 "subselect_evaluation": [
282 ] /* subselect_evaluation */,
283 "resulting_condition": "<in_optimizer>(`where_subselect_19033`.`field1`,<exists>(/* select#2 */ select (/* select#3 */ select `t2`.`col_int_key` from `t2`) from `t1` where (<cache>(`where_subselect_19033`.`field1`) = (/* select#3 */ select `t2`.`col_int_key` from `t2`))))"
284 }
285 ] /* steps */
286 } /* condition_processing */
287 },
288 {
289 "substitute_generated_columns": {
290 } /* substitute_generated_columns */
291 },
292 {
293 "table_dependencies": [
294 {
295 "table": "`where_subselect_19033`",
296 "row_may_be_null": false,
297 "map_bit": 0,
298 "depends_on_map_bits": [
299 ] /* depends_on_map_bits */
300 }
301 ] /* table_dependencies */
302 },
303 {
304 "ref_optimizer_key_uses": [
305 ] /* ref_optimizer_key_uses */
306 },
307 {
308 "rows_estimation": [
309 {
310 "table": "`where_subselect_19033`",
311 "rows": 1,
312 "cost": 1,
313 "table_type": "system",
314 "empty": false
315 }
316 ] /* rows_estimation */
317 },
318 {
319 "subselect_execution": {
320 "select#": 2,
321 "steps": [
322 {
323 "join_optimization": {
324 "select#": 2,
325 "steps": [
326 {
327 "condition_processing": {
328 "condition": "WHERE",
329 "original_condition": "(<cache>('7') = (/* select#3 */ select `t2`.`col_int_key` from `t2`))",
330 "steps": [
331 {
332 "transformation": "equality_propagation",
333 "subselect_evaluation": [
334 ] /* subselect_evaluation */,
335 "resulting_condition": "(<cache>('7') = (/* select#3 */ select `t2`.`col_int_key` from `t2`))"
336 },
337 {
338 "transformation": "constant_propagation",
339 "subselect_evaluation": [
340 ] /* subselect_evaluation */,
341 "resulting_condition": "(<cache>('7') = (/* select#3 */ select `t2`.`col_int_key` from `t2`))"
342 },
343 {
344 "transformation": "trivial_condition_removal",
345 "subselect_evaluation": [
346 ] /* subselect_evaluation */,
347 "resulting_condition": "(<cache>('7') = (/* select#3 */ select `t2`.`col_int_key` from `t2`))"
348 }
349 ] /* steps */
350 } /* condition_processing */
351 },
352 {
353 "substitute_generated_columns": {
354 } /* substitute_generated_columns */
355 },
356 {
357 "table_dependencies": [
358 {
359 "table": "`t1`",
360 "row_may_be_null": false,
361 "map_bit": 0,
362 "depends_on_map_bits": [
363 ] /* depends_on_map_bits */
364 }
365 ] /* table_dependencies */
366 },
367 {
368 "ref_optimizer_key_uses": [
369 ] /* ref_optimizer_key_uses */
370 },
371 {
372 "rows_estimation": [
373 {
374 "table": "`t1`",
375 "rows": 1,
376 "cost": 1,
377 "table_type": "system",
378 "empty": false
379 }
380 ] /* rows_estimation */
381 },
382 {
383 "attaching_conditions_to_tables": {
384 "original_condition": "(<cache>('7') = (/* select#3 */ select `t2`.`col_int_key` from `t2`))",
385 "attached_conditions_computation": [
386 ] /* attached_conditions_computation */,
387 "attached_conditions_summary": [
388 ] /* attached_conditions_summary */
389 } /* attaching_conditions_to_tables */
390 },
391 {
392 "refine_plan": [
393 ] /* refine_plan */
394 }
395 ] /* steps */
396 } /* join_optimization */
397 },
398 {
399 "join_optimization": {
400 "select#": 3,
401 "steps": [
402 {
403 "table_dependencies": [
404 {
405 "table": "`t2`",
406 "row_may_be_null": false,
407 "map_bit": 0,
408 "depends_on_map_bits": [
409 ] /* depends_on_map_bits */
410 }
411 ] /* table_dependencies */
412 },
413 {
414 "rows_estimation": [
415 {
416 "table": "`t2`",
417 "rows": 1,
418 "cost": 1,
419 "table_type": "system",
420 "empty": false
421 }
422 ] /* rows_estimation */
423 },
424 {
425 "attaching_conditions_to_tables": {
426 "original_condition": null,
427 "attached_conditions_computation": [
428 ] /* attached_conditions_computation */,
429 "attached_conditions_summary": [
430 ] /* attached_conditions_summary */
431 } /* attaching_conditions_to_tables */
432 },
433 {
434 "refine_plan": [
435 ] /* refine_plan */
436 }
437 ] /* steps */
438 } /* join_optimization */
439 },
440 {
441 "join_execution": {
442 "select#": 2,
443 "steps": [
444 {
445 "subselect_execution": {
446 "select#": 3,
447 "steps": [
448 {
449 "join_execution": {
450 "select#": 3,
451 "steps": [
452 ] /* steps */
453 } /* join_execution */
454 }
455 ] /* steps */
456 } /* subselect_execution */
457 }
458 ] /* steps */
459 } /* join_execution */
460 }
461 ] /* steps */
462 } /* subselect_execution */
463 },
464 {
465 "condition_on_constant_tables": "<in_optimizer>('7',<exists>(/* select#2 */ select (/* select#3 */ select '7' from dual) from dual where (<cache>('7') = (/* select#3 */ select '7' from dual))))",
466 "condition_value": true
467 },
468 {
469 "attaching_conditions_to_tables": {
470 "original_condition": "<in_optimizer>('7',<exists>(/* select#2 */ select (/* select#3 */ select '7' from dual) from dual where (<cache>('7') = (/* select#3 */ select '7' from dual))))",
471 "attached_conditions_computation": [
472 ] /* attached_conditions_computation */,
473 "attached_conditions_summary": [
474 ] /* attached_conditions_summary */
475 } /* attaching_conditions_to_tables */
476 },
477 {
478 "refine_plan": [
479 ] /* refine_plan */
480 }
481 ] /* steps */
482 } /* join_optimization */
483 },
484 {
485 "join_execution": {
486 "select#": 1,
487 "steps": [
488 ] /* steps */
489 } /* join_execution */
490 }
491 ] /* steps */
492 } 0 0
493 DROP TABLE where_subselect_19033,t1,t2;
494
495 # BUG#12612201 - SEGFAULT IN
496 # SUBSELECT_UNIQUESUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
497
498 CREATE TABLE t1 (
499 col_int_key int(11) DEFAULT NULL,
500 col_varchar_key varchar(1) DEFAULT NULL,
501 col_varchar_nokey varchar(1) DEFAULT NULL
502 );
503 Warnings:
504 Warning 1681 Integer display width is deprecated and will be removed in a future release.
505 CREATE TABLE t2 (
506 pk int(11) NOT NULL AUTO_INCREMENT,
507 col_int_key int(11) DEFAULT NULL,
508 col_varchar_key varchar(1) DEFAULT NULL,
509 col_varchar_nokey varchar(1) DEFAULT NULL,
510 PRIMARY KEY (pk)
511 );
512 Warnings:
513 Warning 1681 Integer display width is deprecated and will be removed in a future release.
514 Warning 1681 Integer display width is deprecated and will be removed in a future release.
515 INSERT INTO t2 VALUES (1,4,'v','v'),(20,5,'r','r');
516 CREATE TABLE t3 (
517 col_int_key int(11) DEFAULT NULL,
518 col_varchar_key varchar(1) DEFAULT NULL,
519 col_varchar_nokey varchar(1) DEFAULT NULL
520 );
521 Warnings:
522 Warning 1681 Integer display width is deprecated and will be removed in a future release.
523 INSERT INTO t3 VALUES (NULL,'j','j'),(8,'c','c');
524 CREATE TABLE where_updatedelete_20769 select count( alias2 . col_varchar_key ) as field1
525 from (
526 (select sq1_alias1 . *
527 from ( t3 as sq1_alias1
528 straight_join t1 as sq1_alias2
529 on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
530 )
531 where sq1_alias1 . col_int_key in (
532 select c_sq1_alias1 . pk as c_sq1_field1
533 from t2 as c_sq1_alias1
534 )
535 ) as alias1
536 left outer join t1 as alias2
537 on (alias2 . col_varchar_key = alias1 . col_varchar_key )
538 )
539 where ( alias2 . col_varchar_key in (
540 select sq2_alias1 . col_varchar_nokey as sq2_field1
541 from t2 as sq2_alias1
542 where sq2_alias1 . col_int_key in (
543 select distinct c_sq2_alias1 . col_int_key as c_sq2_field1
544 from t3 as c_sq2_alias1
545 )
546 ) )
547 or alias1 . col_int_key = 2
548 and alias2 . col_varchar_nokey <= alias1 . col_varchar_nokey
549 order by alias1 . col_varchar_key , field1
550 ;
551 UPDATE where_updatedelete_20769 SET field1 = ( select count( alias2 . col_varchar_key ) as field1
552 from (
553 (select sq1_alias1 . *
554 from ( t3 as sq1_alias1
555 straight_join t1 as sq1_alias2
556 on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
557 )
558 where sq1_alias1 . col_int_key in (
559 select c_sq1_alias1 . pk as c_sq1_field1
560 from t2 as c_sq1_alias1
561 )
562 ) as alias1
563 left outer join t1 as alias2
564 on (alias2 . col_varchar_key = alias1 . col_varchar_key )
565 )
566 where ( alias2 . col_varchar_key in (
567 select sq2_alias1 . col_varchar_nokey as sq2_field1
568 from t2 as sq2_alias1
569 where sq2_alias1 . col_int_key in (
570 select distinct c_sq2_alias1 . col_int_key as c_sq2_field1
571 from t3 as c_sq2_alias1
572 )
573 ) )
574 or alias1 . col_int_key = 2
575 and alias2 . col_varchar_nokey <= alias1 . col_varchar_nokey
576 order by alias1 . col_varchar_key , field1
577 );
578 DROP TABLE where_updatedelete_20769;
579 DROP TABLE t1,t2,t3;
580
581 # BUG#12710761 - INVALID JSON TRACE ON SUBQUERY IN IN-CLAUSE
582
583 CREATE TABLE t1 (col_int_key int, KEY col_int_key (col_int_key));
584 INSERT INTO t1 VALUES (0),(8),(1),(8);
585 CREATE TABLE where_subselect_20070
586 SELECT table2 .col_int_key AS field1,
587 ( SELECT COUNT( col_int_key )
588 FROM t1
589 )
590 FROM t1 AS table1
591 JOIN t1 AS table2
592 ON table2 .col_int_key = table1 .col_int_key;
593 SELECT *
594 FROM where_subselect_20070
595 WHERE (field1, ( SELECT COUNT( col_int_key ) FROM t1 )) IN (
596 SELECT table2 .col_int_key AS field1,
597 ( SELECT COUNT( col_int_key )
598 FROM t1
599 )
600 FROM t1 AS table1
601 JOIN t1 AS table2
602 ON table2 .col_int_key = table1 .col_int_key
603 );
604 field1 ( SELECT COUNT( col_int_key )
605 )
606 0 4
607 1 4
608 8 4
609 8 4
610 8 4
611 8 4
612 FROM t1
613 select * from information_schema.optimizer_trace;
614 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
615 SELECT *
616 FROM where_subselect_20070
617 WHERE (field1, ( SELECT COUNT( col_int_key ) FROM t1 )) IN (
618 SELECT table2 .col_int_key AS field1,
619 ( SELECT COUNT( col_int_key )
620 FROM t1
621 )
622 FROM t1 AS table1
623 JOIN t1 AS table2
624 ON table2 .col_int_key = table1 .col_int_key
625 ) {
626 "steps": [
627 {
628 "join_optimization": {
629 "select#": 1,
630 "steps": [
631 {
632 "condition_processing": {
633 "condition": "WHERE",
634 "original_condition": "<in_optimizer>((`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)),<exists>(/* select#3 */ select `table2`.`col_int_key` AS `field1`,(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`) from `t1` `table1` join `t1` `table2` where ((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))))",
635 "steps": [
636 {
637 "transformation": "equality_propagation",
638 "subselect_evaluation": [
639 ] /* subselect_evaluation */,
640 "resulting_condition": "<in_optimizer>((`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)),<exists>(/* select#3 */ select `table2`.`col_int_key` AS `field1`,(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`) from `t1` `table1` join `t1` `table2` where ((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))))"
641 },
642 {
643 "transformation": "constant_propagation",
644 "subselect_evaluation": [
645 ] /* subselect_evaluation */,
646 "resulting_condition": "<in_optimizer>((`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)),<exists>(/* select#3 */ select `table2`.`col_int_key` AS `field1`,(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`) from `t1` `table1` join `t1` `table2` where ((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))))"
647 },
648 {
649 "transformation": "trivial_condition_removal",
650 "subselect_evaluation": [
651 ] /* subselect_evaluation */,
652 "resulting_condition": "<in_optimizer>((`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)),<exists>(/* select#3 */ select `table2`.`col_int_key` AS `field1`,(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`) from `t1` `table1` join `t1` `table2` where ((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))))"
653 }
654 ] /* steps */
655 } /* condition_processing */
656 },
657 {
658 "substitute_generated_columns": {
659 } /* substitute_generated_columns */
660 },
661 {
662 "table_dependencies": [
663 {
664 "table": "`where_subselect_20070`",
665 "row_may_be_null": false,
666 "map_bit": 0,
667 "depends_on_map_bits": [
668 ] /* depends_on_map_bits */
669 }
670 ] /* table_dependencies */
671 },
672 {
673 "ref_optimizer_key_uses": [
674 ] /* ref_optimizer_key_uses */
675 },
676 {
677 "rows_estimation": [
678 {
679 "table": "`where_subselect_20070`",
680 "table_scan": {
681 "rows": 6,
682 "cost": 0.504761
683 } /* table_scan */
684 }
685 ] /* rows_estimation */
686 },
687 {
688 "considered_execution_plans": [
689 {
690 "plan_prefix": [
691 ] /* plan_prefix */,
692 "table": "`where_subselect_20070`",
693 "best_access_path": {
694 "considered_access_paths": [
695 {
696 "rows_to_scan": 6,
697 "access_type": "scan",
698 "resulting_rows": 6,
699 "cost": 1.10476,
700 "chosen": true
701 }
702 ] /* considered_access_paths */
703 } /* best_access_path */,
704 "condition_filtering_pct": 100,
705 "rows_for_plan": 6,
706 "cost_for_plan": 1.10476,
707 "chosen": true
708 }
709 ] /* considered_execution_plans */
710 },
711 {
712 "attaching_conditions_to_tables": {
713 "original_condition": "<in_optimizer>((`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)),<exists>(/* select#3 */ select `table2`.`col_int_key` AS `field1`,(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`) from `t1` `table1` join `t1` `table2` where ((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))))",
714 "attached_conditions_computation": [
715 ] /* attached_conditions_computation */,
716 "attached_conditions_summary": [
717 {
718 "table": "`where_subselect_20070`",
719 "attached": "<in_optimizer>((`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)),<exists>(/* select#3 */ select `table2`.`col_int_key` AS `field1`,(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`) from `t1` `table1` join `t1` `table2` where ((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))))"
720 }
721 ] /* attached_conditions_summary */
722 } /* attaching_conditions_to_tables */
723 },
724 {
725 "finalizing_table_conditions": [
726 {
727 "table": "`where_subselect_20070`",
728 "original_table_condition": "<in_optimizer>((`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)),<exists>(/* select#3 */ select `table2`.`col_int_key` AS `field1`,(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`) from `t1` `table1` join `t1` `table2` where ((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))))",
729 "final_table_condition ": "<in_optimizer>((`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)),<exists>(/* select#3 */ select `table2`.`col_int_key` AS `field1`,(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`) from `t1` `table1` join `t1` `table2` where ((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))))"
730 }
731 ] /* finalizing_table_conditions */
732 },
733 {
734 "refine_plan": [
735 {
736 "table": "`where_subselect_20070`"
737 }
738 ] /* refine_plan */
739 }
740 ] /* steps */
741 } /* join_optimization */
742 },
743 {
744 "join_optimization": {
745 "select#": 3,
746 "steps": [
747 {
748 "condition_processing": {
749 "condition": "WHERE",
750 "original_condition": "((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))",
751 "steps": [
752 {
753 "transformation": "equality_propagation",
754 "subselect_evaluation": [
755 ] /* subselect_evaluation */,
756 "resulting_condition": "((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and multiple equal(`table2`.`col_int_key`, `table1`.`col_int_key`))"
757 },
758 {
759 "transformation": "constant_propagation",
760 "subselect_evaluation": [
761 ] /* subselect_evaluation */,
762 "resulting_condition": "((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and multiple equal(`table2`.`col_int_key`, `table1`.`col_int_key`))"
763 },
764 {
765 "transformation": "trivial_condition_removal",
766 "subselect_evaluation": [
767 ] /* subselect_evaluation */,
768 "resulting_condition": "((<cache>(`where_subselect_20070`.`field1`) = `table2`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and multiple equal(`table2`.`col_int_key`, `table1`.`col_int_key`))"
769 }
770 ] /* steps */
771 } /* condition_processing */
772 },
773 {
774 "substitute_generated_columns": {
775 } /* substitute_generated_columns */
776 },
777 {
778 "table_dependencies": [
779 {
780 "table": "`t1` `table1`",
781 "row_may_be_null": false,
782 "map_bit": 0,
783 "depends_on_map_bits": [
784 ] /* depends_on_map_bits */
785 },
786 {
787 "table": "`t1` `table2`",
788 "row_may_be_null": false,
789 "map_bit": 1,
790 "depends_on_map_bits": [
791 ] /* depends_on_map_bits */
792 }
793 ] /* table_dependencies */
794 },
795 {
796 "ref_optimizer_key_uses": [
797 {
798 "table": "`t1` `table1`",
799 "field": "col_int_key",
800 "equals": "<cache>(`where_subselect_20070`.`field1`)",
801 "null_rejecting": true
802 },
803 {
804 "table": "`t1` `table1`",
805 "field": "col_int_key",
806 "equals": "`table2`.`col_int_key`",
807 "null_rejecting": true
808 },
809 {
810 "table": "`t1` `table2`",
811 "field": "col_int_key",
812 "equals": "<cache>(`where_subselect_20070`.`field1`)",
813 "null_rejecting": true
814 },
815 {
816 "table": "`t1` `table2`",
817 "field": "col_int_key",
818 "equals": "`table1`.`col_int_key`",
819 "null_rejecting": true
820 }
821 ] /* ref_optimizer_key_uses */
822 },
823 {
824 "rows_estimation": [
825 {
826 "table": "`t1` `table1`",
827 "table_scan": {
828 "rows": 4,
829 "cost": 0.501709
830 } /* table_scan */
831 },
832 {
833 "table": "`t1` `table2`",
834 "table_scan": {
835 "rows": 4,
836 "cost": 0.501709
837 } /* table_scan */
838 }
839 ] /* rows_estimation */
840 },
841 {
842 "considered_execution_plans": [
843 {
844 "plan_prefix": [
845 ] /* plan_prefix */,
846 "table": "`t1` `table1`",
847 "best_access_path": {
848 "considered_access_paths": [
849 {
850 "access_type": "ref",
851 "index": "col_int_key",
852 "rows": 2,
853 "cost": 0.455814,
854 "chosen": true
855 },
856 {
857 "access_type": "scan",
858 "cost": 0.901709,
859 "rows": 4,
860 "chosen": false,
861 "cause": "cost"
862 }
863 ] /* considered_access_paths */
864 } /* best_access_path */,
865 "condition_filtering_pct": 100,
866 "rows_for_plan": 2,
867 "cost_for_plan": 0.455814,
868 "rest_of_plan": [
869 {
870 "plan_prefix": [
871 "`t1` `table1`"
872 ] /* plan_prefix */,
873 "table": "`t1` `table2`",
874 "best_access_path": {
875 "considered_access_paths": [
876 {
877 "access_type": "ref",
878 "index": "col_int_key",
879 "rows": 2,
880 "cost": 0.911628,
881 "chosen": true
882 },
883 {
884 "rows_to_scan": 4,
885 "filtering_effect": [
886 ] /* filtering_effect */,
887 "final_filtering_effect": 1,
888 "access_type": "scan",
889 "using_join_cache": true,
890 "buffers_needed": 1,
891 "resulting_rows": 4,
892 "cost": 1.30173,
893 "chosen": false
894 }
895 ] /* considered_access_paths */
896 } /* best_access_path */,
897 "condition_filtering_pct": 100,
898 "rows_for_plan": 4,
899 "cost_for_plan": 1.36744,
900 "chosen": true
901 }
902 ] /* rest_of_plan */
903 },
904 {
905 "plan_prefix": [
906 ] /* plan_prefix */,
907 "table": "`t1` `table2`",
908 "best_access_path": {
909 "considered_access_paths": [
910 {
911 "access_type": "ref",
912 "index": "col_int_key",
913 "rows": 2,
914 "cost": 0.455814,
915 "chosen": true
916 },
917 {
918 "access_type": "scan",
919 "cost": 0.901709,
920 "rows": 4,
921 "chosen": false,
922 "cause": "cost"
923 }
924 ] /* considered_access_paths */
925 } /* best_access_path */,
926 "condition_filtering_pct": 100,
927 "rows_for_plan": 2,
928 "cost_for_plan": 0.455814,
929 "rest_of_plan": [
930 {
931 "plan_prefix": [
932 "`t1` `table2`"
933 ] /* plan_prefix */,
934 "table": "`t1` `table1`",
935 "best_access_path": {
936 "considered_access_paths": [
937 {
938 "access_type": "ref",
939 "index": "col_int_key",
940 "rows": 2,
941 "cost": 0.911628,
942 "chosen": true
943 },
944 {
945 "rows_to_scan": 4,
946 "filtering_effect": [
947 ] /* filtering_effect */,
948 "final_filtering_effect": 1,
949 "access_type": "scan",
950 "using_join_cache": true,
951 "buffers_needed": 1,
952 "resulting_rows": 4,
953 "cost": 1.30173,
954 "chosen": false
955 }
956 ] /* considered_access_paths */
957 } /* best_access_path */,
958 "condition_filtering_pct": 100,
959 "rows_for_plan": 4,
960 "cost_for_plan": 1.36744,
961 "pruned_by_cost": true
962 }
963 ] /* rest_of_plan */
964 }
965 ] /* considered_execution_plans */
966 },
967 {
968 "attaching_conditions_to_tables": {
969 "original_condition": "((`table2`.`col_int_key` = `table1`.`col_int_key`) and (<cache>(`where_subselect_20070`.`field1`) = `table1`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)))",
970 "attached_conditions_computation": [
971 ] /* attached_conditions_computation */,
972 "attached_conditions_summary": [
973 {
974 "table": "`t1` `table1`",
975 "attached": "((<cache>(`where_subselect_20070`.`field1`) = `table1`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)))"
976 },
977 {
978 "table": "`t1` `table2`",
979 "attached": "(`table2`.`col_int_key` = `table1`.`col_int_key`)"
980 }
981 ] /* attached_conditions_summary */
982 } /* attaching_conditions_to_tables */
983 },
984 {
985 "finalizing_table_conditions": [
986 {
987 "table": "`t1` `table1`",
988 "original_table_condition": "((<cache>(`where_subselect_20070`.`field1`) = `table1`.`col_int_key`) and (<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)))",
989 "final_table_condition ": "(<cache>((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`))"
990 },
991 {
992 "table": "`t1` `table2`",
993 "original_table_condition": "(`table2`.`col_int_key` = `table1`.`col_int_key`)",
994 "final_table_condition ": "(`table2`.`col_int_key` = `table1`.`col_int_key`)"
995 }
996 ] /* finalizing_table_conditions */
997 },
998 {
999 "refine_plan": [
1000 {
1001 "table": "`t1` `table1`"
1002 },
1003 {
1004 "table": "`t1` `table2`"
1005 }
1006 ] /* refine_plan */
1007 }
1008 ] /* steps */
1009 } /* join_optimization */
1010 },
1011 {
1012 "join_optimization": {
1013 "select#": 4,
1014 "steps": [
1015 {
1016 "table_dependencies": [
1017 {
1018 "table": "`t1`",
1019 "row_may_be_null": false,
1020 "map_bit": 0,
1021 "depends_on_map_bits": [
1022 ] /* depends_on_map_bits */
1023 }
1024 ] /* table_dependencies */
1025 },
1026 {
1027 "rows_estimation": [
1028 {
1029 "table": "`t1`",
1030 "table_scan": {
1031 "rows": 4,
1032 "cost": 0.501709
1033 } /* table_scan */
1034 }
1035 ] /* rows_estimation */
1036 },
1037 {
1038 "considered_execution_plans": [
1039 {
1040 "plan_prefix": [
1041 ] /* plan_prefix */,
1042 "table": "`t1`",
1043 "best_access_path": {
1044 "considered_access_paths": [
1045 {
1046 "rows_to_scan": 4,
1047 "access_type": "scan",
1048 "resulting_rows": 4,
1049 "cost": 0.901709,
1050 "chosen": true
1051 }
1052 ] /* considered_access_paths */
1053 } /* best_access_path */,
1054 "condition_filtering_pct": 100,
1055 "rows_for_plan": 4,
1056 "cost_for_plan": 0.901709,
1057 "chosen": true
1058 }
1059 ] /* considered_execution_plans */
1060 },
1061 {
1062 "attaching_conditions_to_tables": {
1063 "original_condition": null,
1064 "attached_conditions_computation": [
1065 ] /* attached_conditions_computation */,
1066 "attached_conditions_summary": [
1067 {
1068 "table": "`t1`",
1069 "attached": null
1070 }
1071 ] /* attached_conditions_summary */
1072 } /* attaching_conditions_to_tables */
1073 },
1074 {
1075 "optimizing_distinct_group_by_order_by": {
1076 } /* optimizing_distinct_group_by_order_by */
1077 },
1078 {
1079 "finalizing_table_conditions": [
1080 ] /* finalizing_table_conditions */
1081 },
1082 {
1083 "refine_plan": [
1084 {
1085 "table": "`t1`"
1086 }
1087 ] /* refine_plan */
1088 },
1089 {
1090 "considering_tmp_tables": [
1091 ] /* considering_tmp_tables */
1092 }
1093 ] /* steps */
1094 } /* join_optimization */
1095 },
1096 {
1097 "join_optimization": {
1098 "select#": 2,
1099 "steps": [
1100 {
1101 "table_dependencies": [
1102 {
1103 "table": "`t1`",
1104 "row_may_be_null": false,
1105 "map_bit": 0,
1106 "depends_on_map_bits": [
1107 ] /* depends_on_map_bits */
1108 }
1109 ] /* table_dependencies */
1110 },
1111 {
1112 "rows_estimation": [
1113 {
1114 "table": "`t1`",
1115 "table_scan": {
1116 "rows": 4,
1117 "cost": 0.501709
1118 } /* table_scan */
1119 }
1120 ] /* rows_estimation */
1121 },
1122 {
1123 "considered_execution_plans": [
1124 {
1125 "plan_prefix": [
1126 ] /* plan_prefix */,
1127 "table": "`t1`",
1128 "best_access_path": {
1129 "considered_access_paths": [
1130 {
1131 "rows_to_scan": 4,
1132 "access_type": "scan",
1133 "resulting_rows": 4,
1134 "cost": 0.901709,
1135 "chosen": true
1136 }
1137 ] /* considered_access_paths */
1138 } /* best_access_path */,
1139 "condition_filtering_pct": 100,
1140 "rows_for_plan": 4,
1141 "cost_for_plan": 0.901709,
1142 "chosen": true
1143 }
1144 ] /* considered_execution_plans */
1145 },
1146 {
1147 "attaching_conditions_to_tables": {
1148 "original_condition": null,
1149 "attached_conditions_computation": [
1150 ] /* attached_conditions_computation */,
1151 "attached_conditions_summary": [
1152 {
1153 "table": "`t1`",
1154 "attached": null
1155 }
1156 ] /* attached_conditions_summary */
1157 } /* attaching_conditions_to_tables */
1158 },
1159 {
1160 "optimizing_distinct_group_by_order_by": {
1161 } /* optimizing_distinct_group_by_order_by */
1162 },
1163 {
1164 "finalizing_table_conditions": [
1165 ] /* finalizing_table_conditions */
1166 },
1167 {
1168 "refine_plan": [
1169 {
1170 "table": "`t1`"
1171 }
1172 ] /* refine_plan */
1173 },
1174 {
1175 "considering_tmp_tables": [
1176 ] /* considering_tmp_tables */
1177 }
1178 ] /* steps */
1179 } /* join_optimization */
1180 },
1181 {
1182 "join_execution": {
1183 "select#": 1,
1184 "steps": [
1185 {
1186 "subselect_execution": {
1187 "select#": 2,
1188 "steps": [
1189 {
1190 "join_execution": {
1191 "select#": 2,
1192 "steps": [
1193 ] /* steps */
1194 } /* join_execution */
1195 }
1196 ] /* steps */
1197 } /* subselect_execution */
1198 },
1199 {
1200 "subselect_execution": {
1201 "select#": 2,
1202 "steps": [
1203 ] /* steps */
1204 } /* subselect_execution */
1205 },
1206 {
1207 "subselect_execution": {
1208 "select#": 3,
1209 "steps": [
1210 {
1211 "join_execution": {
1212 "select#": 3,
1213 "steps": [
1214 {
1215 "subselect_execution": {
1216 "select#": 4,
1217 "steps": [
1218 {
1219 "join_execution": {
1220 "select#": 4,
1221 "steps": [
1222 ] /* steps */
1223 } /* join_execution */
1224 }
1225 ] /* steps */
1226 } /* subselect_execution */
1227 }
1228 ] /* steps */
1229 } /* join_execution */
1230 }
1231 ] /* steps */
1232 } /* subselect_execution */
1233 },
1234 {
1235 "subselect_execution": {
1236 "select#": 2,
1237 "steps": [
1238 ] /* steps */
1239 } /* subselect_execution */
1240 },
1241 {
1242 "subselect_execution": {
1243 "select#": 2,
1244 "steps": [
1245 ] /* steps */
1246 } /* subselect_execution */
1247 },
1248 {
1249 "subselect_execution": {
1250 "select#": 3,
1251 "steps": [
1252 {
1253 "join_execution": {
1254 "select#": 3,
1255 "steps": [
1256 {
1257 "subselect_execution": {
1258 "select#": 4,
1259 "steps": [
1260 ] /* steps */
1261 } /* subselect_execution */
1262 }
1263 ] /* steps */
1264 } /* join_execution */
1265 }
1266 ] /* steps */
1267 } /* subselect_execution */
1268 },
1269 {
1270 "subselect_execution": {
1271 "select#": 2,
1272 "steps": [
1273 ] /* steps */
1274 } /* subselect_execution */
1275 },
1276 {
1277 "subselect_execution": {
1278 "select#": 2,
1279 "steps": [
1280 ] /* steps */
1281 } /* subselect_execution */
1282 },
1283 {
1284 "subselect_execution": {
1285 "select#": 3,
1286 "steps": [
1287 {
1288 "join_execution": {
1289 "select#": 3,
1290 "steps": [
1291 {
1292 "subselect_execution": {
1293 "select#": 4,
1294 "steps": [
1295 ] /* steps */
1296 } /* subselect_execution */
1297 }
1298 ] /* steps */
1299 } /* join_execution */
1300 }
1301 ] /* steps */
1302 } /* subselect_execution */
1303 },
1304 {
1305 "subselect_execution": {
1306 "select#": 2,
1307 "steps": [
1308 ] /* steps */
1309 } /* subselect_execution */
1310 },
1311 {
1312 "subselect_execution": {
1313 "select#": 2,
1314 "steps": [
1315 ] /* steps */
1316 } /* subselect_execution */
1317 },
1318 {
1319 "subselect_execution": {
1320 "select#": 3,
1321 "steps": [
1322 {
1323 "join_execution": {
1324 "select#": 3,
1325 "steps": [
1326 {
1327 "subselect_execution": {
1328 "select#": 4,
1329 "steps": [
1330 ] /* steps */
1331 } /* subselect_execution */
1332 }
1333 ] /* steps */
1334 } /* join_execution */
1335 }
1336 ] /* steps */
1337 } /* subselect_execution */
1338 },
1339 {
1340 "subselect_execution": {
1341 "select#": 2,
1342 "steps": [
1343 ] /* steps */
1344 } /* subselect_execution */
1345 },
1346 {
1347 "subselect_execution": {
1348 "select#": 2,
1349 "steps": [
1350 ] /* steps */
1351 } /* subselect_execution */
1352 },
1353 {
1354 "subselect_execution": {
1355 "select#": 3,
1356 "steps": [
1357 {
1358 "join_execution": {
1359 "select#": 3,
1360 "steps": [
1361 {
1362 "subselect_execution": {
1363 "select#": 4,
1364 "steps": [
1365 ] /* steps */
1366 } /* subselect_execution */
1367 }
1368 ] /* steps */
1369 } /* join_execution */
1370 }
1371 ] /* steps */
1372 } /* subselect_execution */
1373 },
1374 {
1375 "subselect_execution": {
1376 "select#": 2,
1377 "steps": [
1378 ] /* steps */
1379 } /* subselect_execution */
1380 },
1381 {
1382 "subselect_execution": {
1383 "select#": 2,
1384 "steps": [
1385 ] /* steps */
1386 } /* subselect_execution */
1387 },
1388 {
1389 "subselect_execution": {
1390 "select#": 3,
1391 "steps": [
1392 {
1393 "join_execution": {
1394 "select#": 3,
1395 "steps": [
1396 {
1397 "subselect_execution": {
1398 "select#": 4,
1399 "steps": [
1400 ] /* steps */
1401 } /* subselect_execution */
1402 }
1403 ] /* steps */
1404 } /* join_execution */
1405 }
1406 ] /* steps */
1407 } /* subselect_execution */
1408 }
1409 ] /* steps */
1410 } /* join_execution */
1411 }
1412 ] /* steps */
1413 } 0 0
1414 DROP TABLE where_subselect_20070,t1;
1415 #
1416 # Bug#13430443 - ASSERTION `NEW_TYPE[0] != 'U'' FAILED. WHEN
1417 # OPTIMIZER_TRACE IS ENABLED
1418 #
1419 CREATE TABLE t1
1420 (a INT,b INT,c INT, KEY(a),KEY (a,c)) ENGINE=INNODB;
1421 SELECT 1 FROM t1 WHERE 1 LIKE
1422 (SELECT a FROM t1 WHERE a = 1 ORDER BY c);
1423 1
1424 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1425 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1426 SELECT 1 FROM t1 WHERE 1 LIKE
1427 (SELECT a FROM t1 WHERE a = 1 ORDER BY c) {
1428 "steps": [
1429 {
1430 "join_optimization": {
1431 "select#": 1,
1432 "steps": [
1433 {
1434 "condition_processing": {
1435 "condition": "WHERE",
1436 "original_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))",
1437 "steps": [
1438 {
1439 "transformation": "equality_propagation",
1440 "subselect_evaluation": [
1441 ] /* subselect_evaluation */,
1442 "resulting_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))"
1443 },
1444 {
1445 "transformation": "constant_propagation",
1446 "subselect_evaluation": [
1447 ] /* subselect_evaluation */,
1448 "resulting_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))"
1449 },
1450 {
1451 "transformation": "trivial_condition_removal",
1452 "subselect_evaluation": [
1453 {
1454 "subselect_execution": {
1455 "select#": 2,
1456 "steps": [
1457 {
1458 "join_optimization": {
1459 "select#": 2,
1460 "steps": [
1461 {
1462 "condition_processing": {
1463 "condition": "WHERE",
1464 "original_condition": "(`t1`.`a` = 1)",
1465 "steps": [
1466 {
1467 "transformation": "equality_propagation",
1468 "resulting_condition": "multiple equal(1, `t1`.`a`)"
1469 },
1470 {
1471 "transformation": "constant_propagation",
1472 "resulting_condition": "multiple equal(1, `t1`.`a`)"
1473 },
1474 {
1475 "transformation": "trivial_condition_removal",
1476 "resulting_condition": "multiple equal(1, `t1`.`a`)"
1477 }
1478 ] /* steps */
1479 } /* condition_processing */
1480 },
1481 {
1482 "substitute_generated_columns": {
1483 } /* substitute_generated_columns */
1484 },
1485 {
1486 "table_dependencies": [
1487 {
1488 "table": "`t1`",
1489 "row_may_be_null": false,
1490 "map_bit": 0,
1491 "depends_on_map_bits": [
1492 ] /* depends_on_map_bits */
1493 }
1494 ] /* table_dependencies */
1495 },
1496 {
1497 "ref_optimizer_key_uses": [
1498 {
1499 "table": "`t1`",
1500 "field": "a",
1501 "equals": "1",
1502 "null_rejecting": true
1503 },
1504 {
1505 "table": "`t1`",
1506 "field": "a",
1507 "equals": "1",
1508 "null_rejecting": true
1509 }
1510 ] /* ref_optimizer_key_uses */
1511 },
1512 {
1513 "rows_estimation": [
1514 {
1515 "table": "`t1`",
1516 "range_analysis": {
1517 "table_scan": {
1518 "rows": 1,
1519 "cost": 2.45
1520 } /* table_scan */,
1521 "potential_range_indexes": [
1522 {
1523 "index": "a",
1524 "usable": true,
1525 "key_parts": [
1526 "a"
1527 ] /* key_parts */
1528 },
1529 {
1530 "index": "a_2",
1531 "usable": true,
1532 "key_parts": [
1533 "a",
1534 "c"
1535 ] /* key_parts */
1536 }
1537 ] /* potential_range_indexes */,
1538 "best_covering_index_scan": {
1539 "index": "a_2",
1540 "cost": 0.35,
1541 "chosen": true
1542 } /* best_covering_index_scan */,
1543 "setup_range_conditions": [
1544 ] /* setup_range_conditions */,
1545 "group_index_range": {
1546 "chosen": false,
1547 "cause": "not_group_by_or_distinct"
1548 } /* group_index_range */,
1549 "skip_scan_range": {
1550 "potential_skip_scan_indexes": [
1551 {
1552 "index": "a",
1553 "usable": false,
1554 "cause": "query_references_nonkey_column"
1555 },
1556 {
1557 "index": "a_2",
1558 "usable": false,
1559 "cause": "no_range_predicate"
1560 }
1561 ] /* potential_skip_scan_indexes */
1562 } /* skip_scan_range */,
1563 "analyzing_range_alternatives": {
1564 "range_scan_alternatives": [
1565 {
1566 "index": "a",
1567 "ranges": [
1568 "a = 1"
1569 ] /* ranges */,
1570 "index_dives_for_eq_ranges": true,
1571 "rowid_ordered": true,
1572 "using_mrr": false,
1573 "index_only": false,
1574 "in_memory": 1,
1575 "rows": 1,
1576 "cost": 0.61,
1577 "chosen": false,
1578 "cause": "cost"
1579 },
1580 {
1581 "index": "a_2",
1582 "ranges": [
1583 "a = 1"
1584 ] /* ranges */,
1585 "index_dives_for_eq_ranges": true,
1586 "rowid_ordered": false,
1587 "using_mrr": false,
1588 "index_only": true,
1589 "in_memory": 1,
1590 "rows": 1,
1591 "cost": 0.36,
1592 "chosen": false,
1593 "cause": "cost"
1594 }
1595 ] /* range_scan_alternatives */,
1596 "analyzing_roworder_intersect": {
1597 "usable": false,
1598 "cause": "too_few_roworder_scans"
1599 } /* analyzing_roworder_intersect */
1600 } /* analyzing_range_alternatives */
1601 } /* range_analysis */
1602 }
1603 ] /* rows_estimation */
1604 },
1605 {
1606 "considered_execution_plans": [
1607 {
1608 "plan_prefix": [
1609 ] /* plan_prefix */,
1610 "table": "`t1`",
1611 "best_access_path": {
1612 "considered_access_paths": [
1613 {
1614 "access_type": "ref",
1615 "index": "a",
1616 "rows": 1,
1617 "cost": 0.35,
1618 "chosen": true
1619 },
1620 {
1621 "access_type": "ref",
1622 "index": "a_2",
1623 "rows": 1,
1624 "cost": 0.35,
1625 "chosen": false
1626 },
1627 {
1628 "access_type": "scan",
1629 "chosen": false,
1630 "cause": "covering_index_better_than_full_scan"
1631 }
1632 ] /* considered_access_paths */
1633 } /* best_access_path */,
1634 "condition_filtering_pct": 100,
1635 "rows_for_plan": 1,
1636 "cost_for_plan": 0.35,
1637 "chosen": true
1638 }
1639 ] /* considered_execution_plans */
1640 },
1641 {
1642 "attaching_conditions_to_tables": {
1643 "original_condition": "(`t1`.`a` = 1)",
1644 "attached_conditions_computation": [
1645 ] /* attached_conditions_computation */,
1646 "attached_conditions_summary": [
1647 {
1648 "table": "`t1`",
1649 "attached": "(`t1`.`a` = 1)"
1650 }
1651 ] /* attached_conditions_summary */
1652 } /* attaching_conditions_to_tables */
1653 },
1654 {
1655 "finalizing_table_conditions": [
1656 {
1657 "table": "`t1`",
1658 "original_table_condition": "(`t1`.`a` = 1)",
1659 "final_table_condition ": null
1660 }
1661 ] /* finalizing_table_conditions */
1662 },
1663 {
1664 "refine_plan": [
1665 {
1666 "table": "`t1`"
1667 }
1668 ] /* refine_plan */
1669 }
1670 ] /* steps */
1671 } /* join_optimization */
1672 },
1673 {
1674 "join_execution": {
1675 "select#": 2,
1676 "steps": [
1677 ] /* steps */
1678 } /* join_execution */
1679 }
1680 ] /* steps */
1681 } /* subselect_execution */
1682 }
1683 ] /* subselect_evaluation */,
1684 "resulting_condition": null
1685 }
1686 ] /* steps */
1687 } /* condition_processing */
1688 }
1689 ] /* steps */,
1690 "empty_result": {
1691 "cause": "Impossible WHERE"
1692 } /* empty_result */
1693 } /* join_optimization */
1694 },
1695 {
1696 "join_execution": {
1697 "select#": 1,
1698 "steps": [
1699 ] /* steps */
1700 } /* join_execution */
1701 }
1702 ] /* steps */
1703 } 0 0
1704 DROP TABLE t1;
1705 #
1706 # Bug #18346750 OPTIMIZER_TRACE & DBUG_PRINT CRASH IN ST_SELECT_LEX::PRINT
1707 #
1708 "Skipped since this engine does not support native partitioning.
1709 #
1710 # Bug#18791851 CRASH IN ST_SELECT_LEX::PRINT WITH OPTIMIZER_TRACE ON SUBQUERY
1711 #
1712 CREATE TABLE t1 (
1713 pk INT NOT NULL,
1714 col_int_nokey INT,
1715 col_int_key INT,
1716 col_time_key time,
1717 col_varchar_key VARCHAR(1),
1718 col_varchar_nokey VARCHAR(1),
1719 PRIMARY KEY (pk),
1720 KEY col_varchar_key (col_varchar_key,col_int_key)
1721 );
1722 CREATE TABLE t2 (
1723 pk INT NOT NULL,
1724 col_int_nokey INT,
1725 col_int_key INT,
1726 col_varchar_key VARCHAR(1),
1727 col_varchar_nokey VARCHAR(1),
1728 PRIMARY KEY (pk),
1729 KEY col_varchar_key (col_varchar_key,col_int_key)
1730 );
1731 CREATE TABLE t3 (
1732 pk INT NOT NULL,
1733 col_int_nokey INT,
1734 col_int_key INT,
1735 col_varchar_key VARCHAR(1),
1736 col_varchar_nokey VARCHAR(1),
1737 PRIMARY KEY (pk),
1738 KEY col_varchar_key (col_varchar_key,col_int_key)
1739 );
1740 CREATE TABLE t4 (
1741 pk INT NOT NULL,
1742 col_int_nokey INT,
1743 col_int_key INT,
1744 col_time_key time,
1745 col_varchar_key VARCHAR(1),
1746 col_varchar_nokey VARCHAR(1),
1747 PRIMARY KEY (pk),
1748 KEY col_varchar_key (col_varchar_key,col_int_key)
1749 );
1750 SELECT alias1.col_int_nokey AS field1,
1751 alias2.col_varchar_key AS field2,
1752 alias1.col_time_key AS field3,
1753 MIN( alias1.col_int_nokey ) AS field4
1754 FROM (
1755 (
1756 SELECT SQ1_alias1.*
1757 FROM ( t1 AS SQ1_alias1, t2 AS SQ1_alias2 )
1758 ) AS alias1,
1759 t4 AS alias2
1760 )
1761 WHERE
1762 EXISTS (
1763 SELECT DISTINCT SQ2_alias2.col_varchar_nokey AS SQ2_field1
1764 FROM t2 AS SQ2_alias1
1765 INNER JOIN (t4 AS SQ2_alias2
1766 INNER JOIN t3 AS SQ2_alias3
1767 ON SQ2_alias3.pk = SQ2_alias2.pk)
1768 ON SQ2_alias3.col_varchar_key = SQ2_alias2.col_varchar_nokey
1769 )
1770 AND alias1.col_int_key = alias2.pk
1771 HAVING alias1.col_int_nokey IN ( SELECT 2 FROM DUAL ) ;
1772 field1 field2 field3 field4
1773 DROP TABLE t1,t2,t3,t4;
1774 CREATE TABLE t1(a INT);
1775 SET @a:=(SELECT ROW(1, 2)=
1776 ROW((SELECT 1 FROM t1 LEFT JOIN t1 t2 ON 1
1777 HAVING 3 IN (SELECT 2 FROM DUAL)),
1778 1));
1779 DROP TABLE t1;
1780 #
1781 # Bug #18945693 CRASH IN PRINT_TABLE_ARRAY AT SQL/SQL_LEX.CC ON 2ND EXEC OF PREPARED STATEMENT
1782 #
1783 CREATE TABLE t1 (
1784 pk INT NOT NULL,
1785 col_int_nokey INT,
1786 col_int_key INT,
1787 col_varchar_key VARCHAR(1),
1788 col_varchar_nokey VARCHAR(1)
1789 );
1790 INSERT INTO t1 VALUES (13,7,3,'y','y'),(14,0,4,'c','c');
1791 CREATE TABLE t2 (
1792 pk INT NOT NULL,
1793 col_int_nokey INT,
1794 col_int_key INT,
1795 col_varchar_key VARCHAR(1),
1796 col_varchar_nokey VARCHAR(1)
1797 );
1798 INSERT INTO t2 VALUES (20,9,8,'e','e');
1799 CREATE TABLE t3 (
1800 pk INT NOT NULL,
1801 col_int_nokey INT,
1802 col_int_key INT,
1803 col_varchar_key VARCHAR(1),
1804 col_varchar_nokey VARCHAR(1)
1805 );
1806 INSERT INTO t3 VALUES (1,1,7,'k','k');
1807 PREPARE prep_stmt FROM "
1808 SELECT
1809 alias1.col_varchar_nokey AS field1,
1810 MAX(alias1.pk) AS field2
1811 FROM t3 AS alias1
1812 GROUP BY field1
1813 HAVING
1814 1 > (
1815 SELECT MAX(SQ4_alias1.col_varchar_nokey)
1816 FROM t1 AS SQ4_alias1
1817 RIGHT OUTER JOIN
1818 (
1819 t2 AS SQ4_alias2
1820 JOIN t1 AS SQ4_alias3
1821 ON SQ4_alias3.col_int_key = SQ4_alias2.col_int_nokey
1822 )
1823 ON SQ4_alias3.pk = SQ4_alias2.col_int_nokey
1824 )
1825 ";
1826 EXECUTE prep_stmt;
1827 field1 field2
1828 EXECUTE prep_stmt;
1829 field1 field2
1830 DROP TABLE t1,t2,t3;
1831 #
1832 # Bug#19063289 CRASH IN PRINT_TABLE_ARRAY ON 2ND EXECUTION OF PS WITH SUBQUERY AND VIEW
1833 #
1834 CREATE TABLE B (
1835 pk INTEGER,
1836 col_int_key INTEGER,
1837 col_varchar_nokey VARCHAR(1)
1838 );
1839 CREATE TABLE C (
1840 col_int_nokey INTEGER,
1841 col_int_key INTEGER,
1842 col_varchar_key VARCHAR(1),
1843 col_varchar_nokey VARCHAR(1)
1844 ) ;
1845 CREATE TABLE CC (
1846 pk INTEGER,
1847 col_int_key INTEGER
1848 ) ENGINE=MYISAM;
1849 INSERT INTO CC VALUES (1, 0),(2, 7);
1850 PREPARE stmt FROM "
1851 SELECT
1852 alias1.col_int_key AS field1 ,
1853 COUNT(alias1.pk) AS field2
1854 FROM
1855 CC AS alias1
1856 GROUP BY field1
1857 HAVING
1858 alias1.col_int_key >
1859 (
1860 SELECT
1861 MAX( SQ2_alias2.col_int_key ) AS SQ2_field1
1862 FROM
1863 C AS SQ2_alias1 LEFT JOIN
1864 ( B AS SQ2_alias2 INNER JOIN C AS SQ2_alias3
1865 ON 1)
1866 ON 1
1867 )
1868 OR field2 < 2
1869 ";
1870 EXECUTE stmt;
1871 field1 field2
1872 0 1
1873 7 1
1874 EXECUTE stmt;
1875 field1 field2
1876 0 1
1877 7 1
1878 DROP TABLE B,C,CC;
1879 #
1880 # Bug #23259872: OPTIMIZER CHOOSES TO USE NON PRIMARY
1881 # INDEX, EVEN THOUGH COST IS HIGHER
1882 #
1883 CREATE TABLE t1 (
1884 a TINYTEXT NOT NULL,
1885 b TINYINT(3) UNSIGNED NOT NULL,
1886 PRIMARY KEY (a(32),b)
1887 ) ENGINE=MyISAM, CHARSET utf8mb4;
1888 Warnings:
1889 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1890 CREATE TABLE t2 (
1891 a TINYTEXT NOT NULL,
1892 b TINYINT(3) UNSIGNED NOT NULL,
1893 PRIMARY KEY (a(32),b)
1894 ) ENGINE=INNODB, CHARSET utf8mb4;
1895 Warnings:
1896 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1897 INSERT INTO t1 VALUES ('a',1),('a',2);
1898 INSERT INTO t2 VALUES ('a',1),('a',2),('a',3),('b',1),('c',1),('c',4),('e',2);
1899 ANALYZE TABLE t1;
1900 Table Op Msg_type Msg_text
1901 test.t1 analyze status OK
1902 ANALYZE TABLE t2;
1903 Table Op Msg_type Msg_text
1904 test.t2 analyze status OK
1905 EXPLAIN SELECT * FROM t1 WHERE a='a' AND b in (1,2);
1906 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1907 1 SIMPLE t1 NULL range PRIMARY PRIMARY 131 NULL 2 100.00 Using where
1908 Warnings:
1909 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 'a') and (`test`.`t1`.`b` in (1,2)))
1910 SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1911 SELECT @trace RLIKE "range_uses_more_keyparts";
1912 @trace RLIKE "range_uses_more_keyparts"
1913 1
1914 EXPLAIN SELECT * FROM t2 WHERE a='a' AND b in (1,2);
1915 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1916 1 SIMPLE t2 NULL range PRIMARY PRIMARY 131 NULL 2 100.00 Using where
1917 Warnings:
1918 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`a` = 'a') and (`test`.`t2`.`b` in (1,2)))
1919 SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1920 SELECT @trace RLIKE "range_uses_more_keyparts";
1921 @trace RLIKE "range_uses_more_keyparts"
1922 1
1923 DROP TABLE t1, t2;
1924 #
1925 # Bug #23227428: SQL PLAN IS NOT ACCORDING WITH OPTIMIZER_TRACE
1926 #
1927 CREATE TABLE t1(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1928 c2 CHAR(12)) ENGINE=INNODB;
1929 ANALYZE TABLE t1;
1930 Table Op Msg_type Msg_text
1931 test.t1 analyze status OK
1932 EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1;
1933 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1934 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 1 100.00 Backward index scan
1935 Warnings:
1936 Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` order by `test`.`t1`.`c1` desc limit 1
1937 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1938 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1939 EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1 {
1940 "steps": [
1941 {
1942 "join_preparation": {
1943 "select#": 1,
1944 "steps": [
1945 {
1946 "expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` order by `t1`.`c1` desc limit 1"
1947 }
1948 ] /* steps */
1949 } /* join_preparation */
1950 },
1951 {
1952 "join_optimization": {
1953 "select#": 1,
1954 "steps": [
1955 {
1956 "substitute_generated_columns": {
1957 } /* substitute_generated_columns */
1958 },
1959 {
1960 "table_dependencies": [
1961 {
1962 "table": "`t1`",
1963 "row_may_be_null": false,
1964 "map_bit": 0,
1965 "depends_on_map_bits": [
1966 ] /* depends_on_map_bits */
1967 }
1968 ] /* table_dependencies */
1969 },
1970 {
1971 "rows_estimation": [
1972 {
1973 "table": "`t1`",
1974 "table_scan": {
1975 "rows": 1000,
1976 "cost": NN
1977 } /* table_scan */
1978 }
1979 ] /* rows_estimation */
1980 },
1981 {
1982 "considered_execution_plans": [
1983 {
1984 "plan_prefix": [
1985 ] /* plan_prefix */,
1986 "table": "`t1`",
1987 "best_access_path": {
1988 "considered_access_paths": [
1989 {
1990 "rows_to_scan": 1000,
1991 "access_type": "scan",
1992 "resulting_rows": 1000,
1993 "cost": NN,
1994 "chosen": true
1995 }
1996 ] /* considered_access_paths */
1997 } /* best_access_path */,
1998 "condition_filtering_pct": 100,
1999 "rows_for_plan": 1000,
2000 "cost_for_plan": NN,
2001 "chosen": true
2002 }
2003 ] /* considered_execution_plans */
2004 },
2005 {
2006 "attaching_conditions_to_tables": {
2007 "original_condition": null,
2008 "attached_conditions_computation": [
2009 ] /* attached_conditions_computation */,
2010 "attached_conditions_summary": [
2011 {
2012 "table": "`t1`",
2013 "attached": null
2014 }
2015 ] /* attached_conditions_summary */
2016 } /* attaching_conditions_to_tables */
2017 },
2018 {
2019 "optimizing_distinct_group_by_order_by": {
2020 "simplifying_order_by": {
2021 "original_clause": "`t1`.`c1` desc",
2022 "items": [
2023 {
2024 "item": "`t1`.`c1`"
2025 }
2026 ] /* items */,
2027 "resulting_clause_is_simple": true,
2028 "resulting_clause": "`t1`.`c1` desc"
2029 } /* simplifying_order_by */
2030 } /* optimizing_distinct_group_by_order_by */
2031 },
2032 {
2033 "reconsidering_access_paths_for_index_ordering": {
2034 "clause": "ORDER BY",
2035 "steps": [
2036 ] /* steps */,
2037 "index_order_summary": {
2038 "table": "`t1`",
2039 "index_provides_order": true,
2040 "order_direction": "desc",
2041 "index": "PRIMARY",
2042 "plan_changed": true,
2043 "access_type": "index"
2044 } /* index_order_summary */
2045 } /* reconsidering_access_paths_for_index_ordering */
2046 },
2047 {
2048 "finalizing_table_conditions": [
2049 ] /* finalizing_table_conditions */
2050 },
2051 {
2052 "refine_plan": [
2053 {
2054 "table": "`t1`"
2055 }
2056 ] /* refine_plan */
2057 },
2058 {
2059 "considering_tmp_tables": [
2060 ] /* considering_tmp_tables */
2061 }
2062 ] /* steps */
2063 } /* join_optimization */
2064 },
2065 {
2066 "join_explain": {
2067 "select#": 1,
2068 "steps": [
2069 ] /* steps */
2070 } /* join_explain */
2071 }
2072 ] /* steps */
2073 } 0 0
2074 DROP TABLE t1;
2075 # Bug#29016383 - fil_report_invalid_page_access
2076 CREATE TABLE t1 (
2077 pk INTEGER PRIMARY KEY,
2078 a INTEGER,
2079 b VARCHAR(20)
2080 );
2081 CREATE TABLE t2 (
2082 pk INTEGER PRIMARY KEY,
2083 c VARCHAR(20),
2084 KEY (c)
2085 );
2086 INSERT INTO t2 VALUES
2087 (1,'koeiwsgpmfyvvuqvtjnc'),
2088 (2,'pmfyvvuqvtjncdsvq'),
2089 (3,'mfyv'),
2090 (4,NULL);
2091 SET end_markers_in_json=off;
2092 SELECT alias1.*
2093 FROM t2 AS alias1, t2 AS alias2
2094 WHERE alias2.c IN (
2095 WITH qn AS (SELECT b FROM t1 WHERE a = alias1.pk)
2096 SELECT * FROM qn);
2097 pk c
2098 # Prove that the predicate from the WHERE clause of the CTE
2099 # is decorrelated:
2100 SELECT json_extract(trace,'$**.decorrelated_predicates')
2101 FROM information_schema.optimizer_trace;
2102 json_extract(trace,'$**.decorrelated_predicates')
2103 NULL
2104 DROP TABLE t1, t2;