Merge lp:~prafulla-t/drizzle/dr-bug-592444 into lp:~drizzle-trunk/drizzle/development
- dr-bug-592444
- Merge into development
Proposed by
PrafullaT
Status: | Merged |
---|---|
Merged at revision: | 1664 |
Proposed branch: | lp:~prafulla-t/drizzle/dr-bug-592444 |
Merge into: | lp:~drizzle-trunk/drizzle/development |
Diff against target: |
521 lines (+361/-33) 8 files modified
drizzled/item.h (+5/-0) drizzled/join.cc (+65/-20) drizzled/nested_join.h (+10/-0) drizzled/optimizer/range.cc (+5/-2) drizzled/sql_select.cc (+8/-10) drizzled/sql_select.h (+1/-1) tests/r/optimizer.result (+141/-0) tests/t/optimizer.test (+126/-0) |
To merge this branch: | bzr merge lp:~prafulla-t/drizzle/dr-bug-592444 |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Patrick Crews | Approve | ||
Review via email: mp+30229@code.launchpad.net |
Commit message
Merging fixes for bug #592444 , #595305
Description of the change
This branch contains fixes for Bug #592444 , #595305
I have unit-tested this fixes with bug tests.
Patrick has done broad testing for this patches with randgen and usual test-suite
testing.
I have also added the unit-test for both of the bugs
I propose this branch to be merged into trunk.
Thanks
Prafulla
To post a comment you must log in.
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | === modified file 'drizzled/item.h' |
2 | --- drizzled/item.h 2010-03-30 09:29:02 +0000 |
3 | +++ drizzled/item.h 2010-07-19 02:40:58 +0000 |
4 | @@ -732,6 +732,11 @@ |
5 | * cost Item::execution_cost(), |
6 | * where 'cost' is either 'double' or some structure of various cost |
7 | * parameters. |
8 | + * |
9 | + *NOTE |
10 | + * This function is now used to prevent evaluation of materialized IN |
11 | + * subquery predicates before it is allowed. grep for |
12 | + * DontEvaluateMaterializedSubqueryTooEarly to see the uses. |
13 | */ |
14 | virtual bool is_expensive(); |
15 | |
16 | |
17 | === modified file 'drizzled/join.cc' |
18 | --- drizzled/join.cc 2010-07-16 05:40:08 +0000 |
19 | +++ drizzled/join.cc 2010-07-19 02:40:58 +0000 |
20 | @@ -4040,6 +4040,15 @@ |
21 | */ |
22 | join->setPosInPartialPlan(idx, best_pos); |
23 | |
24 | + /* |
25 | + We need to make best_extension_by_limited_search aware of the fact |
26 | + that it's not starting from top level, but from a rather specific |
27 | + position in the list of nested joins. |
28 | + */ |
29 | + check_interleaving_with_nj (best_table); |
30 | + |
31 | + |
32 | + |
33 | /* find the position of 'best_table' in 'join->best_ref' */ |
34 | best_idx= idx; |
35 | JoinTable *pos= join->best_ref[best_idx]; |
36 | @@ -4201,13 +4210,9 @@ |
37 | for (JoinTable **pos= join->best_ref + idx ; (s= *pos) ; pos++) |
38 | { |
39 | table_map real_table_bit= s->table->map; |
40 | - if (idx) |
41 | - { |
42 | - partial_pos= join->getPosFromPartialPlan(idx - 1); |
43 | - } |
44 | if ((remaining_tables & real_table_bit) && |
45 | ! (remaining_tables & s->dependent) && |
46 | - (! idx || ! check_interleaving_with_nj(partial_pos.getJoinTable(), s))) |
47 | + (! idx || ! check_interleaving_with_nj(s))) |
48 | { |
49 | double current_record_count, current_read_time; |
50 | |
51 | @@ -6014,35 +6019,75 @@ |
52 | /** |
53 | Nested joins perspective: Remove the last table from the join order. |
54 | |
55 | + The algorithm is the reciprocal of check_interleaving_with_nj(), hence |
56 | + parent join nest nodes are updated only when the last table in its child |
57 | + node is removed. The ASCII graphic below will clarify. |
58 | + |
59 | + %A table nesting such as <tt> t1 x [ ( t2 x t3 ) x ( t4 x t5 ) ] </tt>is |
60 | + represented by the below join nest tree. |
61 | + |
62 | + @verbatim |
63 | + NJ1 |
64 | + _/ / \ |
65 | + _/ / NJ2 |
66 | + _/ / / \ |
67 | + / / / \ |
68 | + t1 x [ (t2 x t3) x (t4 x t5) ] |
69 | + @endverbatim |
70 | + |
71 | + At the point in time when check_interleaving_with_nj() adds the table t5 to |
72 | + the query execution plan, QEP, it also directs the node named NJ2 to mark |
73 | + the table as covered. NJ2 does so by incrementing its @c counter |
74 | + member. Since all of NJ2's tables are now covered by the QEP, the algorithm |
75 | + proceeds up the tree to NJ1, incrementing its counter as well. All join |
76 | + nests are now completely covered by the QEP. |
77 | + |
78 | + restore_prev_nj_state() does the above in reverse. As seen above, the node |
79 | + NJ1 contains the nodes t2, t3, and NJ2. Its counter being equal to 3 means |
80 | + that the plan covers t2, t3, and NJ2, @e and that the sub-plan (t4 x t5) |
81 | + completely covers NJ2. The removal of t5 from the partial plan will first |
82 | + decrement NJ2's counter to 1. It will then detect that NJ2 went from being |
83 | + completely to partially covered, and hence the algorithm must continue |
84 | + upwards to NJ1 and decrement its counter to 2. %A subsequent removal of t4 |
85 | + will however not influence NJ1 since it did not un-cover the last table in |
86 | + NJ2. |
87 | + |
88 | + SYNOPSIS |
89 | + restore_prev_nj_state() |
90 | + last join table to remove, it is assumed to be the last in current |
91 | + partial join order. |
92 | + |
93 | + DESCRIPTION |
94 | + |
95 | Remove the last table from the partial join order and update the nested |
96 | - joins counters and join->cur_embedding_map. It is ok to call this |
97 | - function for the first table in join order (for which |
98 | + joins counters and join->cur_embedding_map. It is ok to call this |
99 | + function for the first table in join order (for which |
100 | check_interleaving_with_nj has not been called) |
101 | |
102 | @param last join table to remove, it is assumed to be the last in current |
103 | partial join order. |
104 | */ |
105 | + |
106 | static void restore_prev_nj_state(JoinTable *last) |
107 | { |
108 | TableList *last_emb= last->table->pos_in_table_list->getEmbedding(); |
109 | Join *join= last->join; |
110 | - while (last_emb) |
111 | + for (;last_emb != NULL; last_emb= last_emb->getEmbedding()) |
112 | { |
113 | - if (last_emb->on_expr) |
114 | - { |
115 | - if (!(--last_emb->getNestedJoin()->counter_)) |
116 | - join->cur_embedding_map&= ~last_emb->getNestedJoin()->nj_map; |
117 | - else if (last_emb->getNestedJoin()->join_list.elements-1 == |
118 | - last_emb->getNestedJoin()->counter_) |
119 | - join->cur_embedding_map|= last_emb->getNestedJoin()->nj_map; |
120 | - else |
121 | - break; |
122 | - } |
123 | - last_emb= last_emb->getEmbedding(); |
124 | + nested_join_st *nest= last_emb->getNestedJoin(); |
125 | + |
126 | + bool was_fully_covered= nest->is_fully_covered(); |
127 | + |
128 | + if (--nest->counter_ == 0) |
129 | + join->cur_embedding_map&= ~nest->nj_map; |
130 | + |
131 | + if (!was_fully_covered) |
132 | + break; |
133 | + |
134 | + join->cur_embedding_map|= nest->nj_map; |
135 | } |
136 | } |
137 | |
138 | - |
139 | /** |
140 | Create a condition for a const reference and add this to the |
141 | currenct select for the table. |
142 | |
143 | === modified file 'drizzled/nested_join.h' |
144 | --- drizzled/nested_join.h 2010-02-04 08:14:46 +0000 |
145 | +++ drizzled/nested_join.h 2010-07-19 02:40:58 +0000 |
146 | @@ -65,6 +65,16 @@ |
147 | table_map sj_corr_tables; |
148 | |
149 | List<Item> sj_outer_expr_list; |
150 | + |
151 | + /** |
152 | + True if this join nest node is completely covered by the query execution |
153 | + plan. This means two things. |
154 | + |
155 | + 1. All tables on its @c join_list are covered by the plan. |
156 | + |
157 | + 2. All child join nest nodes are fully covered. |
158 | + */ |
159 | + bool is_fully_covered() const { return join_list.elements == counter_; } |
160 | }; |
161 | |
162 | } /* namespace drizzled */ |
163 | |
164 | === modified file 'drizzled/optimizer/range.cc' |
165 | --- drizzled/optimizer/range.cc 2010-07-16 05:40:08 +0000 |
166 | +++ drizzled/optimizer/range.cc 2010-07-19 02:40:58 +0000 |
167 | @@ -2639,8 +2639,11 @@ |
168 | } |
169 | return(tree); |
170 | } |
171 | - /* Here when simple cond */ |
172 | - if (cond->const_item()) |
173 | + /* Here when simple cond |
174 | + There are limits on what kinds of const items we can evaluate, grep for |
175 | + DontEvaluateMaterializedSubqueryTooEarly. |
176 | + */ |
177 | + if (cond->const_item() && !cond->is_expensive()) |
178 | { |
179 | /* |
180 | During the cond->val_int() evaluation we can come across a subselect |
181 | |
182 | === modified file 'drizzled/sql_select.cc' |
183 | --- drizzled/sql_select.cc 2010-07-16 05:40:08 +0000 |
184 | +++ drizzled/sql_select.cc 2010-07-19 02:40:58 +0000 |
185 | @@ -2850,8 +2850,6 @@ |
186 | @endverbatim |
187 | |
188 | @param join Join being processed |
189 | - @param last_tab Last table in current partial join order (this function is |
190 | - not called for empty partial join orders) |
191 | @param next_tab Table we're going to extend the current partial join with |
192 | |
193 | @retval |
194 | @@ -2860,10 +2858,10 @@ |
195 | @retval |
196 | true Requested join order extension not allowed. |
197 | */ |
198 | -bool check_interleaving_with_nj(JoinTable *last_tab, JoinTable *next_tab) |
199 | +bool check_interleaving_with_nj(JoinTable *next_tab) |
200 | { |
201 | TableList *next_emb= next_tab->table->pos_in_table_list->getEmbedding(); |
202 | - Join *join= last_tab->join; |
203 | + Join *join= next_tab->join; |
204 | |
205 | if ((join->cur_embedding_map & ~next_tab->embedding_map).any()) |
206 | { |
207 | @@ -3085,12 +3083,12 @@ |
208 | /* |
209 | TODO: |
210 | Excluding all expensive functions is too restritive we should exclude only |
211 | - materialized IN because it is created later than this phase, and cannot be |
212 | - evaluated at this point. |
213 | - The condition should be something as (need to fix member access): |
214 | - !(cond->type() == Item::FUNC_ITEM && |
215 | - ((Item_func*)cond)->func_name() == "<in_optimizer>" && |
216 | - ((Item_in_optimizer*)cond)->is_expensive())) |
217 | + materialized IN subquery predicates because they can't yet be evaluated |
218 | + here (they need additional initialization that is done later on). |
219 | + |
220 | + The proper way to exclude the subqueries would be to walk the cond tree and |
221 | + check for materialized subqueries there. |
222 | + |
223 | */ |
224 | { |
225 | *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE; |
226 | |
227 | === modified file 'drizzled/sql_select.h' |
228 | --- drizzled/sql_select.h 2010-05-19 00:35:41 +0000 |
229 | +++ drizzled/sql_select.h 2010-07-19 02:40:58 +0000 |
230 | @@ -144,7 +144,7 @@ |
231 | uint32_t elements, |
232 | List<Item> &all_fields); |
233 | bool change_group_ref(Session *session, Item_func *expr, order_st *group_list, bool *changed); |
234 | -bool check_interleaving_with_nj(JoinTable *last, JoinTable *next); |
235 | +bool check_interleaving_with_nj(JoinTable *next); |
236 | |
237 | int join_read_const_table(JoinTable *tab, optimizer::Position *pos); |
238 | int join_read_system(JoinTable *tab); |
239 | |
240 | === modified file 'tests/r/optimizer.result' |
241 | --- tests/r/optimizer.result 2010-07-10 21:56:22 +0000 |
242 | +++ tests/r/optimizer.result 2010-07-19 02:40:58 +0000 |
243 | @@ -71,3 +71,144 @@ |
244 | NULL |
245 | DROP TABLE `bb`, `cc`; |
246 | End Bug#600664 test |
247 | +Bug#595305 |
248 | +- Assertion failed - exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)", file=<value optimized out>, line=318, function=0x84fae0 "virtual bool drizzled::Item_in_subselect::exec() |
249 | +DROP TABLE IF EXISTS `bb`; |
250 | +CREATE TABLE `bb` ( |
251 | +`pk` int NOT NULL AUTO_INCREMENT, |
252 | +`col_text_not_null` text NOT NULL, |
253 | +`col_bigint_key` bigint DEFAULT NULL, |
254 | +`col_int` int DEFAULT NULL, |
255 | +`col_char` varchar(1) DEFAULT NULL, |
256 | +`col_text` text, |
257 | +`col_text_not_null_key` text NOT NULL, |
258 | +`col_char_not_null` varchar(1) NOT NULL, |
259 | +PRIMARY KEY (`pk`), |
260 | +KEY `col_bigint_key` (`col_bigint_key`), |
261 | +KEY `col_text_not_null_key` (`col_text_not_null_key`(255)) |
262 | +) ENGINE=InnoDB; |
263 | +ALTER TABLE `bb` DISABLE KEYS; |
264 | +Warnings: |
265 | +Note 1031 Table storage engine for 'bb' doesn't have this option |
266 | +INSERT INTO `bb` VALUES (1,'',NULL,NULL,'h',NULL,'','f'),(2,'',NULL,1756299264,'p',NULL,'','w'),(3,'',6,-1990000640,'w',NULL,'','w'),(4,'',NULL,NULL,'f',NULL,'','k'),(5,'',NULL,NULL,'k',NULL,'','t'),(6,'',9,NULL,'m',NULL,'','c'),(7,'',6451687941184946176,0,'z',NULL,'','f'),(8,'',8960474408607023104,1805844480,'h',NULL,'','k'),(9,'',8,7,'y',NULL,'','m'),(10,'',NULL,-415629312,'t',NULL,'','h'); |
267 | +ALTER TABLE `bb` ENABLE KEYS; |
268 | +Warnings: |
269 | +Note 1031 Table storage engine for 'bb' doesn't have this option |
270 | +SELECT DISTINCT OUTR . `col_text` AS X FROM BB AS OUTR2 LEFT JOIN BB AS OUTR ON ( OUTR2 . `col_char` <> OUTR . `col_text` ) WHERE OUTR . `col_bigint_key` IN ( SELECT INNR . `pk` AS Y FROM BB AS INNR WHERE INNR . `col_bigint_key` > INNR . `col_int` AND INNR . `col_text_not_null_key` >= INNR . `col_char_not_null` ) AND OUTR . `col_bigint_key` = 9 ORDER BY OUTR . `col_text_not_null` , OUTR . `pk`; |
271 | +X |
272 | +DROP TABLE BB; |
273 | +End Bug#595305 test |
274 | +Bug#592444 |
275 | +- Segfault - operator= (join=0x1fd0fd8, tables=<value optimized out>, conds=<value optimized out>, keyuse_array=<value optimized out>) at ./drizzled/join_table.h:73 |
276 | +DROP TABLE IF EXISTS `p`; |
277 | +CREATE TABLE `p` ( |
278 | +`pk` int NOT NULL AUTO_INCREMENT, |
279 | +`col_int_key` int DEFAULT NULL, |
280 | +PRIMARY KEY (`pk`), |
281 | +KEY `col_int_key` (`col_int_key`) |
282 | +) ENGINE=InnoDB; |
283 | +ALTER TABLE `p` DISABLE KEYS; |
284 | +Warnings: |
285 | +Note 1031 Table storage engine for 'p' doesn't have this option |
286 | +INSERT INTO `p` VALUES (1,NULL),(2,261947392),(3,-1761017856),(4,NULL),(5,NULL),(6,NULL),(7,1941635072),(8,-610992128),(9,NULL),(10,-1486618624),(11,1047199744),(12,5),(13,2),(14,NULL),(15,NULL),(16,-828899328),(17,5),(18,NULL),(19,-1573322752),(20,NULL),(21,1),(22,1080360960),(23,NULL),(24,9),(25,243335168),(26,NULL),(27,2),(28,351469568),(29,NULL),(30,0),(31,1626865664),(32,NULL),(33,NULL),(34,6),(35,NULL),(36,6),(37,NULL),(38,838598656),(39,-1732706304),(40,-120061952),(41,NULL),(42,3),(43,2),(44,8),(45,-1234436096),(46,4),(47,-1542324224),(48,NULL),(49,1607073792),(50,0),(51,9),(52,NULL),(53,NULL),(54,-921174016),(55,-184877056),(56,NULL),(57,-1291059200),(58,0),(59,NULL),(60,565379072),(61,NULL),(62,506855424),(63,NULL),(64,NULL),(65,NULL),(66,5),(67,5),(68,5),(69,2),(70,6),(71,NULL),(72,9),(73,7),(74,-2108555264),(75,6),(76,1057095680),(77,8),(78,-409468928),(79,-438960128),(80,-939393024),(81,-927203328),(82,NULL),(83,-1834680320),(84,NULL),(85,1984561152),(86,8),(87,NULL),(88,8),(89,-326500352),(90,NULL),(91,4),(92,NULL),(93,NULL),(94,-1369833472),(95,NULL),(96,0),(97,31260672),(98,NULL),(99,NULL),(100,0); |
287 | +ALTER TABLE `p` ENABLE KEYS; |
288 | +Warnings: |
289 | +Note 1031 Table storage engine for 'p' doesn't have this option |
290 | +DROP TABLE IF EXISTS `n`; |
291 | +Warnings: |
292 | +Note 1051 Unknown table 'n' |
293 | +CREATE TABLE `n` ( |
294 | +`pk` int NOT NULL AUTO_INCREMENT, |
295 | +`col_varchar_10_key` varchar(10) DEFAULT NULL, |
296 | +PRIMARY KEY (`pk`), |
297 | +KEY `col_varchar_10_key` (`col_varchar_10_key`) |
298 | +) ENGINE=InnoDB; |
299 | +ALTER TABLE `n` DISABLE KEYS; |
300 | +Warnings: |
301 | +Note 1031 Table storage engine for 'n' doesn't have this option |
302 | +INSERT INTO `n` VALUES (1,'rdtxodtcmi'),(2,'corrdtxodt'),(3,'v'),(4,'dcorrdtxod'),(5,'s'),(6,'my'),(7,'e'),(8,'qxszmrdcor'),(9,'bqxszmrdco'),(10,'wbqxszmrdc'),(11,'v'),(12,'kywbqxszmr'),(13,'v'),(14,'lngrakywbq'),(15,'umtlngraky'),(16,'could'),(17,'peiywumtln'),(18,'will'),(19,'tell'),(20,'w'),(21,'jenpeiywum'),(22,'cejenpeiyw'),(23,'x'),(24,'vxcejenpei'); |
303 | +ALTER TABLE `n` ENABLE KEYS; |
304 | +Warnings: |
305 | +Note 1031 Table storage engine for 'n' doesn't have this option |
306 | +DROP TABLE IF EXISTS `d`; |
307 | +Warnings: |
308 | +Note 1051 Unknown table 'd' |
309 | +CREATE TABLE `d` ( |
310 | +`pk` int NOT NULL AUTO_INCREMENT, |
311 | +`col_varchar_10` varchar(10) DEFAULT NULL, |
312 | +PRIMARY KEY (`pk`) |
313 | +) ENGINE=InnoDB; |
314 | +ALTER TABLE `d` DISABLE KEYS; |
315 | +Warnings: |
316 | +Note 1031 Table storage engine for 'd' doesn't have this option |
317 | +INSERT INTO `d` VALUES (1,'f'),(2,'zdvoleqxer'),(3,'i'),(4,'u'),(5,'q'); |
318 | +ALTER TABLE `d` ENABLE KEYS; |
319 | +Warnings: |
320 | +Note 1031 Table storage engine for 'd' doesn't have this option |
321 | +DROP TABLE IF EXISTS `h`; |
322 | +Warnings: |
323 | +Note 1051 Unknown table 'h' |
324 | +CREATE TABLE `h` ( |
325 | +`col_varchar_10` varchar(10) DEFAULT NULL, |
326 | +`pk` int NOT NULL AUTO_INCREMENT, |
327 | +`col_varchar_10_key` varchar(10) DEFAULT NULL, |
328 | +`col_int_key` int DEFAULT NULL, |
329 | +PRIMARY KEY (`pk`), |
330 | +KEY `col_varchar_10_key` (`col_varchar_10_key`), |
331 | +KEY `col_int_key` (`col_int_key`) |
332 | +) ENGINE=InnoDB; |
333 | +ALTER TABLE `h` DISABLE KEYS; |
334 | +Warnings: |
335 | +Note 1031 Table storage engine for 'h' doesn't have this option |
336 | +INSERT INTO `h` VALUES ('n',1,'rshrrbmsyd',4),('h',2,'okay',NULL),('ok',3,'i',-802095104),('just',4,'z',6),('hfnrshrrbm',5,'his',8),('f',6,'xhfnrshrrb',1),('h',7,'wxhfnrshrr',7),('wtwxhfnrsh',8,'v',7),('of',9,'cwtwxhfnrs',-1258487808); |
337 | +ALTER TABLE `h` ENABLE KEYS; |
338 | +Warnings: |
339 | +Note 1031 Table storage engine for 'h' doesn't have this option |
340 | +DROP TABLE IF EXISTS `a`; |
341 | +Warnings: |
342 | +Note 1051 Unknown table 'a' |
343 | +CREATE TABLE `a` ( |
344 | +`pk` int NOT NULL AUTO_INCREMENT, |
345 | +PRIMARY KEY (`pk`) |
346 | +) ENGINE=InnoDB; |
347 | +DROP TABLE IF EXISTS `k`; |
348 | +Warnings: |
349 | +Note 1051 Unknown table 'k' |
350 | +CREATE TABLE `k` ( |
351 | +`pk` int NOT NULL AUTO_INCREMENT, |
352 | +`col_varchar_10` varchar(10) DEFAULT NULL, |
353 | +`col_varchar_10_key` varchar(10) DEFAULT NULL, |
354 | +`col_int_key` int DEFAULT NULL, |
355 | +PRIMARY KEY (`pk`), |
356 | +KEY `col_varchar_10_key` (`col_varchar_10_key`), |
357 | +KEY `col_int_key` (`col_int_key`) |
358 | +) ENGINE=InnoDB; |
359 | +ALTER TABLE `k` DISABLE KEYS; |
360 | +Warnings: |
361 | +Note 1031 Table storage engine for 'k' doesn't have this option |
362 | +INSERT INTO `k` VALUES (1,'bwylrhande','hbwylrhand',-522452992),(2,'how','about',2098200576),(3,'ephbwylrha','pephbwylrh',904331264),(4,'okay','ipephbwylr',-1176633344),(5,'but','one',-1177288704),(6,'tnwkipephb','j',5),(7,'hqtnwkipep','a',9),(8,'with','wdhqtnwkip',NULL),(9,'eyxwdhqtnw','jeyxwdhqtn',2),(10,'right','something',3),(11,'z','ujeyxwdhqt',5),(12,'yyujeyxwdh','we',NULL),(13,'can','syyujeyxwd',0),(14,'s','oh',21037056),(15,'ngvsyyujey','something',1224867840),(16,'gngvsyyuje','qgngvsyyuj',1),(17,'this','really',4),(18,'p','bbuqgngvsy',1865023488),(19,'okay','from',1),(20,'dmbbuqgngv','but',9),(21,'one','rdmbbuqgng',0); |
363 | +ALTER TABLE `k` ENABLE KEYS; |
364 | +Warnings: |
365 | +Note 1031 Table storage engine for 'k' doesn't have this option |
366 | +SELECT |
367 | +table2 . `pk` AS field4 |
368 | +FROM p AS table1 LEFT JOIN |
369 | +n AS table2 LEFT JOIN |
370 | +d AS table3 |
371 | +ON table2 . `pk` = table3 . `pk` |
372 | +RIGHT OUTER JOIN h AS table4 |
373 | +LEFT OUTER JOIN a AS table5 |
374 | +ON table4 . `pk` = table5 . `pk` |
375 | +ON table2 . `col_varchar_10_key` = table4 . `col_varchar_10_key` |
376 | +ON table1 . `col_int_key` = table5 . `pk` |
377 | +LEFT JOIN h AS table6 |
378 | +LEFT JOIN k AS table7 |
379 | +ON table6 . `col_int_key` = table7 . `col_int_key` |
380 | +LEFT OUTER JOIN h AS table8 |
381 | +ON table7 . `col_varchar_10_key` = table8 . `col_varchar_10` |
382 | +ON table3 . `col_varchar_10` = table7 . `col_varchar_10` |
383 | +WHERE ( |
384 | +( table3 . `pk` IN (6) ) |
385 | +OR table7 . `col_int_key` IN (1, 1, 1, 2) ) ; |
386 | +field4 |
387 | +End Bug#592444 test |
388 | |
389 | === modified file 'tests/t/optimizer.test' |
390 | --- tests/t/optimizer.test 2010-07-10 21:56:22 +0000 |
391 | +++ tests/t/optimizer.test 2010-07-19 02:40:58 +0000 |
392 | @@ -86,3 +86,129 @@ |
393 | |
394 | DROP TABLE `bb`, `cc`; |
395 | --echo End Bug#600664 test |
396 | + |
397 | +--echo Bug#595305 |
398 | +--echo - Assertion failed - exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)", file=<value optimized out>, line=318, function=0x84fae0 "virtual bool drizzled::Item_in_subselect::exec() |
399 | +--disable_warnings |
400 | +DROP TABLE IF EXISTS `bb`; |
401 | +--enable_warnings |
402 | + |
403 | +CREATE TABLE `bb` ( |
404 | + `pk` int NOT NULL AUTO_INCREMENT, |
405 | + `col_text_not_null` text NOT NULL, |
406 | + `col_bigint_key` bigint DEFAULT NULL, |
407 | + `col_int` int DEFAULT NULL, |
408 | + `col_char` varchar(1) DEFAULT NULL, |
409 | + `col_text` text, |
410 | + `col_text_not_null_key` text NOT NULL, |
411 | + `col_char_not_null` varchar(1) NOT NULL, |
412 | + PRIMARY KEY (`pk`), |
413 | + KEY `col_bigint_key` (`col_bigint_key`), |
414 | + KEY `col_text_not_null_key` (`col_text_not_null_key`(255)) |
415 | +) ENGINE=InnoDB; |
416 | + |
417 | +ALTER TABLE `bb` DISABLE KEYS; |
418 | +INSERT INTO `bb` VALUES (1,'',NULL,NULL,'h',NULL,'','f'),(2,'',NULL,1756299264,'p',NULL,'','w'),(3,'',6,-1990000640,'w',NULL,'','w'),(4,'',NULL,NULL,'f',NULL,'','k'),(5,'',NULL,NULL,'k',NULL,'','t'),(6,'',9,NULL,'m',NULL,'','c'),(7,'',6451687941184946176,0,'z',NULL,'','f'),(8,'',8960474408607023104,1805844480,'h',NULL,'','k'),(9,'',8,7,'y',NULL,'','m'),(10,'',NULL,-415629312,'t',NULL,'','h'); |
419 | +ALTER TABLE `bb` ENABLE KEYS; |
420 | + |
421 | +SELECT DISTINCT OUTR . `col_text` AS X FROM BB AS OUTR2 LEFT JOIN BB AS OUTR ON ( OUTR2 . `col_char` <> OUTR . `col_text` ) WHERE OUTR . `col_bigint_key` IN ( SELECT INNR . `pk` AS Y FROM BB AS INNR WHERE INNR . `col_bigint_key` > INNR . `col_int` AND INNR . `col_text_not_null_key` >= INNR . `col_char_not_null` ) AND OUTR . `col_bigint_key` = 9 ORDER BY OUTR . `col_text_not_null` , OUTR . `pk`; |
422 | + |
423 | +DROP TABLE BB; |
424 | +--echo End Bug#595305 test |
425 | + |
426 | +--echo Bug#592444 |
427 | +--echo - Segfault - operator= (join=0x1fd0fd8, tables=<value optimized out>, conds=<value optimized out>, keyuse_array=<value optimized out>) at ./drizzled/join_table.h:73 |
428 | +--disable_warnings |
429 | +DROP TABLE IF EXISTS `p`; |
430 | +--enable_warnings |
431 | +CREATE TABLE `p` ( |
432 | + `pk` int NOT NULL AUTO_INCREMENT, |
433 | + `col_int_key` int DEFAULT NULL, |
434 | + PRIMARY KEY (`pk`), |
435 | + KEY `col_int_key` (`col_int_key`) |
436 | +) ENGINE=InnoDB; |
437 | + |
438 | +ALTER TABLE `p` DISABLE KEYS; |
439 | +INSERT INTO `p` VALUES (1,NULL),(2,261947392),(3,-1761017856),(4,NULL),(5,NULL),(6,NULL),(7,1941635072),(8,-610992128),(9,NULL),(10,-1486618624),(11,1047199744),(12,5),(13,2),(14,NULL),(15,NULL),(16,-828899328),(17,5),(18,NULL),(19,-1573322752),(20,NULL),(21,1),(22,1080360960),(23,NULL),(24,9),(25,243335168),(26,NULL),(27,2),(28,351469568),(29,NULL),(30,0),(31,1626865664),(32,NULL),(33,NULL),(34,6),(35,NULL),(36,6),(37,NULL),(38,838598656),(39,-1732706304),(40,-120061952),(41,NULL),(42,3),(43,2),(44,8),(45,-1234436096),(46,4),(47,-1542324224),(48,NULL),(49,1607073792),(50,0),(51,9),(52,NULL),(53,NULL),(54,-921174016),(55,-184877056),(56,NULL),(57,-1291059200),(58,0),(59,NULL),(60,565379072),(61,NULL),(62,506855424),(63,NULL),(64,NULL),(65,NULL),(66,5),(67,5),(68,5),(69,2),(70,6),(71,NULL),(72,9),(73,7),(74,-2108555264),(75,6),(76,1057095680),(77,8),(78,-409468928),(79,-438960128),(80,-939393024),(81,-927203328),(82,NULL),(83,-1834680320),(84,NULL),(85,1984561152),(86,8),(87,NULL),(88,8),(89,-326500352),(90,NULL),(91,4),(92,NULL),(93,NULL),(94,-1369833472),(95,NULL),(96,0),(97,31260672),(98,NULL),(99,NULL),(100,0); |
440 | +ALTER TABLE `p` ENABLE KEYS; |
441 | + |
442 | +DROP TABLE IF EXISTS `n`; |
443 | +CREATE TABLE `n` ( |
444 | + `pk` int NOT NULL AUTO_INCREMENT, |
445 | + `col_varchar_10_key` varchar(10) DEFAULT NULL, |
446 | + PRIMARY KEY (`pk`), |
447 | + KEY `col_varchar_10_key` (`col_varchar_10_key`) |
448 | +) ENGINE=InnoDB; |
449 | + |
450 | +ALTER TABLE `n` DISABLE KEYS; |
451 | +INSERT INTO `n` VALUES (1,'rdtxodtcmi'),(2,'corrdtxodt'),(3,'v'),(4,'dcorrdtxod'),(5,'s'),(6,'my'),(7,'e'),(8,'qxszmrdcor'),(9,'bqxszmrdco'),(10,'wbqxszmrdc'),(11,'v'),(12,'kywbqxszmr'),(13,'v'),(14,'lngrakywbq'),(15,'umtlngraky'),(16,'could'),(17,'peiywumtln'),(18,'will'),(19,'tell'),(20,'w'),(21,'jenpeiywum'),(22,'cejenpeiyw'),(23,'x'),(24,'vxcejenpei'); |
452 | +ALTER TABLE `n` ENABLE KEYS; |
453 | + |
454 | +DROP TABLE IF EXISTS `d`; |
455 | +CREATE TABLE `d` ( |
456 | + `pk` int NOT NULL AUTO_INCREMENT, |
457 | + `col_varchar_10` varchar(10) DEFAULT NULL, |
458 | + PRIMARY KEY (`pk`) |
459 | +) ENGINE=InnoDB; |
460 | + |
461 | +ALTER TABLE `d` DISABLE KEYS; |
462 | +INSERT INTO `d` VALUES (1,'f'),(2,'zdvoleqxer'),(3,'i'),(4,'u'),(5,'q'); |
463 | +ALTER TABLE `d` ENABLE KEYS; |
464 | + |
465 | +DROP TABLE IF EXISTS `h`; |
466 | +CREATE TABLE `h` ( |
467 | + `col_varchar_10` varchar(10) DEFAULT NULL, |
468 | + `pk` int NOT NULL AUTO_INCREMENT, |
469 | + `col_varchar_10_key` varchar(10) DEFAULT NULL, |
470 | + `col_int_key` int DEFAULT NULL, |
471 | + PRIMARY KEY (`pk`), |
472 | + KEY `col_varchar_10_key` (`col_varchar_10_key`), |
473 | + KEY `col_int_key` (`col_int_key`) |
474 | +) ENGINE=InnoDB; |
475 | + |
476 | +ALTER TABLE `h` DISABLE KEYS; |
477 | +INSERT INTO `h` VALUES ('n',1,'rshrrbmsyd',4),('h',2,'okay',NULL),('ok',3,'i',-802095104),('just',4,'z',6),('hfnrshrrbm',5,'his',8),('f',6,'xhfnrshrrb',1),('h',7,'wxhfnrshrr',7),('wtwxhfnrsh',8,'v',7),('of',9,'cwtwxhfnrs',-1258487808); |
478 | +ALTER TABLE `h` ENABLE KEYS; |
479 | + |
480 | +DROP TABLE IF EXISTS `a`; |
481 | +CREATE TABLE `a` ( |
482 | + `pk` int NOT NULL AUTO_INCREMENT, |
483 | + PRIMARY KEY (`pk`) |
484 | +) ENGINE=InnoDB; |
485 | + |
486 | +DROP TABLE IF EXISTS `k`; |
487 | +CREATE TABLE `k` ( |
488 | + `pk` int NOT NULL AUTO_INCREMENT, |
489 | + `col_varchar_10` varchar(10) DEFAULT NULL, |
490 | + `col_varchar_10_key` varchar(10) DEFAULT NULL, |
491 | + `col_int_key` int DEFAULT NULL, |
492 | + PRIMARY KEY (`pk`), |
493 | + KEY `col_varchar_10_key` (`col_varchar_10_key`), |
494 | + KEY `col_int_key` (`col_int_key`) |
495 | +) ENGINE=InnoDB; |
496 | + |
497 | +ALTER TABLE `k` DISABLE KEYS; |
498 | +INSERT INTO `k` VALUES (1,'bwylrhande','hbwylrhand',-522452992),(2,'how','about',2098200576),(3,'ephbwylrha','pephbwylrh',904331264),(4,'okay','ipephbwylr',-1176633344),(5,'but','one',-1177288704),(6,'tnwkipephb','j',5),(7,'hqtnwkipep','a',9),(8,'with','wdhqtnwkip',NULL),(9,'eyxwdhqtnw','jeyxwdhqtn',2),(10,'right','something',3),(11,'z','ujeyxwdhqt',5),(12,'yyujeyxwdh','we',NULL),(13,'can','syyujeyxwd',0),(14,'s','oh',21037056),(15,'ngvsyyujey','something',1224867840),(16,'gngvsyyuje','qgngvsyyuj',1),(17,'this','really',4),(18,'p','bbuqgngvsy',1865023488),(19,'okay','from',1),(20,'dmbbuqgngv','but',9),(21,'one','rdmbbuqgng',0); |
499 | +ALTER TABLE `k` ENABLE KEYS; |
500 | + |
501 | +SELECT |
502 | +table2 . `pk` AS field4 |
503 | +FROM p AS table1 LEFT JOIN |
504 | +n AS table2 LEFT JOIN |
505 | +d AS table3 |
506 | +ON table2 . `pk` = table3 . `pk` |
507 | +RIGHT OUTER JOIN h AS table4 |
508 | +LEFT OUTER JOIN a AS table5 |
509 | +ON table4 . `pk` = table5 . `pk` |
510 | +ON table2 . `col_varchar_10_key` = table4 . `col_varchar_10_key` |
511 | +ON table1 . `col_int_key` = table5 . `pk` |
512 | +LEFT JOIN h AS table6 |
513 | +LEFT JOIN k AS table7 |
514 | +ON table6 . `col_int_key` = table7 . `col_int_key` |
515 | +LEFT OUTER JOIN h AS table8 |
516 | +ON table7 . `col_varchar_10_key` = table8 . `col_varchar_10` |
517 | +ON table3 . `col_varchar_10` = table7 . `col_varchar_10` |
518 | +WHERE ( |
519 | +( table3 . `pk` IN (6) ) |
520 | +OR table7 . `col_int_key` IN (1, 1, 1, 2) ) ; |
521 | +--echo End Bug#592444 test |
Tested with the randgen + standard test suite and this looks good. Approve.