From 2775f80f7d287cb0ed478543bf135b9399f56d66 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 6 Apr 2010 00:16:45 +0400 Subject: MWL#90: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE - First code (needs cleanup). --- sql/item_subselect.h | 21 +++++++++++++++++---- 1 file changed, 17 insertions(+), 4 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index e538f02d80a..5414c2b6bb9 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -52,9 +52,9 @@ public: public: /* unit of subquery */ st_select_lex_unit *unit; -protected: /* engine that perform execution of subselect (single select or union) */ subselect_engine *engine; +protected: /* old engine if engine was changed */ subselect_engine *old_engine; /* cache of used external tables */ @@ -139,6 +139,7 @@ public: bool mark_as_dependent(THD *thd, st_select_lex *select, Item *item); void fix_after_pullout(st_select_lex *new_parent, Item **ref); void recalc_used_tables(st_select_lex *new_parent, bool after_pullout); + virtual int optimize(); virtual bool exec(); virtual void fix_length_and_dec(); table_map used_tables() const; @@ -333,7 +334,9 @@ protected: all JOIN in UNION */ Item *expr; +public: Item_in_optimizer *optimizer; +protected: bool was_null; bool abort_on_null; public: @@ -379,6 +382,10 @@ public: }; enum_exec_method exec_method; + /* JTBM: temporary measure to tell JTBM predicates from SJ predicates */ + bool convert_to_semi_join; + double startup_cost; + bool *get_cond_guard(int i) { return pushed_cond_guards ? pushed_cond_guards + i : NULL; @@ -428,7 +435,7 @@ public: bool fix_fields(THD *thd, Item **ref); void fix_after_pullout(st_select_lex *new_parent, Item **ref); void update_used_tables(); - bool setup_engine(); + bool setup_engine(bool dont_switch_arena); bool init_left_expr_cache(); /* Inform 'this' that it was computed, and contains a valid result. */ void set_first_execution() { if (first_execution) first_execution= FALSE; } @@ -502,6 +509,7 @@ public: THD * get_thd() { return thd; } virtual int prepare()= 0; virtual void fix_length_and_dec(Item_cache** row)= 0; + virtual int optimize() { DBUG_ASSERT(0); return 0; } /* Execute the engine @@ -734,7 +742,7 @@ inline bool Item_subselect::is_uncacheable() const class subselect_hash_sj_engine : public subselect_engine { -protected: +public: /* The table into which the subquery is materialized. */ TABLE *tmp_table; /* TRUE if the subquery was materialized into a temp table. */ @@ -746,14 +754,16 @@ protected: of subselect_single_select_engine::[prepare | cols]. */ subselect_single_select_engine *materialize_engine; +protected: /* The engine used to compute the IN predicate. */ subselect_engine *lookup_engine; /* QEP to execute the subquery and materialize its result into a temporary table. Created during the first call to exec(). */ +public: JOIN *materialize_join; - +protected: /* Keyparts of the only non-NULL composite index in a rowid merge. */ MY_BITMAP non_null_key_parts; /* Keyparts of the single column indexes with NULL, one keypart per index. */ @@ -766,7 +776,9 @@ protected: IN results because index lookups sometimes match values that are actually not equal to the search key in SQL terms. */ +public: Item_cond_and *semi_join_conds; +protected: /* Possible execution strategies that can be used to compute hash semi-join.*/ enum exec_strategy { UNDEFINED, @@ -806,6 +818,7 @@ public: bool init_runtime(); void cleanup(); int prepare() { return 0; } /* Override virtual function in base class. */ + int optimize(); int exec(); virtual void print(String *str, enum_query_type query_type); uint cols() -- cgit v1.2.1 From 30bac798066ced860d65bee8b3ef63d7b100e47d Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 7 Apr 2010 01:29:09 +0400 Subject: Always refer to materialized table as "SUBSELECT#%d" where %d is select number - for Item-based materialization, it was "materialized subselect" - for SJ-Materialization it was "subquery%d" --- sql/item_subselect.h | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 5414c2b6bb9..c26977982d5 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -814,7 +814,7 @@ public: } ~subselect_hash_sj_engine(); - bool init_permanent(List *tmp_columns); + bool init_permanent(List *tmp_columns, uint subquery_id); bool init_runtime(); void cleanup(); int prepare() { return 0; } /* Override virtual function in base class. */ -- cgit v1.2.1 From b2c57cedbfd0f8ce2b4f67021a2aca5dedf9b6c9 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sun, 25 Apr 2010 12:23:52 +0400 Subject: Code cleanup in subquery optimizations --- sql/item_subselect.h | 14 +++++++++++++- 1 file changed, 13 insertions(+), 1 deletion(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index c26977982d5..bd1c11fa29e 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -382,8 +382,20 @@ public: }; enum_exec_method exec_method; - /* JTBM: temporary measure to tell JTBM predicates from SJ predicates */ + /* + JTBM: temporary measure to tell JTBM predicates from SJ predicates + psergey-jtbm-todo: can't we do without this? + - either remove it altogether + - or put into enum_exec_method + + We can't remove it altogether as it is used to classify contents in + join->sj_subselects. + */ bool convert_to_semi_join; + + /* + Cost to populate the temporary table (set on if-needed basis). + */ double startup_cost; bool *get_cond_guard(int i) -- cgit v1.2.1 From ed8aa9868e074e6489f3b71f585606b6eecc97c6 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 11 May 2010 11:53:40 +0400 Subject: - Make subselect_sj2 testcase more stable - Better comments --- sql/item_subselect.h | 2 ++ 1 file changed, 2 insertions(+) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index bd1c11fa29e..3c3d6cdbf00 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -46,6 +46,7 @@ public: < child_join->prepare < engine->prepare *ref= substitution; + substitution= NULL; < Item_subselect::fix_fields */ Item *substitution; @@ -390,6 +391,7 @@ public: We can't remove it altogether as it is used to classify contents in join->sj_subselects. + jtbm-todo: option 1: let sj_subselects list pairs. */ bool convert_to_semi_join; -- cgit v1.2.1 From 3f595889d35c81540eb14ef3c53105cb6c4db833 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sun, 23 May 2010 23:13:18 +0400 Subject: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE - Code cleanup - Query plan change is due to s/ha_rows JOIN_TAB::read_time/double JOIN_TAB::read_time/ --- sql/item_subselect.h | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 3c3d6cdbf00..3e8ec9b174d 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -393,12 +393,12 @@ public: join->sj_subselects. jtbm-todo: option 1: let sj_subselects list pairs. */ - bool convert_to_semi_join; + bool is_flattenable_semijoin; /* Cost to populate the temporary table (set on if-needed basis). */ - double startup_cost; + //double startup_cost; bool *get_cond_guard(int i) { -- cgit v1.2.1 From 709a0a131021135e9fb7a2095fcfcbc223dfb126 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 26 May 2010 13:18:18 -0700 Subject: MWL#106: Backport optimizations for derived tables and views. The main consolidated patch. --- sql/item_subselect.h | 1 + 1 file changed, 1 insertion(+) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index e538f02d80a..00844f6cd42 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -531,6 +531,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 &list); virtual void print(String *str, enum_query_type query_type)= 0; virtual bool change_result(Item_subselect *si, select_result_interceptor *result)= 0; -- cgit v1.2.1 From e9bac8db82f54d715997cceff082452c5406d36e Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 11 Jun 2010 18:43:06 +0400 Subject: MWL#90, code cleanup - Remove garbage comments - fix a bug in join_tab_cmp --- sql/item_subselect.h | 9 +-------- 1 file changed, 1 insertion(+), 8 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 3e8ec9b174d..3454c8a2779 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -384,14 +384,7 @@ public: enum_exec_method exec_method; /* - JTBM: temporary measure to tell JTBM predicates from SJ predicates - psergey-jtbm-todo: can't we do without this? - - either remove it altogether - - or put into enum_exec_method - - We can't remove it altogether as it is used to classify contents in - join->sj_subselects. - jtbm-todo: option 1: let sj_subselects list pairs. + TRUE<=>this is a flattenable semi-join, false overwise. */ bool is_flattenable_semijoin; -- cgit v1.2.1 From 875bd20a7327b7fd03d1b020e25054755e42d5ad Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 16 Jul 2010 13:52:02 +0300 Subject: MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation 1. Changed the lazy optimization for subqueries that can be materialized into bottom-up optimization during the optimization of the main query. The main change is implemented by the method Item_in_subselect::setup_engine. All other changes were required to correct problems resulting from changing the order of optimization. Most of these problems followed the same pattern - there are some shared structures between a subquery and its parent query. Depending on which one is optimized first (parent or child query), these shared strucutres may get different values, thus resulting in an inconsistent query plan. 2. Changed the code-generation for subquery materialization to be performed in runtime memory for each (re)execution, instead of in statement memory (once per prepared statement). - Item_in_subselect::setup_engine() no longer creates materialization related objects in statement memory. - Merged subselect_hash_sj_engine::init_permanent and subselect_hash_sj_engine::init_runtime into subselect_hash_sj_engine::init, which is called for each (re)execution. - Fixed deletion of the temp table accordingly. mysql-test/r/subselect_mat.result: Adjusted changed EXPLAIN because of earlier optimization of subqueries. --- sql/item_subselect.h | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 72f73d58867..15b2dc8448f 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -817,10 +817,9 @@ public: } ~subselect_hash_sj_engine(); - bool init_permanent(List *tmp_columns); - bool init_runtime(); + bool init(List *tmp_columns); void cleanup(); - int prepare() { return 0; } /* Override virtual function in base class. */ + int prepare(); int exec(); virtual void print(String *str, enum_query_type query_type); uint cols() -- cgit v1.2.1 From 78ddd9ffe621697a8518bf0a82821032d4ff48fe Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 18 Jul 2010 14:46:08 +0300 Subject: MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation Step1 in the separation of the creation of IN->EXISTS equi-join conditions from their injection. The goal of this separation is to make it possible that the IN->EXISTS conditions can be used for cost estimation without actually modifying the subquery. This patch separates single_value_in_to_exists_transformer() into two methods: - create_single_value_in_to_exists_cond(), and - inject_single_value_in_to_exists_cond() The patch performs minimal refactoring of the code so that it is easier to solve problems resulting from the separation. There is a lot to be simplified in this code, but this will be done separately. --- sql/item_subselect.h | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 15b2dc8448f..0a02dd3423b 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -425,8 +425,18 @@ public: trans_res select_in_like_transformer(JOIN *join, Comp_creator *func); trans_res single_value_transformer(JOIN *join, Comp_creator *func); trans_res row_value_transformer(JOIN * join); + trans_res single_value_in_to_exists_transformer(JOIN * join, Comp_creator *func); + trans_res create_single_value_in_to_exists_cond(JOIN * join, + Comp_creator *func, + Item **where_term, + Item **having_term); + trans_res inject_single_value_in_to_exists_cond(JOIN * join, + Comp_creator *func, + Item *where_term, + Item *having_term); + trans_res row_value_in_to_exists_transformer(JOIN * join); virtual bool exec(); longlong val_int(); -- cgit v1.2.1 From aa195b25704b4e67423654422ce0b601b54f809d Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 18 Jul 2010 15:59:24 +0300 Subject: MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation Step2 in the separation of the creation of IN->EXISTS equi-join conditions from their injection. The goal of this separation is to make it possible that the IN->EXISTS conditions can be used for cost estimation without actually modifying the subquery. This patch separates row_value_in_to_exists_transformer() into two methods: - create_row_value_in_to_exists_cond(), and - inject_row_value_in_to_exists_cond() The patch performs minimal refactoring of the code so that it is easier to solve problems resulting from the separation. There is a lot to be simplified in this code, but this will be done separately. --- sql/item_subselect.h | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 0a02dd3423b..d6cca97c351 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -438,6 +438,13 @@ public: Item *having_term); trans_res row_value_in_to_exists_transformer(JOIN * join); + trans_res create_row_value_in_to_exists_cond(JOIN * join, + Item **where_term, + Item **having_term); + trans_res inject_row_value_in_to_exists_cond(JOIN * join, + Item *where_term, + Item *having_term); + virtual bool exec(); longlong val_int(); double val_real(); -- cgit v1.2.1 From 18ad3bdc2fa3bbe1bfd7e433adb0bc6b3dbce8c8 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 5 Sep 2010 18:43:47 +0300 Subject: MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation Fixes for multiple problems/bugs/test failures that resulted from moving subquery optimization from the execution phase to the optimization phase. --- sql/item_subselect.h | 65 ++++++++++++++++++++++++++++------------------------ 1 file changed, 35 insertions(+), 30 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index d6cca97c351..d35d036e9ea 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -108,6 +108,9 @@ public: /* subquery is transformed */ bool changed; + /* TIMOUR: this is temporary, remove it. */ + bool is_min_max_optimized; + /* TRUE <=> The underlying SELECT is correlated w.r.t some ancestor select */ bool is_correlated; @@ -180,6 +183,8 @@ public: enum_parsing_place place() { return parsing_place; } bool walk(Item_processor processor, bool walk_subquery, uchar *arg); bool mark_as_eliminated_processor(uchar *arg); + bool eliminate_subselect_processor(uchar *arg); + bool set_fake_select_as_master_processor(uchar *arg); bool enumerate_field_refs_processor(uchar *arg); bool check_vcol_func_processor(uchar *int_arg) { @@ -326,8 +331,6 @@ public: class Item_in_subselect :public Item_exists_subselect { -public: - Item *left_expr; protected: /* Cache of the left operand of the subquery predicate. Allocated in the @@ -350,10 +353,30 @@ protected: Item_in_optimizer *optimizer; bool was_null; bool abort_on_null; -public: /* Used to trigger on/off conditions that were pushed down to subselect */ bool *pushed_cond_guards; - + Comp_creator *func; + +protected: + bool init_cond_guards(); + trans_res select_in_like_transformer(JOIN *join); + trans_res single_value_transformer(JOIN *join); + trans_res row_value_transformer(JOIN * join); + trans_res create_single_in_to_exists_cond(JOIN * join, + Item **where_item, + Item **having_item); + trans_res inject_single_in_to_exists_cond(JOIN * join, + Item *where_item, + Item *having_item); + + trans_res create_row_in_to_exists_cond(JOIN * join, + Item **where_item, + Item **having_item); + trans_res inject_row_in_to_exists_cond(JOIN * join, + Item *where_item, + Item *having_item); +public: + Item *left_expr; /* Priority of this predicate in the convert-to-semi-join-nest process. */ int sj_convert_priority; /* @@ -410,8 +433,9 @@ public: Item_in_subselect() :Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE), is_constant(FALSE), optimizer(0), abort_on_null(0), - pushed_cond_guards(NULL), exec_method(NOT_TRANSFORMED), upper_item(0) - {} + pushed_cond_guards(NULL), func(NULL), exec_method(NOT_TRANSFORMED), + upper_item(0) + {} void cleanup(); subs_type substype() { return IN_SUBS; } void reset() @@ -422,28 +446,8 @@ public: was_null= 0; } trans_res select_transformer(JOIN *join); - trans_res select_in_like_transformer(JOIN *join, Comp_creator *func); - trans_res single_value_transformer(JOIN *join, Comp_creator *func); - trans_res row_value_transformer(JOIN * join); - - trans_res single_value_in_to_exists_transformer(JOIN * join, - Comp_creator *func); - trans_res create_single_value_in_to_exists_cond(JOIN * join, - Comp_creator *func, - Item **where_term, - Item **having_term); - trans_res inject_single_value_in_to_exists_cond(JOIN * join, - Comp_creator *func, - Item *where_term, - Item *having_term); - - trans_res row_value_in_to_exists_transformer(JOIN * join); - trans_res create_row_value_in_to_exists_cond(JOIN * join, - Item **where_term, - Item **having_term); - trans_res inject_row_value_in_to_exists_cond(JOIN * join, - Item *where_term, - Item *having_term); + bool create_in_to_exists_cond(JOIN * join_arg); + bool inject_in_to_exists_cond(JOIN * join_arg); virtual bool exec(); longlong val_int(); @@ -459,11 +463,12 @@ public: bool fix_fields(THD *thd, Item **ref); void fix_after_pullout(st_select_lex *new_parent, Item **ref); void update_used_tables(); - bool setup_engine(); + bool setup_mat_engine(); bool init_left_expr_cache(); /* Inform 'this' that it was computed, and contains a valid result. */ void set_first_execution() { if (first_execution) first_execution= FALSE; } bool is_expensive_processor(uchar *arg); + bool is_expensive() { return TRUE; } bool expr_cache_is_needed(THD *thd); /* @@ -485,7 +490,6 @@ class Item_allany_subselect :public Item_in_subselect { public: chooser_compare_func_creator func_creator; - Comp_creator *func; bool all; Item_allany_subselect(Item * left_expr, chooser_compare_func_creator fc, @@ -494,6 +498,7 @@ public: // only ALL subquery has upper not subs_type substype() { return all?ALL_SUBS:ANY_SUBS; } trans_res select_transformer(JOIN *join); + void create_comp_func(bool invert) { func= func_creator(invert); } virtual void print(String *str, enum_query_type query_type); }; -- cgit v1.2.1 From 8ec5e13f1f0d56afe42e5ded02baeab7a6a60261 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 30 Sep 2010 18:32:44 +0300 Subject: MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation Phase 3: Implementation of re-optimization of subqueries with injected predicates and cost comparison between Materialization and IN->EXISTS strategies. The commit contains the following known problems: - The implementation of EXPLAIN has not been re-engineered to reflect the changes in subquery optimization. EXPLAIN for subqueries is called during the execute phase, which results in different code paths during JOIN::optimize and thus in differing EXPLAIN messages for constant/system tables. - There are some valgrind warnings that need investigation - Several EXPLAINs with minor differences need to be reconsidered after fixing the EXPLAIN problem above. This patch also adds one extra optimizer_switch: 'in_to_exists' for complete manual control of the subquery execution strategies. --- sql/item_subselect.h | 45 ++++++++++++++++++++++++--------------------- 1 file changed, 24 insertions(+), 21 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index d35d036e9ea..308fbc6b20a 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -108,9 +108,6 @@ public: /* subquery is transformed */ bool changed; - /* TIMOUR: this is temporary, remove it. */ - bool is_min_max_optimized; - /* TRUE <=> The underlying SELECT is correlated w.r.t some ancestor select */ bool is_correlated; @@ -121,6 +118,12 @@ public: Item_subselect(); virtual subs_type substype() { return UNKNOWN_SUBS; } + bool is_in_predicate() + { + return (substype() == Item_subselect::IN_SUBS || + substype() == Item_subselect::ALL_SUBS || + substype() == Item_subselect::ANY_SUBS); + } /* We need this method, because some compilers do not allow 'this' @@ -314,6 +317,18 @@ public: }; +/* + Possible methods to execute an IN predicate. These are set by the optimizer + based on user-set optimizer switches, syntactic analysis and cost comparison. +*/ +#define SUBS_NOT_TRANSFORMED 0 /* No execution method was chosen for this IN. */ +#define SUBS_SEMI_JOIN 1 /* IN was converted to semi-join. */ +#define SUBS_IN_TO_EXISTS 2 /* IN was converted to correlated EXISTS. */ +#define SUBS_MATERIALIZATION 4 /* Execute IN via subquery materialization. */ +/* Partial matching substrategies of MATERIALIZATION. */ +#define SUBS_PARTIAL_MATCH_ROWID_MERGE 8 +#define SUBS_PARTIAL_MATCH_TABLE_SCAN 16 + /** Representation of IN subquery predicates of the form "left_expr IN (SELECT ...)". @@ -362,19 +377,13 @@ protected: trans_res select_in_like_transformer(JOIN *join); trans_res single_value_transformer(JOIN *join); trans_res row_value_transformer(JOIN * join); + bool fix_having(Item *having, st_select_lex *select_lex); trans_res create_single_in_to_exists_cond(JOIN * join, Item **where_item, Item **having_item); - trans_res inject_single_in_to_exists_cond(JOIN * join, - Item *where_item, - Item *having_item); - trans_res create_row_in_to_exists_cond(JOIN * join, Item **where_item, Item **having_item); - trans_res inject_row_in_to_exists_cond(JOIN * join, - Item *where_item, - Item *having_item); public: Item *left_expr; /* Priority of this predicate in the convert-to-semi-join-nest process. */ @@ -407,14 +416,8 @@ public: */ bool sjm_scan_allowed; - /* The method chosen to execute the IN predicate. */ - enum enum_exec_method { - NOT_TRANSFORMED, /* No execution method was chosen for this IN. */ - SEMI_JOIN, /* IN was converted to semi-join nest and should be removed. */ - IN_TO_EXISTS, /* IN was converted to correlated EXISTS. */ - MATERIALIZATION /* IN will be executed via subquery materialization. */ - }; - enum_exec_method exec_method; + /* A bitmap of possible execution strategies for an IN predicate. */ + uchar in_strategy; bool *get_cond_guard(int i) { @@ -433,7 +436,7 @@ public: Item_in_subselect() :Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE), is_constant(FALSE), optimizer(0), abort_on_null(0), - pushed_cond_guards(NULL), func(NULL), exec_method(NOT_TRANSFORMED), + pushed_cond_guards(NULL), func(NULL), in_strategy(0), upper_item(0) {} void cleanup(); @@ -446,8 +449,8 @@ public: was_null= 0; } trans_res select_transformer(JOIN *join); - bool create_in_to_exists_cond(JOIN * join_arg); - bool inject_in_to_exists_cond(JOIN * join_arg); + bool create_in_to_exists_cond(JOIN *join_arg); + bool inject_in_to_exists_cond(JOIN *join_arg); virtual bool exec(); longlong val_int(); -- cgit v1.2.1 From 4a3f135a2ed9fe533d1a772d234ff5534524dcbf Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 5 Oct 2010 16:00:31 +0300 Subject: MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation Added/corrected/improved comments. --- sql/item_subselect.h | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 308fbc6b20a..2a6d310cbb4 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -319,7 +319,7 @@ public: /* Possible methods to execute an IN predicate. These are set by the optimizer - based on user-set optimizer switches, syntactic analysis and cost comparison. + based on user-set optimizer switches, semantic analysis and cost comparison. */ #define SUBS_NOT_TRANSFORMED 0 /* No execution method was chosen for this IN. */ #define SUBS_SEMI_JOIN 1 /* IN was converted to semi-join. */ -- cgit v1.2.1 From f670b6d22f55401d4329f9ac9827a5145d1dd667 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 23 Oct 2010 21:28:58 +0300 Subject: MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation Added missing logic to handle the case when subquery tables are optimized away early during optimization. --- sql/item_subselect.h | 26 ++++++++++++++++++-------- 1 file changed, 18 insertions(+), 8 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 2a6d310cbb4..dc29323f756 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -70,6 +70,13 @@ protected: bool inside_first_fix_fields; bool done_first_fix_fields; + /* + Set to TRUE if at optimization or execution time we determine that this + item's value is a constant. We need this member because it is not possible + to substitute 'this' with a constant item. + */ + bool forced_const; + public: /* A reference from inside subquery predicate to somewhere outside of it */ class Ref_to_outside : public Sql_alloc @@ -154,12 +161,21 @@ public: void fix_after_pullout(st_select_lex *new_parent, Item **ref); void recalc_used_tables(st_select_lex *new_parent, bool after_pullout); virtual bool exec(); + /* + If subquery optimization or execution determines that the subquery has + an empty result, mark the subquery predicate as a constant value. + */ + void make_const() + { + used_tables_cache= 0; + const_item_cache= 0; + forced_const= TRUE; + } virtual void fix_length_and_dec(); table_map used_tables() const; table_map not_null_tables() const { return 0; } bool const_item() const; inline table_map get_used_tables_cache() { return used_tables_cache; } - inline bool get_const_item_cache() { return const_item_cache; } Item *get_tmp_table_item(THD *thd); void update_used_tables(); virtual void print(String *str, enum_query_type query_type); @@ -353,12 +369,6 @@ protected: */ List *left_expr_cache; bool first_execution; - /* - Set to TRUE if at query execution time we determine that this item's - value is a constant during this execution. We need this member because - it is not possible to substitute 'this' with a constant item. - */ - bool is_constant; /* expr & optimizer used in subselect rewriting to store Item for @@ -435,7 +445,7 @@ public: Item_in_subselect(Item * left_expr, st_select_lex *select_lex); Item_in_subselect() :Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE), - is_constant(FALSE), optimizer(0), abort_on_null(0), + optimizer(0), abort_on_null(0), pushed_cond_guards(NULL), func(NULL), in_strategy(0), upper_item(0) {} -- cgit v1.2.1 From 2fa5df5f4eea94bdf8b25406c1e9534535b519c7 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 23 Nov 2010 00:01:24 +0200 Subject: Fix LP BUG#680038 Analysis: Single-row subqueries are not considered expensive and are evaluated both during EXPLAIN in to detect errors like "Subquery returns more than 1 row", and during optimization to perform constant optimization. The cause for the failed ASSERT is in JOIN::join_free, where we set bool full= (!select_lex->uncacheable && !thd->lex->describe); Thus for EXPLAIN statements full == FALSE, and as a result the call to JOIN::cleanup doesn't call JOIN_TAB::cleanup which should have called table->disable_keyread(). Solution: Consider all kinds of subquery predicates as expensive. --- sql/item_subselect.h | 13 +++++++++++-- 1 file changed, 11 insertions(+), 2 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 85e0191348e..eae9b0e558c 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -194,6 +194,7 @@ public: */ bool is_evaluated() const; bool is_uncacheable() const; + bool is_expensive() { return TRUE; } /* Used by max/min subquery to initialize value presence registration @@ -210,6 +211,16 @@ public: { return trace_unsupported_by_check_vcol_func_processor("subselect"); } + /** + Callback to test if an IN predicate is expensive. + + @notes + The return value affects the behavior of make_cond_for_table(). + + @retval TRUE if the predicate is expensive + @retval FALSE otherwise + */ + bool is_expensive_processor(uchar *arg) { return TRUE; } Item *safe_charset_converter(CHARSET_INFO *tocs); /** @@ -484,8 +495,6 @@ public: bool init_left_expr_cache(); /* Inform 'this' that it was computed, and contains a valid result. */ void set_first_execution() { if (first_execution) first_execution= FALSE; } - bool is_expensive_processor(uchar *arg); - bool is_expensive() { return TRUE; } bool expr_cache_is_needed(THD *thd); /* -- cgit v1.2.1 From ab5e4eefd5d25c6e0755c2460dbb433a7a9e913b Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Tue, 30 Nov 2010 01:27:14 +0200 Subject: Fixed some compiler warnings found when compiling for windows. Changed rows_read and rows_sent status variables to be longlong (to avoid compiler warnings) sql/item_func.cc: Fixed wrong usage of alias sql/item_subselect.cc: Changed buffer size to ulonglong to be able detect buffers bigger than size_t sql/item_subselect.h: Changed buffer size to ulonglong to be able detect buffers bigger than size_t sql/multi_range_read.cc: Fixed compiler warning by using correct type for function argument sql/mysqld.cc: Changed rows_read and rows_sent status variables to be longlong sql/opt_subselect.h: Fixed compiler warning by using correct type for function argument sql/sql_class.cc: Changed rows_read and rows_sent status variables to be longlong sql/sql_class.h: Changed rows_read and rows_sent status variables to be longlong Changed max_nulls_in_row to uint as this is number of columns in row. This fixed some compiler warnings. sql/sql_select.cc: Added casts to avoid compiler warnings storage/heap/ha_heap.cc: Initilize different types separate storage/oqgraph/ha_oqgraph.cc: Fixed argument to store(longlong) to avoid compiler warnings --- sql/item_subselect.h | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 320c57b679f..25e42487708 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -800,9 +800,9 @@ protected: protected: exec_strategy get_strategy_using_schema(); exec_strategy get_strategy_using_data(); - size_t rowid_merge_buff_size(bool has_non_null_key, - bool has_covering_null_row, - MY_BITMAP *partial_match_key_parts); + ulonglong rowid_merge_buff_size(bool has_non_null_key, + bool has_covering_null_row, + MY_BITMAP *partial_match_key_parts); void choose_partial_match_strategy(bool has_non_null_key, bool has_covering_null_row, MY_BITMAP *partial_match_key_parts); -- cgit v1.2.1 From 4f28dcbe327139d9d5cb71afc9f4ce99cecec25a Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 14 Dec 2010 14:08:05 +0200 Subject: Fix LP BUG#685411 Analysis: The assert failed because st_select_lex::print() was called for subqueries as follows: Item_subselect::print() -> subselect_single_select_engine::print() -> st_select_lex::print() It was Item_subselect::fix_fields() that set the thd by calling set_thd(), so when this print() was called before fix_fields(), subselect_engine::thd was NULL. Solution: The patch makes all constructors of all subselect_engine classes to take a THD parameter. The default subselect_single_select_engine engine is created early during parse time, in the Item_subselect::init call, so we pass the correct THD object already at this point. --- sql/item_subselect.h | 38 ++++++++++++++++++-------------------- 1 file changed, 18 insertions(+), 20 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 25e42487708..8d590bc4273 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -501,14 +501,15 @@ public: INDEXSUBQUERY_ENGINE, HASH_SJ_ENGINE, ROWID_MERGE_ENGINE, TABLE_SCAN_ENGINE}; - subselect_engine(Item_subselect *si, select_result_interceptor *res) - :thd(0) + subselect_engine(THD *thd_arg, Item_subselect *si, + select_result_interceptor *res) { result= res; item= si; res_type= STRING_RESULT; res_field_type= MYSQL_TYPE_VAR_STRING; maybe_null= 0; + set_thd(thd_arg); } virtual ~subselect_engine() {}; // to satisfy compiler virtual void cleanup()= 0; @@ -572,7 +573,7 @@ class subselect_single_select_engine: public subselect_engine st_select_lex *select_lex; /* corresponding select_lex */ JOIN * join; /* corresponding JOIN structure */ public: - subselect_single_select_engine(st_select_lex *select, + subselect_single_select_engine(THD *thd_arg, st_select_lex *select, select_result_interceptor *result, Item_subselect *item); void cleanup(); @@ -601,7 +602,7 @@ class subselect_union_engine: public subselect_engine { st_select_lex_unit *unit; /* corresponding unit structure */ public: - subselect_union_engine(st_select_lex_unit *u, + subselect_union_engine(THD *thd_arg, st_select_lex_unit *u, select_result_interceptor *result, Item_subselect *item); void cleanup(); @@ -657,10 +658,8 @@ public: // constructor can assign THD because it will be called after JOIN::prepare subselect_uniquesubquery_engine(THD *thd_arg, st_join_table *tab_arg, Item_subselect *subs, Item *where) - :subselect_engine(subs, 0), tab(tab_arg), cond(where) - { - set_thd(thd_arg); - } + :subselect_engine(thd_arg, subs, 0), tab(tab_arg), cond(where) + {} ~subselect_uniquesubquery_engine(); void cleanup(); int prepare(); @@ -812,13 +811,11 @@ protected: public: subselect_hash_sj_engine(THD *thd, Item_subselect *in_predicate, subselect_single_select_engine *old_engine) - :subselect_engine(in_predicate, NULL), tmp_table(NULL), + :subselect_engine(thd, in_predicate, NULL), tmp_table(NULL), is_materialized(FALSE), materialize_engine(old_engine), lookup_engine(NULL), materialize_join(NULL), count_partial_match_columns(0), count_null_only_columns(0), semi_join_conds(NULL), strategy(UNDEFINED) - { - set_thd(thd); - } + {} ~subselect_hash_sj_engine(); bool init_permanent(List *tmp_columns); @@ -1056,7 +1053,8 @@ protected: protected: virtual bool partial_match()= 0; public: - subselect_partial_match_engine(subselect_uniquesubquery_engine *engine_arg, + subselect_partial_match_engine(THD *thd_arg, + subselect_uniquesubquery_engine *engine_arg, TABLE *tmp_table_arg, Item_subselect *item_arg, select_result_interceptor *result_arg, List *equi_join_conds_arg, @@ -1148,19 +1146,18 @@ protected: bool test_null_row(rownum_t row_num); bool partial_match(); public: - subselect_rowid_merge_engine(subselect_uniquesubquery_engine *engine_arg, + subselect_rowid_merge_engine(THD *thd_arg, + subselect_uniquesubquery_engine *engine_arg, TABLE *tmp_table_arg, uint keys_count_arg, uint covering_null_row_width_arg, Item_subselect *item_arg, select_result_interceptor *result_arg, List *equi_join_conds_arg) - :subselect_partial_match_engine(engine_arg, tmp_table_arg, item_arg, - result_arg, equi_join_conds_arg, + :subselect_partial_match_engine(thd_arg, engine_arg, tmp_table_arg, + item_arg, result_arg, equi_join_conds_arg, covering_null_row_width_arg), keys_count(keys_count_arg), non_null_key(NULL) - { - thd= lookup_engine->get_thd(); - } + {} ~subselect_rowid_merge_engine(); bool init(MY_BITMAP *non_null_key_parts, MY_BITMAP *partial_match_key_parts); void cleanup(); @@ -1173,7 +1170,8 @@ class subselect_table_scan_engine: public subselect_partial_match_engine protected: bool partial_match(); public: - subselect_table_scan_engine(subselect_uniquesubquery_engine *engine_arg, + subselect_table_scan_engine(THD *thd_arg, + subselect_uniquesubquery_engine *engine_arg, TABLE *tmp_table_arg, Item_subselect *item_arg, select_result_interceptor *result_arg, List *equi_join_conds_arg, -- cgit v1.2.1 From 0bee625feac622190efd1b1d2388f874ed03b50c Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 15 Dec 2010 12:54:25 +0200 Subject: MWL#89 Post-review fixes. Intermediate commit to address review points 1.1, 1.2, 1.3, 1.4, 1.5, and 3.1, 3.2, 3.3. --- sql/item_subselect.h | 27 +++++++++++++-------------- 1 file changed, 13 insertions(+), 14 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index fc278fe03ad..a6d591de103 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -119,7 +119,6 @@ public: /* TRUE <=> The underlying SELECT is correlated w.r.t some ancestor select */ bool is_correlated; - enum trans_res {RES_OK, RES_REDUCE, RES_ERROR}; enum subs_type {UNKNOWN_SUBS, SINGLEROW_SUBS, EXISTS_SUBS, IN_SUBS, ALL_SUBS, ANY_SUBS}; @@ -148,7 +147,7 @@ public: eliminated= FALSE; null_value= 1; } - virtual trans_res select_transformer(JOIN *join); + virtual bool select_transformer(JOIN *join); bool assigned() { return value_assigned; } void assigned(bool a) { value_assigned= a; } enum Type type() const; @@ -259,7 +258,7 @@ public: subs_type substype() { return SINGLEROW_SUBS; } void reset(); - trans_res select_transformer(JOIN *join); + bool select_transformer(JOIN *join); void store(uint i, Item* item); double val_real(); longlong val_int (); @@ -399,16 +398,16 @@ protected: protected: bool init_cond_guards(); - trans_res select_in_like_transformer(JOIN *join); - trans_res single_value_transformer(JOIN *join); - trans_res row_value_transformer(JOIN * join); + bool select_in_like_transformer(JOIN *join); + bool single_value_transformer(JOIN *join); + bool row_value_transformer(JOIN * join); bool fix_having(Item *having, st_select_lex *select_lex); - trans_res create_single_in_to_exists_cond(JOIN * join, - Item **where_item, - Item **having_item); - trans_res create_row_in_to_exists_cond(JOIN * join, - Item **where_item, - Item **having_item); + bool create_single_in_to_exists_cond(JOIN * join, + Item **where_item, + Item **having_item); + bool create_row_in_to_exists_cond(JOIN * join, + Item **where_item, + Item **having_item); public: Item *left_expr; /* Priority of this predicate in the convert-to-semi-join-nest process. */ @@ -473,7 +472,7 @@ public: null_value= 0; was_null= 0; } - trans_res select_transformer(JOIN *join); + bool select_transformer(JOIN *join); bool create_in_to_exists_cond(JOIN *join_arg); bool inject_in_to_exists_cond(JOIN *join_arg); @@ -523,7 +522,7 @@ public: // only ALL subquery has upper not subs_type substype() { return all?ALL_SUBS:ANY_SUBS; } - trans_res select_transformer(JOIN *join); + bool select_transformer(JOIN *join); void create_comp_func(bool invert) { func= func_creator(invert); } virtual void print(String *str, enum_query_type query_type); }; -- cgit v1.2.1 From 019256c9fc857bb89b1ca2ccc232f5ae4a67b992 Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Fri, 18 Feb 2011 23:31:01 +0100 Subject: Fix numerous warnings introduced in the last pushes on Windows --- sql/item_subselect.h | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 8d590bc4273..0484ce6c9bf 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -1009,7 +1009,7 @@ public: void set_null(rownum_t row_num) { - bitmap_set_bit(&null_key, row_num); + bitmap_set_bit(&null_key, (uint)row_num); } bool is_null(rownum_t row_num) { @@ -1025,7 +1025,7 @@ public: } if (row_num > max_null_row || row_num < min_null_row) return FALSE; - return bitmap_is_set(&null_key, row_num); + return bitmap_is_set(&null_key, (uint)row_num); } void print(String *str); }; -- cgit v1.2.1 From 792c67ff618f7fa5edb39563b0ccf48b4a30db0f Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sat, 19 Mar 2011 12:27:08 +0300 Subject: MWL#90: Address review feedback part #1 --- sql/item_subselect.h | 5 ----- 1 file changed, 5 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 772196a4ea3..39780708e60 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -406,11 +406,6 @@ public: */ bool is_flattenable_semijoin; - /* - Cost to populate the temporary table (set on if-needed basis). - */ - //double startup_cost; - bool *get_cond_guard(int i) { return pushed_cond_guards ? pushed_cond_guards + i : NULL; -- cgit v1.2.1 From b77e3dc9f403cea4d8ccebf87267096f5194d31c Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 22 Mar 2011 13:09:55 +0300 Subject: MWL#90: Address review feedback part #5 --- sql/item_subselect.h | 113 ++++++++++++++++++++++++--------------------------- 1 file changed, 54 insertions(+), 59 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 39780708e60..b3b4c717f38 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -36,6 +36,22 @@ class Item_subselect :public Item_result_field protected: /* thread handler, will be assigned in fix_fields only */ THD *thd; + /* old engine if engine was changed */ + subselect_engine *old_engine; + /* cache of used external tables */ + table_map used_tables_cache; + /* allowed number of columns (1 for single value subqueries) */ + uint max_columns; + /* where subquery is placed */ + enum_parsing_place parsing_place; + /* work with 'substitution' */ + bool have_to_be_excluded; + /* cache of constant state */ + bool const_item_cache; + + bool inside_first_fix_fields; + bool done_first_fix_fields; +public: /* Used inside Item_subselect::fix_fields() according to this scenario: > Item_subselect::fix_fields @@ -49,30 +65,12 @@ protected: substitution= NULL; < Item_subselect::fix_fields */ -public: Item *substitution; /* unit of subquery */ st_select_lex_unit *unit; Item *expr_cache; /* engine that perform execution of subselect (single select or union) */ subselect_engine *engine; -protected: - /* old engine if engine was changed */ - subselect_engine *old_engine; - /* cache of used external tables */ - table_map used_tables_cache; - /* allowed number of columns (1 for single value subqueries) */ - uint max_columns; - /* where subquery is placed */ - enum_parsing_place parsing_place; - /* work with 'substitution' */ - bool have_to_be_excluded; - /* cache of constant state */ - bool const_item_cache; - - bool inside_first_fix_fields; - bool done_first_fix_fields; -public: /* A reference from inside subquery predicate to somewhere outside of it */ class Ref_to_outside : public Sql_alloc { @@ -774,61 +772,26 @@ public: of subselect_single_select_engine::[prepare | cols]. */ subselect_single_select_engine *materialize_engine; -protected: - /* The engine used to compute the IN predicate. */ - subselect_engine *lookup_engine; /* QEP to execute the subquery and materialize its result into a temporary table. Created during the first call to exec(). */ -public: JOIN *materialize_join; -protected: - /* Keyparts of the only non-NULL composite index in a rowid merge. */ - MY_BITMAP non_null_key_parts; - /* Keyparts of the single column indexes with NULL, one keypart per index. */ - MY_BITMAP partial_match_key_parts; - uint count_partial_match_columns; - uint count_null_only_columns; /* A conjunction of all the equality condtions between all pairs of expressions that are arguments of an IN predicate. We need these to post-filter some IN results because index lookups sometimes match values that are actually not equal to the search key in SQL terms. - */ -public: + */ Item_cond_and *semi_join_conds; -protected: - /* Possible execution strategies that can be used to compute hash semi-join.*/ - enum exec_strategy { - UNDEFINED, - COMPLETE_MATCH, /* Use regular index lookups. */ - PARTIAL_MATCH, /* Use some partial matching strategy. */ - PARTIAL_MATCH_MERGE, /* Use partial matching through index merging. */ - PARTIAL_MATCH_SCAN, /* Use partial matching through table scan. */ - IMPOSSIBLE /* Subquery materialization is not applicable. */ - }; - /* The chosen execution strategy. Computed after materialization. */ - exec_strategy strategy; -protected: - exec_strategy get_strategy_using_schema(); - exec_strategy get_strategy_using_data(); - ulonglong rowid_merge_buff_size(bool has_non_null_key, - bool has_covering_null_row, - MY_BITMAP *partial_match_key_parts); - void choose_partial_match_strategy(bool has_non_null_key, - bool has_covering_null_row, - MY_BITMAP *partial_match_key_parts); - bool make_semi_join_conds(); - subselect_uniquesubquery_engine* make_unique_engine(); -public: subselect_hash_sj_engine(THD *thd, Item_subselect *in_predicate, subselect_single_select_engine *old_engine) - :subselect_engine(thd, in_predicate, NULL), tmp_table(NULL), - is_materialized(FALSE), materialize_engine(old_engine), lookup_engine(NULL), - materialize_join(NULL), count_partial_match_columns(0), - count_null_only_columns(0), semi_join_conds(NULL), strategy(UNDEFINED) + : subselect_engine(thd, in_predicate, NULL), + tmp_table(NULL), is_materialized(FALSE), materialize_engine(old_engine), + materialize_join(NULL), semi_join_conds(NULL), lookup_engine(NULL), + count_partial_match_columns(0), count_null_only_columns(0), + strategy(UNDEFINED) {} ~subselect_hash_sj_engine(); @@ -856,6 +819,38 @@ public: //=>base class bool change_result(Item_subselect *si, select_result_interceptor *result); bool no_tables();//=>base class + +protected: + /* The engine used to compute the IN predicate. */ + subselect_engine *lookup_engine; + /* Keyparts of the only non-NULL composite index in a rowid merge. */ + MY_BITMAP non_null_key_parts; + /* Keyparts of the single column indexes with NULL, one keypart per index. */ + MY_BITMAP partial_match_key_parts; + uint count_partial_match_columns; + uint count_null_only_columns; + /* Possible execution strategies that can be used to compute hash semi-join.*/ + enum exec_strategy { + UNDEFINED, + COMPLETE_MATCH, /* Use regular index lookups. */ + PARTIAL_MATCH, /* Use some partial matching strategy. */ + PARTIAL_MATCH_MERGE, /* Use partial matching through index merging. */ + PARTIAL_MATCH_SCAN, /* Use partial matching through table scan. */ + IMPOSSIBLE /* Subquery materialization is not applicable. */ + }; + /* The chosen execution strategy. Computed after materialization. */ + exec_strategy strategy; + exec_strategy get_strategy_using_schema(); + exec_strategy get_strategy_using_data(); + ulonglong rowid_merge_buff_size(bool has_non_null_key, + bool has_covering_null_row, + MY_BITMAP *partial_match_key_parts); + void choose_partial_match_strategy(bool has_non_null_key, + bool has_covering_null_row, + MY_BITMAP *partial_match_key_parts); + bool make_semi_join_conds(); + subselect_uniquesubquery_engine* make_unique_engine(); + }; -- cgit v1.2.1 From dc937fb53b33fc7c58612bf3f7ac1ab189393729 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 29 Mar 2011 14:33:59 +0400 Subject: MWL#90: Address review feedback part #16 --- sql/item_subselect.h | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index b3b4c717f38..2a35f5a8079 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -313,6 +313,8 @@ public: }; +TABLE_LIST * const NO_JOIN_NEST=(TABLE_LIST*)0x1; + /** Representation of IN subquery predicates of the form "left_expr IN (SELECT ...)". @@ -351,12 +353,10 @@ protected: all JOIN in UNION */ Item *expr; -public: - Item_in_optimizer *optimizer; -protected: bool was_null; bool abort_on_null; public: + Item_in_optimizer *optimizer; /* Used to trigger on/off conditions that were pushed down to subselect */ bool *pushed_cond_guards; @@ -365,7 +365,7 @@ public: /* Used by subquery optimizations to keep track about in which clause this subquery predicate is located: - (TABLE_LIST*) 1 - the predicate is an AND-part of the WHERE + NO_JOIN_NEST - the predicate is an AND-part of the WHERE join nest pointer - the predicate is an AND-part of ON expression of a join nest NULL - for all other locations @@ -377,7 +377,7 @@ public: - pointer to join nest if the subquery predicate is in the ON expression - (TABLE_LIST*)1 if the predicate is in the WHERE. */ - TABLE_LIST *expr_join_nest; + //TABLE_LIST *expr_join_nest; /* Types of left_expr and subquery's select list allow to perform subquery materialization. Currently, we set this to FALSE when it as well could @@ -420,7 +420,7 @@ public: Item_in_subselect(Item * left_expr, st_select_lex *select_lex); Item_in_subselect() :Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE), - is_constant(FALSE), optimizer(0), abort_on_null(0), + is_constant(FALSE), abort_on_null(0), optimizer(0), pushed_cond_guards(NULL), exec_method(NOT_TRANSFORMED), upper_item(0) {} void cleanup(); -- cgit v1.2.1 From 151207134b710b94e3e061311fed072d5fcc82ea Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 5 Apr 2011 12:20:03 +0400 Subject: MWL#90: Address review feedback part #18 --- sql/item_subselect.h | 18 ++++++++++++------ 1 file changed, 12 insertions(+), 6 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 559e8747068..339b264141a 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -371,12 +371,6 @@ public: See also THD::emb_on_expr_nest. */ TABLE_LIST *emb_on_expr_nest; - /* - Location of the subquery predicate. It is either - - pointer to join nest if the subquery predicate is in the ON expression - - (TABLE_LIST*)1 if the predicate is in the WHERE. - */ - //TABLE_LIST *expr_join_nest; /* Types of left_expr and subquery's select list allow to perform subquery materialization. Currently, we set this to FALSE when it as well could @@ -402,6 +396,18 @@ public: TRUE<=>this is a flattenable semi-join, false overwise. */ bool is_flattenable_semijoin; + + /* + Used to determine how this subselect item is represented in the item tree, + in case there is a need to locate it there and replace with something else. + Two options are possible: + 1. This item is there 'as-is'. + 1. This item is wrapped within Item_in_optimizer. + */ + Item *original_item() + { + return is_flattenable_semijoin ? (Item*)this : (Item*)optimizer; + } bool *get_cond_guard(int i) { -- cgit v1.2.1 From 7b797fe66d7167a8f5cbb071a06a640a627f2186 Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Wed, 4 May 2011 18:08:58 +0300 Subject: Moving max/min optimization from prepare to optimization phase. MWL#148 mysql-test/r/explain.result: fixed results (new item) mysql-test/r/subselect.result: fixed results (new item) mysql-test/r/subselect_no_mat.result: fixed results (new item) mysql-test/r/subselect_no_opts.result: fixed results (new item) mysql-test/r/subselect_no_semijoin.result: Fixed results (new item) mysql-test/suite/pbxt/r/subselect.result: Fixed results (new item) mysql-test/t/explain.test: Fixed results (correct behaviour) sql/item_cmpfunc.cc: Pass through for max/min sql/item_subselect.cc: moving max/min sql/item_subselect.h: moving max/min sql/mysql_priv.h: new uncacheble flags added sql/opt_subselect.cc: maxmin moved. sql/opt_subselect.h: New function for maxmin. sql/sql_class.h: debug code sql/sql_lex.cc: Fixed flags. Limit setting fixed. sql/sql_lex.h: 2 new flags. sql/sql_select.cc: Prepare divided on 2 function to be able recollect some info after transformation. sql/sql_select.h: Prepare divided on 2 functions. --- sql/item_subselect.h | 34 ++++++++++++++++++++++++++-------- 1 file changed, 26 insertions(+), 8 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 740daeecf13..0c02713938e 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -32,7 +32,8 @@ class Cached_item; class Item_subselect :public Item_result_field { - bool value_assigned; /* value already assigned to subselect */ + bool value_assigned; /* value already assigned to subselect */ + bool borrowed_engine; /* the engine was taken from other Item_subselect */ protected: /* thread handler, will be assigned in fix_fields only */ THD *thd; @@ -356,6 +357,9 @@ public: /* Partial matching substrategies of MATERIALIZATION. */ #define SUBS_PARTIAL_MATCH_ROWID_MERGE 8 #define SUBS_PARTIAL_MATCH_TABLE_SCAN 16 +/* ALL/ANY will be transformed with max/min optimization */ +#define SUBS_MAXMIN 32 + /** Representation of IN subquery predicates of the form @@ -486,6 +490,7 @@ public: bool test_limit(st_select_lex_unit *unit); virtual void print(String *str, enum_query_type query_type); bool fix_fields(THD *thd, Item **ref); + void fix_length_and_dec(); void fix_after_pullout(st_select_lex *new_parent, Item **ref); void update_used_tables(); bool setup_mat_engine(); @@ -523,6 +528,8 @@ public: bool select_transformer(JOIN *join); void create_comp_func(bool invert) { func= func_creator(invert); } virtual void print(String *str, enum_query_type query_type); + bool is_maxmin_applicable(JOIN *join); + bool transform_allany(JOIN *join); }; @@ -594,7 +601,8 @@ public: static table_map calc_const_tables(TABLE_LIST *); virtual void print(String *str, enum_query_type query_type)= 0; virtual bool change_result(Item_subselect *si, - select_result_interceptor *result)= 0; + select_result_interceptor *result, + bool temp= FALSE)= 0; virtual bool no_tables()= 0; virtual bool is_executed() const { return FALSE; } /* Check if subquery produced any rows during last query execution */ @@ -626,7 +634,9 @@ public: void exclude(); table_map upper_select_const_tables(); virtual void print (String *str, enum_query_type query_type); - bool change_result(Item_subselect *si, select_result_interceptor *result); + bool change_result(Item_subselect *si, + select_result_interceptor *result, + bool temp); bool no_tables(); bool may_be_null(); bool is_executed() const { return executed; } @@ -655,7 +665,9 @@ public: void exclude(); table_map upper_select_const_tables(); virtual void print (String *str, enum_query_type query_type); - bool change_result(Item_subselect *si, select_result_interceptor *result); + bool change_result(Item_subselect *si, + select_result_interceptor *result, + bool temp= FALSE); bool no_tables(); bool is_executed() const; bool no_rows(); @@ -707,11 +719,13 @@ public: void fix_length_and_dec(Item_cache** row); int exec(); uint cols() { return 1; } - uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; } + uint8 uncacheable() { return UNCACHEABLE_DEPENDENT_INJECTED; } void exclude(); table_map upper_select_const_tables() { return 0; } virtual void print (String *str, enum_query_type query_type); - bool change_result(Item_subselect *si, select_result_interceptor *result); + bool change_result(Item_subselect *si, + select_result_interceptor *result, + bool temp= FALSE); bool no_tables(); int index_lookup(); /* TIMOUR: this method needs refactoring. */ int scan_table(); @@ -879,7 +893,9 @@ public: void fix_length_and_dec(Item_cache** row);//=>base class void exclude(); //=>base class //=>base class - bool change_result(Item_subselect *si, select_result_interceptor *result); + bool change_result(Item_subselect *si, + select_result_interceptor *result, + bool temp= FALSE); bool no_tables();//=>base class }; @@ -1106,7 +1122,9 @@ public: uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; } void exclude() {} table_map upper_select_const_tables() { return 0; } - bool change_result(Item_subselect*, select_result_interceptor*) + bool change_result(Item_subselect*, + select_result_interceptor*, + bool temp= FALSE) { DBUG_ASSERT(FALSE); return false; } bool no_tables() { return false; } bool no_rows() -- cgit v1.2.1 From 524e6aad73b288c16e1dd37989f808f575c14295 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 9 May 2011 10:35:55 +0100 Subject: MWL#90: Non-merged semi-joins - Take into account that grouping or aggregates decrease join output cardinality. (First code, can't make use of index statistics yet) --- sql/item_subselect.h | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 339b264141a..e1794278220 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -147,7 +147,7 @@ public: bool mark_as_dependent(THD *thd, st_select_lex *select, Item *item); void fix_after_pullout(st_select_lex *new_parent, Item **ref); void recalc_used_tables(st_select_lex *new_parent, bool after_pullout); - virtual int optimize(); + virtual int optimize(double *out_rows, double *cost); virtual bool exec(); virtual void fix_length_and_dec(); table_map used_tables() const; @@ -534,7 +534,7 @@ public: THD * get_thd() { return thd; } virtual int prepare()= 0; virtual void fix_length_and_dec(Item_cache** row)= 0; - virtual int optimize() { DBUG_ASSERT(0); return 0; } + virtual int optimize(double *out_rows, double *cost) { DBUG_ASSERT(0); return 0; } /* Execute the engine @@ -804,7 +804,7 @@ public: bool init_runtime(); void cleanup(); int prepare() { return 0; } /* Override virtual function in base class. */ - int optimize(); + int optimize(double *out_rows, double *cost); int exec(); virtual void print(String *str, enum_query_type query_type); uint cols() -- cgit v1.2.1 From 18d08eeacc8010e04b81b6d890456fd90c47bf75 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 12 May 2011 00:14:15 +0300 Subject: Post review fixes of MWL#148 (moving max/min optimization in optimize phase). sql/item_subselect.cc: Cleanup. Comments added. sql/item_subselect.h: Cleanup. sql/mysql_priv.h: Comments added. sql/opt_subselect.cc: The function renamed and turned to method. Comments added. sql/opt_subselect.h: The function turned to method of JOIN. sql/sql_select.cc: Comment added. The function turned to method. sql/sql_select.h: The function turned to method. --- sql/item_subselect.h | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 0c02713938e..2c357b0da20 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -33,7 +33,7 @@ class Cached_item; class Item_subselect :public Item_result_field { bool value_assigned; /* value already assigned to subselect */ - bool borrowed_engine; /* the engine was taken from other Item_subselect */ + bool own_engine; /* the engine was not taken from other Item_subselect */ protected: /* thread handler, will be assigned in fix_fields only */ THD *thd; @@ -318,6 +318,8 @@ class Item_exists_subselect :public Item_subselect protected: bool value; /* value of this item (boolean: exists/not-exists) */ + void init_length_and_dec(); + public: Item_exists_subselect(st_select_lex *select_lex); Item_exists_subselect(): Item_subselect() {} @@ -529,7 +531,7 @@ public: void create_comp_func(bool invert) { func= func_creator(invert); } virtual void print(String *str, enum_query_type query_type); bool is_maxmin_applicable(JOIN *join); - bool transform_allany(JOIN *join); + bool transform_into_max_min(JOIN *join); }; -- cgit v1.2.1 From 4a9c027ad85597e5bcd42b59245a743366af476a Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 17 May 2011 00:00:11 +0300 Subject: Fix LP BUG#778413 Analysis: The subquery is evaluated first during ref-optimization of the outer query because the subquery is considered constant from the perspective of the outer query. Thus an attempt is made to evaluate the MAX subquery and use the new constant to drive an index nested loops join. During this evaluation the inner-most subquery replaces the JOIN_TAB with a new one that fetches the data from a temp table. The function select_describe crashes at the lines: TABLE_LIST *real_table= table->pos_in_table_list; item_list.push_back(new Item_string(real_table->alias, strlen(real_table->alias), cs)); because 'table' is a temp table, and it has no corresponding table reference. This 'real_table' is NULL, and real_table->alias results in a crash. Solution: In the spirit of MWL#89 prevent the evaluation of expensive predicates during optimization. This patch prevents the evaluation of expensive predicates during ref optimization. sql/item_subselect.h: Remove unused class member. Not needed for the fix, but noticed now and removed. --- sql/item_subselect.h | 1 - 1 file changed, 1 deletion(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 2c357b0da20..8d2580cc604 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -619,7 +619,6 @@ protected: class subselect_single_select_engine: public subselect_engine { bool prepared; /* simple subselect is prepared */ - bool optimized; /* simple subselect is optimized */ bool executed; /* simple subselect is executed */ st_select_lex *select_lex; /* corresponding select_lex */ JOIN * join; /* corresponding JOIN structure */ -- cgit v1.2.1 From 742dfc92a2de5ae9bc8b4d115d2bbd6dbb401bcc Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 23 May 2011 10:56:05 +0300 Subject: MWL#89: Address review feedback (by Sergey Petrunia) mysql-test/r/subselect4.result: Moved test case for LP BUG#718593 into the correct test file subselect_mat_cost_bugs.test. mysql-test/t/subselect4.test: Moved test case for LP BUG#718593 into the correct test file subselect_mat_cost_bugs.test. --- sql/item_subselect.h | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 8d2580cc604..1b1be973cf9 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -464,7 +464,7 @@ public: Item_in_subselect() :Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE), optimizer(0), abort_on_null(0), - pushed_cond_guards(NULL), func(NULL), in_strategy(0), + pushed_cond_guards(NULL), func(NULL), in_strategy(SUBS_NOT_TRANSFORMED), upper_item(0) {} void cleanup(); -- cgit v1.2.1 From 77c4c4d8ea042fc1f3cb16aa0681e8516c0cc923 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sat, 28 May 2011 20:34:04 +0400 Subject: post-merge fixes: get MWL#90 code to work with MWL#89's way of processing prepared statements: - conversion subquery_predicate -> TABLE_LIST is once per-statement - However, the code must take into account that materialized temptable is dropped and re-created on each execution (the tricky part is that at start of n-th EXECUTE we have TABLE_LIST object but not its TABLE object) - IN-equality is injected into WHERE on every execution. --- sql/item_subselect.h | 2 ++ 1 file changed, 2 insertions(+) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index ad98949f78e..bb1198eb054 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -839,6 +839,8 @@ public: not equal to the search key in SQL terms. */ Item_cond_and *semi_join_conds; + Name_resolution_context *semi_join_conds_context; + subselect_hash_sj_engine(THD *thd, Item_subselect *in_predicate, subselect_single_select_engine *old_engine) -- cgit v1.2.1 From 037e16583e07ae22a3c809cfd1e2ba3ff41d8b5f Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sun, 29 May 2011 13:22:38 +0400 Subject: Remove garbage comments --- sql/item_subselect.h | 3 --- 1 file changed, 3 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index deae1cb6ca4..8358cebf07d 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -160,7 +160,6 @@ public: bool mark_as_dependent(THD *thd, st_select_lex *select, Item *item); void fix_after_pullout(st_select_lex *new_parent, Item **ref); void recalc_used_tables(st_select_lex *new_parent, bool after_pullout); - //virtual int optimize(double *out_rows, double *cost); virtual bool exec(); /* If subquery optimization or execution determines that the subquery has @@ -593,7 +592,6 @@ public: THD * get_thd() { return thd; } virtual int prepare()= 0; virtual void fix_length_and_dec(Item_cache** row)= 0; - //virtual int optimize(double *out_rows, double *cost) { DBUG_ASSERT(0); return 0; } /* Execute the engine @@ -870,7 +868,6 @@ public: bool init(List *tmp_columns, uint subquery_id); void cleanup(); int prepare(); - //int optimize(double *out_rows, double *cost); int exec(); virtual void print(String *str, enum_query_type query_type); uint cols() -- cgit v1.2.1 From a02682abcc53199e0110ec9f24f2063fa21bd6b5 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 21 Jun 2011 15:50:07 +0300 Subject: MWL#89 - Added regression test with queries over the WORLD database. - Discovered and fixed several bugs in the related cost calculation functionality both in the semijoin and non-semijon subquery code. - Added DBUG printing of the cost variables used to decide between IN-EXISTS and MATERIALIZATION. --- sql/item_subselect.h | 25 +++++++++++++++---------- 1 file changed, 15 insertions(+), 10 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 8358cebf07d..82030bffa91 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -52,6 +52,17 @@ protected: bool inside_first_fix_fields; bool done_first_fix_fields; + Item *expr_cache; + /* + Set to TRUE if at optimization or execution time we determine that this + item's value is a constant. We need this member because it is not possible + to substitute 'this' with a constant item. + */ + bool forced_const; +#ifndef DBUG_OFF + /* Count the number of times this subquery predicate has been executed. */ + uint exec_counter; +#endif public: /* Used inside Item_subselect::fix_fields() according to this scenario: @@ -66,19 +77,13 @@ public: substitution= NULL; < Item_subselect::fix_fields */ + /* TODO make this protected member again. */ Item *substitution; - /* unit of subquery */ - st_select_lex_unit *unit; - Item *expr_cache; /* engine that perform execution of subselect (single select or union) */ + /* TODO make this protected member again. */ subselect_engine *engine; - /* - Set to TRUE if at optimization or execution time we determine that this - item's value is a constant. We need this member because it is not possible - to substitute 'this' with a constant item. - */ - bool forced_const; - + /* unit of subquery */ + st_select_lex_unit *unit; /* A reference from inside subquery predicate to somewhere outside of it */ class Ref_to_outside : public Sql_alloc { -- cgit v1.2.1 From 990584d73a736c19439d249e0936a354deffb59b Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 13 Jul 2011 17:09:09 +0300 Subject: Fixed bug lp:809245 In addition to the bug fix explained below, the patch performs few renames, and adds some comments to avoid similar problems. Analysis: The failed assert was due to a bug in MWL#68, where it was incorrectly assumed that the size of the bitmap subselect_rowid_merge_engine::null_only_columns should be the same as the size of the array of Ordered_keys. The bitmap null_only_columns contains bits to mark columns that contain only NULLs. Therefore the indexes of the bits to be set in null_only_columns are different from the indexes of the Ordered_keys. If there is a NULL-only column that appears in a table after the last partial match column with Ordered_key, this NULL-only column would require setting a bit with index bigger than the size of the bitmap null_only_columns. Accessing such a bit caused the failed assert. Solution: Upon analysis, it turns out that null_only_columns is not needed at all, because we are looking for partial matches, and having such columns guarantees that there is a partial match for any corresponding outer value. Therefore the patch removes subselect_rowid_merge_engine::null_only_columns. --- sql/item_subselect.h | 15 +++++---------- 1 file changed, 5 insertions(+), 10 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 26a45a14dbe..42be1635b6f 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -1191,11 +1191,6 @@ protected: outer reference. */ MY_BITMAP matching_outer_cols; - /* - Columns that consist of only NULLs. Such columns match any value. - Computed once per query execution. - */ - MY_BITMAP null_only_columns; /* Indexes of row numbers, sorted by . If an index may contain NULLs, the NULLs are stored efficiently in a bitmap. @@ -1205,13 +1200,13 @@ protected: non-NULL columns, it is contained in keys[0]. */ Ordered_key **merge_keys; - /* The number of elements in keys. */ - uint keys_count; + /* The number of elements in merge_keys. */ + uint merge_keys_count; /* An index on all non-NULL columns of 'tmp_table'. The index has the logical form: <[v_i1 | ... | v_ik], rownum>. It allows to find the row number where the columns c_i1,...,c1_k contain the values v_i1,...,v_ik. - If such an index exists, it is always the first element of 'keys'. + If such an index exists, it is always the first element of 'merge_keys'. */ Ordered_key *non_null_key; /* @@ -1236,7 +1231,7 @@ protected: public: subselect_rowid_merge_engine(THD *thd_arg, subselect_uniquesubquery_engine *engine_arg, - TABLE *tmp_table_arg, uint keys_count_arg, + TABLE *tmp_table_arg, uint merge_keys_count_arg, uint covering_null_row_width_arg, Item_subselect *item_arg, select_result_interceptor *result_arg, @@ -1244,7 +1239,7 @@ public: :subselect_partial_match_engine(thd_arg, engine_arg, tmp_table_arg, item_arg, result_arg, equi_join_conds_arg, covering_null_row_width_arg), - keys_count(keys_count_arg), non_null_key(NULL) + merge_keys_count(merge_keys_count_arg), non_null_key(NULL) {} ~subselect_rowid_merge_engine(); bool init(MY_BITMAP *non_null_key_parts, MY_BITMAP *partial_match_key_parts); -- cgit v1.2.1 From 1e6bd6b4df9d1302ce37a7b30af4e3ade8acce78 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 14 Jul 2011 00:15:07 +0300 Subject: Fix bug lp:809266 Analysis: This is a bug in MWL#68, where it was incorrectly assumed that if there is a match in the only non-null key, then if there is a covering NULL row on all remaining NULL-able columns there is a partial match. However, this is not the case, because even if there is such a null-only sub-row, it is not guaranteed to be part of the matched sub-row. The matched sub-row and the NULL-only sub-row may be parts of different rows. In fact there are two cases: - there is a complete row with only NULL values, and - all nullable columns contain only NULL values. These two cases were incorrectly mixed up in the class member subselect_partial_match_engine::covering_null_row_width. Solution: The solution is to: - split covering_null_row_width into two members: has_covering_null_row, and has_covering_null_columns, and - take into account each state during initialization and execution. --- sql/item_subselect.h | 27 +++++++++++++++++++-------- 1 file changed, 19 insertions(+), 8 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 42be1635b6f..8a84d446208 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -1131,11 +1131,18 @@ protected: /* A list of equalities between each pair of IN operands. */ List *equi_join_conds; /* - If there is a row, such that all its NULL-able components are NULL, this - member is set to the number of covered columns. If there is no covering - row, then this is 0. + True if there is an all NULL row in tmp_table. If so, then if there is + no complete match, there is a guaranteed partial match. */ - uint covering_null_row_width; + bool has_covering_null_row; + + /* + True if all nullable columns of tmp_table consist of only NULL values. + If so, then if there is a match in the non-null columns, there is a + guaranteed partial match. + */ + bool has_covering_null_columns; + protected: virtual bool partial_match()= 0; public: @@ -1144,7 +1151,8 @@ public: TABLE *tmp_table_arg, Item_subselect *item_arg, select_result_interceptor *result_arg, List *equi_join_conds_arg, - uint covering_null_row_width_arg); + bool has_covering_null_row_arg, + bool has_covering_null_columns_arg); int prepare() { return 0; } int exec(); void fix_length_and_dec(Item_cache**) {} @@ -1232,13 +1240,15 @@ public: subselect_rowid_merge_engine(THD *thd_arg, subselect_uniquesubquery_engine *engine_arg, TABLE *tmp_table_arg, uint merge_keys_count_arg, - uint covering_null_row_width_arg, + bool has_covering_null_row_arg, + bool has_covering_null_columns_arg, Item_subselect *item_arg, select_result_interceptor *result_arg, List *equi_join_conds_arg) :subselect_partial_match_engine(thd_arg, engine_arg, tmp_table_arg, item_arg, result_arg, equi_join_conds_arg, - covering_null_row_width_arg), + has_covering_null_row_arg, + has_covering_null_columns_arg), merge_keys_count(merge_keys_count_arg), non_null_key(NULL) {} ~subselect_rowid_merge_engine(); @@ -1258,7 +1268,8 @@ public: TABLE *tmp_table_arg, Item_subselect *item_arg, select_result_interceptor *result_arg, List *equi_join_conds_arg, - uint covering_null_row_width_arg); + bool has_covering_null_row_arg, + bool has_covering_null_columns_arg); void cleanup(); virtual enum_engine_type engine_type() { return TABLE_SCAN_ENGINE; } }; -- cgit v1.2.1 From d37465a9cc458ab215105de22875ce0a64c0efc2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 16 Jul 2011 23:57:43 -0700 Subject: Fixed LP bug #794901. Also: 1. simplified the code of the function mysql_derived_merge_for_insert. 2. moved merge of views/dt for multi-update/delete to the prepare stage. 3. the list of the references to the candidates for semi-join now is allocated in the statement memory. --- sql/item_subselect.h | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 26a45a14dbe..02239ad77d3 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -458,6 +458,11 @@ public: */ bool is_flattenable_semijoin; + /* + TRUE<=>registered in the list of semijoins in outer select + */ + bool is_registered_semijoin; + /* Used to determine how this subselect item is represented in the item tree, in case there is a need to locate it there and replace with something else. -- cgit v1.2.1 From c9e236828e3e00a29abf4865ec0a852d3754ec0c Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 18 Jul 2011 23:45:38 +0300 Subject: Fix bug lp:782305 Analysis: Both the wrong result and the valgrind warning were a result of incomplete cleanup of the MIN/MAX subquery rewrite. At the first execution of the query, the non-aggregate subquery is transformed into an aggregate MIN/MAX subquery. During the fix_fields phase of the MIN/MAX function, it sets the property st_select_lex::with_sum_func to true. The second execution of the query finds this flag to be ON. When optimization reaches the same MIN/MAX subquery transformation, it tests if the subquery is an aggregate or not. Since select_lex->with_sum_func == true from the previous execution, the transformation executes the second branch that handles aggregate subqueries. This substitutes the subquery Item into a Item_maxmin_subselect. At the same time elsewhere it is assumed that the subquery Item is of type Item_allany_subselect. Ultimately this results in casting the actual object to the wrong class, and calling the wrong any_value() method from empty_underlying_subquery(). Solution: Cleanup the st_select_lex::with_sum_func property in the case when the MIN/MAX transformation was performed for a non-aggregate subquery, so that the transformation can be repeated. --- sql/item_subselect.h | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index a192bb48f5c..5eb24bd311e 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -366,7 +366,10 @@ TABLE_LIST * const NO_JOIN_NEST=(TABLE_LIST*)0x1; #define SUBS_PARTIAL_MATCH_ROWID_MERGE 8 #define SUBS_PARTIAL_MATCH_TABLE_SCAN 16 /* ALL/ANY will be transformed with max/min optimization */ -#define SUBS_MAXMIN 32 +/* The subquery has not aggregates, transform it into a MAX/MIN query. */ +#define SUBS_MAXMIN_INJECTED 32 +/* The subquery has aggregates, use a special max/min subselect engine. */ +#define SUBS_MAXMIN_ENGINE 64 /** @@ -555,6 +558,7 @@ public: Item_allany_subselect(Item * left_expr, chooser_compare_func_creator fc, st_select_lex *select_lex, bool all); + void cleanup(); // only ALL subquery has upper not subs_type substype() { return all?ALL_SUBS:ANY_SUBS; } bool select_transformer(JOIN *join); -- cgit v1.2.1 From 99cce18955dfb43d7d69c7de704cb29f047f8da5 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 19 Jul 2011 23:19:10 +0300 Subject: Fixed LP BUG#800696. The problem was that optimizer removes some outer references (it they are constant for example) and the list of outer items built during prepare phase is not actual during execution phase when we need it as the cache parameters. First solution was use pointer on pointer on outer reference Item and initialize temporary table on demand. This solved most problem except case when optimiser also reduce Item which contains outer references ('OR' in this bug test suite). The solution is to build the list of outer reference items on execution phase (after optimization) on demand (just before temporary table creation) by walking Item tree and finding outer references among Item_ident (Item_field/Item_ref) and Item_sum items. Removed depends_on list (because it is not neede any mnore for the cache, in the place where it was used it replaced with upper_refs). Added processor (collect_outer_ref_processor) and get_cache_parameters() methods to collect outer references (or other expression parameters in future). mysql-test/r/subselect_cache.result: A new test added. mysql-test/r/subselect_scache.result: Changes in creating the cache and its paremeters order or adding arguments of aggregate function (which is a parameter also, but this has no influence on the result). mysql-test/t/subselect_cache.test: Added a new test. sql/item.cc: depends_on removed. Added processor (collect_outer_ref_processor) and get_cache_parameters() methods to collect outer references. Item_cache_wrapper collect parameters befor initialization of its cache. sql/item.h: depends_on removed. Added processor (collect_outer_ref_processor) and get_cache_parameters() methods to collect outer references. sql/item_cmpfunc.cc: depends_on removed. Added processor (collect_outer_ref_processor) to collect outer references. sql/item_cmpfunc.h: Added processor (collect_outer_ref_processor) to collect outer references. sql/item_subselect.cc: depends_on removed. Added processor get_cache_parameters() method to collect outer references. sql/item_subselect.h: depends_on removed. Added processor get_cache_parameters() method to collect outer references. sql/item_sum.cc: Added processor (collect_outer_ref_processor) method to collect outer references. sql/item_sum.h: Added processor (collect_outer_ref_processor) and get_cache_parameters() methods to collect outer references. sql/opt_range.cc: depends_on removed. sql/sql_base.cc: depends_on removed. sql/sql_class.h: New iterator added. sql/sql_expression_cache.cc: Build of list of items resolved in outer query done just before creating expression cache on the first execution of the subquery which removes influence of optimizer removing items (all optimization already done). sql/sql_expression_cache.h: Build of list of items resolved in outer query done just before creating expression cache on the first execution of the subquery which removes influence of optimizer removing items (all optimization already done). sql/sql_lex.cc: depends_on removed. sql/sql_lex.h: depends_on removed. sql/sql_list.h: Added add_unique method to add only unique elements to the list. sql/sql_select.cc: Support of new Item list added. sql/sql_select.h: Support of new Item list added. --- sql/item_subselect.h | 10 ++-------- 1 file changed, 2 insertions(+), 8 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 5eb24bd311e..2c1e3bddb2d 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -102,14 +102,6 @@ public: List upper_refs; st_select_lex *parent_select; - /** - List of references on items subquery depends on (externally resolved); - - @note We can't store direct links on Items because it could be - substituted with other item (for example for grouping). - */ - List depends_on; - /* TRUE<=>Table Elimination has made it redundant to evaluate this select (and so it is not part of QEP, etc) @@ -225,6 +217,7 @@ public: @retval FALSE otherwise */ bool is_expensive_processor(uchar *arg) { return TRUE; } + /** Get the SELECT_LEX structure associated with this Item. @return the SELECT_LEX structure associated with this Item @@ -232,6 +225,7 @@ public: st_select_lex* get_select_lex(); const char *func_name() const { DBUG_ASSERT(0); return "subselect"; } virtual bool expr_cache_is_needed(THD *); + virtual void get_cache_parameters(List ¶meters); friend class select_result_interceptor; friend class Item_in_optimizer; -- cgit v1.2.1