Debian Codegraph @

mysql-test/suite/ndb/r/ndb_bug30573733.result

raw
1 CREATE TABLE t (
2 pk int NOT NULL,
3 col_int int NOT NULL,
4 PRIMARY KEY (`pk`)
5 ) ENGINE=ndbcluster;
6 INSERT INTO t VALUES
7 (19, 4),
8 (12, 36),
9 (5, 47),
10 (7, 23),
11 (3, 7),
12 (9, 16),
13 (21, 48),
14 (22, 10),
15 (10, 27),
16 (14, 17),
17 (11, 4),
18 (18, 6),
19 (20, 39),
20 (1, 32),
21 (15, 49),
22 (13, 18),
23 (25, 21),
24 (6, 6),
25 (23, 3),
26 (2, 16),
27 (17, 13),
28 (16, 0),
29 (4, 2),
30 (24, 23),
31 (8, 7);
32 EXPLAIN SELECT
33 STRAIGHT_JOIN count(*)
34 FROM
35 t AS t1, t AS t2, t as t3, t as t4
36 JOIN t as t5 ON t5.pk = t4.col_int
37 ;
38 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
39 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 25 100.00 NULL
40 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 25 100.00 Using join buffer (hash join)
41 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 25 100.00 Using join buffer (hash join)
42 1 SIMPLE t4 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 25 100.00 Parent of 2 pushed join@1; Using join buffer (hash join)
43 1 SIMPLE t5 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 test.t4.col_int 1 100.00 Child of 't4' in pushed join@1
44 Warnings:
45 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
46 Note 1003 Can't push table 't3' as child, 'type' must be a 'ref' access
47 Note 1003 Can't push table 't4' as child, 'type' must be a 'ref' access
48 Note 1003 Can't push table 't5' as child of 't1', it is in a hash-bucket-branch which can't be referred.
49 Note 1003 Can't push table 't5' as child of 't2', it is in a hash-bucket-branch which can't be referred.
50 Note 1003 Can't push table 't5' as child of 't3', it is in a hash-bucket-branch which can't be referred.
51 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`t` `t1` join `test`.`t` `t2` join `test`.`t` `t3` join `test`.`t` `t4` join `test`.`t` `t5` where (`test`.`t5`.`pk` = `test`.`t4`.`col_int`)
52 EXPLAIN FORMAT=TREE SELECT
53 STRAIGHT_JOIN count(*)
54 FROM
55 t AS t1, t AS t2, t as t3, t as t4
56 JOIN t as t5 ON t5.pk = t4.col_int
57 ;
58 EXPLAIN
59 -> Aggregate: count(0) (cost=143821 rows=1)
60 -> Nested loop inner join (cost=104759 rows=390625)
61 -> Inner hash join (no condition) (cost=65690 rows=390625)
62 -> Table scan on t4, activating pushed join of 2 tables (cost=0.4 rows=25)
63 -> Hash
64 -> Inner hash join (no condition) (cost=20378 rows=15625)
65 -> Table scan on t3 (cost=10 rows=25)
66 -> Hash
67 -> Inner hash join (no condition) (cost=12565 rows=625)
68 -> Table scan on t2 (cost=250 rows=25)
69 -> Hash
70 -> Table scan on t1 (cost=6252 rows=25)
71 -> Single-row index lookup on t5 using PRIMARY (pk=t4.col_int), child of t4 in pushed join (cost=16.3e-6 rows=1)
72
73 Warnings:
74 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
75 Note 1003 Can't push table 't3' as child, 'type' must be a 'ref' access
76 Note 1003 Can't push table 't4' as child, 'type' must be a 'ref' access
77 Note 1003 Can't push table 't5' as child of 't1', it is in a hash-bucket-branch which can't be referred.
78 Note 1003 Can't push table 't5' as child of 't2', it is in a hash-bucket-branch which can't be referred.
79 Note 1003 Can't push table 't5' as child of 't3', it is in a hash-bucket-branch which can't be referred.
80 set join_buffer_size = 128;
81 SELECT
82 STRAIGHT_JOIN count(*)
83 FROM
84 t AS t1, t AS t2, t as t3, t as t4
85 JOIN t as t5 ON t5.pk = t4.col_int
86 ;
87 count(*)
88 265625
89 set join_buffer_size = 1024;
90 SELECT
91 STRAIGHT_JOIN count(*)
92 FROM
93 t AS t1, t AS t2, t as t3, t as t4
94 JOIN t as t5 ON t5.pk = t4.col_int
95 ;
96 count(*)
97 265625
98 set join_buffer_size = 2048;
99 SELECT
100 STRAIGHT_JOIN count(*)
101 FROM
102 t AS t1, t AS t2, t as t3, t as t4
103 JOIN t as t5 ON t5.pk = t4.col_int
104 ;
105 count(*)
106 265625
107 set join_buffer_size = 4096;
108 SELECT
109 STRAIGHT_JOIN count(*)
110 FROM
111 t AS t1, t AS t2, t as t3, t as t4
112 JOIN t as t5 ON t5.pk = t4.col_int
113 ;
114 count(*)
115 265625
116 set join_buffer_size = 16000;
117 SELECT
118 STRAIGHT_JOIN count(*)
119 FROM
120 t AS t1, t AS t2, t as t3, t as t4
121 JOIN t as t5 ON t5.pk = t4.col_int
122 ;
123 count(*)
124 265625
125 set join_buffer_size = 64000;
126 SELECT
127 STRAIGHT_JOIN count(*)
128 FROM
129 t AS t1, t AS t2, t as t3, t as t4
130 JOIN t as t5 ON t5.pk = t4.col_int
131 ;
132 count(*)
133 265625
134 set join_buffer_size = 256000;
135 SELECT
136 STRAIGHT_JOIN count(*)
137 FROM
138 t AS t1, t AS t2, t as t3, t as t4
139 JOIN t as t5 ON t5.pk = t4.col_int
140 ;
141 count(*)
142 265625
143 set join_buffer_size = 1024000;
144 SELECT
145 STRAIGHT_JOIN count(*)
146 FROM
147 t AS t1, t AS t2, t as t3, t as t4
148 JOIN t as t5 ON t5.pk = t4.col_int
149 ;
150 count(*)
151 265625
152 set join_buffer_size = default;
153 DROP TABLE t;