diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 11 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 10 | ||||
| -rw-r--r-- | sql/sql_select.cc | 30 | ||||
| -rw-r--r-- | sql/sql_select.h | 6 |
4 files changed, 52 insertions, 5 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index a2e83729513..db0c3184120 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1503,3 +1503,14 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 system NULL NULL NULL NULL 1 3 UNION t1 system NULL NULL NULL NULL 1 drop table t1; +CREATE TABLE t1 (number char(11) NOT NULL default '') TYPE=MyISAM CHARSET=latin1; +INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); +CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) TYPE=MyISAM CHARSET=latin1; +INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6'); +select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c; +phone code +69294728265 6 +18621828126 1862 +89356874041 NULL +95895001874 NULL +drop table t1, t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 3648210b943..576e96c5c18 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1020,3 +1020,13 @@ select * from t1 where 'f' > any (select s1 from t1); select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1); explain select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1); drop table t1; + +# +# filesort in subquery (restoring join_tab) +# +CREATE TABLE t1 (number char(11) NOT NULL default '') TYPE=MyISAM CHARSET=latin1; +INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); +CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) TYPE=MyISAM CHARSET=latin1; +INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6'); +select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c; +drop table t1, t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 222f5707c69..65515ce7ad9 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -983,8 +983,7 @@ JOIN::optimize() } } - if (select_lex != &thd->lex.select_lex && - select_lex->linkage != DERIVED_TABLE_TYPE) + if (select_lex->master_unit()->dependent) { if (!(tmp_join= (JOIN*)thd->alloc(sizeof(JOIN)))) DBUG_RETURN(-1); @@ -997,10 +996,10 @@ JOIN::optimize() DBUG_RETURN(0); } + /* Restore values in temporary join */ - void JOIN::restore_tmp() { memcpy(tmp_join, this, (size_t) sizeof(JOIN)); @@ -1042,12 +1041,29 @@ JOIN::reinit() if (items0) set_items_ref_array(items0); + if (join_tab_save) + memcpy(join_tab, join_tab_save, sizeof(JOIN_TAB) * tables); + if (tmp_join) restore_tmp(); DBUG_RETURN(0); } + +bool +JOIN::save_join_tab() +{ + if (!join_tab_save && select_lex->master_unit()->dependent) + { + if (!(join_tab_save= (JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB) * tables))) + return 1; + memcpy(join_tab_save, join_tab, sizeof(JOIN_TAB) * tables); + } + return 0; +} + + /* Exec select */ @@ -1249,6 +1265,10 @@ JOIN::exec() if (curr_join->group_list) { thd->proc_info= "Creating sort index"; + if (curr_join->join_tab == join_tab && save_join_tab()) + { + DBUG_VOID_RETURN; + } if (create_sort_index(thd, curr_join, curr_join->group_list, HA_POS_ERROR, HA_POS_ERROR) || make_group_fields(this, curr_join)) @@ -1430,6 +1450,10 @@ JOIN::exec() } } } + if (curr_join->join_tab == join_tab && save_join_tab()) + { + DBUG_VOID_RETURN; + } if (create_sort_index(thd, curr_join, curr_join->group_list ? curr_join->group_list : curr_join->order, diff --git a/sql/sql_select.h b/sql/sql_select.h index 7306f609f66..14450347244 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -131,6 +131,7 @@ class JOIN :public Sql_alloc { public: JOIN_TAB *join_tab,**best_ref,**map2table; + JOIN_TAB *join_tab_save; //saved join_tab for subquery reexecution TABLE **table,**all_tables,*sort_by_table; uint tables,const_tables; uint send_group_parts; @@ -202,7 +203,7 @@ class JOIN :public Sql_alloc void init(THD *thd_arg, List<Item> &fields, ulong select_options_arg, select_result *result_arg) { - join_tab= 0; + join_tab= join_tab_save= 0; table= 0; tables= 0; const_tables= 0; @@ -242,7 +243,7 @@ class JOIN :public Sql_alloc zero_result_cause= 0; optimized= 0; - fields_list = fields; + fields_list= fields; bzero((char*) &keyuse,sizeof(keyuse)); tmp_table_param.copy_field=0; tmp_table_param.end_write_records= HA_POS_ERROR; @@ -280,6 +281,7 @@ class JOIN :public Sql_alloc int rollup_send_data(uint idx); bool test_in_subselect(Item **where); void clear(); + bool save_join_tab(); }; |