Debian Codegraph @

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

raw
1 set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off';
2 set optimizer_trace_max_mem_size=1048576;
3 SET end_markers_in_json=on;
4 SET optimizer_trace="enabled=on,one_line=off";
5 #
6 # BUG#12430646 - SEL_ARG::LEFT AND RIGHT POINTERS INCORRECTLY
7 # USED. CRASHES OPTIMIZER TRACING
8 #
9 CREATE TABLE t1 (
10 a INT,
11 b CHAR(2),
12 c INT,
13 d INT,
14 KEY (c),
15 KEY (d,a,b(2)),
16 KEY (b(1))
17 );
18 INSERT INTO t1 VALUES (NULL, 'a', 1, 2), (NULL, 'a', 1, 2),
19 (1, 'a', 1, 2), (1, 'a', 1, 2);
20 CREATE TABLE t2 (
21 a INT,
22 c INT,
23 e INT,
24 KEY (e)
25 );
26 INSERT INTO t2 VALUES (1, 1, NULL), (1, 1, NULL);
27 SELECT 1
28 FROM t1, t2
29 WHERE t1.d <> '1' AND t1.b > '1'
30 AND t1.a = t2.a AND t1.c = t2.c;
31 1
32 1
33 1
34 1
35 1
36 DROP TABLE t1, t2;
37 #
38 # BUG#12595210 - JSON SYNTAX ERROR ASSERT ON WHERE FIELD NOT IN SUBQUERY
39 #
40 CREATE TABLE t1 (
41 pk INT NOT NULL AUTO_INCREMENT,
42 col_int_key INT DEFAULT NULL,
43 col_varchar_key VARCHAR(1) DEFAULT NULL,
44 PRIMARY KEY (pk)
45 ) ENGINE=InnoDB, CHARSET utf8mb4;
46 CREATE TABLE t2 (
47 pk INT NOT NULL AUTO_INCREMENT,
48 col_int_key INT DEFAULT NULL,
49 col_varchar_key VARCHAR(1) DEFAULT NULL,
50 col_varchar_nokey VARCHAR(1) DEFAULT NULL,
51 PRIMARY KEY (pk)
52 ) ENGINE=InnoDB, CHARSET utf8mb4;
53 CREATE TABLE t3 (
54 pk INT NOT NULL AUTO_INCREMENT,
55 col_int_key INT DEFAULT NULL,
56 col_varchar_key VARCHAR(1) DEFAULT NULL,
57 col_varchar_nokey VARCHAR(1) DEFAULT NULL,
58 PRIMARY KEY (pk)
59 ) ENGINE=InnoDB, CHARSET utf8mb4;
60 CREATE TABLE t4 (
61 pk INT NOT NULL AUTO_INCREMENT,
62 col_int_key INT DEFAULT NULL,
63 col_varchar_key VARCHAR(1) DEFAULT NULL,
64 PRIMARY KEY (pk)
65 ) ENGINE=InnoDB, CHARSET utf8mb4;
66 CREATE VIEW view_t4 AS SELECT * FROM t4;
67 CREATE TABLE where_subselect_19379 SELECT
68 (
69 SELECT SUM(sq1_alias1.pk) AS sq1_field1
70 FROM view_t4 AS sq1_alias1
71 INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
72 sq1_alias1.col_varchar_key )
73 ) AS field1,
74 alias1.col_varchar_nokey AS field2
75 FROM (t2 AS alias1
76 LEFT JOIN ( t2 AS alias2
77 LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
78 alias2.col_varchar_key )
79 ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
80 )
81 WHERE ( alias2.col_varchar_key IN (
82 SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
83 FROM t3 AS sq2_alias1
84 WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
85 )
86 );
87 SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
88 (SELECT
89 (
90 SELECT SUM(sq1_alias1.pk) AS sq1_field1
91 FROM view_t4 AS sq1_alias1
92 INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
93 sq1_alias1.col_varchar_key )
94 ) AS field1,
95 alias1.col_varchar_nokey AS field2
96 FROM (t2 AS alias1
97 LEFT JOIN ( t2 AS alias2
98 LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
99 alias2.col_varchar_key )
100 ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
101 )
102 WHERE ( alias2.col_varchar_key IN (
103 SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
104 FROM t3 AS sq2_alias1
105 WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
106 )
107 ));
108 field1 field2
109 select * from information_schema.OPTIMIZER_TRACE;
110 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
111 SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
112 (SELECT
113 (
114 SELECT SUM(sq1_alias1.pk) AS sq1_field1
115 FROM view_t4 AS sq1_alias1
116 INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
117 sq1_alias1.col_varchar_key )
118 ) AS field1,
119 alias1.col_varchar_nokey AS field2
120 FROM (t2 AS alias1
121 LEFT JOIN ( t2 AS alias2
122 LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
123 alias2.col_varchar_key )
124 ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
125 )
126 WHERE ( alias2.col_varchar_key IN (
127 SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
128 FROM t3 AS sq2_alias1
129 WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
130 )
131 )) {
132 "steps": [
133 {
134 "join_preparation": {
135 "select#": 1,
136 "steps": [
137 {
138 "join_preparation": {
139 "select#": 2,
140 "steps": [
141 {
142 "join_preparation": {
143 "select#": 3,
144 "steps": [
145 {
146 "join_preparation": {
147 "select#": 5,
148 "steps": [
149 {
150 "expanded_query": "/* select#5 */ select `t4`.`pk` AS `pk`,`t4`.`col_int_key` AS `col_int_key`,`t4`.`col_varchar_key` AS `col_varchar_key` from `t4`"
151 }
152 ] /* steps */
153 } /* join_preparation */
154 },
155 {
156 "view": {
157 "table": "`view_t4` `sq1_alias1`",
158 "select#": 5,
159 "merged": true
160 } /* view */
161 },
162 {
163 "expanded_query": "/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from ((`t4`) join `t1` `sq1_alias2` on((`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))"
164 }
165 ] /* steps */
166 } /* join_preparation */
167 },
168 {
169 "join_preparation": {
170 "select#": 4,
171 "steps": [
172 {
173 "expanded_query": "/* 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`)"
174 }
175 ] /* steps */
176 } /* join_preparation */
177 },
178 {
179 "expanded_query": "/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from ((`t4`) join `t1` `sq1_alias2` on((`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from (`t2` `alias1` left join (`t2` `alias2` left join `t2` `alias3` on((`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`))) on((`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`))) where `alias2`.`col_varchar_key` in (/* 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`))"
180 },
181 {
182 "transformation": {
183 "select#": 2,
184 "from": "IN (SELECT)",
185 "to": "EXISTS (CORRELATED SELECT)",
186 "chosen": true,
187 "evaluating_constant_where_conditions": [
188 ] /* evaluating_constant_where_conditions */,
189 "evaluating_constant_having_conditions": [
190 ] /* evaluating_constant_having_conditions */
191 } /* transformation */
192 },
193 {
194 "transformation": {
195 "select#": 4,
196 "from": "IN (SELECT)",
197 "to": "semijoin",
198 "chosen": true,
199 "transformation_to_semi_join": {
200 "subquery_predicate": "`alias2`.`col_varchar_key` in (/* 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`))",
201 "embedded in": "WHERE",
202 "evaluating_constant_semijoin_conditions": [
203 ] /* evaluating_constant_semijoin_conditions */,
204 "semi-join condition": "((`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`))",
205 "decorrelated_predicates": [
206 {
207 "outer": "`alias2`.`col_varchar_key`",
208 "inner": "`sq2_alias1`.`col_varchar_nokey`"
209 }
210 ] /* decorrelated_predicates */
211 } /* transformation_to_semi_join */
212 } /* transformation */
213 }
214 ] /* steps */
215 } /* join_preparation */
216 },
217 {
218 "expanded_query": "/* select#1 */ select `where_subselect_19379`.`field1` AS `field1`,`where_subselect_19379`.`field2` AS `field2` from `where_subselect_19379` where <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` on((`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from (`t2` `alias1` left join (`t2` `alias2` left join `t2` `alias3` on((`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`))) on((`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`))) semi join (`t3` `sq2_alias1`) where (<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` on((`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` on((`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 (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`)) 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` on((`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)"
219 },
220 {
221 "transformations_to_nested_joins": {
222 "transformations": [
223 "JOIN_condition_to_WHERE",
224 "parenthesis_removal"
225 ] /* transformations */,
226 "expanded_query": "/* 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`)"
227 } /* transformations_to_nested_joins */
228 },
229 {
230 "transformations_to_nested_joins": {
231 "transformations": [
232 "semijoin",
233 "outer_join_to_inner_join",
234 "JOIN_condition_to_WHERE",
235 "parenthesis_removal"
236 ] /* transformations */,
237 "expanded_query": "/* 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` semi join (`t3` `sq2_alias1`) where (<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 (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) 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))"
238 } /* transformations_to_nested_joins */
239 }
240 ] /* steps */
241 } /* join_preparation */
242 },
243 {
244 "join_optimization": {
245 "select#": 1,
246 "steps": [
247 {
248 "condition_processing": {
249 "condition": "WHERE",
250 "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` semi join (`t3` `sq2_alias1`) where (<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 (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) 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)",
251 "steps": [
252 {
253 "transformation": "equality_propagation",
254 "subselect_evaluation": [
255 ] /* subselect_evaluation */,
256 "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` semi join (`t3` `sq2_alias1`) where (<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 (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) 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)"
257 },
258 {
259 "transformation": "constant_propagation",
260 "subselect_evaluation": [
261 ] /* subselect_evaluation */,
262 "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` semi join (`t3` `sq2_alias1`) where (<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 (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) 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)"
263 },
264 {
265 "transformation": "trivial_condition_removal",
266 "subselect_evaluation": [
267 ] /* subselect_evaluation */,
268 "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` semi join (`t3` `sq2_alias1`) where (<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 (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) 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)"
269 }
270 ] /* steps */
271 } /* condition_processing */
272 },
273 {
274 "substitute_generated_columns": {
275 } /* substitute_generated_columns */
276 },
277 {
278 "table_dependencies": [
279 {
280 "table": "`where_subselect_19379`",
281 "row_may_be_null": false,
282 "map_bit": 0,
283 "depends_on_map_bits": [
284 ] /* depends_on_map_bits */
285 }
286 ] /* table_dependencies */
287 },
288 {
289 "ref_optimizer_key_uses": [
290 ] /* ref_optimizer_key_uses */
291 },
292 {
293 "rows_estimation": [
294 {
295 "table": "`where_subselect_19379`",
296 "rows": 1,
297 "cost": 1,
298 "table_type": "system",
299 "empty": true
300 }
301 ] /* rows_estimation */
302 }
303 ] /* steps */,
304 "empty_result": {
305 "cause": "no matching row in const table"
306 } /* empty_result */
307 } /* join_optimization */
308 },
309 {
310 "join_execution": {
311 "select#": 1,
312 "steps": [
313 ] /* steps */
314 } /* join_execution */
315 }
316 ] /* steps */
317 } 0 0
318 drop table t1,t2,t3,t4,where_subselect_19379;
319 drop view view_t4;
320 #
321 # BUG#12607524 JSON PARSE ERROR ON SELECT ... FROM ... WHERE .. IN (SUBQUERY)
322 #
323 CREATE TABLE t1 (
324 col_int_key int(11) DEFAULT NULL,
325 col_varchar_key varchar(1) DEFAULT NULL,
326 KEY col_int_key (col_int_key),
327 KEY col_varchar_key (col_varchar_key,col_int_key)
328 ) ENGINE=MyISAM;
329 Warnings:
330 Warning 1681 Integer display width is deprecated and will be removed in a future release.
331 INSERT INTO t1 VALUES (8,'g');
332 CREATE TABLE t2 (
333 col_int_key int(11) DEFAULT NULL,
334 col_varchar_key varchar(1) DEFAULT NULL,
335 KEY col_int_key (col_int_key),
336 KEY col_varchar_key (col_varchar_key,col_int_key)
337 ) ENGINE=MyISAM;
338 Warnings:
339 Warning 1681 Integer display width is deprecated and will be removed in a future release.
340 INSERT INTO t2 VALUES (7,'x');
341 CREATE TABLE where_subselect_19033
342 SELECT
343 ( SELECT col_int_key FROM t2 ) as field1
344 FROM t1
345 ;
346 SELECT * FROM where_subselect_19033;
347 field1
348 7
349 SELECT field1
350 FROM where_subselect_19033
351 WHERE field1 IN
352 ( SELECT
353 ( SELECT col_int_key FROM t2 )
354 FROM t1
355 )
356 ;
357 field1
358 7
359 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
360 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
361 SELECT field1
362 FROM where_subselect_19033
363 WHERE field1 IN
364 ( SELECT
365 ( SELECT col_int_key FROM t2 )
366 FROM t1
367 ) {
368 "steps": [
369 {
370 "join_preparation": {
371 "select#": 1,
372 "steps": [
373 {
374 "join_preparation": {
375 "select#": 2,
376 "steps": [
377 {
378 "join_preparation": {
379 "select#": 3,
380 "steps": [
381 {
382 "expanded_query": "/* select#3 */ select `t2`.`col_int_key` from `t2`"
383 }
384 ] /* steps */
385 } /* join_preparation */
386 },
387 {
388 "expanded_query": "/* select#2 */ select (/* select#3 */ select `t2`.`col_int_key` from `t2`) from `t1`"
389 }
390 ] /* steps */
391 } /* join_preparation */
392 },
393 {
394 "expanded_query": "/* select#1 */ select `where_subselect_19033`.`field1` AS `field1` from `where_subselect_19033` where `where_subselect_19033`.`field1` in (/* select#2 */ select (/* select#3 */ select `t2`.`col_int_key` from `t2`) from `t1`)"
395 },
396 {
397 "transformation": {
398 "select#": 2,
399 "from": "IN (SELECT)",
400 "to": "semijoin",
401 "chosen": true,
402 "transformation_to_semi_join": {
403 "subquery_predicate": "`where_subselect_19033`.`field1` in (/* select#2 */ select (/* select#3 */ select `t2`.`col_int_key` from `t2`) from `t1`)",
404 "embedded in": "WHERE",
405 "semi-join condition": "(`where_subselect_19033`.`field1` = (/* select#3 */ select `t2`.`col_int_key` from `t2`))",
406 "decorrelated_predicates": [
407 {
408 "outer": "`where_subselect_19033`.`field1`",
409 "inner": "(/* select#3 */ select `t2`.`col_int_key` from `t2`)"
410 }
411 ] /* decorrelated_predicates */
412 } /* transformation_to_semi_join */
413 } /* transformation */
414 },
415 {
416 "transformations_to_nested_joins": {
417 "transformations": [
418 "semijoin"
419 ] /* transformations */,
420 "expanded_query": "/* select#1 */ select `where_subselect_19033`.`field1` AS `field1` from `where_subselect_19033` semi join (`t1`) where ((`where_subselect_19033`.`field1` = (/* select#3 */ select `t2`.`col_int_key` from `t2`)))"
421 } /* transformations_to_nested_joins */
422 }
423 ] /* steps */
424 } /* join_preparation */
425 },
426 {
427 "join_optimization": {
428 "select#": 1,
429 "steps": [
430 {
431 "condition_processing": {
432 "condition": "WHERE",
433 "original_condition": "((`where_subselect_19033`.`field1` = (/* select#3 */ select `t2`.`col_int_key` from `t2`)))",
434 "steps": [
435 {
436 "transformation": "equality_propagation",
437 "subselect_evaluation": [
438 ] /* subselect_evaluation */,
439 "resulting_condition": "(multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`))"
440 },
441 {
442 "transformation": "constant_propagation",
443 "resulting_condition": "(multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`))"
444 },
445 {
446 "transformation": "trivial_condition_removal",
447 "resulting_condition": "multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`)"
448 }
449 ] /* steps */
450 } /* condition_processing */
451 },
452 {
453 "substitute_generated_columns": {
454 } /* substitute_generated_columns */
455 },
456 {
457 "table_dependencies": [
458 {
459 "table": "`where_subselect_19033`",
460 "row_may_be_null": false,
461 "map_bit": 0,
462 "depends_on_map_bits": [
463 ] /* depends_on_map_bits */
464 },
465 {
466 "table": "`t1`",
467 "row_may_be_null": false,
468 "map_bit": 1,
469 "depends_on_map_bits": [
470 ] /* depends_on_map_bits */
471 }
472 ] /* table_dependencies */
473 },
474 {
475 "ref_optimizer_key_uses": [
476 ] /* ref_optimizer_key_uses */
477 },
478 {
479 "pulled_out_semijoin_tables": [
480 ] /* pulled_out_semijoin_tables */
481 },
482 {
483 "subselect_execution": {
484 "select#": 3,
485 "steps": [
486 {
487 "join_optimization": {
488 "select#": 3,
489 "steps": [
490 {
491 "table_dependencies": [
492 {
493 "table": "`t2`",
494 "row_may_be_null": false,
495 "map_bit": 0,
496 "depends_on_map_bits": [
497 ] /* depends_on_map_bits */
498 }
499 ] /* table_dependencies */
500 },
501 {
502 "rows_estimation": [
503 {
504 "table": "`t2`",
505 "rows": 1,
506 "cost": 1,
507 "table_type": "system",
508 "empty": false
509 }
510 ] /* rows_estimation */
511 },
512 {
513 "attaching_conditions_to_tables": {
514 "original_condition": null,
515 "attached_conditions_computation": [
516 ] /* attached_conditions_computation */,
517 "attached_conditions_summary": [
518 ] /* attached_conditions_summary */
519 } /* attaching_conditions_to_tables */
520 },
521 {
522 "refine_plan": [
523 ] /* refine_plan */
524 }
525 ] /* steps */
526 } /* join_optimization */
527 },
528 {
529 "join_execution": {
530 "select#": 3,
531 "steps": [
532 ] /* steps */
533 } /* join_execution */
534 }
535 ] /* steps */
536 } /* subselect_execution */
537 },
538 {
539 "rows_estimation": [
540 {
541 "table": "`where_subselect_19033`",
542 "rows": 1,
543 "cost": 1,
544 "table_type": "system",
545 "empty": false
546 },
547 {
548 "table": "`t1`",
549 "table_scan": {
550 "rows": 1,
551 "cost": 0.501221
552 } /* table_scan */
553 }
554 ] /* rows_estimation */
555 },
556 {
557 "execution_plan_for_potential_materialization": {
558 "steps": [
559 {
560 "considered_execution_plans": [
561 {
562 "plan_prefix": [
563 ] /* plan_prefix */,
564 "table": "`t1`",
565 "best_access_path": {
566 "considered_access_paths": [
567 {
568 "rows_to_scan": 1,
569 "access_type": "scan",
570 "resulting_rows": 1,
571 "cost": 0.601221,
572 "chosen": true
573 }
574 ] /* considered_access_paths */
575 } /* best_access_path */,
576 "condition_filtering_pct": 100,
577 "rows_for_plan": 1,
578 "cost_for_plan": 0.601221,
579 "chosen": true
580 }
581 ] /* considered_execution_plans */
582 }
583 ] /* steps */
584 } /* execution_plan_for_potential_materialization */
585 },
586 {
587 "considered_execution_plans": [
588 {
589 "plan_prefix": [
590 "`where_subselect_19033`"
591 ] /* plan_prefix */,
592 "table": "`t1`",
593 "best_access_path": {
594 "considered_access_paths": [
595 {
596 "rows_to_scan": 1,
597 "access_type": "scan",
598 "resulting_rows": 1,
599 "cost": 0.601221,
600 "chosen": true
601 }
602 ] /* considered_access_paths */
603 } /* best_access_path */,
604 "condition_filtering_pct": 100,
605 "rows_for_plan": 1,
606 "cost_for_plan": 0.601221,
607 "semijoin_strategy_choice": [
608 {
609 "strategy": "FirstMatch",
610 "recalculate_access_paths_and_cost": {
611 "tables": [
612 ] /* tables */
613 } /* recalculate_access_paths_and_cost */,
614 "cost": 0.601221,
615 "rows": 1,
616 "chosen": true
617 },
618 {
619 "strategy": "MaterializeLookup",
620 "cost": 1.80122,
621 "rows": 1,
622 "duplicate_tables_left": false,
623 "chosen": false
624 },
625 {
626 "strategy": "DuplicatesWeedout",
627 "cost": 1.80122,
628 "rows": 1,
629 "duplicate_tables_left": false,
630 "chosen": false
631 }
632 ] /* semijoin_strategy_choice */,
633 "chosen": true
634 },
635 {
636 "final_semijoin_strategy": "FirstMatch",
637 "recalculate_access_paths_and_cost": {
638 "tables": [
639 ] /* tables */
640 } /* recalculate_access_paths_and_cost */
641 }
642 ] /* considered_execution_plans */
643 },
644 {
645 "condition_on_constant_tables": "true",
646 "condition_value": true
647 },
648 {
649 "attaching_conditions_to_tables": {
650 "original_condition": "true",
651 "attached_conditions_computation": [
652 ] /* attached_conditions_computation */,
653 "attached_conditions_summary": [
654 {
655 "table": "`t1`",
656 "attached": null
657 }
658 ] /* attached_conditions_summary */
659 } /* attaching_conditions_to_tables */
660 },
661 {
662 "finalizing_table_conditions": [
663 ] /* finalizing_table_conditions */
664 },
665 {
666 "refine_plan": [
667 {
668 "table": "`t1`"
669 }
670 ] /* refine_plan */
671 }
672 ] /* steps */
673 } /* join_optimization */
674 },
675 {
676 "join_execution": {
677 "select#": 1,
678 "steps": [
679 ] /* steps */
680 } /* join_execution */
681 }
682 ] /* steps */
683 } 0 0
684 DROP TABLE where_subselect_19033,t1,t2;
685
686 # BUG#12612201 - SEGFAULT IN
687 # SUBSELECT_UNIQUESUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
688
689 CREATE TABLE t1 (
690 col_int_key int(11) DEFAULT NULL,
691 col_varchar_key varchar(1) DEFAULT NULL,
692 col_varchar_nokey varchar(1) DEFAULT NULL
693 );
694 Warnings:
695 Warning 1681 Integer display width is deprecated and will be removed in a future release.
696 CREATE TABLE t2 (
697 pk int(11) NOT NULL AUTO_INCREMENT,
698 col_int_key int(11) DEFAULT NULL,
699 col_varchar_key varchar(1) DEFAULT NULL,
700 col_varchar_nokey varchar(1) DEFAULT NULL,
701 PRIMARY KEY (pk)
702 );
703 Warnings:
704 Warning 1681 Integer display width is deprecated and will be removed in a future release.
705 Warning 1681 Integer display width is deprecated and will be removed in a future release.
706 INSERT INTO t2 VALUES (1,4,'v','v'),(20,5,'r','r');
707 CREATE TABLE t3 (
708 col_int_key int(11) DEFAULT NULL,
709 col_varchar_key varchar(1) DEFAULT NULL,
710 col_varchar_nokey varchar(1) DEFAULT NULL
711 );
712 Warnings:
713 Warning 1681 Integer display width is deprecated and will be removed in a future release.
714 INSERT INTO t3 VALUES (NULL,'j','j'),(8,'c','c');
715 CREATE TABLE where_updatedelete_20769 select count( alias2 . col_varchar_key ) as field1
716 from (
717 (select sq1_alias1 . *
718 from ( t3 as sq1_alias1
719 straight_join t1 as sq1_alias2
720 on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
721 )
722 where sq1_alias1 . col_int_key in (
723 select c_sq1_alias1 . pk as c_sq1_field1
724 from t2 as c_sq1_alias1
725 )
726 ) as alias1
727 left outer join t1 as alias2
728 on (alias2 . col_varchar_key = alias1 . col_varchar_key )
729 )
730 where ( alias2 . col_varchar_key in (
731 select sq2_alias1 . col_varchar_nokey as sq2_field1
732 from t2 as sq2_alias1
733 where sq2_alias1 . col_int_key in (
734 select distinct c_sq2_alias1 . col_int_key as c_sq2_field1
735 from t3 as c_sq2_alias1
736 )
737 ) )
738 or alias1 . col_int_key = 2
739 and alias2 . col_varchar_nokey <= alias1 . col_varchar_nokey
740 order by alias1 . col_varchar_key , field1
741 ;
742 UPDATE where_updatedelete_20769 SET field1 = ( select count( alias2 . col_varchar_key ) as field1
743 from (
744 (select sq1_alias1 . *
745 from ( t3 as sq1_alias1
746 straight_join t1 as sq1_alias2
747 on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
748 )
749 where sq1_alias1 . col_int_key in (
750 select c_sq1_alias1 . pk as c_sq1_field1
751 from t2 as c_sq1_alias1
752 )
753 ) as alias1
754 left outer join t1 as alias2
755 on (alias2 . col_varchar_key = alias1 . col_varchar_key )
756 )
757 where ( alias2 . col_varchar_key in (
758 select sq2_alias1 . col_varchar_nokey as sq2_field1
759 from t2 as sq2_alias1
760 where sq2_alias1 . col_int_key in (
761 select distinct c_sq2_alias1 . col_int_key as c_sq2_field1
762 from t3 as c_sq2_alias1
763 )
764 ) )
765 or alias1 . col_int_key = 2
766 and alias2 . col_varchar_nokey <= alias1 . col_varchar_nokey
767 order by alias1 . col_varchar_key , field1
768 );
769 DROP TABLE where_updatedelete_20769;
770 DROP TABLE t1,t2,t3;
771
772 # BUG#12710761 - INVALID JSON TRACE ON SUBQUERY IN IN-CLAUSE
773
774 CREATE TABLE t1 (col_int_key int, KEY col_int_key (col_int_key));
775 INSERT INTO t1 VALUES (0),(8),(1),(8);
776 CREATE TABLE where_subselect_20070
777 SELECT table2 .col_int_key AS field1,
778 ( SELECT COUNT( col_int_key )
779 FROM t1
780 )
781 FROM t1 AS table1
782 JOIN t1 AS table2
783 ON table2 .col_int_key = table1 .col_int_key;
784 SELECT *
785 FROM where_subselect_20070
786 WHERE (field1, ( SELECT COUNT( col_int_key ) FROM t1 )) IN (
787 SELECT table2 .col_int_key AS field1,
788 ( SELECT COUNT( col_int_key )
789 FROM t1
790 )
791 FROM t1 AS table1
792 JOIN t1 AS table2
793 ON table2 .col_int_key = table1 .col_int_key
794 );
795 field1 ( SELECT COUNT( col_int_key )
796 )
797 0 4
798 1 4
799 8 4
800 8 4
801 8 4
802 8 4
803 FROM t1
804 select * from information_schema.optimizer_trace;
805 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
806 SELECT *
807 FROM where_subselect_20070
808 WHERE (field1, ( SELECT COUNT( col_int_key ) FROM t1 )) IN (
809 SELECT table2 .col_int_key AS field1,
810 ( SELECT COUNT( col_int_key )
811 FROM t1
812 )
813 FROM t1 AS table1
814 JOIN t1 AS table2
815 ON table2 .col_int_key = table1 .col_int_key
816 ) {
817 "steps": [
818 {
819 "join_preparation": {
820 "select#": 1,
821 "steps": [
822 {
823 "join_preparation": {
824 "select#": 2,
825 "steps": [
826 {
827 "expanded_query": "/* select#2 */ select count(`t1`.`col_int_key`) from `t1`"
828 }
829 ] /* steps */
830 } /* join_preparation */
831 },
832 {
833 "join_preparation": {
834 "select#": 3,
835 "steps": [
836 {
837 "join_preparation": {
838 "select#": 4,
839 "steps": [
840 {
841 "expanded_query": "/* select#4 */ select count(`t1`.`col_int_key`) from `t1`"
842 }
843 ] /* steps */
844 } /* join_preparation */
845 },
846 {
847 "expanded_query": "/* 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` on((`table2`.`col_int_key` = `table1`.`col_int_key`)))"
848 }
849 ] /* steps */
850 } /* join_preparation */
851 },
852 {
853 "expanded_query": "/* select#1 */ select `where_subselect_20070`.`field1` AS `field1`,`where_subselect_20070`.`( SELECT COUNT( col_int_key )\nFROM t1\n)` AS `( SELECT COUNT( col_int_key )\nFROM t1\n)` from `where_subselect_20070` where (`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) in (/* 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` on((`table2`.`col_int_key` = `table1`.`col_int_key`))))"
854 },
855 {
856 "transformation": {
857 "select#": 3,
858 "from": "IN (SELECT)",
859 "to": "semijoin",
860 "chosen": true,
861 "transformation_to_semi_join": {
862 "subquery_predicate": "(`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) in (/* 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` on((`table2`.`col_int_key` = `table1`.`col_int_key`))))",
863 "embedded in": "WHERE",
864 "evaluating_constant_semijoin_conditions": [
865 ] /* evaluating_constant_semijoin_conditions */,
866 "semi-join condition": "((`where_subselect_20070`.`field1` = `table2`.`col_int_key`) and ((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)))",
867 "decorrelated_predicates": [
868 {
869 "outer": "`where_subselect_20070`.`field1`",
870 "inner": "`table2`.`col_int_key`"
871 },
872 {
873 "outer": "(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)",
874 "inner": "(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)"
875 }
876 ] /* decorrelated_predicates */
877 } /* transformation_to_semi_join */
878 } /* transformation */
879 },
880 {
881 "transformations_to_nested_joins": {
882 "transformations": [
883 "semijoin",
884 "JOIN_condition_to_WHERE",
885 "parenthesis_removal"
886 ] /* transformations */,
887 "expanded_query": "/* select#1 */ select `where_subselect_20070`.`field1` AS `field1`,`where_subselect_20070`.`( SELECT COUNT( col_int_key )\nFROM t1\n)` AS `( SELECT COUNT( col_int_key )\nFROM t1\n)` from `where_subselect_20070` semi join (`t1` `table1` join `t1` `table2`) where ((`where_subselect_20070`.`field1` = `table2`.`col_int_key`) and ((/* 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`))"
888 } /* transformations_to_nested_joins */
889 }
890 ] /* steps */
891 } /* join_preparation */
892 },
893 {
894 "join_optimization": {
895 "select#": 1,
896 "steps": [
897 {
898 "condition_processing": {
899 "condition": "WHERE",
900 "original_condition": "((`where_subselect_20070`.`field1` = `table2`.`col_int_key`) and ((/* 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`))",
901 "steps": [
902 {
903 "transformation": "equality_propagation",
904 "subselect_evaluation": [
905 ] /* subselect_evaluation */,
906 "resulting_condition": "(((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and multiple equal(`where_subselect_20070`.`field1`, `table2`.`col_int_key`, `table1`.`col_int_key`))"
907 },
908 {
909 "transformation": "constant_propagation",
910 "subselect_evaluation": [
911 ] /* subselect_evaluation */,
912 "resulting_condition": "(((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and multiple equal(`where_subselect_20070`.`field1`, `table2`.`col_int_key`, `table1`.`col_int_key`))"
913 },
914 {
915 "transformation": "trivial_condition_removal",
916 "subselect_evaluation": [
917 {
918 "subselect_execution": {
919 "select#": 2,
920 "steps": [
921 {
922 "join_optimization": {
923 "select#": 2,
924 "steps": [
925 {
926 "table_dependencies": [
927 {
928 "table": "`t1`",
929 "row_may_be_null": false,
930 "map_bit": 0,
931 "depends_on_map_bits": [
932 ] /* depends_on_map_bits */
933 }
934 ] /* table_dependencies */
935 },
936 {
937 "rows_estimation": [
938 {
939 "table": "`t1`",
940 "table_scan": {
941 "rows": 4,
942 "cost": 0.501709
943 } /* table_scan */
944 }
945 ] /* rows_estimation */
946 },
947 {
948 "considered_execution_plans": [
949 {
950 "plan_prefix": [
951 ] /* plan_prefix */,
952 "table": "`t1`",
953 "best_access_path": {
954 "considered_access_paths": [
955 {
956 "rows_to_scan": 4,
957 "access_type": "scan",
958 "resulting_rows": 4,
959 "cost": 0.901709,
960 "chosen": true
961 }
962 ] /* considered_access_paths */
963 } /* best_access_path */,
964 "condition_filtering_pct": 100,
965 "rows_for_plan": 4,
966 "cost_for_plan": 0.901709,
967 "chosen": true
968 }
969 ] /* considered_execution_plans */
970 },
971 {
972 "attaching_conditions_to_tables": {
973 "original_condition": null,
974 "attached_conditions_computation": [
975 ] /* attached_conditions_computation */,
976 "attached_conditions_summary": [
977 {
978 "table": "`t1`",
979 "attached": null
980 }
981 ] /* attached_conditions_summary */
982 } /* attaching_conditions_to_tables */
983 },
984 {
985 "optimizing_distinct_group_by_order_by": {
986 } /* optimizing_distinct_group_by_order_by */
987 },
988 {
989 "finalizing_table_conditions": [
990 ] /* finalizing_table_conditions */
991 },
992 {
993 "refine_plan": [
994 {
995 "table": "`t1`"
996 }
997 ] /* refine_plan */
998 },
999 {
1000 "considering_tmp_tables": [
1001 ] /* considering_tmp_tables */
1002 }
1003 ] /* steps */
1004 } /* join_optimization */
1005 },
1006 {
1007 "join_execution": {
1008 "select#": 2,
1009 "steps": [
1010 ] /* steps */
1011 } /* join_execution */
1012 }
1013 ] /* steps */
1014 } /* subselect_execution */
1015 },
1016 {
1017 "subselect_execution": {
1018 "select#": 4,
1019 "steps": [
1020 {
1021 "join_optimization": {
1022 "select#": 4,
1023 "steps": [
1024 {
1025 "table_dependencies": [
1026 {
1027 "table": "`t1`",
1028 "row_may_be_null": false,
1029 "map_bit": 0,
1030 "depends_on_map_bits": [
1031 ] /* depends_on_map_bits */
1032 }
1033 ] /* table_dependencies */
1034 },
1035 {
1036 "rows_estimation": [
1037 {
1038 "table": "`t1`",
1039 "table_scan": {
1040 "rows": 4,
1041 "cost": 0.501709
1042 } /* table_scan */
1043 }
1044 ] /* rows_estimation */
1045 },
1046 {
1047 "considered_execution_plans": [
1048 {
1049 "plan_prefix": [
1050 ] /* plan_prefix */,
1051 "table": "`t1`",
1052 "best_access_path": {
1053 "considered_access_paths": [
1054 {
1055 "rows_to_scan": 4,
1056 "access_type": "scan",
1057 "resulting_rows": 4,
1058 "cost": 0.901709,
1059 "chosen": true
1060 }
1061 ] /* considered_access_paths */
1062 } /* best_access_path */,
1063 "condition_filtering_pct": 100,
1064 "rows_for_plan": 4,
1065 "cost_for_plan": 0.901709,
1066 "chosen": true
1067 }
1068 ] /* considered_execution_plans */
1069 },
1070 {
1071 "attaching_conditions_to_tables": {
1072 "original_condition": null,
1073 "attached_conditions_computation": [
1074 ] /* attached_conditions_computation */,
1075 "attached_conditions_summary": [
1076 {
1077 "table": "`t1`",
1078 "attached": null
1079 }
1080 ] /* attached_conditions_summary */
1081 } /* attaching_conditions_to_tables */
1082 },
1083 {
1084 "optimizing_distinct_group_by_order_by": {
1085 } /* optimizing_distinct_group_by_order_by */
1086 },
1087 {
1088 "finalizing_table_conditions": [
1089 ] /* finalizing_table_conditions */
1090 },
1091 {
1092 "refine_plan": [
1093 {
1094 "table": "`t1`"
1095 }
1096 ] /* refine_plan */
1097 },
1098 {
1099 "considering_tmp_tables": [
1100 ] /* considering_tmp_tables */
1101 }
1102 ] /* steps */
1103 } /* join_optimization */
1104 },
1105 {
1106 "join_execution": {
1107 "select#": 4,
1108 "steps": [
1109 ] /* steps */
1110 } /* join_execution */
1111 }
1112 ] /* steps */
1113 } /* subselect_execution */
1114 }
1115 ] /* subselect_evaluation */,
1116 "resulting_condition": "multiple equal(`where_subselect_20070`.`field1`, `table2`.`col_int_key`, `table1`.`col_int_key`)"
1117 }
1118 ] /* steps */
1119 } /* condition_processing */
1120 },
1121 {
1122 "substitute_generated_columns": {
1123 } /* substitute_generated_columns */
1124 },
1125 {
1126 "table_dependencies": [
1127 {
1128 "table": "`where_subselect_20070`",
1129 "row_may_be_null": false,
1130 "map_bit": 0,
1131 "depends_on_map_bits": [
1132 ] /* depends_on_map_bits */
1133 },
1134 {
1135 "table": "`t1` `table1`",
1136 "row_may_be_null": false,
1137 "map_bit": 1,
1138 "depends_on_map_bits": [
1139 ] /* depends_on_map_bits */
1140 },
1141 {
1142 "table": "`t1` `table2`",
1143 "row_may_be_null": false,
1144 "map_bit": 2,
1145 "depends_on_map_bits": [
1146 ] /* depends_on_map_bits */
1147 }
1148 ] /* table_dependencies */
1149 },
1150 {
1151 "ref_optimizer_key_uses": [
1152 {
1153 "table": "`t1` `table1`",
1154 "field": "col_int_key",
1155 "equals": "`where_subselect_20070`.`field1`",
1156 "null_rejecting": true
1157 },
1158 {
1159 "table": "`t1` `table1`",
1160 "field": "col_int_key",
1161 "equals": "`table2`.`col_int_key`",
1162 "null_rejecting": true
1163 },
1164 {
1165 "table": "`t1` `table2`",
1166 "field": "col_int_key",
1167 "equals": "`where_subselect_20070`.`field1`",
1168 "null_rejecting": true
1169 },
1170 {
1171 "table": "`t1` `table2`",
1172 "field": "col_int_key",
1173 "equals": "`table1`.`col_int_key`",
1174 "null_rejecting": true
1175 }
1176 ] /* ref_optimizer_key_uses */
1177 },
1178 {
1179 "pulled_out_semijoin_tables": [
1180 ] /* pulled_out_semijoin_tables */
1181 },
1182 {
1183 "rows_estimation": [
1184 {
1185 "table": "`where_subselect_20070`",
1186 "table_scan": {
1187 "rows": 6,
1188 "cost": 0.504761
1189 } /* table_scan */
1190 },
1191 {
1192 "table": "`t1` `table1`",
1193 "table_scan": {
1194 "rows": 4,
1195 "cost": 0.501709
1196 } /* table_scan */
1197 },
1198 {
1199 "table": "`t1` `table2`",
1200 "table_scan": {
1201 "rows": 4,
1202 "cost": 0.501709
1203 } /* table_scan */
1204 }
1205 ] /* rows_estimation */
1206 },
1207 {
1208 "execution_plan_for_potential_materialization": {
1209 "steps": [
1210 {
1211 "considered_execution_plans": [
1212 {
1213 "plan_prefix": [
1214 ] /* plan_prefix */,
1215 "table": "`t1` `table1`",
1216 "best_access_path": {
1217 "considered_access_paths": [
1218 {
1219 "access_type": "ref",
1220 "index": "col_int_key",
1221 "usable": false,
1222 "chosen": false
1223 },
1224 {
1225 "rows_to_scan": 4,
1226 "filtering_effect": [
1227 ] /* filtering_effect */,
1228 "final_filtering_effect": 1,
1229 "access_type": "scan",
1230 "resulting_rows": 4,
1231 "cost": 0.901709,
1232 "chosen": true
1233 }
1234 ] /* considered_access_paths */
1235 } /* best_access_path */,
1236 "condition_filtering_pct": 100,
1237 "rows_for_plan": 4,
1238 "cost_for_plan": 0.901709,
1239 "rest_of_plan": [
1240 {
1241 "plan_prefix": [
1242 "`t1` `table1`"
1243 ] /* plan_prefix */,
1244 "table": "`t1` `table2`",
1245 "best_access_path": {
1246 "considered_access_paths": [
1247 {
1248 "access_type": "ref",
1249 "index": "col_int_key",
1250 "rows": 2,
1251 "cost": 1.82326,
1252 "chosen": true
1253 },
1254 {
1255 "rows_to_scan": 4,
1256 "filtering_effect": [
1257 ] /* filtering_effect */,
1258 "final_filtering_effect": 1,
1259 "access_type": "scan",
1260 "using_join_cache": true,
1261 "buffers_needed": 1,
1262 "resulting_rows": 4,
1263 "cost": 2.10175,
1264 "chosen": false
1265 }
1266 ] /* considered_access_paths */
1267 } /* best_access_path */,
1268 "condition_filtering_pct": 100,
1269 "rows_for_plan": 8,
1270 "cost_for_plan": 2.72496,
1271 "chosen": true
1272 }
1273 ] /* rest_of_plan */
1274 },
1275 {
1276 "plan_prefix": [
1277 ] /* plan_prefix */,
1278 "table": "`t1` `table2`",
1279 "best_access_path": {
1280 "considered_access_paths": [
1281 {
1282 "access_type": "ref",
1283 "index": "col_int_key",
1284 "usable": false,
1285 "chosen": false
1286 },
1287 {
1288 "rows_to_scan": 4,
1289 "filtering_effect": [
1290 ] /* filtering_effect */,
1291 "final_filtering_effect": 1,
1292 "access_type": "scan",
1293 "resulting_rows": 4,
1294 "cost": 0.901709,
1295 "chosen": true
1296 }
1297 ] /* considered_access_paths */
1298 } /* best_access_path */,
1299 "condition_filtering_pct": 100,
1300 "rows_for_plan": 4,
1301 "cost_for_plan": 0.901709,
1302 "rest_of_plan": [
1303 {
1304 "plan_prefix": [
1305 "`t1` `table2`"
1306 ] /* plan_prefix */,
1307 "table": "`t1` `table1`",
1308 "best_access_path": {
1309 "considered_access_paths": [
1310 {
1311 "access_type": "ref",
1312 "index": "col_int_key",
1313 "rows": 2,
1314 "cost": 1.82326,
1315 "chosen": true
1316 },
1317 {
1318 "rows_to_scan": 4,
1319 "filtering_effect": [
1320 ] /* filtering_effect */,
1321 "final_filtering_effect": 1,
1322 "access_type": "scan",
1323 "using_join_cache": true,
1324 "buffers_needed": 1,
1325 "resulting_rows": 4,
1326 "cost": 2.10175,
1327 "chosen": false
1328 }
1329 ] /* considered_access_paths */
1330 } /* best_access_path */,
1331 "condition_filtering_pct": 100,
1332 "rows_for_plan": 8,
1333 "cost_for_plan": 2.72496,
1334 "pruned_by_cost": true
1335 }
1336 ] /* rest_of_plan */
1337 }
1338 ] /* considered_execution_plans */
1339 }
1340 ] /* steps */
1341 } /* execution_plan_for_potential_materialization */
1342 },
1343 {
1344 "considered_execution_plans": [
1345 {
1346 "plan_prefix": [
1347 ] /* plan_prefix */,
1348 "table": "`where_subselect_20070`",
1349 "best_access_path": {
1350 "considered_access_paths": [
1351 {
1352 "rows_to_scan": 6,
1353 "filtering_effect": [
1354 ] /* filtering_effect */,
1355 "final_filtering_effect": 1,
1356 "access_type": "scan",
1357 "resulting_rows": 6,
1358 "cost": 1.10476,
1359 "chosen": true
1360 }
1361 ] /* considered_access_paths */
1362 } /* best_access_path */,
1363 "condition_filtering_pct": 100,
1364 "rows_for_plan": 6,
1365 "cost_for_plan": 1.10476,
1366 "semijoin_strategy_choice": [
1367 ] /* semijoin_strategy_choice */,
1368 "rest_of_plan": [
1369 {
1370 "plan_prefix": [
1371 "`where_subselect_20070`"
1372 ] /* plan_prefix */,
1373 "table": "`t1` `table1`",
1374 "best_access_path": {
1375 "considered_access_paths": [
1376 {
1377 "access_type": "ref",
1378 "index": "col_int_key",
1379 "rows": 2,
1380 "cost": 2.73488,
1381 "chosen": true
1382 },
1383 {
1384 "rows_to_scan": 4,
1385 "filtering_effect": [
1386 ] /* filtering_effect */,
1387 "final_filtering_effect": 1,
1388 "access_type": "scan",
1389 "using_join_cache": true,
1390 "buffers_needed": 1,
1391 "resulting_rows": 4,
1392 "cost": 2.90186,
1393 "chosen": false
1394 }
1395 ] /* considered_access_paths */
1396 } /* best_access_path */,
1397 "condition_filtering_pct": 100,
1398 "rows_for_plan": 12,
1399 "cost_for_plan": 3.83964,
1400 "semijoin_strategy_choice": [
1401 ] /* semijoin_strategy_choice */,
1402 "rest_of_plan": [
1403 {
1404 "plan_prefix": [
1405 "`where_subselect_20070`",
1406 "`t1` `table1`"
1407 ] /* plan_prefix */,
1408 "table": "`t1` `table2`",
1409 "best_access_path": {
1410 "considered_access_paths": [
1411 {
1412 "access_type": "ref",
1413 "index": "col_int_key",
1414 "rows": 2,
1415 "cost": 5.46977,
1416 "chosen": true
1417 },
1418 {
1419 "rows_to_scan": 4,
1420 "filtering_effect": [
1421 ] /* filtering_effect */,
1422 "final_filtering_effect": 1,
1423 "access_type": "scan",
1424 "using_join_cache": true,
1425 "buffers_needed": 1,
1426 "resulting_rows": 4,
1427 "cost": 5.30212,
1428 "chosen": true
1429 }
1430 ] /* considered_access_paths */
1431 } /* best_access_path */,
1432 "condition_filtering_pct": 25,
1433 "rows_for_plan": 12,
1434 "cost_for_plan": 9.14177,
1435 "semijoin_strategy_choice": [
1436 {
1437 "strategy": "FirstMatch",
1438 "recalculate_access_paths_and_cost": {
1439 "tables": [
1440 {
1441 "table": "`t1` `table2`",
1442 "best_access_path": {
1443 "considered_access_paths": [
1444 {
1445 "access_type": "ref",
1446 "index": "col_int_key",
1447 "rows": 2,
1448 "cost": 5.46977,
1449 "chosen": true
1450 },
1451 {
1452 "rows_to_scan": 4,
1453 "filtering_effect": [
1454 ] /* filtering_effect */,
1455 "final_filtering_effect": 1,
1456 "access_type": "scan",
1457 "resulting_rows": 4,
1458 "cost": 10.8205,
1459 "chosen": false
1460 }
1461 ] /* considered_access_paths */
1462 } /* best_access_path */
1463 }
1464 ] /* tables */
1465 } /* recalculate_access_paths_and_cost */,
1466 "cost": 9.30941,
1467 "rows": 6,
1468 "chosen": true
1469 },
1470 {
1471 "strategy": "MaterializeLookup",
1472 "cost": 6.22973,
1473 "rows": 6,
1474 "duplicate_tables_left": false,
1475 "chosen": true
1476 },
1477 {
1478 "strategy": "DuplicatesWeedout",
1479 "cost": 11.9418,
1480 "rows": 6,
1481 "duplicate_tables_left": false,
1482 "chosen": false
1483 }
1484 ] /* semijoin_strategy_choice */,
1485 "chosen": true
1486 }
1487 ] /* rest_of_plan */
1488 },
1489 {
1490 "plan_prefix": [
1491 "`where_subselect_20070`"
1492 ] /* plan_prefix */,
1493 "table": "`t1` `table2`",
1494 "best_access_path": {
1495 "considered_access_paths": [
1496 {
1497 "access_type": "ref",
1498 "index": "col_int_key",
1499 "rows": 2,
1500 "cost": 2.73488,
1501 "chosen": true
1502 },
1503 {
1504 "rows_to_scan": 4,
1505 "filtering_effect": [
1506 ] /* filtering_effect */,
1507 "final_filtering_effect": 1,
1508 "access_type": "scan",
1509 "using_join_cache": true,
1510 "buffers_needed": 1,
1511 "resulting_rows": 4,
1512 "cost": 2.90186,
1513 "chosen": false
1514 }
1515 ] /* considered_access_paths */
1516 } /* best_access_path */,
1517 "condition_filtering_pct": 100,
1518 "rows_for_plan": 12,
1519 "cost_for_plan": 3.83964,
1520 "semijoin_strategy_choice": [
1521 ] /* semijoin_strategy_choice */,
1522 "rest_of_plan": [
1523 {
1524 "plan_prefix": [
1525 "`where_subselect_20070`",
1526 "`t1` `table2`"
1527 ] /* plan_prefix */,
1528 "table": "`t1` `table1`",
1529 "best_access_path": {
1530 "considered_access_paths": [
1531 {
1532 "access_type": "ref",
1533 "index": "col_int_key",
1534 "rows": 2,
1535 "cost": 5.46977,
1536 "chosen": true
1537 },
1538 {
1539 "rows_to_scan": 4,
1540 "filtering_effect": [
1541 ] /* filtering_effect */,
1542 "final_filtering_effect": 1,
1543 "access_type": "scan",
1544 "using_join_cache": true,
1545 "buffers_needed": 1,
1546 "resulting_rows": 4,
1547 "cost": 5.30212,
1548 "chosen": true
1549 }
1550 ] /* considered_access_paths */
1551 } /* best_access_path */,
1552 "condition_filtering_pct": 25,
1553 "rows_for_plan": 12,
1554 "cost_for_plan": 9.14177,
1555 "semijoin_strategy_choice": [
1556 {
1557 "strategy": "FirstMatch",
1558 "recalculate_access_paths_and_cost": {
1559 "tables": [
1560 {
1561 "table": "`t1` `table1`",
1562 "best_access_path": {
1563 "considered_access_paths": [
1564 {
1565 "access_type": "ref",
1566 "index": "col_int_key",
1567 "rows": 2,
1568 "cost": 5.46977,
1569 "chosen": true
1570 },
1571 {
1572 "rows_to_scan": 4,
1573 "filtering_effect": [
1574 ] /* filtering_effect */,
1575 "final_filtering_effect": 1,
1576 "access_type": "scan",
1577 "resulting_rows": 4,
1578 "cost": 10.8205,
1579 "chosen": false
1580 }
1581 ] /* considered_access_paths */
1582 } /* best_access_path */
1583 }
1584 ] /* tables */
1585 } /* recalculate_access_paths_and_cost */,
1586 "cost": 9.30941,
1587 "rows": 6,
1588 "chosen": true
1589 },
1590 {
1591 "strategy": "MaterializeLookup",
1592 "cost": 6.22973,
1593 "rows": 6,
1594 "duplicate_tables_left": false,
1595 "chosen": true
1596 },
1597 {
1598 "strategy": "DuplicatesWeedout",
1599 "cost": 11.9418,
1600 "rows": 6,
1601 "duplicate_tables_left": false,
1602 "chosen": false
1603 }
1604 ] /* semijoin_strategy_choice */,
1605 "pruned_by_cost": true
1606 }
1607 ] /* rest_of_plan */
1608 }
1609 ] /* rest_of_plan */
1610 },
1611 {
1612 "plan_prefix": [
1613 ] /* plan_prefix */,
1614 "table": "`t1` `table1`",
1615 "best_access_path": {
1616 "considered_access_paths": [
1617 {
1618 "access_type": "ref",
1619 "index": "col_int_key",
1620 "usable": false,
1621 "chosen": false
1622 },
1623 {
1624 "rows_to_scan": 4,
1625 "filtering_effect": [
1626 ] /* filtering_effect */,
1627 "final_filtering_effect": 1,
1628 "access_type": "scan",
1629 "resulting_rows": 4,
1630 "cost": 0.901709,
1631 "chosen": true
1632 }
1633 ] /* considered_access_paths */
1634 } /* best_access_path */,
1635 "condition_filtering_pct": 100,
1636 "rows_for_plan": 4,
1637 "cost_for_plan": 0.901709,
1638 "semijoin_strategy_choice": [
1639 ] /* semijoin_strategy_choice */,
1640 "rest_of_plan": [
1641 {
1642 "plan_prefix": [
1643 "`t1` `table1`"
1644 ] /* plan_prefix */,
1645 "table": "`where_subselect_20070`",
1646 "best_access_path": {
1647 "considered_access_paths": [
1648 {
1649 "rows_to_scan": 6,
1650 "filtering_effect": [
1651 ] /* filtering_effect */,
1652 "final_filtering_effect": 1,
1653 "access_type": "scan",
1654 "using_join_cache": true,
1655 "buffers_needed": 1,
1656 "resulting_rows": 6,
1657 "cost": 2.9048,
1658 "chosen": true
1659 }
1660 ] /* considered_access_paths */
1661 } /* best_access_path */,
1662 "condition_filtering_pct": 16.6667,
1663 "rows_for_plan": 4,
1664 "cost_for_plan": 3.80651,
1665 "semijoin_strategy_choice": [
1666 ] /* semijoin_strategy_choice */,
1667 "rest_of_plan": [
1668 {
1669 "plan_prefix": [
1670 "`t1` `table1`",
1671 "`where_subselect_20070`"
1672 ] /* plan_prefix */,
1673 "table": "`t1` `table2`",
1674 "best_access_path": {
1675 "considered_access_paths": [
1676 {
1677 "access_type": "ref",
1678 "index": "col_int_key",
1679 "rows": 2,
1680 "cost": 1.82326,
1681 "chosen": true
1682 },
1683 {
1684 "rows_to_scan": 4,
1685 "filtering_effect": [
1686 ] /* filtering_effect */,
1687 "final_filtering_effect": 1,
1688 "access_type": "scan",
1689 "using_join_cache": true,
1690 "buffers_needed": 1,
1691 "resulting_rows": 4,
1692 "cost": 2.10185,
1693 "chosen": false
1694 }
1695 ] /* considered_access_paths */
1696 } /* best_access_path */,
1697 "condition_filtering_pct": 100,
1698 "rows_for_plan": 8,
1699 "cost_for_plan": 5.62976,
1700 "semijoin_strategy_choice": [
1701 {
1702 "strategy": "DuplicatesWeedout",
1703 "cost": 7.52976,
1704 "rows": 1,
1705 "duplicate_tables_left": true,
1706 "chosen": true
1707 }
1708 ] /* semijoin_strategy_choice */,
1709 "pruned_by_cost": true
1710 }
1711 ] /* rest_of_plan */
1712 },
1713 {
1714 "plan_prefix": [
1715 "`t1` `table1`"
1716 ] /* plan_prefix */,
1717 "table": "`t1` `table2`",
1718 "best_access_path": {
1719 "considered_access_paths": [
1720 {
1721 "access_type": "ref",
1722 "index": "col_int_key",
1723 "rows": 2,
1724 "cost": 1.82326,
1725 "chosen": true
1726 },
1727 {
1728 "rows_to_scan": 4,
1729 "filtering_effect": [
1730 ] /* filtering_effect */,
1731 "final_filtering_effect": 1,
1732 "access_type": "scan",
1733 "using_join_cache": true,
1734 "buffers_needed": 1,
1735 "resulting_rows": 4,
1736 "cost": 2.10175,
1737 "chosen": false
1738 }
1739 ] /* considered_access_paths */
1740 } /* best_access_path */,
1741 "condition_filtering_pct": 100,
1742 "rows_for_plan": 8,
1743 "cost_for_plan": 2.72496,
1744 "semijoin_strategy_choice": [
1745 {
1746 "strategy": "MaterializeScan",
1747 "choice": "deferred"
1748 }
1749 ] /* semijoin_strategy_choice */,
1750 "rest_of_plan": [
1751 {
1752 "plan_prefix": [
1753 "`t1` `table1`",
1754 "`t1` `table2`"
1755 ] /* plan_prefix */,
1756 "table": "`where_subselect_20070`",
1757 "best_access_path": {
1758 "considered_access_paths": [
1759 {
1760 "rows_to_scan": 6,
1761 "filtering_effect": [
1762 ] /* filtering_effect */,
1763 "final_filtering_effect": 1,
1764 "access_type": "scan",
1765 "using_join_cache": true,
1766 "buffers_needed": 1,
1767 "resulting_rows": 6,
1768 "cost": 5.30491,
1769 "chosen": true
1770 }
1771 ] /* considered_access_paths */
1772 } /* best_access_path */,
1773 "condition_filtering_pct": 16.6667,
1774 "rows_for_plan": 8,
1775 "cost_for_plan": 8.02988,
1776 "semijoin_strategy_choice": [
1777 {
1778 "strategy": "LooseScan",
1779 "recalculate_access_paths_and_cost": {
1780 "tables": [
1781 {
1782 "table": "`t1` `table1`",
1783 "best_access_path": {
1784 "considered_access_paths": [
1785 {
1786 "access_type": "ref",
1787 "index": "col_int_key",
1788 "usable": false,
1789 "chosen": false
1790 },
1791 {
1792 "rows_to_scan": 4,
1793 "filtering_effect": [
1794 ] /* filtering_effect */,
1795 "final_filtering_effect": 1,
1796 "access_type": "scan",
1797 "resulting_rows": 4,
1798 "cost": 0.901709,
1799 "chosen": true
1800 }
1801 ] /* considered_access_paths */
1802 } /* best_access_path */,
1803 "unknown_key_1": {
1804 "searching_loose_scan_index": {
1805 "indexes": [
1806 {
1807 "index": "col_int_key",
1808 "index_handles_needed_semijoin_equalities": false
1809 }
1810 ] /* indexes */
1811 } /* searching_loose_scan_index */
1812 }
1813 }
1814 ] /* tables */
1815 } /* recalculate_access_paths_and_cost */,
1816 "chosen": false
1817 },
1818 {
1819 "strategy": "MaterializeScan",
1820 "recalculate_access_paths_and_cost": {
1821 "tables": [
1822 {
1823 "table": "`where_subselect_20070`",
1824 "best_access_path": {
1825 "considered_access_paths": [
1826 {
1827 "rows_to_scan": 6,
1828 "filtering_effect": [
1829 ] /* filtering_effect */,
1830 "final_filtering_effect": 1,
1831 "access_type": "scan",
1832 "using_join_cache": true,
1833 "buffers_needed": 1,
1834 "resulting_rows": 6,
1835 "cost": 2.90484,
1836 "chosen": true
1837 }
1838 ] /* considered_access_paths */
1839 } /* best_access_path */
1840 }
1841 ] /* tables */
1842 } /* recalculate_access_paths_and_cost */,
1843 "cost": 7.8298,
1844 "rows": 1,
1845 "duplicate_tables_left": true,
1846 "chosen": true
1847 },
1848 {
1849 "strategy": "DuplicatesWeedout",
1850 "cost": 9.92988,
1851 "rows": 1,
1852 "duplicate_tables_left": false,
1853 "chosen": false
1854 }
1855 ] /* semijoin_strategy_choice */,
1856 "pruned_by_cost": true
1857 }
1858 ] /* rest_of_plan */
1859 }
1860 ] /* rest_of_plan */
1861 },
1862 {
1863 "plan_prefix": [
1864 ] /* plan_prefix */,
1865 "table": "`t1` `table2`",
1866 "best_access_path": {
1867 "considered_access_paths": [
1868 {
1869 "access_type": "ref",
1870 "index": "col_int_key",
1871 "usable": false,
1872 "chosen": false
1873 },
1874 {
1875 "rows_to_scan": 4,
1876 "filtering_effect": [
1877 ] /* filtering_effect */,
1878 "final_filtering_effect": 1,
1879 "access_type": "scan",
1880 "resulting_rows": 4,
1881 "cost": 0.901709,
1882 "chosen": true
1883 }
1884 ] /* considered_access_paths */
1885 } /* best_access_path */,
1886 "condition_filtering_pct": 100,
1887 "rows_for_plan": 4,
1888 "cost_for_plan": 0.901709,
1889 "semijoin_strategy_choice": [
1890 ] /* semijoin_strategy_choice */,
1891 "rest_of_plan": [
1892 {
1893 "plan_prefix": [
1894 "`t1` `table2`"
1895 ] /* plan_prefix */,
1896 "table": "`where_subselect_20070`",
1897 "best_access_path": {
1898 "considered_access_paths": [
1899 {
1900 "rows_to_scan": 6,
1901 "filtering_effect": [
1902 ] /* filtering_effect */,
1903 "final_filtering_effect": 1,
1904 "access_type": "scan",
1905 "using_join_cache": true,
1906 "buffers_needed": 1,
1907 "resulting_rows": 6,
1908 "cost": 2.9048,
1909 "chosen": true
1910 }
1911 ] /* considered_access_paths */
1912 } /* best_access_path */,
1913 "condition_filtering_pct": 16.6667,
1914 "rows_for_plan": 4,
1915 "cost_for_plan": 3.80651,
1916 "semijoin_strategy_choice": [
1917 ] /* semijoin_strategy_choice */,
1918 "rest_of_plan": [
1919 {
1920 "plan_prefix": [
1921 "`t1` `table2`",
1922 "`where_subselect_20070`"
1923 ] /* plan_prefix */,
1924 "table": "`t1` `table1`",
1925 "best_access_path": {
1926 "considered_access_paths": [
1927 {
1928 "access_type": "ref",
1929 "index": "col_int_key",
1930 "rows": 2,
1931 "cost": 1.82326,
1932 "chosen": true
1933 },
1934 {
1935 "rows_to_scan": 4,
1936 "filtering_effect": [
1937 ] /* filtering_effect */,
1938 "final_filtering_effect": 1,
1939 "access_type": "scan",
1940 "using_join_cache": true,
1941 "buffers_needed": 1,
1942 "resulting_rows": 4,
1943 "cost": 2.10185,
1944 "chosen": false
1945 }
1946 ] /* considered_access_paths */
1947 } /* best_access_path */,
1948 "condition_filtering_pct": 100,
1949 "rows_for_plan": 8,
1950 "cost_for_plan": 5.62976,
1951 "semijoin_strategy_choice": [
1952 {
1953 "strategy": "DuplicatesWeedout",
1954 "cost": 7.52976,
1955 "rows": 1,
1956 "duplicate_tables_left": true,
1957 "chosen": true
1958 }
1959 ] /* semijoin_strategy_choice */,
1960 "pruned_by_cost": true
1961 }
1962 ] /* rest_of_plan */
1963 },
1964 {
1965 "plan_prefix": [
1966 "`t1` `table2`"
1967 ] /* plan_prefix */,
1968 "table": "`t1` `table1`",
1969 "best_access_path": {
1970 "considered_access_paths": [
1971 {
1972 "access_type": "ref",
1973 "index": "col_int_key",
1974 "rows": 2,
1975 "cost": 1.82326,
1976 "chosen": true
1977 },
1978 {
1979 "rows_to_scan": 4,
1980 "filtering_effect": [
1981 ] /* filtering_effect */,
1982 "final_filtering_effect": 1,
1983 "access_type": "scan",
1984 "using_join_cache": true,
1985 "buffers_needed": 1,
1986 "resulting_rows": 4,
1987 "cost": 2.10175,
1988 "chosen": false
1989 }
1990 ] /* considered_access_paths */
1991 } /* best_access_path */,
1992 "condition_filtering_pct": 100,
1993 "rows_for_plan": 8,
1994 "cost_for_plan": 2.72496,
1995 "semijoin_strategy_choice": [
1996 {
1997 "strategy": "MaterializeScan",
1998 "choice": "deferred"
1999 }
2000 ] /* semijoin_strategy_choice */,
2001 "rest_of_plan": [
2002 {
2003 "plan_prefix": [
2004 "`t1` `table2`",
2005 "`t1` `table1`"
2006 ] /* plan_prefix */,
2007 "table": "`where_subselect_20070`",
2008 "best_access_path": {
2009 "considered_access_paths": [
2010 {
2011 "rows_to_scan": 6,
2012 "filtering_effect": [
2013 ] /* filtering_effect */,
2014 "final_filtering_effect": 1,
2015 "access_type": "scan",
2016 "using_join_cache": true,
2017 "buffers_needed": 1,
2018 "resulting_rows": 6,
2019 "cost": 5.30491,
2020 "chosen": true
2021 }
2022 ] /* considered_access_paths */
2023 } /* best_access_path */,
2024 "condition_filtering_pct": 16.6667,
2025 "rows_for_plan": 8,
2026 "cost_for_plan": 8.02988,
2027 "semijoin_strategy_choice": [
2028 {
2029 "strategy": "LooseScan",
2030 "recalculate_access_paths_and_cost": {
2031 "tables": [
2032 {
2033 "table": "`t1` `table2`",
2034 "best_access_path": {
2035 "considered_access_paths": [
2036 {
2037 "access_type": "ref",
2038 "index": "col_int_key",
2039 "usable": false,
2040 "chosen": false
2041 },
2042 {
2043 "rows_to_scan": 4,
2044 "filtering_effect": [
2045 ] /* filtering_effect */,
2046 "final_filtering_effect": 1,
2047 "access_type": "scan",
2048 "resulting_rows": 4,
2049 "cost": 0.901709,
2050 "chosen": true
2051 }
2052 ] /* considered_access_paths */
2053 } /* best_access_path */,
2054 "unknown_key_2": {
2055 "searching_loose_scan_index": {
2056 "indexes": [
2057 {
2058 "index": "col_int_key",
2059 "covering_scan": {
2060 "cost": 0.267442,
2061 "chosen": true
2062 } /* covering_scan */
2063 }
2064 ] /* indexes */
2065 } /* searching_loose_scan_index */
2066 }
2067 },
2068 {
2069 "table": "`where_subselect_20070`",
2070 "best_access_path": {
2071 "considered_access_paths": [
2072 {
2073 "rows_to_scan": 6,
2074 "filtering_effect": [
2075 ] /* filtering_effect */,
2076 "final_filtering_effect": 1,
2077 "access_type": "scan",
2078 "using_join_cache": true,
2079 "buffers_needed": 1,
2080 "resulting_rows": 6,
2081 "cost": 5.30491,
2082 "chosen": true
2083 }
2084 ] /* considered_access_paths */
2085 } /* best_access_path */
2086 }
2087 ] /* tables */
2088 } /* recalculate_access_paths_and_cost */,
2089 "cost": 7.79561,
2090 "rows": 1,
2091 "chosen": true
2092 },
2093 {
2094 "strategy": "MaterializeScan",
2095 "recalculate_access_paths_and_cost": {
2096 "tables": [
2097 {
2098 "table": "`where_subselect_20070`",
2099 "best_access_path": {
2100 "considered_access_paths": [
2101 {
2102 "rows_to_scan": 6,
2103 "filtering_effect": [
2104 ] /* filtering_effect */,
2105 "final_filtering_effect": 1,
2106 "access_type": "scan",
2107 "using_join_cache": true,
2108 "buffers_needed": 1,
2109 "resulting_rows": 6,
2110 "cost": 2.90484,
2111 "chosen": true
2112 }
2113 ] /* considered_access_paths */
2114 } /* best_access_path */
2115 }
2116 ] /* tables */
2117 } /* recalculate_access_paths_and_cost */,
2118 "cost": 7.8298,
2119 "rows": 1,
2120 "duplicate_tables_left": false,
2121 "chosen": false
2122 },
2123 {
2124 "strategy": "DuplicatesWeedout",
2125 "cost": 9.92988,
2126 "rows": 1,
2127 "duplicate_tables_left": false,
2128 "chosen": false
2129 }
2130 ] /* semijoin_strategy_choice */,
2131 "pruned_by_cost": true
2132 }
2133 ] /* rest_of_plan */
2134 }
2135 ] /* rest_of_plan */
2136 },
2137 {
2138 "final_semijoin_strategy": "MaterializeLookup"
2139 }
2140 ] /* considered_execution_plans */
2141 },
2142 {
2143 "creating_tmp_table": {
2144 "tmp_table_info": {
2145 "table": "<subquery3>",
2146 "columns": 2,
2147 "row_length": 13,
2148 "key_length": 14,
2149 "unique_constraint": false,
2150 "makes_grouped_rows": false,
2151 "cannot_insert_duplicates": true,
2152 "location": "TempTable"
2153 } /* tmp_table_info */
2154 } /* creating_tmp_table */
2155 },
2156 {
2157 "subselect_execution": {
2158 "select#": 2,
2159 "steps": [
2160 ] /* steps */
2161 } /* subselect_execution */
2162 },
2163 {
2164 "attaching_conditions_to_tables": {
2165 "original_condition": "((`<subquery3>`.`field1` = `where_subselect_20070`.`field1`) and (`table2`.`col_int_key` = `table1`.`col_int_key`))",
2166 "attached_conditions_computation": [
2167 {
2168 "table": "`t1` `table1`",
2169 "rechecking_index_usage": {
2170 "recheck_reason": "not_first_table",
2171 "range_analysis": {
2172 "table_scan": {
2173 "rows": 4,
2174 "cost": 3.00171
2175 } /* table_scan */,
2176 "potential_range_indexes": [
2177 {
2178 "index": "col_int_key",
2179 "usable": true,
2180 "key_parts": [
2181 "col_int_key"
2182 ] /* key_parts */
2183 }
2184 ] /* potential_range_indexes */,
2185 "best_covering_index_scan": {
2186 "index": "col_int_key",
2187 "cost": 0.667442,
2188 "chosen": true
2189 } /* best_covering_index_scan */,
2190 "setup_range_conditions": [
2191 ] /* setup_range_conditions */,
2192 "group_index_range": {
2193 "chosen": false,
2194 "cause": "not_original_query_table"
2195 } /* group_index_range */,
2196 "skip_scan_range": {
2197 "chosen": false,
2198 "cause": "not_single_table"
2199 } /* skip_scan_range */,
2200 "analyzing_range_alternatives": {
2201 "range_scan_alternatives": [
2202 {
2203 "index": "col_int_key",
2204 "ranges": [
2205 "NULL < col_int_key"
2206 ] /* ranges */,
2207 "index_dives_for_eq_ranges": true,
2208 "rowid_ordered": false,
2209 "using_mrr": false,
2210 "index_only": true,
2211 "in_memory": -1,
2212 "rows": 4,
2213 "cost": 0.677442,
2214 "chosen": false,
2215 "cause": "cost"
2216 }
2217 ] /* range_scan_alternatives */,
2218 "analyzing_roworder_intersect": {
2219 "usable": false,
2220 "cause": "too_few_roworder_scans"
2221 } /* analyzing_roworder_intersect */
2222 } /* analyzing_range_alternatives */
2223 } /* range_analysis */
2224 } /* rechecking_index_usage */
2225 }
2226 ] /* attached_conditions_computation */,
2227 "attached_conditions_summary": [
2228 {
2229 "table": "`where_subselect_20070`",
2230 "attached": "(`where_subselect_20070`.`field1` is not null)"
2231 },
2232 {
2233 "table": "`<subquery3>`",
2234 "attached": "(`<subquery3>`.`field1` = `where_subselect_20070`.`field1`)"
2235 },
2236 {
2237 "table": "`t1` `table1`",
2238 "attached": "(`table1`.`col_int_key` is not null)"
2239 },
2240 {
2241 "table": "`t1` `table2`",
2242 "attached": "(`table2`.`col_int_key` = `table1`.`col_int_key`)"
2243 }
2244 ] /* attached_conditions_summary */
2245 } /* attaching_conditions_to_tables */
2246 },
2247 {
2248 "finalizing_table_conditions": [
2249 {
2250 "table": "`where_subselect_20070`",
2251 "original_table_condition": "(`where_subselect_20070`.`field1` is not null)",
2252 "final_table_condition ": "(`where_subselect_20070`.`field1` is not null)"
2253 },
2254 {
2255 "table": "`<subquery3>`",
2256 "original_table_condition": "(`<subquery3>`.`field1` = `where_subselect_20070`.`field1`)",
2257 "final_table_condition ": null
2258 },
2259 {
2260 "table": "`t1` `table1`",
2261 "original_table_condition": "(`table1`.`col_int_key` is not null)",
2262 "final_table_condition ": "(`table1`.`col_int_key` is not null)"
2263 },
2264 {
2265 "table": "`t1` `table2`",
2266 "original_table_condition": "(`table2`.`col_int_key` = `table1`.`col_int_key`)",
2267 "final_table_condition ": null
2268 }
2269 ] /* finalizing_table_conditions */
2270 },
2271 {
2272 "refine_plan": [
2273 {
2274 "table": "`where_subselect_20070`"
2275 },
2276 {
2277 "table": "`<subquery3>`"
2278 },
2279 {
2280 "table": "`t1` `table1`"
2281 },
2282 {
2283 "table": "`t1` `table2`"
2284 }
2285 ] /* refine_plan */
2286 }
2287 ] /* steps */
2288 } /* join_optimization */
2289 },
2290 {
2291 "join_execution": {
2292 "select#": 1,
2293 "steps": [
2294 {
2295 "materialize": {
2296 "select#": 1,
2297 "steps": [
2298 {
2299 "subselect_execution": {
2300 "select#": 4,
2301 "steps": [
2302 ] /* steps */
2303 } /* subselect_execution */
2304 },
2305 {
2306 "subselect_execution": {
2307 "select#": 4,
2308 "steps": [
2309 ] /* steps */
2310 } /* subselect_execution */
2311 },
2312 {
2313 "subselect_execution": {
2314 "select#": 4,
2315 "steps": [
2316 ] /* steps */
2317 } /* subselect_execution */
2318 },
2319 {
2320 "subselect_execution": {
2321 "select#": 4,
2322 "steps": [
2323 ] /* steps */
2324 } /* subselect_execution */
2325 },
2326 {
2327 "subselect_execution": {
2328 "select#": 4,
2329 "steps": [
2330 ] /* steps */
2331 } /* subselect_execution */
2332 },
2333 {
2334 "subselect_execution": {
2335 "select#": 4,
2336 "steps": [
2337 ] /* steps */
2338 } /* subselect_execution */
2339 },
2340 {
2341 "subselect_execution": {
2342 "select#": 4,
2343 "steps": [
2344 ] /* steps */
2345 } /* subselect_execution */
2346 },
2347 {
2348 "subselect_execution": {
2349 "select#": 4,
2350 "steps": [
2351 ] /* steps */
2352 } /* subselect_execution */
2353 },
2354 {
2355 "subselect_execution": {
2356 "select#": 4,
2357 "steps": [
2358 ] /* steps */
2359 } /* subselect_execution */
2360 },
2361 {
2362 "subselect_execution": {
2363 "select#": 4,
2364 "steps": [
2365 ] /* steps */
2366 } /* subselect_execution */
2367 },
2368 {
2369 "subselect_execution": {
2370 "select#": 4,
2371 "steps": [
2372 ] /* steps */
2373 } /* subselect_execution */
2374 },
2375 {
2376 "subselect_execution": {
2377 "select#": 4,
2378 "steps": [
2379 ] /* steps */
2380 } /* subselect_execution */
2381 }
2382 ] /* steps */
2383 } /* materialize */
2384 }
2385 ] /* steps */
2386 } /* join_execution */
2387 }
2388 ] /* steps */
2389 } 0 0
2390 DROP TABLE where_subselect_20070,t1;
2391 #
2392 # Bug#13430443 - ASSERTION `NEW_TYPE[0] != 'U'' FAILED. WHEN
2393 # OPTIMIZER_TRACE IS ENABLED
2394 #
2395 CREATE TABLE t1
2396 (a INT,b INT,c INT, KEY(a),KEY (a,c)) ENGINE=INNODB;
2397 SELECT 1 FROM t1 WHERE 1 LIKE
2398 (SELECT a FROM t1 WHERE a = 1 ORDER BY c);
2399 1
2400 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2401 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2402 SELECT 1 FROM t1 WHERE 1 LIKE
2403 (SELECT a FROM t1 WHERE a = 1 ORDER BY c) {
2404 "steps": [
2405 {
2406 "join_preparation": {
2407 "select#": 1,
2408 "steps": [
2409 {
2410 "join_preparation": {
2411 "select#": 2,
2412 "steps": [
2413 {
2414 "transformations_to_subquery": [
2415 "removed_ordering"
2416 ] /* transformations_to_subquery */
2417 },
2418 {
2419 "expanded_query": "/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)"
2420 }
2421 ] /* steps */
2422 } /* join_preparation */
2423 },
2424 {
2425 "expanded_query": "/* select#1 */ select 1 AS `1` from `t1` where (1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))"
2426 }
2427 ] /* steps */
2428 } /* join_preparation */
2429 },
2430 {
2431 "join_optimization": {
2432 "select#": 1,
2433 "steps": [
2434 {
2435 "condition_processing": {
2436 "condition": "WHERE",
2437 "original_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))",
2438 "steps": [
2439 {
2440 "transformation": "equality_propagation",
2441 "subselect_evaluation": [
2442 ] /* subselect_evaluation */,
2443 "resulting_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))"
2444 },
2445 {
2446 "transformation": "constant_propagation",
2447 "subselect_evaluation": [
2448 ] /* subselect_evaluation */,
2449 "resulting_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))"
2450 },
2451 {
2452 "transformation": "trivial_condition_removal",
2453 "subselect_evaluation": [
2454 {
2455 "subselect_execution": {
2456 "select#": 2,
2457 "steps": [
2458 {
2459 "join_optimization": {
2460 "select#": 2,
2461 "steps": [
2462 {
2463 "condition_processing": {
2464 "condition": "WHERE",
2465 "original_condition": "(`t1`.`a` = 1)",
2466 "steps": [
2467 {
2468 "transformation": "equality_propagation",
2469 "resulting_condition": "multiple equal(1, `t1`.`a`)"
2470 },
2471 {
2472 "transformation": "constant_propagation",
2473 "resulting_condition": "multiple equal(1, `t1`.`a`)"
2474 },
2475 {
2476 "transformation": "trivial_condition_removal",
2477 "resulting_condition": "multiple equal(1, `t1`.`a`)"
2478 }
2479 ] /* steps */
2480 } /* condition_processing */
2481 },
2482 {
2483 "substitute_generated_columns": {
2484 } /* substitute_generated_columns */
2485 },
2486 {
2487 "table_dependencies": [
2488 {
2489 "table": "`t1`",
2490 "row_may_be_null": false,
2491 "map_bit": 0,
2492 "depends_on_map_bits": [
2493 ] /* depends_on_map_bits */
2494 }
2495 ] /* table_dependencies */
2496 },
2497 {
2498 "ref_optimizer_key_uses": [
2499 {
2500 "table": "`t1`",
2501 "field": "a",
2502 "equals": "1",
2503 "null_rejecting": true
2504 },
2505 {
2506 "table": "`t1`",
2507 "field": "a",
2508 "equals": "1",
2509 "null_rejecting": true
2510 }
2511 ] /* ref_optimizer_key_uses */
2512 },
2513 {
2514 "rows_estimation": [
2515 {
2516 "table": "`t1`",
2517 "range_analysis": {
2518 "table_scan": {
2519 "rows": 1,
2520 "cost": 2.45
2521 } /* table_scan */,
2522 "potential_range_indexes": [
2523 {
2524 "index": "a",
2525 "usable": true,
2526 "key_parts": [
2527 "a"
2528 ] /* key_parts */
2529 },
2530 {
2531 "index": "a_2",
2532 "usable": true,
2533 "key_parts": [
2534 "a",
2535 "c"
2536 ] /* key_parts */
2537 }
2538 ] /* potential_range_indexes */,
2539 "best_covering_index_scan": {
2540 "index": "a_2",
2541 "cost": 0.35,
2542 "chosen": true
2543 } /* best_covering_index_scan */,
2544 "setup_range_conditions": [
2545 ] /* setup_range_conditions */,
2546 "group_index_range": {
2547 "chosen": false,
2548 "cause": "not_group_by_or_distinct"
2549 } /* group_index_range */,
2550 "skip_scan_range": {
2551 "potential_skip_scan_indexes": [
2552 {
2553 "index": "a",
2554 "usable": false,
2555 "cause": "query_references_nonkey_column"
2556 },
2557 {
2558 "index": "a_2",
2559 "usable": false,
2560 "cause": "no_range_predicate"
2561 }
2562 ] /* potential_skip_scan_indexes */
2563 } /* skip_scan_range */,
2564 "analyzing_range_alternatives": {
2565 "range_scan_alternatives": [
2566 {
2567 "index": "a",
2568 "ranges": [
2569 "a = 1"
2570 ] /* ranges */,
2571 "index_dives_for_eq_ranges": true,
2572 "rowid_ordered": true,
2573 "using_mrr": true,
2574 "index_only": false,
2575 "in_memory": 1,
2576 "rows": 1,
2577 "cost": 0.6,
2578 "chosen": false,
2579 "cause": "cost"
2580 },
2581 {
2582 "index": "a_2",
2583 "ranges": [
2584 "a = 1"
2585 ] /* ranges */,
2586 "index_dives_for_eq_ranges": true,
2587 "rowid_ordered": false,
2588 "using_mrr": false,
2589 "index_only": true,
2590 "in_memory": 1,
2591 "rows": 1,
2592 "cost": 0.36,
2593 "chosen": false,
2594 "cause": "cost"
2595 }
2596 ] /* range_scan_alternatives */,
2597 "analyzing_roworder_intersect": {
2598 "usable": false,
2599 "cause": "too_few_roworder_scans"
2600 } /* analyzing_roworder_intersect */
2601 } /* analyzing_range_alternatives */
2602 } /* range_analysis */
2603 }
2604 ] /* rows_estimation */
2605 },
2606 {
2607 "considered_execution_plans": [
2608 {
2609 "plan_prefix": [
2610 ] /* plan_prefix */,
2611 "table": "`t1`",
2612 "best_access_path": {
2613 "considered_access_paths": [
2614 {
2615 "access_type": "ref",
2616 "index": "a",
2617 "rows": 1,
2618 "cost": 0.35,
2619 "chosen": true
2620 },
2621 {
2622 "access_type": "ref",
2623 "index": "a_2",
2624 "rows": 1,
2625 "cost": 0.35,
2626 "chosen": false
2627 },
2628 {
2629 "access_type": "scan",
2630 "chosen": false,
2631 "cause": "covering_index_better_than_full_scan"
2632 }
2633 ] /* considered_access_paths */
2634 } /* best_access_path */,
2635 "condition_filtering_pct": 100,
2636 "rows_for_plan": 1,
2637 "cost_for_plan": 0.35,
2638 "chosen": true
2639 }
2640 ] /* considered_execution_plans */
2641 },
2642 {
2643 "attaching_conditions_to_tables": {
2644 "original_condition": "(`t1`.`a` = 1)",
2645 "attached_conditions_computation": [
2646 ] /* attached_conditions_computation */,
2647 "attached_conditions_summary": [
2648 {
2649 "table": "`t1`",
2650 "attached": "(`t1`.`a` = 1)"
2651 }
2652 ] /* attached_conditions_summary */
2653 } /* attaching_conditions_to_tables */
2654 },
2655 {
2656 "finalizing_table_conditions": [
2657 {
2658 "table": "`t1`",
2659 "original_table_condition": "(`t1`.`a` = 1)",
2660 "final_table_condition ": null
2661 }
2662 ] /* finalizing_table_conditions */
2663 },
2664 {
2665 "refine_plan": [
2666 {
2667 "table": "`t1`"
2668 }
2669 ] /* refine_plan */
2670 }
2671 ] /* steps */
2672 } /* join_optimization */
2673 },
2674 {
2675 "join_execution": {
2676 "select#": 2,
2677 "steps": [
2678 ] /* steps */
2679 } /* join_execution */
2680 }
2681 ] /* steps */
2682 } /* subselect_execution */
2683 }
2684 ] /* subselect_evaluation */,
2685 "resulting_condition": null
2686 }
2687 ] /* steps */
2688 } /* condition_processing */
2689 }
2690 ] /* steps */,
2691 "empty_result": {
2692 "cause": "Impossible WHERE"
2693 } /* empty_result */
2694 } /* join_optimization */
2695 },
2696 {
2697 "join_execution": {
2698 "select#": 1,
2699 "steps": [
2700 ] /* steps */
2701 } /* join_execution */
2702 }
2703 ] /* steps */
2704 } 0 0
2705 DROP TABLE t1;
2706 #
2707 # Bug #18346750 OPTIMIZER_TRACE & DBUG_PRINT CRASH IN ST_SELECT_LEX::PRINT
2708 #
2709 "Skipped since this engine does not support native partitioning.
2710 #
2711 # Bug#18791851 CRASH IN ST_SELECT_LEX::PRINT WITH OPTIMIZER_TRACE ON SUBQUERY
2712 #
2713 CREATE TABLE t1 (
2714 pk INT NOT NULL,
2715 col_int_nokey INT,
2716 col_int_key INT,
2717 col_time_key time,
2718 col_varchar_key VARCHAR(1),
2719 col_varchar_nokey VARCHAR(1),
2720 PRIMARY KEY (pk),
2721 KEY col_varchar_key (col_varchar_key,col_int_key)
2722 );
2723 CREATE TABLE t2 (
2724 pk INT NOT NULL,
2725 col_int_nokey INT,
2726 col_int_key INT,
2727 col_varchar_key VARCHAR(1),
2728 col_varchar_nokey VARCHAR(1),
2729 PRIMARY KEY (pk),
2730 KEY col_varchar_key (col_varchar_key,col_int_key)
2731 );
2732 CREATE TABLE t3 (
2733 pk INT NOT NULL,
2734 col_int_nokey INT,
2735 col_int_key INT,
2736 col_varchar_key VARCHAR(1),
2737 col_varchar_nokey VARCHAR(1),
2738 PRIMARY KEY (pk),
2739 KEY col_varchar_key (col_varchar_key,col_int_key)
2740 );
2741 CREATE TABLE t4 (
2742 pk INT NOT NULL,
2743 col_int_nokey INT,
2744 col_int_key INT,
2745 col_time_key time,
2746 col_varchar_key VARCHAR(1),
2747 col_varchar_nokey VARCHAR(1),
2748 PRIMARY KEY (pk),
2749 KEY col_varchar_key (col_varchar_key,col_int_key)
2750 );
2751 SELECT alias1.col_int_nokey AS field1,
2752 alias2.col_varchar_key AS field2,
2753 alias1.col_time_key AS field3,
2754 MIN( alias1.col_int_nokey ) AS field4
2755 FROM (
2756 (
2757 SELECT SQ1_alias1.*
2758 FROM ( t1 AS SQ1_alias1, t2 AS SQ1_alias2 )
2759 ) AS alias1,
2760 t4 AS alias2
2761 )
2762 WHERE
2763 EXISTS (
2764 SELECT DISTINCT SQ2_alias2.col_varchar_nokey AS SQ2_field1
2765 FROM t2 AS SQ2_alias1
2766 INNER JOIN (t4 AS SQ2_alias2
2767 INNER JOIN t3 AS SQ2_alias3
2768 ON SQ2_alias3.pk = SQ2_alias2.pk)
2769 ON SQ2_alias3.col_varchar_key = SQ2_alias2.col_varchar_nokey
2770 )
2771 AND alias1.col_int_key = alias2.pk
2772 HAVING alias1.col_int_nokey IN ( SELECT 2 FROM DUAL ) ;
2773 field1 field2 field3 field4
2774 DROP TABLE t1,t2,t3,t4;
2775 CREATE TABLE t1(a INT);
2776 SET @a:=(SELECT ROW(1, 2)=
2777 ROW((SELECT 1 FROM t1 LEFT JOIN t1 t2 ON 1
2778 HAVING 3 IN (SELECT 2 FROM DUAL)),
2779 1));
2780 DROP TABLE t1;
2781 #
2782 # Bug #18945693 CRASH IN PRINT_TABLE_ARRAY AT SQL/SQL_LEX.CC ON 2ND EXEC OF PREPARED STATEMENT
2783 #
2784 CREATE TABLE t1 (
2785 pk INT NOT NULL,
2786 col_int_nokey INT,
2787 col_int_key INT,
2788 col_varchar_key VARCHAR(1),
2789 col_varchar_nokey VARCHAR(1)
2790 );
2791 INSERT INTO t1 VALUES (13,7,3,'y','y'),(14,0,4,'c','c');
2792 CREATE TABLE t2 (
2793 pk INT NOT NULL,
2794 col_int_nokey INT,
2795 col_int_key INT,
2796 col_varchar_key VARCHAR(1),
2797 col_varchar_nokey VARCHAR(1)
2798 );
2799 INSERT INTO t2 VALUES (20,9,8,'e','e');
2800 CREATE TABLE t3 (
2801 pk INT NOT NULL,
2802 col_int_nokey INT,
2803 col_int_key INT,
2804 col_varchar_key VARCHAR(1),
2805 col_varchar_nokey VARCHAR(1)
2806 );
2807 INSERT INTO t3 VALUES (1,1,7,'k','k');
2808 PREPARE prep_stmt FROM "
2809 SELECT
2810 alias1.col_varchar_nokey AS field1,
2811 MAX(alias1.pk) AS field2
2812 FROM t3 AS alias1
2813 GROUP BY field1
2814 HAVING
2815 1 > (
2816 SELECT MAX(SQ4_alias1.col_varchar_nokey)
2817 FROM t1 AS SQ4_alias1
2818 RIGHT OUTER JOIN
2819 (
2820 t2 AS SQ4_alias2
2821 JOIN t1 AS SQ4_alias3
2822 ON SQ4_alias3.col_int_key = SQ4_alias2.col_int_nokey
2823 )
2824 ON SQ4_alias3.pk = SQ4_alias2.col_int_nokey
2825 )
2826 ";
2827 EXECUTE prep_stmt;
2828 field1 field2
2829 EXECUTE prep_stmt;
2830 field1 field2
2831 DROP TABLE t1,t2,t3;
2832 #
2833 # Bug#19063289 CRASH IN PRINT_TABLE_ARRAY ON 2ND EXECUTION OF PS WITH SUBQUERY AND VIEW
2834 #
2835 CREATE TABLE B (
2836 pk INTEGER,
2837 col_int_key INTEGER,
2838 col_varchar_nokey VARCHAR(1)
2839 );
2840 CREATE TABLE C (
2841 col_int_nokey INTEGER,
2842 col_int_key INTEGER,
2843 col_varchar_key VARCHAR(1),
2844 col_varchar_nokey VARCHAR(1)
2845 ) ;
2846 CREATE TABLE CC (
2847 pk INTEGER,
2848 col_int_key INTEGER
2849 ) ENGINE=MYISAM;
2850 INSERT INTO CC VALUES (1, 0),(2, 7);
2851 PREPARE stmt FROM "
2852 SELECT
2853 alias1.col_int_key AS field1 ,
2854 COUNT(alias1.pk) AS field2
2855 FROM
2856 CC AS alias1
2857 GROUP BY field1
2858 HAVING
2859 alias1.col_int_key >
2860 (
2861 SELECT
2862 MAX( SQ2_alias2.col_int_key ) AS SQ2_field1
2863 FROM
2864 C AS SQ2_alias1 LEFT JOIN
2865 ( B AS SQ2_alias2 INNER JOIN C AS SQ2_alias3
2866 ON 1)
2867 ON 1
2868 )
2869 OR field2 < 2
2870 ";
2871 EXECUTE stmt;
2872 field1 field2
2873 0 1
2874 7 1
2875 EXECUTE stmt;
2876 field1 field2
2877 0 1
2878 7 1
2879 DROP TABLE B,C,CC;
2880 #
2881 # Bug #23259872: OPTIMIZER CHOOSES TO USE NON PRIMARY
2882 # INDEX, EVEN THOUGH COST IS HIGHER
2883 #
2884 CREATE TABLE t1 (
2885 a TINYTEXT NOT NULL,
2886 b TINYINT(3) UNSIGNED NOT NULL,
2887 PRIMARY KEY (a(32),b)
2888 ) ENGINE=MyISAM, CHARSET utf8mb4;
2889 Warnings:
2890 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2891 CREATE TABLE t2 (
2892 a TINYTEXT NOT NULL,
2893 b TINYINT(3) UNSIGNED NOT NULL,
2894 PRIMARY KEY (a(32),b)
2895 ) ENGINE=INNODB, CHARSET utf8mb4;
2896 Warnings:
2897 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2898 INSERT INTO t1 VALUES ('a',1),('a',2);
2899 INSERT INTO t2 VALUES ('a',1),('a',2),('a',3),('b',1),('c',1),('c',4),('e',2);
2900 ANALYZE TABLE t1;
2901 Table Op Msg_type Msg_text
2902 test.t1 analyze status OK
2903 ANALYZE TABLE t2;
2904 Table Op Msg_type Msg_text
2905 test.t2 analyze status OK
2906 EXPLAIN SELECT * FROM t1 WHERE a='a' AND b in (1,2);
2907 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2908 1 SIMPLE t1 NULL range PRIMARY PRIMARY 131 NULL 2 100.00 Using where
2909 Warnings:
2910 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)))
2911 SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2912 SELECT @trace RLIKE "range_uses_more_keyparts";
2913 @trace RLIKE "range_uses_more_keyparts"
2914 1
2915 EXPLAIN SELECT * FROM t2 WHERE a='a' AND b in (1,2);
2916 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2917 1 SIMPLE t2 NULL range PRIMARY PRIMARY 131 NULL 2 100.00 Using where
2918 Warnings:
2919 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)))
2920 SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2921 SELECT @trace RLIKE "range_uses_more_keyparts";
2922 @trace RLIKE "range_uses_more_keyparts"
2923 1
2924 DROP TABLE t1, t2;
2925 #
2926 # Bug #23227428: SQL PLAN IS NOT ACCORDING WITH OPTIMIZER_TRACE
2927 #
2928 CREATE TABLE t1(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
2929 c2 CHAR(12)) ENGINE=INNODB;
2930 ANALYZE TABLE t1;
2931 Table Op Msg_type Msg_text
2932 test.t1 analyze status OK
2933 EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1;
2934 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2935 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 1 100.00 Backward index scan
2936 Warnings:
2937 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
2938 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2939 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2940 EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1 {
2941 "steps": [
2942 {
2943 "join_preparation": {
2944 "select#": 1,
2945 "steps": [
2946 {
2947 "expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` order by `t1`.`c1` desc limit 1"
2948 }
2949 ] /* steps */
2950 } /* join_preparation */
2951 },
2952 {
2953 "join_optimization": {
2954 "select#": 1,
2955 "steps": [
2956 {
2957 "substitute_generated_columns": {
2958 } /* substitute_generated_columns */
2959 },
2960 {
2961 "table_dependencies": [
2962 {
2963 "table": "`t1`",
2964 "row_may_be_null": false,
2965 "map_bit": 0,
2966 "depends_on_map_bits": [
2967 ] /* depends_on_map_bits */
2968 }
2969 ] /* table_dependencies */
2970 },
2971 {
2972 "rows_estimation": [
2973 {
2974 "table": "`t1`",
2975 "table_scan": {
2976 "rows": 1000,
2977 "cost": NN
2978 } /* table_scan */
2979 }
2980 ] /* rows_estimation */
2981 },
2982 {
2983 "considered_execution_plans": [
2984 {
2985 "plan_prefix": [
2986 ] /* plan_prefix */,
2987 "table": "`t1`",
2988 "best_access_path": {
2989 "considered_access_paths": [
2990 {
2991 "rows_to_scan": 1000,
2992 "access_type": "scan",
2993 "resulting_rows": 1000,
2994 "cost": NN,
2995 "chosen": true
2996 }
2997 ] /* considered_access_paths */
2998 } /* best_access_path */,
2999 "condition_filtering_pct": 100,
3000 "rows_for_plan": 1000,
3001 "cost_for_plan": NN,
3002 "chosen": true
3003 }
3004 ] /* considered_execution_plans */
3005 },
3006 {
3007 "attaching_conditions_to_tables": {
3008 "original_condition": null,
3009 "attached_conditions_computation": [
3010 ] /* attached_conditions_computation */,
3011 "attached_conditions_summary": [
3012 {
3013 "table": "`t1`",
3014 "attached": null
3015 }
3016 ] /* attached_conditions_summary */
3017 } /* attaching_conditions_to_tables */
3018 },
3019 {
3020 "optimizing_distinct_group_by_order_by": {
3021 "simplifying_order_by": {
3022 "original_clause": "`t1`.`c1` desc",
3023 "items": [
3024 {
3025 "item": "`t1`.`c1`"
3026 }
3027 ] /* items */,
3028 "resulting_clause_is_simple": true,
3029 "resulting_clause": "`t1`.`c1` desc"
3030 } /* simplifying_order_by */
3031 } /* optimizing_distinct_group_by_order_by */
3032 },
3033 {
3034 "reconsidering_access_paths_for_index_ordering": {
3035 "clause": "ORDER BY",
3036 "steps": [
3037 ] /* steps */,
3038 "index_order_summary": {
3039 "table": "`t1`",
3040 "index_provides_order": true,
3041 "order_direction": "desc",
3042 "index": "PRIMARY",
3043 "plan_changed": true,
3044 "access_type": "index"
3045 } /* index_order_summary */
3046 } /* reconsidering_access_paths_for_index_ordering */
3047 },
3048 {
3049 "finalizing_table_conditions": [
3050 ] /* finalizing_table_conditions */
3051 },
3052 {
3053 "refine_plan": [
3054 {
3055 "table": "`t1`"
3056 }
3057 ] /* refine_plan */
3058 },
3059 {
3060 "considering_tmp_tables": [
3061 ] /* considering_tmp_tables */
3062 }
3063 ] /* steps */
3064 } /* join_optimization */
3065 },
3066 {
3067 "join_explain": {
3068 "select#": 1,
3069 "steps": [
3070 ] /* steps */
3071 } /* join_explain */
3072 }
3073 ] /* steps */
3074 } 0 0
3075 DROP TABLE t1;
3076 # Bug#29016383 - fil_report_invalid_page_access
3077 CREATE TABLE t1 (
3078 pk INTEGER PRIMARY KEY,
3079 a INTEGER,
3080 b VARCHAR(20)
3081 );
3082 CREATE TABLE t2 (
3083 pk INTEGER PRIMARY KEY,
3084 c VARCHAR(20),
3085 KEY (c)
3086 );
3087 INSERT INTO t2 VALUES
3088 (1,'koeiwsgpmfyvvuqvtjnc'),
3089 (2,'pmfyvvuqvtjncdsvq'),
3090 (3,'mfyv'),
3091 (4,NULL);
3092 SET end_markers_in_json=off;
3093 SELECT alias1.*
3094 FROM t2 AS alias1, t2 AS alias2
3095 WHERE alias2.c IN (
3096 WITH qn AS (SELECT b FROM t1 WHERE a = alias1.pk)
3097 SELECT * FROM qn);
3098 pk c
3099 # Prove that the predicate from the WHERE clause of the CTE
3100 # is decorrelated:
3101 SELECT json_extract(trace,'$**.decorrelated_predicates')
3102 FROM information_schema.optimizer_trace;
3103 json_extract(trace,'$**.decorrelated_predicates')
3104 [[{"inner": "`t1`.`b`", "outer": "`alias2`.`c`"}, {"inner": "`t1`.`a`", "outer": "`alias1`.`pk`"}]]
3105 DROP TABLE t1, t2;