diff options
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); |