diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 14 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 10 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 27 | ||||
| -rw-r--r-- | sql/item_subselect.h | 8 | ||||
| -rw-r--r-- | sql/sql_union.cc | 7 |
5 files changed, 56 insertions, 10 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index a196b05b142..a2e83729513 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1489,3 +1489,17 @@ set sort_buffer_size = (select s1 from t1); ERROR 21000: Subquery returns more than 1 row do (select * from t1); drop table t1; +create table t1 (s1 char); +insert into t1 values ('e'); +select * from t1 where 'f' > any (select s1 from t1); +s1 +e +select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1); +s1 +e +explain select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t1 system NULL NULL NULL NULL 1 +3 UNION t1 system NULL NULL NULL NULL 1 +drop table t1; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index e585375b385..3648210b943 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1010,3 +1010,13 @@ insert into t1 values (2); set sort_buffer_size = (select s1 from t1); do (select * from t1); drop table t1; + +# +# optimized ALL/ANY with union +# +create table t1 (s1 char); +insert into t1 values ('e'); +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; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 6ac191af267..589a41052c5 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -35,7 +35,7 @@ inline Item * and_items(Item* cond, Item *item) } Item_subselect::Item_subselect(): - Item_result_field(), engine_owner(1), value_assigned(0), substitution(0), + Item_result_field(), value_assigned(0), substitution(0), engine(0), used_tables_cache(0), have_to_be_excluded(0), const_item_cache(1), engine_changed(0) { @@ -66,8 +66,7 @@ void Item_subselect::init(st_select_lex *select_lex, Item_subselect::~Item_subselect() { - if (engine_owner) - delete engine; + delete engine; } Item_subselect::trans_res @@ -183,7 +182,8 @@ Item_singlerow_subselect::Item_singlerow_subselect(st_select_lex *select_lex) DBUG_VOID_RETURN; } -Item_maxmin_subselect::Item_maxmin_subselect(st_select_lex *select_lex, +Item_maxmin_subselect::Item_maxmin_subselect(Item_subselect *parent, + st_select_lex *select_lex, bool max) :Item_singlerow_subselect() { @@ -192,6 +192,14 @@ Item_maxmin_subselect::Item_maxmin_subselect(st_select_lex *select_lex, max_columns= 1; maybe_null= 1; max_columns= 1; + + /* + Following information was collected during performing fix_fields() + of Items belonged to subquery, which will be not repeated + */ + used_tables_cache= parent->get_used_tables_cache(); + const_item_cache= parent->get_const_item_cache(); + DBUG_VOID_RETURN; } @@ -527,9 +535,16 @@ Item_in_subselect::single_value_transformer(JOIN *join, func == &Item_bool_func2::ge_creator || func == &Item_bool_func2::le_creator)) { + if (substitution) + { + // It is second (third, ...) SELECT of UNION => All is done + DBUG_RETURN(RES_OK); + } + Item *subs; if (!select_lex->group_list.elements && - !select_lex->with_sum_func) + !select_lex->with_sum_func && + !(select_lex->next_select())) { Item *item; subs_type type= substype(); @@ -565,7 +580,7 @@ Item_in_subselect::single_value_transformer(JOIN *join, // remove LIMIT placed by ALL/ANY subquery select_lex->master_unit()->global_parameters->select_limit= HA_POS_ERROR; - subs= new Item_maxmin_subselect(select_lex, + subs= new Item_maxmin_subselect(this, select_lex, (func == &Item_bool_func2::le_creator || func == &Item_bool_func2::lt_creator)); } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 3a543ff288c..7e735165c02 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -33,7 +33,6 @@ typedef Item_bool_func2* (*compare_func_creator)(Item*, Item*); class Item_subselect :public Item_result_field { - my_bool engine_owner; /* Is this item owner of engine */ my_bool value_assigned; /* value already assigned to subselect */ protected: /* thread handler, will be assigned in fix_fields only */ @@ -90,6 +89,8 @@ public: virtual void fix_length_and_dec(); table_map used_tables() const; bool const_item() const; + inline table_map get_used_tables_cache() { return used_tables_cache; } + inline bool get_const_item_cache() { return const_item_cache; } void update_used_tables(); void print(String *str) { @@ -144,10 +145,11 @@ public: }; /* used in static ALL/ANY optimisation */ -class Item_maxmin_subselect: public Item_singlerow_subselect +class Item_maxmin_subselect :public Item_singlerow_subselect { public: - Item_maxmin_subselect(st_select_lex *select_lex, bool max); + Item_maxmin_subselect(Item_subselect *parent, + st_select_lex *select_lex, bool max); }; /* exists subselect */ diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 3a903d2e896..de2bb4a786c 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -119,13 +119,18 @@ int st_select_lex_unit::prepare(THD *thd, select_result *sel_result, SELECT_LEX *select_cursor,*sl; DBUG_ENTER("st_select_lex_unit::prepare"); + /* + result object should be reassigned even if preparing already done for + max/min subquery (ALL/ANY optimization) + */ + result= sel_result; + if (prepared) DBUG_RETURN(0); prepared= 1; res= 0; found_rows_for_union= first_select_in_union()->options & OPTION_FOUND_ROWS; TMP_TABLE_PARAM tmp_table_param; - result= sel_result; t_and_f= tables_and_fields_initied; bzero((char *)&tmp_table_param,sizeof(TMP_TABLE_PARAM)); |