Debian Codegraph @

mysql-test/suite/secondary_engine/r/transforms_hypergraph.result

raw
1 CREATE TABLE t1(a DATETIME NOT NULL);
2 INSERT INTO t1 VALUES (TIMESTAMP'2006-06-06 15:55:55');
3 ALTER TABLE t1 SECONDARY_ENGINE MOCK;
4 ALTER TABLE t1 SECONDARY_LOAD;
5 EXPLAIN SELECT max(a) from t1;
6 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
7 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
8 Warnings:
9 Note 1003 /* select#1 */ select max(`test`.`t1`.`a`) AS `max(a)` from `test`.`t1`
10 # Check how the two transforms {scalar,table}-subquery-to-derived
11 # behave in PREPARE-<change conditions>-EXECUTE--<change conditions>-EXECUTE
12
13 # Transform is not active for InnoDB while we the do initial PREPARE
14 PREPARE s FROM "EXPLAIN SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a=TIMESTAMP'2006-06-06 15:55:55')";
15 PREPARE s2 FROM "EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t1 AS t2)";
16 SET secondary_engine_cost_threshold = 0;
17 EXECUTE s;
18 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
19 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
20 Warnings:
21 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
22
23 EXECUTE s2;
24 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
25 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
26 Warnings:
27 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
28 SET optimizer_switch="subquery_to_derived=on";
29 EXECUTE s;
30 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
31 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
32 Warnings:
33 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
34
35 EXECUTE s2;
36 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
37 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
38 Warnings:
39 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
40 SET optimizer_switch="subquery_to_derived=off";
41 EXECUTE s;
42 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
43 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
44 Warnings:
45 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
46
47 EXECUTE s2;
48 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
49 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
50 Warnings:
51 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
52 SET secondary_engine_cost_threshold = 10000;
53 EXECUTE s;
54
55 EXECUTE s2;
56 SET optimizer_switch="subquery_to_derived=on";
57 EXECUTE s;
58
59 EXECUTE s2;
60 SET optimizer_switch="subquery_to_derived=off";
61 EXECUTE s;
62
63 EXECUTE s2;
64 SET secondary_engine_cost_threshold = 0;
65 EXECUTE s;
66 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
67 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
68 Warnings:
69 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
70
71 EXECUTE s2;
72 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
73 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
74 Warnings:
75 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
76 SET optimizer_switch="subquery_to_derived=on";
77 EXECUTE s;
78 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
79 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
80 Warnings:
81 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
82
83 EXECUTE s2;
84 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
85 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
86 Warnings:
87 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
88 SET optimizer_switch="subquery_to_derived=off";
89 EXECUTE s;
90 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
91 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
92 Warnings:
93 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
94
95 EXECUTE s2;
96 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
97 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
98 Warnings:
99 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
100 SET optimizer_switch="subquery_to_derived=on";
101 # Let transform be active for InnoDB while we the initial PREPARE
102 PREPARE s FROM "EXPLAIN SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a=TIMESTAMP'2006-06-06 15:55:55')";
103 EXECUTE s;
104 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
105 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
106 Warnings:
107 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
108
109 EXECUTE s2;
110 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
111 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
112 Warnings:
113 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
114 SET secondary_engine_cost_threshold = 0;
115 EXECUTE s;
116 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
117 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
118 Warnings:
119 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
120
121 EXECUTE s2;
122 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
123 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
124 Warnings:
125 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
126 SET optimizer_switch="subquery_to_derived=on";
127 EXECUTE s;
128 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
129 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
130 Warnings:
131 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
132
133 EXECUTE s2;
134 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
135 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
136 Warnings:
137 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
138 SET optimizer_switch="subquery_to_derived=off";
139 EXECUTE s;
140 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
141 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
142 Warnings:
143 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
144
145 EXECUTE s2;
146 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
147 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
148 Warnings:
149 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
150 SET secondary_engine_cost_threshold = 10000;
151 EXECUTE s;
152
153 EXECUTE s2;
154 SET optimizer_switch="subquery_to_derived=on";
155 EXECUTE s;
156
157 EXECUTE s2;
158 SET optimizer_switch="subquery_to_derived=off";
159 EXECUTE s;
160
161 EXECUTE s2;
162 SET secondary_engine_cost_threshold = 0;
163 EXECUTE s;
164 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
165 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
166 Warnings:
167 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
168
169 EXECUTE s2;
170 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
171 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
172 Warnings:
173 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
174 SET optimizer_switch="subquery_to_derived=on";
175 EXECUTE s;
176 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
177 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
178 Warnings:
179 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
180
181 EXECUTE s2;
182 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
183 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
184 Warnings:
185 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
186 SET optimizer_switch="subquery_to_derived=off";
187 EXECUTE s;
188 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
189 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
190 Warnings:
191 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1`) `derived_1_2` where multiple equal(TIMESTAMP'2006-06-06 15:55:55', `test`.`t1`.`a`, `derived_1_2`.`MAX(a)`)
192
193 EXECUTE s2;
194 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
195 1 NONE NULL NULL NULL NULL NULL NULL NULL NULL NULL Using secondary engine MOCK. Use EXPLAIN FORMAT=TREE to show the plan.
196 Warnings:
197 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (/* select#2 */ select distinct `test`.`t2`.`a` AS `Name_exp_1` from `test`.`t1` `t2`) `derived_1_2` on(multiple equal(`test`.`t1`.`a`, `derived_1_2`.`Name_exp_1`)) where (`derived_1_2`.`Name_exp_1` is null)
198 DROP PREPARE s;
199 DROP PREPARE s2;
200 DROP TABLE t1;
201 SET secondary_engine_cost_threshold = default;
202 SET optimizer_switch="subquery_to_derived=default";
203 #
204 # Bug#34445328 (Hypergraph) Optimizer degenerate join
205 # conditions when encountering semi join + outer join
206 #
207 CREATE TABLE t (a INT) SECONDARY_ENGINE MOCK;
208 INSERT INTO t VALUES (), (), ();
209 ALTER TABLE t SECONDARY_LOAD;
210 ANALYZE TABLE t;
211 Table Op Msg_type Msg_text
212 test.t analyze status OK
213 EXPLAIN FORMAT=TREE
214 SELECT 1
215 FROM t AS t1 LEFT JOIN t AS t2
216 ON 1 IN (
217 SELECT 1 FROM t AS t3 WHERE a <=>
218 (SELECT a FROM t AS t4)
219 );
220 EXPLAIN
221 -> Left hash join (no condition) (rows=3)
222 -> Table scan on t1 in secondary engine MOCK (rows=3)
223 -> Hash
224 -> Filter: (t3.a <=> derived_2_3.a) (rows=0.3)
225 -> Inner hash join (no condition) (rows=3)
226 -> Limit: 1 row(s) (rows=1)
227 -> Left hash join (no condition) (rows=9)
228 -> Table scan on t3 in secondary engine MOCK (rows=3)
229 -> Hash
230 -> Table scan on derived_2_3 (rows=3)
231 -> Materialize (rows=3)
232 -> Table scan on t4 in secondary engine MOCK (rows=3)
233 -> Hash
234 -> Table scan on t2 in secondary engine MOCK (rows=3)
235
236 Warnings:
237 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
238 DROP TABLE t;
239 #
240 # Bug#34534373: Heatwave offload issue - Sees inner tables
241 # of a semijoin when it should not
242 #
243 CREATE TABLE t1(f1 INTEGER, f2 INTEGER) SECONDARY_ENGINE MOCK;
244 ALTER TABLE t1 SECONDARY_LOAD;
245 ANALYZE TABLE t1;
246 Table Op Msg_type Msg_text
247 test.t1 analyze status OK
248 set optimizer_switch="subquery_to_derived=on";
249 EXPLAIN FORMAT=TREE
250 SELECT 1
251 FROM (t1 JOIN t1 AS t2
252 ON t2.f1 = t1.f2 AND t1.f1 IN (SELECT f1 FROM t1 AS t3))
253 WHERE (SELECT SUM(f2) FROM t1 AS t4) IS NULL
254 AND t1.f1 = t2.f1 OR t2.f2 <= 0;
255 EXPLAIN
256 -> Filter: (((derived_1_3.`SUM(f2)` is null) and ((t1.f1 = t1.f2) and (t1.f1 = t2.f1))) or (t2.f2 <= 0)) (rows=0.01)
257 -> Left hash join (no condition) (rows=0.01)
258 -> Hash semijoin (t1.f1 = t3.f1), extra conditions: (((t1.f1 = t1.f2) and ((t1.f1 = t3.f1) and (t3.f1 = t2.f1))) or (t2.f2 <= 0)) (rows=0.01)
259 -> Inner hash join (t2.f1 = t1.f2), extra conditions: ((t1.f1 = t1.f2) or (t2.f2 <= 0)) (rows=0.1)
260 -> Table scan on t1 in secondary engine MOCK (rows=1)
261 -> Hash
262 -> Table scan on t2 in secondary engine MOCK (rows=1)
263 -> Hash
264 -> Table scan on t3 in secondary engine MOCK (rows=1)
265 -> Hash
266 -> Table scan on derived_1_3 (rows=1)
267 -> Materialize (rows=1)
268 -> Aggregate: sum(t4.f2) (rows=1)
269 -> Table scan on t4 in secondary engine MOCK (rows=1)
270
271 Warnings:
272 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
273 DROP TABLE t1;
274 set optimizer_switch="subquery_to_derived=off";
275 #
276 # Bug#34828328: Assertion `right != nullptr' failed in
277 # make_join_hypergraph.cc
278 CREATE TABLE t1(f1 INTEGER, f2 INTEGER) SECONDARY_ENGINE MOCK;
279 ALTER TABLE t1 SECONDARY_LOAD;
280 ANALYZE TABLE t1;
281 Table Op Msg_type Msg_text
282 test.t1 analyze status OK
283 set optimizer_switch="subquery_to_derived=on";
284 EXPLAIN FORMAT=tree SELECT 1
285 FROM t1 JOIN t1 AS t2 ON (t2.f1 = t1.f1)
286 LEFT JOIN t1 AS t3 ON (t2.f2 = t1.f2 AND t2.f2 IN (SELECT f1 FROM t1 AS t4));
287 EXPLAIN
288 -> Left hash join (no condition), extra conditions: (t2.f2 = t1.f2) and <in_optimizer>(t2.f2,<exists>(select #2)) (rows=0.1)
289 -> Nested loop inner join (rows=0.1)
290 -> Table scan on t1 (rows=1)
291 -> Filter: (t2.f1 = t1.f1) (rows=0.1)
292 -> Table scan on t2 (rows=1)
293 -> Hash
294 -> Table scan on t3 (rows=1)
295
296 SELECT 1
297 FROM t1 JOIN t1 AS t2 ON (t2.f1 = t1.f1)
298 LEFT JOIN t1 AS t3 ON (t2.f2 = t1.f2 AND t2.f2 IN (SELECT f1 FROM t1 AS t4));
299 1
300 DROP TABLE t1;
301 set optimizer_switch="subquery_to_derived=off";