diff options
| author | Igor Babaev <igor@askmonty.org> | 2011-05-16 22:39:43 -0700 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2011-05-16 22:39:43 -0700 |
| commit | 704f97035fed1a454ea3300d2ad5dda906dbce50 (patch) | |
| tree | 7204d0246b026782b99b8effe9a0ec682e90e209 /sql | |
| parent | 7b797fe66d7167a8f5cbb071a06a640a627f2186 (diff) | |
| parent | a8be09a9f26b43139ad84ead314898d23394f673 (diff) | |
| download | mariadb-git-704f97035fed1a454ea3300d2ad5dda906dbce50.tar.gz | |
Merged the code of MWL#106 into 5.3
Resolved all conflicts, bad merges and fixed a few minor bugs in the code.
Commented out the queries from multi_update, view, subselect_sj, func_str,
derived_view, view_grant that failed either with crashes in ps-protocol or
with wrong results.
The failures are clear indications of some bugs in the code and these bugs
are to be fixed.
Diffstat (limited to 'sql')
45 files changed, 3202 insertions, 797 deletions
diff --git a/sql/field.cc b/sql/field.cc index 461d6c1eda2..3329d21f9d3 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -10484,3 +10484,27 @@ Field::set_datetime_warning(MYSQL_ERROR::enum_warning_level level, uint code, field_name); } } + + +/* + @brief + Return possible keys for a field + + @details + Return bit map of keys over this field which can be used by the range + optimizer. For a field of a generic table such keys are all keys that starts + from this field. For a field of a materialized derived table/view such keys + are all keys in which this field takes a part. This is less restrictive as + keys for a materialized derived table/view are generated on the fly from + present fields, thus the case when a field for the beginning of a key is + absent is impossible. + + @return map of possible keys +*/ + +key_map Field::get_possible_keys() +{ + DBUG_ASSERT(table->pos_in_table_list); + return (table->pos_in_table_list->is_materialized_derived() ? + part_of_key : key_start); +} diff --git a/sql/field.h b/sql/field.h index d695479f197..ab9a783b287 100644 --- a/sql/field.h +++ b/sql/field.h @@ -588,6 +588,9 @@ public: DBUG_ASSERT(0); return GEOM_GEOMETRY; } + + key_map get_possible_keys(); + /* Hash value */ virtual void hash(ulong *nr, ulong *nr2); diff --git a/sql/handler.cc b/sql/handler.cc index 520402c8e38..d3073c487d3 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -2599,8 +2599,9 @@ int handler::update_auto_increment() void handler::column_bitmaps_signal() { DBUG_ENTER("column_bitmaps_signal"); - DBUG_PRINT("info", ("read_set: 0x%lx write_set: 0x%lx", (long) table->read_set, - (long) table->write_set)); + if (table) + DBUG_PRINT("info", ("read_set: 0x%lx write_set: 0x%lx", + (long) table->read_set, (long) table->write_set)); DBUG_VOID_RETURN; } diff --git a/sql/item.cc b/sql/item.cc index 0b91d4eae9f..0660ea4fd41 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -767,6 +767,23 @@ bool Item_field::register_field_in_bitmap(uchar *arg) return 0; } + +/* + Mark field in write_map + + NOTES + This is used by UPDATE to register underlying fields of used view fields. +*/ + +bool Item_field::register_field_in_write_map(uchar *arg) +{ + TABLE *table= (TABLE *) arg; + if (field->table == table || !table) + bitmap_set_bit(field->table->write_set, field->field_index); + return 0; +} + + bool Item::check_cols(uint c) { if (c != 1) @@ -2278,6 +2295,10 @@ table_map Item_field::used_tables() const return (depended_from ? OUTER_REF_TABLE_BIT : field->table->map); } +table_map Item_field::all_used_tables() const +{ + return (depended_from ? OUTER_REF_TABLE_BIT : field->table->map); +} void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref) { @@ -2530,7 +2551,7 @@ my_decimal *Item_float::val_decimal(my_decimal *decimal_value) void Item_string::print(String *str, enum_query_type query_type) { - if (query_type == QT_ORDINARY && is_cs_specified()) + if (query_type != QT_IS && is_cs_specified()) { str->append('_'); str->append(collation.collation->csname); @@ -2538,7 +2559,7 @@ void Item_string::print(String *str, enum_query_type query_type) str->append('\''); - if (query_type == QT_ORDINARY || + if (query_type != QT_IS || my_charset_same(str_value.charset(), system_charset_info)) { str_value.print(str); @@ -4030,6 +4051,34 @@ resolve_ref_in_select_and_group(THD *thd, Item_ident *ref, SELECT_LEX *select) } +/* + @brief + Whether a table belongs to an outer select. + + @param table table to check + @param select current select + + @details + Try to find select the table belongs to by ascending the derived tables chain. +*/ + +static +bool is_outer_table(TABLE_LIST *table, SELECT_LEX *select) +{ + DBUG_ASSERT(table->select_lex != select); + TABLE_LIST *tl; + + for (tl= select->master_unit()->derived; + tl && tl->is_merged_derived(); + select= tl->select_lex, tl= select->master_unit()->derived) + { + if (tl->select_lex == table->select_lex) + return FALSE; + } + return TRUE; +} + + /** Resolve the name of an outer select column reference. @@ -4478,7 +4527,8 @@ bool Item_field::fix_fields(THD *thd, Item **reference) if (!outer_fixed && cached_table && cached_table->select_lex && context->select_lex && - cached_table->select_lex != context->select_lex) + cached_table->select_lex != context->select_lex && + is_outer_table(cached_table, context->select_lex)) { int ret; if ((ret= fix_outer_field(thd, &from_field, reference)) < 0) @@ -5948,8 +5998,9 @@ public: st_select_lex *sel; for (sel= current_select; sel; sel= sel->outer_select()) { + List_iterator<TABLE_LIST> li(sel->leaf_tables); TABLE_LIST *tbl; - for (tbl= sel->leaf_tables; tbl; tbl= tbl->next_leaf) + while ((tbl= li++)) { if (tbl->table == item->field->table) { @@ -8433,6 +8484,8 @@ Item_result Item_type_holder::result_type() const enum_field_types Item_type_holder::get_real_type(Item *item) { + if (item->type() == REF_ITEM) + item= item->real_item(); switch(item->type()) { case FIELD_ITEM: diff --git a/sql/item.h b/sql/item.h index 84fd4b5ce24..5527ffd9ca1 100644 --- a/sql/item.h +++ b/sql/item.h @@ -822,6 +822,7 @@ public: class Field_enumerator) */ virtual table_map used_tables() const { return (table_map) 0L; } + virtual table_map all_used_tables() const { return used_tables(); } /* Return table map of tables that can't be NULL tables (tables that are used in a context where if they would contain a NULL row generated @@ -979,10 +980,13 @@ public: virtual bool reset_query_id_processor(uchar *query_id_arg) { return 0; } virtual bool is_expensive_processor(uchar *arg) { return 0; } virtual bool register_field_in_read_map(uchar *arg) { return 0; } + virtual bool register_field_in_write_map(uchar *arg) { return 0; } virtual bool enumerate_field_refs_processor(uchar *arg) { return 0; } virtual bool mark_as_eliminated_processor(uchar *arg) { return 0; } virtual bool eliminate_subselect_processor(uchar *arg) { return 0; } virtual bool set_fake_select_as_master_processor(uchar *arg) { return 0; } + virtual bool view_used_tables_processor(uchar *arg) { return 0; } + virtual bool eval_not_null_tables(uchar *opt_arg) { return 0; } /* To call bool function for all arguments */ struct bool_func_call_args @@ -998,6 +1002,7 @@ public: (this->*(info->bool_function))(); return FALSE; } + /* The next function differs from the previous one that a bitmap to be updated is passed as uchar *arg. @@ -1076,6 +1081,7 @@ public: *arg= NULL; return TRUE; } + /* @brief Processor used to check acceptability of an item in the defining @@ -1209,8 +1215,8 @@ public: { return Field::GEOM_GEOMETRY; }; String *check_well_formed_result(String *str, bool send_error= 0); bool eq_by_collation(Item *item, bool binary_cmp, CHARSET_INFO *cs); - Item* set_expr_cache(THD *thd, List<Item*> &depends_on); + virtual Item *get_cached_item() { return NULL; } virtual Item_equal *get_item_equal() { return NULL; } virtual void set_item_equal(Item_equal *item_eq) {}; @@ -1225,6 +1231,13 @@ public: join_tab_idx= join_tab_idx_arg; } virtual uint get_join_tab_idx() { return join_tab_idx; } + + table_map view_used_tables(TABLE_LIST *view) + { + view->view_used_tables= 0; + walk(&Item::view_used_tables_processor, 0, (uchar *) view); + return view->view_used_tables; + } }; @@ -1711,6 +1724,7 @@ public: int save_in_field(Field *field,bool no_conversions); void save_org_in_field(Field *field); table_map used_tables() const; + table_map all_used_tables() const; enum Item_result result_type () const { return field->result_type(); @@ -1740,6 +1754,7 @@ public: bool add_field_to_set_processor(uchar * arg); bool find_item_in_field_list_processor(uchar *arg); bool register_field_in_read_map(uchar *arg); + bool register_field_in_write_map(uchar *arg); bool register_field_in_bitmap(uchar *arg); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} bool vcol_in_partition_func_processor(uchar *bool_arg); @@ -2515,7 +2530,7 @@ public: } void update_used_tables() { - if (!depended_from) + if (!depended_from) (*ref)->update_used_tables(); } bool const_item() const @@ -2806,12 +2821,14 @@ public: class Item_direct_view_ref :public Item_direct_ref { Item_equal *item_equal; + TABLE_LIST *view; public: Item_direct_view_ref(Name_resolution_context *context_arg, Item **item, - const char *table_name_arg, - const char *field_name_arg) + const char *table_name_arg, + const char *field_name_arg, + TABLE_LIST *view_arg) :Item_direct_ref(context_arg, item, table_name_arg, field_name_arg), - item_equal(0) {} + item_equal(0), view(view_arg) {} /* Constructor need to process subselect with temporary tables (see Item) */ Item_direct_view_ref(THD *thd, Item_direct_ref *item) :Item_direct_ref(thd, item), item_equal(0) {} @@ -2835,6 +2852,24 @@ public: bool subst_argument_checker(uchar **arg); Item *equal_fields_propagator(uchar *arg); Item *replace_equal_field(uchar *arg); + table_map used_tables() const + { + return depended_from ? + OUTER_REF_TABLE_BIT : + (view->merged ? (*ref)->used_tables() : view->table->map); + } + bool walk(Item_processor processor, bool walk_subquery, uchar *arg) + { + return (*ref)->walk(processor, walk_subquery, arg) || + (this->*processor)(arg); + } + bool view_used_tables_processor(uchar *arg) + { + TABLE_LIST *view_arg= (TABLE_LIST *) arg; + if (view_arg == view) + view_arg->view_used_tables|= (*ref)->used_tables(); + return 0; + } }; @@ -3191,6 +3226,17 @@ public: value. */ +/* + Cached_item_XXX objects are not exactly caches. They do the following: + + Each Cached_item_XXX object has + - its source item + - saved value of the source item + - cmp() method that compares the saved value with the current value of the + source item, and if they were not equal saves item's value into the saved + value. +*/ + class Cached_item :public Sql_alloc { public: diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 79eb473cb75..1a9e12efccc 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2396,6 +2396,16 @@ bool Item_func_between::fix_fields(THD *thd, Item **ref) thd->lex->current_select->between_count++; + + return 0; +} + + +bool Item_func_between::eval_not_null_tables(uchar *opt_arg) +{ + if (Item_func_opt_neg::eval_not_null_tables(NULL)) + return 1; + /* not_null_tables_cache == union(T1(e),T1(e1),T1(e2)) */ if (pred_level && !negated) return 0; @@ -2404,9 +2414,8 @@ bool Item_func_between::fix_fields(THD *thd, Item **ref) not_null_tables_cache= (args[0]->not_null_tables() | (args[1]->not_null_tables() & args[2]->not_null_tables())); - return 0; -} +} void Item_func_between::fix_length_and_dec() @@ -2767,13 +2776,22 @@ Item_func_if::fix_fields(THD *thd, Item **ref) if (Item_func::fix_fields(thd, ref)) return 1; + return 0; +} + + +bool +Item_func_if::eval_not_null_tables(uchar *opt_arg) +{ + if (Item_func::eval_not_null_tables(NULL)) + return 1; + not_null_tables_cache= (args[1]->not_null_tables() & args[2]->not_null_tables()); return 0; } - void Item_func_if::fix_length_and_dec() { @@ -3955,11 +3973,22 @@ bool Item_func_in::nulls_in_row() bool Item_func_in::fix_fields(THD *thd, Item **ref) { - Item **arg, **arg_end; if (Item_func_opt_neg::fix_fields(thd, ref)) return 1; + return 0; +} + + +bool +Item_func_in::eval_not_null_tables(uchar *opt_arg) +{ + Item **arg, **arg_end; + + if (Item_func_opt_neg::eval_not_null_tables(NULL)) + return 1; + /* not_null_tables_cache == union(T1(e),union(T1(ei))) */ if (pred_level && negated) return 0; @@ -4388,7 +4417,6 @@ Item_cond::fix_fields(THD *thd, Item **ref) */ while ((item=li++)) { - table_map tmp_table_map; while (item->type() == Item::COND_ITEM && ((Item_cond*) item)->functype() == functype() && !((Item_cond*) item)->list.is_empty()) @@ -4410,11 +4438,12 @@ Item_cond::fix_fields(THD *thd, Item **ref) and_tables_cache= (table_map) 0; else { - tmp_table_map= item->not_null_tables(); + table_map tmp_table_map= item->not_null_tables(); not_null_tables_cache|= tmp_table_map; and_tables_cache&= tmp_table_map; const_item_cache= FALSE; - } + } + with_sum_func= with_sum_func || item->with_sum_func; with_field= with_field || item->with_field; with_subselect|= item->with_subselect; @@ -4429,6 +4458,28 @@ Item_cond::fix_fields(THD *thd, Item **ref) } +bool +Item_cond::eval_not_null_tables(uchar *opt_arg) +{ + Item *item; + List_iterator<Item> li(list); + and_tables_cache= ~(table_map) 0; + while ((item=li++)) + { + table_map tmp_table_map; + if (item->const_item()) + and_tables_cache= (table_map) 0; + else + { + tmp_table_map= item->not_null_tables(); + not_null_tables_cache|= tmp_table_map; + and_tables_cache&= tmp_table_map; + } + } + return 0; +} + + void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref) { List_iterator<Item> li(list); diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 7b7fe29470e..9dbcc8e1261 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -647,6 +647,7 @@ public: bool is_bool_func() { return 1; } CHARSET_INFO *compare_collation() { return cmp_collation.collation; } uint decimal_precision() const { return 1; } + bool eval_not_null_tables(uchar *opt_arg); }; @@ -746,6 +747,7 @@ public: void fix_length_and_dec(); uint decimal_precision() const; const char *func_name() const { return "if"; } + bool eval_not_null_tables(uchar *opt_arg); }; @@ -1272,6 +1274,7 @@ public: bool nulls_in_row(); bool is_bool_func() { return 1; } CHARSET_INFO *compare_collation() { return cmp_collation.collation; } + bool eval_not_null_tables(uchar *opt_arg); }; class cmp_item_row :public cmp_item @@ -1527,6 +1530,7 @@ public: bool subst_argument_checker(uchar **arg) { return TRUE; } Item *compile(Item_analyzer analyzer, uchar **arg_p, Item_transformer transformer, uchar *arg_t); + bool eval_not_null_tables(uchar *opt_arg); }; diff --git a/sql/item_func.cc b/sql/item_func.cc index 63b8419aaaa..5090120e3f9 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -202,7 +202,6 @@ Item_func::fix_fields(THD *thd, Item **ref) with_sum_func= with_sum_func || item->with_sum_func; with_field= with_field || item->with_field; used_tables_cache|= item->used_tables(); - not_null_tables_cache|= item->not_null_tables(); const_item_cache&= item->const_item(); with_subselect|= item->with_subselect; } @@ -231,6 +230,21 @@ Item_func::quick_fix_field() } +bool +Item_func::eval_not_null_tables(uchar *opt_arg) +{ + Item **arg,**arg_end; + if (arg_count) + { + for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++) + { + not_null_tables_cache|= (*arg)->not_null_tables(); + } + } + return FALSE; +} + + void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref) { Item **arg,**arg_end; @@ -3892,6 +3906,20 @@ bool Item_func_set_user_var::fix_fields(THD *thd, Item **ref) entry->collation.set(args[0]->collation.collation, DERIVATION_IMPLICIT); collation.set(entry->collation.collation, DERIVATION_IMPLICIT); cached_result_type= args[0]->result_type(); + { + /* + When this function is used in a derived table/view force the derived + table to be materialized to preserve possible side-effect of setting a + user variable. + */ + SELECT_LEX_UNIT *unit= thd->lex->current_select->master_unit(); + TABLE_LIST *derived; + for (derived= unit->derived; + derived; + derived= derived->select_lex->master_unit()->derived) + derived->set_materialized_derived(); + } + return FALSE; } diff --git a/sql/item_func.h b/sql/item_func.h index 5c5ea33f247..210f9c26fb3 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -190,6 +190,7 @@ public: Item_transformer transformer, uchar *arg_t); void traverse_cond(Cond_traverser traverser, void * arg, traverse_order order); + bool eval_not_null_tables(uchar *opt_arg); // bool is_expensive_processor(uchar *arg); // virtual bool is_expensive() { return 0; } inline double fix_result(double value) @@ -1682,14 +1683,7 @@ public: void fix_length_and_dec() { decimals=0; max_length=1; maybe_null=1;} bool check_vcol_func_processor(uchar *int_arg) { -#if 0 - DBUG_ENTER("Item_func_is_free_lock::check_vcol_func_processor"); - DBUG_PRINT("info", - ("check_vcol_func_processor returns TRUE: unsupported function")); - DBUG_RETURN(TRUE); -#else return trace_unsupported_by_check_vcol_func_processor(func_name()); -#endif } }; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index c8336bbabce..759c20d8bb1 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -3131,6 +3131,9 @@ int subselect_uniquesubquery_engine::exec() DBUG_RETURN(0); } + if (!tab->preread_init_done && tab->preread_init()) + DBUG_RETURN(1); + if (null_keypart) DBUG_RETURN(scan_table()); @@ -3263,7 +3266,7 @@ subselect_uniquesubquery_engine::~subselect_uniquesubquery_engine() int subselect_indexsubquery_engine::exec() { - DBUG_ENTER("subselect_indexsubquery_engine::exec"); + DBUG_ENTER("subselect_indexsubquery_engine"); int error; bool null_finding= 0; TABLE *table= tab->table; @@ -3294,6 +3297,9 @@ int subselect_indexsubquery_engine::exec() DBUG_RETURN(0); } + if (!tab->preread_init_done && tab->preread_init()) + DBUG_RETURN(1); + if (null_keypart) DBUG_RETURN(scan_table()); @@ -3395,10 +3401,13 @@ void subselect_uniquesubquery_engine::exclude() } -table_map subselect_engine::calc_const_tables(TABLE_LIST *table) +table_map subselect_engine::calc_const_tables(List<TABLE_LIST> &list) { table_map map= 0; - for (; table; table= table->next_leaf) + List_iterator<TABLE_LIST> ti(list); + TABLE_LIST *table; + //for (; table; table= table->next_leaf) + while ((table= ti++)) { TABLE *tbl= table->table; if (tbl && tbl->const_table) @@ -3961,7 +3970,7 @@ bool subselect_hash_sj_engine::init(List<Item> *tmp_columns) result_sink->get_tmp_table_param()->materialized_subquery= true; if (result_sink->create_result_table(thd, tmp_columns, TRUE, tmp_create_options, - "materialized subselect", TRUE)) + "materialized subselect", TRUE, TRUE)) DBUG_RETURN(TRUE); tmp_table= result_sink->table; diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 0c02713938e..7478cac66cf 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -599,6 +599,7 @@ public: virtual bool may_be_null() { return maybe_null; }; virtual table_map upper_select_const_tables()= 0; static table_map calc_const_tables(TABLE_LIST *); + static table_map calc_const_tables(List<TABLE_LIST> &list); virtual void print(String *str, enum_query_type query_type)= 0; virtual bool change_result(Item_subselect *si, select_result_interceptor *result, diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index f8dec1cf957..3876b07563e 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -61,12 +61,15 @@ class Parser_state; QT_ORDINARY -- ordinary SQL query. QT_IS -- SQL query to be shown in INFORMATION_SCHEMA (in utf8 and without - character set introducers). + character set introducers). + QT_VIEW_INTERNAL -- view internal representation (like QT_ORDINARY except + ORDER BY clause) */ enum enum_query_type { QT_ORDINARY, - QT_IS + QT_IS, + QT_VIEW_INTERNAL }; /* TODO convert all these three maps to Bitmap classes */ @@ -515,7 +518,6 @@ protected: #define OPTION_PROFILING (ULL(1) << 33) - /** Maximum length of time zone name that we support (Time zone name is char(64) in db). mysqlbinlog needs it. @@ -1290,11 +1292,9 @@ int mysql_explain_select(THD *thd, SELECT_LEX *sl, char const *type, select_result *result); bool mysql_union(THD *thd, LEX *lex, select_result *result, SELECT_LEX_UNIT *unit, ulong setup_tables_done_option); -bool mysql_handle_derived(LEX *lex, bool (*processor)(THD *thd, - LEX *lex, - TABLE_LIST *table)); -bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *t); -bool mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *t); +bool mysql_handle_derived(LEX *lex, uint phases); +bool mysql_handle_single_derived(LEX *lex, TABLE_LIST *derived, uint phases); +bool mysql_handle_list_of_derived(LEX *lex, TABLE_LIST *dt_list, uint phases); Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, Item ***copy_func, Field **from_field, Field **def_field, @@ -1303,6 +1303,11 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, bool make_copy_field, uint convert_blob_length); bool open_tmp_table(TABLE *table); +bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, + ENGINE_COLUMNDEF *start_recinfo, + ENGINE_COLUMNDEF **recinfo, + ulonglong options); + void sp_prepare_create_field(THD *thd, Create_field *sql_field); int prepare_create_field(Create_field *sql_field, uint *blob_columns, @@ -1609,17 +1614,21 @@ bool get_key_map_from_key_list(key_map *map, TABLE *table, bool insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, const char *table_name, List_iterator<Item> *it, bool any_privileges); +void make_leaves_list(List<TABLE_LIST> &list, TABLE_LIST *tables, + bool full_table_list, TABLE_LIST *boundary); bool setup_tables(THD *thd, Name_resolution_context *context, List<TABLE_LIST> *from_clause, TABLE_LIST *tables, - TABLE_LIST **leaves, bool select_insert); + List<TABLE_LIST> &leaves, bool select_insert, + bool full_table_list); bool setup_tables_and_check_access(THD *thd, Name_resolution_context *context, List<TABLE_LIST> *from_clause, TABLE_LIST *tables, - TABLE_LIST **leaves, + List<TABLE_LIST> &leaves, bool select_insert, ulong want_access_first, - ulong want_access); + ulong want_access, + bool full_table_list); int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, List<Item> *sum_func_list, uint wild_num); bool setup_fields(THD *thd, Item** ref_pointer_array, @@ -1638,7 +1647,7 @@ inline bool setup_fields_with_no_wrap(THD *thd, Item **ref_pointer_array, thd->lex->select_lex.no_wrap_view_item= FALSE; return res; } -int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, +int setup_conds(THD *thd, TABLE_LIST *tables, List<TABLE_LIST> &leaves, COND **conds); void wrap_ident(THD *thd, Item **conds); int setup_ftfuncs(SELECT_LEX* select); @@ -1661,7 +1670,8 @@ inline int open_and_lock_tables(THD *thd, TABLE_LIST *tables) /* simple open_and_lock_tables without derived handling for single table */ TABLE *open_n_lock_single_table(THD *thd, TABLE_LIST *table_l, thr_lock_type lock_type); -bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags); +bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags, + uint dt_phases); int lock_tables(THD *thd, TABLE_LIST *tables, uint counter, bool *need_reopen); int decide_logging_format(THD *thd, TABLE_LIST *tables); TABLE *open_temporary_table(THD *thd, const char *path, const char *db, @@ -1691,6 +1701,7 @@ void flush_tables(); bool is_equal(const LEX_STRING *a, const LEX_STRING *b); char *make_default_log_name(char *buff,const char* log_ext); char *make_once_alloced_filename(const char *basename, const char *ext); +void unfix_fields(List<Item> &items); #ifdef WITH_PARTITION_STORAGE_ENGINE uint fast_alter_partition_table(THD *thd, TABLE *table, @@ -2565,7 +2576,7 @@ Item * all_any_subquery_creator(Item *left_expr, inline void setup_table_map(TABLE *table, TABLE_LIST *table_list, uint tablenr) { table->used_fields= 0; - table->const_table= 0; + table_list->reset_const_table(); table->null_row= 0; table->status= STATUS_NO_RECORD; table->maybe_null= table_list->outer_join; @@ -2581,6 +2592,14 @@ inline void setup_table_map(TABLE *table, TABLE_LIST *table_list, uint tablenr) table->force_index_order= table->force_index_group= 0; table->covering_keys= table->s->keys_for_keyread; table->merge_keys.clear_all(); + TABLE_LIST *orig= table_list->select_lex ? + table_list->select_lex->master_unit()->derived : 0; + if (!orig || !orig->is_merged_derived()) + { + /* Tables merged from derived were set up already.*/ + table->covering_keys= table->s->keys_for_keyread; + table->merge_keys.clear_all(); + } } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 5d524c3acd1..35ce5d29644 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -9478,7 +9478,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, SEL_ARG_RANGE_SEQ seq; RANGE_SEQ_IF seq_if = {NULL, sel_arg_range_seq_init, sel_arg_range_seq_next, 0, 0}; handler *file= param->table->file; - ha_rows rows; + ha_rows rows= HA_POS_ERROR; uint keynr= param->real_keynr[idx]; DBUG_ENTER("check_quick_select"); @@ -9518,8 +9518,13 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, *mrr_flags |= HA_MRR_USE_DEFAULT_IMPL; *bufsize= param->thd->variables.mrr_buff_size; - rows= file->multi_range_read_info_const(keynr, &seq_if, (void*)&seq, 0, - bufsize, mrr_flags, cost); + /* + Skip materialized derived table/view result table from MRR check as + they aren't contain any data yet. + */ + if (param->table->pos_in_table_list->is_non_derived()) + rows= file->multi_range_read_info_const(keynr, &seq_if, (void*)&seq, 0, + bufsize, mrr_flags, cost); if (rows != HA_POS_ERROR) { param->quick_rows[keynr]= rows; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index d3fdda04520..46a900616ec 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -26,8 +26,6 @@ static int subq_sj_candidate_cmp(Item_in_subselect* const *el1, Item_in_subselect* const *el2); static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred); static TABLE_LIST *alloc_join_nest(THD *thd); -static -void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist); static uint get_tmp_table_rec_length(List<Item> &items); static double get_tmp_table_lookup_cost(THD *thd, double row_count, uint row_size); @@ -179,9 +177,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join) !join->having && !select_lex->with_sum_func && // 4 thd->thd_marker.emb_on_expr_nest && // 5 select_lex->outer_select()->join && // 6 - parent_unit->first_select()->leaf_tables && // 7 + parent_unit->first_select()->leaf_tables.elements && // 7 !in_subs->in_strategy && // 8 - select_lex->outer_select()->leaf_tables && // 9 + select_lex->outer_select()->leaf_tables.elements && // 9 !((join->select_options | // 10 select_lex->outer_select()->join->select_options) // 10 & SELECT_STRAIGHT_JOIN)) // 10 @@ -242,9 +240,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join) */ if (!(in_subs->in_strategy & SUBS_MATERIALIZATION && !select_lex->is_part_of_union() && // 1 - parent_unit->first_select()->leaf_tables && // 2 + parent_unit->first_select()->leaf_tables.elements && // 2 thd->lex->sql_command == SQLCOM_SELECT && // * - select_lex->outer_select()->leaf_tables && // 2A + select_lex->outer_select()->leaf_tables.elements && // 2A subquery_types_allow_materialization(in_subs) && // psergey-todo: duplicated_subselect_card_check: where it's done? (in_subs->is_top_level_item() || //3 @@ -401,6 +399,26 @@ bool convert_max_min_subquery(JOIN *join) } +bool check_for_outer_joins(List<TABLE_LIST> *join_list) +{ + TABLE_LIST *table; + NESTED_JOIN *nested_join; + List_iterator<TABLE_LIST> li(*join_list); + while ((table= li++)) + { + if ((nested_join= table->nested_join)) + { + if (check_for_outer_joins(&nested_join->join_list)) + return TRUE; + } + + if (table->outer_join) + return TRUE; + } + return FALSE; +} + + /* Convert semi-join subquery predicates into semi-join join nests @@ -454,11 +472,25 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) Item_in_subselect **in_subq; Item_in_subselect **in_subq_end; THD *thd= join->thd; + List_iterator<TABLE_LIST> ti(join->select_lex->leaf_tables); DBUG_ENTER("convert_join_subqueries_to_semijoins"); if (join->sj_subselects.elements() == 0) DBUG_RETURN(FALSE); + for (in_subq= join->sj_subselects.front(), + in_subq_end= join->sj_subselects.back(); + in_subq != in_subq_end; + in_subq++) + { + SELECT_LEX *subq_sel= (*in_subq)->get_select_lex(); + if (subq_sel->handle_derived(thd->lex, DT_OPTIMIZE)) + DBUG_RETURN(1); + if (subq_sel->handle_derived(thd->lex, DT_MERGE)) + DBUG_RETURN(TRUE); + subq_sel->update_used_tables(); + } + /* First, convert child join's subqueries. We proceed bottom-up here */ for (in_subq= join->sj_subselects.front(), in_subq_end= join->sj_subselects.back(); @@ -485,16 +517,24 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) // Temporary measure: disable semi-joins when they are together with outer // joins. - for (TABLE_LIST *tbl= join->select_lex->leaf_tables; tbl; tbl=tbl->next_leaf) + /* + while ((tbl= ti++)) { TABLE_LIST *embedding= tbl->embedding; - if (tbl->on_expr || (tbl->embedding && !(embedding->sj_on_expr && - !embedding->embedding))) + if (tbl->on_expr || + (embedding && embedding->outer_join && + !(embedding->sj_on_expr && !embedding->embedding))) { in_subq= join->sj_subselects.front(); arena= thd->activate_stmt_arena_if_needed(&backup); goto skip_conversion; } + }*/ + if (check_for_outer_joins(join->join_list)) + { + in_subq= join->sj_subselects.front(); + arena= thd->activate_stmt_arena_if_needed(&backup); + goto skip_conversion; } //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables); @@ -808,7 +848,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) st_select_lex *subq_lex= subq_pred->unit->first_select(); nested_join->join_list.empty(); List_iterator_fast<TABLE_LIST> li(subq_lex->top_join_list); - TABLE_LIST *tl, *last_leaf; + TABLE_LIST *tl; while ((tl= li++)) { tl->embedding= sj_nest; @@ -823,17 +863,15 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) NOTE: We actually insert them at the front! That's because the order is reversed in this list. */ - for (tl= parent_lex->leaf_tables; tl->next_leaf; tl= tl->next_leaf) ; - tl->next_leaf= subq_lex->leaf_tables; - last_leaf= tl; + parent_lex->leaf_tables.concat(&subq_lex->leaf_tables); /* Same as above for next_local chain (a theory: a next_local chain always starts with ::leaf_tables because view's tables are inserted after the view) */ - for (tl= parent_lex->leaf_tables; tl->next_local; tl= tl->next_local) ; - tl->next_local= subq_lex->leaf_tables; + for (tl= parent_lex->leaf_tables.head(); tl->next_local; tl= tl->next_local) ; + tl->next_local= subq_lex->leaf_tables.head(); /* A theory: no need to re-connect the next_global chain */ @@ -846,7 +884,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) /* n. Adjust the parent_join->tables counter */ uint table_no= parent_join->tables; /* n. Walk through child's tables and adjust table->map */ - for (tl= subq_lex->leaf_tables; tl; tl= tl->next_leaf, table_no++) + List_iterator_fast<TABLE_LIST> si(subq_lex->leaf_tables); + while ((tl= si++)) { tl->table->tablenr= table_no; tl->table->map= ((table_map)1) << table_no; @@ -856,6 +895,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) emb && emb->select_lex == old_sl; emb= emb->embedding) emb->select_lex= parent_join->select_lex; + table_no++; } parent_join->tables += subq_lex->join->tables; @@ -948,7 +988,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) */ save_lex= thd->lex->current_select; thd->lex->current_select=parent_join->select_lex; - parent_join->conds->fix_fields(parent_join->thd, &parent_join->conds); + if (!parent_join->conds->fixed) + parent_join->conds->fix_fields(parent_join->thd, &parent_join->conds); thd->lex->current_select=save_lex; parent_join->select_lex->where= parent_join->conds; } @@ -976,7 +1017,6 @@ static TABLE_LIST *alloc_join_nest(THD *thd) } -static void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist) { List_iterator<TABLE_LIST> it(*tlist); @@ -1545,6 +1585,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, TABLE_LIST *emb_sj_nest; POSITION *pos= join->positions + idx; remaining_tables &= ~new_join_tab->table->map; + bool disable_jbuf= join->thd->variables.join_cache_level == 0; pos->prefix_cost.convert_from_cost(*current_read_time); pos->prefix_record_count= *current_record_count; @@ -1710,7 +1751,8 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, optimize_wo_join_buffering(join, pos->first_loosescan_table, idx, remaining_tables, TRUE, //first_alt - pos->first_loosescan_table + n_tables, + disable_jbuf ? join->tables : + pos->first_loosescan_table + n_tables, current_record_count, current_read_time); /* @@ -1849,8 +1891,8 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, /* Need to re-run best-access-path as we prefix_rec_count has changed */ for (i= first_tab + mat_info->tables; i <= idx; i++) { - best_access_path(join, join->positions[i].table, rem_tables, i, FALSE, - prefix_rec_count, &curpos, &dummy); + best_access_path(join, join->positions[i].table, rem_tables, i, + disable_jbuf, prefix_rec_count, &curpos, &dummy); prefix_rec_count *= curpos.records_read; prefix_cost += curpos.read_time; } @@ -2149,6 +2191,7 @@ at_sjmat_pos(const JOIN *join, table_map remaining_tables, const JOIN_TAB *tab, void fix_semijoin_strategies_for_picked_join_order(JOIN *join) { uint table_count=join->tables; + bool disable_jbuf= join->thd->variables.join_cache_level == 0; uint tablenr; table_map remaining_tables= 0; table_map handled_tabs= 0; @@ -2210,8 +2253,9 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) join->cur_sj_inner_tables= 0; for (i= first + sjm->tables; i <= tablenr; i++) { - best_access_path(join, join->best_positions[i].table, rem_tables, i, FALSE, - prefix_rec_count, join->best_positions + i, &dummy); + best_access_path(join, join->best_positions[i].table, rem_tables, i, + disable_jbuf, prefix_rec_count, + join->best_positions + i, &dummy); prefix_rec_count *= join->best_positions[i].records_read; rem_tables &= ~join->best_positions[i].table->table->map; } diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 310b48756d0..d08cf6890e6 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -74,10 +74,12 @@ static int maxmin_in_range(bool max_fl, Field* field, COND *cond); # Multiplication of number of rows in all tables */ -static ulonglong get_exact_record_count(TABLE_LIST *tables) +static ulonglong get_exact_record_count(List<TABLE_LIST> &tables) { ulonglong count= 1; - for (TABLE_LIST *tl= tables; tl; tl= tl->next_leaf) + TABLE_LIST *tl; + List_iterator<TABLE_LIST> ti(tables); + while ((tl= ti++)) { ha_rows tmp= tl->table->file->records(); if ((tmp == HA_POS_ERROR)) @@ -230,9 +232,11 @@ static int get_index_max_value(TABLE *table, TABLE_REF *ref, uint range_fl) HA_ERR_... if a deadlock or a lock wait timeout happens, for example */ -int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) +int opt_sum_query(List<TABLE_LIST> &tables, List<Item> &all_fields,COND *conds) { List_iterator_fast<Item> it(all_fields); + List_iterator<TABLE_LIST> ti(tables); + TABLE_LIST *tl; int const_result= 1; bool recalc_const_item= 0; ulonglong count= 1; @@ -240,7 +244,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) table_map removed_tables= 0, outer_tables= 0, used_tables= 0; table_map where_tables= 0; Item *item; - int error; + int error= 0; if (conds) where_tables= conds->used_tables(); @@ -249,7 +253,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) Analyze outer join dependencies, and, if possible, compute the number of returned rows. */ - for (TABLE_LIST *tl= tables; tl; tl= tl->next_leaf) + while ((tl= ti++)) { TABLE_LIST *embedded; for (embedded= tl ; embedded; embedded= embedded->embedding) @@ -290,6 +294,14 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) is_exact_count= FALSE; count= 1; // ensure count != 0 } + else if (tl->is_materialized_derived()) + { + /* + Can't remove a derived table as it's number of rows is just an + estimate. + */ + return 0; + } else { error= tl->table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); diff --git a/sql/records.cc b/sql/records.cc index 6bb7c7cffa1..5ce7d8660e9 100644 --- a/sql/records.cc +++ b/sql/records.cc @@ -299,7 +299,8 @@ void end_read_record(READ_RECORD *info) if (info->table) { filesort_free_buffers(info->table,0); - (void) info->file->extra(HA_EXTRA_NO_CACHE); + if (info->table->created) + (void) info->file->extra(HA_EXTRA_NO_CACHE); if (info->read_record != rr_quick) // otherwise quick_range does it (void) info->file->ha_index_or_rnd_end(); info->table=0; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index dbaeb9467ae..737de8e8f7c 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -2847,6 +2847,9 @@ int sp_instr::exec_open_and_lock_tables(THD *thd, TABLE_LIST *tables) result= -1; else result= 0; + /* Prepare all derived tables/views to catch possible errors. */ + if (!result) + result= mysql_handle_derived(thd->lex, DT_PREPARE) ? -1 : 0; return result; } diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 47d85238cff..e315b069f8b 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -3057,7 +3057,8 @@ int mysql_table_grant(THD *thd, TABLE_LIST *table_list, class LEX_COLUMN *column; List_iterator <LEX_COLUMN> column_iter(columns); - if (open_and_lock_tables(thd, table_list)) + if (open_and_lock_tables(thd, table_list) || + mysql_handle_derived(thd->lex, DT_PREPARE)) DBUG_RETURN(TRUE); while ((column = column_iter++)) diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 31b5a703b19..b13a6d91533 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3026,6 +3026,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, */ DBUG_ASSERT(table->file->pushed_cond == NULL); table->reginfo.impossible_range= 0; + table->created= TRUE; /* Catch wrong handling of the auto_increment_field_not_null. */ DBUG_ASSERT(!table->auto_increment_field_not_null); table->auto_increment_field_not_null= FALSE; @@ -5123,9 +5124,10 @@ int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived) close_tables_for_reopen(thd, &tables); } if (derived && - (mysql_handle_derived(thd->lex, &mysql_derived_prepare) || - (thd->fill_derived_tables() && - mysql_handle_derived(thd->lex, &mysql_derived_filling)))) + (mysql_handle_derived(thd->lex, DT_INIT))) + DBUG_RETURN(TRUE); /* purecov: inspected */ + if (thd->prepare_derived_at_open && derived && + (mysql_handle_derived(thd->lex, DT_PREPARE))) DBUG_RETURN(TRUE); /* purecov: inspected */ DBUG_RETURN(0); } @@ -5141,6 +5143,7 @@ int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived) flags - bitmap of flags to modify how the tables will be open: MYSQL_LOCK_IGNORE_FLUSH - open table even if someone has done a flush on it. + dt_phases - set of flags to pass to the mysql_handle_derived RETURN FALSE - ok @@ -5151,13 +5154,14 @@ int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived) data from the tables. */ -bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags) +bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags, + uint dt_phases) { uint counter; DBUG_ENTER("open_normal_and_derived_tables"); DBUG_ASSERT(!thd->fill_derived_tables()); if (open_tables(thd, &tables, &counter, flags) || - mysql_handle_derived(thd->lex, &mysql_derived_prepare)) + mysql_handle_derived(thd->lex, dt_phases)) DBUG_RETURN(TRUE); /* purecov: inspected */ DBUG_RETURN(0); } @@ -5829,9 +5833,7 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list, Field_iterator_view field_it; field_it.set(table_list); Query_arena *arena= 0, backup; - - DBUG_ASSERT(table_list->schema_table_reformed || - (ref != 0 && table_list->view != 0)); + for (; !field_it.end_of_fields(); field_it.next()) { if (!my_strcasecmp(system_charset_info, field_it.name(), name)) @@ -5850,6 +5852,8 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list, if (!item) DBUG_RETURN(0); + if (!ref) + DBUG_RETURN((Field*) view_ref_found); /* *ref != NULL means that *ref contains the item that we need to replace. If the item was aliased by the user, set the alias to @@ -6254,6 +6258,8 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, Field *field_to_set= NULL; if (fld == view_ref_found) { + if (!ref) + DBUG_RETURN(fld); Item *it= (*ref)->real_item(); if (it->type() == Item::FIELD_ITEM) field_to_set= ((Item_field*)it)->field; @@ -6261,6 +6267,8 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, { if (thd->mark_used_columns == MARK_COLUMNS_READ) it->walk(&Item::register_field_in_read_map, 1, (uchar *) 0); + else + it->walk(&Item::register_field_in_write_map, 1, (uchar *) 0); } } else @@ -6400,8 +6408,11 @@ find_field_in_tables(THD *thd, Item_ident *item, find_field_in_table even in the case of information schema tables when table_ref->field_translation != NULL. */ - if (table_ref->table && !table_ref->view) + if (table_ref->table && + (!table_ref->is_merged_derived() || + (!table_ref->is_multitable() && table_ref->merged_for_insert))) { + found= find_field_in_table(thd, table_ref->table, name, length, TRUE, &(item->cached_field_index)); #ifndef NO_EMBEDDED_ACCESS_CHECKS @@ -6426,7 +6437,8 @@ find_field_in_tables(THD *thd, Item_ident *item, Only views fields should be marked as dependent, not an underlying fields. */ - if (!table_ref->belong_to_view) + if (!table_ref->belong_to_view && + !table_ref->belong_to_derived) { SELECT_LEX *current_sel= thd->lex->current_select; SELECT_LEX *last_select= table_ref->select_lex; @@ -7021,6 +7033,10 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2, */ if (nj_col_2 && (!using_fields ||is_using_column_1)) { + /* + Create non-fixed fully qualified field and let fix_fields to + resolve it. + */ Item *item_1= nj_col_1->create_item(thd); Item *item_2= nj_col_2->create_item(thd); Field *field_1= nj_col_1->field(); @@ -7684,27 +7700,36 @@ bool setup_fields(THD *thd, Item **ref_pointer_array, make_leaves_list() list pointer to pointer on list first element tables table list + full_table_list whether to include tables from mergeable derived table/view. + we need them for checks for INSERT/UPDATE statements only. RETURN pointer on pointer to next_leaf of last element */ -TABLE_LIST **make_leaves_list(TABLE_LIST **list, TABLE_LIST *tables) +void make_leaves_list(List<TABLE_LIST> &list, TABLE_LIST *tables, + bool full_table_list, TABLE_LIST *boundary) + { for (TABLE_LIST *table= tables; table; table= table->next_local) { - if (table->merge_underlying_list) + if (table == boundary) + full_table_list= !full_table_list; + if (full_table_list && table->is_merged_derived()) { - DBUG_ASSERT(table->view && - table->effective_algorithm == VIEW_ALGORITHM_MERGE); - list= make_leaves_list(list, table->merge_underlying_list); + SELECT_LEX *select_lex= table->get_single_select(); + /* + It's safe to use select_lex->leaf_tables because all derived + tables/views were already prepared and has their leaf_tables + set properly. + */ + make_leaves_list(list, select_lex->get_table_list(), + full_table_list, boundary); } else { - *list= table; - list= &table->next_leaf; + list.push_back(table); } } - return list; } /* @@ -7719,6 +7744,7 @@ TABLE_LIST **make_leaves_list(TABLE_LIST **list, TABLE_LIST *tables) leaves List of join table leaves list (select_lex->leaf_tables) refresh It is onle refresh for subquery select_insert It is SELECT ... INSERT command + full_table_list a parameter to pass to the make_leaves_list function NOTE Check also that the 'used keys' and 'ignored keys' exists and set up the @@ -7737,9 +7763,13 @@ TABLE_LIST **make_leaves_list(TABLE_LIST **list, TABLE_LIST *tables) bool setup_tables(THD *thd, Name_resolution_context *context, List<TABLE_LIST> *from_clause, TABLE_LIST *tables, - TABLE_LIST **leaves, bool select_insert) + List<TABLE_LIST> &leaves, bool select_insert, + bool full_table_list) { uint tablenr= 0; + List_iterator<TABLE_LIST> ti(leaves); + TABLE_LIST *table_list; + DBUG_ENTER("setup_tables"); DBUG_ASSERT ((select_insert && !tables->next_name_resolution_table) || !tables || @@ -7751,40 +7781,57 @@ bool setup_tables(THD *thd, Name_resolution_context *context, TABLE_LIST *first_select_table= (select_insert ? tables->next_local: 0); - if (!(*leaves)) - make_leaves_list(leaves, tables); - - TABLE_LIST *table_list; - for (table_list= *leaves; - table_list; - table_list= table_list->next_leaf, tablenr++) + SELECT_LEX *select_lex= select_insert ? &thd->lex->select_lex : + thd->lex->current_select; + if (select_lex->first_cond_optimization) { - TABLE *table= table_list->table; - table->pos_in_table_list= table_list; - if (first_select_table && - table_list->top_table() == first_select_table) + leaves.empty(); + select_lex->leaf_tables_exec.empty(); + make_leaves_list(leaves, tables, full_table_list, first_select_table); + + while ((table_list= ti++)) { - /* new counting for SELECT of INSERT ... SELECT command */ - first_select_table= 0; - tablenr= 0; + TABLE *table= table_list->table; + table->pos_in_table_list= table_list; + if (first_select_table && + table_list->top_table() == first_select_table) + { + /* new counting for SELECT of INSERT ... SELECT command */ + first_select_table= 0; + thd->lex->select_lex.insert_tables= tablenr; + tablenr= 0; + } + setup_table_map(table, table_list, tablenr); + if (table_list->process_index_hints(table)) + DBUG_RETURN(1); + tablenr++; } - setup_table_map(table, table_list, tablenr); - if (table_list->process_index_hints(table)) + if (tablenr > MAX_TABLES) + { + my_error(ER_TOO_MANY_TABLES,MYF(0),MAX_TABLES); DBUG_RETURN(1); + } } - if (tablenr > MAX_TABLES) - { - my_error(ER_TOO_MANY_TABLES,MYF(0),MAX_TABLES); - DBUG_RETURN(1); - } + else + { + List_iterator_fast <TABLE_LIST> ti(select_lex->leaf_tables_exec); + select_lex->leaf_tables.empty(); + while ((table_list= ti++)) + { + table_list->table->tablenr= table_list->tablenr_exec; + table_list->table->map= table_list->map_exec; + table_list->table->pos_in_table_list= table_list; + select_lex->leaf_tables.push_back(table_list); + } + } + for (table_list= tables; table_list; table_list= table_list->next_local) { if (table_list->merge_underlying_list) { - DBUG_ASSERT(table_list->view && - table_list->effective_algorithm == VIEW_ALGORITHM_MERGE); + DBUG_ASSERT(table_list->is_merged_derived()); Query_arena *arena= thd->stmt_arena, backup; bool res; if (arena->is_conventional()) @@ -7811,7 +7858,7 @@ bool setup_tables(THD *thd, Name_resolution_context *context, prepare tables and check access for the view tables SYNOPSIS - setup_tables_and_check_view_access() + setup_tables_and_check_access() thd Thread handler context name resolution contest to setup table list there from_clause Top-level list of table references in the FROM clause @@ -7821,6 +7868,7 @@ bool setup_tables(THD *thd, Name_resolution_context *context, refresh It is onle refresh for subquery select_insert It is SELECT ... INSERT command want_access what access is needed + full_table_list a parameter to pass to the make_leaves_list function NOTE a wrapper for check_tables that will also check the resulting @@ -7834,27 +7882,26 @@ bool setup_tables_and_check_access(THD *thd, Name_resolution_context *context, List<TABLE_LIST> *from_clause, TABLE_LIST *tables, - TABLE_LIST **leaves, + List<TABLE_LIST> &leaves, bool select_insert, ulong want_access_first, - ulong want_access) + ulong want_access, + bool full_table_list) { - TABLE_LIST *leaves_tmp= NULL; bool first_table= true; DBUG_ENTER("setup_tables_and_check_access"); if (setup_tables(thd, context, from_clause, tables, - &leaves_tmp, select_insert)) + leaves, select_insert, full_table_list)) DBUG_RETURN(TRUE); - if (leaves) - *leaves= leaves_tmp; - - for (; leaves_tmp; leaves_tmp= leaves_tmp->next_leaf) + List_iterator<TABLE_LIST> ti(leaves); + TABLE_LIST *table_list; + while((table_list= ti++)) { - if (leaves_tmp->belong_to_view && + if (table_list->belong_to_view && !table_list->view && check_single_table_access(thd, first_table ? want_access_first : - want_access, leaves_tmp, FALSE)) + want_access, table_list, FALSE)) { tables->hide_view_error(thd); DBUG_RETURN(TRUE); @@ -7997,8 +8044,10 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, information_schema table, or a nested table reference. See the comment for TABLE_LIST. */ - if (!((table && !tables->view && (table->grant.privilege & SELECT_ACL)) || - (tables->view && (tables->grant.privilege & SELECT_ACL))) && + if (!((table && tables->is_non_derived() && + (table->grant.privilege & SELECT_ACL)) || + ((!tables->is_non_derived() && + (tables->grant.privilege & SELECT_ACL)))) && !any_privileges) { field_iterator.set(tables); @@ -8028,7 +8077,7 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, if (!(item= field_iterator.create_item(thd))) DBUG_RETURN(TRUE); - DBUG_ASSERT(item->fixed); +// DBUG_ASSERT(item->fixed); /* cache the table for the Item_fields inserted by expanding stars */ if (item->type() == Item::FIELD_ITEM && tables->cacheable_table) ((Item_field *)item)->cached_table= tables; @@ -8181,13 +8230,14 @@ void wrap_ident(THD *thd, Item **conds) FALSE if all is OK */ -int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, +int setup_conds(THD *thd, TABLE_LIST *tables, List<TABLE_LIST> &leaves, COND **conds) { SELECT_LEX *select_lex= thd->lex->current_select; Query_arena *arena= thd->stmt_arena, backup; TABLE_LIST *table= NULL; // For HP compilers TABLE_LIST *save_emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest; + List_iterator<TABLE_LIST> ti(leaves); /* it_is_update set to TRUE when tables of primary SELECT_LEX (SELECT_LEX which belong to LEX, i.e. most up SELECT) will be updated by @@ -8199,9 +8249,15 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, bool it_is_update= (select_lex == &thd->lex->select_lex) && thd->lex->which_check_option_applicable(); bool save_is_item_list_lookup= select_lex->is_item_list_lookup; - select_lex->is_item_list_lookup= 0; + TABLE_LIST *derived= select_lex->master_unit()->derived; DBUG_ENTER("setup_conds"); + /* Do not fix conditions for the derived tables that have been merged */ + if (derived && derived->merged) + DBUG_RETURN(0); + + select_lex->is_item_list_lookup= 0; + if (select_lex->conds_processed_with_permanent_arena || arena->is_conventional()) arena= 0; // For easier test @@ -8214,7 +8270,10 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, for (table= tables; table; table= table->next_local) { - if (table->prepare_where(thd, conds, FALSE)) + if (select_lex == &thd->lex->select_lex && + select_lex->first_cond_optimization && + table->merged_for_insert && + table->prepare_where(thd, conds, FALSE)) goto err_no_arena; } @@ -8230,7 +8289,7 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, Wrap alone field in WHERE clause in case it will be outer field of subquery which need persistent pointer on it, but conds could be changed by optimizer */ - if ((*conds)->type() == Item::FIELD_ITEM) + if ((*conds)->type() == Item::FIELD_ITEM && !derived) wrap_ident(thd, conds); if ((!(*conds)->fixed && (*conds)->fix_fields(thd, conds)) || (*conds)->check_cols(1)) @@ -8242,7 +8301,7 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, Apply fix_fields() to all ON clauses at all levels of nesting, including the ones inside view definitions. */ - for (table= leaves; table; table= table->next_leaf) + while ((table= ti++)) { TABLE_LIST *embedded; /* The table at the current level of nesting. */ TABLE_LIST *embedding= table; /* The parent nested table reference. */ @@ -9435,6 +9494,27 @@ void close_performance_schema_table(THD *thd, Open_tables_state *backup) thd->restore_backup_open_tables_state(backup); } + +/** + @brief + Remove 'fixed' flag from items in a list + + @param items list of items to un-fix + + @details + This function sets to 0 the 'fixed' flag for items in the 'items' list. + It's needed to force correct marking of views' fields for INSERT/UPDATE + statements. +*/ + +void unfix_fields(List<Item> &fields) +{ + List_iterator<Item> li(fields); + Item *item; + while ((item= li++)) + item->fixed= 0; +} + /** @} (end of group Data_Dictionary) */ diff --git a/sql/sql_bitmap.h b/sql/sql_bitmap.h index 5385acc934f..25725ff6c42 100644 --- a/sql/sql_bitmap.h +++ b/sql/sql_bitmap.h @@ -91,6 +91,10 @@ public: DBUG_ASSERT(sizeof(buffer) >= 4); return (ulonglong) uint4korr(buffer); } + uint bits_set() + { + return bitmap_bits_set(&map); + } }; /* An iterator to quickly walk over bits in unlonglong bitmap. */ @@ -169,5 +173,16 @@ public: public: Iterator(Bitmap<64> &bmp) : Table_map_iterator(bmp.map) {} }; + uint bits_set() + { + //TODO: use my_count_bits() + uint res= 0, i= 0; + for (; i < 64 ; i++) + { + if (map & ((ulonglong)1<<i)) + res++; + } + return res; + } }; diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc index 81cc87723c5..cc9614c6af1 100644 --- a/sql/sql_cache.cc +++ b/sql/sql_cache.cc @@ -3529,16 +3529,17 @@ Query_cache::process_and_count_tables(THD *thd, TABLE_LIST *tables_used, } else { - DBUG_PRINT("qcache", ("table: %s db: %s type: %u", - tables_used->table->s->table_name.str, - tables_used->table->s->db.str, - tables_used->table->s->db_type()->db_type)); if (tables_used->derived) { + DBUG_PRINT("qcache", ("table: %s", tables_used->alias)); table_count--; DBUG_PRINT("qcache", ("derived table skipped")); continue; } + DBUG_PRINT("qcache", ("table: %s db: %s type: %u", + tables_used->table->s->table_name.str, + tables_used->table->s->db.str, + tables_used->table->s->db_type()->db_type)); *tables_type|= tables_used->table->file->table_cache_type(); /* diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 872c065e36a..b5bc9a3e295 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -813,6 +813,7 @@ THD::THD() m_binlog_invoker= FALSE; memset(&invoker_user, 0, sizeof(invoker_user)); memset(&invoker_host, 0, sizeof(invoker_host)); + prepare_derived_at_open= FALSE; } @@ -3046,7 +3047,8 @@ bool select_materialize_with_stats:: create_result_table(THD *thd_arg, List<Item> *column_types, bool is_union_distinct, ulonglong options, - const char *table_alias, bool bit_fields_as_long) + const char *table_alias, bool bit_fields_as_long, + bool create_table) { DBUG_ASSERT(table == 0); tmp_table_param.field_count= column_types->elements; diff --git a/sql/sql_class.h b/sql/sql_class.h index 91b4ddfc184..53bca5f1aec 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1570,6 +1570,9 @@ public: */ TABLE_LIST *emb_on_expr_nest; } thd_marker; + + bool prepare_derived_at_open; + #ifndef MYSQL_CLIENT int binlog_setup_trx_data(); @@ -3020,13 +3023,14 @@ public: class select_union :public select_result_interceptor { -protected: +public: TMP_TABLE_PARAM tmp_table_param; int write_err; /* Error code from the last send_data->ha_write_row call. */ public: TABLE *table; + ha_rows records; - select_union() :write_err(0),table(0) { tmp_table_param.init(); } + select_union() :write_err(0), table(0), records(0) { tmp_table_param.init(); } int prepare(List<Item> &list, SELECT_LEX_UNIT *u); bool send_data(List<Item> &items); bool send_eof(); @@ -3034,7 +3038,9 @@ public: void cleanup(); virtual bool create_result_table(THD *thd, List<Item> *column_types, bool is_distinct, ulonglong options, - const char *alias, bool bit_fields_as_long); + const char *alias, + bool bit_fields_as_long, + bool create_table); TMP_TABLE_PARAM *get_tmp_table_param() { return &tmp_table_param; } }; @@ -3100,9 +3106,11 @@ protected: public: select_materialize_with_stats() { tmp_table_param.init(); } - virtual bool create_result_table(THD *thd, List<Item> *column_types, - bool is_distinct, ulonglong options, - const char *alias, bool bit_fields_as_long); + bool create_result_table(THD *thd, List<Item> *column_types, + bool is_distinct, ulonglong options, + const char *alias, + bool bit_fields_as_long, + bool create_table); bool init_result_table(ulonglong select_options); bool send_data(List<Item> &items); void cleanup(); @@ -3410,7 +3418,7 @@ public: class multi_update :public select_result_interceptor { TABLE_LIST *all_tables; /* query/update command tables */ - TABLE_LIST *leaves; /* list of leves of join table tree */ + List<TABLE_LIST> *leaves; /* list of leves of join table tree */ TABLE_LIST *update_tables, *table_being_updated; TABLE **tmp_tables, *main_table, *table_to_update; TMP_TABLE_PARAM *tmp_table_param; @@ -3436,7 +3444,7 @@ class multi_update :public select_result_interceptor bool error_handled; public: - multi_update(TABLE_LIST *ut, TABLE_LIST *leaves_list, + multi_update(TABLE_LIST *ut, List<TABLE_LIST> *leaves_list, List<Item> *fields, List<Item> *values, enum_duplicates handle_duplicates, bool ignore); ~multi_update(); diff --git a/sql/sql_cursor.cc b/sql/sql_cursor.cc index 308c49fc15c..092fcbca46f 100644 --- a/sql/sql_cursor.cc +++ b/sql/sql_cursor.cc @@ -720,8 +720,8 @@ bool Select_materialize::send_fields(List<Item> &list, uint flags) DBUG_ASSERT(table == 0); if (create_result_table(unit->thd, unit->get_unit_column_types(), FALSE, thd->options | TMP_TABLE_ALL_COLUMNS, "", - FALSE)) - return TRUE; + FALSE, TRUE)) + return TRUE; materialized_cursor= new (&table->mem_root) Materialized_cursor(result, table); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index afda3070f08..c5113561ba3 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -60,10 +60,18 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, if (open_and_lock_tables(thd, table_list)) DBUG_RETURN(TRUE); - if (!(table= table_list->table)) + + if (mysql_handle_list_of_derived(thd->lex, table_list, DT_MERGE_FOR_INSERT) || + mysql_handle_list_of_derived(thd->lex, table_list, DT_PREPARE)) + DBUG_RETURN(TRUE); + + if (!(table= table_list->table) || !table->created) { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - table_list->view_db.str, table_list->view_name.str); + if (!table_list->updatable) + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE"); + else + my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), + table_list->view_db.str, table_list->view_name.str); DBUG_RETURN(TRUE); } thd_proc_info(thd, "init"); @@ -72,6 +80,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, if (mysql_prepare_delete(thd, table_list, &conds)) DBUG_RETURN(TRUE); + if (thd->lex->current_select->first_cond_optimization) + { + thd->lex->current_select->save_leaf_tables(thd); + thd->lex->current_select->first_cond_optimization= 0; + } /* check ORDER BY even if it can be ignored */ if (order && order->elements) { @@ -405,6 +418,12 @@ cleanup: query_cache_invalidate3(thd, table_list, 1); } + if (thd->lex->current_select->first_cond_optimization) + { + thd->lex->current_select->save_leaf_tables(thd); + thd->lex->current_select->first_cond_optimization= 0; + } + delete select; transactional_table= table->file->has_transactions(); @@ -506,8 +525,8 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds) if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context, &thd->lex->select_lex.top_join_list, table_list, - &select_lex->leaf_tables, FALSE, - DELETE_ACL, SELECT_ACL) || + select_lex->leaf_tables, FALSE, + DELETE_ACL, SELECT_ACL, TRUE) || setup_conds(thd, table_list, select_lex->leaf_tables, conds) || setup_ftfuncs(select_lex)) DBUG_RETURN(TRUE); @@ -565,6 +584,11 @@ int mysql_multi_delete_prepare(THD *thd) TABLE_LIST *target_tbl; DBUG_ENTER("mysql_multi_delete_prepare"); + TABLE_LIST *tables= lex->query_tables; + if (mysql_handle_derived(lex, DT_INIT) || + mysql_handle_list_of_derived(lex, tables, DT_MERGE_FOR_INSERT) || + mysql_handle_list_of_derived(lex, tables, DT_PREPARE)) + DBUG_RETURN(TRUE); /* setup_tables() need for VIEWs. JOIN::prepare() will not do it second time. @@ -574,8 +598,8 @@ int mysql_multi_delete_prepare(THD *thd) if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context, &thd->lex->select_lex.top_join_list, lex->query_tables, - &lex->select_lex.leaf_tables, FALSE, - DELETE_ACL, SELECT_ACL)) + lex->select_lex.leaf_tables, FALSE, + DELETE_ACL, SELECT_ACL, TRUE)) DBUG_RETURN(TRUE); @@ -589,16 +613,13 @@ int mysql_multi_delete_prepare(THD *thd) target_tbl; target_tbl= target_tbl->next_local) { + if (!(target_tbl->table= target_tbl->correspondent_table->table)) { - DBUG_ASSERT(target_tbl->correspondent_table->view && - target_tbl->correspondent_table->merge_underlying_list && - target_tbl->correspondent_table->merge_underlying_list-> - next_local); - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - target_tbl->correspondent_table->view_db.str, - target_tbl->correspondent_table->view_name.str); - DBUG_RETURN(TRUE); + my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), + target_tbl->correspondent_table->view_db.str, + target_tbl->correspondent_table->view_name.str); + DBUG_RETURN(TRUE); } if (!target_tbl->correspondent_table->updatable || @@ -648,6 +669,12 @@ multi_delete::prepare(List<Item> &values, SELECT_LEX_UNIT *u) unit= u; do_delete= 1; thd_proc_info(thd, "deleting from main table"); + SELECT_LEX *select_lex= u->first_select(); + if (select_lex->first_cond_optimization) + { + if (select_lex->handle_derived(thd->lex, DT_MERGE)) + DBUG_RETURN(TRUE); + } DBUG_RETURN(0); } diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index e92b8f83c95..c2f4b0db606 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -23,38 +23,79 @@ #include "mysql_priv.h" #include "sql_select.h" +typedef bool (*dt_processor)(THD *thd, LEX *lex, TABLE_LIST *derived); +bool mysql_derived_init(THD *thd, LEX *lex, TABLE_LIST *derived); +bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived); +bool mysql_derived_optimize(THD *thd, LEX *lex, TABLE_LIST *derived); +bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived); +bool mysql_derived_create(THD *thd, LEX *lex, TABLE_LIST *derived); +bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived); +bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived); +bool mysql_derived_merge_for_insert(THD *thd, LEX *lex, TABLE_LIST *derived); + + +dt_processor processors[]= +{ + &mysql_derived_init, + &mysql_derived_prepare, + &mysql_derived_optimize, + &mysql_derived_merge, + &mysql_derived_merge_for_insert, + &mysql_derived_create, + &mysql_derived_fill, + &mysql_derived_reinit, +}; /* - Call given derived table processor (preparing or filling tables) + @brief + Run specified phases on all derived tables/views in given LEX. - SYNOPSIS - mysql_handle_derived() - lex LEX for this thread - processor procedure of derived table processing + @param lex LEX for this thread + @param phases phases to run derived tables/views through - RETURN - FALSE OK - TRUE Error + @return FALSE OK + @return TRUE Error */ - bool -mysql_handle_derived(LEX *lex, bool (*processor)(THD*, LEX*, TABLE_LIST*)) +mysql_handle_derived(LEX *lex, uint phases) { bool res= FALSE; - if (lex->derived_tables) + THD *thd= lex->thd; + if (!lex->derived_tables) + return FALSE; + + lex->thd->derived_tables_processing= TRUE; + + for (uint phase= 0; phase < DT_PHASES && !res; phase++) { - lex->thd->derived_tables_processing= TRUE; + uint phase_flag= DT_INIT << phase; + if (phase_flag > phases) + break; + if (!(phases & phase_flag)) + continue; + if (phase_flag >= DT_CREATE && !thd->fill_derived_tables()) + break; + for (SELECT_LEX *sl= lex->all_selects_list; - sl; + sl && !res; sl= sl->next_select_in_list()) { for (TABLE_LIST *cursor= sl->get_table_list(); - cursor; + cursor && !res; cursor= cursor->next_local) { - if ((res= (*processor)(lex->thd, lex, cursor))) - goto out; + uint8 allowed_phases= (cursor->is_merged_derived() ? DT_PHASES_MERGE : + DT_PHASES_MATERIALIZE); + /* + Skip derived tables to which the phase isn't applicable. + TODO: mark derived at the parse time, later set it's type + (merged or materialized) + */ + if ((phase_flag != DT_PREPARE && !(allowed_phases & phase_flag)) || + (cursor->merged_for_insert && phase_flag != DT_REINIT)) + continue; + res= (*processors[phase])(lex->thd, lex, cursor); } if (lex->describe) { @@ -67,30 +108,457 @@ mysql_handle_derived(LEX *lex, bool (*processor)(THD*, LEX*, TABLE_LIST*)) } } } -out: + lex->thd->derived_tables_processing= FALSE; + return res; +} + +/* + @brief + Run through phases for the given derived table/view. + + @param lex LEX for this thread + @param derived the derived table to handle + @param phase_map phases to process tables/views through + + @details + + This function process the derived table (view) 'derived' to performs all + actions that are to be done on the table at the phases specified by + phase_map. The processing is carried out starting from the actions + performed at the earlier phases (those having smaller ordinal numbers). + + @note + This function runs specified phases of the derived tables handling on the + given derived table/view. This function is used in the chain of calls: + SELECT_LEX::handle_derived -> + TABLE_LIST::handle_derived -> + mysql_handle_single_derived + This chain of calls implements the bottom-up handling of the derived tables: + i.e. most inner derived tables/views are handled first. This order is + required for the all phases except the merge and the create steps. + For the sake of code simplicity this order is kept for all phases. + + @return FALSE ok + @return TRUE error +*/ + +bool +mysql_handle_single_derived(LEX *lex, TABLE_LIST *derived, uint phases) +{ + bool res= FALSE; + THD *thd= lex->thd; + uint8 allowed_phases= (derived->is_merged_derived() ? DT_PHASES_MERGE : + DT_PHASES_MATERIALIZE); + if (!lex->derived_tables) + return FALSE; + + lex->thd->derived_tables_processing= TRUE; + + for (uint phase= 0; phase < DT_PHASES; phase++) + { + uint phase_flag= DT_INIT << phase; + if (phase_flag > phases) + break; + if (!(phases & phase_flag)) + continue; + /* Skip derived tables to which the phase isn't applicable. */ + if (phase_flag != DT_PREPARE && + !(allowed_phases & phase_flag)) + continue; + if (phase_flag >= DT_CREATE && !thd->fill_derived_tables()) + break; + + if ((res= (*processors[phase])(lex->thd, lex, derived))) + break; + } lex->thd->derived_tables_processing= FALSE; return res; } /** - @brief Create temporary table structure (but do not fill it). + @brief + Run specified phases for derived tables/views in the given list - @param thd Thread handle - @param lex LEX for this thread - @param orig_table_list TABLE_LIST for the upper SELECT + @param lex LEX for this thread + @param table_list list of derived tables/view to handle + @param phase_map phases to process tables/views through - @details + @details + This function runs phases specified by the 'phases_map' on derived + tables/views found in the 'dt_list' with help of the + TABLE_LIST::handle_derived function. + 'lex' is passed as an argument to the TABLE_LIST::handle_derived. - This function is called before any command containing derived tables is - executed. Currently the function is used for derived tables, i.e. + @return FALSE ok + @return TRUE error +*/ - - Anonymous derived tables, or - - Named derived tables (aka views) with the @c TEMPTABLE algorithm. - - The table reference, contained in @c orig_table_list, is updated with the - fields of a new temporary table. +bool +mysql_handle_list_of_derived(LEX *lex, TABLE_LIST *table_list, uint phases) +{ + for (TABLE_LIST *tl= table_list; tl; tl= tl->next_local) + { + if (tl->is_view_or_derived() && + tl->handle_derived(lex, phases)) + return TRUE; + } + return FALSE; +} + + +/** + @brief + Merge a derived table/view into the embedding select + + @param thd thread handle + @param lex LEX of the embedding query. + @param derived reference to the derived table. + + @details + This function merges the given derived table / view into the parent select + construction. Any derived table/reference to view occurred in the FROM + clause of the embedding select is represented by a TABLE_LIST structure a + pointer to which is passed to the function as in the parameter 'derived'. + This structure contains the number/map, alias, a link to SELECT_LEX of the + derived table and other info. If the 'derived' table is used in a nested join + then additionally the structure contains a reference to the ON expression + for this join. + + The merge process results in elimination of the derived table (or the + reference to a view) such that: + - the FROM list of the derived table/view is wrapped into a nested join + after which the nest is added to the FROM list of the embedding select + - the WHERE condition of the derived table (view) is ANDed with the ON + condition attached to the table. + + @note + Tables are merged into the leaf_tables list, original derived table is removed + from this list also. SELECT_LEX::table_list list is left untouched. + Where expression is merged with derived table's on_expr and can be found after + the merge through the SELECT_LEX::table_list. + + Examples of the derived table/view merge: + + Schema: + Tables: t1(f1), t2(f2), t3(f3) + View v1: SELECT f1 FROM t1 WHERE f1 < 1 + + Example with a view: + Before merge: + + The query (Q1): SELECT f1,f2 FROM t2 LEFT JOIN v1 ON f1 = f2 + + (LEX of the main query) + | + (select_lex) + | + (FROM table list) + | + (join list)= t2, v1 + / \ + / (on_expr)= (f1 = f2) + | + (LEX of the v1 view) + | + (select_lex)= SELECT f1 FROM t1 WHERE f1 < 1 + + + After merge: + + The rewritten query Q1 (Q1'): + SELECT f1,f2 FROM t2 LEFT JOIN (t1) ON ((f1 = f2) and (f1 < 1)) + + (LEX of the main query) + | + (select_lex) + | + (FROM table list) + | + (join list)= t2, (t1) + \ + (on_expr)= (f1 = f2) and (f1 < 1) + + In this example table numbers are assigned as follows: + (outer select): t2 - 1, v1 - 2 + (inner select): t1 - 1 + After the merge table numbers will be: + (outer select): t2 - 1, t1 - 2 + + Example with a derived table: + The query Q2: + SELECT f1,f2 + FROM (SELECT f1 FROM t1, t3 WHERE f1=f3 and f1 < 1) tt, t2 + WHERE f1 = f2 + + Before merge: + (LEX of the main query) + | + (select_lex) + / \ + (FROM table list) (WHERE clause)= (f1 = f2) + | + (join list)= tt, t2 + / \ + / (on_expr)= (empty) + / + (select_lex)= SELECT f1 FROM t1, t3 WHERE f1 = f3 and f1 < 1 + + After merge: + + The rewritten query Q2 (Q2'): + SELECT f1,f2 + FROM (t1, t3) JOIN t2 ON (f1 = f3 and f1 < 1) + WHERE f1 = f2 + + (LEX of the main query) + | + (select_lex) + / \ + (FROM table list) (WHERE clause)= (f1 = f2) + | + (join list)= t2, (t1, t3) + \ + (on_expr)= (f1 = f3 and f1 < 1) + + In this example table numbers are assigned as follows: + (outer select): tt - 1, t2 - 2 + (inner select): t1 - 1, t3 - 2 + After the merge table numbers will be: + (outer select): t1 - 1, t2 - 2, t3 - 3 + + @return FALSE if derived table/view were successfully merged. + @return TRUE if an error occur. +*/ + +bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) +{ + bool res= FALSE; + SELECT_LEX *dt_select= derived->get_single_select(); + table_map map; + uint tablenr; + SELECT_LEX *parent_lex= derived->select_lex; + Query_arena *arena, backup; + + if (derived->merged) + return FALSE; + + arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test + derived->merged= TRUE; + /* + Check whether there is enough free bits in table map to merge subquery. + If not - materialize it. This check isn't cached so when there is a big + and small subqueries, and the bigger one can't be merged it wouldn't + block the smaller one. + */ + if (parent_lex->get_free_table_map(&map, &tablenr)) + { + /* There is no enough table bits, fall back to materialization. */ + derived->change_refs_to_fields(); + derived->set_materialized_derived(); + goto exit_merge; + } + + if (dt_select->leaf_tables.elements + tablenr > MAX_TABLES) + { + /* There is no enough table bits, fall back to materialization. */ + derived->change_refs_to_fields(); + derived->set_materialized_derived(); + goto exit_merge; + } + + if (dt_select->options & OPTION_SCHEMA_TABLE) + parent_lex->options |= OPTION_SCHEMA_TABLE; + + parent_lex->cond_count+= dt_select->cond_count; + + if (!derived->get_unit()->prepared) + { + dt_select->leaf_tables.empty(); + make_leaves_list(dt_select->leaf_tables, derived, TRUE, 0); + } + + if (!derived->merged_for_insert) + { derived->nested_join= (NESTED_JOIN*) thd->calloc(sizeof(NESTED_JOIN)); + if (!derived->nested_join) + { + res= TRUE; + goto exit_merge; + } + + /* Merge derived table's subquery in the parent select. */ + if (parent_lex->merge_subquery(derived, dt_select, tablenr, map)) + { + res= TRUE; + goto exit_merge; + } + + /* + exclude select lex so it doesn't show up in explain. + do this only for derived table as for views this is already done. + + From sql_view.cc + Add subqueries units to SELECT into which we merging current view. + unit(->next)* chain starts with subqueries that are used by this + view and continues with subqueries that are used by other views. + We must not add any subquery twice (otherwise we'll form a loop), + to do this we remember in end_unit the first subquery that has + been already added. + */ + derived->get_unit()->exclude_level(); + if (parent_lex->join) + parent_lex->join->tables+= dt_select->join->tables - 1; + } + if (derived->get_unit()->prepared) + { + Item *expr= derived->on_expr; + expr= and_conds(expr, dt_select->join ? dt_select->join->conds : 0); + if (expr && (derived->prep_on_expr || expr != derived->on_expr)) + { + derived->on_expr= expr; + derived->prep_on_expr= expr->copy_andor_structure(thd); + } + if (derived->on_expr && + ((!derived->on_expr->fixed && + derived->on_expr->fix_fields(thd, &derived->on_expr)) || + derived->on_expr->check_cols(1))) + { + res= TRUE; /* purecov: inspected */ + goto exit_merge; + } + // Update used tables cache according to new table map + if (derived->on_expr) + { + derived->on_expr->fix_after_pullout(parent_lex, &derived->on_expr); + fix_list_after_tbl_changes(parent_lex, &derived->nested_join->join_list); + } + } + +exit_merge: + if (arena) + thd->restore_active_arena(arena, &backup); + return res; +} + + +/** + @brief + Merge a view for the embedding INSERT/UPDATE/DELETE + + @param thd thread handle + @param lex LEX of the embedding query. + @param derived reference to the derived table. + + @details + This function substitutes the derived table for the first table from + the query of the derived table thus making it a correct target table for the + INSERT/UPDATE/DELETE statements. As this operation is correct only for + single table views only, for multi table views this function does nothing. + The derived parameter isn't checked to be a view as derived tables aren't + allowed for INSERT/UPDATE/DELETE statements. + + @return FALSE if derived table/view were successfully merged. + @return TRUE if an error occur. +*/ + +bool mysql_derived_merge_for_insert(THD *thd, LEX *lex, TABLE_LIST *derived) +{ + SELECT_LEX *dt_select= derived->get_single_select(); + + if (derived->merged_for_insert) + return FALSE; + if (!derived->is_multitable()) + { + TABLE_LIST *tl=((TABLE_LIST*)dt_select->table_list.first); + TABLE *table= tl->table; + /* preserve old map & tablenr. */ + if (!derived->merged_for_insert && derived->table) + table->set_table_map(derived->table->map, derived->table->tablenr); + + derived->table= table; + derived->schema_table= + ((TABLE_LIST*)dt_select->table_list.first)->schema_table; + if (!derived->merged) + { + Query_arena *arena, backup; + arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test + derived->select_lex->leaf_tables.push_back(tl); + derived->nested_join= (NESTED_JOIN*) thd->calloc(sizeof(NESTED_JOIN)); + if (derived->nested_join) + { + derived->wrap_into_nested_join(tl->select_lex->top_join_list); + derived->get_unit()->exclude_level(); + } + if (arena) + thd->restore_active_arena(arena, &backup); + derived->merged= TRUE; + if (!derived->nested_join) + return TRUE; + } + } + else + { + if (!derived->merged_for_insert && mysql_derived_merge(thd, lex, derived)) + return TRUE; + } + derived->merged_for_insert= TRUE; + + return FALSE; +} + + +/* + @brief + Initialize a derived table/view + + @param thd Thread handle + @param lex LEX of the embedding query. + @param derived reference to the derived table. + + @detail + Fill info about derived table/view without preparing an + underlying select. Such as: create a field translation for views, mark it as + a multitable if it is and so on. + + @return + false OK + true Error +*/ + + +bool mysql_derived_init(THD *thd, LEX *lex, TABLE_LIST *derived) +{ + SELECT_LEX_UNIT *unit= derived->get_unit(); + DBUG_ENTER("mysql_derived_init"); + // Skip already prepared views/DT + if (!unit || unit->prepared) + DBUG_RETURN(FALSE); + + DBUG_RETURN(derived->init_derived(thd, TRUE)); +} + + +/* + @brief + Create temporary table structure (but do not fill it) + + @param thd Thread handle + @param lex LEX of the embedding query. + @param derived reference to the derived table. + + @detail + Prepare underlying select for a derived table/view. To properly resolve + names in the embedding query the TABLE structure is created. Actual table + is created later by the mysql_derived_create function. + + This function is called before any command containing derived table + is executed. All types of derived tables are handled by this function: + - Anonymous derived tables, or + - Named derived tables (aka views). + + The table reference, contained in @c derived, is updated with the + fields of a new temporary table. Derived tables are stored in @c thd->derived_tables and closed by close_thread_tables(). @@ -114,203 +582,360 @@ out: the state of privilege checking (GRANT_INFO struct) is copied as-is to the temporary table. - This function implements a signature called "derived table processor", and - is passed as a function pointer to mysql_handle_derived(). + Only the TABLE structure is created here, actual table is created by the + mysql_derived_create function. @note This function sets @c SELECT_ACL for @c TEMPTABLE views as well as anonymous derived tables, but this is ok since later access checking will distinguish between them. - @see mysql_handle_derived(), mysql_derived_filling(), GRANT_INFO + @see mysql_handle_derived(), mysql_derived_fill(), GRANT_INFO @return false OK true Error */ -bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *orig_table_list) +bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) { - SELECT_LEX_UNIT *unit= orig_table_list->derived; - ulonglong create_options; + SELECT_LEX_UNIT *unit= derived->get_unit(); DBUG_ENTER("mysql_derived_prepare"); bool res= FALSE; - if (unit) + + // Skip already prepared views/DT + if (!unit || unit->prepared) + DBUG_RETURN(FALSE); + + /* It's a target view for an INSERT, create field translation only. */ + if (derived->merged_for_insert) { - SELECT_LEX *first_select= unit->first_select(); - TABLE *table= 0; - select_union *derived_result; - - /* prevent name resolving out of derived table */ - for (SELECT_LEX *sl= first_select; sl; sl= sl->next_select()) - sl->context.outer_context= 0; - - if (!(derived_result= new select_union)) - DBUG_RETURN(TRUE); // out of memory - - lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED; - // st_select_lex_unit::prepare correctly work for single select - if ((res= unit->prepare(thd, derived_result, 0))) - goto exit; - lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED; - if ((res= check_duplicate_names(unit->types, 0))) - goto exit; - - create_options= (first_select->options | thd->options | - TMP_TABLE_ALL_COLUMNS); - /* - Temp table is created so that it hounours if UNION without ALL is to be - processed - - As 'distinct' parameter we always pass FALSE (0), because underlying - query will control distinct condition by itself. Correct test of - distinct underlying query will be is_union && - !unit->union_distinct->next_select() (i.e. it is union and last distinct - SELECT is last SELECT of UNION). - */ - if ((res= derived_result->create_result_table(thd, &unit->types, FALSE, - create_options, - orig_table_list->alias, - FALSE))) - goto exit; + res= derived->create_field_translation(thd); + DBUG_RETURN(res); + } + + Query_arena *arena= thd->stmt_arena, backup; + if (arena->is_conventional()) + arena= 0; // For easier test + else + thd->set_n_backup_active_arena(arena, &backup); + + SELECT_LEX *first_select= unit->first_select(); + + /* prevent name resolving out of derived table */ + for (SELECT_LEX *sl= first_select; sl; sl= sl->next_select()) + { + sl->context.outer_context= 0; + // Prepare underlying views/DT first. + sl->handle_derived(lex, DT_PREPARE); + } - table= derived_result->table; + unit->derived= derived; + + if (!(derived->derived_result= new select_union)) + DBUG_RETURN(TRUE); // out of memory + + lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED; + // st_select_lex_unit::prepare correctly work for single select + if ((res= unit->prepare(thd, derived->derived_result, 0))) + goto exit; + lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED; + if ((res= check_duplicate_names(unit->types, 0))) + goto exit; + + /* + Check whether we can merge this derived table into main select. + Depending on the result field translation will or will not + be created. + */ + if (derived->init_derived(thd, FALSE)) + goto exit; + + /* + Temp table is created so that it hounours if UNION without ALL is to be + processed + + As 'distinct' parameter we always pass FALSE (0), because underlying + query will control distinct condition by itself. Correct test of + distinct underlying query will be is_union && + !unit->union_distinct->next_select() (i.e. it is union and last distinct + SELECT is last SELECT of UNION). + */ + if (derived->derived_result->create_result_table(thd, &unit->types, FALSE, + (first_select->options | + thd->options | + TMP_TABLE_ALL_COLUMNS), + derived->alias, + FALSE, FALSE)) + goto exit; + + derived->table= derived->derived_result->table; + if (derived->is_derived() && derived->is_merged_derived()) + first_select->mark_as_belong_to_derived(derived); exit: - /* Hide "Unknown column" or "Unknown function" error */ - if (orig_table_list->view) - { - if (thd->is_error() && + /* Hide "Unknown column" or "Unknown function" error */ + if (derived->view) + { + if (thd->is_error() && (thd->main_da.sql_errno() == ER_BAD_FIELD_ERROR || - thd->main_da.sql_errno() == ER_FUNC_INEXISTENT_NAME_COLLISION || - thd->main_da.sql_errno() == ER_SP_DOES_NOT_EXIST)) - { - thd->clear_error(); - my_error(ER_VIEW_INVALID, MYF(0), orig_table_list->db, - orig_table_list->table_name); - } - } - - /* - if it is preparation PS only or commands that need only VIEW structure - then we do not need real data and we can skip execution (and parameters - is not defined, too) - */ - if (res) + thd->main_da.sql_errno() == ER_FUNC_INEXISTENT_NAME_COLLISION || + thd->main_da.sql_errno() == ER_SP_DOES_NOT_EXIST)) { - if (table) - free_tmp_table(thd, table); - delete derived_result; + thd->clear_error(); + my_error(ER_VIEW_INVALID, MYF(0), derived->db, + derived->table_name); } + } + + /* + if it is preparation PS only or commands that need only VIEW structure + then we do not need real data and we can skip execution (and parameters + is not defined, too) + */ + if (res) + { + if (derived->table) + free_tmp_table(thd, derived->table); + delete derived->derived_result; + } + else + { + TABLE *table= derived->table; + table->derived_select_number= first_select->select_number; + table->s->tmp_table= NON_TRANSACTIONAL_TMP_TABLE; +#ifndef NO_EMBEDDED_ACCESS_CHECKS + if (derived->referencing_view) + table->grant= derived->grant; else { - if (!thd->fill_derived_tables()) - { - delete derived_result; - derived_result= NULL; - } - orig_table_list->derived_result= derived_result; - orig_table_list->table= table; - orig_table_list->table_name= table->s->table_name.str; - orig_table_list->table_name_length= table->s->table_name.length; - table->derived_select_number= first_select->select_number; - table->s->tmp_table= NON_TRANSACTIONAL_TMP_TABLE; -#ifndef NO_EMBEDDED_ACCESS_CHECKS - if (orig_table_list->referencing_view) - table->grant= orig_table_list->grant; - else - table->grant.privilege= SELECT_ACL; -#endif - orig_table_list->db= (char *)""; - orig_table_list->db_length= 0; - // Force read of table stats in the optimizer - table->file->info(HA_STATUS_VARIABLE); - /* Add new temporary table to list of open derived tables */ - table->next= thd->derived_tables; - thd->derived_tables= table; + table->grant.privilege= SELECT_ACL; + if (derived->is_derived()) + derived->grant.privilege= SELECT_ACL; } +#endif + /* Add new temporary table to list of open derived tables */ + table->next= thd->derived_tables; + thd->derived_tables= table; } - else if (orig_table_list->merge_underlying_list) - orig_table_list->set_underlying_merge(); + if (arena) + thd->restore_active_arena(arena, &backup); DBUG_RETURN(res); } -/* - fill derived table - - SYNOPSIS - mysql_derived_filling() - thd Thread handle - lex LEX for this thread - unit node that contains all SELECT's for derived tables - orig_table_list TABLE_LIST for the upper SELECT - - IMPLEMENTATION - Derived table is resolved with temporary table. It is created based on the - queries defined. After temporary table is filled, if this is not EXPLAIN, - then the entire unit / node is deleted. unit is deleted if UNION is used - for derived table and node is deleted is it is a simple SELECT. - If you use this function, make sure it's not called at prepare. - Due to evaluation of LIMIT clause it can not be used at prepared stage. - - RETURN - FALSE OK - TRUE Error +/** + @brief + Runs optimize phase for a derived table/view. + + @param thd thread handle + @param lex LEX of the embedding query. + @param derived reference to the derived table. + + @details + Runs optimize phase for given 'derived' derived table/view. + If optimizer finds out that it's of the type "SELECT a_constant" then this + functions also materializes it. + + @return FALSE ok. + @return TRUE if an error occur. */ -bool mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *orig_table_list) +bool mysql_derived_optimize(THD *thd, LEX *lex, TABLE_LIST *derived) { - TABLE *table= orig_table_list->table; - SELECT_LEX_UNIT *unit= orig_table_list->derived; + SELECT_LEX_UNIT *unit= derived->get_unit(); + SELECT_LEX *first_select= unit->first_select(); + SELECT_LEX *save_current_select= lex->current_select; + bool res= FALSE; - /*check that table creation pass without problem and it is derived table */ - if (table && unit) + if (unit->optimized && !unit->uncacheable && !unit->describe) + return FALSE; + lex->current_select= first_select; + + if (unit->is_union()) + { + // optimize union without execution + res= unit->optimize(); + } + else if (unit->derived) { - SELECT_LEX *first_select= unit->first_select(); - select_union *derived_result= orig_table_list->derived_result; - SELECT_LEX *save_current_select= lex->current_select; - if (unit->is_union()) + if (!derived->is_merged_derived()) { - // execute union without clean up - res= unit->exec(); + unit->optimized= TRUE; + if ((res= first_select->join->optimize())) + goto err; } - else + } + /* + Materialize derived tables/views of the "SELECT a_constant" type. + Such tables should be materialized at the optimization phase for + correct constant evaluation. + */ + if (!res && derived->fill_me && !derived->merged_for_insert) + { + if (derived->is_merged_derived()) { - unit->set_limit(first_select); - if (unit->select_limit_cnt == HA_POS_ERROR) - first_select->options&= ~OPTION_FOUND_ROWS; - - lex->current_select= first_select; - res= mysql_select(thd, &first_select->ref_pointer_array, - first_select->table_list.first, - first_select->with_wild, - first_select->item_list, first_select->where, - (first_select->order_list.elements+ - first_select->group_list.elements), - first_select->order_list.first, - first_select->group_list.first, - first_select->having, (ORDER*) NULL, - (first_select->options | thd->options | - SELECT_NO_UNLOCK), - derived_result, unit, first_select); + derived->change_refs_to_fields(); + derived->set_materialized_derived(); } + if ((res= mysql_derived_create(thd, lex, derived))) + goto err; + if ((res= mysql_derived_fill(thd, lex, derived))) + goto err; + } +err: + lex->current_select= save_current_select; + return res; +} - if (!res) - { - /* - Here we entirely fix both TABLE_LIST and list of SELECT's as - there were no derived tables - */ - if (derived_result->flush()) - res= TRUE; - - if (!lex->describe) - unit->cleanup(); - } - else - unit->cleanup(); - lex->current_select= save_current_select; + +/** + @brief + Actually create result table for a materialized derived table/view. + + @param thd thread handle + @param lex LEX of the embedding query. + @param derived reference to the derived table. + + @details + This function actually creates the result table for given 'derived' + table/view, but it doesn't fill it. + 'thd' and 'lex' parameters are not used by this function. + + @return FALSE ok. + @return TRUE if an error occur. +*/ + +bool mysql_derived_create(THD *thd, LEX *lex, TABLE_LIST *derived) +{ + TABLE *table= derived->table; + SELECT_LEX_UNIT *unit= derived->get_unit(); + + if (table->created) + return FALSE; + select_union *result= (select_union*)unit->result; + if (table->s->db_type() == TMP_ENGINE_HTON) + { + if (create_internal_tmp_table(table, result->tmp_table_param.keyinfo, + result->tmp_table_param.start_recinfo, + &result->tmp_table_param.recinfo, + (unit->first_select()->options | + thd->options | TMP_TABLE_ALL_COLUMNS))) + return(TRUE); } + if (open_tmp_table(table)) + return TRUE; + table->file->extra(HA_EXTRA_WRITE_CACHE); + table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); + return FALSE; +} + + +/* + @brief + Execute subquery of a materialized derived table/view and fill the result + table. + + @param thd Thread handle + @param lex LEX for this thread + @param derived reference to the derived table. + + @details + Execute subquery of given 'derived' table/view and fill the result + table. After result table is filled, if this is not the EXPLAIN statement, + the entire unit / node is deleted. unit is deleted if UNION is used + for derived table and node is deleted is it is a simple SELECT. + 'lex' is unused and 'thd' is passed as an argument to an underlying function. + + @note + If you use this function, make sure it's not called at prepare. + Due to evaluation of LIMIT clause it can not be used at prepared stage. + + @return FALSE OK + @return TRUE Error +*/ + +bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) +{ + TABLE *table= derived->table; + SELECT_LEX_UNIT *unit= derived->get_unit(); + bool res= FALSE; + + if (unit->executed && !unit->uncacheable && !unit->describe) + return FALSE; + /*check that table creation passed without problems. */ + DBUG_ASSERT(table && table->created); + SELECT_LEX *first_select= unit->first_select(); + select_union *derived_result= derived->derived_result; + SELECT_LEX *save_current_select= lex->current_select; + if (unit->is_union()) + { + // execute union without clean up + res= unit->exec(); + } + else + { + unit->set_limit(first_select); + if (unit->select_limit_cnt == HA_POS_ERROR) + first_select->options&= ~OPTION_FOUND_ROWS; + + lex->current_select= first_select; + res= mysql_select(thd, &first_select->ref_pointer_array, + (TABLE_LIST*) first_select->table_list.first, + first_select->with_wild, + first_select->item_list, first_select->where, + (first_select->order_list.elements+ + first_select->group_list.elements), + (ORDER *) first_select->order_list.first, + (ORDER *) first_select->group_list.first, + first_select->having, (ORDER*) NULL, + (first_select->options | thd->options | + SELECT_NO_UNLOCK), + derived_result, unit, first_select); + } + + if (!res) + { + if (derived_result->flush()) + res= TRUE; + unit->executed= TRUE; + } + if (res || !lex->describe) + unit->cleanup(); + lex->current_select= save_current_select; + return res; } + + +/** + @brief + Re-initialize given derived table/view for the next execution. + + @param thd thread handle + @param lex LEX for this thread + @param derived reference to the derived table. + + @details + Re-initialize given 'derived' table/view for the next execution. + All underlying views/derived tables are recursively reinitialized prior + to re-initialization of given derived table. + 'thd' and 'lex' are passed as arguments to called functions. + + @return FALSE OK + @return TRUE Error +*/ + +bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived) +{ + st_select_lex_unit *unit= derived->get_unit(); + + if (derived->table) + derived->merged_for_insert= FALSE; + unit->unclean(); + unit->types.empty(); + /* for derived tables & PS (which can't be reset by Item_subquery) */ + unit->reinit_exec_mechanism(); + unit->set_thd(thd); + return FALSE; +} diff --git a/sql/sql_help.cc b/sql/sql_help.cc index 6ede7bb9574..af946737b87 100644 --- a/sql/sql_help.cc +++ b/sql/sql_help.cc @@ -637,7 +637,7 @@ bool mysqld_help(THD *thd, const char *mask) Protocol *protocol= thd->protocol; SQL_SELECT *select; st_find_field used_fields[array_elements(init_used_fields)]; - TABLE_LIST *leaves= 0; + List<TABLE_LIST> leaves; TABLE_LIST tables[4]; List<String> topics_list, categories_list, subcategories_list; String name, description, example; @@ -676,7 +676,7 @@ bool mysqld_help(THD *thd, const char *mask) thd->lex->select_lex.context.first_name_resolution_table= &tables[0]; if (setup_tables(thd, &thd->lex->select_lex.context, &thd->lex->select_lex.top_join_list, - tables, &leaves, FALSE)) + tables, leaves, FALSE, FALSE)) goto error; memcpy((char*) used_fields, (char*) init_used_fields, sizeof(used_fields)); if (init_fields(thd, tables, used_fields, array_elements(used_fields))) diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 05804c2ee7e..4691229659e 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -115,7 +115,7 @@ bool check_view_single_update(List<Item> &fields, List<Item> *values, { it.init(*values); while ((item= it++)) - tables|= item->used_tables(); + tables|= item->view_used_tables(view); } /* Convert to real table bits */ @@ -131,6 +131,11 @@ bool check_view_single_update(List<Item> &fields, List<Item> *values, if (view->check_single_table(&tbl, tables, view) || tbl == 0) goto error; + /* + A buffer for the insert values was allocated for the merged view. + Use it. + */ + //tbl->table->insert_values= view->table->insert_values; view->table= tbl->table; *map= tables; @@ -234,6 +239,10 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list, */ table_list->next_local= 0; context->resolve_in_table_list_only(table_list); + /* 'Unfix' fields to allow correct marking by the setup_fields function. */ + if (table_list->is_view()) + unfix_fields(fields); + res= setup_fields(thd, 0, fields, MARK_COLUMNS_WRITE, 0, 0); /* Restore the current context. */ @@ -243,7 +252,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list, if (res) return -1; - if (table_list->effective_algorithm == VIEW_ALGORITHM_MERGE) + if (table_list->is_view() && table_list->is_merged_derived()) { if (check_view_single_update(fields, fields_and_values_from_different_maps ? @@ -332,7 +341,8 @@ static int check_update_fields(THD *thd, TABLE_LIST *insert_table_list, if (setup_fields(thd, 0, update_fields, MARK_COLUMNS_WRITE, 0, 0)) return -1; - if (insert_table_list->effective_algorithm == VIEW_ALGORITHM_MERGE && + if (insert_table_list->is_view() && + insert_table_list->is_merged_derived() && check_view_single_update(update_fields, &update_values, insert_table_list, map)) return -1; @@ -631,6 +641,12 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, table_list->table_name); DBUG_RETURN(TRUE); } + /* + mark the table_list as a target for insert, to skip the DT/view prepare phase + for correct access rights checks + TODO: remove this hack + */ + table_list->skip_prepare_derived= TRUE; if (table_list->lock_type == TL_WRITE_DELAYED) { @@ -642,6 +658,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, if (open_and_lock_tables(thd, table_list)) DBUG_RETURN(TRUE); } + lock_type= table_list->lock_type; thd_proc_info(thd, "init"); @@ -1005,6 +1022,12 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, ::my_ok(thd, (ulong) thd->row_count_func, id, buff); } thd->abort_on_warning= 0; + if (thd->lex->current_select->first_cond_optimization) + { + thd->lex->current_select->save_leaf_tables(thd); + thd->lex->current_select->first_cond_optimization= 0; + } + DBUG_RETURN(FALSE); abort: @@ -1133,6 +1156,11 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list, bool insert_into_view= (table_list->view != 0); DBUG_ENTER("mysql_prepare_insert_check_table"); + if (!table_list->updatable) + { + my_error(ER_NON_INSERTABLE_TABLE, MYF(0), table_list->alias, "INSERT"); + DBUG_RETURN(TRUE); + } /* first table in list is the one we'll INSERT into, requires INSERT_ACL. all others require SELECT_ACL only. the ACL requirement below is for @@ -1143,14 +1171,16 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list, if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context, &thd->lex->select_lex.top_join_list, table_list, - &thd->lex->select_lex.leaf_tables, - select_insert, INSERT_ACL, SELECT_ACL)) + thd->lex->select_lex.leaf_tables, + select_insert, INSERT_ACL, SELECT_ACL, + TRUE)) DBUG_RETURN(TRUE); if (insert_into_view && !fields.elements) { thd->lex->empty_field_list_on_rset= 1; - if (!table_list->table) + if (!thd->lex->select_lex.leaf_tables.head()->table || + table_list->is_multitable()) { my_error(ER_VIEW_NO_INSERT_FIELD_LIST, MYF(0), table_list->view_db.str, table_list->view_name.str); @@ -1241,6 +1271,12 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, /* INSERT should have a SELECT or VALUES clause */ DBUG_ASSERT (!select_insert || !values); + if (mysql_handle_derived(thd->lex, DT_INIT)) + DBUG_RETURN(TRUE); + if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(TRUE); + if (mysql_handle_list_of_derived(thd->lex, table_list, DT_PREPARE)) + DBUG_RETURN(TRUE); /* For subqueries in VALUES() we should not see the table in which we are inserting (for INSERT ... SELECT this is done by changing table_list, @@ -2929,9 +2965,9 @@ bool mysql_insert_select_prepare(THD *thd) { LEX *lex= thd->lex; SELECT_LEX *select_lex= &lex->select_lex; - TABLE_LIST *first_select_leaf_table; DBUG_ENTER("mysql_insert_select_prepare"); + /* Statement-based replication of INSERT ... SELECT ... LIMIT is not safe as order of rows is not defined, so in mixed mode we go to row-based. @@ -2957,21 +2993,37 @@ bool mysql_insert_select_prepare(THD *thd) &select_lex->where, TRUE, FALSE, FALSE)) DBUG_RETURN(TRUE); + DBUG_ASSERT(select_lex->leaf_tables.elements != 0); + List_iterator<TABLE_LIST> ti(select_lex->leaf_tables); + TABLE_LIST *table; + uint insert_tables; + + if (select_lex->first_cond_optimization) + { + /* Back up leaf_tables list. */ + Query_arena *arena= thd->stmt_arena, backup; + arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test + + insert_tables= select_lex->insert_tables; + while ((table= ti++) && insert_tables--) + { + select_lex->leaf_tables_exec.push_back(table); + table->tablenr_exec= table->table->tablenr; + table->map_exec= table->table->map; + } + if (arena) + thd->restore_active_arena(arena, &backup); + } + ti.rewind(); /* exclude first table from leaf tables list, because it belong to INSERT */ - DBUG_ASSERT(select_lex->leaf_tables != 0); - lex->leaf_tables_insert= select_lex->leaf_tables; /* skip all leaf tables belonged to view where we are insert */ - for (first_select_leaf_table= select_lex->leaf_tables->next_leaf; - first_select_leaf_table && - first_select_leaf_table->belong_to_view && - first_select_leaf_table->belong_to_view == - lex->leaf_tables_insert->belong_to_view; - first_select_leaf_table= first_select_leaf_table->next_leaf) - {} - select_lex->leaf_tables= first_select_leaf_table; + insert_tables= select_lex->insert_tables; + while ((table= ti++) && insert_tables--) + ti.remove(); + DBUG_RETURN(FALSE); } @@ -3188,7 +3240,7 @@ void select_insert::cleanup() select_insert::~select_insert() { DBUG_ENTER("~select_insert"); - if (table) + if (table && table->created) { table->next_number_field=0; table->auto_increment_field_not_null= FALSE; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f2e862b6c36..bbdcbab8133 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -23,6 +23,7 @@ #include <hash.h> #include "sp.h" #include "sp_head.h" +#include "sql_select.h" /* We are using pointer to this variable for distinguishing between assignment @@ -309,7 +310,6 @@ void lex_start(THD *thd) lex->derived_tables= 0; lex->lock_option= TL_READ; lex->safe_to_cache_query= 1; - lex->leaf_tables_insert= 0; lex->parsing_options.reset(); lex->empty_field_list_on_rset= 0; lex->select_lex.select_number= 1; @@ -1591,6 +1591,7 @@ void st_select_lex_unit::init_query() describe= 0; found_rows_for_union= 0; insert_table_with_stored_vcol= 0; + derived= 0; } void st_select_lex::init_query() @@ -1599,7 +1600,8 @@ void st_select_lex::init_query() table_list.empty(); top_join_list.empty(); join_list= &top_join_list; - embedding= leaf_tables= 0; + embedding= 0; + leaf_tables.empty(); item_list.empty(); join= 0; having= prep_having= where= prep_where= 0; @@ -2151,9 +2153,27 @@ void st_select_lex::print_order(String *str, { if (order->counter_used) { - char buffer[20]; - size_t length= my_snprintf(buffer, 20, "%d", order->counter); - str->append(buffer, (uint) length); + if (query_type != QT_VIEW_INTERNAL) + { + char buffer[20]; + size_t length= my_snprintf(buffer, 20, "%d", order->counter); + str->append(buffer, (uint) length); + } + else + { + /* replace numeric reference with expression */ + if (order->item[0]->type() == Item::INT_ITEM && + order->item[0]->basic_const_item()) + { + char buffer[20]; + size_t length= my_snprintf(buffer, 20, "%d", order->counter); + str->append(buffer, (uint) length); + /* make it expression instead of integer constant */ + str->append(STRING_WITH_LEN("+0")); + } + else + (*order->item)->print(str, query_type); + } } else (*order->item)->print(str, query_type); @@ -2345,22 +2365,6 @@ bool st_lex::can_be_merged() /* find non VIEW subqueries/unions */ bool selects_allow_merge= select_lex.next_select() == 0; - if (selects_allow_merge) - { - for (SELECT_LEX_UNIT *tmp_unit= select_lex.first_inner_unit(); - tmp_unit; - tmp_unit= tmp_unit->next_unit()) - { - if (tmp_unit->first_select()->parent_lex == this && - (tmp_unit->item == 0 || - (tmp_unit->item->place() != IN_WHERE && - tmp_unit->item->place() != IN_ON))) - { - selects_allow_merge= 0; - break; - } - } - } return (selects_allow_merge && select_lex.group_list.elements == 0 && @@ -2990,7 +2994,11 @@ static void fix_prepare_info_in_table_list(THD *thd, TABLE_LIST *tbl) thd->check_and_register_item_tree(&tbl->prep_on_expr, &tbl->on_expr); tbl->on_expr= tbl->on_expr->copy_andor_structure(thd); } - fix_prepare_info_in_table_list(thd, tbl->merge_underlying_list); + if (tbl->is_view_or_derived() && tbl->is_merged_derived()) + { + SELECT_LEX *sel= tbl->get_single_select(); + fix_prepare_info_in_table_list(thd, sel->get_table_list()); + } } } @@ -3116,6 +3124,8 @@ bool st_select_lex::optimize_unflattened_subqueries() for (SELECT_LEX *sl= un->first_select(); sl; sl= sl->next_select()) { JOIN *inner_join= sl->join; + if (!inner_join) + continue; SELECT_LEX *save_select= un->thd->lex->current_select; ulonglong save_options; int res; @@ -3141,19 +3151,348 @@ bool st_select_lex::optimize_unflattened_subqueries() } + +/** + @brief Process all derived tables/views of the SELECT. + + @param lex LEX of this thread + @param phase phases to run derived tables/views through + + @details + This function runs specified 'phases' on all tables from the + table_list of this select. + + @return FALSE ok. + @return TRUE an error occur. +*/ + +bool st_select_lex::handle_derived(struct st_lex *lex, uint phases) +{ + for (TABLE_LIST *cursor= (TABLE_LIST*) table_list.first; + cursor; + cursor= cursor->next_local) + { + if (cursor->is_view_or_derived() && cursor->handle_derived(lex, phases)) + return TRUE; + } + return FALSE; +} + + +/** + @brief + Returns first unoccupied table map and table number + + @param map [out] return found map + @param tablenr [out] return found tablenr + + @details + Returns first unoccupied table map and table number in this select. + Map and table are returned in *'map' and *'tablenr' accordingly. + + @retrun TRUE no free table map/table number + @return FALSE found free table map/table number +*/ + +bool st_select_lex::get_free_table_map(table_map *map, uint *tablenr) +{ + *map= 0; + *tablenr= 0; + TABLE_LIST *tl; + if (!join) + { + (*map)= 1<<1; + (*tablenr)++; + return FALSE; + } + List_iterator<TABLE_LIST> ti(leaf_tables); + while ((tl= ti++)) + { + if (tl->table->map > *map) + *map= tl->table->map; + if (tl->table->tablenr > *tablenr) + *tablenr= tl->table->tablenr; + } + (*map)<<= 1; + (*tablenr)++; + if (*tablenr >= MAX_TABLES) + return TRUE; + return FALSE; +} + + +/** + @brief + Append given table to the leaf_tables list. + + @param link Offset to which list in table structure to use + @param table Table to append + + @details + Append given 'table' to the leaf_tables list using the 'link' offset. + If the 'table' is linked with other tables through next_leaf/next_local + chains then whole list will be appended. +*/ + +void st_select_lex::append_table_to_list(TABLE_LIST *TABLE_LIST::*link, + TABLE_LIST *table) +{ + TABLE_LIST *tl; + for (tl= leaf_tables.head(); tl->*link; tl= tl->*link); + tl->*link= table; +} + +/* + @brief + Remove given table from the leaf_tables list. + + @param link Offset to which list in table structure to use + @param table Table to remove + + @details + Remove 'table' from the leaf_tables list using the 'link' offset. +*/ + +void st_select_lex::remove_table_from_list(TABLE_LIST *table) +{ + TABLE_LIST *tl; + List_iterator<TABLE_LIST> ti(leaf_tables); + while ((tl= ti++)) + { + if (tl == table) + { + ti.remove(); + break; + } + } +} + + +/** + @brief + Assigns new table maps to tables in the leaf_tables list + + @param derived Derived table to take initial table map from + @param map table map to begin with + @param tablenr table number to begin with + @param parent_lex new parent select_lex + + @details + Assign new table maps/table numbers to all tables in the leaf_tables list. + 'map'/'tablenr' are used for the first table and shifted to left/ + increased for each consequent table in the leaf_tables list. + If the 'derived' table is given then it's table map/number is used for the + first table in the list and 'map'/'tablenr' are used for the second and + all consequent tables. + The 'parent_lex' is set as the new parent select_lex for all tables in the + list. +*/ + +void st_select_lex::remap_tables(TABLE_LIST *derived, table_map map, + uint tablenr, SELECT_LEX *parent_lex) +{ + bool first_table= TRUE; + TABLE_LIST *tl; + table_map first_map; + uint first_tablenr; + + if (derived && derived->table) + { + first_map= derived->table->map; + first_tablenr= derived->table->tablenr; + } + else + { + first_map= map; + map<<= 1; + first_tablenr= tablenr++; + } + /* + Assign table bit/table number. + To the first table of the subselect the table bit/tablenr of the + derived table is assigned. The rest of tables are getting bits + sequentially, starting from the provided table map/tablenr. + */ + List_iterator<TABLE_LIST> ti(leaf_tables); + while ((tl= ti++)) + { + if (first_table) + { + first_table= FALSE; + tl->table->set_table_map(first_map, first_tablenr); + } + else + { + tl->table->set_table_map(map, tablenr); + tablenr++; + map<<= 1; + } + SELECT_LEX *old_sl= tl->select_lex; + tl->select_lex= parent_lex; + for(TABLE_LIST *emb= tl->embedding; + emb && emb->select_lex == old_sl; + emb= emb->embedding) + emb->select_lex= parent_lex; + } +} + +/** + @brief + Merge a subquery into this select. + + @param derived derived table of the subquery to be merged + @param subq_select select_lex of the subquery + @param map table map for assigning to merged tables from subquery + @param table_no table number for assigning to merged tables from subquery + + @details + This function merges a subquery into its parent select. In short the + merge operation appends the subquery FROM table list to the parent's + FROM table list. In more details: + .) the top_join_list of the subquery is wrapped into a join_nest + and attached to 'derived' + .) subquery's leaf_tables list is merged with the leaf_tables + list of this select_lex + .) the table maps and table numbers of the tables merged from + the subquery are adjusted to reflect their new binding to + this select + + @return TRUE an error occur + @return FALSE ok +*/ + +bool SELECT_LEX::merge_subquery(TABLE_LIST *derived, SELECT_LEX *subq_select, + uint table_no, table_map map) +{ + derived->wrap_into_nested_join(subq_select->top_join_list); + /* Reconnect the next_leaf chain. */ + leaf_tables.concat(&subq_select->leaf_tables); + + ftfunc_list->concat(subq_select->ftfunc_list); + if (join) + { + Item_in_subselect **in_subq; + Item_in_subselect **in_subq_end; + for (in_subq= subq_select->join->sj_subselects.front(), + in_subq_end= subq_select->join->sj_subselects.back(); + in_subq != in_subq_end; + in_subq++) + { + join->sj_subselects.append(join->thd->mem_root, *in_subq); + (*in_subq)->emb_on_expr_nest= derived; + } + } + /* + Remove merged table from chain. + When merge_subquery is called at a subquery-to-semijoin transformation + the derived isn't in the leaf_tables list, so in this case the call of + remove_table_from_list does not cause any actions. + */ + remove_table_from_list(derived); + + /* Walk through child's tables and adjust table map, tablenr, + * parent_lex */ + subq_select->remap_tables(derived, map, table_no, this); + return FALSE; +} + + +/** + @brief + Mark tables from the leaf_tables list as belong to a derived table. + + @param derived tables will be marked as belonging to this derived + + @details + Run through the leaf_list and mark all tables as belonging to the 'derived'. +*/ + +void SELECT_LEX::mark_as_belong_to_derived(TABLE_LIST *derived) +{ + /* Mark tables as belonging to this DT */ + TABLE_LIST *tl; + List_iterator<TABLE_LIST> ti(leaf_tables); + while ((tl= ti++)) + { + tl->skip_temporary= 1; + tl->belong_to_derived= derived; + } +} + + +/** + @brief + Update used_tables cache for this select + + @details + This function updates used_tables cache of ON expressions of all tables + in the leaf_tables list and of the conds expression (if any). +*/ + +void SELECT_LEX::update_used_tables() +{ + TABLE_LIST *tl; + List_iterator<TABLE_LIST> ti(leaf_tables); + while ((tl= ti++)) + { + if (tl->on_expr) + { + tl->on_expr->update_used_tables(); + tl->on_expr->walk(&Item::eval_not_null_tables, 0, NULL); + } + TABLE_LIST *embedding= tl->embedding; + while (embedding) + { + if (embedding->on_expr && + embedding->nested_join->join_list.head() == tl) + { + embedding->on_expr->update_used_tables(); + embedding->on_expr->walk(&Item::eval_not_null_tables, 0, NULL); + } + tl= embedding; + embedding= tl->embedding; + } + } + if (join->conds) + { + join->conds->update_used_tables(); + join->conds->walk(&Item::eval_not_null_tables, 0, NULL); + } +} + + /** Set the EXPLAIN type for this subquery. */ void st_select_lex::set_explain_type() { + bool is_primary= FALSE; + if (next_select()) + is_primary= TRUE; + + if (!is_primary && first_inner_unit()) + { + /* + If there is at least one materialized derived|view then it's a PRIMARY select. + Otherwise, all derived tables/views were merged and this select is a SIMPLE one. + */ + for (SELECT_LEX_UNIT *un= first_inner_unit(); un; un= un->next_unit()) + { + if ((!un->derived || un->derived->is_materialized_derived())) + { + is_primary= TRUE; + break; + } + } + } + SELECT_LEX *first= master_unit()->first_select(); /* drop UNCACHEABLE_EXPLAIN, because it is for internal usage only */ uint8 is_uncacheable= (uncacheable & ~UNCACHEABLE_EXPLAIN); type= ((&master_unit()->thd->lex->select_lex == this) ? - (first_inner_unit() || next_select() ? - "PRIMARY" : "SIMPLE") : + (is_primary ? "PRIMARY" : "SIMPLE"): ((this == first) ? ((linkage == DERIVED_TABLE_TYPE) ? "DERIVED" : @@ -3170,6 +3509,76 @@ void st_select_lex::set_explain_type() /** + @brief + Increase estimated number of records for a derived table/view + + @param records number of records to increase estimate by + + @details + This function increases estimated number of records by the 'records' + for the derived table to which this select belongs to. +*/ + +void SELECT_LEX::increase_derived_records(uint records) +{ + SELECT_LEX_UNIT *unit= master_unit(); + DBUG_ASSERT(unit->derived); + + select_union *result= (select_union*)unit->result; + result->records+= records; +} + + +/** + @brief + Mark select's derived table as a const one. + + @param empty Whether select has an empty result set + + @details + Mark derived table/view of this select as a constant one (to + materialize it at the optimization phase) unless this select belongs to a + union. Estimated number of rows is incremented if this select has non empty + result set. +*/ + +void SELECT_LEX::mark_const_derived(bool empty) +{ + TABLE_LIST *derived= master_unit()->derived; + if (!join->thd->lex->describe && derived) + { + if (!empty) + increase_derived_records(1); + if (!master_unit()->is_union() && !derived->is_merged_derived()) + derived->fill_me= TRUE; + } +} + +bool st_select_lex::save_leaf_tables(THD *thd) +{ + Query_arena *arena= thd->stmt_arena, backup; + if (arena->is_conventional()) + arena= 0; + else + thd->set_n_backup_active_arena(arena, &backup); + + List_iterator_fast<TABLE_LIST> li(leaf_tables); + TABLE_LIST *table; + while ((table= li++)) + { + if (leaf_tables_exec.push_back(table)) + return 1; + table->tablenr_exec= table->table->tablenr; + table->map_exec= table->table->map; + } + if (arena) + thd->restore_active_arena(arena, &backup); + + return 0; +} + + +/** A routine used by the parser to decide whether we are specifying a full partitioning or if only partitions to add or to split. diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 6fc1167235c..e8efae8859f 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -472,6 +472,11 @@ public: friend bool mysql_new_select(struct st_lex *lex, bool move_down); friend bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table, uint flags); + friend bool mysql_derived_prepare(THD *thd, st_lex *lex, + TABLE_LIST *orig_table_list); + friend bool mysql_derived_merge(THD *thd, st_lex *lex, + TABLE_LIST *orig_table_list); + friend bool TABLE_LIST::init_derived(THD *thd, bool init_view); private: void fast_exclude(); }; @@ -490,13 +495,12 @@ class st_select_lex_unit: public st_select_lex_node { protected: TABLE_LIST result_table_list; select_union *union_result; - TABLE *table; /* temporary table using for appending UNION results */ - - select_result *result; ulonglong found_rows_for_union; bool saved_error; public: + TABLE *table; /* temporary table using for appending UNION results */ + select_result *result; bool prepared, // prepare phase already performed for UNION (unit) optimized, // optimize phase already performed for UNION (unit) executed, // already executed @@ -523,6 +527,11 @@ public: ha_rows select_limit_cnt, offset_limit_cnt; /* not NULL if unit used in subselect, point to subselect item */ Item_subselect *item; + /* + TABLE_LIST representing this union in the embedding select. Used for + derived tables/views handling. + */ + TABLE_LIST *derived; /* thread handler */ THD *thd; /* @@ -559,6 +568,7 @@ public: /* UNION methods */ bool prepare(THD *thd, select_result *result, ulong additional_options); + bool optimize(); bool exec(); bool cleanup(); inline void unclean() { cleaned= 0; } @@ -620,8 +630,15 @@ public: Beginning of the list of leaves in a FROM clause, where the leaves inlcude all base tables including view tables. The tables are connected by TABLE_LIST::next_leaf, so leaf_tables points to the left-most leaf. + + List of all base tables local to a subquery including all view + tables. Unlike 'next_local', this in this list views are *not* + leaves. Created in setup_tables() -> make_leaves_list(). */ - TABLE_LIST *leaf_tables; + List<TABLE_LIST> leaf_tables; + List<TABLE_LIST> leaf_tables_exec; + uint insert_tables; + const char *type; /* type of select for EXPLAIN */ SQL_I_List<ORDER> order_list; /* ORDER clause */ @@ -857,6 +874,27 @@ public: bool optimize_unflattened_subqueries(); /* Set the EXPLAIN type for this subquery. */ void set_explain_type(); + bool handle_derived(struct st_lex *lex, uint phases); + void append_table_to_list(TABLE_LIST *TABLE_LIST::*link, TABLE_LIST *table); + bool get_free_table_map(table_map *map, uint *tablenr); + void remove_table_from_list(TABLE_LIST *table); + void remap_tables(TABLE_LIST *derived, table_map map, + uint tablenr, st_select_lex *parent_lex); + bool merge_subquery(TABLE_LIST *derived, st_select_lex *subq_lex, + uint tablenr, table_map map); + inline bool is_mergeable() + { + return (next_select() == 0 && group_list.elements == 0 && + having == 0 && with_sum_func == 0 && + table_list.elements >= 1 && !(options & SELECT_DISTINCT) && + select_limit == 0); + } + void mark_as_belong_to_derived(TABLE_LIST *derived); + void increase_derived_records(uint records); + void update_used_tables(); + void mark_const_derived(bool empty); + + bool save_leaf_tables(THD *thd); private: /* current index hint kind. used in filling up index_hints */ @@ -1629,8 +1667,6 @@ typedef struct st_lex : public Query_tables_list CHARSET_INFO *charset; bool text_string_is_7bit; - /* store original leaf_tables for INSERT SELECT and PS/SP */ - TABLE_LIST *leaf_tables_insert; /** SELECT of CREATE VIEW statement */ LEX_STRING create_view_select; @@ -1747,7 +1783,7 @@ typedef struct st_lex : public Query_tables_list DERIVED_SUBQUERY and DERIVED_VIEW). */ uint8 derived_tables; - uint8 create_view_algorithm; + uint16 create_view_algorithm; uint8 create_view_check; bool drop_if_exists, drop_temporary, local_file, one_shot_set; bool autocommit; @@ -1934,6 +1970,8 @@ typedef struct st_lex : public Query_tables_list switch (sql_command) { case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: case SQLCOM_INSERT: case SQLCOM_INSERT_SELECT: case SQLCOM_REPLACE: diff --git a/sql/sql_list.h b/sql/sql_list.h index e90f16aeb99..3a6e61be9e3 100644 --- a/sql/sql_list.h +++ b/sql/sql_list.h @@ -260,11 +260,13 @@ public: list_node *node= first; list_node *list_first= list->first; elements=0; - while (node->info && node != list_first) + while (node != list_first) { prev= &node->next; node= node->next; elements++; + if (node == &end_of_list) + return; } *prev= *last; last= prev; diff --git a/sql/sql_load.cc b/sql/sql_load.cc index d6276d2f47c..905d32892d9 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -172,12 +172,15 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, if (open_and_lock_tables(thd, table_list)) DBUG_RETURN(TRUE); + if (mysql_handle_single_derived(thd->lex, table_list, DT_MERGE_FOR_INSERT) || + mysql_handle_single_derived(thd->lex, table_list, DT_PREPARE)) + DBUG_RETURN(TRUE); if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context, &thd->lex->select_lex.top_join_list, table_list, - &thd->lex->select_lex.leaf_tables, FALSE, + thd->lex->select_lex.leaf_tables, FALSE, INSERT_ACL | UPDATE_ACL, - INSERT_ACL | UPDATE_ACL)) + INSERT_ACL | UPDATE_ACL, FALSE)) DBUG_RETURN(-1); if (!table_list->table || // do not suport join view !table_list->updatable || // and derived tables diff --git a/sql/sql_olap.cc b/sql/sql_olap.cc index 21deef8c664..b0537d984c6 100644 --- a/sql/sql_olap.cc +++ b/sql/sql_olap.cc @@ -153,8 +153,7 @@ int handle_olaps(LEX *lex, SELECT_LEX *select_lex) if (setup_tables(lex->thd, &select_lex->context, &select_lex->top_join_list, - select_lex->table_list.first - &select_lex->leaf_tables, FALSE) || + FALSE, FALSE) || setup_fields(lex->thd, 0, select_lex->item_list, MARK_COLUMNS_READ, &all_fields,1) || setup_fields(lex->thd, 0, item_list_copy, MARK_COLUMNS_READ, diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index e2031c129b0..0f796b92f30 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2709,6 +2709,9 @@ mysql_execute_command(THD *thd) } } } + if (mysql_handle_single_derived(thd->lex, create_table, + DT_MERGE_FOR_INSERT)) + DBUG_RETURN(1); /* So that CREATE TEMPORARY TABLE gets to binlog at commit/rollback */ if (create_info.options & HA_LEX_CREATE_TMP_TABLE) @@ -3235,6 +3238,10 @@ end_with_restore_list: if (!(res= open_and_lock_tables(thd, all_tables))) { + /* + Only the INSERT table should be merged. Other will be handled by + select. + */ /* Skip first table, which is the table we are inserting in */ TABLE_LIST *second_table= first_table->next_local; select_lex->table_list.first= second_table; @@ -5200,6 +5207,8 @@ bool check_single_table_access(THD *thd, ulong privilege, /* Show only 1 table for check_grant */ if (!(all_tables->belong_to_view && (thd->lex->sql_command == SQLCOM_SHOW_FIELDS)) && + !(all_tables->is_view() && + all_tables->is_merged_derived()) && check_grant(thd, privilege, all_tables, 0, 1, no_errors)) goto deny; diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index db623133cb1..75649062b11 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -1168,7 +1168,7 @@ static bool mysql_test_insert(Prepared_statement *stmt, If we would use locks, then we have to ensure we are not using TL_WRITE_DELAYED as having two such locks can cause table corruption. */ - if (open_normal_and_derived_tables(thd, table_list, 0)) + if (open_normal_and_derived_tables(thd, table_list, 0, DT_INIT)) goto error; if ((values= its++)) @@ -1252,7 +1252,10 @@ static int mysql_test_update(Prepared_statement *stmt, open_tables(thd, &table_list, &table_count, 0)) goto error; - if (table_list->multitable_view) + if (mysql_handle_derived(thd->lex, DT_INIT)) + goto error; + + if (table_list->is_multitable()) { DBUG_ASSERT(table_list->view != 0); DBUG_PRINT("info", ("Switch to multi-update")); @@ -1266,8 +1269,15 @@ static int mysql_test_update(Prepared_statement *stmt, thd->fill_derived_tables() is false here for sure (because it is preparation of PS, so we even do not check it). */ - if (mysql_handle_derived(thd->lex, &mysql_derived_prepare)) + if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT) || + table_list->handle_derived(thd->lex, DT_PREPARE)) + goto error; + + if (!table_list->updatable) + { + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE"); goto error; + } #ifndef NO_EMBEDDED_ACCESS_CHECKS /* Force privilege re-checking for views after they have been opened. */ @@ -1321,12 +1331,18 @@ error: static bool mysql_test_delete(Prepared_statement *stmt, TABLE_LIST *table_list) { + uint table_count= 0; THD *thd= stmt->thd; LEX *lex= stmt->lex; DBUG_ENTER("mysql_test_delete"); if (delete_precheck(thd, table_list) || - open_normal_and_derived_tables(thd, table_list, 0)) + open_tables(thd, &table_list, &table_count, 0)) + goto error; + + if (mysql_handle_derived(thd->lex, DT_INIT) || + mysql_handle_list_of_derived(thd->lex, table_list, DT_MERGE_FOR_INSERT) || + mysql_handle_list_of_derived(thd->lex, table_list, DT_PREPARE)) goto error; if (!table_list->table) @@ -1384,7 +1400,8 @@ static int mysql_test_select(Prepared_statement *stmt, goto error; } - if (open_normal_and_derived_tables(thd, tables, 0)) + if (open_normal_and_derived_tables(thd, tables, 0, + DT_PREPARE | DT_CREATE)) goto error; thd->used_tables= 0; // Updated by setup_fields @@ -1445,7 +1462,8 @@ static bool mysql_test_do_fields(Prepared_statement *stmt, if (tables && check_table_access(thd, SELECT_ACL, tables, UINT_MAX, FALSE)) DBUG_RETURN(TRUE); - if (open_normal_and_derived_tables(thd, tables, 0)) + if (open_normal_and_derived_tables(thd, tables, 0, + DT_PREPARE | DT_CREATE)) DBUG_RETURN(TRUE); DBUG_RETURN(setup_fields(thd, 0, *values, MARK_COLUMNS_NONE, 0, 0)); } @@ -1475,7 +1493,8 @@ static bool mysql_test_set_fields(Prepared_statement *stmt, if ((tables && check_table_access(thd, SELECT_ACL, tables, UINT_MAX, FALSE)) || - open_normal_and_derived_tables(thd, tables, 0)) + open_normal_and_derived_tables(thd, tables, 0, + DT_PREPARE | DT_CREATE)) goto error; while ((var= it++)) @@ -1512,7 +1531,7 @@ static bool mysql_test_call_fields(Prepared_statement *stmt, if ((tables && check_table_access(thd, SELECT_ACL, tables, UINT_MAX, FALSE)) || - open_normal_and_derived_tables(thd, tables, 0)) + open_normal_and_derived_tables(thd, tables, 0, DT_PREPARE)) goto err; while ((item= it++)) @@ -1587,6 +1606,7 @@ select_like_stmt_test_with_open(Prepared_statement *stmt, int (*specific_prepare)(THD *thd), ulong setup_tables_done_option) { + uint table_count= 0; DBUG_ENTER("select_like_stmt_test_with_open"); /* @@ -1595,7 +1615,8 @@ select_like_stmt_test_with_open(Prepared_statement *stmt, prepared EXPLAIN yet so derived tables will clean up after themself. */ - if (open_normal_and_derived_tables(stmt->thd, tables, 0)) + THD *thd= stmt->thd; + if (open_tables(thd, &tables, &table_count, 0)) DBUG_RETURN(TRUE); DBUG_RETURN(select_like_stmt_test(stmt, specific_prepare, @@ -1640,7 +1661,8 @@ static bool mysql_test_create_table(Prepared_statement *stmt) create_table->skip_temporary= true; } - if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0)) + if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0, + DT_PREPARE | DT_CREATE)) DBUG_RETURN(TRUE); if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE)) @@ -1658,7 +1680,8 @@ static bool mysql_test_create_table(Prepared_statement *stmt) we validate metadata of all CREATE TABLE statements, which keeps metadata validation code simple. */ - if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0)) + if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0, + DT_PREPARE)) DBUG_RETURN(TRUE); } @@ -1693,7 +1716,7 @@ static bool mysql_test_create_view(Prepared_statement *stmt) if (create_view_precheck(thd, tables, view, lex->create_view_mode)) goto err; - if (open_normal_and_derived_tables(thd, tables, 0)) + if (open_normal_and_derived_tables(thd, tables, 0, DT_PREPARE)) goto err; lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW; @@ -2429,6 +2452,7 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) /* Fix ORDER list */ for (order= sl->order_list.first; order; order= order->next) order->item= &order->item_ptr; + sl->handle_derived(lex, DT_REINIT); /* clear the no_error flag for INSERT/UPDATE IGNORE */ sl->no_error= FALSE; @@ -2472,9 +2496,6 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) } lex->current_select= &lex->select_lex; - /* restore original list used in INSERT ... SELECT */ - if (lex->leaf_tables_insert) - lex->select_lex.leaf_tables= lex->leaf_tables_insert; if (lex->result) { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5b88436b4cf..2c769e40f72 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -50,8 +50,8 @@ const char *copy_to_tmp_table= "Copying to tmp table"; struct st_sargable_param; static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array); -static bool make_join_statistics(JOIN *join, TABLE_LIST *leaves, COND *conds, - DYNAMIC_ARRAY *keyuse); +static bool make_join_statistics(JOIN *join, List<TABLE_LIST> &leaves, + COND *conds, DYNAMIC_ARRAY *keyuse); static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse, JOIN_TAB *join_tab, uint tables, COND *conds, @@ -100,7 +100,8 @@ static void update_depend_map(JOIN *join); static void update_depend_map(JOIN *join, ORDER *order); static ORDER *remove_const(JOIN *join,ORDER *first_order,COND *cond, bool change_list, bool *simple_order); -static int return_zero_rows(JOIN *join, select_result *res,TABLE_LIST *tables, +static int return_zero_rows(JOIN *join, select_result *res, + List<TABLE_LIST> &tables, List<Item> &fields, bool send_row, ulonglong select_options, const char *info, Item *having); @@ -211,7 +212,7 @@ static ORDER *create_distinct_group(THD *thd, Item **ref_pointer_array, List<Item> &all_fields, bool *all_order_by_fields_used); static bool test_if_subpart(ORDER *a,ORDER *b); -static TABLE *get_sort_by_table(ORDER *a,ORDER *b,TABLE_LIST *tables); +static TABLE *get_sort_by_table(ORDER *a,ORDER *b,List<TABLE_LIST> &tables); static void calc_group_buffer(JOIN *join,ORDER *group); static bool make_group_fields(JOIN *main_join, JOIN *curr_join); static bool alloc_group_fields(JOIN *join,ORDER *group); @@ -236,6 +237,7 @@ static void select_describe(JOIN *join, bool need_tmp_table,bool need_order, bool distinct, const char *message=NullS); static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab); static uint make_join_orderinfo(JOIN *join); +static bool generate_derived_keys(DYNAMIC_ARRAY *keyuse_array); static int join_read_record_no_init(JOIN_TAB *tab); @@ -422,7 +424,7 @@ fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select, */ inline int setup_without_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, - TABLE_LIST *leaves, + List<TABLE_LIST> &leaves, List<Item> &fields, List<Item> &all_fields, COND **conds, @@ -500,21 +502,22 @@ JOIN::prepare(Item ***rref_pointer_array, join_list= &select_lex->top_join_list; union_part= unit_arg->is_union(); + if (select_lex->handle_derived(thd->lex, DT_PREPARE)) + DBUG_RETURN(1); + thd->lex->current_select->is_item_list_lookup= 1; /* If we have already executed SELECT, then it have not sense to prevent its table from update (see unique_table()) + Affects only materialized derived tables. */ - if (thd->derived_tables_processing) - select_lex->exclude_from_table_unique_test= TRUE; - /* Check that all tables, fields, conds and order are ok */ - if (!(select_options & OPTION_SETUP_TABLES_DONE) && setup_tables_and_check_access(thd, &select_lex->context, join_list, - tables_list, &select_lex->leaf_tables, - FALSE, SELECT_ACL, SELECT_ACL)) + tables_list, select_lex->leaf_tables, + FALSE, SELECT_ACL, SELECT_ACL, FALSE)) DBUG_RETURN(-1); + /* TRUE if the SELECT list mixes elements with and without grouping, and there is no GROUP BY clause. Mixing non-aggregated fields with @@ -543,10 +546,12 @@ JOIN::prepare(Item ***rref_pointer_array, } } } + + tables= select_lex->leaf_tables.elements; - for (TABLE_LIST *table_ptr= select_lex->leaf_tables; - table_ptr; - table_ptr= table_ptr->next_leaf) + TABLE_LIST *tbl; + List_iterator_fast<TABLE_LIST> li(select_lex->leaf_tables); + while ((tbl= li++)) { tables++; /* Count the number of tables in the join. */ /* @@ -556,7 +561,7 @@ JOIN::prepare(Item ***rref_pointer_array, semantic analysis to take into account this change of nullability. */ if (mixed_implicit_grouping) - table_ptr->table->maybe_null= 1; + tbl->table->maybe_null= 1; } if ((wild_num && setup_wild(thd, tables_list, fields_list, &all_fields, @@ -674,10 +679,6 @@ JOIN::prepare(Item ***rref_pointer_array, } } - if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */ - DBUG_RETURN(-1); - - /* Check if there are references to un-aggregated columns when computing aggregate functions with implicit grouping (there is no GROUP BY). @@ -803,16 +804,45 @@ JOIN::optimize() if (optimized) DBUG_RETURN(0); optimized= 1; - thd_proc_info(thd, "optimizing"); set_allowed_join_cache_types(); - /* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */ - if (convert_max_min_subquery(this) || - convert_join_subqueries_to_semijoins(this)) - DBUG_RETURN(1); /* purecov: inspected */ - /* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */ + + /* Run optimize phase for all derived tables/views used in this SELECT. */ + if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE)) + DBUG_RETURN(1); + + if (select_lex->first_cond_optimization) + { + //Do it only for the first execution + /* Merge all mergeable derived tables/views in this SELECT. */ + if (select_lex->handle_derived(thd->lex, DT_MERGE)) + DBUG_RETURN(TRUE); + tables= select_lex->leaf_tables.elements; + select_lex->update_used_tables(); + } + + if (convert_max_min_subquery(this)) + DBUG_RETURN(1); + + if (select_lex->first_cond_optimization) + { + /* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */ + if (convert_join_subqueries_to_semijoins(this)) + DBUG_RETURN(1); /* purecov: inspected */ + /* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */ + select_lex->update_used_tables(); + + /* Save this info for the next executions */ + if (select_lex->save_leaf_tables(thd)) + DBUG_RETURN(1); + } + + tables= select_lex->leaf_tables.elements; + + if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */ + DBUG_RETURN(-1); row_limit= ((select_distinct || order || group_list) ? HA_POS_ERROR : unit->select_limit_cnt); @@ -846,7 +876,8 @@ JOIN::optimize() } } #endif - SELECT_LEX *sel= thd->lex->current_select; + + SELECT_LEX *sel= select_lex; if (sel->first_cond_optimization) { /* @@ -910,7 +941,8 @@ JOIN::optimize() #ifdef WITH_PARTITION_STORAGE_ENGINE { TABLE_LIST *tbl; - for (tbl= select_lex->leaf_tables; tbl; tbl= tbl->next_leaf) + List_iterator_fast<TABLE_LIST> li(select_lex->leaf_tables); + while ((tbl= li++)) { /* If tbl->embedding!=NULL that means that this table is in the inner @@ -1009,6 +1041,8 @@ JOIN::optimize() DBUG_RETURN(1); } + drop_unused_derived_keys(); + if (rollup.state != ROLLUP::STATE_NONE) { if (rollup_process_const_fields()) @@ -1144,6 +1178,7 @@ JOIN::optimize() { zero_result_cause= "Impossible WHERE noticed after reading const tables"; + select_lex->mark_const_derived(zero_result_cause); goto setup_subq_exit; } @@ -1473,7 +1508,7 @@ JOIN::optimize() if (select_options & SELECT_DESCRIBE) { error= 0; - DBUG_RETURN(0); + goto derived_exit; } having= 0; @@ -1502,6 +1537,9 @@ setup_subq_exit: if (optimize_unflattened_subqueries()) DBUG_RETURN(1); error= 0; + +derived_exit: + select_lex->mark_const_derived(zero_result_cause); DBUG_RETURN(0); } @@ -2064,6 +2102,11 @@ JOIN::exec() !tables ? "No tables used" : NullS); DBUG_VOID_RETURN; } + else + { + /* it's a const select, materialize it. */ + select_lex->mark_const_derived(zero_result_cause); + } if (!initialized && init_execution()) DBUG_VOID_RETURN; @@ -2848,12 +2891,11 @@ typedef struct st_sargable_param */ static bool -make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, COND *conds, - DYNAMIC_ARRAY *keyuse_array) +make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, + COND *conds, DYNAMIC_ARRAY *keyuse_array) { - int error; + int error= 0; TABLE *table; - TABLE_LIST *tables= tables_arg; uint i,table_count,const_count,key; table_map found_const_table_map, all_table_map, found_ref, refs; key_map const_ref, eq_part; @@ -2864,6 +2906,8 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, COND *conds, table_map no_rows_const_tables= 0; SARGABLE_PARAM *sargables= 0; JOIN_TAB *stat_vector[MAX_TABLES+1]; + List_iterator<TABLE_LIST> ti(tables_list); + TABLE_LIST *tables; DBUG_ENTER("make_join_statistics"); table_count=join->tables; @@ -2879,9 +2923,7 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, COND *conds, found_const_table_map= all_table_map=0; const_count=0; - for (s= stat, i= 0; - tables; - s++, tables= tables->next_leaf, i++) + for (s= stat, i= 0; (tables= ti++); s++, i++) { TABLE_LIST *embedding= tables->embedding; stat_vector[i]=s; @@ -2891,7 +2933,7 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, COND *conds, s->needed_reg.init(); table_vector[i]=s->table=table=tables->table; table->pos_in_table_list= tables; - error= table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); + error= tables->fetch_number_of_rows(); if (error) { table->file->print_error(error, MYF(0)); @@ -2963,6 +3005,7 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, COND *conds, no_rows_const_tables |= table->map; } } + stat_vector[i]=0; join->outer_join=outer_join; @@ -3234,7 +3277,7 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, COND *conds, } /* Approximate found rows and time to read them */ s->found_records=s->records=s->table->file->stats.records; - s->read_time=(ha_rows) s->table->file->scan_time(); + s->scan_time(); /* Set a max range of how many seeks we can expect when using keys @@ -3319,19 +3362,35 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, COND *conds, if (optimize_semijoin_nests(join, all_table_map)) DBUG_RETURN(TRUE); /* purecov: inspected */ - /* Find an optimal join order of the non-constant tables. */ - if (join->const_tables != join->tables) { - if (choose_plan(join, all_table_map & ~join->const_table_map)) - goto error; - } - else - { - memcpy((uchar*) join->best_positions,(uchar*) join->positions, - sizeof(POSITION)*join->const_tables); - join->record_count= 1.0; - join->best_read=1.0; + ha_rows records= 1; + SELECT_LEX_UNIT *unit= join->select_lex->master_unit(); + + /* Find an optimal join order of the non-constant tables. */ + if (join->const_tables != join->tables) + { + if (choose_plan(join, all_table_map & ~join->const_table_map)) + goto error; + /* + Calculate estimated number of rows for materialized derived + table/view. + */ + for (i= 0; i < join->tables ; i++) + records*= join->best_positions[i].records_read ? + (ha_rows)join->best_positions[i].records_read : 1; + } + else + { + memcpy((uchar*) join->best_positions,(uchar*) join->positions, + sizeof(POSITION)*join->const_tables); + join->record_count= 1.0; + join->best_read=1.0; + } + + if (unit->derived && unit->derived->is_materialized_derived()) + join->select_lex->increase_derived_records(records); } + if (join->choose_subquery_plan(all_table_map & ~join->const_table_map)) goto error; @@ -3345,8 +3404,12 @@ error: may not be assigned yet by this function (which is building join_tab). Dangling TABLE::reginfo.join_tab may cause part_of_refkey to choke. */ - for (tables= tables_arg; tables; tables= tables->next_leaf) - tables->table->reginfo.join_tab= NULL; + { + TABLE_LIST *table; + List_iterator<TABLE_LIST> ti(tables_list); + while ((table= ti++)) + table->table->reginfo.join_tab= NULL; + } DBUG_RETURN (1); } @@ -3611,8 +3674,11 @@ add_key_field(JOIN *join, table_map usable_tables, SARGABLE_PARAM **sargables) { uint optimize= 0; - if (eq_func && join->is_allowed_hash_join_access() && - field->hash_join_is_possible()) + if (eq_func && + ((join->is_allowed_hash_join_access() && + field->hash_join_is_possible()) || + (field->table->pos_in_table_list->is_materialized_derived() && + !field->table->created))) { optimize= KEY_OPTIMIZE_EQ; } @@ -3647,7 +3713,7 @@ add_key_field(JOIN *join, else { JOIN_TAB *stat=field->table->reginfo.join_tab; - key_map possible_keys=field->key_start; + key_map possible_keys=field->get_possible_keys(); possible_keys.intersect(field->table->keys_in_use_for_query); stat[0].keys.merge(possible_keys); // Add possible keys @@ -4098,10 +4164,6 @@ add_keyuse(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field, } else { - /* - If this is a key use for hash join then keypart of - the added element actually contains the field number. - */ keyuse.keypart= field->field_index; keyuse.keypart_map= (key_part_map) 0; } @@ -4242,6 +4304,9 @@ sort_keyuse(KEYUSE *a,KEYUSE *b) return (int) (a->table->tablenr - b->table->tablenr); if (a->key != b->key) return (int) (a->key - b->key); + if (a->key == MAX_KEY && b->key == MAX_KEY && + a->used_tables != b->used_tables) + return (int) ((ulong) a->used_tables - (ulong) b->used_tables); if (a->keypart != b->keypart) return (int) (a->keypart - b->keypart); // Place const values before other ones @@ -4391,19 +4456,21 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64)) return TRUE; + if (cond) { + KEY_FIELD *saved_field= field; add_key_fields(join_tab->join, &end, &and_level, cond, normal_tables, sargables); for (; field != end ; field++) { - if (add_key_part(keyuse,field)) - return TRUE; + /* Mark that we can optimize LEFT JOIN */ if (field->val->type() == Item::NULL_ITEM && !field->field->real_maybe_null()) field->field->table->reginfo.not_exists_optimize=1; } + field= saved_field; } for (i=0 ; i < tables ; i++) { @@ -4472,6 +4539,8 @@ static bool sort_and_filter_keyuse(DYNAMIC_ARRAY *keyuse) my_qsort(keyuse->buffer, keyuse->elements, sizeof(KEYUSE), (qsort_cmp) sort_keyuse); + generate_derived_keys(keyuse); + bzero((char*) &key_end, sizeof(key_end)); /* Add for easy testing */ if (insert_dynamic(keyuse, (uchar*) &key_end)) return TRUE; @@ -4547,7 +4616,7 @@ static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array) ~OUTER_REF_TABLE_BIT))) { uint tablenr; - for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ; + tablenr= my_count_bits(map); if (map == 1) // Only one table { TABLE *tmp_table=join->all_tables[tablenr]; @@ -5221,7 +5290,7 @@ best_access_path(JOIN *join, else { /* Estimate cost of reading table. */ - tmp= s->table->file->scan_time(); + tmp= s->scan_time(); if ((s->table->map & join->outer_join) || disable_jbuf) // Can't use join cache { /* @@ -5573,6 +5642,7 @@ optimize_straight_join(JOIN *join, table_map join_tables) { JOIN_TAB *s; uint idx= join->const_tables; + bool disable_jbuf= join->thd->variables.join_cache_level == 0; double record_count= 1.0; double read_time= 0.0; POSITION loose_scan_pos; @@ -5580,7 +5650,7 @@ optimize_straight_join(JOIN *join, table_map join_tables) for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++) { /* Find the best access method from 's' to the current partial plan */ - best_access_path(join, s, join_tables, idx, FALSE, record_count, + best_access_path(join, s, join_tables, idx, disable_jbuf, record_count, join->positions + idx, &loose_scan_pos); /* compute the cost of the new plan extended with 's' */ @@ -5969,6 +6039,7 @@ best_extension_by_limited_search(JOIN *join, JOIN_TAB *s; double best_record_count= DBL_MAX; double best_read_time= DBL_MAX; + bool disable_jbuf= join->thd->variables.join_cache_level == 0; DBUG_EXECUTE("opt", print_plan(join, idx, record_count, read_time, read_time, "part_plan");); @@ -5994,8 +6065,8 @@ best_extension_by_limited_search(JOIN *join, /* Find the best access method from 's' to the current partial plan */ POSITION loose_scan_pos; - best_access_path(join, s, remaining_tables, idx, FALSE, record_count, - join->positions + idx, &loose_scan_pos); + best_access_path(join, s, remaining_tables, idx, disable_jbuf, + record_count, join->positions + idx, &loose_scan_pos); /* Compute the cost of extending the plan with 's' */ @@ -6140,6 +6211,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, JOIN_TAB *s; double best_record_count=DBL_MAX,best_read_time=DBL_MAX; + bool disable_jbuf= join->thd->variables.join_cache_level == 0; for (JOIN_TAB **pos=join->best_ref+idx ; (s=*pos) ; pos++) { table_map real_table_bit=s->table->map; @@ -6148,7 +6220,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, { double records, best; POSITION loose_scan_pos; - best_access_path(join, s, rest_tables, idx, FALSE, record_count, + best_access_path(join, s, rest_tables, idx, disable_jbuf, record_count, join->positions + idx, &loose_scan_pos); records= join->positions[idx].records_read; best= join->positions[idx].read_time; @@ -6688,8 +6760,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, if (keyuse->null_rejecting) j->ref.null_rejecting |= 1 << i; keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; - if (!keyuse->used_tables && - !(join->select_options & SELECT_DESCRIBE)) + if (!keyuse->used_tables && !thd->lex->describe) { // Compare against constant store_key_item tmp(thd, keyinfo->key_part[i].field, @@ -6766,9 +6837,10 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables, } else if (keyuse->val->type() == Item::FIELD_ITEM || (keyuse->val->type() == Item::REF_ITEM && - ((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF && - (*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() == - Item_ref::DIRECT_REF && + ((((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF && + (*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() == + Item_ref::DIRECT_REF) || + ((Item_ref*)keyuse->val)->ref_type() == Item_ref::VIEW_REF) && keyuse->val->real_item()->type() == Item::FIELD_ITEM)) return new store_key_field(thd, key_part->field, @@ -7124,7 +7196,7 @@ make_outerjoin_info(JOIN *join) for ( ; embedding ; embedding= embedding->embedding) { /* Ignore sj-nests: */ - if (!embedding->on_expr) + if (!(embedding->on_expr && embedding->outer_join)) continue; NESTED_JOIN *nested_join= embedding->nested_join; if (!nested_join->counter) @@ -7654,6 +7726,133 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) } +static +uint get_next_field_for_derived_key(uchar *arg) +{ + KEYUSE *keyuse= *(KEYUSE **) arg; + if (!keyuse) + return (uint) (-1); + TABLE *table= keyuse->table; + uint key= keyuse->key; + uint fldno= keyuse->keypart; + uint keypart= keyuse->keypart_map == (key_part_map) 1 ? + 0 : (keyuse-1)->keypart+1; + for ( ; + keyuse->table == table && keyuse->key == key && keyuse->keypart == fldno; + keyuse++) + keyuse->keypart= keypart; + if (keyuse->key != key) + keyuse= 0; + return fldno; +} + + +static +bool generate_derived_keys_for_table(KEYUSE *keyuse, uint count, uint keys) +{ + TABLE *table= keyuse->table; + if (table->alloc_keys(keys)) + return TRUE; + uint keyno= 0; + KEYUSE *first_keyuse= keyuse; + uint prev_part= (uint) (-1); + uint parts= 0; + uint i= 0; + do + { + keyuse->key= keyno; + keyuse->keypart_map= (key_part_map) (1 << parts); + keyuse++; + if (++i == count || keyuse->used_tables != first_keyuse->used_tables) + { + if (table->add_tmp_key(keyno, ++parts, + get_next_field_for_derived_key, + (uchar *) &first_keyuse, + FALSE)) + return TRUE; + table->reginfo.join_tab->keys.set_bit(keyno); + first_keyuse= keyuse; + keyno++; + parts= 0; + } + else if (keyuse->keypart != prev_part) + { + parts++; + prev_part= keyuse->keypart; + } + } while (keyno < keys); + return FALSE; +} + + +static +bool generate_derived_keys(DYNAMIC_ARRAY *keyuse_array) +{ + KEYUSE *keyuse= dynamic_element(keyuse_array, 0, KEYUSE*); + uint elements= keyuse_array->elements; + TABLE *prev_table= 0; + for (uint i= 0; i < elements; i++, keyuse++) + { + KEYUSE *first_table_keyuse; + table_map last_used_tables; + uint count; + uint keys; + TABLE_LIST *derived= NULL; + if (keyuse->table != prev_table) + derived= keyuse->table->pos_in_table_list; + while (derived && derived->is_materialized_derived() && + keyuse->key == MAX_KEY) + { + if (keyuse->table != prev_table) + { + prev_table= keyuse->table; + first_table_keyuse= keyuse; + last_used_tables= keyuse->used_tables; + count= 0; + keys= 0; + } + else if (keyuse->used_tables != last_used_tables) + { + keys++; + last_used_tables= keyuse->used_tables; + } + count++; + keyuse++; + i++; + if (keyuse->table != prev_table && + generate_derived_keys_for_table(first_table_keyuse, count, ++keys)) + return TRUE; + } + } + return FALSE; +} + + +/* + @brief + Drops unused keys for each materialized derived table/view + + @details + For materialized derived tables only ref access can be used, it employs + only one index, thus we don't need the rest. For each materialized derived + table/view call TABLE::use_index to save one index chosen by the optimizer + and free others. No key is chosen then all keys will be dropped. +*/ + +void JOIN::drop_unused_derived_keys() +{ + for (uint i= const_tables ; i < tables ; i++) + { + JOIN_TAB *tab=join_tab+i; + TABLE *table=tab->table; + if (!table->pos_in_table_list->is_materialized_derived() || + table->max_keys <= 1) + continue; + table->use_index(tab->ref.key); + tab->ref.key= 0; + } +} + /* Determine {after which table we'll produce ordered set} @@ -8642,6 +8841,28 @@ void JOIN_TAB::cleanup() DBUG_VOID_RETURN; } +/** + Initialize the join_tab before reading. + Currently only derived table/view materialization is done here. +*/ +bool JOIN_TAB::preread_init() +{ + TABLE_LIST *derived= table->pos_in_table_list; + if (!derived || !derived->is_materialized_derived()) + { + preread_init_done= TRUE; + return FALSE; + } + + /* Materialize derived table/view. */ + if (!derived->get_unit()->executed && + mysql_handle_single_derived(join->thd->lex, + derived, DT_CREATE | DT_FILL)) + return TRUE; + preread_init_done= TRUE; + return FALSE; +} + /** Build a TABLE_REF structure for index lookup in the temporary table @@ -9145,7 +9366,7 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond, static int -return_zero_rows(JOIN *join, select_result *result,TABLE_LIST *tables, +return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables, List<Item> &fields, bool send_row, ulonglong select_options, const char *info, Item *having) { @@ -9161,7 +9382,9 @@ return_zero_rows(JOIN *join, select_result *result,TABLE_LIST *tables, if (send_row) { - for (TABLE_LIST *table= tables; table; table= table->next_leaf) + List_iterator<TABLE_LIST> ti(tables); + TABLE_LIST *table; + while ((table= ti++)) mark_as_null_row(table->table); // All fields are NULL if (having && having->val_int() == 0) send_row=0; @@ -10885,14 +11108,16 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top, { TABLE_LIST *tbl; List_iterator<TABLE_LIST> it(nested_join->join_list); + List<TABLE_LIST> repl_list; while ((tbl= it++)) { tbl->embedding= table->embedding; if (!tbl->embedding && !tbl->on_expr && tbl->table) tbl->table->maybe_null= FALSE; tbl->join_list= table->join_list; + repl_list.push_back(tbl); } - li.replace(nested_join->join_list); + li.replace(repl_list); /* Need to update the name resolution table chain when flattening joins */ fix_name_res= TRUE; table= *li.ref(); @@ -11862,13 +12087,29 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, If item have to be able to store NULLs but underlaid field can't do it, create_tmp_field_from_field() can't be used for tmp field creation. */ - if (field->maybe_null && field->in_rollup && !field->field->maybe_null()) + if (((field->maybe_null && field->in_rollup) || + (orig_item && orig_item->maybe_null)) && /* for outer joined views/dt*/ + !field->field->maybe_null()) { + bool save_maybe_null; + /* + The item the ref points to may have maybe_null flag set while + the ref doesn't have it. This may happen for outer fields + when the outer query decided at some point after name resolution phase + that this field might be null. Take this into account here. + */ + if (orig_item) + { + save_maybe_null= item->maybe_null; + item->maybe_null= orig_item->maybe_null; + } result= create_tmp_field_from_item(thd, item, table, NULL, modify_item, convert_blob_length); *from_field= field->field; if (result && modify_item) field->result_field= result; + if (orig_item) + item->maybe_null= save_maybe_null; } else if (table_cant_handle_bit_fields && field->field->type() == MYSQL_TYPE_BIT) @@ -12022,7 +12263,7 @@ TABLE * create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, ORDER *group, bool distinct, bool save_sum_fields, ulonglong select_options, ha_rows rows_limit, - char *table_alias) + char *table_alias, bool do_not_open) { MEM_ROOT *mem_root_save, own_root; TABLE *table; @@ -12595,7 +12836,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, share->uniques= test(using_unique_constraint); table->key_info= table->s->key_info= keyinfo; keyinfo->key_part=key_part_info; - keyinfo->flags=HA_NOSAME; + keyinfo->flags=HA_NOSAME | HA_BINARY_PACK_KEY | HA_PACK_KEY; keyinfo->usable_key_parts=keyinfo->key_parts= param->group_parts; keyinfo->key_length=0; keyinfo->rec_per_key=0; @@ -12680,7 +12921,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, bzero((void*) key_part_info, keyinfo->key_parts * sizeof(KEY_PART_INFO)); table->key_info= table->s->key_info= keyinfo; keyinfo->key_part=key_part_info; - keyinfo->flags=HA_NOSAME | HA_NULL_ARE_EQUAL; + keyinfo->flags=HA_NOSAME | HA_NULL_ARE_EQUAL | HA_BINARY_PACK_KEY | HA_PACK_KEY; keyinfo->key_length= 0; // Will compute the sum of the parts below. keyinfo->name= (char*) "distinct_key"; keyinfo->algorithm= HA_KEY_ALG_UNDEF; @@ -12748,15 +12989,14 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, if (thd->is_fatal_error) // If end of memory goto err; /* purecov: inspected */ share->db_record_offset= 1; - if (share->db_type() == TMP_ENGINE_HTON) - { - if (create_internal_tmp_table(table, param->keyinfo, param->start_recinfo, - ¶m->recinfo, select_options)) - goto err; - } - DBUG_PRINT("info", ("skip_create_table: %d", (int)param->skip_create_table)); - if (!param->skip_create_table) + if (!do_not_open) { + if (share->db_type() == TMP_ENGINE_HTON) + { + if (create_internal_tmp_table(table, param->keyinfo, param->start_recinfo, + ¶m->recinfo, select_options)) + goto err; + } if (open_tmp_table(table)) goto err; } @@ -12916,6 +13156,7 @@ bool open_tmp_table(TABLE *table) } table->db_stat= HA_OPEN_KEYFILE+HA_OPEN_RNDFILE; (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ + table->created= TRUE; return(0); } @@ -13224,6 +13465,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, } status_var_increment(table->in_use->status_var.created_tmp_disk_tables); share->db_record_offset= 1; + table->created= TRUE; DBUG_RETURN(0); err: DBUG_RETURN(1); @@ -13387,7 +13629,7 @@ free_tmp_table(THD *thd, TABLE *entry) save_proc_info=thd->proc_info; thd_proc_info(thd, "removing tmp table"); - if (entry->file) + if (entry->file && entry->created) { if (entry->db_stat) entry->file->ha_drop_table(entry->s->table_name.str); @@ -13570,8 +13812,9 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) With implicit grouping all fields of special row produced for an empty result are NULL. See return_zero_rows() for the same behavior. */ - for (TABLE_LIST *table= join->select_lex->leaf_tables; - table; table= table->next_leaf) + TABLE_LIST *table; + List_iterator_fast<TABLE_LIST> li(join->select_lex->leaf_tables); + while ((table= li++)) mark_as_null_row(table->table); rc= join->result->send_data(*columns_list); } @@ -14010,6 +14253,9 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) do_sj_reset(join_tab->flush_weedout_table); } + if (!join_tab->preread_init_done && join_tab->preread_init()) + DBUG_RETURN(NESTED_LOOP_ERROR); + if (join->resume_nested_loop) { /* If not the last table, plunge down the nested loop */ @@ -14384,13 +14630,21 @@ static int join_read_const_table(JOIN_TAB *tab, POSITION *pos) { int error; + TABLE_LIST *tbl; DBUG_ENTER("join_read_const_table"); TABLE *table=tab->table; table->const_table=1; table->null_row=0; table->status=STATUS_NO_RECORD; - if (tab->type == JT_SYSTEM) + if (tab->table->pos_in_table_list->is_materialized_derived() && + !tab->table->pos_in_table_list->fill_me) + { + //TODO: don't get here at all + /* Skip materialized derived tables/views. */ + DBUG_RETURN(0); + } + else if (tab->type == JT_SYSTEM) { if ((error=join_read_system(tab))) { // Info for DESCRIBE @@ -14454,26 +14708,27 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos) if (!table->null_row) table->maybe_null=0; - /* Check appearance of new constant items in Item_equal objects */ - JOIN *join= tab->join; - if (join->conds) - update_const_equal_items(join->conds, tab); - TABLE_LIST *tbl; - for (tbl= join->select_lex->leaf_tables; tbl; tbl= tbl->next_leaf) { - TABLE_LIST *embedded; - TABLE_LIST *embedding= tbl; - do - { - embedded= embedding; - if (embedded->on_expr) - update_const_equal_items(embedded->on_expr, tab); - embedding= embedded->embedding; + JOIN *join= tab->join; + List_iterator<TABLE_LIST> ti(join->select_lex->leaf_tables); + /* Check appearance of new constant items in Item_equal objects */ + if (join->conds) + update_const_equal_items(join->conds, tab); + while ((tbl= ti++)) + { + TABLE_LIST *embedded; + TABLE_LIST *embedding= tbl; + do + { + embedded= embedding; + if (embedded->on_expr) + update_const_equal_items(embedded->on_expr, tab); + embedding= embedded->embedding; + } + while (embedding && + embedding->nested_join->join_list.head() == embedded); } - while (embedding && - embedding->nested_join->join_list.head() == embedded); } - DBUG_RETURN(0); } @@ -14820,6 +15075,8 @@ int join_init_read_record(JOIN_TAB *tab) { if (tab->select && tab->select->quick && tab->select->quick->reset()) return 1; + if (!tab->preread_init_done && tab->preread_init()) + return 1; if (init_read_record(&tab->read_record, tab->join->thd, tab->table, tab->select,1,1, FALSE)) return 1; @@ -16868,6 +17125,8 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, get_schema_tables_result(join, PROCESSED_BY_CREATE_SORT_INDEX)) goto err; + if (!tab->preread_init_done && tab->preread_init()) + goto err; if (table->s->tmp_table) table->file->info(HA_STATUS_VARIABLE); // Get record count table->sort.found_records=filesort(thd, table,join->sortorder, length, @@ -17429,7 +17688,7 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, order->in_field_list= 1; order->counter= count; order->counter_used= 1; - return FALSE; + return FALSE; } /* Lookup the current GROUP/ORDER field in the SELECT clause. */ select_item= find_item_in_list(order_item, fields, &counter, @@ -17874,8 +18133,10 @@ test_if_subpart(ORDER *a,ORDER *b) */ static TABLE * -get_sort_by_table(ORDER *a,ORDER *b,TABLE_LIST *tables) +get_sort_by_table(ORDER *a,ORDER *b, List<TABLE_LIST> &tables) { + TABLE_LIST *table; + List_iterator<TABLE_LIST> ti(tables); table_map map= (table_map) 0; DBUG_ENTER("get_sort_by_table"); @@ -17893,11 +18154,11 @@ get_sort_by_table(ORDER *a,ORDER *b,TABLE_LIST *tables) if (!map || (map & (RAND_TABLE_BIT | OUTER_REF_TABLE_BIT))) DBUG_RETURN(0); - for (; !(map & tables->table->map); tables= tables->next_leaf) ; - if (map != tables->table->map) + while ((table= ti++) && !(map & table->table->map)); + if (map != table->table->map) DBUG_RETURN(0); // More than one table - DBUG_PRINT("exit",("sort by table: %d",tables->table->tablenr)); - DBUG_RETURN(tables->table); + DBUG_PRINT("exit",("sort by table: %d",table->table->tablenr)); + DBUG_RETURN(table->table); } @@ -19297,7 +19558,8 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, if (result->send_data(item_list)) join->error= 1; } - else + else if (!join->select_lex->master_unit()->derived || + join->select_lex->master_unit()->derived->is_materialized_derived()) { table_map used_tables=0; @@ -19643,6 +19905,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, if (examined_rows) f= (float) (100.0 * join->best_positions[i].records_read / examined_rows); + set_if_smaller(f, 100.0); item_list.push_back(new Item_float(f, 2)); } } diff --git a/sql/sql_select.h b/sql/sql_select.h index 1df07e002b5..98932ec237e 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -336,6 +336,8 @@ typedef struct st_join_table { struct st_join_table *first_sjm_sibling; + bool preread_init_done; + void cleanup(); inline bool is_using_loose_index_scan() { @@ -441,6 +443,22 @@ typedef struct st_join_table { { return (is_hash_join_key_no(key) ? hj_key : table->key_info+key); } + double scan_time() + { + double res; + if (table->created) + { + res= table->file->scan_time(); + read_time=(ha_rows) res; + } + else + { + read_time= found_records ? found_records: 10;// TODO:fix this stub + res= (double)read_time; + } + return res; + } + bool preread_init(); } JOIN_TAB; @@ -892,6 +910,7 @@ public: bool optimized; ///< flag to avoid double optimization in EXPLAIN bool initialized; ///< flag to avoid double init_execution calls + Array<Item_in_subselect> sj_subselects; /* Additional WHERE and HAVING predicates to be considered for IN=>EXISTS @@ -1057,6 +1076,7 @@ public: { return (table_map(1) << tables) - 1; } + void drop_unused_derived_keys(); /* Return the table for which an index scan can be used to satisfy the sort order needed by the ORDER BY/(implicit) GROUP BY clause @@ -1118,7 +1138,7 @@ Field* create_tmp_field_from_field(THD *thd, Field* org_field, /* functions from opt_sum.cc */ bool simple_pred(Item_func *func_item, Item **args, bool *inv_order); -int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds); +int opt_sum_query(List<TABLE_LIST> &tables, List<Item> &all_fields,COND *conds); /* from sql_delete.cc, used by opt_range.cc */ extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b); @@ -1380,7 +1400,7 @@ void push_index_cond(JOIN_TAB *tab, uint keyno); TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, ORDER *group, bool distinct, bool save_sum_fields, ulonglong select_options, ha_rows rows_limit, - char* alias); + char* alias, bool do_not_open=FALSE); void free_tmp_table(THD *thd, TABLE *entry); bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table, ENGINE_COLUMNDEF *start_recinfo, @@ -1393,5 +1413,6 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, bool open_tmp_table(TABLE *table); void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps); double prev_record_reads(POSITION *positions, uint idx, table_map found_ref); +void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist); #endif /* SQL_SELECT_INCLUDED */ diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 833b695e4bf..4d82b8bb5b4 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -755,7 +755,8 @@ mysqld_show_create(THD *thd, TABLE_LIST *table_list) { Show_create_error_handler view_error_suppressor(thd, table_list); thd->push_internal_handler(&view_error_suppressor); - bool error= open_normal_and_derived_tables(thd, table_list, 0); + bool error= open_normal_and_derived_tables(thd, table_list, 0, + DT_PREPARE | DT_CREATE); thd->pop_internal_handler(); if (error && (thd->killed || thd->main_da.is_error())) DBUG_RETURN(TRUE); @@ -930,7 +931,8 @@ mysqld_list_fields(THD *thd, TABLE_LIST *table_list, const char *wild) DBUG_ENTER("mysqld_list_fields"); DBUG_PRINT("enter",("table: %s",table_list->table_name)); - if (open_normal_and_derived_tables(thd, table_list, 0)) + if (open_normal_and_derived_tables(thd, table_list, 0, + DT_PREPARE | DT_CREATE)) DBUG_VOID_RETURN; table= table_list->table; @@ -1744,7 +1746,7 @@ view_store_options(THD *thd, TABLE_LIST *table, String *buff) static void append_algorithm(TABLE_LIST *table, String *buff) { buff->append(STRING_WITH_LEN("ALGORITHM=")); - switch ((int8)table->algorithm) { + switch ((int16)table->algorithm) { case VIEW_ALGORITHM_UNDEFINED: buff->append(STRING_WITH_LEN("UNDEFINED ")); break; @@ -3441,8 +3443,9 @@ fill_schema_show_cols_or_idxs(THD *thd, TABLE_LIST *tables, SQLCOM_SHOW_FIELDS is used because it satisfies 'only_view_structure()' */ lex->sql_command= SQLCOM_SHOW_FIELDS; - res= open_normal_and_derived_tables(thd, show_table_list, - MYSQL_LOCK_IGNORE_FLUSH); + res= (open_normal_and_derived_tables(thd, show_table_list, + MYSQL_LOCK_IGNORE_FLUSH, + DT_PREPARE | DT_CREATE)); lex->sql_command= save_sql_command; /* get_all_tables() returns 1 on failure and 0 on success thus @@ -3879,8 +3882,9 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) show_table_list->i_s_requested_object= schema_table->i_s_requested_object; DEBUG_SYNC(thd, "before_open_in_get_all_tables"); - res= open_normal_and_derived_tables(thd, show_table_list, - MYSQL_LOCK_IGNORE_FLUSH); + res= (open_normal_and_derived_tables(thd, show_table_list, + MYSQL_LOCK_IGNORE_FLUSH, + DT_PREPARE | DT_CREATE)); lex->sql_command= save_sql_command; /* XXX: show_table_list has a flag i_is_requested, diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 222991b1ed2..a074ccb00c8 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4668,8 +4668,13 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, thd->no_warnings_for_error= no_warnings_for_error; if (view_operator_func == NULL) table->required_type=FRMTYPE_TABLE; - + if (lex->sql_command == SQLCOM_CHECK || + lex->sql_command == SQLCOM_REPAIR || + lex->sql_command == SQLCOM_ANALYZE || + lex->sql_command == SQLCOM_OPTIMIZE) + thd->prepare_derived_at_open= TRUE; open_and_lock_tables(thd, table); + thd->prepare_derived_at_open= FALSE; thd->no_warnings_for_error= 0; table->next_global= save_next_global; table->next_local= save_next_local; @@ -4767,7 +4772,7 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, else /* Default failure code is corrupt table */ result_code= HA_ADMIN_CORRUPT; - goto send_result; + goto send_result; } if (table->view) diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 4a6c37f5f80..6ad5b814676 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -104,6 +104,7 @@ bool select_union::flush() options create options table_alias name of the temporary table bit_fields_as_long convert bit fields to ulonglong + create_table whether to physically create result table DESCRIPTION Create a temporary table that is used to store the result of a UNION, @@ -118,7 +119,7 @@ bool select_union::create_result_table(THD *thd_arg, List<Item> *column_types, bool is_union_distinct, ulonglong options, const char *alias, - bool bit_fields_as_long) + bool bit_fields_as_long, bool create_table) { DBUG_ASSERT(table == 0); tmp_table_param.init(); @@ -127,10 +128,14 @@ select_union::create_result_table(THD *thd_arg, List<Item> *column_types, if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types, (ORDER*) 0, is_union_distinct, 1, - options, HA_POS_ERROR, (char*) alias))) + options, HA_POS_ERROR, (char*) alias, + !create_table))) return TRUE; - table->file->extra(HA_EXTRA_WRITE_CACHE); - table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); + if (create_table) + { + table->file->extra(HA_EXTRA_WRITE_CACHE); + table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); + } return FALSE; } @@ -296,6 +301,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, (is_union_select ? NULL : thd_arg->lex->proc_list.first), sl, this); + /* There are no * in the statement anymore (for PS) */ sl->with_wild= 0; last_procedure= join->procedure; @@ -350,6 +356,8 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, List_iterator_fast<Item> tp(types); Item *type; ulonglong create_options; + uint save_tablenr; + table_map save_map; while ((type= tp++)) { @@ -402,12 +410,22 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, create_options= create_options | TMP_TABLE_FORCE_MYISAM; if (union_result->create_result_table(thd, &types, test(union_distinct), - create_options, "", FALSE)) + create_options, "", FALSE, TRUE)) goto err; + if (fake_select_lex && !fake_select_lex->first_cond_optimization) + { + save_tablenr= result_table_list.tablenr_exec; + save_map= result_table_list.map_exec; + } bzero((char*) &result_table_list, sizeof(result_table_list)); result_table_list.db= (char*) ""; result_table_list.table_name= result_table_list.alias= (char*) "union"; result_table_list.table= table= union_result->table; + if (fake_select_lex && !fake_select_lex->first_cond_optimization) + { + result_table_list.tablenr_exec= save_tablenr; + result_table_list.map_exec= save_map; + } thd_arg->lex->current_select= lex_select_save; if (!item_list.elements) @@ -471,18 +489,21 @@ err: } -bool st_select_lex_unit::exec() +/** + Run optimization phase. + + @return FALSE unit successfully passed optimization phase. + @return TRUE an error occur. +*/ +bool st_select_lex_unit::optimize() { SELECT_LEX *lex_select_save= thd->lex->current_select; SELECT_LEX *select_cursor=first_select(); - ulonglong add_rows=0; - ha_rows examined_rows= 0; - DBUG_ENTER("st_select_lex_unit::exec"); + DBUG_ENTER("st_select_lex_unit::optimize"); - if (executed && !uncacheable && !describe) + if (optimized && !uncacheable && !describe) DBUG_RETURN(FALSE); - executed= 1; - + if (uncacheable || !item || !item->assigned() || describe) { if (item) @@ -503,7 +524,6 @@ bool st_select_lex_unit::exec() } for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select()) { - ha_rows records_at_start= 0; thd->lex->current_select= sl; if (optimized) @@ -530,6 +550,66 @@ bool st_select_lex_unit::exec() sl->join->select_options= (select_limit_cnt == HA_POS_ERROR || sl->braces) ? sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union; + + saved_error= sl->join->optimize(); + } + + if (saved_error) + { + thd->lex->current_select= lex_select_save; + DBUG_RETURN(saved_error); + } + } + } + optimized= 1; + + thd->lex->current_select= lex_select_save; + DBUG_RETURN(saved_error); +} + + +bool st_select_lex_unit::exec() +{ + SELECT_LEX *lex_select_save= thd->lex->current_select; + SELECT_LEX *select_cursor=first_select(); + ulonglong add_rows=0; + ha_rows examined_rows= 0; + DBUG_ENTER("st_select_lex_unit::exec"); + + if (executed && !uncacheable && !describe) + DBUG_RETURN(FALSE); + executed= 1; + + saved_error= optimize(); + + if (uncacheable || !item || !item->assigned() || describe) + { + for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select()) + { + ha_rows records_at_start= 0; + thd->lex->current_select= sl; + + { + set_limit(sl); + if (sl == global_parameters || describe) + { + offset_limit_cnt= 0; + /* + We can't use LIMIT at this stage if we are using ORDER BY for the + whole query + */ + if (sl->order_list.first || describe) + select_limit_cnt= HA_POS_ERROR; + } + + /* + When using braces, SQL_CALC_FOUND_ROWS affects the whole query: + we don't calculate found_rows() per union part. + Otherwise, SQL_CALC_FOUND_ROWS should be done on all sub parts. + */ + sl->join->select_options= + (select_limit_cnt == HA_POS_ERROR || sl->braces) ? + sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union; saved_error= sl->join->optimize(); } if (!saved_error) @@ -582,7 +662,6 @@ bool st_select_lex_unit::exec() } } } - optimized= 1; /* Send result to 'result' */ saved_error= TRUE; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 7e40e3516f6..5d26de4fdd6 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -229,7 +229,11 @@ int mysql_update(THD *thd, if (open_tables(thd, &table_list, &table_count, 0)) DBUG_RETURN(1); - if (table_list->multitable_view) + //Prepare views so they are handled correctly. + if (mysql_handle_derived(thd->lex, DT_INIT)) + DBUG_RETURN(1); + + if (table_list->is_multitable()) { DBUG_ASSERT(table_list->view != 0); DBUG_PRINT("info", ("Switch to multi-update")); @@ -244,15 +248,19 @@ int mysql_update(THD *thd, DBUG_RETURN(1); close_tables_for_reopen(thd, &table_list); } - - if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) || - (thd->fill_derived_tables() && - mysql_handle_derived(thd->lex, &mysql_derived_filling))) + if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(1); + if (table_list->handle_derived(thd->lex, DT_PREPARE)) DBUG_RETURN(1); thd_proc_info(thd, "init"); table= table_list->table; + if (!table_list->updatable) + { + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE"); + DBUG_RETURN(1); + } /* Calculate "table->covering_keys" based on the WHERE */ table->covering_keys= table->s->keys_in_use; table->quick_keys.clear_all(); @@ -271,13 +279,17 @@ int mysql_update(THD *thd, table_list->grant.want_privilege= table->grant.want_privilege= want_privilege; table_list->register_want_access(want_privilege); #endif + /* 'Unfix' fields to allow correct marking by the setup_fields function. */ + if (table_list->is_view()) + unfix_fields(fields); + if (setup_fields_with_no_wrap(thd, 0, fields, MARK_COLUMNS_WRITE, 0, 0)) DBUG_RETURN(1); /* purecov: inspected */ if (table_list->view && check_fields(thd, fields)) { DBUG_RETURN(1); } - if (!table_list->updatable || check_key_in_view(thd, table_list)) + if (check_key_in_view(thd, table_list)) { my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE"); DBUG_RETURN(1); @@ -869,6 +881,11 @@ int mysql_update(THD *thd, } thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* calc cuted fields */ thd->abort_on_warning= 0; + if (thd->lex->current_select->first_cond_optimization) + { + thd->lex->current_select->save_leaf_tables(thd); + thd->lex->current_select->first_cond_optimization= 0; + } DBUG_RETURN((error >= 0 || thd->is_error()) ? 1 : 0); err: @@ -929,8 +946,8 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list, if (setup_tables_and_check_access(thd, &select_lex->context, &select_lex->top_join_list, table_list, - &select_lex->leaf_tables, - FALSE, UPDATE_ACL, SELECT_ACL) || + select_lex->leaf_tables, + FALSE, UPDATE_ACL, SELECT_ACL, TRUE) || setup_conds(thd, table_list, select_lex->leaf_tables, conds) || select_lex->setup_ref_array(thd, order_num) || setup_order(thd, select_lex->ref_pointer_array, @@ -967,8 +984,8 @@ static table_map get_table_map(List<Item> *items) Item_field *item; table_map map= 0; - while ((item= (Item_field *) item_it++)) - map|= item->used_tables(); + while ((item= (Item_field *) item_it++)) + map|= item->all_used_tables(); DBUG_PRINT("info", ("table_map: 0x%08lx", (long) map)); return map; } @@ -990,7 +1007,7 @@ int mysql_multi_update_prepare(THD *thd) { LEX *lex= thd->lex; TABLE_LIST *table_list= lex->query_tables; - TABLE_LIST *tl, *leaves; + TABLE_LIST *tl; List<Item> *fields= &lex->select_lex.item_list; table_map tables_for_update; bool update_view= 0; @@ -1013,19 +1030,24 @@ reopen_tables: /* open tables and create derived ones, but do not lock and fill them */ if (((original_multiupdate || need_reopen) && open_tables(thd, &table_list, &table_count, 0)) || - mysql_handle_derived(lex, &mysql_derived_prepare)) + mysql_handle_derived(lex, DT_INIT)) DBUG_RETURN(TRUE); /* setup_tables() need for VIEWs. JOIN::prepare() will call setup_tables() second time, but this call will do nothing (there are check for second call in setup_tables()). */ + //We need to merge for insert prior to prepare. + if (mysql_handle_list_of_derived(lex, table_list, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(1); + if (mysql_handle_list_of_derived(lex, table_list, DT_PREPARE)) + DBUG_RETURN(1); if (setup_tables_and_check_access(thd, &lex->select_lex.context, &lex->select_lex.top_join_list, table_list, - &lex->select_lex.leaf_tables, FALSE, - UPDATE_ACL, SELECT_ACL)) + lex->select_lex.leaf_tables, FALSE, + UPDATE_ACL, SELECT_ACL, TRUE)) DBUG_RETURN(TRUE); if (setup_fields_with_no_wrap(thd, 0, *fields, MARK_COLUMNS_WRITE, 0, 0)) @@ -1050,8 +1072,8 @@ reopen_tables: /* Setup timestamp handling and locking mode */ - leaves= lex->select_lex.leaf_tables; - for (tl= leaves; tl; tl= tl->next_leaf) + List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables); + while ((tl= ti++)) { TABLE *table= tl->table; /* Only set timestamp column if this is not modified */ @@ -1093,7 +1115,7 @@ reopen_tables: for (tl= table_list; tl; tl= tl->next_local) { /* Check access privileges for table */ - if (!tl->derived) + if (!tl->is_derived()) { uint want_privilege= tl->updating ? UPDATE_ACL : SELECT_ACL; if (check_access(thd, want_privilege, @@ -1107,7 +1129,7 @@ reopen_tables: /* check single table update for view compound from several tables */ for (tl= table_list; tl; tl= tl->next_local) { - if (tl->effective_algorithm == VIEW_ALGORITHM_MERGE) + if (tl->is_merged_derived()) { TABLE_LIST *for_update= 0; if (tl->check_single_table(&for_update, tables_for_update, tl)) @@ -1162,6 +1184,8 @@ reopen_tables: */ unit->unclean(); } + // Reset 'prepared' flags for all derived tables/views + mysql_handle_list_of_derived(thd->lex, table_list, DT_REINIT); /* Also we need to cleanup Natural_join_column::table_field items. @@ -1184,7 +1208,8 @@ reopen_tables: */ lex->select_lex.exclude_from_table_unique_test= TRUE; /* We only need SELECT privilege for columns in the values list */ - for (tl= leaves; tl; tl= tl->next_leaf) + ti.rewind(); + while ((tl= ti++)) { TABLE *table= tl->table; TABLE_LIST *tlist; @@ -1213,10 +1238,6 @@ reopen_tables: */ lex->select_lex.exclude_from_table_unique_test= FALSE; - if (thd->fill_derived_tables() && - mysql_handle_derived(lex, &mysql_derived_filling)) - DBUG_RETURN(TRUE); - DBUG_RETURN (FALSE); } @@ -1239,7 +1260,7 @@ bool mysql_multi_update(THD *thd, DBUG_ENTER("mysql_multi_update"); if (!(result= new multi_update(table_list, - thd->lex->select_lex.leaf_tables, + &thd->lex->select_lex.leaf_tables, fields, values, handle_duplicates, ignore))) DBUG_RETURN(TRUE); @@ -1274,7 +1295,7 @@ bool mysql_multi_update(THD *thd, multi_update::multi_update(TABLE_LIST *table_list, - TABLE_LIST *leaves_list, + List<TABLE_LIST> *leaves_list, List<Item> *field_list, List<Item> *value_list, enum enum_duplicates handle_duplicates_arg, bool ignore_arg) @@ -1292,6 +1313,7 @@ multi_update::multi_update(TABLE_LIST *table_list, int multi_update::prepare(List<Item> ¬_used_values, SELECT_LEX_UNIT *lex_unit) + { TABLE_LIST *table_ref; SQL_I_List<TABLE_LIST> update; @@ -1301,12 +1323,20 @@ int multi_update::prepare(List<Item> ¬_used_values, List_iterator_fast<Item> value_it(*values); uint i, max_fields; uint leaf_table_count= 0; + List_iterator<TABLE_LIST> ti(*leaves); DBUG_ENTER("multi_update::prepare"); thd->count_cuted_fields= CHECK_FIELD_WARN; thd->cuted_fields=0L; thd_proc_info(thd, "updating main table"); + SELECT_LEX *select_lex= lex_unit->first_select(); + if (select_lex->first_cond_optimization) + { + if (select_lex->handle_derived(thd->lex, DT_MERGE)) + DBUG_RETURN(TRUE); + } + tables_to_update= get_table_map(fields); if (!tables_to_update) @@ -1320,7 +1350,7 @@ int multi_update::prepare(List<Item> ¬_used_values, TABLE::tmp_set by pointing TABLE::read_set to it and then restore it after setup_fields(). */ - for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf) + while ((table_ref= ti++)) { TABLE *table= table_ref->table; if (tables_to_update & table->map) @@ -1338,7 +1368,8 @@ int multi_update::prepare(List<Item> ¬_used_values, int error= setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0); - for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf) + ti.rewind(); + while ((table_ref= ti++)) { TABLE *table= table_ref->table; if (tables_to_update & table->map) @@ -1367,7 +1398,8 @@ int multi_update::prepare(List<Item> ¬_used_values, */ update.empty(); - for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf) + ti.rewind(); + while ((table_ref= ti++)) { /* TODO: add support of view of join support */ TABLE *table=table_ref->table; @@ -1593,9 +1625,9 @@ loop_end: { table_map unupdated_tables= table_ref->check_option->used_tables() & ~first_table_for_update->map; - for (TABLE_LIST *tbl_ref =leaves; - unupdated_tables && tbl_ref; - tbl_ref= tbl_ref->next_leaf) + List_iterator<TABLE_LIST> ti(*leaves); + TABLE_LIST *tbl_ref; + while ((tbl_ref= ti++) && unupdated_tables) { if (unupdated_tables & tbl_ref->table->map) unupdated_tables&= ~tbl_ref->table->map; diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 0f7b7637744..89ccfd10a45 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -248,7 +248,7 @@ fill_defined_view_parts (THD *thd, TABLE_LIST *view) view->definer.user= decoy.definer.user; lex->definer= &view->definer; } - if (lex->create_view_algorithm == VIEW_ALGORITHM_UNDEFINED) + if (lex->create_view_algorithm == DTYPE_ALGORITHM_UNDEFINED) lex->create_view_algorithm= (uint8) decoy.algorithm; if (lex->create_view_suid == VIEW_SUID_DEFAULT) lex->create_view_suid= decoy.view_suid ? @@ -843,7 +843,7 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, ulong sql_mode= thd->variables.sql_mode & MODE_ANSI_QUOTES; thd->variables.sql_mode&= ~MODE_ANSI_QUOTES; - lex->unit.print(&view_query, QT_ORDINARY); + lex->unit.print(&view_query, QT_VIEW_INTERNAL); lex->unit.print(&is_query, QT_IS); thd->variables.sql_mode|= sql_mode; @@ -876,7 +876,7 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, { push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_VIEW_MERGE, ER(ER_WARN_VIEW_MERGE)); - lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; + lex->create_view_algorithm= DTYPE_ALGORITHM_UNDEFINED; } view->algorithm= lex->create_view_algorithm; view->definer.user= lex->definer->user; @@ -1460,7 +1460,7 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table, List_iterator_fast<TABLE_LIST> ti(view_select->top_join_list); - table->effective_algorithm= VIEW_ALGORITHM_MERGE; + table->derived_type= VIEW_ALGORITHM_MERGE; DBUG_PRINT("info", ("algorithm: MERGE")); table->updatable= (table->updatable_view != 0); table->effective_with_check= @@ -1474,67 +1474,10 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table, /* prepare view context */ lex->select_lex.context.resolve_in_table_list_only(view_main_select_tables); lex->select_lex.context.outer_context= 0; - lex->select_lex.context.select_lex= table->select_lex; lex->select_lex.select_n_having_items+= table->select_lex->select_n_having_items; - /* - Tables of the main select of the view should be marked as belonging - to the same select as original view (again we can use LEX::select_lex - for this purprose because we don't support MERGE algorithm for views - with unions). - */ - for (tbl= lex->select_lex.get_table_list(); tbl; tbl= tbl->next_local) - tbl->select_lex= table->select_lex; - - { - if (view_main_select_tables->next_local) - { - table->multitable_view= TRUE; - if (table->belong_to_view) - table->belong_to_view->multitable_view= TRUE; - } - /* make nested join structure for view tables */ - NESTED_JOIN *nested_join; - if (!(nested_join= table->nested_join= - (NESTED_JOIN *) thd->calloc(sizeof(NESTED_JOIN)))) - goto err; - nested_join->join_list= view_select->top_join_list; - - /* re-nest tables of VIEW */ - ti.rewind(); - while ((tbl= ti++)) - { - tbl->join_list= &nested_join->join_list; - tbl->embedding= table; - } - } - - /* Store WHERE clause for post-processing in setup_underlying */ table->where= view_select->where; - /* - Add subqueries units to SELECT into which we merging current view. - unit(->next)* chain starts with subqueries that are used by this - view and continues with subqueries that are used by other views. - We must not add any subquery twice (otherwise we'll form a loop), - to do this we remember in end_unit the first subquery that has - been already added. - - NOTE: we do not support UNION here, so we take only one select - */ - SELECT_LEX_NODE *end_unit= table->select_lex->slave; - SELECT_LEX_UNIT *next_unit; - for (SELECT_LEX_UNIT *unit= lex->select_lex.first_inner_unit(); - unit; - unit= next_unit) - { - if (unit == end_unit) - break; - SELECT_LEX_NODE *save_slave= unit->slave; - next_unit= unit->next_unit(); - unit->include_down(table->select_lex); - unit->slave= save_slave; // fix include_down initialisation - } /* We can safely ignore the VIEW's ORDER BY if we merge into union @@ -1551,23 +1494,22 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table, goto ok; } - table->effective_algorithm= VIEW_ALGORITHM_TMPTABLE; + table->derived_type= VIEW_ALGORITHM_TMPTABLE; DBUG_PRINT("info", ("algorithm: TEMPORARY TABLE")); view_select->linkage= DERIVED_TABLE_TYPE; table->updatable= 0; table->effective_with_check= VIEW_CHECK_NONE; old_lex->subqueries= TRUE; - /* SELECT tree link */ - lex->unit.include_down(table->select_lex); - lex->unit.slave= view_select; // fix include_down initialisation - table->derived= &lex->unit; } else goto err; ok: + /* SELECT tree link */ + lex->unit.include_down(table->select_lex); + lex->unit.slave= view_select; // fix include_down initialisation /* global SELECT list linking */ end= view_select; // primary SELECT_LEX is always last end->link_next= old_lex->all_selects_list; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index fbced869951..4faf8a6b5fb 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1931,7 +1931,7 @@ create: | CREATE { Lex->create_view_mode= VIEW_CREATE_NEW; - Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; + Lex->create_view_algorithm= DTYPE_ALGORITHM_UNDEFINED; Lex->create_view_suid= TRUE; } view_or_trigger_or_sp_or_event @@ -5979,7 +5979,7 @@ alter: my_error(ER_SP_BADSTATEMENT, MYF(0), "ALTER VIEW"); MYSQL_YYABORT; } - lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; + lex->create_view_algorithm= DTYPE_ALGORITHM_UNDEFINED; lex->create_view_mode= VIEW_ALTER; } view_tail @@ -13553,7 +13553,7 @@ view_replace: view_algorithm: ALGORITHM_SYM EQ UNDEFINED_SYM - { Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } + { Lex->create_view_algorithm= DTYPE_ALGORITHM_UNDEFINED; } | ALGORITHM_SYM EQ MERGE_SYM { Lex->create_view_algorithm= VIEW_ALGORITHM_MERGE; } | ALGORITHM_SYM EQ TEMPTABLE_SYM diff --git a/sql/table.cc b/sql/table.cc index 41fb9a1c8b4..b2fca4b3672 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -3508,129 +3508,112 @@ void TABLE_LIST::calc_md5(char *buffer) /** - @brief Set underlying table for table place holder of view. - - @details - - Replace all views that only use one table with the table itself. This - allows us to treat the view as a simple table and even update it (it is a - kind of optimization). + @brief + Create field translation for mergeable derived table/view. - @note + @param thd Thread handle - This optimization is potentially dangerous as it makes views - masquerade as base tables: Views don't have the pointer TABLE_LIST::table - set to non-@c NULL. + @details + Create field translation for mergeable derived table/view. - We may have the case where a view accesses tables not normally accessible - in the current Security_context (only in the definer's - Security_context). According to the table's GRANT_INFO (TABLE::grant), - access is fulfilled, but this is implicitly meant in the definer's security - context. Hence we must never look at only a TABLE's GRANT_INFO without - looking at the one of the referring TABLE_LIST. + @return FALSE ok. + @return TRUE an error occur. */ -void TABLE_LIST::set_underlying_merge() +bool TABLE_LIST::create_field_translation(THD *thd) { - TABLE_LIST *tbl; + Item *item; + Field_translator *transl; + SELECT_LEX *select= get_single_select(); + List_iterator_fast<Item> it(select->item_list); + uint field_count= 0; + Query_arena *arena= thd->stmt_arena, backup; + bool res= FALSE; - if ((tbl= merge_underlying_list)) + used_items.empty(); + + if (field_translation) { - /* This is a view. Process all tables of view */ - DBUG_ASSERT(view && effective_algorithm == VIEW_ALGORITHM_MERGE); - do + /* + Update items in the field translation aftet view have been prepared. + It's needed because some items in the select list, like IN subselects, + might be substituted for optimized ones. + */ + if (is_view() && get_unit()->prepared && !field_translation_updated) { - if (tbl->merge_underlying_list) // This is a view + while ((item= it++)) { - DBUG_ASSERT(tbl->view && - tbl->effective_algorithm == VIEW_ALGORITHM_MERGE); - /* - This is the only case where set_ancestor is called on an object - that may not be a view (in which case ancestor is 0) - */ - tbl->merge_underlying_list->set_underlying_merge(); + field_translation[field_count++].item= item; } - } while ((tbl= tbl->next_local)); - - if (!multitable_view) - { - table= merge_underlying_list->table; - schema_table= merge_underlying_list->schema_table; + field_translation_updated= TRUE; } + + return FALSE; + } + + if (arena->is_conventional()) + arena= 0; // For easier test + else + thd->set_n_backup_active_arena(arena, &backup); + + /* Create view fields translation table */ + + if (!(transl= + (Field_translator*)(thd->stmt_arena-> + alloc(select->item_list.elements * + sizeof(Field_translator))))) + { + res= TRUE; + goto exit; } + + while ((item= it++)) + { + transl[field_count].name= item->name; + transl[field_count++].item= item; + } + field_translation= transl; + field_translation_end= transl + field_count; + +exit: + if (arena) + thd->restore_active_arena(arena, &backup); + + return res; } -/* - setup fields of placeholder of merged VIEW +/** + @brief + Create field translation for mergeable derived table/view. - SYNOPSIS - TABLE_LIST::setup_underlying() - thd - thread handler + @param thd Thread handle - DESCRIPTION - It is: - - preparing translation table for view columns - If there are underlying view(s) procedure first will be called for them. + @details + Create field translation for mergeable derived table/view. - RETURN - FALSE - OK - TRUE - error + @return FALSE ok. + @return TRUE an error occur. */ bool TABLE_LIST::setup_underlying(THD *thd) { DBUG_ENTER("TABLE_LIST::setup_underlying"); - if (!field_translation && merge_underlying_list) + if (!view || (!field_translation && merge_underlying_list)) { - Field_translator *transl; - SELECT_LEX *select= &view->select_lex; - Item *item; - TABLE_LIST *tbl; - List_iterator_fast<Item> it(select->item_list); - uint field_count= 0; - - if (check_stack_overrun(thd, STACK_MIN_SIZE, (uchar*) &field_count)) - { - DBUG_RETURN(TRUE); - } - - for (tbl= merge_underlying_list; tbl; tbl= tbl->next_local) - { - if (tbl->merge_underlying_list && - tbl->setup_underlying(thd)) - { - DBUG_RETURN(TRUE); - } - } - - /* Create view fields translation table */ - - if (!(transl= - (Field_translator*)(thd->stmt_arena-> - alloc(select->item_list.elements * - sizeof(Field_translator))))) - { + SELECT_LEX *select= get_single_select(); + + if (create_field_translation(thd)) DBUG_RETURN(TRUE); - } - - while ((item= it++)) - { - transl[field_count].name= item->name; - transl[field_count++].item= item; - } - field_translation= transl; - field_translation_end= transl + field_count; - /* TODO: use hash for big number of fields */ /* full text function moving to current select */ - if (view->select_lex.ftfunc_list->elements) + if (select->ftfunc_list->elements) { Item_func_match *ifm; SELECT_LEX *current_select= thd->lex->current_select; List_iterator_fast<Item_func_match> - li(*(view->select_lex.ftfunc_list)); + li(*(select_lex->ftfunc_list)); while ((ifm= li++)) current_select->ftfunc_list->push_front(ifm); } @@ -3640,7 +3623,7 @@ bool TABLE_LIST::setup_underlying(THD *thd) /* - Prepare where expression of view + Prepare where expression of derived table/view SYNOPSIS TABLE_LIST::prep_where() @@ -3664,7 +3647,8 @@ bool TABLE_LIST::prep_where(THD *thd, Item **conds, for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { - if (tbl->view && tbl->prep_where(thd, conds, no_where_clause)) + if (tbl->is_view_or_derived() && + tbl->prep_where(thd, conds, no_where_clause)) { DBUG_RETURN(TRUE); } @@ -3672,6 +3656,8 @@ bool TABLE_LIST::prep_where(THD *thd, Item **conds, if (where) { + if (where->fixed) + where->update_used_tables(); if (!where->fixed && where->fix_fields(thd, &where)) { DBUG_RETURN(TRUE); @@ -3704,7 +3690,13 @@ bool TABLE_LIST::prep_where(THD *thd, Item **conds, } } if (tbl == 0) + { + if (*conds && !(*conds)->fixed) + (*conds)->fix_fields(thd, conds); *conds= and_conds(*conds, where->copy_andor_structure(thd)); + if (*conds && !(*conds)->fixed) + (*conds)->fix_fields(thd, conds); + } if (arena) thd->restore_active_arena(arena, &backup); where_processed= TRUE; @@ -3743,10 +3735,11 @@ merge_on_conds(THD *thd, TABLE_LIST *table, bool is_cascaded) DBUG_PRINT("info", ("alias: %s", table->alias)); if (table->on_expr) cond= table->on_expr->copy_andor_structure(thd); - if (!table->nested_join) + if (!table->view) DBUG_RETURN(cond); - List_iterator<TABLE_LIST> li(table->nested_join->join_list); - while (TABLE_LIST *tbl= li++) + for (TABLE_LIST *tbl= (TABLE_LIST*)table->view->select_lex.table_list.first; + tbl; + tbl= tbl->next_local) { if (tbl->view && !is_cascaded) continue; @@ -3786,7 +3779,7 @@ bool TABLE_LIST::prep_check_option(THD *thd, uint8 check_opt_type) { DBUG_ENTER("TABLE_LIST::prep_check_option"); bool is_cascaded= check_opt_type == VIEW_CHECK_CASCADED; - + TABLE_LIST *merge_underlying_list= view->select_lex.get_table_list(); for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { /* see comment of check_opt_type parameter */ @@ -3899,10 +3892,14 @@ void TABLE_LIST::hide_view_error(THD *thd) TABLE_LIST *TABLE_LIST::find_underlying_table(TABLE *table_to_find) { /* is this real table and table which we are looking for? */ - if (table == table_to_find && merge_underlying_list == 0) + if (table == table_to_find && view == 0) return this; + if (!view) + return 0; - for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) + for (TABLE_LIST *tbl= view->select_lex.get_table_list(); + tbl; + tbl= tbl->next_local) { TABLE_LIST *result; if ((result= tbl->find_underlying_table(table_to_find))) @@ -3984,7 +3981,12 @@ bool TABLE_LIST::check_single_table(TABLE_LIST **table_arg, table_map map, TABLE_LIST *view_arg) { - for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) + if (!select_lex) + return FALSE; + DBUG_ASSERT(is_merged_derived()); + for (TABLE_LIST *tbl= get_single_select()->get_table_list(); + tbl; + tbl= tbl->next_local) { if (tbl->table) { @@ -4026,8 +4028,10 @@ bool TABLE_LIST::set_insert_values(MEM_ROOT *mem_root) } else { - DBUG_ASSERT(view && merge_underlying_list); - for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) + DBUG_ASSERT(is_view_or_derived() && is_merged_derived()); + for (TABLE_LIST *tbl= (TABLE_LIST*)view->select_lex.table_list.first; + tbl; + tbl= tbl->next_local) if (tbl->set_insert_values(mem_root)) return TRUE; } @@ -4053,7 +4057,7 @@ bool TABLE_LIST::set_insert_values(MEM_ROOT *mem_root) */ bool TABLE_LIST::is_leaf_for_name_resolution() { - return (view || is_natural_join || is_join_columns_complete || + return (is_merged_derived() || is_natural_join || is_join_columns_complete || !nested_join); } @@ -4191,7 +4195,11 @@ void TABLE_LIST::register_want_access(ulong want_access) if (table) table->grant.want_privilege= want_access; } - for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) + if (!view) + return; + for (TABLE_LIST *tbl= view->select_lex.get_table_list(); + tbl; + tbl= tbl->next_local) tbl->register_want_access(want_access); } @@ -4421,14 +4429,23 @@ const char *Natural_join_column::db_name() DBUG_ASSERT(!strcmp(table_ref->db, table_ref->table->s->db.str) || (table_ref->schema_table && - table_ref->table->s->db.str[0] == 0)); + table_ref->table->s->db.str[0] == 0) || + table_ref->is_materialized_derived()); return table_ref->db; } GRANT_INFO *Natural_join_column::grant() { - if (view_field) +/* if (view_field) + return &(table_ref->grant); + return &(table_ref->table->grant);*/ + /* + Have to check algorithm because merged derived also has + field_translation. + */ +//if (table_ref->effective_algorithm == DTYPE_ALGORITHM_MERGE) + if (table_ref->is_merged_derived()) return &(table_ref->grant); return &(table_ref->table->grant); } @@ -4509,7 +4526,17 @@ Item *create_view_field(THD *thd, TABLE_LIST *view, Item **field_ref, { DBUG_RETURN(field); } - Item *item= new Item_direct_view_ref(view, field_ref, name); + Item *item= new Item_direct_view_ref(&view->view->select_lex.context, + field_ref, view->alias, + name, view); + /* + Force creation of nullable item for the result tmp table for outer joined + views/derived tables. + */ + if (view->outer_join) + item->maybe_null= TRUE; + /* Save item in case we will need to fall back to materialization. */ + view->used_items.push_back(item); DBUG_RETURN(item); } @@ -4563,8 +4590,7 @@ void Field_iterator_table_ref::set_field_iterator() /* This is a merge view, so use field_translation. */ else if (table_ref->field_translation) { - DBUG_ASSERT(table_ref->view && - table_ref->effective_algorithm == VIEW_ALGORITHM_MERGE); + DBUG_ASSERT(table_ref->is_merged_derived()); field_it= &view_field_it; DBUG_PRINT("info", ("field_it for '%s' is Field_iterator_view", table_ref->alias)); @@ -5188,15 +5214,16 @@ void st_table::mark_virtual_columns_for_write(bool insert_fl) /** + @brief Allocate space for keys - @param key_count number of keys to allocate. + @param key_count number of keys to allocate @details - Allocates space enough to fit 'key_count' keys for this table. + The function allocates memory to fit 'key_count' keys for this table. - @return FALSE space was successfully allocated. - @return TRUE an error occur. + @return FALSE space was successfully allocated + @return TRUE an error occur */ bool TABLE::alloc_keys(uint key_count) @@ -5255,22 +5282,24 @@ void TABLE::create_key_part_by_field(KEY *keyinfo, /** - Add a key to a temporary table + @brief + Add one key to a temporary table @param key the number of the key @param key_parts number of components of the key @param next_field_no the call-back function that returns the number of the field used as the next component of the key @param arg the argument for the above function - @param unique Is it unique index + @param unique TRUE <=> it is a unique index @details - The function adds a new key to the table that is assumed to be - temprary table. The call-back function must at each call must return - the number of the field that used as next component of this key + The function adds a new key to the table that is assumed to be a temporary + table. At each its invocation the call-back function must return + the number of the field that is used as the next component of this key. @return FALSE is a success @return TRUE if a failure + */ bool TABLE::add_tmp_key(uint key, uint key_parts, @@ -5321,6 +5350,31 @@ bool TABLE::add_tmp_key(uint key, uint key_parts, return FALSE; } +/* + @brief + Drop all indexes except specified one. + + @param key_to_save the key to save + + @details + Drop all indexes on this table except 'key_to_save'. The saved key becomes + key #0. Memory occupied by key parts of dropped keys are freed. + If the 'key_to_save' is negative then all keys are freed. +*/ + +void TABLE::use_index(int key_to_save) +{ + uint i= 1; + DBUG_ASSERT(!created && key_to_save < (int)s->keys); + if (key_to_save >= 0) + /* Save the given key. */ + memcpy(key_info, key_info + key_to_save, sizeof(KEY)); + else + /* Drop all keys; */ + i= 0; + + s->keys= (key_to_save < 0) ? 0 : 1; +} /** @brief Check if this is part of a MERGE table with attached children. @@ -5370,6 +5424,7 @@ void TABLE_LIST::reinit_before_use(THD *thd) parent_embedding->nested_join->join_list.head() == embedded); } + /* Return subselect that contains the FROM list this table is taken from @@ -5641,6 +5696,296 @@ int update_virtual_fields(THD *thd, TABLE *table, bool for_write) DBUG_RETURN(0); } +/* + @brief Reset const_table flag + + @detail + Reset const_table flag for this table. If this table is a merged derived + table/view the flag is recursively reseted for all tables of the underlying + select. +*/ + +void TABLE_LIST::reset_const_table() +{ + table->const_table= 0; + if (is_merged_derived()) + { + SELECT_LEX *select_lex= get_unit()->first_select(); + TABLE_LIST *tl; + List_iterator<TABLE_LIST> ti(select_lex->leaf_tables); + while ((tl= ti++)) + tl->reset_const_table(); + } +} + + +/* + @brief Run derived tables/view handling phases on underlying select_lex. + + @param lex LEX for this thread + @param phases derived tables/views handling phases to run + (set of DT_XXX constants) + @details + This function runs this derived table through specified 'phases'. + Underlying tables of this select are handled prior to this derived. + 'lex' is passed as an argument to called functions. + + @return TRUE on error + @return FALSE ok +*/ + +bool TABLE_LIST::handle_derived(struct st_lex *lex, uint phases) +{ + SELECT_LEX_UNIT *unit= get_unit(); + if (unit) + { + for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select()) + if (sl->handle_derived(lex, phases)) + return TRUE; + return mysql_handle_single_derived(lex, this, phases); + } + return FALSE; +} + + +/** + @brief + Return unit of this derived table/view + + @return reference to a unit if it's a derived table/view. + @return 0 when it's not a derived table/view. +*/ + +st_select_lex_unit *TABLE_LIST::get_unit() +{ + return (view ? &view->unit : derived); +} + + +/** + @brief + Return select_lex of this derived table/view + + @return select_lex of this derived table/view. + @return 0 when it's not a derived table. +*/ + +st_select_lex *TABLE_LIST::get_single_select() +{ + SELECT_LEX_UNIT *unit= get_unit(); + return (unit ? unit->first_select() : 0); +} + + +/** + @brief + Attach a join table list as a nested join to this TABLE_LIST. + + @param join_list join table list to attach + + @details + This function wraps 'join_list' into a nested_join of this table, thus + turning it to a nested join leaf. +*/ + +void TABLE_LIST::wrap_into_nested_join(List<TABLE_LIST> &join_list) +{ + TABLE_LIST *tl; + /* + Walk through derived table top list and set 'embedding' to point to + the nesting table. + */ + nested_join->join_list.empty(); + List_iterator_fast<TABLE_LIST> li(join_list); + nested_join->join_list= join_list; + while ((tl= li++)) + { + tl->embedding= this; + tl->join_list= &nested_join->join_list; + } +} + + +/** + @brief + Initialize this derived table/view + + @param thd Thread handle + + @details + This function makes initial preparations of this derived table/view for + further processing: + if it's a derived table this function marks it either as mergeable or + materializable + creates temporary table for name resolution purposes + creates field translation for mergeable derived table/view + + @return TRUE an error occur + @return FALSE ok +*/ + +bool TABLE_LIST::init_derived(THD *thd, bool init_view) +{ + SELECT_LEX *first_select= get_single_select(); + SELECT_LEX_UNIT *unit= get_unit(); + + if (!unit) + return FALSE; + /* + Check whether we can merge this derived table into main select. + Depending on the result field translation will or will not + be created. + */ + TABLE_LIST *first_table= (TABLE_LIST *) first_select->table_list.first; + if (first_select->table_list.elements > 1 || + (first_table && first_table->is_multitable())) + set_multitable(); + + unit->derived= this; + if (init_view && !view) + { + /* This is all what we can do for a derived table for now. */ + set_derived(); + } + + if (!is_view()) + { + /* A subquery might be forced to be materialized due to a side-effect. */ + if (!is_materialized_derived() && first_select->is_mergeable()) + set_merged_derived(); + else + set_materialized_derived(); + } + /* + Derived tables/view are materialized prior to UPDATE, thus we can skip + them from table uniqueness check + */ + if (is_materialized_derived()) + { + SELECT_LEX *sl; + for (sl= first_select ;sl ; sl= sl->next_select()) + sl->exclude_from_table_unique_test= TRUE; + } + /* + Create field translation for mergeable derived tables/views. + For derived tables field translation can be created only after + unit is prepared so all '*' are get unrolled. + */ + if (is_merged_derived()) + { + if (is_view() || unit->prepared) + create_field_translation(thd); + } + + return FALSE; +} + + +/** + @brief + Retrieve number of rows in the table + + @details + Retrieve number of rows in the table referred by this TABLE_LIST and + store it in the table's stats.records variable. If this TABLE_LIST refers + to a materialized derived table/view then the estimated number of rows of + the derived table/view is used instead. + + @return 0 ok + @return non zero error +*/ + +int TABLE_LIST::fetch_number_of_rows() +{ + int error= 0; + if (is_materialized_derived() && !fill_me) + + { + table->file->stats.records= ((select_union*)derived->result)->records; + set_if_bigger(table->file->stats.records, 2); + } + else + error= table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); + return error; +} + +/* + Procedure of keys generation for result tables of materialized derived + tables/views. + + A key is generated for each equi-join pair derived table-another table. + Each generated key consists of fields of derived table used in equi-join. + Example: + + SELECT * FROM (SELECT * FROM t1 GROUP BY 1) tt JOIN + t1 ON tt.f1=t1.f3 and tt.f2.=t1.f4; + In this case for the derived table tt one key will be generated. It will + consist of two parts f1 and f2. + Example: + + SELECT * FROM (SELECT * FROM t1 GROUP BY 1) tt JOIN + t1 ON tt.f1=t1.f3 JOIN + t2 ON tt.f2=t2.f4; + In this case for the derived table tt two keys will be generated. + One key over f1 field, and another key over f2 field. + Currently optimizer may choose to use only one such key, thus the second + one will be dropped after range optimizer is finished. + See also JOIN::drop_unused_derived_keys function. + Example: + + SELECT * FROM (SELECT * FROM t1 GROUP BY 1) tt JOIN + t1 ON tt.f1=a_function(t1.f3); + In this case for the derived table tt one key will be generated. It will + consist of one field - f1. +*/ + + + +/* + @brief + Change references to underlying items of a merged derived table/view + for fields in derived table's result table. + + @return FALSE ok + @return TRUE Out of memory +*/ +bool TABLE_LIST::change_refs_to_fields() +{ + List_iterator<Item> li(used_items); + Item_direct_ref *ref; + Field_iterator_view field_it; + THD *thd= table->in_use; + DBUG_ASSERT(is_merged_derived()); + + if (!used_items.elements) + return FALSE; + + materialized_items= (Item**)thd->calloc(sizeof(void*) * table->s->fields); + + while ((ref= (Item_direct_ref*)li++)) + { + uint idx; + Item *orig_item= *ref->ref; + field_it.set(this); + for (idx= 0; !field_it.end_of_fields(); field_it.next(), idx++) + { + if (field_it.item() == orig_item) + break; + } + DBUG_ASSERT(!field_it.end_of_fields()); + if (!materialized_items[idx]) + { + materialized_items[idx]= new Item_field(table->field[idx]); + if (!materialized_items[idx]) + return TRUE; + } + ref->ref= materialized_items + idx; + } + + return FALSE; +} + + /***************************************************************************** ** Instansiate templates *****************************************************************************/ diff --git a/sql/table.h b/sql/table.h index d43f5c85849..5b18749fe9b 100644 --- a/sql/table.h +++ b/sql/table.h @@ -791,7 +791,6 @@ struct st_table { uint temp_pool_slot; /* Used by intern temp tables */ uint status; /* What's in record[0] */ uint db_stat; /* mode of file as in handler.h */ - uint max_keys; /* Size of allocated key_info array. */ /* number of select if it is derived table */ uint derived_select_number; int current_lock; /* Type of lock on table */ @@ -867,8 +866,10 @@ struct st_table { */ bool auto_increment_field_not_null; bool insert_or_update; /* Can be used by the handler */ - bool alias_name_used; /* true if table_name is alias */ + bool alias_name_used; /* true if table_name is alias */ bool get_fields_in_item_tree; /* Signal to fix_field */ + bool created; /* For tmp tables. TRUE <=> tmp table was actually created.*/ + /* If MERGE children attached to parent. See top comment in ha_myisammrg.cc */ bool children_attached; @@ -889,6 +890,7 @@ struct st_table { bool no_partitions_used; /* If true, all partitions have been pruned away */ #endif + uint max_keys; /* Size of allocated key_info array. */ bool fill_item_list(List<Item> *item_list) const; void reset_item_list(List<Item> *item_list) const; void clear_column_bitmaps(void); @@ -958,6 +960,12 @@ struct st_table { bool unique); void create_key_part_by_field(KEY *keyinfo, KEY_PART_INFO *key_part_info, Field *field); + void use_index(int key_to_save); + void set_table_map(table_map map_arg, uint tablenr_arg) + { + map= map_arg; + tablenr= tablenr_arg; + } bool is_children_attached(void); inline void enable_keyread() { @@ -1109,13 +1117,52 @@ typedef struct st_schema_table } ST_SCHEMA_TABLE; +/* + Types of derived tables. The ending part is a bitmap of phases that are + applicable to a derived table of the type. + * / +#define VIEW_ALGORITHM_UNDEFINED 0 +#define VIEW_ALGORITHM_MERGE 1 + DT_COMMON + DT_MERGE +#define DERIVED_ALGORITHM_MERGE 2 + DT_COMMON + DT_MERGE +#define VIEW_ALGORITHM_TMPTABLE 3 + DT_COMMON + DT_MATERIALIZE +#define DERIVED_ALGORITHM_MATERIALIZE 4 + DT_COMMON + DT_MATERIALIZE +*/ +#define DTYPE_ALGORITHM_UNDEFINED 0 +#define DTYPE_VIEW 1 +#define DTYPE_TABLE 2 +#define DTYPE_MERGE 4 +#define DTYPE_MATERIALIZE 8 +#define DTYPE_MULTITABLE 16 +#define DTYPE_MASK 19 + +/* + Phases of derived tables/views handling, see sql_derived.cc + Values are used as parts of a bitmap attached to derived table types. +*/ +#define DT_INIT 1 +#define DT_PREPARE 2 +#define DT_OPTIMIZE 4 +#define DT_MERGE 8 +#define DT_MERGE_FOR_INSERT 16 +#define DT_CREATE 32 +#define DT_FILL 64 +#define DT_REINIT 128 +#define DT_PHASES 8 +/* Phases that are applicable to all derived tables. */ +#define DT_COMMON (DT_INIT + DT_PREPARE + DT_REINIT + DT_OPTIMIZE) +/* Phases that are applicable only to materialized derived tables. */ +#define DT_MATERIALIZE (DT_CREATE + DT_FILL) + +#define DT_PHASES_MERGE (DT_COMMON | DT_MERGE | DT_MERGE_FOR_INSERT) +#define DT_PHASES_MATERIALIZE (DT_COMMON | DT_MATERIALIZE) + +#define VIEW_ALGORITHM_UNDEFINED 0 +#define VIEW_ALGORITHM_MERGE (DTYPE_VIEW | DTYPE_MERGE) +#define VIEW_ALGORITHM_TMPTABLE (DTYPE_VIEW + DTYPE_MATERIALIZE ) + #define JOIN_TYPE_LEFT 1 #define JOIN_TYPE_RIGHT 2 -#define VIEW_ALGORITHM_UNDEFINED 0 -#define VIEW_ALGORITHM_TMPTABLE 1 -#define VIEW_ALGORITHM_MERGE 2 - #define VIEW_SUID_INVOKER 0 #define VIEW_SUID_DEFINER 1 #define VIEW_SUID_DEFAULT 2 @@ -1205,6 +1252,7 @@ class Item_in_subselect; also (TABLE_LIST::field_translation != NULL) - tmptable (TABLE_LIST::effective_algorithm == VIEW_ALGORITHM_TMPTABLE) also (TABLE_LIST::field_translation == NULL) + 2.5) TODO: Add derived tables description here 3) nested table reference (TABLE_LIST::nested_join != NULL) - table sequence - e.g. (t1, t2, t3) TODO: how to distinguish from a JOIN? @@ -1217,6 +1265,7 @@ class Item_in_subselect; */ class Index_hint; +struct st_lex; struct TABLE_LIST { TABLE_LIST() {} /* Remove gcc warning */ @@ -1310,6 +1359,8 @@ struct TABLE_LIST filling procedure */ select_union *derived_result; + /* Stub used for materialized derived tables. */ + table_map map; /* ID bit of table (1,2,4,8,16...) */ /* Reference from aux_tables to local list entry of main select of multi-delete statement: @@ -1354,6 +1405,7 @@ struct TABLE_LIST Field_translator *field_translation; /* array of VIEW fields */ /* pointer to element after last one in translation table above */ Field_translator *field_translation_end; + bool field_translation_updated; /* List (based on next_local) of underlying tables of this view. I.e. it does not include the tables of subqueries used in the view. Is set only @@ -1368,11 +1420,18 @@ struct TABLE_LIST List<TABLE_LIST> *view_tables; /* most upper view this table belongs to */ TABLE_LIST *belong_to_view; + /* A derived table this table belongs to */ + TABLE_LIST *belong_to_derived; /* The view directly referencing this table (non-zero only for merged underlying tables of a view). */ TABLE_LIST *referencing_view; + + table_map view_used_tables; + table_map map_exec; + uint tablenr_exec; + /* Ptr to parent MERGE table list item. See top comment in ha_myisammrg.cc */ TABLE_LIST *parent_l; /* @@ -1385,13 +1444,7 @@ struct TABLE_LIST SQL SECURITY DEFINER) */ Security_context *view_sctx; - /* - List of all base tables local to a subquery including all view - tables. Unlike 'next_local', this in this list views are *not* - leaves. Created in setup_tables() -> make_leaves_list(). - */ bool allowed_show; - TABLE_LIST *next_leaf; Item *where; /* VIEW WHERE clause condition */ Item *check_option; /* WITH CHECK OPTION condition */ LEX_STRING select_stmt; /* text of (CREATE/SELECT) statement */ @@ -1427,7 +1480,7 @@ struct TABLE_LIST - VIEW_ALGORITHM_MERGE @to do Replace with an enum */ - uint8 effective_algorithm; + uint8 derived_type; GRANT_INFO grant; /* data need by some engines in query cache*/ ulonglong engine_data; @@ -1454,7 +1507,6 @@ struct TABLE_LIST bool skip_temporary; /* this table shouldn't be temporary */ /* TRUE if this merged view contain auto_increment field */ bool contain_auto_increment; - bool multitable_view; /* TRUE iff this is multitable view */ bool compact_view_format; /* Use compact format for SHOW CREATE VIEW */ /* view where processed */ bool where_processed; @@ -1478,6 +1530,17 @@ struct TABLE_LIST bool internal_tmp_table; bool deleting; /* going to delete this table */ + /* TRUE <=> derived table should be filled right after optimization. */ + bool fill_me; + /* TRUE <=> view/DT is merged. */ + bool merged; + bool merged_for_insert; + /* TRUE <=> don't prepare this derived table/view as it should be merged.*/ + bool skip_prepare_derived; + + List<Item> used_items; + Item **materialized_items; + /* View creation context. */ View_creation_ctx *view_creation_ctx; @@ -1515,9 +1578,10 @@ struct TABLE_LIST bool has_table_lookup_value; uint table_open_method; enum enum_schema_table_state schema_table_state; + void calc_md5(char *buffer); - void set_underlying_merge(); int view_check_option(THD *thd, bool ignore_failure); + bool create_field_translation(THD *thd); bool setup_underlying(THD *thd); void cleanup_items(); bool placeholder() @@ -1547,7 +1611,7 @@ struct TABLE_LIST inline bool prepare_where(THD *thd, Item **conds, bool no_where_clause) { - if (effective_algorithm == VIEW_ALGORITHM_MERGE) + if (!view || is_merged_derived()) return prep_where(thd, conds, no_where_clause); return FALSE; } @@ -1613,6 +1677,60 @@ struct TABLE_LIST m_table_ref_version= s->get_table_ref_version(); } + /* Set of functions returning/setting state of a derived table/view. */ + inline bool is_non_derived() + { + return (!derived_type); + } + inline bool is_view_or_derived() + { + return (derived_type); + } + inline bool is_view() + { + return (derived_type & DTYPE_VIEW); + } + inline bool is_derived() + { + return (derived_type & DTYPE_TABLE); + } + inline void set_view() + { + derived_type= DTYPE_VIEW; + } + inline void set_derived() + { + derived_type= DTYPE_TABLE; + } + inline bool is_merged_derived() + { + return (derived_type & DTYPE_MERGE); + } + inline void set_merged_derived() + { + derived_type= ((derived_type & DTYPE_MASK) | + DTYPE_TABLE | DTYPE_MERGE); + } + inline bool is_materialized_derived() + { + return (derived_type & DTYPE_MATERIALIZE); + } + inline void set_materialized_derived() + { + derived_type= ((derived_type & DTYPE_MASK) | + DTYPE_TABLE | DTYPE_MATERIALIZE); + } + inline bool is_multitable() + { + return (derived_type & DTYPE_MULTITABLE); + } + inline void set_multitable() + { + derived_type|= DTYPE_MULTITABLE; + } + void reset_const_table(); + bool handle_derived(struct st_lex *lex, uint phases); + /** @brief True if this TABLE_LIST represents an anonymous derived table, i.e. the result of a subquery. @@ -1632,6 +1750,12 @@ struct TABLE_LIST respectively. */ char *get_table_name() { return view != NULL ? view_name.str : table_name; } + st_select_lex_unit *get_unit(); + st_select_lex *get_single_select(); + void wrap_into_nested_join(List<TABLE_LIST> &join_list); + bool init_derived(THD *thd, bool init_view); + int fetch_number_of_rows(); + bool change_refs_to_fields(); private: bool prep_check_option(THD *thd, uint8 check_opt_type); |