At file:///home/psergey/dev/maria-5.3-subqueries-r7-rel/ ------------------------------------------------------------ revno: 2774 revision-id: psergey@askmonty.org-20100314175412-umtxuabkn4txl1yd parent: psergey@askmonty.org-20100307154145-ksby2b1l0sqm1xne committer: Sergey Petrunya branch nick: maria-5.3-subqueries-r7-rel timestamp: Sun 2010-03-14 20:54:12 +0300 message: BUG#43768: Prepared query with nested subqueries core dumps on second execution Fix two problems: 1. Let optimize_semijoin_nests() reset sj_nest->sjmat_info irrespectively of value of optimizer_flag. We need this in case somebody has turned optimization off between reexecutions of the same statement. 2. Do not pull out constant tables out of semi-join nests. The problem is that pullout operation is not undoable, and if a table is constant because it is 1/0-row table it may cease to be constant on the next execution. Note that tables that are constant because of possible eq_ref(const) access will still be pulled out as they are considered functionally-dependent. === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-02-24 11:33:42 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-03-14 17:54:12 +0000 @@ -1,4 +1,4 @@ -drop table if exists t0, t1, t2, t10, t11, t12; +drop table if exists t0, t1, t2, t3, t4, t10, t11, t12; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); @@ -871,3 +871,54 @@ DROP TABLE t1, t2, t3; DROP VIEW v2, v3; # End of Bug#49198 +# +# BUG#43768: Prepared query with nested subqueries core dumps on second execution +# +create table t1 ( +id int(11) unsigned not null primary key auto_increment, +partner_id varchar(35) not null, +t1_status_id int(10) unsigned +); +insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"), +("3", "partner3", "10"), ("4", "partner4", "10"); +create table t2 ( +id int(11) unsigned not null default '0', +t1_line_id int(11) unsigned not null default '0', +article_id varchar(20), +sequence int(11) not null default '0', +primary key (id,t1_line_id) +); +insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"), +("2", "2", "sup", "2"), ("2", "3", "sup", "3"), +("2", "4", "imp", "4"), ("3", "1", "sup", "0"), +("4", "1", "sup", "0"); +create table t3 ( +id int(11) not null default '0', +preceeding_id int(11) not null default '0', +primary key (id,preceeding_id) +); +create table t4 ( +user_id varchar(50) not null, +article_id varchar(20) not null, +primary key (user_id,article_id) +); +insert into t4 values("nicke", "imp"); +prepare stmt from +'select t1.partner_id +from t1 +where + t1.id in ( + select pl_inner.id + from t2 as pl_inner + where pl_inner.article_id in ( + select t4.article_id from t4 + where t4.user_id = \'nicke\' + ) + )'; +execute stmt; +partner_id +partner2 +execute stmt; +partner_id +partner2 +drop table t1,t2,t3,t4; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-03-07 15:41:45 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-14 17:54:12 +0000 @@ -2,7 +2,7 @@ show variables like 'join_cache_level'; Variable_name Value join_cache_level 6 -drop table if exists t0, t1, t2, t10, t11, t12; +drop table if exists t0, t1, t2, t3, t4, t10, t11, t12; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); @@ -876,6 +876,57 @@ DROP VIEW v2, v3; # End of Bug#49198 # +# BUG#43768: Prepared query with nested subqueries core dumps on second execution +# +create table t1 ( +id int(11) unsigned not null primary key auto_increment, +partner_id varchar(35) not null, +t1_status_id int(10) unsigned +); +insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"), +("3", "partner3", "10"), ("4", "partner4", "10"); +create table t2 ( +id int(11) unsigned not null default '0', +t1_line_id int(11) unsigned not null default '0', +article_id varchar(20), +sequence int(11) not null default '0', +primary key (id,t1_line_id) +); +insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"), +("2", "2", "sup", "2"), ("2", "3", "sup", "3"), +("2", "4", "imp", "4"), ("3", "1", "sup", "0"), +("4", "1", "sup", "0"); +create table t3 ( +id int(11) not null default '0', +preceeding_id int(11) not null default '0', +primary key (id,preceeding_id) +); +create table t4 ( +user_id varchar(50) not null, +article_id varchar(20) not null, +primary key (user_id,article_id) +); +insert into t4 values("nicke", "imp"); +prepare stmt from +'select t1.partner_id +from t1 +where + t1.id in ( + select pl_inner.id + from t2 as pl_inner + where pl_inner.article_id in ( + select t4.article_id from t4 + where t4.user_id = \'nicke\' + ) + )'; +execute stmt; +partner_id +partner2 +execute stmt; +partner_id +partner2 +drop table t1,t2,t3,t4; +# # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off # CREATE TABLE t0 (a INT); === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2010-02-24 11:33:42 +0000 +++ b/mysql-test/t/subselect_sj.test 2010-03-14 17:54:12 +0000 @@ -2,7 +2,7 @@ # Nested Loops semi-join subquery evaluation tests # --disable_warnings -drop table if exists t0, t1, t2, t10, t11, t12; +drop table if exists t0, t1, t2, t3, t4, t10, t11, t12; --enable_warnings # @@ -770,3 +770,60 @@ DROP VIEW v2, v3; --echo # End of Bug#49198 + +--echo # +--echo # BUG#43768: Prepared query with nested subqueries core dumps on second execution +--echo # +create table t1 ( + id int(11) unsigned not null primary key auto_increment, + partner_id varchar(35) not null, + t1_status_id int(10) unsigned +); + +insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"), + ("3", "partner3", "10"), ("4", "partner4", "10"); + +create table t2 ( + id int(11) unsigned not null default '0', + t1_line_id int(11) unsigned not null default '0', + article_id varchar(20), + sequence int(11) not null default '0', + primary key (id,t1_line_id) +); + +insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"), + ("2", "2", "sup", "2"), ("2", "3", "sup", "3"), + ("2", "4", "imp", "4"), ("3", "1", "sup", "0"), + ("4", "1", "sup", "0"); +create table t3 ( + id int(11) not null default '0', + preceeding_id int(11) not null default '0', + primary key (id,preceeding_id) +); + +create table t4 ( + user_id varchar(50) not null, + article_id varchar(20) not null, + primary key (user_id,article_id) +); + +insert into t4 values("nicke", "imp"); +prepare stmt from +'select t1.partner_id +from t1 +where + t1.id in ( + select pl_inner.id + from t2 as pl_inner + where pl_inner.article_id in ( + select t4.article_id from t4 + where t4.user_id = \'nicke\' + ) + )'; + +execute stmt; +execute stmt; +drop table t1,t2,t3,t4; + + + === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2010-03-07 15:41:45 +0000 +++ b/sql/opt_subselect.cc 2010-03-14 17:54:12 +0000 @@ -963,7 +963,6 @@ { /* Action #1: Mark the constant tables to be pulled out */ table_map pulled_tables= 0; - List_iterator child_li(sj_nest->nested_join->join_list); TABLE_LIST *tbl; while ((tbl= child_li++)) @@ -971,12 +970,34 @@ if (tbl->table) { tbl->table->reginfo.join_tab->emb_sj_nest= sj_nest; +#if 0 + /* + Do not pull out tables because they are constant. This operation has + a problem: + - Some constant tables may become/cease to be constant across PS + re-executions + - Contrary to our initial assumption, it turned out that table pullout + operation is not easily undoable. + + The solution is to leave constant tables where they are. This will + affect only constant tables that are 1-row or empty, tables that are + constant because they are accessed via eq_ref(const) access will + still be pulled out as functionally-dependent. + + This will cause us to miss the chance to flatten some of the + subqueries, but since const tables do not generate many duplicates, + it really doesn't matter that much whether they were pulled out or + not. + + All of this was done as fix for BUG#43768. + */ if (tbl->table->map & join->const_table_map) { pulled_tables |= tbl->table->map; DBUG_PRINT("info", ("Table %s pulled out (reason: constant)", tbl->table->alias)); } +#endif } } @@ -1048,6 +1069,7 @@ pointers. */ child_li.remove(); + sj_nest->nested_join->used_tables &= ~tbl->table->map; upper_join_list->push_back(tbl); tbl->join_list= upper_join_list; tbl->embedding= sj_nest->embedding; @@ -1104,20 +1126,20 @@ DBUG_ENTER("optimize_semijoin_nests"); List_iterator sj_list_it(join->select_lex->sj_nests); TABLE_LIST *sj_nest; - /* - The statement may have been executed with 'semijoin=on' earlier. - We need to verify that 'semijoin=on' still holds. - */ - if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_SEMIJOIN) && - optimizer_flag(join->thd, OPTIMIZER_SWITCH_MATERIALIZATION)) + while ((sj_nest= sj_list_it++)) { - while ((sj_nest= sj_list_it++)) + /* semi-join nests with only constant tables are not valid */ + /// DBUG_ASSERT(sj_nest->sj_inner_tables & ~join->const_table_map); + + sj_nest->sj_mat_info= NULL; + /* + The statement may have been executed with 'semijoin=on' earlier. + We need to verify that 'semijoin=on' still holds. + */ + if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_SEMIJOIN) && + optimizer_flag(join->thd, OPTIMIZER_SWITCH_MATERIALIZATION)) { - /* semi-join nests with only constant tables are not valid */ - DBUG_ASSERT(sj_nest->sj_inner_tables & ~join->const_table_map); - - sj_nest->sj_mat_info= NULL; - if (sj_nest->sj_inner_tables && /* not everything was pulled out */ + if ((sj_nest->sj_inner_tables & ~join->const_table_map) && /* not everything was pulled out */ !sj_nest->sj_subq_pred->is_correlated && sj_nest->sj_subq_pred->types_allow_materialization) { @@ -1128,7 +1150,7 @@ The best plan to run the subquery is now in join->best_positions, save it. */ - uint n_tables= my_count_bits(sj_nest->sj_inner_tables); + uint n_tables= my_count_bits(sj_nest->sj_inner_tables & ~join->const_table_map); SJ_MATERIALIZATION_INFO* sjm; if (!(sjm= new SJ_MATERIALIZATION_INFO) || !(sjm->positions= (POSITION*)join->thd->alloc(sizeof(POSITION)* @@ -1443,7 +1465,7 @@ new_join_tab->emb_sj_nest->nested_join->sj_corr_tables | new_join_tab->emb_sj_nest->nested_join->sj_depends_on; const table_map sj_inner_tables= - new_join_tab->emb_sj_nest->sj_inner_tables; + new_join_tab->emb_sj_nest->sj_inner_tables & ~join->const_table_map; /* Enter condition: === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-03-07 15:41:45 +0000 +++ b/sql/sql_select.cc 2010-03-14 17:54:12 +0000 @@ -5127,7 +5127,9 @@ /* number of tables that remain to be optimized */ n_tables= size_remain= my_count_bits(remaining_tables & (join->emb_sjm_nest? - join->emb_sjm_nest->sj_inner_tables : + (join->emb_sjm_nest->sj_inner_tables & + ~join->const_table_map) + : ~(table_map)0)); do { @@ -5387,7 +5389,7 @@ table_map allowed_tables= ~(table_map)0; if (join->emb_sjm_nest) - allowed_tables= join->emb_sjm_nest->sj_inner_tables; + allowed_tables= join->emb_sjm_nest->sj_inner_tables & ~join->const_table_map; for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++) {