Debian Codegraph @

mysql-test/suite/ndb_opt/join_pushdown.inc

raw
1 ########################################
2 # Define two connections as we want DDL to use its own connection
3 # in order to keep DDL statistics counting out of the way
4 # of the SPJ testing
5 ########################################
6 connect (spj,localhost,root,,test);
7 connect (ddl,localhost,root,,test);
8
9 select @@session.optimizer_switch;
10
11 connection spj;
12 # Save old mysqld counter values.
13 create temporary table server_counts_at_startup
14 select * from performance_schema.global_status
15 where variable_name in
16 ('Ndb_pruned_scan_count',
17 'Ndb_sorted_scan_count',
18 'Ndb_pushed_queries_defined',
19 'Ndb_pushed_queries_dropped');
20
21 ##############
22 # Test start
23
24 --error 0,1193
25 set @save_debug = @@global.debug;
26 set @save_ndb_join_pushdown = @@session.ndb_join_pushdown;
27 set ndb_join_pushdown = true;
28
29 connection ddl;
30 create table t1 (
31 a int not null,
32 b int not null,
33 c int not null,
34 d int not null,
35 primary key (`a`,`b`)
36 ) engine=ndbcluster
37 partition by key() partitions 8;
38
39 connection spj;
40 insert into t1 values
41 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
42 (1,2,5,1), (1,3,1,2), (1,4,2,3),
43 (2,1,3,4), (2,3,4,5), (2,4,5,1),
44 (3,1,1,2), (3,2,2,3), (3,4,3,4),
45 (4,1,4,5), (4,2,5,1), (4,3,1,2);
46
47 explain
48 select *
49 from t1
50 join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
51 --sorted_result
52 select *
53 from t1
54 join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
55
56 # Check that new JSON explain format is also handled:
57 explain format=JSON
58 select *
59 from t1
60 join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
61
62 # Check that we do not push an operation if this prevents us from using
63 # 'join buffer'.
64 explain
65 select straight_join count(*)
66 from t1 as x1
67 join t1 as x2 on x1.d > x2.a + 1000
68 join t1 as x3 on x1.c=x3.a and x1.d=x3.b;
69 select straight_join count(*)
70 from t1 as x1
71 join t1 as x2 on x1.d > x2.a + 1000
72 join t1 as x3 on x1.c=x3.a and x1.d=x3.b;
73
74 # Check that we do not push an operation if this prevents us from using
75 # 'join buffer'.
76 explain select *
77 from t1 as x1
78 join t1 as x2 on x1.a=1 and x1.c=x2.a and x1.d=x2.b
79 join t1 as x3
80 join t1 as x4 where x4.a=x3.c and x4.b=x1.d;
81 --sorted_result
82 select *
83 from t1 as x1
84 join t1 as x2 on x1.a=1 and x1.c=x2.a and x1.d=x2.b
85 join t1 as x3
86 join t1 as x4 where x4.a=x3.c and x4.b=x1.d;
87
88 explain
89 select *
90 from t1
91 left join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
92 --sorted_result
93 select *
94 from t1
95 left join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
96
97 explain
98 select *
99 from t1
100 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
101 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
102 where t1.a = 1 and t1.b = 1;
103 --sorted_result
104 select *
105 from t1
106 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
107 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
108 where t1.a = 1 and t1.b = 1;
109
110 explain
111 select *
112 from t1
113 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
114 join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
115 --sorted_result
116 select *
117 from t1
118 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
119 join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
120
121 explain
122 select *
123 from t1
124 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
125 left join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
126 --sorted_result
127 select *
128 from t1
129 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
130 left join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
131
132 explain
133 select *
134 from t1
135 left join t1 as t2 on t2.a = t1.b and t2.b = t1.c
136 left join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
137 --sorted_result
138 select *
139 from t1
140 left join t1 as t2 on t2.a = t1.b and t2.b = t1.c
141 left join t1 as t3 on t3.a = t2.a and t3.b = t2.b;
142
143 set ndb_join_pushdown=true;
144
145 --replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
146 explain
147 select *
148 from t1
149 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
150 where t1.a = 2 and t1.b = 3;
151 select *
152 from t1
153 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
154 where t1.a = 2 and t1.b = 3;
155
156 ################################################################
157 # Bug#13901890 SQL NODE CRASHES DURING INSERT SELECT
158 #
159 # Incorrect lifetime handling for NdbQuery objects where
160 # the root operation was a 'const' lookup operation.
161 #
162 # 'virtual handler::index_read_idx_map()' was incorectly
163 # implemented by ha_ndbcluster, such that it failed to
164 # 'close' the table when the single 'const' row has been read.
165 # Neither was it registered as 'open' by using the ::ha_index_init().
166 # (Called ha_ndbcluster::index_init() directly)
167 # This resulted in that NdbQuery::close() was never called
168 # on these 'const' SPJ queries. Instead NdbTransaction end
169 # forcefully whiped them away by calling NdbQuery::release().
170 # However, as there still was dangling pointer refs to
171 # them from ha_ndbcluster::m_active_query, we may later refer
172 # them and crash!
173 #
174 # - Same query as above.
175 # - Added explict table locks as cleanup of these is one
176 # (of several?) way to cause released NdbQuery objects to
177 # be refered.
178 ################################################################
179
180 # LOCK'ed tables invokes the same code path as executing
181 # an 'INSERT... SELECT' inside a procedure invoked from a trigger.
182 # ... and is a much simpler testcase.....
183 LOCK TABLES t1 read, t1 as t2 read;
184
185 --replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
186 explain
187 select *
188 from t1
189 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
190 where t1.a = 2 and t1.b = 3;
191
192 #Returns empty result set
193 select *
194 from t1
195 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
196 where t1.a = 2 and t1.b = 3;
197
198 #Returns a single row
199 select *
200 from t1
201 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
202 where t1.a = 3 and t1.b = 3;
203 UNLOCK TABLES;
204
205 --replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
206 explain
207 select *
208 from t1
209 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
210 where t1.a = 2 and t1.b = 3;
211 select *
212 from t1
213 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
214 where t1.a = 2 and t1.b = 3;
215
216 --replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
217 explain
218 select *
219 from t1
220 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
221 where t1.a = 2 and t1.b = 3
222 order by t1.c;
223 select *
224 from t1
225 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
226 where t1.a = 2 and t1.b = 3
227 order by t1.c;
228
229 set ndb_join_pushdown=false;
230 --replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
231 explain
232 select *
233 from t1
234 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
235 where t1.a = 2 and t1.b = 3;
236 select *
237 from t1
238 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
239 where t1.a = 2 and t1.b = 3;
240
241 --replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p0 pX p5 pX
242 explain
243 select *
244 from t1
245 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
246 where t1.a = 2 and t1.b = 3;
247 select *
248 from t1
249 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
250 where t1.a = 2 and t1.b = 3;
251
252 set ndb_join_pushdown=true;
253 explain
254 select *
255 from t1
256 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
257 where t1.a = 1 and t1.b = 1;
258 select *
259 from t1
260 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
261 where t1.a = 1 and t1.b = 1;
262
263 explain
264 select *
265 from t1
266 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
267 where t1.a = 1 and t1.b = 1;
268 select *
269 from t1
270 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
271 where t1.a = 1 and t1.b = 1;
272
273 set ndb_join_pushdown=false;
274 explain
275 select *
276 from t1
277 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
278 where t1.a = 1 and t1.b = 1;
279 select *
280 from t1
281 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
282 where t1.a = 1 and t1.b = 1;
283
284 explain
285 select *
286 from t1
287 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
288 where t1.a = 1 and t1.b = 1;
289 select *
290 from t1
291 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d
292 where t1.a = 1 and t1.b = 1;
293
294 ## Join as 't1 ALL' -> 't2 RANGE' -> 't3 EQ_REF'
295 ## Possibly joinable starting with 't2 - RANGE' as root.
296 ## However t3's join condition 't3.a = t1.c' refers t1 which is
297 ## outside the scope of current queryplan. The equality set
298 ## should be consulted in order to replace 't1.c' with 't2.a'
299 ## inside the scope
300 set ndb_join_pushdown=true;
301 explain
302 select *
303 from t1
304 join t1 as t2 on t2.a = t1.c
305 join t1 as t3 on t3.a = t1.c and t3.b = t2.b;
306 --sorted_result
307 select *
308 from t1
309 join t1 as t2 on t2.a = t1.c
310 join t1 as t3 on t3.a = t1.c and t3.b = t2.b;
311
312 ## Join as 'x ALL' -> 'y ALL' -> 'z EQ_REF'
313 ## As Scan vs. scan is not pushable, only y,z is pushed
314 ## However join cond on z refer x which is a
315 ## (pseudo constant) paramValue wrt. the pushed join.
316 ## As we have a dependency on previous rows these
317 ## should not be join cached (ref. HA_PUSH_BLOCK_JOINCACHE)
318 explain
319 select straight_join *
320 from (t1 as x cross join t1 as y)
321 join t1 as z on z.a=x.a and z.b=y.b;
322 --sorted_result
323 select straight_join *
324 from (t1 as x cross join t1 as y)
325 join t1 as z on z.a=x.a and z.b=y.b;
326
327
328 ## Some variants of the above where t3 has a join conditions in t1
329 ## where t1 is outside scope of pushed join (as above). However, in
330 ## these tests t3 is also linked with t2 through another join condition.
331 ## This makes t3 join pushable by specifying the value of t1.c as a
332 ## paramValue()
333 explain
334 select *
335 from t1
336 straight_join t1 as t2 on t2.a = t1.b+0 and t2.b = t1.c
337 straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b;
338 --sorted_result
339 select *
340 from t1
341 straight_join t1 as t2 on t2.a = t1.b+0 and t2.b = t1.c
342 straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b;
343
344 --replace_column 10 # 11 #
345 explain
346 select *
347 from t1
348 straight_join t1 as t2 on t2.a = t1.b+0 and t2.b = t1.c
349 straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b
350 where t1.a=1 and t1.d=1;
351 --sorted_result
352 select *
353 from t1
354 straight_join t1 as t2 on t2.a = t1.b+0 and t2.b = t1.c
355 straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b
356 where t1.a=1 and t1.d=1;
357
358 explain
359 select *
360 from t1
361 straight_join t1 as t2 on t2.a = t1.b+0
362 straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b;
363 --sorted_result
364 select *
365 from t1
366 straight_join t1 as t2 on t2.a = t1.b+0
367 straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b;
368
369
370 ## Create a non-ndb table used as a tool to force part of
371 ## a query to be non-pushable.
372 connection ddl;
373 create table t1_myisam (
374 a int not null,
375 b int not null,
376 c int not null,
377 d int not null,
378 primary key (`a`,`b`)
379 ) engine=myisam;
380
381 connection spj;
382 insert into t1_myisam values
383 (1,1,1,1), (2,2,1,1), (3,3,1,1), (4,4,1,1);
384
385
386 ## Optimizer will use the equality set to replace 't2.a'
387 ## in the term 't3.a = t2.a' with 't1.c' (as 't2.a = t1.c').
388 ## Furthermore the MyIsam table t1 is const table optimized making
389 ## 't1.c' a const_item. This constant value has not yet been materialized
390 ## into the key_buffer when the definition for the linked query is
391 ## created. However, it is always available through the
392 ## Field defining the KEY_PART of this JT_EQ_REF.
393 ##
394
395 set ndb_join_pushdown=true;
396
397 explain
398 select *
399 from t1_myisam as t1
400 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
401 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
402 where t1.a=2 and t1.b=2;
403 --sorted_result
404 select *
405 from t1_myisam as t1
406 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
407 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
408 where t1.a=2 and t1.b=2;
409
410 ## Bug#28898811 INCORRECTLY REJECT JOIN TO BE PUSHED DOWN
411 ##
412 ## .. also served to detect (also see ndb_endian.test):
413 ## Bug#29010641 FAILS TO HANDLE DIFFERENT ENDIANNESS FORMATS IN PUSHED JOIN KEYS
414 ##
415 ## Tables not beings considered in the join pushdown analysis,
416 ## e.g. a table not being a ndb table, where also incorrectly
417 ## excluded as a table which could be referred in the join conditions
418 ## from a pushed child table (on .. t3.b = t1.b <-, below)
419 ##
420 explain
421 select *
422 from t1_myisam as t1
423 join t1 as t2 on t2.a = t1.c
424 join t1 as t3 on t3.a = t2.b and t3.b = t1.b;
425 --sorted_result
426 select *
427 from t1_myisam as t1
428 join t1 as t2 on t2.a = t1.c
429 join t1 as t3 on t3.a = t2.b and t3.b = t1.b;
430
431 connection ddl;
432 drop table t1_myisam;
433
434 #
435 # Test scans with filter. These should be pushed as linked operations
436 # where the root operation is a scan *with* a (pushed) filter and a
437 # primary key lookup child operation.
438 #
439
440 connection spj;
441 set ndb_join_pushdown=true;
442
443 # Table scan
444 --replace_column 10 # 11 #
445 explain select *
446 from t1
447 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
448 where t1.d = 3;
449 --sorted_result
450 select *
451 from t1
452 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
453 where t1.d = 3;
454
455 # Ordered index scan
456 --replace_column 10 # 11 #
457 explain select *
458 from t1
459 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
460 where t1.a > 2 and t1.d = 3;
461 --sorted_result
462 select *
463 from t1
464 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
465 where t1.a > 2 and t1.d = 3;
466
467 # Sorted scan of ordered index.
468 --replace_column 10 # 11 #
469 explain select *
470 from t1 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
471 where t1.d = 3
472 order by t1.a;
473 --sorted_result
474 select *
475 from t1 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
476 where t1.d = 3
477 order by t1.a;
478
479 #
480 # Test index scan w/ equal-bounds (low == high)
481 # NOTE: There used to be temp restriction of not allowing ordered
482 # index scans to be pushed. (Has later been lifted)
483 # SQL stmt. are therefore written with pushable JT_REFs from table 2 ->
484 #
485 set ndb_join_pushdown=true;
486 explain
487 select *
488 from t1
489 join t1 as t2 on t2.a = t1.c
490 join t1 as t3 on t3.a = t2.c and t3.b = t2.d
491 where t1.a = 1 and t1.b = 1;
492 --sorted_result
493 select *
494 from t1
495 join t1 as t2 on t2.a = t1.c
496 join t1 as t3 on t3.a = t2.c and t3.b = t2.d
497 where t1.a = 1 and t1.b = 1;
498
499 explain
500 select *
501 from t1
502 left join t1 as t2 on t2.a = t1.c
503 left join t1 as t3 on t3.a = t2.c and t3.b = t2.d
504 where t1.a = 1 and t1.b = 1;
505 --sorted_result
506 select *
507 from t1
508 left join t1 as t2 on t2.a = t1.c
509 left join t1 as t3 on t3.a = t2.c and t3.b = t2.d
510 where t1.a = 1 and t1.b = 1;
511
512 set ndb_join_pushdown=false;
513 explain
514 select *
515 from t1
516 join t1 as t2 on t2.a = t1.c
517 join t1 as t3 on t3.a = t2.c and t3.b = t2.d
518 where t1.a = 1 and t1.b = 1;
519 --sorted_result
520 select *
521 from t1
522 join t1 as t2 on t2.a = t1.c
523 join t1 as t3 on t3.a = t2.c and t3.b = t2.d
524 where t1.a = 1 and t1.b = 1;
525
526 explain
527 select *
528 from t1
529 left join t1 as t2 on t2.a = t1.c
530 left join t1 as t3 on t3.a = t2.c and t3.b = t2.d
531 where t1.a = 1 and t1.b = 1;
532 --sorted_result
533 select *
534 from t1
535 left join t1 as t2 on t2.a = t1.c
536 left join t1 as t3 on t3.a = t2.c and t3.b = t2.d
537 where t1.a = 1 and t1.b = 1;
538
539 # JT_REF as root operations is now supported as pushed joins
540 set ndb_join_pushdown=true;
541 explain
542 select *
543 from t1 as t2
544 join t1 as t3 on t3.a = t2.c and t3.b = t2.d
545 where t2.a = 1;
546 --sorted_result
547 select *
548 from t1 as t2
549 join t1 as t3 on t3.a = t2.c and t3.b = t2.d
550 where t2.a = 1;
551
552 # Test multiparent pushed joins where it is not possible
553 # to find a single common parent by using the equality set
554 #
555 # NOTE: We should take care to join the multiparent linked
556 # table on field refs. not also being refered from other join expr.
557 # as this will make them candidates for equality set replacement.
558 #
559 set ndb_join_pushdown=true;
560
561 # t3 refer both t1,t2 as parrent.
562 # t1 should be identifed as a grandparent available
563 # through its child t2.
564 explain
565 select straight_join *
566 from t1
567 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
568 join t1 as t3 on t3.a = t2.c and t3.b = t1.c;
569 --sorted_result
570 select straight_join *
571 from t1
572 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
573 join t1 as t3 on t3.a = t2.c and t3.b = t1.c;
574
575 # t4 is pushable iff we force an artificial parental dependency between t2 & t3.
576 explain
577 select straight_join *
578 from t1
579 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
580 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
581 join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
582 --sorted_result
583 select straight_join *
584 from t1
585 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
586 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
587 join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
588
589 # t3 is a child of t2 and grandchild of t1
590 # t4 is a child of t3 and grandchild of t2
591 explain
592 select straight_join *
593 from t1
594 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
595 join t1 as t3 on t3.a = t1.c and t3.b = t2.d
596 join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
597 --sorted_result
598 select straight_join *
599 from t1
600 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
601 join t1 as t3 on t3.a = t1.c and t3.b = t2.d
602 join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
603
604 # t3 is a child of t2 and grandchild of t1
605 # t4 is a child of t3 and grandgrandchild of t1
606 explain
607 select straight_join *
608 from t1
609 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
610 join t1 as t3 on t3.a = t1.c and t3.b = t2.d
611 join t1 as t4 on t4.a = t3.c and t4.b = t1.d;
612 --sorted_result
613 select straight_join *
614 from t1
615 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
616 join t1 as t3 on t3.a = t1.c and t3.b = t2.d
617 join t1 as t4 on t4.a = t3.c and t4.b = t1.d;
618
619 # Some testcases where t4 is not directly pushable, but
620 # may be made pushable by equality set replacement.
621 #
622 # BEWARE: mysqld optimizer may do its own replacement
623 # before ha_ndbcluster_push analyze the AQP. We therefore
624 # provide multiple similar testcases and hope that
625 # some of them will trigger the replacement code in
626 # ha_ndbcluster_push :-o
627 explain
628 select straight_join *
629 from t1
630 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
631 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
632 join t1 as t4 on t4.a = t3.a and t4.b = t2.c;
633 --sorted_result
634 select straight_join *
635 from t1
636 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
637 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
638 join t1 as t4 on t4.a = t3.a and t4.b = t2.c;
639
640 explain
641 select straight_join *
642 from t1
643 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
644 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
645 join t1 as t4 on t4.a = t3.b and t4.b = t2.c;
646 --sorted_result
647 select straight_join *
648 from t1
649 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
650 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
651 join t1 as t4 on t4.a = t3.b and t4.b = t2.c;
652
653 explain
654 select straight_join *
655 from t1
656 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
657 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
658 join t1 as t4 on t4.a = t3.c and t4.b = t2.a;
659 --sorted_result
660 select straight_join *
661 from t1
662 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
663 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
664 join t1 as t4 on t4.a = t3.c and t4.b = t2.a;
665
666 explain
667 select straight_join *
668 from t1
669 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
670 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
671 join t1 as t4 on t4.a = t3.c and t4.b = t2.b;
672 --sorted_result
673 select straight_join *
674 from t1
675 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
676 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
677 join t1 as t4 on t4.a = t3.c and t4.b = t2.b;
678
679 explain
680 select straight_join *
681 from t1
682 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
683 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
684 join t1 as t4 on t4.a = t1.c and t4.b = t2.c;
685 --sorted_result
686 select straight_join *
687 from t1
688 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
689 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
690 join t1 as t4 on t4.a = t1.c and t4.b = t2.c;
691
692 explain
693 select straight_join *
694 from t1
695 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
696 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
697 join t1 as t4 on t4.a = t3.c and t4.b = t1.b;
698 --sorted_result
699 select straight_join *
700 from t1
701 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
702 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
703 join t1 as t4 on t4.a = t3.c and t4.b = t1.b;
704
705 # Added more multidependency tests;
706 #
707
708 explain
709 select straight_join *
710 from t1
711 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
712 join t1 as t2x on t2x.a = t2.c and t2x.b = t2.d
713 join t1 as t3x on t3x.a = t1.c and t3x.b = t1.d
714 join t1 as t4 on t4.a = t3x.c and t4.b = t2x.c;
715
716 explain
717 select straight_join *
718 from t1
719 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
720 join t1 as t2x on t2x.a = t2.c and t2x.b = t2.d
721 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
722 join t1 as t3x on t3x.a = t3.c and t3x.b = t3.d
723 join t1 as t4 on t4.a = t3x.c and t4.b = t2x.c;
724
725 explain
726 select straight_join *
727 from t1
728 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
729 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
730 join t1 as t2x on t2x.a = t2.c and t2x.b = t2.d
731 join t1 as t3x on t3x.a = t3.c and t3x.b = t3.d
732 join t1 as t4 on t4.a = t3x.c and t4.b = t2x.c;
733
734 # 't3' is not referred as ancestor and should not be
735 # included in the forced dependencies
736 # (Depends directly on 't1' and can be bushy wrt. to
737 # the other tables)
738 explain
739 select straight_join *
740 from t1
741 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
742 join t1 as t2x on t2x.a = t2.c and t2x.b = t2.d
743 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
744 join t1 as t3x on t3x.a = t1.c and t3x.b = t1.d
745 join t1 as t4 on t4.a = t3x.c and t4.b = t2x.c;
746
747 # 't3' is still independent - see comment above.
748 explain
749 select straight_join *
750 from t1
751 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
752 join t1 as t2x on t2x.a = t2.c and t2x.b = t2.d
753 join t1 as t3 on t3.a = t1.c and t3.b = t1.b
754 join t1 as t3x on t3x.a = t1.c and t3x.b = t1.d
755 join t1 as t4 on t4.a = t3x.c and t4.b = t2x.c;
756
757 ## It should not be possible to force grandparent dependencies
758 ## via a previously outer joined table:
759 explain
760 select straight_join *
761 from t1
762 left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
763 join t1 as t3 on t3.a = t1.c and t3.b = t1.d
764 left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
765
766 explain
767 select straight_join *
768 from t1
769 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
770 left join t1 as t3 on t3.a = t1.c and t3.b = t1.d
771 left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
772
773 explain
774 select straight_join *
775 from t1
776 left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
777 join t1 as t3 on t3.a = t1.a
778 left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
779
780 explain
781 select straight_join *
782 from t1
783 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
784 left join t1 as t3 on t3.a = t1.a
785 left join t1 as t4 on t4.a = t3.c and t4.b = t2.c;
786
787 explain
788 select straight_join *
789 from t1
790 left join t1 as t2 on t2.a = t1.a and t2.b = t1.b
791 join t1 as t3 on t3.a = t1.a
792 left join t1 as t4 on t4.a = t3.c;
793
794 explain
795 select straight_join *
796 from t1
797 join t1 as t2 on t2.a = t1.a and t2.b = t1.b
798 left join t1 as t3 on t3.a = t1.a
799 left join t1 as t4 on t4.a = t3.c;
800
801 # Bug#16199028:
802 # Tescase where a too strict 'root' was checked:
803 # In these cases t4 was not pushed as it was incorrectly
804 # concluded that its grandparent ref of 't2' through 't3'
805 # introduced dependencies on the outer joined t1.
806 #
807 explain
808 select straight_join *
809 from
810 ( t1 as t0 left join t1 as t1 on t1.a = t0.a and t1.b = t0.b
811 )
812 left join
813 ( t1 as t2 join t1 as t3 on t3.a = t2.c and t3.b = t2.d
814 join t1 as t4 on t4.a = t3.c and t4.b = t2.c
815 )
816 on t2.a = t1.a and t2.b = t1.b;
817
818 explain
819 select straight_join *
820 from
821 t1 as x1
822 left join
823 ( t1 as x2 join t1 as x3 on x3.a=x2.c
824 join t1 as x4 on x4.a=x2.d
825 join t1 as x5 on x5.a=x3.d and x5.b=x4.d
826 )
827 on x2.a=x1.c and x2.b=x1.c and
828 x3.b=x1.d and
829 x4.b=x1.d;
830
831
832 # Bug#16198866:
833 # Additional tests where equality set [t1.d, t3.b, t4.d]
834 # propagation incorrectly allowed grandparent references
835 # to be allowed 'through' outer joins (t2):
836 #
837 # Test: don't allow t4 to propagate parent deps past
838 # outer joined 't2' into t3.
839 # (As that would implicit cause t3 to depend on t2.)
840 #
841 explain
842 select straight_join * from
843 (t1 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d)
844 inner join t1 as t3 on t3.a = t1.b and t3.b = t1.c
845 left join t1 as t4 on t4.a = t2.c and t4.b = t1.c;
846
847 --sorted_result
848 select straight_join * from
849 (t1 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d)
850 inner join t1 as t3 on t3.a = t1.b and t3.b = t1.c
851 left join t1 as t4 on t4.a = t2.c and t4.b = t1.c;
852
853 # However, if t3 already depends on t2 (t3.a = t2.b)
854 # it *is* allowed as it will not introduce any new
855 # t3 dependencies.
856 #
857 # Note: Both these two queries are fully pushable, but
858 # the later will likely result in a better query plan.
859 #
860 explain
861 select straight_join * from
862 (t1 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d)
863 inner join t1 as t3 on t3.a = t2.b and t3.b = t1.c
864 left join t1 as t4 on t4.a = t2.c and t4.b = t1.c;
865
866 --sorted_result
867 select straight_join * from
868 (t1 left join t1 as t2 on t2.a = t1.c and t2.b = t1.d)
869 inner join t1 as t3 on t3.a = t2.b and t3.b = t1.c
870 left join t1 as t4 on t4.a = t2.c and t4.b = t1.c;
871
872 # Test a combination of pushed table scan (x, y)
873 # & pushed EQ-bound (indexScan) (z, t1)
874 # This used to give incorrect results with random result for last table (t1)
875 set ndb_join_pushdown=true;
876 explain
877 select * from t1 x, t1 y, t1 z, t1 where
878 y.a=x.d and y.b=x.b and
879 z.a=y.d and
880 t1.a = z.d and t1.b=z.b;
881 --sorted_result
882 select * from t1 x, t1 y, t1 z, t1 where
883 y.a=x.d and y.b=x.b and
884 z.a=y.d and
885 t1.a = z.d and t1.b=z.b;
886
887 # Pushed scanIndex() with (multi-)range:
888 explain
889 select * from t1 x, t1 y where
890 x.a <= 2 and
891 y.a=x.d and y.b=x.b;
892 --sorted_result
893 select * from t1 x, t1 y where
894 x.a <= 2 and
895 y.a=x.d and y.b=x.b;
896
897 explain
898 select * from t1 x, t1 y where
899 (x.a <= 2 or x.a > 3) and
900 y.a=x.d and y.b=x.b;
901 --sorted_result
902 select * from t1 x, t1 y where
903 (x.a <= 2 or x.a > 3) and
904 y.a=x.d and y.b=x.b;
905
906 # 'open' range:
907 --replace_column 10 # 11 #
908 explain
909 select * from t1 x, t1 y where
910 (x.a >= 2 or x.a < 3) and
911 y.a=x.d and y.b=x.b;
912 --sorted_result
913 select * from t1 x, t1 y where
914 (x.a >= 2 or x.a < 3) and
915 y.a=x.d and y.b=x.b;
916
917 # Combination of range and 'in' list
918 explain
919 select * from t1 x, t1 y where
920 (x.a <= 2 or x.a in (0,5,4)) and
921 y.a=x.d and y.b=x.b;
922 --sorted_result
923 select * from t1 x, t1 y where
924 (x.a <= 2 or x.a in (0,5,4)) and
925 y.a=x.d and y.b=x.b;
926
927 # Combination of range and 'in' list with exact match
928 # NOTE: Due to simplification in pushed mrr, exact matches are also
929 # executed as range scans
930 explain
931 select * from t1 x, t1 y where
932 (x.a <= 2 or (x.a,x.b) in ((0,0),(5,0),(4,3))) and
933 y.a=x.d and y.b=x.b;
934 --sorted_result
935 select * from t1 x, t1 y where
936 (x.a <= 2 or (x.a,x.b) in ((0,0),(5,0),(4,3))) and
937 y.a=x.d and y.b=x.b;
938
939 # Test ORDER BY expressions
940 # If it is a 'simple' order, i.e all order by's are plain column refs
941 # to the first non-const table, the optimizer will (by heuristic)
942 # make the first table 'ordered' beforing joining in the siblings.
943 # This may involve presorting of the first table into intermediate storage.
944 #
945 # This will make this (parent-) table non-pushable as
946 # read of rows to be filesorted will also prefetch rows from pushed child
947 # operands. These are not cached by the filesort buffer mechanisnm and are
948 # effectively lost.
949 #
950 # Non simple ordering will always writte entire resultset to temp.
951 # table and filesort that -> No extra push restrictions on these.
952 #
953 # Integrating pushed joins we either has to:
954 # 1) find a suitable ordered index which we can create an ordered indexscan on
955 # (-> joinType() -> JT_NEXT, or type: 'index' w/ explain)
956 # or:
957 # 2) Reject pushing of the this parent table.
958 #
959 # Comment1: As 'order by' correctness is part of what we want to test,
960 # '--sorted_result' is *not* specified. Instead we aim at
961 # specifying a deterministic sort ordering in order by list.
962 #
963 # Comment2: 't1.a, t1.b' is appended where required to the order by spec
964 # to get a deterministic sorting order wo/ '--sorted_result'
965 #
966
967 #
968 # Bug #30338585
969 # SPJ INTEGRATION: FAILS TO DETECT FILESORT OF A TABLE IN MIDDLE OF PUSHED JOIN
970 #
971 # Extracted from TPC-H Q16, which failed to detect that the first
972 # table in the query plan required a 'filesort', and thus should not
973 # have been pushed. That effectively voided the entire pushed join and
974 # caused poor performance. The correct query plan for Q16 would be
975 # to 'filesort' the first table, then push the join of the two preceeding
976 # tables.
977 #
978 # Below is a test case transformed to the test tables we normally use.
979 # Optimizer will decide to presort t1 into a temporary table before
980 # joining with t2. Thus the below query should not be pushed.
981 #
982
983 explain format=tree
984 select straight_join t1.b, count(distinct t2.b) as cnt
985 from t1
986 join t1 as t2 on t2.a = t1.c
987 group by t1.b
988 order by t1.b, cnt;
989
990 select straight_join t1.b, count(distinct t2.b) as cnt
991 from t1
992 join t1 as t2 on t2.a = t1.c
993 group by t1.b
994 order by t1.b, cnt;
995
996 # Remaining (Original) test cases for eliminating filesort'ed tables
997 # from a pushed join:
998
999 ## Non-pushed join w/ 'simple order' on non_PK - Presorts parent table.
1000 explain
1001 select *
1002 from t1
1003 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1004 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1005 order by t1.c,t1.d,
1006 t1.a, t1.b;
1007 #--sorted_result
1008 select *
1009 from t1
1010 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1011 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1012 order by t1.c,t1.d,
1013 t1.a, t1.b;
1014
1015 ## pushed join w/ non-'simple order' on non_PK - Need temp table + filesort
1016 explain
1017 select *
1018 from t1
1019 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1020 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1021 order by t1.c,t2.d,
1022 t1.a, t1.b;
1023 #--sorted_result
1024 select *
1025 from t1
1026 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1027 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1028 order by t1.c,t2.d,
1029 t1.a, t1.b;
1030
1031 ## pushed join w/ 'simple order' on PK - Should use ordered index scan
1032 explain
1033 select *
1034 from t1
1035 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1036 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1037 order by t1.a,t1.b;
1038 #--sorted_result
1039 select *
1040 from t1
1041 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1042 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1043 order by t1.a,t1.b;
1044
1045 ## pushed join w/ non-'simple order' on PK - will need temp table + filesort
1046 explain
1047 select *
1048 from t1
1049 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1050 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1051 order by t1.a,t2.b,
1052 t1.a, t1.b;
1053 #--sorted_result
1054 select *
1055 from t1
1056 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1057 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1058 order by t1.a,t2.b,
1059 t1.a, t1.b;
1060
1061 ## Descending ordering on PK - use reversed ordered index scan
1062 explain
1063 select *
1064 from t1
1065 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1066 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1067 order by t1.a desc,t1.b desc;
1068 #--sorted_result
1069 select *
1070 from t1
1071 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1072 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1073 order by t1.a desc,t1.b desc;
1074
1075 ## Simple-PK column in incorrect order, -> Presort parent table, (no-push)
1076 explain
1077 select *
1078 from t1
1079 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1080 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1081 order by t1.b,t1.a;
1082 #--sorted_result
1083 select *
1084 from t1
1085 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1086 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1087 order by t1.b,t1.a;
1088
1089 ## Explore other permutations of PK columns in ORDER BY clause
1090 ## Don't care about the results here....
1091 # Subset of first part of PK -> ordered index scan
1092 explain
1093 select *
1094 from t1
1095 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1096 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1097 order by t1.a;
1098
1099 # PK columns not including first part -> Presort parent table, (no-push)
1100 explain
1101 select *
1102 from t1
1103 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1104 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1105 order by t1.b;
1106
1107
1108 ## Similar tests for GROUP BY expression
1109 ## PK grouping (or subpart) may be optimized
1110 ## by ordered index access.
1111 ##
1112 explain
1113 select t1.a, t1.b, count(*)
1114 from t1
1115 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1116 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1117 group by t1.a, t1.b;
1118 --sorted_result
1119 select t1.a, t1.b, count(*)
1120 from t1
1121 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1122 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1123 group by t1.a, t1.b;
1124
1125 explain
1126 select t1.a, count(*)
1127 from t1
1128 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1129 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1130 group by t1.a;
1131 --sorted_result
1132 select t1.a, count(*)
1133 from t1
1134 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1135 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1136 group by t1.a;
1137
1138 explain
1139 select t1.b, count(*)
1140 from t1
1141 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1142 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1143 group by t1.b;
1144 --sorted_result
1145 select t1.b, count(*)
1146 from t1
1147 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
1148 join t1 as t3 on t3.a = t2.a and t3.b = t2.b
1149 group by t1.b;
1150
1151 --replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p1 pX p2 pX
1152 explain
1153 select t2.c, count(distinct t2.a)
1154 from t1
1155 join t1 as t2 on t1.a = t2.c and t1.b = t2.d
1156 where t2.a = 4 and t2.b=4
1157 group by t2.c;
1158 --sorted_result
1159 select t2.c, count(distinct t2.a)
1160 from t1
1161 join t1 as t2 on t1.a = t2.c and t1.b = t2.d
1162 where t2.a = 4 and t2.b=4
1163 group by t2.c;
1164
1165 explain
1166 select t2.c, count(distinct t2.a)
1167 from t1
1168 join t1 as t2 on t1.a = t2.c and t1.b = t2.d
1169 where t2.a = 4
1170 group by t2.c;
1171 --sorted_result
1172 select t2.c, count(distinct t2.a)
1173 from t1
1174 join t1 as t2 on t1.a = t2.c and t1.b = t2.d
1175 where t2.a = 4
1176 group by t2.c;
1177
1178 --replace_result p0,p1,p2,p3,p4,p5,p6,p7 pXYZ p1 pX p2 pX
1179 explain
1180 select t2.c, count(distinct t2.a)
1181 from t1
1182 join t1 as t2 on t1.a = t2.c and t1.b = t2.d
1183 where t2.a = 4 and t2.b=4
1184 group by t2.c order by t2.c;
1185 --sorted_result
1186 select t2.c, count(distinct t2.a)
1187 from t1
1188 join t1 as t2 on t1.a = t2.c and t1.b = t2.d
1189 where t2.a = 4 and t2.b=4
1190 group by t2.c order by t2.c;
1191
1192 connection ddl;
1193 create table tx like t1;
1194
1195 connection spj;
1196 insert into tx
1197 select x1.a+x2.a*16, x1.b+x2.b*16, x1.c+x2.c*16, x1.d+x2.d*16
1198 from t1 as x1 cross join t1 as x2;
1199
1200 # Test of outer join with scan child.
1201 # This query should not be pushed. Doing so would produce lots of extra
1202 # [<x1 row>.NULL] rows, since the x1.d=x2.d predicate cannot be pushed.
1203 explain select count(*) from tx as x1
1204 left join tx as x2 on x1.c=x2.a and x1.d=x2.d;
1205 select count(*) from tx as x1
1206 left join tx as x2 on x1.c=x2.a and x1.d=x2.d;
1207
1208 connection ddl;
1209 drop table tx;
1210
1211 # Test bushy join with pruned scan.
1212 connection ddl;
1213 alter table t1 partition by key(a);
1214
1215 connection spj;
1216
1217 explain select count(*) from t1
1218 join t1 as t2 on t2.a = t1.c
1219 join t1 as t3 on t3.a = t1.d;
1220 select count(*) from t1
1221 join t1 as t2 on t2.a = t1.c
1222 join t1 as t3 on t3.a = t1.d;
1223
1224 # Test bushy join with pruned scan and larger result set.
1225
1226 connection ddl;
1227 CREATE TABLE tx (
1228 a int NOT NULL,
1229 PRIMARY KEY (`a`)
1230 ) comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1231
1232 connection spj;
1233 delete from t1;
1234
1235 insert into tx values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
1236
1237 insert into t1 select 1, x1.a * 10+x2.a, 1, 1 from tx as x1 cross join tx as x2;
1238
1239 --error 0,1193
1240 set global debug='+d,max_64rows_in_spj_batches';
1241
1242 --replace_column 10 # 11 #
1243 explain select count(*) from t1 as x1
1244 join t1 as x2 on x2.a = x1.c and x1.b < 2
1245 join t1 as x3 on x3.a = x1.d;
1246 select count(*) from t1 as x1
1247 join t1 as x2 on x2.a = x1.c and x1.b < 2
1248 join t1 as x3 on x3.a = x1.d;
1249
1250 --error 0,1193
1251 set global debug=@save_debug;
1252
1253 connection ddl;
1254 drop table t1;
1255 drop table tx;
1256
1257 # Test user defined partition not being pushed
1258 #
1259 # Note: User defined partitions are handled
1260 # by the SQL layer, and as such are unknown
1261 # to the NDB datanodes.
1262 #
1263
1264 connection spj;
1265 create table t1 (
1266 a int not null,
1267 b int not null,
1268 c int not null,
1269 d int not null,
1270 primary key (`a`,`b`)
1271 ) engine=ndbcluster
1272 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
1273 partition by key(a);
1274
1275 connection spj;
1276 insert into t1 values
1277 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
1278 (1,2,5,1), (1,3,1,2), (1,4,2,3),
1279 (2,1,3,4), (2,3,4,5), (2,4,5,1),
1280 (3,1,1,2), (3,2,2,3), (3,4,3,4),
1281 (4,1,4,5), (4,2,5,1), (4,3,1,2);
1282
1283 # Only this query('partition by key') should be pushed
1284 explain
1285 select *
1286 from t1
1287 join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
1288
1289 connection ddl;
1290 set new=on;
1291 alter table t1 partition by hash(a);
1292
1293 explain
1294 select *
1295 from t1
1296 join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
1297
1298 connection ddl;
1299 alter table t1 partition by list(a) (
1300 partition p1 values in (1),
1301 partition p2 values in (2),
1302 partition p3 values in (3),
1303 partition p4 values in (4)
1304 );
1305
1306 explain
1307 select *
1308 from t1
1309 join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
1310
1311 connection ddl;
1312 alter table t1 partition by range(a) partitions 4 (
1313 partition p1 values less than (0),
1314 partition p2 values less than (2),
1315 partition p3 values less than (4),
1316 partition p4 values less than (99999)
1317 );
1318
1319 explain
1320 select *
1321 from t1
1322 join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
1323
1324 connection ddl;
1325 drop table t1;
1326 set new=default;
1327
1328
1329 # pushed mrr does not yet handle multiple PK operations in same transaction
1330 # Need 6.0 result handling stuff to simplify result handling
1331 # *** join push is currently dissabled for these ****
1332 #
1333 connection ddl;
1334 create table t1 (a int, b int, primary key(a) using hash) engine = ndb
1335 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1336
1337 connection spj;
1338 insert into t1 values (1, 2);
1339 insert into t1 values (2, 3);
1340 insert into t1 values (3, 1);
1341
1342 set ndb_join_pushdown=true;
1343
1344 ##
1345 # In ps-protocol, server will adds calls to execute(Commit)
1346 # (these are optimized away by ndbapi, since there is nothing to commit)
1347 # and these generates a diff in execute-count
1348 # To not have to investigate problem futher, I simply set autocommit=off
1349 # (and back further down where we don't track execute-count any longer)
1350 # It would probably be good to changes these tests to instead use frazers new
1351 # ndbapi counters, and instead measure #round-trips
1352 set autocommit=off;
1353
1354 explain
1355 select *
1356 from t1, t1 as t2
1357 where t1.a in (1,3,5)
1358 and t2.a = t1.b;
1359 --source suite/ndb/include/ndb_init_execute_count.inc
1360 --sorted_result
1361 select *
1362 from t1, t1 as t2
1363 where t1.a in (1,3,5)
1364 and t2.a = t1.b;
1365 --source suite/ndb/include/ndb_execute_count.inc
1366 --echo This should yield 3 executes (for now...buh)
1367
1368 set autocommit=on;
1369
1370 connection ddl;
1371 drop table t1;
1372
1373 # Same case when there is an ordered index on PK
1374 connection ddl;
1375 create table t1 (a int, b int, primary key(a)) engine = ndb
1376 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1377
1378 connection spj;
1379 insert into t1 values (1, 2);
1380 insert into t1 values (2, 3);
1381 insert into t1 values (3, 1);
1382
1383 set ndb_join_pushdown=true;
1384
1385 ##
1386 # In ps-protocol, server will adds calls to execute(Commit)
1387 # (these are optimized away by ndbapi, since there is nothing to commit)
1388 # and these generates a diff in execute-count
1389 # To not have to investigate problem futher, I simply set autocommit=off
1390 # (and back further down where we don't track execute-count any longer)
1391 # It would probably be good to changes these tests to instead use frazers new
1392 # ndbapi counters, and instead measure #round-trips
1393 set autocommit=off;
1394
1395 explain
1396 select *
1397 from t1, t1 as t2
1398 where t1.a in (1,3,5)
1399 and t2.a = t1.b;
1400 --source suite/ndb/include/ndb_init_execute_count.inc
1401 --sorted_result
1402 select *
1403 from t1, t1 as t2
1404 where t1.a in (1,3,5)
1405 and t2.a = t1.b;
1406 --source suite/ndb/include/ndb_execute_count.inc
1407 --echo This should yield 1 execute (but inefficient since it's based on scan)
1408
1409 set autocommit=on;
1410
1411 ## Adding and 'order by ... desc' trigger the usage
1412 ## of QUICK_SELECT_DESC which somehow prepares a
1413 ## pushed join as indexscan but ends up executing it as
1414 ## primary key access. This (auto-) disables the pushed
1415 ## join execution (EXPLAIN still says 'pushdown') which
1416 ## should test handling of this in ha_ndbcluster::index_read_pushed()
1417
1418 explain
1419 select *
1420 from t1, t1 as t2
1421 where t1.a in (1,3,5)
1422 and t2.a = t1.b
1423 order by t1.a desc;
1424 select *
1425 from t1, t1 as t2
1426 where t1.a in (1,3,5)
1427 and t2.a = t1.b
1428 order by t1.a desc;
1429
1430
1431 connection ddl;
1432 drop table t1;
1433
1434 set ndb_join_pushdown=true;
1435
1436 connection ddl;
1437 create table t1 (a int, b int, primary key(a)) engine = ndb
1438 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1439 create table t2 (c int, d int, primary key(c)) engine = ndb
1440 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1441 create table t3 (a3 int, b3 int, c3 int not null, d3 int not null,
1442 primary key(a3, b3)) engine = ndb
1443 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1444 create table t3_hash (a3 int, b3 int, c3 int not null, d3 int not null,
1445 primary key(a3, b3) using hash) engine = ndb
1446 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1447
1448 connection spj;
1449 insert into t1 values (0x1f, 0x2f);
1450 insert into t1 values (0x2f, 0x3f);
1451 insert into t1 values (0x3f, 0x1f);
1452
1453 insert into t2 values (0x1f, 0x2f);
1454 insert into t2 values (0x2f, 0x3f);
1455 insert into t2 values (0x3f, 0x1f);
1456
1457 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
1458 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
1459 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
1460
1461 insert into t3_hash values (0x1f, 0x2f, 1, 0x1f);
1462 insert into t3_hash values (0x2f, 0x3f, 2, 0x2f);
1463 insert into t3_hash values (0x3f, 0x1f, 3, 0x3f);
1464
1465 explain
1466 select * from t3 x, t3 y, t1 where y.a3=x.d3 and y.b3=x.b3 and t1.a = y.d3;
1467 --sorted_result
1468 select * from t3 x, t3 y, t1 where y.a3=x.d3 and y.b3=x.b3 and t1.a = y.d3;
1469
1470 explain
1471 select *
1472 from t3 x, t3 y, t3 z, t3 z2, t1
1473 where y.a3=x.d3 and y.b3=x.b3 and
1474 z.a3=y.d3 and z.b3=y.b3 and
1475 z2.a3=z.d3 and z2.b3=z.b3 and
1476 t1.a = z2.d3;
1477 --sorted_result
1478 select *
1479 from t3 x, t3 y, t3 z, t3 z2, t1
1480 where y.a3=x.d3 and y.b3=x.b3 and
1481 z.a3=y.d3 and z.b3=y.b3 and
1482 z2.a3=z.d3 and z2.b3=z.b3 and
1483 t1.a = z2.d3;
1484
1485 # Table expressions wo/ parent-child linkage should *not* be executes as a pushed join:
1486 --replace_column 10 # 11 #
1487 explain
1488 select straight_join * from t1 x, t1 y where y.a=0x1f and x.b = 0x1f;
1489 select straight_join * from t1 x, t1 y where y.a=0x1f and x.b = 0x1f;
1490
1491 # NOTE: Due to constValue replacement in equality sets, query below are
1492 # effectively the same as the one above. -> Don't push either
1493 --replace_column 10 # 11 #
1494 explain
1495 select straight_join * from t1 x, t1 y where y.a=x.b and x.b = 0x1f;
1496 select straight_join * from t1 x, t1 y where y.a=x.b and x.b = 0x1f;
1497
1498
1499 # Tests usage of unique index
1500 connection ddl;
1501 create unique index t3_d3 on t3(d3);
1502 create unique index t3_d3 on t3_hash(d3);
1503 commit;
1504
1505 connection spj;
1506 # Use an unique key to lookup root in pushed join:
1507 explain
1508 select * from t3 x, t3 y where x.d3=31 and y.a3=x.d3 and y.b3=x.b3;
1509 select * from t3 x, t3 y where x.d3=31 and y.a3=x.d3 and y.b3=x.b3;
1510
1511 # No data-found on unique key lookup root
1512 explain
1513 select * from t3 x, t3 y where x.d3=0 and y.a3=x.d3 and y.b3=x.b3;
1514 select * from t3 x, t3 y where x.d3=0 and y.a3=x.d3 and y.b3=x.b3;
1515
1516 # Use an unique key to lookup joined child tables
1517 explain
1518 select * from t1 x, t3 y where y.d3=x.b;
1519 --sorted_result
1520 select * from t1 x, t3 y where y.d3=x.b;
1521
1522 # Unique index used both for root lookup and child linkage.
1523 explain
1524 select * from t3 x, t3 y where x.d3=31 and y.d3=x.b3;
1525 select * from t3 x, t3 y where x.d3=31 and y.d3=x.b3;
1526
1527 # No data-found on unique key lookup child
1528 explain
1529 select * from t3 x, t3 y where x.d3=31 and y.d3=x.c3;
1530 select * from t3 x, t3 y where x.d3=31 and y.d3=x.c3;
1531
1532 # 'index_merge' between PRIMARY and index t3.d3
1533 # NOTE: currently unhandled
1534 explain
1535 select * from t3 x, t3 y
1536 where ((x.a3=0x2f and x.b3=0x3f) or x.d3=0x1f)
1537 and (y.a3=x.d3 and y.b3=x.b3);
1538 # No 'sorted_result' required as index merge itself sort on PK
1539 select * from t3 x, t3 y
1540 where ((x.a3=0x2f and x.b3=0x3f) or x.d3=0x1f)
1541 and (y.a3=x.d3 and y.b3=x.b3);
1542
1543 explain
1544 select * from t3_hash x, t3_hash y
1545 where ((x.a3=0x2f and x.b3=0x3f) or x.d3=0x1f)
1546 and (y.a3=x.d3 and y.b3=x.b3);
1547 # No 'sorted_result' required as index merge itself sort on PK
1548 select * from t3_hash x, t3_hash y
1549 where ((x.a3=0x2f and x.b3=0x3f) or x.d3=0x1f)
1550 and (y.a3=x.d3 and y.b3=x.b3);
1551
1552 # Any ordered index may also be used to scan a 'range'
1553 explain
1554 select * from t3 x, t3 y where x.d3>=31 and y.d3=x.b3;
1555 --sorted_result
1556 select * from t3 x, t3 y where x.d3>=31 and y.d3=x.b3;
1557
1558
1559 # handle "null" key
1560 insert into t1 values (0x4f, null);
1561 --sorted_result
1562 select * from t1 left join t1 as t2 on t2.a = t1.b;
1563
1564 ### Test max number of pushable operations.
1565
1566 insert into t3 values (8,8,8,8);
1567
1568 # Unique lookups only.
1569 explain select count(*) from t3 as x0
1570 join t3 as x1 on x0.b3=x1.d3 and x0.d3=8
1571 join t3 as x2 on x1.b3=x2.d3
1572 join t3 as x3 on x2.b3=x3.d3
1573 join t3 as x4 on x3.b3=x4.d3
1574 join t3 as x5 on x4.b3=x5.d3
1575 join t3 as x6 on x5.b3=x6.d3
1576 join t3 as x7 on x6.b3=x7.d3
1577 join t3 as x8 on x7.b3=x8.d3
1578 join t3 as x9 on x8.b3=x9.d3
1579 join t3 as x10 on x9.b3=x10.d3
1580 join t3 as x11 on x10.b3=x11.d3
1581 join t3 as x12 on x11.b3=x12.d3
1582 join t3 as x13 on x12.b3=x13.d3
1583 join t3 as x14 on x13.b3=x14.d3
1584 join t3 as x15 on x14.b3=x15.d3
1585 join t3 as x16 on x15.b3=x16.d3
1586 join t3 as x17 on x16.b3=x17.d3;
1587
1588 select count(*) from t3 as x0
1589 join t3 as x1 on x0.b3=x1.d3 and x0.d3=8
1590 join t3 as x2 on x1.b3=x2.d3
1591 join t3 as x3 on x2.b3=x3.d3
1592 join t3 as x4 on x3.b3=x4.d3
1593 join t3 as x5 on x4.b3=x5.d3
1594 join t3 as x6 on x5.b3=x6.d3
1595 join t3 as x7 on x6.b3=x7.d3
1596 join t3 as x8 on x7.b3=x8.d3
1597 join t3 as x9 on x8.b3=x9.d3
1598 join t3 as x10 on x9.b3=x10.d3
1599 join t3 as x11 on x10.b3=x11.d3
1600 join t3 as x12 on x11.b3=x12.d3
1601 join t3 as x13 on x12.b3=x13.d3
1602 join t3 as x14 on x13.b3=x14.d3
1603 join t3 as x15 on x14.b3=x15.d3
1604 join t3 as x16 on x15.b3=x16.d3
1605 join t3 as x17 on x16.b3=x17.d3;
1606
1607
1608 # Max scans
1609 explain select count(*) from t3 as x0
1610 join t3 as x1 on x0.c3=x1.a3
1611 join t3 as x2 on x1.c3=x2.a3
1612 join t3 as x3 on x2.c3=x3.a3
1613 join t3 as x4 on x3.c3=x4.a3
1614 join t3 as x5 on x4.c3=x5.a3
1615 join t3 as x6 on x5.c3=x6.a3
1616 join t3 as x7 on x6.c3=x7.a3
1617 join t3 as x8 on x7.c3=x8.a3
1618 join t3 as x9 on x8.c3=x9.a3
1619 join t3 as x10 on x9.c3=x10.a3
1620 join t3 as x11 on x10.c3=x11.a3
1621 join t3 as x12 on x11.c3=x12.a3
1622 join t3 as x13 on x12.c3=x13.a3
1623 join t3 as x14 on x13.c3=x14.a3
1624 join t3 as x15 on x14.c3=x15.a3
1625 join t3 as x16 on x15.c3=x16.a3
1626 join t3 as x17 on x16.c3=x17.a3
1627 join t3 as x18 on x17.c3=x18.a3
1628 join t3 as x19 on x18.c3=x19.a3
1629 join t3 as x20 on x19.c3=x20.a3
1630 join t3 as x21 on x20.c3=x21.a3
1631 join t3 as x22 on x21.c3=x22.a3
1632 join t3 as x23 on x22.c3=x23.a3
1633 join t3 as x24 on x23.c3=x24.a3
1634 join t3 as x25 on x24.c3=x25.a3
1635 join t3 as x26 on x25.c3=x26.a3
1636 join t3 as x27 on x26.c3=x27.a3
1637 join t3 as x28 on x27.c3=x28.a3
1638 join t3 as x29 on x28.c3=x29.a3
1639 join t3 as x30 on x29.c3=x30.a3
1640 join t3 as x31 on x30.c3=x31.a3
1641 join t3 as x32 on x31.c3=x32.a3
1642 join t3 as x33 on x32.c3=x33.a3;
1643
1644 select count(*) from t3 as x0
1645 join t3 as x1 on x0.c3=x1.a3
1646 join t3 as x2 on x1.c3=x2.a3
1647 join t3 as x3 on x2.c3=x3.a3
1648 join t3 as x4 on x3.c3=x4.a3
1649 join t3 as x5 on x4.c3=x5.a3
1650 join t3 as x6 on x5.c3=x6.a3
1651 join t3 as x7 on x6.c3=x7.a3
1652 join t3 as x8 on x7.c3=x8.a3
1653 join t3 as x9 on x8.c3=x9.a3
1654 join t3 as x10 on x9.c3=x10.a3
1655 join t3 as x11 on x10.c3=x11.a3
1656 join t3 as x12 on x11.c3=x12.a3
1657 join t3 as x13 on x12.c3=x13.a3
1658 join t3 as x14 on x13.c3=x14.a3
1659 join t3 as x15 on x14.c3=x15.a3
1660 join t3 as x16 on x15.c3=x16.a3
1661 join t3 as x17 on x16.c3=x17.a3
1662 join t3 as x18 on x17.c3=x18.a3
1663 join t3 as x19 on x18.c3=x19.a3
1664 join t3 as x20 on x19.c3=x20.a3
1665 join t3 as x21 on x20.c3=x21.a3
1666 join t3 as x22 on x21.c3=x22.a3
1667 join t3 as x23 on x22.c3=x23.a3
1668 join t3 as x24 on x23.c3=x24.a3
1669 join t3 as x25 on x24.c3=x25.a3
1670 join t3 as x26 on x25.c3=x26.a3
1671 join t3 as x27 on x26.c3=x27.a3
1672 join t3 as x28 on x27.c3=x28.a3
1673 join t3 as x29 on x28.c3=x29.a3
1674 join t3 as x30 on x29.c3=x30.a3
1675 join t3 as x31 on x30.c3=x31.a3
1676 join t3 as x32 on x31.c3=x32.a3
1677 join t3 as x33 on x32.c3=x33.a3;
1678
1679 #Mixed join
1680 explain select count(*) from t3 as x0
1681 join t3 as x1 on x0.b3=x1.d3
1682 join t3 as x2 on x1.b3=x2.d3
1683 join t3 as x3 on x2.b3=x3.d3
1684 join t3 as x4 on x3.b3=x4.d3
1685 join t3 as x5 on x4.b3=x5.d3
1686 join t3 as x6 on x5.b3=x6.d3
1687 join t3 as x7 on x6.b3=x7.d3
1688 join t3 as x8 on x7.b3=x8.d3
1689 join t3 as x9 on x8.b3=x9.d3
1690 join t3 as x10 on x9.b3=x10.d3
1691 join t3 as x11 on x10.b3=x11.d3
1692 join t3 as x12 on x11.b3=x12.d3
1693 join t3 as x13 on x12.b3=x13.d3
1694 join t3 as x14 on x13.b3=x14.d3
1695 join t3 as x15 on x14.b3=x15.d3
1696 join t3 as x16 on x15.b3=x16.d3
1697 join t3 as x17 on x15.b3=x17.a3
1698 join t3 as x18 on x16.b3=x18.d3;
1699
1700 select count(*) from t3 as x0
1701 join t3 as x1 on x0.b3=x1.d3
1702 join t3 as x2 on x1.b3=x2.d3
1703 join t3 as x3 on x2.b3=x3.d3
1704 join t3 as x4 on x3.b3=x4.d3
1705 join t3 as x5 on x4.b3=x5.d3
1706 join t3 as x6 on x5.b3=x6.d3
1707 join t3 as x7 on x6.b3=x7.d3
1708 join t3 as x8 on x7.b3=x8.d3
1709 join t3 as x9 on x8.b3=x9.d3
1710 join t3 as x10 on x9.b3=x10.d3
1711 join t3 as x11 on x10.b3=x11.d3
1712 join t3 as x12 on x11.b3=x12.d3
1713 join t3 as x13 on x12.b3=x13.d3
1714 join t3 as x14 on x13.b3=x14.d3
1715 join t3 as x15 on x14.b3=x15.d3
1716 join t3 as x16 on x15.b3=x16.d3
1717 join t3 as x17 on x15.b3=x17.a3
1718 join t3 as x18 on x16.b3=x18.d3;
1719
1720 connection ddl;
1721 drop table t1,t2,t3, t3_hash;
1722
1723 ###############################
1724 ## Test Primary key and unique key defined 'out of order'
1725 ## wrt. the order in which columns was defined in 'create table'
1726
1727 connection ddl;
1728 create table t3 (a3 int, b3 int, c3 int, d3 int,
1729 primary key(b3, a3)) engine = ndb
1730 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1731
1732 create table t3_hash (a3 int, b3 int, c3 int, d3 int,
1733 primary key(b3,a3) using hash) engine = ndb
1734 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1735
1736 create table t3_unq (pk int, a3 int not null, b3 int not null, c3 int, d3 int,
1737 primary key(pk) using hash, unique key(b3,a3) using hash) engine = ndb
1738 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1739
1740 connection spj;
1741 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
1742 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
1743 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
1744
1745 insert into t3_hash values (0x1f, 0x2f, 1, 0x1f);
1746 insert into t3_hash values (0x2f, 0x3f, 2, 0x2f);
1747 insert into t3_hash values (0x3f, 0x1f, 3, 0x3f);
1748
1749 insert into t3_unq values (1001, 0x1f, 0x2f, 1, 0x1f);
1750 insert into t3_unq values (1002, 0x2f, 0x3f, 2, 0x2f);
1751 insert into t3_unq values (1003, 0x3f, 0x1f, 3, 0x3f);
1752
1753 ## Table scans (ALL) as pushed root
1754 explain
1755 select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3;
1756 --sorted_result
1757 select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3;
1758
1759 explain
1760 select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3;
1761 --sorted_result
1762 select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3;
1763
1764 explain
1765 select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3;
1766 --sorted_result
1767 select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3;
1768
1769 ## Lookup (eq_ref/const) as pushed root
1770 explain
1771 select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3
1772 and x.a3=0x2f and x.b3=0x3f;
1773 select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3
1774 and x.a3=0x2f and x.b3=0x3f;
1775
1776 explain
1777 select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3
1778 and x.a3=0x2f and x.b3=0x3f;
1779 select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3
1780 and x.a3=0x2f and x.b3=0x3f;
1781
1782 explain
1783 select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3
1784 and x.a3=0x2f and x.b3=0x3f;
1785 select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3
1786 and x.a3=0x2f and x.b3=0x3f;
1787
1788 connection ddl;
1789 drop table t3, t3_hash, t3_unq;
1790
1791 ###########
1792
1793 connection ddl;
1794 create table t3 (a3 int, b3 int, c3 int, d3 int,
1795 primary key(a3), unique key(d3)) engine = ndb
1796 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1797
1798 connection spj;
1799 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
1800 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
1801 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
1802 insert into t3 values (0x4f, 0, null, null);
1803
1804 explain
1805 select * from t3 as t1
1806 left outer join t3 as t2 on t2.d3 = t1.d3
1807 left outer join t3 as t3 on t3.a3 = t2.d3;
1808 --sorted_result
1809 select * from t3 as t1
1810 left outer join t3 as t2 on t2.d3 = t1.d3
1811 left outer join t3 as t3 on t3.a3 = t2.d3;
1812
1813 ## Test usage of nullable unique key column in where clause on pushed parent node
1814 explain
1815 select * from t3 as t1
1816 left outer join t3 as t2 on t2.d3 = t1.a3
1817 left outer join t3 as t3 on t3.a3 = t2.d3
1818 where t1.d3 = 47;
1819 --sorted_result
1820 select * from t3 as t1
1821 left outer join t3 as t2 on t2.d3 = t1.a3
1822 left outer join t3 as t3 on t3.a3 = t2.d3
1823 where t1.d3 = 47;
1824
1825 explain
1826 select * from t3 as t1
1827 left outer join t3 as t2 on t2.d3 = t1.a3
1828 left outer join t3 as t3 on t3.a3 = t2.d3
1829 where t1.d3 >= 47;
1830 --sorted_result
1831 select * from t3 as t1
1832 left outer join t3 as t2 on t2.d3 = t1.a3
1833 left outer join t3 as t3 on t3.a3 = t2.d3
1834 where t1.d3 >= 47;
1835
1836 explain
1837 select * from t3 as t1
1838 left outer join t3 as t2 on t2.d3 = t1.a3
1839 left outer join t3 as t3 on t3.a3 = t2.d3
1840 where t1.d3 is null;
1841 --sorted_result
1842 select * from t3 as t1
1843 left outer join t3 as t2 on t2.d3 = t1.a3
1844 left outer join t3 as t3 on t3.a3 = t2.d3
1845 where t1.d3 is null;
1846
1847 explain
1848 select * from t3 as t1
1849 left outer join t3 as t2 on t2.d3 = t1.a3
1850 left outer join t3 as t3 on t3.a3 = t2.d3
1851 where t1.d3 is not null;
1852 --sorted_result
1853 select * from t3 as t1
1854 left outer join t3 as t2 on t2.d3 = t1.a3
1855 left outer join t3 as t3 on t3.a3 = t2.d3
1856 where t1.d3 is not null;
1857
1858 ################################
1859 # Bug#33451256 AbstractQueryPlan (AQP) return incorrect
1860 # access type to be used for a table.
1861 #
1862 # If the column(s) an unique index is specified over
1863 # are nullable, _and_ the key contains a NULL value,
1864 # multiple NULL-valued rows may still be returned from the UNIQUE index.
1865 # (All the non-NULL columns are unique though)
1866 #
1867 # Note that the optimizer correctly set the access type to REF (-> non-unique)
1868 # However, the AQP still decided it was 'unique' as all key values
1869 # was specified.
1870 #
1871 # Note that none of the 4 test cases added for this bug should
1872 # emit the Warning '1296 Prepared pushed join could not be executed'
1873 # with the patch for this bug. (It does without the patch)
1874
1875 # Insert an additional NULL value, such that the unique index on column d3
1876 # now has 2 null values - Should not fail.
1877 insert into t3 values (0x5f, 0x5f, 4, null);
1878
1879 # Need a non-unique index
1880 alter table t3 add index ix(b3);
1881
1882 ###################################
1883 # AQP incorrectly clasified 'where t1.d3 is null' as a
1884 # single row lookup operation (Even if explain says 'REF').
1885 #
1886 # As pushdown of index scans with a lookup root is not supported,
1887 # we could not push 't3 as t1' as a root in query below.
1888 # (t2 join t3) was pushed though.
1889 #
1890 # Correctly classifying t1-access as a index access would have
1891 # allowed all 3 tables to be pushed.
1892
1893 let $query1 =
1894 select straight_join *
1895 from t3 as t1
1896 left join t3 as t2 on t2.b3 = t1.a3
1897 left join t3 as t3 on t3.b3 = t2.d3
1898 where t1.d3 is null;
1899
1900 eval explain $query1;
1901 --sorted_result
1902 eval $query1;
1903
1904 #################################
1905 # The other way around, making t2 and t3 acccess an EQ_REF:
1906 # t1 is still incorrectly believed to be a single row operation
1907 #
1908 # A pushed join was now prepared for the full 't1 join t2 join t3' -
1909 # ... with t1 as single row lookup operation.
1910 #
1911 # At execution time it turns out that t1 had a NULL in its key,
1912 # which required an index scan access type. Thus the prepared pushed join
1913 # ends up being unsuitable, and is dropped from being executed
1914
1915 let $query2 =
1916 select straight_join *
1917 from t3 as t1
1918 left join t3 as t2 on t2.d3 = t1.a3
1919 left join t3 as t3 on t3.d3 = t2.d3
1920 where t1.d3 is null;
1921
1922 eval explain $query2;
1923 --sorted_result
1924 eval $query2;
1925
1926
1927 # The only time where the optimizer set up a REF access, and
1928 # we still do a unique single row access against it, is when
1929 # we are using a HASH index. Then there is no ordered index
1930 # available for doing a range type access:
1931
1932 alter table t3 drop index d3;
1933
1934 # Note that a 'using hash' index will warn about doing full table scans
1935 # if the key has NULL values
1936 alter table t3 add unique key(d3) using hash;
1937
1938 ######################################
1939 # Use the same two queries a above, now with a redefined HASH index:
1940 # t1 access will now either be a unique single row acces, if key
1941 # has no NULLs, or a full table scan. Which of them we can't really
1942 # know until execution time when the key values are known, unless
1943 # they are a const-literal.
1944 #
1945 # The first one now fails to push t1 as root as it belived it was
1946 # a lookup type operation with t2 & t3 as scan child.
1947 #
1948 # As the t1 key is constructed from 't1.d3 is null', we should be
1949 # able to detect early that t1 will be accessed using a full table scan.
1950 # Thus preparing a scan type pushed join for all 3 tables
1951
1952 eval explain $query1;
1953 --sorted_result
1954 eval $query1;
1955
1956 ####################################
1957 # The other query was, and still is, asuming t1 to be a single row lookup.
1958 # Thus we may prepare a pushed lookup join for all 3 tables.
1959 #
1960 # However, as the t1 condition 't1.d3 is null' turns out to need a
1961 # full table scan, the pushed join is rejected at execution time
1962 #
1963 # We should be able to detect at prepare time that the key contain
1964 # a NULL value, thus needing a table scan.
1965
1966 eval explain $query2;
1967 --sorted_result
1968 eval $query2;
1969
1970 # Bug#33451256 ... end
1971 ###################################
1972
1973 connection ddl;
1974 drop table t3;
1975
1976 ####### Composite unique keys, 'const' is part of EQ_REF on child nodes ####
1977
1978 connection ddl;
1979 create table t3 (a3 int not null, b3 int not null, c3 int, d3 int,
1980 primary key(a3), unique key(b3,d3), unique key(c3,b3), unique key(c3,d3)) engine = ndb
1981 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
1982
1983 connection spj;
1984 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
1985 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
1986 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
1987 insert into t3 values (0x40, 0, null, null);
1988 insert into t3 values (0x41, 0, null, null);
1989 insert into t3 values (0x42, 0, 4, null);
1990 insert into t3 values (0x43, 0, null, 0x43);
1991
1992 ## Baseline: Not pushed as only one of the columns in unique indexes are REF'ed
1993 explain
1994 select straight_join *
1995 from t3 as x join t3 as y on x.b3 = y.b3;
1996 --sorted_result
1997 select straight_join *
1998 from t3 as x join t3 as y on x.b3 = y.b3;
1999
2000 ## Extend query above with 'where <const cond>'
2001 explain
2002 select straight_join *
2003 from t3 as x join t3 as y on x.b3 = y.b3
2004 where y.d3 = 0x2f;
2005 --sorted_result
2006 select straight_join *
2007 from t3 as x join t3 as y on x.b3 = y.b3
2008 where y.d3 = 0x2f;
2009
2010 --replace_column 10 # 11 #
2011 explain
2012 select straight_join *
2013 from t3 as x join t3 as y on x.c3 = y.c3
2014 where y.d3 = 0x2f;
2015 --sorted_result
2016 select straight_join *
2017 from t3 as x join t3 as y on x.c3 = y.c3
2018 where y.d3 = 0x2f;
2019
2020 explain
2021 select straight_join *
2022 from t3 as x join t3 as y on x.d3 = y.d3
2023 where y.b3 = 0x2f;
2024 --sorted_result
2025 select straight_join *
2026 from t3 as x join t3 as y on x.d3 = y.d3
2027 where y.b3 = 0x2f;
2028
2029 explain
2030 select straight_join *
2031 from t3 as x join t3 as y on x.d3 = y.d3
2032 where y.b3 = 0x20+0x2f;
2033 --sorted_result
2034 select straight_join *
2035 from t3 as x join t3 as y on x.d3 = y.d3
2036 where y.b3 = 0x20+0x2f;
2037
2038
2039 ## Not pushable as 'not null' is actually not a single const value
2040 --replace_column 10 # 11 #
2041 explain
2042 select straight_join *
2043 from t3 as x join t3 as y on x.b3 = y.b3
2044 where y.d3 is not null;
2045 --sorted_result
2046 select straight_join *
2047 from t3 as x join t3 as y on x.b3 = y.b3
2048 where y.d3 is not null;
2049
2050 ## Neither 'is null' pushable as uniqueness is not defined for null
2051 ## ... and null's are not present in the unique index
2052 explain
2053 select straight_join *
2054 from t3 as x join t3 as y on x.b3 = y.b3
2055 where y.d3 is null;
2056 --sorted_result
2057 select straight_join *
2058 from t3 as x join t3 as y on x.b3 = y.b3
2059 where y.d3 is null;
2060
2061 explain
2062 select straight_join *
2063 from t3 as x join t3 as y on x.c3 = y.c3
2064 where y.b3 = 0;
2065 --sorted_result
2066 select straight_join *
2067 from t3 as x join t3 as y on x.c3 = y.c3
2068 where y.b3 = 0;
2069
2070 ## As 'b3' is defined as 'not null', this query will optimized as 'Impossible WHERE' (No push)
2071 explain
2072 select straight_join *
2073 from t3 as x join t3 as y on x.c3 = y.c3
2074 where y.b3 is null;
2075 --sorted_result
2076 select straight_join *
2077 from t3 as x join t3 as y on x.c3 = y.c3
2078 where y.b3 is null;
2079
2080 ## Will break up query in 2 pushed joins.
2081 ## Last join (join t3 as y2) refer x1.c3 which will
2082 ## be handled as a constant paramValue wrt. scope of the
2083 ## second pushed join.
2084 explain
2085 select straight_join * from
2086 t3 as x1
2087 join t3 as y1 on y1.b3 = x1.b3 and y1.d3 = x1.d3
2088 join t3 as x2 on x2.b3 = y1.b3+0
2089 join t3 as y2 on y2.b3 = x2.c3 and y2.d3 = x1.c3;
2090 --sorted_result
2091 select straight_join * from
2092 t3 as x1
2093 join t3 as y1 on y1.b3 = x1.b3 and y1.d3 = x1.d3
2094 join t3 as x2 on x2.b3 = y1.b3+0
2095 join t3 as y2 on y2.b3 = x2.c3 and y2.d3 = x1.c3;
2096
2097
2098 ###########################
2099 ### Prepared statments ####
2100 ###########################
2101
2102 prepare stmt1 from
2103 'select straight_join *
2104 from t3 as x join t3 as y on x.b3 = y.b3
2105 where y.d3 = 0x2f';
2106
2107 #execute multiple times
2108 execute stmt1;
2109 execute stmt1;
2110
2111 # Execute after drop expected to fail
2112 drop prepare stmt1;
2113 --error 1243
2114 execute stmt1;
2115
2116 # Multiple prepare of same stmt should silently discard prev prepared stmt
2117 prepare stmt1 from
2118 'select straight_join *
2119 from t3 as x join t3 as y on x.b3 = y.b3
2120 where y.d3 = 0x2f';
2121 prepare stmt1 from
2122 'select straight_join *
2123 from t3 as x join t3 as y on x.b3 = y.b3
2124 where y.d3 = 0x2f';
2125 drop prepare stmt1;
2126
2127 #Prepare explain'ed statement and execute it
2128 prepare stmt1 from
2129 'explain select straight_join *
2130 from t3 as x join t3 as y on x.b3 = y.b3
2131 where y.d3 = 0x2f';
2132 execute stmt1;
2133 execute stmt1;
2134
2135 #survives commit;
2136 commit;
2137 execute stmt1;
2138
2139 # Drop index used by query -> Query plan should change to unpushed join
2140 connection ddl;
2141 drop index b3 on t3;
2142
2143 connection spj;
2144 --replace_column 10 # 11 #
2145 execute stmt1;
2146
2147 # Then recreate it -> original query plan
2148 connection ddl;
2149 create unique index b3 on t3(b3,d3);
2150
2151 connection spj;
2152 execute stmt1;
2153 drop prepare stmt1;
2154
2155 ### Prepared stmt with dynamic parameters ('?') ###
2156 prepare stmt1 from
2157 'explain select straight_join *
2158 from t3 as x join t3 as y on x.b3 = y.b3
2159 where y.d3 = ?';
2160
2161 set @a=47;
2162 execute stmt1 using @a;
2163 set @a=0;
2164 execute stmt1 using @a;
2165 set @a=null;
2166 execute stmt1 using @a;
2167
2168 prepare stmt1 from
2169 'select straight_join *
2170 from t3 as x join t3 as y on x.b3 = y.b3
2171 where y.d3 = ?';
2172
2173 set @a=47;
2174 execute stmt1 using @a;
2175 set @a=0;
2176 execute stmt1 using @a;
2177 set @a=null;
2178 execute stmt1 using @a;
2179
2180 prepare stmt1 from
2181 'explain select straight_join *
2182 from t3 as x join t3 as y on x.b3 = y.b3 and x.d3 = y.d3
2183 where x.a3 = ?';
2184
2185 set @a=47;
2186 execute stmt1 using @a;
2187 set @a=0;
2188 execute stmt1 using @a;
2189 set @a=null;
2190 execute stmt1 using @a;
2191
2192 prepare stmt1 from
2193 'select straight_join *
2194 from t3 as x join t3 as y on x.b3 = y.b3 and x.d3 = y.d3
2195 where x.a3 = ?';
2196
2197 set @a=47;
2198 execute stmt1 using @a;
2199 set @a=0;
2200 execute stmt1 using @a;
2201 set @a=null;
2202 execute stmt1 using @a;
2203
2204 connection ddl;
2205 drop table t3;
2206
2207 connection spj;
2208 # Execute after table dropped should fail
2209 set @a=47;
2210 --error 1146
2211 execute stmt1 using @a;
2212
2213 ####################
2214
2215 # test index scan disguised as JT_ALL
2216 connection ddl;
2217 create table t1 (a int primary key, b int, c int, index(b,c)) engine = ndb
2218 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2219
2220 connection spj;
2221 insert into t1 values (1,null, 2);
2222 insert into t1 values (2,1, null);
2223 insert into t1 values (3,2,2);
2224 insert into t1 values (4,null, 2);
2225 insert into t1 values (5,1, null);
2226 insert into t1 values (6,2,2);
2227
2228 set ndb_join_pushdown=false;
2229
2230 --sorted_result
2231 select *
2232 from t1
2233 join t1 as t2 on (t2.b = t1.b or t2.b = t1.a)
2234 join t1 as t3 on t3.a = t2.a
2235 join t1 as t4 on t4.a = t3.b /* index scan disguised as JT_ALL, pushdown=off */;
2236
2237 set ndb_join_pushdown=true;
2238
2239 explain
2240 select *
2241 from t1
2242 join t1 as t2 on (t2.b = t1.b or t2.b = t1.a)
2243 join t1 as t3 on t3.a = t2.a
2244 join t1 as t4 on t4.a = t3.b /* index scan disguised as JT_ALL, pushdown=on */;
2245 --sorted_result
2246 select *
2247 from t1
2248 join t1 as t2 on (t2.b = t1.b or t2.b = t1.a)
2249 join t1 as t3 on t3.a = t2.a
2250 join t1 as t4 on t4.a = t3.b /* index scan disguised as JT_ALL, pushdown=on */;
2251
2252 ## Test subquery execution where 'Full scan on null key' strategy requires
2253 ## table scan execution in addition to the key lookup which was prepared
2254 ## as part of the pushed join NdbQuery
2255 explain
2256 select *
2257 from t1 where b in
2258 (select x.a from t1 as x join t1 as y on (y.a = x.b))
2259 xor c > 5;
2260 --sorted_result
2261 select *
2262 from t1 where b in
2263 (select x.a from t1 as x join t1 as y on (y.a = x.b))
2264 xor c > 5;
2265
2266 ##############
2267 ## Subqueries with EQ_REFs in subquery containing an outer referrences
2268 ## to 't1.b' should not be pushed as outer referrences are outside
2269 ## the scope of our JOIN_TAB's
2270 ##############
2271 --replace_column 10 # 11 #
2272 explain
2273 select t1.a, (select straight_join x.a from t1 as x join t1 as y on x.a=y.b where y.a = t1.b) from t1;
2274 --sorted_result
2275 select t1.a, (select straight_join x.a from t1 as x join t1 as y on x.a=y.b where y.a = t1.b) from t1;
2276
2277 connection ddl;
2278 drop table t1;
2279
2280 # mixed engines
2281
2282 connection ddl;
2283 create table t1 (a int primary key, b int) engine = ndb
2284 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2285 create table t2 (a int primary key, b int) engine = myisam;
2286
2287 connection spj;
2288 insert into t1 values(1,1), (2,2), (3,3), (4,4);
2289 insert into t2 values(1,1), (2,2), (3,3), (4,4);
2290
2291 explain
2292 select * from t1, t2, t1 as t3
2293 where t2.a = t1.b
2294 and t3.a = t2.b /* mixed engines */;
2295 --sorted_result
2296 select * from t1, t2, t1 as t3
2297 where t2.a = t1.b
2298 and t3.a = t2.b /* mixed engines */;
2299
2300 connection ddl;
2301 drop table t1, t2;
2302
2303 # Tables with blob, but not in the selected columns:
2304
2305 connection ddl;
2306 create table t1 (a int primary key, b int, c blob) engine = ndb
2307 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2308 create table t2 (a int primary key, b int) engine = ndb
2309 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2310
2311 connection spj;
2312 insert into t1 values (1,1, 'kalle');
2313 insert into t1 values (2,1, 'kalle');
2314 insert into t1 values (3,3, 'kalle');
2315 insert into t1 values (4,1, 'kalle');
2316
2317 insert into t2 values (1,1);
2318 insert into t2 values (2,1);
2319 insert into t2 values (3,3);
2320 insert into t2 values (4,1);
2321
2322 set ndb_join_pushdown=true;
2323 explain
2324 select t1.a, t1.b, t2.a, t2.b
2325 from t1, t2
2326 where t2.a = t1.b;
2327 --sorted_result
2328 select t1.a, t1.b, t2.a, t2.b
2329 from t1, t2
2330 where t2.a = t1.b;
2331
2332 explain
2333 select t1.a, t1.b, t2.a, t2.b
2334 from t1, t2
2335 where t2.a = t1.b
2336 and t1.a = 2;
2337 --sorted_result
2338 select t1.a, t1.b, t2.a, t2.b
2339 from t1, t2
2340 where t2.a = t1.b
2341 and t1.a = 2;
2342
2343 explain
2344 select t1.a, t1.b, t2.a, t2.b
2345 from t1, t2
2346 where t1.a = t2.b;
2347 --sorted_result
2348 select t1.a, t1.b, t2.a, t2.b
2349 from t1, t2
2350 where t2.a = t1.b;
2351
2352 explain
2353 select t1.a, t1.b, t2.a, t2.b
2354 from t1, t2
2355 where t1.a = t2.b
2356 and t2.a = 3;
2357 --sorted_result
2358 select t1.a, t1.b, t2.a, t2.b
2359 from t1, t2
2360 where t1.a = t2.b
2361 and t2.a = 3;
2362
2363 #
2364 # result sets contain blob
2365 # i.e no push
2366 explain
2367 select *
2368 from t1, t2
2369 where t2.a = t1.b;
2370 --sorted_result
2371 select *
2372 from t1, t2
2373 where t2.a = t1.b;
2374
2375 explain
2376 select *
2377 from t1, t2
2378 where t2.a = t1.b
2379 and t1.a = 2;
2380 --sorted_result
2381 select *
2382 from t1, t2
2383 where t2.a = t1.b
2384 and t1.a = 2;
2385
2386 explain
2387 select *
2388 from t1, t2
2389 where t1.a = t2.b;
2390 --sorted_result
2391 select *
2392 from t1, t2
2393 where t2.a = t1.b;
2394
2395 explain
2396 select *
2397 from t1, t2
2398 where t1.a = t2.b
2399 and t2.a = 3;
2400 --sorted_result
2401 select *
2402 from t1, t2
2403 where t1.a = t2.b
2404 and t2.a = 3;
2405
2406 connection ddl;
2407 drop table t1, t2;
2408
2409 ## Test usage of a constValue() as part of the EQ_REF key relating a child operation
2410 ## with its previous parents.
2411 ## All datatypes are tested in the section below
2412 ##
2413 connection ddl;
2414 create table t3 (a3 int, b3 tinyint, c3 int not null, d3 int not null,
2415 primary key(a3,b3)) engine = ndb
2416 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2417
2418 connection spj;
2419 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2420 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2421 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2422 explain
2423 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3="63";
2424 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3="63";
2425
2426 connection ddl;
2427 drop table t3;
2428 create table t3 (a3 int, b3 tinyint unsigned, c3 int not null, d3 int not null,
2429 primary key(a3,b3)) engine = ndb
2430 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2431 connection spj;
2432 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2433 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2434 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2435 explain
2436 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2437 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2438
2439 connection ddl;
2440 drop table t3;
2441 create table t3 (a3 int, b3 smallint, c3 int not null, d3 int not null,
2442 primary key(a3,b3)) engine = ndb
2443 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2444 connection spj;
2445 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2446 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2447 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2448 explain
2449 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2450 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2451
2452 connection ddl;
2453 drop table t3;
2454 create table t3 (a3 int, b3 smallint unsigned, c3 int not null, d3 int not null,
2455 primary key(a3,b3)) engine = ndb
2456 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2457 connection spj;
2458 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2459 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2460 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2461 explain
2462 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2463 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2464
2465 connection ddl;
2466 drop table t3;
2467 create table t3 (a3 int, b3 mediumint, c3 int not null, d3 int not null,
2468 primary key(a3,b3)) engine = ndb
2469 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2470 connection spj;
2471 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2472 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2473 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2474 explain
2475 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2476 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2477
2478 connection ddl;
2479 drop table t3;
2480 create table t3 (a3 int, b3 mediumint unsigned, c3 int not null, d3 int not null,
2481 primary key(a3,b3)) engine = ndb
2482 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2483 connection spj;
2484 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2485 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2486 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2487 explain
2488 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2489 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2490
2491 connection ddl;
2492 drop table t3;
2493 create table t3 (a3 int, b3 int, c3 int not null, d3 int not null,
2494 primary key(a3,b3)) engine = ndb
2495 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2496 connection spj;
2497 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2498 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2499 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2500 explain
2501 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2502 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2503
2504 connection ddl;
2505 drop table t3;
2506 create table t3 (a3 int, b3 int unsigned, c3 int not null, d3 int not null,
2507 primary key(a3,b3)) engine = ndb
2508 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2509 connection spj;
2510 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2511 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2512 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2513 explain
2514 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2515 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2516
2517 connection ddl;
2518 drop table t3;
2519 create table t3 (a3 int, b3 bigint, c3 int not null, d3 int not null,
2520 primary key(a3,b3)) engine = ndb
2521 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2522 connection spj;
2523 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2524 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2525 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2526 explain
2527 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2528 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2529
2530 connection ddl;
2531 drop table t3;
2532 create table t3 (a3 int, b3 bigint unsigned, c3 int not null, d3 int not null,
2533 primary key(a3,b3)) engine = ndb
2534 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2535 connection spj;
2536 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2537 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2538 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2539 explain
2540 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2541 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
2542
2543 connection ddl;
2544 drop table t3;
2545 create table t3 (a3 int, b3 boolean, c3 int not null, d3 int not null,
2546 primary key(a3,b3)) engine = ndb
2547 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2548 connection spj;
2549 insert into t3 values (0x1f, 0, 1, 0x1f);
2550 insert into t3 values (0x2f, 1, 2, 0x2f);
2551 insert into t3 values (0x3f, 0, 3, 0x3f);
2552 explain
2553 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=1;
2554 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=1;
2555
2556 connection ddl;
2557 drop table t3;
2558 create table t3 (a3 int, b3 float, c3 int not null, d3 int not null,
2559 primary key(a3,b3)) engine = ndb
2560 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2561 connection spj;
2562 insert into t3 values (0x1f, 2.71, 1, 0x1f);
2563 insert into t3 values (0x2f, 3.00, 2, 0x2f);
2564 insert into t3 values (0x3f, 0.50, 3, 0x3f);
2565 explain
2566 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
2567 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
2568
2569 connection ddl;
2570 drop table t3;
2571 create table t3 (a3 int, b3 float unsigned, c3 int not null, d3 int not null,
2572 primary key(a3,b3)) engine = ndb
2573 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2574 connection spj;
2575 insert into t3 values (0x1f, 2.71, 1, 0x1f);
2576 insert into t3 values (0x2f, 3.00, 2, 0x2f);
2577 insert into t3 values (0x3f, 0.50, 3, 0x3f);
2578 explain
2579 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
2580 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.0;
2581
2582 connection ddl;
2583 drop table t3;
2584 create table t3 (a3 int, b3 double, c3 int not null, d3 int not null,
2585 primary key(a3,b3)) engine = ndb
2586 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2587 connection spj;
2588 insert into t3 values (0x1f, 2.71, 1, 0x1f);
2589 insert into t3 values (0x2f, 3.14, 2, 0x2f);
2590 insert into t3 values (0x3f, 0.50, 3, 0x3f);
2591 explain
2592 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
2593 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
2594
2595 connection ddl;
2596 drop table t3;
2597 create table t3 (a3 int, b3 double unsigned, c3 int not null, d3 int not null,
2598 primary key(a3,b3)) engine = ndb
2599 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2600 connection spj;
2601 insert into t3 values (0x1f, 2.71, 1, 0x1f);
2602 insert into t3 values (0x2f, 3.14, 2, 0x2f);
2603 insert into t3 values (0x3f, 0.50, 3, 0x3f);
2604 explain
2605 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
2606 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
2607
2608 connection ddl;
2609 drop table t3;
2610 create table t3 (a3 int, b3 decimal, c3 int not null, d3 int not null,
2611 primary key(a3,b3)) engine = ndb
2612 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2613 connection spj;
2614 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2615 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2616 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2617 explain
2618 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=63;
2619 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=63;
2620
2621 connection ddl;
2622 drop table t3;
2623 create table t3 (a3 int, b3 decimal(12,4), c3 int not null, d3 int not null,
2624 primary key(a3,b3)) engine = ndb
2625 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2626 connection spj;
2627 insert into t3 values (0x1f, 2.71, 1, 0x1f);
2628 insert into t3 values (0x2f, 3.14, 2, 0x2f);
2629 insert into t3 values (0x3f, 0.50, 3, 0x3f);
2630 explain
2631 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
2632 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=3.14;
2633
2634 connection ddl;
2635 drop table t3;
2636 create table t3 (a3 int, b3 date, c3 int not null, d3 int not null,
2637 primary key(a3,b3)) engine = ndb
2638 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2639 connection spj;
2640 insert into t3 values (0x1f, '1905-05-17', 1, 0x1f);
2641 insert into t3 values (0x2f, '2000-02-28', 2, 0x2f);
2642 insert into t3 values (0x3f, '2000-02-29', 3, 0x3f);
2643 explain
2644 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28';
2645 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28';
2646
2647 ## Temp removed due to lack of DATETIME2 support
2648 #connection ddl;
2649 # drop table t3;
2650 # create table t3 (a3 int, b3 datetime, c3 int not null, d3 int not null,
2651 # primary key(a3,b3)) engine = ndb;
2652 #connection spj;
2653 # insert into t3 values (0x1f, '1905-05-17 12:30:00', 1, 0x1f);
2654 # insert into t3 values (0x2f, '2000-02-28 23:59:00', 2, 0x2f);
2655 # insert into t3 values (0x3f, '2000-02-29 12:59:59', 2, 0x3f);
2656 # explain
2657 # select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28 23:59';
2658 # select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='2000-02-28 23:59';
2659 #
2660 #connection ddl;
2661 # drop table t3;
2662 # create table t3 (a3 int, b3 time, c3 int not null, d3 int not null,
2663 # primary key(a3,b3)) engine = ndb;
2664 #connection spj;
2665 # insert into t3 values (0x1f, '12:30:00', 1, 0x1f);
2666 # insert into t3 values (0x2f, '23:59:00', 2, 0x2f);
2667 # insert into t3 values (0x3f, '12:59:59', 2, 0x3f);
2668 # explain
2669 # select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='23:59';
2670 # select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='23:59';
2671
2672 connection ddl;
2673 drop table t3;
2674 create table t3 (a3 int, b3 char(16), c3 int not null, d3 int not null,
2675 primary key(a3,b3)) engine = ndb
2676 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2677 connection spj;
2678 insert into t3 values (0x1f, 'Ole', 1, 0x1f);
2679 insert into t3 values (0x2f, 'Dole', 2, 0x2f);
2680 insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
2681 explain
2682 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
2683 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
2684
2685 connection ddl;
2686 drop table t3;
2687 create table t3 (a3 int, b3 varchar(16), c3 int not null, d3 int not null,
2688 primary key(a3,b3)) engine = ndb
2689 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2690 connection spj;
2691 insert into t3 values (0x1f, 'Ole', 1, 0x1f);
2692 insert into t3 values (0x2f, 'Dole', 2, 0x2f);
2693 insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
2694 explain
2695 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
2696 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
2697
2698 connection ddl;
2699 drop table t3;
2700 create table t3 (a3 int, b3 varchar(512), c3 int not null, d3 int not null,
2701 primary key(a3,b3)) engine = ndb
2702 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2703 connection spj;
2704 insert into t3 values (0x1f, 'Ole', 1, 0x1f);
2705 insert into t3 values (0x2f, 'Dole', 2, 0x2f);
2706 insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
2707 explain
2708 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
2709 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
2710
2711 connection ddl;
2712 drop table t3;
2713 create table t3 (a3 int, b3 binary(16), c3 int not null, d3 int not null,
2714 primary key(a3,b3)) engine = ndb
2715 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2716 connection spj;
2717 insert into t3 values (0x1f, 'Ole', 1, 0x1f);
2718 insert into t3 values (0x2f, 'Dole', 2, 0x2f);
2719 insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
2720 explain
2721 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
2722 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
2723
2724 connection ddl;
2725 drop table t3;
2726 create table t3 (a3 int, b3 varbinary(16), c3 int not null, d3 int not null,
2727 primary key(a3,b3)) engine = ndb
2728 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2729 connection spj;
2730 insert into t3 values (0x1f, 'Ole', 1, 0x1f);
2731 insert into t3 values (0x2f, 'Dole', 2, 0x2f);
2732 insert into t3 values (0x3f, 'Doffen', 2, 0x3f);
2733 explain
2734 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
2735 select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3='Dole';
2736
2737 connection ddl;
2738 drop table t3;
2739
2740
2741 ## Joins where the datatype of the EQ_REF columns are not identical
2742 ## should not be pushed
2743 ##
2744 connection ddl;
2745 create table t3 (a3 int, b3 tinyint, c3 int not null, d3 int not null,
2746 primary key(a3,b3)) engine = ndb
2747 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2748
2749 connection spj;
2750 insert into t3 values (0x1f, 0x2f, 1, 0x1f);
2751 insert into t3 values (0x2f, 0x3f, 2, 0x2f);
2752 insert into t3 values (0x3f, 0x1f, 3, 0x3f);
2753 explain
2754 select * from t3 x, t3 y where y.a3=x.b3 and y.b3="63";
2755 select * from t3 x, t3 y where y.a3=x.b3 and y.b3="63";
2756
2757 connection ddl;
2758 drop table t3;
2759
2760
2761 ##
2762 ## Testing of varchar datatype as part of lookup key and index bounds.
2763 ## Need special attention due to the 'ShrinkVarchar' format used by mysqld.
2764
2765 connection ddl;
2766 create table t3 (a3 varchar(16), b3 int, c3 int not null, d3 int not null,
2767 primary key(a3,b3)) engine = ndb
2768 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2769
2770 connection spj;
2771 insert into t3 values ('Ole', 0x1f, 1, 0x1f);
2772 insert into t3 values ('Dole', 0x2f, 2, 0x2f);
2773 insert into t3 values ('Doffen', 0x3f, 2, 0x3f);
2774
2775 # Varchar is lookup key
2776 explain
2777 select * from t3 x, t3 y where x.a3='Dole' and x.b3=0x2f and y.a3=x.a3 and y.b3=x.d3;
2778 select * from t3 x, t3 y where x.a3='Dole' and x.b3=0x2f and y.a3=x.a3 and y.b3=x.d3;
2779
2780 # Varchar as hi/low bound
2781 explain
2782 select * from t3 x, t3 y where x.a3='Dole' and y.a3=x.a3 and y.b3=x.d3;
2783 select * from t3 x, t3 y where x.a3='Dole' and y.a3=x.a3 and y.b3=x.d3;
2784
2785 connection ddl;
2786 drop table t3;
2787
2788 connection ddl;
2789 create table t1 (k int primary key, b int) engine = ndb
2790 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2791
2792 connection spj;
2793 insert into t1 values (1,1), (2,1), (3,1), (4,1);
2794
2795 ## Pushed join driven by a scan, with cached row lookups:
2796 ## (Note: We force the Scan to not be pushed as the parent op
2797 ## by making the join condition on t2 a non-FIELD_ITEM ('t1.b+0')
2798 ## As all column 'b' has the same value (1), the scan will refer
2799 ## the same t2 (parent) row in every access. This will trigger the
2800 ## row caching in join_read_key() where we eliminate redundant lookups
2801 ## where 'next row == current row'. In order to work for linked operations,
2802 ## the value and status for all linked tables should be kept unaltered.
2803 explain
2804 select *
2805 from t1
2806 straight_join t1 as t2 on t2.k = t1.b+0
2807 straight_join t1 as t3 on t3.k = t2.b
2808 straight_join t1 as t4 on t4.k = t1.b;
2809 --sorted_result
2810 select *
2811 from t1
2812 straight_join t1 as t2 on t2.k = t1.b+0
2813 straight_join t1 as t3 on t3.k = t2.b
2814 straight_join t1 as t4 on t4.k = t1.b;
2815
2816 ## Similar example as above, except that access to 't2' is made
2817 ## a const table access
2818 explain
2819 select *
2820 from t1
2821 straight_join t1 as t2 on t2.k = t1.b+0
2822 straight_join t1 as t3 on t3.k = t2.b
2823 straight_join t1 as t4 on t4.k = t1.b
2824 where t2.k = 1;
2825 --sorted_result
2826 select *
2827 from t1
2828 straight_join t1 as t2 on t2.k = t1.b+0
2829 straight_join t1 as t3 on t3.k = t2.b
2830 straight_join t1 as t4 on t4.k = t1.b
2831 where t2.k = 1;
2832
2833 connection ddl;
2834 drop table t1;
2835
2836 ##
2837 # Try with higher row-count to test batching/flow control
2838 #
2839 --error 0,1193
2840 set global debug='+d,max_64rows_in_spj_batches';
2841
2842 connection ddl;
2843 create table t1 (
2844 a int not null auto_increment,
2845 b char(255) not null,
2846 c int not null,
2847 d char(255) not null,
2848 primary key (`a`,`b`)
2849 ) engine=ndbcluster
2850 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2851
2852 connection spj;
2853 let $1=1000;
2854 disable_query_log;
2855 while ($1)
2856 {
2857 eval insert into t1(a,b,c,d) values
2858 ($1, 'a', $1, 'a'),($1, 'b', $1+1, 'b'),($1, 'c', $1-1, 'c');
2859 dec $1;
2860 }
2861 enable_query_log;
2862
2863 explain
2864 select count(*)
2865 from t1
2866 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
2867 join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
2868 select count(*)
2869 from t1
2870 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
2871 join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
2872
2873 explain
2874 select count(*)
2875 from t1
2876 join t1 as t2 on t2.a = t1.c
2877 join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
2878 select count(*)
2879 from t1
2880 join t1 as t2 on t2.a = t1.c
2881 join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
2882
2883 explain
2884 select count(*)
2885 from t1
2886 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
2887 join t1 as t3 on t3.a = t2.c;
2888 select count(*)
2889 from t1
2890 join t1 as t2 on t2.a = t1.c and t2.b = t1.d
2891 join t1 as t3 on t3.a = t2.c;
2892
2893 connection ddl;
2894 alter table t1 partition by key(a);
2895
2896 connection spj;
2897 explain
2898 select count(*)
2899 from t1
2900 join t1 as t2 on t2.a = t1.c
2901 join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
2902 select count(*)
2903 from t1
2904 join t1 as t2 on t2.a = t1.c
2905 join t1 as t3 on t3.a = t2.c and t3.b = t2.d;
2906
2907 connection ddl;
2908 drop table t1;
2909
2910 --error 0,1193
2911 set global debug=@save_debug;
2912
2913 # Pushed join accessing disk data.
2914
2915 connection ddl;
2916 create logfile group lg1
2917 add undofile 'undofile.dat'
2918 initial_size 1m
2919 undo_buffer_size = 1m
2920 engine=ndb;
2921
2922 create tablespace ts1
2923 add datafile 'datafile.dat'
2924 use logfile group lg1
2925 initial_size 6m
2926 engine ndb;
2927
2928 create table t1 (a int not null,
2929 b int not null storage disk,
2930 c int not null storage memory,
2931 primary key(a))
2932 tablespace ts1 storage disk engine = ndb
2933 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2934
2935 connection spj;
2936 insert into t1 values (10, 11, 11);
2937 insert into t1 values (11, 12, 12);
2938 insert into t1 values (12, 13, 13);
2939
2940 connection ddl;
2941 create table t2 (a int not null,
2942 b int not null, primary key(a)) engine = ndb
2943 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
2944
2945 connection spj;
2946 insert into t2 values (10, 11);
2947 insert into t2 values (11, 12);
2948 insert into t2 values (12, 13);
2949
2950 # Disk data in projection of first op.
2951 explain select * from t1, t2 where t1.c = t2.a;
2952 --sorted_result
2953 select * from t1, t2 where t1.c = t2.a;
2954
2955 explain select * from t1, t2 where t1.a=11 and t1.c = t2.a;
2956 select * from t1, t2 where t1.a=11 and t1.c = t2.a;
2957
2958 # Disk data in projection of second op.
2959 explain select * from t2, t1 where t2.b = t1.a;
2960 --sorted_result
2961 select * from t2, t1 where t2.b = t1.a;
2962
2963 explain select * from t2, t1 where t2.a=11 and t2.b = t1.a;
2964 select * from t2, t1 where t2.a=11 and t2.b = t1.a;
2965
2966 # Disk data in predicate but not in projection
2967 explain select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.b = t2.a;
2968 --sorted_result
2969 select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.b = t2.a;
2970
2971 explain select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.a=11 and t1.b = t2.a;
2972 select t1.a, t1.c, t2.a, t2.b from t1, t2 where t1.a=11 and t1.b = t2.a;
2973
2974 connection ddl;
2975 drop table t1;
2976 drop table t2;
2977
2978 alter tablespace ts1
2979 drop datafile 'datafile.dat';
2980
2981 drop tablespace ts1;
2982
2983 drop logfile group lg1
2984 engine ndb;
2985 connection spj;
2986
2987 # Store old counter values.
2988 connection ddl;
2989 create temporary table old_count
2990 select counter_name, sum(val) as val
2991 from ndbinfo.counters
2992 where block_name='DBSPJ'
2993 group by counter_name;
2994
2995 connection ddl;
2996 # Specify number of partition to be independent of configured #LDM's
2997 create table t1 (a int not null,
2998 b int not null,
2999 c int not null,
3000 primary key(a))
3001 engine = ndb
3002 partition by key() partitions 8;
3003
3004 connection spj;
3005 # We use key values that have the same representation in little and big endian.
3006 # Otherwise, the numbers for local and remote reads may depend on endian-ness,
3007 # since hashing is endian dependent.
3008 insert into t1 values (1, 2, 2);
3009 insert into t1 values (2, 3, 3);
3010 insert into t1 values (3, 4, 4);
3011
3012 # Run some queries that should increment the counters.
3013 select * from t1 t1, t1 t2 where t1.a = 2 and t2.a = t1.b;
3014
3015 select count(*) from t1 t1, t1 t2 where t2.a = t1.b;
3016
3017 select count(*) from t1 t1, t1 t2 where t1.a >= 2 and t2.a = t1.b;
3018
3019
3020 # Get new counter values.
3021 connection ddl;
3022 create temporary table new_count
3023 select counter_name, sum(val) as val
3024 from ndbinfo.counters
3025 where block_name='DBSPJ'
3026 group by counter_name;
3027
3028 # Compute the difference.
3029 --sorted_result
3030 select new_count.counter_name, new_count.val - old_count.val
3031 from new_count, old_count
3032 where new_count.counter_name = old_count.counter_name
3033 and new_count.counter_name <> 'LOCAL_READS_SENT'
3034 and new_count.counter_name <> 'REMOTE_READS_SENT';
3035
3036 select 'READS_SENT', sum(new_count.val - old_count.val)
3037 from new_count, old_count
3038 where new_count.counter_name = old_count.counter_name
3039 and (new_count.counter_name = 'LOCAL_READS_SENT'
3040 or new_count.counter_name = 'REMOTE_READS_SENT');
3041
3042 connection ddl;
3043 drop table old_count;
3044 drop table new_count;
3045 drop table t1;
3046
3047 ### Test that scan filters are used for pushed operations.
3048
3049 connection ddl;
3050 create table t1 (
3051 a int primary key,
3052 b int,
3053 c int) engine = ndb
3054 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
3055
3056 connection spj;
3057 insert into t1 values (1, 2, 3);
3058 insert into t1 values (2, 3, 4);
3059 insert into t1 values (3, 4, 5);
3060
3061 # Find the total number of lookups issued by the SPJ blocks.
3062 let $spj_lookups = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT'), Value, 1);
3063
3064
3065 # Root scan should give only one tuple if scan filter is pushed.
3066 # Therefore only one lookup on 'y'.
3067 --replace_column 10 # 11 #
3068 explain select * from t1 x, t1 y where x.b=y.a and x.c=4;
3069
3070 select * from t1 x, t1 y where x.b=y.a and x.c=4;
3071
3072 --disable_query_log
3073 --eval select sum(val) - $spj_lookups as lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT')
3074 --enable_query_log
3075
3076 # Lookup on y should only give one result tuple if filter is pushed.
3077 # This should give 3 lookups on 'y' and 1 on 'z', 4 in all.
3078 --replace_column 10 # 11 #
3079 explain select * from t1 x, t1 y, t1 z where x.b=y.a and y.c=4 and y.b=z.a;
3080
3081 select * from t1 x, t1 y, t1 z where x.b=y.a and y.c=4 and y.b=z.a;
3082
3083 --disable_query_log
3084 --eval select sum(val) - $spj_lookups as lookups from ndbinfo.counters where block_name='DBSPJ' and (counter_name='LOCAL_READS_SENT' or counter_name='REMOTE_READS_SENT')
3085 --enable_query_log
3086
3087 connection ddl;
3088 drop table t1;
3089
3090 # Test and server status variables (i.e. mysqld counters)
3091
3092 connection ddl;
3093 create table t1(
3094 a int not null,
3095 b int not null,
3096 c int not null,
3097 primary key(a,b))
3098 engine = ndb
3099 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
3100 partition by key (a);
3101
3102 connection spj;
3103 insert into t1 values (10, 10, 11);
3104 insert into t1 values (11, 11, 12);
3105 insert into t1 values (12, 12, 13);
3106
3107 # First query against a new table causes an extra scan (of a dictionaty table??)
3108 # so adding an extra scan here to make results from the following part easier
3109 # to interpret.
3110 select * from t1 t1, t1 t2
3111 where t1.a = 10 and t1.b = 10 and
3112 t2.a = t1.c and t2.b = t1.c;
3113
3114 # Save old counter values.
3115 # Save old mysqld counter values.
3116 create temporary table server_counts
3117 select * from performance_schema.global_status
3118 where variable_name in
3119 ('Ndb_scan_count',
3120 'Ndb_pruned_scan_count',
3121 'Ndb_sorted_scan_count',
3122 'Ndb_pushed_queries_defined',
3123 'Ndb_pushed_queries_dropped',
3124 'Ndb_pushed_reads');
3125
3126 # Run some queries that should increment the counters.
3127 # This query should push a single read.
3128 select * from t1 t1, t1 t2
3129 where t1.a = 11 and t1.b = 11 and
3130 t2.a = t1.c and t2.b = t1.c;
3131
3132 # This query should push a sorted scan (and three reads).
3133 select * from t1 t1, t1 t2
3134 where t2.a = t1.c and t2.b = t1.c
3135 order by t1.a;
3136
3137 # This query should push a pruned scan (but pruning must be fixed for
3138 # pushed scans.)
3139 select count(*) from t1 t1, t1 t2
3140 where t1.a = 11 and
3141 t2.a = t1.c and t2.b = t1.c;
3142
3143 # Calculate the change in mysqld counters.
3144 select new.variable_name, new.variable_value - old.variable_value
3145 from server_counts as old,
3146 performance_schema.global_status as new
3147 where new.variable_name = old.variable_name
3148 order by new.variable_name;
3149
3150 drop table server_counts;
3151
3152 connection ddl;
3153 drop table t1;
3154
3155 # Test scan pruning
3156 connection ddl;
3157 create table t1(
3158 d int not null,
3159 c int not null,
3160 a int not null,
3161 b int not null,
3162 primary key using hash (a,b))
3163 engine = ndb
3164 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
3165 partition by key (a);
3166
3167 connection spj;
3168 insert into t1(a,b,c,d) values (10, 10, 11, 11);
3169 insert into t1(a,b,c,d) values (11, 11, 12, 12);
3170 insert into t1(a,b,c,d) values (12, 12, 13, 13);
3171
3172 let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3173
3174 # Should give pruned scan.
3175
3176 connection ddl;
3177 create index i1 on t1(c,a);
3178
3179 connection spj;
3180
3181 select count(*) from t1 t1, t1 t2 where t1.c = 12 and t1.a = 11 and t2.a = t1.d and t2.b = t1.d;
3182
3183 connection ddl;
3184 drop index i1 on t1;
3185
3186 connection spj;
3187 --disable_query_log
3188 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3189 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
3190 let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3191 --enable_query_log
3192
3193 # Should give pruned scan. There is a one sided limit for t1.b, but this is
3194 # after the partition key prefix.
3195
3196 connection ddl;
3197 create index i2 on t1(a,b);
3198
3199 connection spj;
3200 select count(*) from t1 t1, t1 t2 where t1.a = 11 and t1.b<13 and t2.a = t1.c and t2.b = t1.c;
3201
3202 --disable_query_log
3203 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3204 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
3205 let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3206 --enable_query_log
3207
3208 # Should give pruned scan. Upper and lower bounds for t1.a are the sane.
3209 select count(*) from t1 t1, t1 t2 where t1.a >= 12 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c;
3210
3211 --disable_query_log
3212 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3213 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
3214 let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3215 --enable_query_log
3216
3217 # Should not give pruned scan. Upper and lower bounds for t1.a are different.
3218 select count(*) from t1 t1, t1 t2 where t1.a >= 11 and t1.a<=12 and t2.a = t1.c and t2.b = t1.c;
3219
3220 --disable_query_log
3221 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3222 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
3223 let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3224 --enable_query_log
3225
3226 # Should not give pruned scan. There will be two sets of bounds that have
3227 # different distribution keys (t1.a=10 and t1.a=12).
3228 select count(*) from t1 t1, t1 t2 where (t1.a = 10 or t1.a=12) and t1.b<13 and t2.a = t1.c and t2.b = t1.c;
3229
3230 --disable_query_log
3231 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3232 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
3233 let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3234 --enable_query_log
3235
3236 # Should give pruned scan. There will be two sets of bounds, but they have the
3237 # same distribution key.
3238
3239 select count(*) from t1 t1, t1 t2 where t1.a = 10 and (t1.b<11 or t1.b>11) and t2.a = t1.c and t2.b = t1.c;
3240
3241 --disable_query_log
3242 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3243 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
3244 let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3245 --enable_query_log
3246
3247 connection ddl;
3248 drop table t1;
3249
3250 create table t2(
3251 d int not null,
3252 e int not null,
3253 f int not null,
3254 a int not null,
3255 b int not null,
3256 c int not null,
3257 primary key using hash (a,b,c))
3258 engine = ndb
3259 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
3260 partition by key (b,a);
3261
3262 connection spj;
3263 insert into t2(a,b,c,d,e,f) values (1, 2, 3, 1, 2, 3);
3264 insert into t2(a,b,c,d,e,f) values (1, 2, 4, 1, 2, 3);
3265 insert into t2(a,b,c,d,e,f) values (2, 3, 4, 1, 2, 3);
3266 insert into t2(a,b,c,d,e,f) values (3, 4, 5, 1, 2, 3);
3267 insert into t2(a,b,c,d,e,f) values (4, 5, 6, 1, 2, 3);
3268 insert into t2(a,b,c,d,e,f) values (5, 6, 7, 1, 2, 3);
3269 insert into t2(a,b,c,d,e,f) values (6, 7, 8, 1, 2, 3);
3270 insert into t2(a,b,c,d,e,f) values (7, 8, 9, 1, 2, 3);
3271
3272 connection ddl;
3273 create index i2_1 on t2(d, a, b, e);
3274
3275 connection spj;
3276
3277 # Should give pruned scan. The index prefix containing the distribution key
3278 # has a single possible value.
3279 select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
3280
3281 --disable_query_log
3282 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3283 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
3284 let $old_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3285 --enable_query_log
3286
3287 connection ddl;
3288 drop index i2_1 on t2;
3289 create index i2_3 on t2(a, d, b, e);
3290
3291 connection spj;
3292
3293 # Should give pruned scan. The index prefix containing the distribution key
3294 # has a single possible value.
3295
3296 # Turn off condition_fanout_filter to keep "old" QEP
3297 set optimizer_switch='condition_fanout_filter=off';
3298 select count(*) from t2 x, t2 y where x.d=1 and x.a=1 and x.b=2 and y.a=x.d and y.b=x.e and y.c=3;
3299
3300 # Restore condition_fanout_filter to default value
3301 set optimizer_switch='condition_fanout_filter=default';
3302
3303 --disable_query_log
3304 let $new_pruned_scan_count = query_get_value(show status like 'Ndb_pruned_scan_count', Value, 1);
3305 --eval select $new_pruned_scan_count - $old_pruned_scan_count as pruned_scan_count
3306 --enable_query_log
3307
3308 connection ddl;
3309 drop table t2;
3310
3311 connection ddl;
3312 create table t1 (a binary(10) primary key, b binary(10) not null) engine = ndb
3313 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
3314
3315 connection spj;
3316 insert into t1 values ('\0123456789', '1234567890');
3317 insert into t1 values ('1234567890', '\0123456789');
3318
3319 explain
3320 select count(*)
3321 from t1 join t1 as t2 on t2.a = t1.b
3322 where t1.a = '\0123456789';
3323 select count(*)
3324 from t1 join t1 as t2 on t2.a = t1.b
3325 where t1.a = '\0123456789';
3326
3327 connection ddl;
3328 drop table t1;
3329
3330
3331 # Tests for some bugfixes which have been cherry picked from 5.1 main
3332 # Not necessarily test of SPJ functionality, but may test optimizer
3333 # behaviour which we depend on when doing RQG testing
3334 # We can remove these testcases when fixes - and propper MTR testcases -
3335 # Have been merged from 5.1 main branch.
3336
3337 connection ddl;
3338 create table t1 (pk int primary key, a int unique key) engine = ndb
3339 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
3340
3341 connection spj;
3342 insert into t1 values (1,10), (2,20), (3,30);
3343
3344 set ndb_join_pushdown = false;
3345
3346 # Bug#53334:
3347 # Join should be optimized as 'Impossible On condition'
3348 # ... *not* 'Impossible WHERE'
3349
3350 explain
3351 select * from t1 as x right join t1 as y
3352 on x.pk = y.pk
3353 and x.pk = y.a
3354 and x.a = y.pk
3355 where y.pk = 2;
3356 select * from t1 as x right join t1 as y
3357 on x.pk = y.pk
3358 and x.pk = y.a
3359 and x.a = y.pk
3360 where y.pk = 2;
3361
3362
3363 set ndb_join_pushdown = true;
3364
3365 explain
3366 select * from t1 as x right join t1 as y
3367 on x.pk = y.pk
3368 and x.pk = y.a
3369 and x.a = y.pk
3370 where y.pk = 2;
3371 select * from t1 as x right join t1 as y
3372 on x.pk = y.pk
3373 and x.pk = y.a
3374 and x.a = y.pk
3375 where y.pk = 2;
3376
3377 connection ddl;
3378 drop table t1;
3379
3380 #########################################
3381 # Test section for scan-child operations
3382 #########################################
3383
3384 # Test scan-lookup-scan query (see http://lists.mysql.com/commits/115164)
3385
3386 connection ddl;
3387 create table t1 (pk int primary key, u int not null, a int, b int) engine=ndb
3388 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
3389 create index ix1 on t1(b,a);
3390
3391 connection spj;
3392 insert into t1 values (0,1,10,20);
3393 insert into t1 values (1,2,20,30);
3394 insert into t1 values (2,3,30,40);
3395
3396 --replace_column 10 # 11 #
3397 explain select * from t1 as x join t1 as y join t1 as z on x.u=y.pk and y.a=z.b;
3398 --sorted_result
3399 select * from t1 as x join t1 as y join t1 as z on x.u=y.pk and y.a=z.b;
3400
3401 connection ddl;
3402 drop table t1;
3403
3404 # Test sorted scan where inner join eliminates all rows (known regression).
3405 connection ddl;
3406 create table t1 (pk int primary key, u int not null) engine=ndb
3407 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
3408
3409 connection spj;
3410 insert into t1 values (0,-1), (1,-1), (2,-1), (3,-1), (4,-1), (5,-1), (6,-1),
3411 (7,-1), (8,-1), (9,-1), (10,-1), (11,-1), (12,-1), (13,-1), (14,-1), (15,-1),
3412 (16,-1), (17,-1), (18,-1), (19,-1), (20,-1), (21,-1), (22,-1), (23,-1),
3413 (24,-1), (25,-1), (26,-1), (27,-1), (28,-1), (29,-1), (30,-1), (31,-1),
3414 (32,-1), (33,-1), (34,-1), (35,-1), (36,-1), (37,-1), (38,-1), (39,-1),
3415 (40,-1), (41,-1), (42,-1), (43,-1), (44,-1), (45,-1), (46,-1), (47,-1),
3416 (48,-1), (49,-1), (50,-1), (51,-1), (52,-1), (53,-1), (54,-1), (55,-1),
3417 (56,-1), (57,-1), (58,-1), (59,-1), (60,-1), (61,-1), (62,-1), (63,-1),
3418 (64,-1), (65,-1), (66,-1), (67,-1), (68,-1), (69,-1), (70,-1), (71,-1),
3419 (72,-1), (73,-1), (74,-1), (75,-1), (76,-1), (77,-1), (78,-1), (79,-1),
3420 (80,-1), (81,-1), (82,-1), (83,-1), (84,-1), (85,-1), (86,-1), (87,-1),
3421 (88,-1), (89,-1), (90,-1), (91,-1), (92,-1), (93,-1), (94,-1), (95,-1),
3422 (96,-1), (97,-1), (98,-1), (99,-1), (100,-1), (101,-1), (102,-1), (103,-1),
3423 (104,-1), (105,-1), (106,-1), (107,-1), (108,-1), (109,-1), (110,-1),
3424 (111,-1), (112,-1), (113,-1), (114,-1), (115,-1), (116,-1), (117,-1),
3425 (118,-1), (119,-1), (120,-1), (121,-1), (122,-1), (123,-1), (124,-1),
3426 (125,-1), (126,-1), (127,-1), (128,-1), (129,-1), (130,-1), (131,-1),
3427 (132,-1), (133,-1), (134,-1), (135,-1), (136,-1), (137,-1), (138,-1), (139,-1);
3428
3429 --error 0,1193
3430 set global debug='+d,max_64rows_in_spj_batches';
3431
3432 explain select * from t1 as x join t1 as y on x.u=y.pk order by(x.pk);
3433 select * from t1 as x join t1 as y on x.u=y.pk order by(x.pk);
3434
3435 --error 0,1193
3436 set global debug=@save_debug;
3437
3438 connection ddl;
3439 drop table t1;
3440
3441 # Test query using "scan -> unique index lookup -> index scan".
3442
3443 connection ddl;
3444 create table t1 (pk int primary key, u int not null, a int, b int) engine=ndb
3445 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
3446 create index ix1 on t1(b,a);
3447 create unique index ix2 on t1(u);
3448
3449 connection spj;
3450 insert into t1 values (0,0,10,10);
3451 insert into t1 values (1,1,10,10);
3452 insert into t1 values (2,2,10,10);
3453 insert into t1 values (3,3,10,10);
3454 insert into t1 values (4,4,10,10);
3455 insert into t1 values (5,5,10,10);
3456 insert into t1 values (6,6,10,10);
3457 insert into t1 values (7,7,10,10);
3458 insert into t1 values (8,8,10,10);
3459 insert into t1 values (9,9,10,10);
3460 insert into t1 values (10,10,10,10);
3461 insert into t1 values (11,11,10,10);
3462
3463
3464 explain select count(*) from t1 as x1 join t1 as x2 join t1 as x3
3465 on x1.a=x2.u and x2.a = x3.b;
3466
3467 select count(*) from t1 as x1 join t1 as x2 join t1 as x3
3468 on x1.a=x2.u and x2.a = x3.b;
3469
3470 explain select count(*) from t1 as x1, t1 as x2, t1 as x3
3471 where x1.u=x2.pk and x1.a=x3.b;
3472
3473 select count(*) from t1 as x1, t1 as x2, t1 as x3
3474 where x1.u=x2.pk and x1.a=x3.b;
3475
3476 # Regression test for commit http://lists.mysql.com/commits/116372
3477 # (missing rows in left join query with multiple result batches).
3478
3479 insert into t1 values (12,12,20,10);
3480
3481 explain select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b;
3482 select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b;
3483 set ndb_join_pushdown=off;
3484 select count(*) from t1 as x1 left join t1 as x2 on x1.a=x2.b;
3485 set ndb_join_pushdown=on;
3486
3487 # Test left join with mix of scan and lookup.
3488 explain select count(*) from t1 as x1
3489 left join t1 as x2 on x1.u=x2.pk
3490 left join t1 as x3 on x2.a=x3.b;
3491
3492 select count(*) from t1 as x1
3493 left join t1 as x2 on x1.u=x2.pk
3494 left join t1 as x3 on x2.a=x3.b;
3495 set ndb_join_pushdown=off;
3496 select count(*) from t1 as x1
3497 left join t1 as x2 on x1.u=x2.pk
3498 left join t1 as x3 on x2.a=x3.b;
3499 set ndb_join_pushdown=on;
3500
3501 explain select count(*) from t1 as x1
3502 left join t1 as x2 on x1.u=x2.pk
3503 left join t1 as x3 on x2.a=x3.b
3504 left join t1 as x4 on x3.u=x4.pk
3505 left join t1 as x5 on x4.a=x5.b;
3506
3507 select count(*) from t1 as x1
3508 left join t1 as x2 on x1.u=x2.pk
3509 left join t1 as x3 on x2.a=x3.b
3510 left join t1 as x4 on x3.u=x4.pk
3511 left join t1 as x5 on x4.a=x5.b;
3512 set ndb_join_pushdown=off;
3513 select count(*) from t1 as x1
3514 left join t1 as x2 on x1.u=x2.pk
3515 left join t1 as x3 on x2.a=x3.b
3516 left join t1 as x4 on x3.u=x4.pk
3517 left join t1 as x5 on x4.a=x5.b;
3518 set ndb_join_pushdown=on;
3519
3520
3521 ############################
3522 # Testcase for 'Got error 20002 'Unknown error code' from NDBCLUSTER'
3523 # Caused by failure to identify AT_MULTI_PRIMARY_KEY as a lookup operation.
3524 # This in turn caused a pushed 'lookup-scan' query to be produced - which we don't support
3525
3526 # Should not be pushed (lookup-scan query)
3527 explain select count(*) from t1 as x1
3528 join t1 as x2 on x1.a=x2.b
3529 where x1.pk = 1 or x1.u=1;
3530
3531 select count(*) from t1 as x1
3532 join t1 as x2 on x1.a=x2.b
3533 where x1.pk = 1 or x1.u=1;
3534
3535 ############################
3536 # Testcase which forced us to ditch using the 'global cursor'
3537 # on the NdbQuery result set from mysqld.
3538 #
3539 # As the global cursor will fool mysqld into handling the resultset
3540 # as a result from a scan - n*lookup query, incorrect cardinality on the
3541 # parent operation was perceived. Which caused extra null-joined outer rows
3542 # to be emitted in this testcase.
3543 #
3544 # Refactored handler interface and SPJ API use subcursor on each operation
3545 # which correctly preserves the dependency between the parent subscans
3546 # and its child(s).
3547
3548 --error 0,1193
3549 set global debug='+d,max_4rows_in_spj_batches';
3550
3551 set ndb_join_pushdown=on;
3552 explain
3553 select straight_join * from t1 as table1
3554 left join
3555 (t1 as table2 join t1 as table3 on table2.pk = table3.b)
3556 on table1.pk = table2.b;
3557 --sorted_result
3558 select straight_join * from t1 as table1
3559 left join
3560 (t1 as table2 join t1 as table3 on table2.pk = table3.b)
3561 on table1.pk = table2.b;
3562
3563 --error 0,1193
3564 set global debug=@save_debug;
3565
3566 #############
3567 # Testcase for 'sledgehammer' fix for scan -> outer join scan:
3568 # Pushing of outer joined has to be dissabled as incomplete child batches
3569 # may cause the parent row to be returned multiple times:
3570
3571 # Push scan-scan when inner joined
3572 explain select straight_join * from t1 as x1
3573 inner join t1 as x2 on x2.b = x1.a;
3574
3575 # Outer joined scans are not pushed.
3576 explain select straight_join * from t1 as x1
3577 left join t1 as x2 on x2.b = x1.a;
3578 explain select straight_join * from t1 as x1
3579 right join t1 as x2 on x2.b = x1.a;
3580
3581 # If there is a lookup operation(s) inbetween the scans
3582 # pushing is disabled if any of these are outer joined
3583
3584 # inner joined lookups, push allowed
3585 explain select straight_join * from
3586 t1 as x1 inner join
3587 (t1 as x2 inner join t1 as x3 on x3.b = x2.a)
3588 on x2.pk = x1.a;
3589
3590 # Even if x3 is inner joined with x2 (lookup)
3591 # push is dissabled as x2 is outer joined with embedding scan operation
3592 # which makes join relation between the scans on x1 & x3 an 'indirect' outer join
3593 explain select straight_join * from
3594 t1 as x1 left join
3595 (t1 as x2 inner join t1 as x3 on x3.b = x2.a)
3596 on x2.pk = x1.a;
3597
3598 #############
3599 # Test bushy-scans:
3600 # These should be allowed to be executed in 'parallel', depending on
3601 # only the root operation
3602 #
3603
3604 explain select straight_join count(*) from t1 as x1
3605 join t1 as x2 on x2.b = x1.a
3606 join t1 as x3 on x3.b = x1.b;
3607 set ndb_join_pushdown=off;
3608 select straight_join count(*) from t1 as x1
3609 join t1 as x2 on x2.b = x1.a
3610 join t1 as x3 on x3.b = x1.b;
3611 set ndb_join_pushdown=on;
3612 select straight_join count(*) from t1 as x1
3613 join t1 as x2 on x2.b = x1.a
3614 join t1 as x3 on x3.b = x1.b;
3615
3616 # A really bushy scan - would take almost forever to execute if
3617 # we had to force the child scan to be non-bushy (serialized by
3618 # adding artificial parent dependencies)
3619 #
3620 --replace_column 10 # 11 #
3621 explain select straight_join count(*) from t1 as x1
3622 join t1 as x2 on x2.b = x1.a
3623 join t1 as x3 on x3.b = x1.a
3624 join t1 as x4 on x4.b = x1.a
3625 join t1 as x5 on x5.b = x1.a
3626 join t1 as x6 on x6.b = x1.a
3627 join t1 as x7 on x7.b = x1.a
3628 where x3.a < x2.pk and x4.a < x3.pk;
3629
3630 # set '64rows' in order to avoid to small batches which will
3631 # cause all subscans to be repeated... and repeated... and
3632
3633 --error 0,1193
3634 set global debug='+d,max_64rows_in_spj_batches';
3635
3636 select straight_join count(*) from t1 as x1
3637 join t1 as x2 on x2.b = x1.a
3638 join t1 as x3 on x3.b = x1.a
3639 join t1 as x4 on x4.b = x1.a
3640 join t1 as x5 on x5.b = x1.a
3641 join t1 as x6 on x6.b = x1.a
3642 join t1 as x7 on x7.b = x1.a
3643 where x3.a < x2.pk and x4.a < x3.pk;
3644
3645 --error 0,1193
3646 set global debug=@save_debug;
3647
3648 #############
3649 # If we have an outer join, we can't create an artificial dep. 'through' the outer join.
3650 # In this case the child scan can't be part of the pushed query.
3651 #
3652 explain select straight_join count(*) from t1 as x1
3653 left join t1 as x2 on x2.b = x1.a
3654 join t1 as x3 on x3.b = x1.b;
3655
3656 set ndb_join_pushdown=off;
3657 select straight_join count(*) from t1 as x1
3658 left join t1 as x2 on x2.b = x1.a
3659 join t1 as x3 on x3.b = x1.b;
3660 set ndb_join_pushdown=on;
3661 select straight_join count(*) from t1 as x1
3662 left join t1 as x2 on x2.b = x1.a
3663 join t1 as x3 on x3.b = x1.b;
3664
3665 ############
3666 # However, When the scanchild itself is an outer join, we *can* push that scan operation
3667 #
3668 explain select straight_join count(*) from t1 as x1
3669 join t1 as x2 on x2.b = x1.a
3670 left join t1 as x3 on x3.b = x1.b;
3671
3672 set ndb_join_pushdown=off;
3673 select straight_join count(*) from t1 as x1
3674 join t1 as x2 on x2.b = x1.a
3675 left join t1 as x3 on x3.b = x1.b;
3676 set ndb_join_pushdown=on;
3677 select straight_join count(*) from t1 as x1
3678 join t1 as x2 on x2.b = x1.a
3679 left join t1 as x3 on x3.b = x1.b;
3680
3681 ##############
3682 # If we have a bushy lookup, with scandescendants depending on these lookups,
3683 # the query is 'scan-bushy' through these lookups.
3684 #
3685 # Bushy execution is expected for these scans (x2 & x4) wrt. root (x1)
3686 #
3687 explain
3688 select straight_join count(*) from t1 as x1
3689 join t1 as x2 on x2.b = x1.a
3690 join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
3691
3692 set ndb_join_pushdown=off;
3693 select straight_join count(*) from t1 as x1
3694 join t1 as x2 on x2.b = x1.a
3695 join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
3696 set ndb_join_pushdown=on;
3697 select straight_join count(*) from t1 as x1
3698 join t1 as x2 on x2.b = x1.a
3699 join t1 as x3 on x3.pk = x1.a join t1 as x4 on x4.b = x3.a;
3700
3701 #############
3702 # Test bushy lookups + 1scan,
3703 # (Regression test for previous commit: http://lists.mysql.com/commits/117571)
3704 # Repeatable child rangescan with same parent should be allowed to be in
3705 # 'm_iterState != Iter_finished' if the child row didn't exist (outer join):
3706
3707 explain select straight_join count(*) from t1 as x1
3708 left join t1 as x3 on x3.b = x1.a
3709 join t1 as x2 on x2.pk = x1.a;
3710 select straight_join count(*) from t1 as x1
3711 left join t1 as x3 on x3.b = x1.a
3712 join t1 as x2 on x2.pk = x1.a;
3713
3714 # Modify rows to force null rows from outer join
3715 update t1 set b=b+10;
3716
3717 select straight_join count(*) from t1 as x1
3718 left join t1 as x3 on x3.b = x1.a
3719 join t1 as x2 on x2.pk = x1.a;
3720
3721 #Undo update
3722 update t1 set b=b-10;
3723
3724 ##############
3725 # Testcase for: http://lists.mysql.com/commits/118917
3726 # There used to be a bug in SPJ API resulthandling of incomplete
3727 # child batches where we tested for incomplete fetch for any
3728 # childs (in a bushy scan) instead if this particular child batch
3729 # being incomplete.
3730 #
3731 # In this testcase (x inner join y) will have incomplete
3732 # childbatches, while (x left join z) will be complete.
3733
3734 # Modify rows to force null rows from lookup(z) below
3735 update t1 set u=u+100;
3736
3737 set ndb_join_pushdown=on;
3738
3739 --error 0,1193
3740 set global debug='+d,max_64rows_in_spj_batches';
3741
3742 explain select straight_join count(*) from
3743 (t1 as x join t1 as y on y.b = x.a)
3744 left outer join t1 as z on z.u = x.a;
3745 select straight_join count(*) from
3746 (t1 as x join t1 as y on y.b = x.a)
3747 left outer join t1 as z on z.u = x.a;
3748
3749 --error 0,1193
3750 set global debug=@save_debug;
3751
3752 #Undo update
3753 update t1 set u=u-100;
3754
3755 #############
3756 # Additional testcase added as part of WL introducing equi-join
3757 # non-matching row elimination optimizations into the SPJ block
3758 # Test query topology which lacked coverage. (Found to fail
3759 # in manual or RQG testing)
3760
3761 # Modify rows to force null rows from outer joined lookup below
3762 update t1 set a=a+pk;
3763
3764 set ndb_join_pushdown=on;
3765 explain
3766 select straight_join count(*) from t1 as x
3767 left join t1 as y on y.pk = x.a
3768 join t1 as z on z.b = x.b;
3769 --sorted_result
3770 select straight_join count(*) from t1 as x
3771 left join t1 as y on y.pk = x.a
3772 join t1 as z on z.b = x.b;
3773
3774 explain
3775 select straight_join count(*) from t1 as x
3776 left join t1 as y1 on y1.pk = x.a
3777 left join t1 as y2 on y2.pk = x.a
3778 join t1 as z on z.b = x.b;
3779 --sorted_result
3780 select straight_join count(*) from t1 as x
3781 left join t1 as y1 on y1.pk = x.a
3782 left join t1 as y2 on y2.pk = x.a
3783 join t1 as z on z.b = x.b;
3784
3785 explain
3786 select straight_join count(*) from t1 as x
3787 join t1 as y1 on y1.pk = x.a
3788 left join t1 as y2 on y2.pk = x.a
3789 join t1 as z on z.b = x.b;
3790 --sorted_result
3791 select straight_join count(*) from t1 as x
3792 join t1 as y1 on y1.pk = x.a
3793 left join t1 as y2 on y2.pk = x.a
3794 join t1 as z on z.b = x.b;
3795
3796 explain
3797 select straight_join count(*) from t1 as x
3798 left join t1 as y1 on y1.pk = x.a
3799 join t1 as y2 on y2.pk = x.a
3800 join t1 as z on z.b = x.b;
3801 --sorted_result
3802 select straight_join count(*) from t1 as x
3803 left join t1 as y1 on y1.pk = x.a
3804 join t1 as y2 on y2.pk = x.a
3805 join t1 as z on z.b = x.b;
3806
3807 explain
3808 select straight_join count(*) from t1 as x
3809 left join (t1 as y join t1 as yy on yy.pk=y.u) on y.pk = x.a
3810 join t1 as z on z.b = x.b;
3811 --sorted_result
3812 select straight_join count(*) from t1 as x
3813 left join (t1 as y join t1 as yy on yy.pk=y.u) on y.pk = x.a
3814 join t1 as z on z.b = x.b;
3815
3816 explain
3817 select straight_join count(*) from t1 as x
3818 join t1 as z on z.b = x.b
3819 left join t1 as y on y.pk = x.a;
3820 --sorted_result
3821 select straight_join count(*) from t1 as x
3822 join t1 as z on z.b = x.b
3823 left join t1 as y on y.pk = x.a;
3824
3825 explain
3826 select straight_join count(*) from t1 as x
3827 left join t1 as y1 on y1.pk = x.a
3828 left join t1 as y2 on y2.pk = x.a
3829 left join t1 as y3 on y3.pk = x.a;
3830 --sorted_result
3831 select straight_join count(*) from t1 as x
3832 left join t1 as y1 on y1.pk = x.a
3833 left join t1 as y2 on y2.pk = x.a
3834 left join t1 as y3 on y3.pk = x.a;
3835
3836 explain
3837 select straight_join count(*) from t1 as x
3838 left join t1 as y on y.pk = x.a
3839 left join t1 as z on z.pk = x.a
3840 left join t1 as y1 on y1.pk = y.b;
3841 --sorted_result
3842 select straight_join count(*) from t1 as x
3843 left join t1 as y on y.pk = x.a
3844 left join t1 as z on z.pk = x.a
3845 left join t1 as y1 on y1.pk = y.b;
3846
3847 explain
3848 select straight_join count(*) from t1 as x
3849 join t1 as y on y.pk = x.a
3850 left join t1 as y1 on y1.pk = y.b
3851 left join t1 as z on z.pk = x.b;
3852 --sorted_result
3853 select straight_join count(*) from t1 as x
3854 join t1 as y on y.pk = x.a
3855 left join t1 as y1 on y1.pk = y.b
3856 left join t1 as z on z.pk = x.b;
3857
3858 explain
3859 select straight_join count(*) from t1 as x
3860 left join t1 as y1 on y1.pk = x.a
3861 join t1 as y2 on y2.pk = x.b
3862 join t1 as z on z.b = y2.b;
3863 --sorted_result
3864 select straight_join count(*) from t1 as x
3865 left join t1 as y1 on y1.pk = x.a
3866 join t1 as y2 on y2.pk = x.b
3867 join t1 as z on z.b = y2.b;
3868
3869 #undo update
3870 update t1 set a=a-pk;
3871
3872 ##############
3873
3874 connection ddl;
3875 drop index ix2 on t1;
3876 create unique index ix2 on t1(a,u);
3877
3878 connection spj;
3879 set ndb_join_pushdown=on;
3880 explain
3881 select straight_join * from
3882 t1 as table1 join
3883 (t1 as table2 join t1 as table3 on table3.a = table2.a)
3884 on table3.u = table1.u
3885 where table2.pk = 3;
3886
3887 --sorted_result
3888 select straight_join * from
3889 t1 as table1 join
3890 (t1 as table2 join t1 as table3 on table3.a = table2.a)
3891 on table3.u = table1.u
3892 where table2.pk = 3;
3893
3894
3895 ##############
3896 connection ddl;
3897 drop table t1;
3898
3899 ##############
3900 # Test that branches of a bushy scan are correctly reset.
3901
3902 connection ddl;
3903 CREATE TABLE t1 (
3904 a int NOT NULL,
3905 b int NOT NULL,
3906 c int NOT NULL,
3907 d int NOT NULL,
3908 PRIMARY KEY (`a`,`b`)
3909 ) ENGINE=ndbcluster
3910 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
3911
3912 connection spj;
3913 insert into t1 values (1,1,1,1), (1,2,1,1), (1,3,1,1), (1,4,1,2);
3914
3915 connection ddl;
3916 CREATE TABLE t2 (
3917 a int NOT NULL,
3918 PRIMARY KEY (`a`)
3919 ) ENGINE=ndbcluster
3920 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
3921
3922 CREATE TABLE t3 (
3923 a int NOT NULL,
3924 b int NOT NULL,
3925 PRIMARY KEY (`a`,`b`)
3926 ) ENGINE=ndbcluster
3927 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
3928
3929 connection ddl;
3930 insert into t2 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
3931
3932 connection spj;
3933 # Make t3 so big that it takes multiple batches to scan it.
3934 insert into t3 select 1, x1.a * 10+x2.a from t2 as x1 cross join t2 as x2;
3935
3936 --error 0,1193
3937 set global debug='+d,max_64rows_in_spj_batches';
3938
3939 explain select straight_join count(*) from t1 as x0
3940 join t3 as x1 on x1.a=x0.c
3941 join t1 as x2 on x2.a=x0.d
3942 join t3 as x3 on x3.a=x2.c
3943 join t1 as x4 on x4.a=x0.d and x4.b=x3.b;
3944
3945 select straight_join count(*) from t1 as x0
3946 join t3 as x1 on x1.a=x0.c
3947 join t1 as x2 on x2.a=x0.d
3948 join t3 as x3 on x3.a=x2.c
3949 join t1 as x4 on x4.a=x0.d and x4.b=x3.b;
3950
3951 # If the first batch of an index scan has low parallelism and returns few rows,
3952 # there is a mechanism that will try to query the remaining fragments within
3953 # the same batch. This is done in order to avoid repeating other branches of
3954 # a bushy scan whenever possible. This is a test of that mechanism. Scan
3955 # of x2 should return only one row. Therefore we should be able to fetch
3956 # x2 in one batch and scan x3 only once.
3957
3958 let $scan_rows = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name='LOCAL_RANGE_SCANS_SENT', Value, 1);
3959
3960 --replace_column 10 # 11 #
3961 explain select straight_join count(*) from t1 as x1
3962 join t1 as x2 on x1.c=x2.a and x2.d=2
3963 join t3 as x3 on x1.d=x3.a;
3964
3965 select straight_join count(*) from t1 as x1
3966 join t1 as x2 on x1.c=x2.a and x2.d=2
3967 join t3 as x3 on x1.d=x3.a;
3968
3969 #####
3970 # Bug #18175080
3971 # INCORRECT 'LOCAL_RANGE_SCAN' REPORTED FROM NDB_JOIN_PUSHDOWN_*.TEST
3972 #
3973 # Reported local_range_scan was not deterministic, so had
3974 # to disable this subtest.
3975 #
3976 #--disable_query_log
3977 #--eval select sum(val) - $scan_rows as Local_range_scans from ndbinfo.counters where block_name='DBSPJ' and counter_name='LOCAL_RANGE_SCANS_SENT';
3978 #--enable_query_log
3979
3980 --error 0,1193
3981 set global debug=@save_debug;
3982
3983 connection ddl;
3984 drop table t1;
3985 drop table t2;
3986 drop table t3;
3987
3988 #############################################
3989 # Test pruned index scan:
3990 connection ddl;
3991 create table t1(
3992 d int not null,
3993 e int null,
3994 f int null,
3995 a int not null,
3996 b int not null,
3997 c int not null,
3998 primary key (a,b,c))
3999 engine = ndb partition by key (b) partitions 8;
4000
4001 connection spj;
4002 insert into t1(a,b,c,d,e,f) values
4003 (1, 2, 3, 1, 2, 3),
4004 (1, 2, 4, 1, 2, 3),
4005 (2, 3, 4, 1, 2, 3),
4006 (3, 4, 5, 1, 2, 3),
4007 (4, 5, 6, 1, 2, 3),
4008 (5, 6, 7, 1, 2, 3),
4009 (6, 7, 8, 1, 2, 3),
4010 (7, 8, 9, 1, 2, 3);
4011
4012 # Find the total number of pruned range scans so far
4013 let $pruned_range = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name='PRUNED_RANGE_SCANS_RECEIVED', Value, 1);
4014
4015 let $const_pruned_range = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name='CONST_PRUNED_RANGE_SCANS_RECEIVED', Value, 1);
4016
4017
4018 set ndb_join_pushdown=on;
4019
4020 --replace_column 10 # 11 #
4021 explain
4022 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
4023
4024 --sorted_result
4025 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
4026
4027 connection ddl;
4028 alter table t1 partition by key (a) partitions 8;
4029
4030 connection spj;
4031 --sorted_result
4032 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
4033
4034 connection ddl;
4035 alter table t1 partition by key (a,b) partitions 8;
4036
4037 connection spj;
4038 --sorted_result
4039 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
4040
4041 connection ddl;
4042 alter table t1 partition by key (b,a) partitions 8;
4043
4044 connection spj;
4045 --sorted_result
4046 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
4047
4048 #########
4049 # const pruned testcase
4050 #########
4051 connection ddl;
4052 alter table t1 partition by key (b) partitions 8;
4053
4054 connection spj;
4055 --sorted_result
4056 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=2;
4057
4058 connection ddl;
4059 alter table t1 partition by key (a) partitions 8;
4060
4061 connection spj;
4062 --sorted_result
4063 select straight_join * from t1 x, t1 y where y.a=1 and y.b=x.e;
4064 select straight_join * from t1 x, t1 y where y.a=0 and y.b=x.e;
4065
4066 # Non-const pruned as both partition keys are not const
4067 connection ddl;
4068 alter table t1 partition by key (a,b) partitions 8;
4069
4070 connection spj;
4071 --sorted_result
4072 select straight_join * from t1 x, t1 y where y.a=1 and y.b=x.e;
4073 --sorted_result
4074 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=2;
4075
4076 ##########
4077 # Test pruned scan using an index:
4078 # Declaring PK as 'using hash' will prevent that PK is used as index
4079 # Declare PK / ix1 with mismatching column order will test correct
4080 # usage of NdbRecord::distkey_indexes[]
4081 ##########
4082 connection ddl;
4083 alter table t1 drop primary key, add primary key using hash (d,b,a,c);
4084 alter table t1 partition by key (b) partitions 8;
4085 create index ix1 on t1(b,d,a);
4086
4087 connection spj;
4088 --replace_column 10 # 11 #
4089 explain
4090 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
4091
4092 ###########
4093 # Partition keys may evaluate to null-values:
4094 ###########
4095 insert into t1(a,b,c,d,e,f) values
4096 (8, 9, 0, 1, null, 3),
4097 (9, 9, 0, 1, 2, null);
4098
4099 connection ddl;
4100 alter table t1 partition by key (b) partitions 8;
4101
4102 connection spj;
4103 --sorted_result
4104 select straight_join * from t1 x, t1 y where y.a=x.d and y.b=x.e;
4105
4106 # Verify pruned execution by comparing the NDB$INFO counters
4107 --disable_query_log
4108 --eval select sum(val) - $pruned_range as pruned from ndbinfo.counters where block_name='DBSPJ' and counter_name='PRUNED_RANGE_SCANS_RECEIVED'
4109 --eval select sum(val) - $const_pruned_range as const_pruned from ndbinfo.counters where block_name='DBSPJ' and counter_name='CONST_PRUNED_RANGE_SCANS_RECEIVED'
4110 --enable_query_log
4111
4112 connection ddl;
4113 drop table t1;
4114
4115 ###
4116 # Sorted scan with sub scan used to not being pushable.
4117 # However since v7.2.6(?) we support this by enforcing
4118 # batchRowSize=1 for the parent table in the scan-scan.
4119 # (At the cost of more roundrtrips)
4120 ###
4121 connection ddl;
4122 create table t1 (pk int primary key, a int, b int) engine=ndb
4123 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4124 create index ix1 on t1(b,a);
4125
4126 connection spj;
4127 insert into t1 values (0,10,10);
4128 insert into t1 values (1,10,20);
4129 insert into t1 values (2,20,20);
4130 insert into t1 values (3,10,10);
4131 insert into t1 values (4,10,20);
4132 insert into t1 values (5,10,20);
4133 insert into t1 values (6,10,10);
4134 insert into t1 values (7,10,10);
4135 insert into t1 values (8,10,20);
4136 insert into t1 values (9,10,10);
4137
4138
4139 # This sorted scan-scan is pushed since V7.2.6
4140 explain select x1.pk,x1.a,x1.b from t1 as x1
4141 join t1 as x2 on x1.a=x2.b
4142 join t1 as x3 on x2.a=x3.b
4143 order by x1.pk limit 70;
4144 select x1.pk,x1.a,x1.b from t1 as x1
4145 join t1 as x2 on x1.a=x2.b
4146 join t1 as x3 on x2.a=x3.b
4147 order by x1.pk limit 70;
4148
4149 # This query should not be pushed, since mysqld requires sorted
4150 # results for the root scan.
4151 explain select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
4152 select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
4153
4154 connection ddl;
4155 drop table t1;
4156
4157 ########
4158 # Test correct cleanup of MRR accesses being executed multiple times.
4159 # This used to be bug#57481, and this is a SPJ specific testcase in addition to
4160 # the specific testcase commited together with patch for this bug.
4161 #######
4162 connection ddl;
4163 create table t (pk int primary key, a int) engine=ndb
4164 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4165 insert into t values
4166 (1,1), (2,1),
4167 (4,3), (6,3),
4168 (7,4), (8,4);
4169
4170 connection spj;
4171 --replace_column 10 # 11 #
4172 explain
4173 select distinct straight_join table1.pk FROM
4174 t as table1 join
4175 (t as table2 join
4176 (t as table3 join t as table4 on table3.pk = table4.a)
4177 on table2.pk = table3.pk )
4178 on table1.a = table4.pk
4179 where table2.pk != 6;
4180
4181 --sorted_result
4182 select distinct straight_join table1.pk FROM
4183 t as table1 join
4184 (t as table2 join
4185 (t as table3 join t as table4 on table3.pk = table4.a)
4186 on table2.pk = table3.pk )
4187 on table1.a = table4.pk
4188 where table2.pk != 6;
4189
4190 connection ddl;
4191 drop table t;
4192
4193 ########
4194 # SPJ variant of bug#57396
4195 # Test correct format of an 'open bound'
4196 ########
4197 connection ddl;
4198 create table t (b int, a int, primary key (a,b)) engine=ndb
4199 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4200
4201 connection spj;
4202 insert into t values(0,0);
4203
4204 --replace_column 10 # 11 #
4205 explain
4206 select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8;
4207 select * from t as t1 join t as t2 on t2.a=t1.a where t1.a < 8 or t1.a >= 8;
4208
4209 connection ddl;
4210 drop table t;
4211
4212 #######
4213 # Testcase for bug introduced by initial fix for
4214 # bug#57601 'Optimizer is overly eager to request ordered access.'
4215 # When we turned of 'sorted' for 'descending', we broke QUICK_SELECT_DESC
4216 # which required result to be read as an ordered index access
4217 # Note: Before V7.2.6, and as a result of WL5558, such 'turn of sorting'
4218 # is not expected to happen any more.
4219 #######
4220 connection ddl;
4221 create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb
4222 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4223
4224 connection spj;
4225 insert into t values (1,3), (3,6), (6,9), (9,1);
4226
4227 --replace_column 10 # 11 #
4228 explain
4229 select * from t as t1 join t as t2
4230 on t1.pk2 = t2.pk1
4231 where t1.pk1 != 6
4232 order by t1.pk1 DESC;
4233
4234 select * from t as t1 join t as t2
4235 on t1.pk2 = t2.pk1
4236 where t1.pk1 != 6
4237 order by t1.pk1 DESC;
4238
4239 connection ddl;
4240 drop table t;
4241
4242 #######
4243 # Testcase using 'REF_OR_NULL'
4244 # 'ref_or_null' contains elements of left outer join wo/ being identical.
4245 #
4246 connection ddl;
4247 create table t (k int, uq int, unique key ix1 (uq)) engine = ndb
4248 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4249
4250 connection spj;
4251 insert into t values (1,3), (3,NULL), (6,9), (9,1);
4252
4253 # Currently we do not handle 'ref_or_null' correctly.
4254 # It is therefore disabled as pushable
4255 explain
4256 select straight_join * from t as a join t as b
4257 on a.uq=b.uq or b.uq is null;
4258
4259 --sorted_result
4260 select straight_join * from t as a join t as b
4261 on a.uq=b.uq or b.uq is null;
4262
4263 connection ddl;
4264 drop table t;
4265
4266 ########
4267 # JT_SYSTEM testcase, 'a.k is null' is known 'false' ->
4268 # Join condition will always fail, and all 'left joins' can be NULL complemented wo/
4269 # even requiring to access left table (b) which becomes 'system' -> No pushed joins !
4270 ########
4271 connection ddl;
4272 create table t (k int primary key, uq int) engine = ndb
4273 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4274
4275 connection spj;
4276 insert into t values (1,3), (3,NULL), (6,9), (9,1);
4277
4278 --echo #############
4279 --echo # WL#14370: AccessPath will contain a 'zero rows'-AccessPath in place
4280 --echo # of the known 'false' condition.
4281 let $query =
4282 select * from t as a left join t as b
4283 on a.k is null and a.uq=b.uq;
4284
4285 eval explain $query;
4286 --replace_regex / \(cost=.*//
4287 eval explain format=tree $query;
4288 --sorted_result
4289 eval $query;
4290
4291 connection ddl;
4292 drop table t;
4293
4294 #######
4295 # Test of varchar query parameteres.
4296 #######
4297
4298 connection ddl;
4299 create table tc(
4300 a varchar(10) not null,
4301 b varchar(10),
4302 c varchar(10),
4303 primary key (a),
4304 unique key uk1 (b, c)
4305 ) engine=ndbcluster
4306 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4307
4308 connection spj;
4309 insert into tc values ('aa','bb', 'x'), ('bb','cc', 'x'), ('cc', 'dd', 'x');
4310
4311 explain select * from tc as x1
4312 right outer join tc as x2 on x1.b=x2.a
4313 left outer join tc as x3 on x2.b = x3.b and x1.c=x3.c;
4314
4315 --sorted_result
4316 select * from tc as x1
4317 right outer join tc as x2 on x1.b=x2.a
4318 left outer join tc as x3 on x2.b = x3.b and x1.c=x3.c;
4319
4320 ####
4321 # Test that 'select ... for update' is not pushed, since this requires locking.
4322 ####
4323
4324 explain select * from tc as x1, tc as x2 where x1.b=x2.a for update;
4325
4326 explain select * from tc as x1, tc as x2 where x1.b=x2.a;
4327
4328 connection ddl;
4329 drop table tc;
4330
4331 ###
4332 # prune with xfrm set incorrect keylen
4333 #
4334 connection ddl;
4335 create table t1 (
4336 a varchar(16) not null,
4337 b int not null,
4338 c varchar(16) not null,
4339 d int not null,
4340 primary key (a,b)
4341 ) engine ndb
4342 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
4343 partition by key (a);
4344
4345 connection spj;
4346 insert into t1 values ('aaa', 1, 'aaa', 1);
4347 select * from t1 as q1, t1 as q2 where q1.a = 'aaa' and q1.c=q2.a;
4348
4349 connection ddl;
4350 drop table t1;
4351
4352 ########################
4353 # Bug#17845161 'CORRUPT KEY IN TC, UNABLE TO XFRM'
4354 #
4355 # Created an incorrect lookup key when a varchar()
4356 # in the key was NULL
4357
4358 connection ddl;
4359 CREATE TABLE t1 (
4360 id int NOT NULL AUTO_INCREMENT,
4361 t2_id int,
4362 PRIMARY KEY (id)
4363 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
4364 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4365
4366 CREATE TABLE t2 (
4367 id int NOT NULL AUTO_INCREMENT,
4368 t3_id varchar(20) DEFAULT NULL,
4369 PRIMARY KEY (id)
4370 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
4371 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4372
4373 CREATE TABLE t3 (
4374 id varchar(20) NOT NULL,
4375 PRIMARY KEY (`id`)
4376 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
4377 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4378
4379 CREATE TABLE t4 (
4380 pk int NOT NULL,
4381 id varchar(20) NOT NULL,
4382 PRIMARY KEY (pk),
4383 UNIQUE KEY (`id`)
4384 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
4385 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4386
4387
4388 connection spj;
4389 INSERT INTO t1 VALUES (20, NULL);
4390 INSERT INTO t1 VALUES (23, 24);
4391
4392 INSERT INTO t2 VALUES (24, NULL);
4393
4394 EXPLAIN
4395 SELECT *
4396 FROM t1
4397 INNER JOIN t2 ON t2.id = t1.t2_id
4398 LEFT OUTER JOIN t3 ON t3.id = t2.t3_id
4399 WHERE t1.id = 20;
4400 SELECT *
4401 FROM t1
4402 INNER JOIN t2 ON t2.id = t1.t2_id
4403 LEFT OUTER JOIN t3 ON t3.id = t2.t3_id
4404 WHERE t1.id = 20;
4405
4406
4407 EXPLAIN
4408 SELECT *
4409 FROM t1
4410 INNER JOIN t2 ON t2.id = t1.t2_id
4411 LEFT OUTER JOIN t3 ON t3.id = t2.t3_id
4412 WHERE t1.id = 23;
4413 SELECT *
4414 FROM t1
4415 INNER JOIN t2 ON t2.id = t1.t2_id
4416 LEFT OUTER JOIN t3 ON t3.id = t2.t3_id
4417 WHERE t1.id = 23;
4418
4419 EXPLAIN
4420 SELECT *
4421 FROM t1
4422 INNER JOIN t2 ON t2.id = t1.t2_id
4423 LEFT OUTER JOIN t4 ON t4.id = t2.t3_id
4424 WHERE t1.id = 23;
4425 SELECT *
4426 FROM t1
4427 INNER JOIN t2 ON t2.id = t1.t2_id
4428 LEFT OUTER JOIN t4 ON t4.id = t2.t3_id
4429 WHERE t1.id = 23;
4430
4431
4432 EXPLAIN
4433 SELECT *
4434 FROM t1
4435 LEFT OUTER JOIN t2 ON t2.id = t1.t2_id
4436 LEFT OUTER JOIN t2 as t3 ON t3.id = t1.t2_id;
4437 --sorted_result
4438 SELECT *
4439 FROM t1
4440 LEFT OUTER JOIN t2 ON t2.id = t1.t2_id
4441 LEFT OUTER JOIN t2 as t3 ON t3.id = t1.t2_id;
4442
4443 connection ddl;
4444 DROP TABLE t1,t2,t3,t4;
4445
4446 #######################################
4447 # Some tests for nested left joins.
4448
4449 connection ddl;
4450 CREATE TABLE t1 (
4451 a int NOT NULL,
4452 b int NOT NULL,
4453 c int NOT NULL,
4454 d int,
4455 PRIMARY KEY (`a`,`b`),
4456 unique key(c)
4457 ) ENGINE=ndbcluster
4458 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4459
4460 connection spj;
4461 insert into t1 values
4462 (1,1,1,1),
4463 (1,2,2,1),
4464 (1,3,3,1),
4465 (1,4,4,1),
4466 (1,5,5,2),
4467 (1,6,6,2),
4468 (1,7,7,2),
4469 (1,8,8,2);
4470
4471 explain select count(*) from t1 as x1
4472 join (t1 as x2
4473 left join (t1 as x3
4474 cross join t1 as x4)
4475 on x2.d=x3.a)
4476 on x2.c is null or x1.a=x4.d;
4477 select count(*) from t1 as x1
4478 join (t1 as x2
4479 left join (t1 as x3
4480 cross join t1 as x4)
4481 on x2.d=x3.a)
4482 on x2.c is null or x1.a=x4.d;
4483
4484 explain select count(*) from t1 as x1
4485 left join (t1 as x2
4486 cross join t1 as x3)
4487 on x1.d=x2.a;
4488 select count(*) from t1 as x1
4489 left join (t1 as x2
4490 cross join t1 as x3)
4491 on x1.d=x2.a;
4492
4493 --echo #################
4494 --echo # Prior to WL#14370 we did 'pending condition' analysis inside the
4495 --echo # join pushdown handler. That code took a too conservative approach
4496 --echo # in identifying such pending conditions, somethimes resulting in pushdown
4497 --echo # to be rejected due to 'table condition can not be fully evaluated...'
4498 --echo # The AccessPath construction code does a much better job of identifying
4499 --echo # these pending condition, and placing them at the correct branch level.
4500 --echo # As this information is now available to us through the AccessPath
4501 --echo # integration, we eliminated our own (incomplete) code for doing such
4502 --echo # analysis and could avoid such false rejections at the same time.
4503 --echo #
4504 --echo # Note the diff in the traditional and tree format explain below:
4505 --echo # The traditional explain says table x4 is 'using where', which was
4506 --echo # incorrectly identified as a trigger condition on join(x2,x4).
4507 --echo # The tree format explain shows that it has been liftet out of that
4508 --echo # join scope as a 'filter' on the entire x1..x4 join - Thus there is
4509 --echo # noting preventing the left-join(x2,x4)
4510 --echo #################
4511
4512 let $query =
4513 select count(*) from t1 as x0
4514 left join (t1 as x1
4515 join (t1 as x2
4516 left join (t1 as x3
4517 join t1 as x4 on x3.d=x4.a)
4518 on x2.d=x3.a)
4519 on x2.c is null or x1.a=x4.d)
4520 on x0.d=x1.a;
4521
4522 eval explain $query;
4523 --replace_regex / \(cost=.*//
4524 eval explain format=tree $query;
4525 eval $query;
4526
4527 connection ddl;
4528 drop table t1;
4529
4530 ## Test scan sorted on string field.
4531 connection ddl;
4532 create table t1 (pk char(10) primary key, u int not null) engine=ndb
4533 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4534 create table t2 (pk int primary key, u int not null) engine=ndb
4535 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4536
4537 connection spj;
4538 insert into t1 values ('wh',1);
4539 insert into t1 values ('ik',2);
4540 insert into t1 values ('cu',3);
4541 insert into t1 values ('pw',4);
4542 insert into t1 values ('cq',4);
4543
4544 insert into t2 values (1,2), (2,3), (3,4), (4,5);
4545
4546 explain select * from t1 join t2 on t1.u = t2.pk order by t1.pk;
4547 select * from t1 join t2 on t1.u = t2.pk order by t1.pk;
4548
4549 connection ddl;
4550 drop table t1;
4551 drop table t2;
4552
4553 ########################################
4554 # Test query with very long records.
4555 connection ddl;
4556 create table t1 (
4557 a char(10) primary key,
4558 b char(10) not null,
4559 c char(10) not null,
4560 l00 char(255) not null,
4561 l01 char(255) not null,
4562 l02 char(255) not null,
4563 l03 char(255) not null,
4564 l04 char(255) not null,
4565 l05 char(255) not null,
4566 l06 char(255) not null,
4567 l07 char(255) not null,
4568 l08 char(255) not null,
4569 l09 char(255) not null,
4570 l10 char(255) not null,
4571 l11 char(255) not null,
4572 l12 char(255) not null,
4573 l13 char(255) not null,
4574 l14 char(255) not null,
4575 l15 char(255) not null,
4576 l16 char(255) not null,
4577 l17 char(255) not null,
4578 l18 char(255) not null,
4579 l19 char(255) not null,
4580 l20 char(255) not null,
4581 l21 char(255) not null,
4582 l22 char(255) not null,
4583 l23 char(255) not null,
4584 l24 char(255) not null,
4585 l25 char(255) not null,
4586 l26 char(255) not null,
4587 l27 char(255) not null,
4588 l28 char(255) not null,
4589 l29 char(255) not null,
4590 l30 char(255) not null,
4591 l31 char(255) not null,
4592 index(c, b)
4593 ) engine=ndb character set latin1 partition by key(a) partitions 8;
4594
4595 connection spj;
4596
4597 insert into t1 values ('a','a','a','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x');
4598
4599 insert into t1 values ('b','b','b','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x');
4600
4601 insert into t1 values ('c','c','c','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x','x');
4602
4603 --replace_column 10 # 11 #
4604 explain select count(*) from t1 as x1 join t1 as x2 on x1.b = x2.c;
4605
4606 select count(*) from t1 as x1 join t1 as x2 on x1.b = x2.c;
4607
4608 connection ddl;
4609 drop table t1;
4610
4611 ####################
4612 # Test pruned child scans using parameter values (known regression).
4613 ####################
4614 create table t1
4615 (a int not null,
4616 b int not null,
4617 c int not null,
4618 d int not null,
4619 primary key(a,b,c,d)) engine=ndb
4620 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
4621 partition by key (b,c);
4622
4623 connection spj;
4624 insert into t1 values (0x4f, 0x4f, 0x4f, 0x4f);
4625
4626 # Prune key depends on parent row.
4627 --replace_column 10 # 11 #
4628 explain select * from t1 as x1
4629 join t1 as x2 on x1.c=0x4f and x2.a=0+x1.b and x2.b=x1.b
4630 join t1 as x3 on x3.a=x2.d and x3.b=x1.d and x3.c=x2.c;
4631
4632 select * from t1 as x1
4633 join t1 as x2 on x1.c=0x4f and x2.a=0+x1.b and x2.b=x1.b
4634 join t1 as x3 on x3.a=x2.c and x3.b=x1.d and x3.c=x2.c;
4635
4636 # Prune key is fixed.
4637 --replace_column 10 # 11 #
4638 explain select * from t1 as x1
4639 join t1 as x2 on x1.c=0x4f and x2.a=0+x1.b and x2.b=x1.b
4640 join t1 as x3 on x3.a=x2.d and x3.b=x1.d and x3.c=0x4f;
4641
4642 select * from t1 as x1
4643 join t1 as x2 on x1.c=0x4f and x2.a=0+x1.b and x2.b=x1.b
4644 join t1 as x3 on x3.a=x2.c and x3.b=x1.d and x3.c=0x4f;
4645
4646 connection ddl;
4647 drop table t1;
4648
4649 ############################################################
4650 # Bug#13990924 / Bug#64865 Large WHERE IN with SPJ leads to
4651 # cluster shutdown
4652 #
4653 # Caused by incorrect error handling in SPJ block when
4654 # there was a buffer overflow (>32K)
4655 # (Pushed condition became to large)
4656 #
4657 # API also failed to prevent this from happen as it checked
4658 # for size <= 64K, while actuall buffer size was 32K
4659 # (Has now been increased)
4660 ############################################################
4661 create table t1 (
4662 k1 int primary key,
4663 i int,
4664 name varchar(32),
4665 key (name)
4666 )
4667 default charset = utf8
4668 engine = ndb
4669 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4670
4671 insert into t1 values (1, 1, 'Ole');
4672 insert into t1 values (2, 2, 'Dole');
4673 insert into t1 values (3, 3, 'Doffen');
4674 insert into t1 values (4, 4, 'row# 999');
4675
4676 ###################################################
4677 # In the IN-value tests below we use regex filtering to
4678 # avoid having the entire IN-value list in the result-file.
4679 # We filter out all IN-values except the first 'foo' value
4680 # and the last 'row# <last value', leaving the result:
4681 #
4682 # in ('foo' .... 'row# <last value')
4683 #
4684 # Note, there is a regex bug:
4685 # Bug 86164 - std::regex crashes when matching long lines
4686 # https://gcc.gnu.org/bugzilla/show_bug.cgi?id=86164
4687 #
4688 # The root cause is a too deep recursion when matching '+' and '*' patterns.
4689 # in combination with a too long text to match. We hit that bug when
4690 # regex-matching such long lines as generated in these IN-value lists.
4691 #
4692 # As a workaround for that bug we do the regex matching in two steps:
4693 #
4694 # 1) Consume all 'row# nnn' with a trailing ',' - Keeping just the last
4695 # row# nnn which has no trailing ','
4696 # 2) Add ' .... ' in front of the last remaining 'row#"
4697 #
4698 #######################################################
4699
4700 # These queries with large IN clauses exceeds the default memory limit of the
4701 # range optimizer on 64 bit but not on 32 bit, thus cauing different result.
4702 # Fix by setting a fairly low value in order to get consistent behaviour
4703 set @save_range_opt_max = @@session.range_optimizer_max_mem_size;
4704 set range_optimizer_max_mem_size = 64*1024;
4705
4706 echo ------------------------------------------------------------------;
4707 echo;
4708 # Build SPJ query with 2000 IN values
4709 # This used to result in buffer overflow, but will now
4710 # pass as buffer size is increased from 32K -> 64K
4711 #
4712 let $query = select * from t1 x, t1 y where y.k1=x.i and x.name in ('foo';
4713 let $values = 0;
4714 while ($values < 2000)
4715 {
4716 let $query = $query,'row# $values';
4717 inc $values;
4718 }
4719 let $query = $query );
4720
4721 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4722 eval explain $query;
4723 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4724 eval $query;
4725
4726 echo ------------------------------------------------------------------;
4727 echo;
4728 # Same SPJ query with 4000 IN values
4729 # This will hit the actuall limit of 64K and will be
4730 # detected by API which will drop this pushed condition.
4731 # 'Warning 4294 Scan filter is too large, discarded'
4732 # (Pushed condition evaluated by ha_ndbcluster handler instead)
4733 #
4734 let $query = select * from t1 x, t1 y where y.k1=x.i and x.name in ('foo';
4735 let $values = 0;
4736 while ($values < 4000)
4737 {
4738 let $query = $query,'row# $values';
4739 inc $values;
4740 }
4741 let $query = $query );
4742
4743 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4744 eval explain $query;
4745 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4746 eval $query;
4747
4748 echo ------------------------------------------------------------------;
4749 echo;
4750 # Build single table (non-SPJ) query with 4000 IN values
4751 # Should give same 'Warning 4294' as above
4752 let $query = select * from t1 x where x.name in ('foo';
4753 let $values = 0;
4754 while ($values < 4000)
4755 {
4756 let $query = $query,'row# $values';
4757 inc $values;
4758 }
4759 let $query = $query );
4760
4761 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4762 eval explain $query;
4763 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4764 eval $query;
4765
4766
4767 ############################################################
4768 # Bug #27397802 ENTIRE CLUSTER CRASHES WITH "MESSAGE TO BIG
4769 # IN SENDSIGNAL" WITH BIG IN CLAUSE.
4770 #
4771 # Similar problem as Bug#13990924; This time with a pushed
4772 # condition as part of an EQ_REF child node
4773 #
4774 ############################################################
4775
4776 insert into t1 values (5, 4, 'row# 1');
4777 insert into t1 values (6, 4, 'row# 2');
4778 insert into t1 values (7, 4, 'row# 3');
4779 insert into t1 values (8, 4, 'row# 4');
4780
4781 echo ------------------------------------------------------------------;
4782 echo;
4783 # A 'small' 1000 valued IN-list is handled OK
4784 #
4785 let $query = select straight_join * from t1 x, t1 y where y.k1=x.i and y.name in ('foo';
4786 let $values = 0;
4787 while ($values < 1000)
4788 {
4789 let $query = $query,'row# $values';
4790 inc $values;
4791 }
4792 let $query = $query ) order by x.k1;
4793
4794 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4795 eval explain $query;
4796 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4797 eval $query;
4798
4799 echo ------------------------------------------------------------------;
4800 echo;
4801 # Also a sufficient HUGE IN value list is ok as the pushed
4802 # condition code is > 64K and catched by other 'Too large'
4803 # checks.
4804 #
4805 let $query = select straight_join * from t1 x, t1 y where y.k1=x.i and y.name in ('foo';
4806 let $values = 0;
4807 while ($values < 4000)
4808 {
4809 let $query = $query,'row# $values';
4810 inc $values;
4811 }
4812 let $query = $query ) order by x.k1;
4813
4814 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4815 eval explain $query;
4816 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4817 eval $query;
4818
4819
4820 echo ------------------------------------------------------------------;
4821 echo;
4822 # A pushed condition with size between 32K - 64K was not 'Too large',
4823 # but would require fragmented sending in order to work -> Crashed.
4824 #
4825 let $query = select straight_join * from t1 x, t1 y where y.k1=x.i and y.name in ('foo';
4826 let $values = 0;
4827 while ($values < 2000)
4828 {
4829 let $query = $query,'row# $values';
4830 inc $values;
4831 }
4832 let $query = $query ) order by x.k1;
4833
4834 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4835 eval explain $query;
4836 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4837 eval $query;
4838
4839 set range_optimizer_max_mem_size = @save_range_opt_max;
4840
4841 drop table t1;
4842
4843 echo ------------------------------------------------------------------;
4844 echo Bug#35185670 'mysqld crashes on expensive query';
4845 echo or we may assert, or just hang until timeout...;
4846 echo;
4847 #
4848 # In a fragmenetd signal, the list of ReceiverId's were not correctly
4849 # compiled into the REQuest. Some were duplicated, others were missing.
4850 # In order to provoke the bug we need more than 16 receiverId's
4851 # -> Create table with many partitions.
4852 # -> Require the result to be ordered, which prevent multiple
4853 # fragments to be folded into the same receiver.
4854 #
4855 # Test case query is based on the above large IN-list queries.
4856 #
4857
4858 create table t1 (
4859 k1 varchar(32) not null,
4860 k2 int not null default 0,
4861 primary key(k1,k2),
4862 i int default 0
4863 ) engine = ndbcluster
4864 partition by key() partitions 32;
4865
4866 create table t2 (
4867 k1 int not null primary key
4868 ) engine = ndbcluster
4869 partition by key() partitions 32;
4870
4871 let $query = insert into t1(k1) values ('foo');
4872 let $values = 1;
4873 while ($values < 2000)
4874 {
4875 let $query = $query,('row# $values');
4876 inc $values;
4877 }
4878
4879 ###################
4880 echo;
4881 echo Inserting 2000 rows in table t1;
4882
4883 --disable_query_log
4884 eval $query;
4885 analyze table t1;
4886
4887 insert into t2(k1) values(0);
4888 analyze table t2;
4889 --enable_query_log
4890
4891 let $query = select * from t1 left join t2 on t2.k1=t1.i where t1.k1 in ('foo';
4892 let $values = 0;
4893 while ($values < 2000)
4894 {
4895 let $query = $query,'row# $values';
4896 inc $values;
4897 }
4898 let $query = $query ) order by t1.k1, t1.k2;
4899
4900 echo;
4901 echo Expect query and IN-condition to be pushed;
4902 echo (If join_pushdown is enabled);
4903
4904 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4905 eval explain $query;
4906
4907 --disable_result_log
4908 --replace_regex /'row# [0-9]+',// /'row#/ .... 'row#/
4909 eval $query;
4910 --enable_result_log
4911
4912 drop table t1;
4913 drop table t2;
4914
4915 ############################################################
4916 # Bug#14010406 LARGE PUSHED JOIN HIT ASSERT IN SPJ BLOCK
4917 #
4918 # Buffered rows (caused by PARENT refs) was prematurely
4919 # released when there was no TN_ACTIVE treeNodes childs.
4920 # However, in bushy scans, a scan branch can be 'repeated'
4921 # even if its previous execution was 'complete'.
4922 #
4923 # Thus we have to use a less eager release strategy where
4924 # we don't release any buffered rows until we prepare for
4925 # a NEXTREQ which will fetch more rows into the treeNode.
4926 ############################################################
4927
4928 create table t(
4929 pk int primary key auto_increment,
4930 i int,
4931 j int,
4932 k int,
4933 index(i,j),
4934 index(i),
4935 index(j),
4936 index(k)
4937 ) engine = ndb
4938 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4939
4940 insert into t(i,j,k) values
4941 (1,1,1), (1,1,1), (1,1,1),
4942 (2,2,2), (2,2,2), (2,2,2);
4943
4944 --error 0,1193
4945 set global debug='+d,max_4rows_in_spj_batches';
4946
4947 --replace_column 10 # 11 #
4948 explain
4949 select straight_join count(*) from
4950 t as t1
4951 join t as t2 on t2.i = t1.i
4952 join (t as t3 join t as t4 on t4.k=t3.k join t as t5 on t5.i=t4.i and t5.j=t3.j) on t3.pk=t1.j
4953 join t as t6 on t6.k = t1.k
4954 where t1.i < 2;
4955
4956 select straight_join count(*) from
4957 t as t1
4958 join t as t2 on t2.i = t1.i
4959 join (t as t3 join t as t4 on t4.k=t3.k join t as t5 on t5.i=t4.i and t5.j=t3.j) on t3.pk=t1.j
4960 join t as t6 on t6.k = t1.k
4961 where t1.i < 2;
4962
4963 --error 0,1193
4964 set global debug=@save_debug;
4965
4966 drop table t;
4967
4968 ####################################################
4969 # Test LQH handling of zero-length key in LQHKEYREQ
4970 # Bug#
4971 create table t1 (a int primary key, b int, c int, index(b,c)) engine = ndb
4972 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4973 insert into t1 values (4,null, 2);
4974
4975 explain
4976 select x.a from t1 as x join t1 as y on y.a = x.b where x.a=4;
4977 select x.a from t1 as x join t1 as y on y.a = x.b where x.a=4;
4978
4979 drop table t1;
4980
4981 ####################################################
4982 # Regression test for Bug#14644936 "INEFFICIENT AND INCORRECT EXECUTION OF
4983 # PUSHABLE OUTER JOINS".
4984 ####################################################
4985
4986 connection ddl;
4987 CREATE TABLE t1 (
4988 a int NOT NULL,
4989 b int DEFAULT NULL,
4990 c int NOT NULL,
4991 d int NOT NULL,
4992 PRIMARY KEY (`a`)
4993 ) ENGINE=ndbcluster
4994 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
4995
4996 create unique index ix1 on t1(b,c) using hash;
4997
4998 connection spj;
4999
5000 insert into t1 values (1,NULL,1,1);
5001
5002
5003 explain select * from t1 as x1 left join (t1 as x2 join t1 as x3 on x2.d=x3.c) on x1.b=x3.b;
5004
5005 # Record value before query.
5006 create temporary table scan_count
5007 select * from performance_schema.global_status
5008 where variable_name = 'Ndb_scan_count';
5009
5010 # Query gives wrong result on mysql-trunk-cluster, and extra scan on both
5011 # mysql-5.5-cluster-7.2 and mysql-trunk-cluster.
5012 select * from t1 as x1 left join (t1 as x2 join t1 as x3 on x2.d=x3.c) on x1.b=x3.b;
5013
5014 # Bug causes value to increase by 3 rather than 2.
5015 select scan_count.VARIABLE_NAME,
5016 old.VARIABLE_VALUE - scan_count.VARIABLE_VALUE
5017 from scan_count, performance_schema.global_status as old
5018 where old.variable_name = 'Ndb_scan_count';
5019
5020 drop table scan_count;
5021
5022 connection ddl;
5023 drop table t1;
5024
5025 ##############
5026 # Bug#16925513: HIT DBUG_ASSERT IN SPJ BLOCK, ~LINE 4406
5027 #
5028 # Testcase extracted from failing RQG test.
5029
5030 connection ddl;
5031 CREATE TABLE table1 (
5032 col_int_unique int(11),
5033 PRIMARY KEY (col_int_unique)
5034 ) ENGINE=ndbcluster
5035 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
5036
5037 CREATE TABLE table3 (
5038 col_int int(11) NOT NULL DEFAULT '0',
5039 KEY (col_int)
5040 ) ENGINE=ndbcluster
5041 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
5042
5043 CREATE TABLE table4 (
5044 col_int int(11) DEFAULT NULL,
5045 pk int(11) NOT NULL,
5046 PRIMARY KEY (pk)
5047 ) ENGINE=ndbcluster
5048 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
5049
5050 CREATE TABLE table6 (
5051 col_int int(11) DEFAULT NULL,
5052 col_int_unique int(11) DEFAULT NULL
5053 ) ENGINE=ndbcluster
5054 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
5055
5056 connection spj;
5057 insert into table6 values
5058 (2,NULL),
5059 (2,NULL),
5060 (2,NULL),
5061 (2,NULL),
5062 (2,NULL),
5063 (2,NULL),
5064 (2,NULL),
5065 (2,NULL);
5066
5067 --error 0,1193
5068 set global debug='+d,max_4rows_in_spj_batches';
5069
5070 EXPLAIN
5071 SELECT * FROM
5072 table1 RIGHT JOIN
5073 table3 LEFT JOIN table4 ON table3.col_int = table4.col_int
5074 JOIN
5075 table6 ON table4.pk = table6.col_int_unique
5076 ON table1.col_int_unique = table6.col_int;
5077
5078 --sorted_result
5079 SELECT * FROM
5080 table1 RIGHT JOIN
5081 table3 LEFT JOIN table4 ON table3.col_int = table4.col_int
5082 JOIN
5083 table6 ON table4.pk = table6.col_int_unique
5084 ON table1.col_int_unique = table6.col_int;
5085
5086
5087 --error 0,1193
5088 set global debug=@save_debug;
5089
5090 connection ddl;
5091 drop table table6;
5092 drop table table4;
5093 drop table table3;
5094 drop table table1;
5095
5096
5097 --echo 5.6 tests
5098 #######################################
5099 # MySQL 5.6 include improved optimizer
5100 # handling of subqueries. Some specific
5101 # test for handling limitations / problems
5102 # specific to these features
5103
5104 #############################################
5105 # Testing of subqueries and join pushdown
5106 #
5107 # Note that subqueries has limitations very similar
5108 # to outer joins.
5109 #############################################
5110
5111 ##############
5112 # Bug #16664035
5113 # PUSHED JOIN TOGETHER WITH 'FIRSTMATCH' STRATEGY MAY RETURN TOO MANY ROWS
5114
5115 connection ddl;
5116 create table t1 (
5117 a int not null,
5118 b int not null,
5119 c int not null,
5120 d int not null,
5121 primary key (`a`,`b`),
5122 key(c), key(d)
5123 ) engine=ndbcluster
5124 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
5125
5126 connection spj;
5127 insert into t1 values
5128 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
5129 (1,2,5,1), (1,3,1,2), (1,4,2,3),
5130 (2,1,3,4), (2,3,4,5), (2,4,5,1),
5131 (3,1,1,2), (3,2,2,3), (3,4,3,4),
5132 (4,1,4,5), (4,2,5,1), (4,3,1,2);
5133
5134 # Use debug hook to enforce only 4 rows in each batch (Two roundtips)
5135 --error 0,1193
5136 set global debug='+d,max_4rows_in_spj_batches';
5137
5138 # Make use of FirstMatch more likely:
5139 set @save_optimizer_switch = @@optimizer_switch;
5140 set optimizer_switch='materialization=off';
5141
5142 # 'subq' should use 'ref, FirstMatch(t1)', preventing join pushdown
5143 explain
5144 select count(*) from t1 where
5145 t1.c in (select c from t1 as subq);
5146 select count(*) from t1 where
5147 t1.c in (select c from t1 as subq);
5148
5149 explain
5150 select count(*) from t1 where
5151 t1.c in (select c from t1 as subq1) and
5152 t1.d in (select d from t1 as subq2);
5153 select count(*) from t1 where
5154 t1.c in (select c from t1 as subq1) and
5155 t1.d in (select d from t1 as subq2);
5156
5157 explain
5158 select count(*) from t1 where
5159 t1.c in (select c from t1 as subq1 where
5160 subq1.c in (select c from t1 as subq2));
5161 select count(*) from t1 where
5162 t1.c in (select c from t1 as subq1 where
5163 subq1.c in (select c from t1 as subq2));
5164
5165 explain
5166 select count(*) from t1 where
5167 t1.c in (select subq1.c from t1 as subq1 straight_join t1 as subq2 on subq1.a = subq2.c);
5168 select count(*) from t1 where
5169 t1.c in (select subq1.c from t1 as subq1 straight_join t1 as subq2 on subq1.a = subq2.c);
5170
5171 set optimizer_switch=@save_optimizer_switch;
5172
5173 --error 0,1193
5174 set global debug=@save_debug;
5175
5176 ####################################
5177 --echo #
5178 --echo # Bug#29860378
5179 --echo # RESULTS NOT SORTED AS SPECIFIED WHEN QUERY IS A 'PUSHED JOIN'
5180 --echo #
5181
5182 # Force a small batch size in order to reproduce bug without needing
5183 # a large test table.
5184 --error 0,1193
5185 set global debug='+d,max_4rows_in_spj_batches';
5186
5187 explain
5188 select straight_join t1.* from
5189 t1 join t1 as t2 on t2.d = t1.a
5190 where t1.a > 0
5191 order by t1.a,t1.b;
5192
5193 select straight_join t1.* from
5194 t1 join t1 as t2 on t2.d = t1.a
5195 where t1.a > 0
5196 order by t1.a,t1.b;
5197
5198 explain
5199 select straight_join t1.a,t1.b,count(*) from
5200 t1 join t1 as t2 on t2.d = t1.a
5201 where t1.a > 0
5202 group by t1.a,t1.b;
5203
5204 --sorted_result
5205 select straight_join t1.a,t1.b,count(*) from
5206 t1 join t1 as t2 on t2.d = t1.a
5207 where t1.a > 0
5208 group by t1.a,t1.b;
5209
5210 --error 0,1193
5211 set global debug=@save_debug;
5212
5213
5214 ##################################################
5215 # Bug##26926666
5216 # 'LOOSESCAN' TABLES SHOULD NOT BE CHILD MEMBERS IN A PUSHED JOIN
5217 #
5218 ##################################################
5219
5220 ######
5221 # Expect a query plan where the subquery table1s becomes
5222 # a child candidate in a pushed join. 'LooseScan' require
5223 # sorted results to be returned from the (child) table, which
5224 # we do not implement.
5225
5226 connection spj;
5227 set @save_optimizer_switch = @@optimizer_switch;
5228 set @@optimizer_switch='semijoin=on';
5229 set @@optimizer_switch='loosescan=on';
5230
5231 explain SELECT count(*)
5232 FROM (t1 AS table1 JOIN t1 AS table2 USING(a,b))
5233 WHERE table1.b IN
5234 (SELECT /*+ SEMIJOIN(LOOSESCAN)*/ table1s.c FROM t1 as table1s);
5235 SELECT count(*)
5236 FROM (t1 AS table1 JOIN t1 AS table2 USING(a,b))
5237 WHERE table1.b IN
5238 (SELECT /*+ SEMIJOIN(LOOSESCAN)*/ table1s.c FROM t1 as table1s);
5239
5240 ########
5241 # Same query with 'FirstMatch', suplements FirstMatch (bug#16664035)
5242 # testcase above
5243
5244 set @@optimizer_switch='firstmatch=on';
5245
5246 explain SELECT count(*)
5247 FROM (t1 AS table1 JOIN t1 AS table2 USING(a,b))
5248 WHERE table1.b IN
5249 (SELECT /*+ SEMIJOIN(FIRSTMATCH)*/ table1s.c FROM t1 as table1s);
5250 SELECT count(*)
5251 FROM (t1 AS table1 JOIN t1 AS table2 USING(a,b))
5252 WHERE table1.b IN
5253 (SELECT /*+ SEMIJOIN(FIRSTMATCH)*/ table1s.c FROM t1 as table1s);
5254
5255 set optimizer_switch=@save_optimizer_switch;
5256
5257 ##################################################
5258 # Bug#26919289:
5259 # mysqld crash during join_pushdown analysis
5260 #
5261 # The mysql optimizer introduce syntetic JOIN_TAB
5262 # elements representing where the materialized subq
5263 # should be accessed in the query plan. As these
5264 # are not representing 'real tables', their 'tableno'
5265 # was not set up in ndb_pushed_builder_ctx C'tor.
5266 # It effectively contained uninitialized data, which was
5267 # then later used to access a 'tableno-bitmask'.
5268 ###################################################
5269
5270 --echo ####################################################################
5271 --echo # WL#14370: Change EXPLAIN to use 'format=tree'. Traditional explain
5272 --echo # didn't correctly represent the order of tables in MATERIALIZED
5273 --echo # sub queries. That resulted in some confusing 'EXPLAIN_NO_PUSH' diffs
5274 --echo # where the reported root/child order didn't make sense.
5275 --echo # As the tree-format explain will show, the changed output
5276 --echo # make more sense related to the query plan represented by the
5277 --echo # AccessPath tree.
5278 --echo # WL#14370: Materialized semijoin's is not the only SJ-strategi
5279 --echo # where we do no pushdown across main-query -> sub-query border.
5280 --echo # (The sub-query itself may be pushed though)
5281 --echo ###################################################################
5282
5283 connection spj;
5284 set @save_optimizer_switch = @@optimizer_switch;
5285 set @@optimizer_switch='semijoin=on';
5286 set @@optimizer_switch='materialization=on';
5287
5288 let $query =
5289 select count(*)
5290 from t1
5291 join t1 as t2 on t2.a = t1.b
5292 where t1.c IN (select /*+ SEMIJOIN(MATERIALIZATION)*/ c from t1 as t3);
5293
5294 --replace_regex / \(cost=.*//
5295 eval explain format=tree $query;
5296 eval $query;
5297
5298
5299 let $query =
5300 select count(*)
5301 from t1
5302 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
5303 where t1.c IN (select /*+ SEMIJOIN(MATERIALIZATION)*/ c from t1 as t3);
5304
5305 --replace_regex / \(cost=.*//
5306 eval explain format=tree $query;
5307 eval $query;
5308
5309
5310 let $query =
5311 select count(*)
5312 from (select * from t1 where t1.c IN (select /*+ SEMIJOIN(MATERIALIZATION)*/ c from t1 as t3)) as t1
5313 join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
5314
5315 --replace_regex / \(cost=.*//
5316 eval explain format=tree $query;
5317 eval $query;
5318
5319
5320 let $query =
5321 select count(*)
5322 from (select * from t1 where t1.c IN (select /*+ SEMIJOIN(MATERIALIZATION)*/ c from t1 as t3)) as t1
5323 join t1 as t2 on t2.a = t1.b and t2.b = t1.c
5324 where t1.c IN (select /*+ SEMIJOIN(MATERIALIZATION)*/ c from t1 as t4);
5325
5326 --replace_regex / \(cost=.*//
5327 eval explain format=tree $query;
5328 eval $query;
5329
5330
5331 set optimizer_switch='block_nested_loop=off';
5332
5333 let $query =
5334 select count(*)
5335 from t1 cross join t1 as t2
5336 where t2.c IN (select /*+ SEMIJOIN(MATERIALIZATION)*/ t4s.c
5337 from t1 as t3s join t1 as t4s on t4s.a = t3s.b);
5338
5339 --replace_regex / \(cost=.*//
5340 eval explain format=tree $query;
5341 eval $query;
5342
5343 set optimizer_switch=@save_optimizer_switch;
5344
5345
5346 ###################################################
5347 # Bug #26984919
5348 # SPJ: 'DUPLICATE WEEDOUT' STRATEGY MAY RETURN INCORRECT RESULTS
5349
5350 ###################################################
5351
5352 # Use debug hook to enforce only 4 rows in each batch (multiple roundtips)
5353 --error 0,1193
5354 set global debug='+d,max_4rows_in_spj_batches';
5355
5356 connection spj;
5357 set @save_optimizer_switch = @@optimizer_switch;
5358 set @@optimizer_switch='semijoin=on';
5359
5360 explain
5361 select count(*)
5362 from t1
5363 join t1 as t2 on t2.a = t1.b
5364 where t1.c IN (
5365 select /*+ SEMIJOIN(DUPSWEEDOUT)*/ t3s.c from t1 as t3s
5366 join t1 as t4s on t4s.a = t3s.b);
5367
5368 select count(*)
5369 from t1
5370 join t1 as t2 on t2.a = t1.b
5371 where t1.c IN (
5372 select /*+ SEMIJOIN(DUPSWEEDOUT)*/ t3s.c from t1 as t3s
5373 join t1 as t4s on t4s.a = t3s.b);
5374
5375
5376 explain
5377 select count(*)
5378 from t1
5379 join t1 as t2 on t2.a = t1.b
5380 where t2.c IN (
5381 select /*+ SEMIJOIN(DUPSWEEDOUT)*/ t3s.c from t1 as t3s
5382 join t1 as t4s on t4s.a = t3s.b);
5383
5384 select count(*)
5385 from t1
5386 join t1 as t2 on t2.a = t1.b
5387 where t2.c IN (
5388 select /*+ SEMIJOIN(DUPSWEEDOUT)*/ t3s.c from t1 as t3s
5389 join t1 as t4s on t4s.a = t3s.b);
5390
5391
5392 explain
5393 select count(*)
5394 from t1
5395 join t1 as t2 on t2.a = t1.b
5396 join t1 as t3 on t3.a = t2.b
5397 where t1.c IN (
5398 select /*+ SEMIJOIN(DUPSWEEDOUT)*/ t4s.c from t1 as t4s
5399 join t1 as t5s on t5s.a = t4s.b
5400 join t1 as t6s on t6s.a = t5s.b
5401 );
5402
5403 select count(*)
5404 from t1
5405 join t1 as t2 on t2.a = t1.b
5406 join t1 as t3 on t3.a = t2.b
5407 where t1.c IN (
5408 select /*+ SEMIJOIN(DUPSWEEDOUT)*/ t4s.c from t1 as t4s
5409 join t1 as t5s on t5s.a = t4s.b
5410 join t1 as t6s on t6s.a = t5s.b
5411 );
5412
5413
5414 explain
5415 select count(*)
5416 from t1
5417 join t1 as t2 on t2.a = t1.b
5418 join t1 as t3 on t3.a = t2.b
5419 where t2.c IN (
5420 select /*+ SEMIJOIN(DUPSWEEDOUT)*/ t4s.c from t1 as t4s
5421 join t1 as t5s on t5s.a = t4s.b
5422 join t1 as t6s on t6s.a = t5s.b
5423 );
5424
5425 select count(*)
5426 from t1
5427 join t1 as t2 on t2.a = t1.b
5428 join t1 as t3 on t3.a = t2.b
5429 where t2.c IN (
5430 select /*+ SEMIJOIN(DUPSWEEDOUT)*/ t4s.c from t1 as t4s
5431 join t1 as t5s on t5s.a = t4s.b
5432 join t1 as t6s on t6s.a = t5s.b
5433 );
5434
5435
5436 explain
5437 select count(*)
5438 from t1
5439 join t1 as t2 on t2.a = t1.b
5440 join t1 as t3 on t3.a = t2.b
5441 where t3.c IN (
5442 select /*+ SEMIJOIN(DUPSWEEDOUT)*/ t4s.c from t1 as t4s
5443 join t1 as t5s on t5s.a = t4s.b
5444 join t1 as t6s on t6s.a = t5s.b
5445 );
5446
5447 select count(*)
5448 from t1
5449 join t1 as t2 on t2.a = t1.b
5450 join t1 as t3 on t3.a = t2.b
5451 where t3.c IN (
5452 select /*+ SEMIJOIN(DUPSWEEDOUT)*/ t4s.c from t1 as t4s
5453 join t1 as t5s on t5s.a = t4s.b
5454 join t1 as t6s on t6s.a = t5s.b
5455 );
5456
5457
5458 --error 0,1193
5459 set global debug=@save_debug;
5460
5461 set optimizer_switch=@save_optimizer_switch;
5462
5463 ###################################################
5464 # Bug#27022925
5465 # SPJ: 'MATERIALIZED' SEMI JOINS ARE NOT CONSIDDERED FOR JOIN PUSHOWN
5466 #
5467 # Joins entirely inside a 'MATERIALIZED' semi join
5468 # not considdered for join pushdown.
5469 #
5470 # Test cases should demonstrate that the 't3s join t4s'
5471 # subqueries can now be pushed down, and materialized.
5472 #
5473 ###################################################
5474
5475 --echo ###########
5476 --echo # WL#14370: EXPLAIN need to use 'format=tree as table access order
5477 --echo # in MATERIALIZED execution is not currectly represented by
5478 --echo # the traditional explain.
5479 --echo ###########
5480
5481 let $query =
5482 select count(*)
5483 from t1
5484 join t1 as t2 on t2.a = t1.b
5485 where t1.c IN (
5486 select /*+ SEMIJOIN(MATERIALIZATION)*/ t3s.c from t1 as t3s
5487 join t1 as t4s on t4s.a = t3s.b);
5488
5489 --replace_regex / \(cost=.*//
5490 eval explain format=tree $query;
5491 eval $query;
5492
5493
5494 let $query =
5495 select count(*)
5496 from t1
5497 join t1 as t2 on t2.a = t1.b
5498 where t1.c IN(
5499 select /*+ SEMIJOIN(MATERIALIZATION)*/ t3s1.c from t1 as t3s1
5500 join t1 as t4s1 on t4s1.a = t3s1.b
5501 )
5502 and t1.d IN (
5503 select /*+ SEMIJOIN(MATERIALIZATION)*/ t3s2.c from t1 as t3s2
5504 join t1 as t4s2 on t4s2.a = t3s2.b
5505 );
5506
5507 --replace_regex / \(cost=.*//
5508 eval explain format=tree $query;
5509 eval $query;
5510
5511 #############################################
5512 # Subqueries-end
5513 #############################################
5514
5515
5516 ###################################################
5517 # Bug#33416308
5518 # Incorrect result for pushed join in combination with batched_key_access
5519 #
5520 # SPJ API / handler integration didn't implement retrieval of the
5521 # RANGE_NO pseudo column. Thus, when MRR specified multiple Bound's,
5522 # and that MRR operation was the root of a pushed join, we failed
5523 # to related the returned SPJ rows with the correct 'Batched Key'
5524 #
5525 ##################################################
5526
5527 # We use optimizer hints to ensure:
5528 # - t2 is BKA joined (using MRR) with t1.
5529 # - t3 is _not_ BKA joined with t2, thus enabling
5530 # t2 join t3 to be pushed.
5531 #
5532 # 't1 BKA_JOIN t2' will use MRR access for the
5533 # pushed join root t2, which will need RANGE_NO's from t2 results
5534 # in order to relate them to the correct batched t1 row.
5535 #
5536 explain
5537 select /*+ BKA(t2) NO_BKA(t3) */ t1.a, t2.a, t3.a
5538 from t1
5539 straight_join t1 as t2 on t2.a = t1.c
5540 straight_join t1 as t3 on t3.a = t2.c and t3.b=t2.d;
5541 --sorted_result
5542 select /*+ BKA(t2) NO_BKA(t3) */ t1.a, t2.a, t3.a
5543 from t1
5544 straight_join t1 as t2 on t2.a = t1.c
5545 straight_join t1 as t3 on t3.a = t2.c and t3.b=t2.d;
5546
5547 # Variant where t3 join condition refers t1.
5548 # That will hit an assert(key_cmp... added as part of patch
5549 explain
5550 select /*+ BKA(t2) NO_BKA(t3) */ t1.a, t2.a, t3.a
5551 from t1
5552 straight_join t1 as t2 on t2.a = t1.c
5553 straight_join t1 as t3 on t3.a = t1.c and t3.b=t2.d;
5554 --sorted_result
5555 select /*+ BKA(t2) NO_BKA(t3) */ t1.a, t2.a, t3.a
5556 from t1
5557 straight_join t1 as t2 on t2.a = t1.c
5558 straight_join t1 as t3 on t3.a = t1.c and t3.b=t2.d;
5559
5560 connection ddl;
5561 drop table t1;
5562
5563 ##############
5564 # 16999886 ORDER BY DOESN'T WORK WITH JOIN
5565 # (Limited to pushed join + DESC order only)
5566
5567 connection ddl;
5568 CREATE TABLE ndb_order_test (
5569 node_id int(10) unsigned NOT NULL,
5570 user_id int(10) unsigned NOT NULL,
5571 sort_number int(10) unsigned NOT NULL,
5572 KEY node_id (node_id,sort_number)
5573 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8
5574 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
5575
5576 CREATE TABLE ndb_user_test (
5577 user_id int(10) unsigned NOT NULL AUTO_INCREMENT,
5578 name varchar(20) NOT NULL,
5579 PRIMARY KEY (user_id)
5580 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8 AUTO_INCREMENT=2
5581 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
5582
5583
5584 connection spj;
5585 INSERT INTO ndb_order_test (node_id, user_id, sort_number) VALUES
5586 (68, 1, 1398029),
5587 (68, 1, 549053);
5588
5589 INSERT INTO ndb_user_test (user_id, name) VALUES
5590 (1, 'Shawn');
5591
5592
5593 EXPLAIN
5594 SELECT *
5595 FROM ndb_order_test JOIN ndb_user_test USING (user_id)
5596 WHERE node_id = 68
5597 ORDER BY sort_number DESC;
5598
5599 SELECT *
5600 FROM ndb_order_test JOIN ndb_user_test USING (user_id)
5601 WHERE node_id = 68
5602 ORDER BY sort_number DESC;
5603
5604 EXPLAIN
5605 SELECT *
5606 FROM ndb_order_test JOIN ndb_user_test USING (user_id)
5607 WHERE node_id = 68
5608 ORDER BY sort_number ASC;
5609
5610 SELECT *
5611 FROM ndb_order_test JOIN ndb_user_test USING (user_id)
5612 WHERE node_id = 68
5613 ORDER BY sort_number ASC;
5614
5615 connection ddl;
5616 DROP TABLE ndb_order_test, ndb_user_test;
5617
5618
5619 --echo ##################
5620 --echo #
5621 --echo # Bug #29501263 CRASH IN SPJ BLOCK, ILLEGAL ACCESS
5622 --echo # TO ARRAYPOOL<T>::GETPTR
5623 --echo #
5624 --echo ##################
5625
5626 create table t1 (
5627 pk int primary key,
5628 i1 int
5629 ) engine = ndb;
5630
5631 insert into t1 values (1,1),(2,2),(3,3);
5632
5633 alter table t1 add column (i2 int, i3 int, i4 int, i5 int);
5634 update t1 set i2=i1, i3=i1, i4=i1, i5=i1;
5635
5636 alter table t1 add index ix1(i1);
5637 alter table t1 add index ix2(i2);
5638 alter table t1 add index ix3(i3);
5639 alter table t1 add index ix4(i4);
5640 alter table t1 add index ix5(i5);
5641 alter table t1 add unique index uix(i4,i5);
5642
5643 --sorted_result
5644 select straight_join * from
5645 t1
5646 join t1 as t2 on t2.i1 = t1.i1
5647 join t1 as t3 on t3.pk = t2.i2
5648 left join t1 as t4 on t4.pk = t3.i3
5649 left join t1 as t5 on t5.i4 = t2.i4 and t5.i5 = t1.i5;
5650
5651 drop table t1;
5652
5653
5654 --echo ##################
5655 --echo #
5656 --echo # Bug#32203548 REQUIRE_FAILED WHEN COMPARE_NDBRECORD()
5657 --echo # SORT-MERGE SPJ RESULTS FROM INDEX
5658 --echo #
5659 --echo ##################
5660
5661 create table t1 (
5662 a int not null,
5663 b int not null,
5664 c varchar(1) not null,
5665 d varchar(1) not null,
5666 primary key (a,b),
5667 index ix2 (d,c)
5668 ) engine=ndbcluster;
5669
5670 # Note that we insert all "1" into column d, such that
5671 # the merge sort on index 'ix2 (d,c)' will always find
5672 # column 'd' equal, and continue comparing column 'c'
5673 # if we do not break out of compare loop for columns not
5674 # selected.
5675 #
5676 insert into t1 values
5677 (1,1,"1","1"), (2,2,"2","1"), (3,3,"3","1"), (4,4,"4","1"),
5678 (1,2,"5","1"), (1,3,"1","1"), (1,4,"2","1"),
5679 (2,1,"3","1"), (2,3,"4","1"), (2,4,"5","1");
5680
5681 # We force index 'ix2' to be used for 'order by'
5682 explain
5683 select t1.d from t1 FORCE INDEX FOR ORDER BY (ix2)
5684 straight_join t1 as t2 on t2.a = t1.a
5685 order by t1.d;
5686
5687 select t1.d from t1 FORCE INDEX FOR ORDER BY (ix2)
5688 straight_join t1 as t2 on t2.a = t1.a
5689 order by t1.d;
5690
5691 drop table t1;
5692
5693 --echo ##################
5694 --echo #
5695 --echo # Specific WL-14370, 'AccessPath' tests
5696 --echo # (Unknown if/how this worked pre-wl-14370)
5697 --echo #
5698 --echo ##################
5699
5700 --echo Testcase for AccessPath::NESTED_LOOP_SEMIJOIN_WITH_DUPLICATE_REMOVAL
5701 --echo Expect explain: 'Nested loop semijoin with duplicate removal on...'
5702 --echo (Hardly ever used unless forced to...)
5703
5704
5705 --echo Testcase for AccessPath::WINDOWING
5706 # (Picked from main.subquery_scalar_to_derived_correlation.test)
5707
5708 create table t2(a int) engine=ndbcluster;
5709 insert into t2 values(1), (2);
5710 create table t3(a int, b int) engine=ndbcluster;
5711 insert into t3 values(1, 3), (2, 3);
5712
5713 let $query =
5714 select a,
5715 (select sum(a) over w from t2 window w as(order by t3.a) limit 1)
5716 from t3;
5717
5718 --replace_regex / \(cost=.*//
5719 eval explain format=tree $query;
5720 --sorted_result
5721 eval $query;
5722
5723 drop table t2,t3;
5724
5725 #WL#14370: Query_expression with multiple Query_blocks:
5726 # (Testing Accesspath::APPEND of multiple STREAMs.)
5727 # The constructed AQP::Join_plan is limited to handling single
5728 # Query_blocks, which is implemented by just iterating over the
5729 # 'block' in the Query_expression. Thus we dont have to handle
5730 # Accesspath::APPEND and multiple 'blocks' in the 'plan'.
5731 #
5732
5733 create table t1 (
5734 a int not null,
5735 b int not null,
5736 c int not null,
5737 d int not null,
5738 primary key (`a`,`b`)
5739 ) engine=ndbcluster
5740 partition by key() partitions 8;
5741
5742 insert into t1 values
5743 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
5744 (1,2,5,1), (1,3,1,2), (1,4,2,3),
5745 (2,1,3,4), (2,3,4,5), (2,4,5,1),
5746 (3,1,1,2), (3,2,2,3), (3,4,3,4),
5747 (4,1,4,5), (4,2,5,1), (4,3,1,2);
5748
5749 let $query =
5750 select *
5751 from t1
5752 straight_join t1 as t2 on t2.a = t1.b+0 and t2.b = t1.c
5753 straight_join t1 as t3 on t3.a = t1.b and t3.b = t2.b
5754 where t1.a=1 and t1.d=1;
5755
5756 --replace_regex / \(cost=.*//
5757 eval explain format=tree $query union all $query;
5758 --sorted_result
5759 eval $query union all $query;
5760
5761 --replace_regex / \(cost=.*//
5762 eval explain format=tree $query union $query;
5763 --sorted_result
5764 eval $query union $query;
5765
5766 drop table t1;
5767
5768 --echo ##################
5769 --echo #
5770 --echo # Bug#33669039 Stale rows returned from SPJ API,
5771 --echo # hit assert(key_cmp_if_same(...)) in debug build
5772 --echo #
5773 --echo ##################
5774
5775 create table t (
5776 pk int primary key,
5777 a int,
5778 b int,
5779 c int,
5780 d int
5781 ) engine = ndbcluster;
5782
5783
5784 ##############################
5785 # The inserted rows, in combination with query,
5786 # will do a self-join between all rows, except
5787 # the row where pk=7, containing a=-1
5788 insert into t values (0,0,0,0,0);
5789 insert into t values (1,1,1,1,1);
5790 insert into t values (2,2,2,2,2);
5791 insert into t values (3,3,3,3,3);
5792 insert into t values (4,4,4,4,4);
5793 insert into t values (5,5,5,5,5);
5794 insert into t values (6,6,6,6,6);
5795 insert into t values (7,-1,7,7,7);
5796 insert into t values (8,8,8,8,8);
5797 insert into t values (9,9,9,9,9);
5798 insert into t values (10,10,10,10,10);
5799 insert into t values (11,11,11,11,11);
5800 insert into t values (12,12,12,12,12);
5801 insert into t values (13,13,13,13,13);
5802 insert into t values (14,14,14,14,14);
5803 insert into t values (15,15,15,15,15);
5804
5805
5806 ##############################
5807 # The query below has the join nests t1 oj (t2,t3) oj (t4,t5). In addition
5808 # There are join condition dependencies between the (t4,t5) nests, and t3
5809 # in the other outer-nest.
5810 # When the iterator executor evaluate the join for the row with pk=7 it
5811 # will not find any t2 rows where t2.pk = t1.a. This will also conclude
5812 # with a 'non match' for the nest (t2,t3)... *without* a new t3 row needed
5813 # to be read.
5814 # The iterator executor will then continue with trying to find matches
5815 # for the (t4,t5) nest. This nest will have join dependencies on t3, which
5816 # now has an old (invalid) 'current row' as read from t3 was skipped in the
5817 # previous step.
5818 #
5819 let $query=
5820 select t1.pk, t2.pk, t3.pk, t4.pk, t5.pk
5821 from t as t1
5822 left join (
5823 t as t2
5824 straight_join t as t3 on t3.pk = t2.b
5825 ) on t2.pk = t1.a
5826 left join (
5827 t as t4
5828 straight_join t as t5
5829 ) on t4.pk = t1.c and t5.pk = t3.d;
5830
5831 --replace_regex / \(cost=.*//
5832 eval explain format=tree $query;
5833 --sorted_result
5834 eval $query;
5835
5836 drop table t;
5837
5838
5839 --echo ##################
5840 --echo #
5841 --echo # Bug#34482783 NDB: mysqld crash in ::is_pushable_within_nest()
5842 --echo #
5843 --echo ##################
5844
5845 create table t (
5846 pk int primary key,
5847 col_int_key int not null,
5848 col_int int not null,
5849 key(col_int_key)
5850 ) engine = ndbcluster;
5851
5852 # No rows needed in table to provoke bug
5853
5854 let $query=
5855 SELECT *
5856 FROM t AS table1
5857 LEFT JOIN t AS table2
5858 JOIN t AS table3 ON table2.col_int IS NULL # <- 'zero rows'
5859 ON table1.col_int = table2.col_int AND
5860 table1.col_int_key = table2.col_int_key AND
5861 table1.pk = table3.pk;
5862
5863 --replace_regex / \(cost=.*//
5864 eval explain format=tree $query;
5865 --sorted_result
5866 eval $query;
5867
5868 drop table t;
5869
5870
5871 --echo ##################
5872 --echo #
5873 --echo # Bug#34723413 A 'pushed join' should not start with a scan-table
5874 --echo # returning very few rows .
5875 --echo #
5876 --echo ##################
5877
5878 create table t10 (
5879 k int not null auto_increment,
5880 i int,
5881 primary key(k),
5882 key(i)
5883 ) engine = ndbcluster;
5884
5885 insert into t10(i) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
5886
5887 create table t100 like t10;
5888 insert into t100(i)
5889 select x.i from t10 as x, t10 as y;
5890
5891 create table t1000 like t10;
5892 insert into t1000(i)
5893 select x.i from t10 as x, t10 as y, t10 as z;
5894
5895 # Make t10 even smaller, increase join-fanout on 'i' for t100 and t1000
5896 delete from t10 where i > 2;
5897 update t100 set i = i/3;
5898 update t1000 set i = i/3;
5899
5900 analyze table t10,t100,t1000;
5901
5902 ############################
5903 # We could have pushed 't10 join t1000 join t100'
5904 # However the small table t10 might not deliver rows to all SPJ workers,
5905 # leaving some of them idle. Thus it will usually be better to :
5906 # - Fetch t10 rows
5907 # - For every t10 row: Push 't1000 join t100'
5908 #
5909 # Expect the explain-warning:
5910 #
5911 # Didn't push table 't10' as root, too few rows to enable full parallelism
5912 #
5913 --replace_regex / \(cost=.*//
5914 explain format=tree
5915 select *
5916 from t10
5917 straight_join t1000 on t1000.i = t10.i
5918 straight_join t100 on t100.k = t1000.k;
5919
5920 ############################
5921 # However:
5922 # Table 't100' is an ancestor of t10, and can't be made
5923 # and t1000 ancestor. Not pushing t10 will yield t1000 and t100
5924 # unpushed as well -> Push all 3 tables as first planned.
5925 #
5926 --replace_regex / \(cost=.*//
5927 explain format=tree select *
5928 from t10
5929 straight_join t1000 on t1000.i = t10.i
5930 straight_join t100 on t100.k = t10.k;
5931
5932 drop table t10,t100,t1000;
5933
5934
5935 --echo ##################
5936 --echo #
5937 --echo # Bug#34782276:
5938 --echo # Improve SPJ-internal execution plan, evaluate inner-joins first
5939 --echo #
5940 --echo ##################
5941 #
5942 # The SPJ block will build its own query plan where it will evaluate
5943 # inner-joins first. Even in a star-join plan the joins will be evaluated
5944 # in sequence and found matches noted in the common ancestor. Later joins
5945 # will be skipped if some of the previously evaluated joins didn't find
5946 # a match. (It is a property of the inner-join that matches are required
5947 # for all tables in the same inner join 'nest').
5948 # This will reduce the work needed to be performed by the data nodes
5949 # in later table REQuests.
5950 #
5951 # Test case is the query tree: t1
5952 # / \
5953 # t2 t3
5954 #
5955 # There are matches for all rows in the join(t1,t3). However only 3 of the 9
5956 # rows will match in join(t1,t2). The SPJ block will create its own execution
5957 # Plan where:
5958 # 1) join(t1,t2), Annotate t1 rows where matches are found.
5959 # 2) Generate t3 requests from buffered t1 rows having matches.
5960 # -> There should only be 3 keys in the REQuest sent to t3
5961
5962 create table t (
5963 k int,
5964 a int,
5965 key ix1(k),
5966 key ix2(a)
5967 ) engine = ndb;
5968
5969 insert into t values
5970 (0,0), (1,1), (2,2),
5971 (3,0), (4,1), (5,2),
5972 (6,0), (7,1), (8,2);
5973
5974 let $old_val = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name = 'SCAN_ROWS_RETURNED', Value, 1);
5975
5976 # This test case is just for reference - Worked as expected even before bug fix.
5977 let $query=
5978 select straight_join *
5979 from t as t1
5980 inner join t as t2 on t2.a = t1.k
5981 inner join t as t3 on t3.k = t1.k;
5982
5983 --replace_regex / \(cost=.*//
5984 eval explain format=tree $query;
5985
5986 let $old_val = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name = 'SCAN_ROWS_RETURNED', Value, 1);
5987
5988 --disable_result_log
5989 eval $query;
5990 --enable_result_log
5991
5992 --echo ############
5993 --echo # Verify the expect 21 rows were returned from SPJ:
5994 --echo # - 2 x 9 rows from t1 inner join t2
5995 --echo # - 3 rows from t1 join t3
5996 --echo # -> 21 rows (0 *SPJ* rows when not join-pushed (BKA))
5997 --echo #
5998 --disable_query_log
5999 --eval select sum(val) - $old_val as SPJ_rows_returned from ndbinfo.counters where block_name="DBSPJ" and counter_name = "SCAN_ROWS_RETURNED"
6000 --enable_query_log
6001
6002
6003 ##### Same test case with outer join:
6004 #
6005 # Should stil evaluate t1 join t2 first.
6006 # A non-match on t2 will eliminate the inner joined t1 rows.
6007 # Thus, t1 left join t3 may be skipped for t1 rows not having t2 matches
6008 #
6009 let $query=
6010 select straight_join *
6011 from t as t1
6012 inner join t as t2 on t2.a = t1.k
6013 left join t as t3 on t3.k = t1.k;
6014
6015 --replace_regex / \(cost=.*//
6016 eval explain format=tree $query;
6017
6018 let $old_val = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name = 'SCAN_ROWS_RETURNED', Value, 1);
6019
6020 --disable_result_log
6021 eval $query;
6022 --enable_result_log
6023
6024 --echo ############
6025 --echo # As above, t1 inner join t2 is evaluated first
6026 --echo # - 2 x 9 rows from join(t1,t2)
6027 --echo # - Only the 3 matching t1 rows are requested from t3!
6028 --echo # -> still 21 rows (and still 0 *SPJ* rows when not join-pushed (BKA))
6029 --echo #
6030 --disable_query_log
6031 --eval select sum(val) - $old_val as SPJ_rows_returned from ndbinfo.counters where block_name="DBSPJ" and counter_name = "SCAN_ROWS_RETURNED";
6032 --enable_query_log
6033
6034
6035 ##### Same test case with outer join evaluated before inner join:
6036 #
6037 # There is a legacy limitation in the SPJ API protocol, where scans need
6038 # to be evaluated in the same order as specified in the optimizer table order.
6039 # Test case verify that we do *not* skip t3 rows in these cases.
6040 # (As t1 left join t2 will not eliminate non matching t1 rows and we cant evaluate
6041 # t1 inner join t3 first due to legacy limitation.)
6042 #
6043 let $query=
6044 select straight_join *
6045 from t as t1
6046 left join t as t2 on t2.a = t1.k
6047 inner join t as t3 on t3.k = t1.k;
6048
6049 --replace_regex / \(cost=.*//
6050 eval explain format=tree $query;
6051
6052 let $old_val = query_get_value(select sum(val) as Value from ndbinfo.counters where block_name='DBSPJ' and counter_name = 'SCAN_ROWS_RETURNED', Value, 1);
6053
6054 --disable_result_log
6055 eval $query;
6056 --enable_result_log
6057
6058 --echo ############
6059 --echo # Verify that all 3 x 9 rows were returned from SPJ:
6060 --echo # - 2 x 9 rows from t1 outer join t2
6061 --echo # (No t1-match elimination in an outer-join)
6062 --echo # - 9 rows from t1 inner join t3
6063 --echo # -> 27 rows
6064 --echo #
6065 --disable_query_log
6066 --eval select sum(val) - $old_val as SPJ_rows_returned from ndbinfo.counters where block_name="DBSPJ" and counter_name = "SCAN_ROWS_RETURNED"
6067 --enable_query_log
6068
6069 drop table t;
6070
6071
6072 ########################################
6073 # Verify counters for entire test:
6074 # Note: These tables are 'temporary' withing 'connection spj'
6075
6076 connection spj;
6077
6078 # Calculate the change in mysqld counters.
6079 select new.variable_name, new.variable_value - old.variable_value
6080 from server_counts_at_startup as old,
6081 performance_schema.global_status as new
6082 where new.variable_name = old.variable_name
6083 order by new.variable_name;
6084
6085 drop table server_counts_at_startup;
6086
6087 set ndb_join_pushdown = @save_ndb_join_pushdown;
6088