From: Sergey Petrunia Date: January 28 2009 5:05pm Subject: bzr commit into mysql-6.0 branch (sergefp:2817) Bug#41842 List-Archive: http://lists.mysql.com/commits/64357 X-Bug: 41842 Message-Id: <20090128170518.3E4A92EA007@pslp2.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7BIT #At file:///home/spetrunia/dev/mysql-6.0-bug41842-r2/ based on revid:sergefp@stripped 2817 Sergey Petrunia 2009-01-28 BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING - Make optimize_cond() not to do build_equal_items() for HAVING clause - Make setup_sj_materialization(), SJM-Scan setup code correctly pick an Item_equal element that sjm-scan should unpack values to. modified: mysql-test/r/subselect_sj.result mysql-test/r/subselect_sj_jcl6.result mysql-test/t/subselect_sj.test sql/sql_select.cc per-file messages: mysql-test/r/subselect_sj.result BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING - Testcase mysql-test/r/subselect_sj_jcl6.result BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING - Testcase mysql-test/t/subselect_sj.test BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING - Testcase sql/sql_select.cc BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING - Make optimize_cond() not to do build_equal_items() for HAVING clause - Make setup_sj_materialization(), SJM-Scan setup code correctly pick an Item_equal element that sjm-scan should unpack values to. === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2009-01-26 16:48:55 +0000 +++ b/mysql-test/r/subselect_sj.result 2009-01-28 17:05:03 +0000 @@ -261,3 +261,69 @@ pk varchar_nokey 2 j 3 aew drop table t1; +# +# BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +int_nokey int(11) NOT NULL, +time_key time NOT NULL, +datetime_key datetime NOT NULL, +datetime_nokey datetime NOT NULL, +varchar_key varchar(1) NOT NULL, +varchar_nokey varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY time_key (time_key), +KEY datetime_key (datetime_key), +KEY varchar_key (varchar_key) +); +INSERT INTO t1 VALUES +(1,0, '00:16:10','2008-09-03 14:25:40','2008-09-03 14:25:40','h','h'), +(2,7, '00:00:00','2001-01-13 00:00:00','2001-01-13 00:00:00','',''), +(3,0, '00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'), +(4,2, '16:29:24','2000-10-16 01:39:08','2000-10-16 01:39:08','w','w'), +(5,1, '09:23:32','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'), +(6,3, '00:00:00','2007-12-02 00:00:00','2007-12-02 00:00:00','o','o'), +(7,3, '00:00:00','2008-09-11 00:00:00','2008-09-11 00:00:00','',''), +(8,0, '13:59:04','0000-00-00 00:00:00','0000-00-00 00:00:00','s','s'), +(9,7, '09:01:06','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'), +(10,5,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','n','n'), +(11,0,'21:06:46','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), +(12,2,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','',''), +(13,6,'14:45:34','2003-07-28 02:34:08','2003-07-28 02:34:08','w','w'), +(14,1,'15:04:12','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), +(15,0,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'), +(16,0,'15:55:23','2004-03-17 00:32:27','2004-03-17 00:32:27','p','p'), +(17,1,'16:30:00','2004-12-27 19:20:00','2004-12-27 19:20:00','d','d'), +(18,0,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'), +(19,0,'14:13:26','2008-11-09 05:53:48','2008-11-09 05:53:48','o','o'), +(20,0,'00:00:00','2009-10-11 06:58:04','2009-10-11 06:58:04','k','k'); +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +int_nokey int(11) NOT NULL, +time_key time NOT NULL, +datetime_key datetime NOT NULL, +datetime_nokey datetime NOT NULL, +varchar_key varchar(1) NOT NULL, +varchar_nokey varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY time_key (time_key), +KEY datetime_key (datetime_key), +KEY varchar_key (varchar_key) +); +INSERT INTO t2 VALUES +(10,0,'19:39:13','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'), +(11,8,'03:43:53','0000-00-00 00:00:00','0000-00-00 00:00:00','b','b'); +SELECT OUTR.datetime_nokey AS X FROM t1 AS OUTR +WHERE +OUTR.varchar_nokey IN (SELECT +INNR . varchar_nokey AS Y +FROM t2 AS INNR +WHERE +INNR . datetime_key >= INNR . time_key OR +INNR . pk = INNR . int_nokey +) +AND OUTR . varchar_nokey <= 'w' +HAVING X > '2012-12-12'; +X +drop table t1, t2; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2009-01-26 16:48:55 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2009-01-28 17:05:03 +0000 @@ -265,6 +265,72 @@ pk varchar_nokey 2 j 3 aew drop table t1; +# +# BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +int_nokey int(11) NOT NULL, +time_key time NOT NULL, +datetime_key datetime NOT NULL, +datetime_nokey datetime NOT NULL, +varchar_key varchar(1) NOT NULL, +varchar_nokey varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY time_key (time_key), +KEY datetime_key (datetime_key), +KEY varchar_key (varchar_key) +); +INSERT INTO t1 VALUES +(1,0, '00:16:10','2008-09-03 14:25:40','2008-09-03 14:25:40','h','h'), +(2,7, '00:00:00','2001-01-13 00:00:00','2001-01-13 00:00:00','',''), +(3,0, '00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'), +(4,2, '16:29:24','2000-10-16 01:39:08','2000-10-16 01:39:08','w','w'), +(5,1, '09:23:32','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'), +(6,3, '00:00:00','2007-12-02 00:00:00','2007-12-02 00:00:00','o','o'), +(7,3, '00:00:00','2008-09-11 00:00:00','2008-09-11 00:00:00','',''), +(8,0, '13:59:04','0000-00-00 00:00:00','0000-00-00 00:00:00','s','s'), +(9,7, '09:01:06','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'), +(10,5,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','n','n'), +(11,0,'21:06:46','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), +(12,2,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','',''), +(13,6,'14:45:34','2003-07-28 02:34:08','2003-07-28 02:34:08','w','w'), +(14,1,'15:04:12','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), +(15,0,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'), +(16,0,'15:55:23','2004-03-17 00:32:27','2004-03-17 00:32:27','p','p'), +(17,1,'16:30:00','2004-12-27 19:20:00','2004-12-27 19:20:00','d','d'), +(18,0,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'), +(19,0,'14:13:26','2008-11-09 05:53:48','2008-11-09 05:53:48','o','o'), +(20,0,'00:00:00','2009-10-11 06:58:04','2009-10-11 06:58:04','k','k'); +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +int_nokey int(11) NOT NULL, +time_key time NOT NULL, +datetime_key datetime NOT NULL, +datetime_nokey datetime NOT NULL, +varchar_key varchar(1) NOT NULL, +varchar_nokey varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY time_key (time_key), +KEY datetime_key (datetime_key), +KEY varchar_key (varchar_key) +); +INSERT INTO t2 VALUES +(10,0,'19:39:13','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'), +(11,8,'03:43:53','0000-00-00 00:00:00','0000-00-00 00:00:00','b','b'); +SELECT OUTR.datetime_nokey AS X FROM t1 AS OUTR +WHERE +OUTR.varchar_nokey IN (SELECT +INNR . varchar_nokey AS Y +FROM t2 AS INNR +WHERE +INNR . datetime_key >= INNR . time_key OR +INNR . pk = INNR . int_nokey +) +AND OUTR . varchar_nokey <= 'w' +HAVING X > '2012-12-12'; +X +drop table t1, t2; set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2009-01-26 16:48:55 +0000 +++ b/mysql-test/t/subselect_sj.test 2009-01-28 17:05:03 +0000 @@ -147,3 +147,73 @@ WHERE varchar_nokey IN ( t1 ) XOR pk = 30; drop table t1; + +--echo # +--echo # BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING +--echo # + +CREATE TABLE t1 ( + pk int(11) NOT NULL AUTO_INCREMENT, + int_nokey int(11) NOT NULL, + time_key time NOT NULL, + datetime_key datetime NOT NULL, + datetime_nokey datetime NOT NULL, + varchar_key varchar(1) NOT NULL, + varchar_nokey varchar(1) NOT NULL, + PRIMARY KEY (pk), + KEY time_key (time_key), + KEY datetime_key (datetime_key), + KEY varchar_key (varchar_key) +); +INSERT INTO t1 VALUES +(1,0, '00:16:10','2008-09-03 14:25:40','2008-09-03 14:25:40','h','h'), +(2,7, '00:00:00','2001-01-13 00:00:00','2001-01-13 00:00:00','',''), +(3,0, '00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'), +(4,2, '16:29:24','2000-10-16 01:39:08','2000-10-16 01:39:08','w','w'), +(5,1, '09:23:32','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'), +(6,3, '00:00:00','2007-12-02 00:00:00','2007-12-02 00:00:00','o','o'), +(7,3, '00:00:00','2008-09-11 00:00:00','2008-09-11 00:00:00','',''), +(8,0, '13:59:04','0000-00-00 00:00:00','0000-00-00 00:00:00','s','s'), +(9,7, '09:01:06','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'), +(10,5,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','n','n'), +(11,0,'21:06:46','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), +(12,2,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','',''), +(13,6,'14:45:34','2003-07-28 02:34:08','2003-07-28 02:34:08','w','w'), +(14,1,'15:04:12','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), +(15,0,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'), +(16,0,'15:55:23','2004-03-17 00:32:27','2004-03-17 00:32:27','p','p'), +(17,1,'16:30:00','2004-12-27 19:20:00','2004-12-27 19:20:00','d','d'), +(18,0,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'), +(19,0,'14:13:26','2008-11-09 05:53:48','2008-11-09 05:53:48','o','o'), +(20,0,'00:00:00','2009-10-11 06:58:04','2009-10-11 06:58:04','k','k'); + +CREATE TABLE t2 ( + pk int(11) NOT NULL AUTO_INCREMENT, + int_nokey int(11) NOT NULL, + time_key time NOT NULL, + datetime_key datetime NOT NULL, + datetime_nokey datetime NOT NULL, + varchar_key varchar(1) NOT NULL, + varchar_nokey varchar(1) NOT NULL, + PRIMARY KEY (pk), + KEY time_key (time_key), + KEY datetime_key (datetime_key), + KEY varchar_key (varchar_key) +); +INSERT INTO t2 VALUES +(10,0,'19:39:13','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'), +(11,8,'03:43:53','0000-00-00 00:00:00','0000-00-00 00:00:00','b','b'); +SELECT OUTR.datetime_nokey AS X FROM t1 AS OUTR +WHERE + OUTR.varchar_nokey IN (SELECT + INNR . varchar_nokey AS Y + FROM t2 AS INNR + WHERE + INNR . datetime_key >= INNR . time_key OR + INNR . pk = INNR . int_nokey + ) + AND OUTR . varchar_nokey <= 'w' +HAVING X > '2012-12-12'; +drop table t1, t2; + + === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2009-01-26 22:21:14 +0000 +++ b/sql/sql_select.cc 2009-01-28 17:05:03 +0000 @@ -136,7 +136,8 @@ static void restore_prev_sj_state(const static COND *optimize_cond(JOIN *join, COND *conds, List *join_list, - Item::cond_result *cond_value); + bool build_equalites, + Item::cond_result *cond_value); static bool const_expression_in_where(COND *conds,Item *item, Item **comp_item); static bool open_tmp_table(TABLE *table); static bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, @@ -1487,7 +1488,7 @@ JOIN::optimize() thd->restore_active_arena(arena, &backup); } - conds= optimize_cond(this, conds, join_list, &cond_value); + conds= optimize_cond(this, conds, join_list, TRUE, &cond_value); if (thd->is_error()) { error= 1; @@ -1496,7 +1497,7 @@ JOIN::optimize() } { - having= optimize_cond(this, having, join_list, &having_value); + having= optimize_cond(this, having, join_list, FALSE, &having_value); if (thd->is_error()) { error= 1; @@ -9725,14 +9726,45 @@ bool setup_sj_materialization(JOIN_TAB * Item_equal *item_eq; Field *copy_to=((Item_field*)it++)->field; Item *head; + /* + Tricks with Item_equal are due to the following: suppose we have a + query: + + ... WHERE cond(ot.col) AND ot.col IN (SELECT it2.col FROM it1,it2 + WHERE it1.col= it2.col) + then equality propagation will create an + + Item_equal(it1.col, it2.col, ot.col) + + then substitute_for_best_equal_field() will change the conditions + according to the join order: + + it1 + it2 it1.col=it2.col + ot cond(it1.col) + + although we've originally had "SELECT it2.col", conditions attached + to subsequent outer tables will refer to it1.col, so SJM-Scan will + need to unpack data to there. + That is, if an element from subquery's select list participates in + equality propagation, then we need to unpack it to the first + element equality propagation member that refers to table that is + within the subquery. + */ item_eq= find_item_equal(tab->join->cond_equal, copy_to, &dummy); - if (!item_eq->const_item && - (head= item_eq->fields.head())->used_tables() & - emb_sj_nest->sj_inner_tables) + if (item_eq) { - DBUG_ASSERT(head->type() == Item::FIELD_ITEM); - copy_to= ((Item_field*)head)->field; + List_iterator it(item_eq->fields); + Item_field *item; + while ((item= it++)) + { + if (!(item->used_tables() & ~emb_sj_nest->sj_inner_tables)) + { + copy_to= item->field; + break; + } + } } sjm->copy_field[i].set(copy_to, sjm->table->field[i], FALSE); } @@ -13210,7 +13242,7 @@ static void restore_prev_sj_state(const static COND * optimize_cond(JOIN *join, COND *conds, List *join_list, - Item::cond_result *cond_value) + bool build_equalites, Item::cond_result *cond_value) { THD *thd= join->thd; DBUG_ENTER("optimize_cond"); @@ -13228,10 +13260,12 @@ optimize_cond(JOIN *join, COND *conds, L multiple equality contains a constant. */ DBUG_EXECUTE("where", print_where(conds, "original", QT_ORDINARY);); - conds= build_equal_items(join->thd, conds, NULL, join_list, - &join->cond_equal); - DBUG_EXECUTE("where",print_where(conds,"after equal_items", QT_ORDINARY);); - + if (build_equalites) + { + conds= build_equal_items(join->thd, conds, NULL, join_list, + &join->cond_equal); + DBUG_EXECUTE("where",print_where(conds,"after equal_items", QT_ORDINARY);); + } /* change field = field to field = const for each found field = const */ propagate_cond_constants(thd, (I_List *) 0, conds, conds); /*