Debian Codegraph @

mysql-test/suite/ndb_opt/spj_rqg_hypergraph.result

raw
1 Table Op Msg_type Msg_text
2 test.a analyze status OK
3 test.b analyze status OK
4 test.c analyze status OK
5 test.d analyze status OK
6 test.e analyze status OK
7 test.f analyze status OK
8 test.g analyze status OK
9 test.h analyze status OK
10 test.i analyze status OK
11 test.j analyze status OK
12 test.k analyze status OK
13 test.l analyze status OK
14 test.m analyze status OK
15 test.n analyze status OK
16 test.o analyze status OK
17 test.p analyze status OK
18 test.q analyze status OK
19 test.r analyze status OK
20 test.s analyze status OK
21 test.t analyze status OK
22 test.u analyze status OK
23 test.v analyze status OK
24 test.w analyze status OK
25 test.x analyze status OK
26 test.y analyze status OK
27 test.z analyze status OK
28 set optimizer_switch='hypergraph_optimizer=on';
29 explain format=tree SELECT
30 table1.pk, table2.pk
31 FROM m AS table1
32 LEFT JOIN r AS table2 ON table2.col_int IN (4)
33 WHERE table1.pk = ANY (
34 SELECT table2s.col_int AS field2
35 FROM r AS table1s
36 LEFT JOIN p AS table2s ON table1s.col_int_unique = table2s.col_int_key
37 GROUP BY field2
38 );
39 EXPLAIN
40 -> Nested loop inner join
41 -> Remove duplicates from input grouped on table2s.col_int
42 -> Sort: table2s.col_int
43 -> Nested loop inner join
44 -> Table scan on table1s, activating pushed join of 2 tables
45 -> Index lookup on table2s using col_int_key (col_int_key=table1s.col_int_unique), child of table1s in pushed join
46 -> Left hash join (no condition)
47 -> Single-row index lookup on table1 using PRIMARY (pk=table2s.col_int)
48 -> Hash
49 -> Index range scan on table2 using PRIMARY over (col_int = 4)
50
51 Warnings:
52 Note 1003 Can't push table 'table1' as child of 'table1s', it is in a sorted-branch which can't be referred.
53 Note 1003 Can't push table 'table2' as child, access type 'Range-scan' not implemented
54 SELECT
55 table1.pk, table2.pk
56 FROM m AS table1
57 LEFT JOIN r AS table2 ON table2.col_int IN (4)
58 WHERE table1.pk = ANY (
59 SELECT table2s.col_int AS field2
60 FROM r AS table1s
61 LEFT JOIN p AS table2s ON table1s.col_int_unique = table2s.col_int_key
62 GROUP BY field2
63 );
64 pk pk
65 1 NULL
66 2 NULL
67 3 NULL
68 explain format=tree SELECT
69 table1.pk, table2.pk, table3.pk
70 FROM t AS table1
71 LEFT JOIN g AS table2 ON table1.pk = table2.col_int
72 LEFT JOIN z AS table3 ON table2.col_int_key = table3.col_int_key
73 WHERE CHAR_LENGTH(table2.col_varchar_10_unique) IN (
74 SELECT STRAIGHT_JOIN
75 table3s.col_int AS field3
76 FROM m AS table1s JOIN (
77 p AS table2s JOIN l AS table3s ON table2s.pk = table3s.pk
78 ) ON table1s.col_int_unique = table2s.col_int_unique
79 );
80 EXPLAIN
81 -> Inner hash join (table1.pk = table2.col_int)
82 -> Nested loop left join
83 -> Filter: <in_optimizer>(char_length(table2.col_varchar_10_unique),char_length(table2.col_varchar_10_unique) in (select #2))
84 -> Table scan on table2, activating pushed join of 2 tables
85 -> Select #2 (subquery in condition; run only once)
86 -> Filter: ((char_length(table2.col_varchar_10_unique) = `<materialized_subquery>`.field3))
87 -> Limit: 1 row(s)
88 -> Index lookup on <materialized_subquery> using <auto_distinct_key> (field3=char_length(table2.col_varchar_10_unique))
89 -> Materialize with deduplication
90 -> Inner hash join (table1s.col_int_unique = table2s.col_int_unique)
91 -> Table scan on table1s
92 -> Hash
93 -> Nested loop inner join
94 -> Table scan on table3s, activating pushed join of 2 tables
95 -> Single-row index lookup on table2s using PRIMARY (pk=table3s.pk), child of table3s in pushed join
96 -> Index lookup on table3 using col_int_key (col_int_key=table2.col_int_key), child of table2 in pushed join
97 -> Hash
98 -> Table scan on table1
99
100 Warnings:
101 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
102 Note 1003 Can't push table 'table3' as child of 'table1', it is in a hash-bucket-branch which can't be referred.
103 Note 1003 Can't push table 'table1s' as child, 'type' must be a 'ref' access
104 SELECT
105 table1.pk, table2.pk, table3.pk
106 FROM t AS table1
107 LEFT JOIN g AS table2 ON table1.pk = table2.col_int
108 LEFT JOIN z AS table3 ON table2.col_int_key = table3.col_int_key
109 WHERE CHAR_LENGTH(table2.col_varchar_10_unique) IN (
110 SELECT STRAIGHT_JOIN
111 table3s.col_int AS field3
112 FROM m AS table1s JOIN (
113 p AS table2s JOIN l AS table3s ON table2s.pk = table3s.pk
114 ) ON table1s.col_int_unique = table2s.col_int_unique
115 );
116 pk pk pk
117 explain format=tree SELECT
118 table1.pk
119 FROM y AS table1
120 WHERE table1.col_int IN (
121 SELECT STRAIGHT_JOIN
122 table2s.col_int_unique
123 FROM r AS table1s
124 JOIN z AS table2s ON table1s.pk = table2s.col_int_key
125 );
126 EXPLAIN
127 -> Filter: <in_optimizer>(table1.col_int,table1.col_int in (select #2))
128 -> Table scan on table1
129 -> Select #2 (subquery in condition; run only once)
130 -> Filter: ((table1.col_int = `<materialized_subquery>`.col_int_unique))
131 -> Limit: 1 row(s)
132 -> Index lookup on <materialized_subquery> using <auto_distinct_key> (col_int_unique=table1.col_int)
133 -> Materialize with deduplication
134 -> Nested loop inner join
135 -> Table scan on table1s, activating pushed join of 2 tables
136 -> Index lookup on table2s using col_int_key (col_int_key=table1s.pk), child of table1s in pushed join
137
138 SELECT
139 table1.pk
140 FROM y AS table1
141 WHERE table1.col_int IN (
142 SELECT STRAIGHT_JOIN
143 table2s.col_int_unique
144 FROM r AS table1s
145 JOIN z AS table2s ON table1s.pk = table2s.col_int_key
146 );
147 pk
148 11
149 13
150 14
151 15
152 16
153 19
154 2
155 20
156 23
157 24
158 25
159 26
160 27
161 30
162 33
163 4
164 explain format=tree SELECT
165 table1.pk, table2.pk
166 FROM c AS table1
167 JOIN c AS table2 ON table1.col_int = table2.col_int_key
168 WHERE EXISTS (
169 SELECT * FROM d
170 );
171 EXPLAIN
172 -> Nested loop inner join
173 -> Limit: 1 row(s)
174 -> Table scan on d
175 -> Nested loop inner join
176 -> Table scan on table2, activating pushed join of 2 tables
177 -> Index lookup on table1 using PRIMARY (col_int=table2.col_int_key), child of table2 in pushed join
178
179 Warnings:
180 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
181 Note 1003 Can't push table 'table1' as child of 'd', column 'table2.col_int_key' refers a table which was not pushed
182 SELECT
183 table1.pk, table2.pk
184 FROM c AS table1
185 JOIN c AS table2 ON table1.col_int = table2.col_int_key
186 WHERE EXISTS (
187 SELECT * FROM d
188 );
189 pk pk
190 1 3
191 2 1
192 2 2
193 2 5
194 3 3
195 4 3
196 5 1
197 5 2
198 5 5
199 explain format=tree SELECT
200 table1.pk, table2.pk, table3.pk, table4.pk
201 FROM b AS table1
202 LEFT JOIN s AS table2
203 LEFT JOIN x AS table3 ON table2.col_int = table3.col_int
204 ON table1.col_int_key = table2.col_int_key AND
205 table1.col_int_unique = table2.pk
206 LEFT JOIN e AS table4 ON table2.col_int_key = table4.pk;
207 EXPLAIN
208 -> Left hash join (table2.col_int_key = table4.pk)
209 -> Nested loop left join
210 -> Nested loop left join
211 -> Table scan on table1, activating pushed join of 3 tables
212 -> Single-row index lookup on table2 using PRIMARY (pk=table1.col_int_unique), child of table1 in pushed join, with pushed condition: (table1.col_int_key = table2.col_int_key)
213 -> Index lookup on table3 using PRIMARY (col_int=table2.col_int), child of table2 in pushed join
214 -> Hash
215 -> Table scan on table4
216
217 Warnings:
218 Note 1003 Can't push table 'table4' as child, 'type' must be a 'ref' access
219 SELECT
220 table1.pk, table2.pk, table3.pk, table4.pk
221 FROM b AS table1
222 LEFT JOIN s AS table2
223 LEFT JOIN x AS table3 ON table2.col_int = table3.col_int
224 ON table1.col_int_key = table2.col_int_key AND
225 table1.col_int_unique = table2.pk
226 LEFT JOIN e AS table4 ON table2.col_int_key = table4.pk;
227 pk pk pk pk
228 1 NULL NULL NULL
229 explain format=tree SELECT DISTINCT
230 table1.col_char_16_key AS field1
231 FROM m AS table1
232 JOIN k AS table2 ON table1.col_int_key = table2.col_int AND
233 table1.col_int_unique = table2.col_int_unique
234 WHERE table2.col_int_key <> 5;
235 EXPLAIN
236 -> Sort with duplicate removal: table1.col_char_16_key
237 -> Inner hash join (table1.col_int_key = table2.col_int), (table1.col_int_unique = table2.col_int_unique)
238 -> Index scan on table1 using col_char_16_key
239 -> Hash
240 -> Index range scan on table2 using col_int_key over (NULL < col_int_key < 5) OR (5 < col_int_key)
241
242 Warnings:
243 Note 1003 Can't push table 'table1' as child, can't provide rows in index order
244 SELECT DISTINCT
245 table1.col_char_16_key AS field1
246 FROM m AS table1
247 JOIN k AS table2 ON table1.col_int_key = table2.col_int AND
248 table1.col_int_unique = table2.col_int_unique
249 WHERE table2.col_int_key <> 5;
250 field1
251 will
252 explain format=tree SELECT DISTINCT
253 'auq' AS field1, table1.col_varchar_10_unique AS field2
254 FROM j AS table1
255 LEFT JOIN r AS table2 ON table1.pk = table2.col_int_key
256 WHERE table1.col_int BETWEEN 6 AND 7 OR table2.pk <= 7;
257 EXPLAIN
258 -> Remove duplicates from input grouped on table1.col_varchar_10_unique
259 -> Filter: ((table1.col_int between 6 and 7) or (table2.pk <= 7))
260 -> Nested loop left join
261 -> Index scan on table1 using col_varchar_10_unique, activating pushed join of 2 tables
262 -> Index lookup on table2 using col_int_key (col_int_key=table1.pk), child of table1 in pushed join
263
264 SELECT DISTINCT
265 'auq' AS field1, table1.col_varchar_10_unique AS field2
266 FROM j AS table1
267 LEFT JOIN r AS table2 ON table1.pk = table2.col_int_key
268 WHERE table1.col_int BETWEEN 6 AND 7 OR table2.pk <= 7;
269 field1 field2
270 auq qqjwasfkve
271 auq tfobocgpft
272 auq this
273 explain format=tree SELECT
274 table1.col_int_key AS field1, table2.pk AS field2
275 FROM h AS table1
276 LEFT OUTER JOIN w AS table2 ON table1.col_int_key = table2.col_int_key
277 WHERE #table1.col_int = 8 AND
278 EXISTS (
279 SELECT *
280 #table1s.col_int_key AS field3, table1s.col_int_unique AS field4, table1s.pk AS field5
281 FROM a AS table1s
282 WHERE table1.pk = table1s.col_int_key)
283 GROUP BY field1, field2;
284 EXPLAIN
285 -> Group (no aggregates)
286 -> Sort: table1.col_int_key, table2.pk
287 -> Nested loop left join
288 -> Nested loop inner join
289 -> Remove duplicates from input grouped on table1s.col_int_key
290 -> Index scan on table1s using col_int_key, activating pushed join of 2 tables
291 -> Single-row index lookup on table1 using PRIMARY (pk=table1s.col_int_key), child of table1s in pushed join
292 -> Index lookup on table2 using col_int_key (col_int_key=table1.col_int_key)
293
294 Warnings:
295 Note 1276 Field or reference 'test.table1.pk' of SELECT #2 was resolved in SELECT #1
296 Note 1003 Can't push table 'table2' as scan-child of 'table1s', depends on tables being 'skip-read'
297 SELECT
298 table1.col_int_key AS field1, table2.pk AS field2
299 FROM h AS table1
300 LEFT OUTER JOIN w AS table2 ON table1.col_int_key = table2.col_int_key
301 WHERE #table1.col_int = 8 AND
302 EXISTS (
303 SELECT *
304 #table1s.col_int_key AS field3, table1s.col_int_unique AS field4, table1s.pk AS field5
305 FROM a AS table1s
306 WHERE table1.pk = table1s.col_int_key)
307 GROUP BY field1, field2;
308 field1 field2
309 explain format=tree SELECT
310 table1.col_int_unique
311 FROM q AS table1
312 JOIN j AS table2 ON table1.pk = table2.col_int_unique
313 WHERE EXISTS (
314 SELECT
315 table1s.col_int_unique
316 FROM s AS table1s
317 JOIN p AS table2s ON table1s.col_int_key = table2s.col_int_key
318 WHERE table1s.col_int_unique = table1.col_int_unique
319 );
320 EXPLAIN
321 -> Hash semijoin (table1.col_int_unique = table1s.col_int_unique)
322 -> Nested loop inner join
323 -> Table scan on table2, activating pushed join of 2 tables
324 -> Single-row index lookup on table1 using PRIMARY (pk=table2.col_int_unique), child of table2 in pushed join
325 -> Hash
326 -> Nested loop inner join
327 -> Table scan on table2s, activating pushed join of 2 tables
328 -> Index lookup on table1s using ix2 (col_int_key=table2s.col_int_key), child of table2s in pushed join
329
330 Warnings:
331 Note 1276 Field or reference 'test.table1.col_int_unique' of SELECT #2 was resolved in SELECT #1
332 Note 1003 Can't push table 'table2s' as child, 'type' must be a 'ref' access
333 Note 1003 Can't push table 'table1s' as child of 'table2', it is in a hash-probe-branch which can't be referred.
334 SELECT
335 table1.col_int_unique
336 FROM q AS table1
337 JOIN j AS table2 ON table1.pk = table2.col_int_unique
338 WHERE EXISTS (
339 SELECT
340 table1s.col_int_unique
341 FROM s AS table1s
342 JOIN p AS table2s ON table1s.col_int_key = table2s.col_int_key
343 WHERE table1s.col_int_unique = table1.col_int_unique
344 );
345 col_int_unique
346 0
347 11
348 2
349 4
350 9
351 explain format=tree SELECT
352 table1.pk, table2.pk
353 FROM i AS table1
354 JOIN d AS table2 ON table1.pk = table2.col_int
355 WHERE table1.col_int_key BETWEEN 5 AND 5+4;
356 EXPLAIN
357 -> Nested loop inner join
358 -> Table scan on table2
359 -> Index range scan on table1 using col_int_key over (5 <= col_int_key <= 9), with pushed condition: ((table1.col_int_key between 5 and <cache>((5 + 4))) and (table1.pk = table2.col_int))
360
361 Warnings:
362 Note 1003 Can't push table 'table1' as child, access type 'Range-scan' not implemented
363 SELECT
364 table1.pk, table2.pk
365 FROM i AS table1
366 JOIN d AS table2 ON table1.pk = table2.col_int
367 WHERE table1.col_int_key BETWEEN 5 AND 5+4;
368 pk pk
369 set optimizer_switch='hypergraph_optimizer=default';
370 DROP TABLE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z;