Merge lp:~prafulla-t/drizzle/dr-bug-592444 into lp:~drizzle-trunk/drizzle/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
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.
Revision history for this message
Patrick Crews (patrick-crews) wrote :

Tested with the randgen + standard test suite and this looks good. Approve.

review: Approve

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