Debian Codegraph @

mysql-test/suite/ndb/t/ndb_bug30573733.test

raw
1 ##################################################################
2 ##
3 ## Test case for bug#30573733
4 ## INCORRECT RESULT IF HASH-JOIN USE 'SPILL_TO_DISK' FOR A PUSHED JOIN
5 ##
6 ## The 'spill_to_disk' strategy must be avoided when hash join is used
7 ## to join any tables being a member of a pushed join.
8 ##
9
10 -- source include/have_ndb.inc
11
12
13 CREATE TABLE t (
14 pk int NOT NULL,
15 col_int int NOT NULL,
16 PRIMARY KEY (`pk`)
17 ) ENGINE=ndbcluster;
18
19 INSERT INTO t VALUES
20 (19, 4),
21 (12, 36),
22 (5, 47),
23 (7, 23),
24 (3, 7),
25 (9, 16),
26 (21, 48),
27 (22, 10),
28 (10, 27),
29 (14, 17),
30 (11, 4),
31 (18, 6),
32 (20, 39),
33 (1, 32),
34 (15, 49),
35 (13, 18),
36 (25, 21),
37 (6, 6),
38 (23, 3),
39 (2, 16),
40 (17, 13),
41 (16, 0),
42 (4, 2),
43 (24, 23),
44 (8, 7);
45
46
47 #############################################################
48 ## Query do a cross product of first 4 tables in order to create
49 ## an intermediate join result not fitting in the join buffer.
50 ## The 'Inner hash join' algorithm will be used to join t1..t4
51 ##
52 ## The join of t4 and t5 should be executed as a 'pushed join', requiring
53 ## t5 rows to be read while we have the t4 rows it depends on as 'current'.
54 ## Thus, we must avoid that t4 rows are 'spilled_to_disk' during the
55 ## hash join with t3.
56
57 let $query=
58 SELECT
59 STRAIGHT_JOIN count(*)
60 FROM
61 t AS t1, t AS t2, t as t3, t as t4
62 JOIN t as t5 ON t5.pk = t4.col_int
63 ;
64
65 eval EXPLAIN $query;
66 eval EXPLAIN FORMAT=TREE $query;
67
68 set join_buffer_size = 128;
69 --sorted_result
70 eval $query;
71
72 set join_buffer_size = 1024;
73 --sorted_result
74 eval $query;
75
76 set join_buffer_size = 2048;
77 --sorted_result
78 eval $query;
79
80 set join_buffer_size = 4096;
81 --sorted_result
82 eval $query;
83
84 set join_buffer_size = 16000;
85 --sorted_result
86 eval $query;
87
88 set join_buffer_size = 64000;
89 --sorted_result
90 eval $query;
91
92 set join_buffer_size = 256000;
93 --sorted_result
94 eval $query;
95
96 set join_buffer_size = 1024000;
97 --sorted_result
98 eval $query;
99
100
101 set join_buffer_size = default;
102
103 DROP TABLE t;