2 # A simple test of the greedy query optimization algorithm and the switches that
3 # control the optimizationprocess.
10 drop table if exists t1,t2,t3,t4,t5,t6,t7;
14 c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer,
18 c21 integer,c22 integer,c23 integer,c24 integer,c25 integer,c26 integer
21 c31 integer,c32 integer,c33 integer,c34 integer,c35 integer,c36 integer,
25 c41 integer,c42 integer,c43 integer,c44 integer,c45 integer,c46 integer
28 c51 integer,c52 integer,c53 integer,c54 integer,c55 integer,c56 integer,
32 c61 integer,c62 integer,c63 integer,c64 integer,c65 integer,c66 integer
35 c71 integer,c72 integer,c73 integer,c74 integer,c75 integer,c76 integer,
41 # cardinality(Ti) = cardinality(T(i-1)) + 3
43 insert into t1 values (1,2,3,4,5,6);
44 insert into t1 values (2,2,3,4,5,6);
45 insert into t1 values (3,2,3,4,5,6);
47 insert into t2 values (1,2,3,4,5,6);
48 insert into t2 values (2,2,3,4,5,6);
49 insert into t2 values (3,2,3,4,5,6);
50 insert into t2 values (4,2,3,4,5,6);
51 insert into t2 values (5,2,3,4,5,6);
52 insert into t2 values (6,2,3,4,5,6);
54 insert into t3 values (1,2,3,4,5,6);
55 insert into t3 values (2,2,3,4,5,6);
56 insert into t3 values (3,2,3,4,5,6);
57 insert into t3 values (4,2,3,4,5,6);
58 insert into t3 values (5,2,3,4,5,6);
59 insert into t3 values (6,2,3,4,5,6);
60 insert into t3 values (7,2,3,4,5,6);
61 insert into t3 values (8,2,3,4,5,6);
62 insert into t3 values (9,2,3,4,5,6);
64 insert into t4 values (1,2,3,4,5,6);
65 insert into t4 values (2,2,3,4,5,6);
66 insert into t4 values (3,2,3,4,5,6);
67 insert into t4 values (4,2,3,4,5,6);
68 insert into t4 values (5,2,3,4,5,6);
69 insert into t4 values (6,2,3,4,5,6);
70 insert into t4 values (7,2,3,4,5,6);
71 insert into t4 values (8,2,3,4,5,6);
72 insert into t4 values (9,2,3,4,5,6);
73 insert into t4 values (10,2,3,4,5,6);
74 insert into t4 values (11,2,3,4,5,6);
75 insert into t4 values (12,2,3,4,5,6);
77 insert into t5 values (1,2,3,4,5,6);
78 insert into t5 values (2,2,3,4,5,6);
79 insert into t5 values (3,2,3,4,5,6);
80 insert into t5 values (4,2,3,4,5,6);
81 insert into t5 values (5,2,3,4,5,6);
82 insert into t5 values (6,2,3,4,5,6);
83 insert into t5 values (7,2,3,4,5,6);
84 insert into t5 values (8,2,3,4,5,6);
85 insert into t5 values (9,2,3,4,5,6);
86 insert into t5 values (10,2,3,4,5,6);
87 insert into t5 values (11,2,3,4,5,6);
88 insert into t5 values (12,2,3,4,5,6);
89 insert into t5 values (13,2,3,4,5,6);
90 insert into t5 values (14,2,3,4,5,6);
91 insert into t5 values (15,2,3,4,5,6);
93 insert into t6 values (1,2,3,4,5,6);
94 insert into t6 values (2,2,3,4,5,6);
95 insert into t6 values (3,2,3,4,5,6);
96 insert into t6 values (4,2,3,4,5,6);
97 insert into t6 values (5,2,3,4,5,6);
98 insert into t6 values (6,2,3,4,5,6);
99 insert into t6 values (7,2,3,4,5,6);
100 insert into t6 values (8,2,3,4,5,6);
101 insert into t6 values (9,2,3,4,5,6);
102 insert into t6 values (10,2,3,4,5,6);
103 insert into t6 values (11,2,3,4,5,6);
104 insert into t6 values (12,2,3,4,5,6);
105 insert into t6 values (13,2,3,4,5,6);
106 insert into t6 values (14,2,3,4,5,6);
107 insert into t6 values (15,2,3,4,5,6);
108 insert into t6 values (16,2,3,4,5,6);
109 insert into t6 values (17,2,3,4,5,6);
110 insert into t6 values (18,2,3,4,5,6);
112 insert into t7 values (1,2,3,4,5,6);
113 insert into t7 values (2,2,3,4,5,6);
114 insert into t7 values (3,2,3,4,5,6);
115 insert into t7 values (4,2,3,4,5,6);
116 insert into t7 values (5,2,3,4,5,6);
117 insert into t7 values (6,2,3,4,5,6);
118 insert into t7 values (7,2,3,4,5,6);
119 insert into t7 values (8,2,3,4,5,6);
120 insert into t7 values (9,2,3,4,5,6);
121 insert into t7 values (10,2,3,4,5,6);
122 insert into t7 values (11,2,3,4,5,6);
123 insert into t7 values (12,2,3,4,5,6);
124 insert into t7 values (13,2,3,4,5,6);
125 insert into t7 values (14,2,3,4,5,6);
126 insert into t7 values (15,2,3,4,5,6);
127 insert into t7 values (16,2,3,4,5,6);
128 insert into t7 values (17,2,3,4,5,6);
129 insert into t7 values (18,2,3,4,5,6);
130 insert into t7 values (19,2,3,4,5,6);
131 insert into t7 values (20,2,3,4,5,6);
132 insert into t7 values (21,2,3,4,5,6);
135 # The actual test begins here
138 # Check the default values for the optimizer paramters
140 select @@optimizer_search_depth;
141 select @@optimizer_prune_level;
143 # This value swithes back to the old implementation of 'find_best()'
144 # set optimizer_search_depth=63; - old (independent of the optimizer_prune_level)
146 # These are the values for the parameters that control the greedy optimizer
147 # (total 6 combinations - 3 for optimizer_search_depth, 2 for optimizer_prune_level):
149 # set optimizer_search_depth=0; - automatic
150 # set optimizer_search_depth=1; - min
151 # set optimizer_search_depth=62; - max (default)
153 # set optimizer_prune_level=0 - exhaustive;
154 # set optimizer_prune_level=1 - heuristic; # default
158 # Compile several queries with all combinations of the query
159 # optimizer parameters. Each test query has two variants, where
160 # in the second variant the tables in the FROM clause are in
161 # inverse order to the tables in the first variant.
162 # Due to pre-sorting of tables before compilation, there should
163 # be no difference in the plans for each two such query variants.
166 # First, for reference compile the test queries with the 'old' optimization
167 # procedure 'find_best'. Notice that 'find_best' does not depend on the
168 # choice of heuristic.
170 set optimizer_search_depth=63;
171 select @@optimizer_search_depth;
173 # 6-table join, chain
174 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
175 show status like 'Last_query_cost';
176 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
177 show status like 'Last_query_cost';
179 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
180 show status like 'Last_query_cost';
181 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
182 show status like 'Last_query_cost';
183 # 6-table join, clique
184 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
185 show status like 'Last_query_cost';
186 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
187 show status like 'Last_query_cost';
190 # Test the new optimization procedures
192 set optimizer_prune_level=0;
193 select @@optimizer_prune_level;
195 set optimizer_search_depth=0;
196 select @@optimizer_search_depth;
198 # 6-table join, chain
199 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
200 show status like 'Last_query_cost';
201 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
202 show status like 'Last_query_cost';
204 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
205 show status like 'Last_query_cost';
206 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
207 show status like 'Last_query_cost';
208 # 6-table join, clique
209 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
210 show status like 'Last_query_cost';
211 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
212 show status like 'Last_query_cost';
214 set optimizer_search_depth=1;
215 select @@optimizer_search_depth;
217 # 6-table join, chain
218 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
219 show status like 'Last_query_cost';
220 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
221 show status like 'Last_query_cost';
223 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
224 show status like 'Last_query_cost';
225 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
226 show status like 'Last_query_cost';
227 # 6-table join, clique
228 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
229 show status like 'Last_query_cost';
230 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
231 show status like 'Last_query_cost';
233 set optimizer_search_depth=62;
234 select @@optimizer_search_depth;
236 # 6-table join, chain
237 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
238 show status like 'Last_query_cost';
239 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
240 show status like 'Last_query_cost';
242 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
243 show status like 'Last_query_cost';
244 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
245 show status like 'Last_query_cost';
246 # 6-table join, clique
247 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
248 show status like 'Last_query_cost';
249 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
250 show status like 'Last_query_cost';
253 set optimizer_prune_level=1;
254 select @@optimizer_prune_level;
256 set optimizer_search_depth=0;
257 select @@optimizer_search_depth;
259 # 6-table join, chain
260 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
261 show status like 'Last_query_cost';
262 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
263 show status like 'Last_query_cost';
265 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
266 show status like 'Last_query_cost';
267 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
268 show status like 'Last_query_cost';
269 # 6-table join, clique
270 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
271 show status like 'Last_query_cost';
272 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
273 show status like 'Last_query_cost';
275 set optimizer_search_depth=1;
276 select @@optimizer_search_depth;
278 # 6-table join, chain
279 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
280 show status like 'Last_query_cost';
281 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
282 show status like 'Last_query_cost';
284 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
285 show status like 'Last_query_cost';
286 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
287 show status like 'Last_query_cost';
288 # 6-table join, clique
289 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
290 show status like 'Last_query_cost';
291 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
292 show status like 'Last_query_cost';
294 set optimizer_search_depth=62;
295 select @@optimizer_search_depth;
297 # 6-table join, chain
298 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
299 show status like 'Last_query_cost';
300 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
301 show status like 'Last_query_cost';
303 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
304 show status like 'Last_query_cost';
305 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
306 show status like 'Last_query_cost';
307 # 6-table join, clique
308 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
309 show status like 'Last_query_cost';
310 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
311 show status like 'Last_query_cost';
313 drop table t1,t2,t3,t4,t5,t6,t7;
317 # Bug # 38795: Automatic search depth and nested join's results in server
321 CREATE TABLE t1 (a int, b int, d int, i int); INSERT INTO t1 VALUES (1,1,1,1);
322 CREATE TABLE t2 (b int, c int, j int); INSERT INTO t2 VALUES (1,1,1);
323 CREATE TABLE t2_1 (j int); INSERT INTO t2_1 VALUES (1);
324 CREATE TABLE t3 (c int, f int); INSERT INTO t3 VALUES (1,1);
325 CREATE TABLE t3_1 (f int); INSERT INTO t3_1 VALUES (1);
326 CREATE TABLE t4 (d int, e int, k int); INSERT INTO t4 VALUES (1,1,1);
327 CREATE TABLE t4_1 (k int); INSERT INTO t4_1 VALUES (1);
328 CREATE TABLE t5 (g int, d int, h int, l int); INSERT INTO t5 VALUES (1,1,1,1);
329 CREATE TABLE t5_1 (l int); INSERT INTO t5_1 VALUES (1);
331 SET optimizer_search_depth = 3;
336 t2 JOIN t3 ON t3.c = t2.c
339 t4 JOIN t5 ON t5.d = t4.d
346 t2 LEFT JOIN (t3 JOIN t3_1 ON t3.f = t3_1.f) ON t3.c = t2.c
349 t4 JOIN t5 ON t5.d = t4.d
356 (t2 JOIN t2_1 ON t2.j = t2_1.j) JOIN t3 ON t3.c = t2.c
359 t4 JOIN t5 ON t5.d = t4.d
366 t2 JOIN t3 ON t3.c = t2.c
369 (t4 JOIN t4_1 ON t4.k = t4_1.k) LEFT JOIN t5 ON t5.d = t4.d
376 t2 JOIN t3 ON t3.c = t2.c
379 t4 LEFT JOIN (t5 JOIN t5_1 ON t5.l = t5_1.l) ON t5.d = t4.d
383 SET optimizer_search_depth = DEFAULT;
384 DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
386 --echo End of 5.0 tests