Debian Codegraph @

mysql-test/suite/secondary_engine/r/transforms.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 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
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 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
20 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
21 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
22 Warnings:
23 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
24
25 EXECUTE s2;
26 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
27 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
28 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
29 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
30 Warnings:
31 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
32 SET optimizer_switch="subquery_to_derived=on";
33 EXECUTE s;
34 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
35 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
36 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
37 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
38 Warnings:
39 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
40
41 EXECUTE s2;
42 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
43 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
44 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
45 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
46 Warnings:
47 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
48 SET optimizer_switch="subquery_to_derived=off";
49 EXECUTE s;
50 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
51 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
52 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
53 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
54 Warnings:
55 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
56
57 EXECUTE s2;
58 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
59 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
60 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
61 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
62 Warnings:
63 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
64 SET secondary_engine_cost_threshold = 10000;
65 EXECUTE s;
66 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
67 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
68 2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
69 Warnings:
70 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1`))
71
72 EXECUTE s2;
73 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
74 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
75 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Not exists; Using join buffer (hash join)
76 Warnings:
77 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` anti join (`test`.`t1` `t2`) on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where true
78 SET optimizer_switch="subquery_to_derived=on";
79 EXECUTE s;
80 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
81 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
82 2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
83 Warnings:
84 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1`))
85
86 EXECUTE s2;
87 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
88 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
89 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Not exists; Using join buffer (hash join)
90 Warnings:
91 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` anti join (`test`.`t1` `t2`) on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where true
92 SET optimizer_switch="subquery_to_derived=off";
93 EXECUTE s;
94 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
95 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
96 2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
97 Warnings:
98 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1`))
99
100 EXECUTE s2;
101 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
102 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
103 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Not exists; Using join buffer (hash join)
104 Warnings:
105 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` anti join (`test`.`t1` `t2`) on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where true
106 SET secondary_engine_cost_threshold = 0;
107 EXECUTE s;
108 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
109 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
110 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
111 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
112 Warnings:
113 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
114
115 EXECUTE s2;
116 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
117 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
118 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
119 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
120 Warnings:
121 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
122 SET optimizer_switch="subquery_to_derived=on";
123 EXECUTE s;
124 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
125 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
126 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
127 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
128 Warnings:
129 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
130
131 EXECUTE s2;
132 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
133 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
134 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
135 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) 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 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
142 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
143 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
144 Warnings:
145 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
146
147 EXECUTE s2;
148 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
149 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
150 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
151 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
152 Warnings:
153 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
154 SET optimizer_switch="subquery_to_derived=on";
155 # Let transform be active for InnoDB while we the initial PREPARE
156 PREPARE s FROM "EXPLAIN SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a=TIMESTAMP'2006-06-06 15:55:55')";
157 EXECUTE s;
158 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
159 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
160 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
161 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
162 Warnings:
163 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
164
165 EXECUTE s2;
166 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
167 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
168 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
169 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
170 Warnings:
171 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
172 SET secondary_engine_cost_threshold = 0;
173 EXECUTE s;
174 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
175 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
176 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
177 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
180
181 EXECUTE s2;
182 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
183 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
184 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
185 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
186 Warnings:
187 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
188 SET optimizer_switch="subquery_to_derived=on";
189 EXECUTE s;
190 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
191 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
192 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
193 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
194 Warnings:
195 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
196
197 EXECUTE s2;
198 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
199 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
200 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
201 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
202 Warnings:
203 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
204 SET optimizer_switch="subquery_to_derived=off";
205 EXECUTE s;
206 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
207 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
208 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
209 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
210 Warnings:
211 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
212
213 EXECUTE s2;
214 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
215 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
216 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
217 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
218 Warnings:
219 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
220 SET secondary_engine_cost_threshold = 10000;
221 EXECUTE s;
222 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
223 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
224 2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
225 Warnings:
226 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1`))
227
228 EXECUTE s2;
229 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
230 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
231 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Not exists; Using join buffer (hash join)
232 Warnings:
233 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` anti join (`test`.`t1` `t2`) on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where true
234 SET optimizer_switch="subquery_to_derived=on";
235 EXECUTE s;
236 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
237 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
238 2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
239 Warnings:
240 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1`))
241
242 EXECUTE s2;
243 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
244 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
245 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Not exists; Using join buffer (hash join)
246 Warnings:
247 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` anti join (`test`.`t1` `t2`) on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where true
248 SET optimizer_switch="subquery_to_derived=off";
249 EXECUTE s;
250 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
251 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
252 2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
253 Warnings:
254 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1`))
255
256 EXECUTE s2;
257 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
258 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
259 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Not exists; Using join buffer (hash join)
260 Warnings:
261 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` anti join (`test`.`t1` `t2`) on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where true
262 SET secondary_engine_cost_threshold = 0;
263 EXECUTE s;
264 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
265 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
266 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
267 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
268 Warnings:
269 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
270
271 EXECUTE s2;
272 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
273 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
274 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
275 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
276 Warnings:
277 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
278 SET optimizer_switch="subquery_to_derived=on";
279 EXECUTE s;
280 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
281 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
282 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
283 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
284 Warnings:
285 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
286
287 EXECUTE s2;
288 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
289 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
290 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
291 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
292 Warnings:
293 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
294 SET optimizer_switch="subquery_to_derived=off";
295 EXECUTE s;
296 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
297 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using secondary engine MOCK
298 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
299 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
300 Warnings:
301 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 ((`test`.`t1`.`a` = TIMESTAMP'2006-06-06 15:55:55') and (`derived_1_2`.`MAX(a)` = TIMESTAMP'2006-06-06 15:55:55'))
302
303 EXECUTE s2;
304 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
305 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using secondary engine MOCK
306 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
307 2 DERIVED t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using secondary engine MOCK
308 Warnings:
309 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((`derived_1_2`.`Name_exp_1` = `test`.`t1`.`a`)) where (`derived_1_2`.`Name_exp_1` is null)
310 DROP PREPARE s;
311 DROP PREPARE s2;
312 DROP TABLE t1;
313 SET secondary_engine_cost_threshold = default;
314 SET optimizer_switch="subquery_to_derived=default";
315 #
316 # Bug#34445328 (Hypergraph) Optimizer degenerate join
317 # conditions when encountering semi join + outer join
318 #
319 CREATE TABLE t (a INT) SECONDARY_ENGINE MOCK;
320 INSERT INTO t VALUES (), (), ();
321 ALTER TABLE t SECONDARY_LOAD;
322 ANALYZE TABLE t;
323 Table Op Msg_type Msg_text
324 test.t analyze status OK
325 EXPLAIN FORMAT=TREE
326 SELECT 1
327 FROM t AS t1 LEFT JOIN t AS t2
328 ON 1 IN (
329 SELECT 1 FROM t AS t3 WHERE a <=>
330 (SELECT a FROM t AS t4)
331 );
332 EXPLAIN
333 -> Nested loop left join (rows=81)
334 -> Table scan on t1 in secondary engine MOCK (rows=3)
335 -> Nested loop inner join (rows=27)
336 -> Table scan on t2 in secondary engine MOCK (rows=3)
337 -> Constant row from <subquery2> (rows=1)
338 -> Materialize with deduplication (rows=9)
339 -> Left hash join (no condition) (rows=9)
340 -> Table scan on t3 in secondary engine MOCK (rows=3)
341 -> Hash
342 -> Table scan on derived_2_3 (rows=3)
343 -> Materialize (rows=3)
344 -> Table scan on t4 in secondary engine MOCK (rows=3)
345
346 Warnings:
347 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
348 DROP TABLE t;
349 #
350 # Bug#34534373: Heatwave offload issue - Sees inner tables
351 # of a semijoin when it should not
352 #
353 CREATE TABLE t1(f1 INTEGER, f2 INTEGER) SECONDARY_ENGINE MOCK;
354 ALTER TABLE t1 SECONDARY_LOAD;
355 ANALYZE TABLE t1;
356 Table Op Msg_type Msg_text
357 test.t1 analyze status OK
358 set optimizer_switch="subquery_to_derived=on";
359 EXPLAIN FORMAT=TREE
360 SELECT 1
361 FROM (t1 JOIN t1 AS t2
362 ON t2.f1 = t1.f2 AND t1.f1 IN (SELECT f1 FROM t1 AS t3))
363 WHERE (SELECT SUM(f2) FROM t1 AS t4) IS NULL
364 AND t1.f1 = t2.f1 OR t2.f2 <= 0;
365 EXPLAIN
366 -> Filter: (((t1.f2 = t1.f1) and (derived_1_3.`SUM(f2)` is null)) or (t2.f2 <= 0)) (rows=1)
367 -> Left hash join (no condition) (rows=1)
368 -> Hash semijoin (t3.f1 = t1.f1) (rows=1)
369 -> Filter: ((t1.f2 = t1.f1) or (t2.f2 <= 0)) (rows=1)
370 -> Inner hash join (t2.f1 = t1.f2) (rows=1)
371 -> Table scan on t2 in secondary engine MOCK (rows=1)
372 -> Hash
373 -> Table scan on t1 in secondary engine MOCK (rows=1)
374 -> Hash
375 -> Table scan on t3 in secondary engine MOCK (rows=1)
376 -> Hash
377 -> Table scan on derived_1_3 (rows=1)
378 -> Materialize (rows=1)
379 -> Aggregate: sum(t4.f2) (rows=1)
380 -> Table scan on t4 in secondary engine MOCK (rows=1)
381
382 Warnings:
383 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
384 DROP TABLE t1;
385 set optimizer_switch="subquery_to_derived=off";
386 #
387 # Bug#34828328: Assertion `right != nullptr' failed in
388 # make_join_hypergraph.cc
389 CREATE TABLE t1(f1 INTEGER, f2 INTEGER) SECONDARY_ENGINE MOCK;
390 ALTER TABLE t1 SECONDARY_LOAD;
391 ANALYZE TABLE t1;
392 Table Op Msg_type Msg_text
393 test.t1 analyze status OK
394 set optimizer_switch="subquery_to_derived=on";
395 EXPLAIN FORMAT=tree SELECT 1
396 FROM t1 JOIN t1 AS t2 ON (t2.f1 = t1.f1)
397 LEFT JOIN t1 AS t3 ON (t2.f2 = t1.f2 AND t2.f2 IN (SELECT f1 FROM t1 AS t4));
398 EXPLAIN
399 -> Nested loop left join (rows=1)
400 -> Inner hash join (t2.f1 = t1.f1) (rows=1)
401 -> Table scan on t2 in secondary engine MOCK (rows=1)
402 -> Hash
403 -> Table scan on t1 in secondary engine MOCK (rows=1)
404 -> Filter: (t2.f2 = t1.f2) (rows=1)
405 -> Hash semijoin (no condition) (rows=1)
406 -> Table scan on t3 in secondary engine MOCK (rows=1)
407 -> Hash
408 -> Limit: 1 row(s) (rows=1)
409 -> Filter: (t4.f1 = t1.f2) (rows=1)
410 -> Table scan on t4 in secondary engine MOCK (rows=1)
411
412 Warnings:
413 Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one
414 SELECT 1
415 FROM t1 JOIN t1 AS t2 ON (t2.f1 = t1.f1)
416 LEFT JOIN t1 AS t3 ON (t2.f2 = t1.f2 AND t2.f2 IN (SELECT f1 FROM t1 AS t4));
417 1
418 DROP TABLE t1;
419 set optimizer_switch="subquery_to_derived=off";