Debian Codegraph @

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