| List: | Commits | « Previous MessageNext Message » | |
| From: | Sergey Petrunia | Date: | November 15 2006 4:14am |
| Subject: | bk commit into 5.0 tree (sergefp:1.2304) BUG#24127 | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.0 repository of psergey. When psergey does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2006-11-15 06:14:17+03:00, sergefp@stripped +13 -0 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - Make the code produce correct result: use an array of triggers to turn on/off equalities for each compared column. Also turn on/off optimizations based on those equalities. - Make EXPLAIN output show "Full scan on NULL key" for tables for which we switch between ref/unique_subquery/index_subquery and ALL access. - Fix incorrect presense of "Using index" for index/unique-based subqueries (BUG#22930) // bk trigger note: this commit refers to BUG#24127 mysql-test/r/subselect.result@stripped, 2006-11-15 06:14:13+03:00, sergefp@stripped +9 -9 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - Updated test results - The changes in the result also shows that BUG#22930 is fixed. mysql-test/r/subselect2.result@stripped, 2006-11-15 06:14:13+03:00, sergefp@stripped +5 -5 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - Updated test results mysql-test/r/subselect3.result@stripped, 2006-11-15 06:14:13+03:00, sergefp@stripped +40 -3 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - Test case - Updated test results mysql-test/t/subselect3.test@stripped, 2006-11-15 06:14:13+03:00, sergefp@stripped +28 -0 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - Test case - Updated test results sql/item_cmpfunc.cc@stripped, 2006-11-15 06:14:13+03:00, sergefp@stripped +28 -5 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - For row-based IN subqueries, use one flag per each column. Set the flags appropriately before running the subquery. sql/item_cmpfunc.h@stripped, 2006-11-15 06:14:13+03:00, sergefp@stripped +1 -0 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - Added Item_func_trig_cond::marker sql/item_subselect.cc@stripped, 2006-11-15 06:14:13+03:00, sergefp@stripped +136 -81 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - For execution of row-based IN subqueries, use an array of flags to indicate which predicates are enabled. The new code does this: = create the array of flags = When we push equalities into subquery, for each column, wrap its predicate into trig_cond that corresponds to that column. = Let the execution engine find the ref-like accesses that were created from wrapped predicates and change them to 'ALL' if corresponding predicates were disabled. sql/item_subselect.h@stripped, 2006-11-15 06:14:13+03:00, sergefp@stripped +17 -13 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - For execution of row-based IN subqueries, use an array of flags to indicate which predicates are enabled. The new code does this: = create the array of flags = When we push equalities into subquery, for each column, wrap its predicate into trig_cond that corresponds to that column. = Let the execution engine find the ref-like accesses that were created from wrapped predicates and change them to 'ALL' if corresponding predicates were disabled. sql/mysql_priv.h@stripped, 2006-11-15 06:14:13+03:00, sergefp@stripped +1 -1 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - Added in_having_cond special name which is used to check if a HAVING clause of a JOIN was created by subquery optimizer sql/mysqld.cc@stripped, 2006-11-15 06:14:13+03:00, sergefp@stripped +3 -0 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - Added in_having_cond special name which is used to check if a HAVING clause of a JOIN was created by subquery optimizer sql/sql_lex.h@stripped, 2006-11-15 06:14:14+03:00, sergefp@stripped +1 -1 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) sql/sql_select.cc@stripped, 2006-11-15 06:14:14+03:00, sergefp@stripped +92 -77 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - For equalities used in ref-like acccess: Keep track if an equality was created from a triggered predicate (subquery optimizer pushes such predicates into subselects and must be able to locate them on execution phase). - Re-worked the code that converts a JOIN into (unique|index)_subquery = Remove deadcode, add comments, change the "is this a pushed-down predicate" checks to match what is pushed by subquery optimizer = Produce correct "Using index" and "Using where" (this fixes BUG#22390) - For all kinds of subqueries, show "Full scan on NULL key" in EXPLAIN where appropriate sql/sql_select.h@stripped, 2006-11-15 06:14:14+03:00, sergefp@stripped +31 -3 BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...) - Added outer_ref_col(s) to KEY_FIELD, KEYUSE, and store_key. It is used to remember which equalities were created from pushed-down triggered predicates. - Added JOIN_TAB::packed_info where we store what should be shown in 'Extra' column in EXPLAIN output for (unique|index)_subquery rows. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: sergefp # Host: newbox.mylan # Root: /home/psergey/mysql-5.0-bug8804-r10 --- 1.224/sql/item_cmpfunc.cc 2006-11-15 06:14:23 +03:00 +++ 1.225/sql/item_cmpfunc.cc 2006-11-15 06:14:23 +03:00 @@ -814,11 +814,34 @@ We disable the predicates we've pushed down into subselect, run the subselect and see if it has produced any rows. */ - ((Item_in_subselect*)args[1])->enable_pushed_conds= FALSE; - longlong tmp= args[1]->val_bool_result(); - result_for_null_param= null_value= - !((Item_in_subselect*)args[1])->engine->no_rows(); - ((Item_in_subselect*)args[1])->enable_pushed_conds= TRUE; + Item_in_subselect *item_subs=(Item_in_subselect*)args[1]; + if (cache->cols() == 1) + { + item_subs->enable_pushed_conds= FALSE; + longlong tmp= args[1]->val_bool_result(); + result_for_null_param= null_value= !item_subs->engine->no_rows(); + item_subs->enable_pushed_conds= TRUE; + } + else + { + uint ncols= cache->cols(); + /* + Turn off the predicates that are based on column compares for + which the left part is currently NULL + */ + for (uint i= 0; i < ncols; i++) + { + if (cache->el(i)->null_value) + item_subs->enable_pushed_conds_vec[i]= FALSE; + } + + longlong tmp= args[1]->val_bool_result(); + result_for_null_param= null_value= !item_subs->engine->no_rows(); + + /* Turn all predicates back on */ + for (uint i= 0; i < ncols; i++) + item_subs->enable_pushed_conds_vec[i]= TRUE; + } } } return 0; --- 1.134/sql/item_cmpfunc.h 2006-11-15 06:14:23 +03:00 +++ 1.135/sql/item_cmpfunc.h 2006-11-15 06:14:23 +03:00 @@ -307,6 +307,7 @@ { bool *trig_var; public: + int marker; /* An opaque value used by module that created this trigger */ Item_func_trig_cond(Item *a, bool *f) : Item_bool_func(a) { trig_var= f; } longlong val_int() { return *trig_var ? args[0]->val_int() : 1; } enum Functype functype() const { return TRIG_COND_FUNC; }; --- 1.418/sql/mysql_priv.h 2006-11-15 06:14:23 +03:00 +++ 1.419/sql/mysql_priv.h 2006-11-15 06:14:23 +03:00 @@ -1189,7 +1189,7 @@ extern const char *first_keyword, *my_localhost, *delayed_user, *binary_keyword; extern const char **errmesg; /* Error messages */ extern const char *myisam_recover_options_str; -extern const char *in_left_expr_name, *in_additional_cond; +extern const char *in_left_expr_name, *in_additional_cond, *in_having_cond; extern const char * const triggers_file_ext; extern const char * const trigname_file_ext; extern Eq_creator eq_creator; --- 1.576/sql/mysqld.cc 2006-11-15 06:14:23 +03:00 +++ 1.577/sql/mysqld.cc 2006-11-15 06:14:23 +03:00 @@ -444,10 +444,13 @@ const char **errmesg; /* Error messages */ const char *myisam_recover_options_str="OFF"; const char *myisam_stats_method_str="nulls_unequal"; + /* name of reference on left espression in rewritten IN subquery */ const char *in_left_expr_name= "<left expr>"; /* name of additional condition */ const char *in_additional_cond= "<IN COND>"; +const char *in_having_cond= "<IN HAVING>"; + my_decimal decimal_zero; /* classes for comparation parsing/processing */ Eq_creator eq_creator; --- 1.231/sql/sql_lex.h 2006-11-15 06:14:23 +03:00 +++ 1.232/sql/sql_lex.h 2006-11-15 06:14:23 +03:00 @@ -470,7 +470,7 @@ void set_thd(THD *thd_arg) { thd= thd_arg; } friend void lex_start(THD *thd, uchar *buf, uint length); - friend int subselect_union_engine::exec(bool); + friend int subselect_union_engine::exec(bool*); List<Item> *get_unit_column_types(); }; --- 1.470/sql/sql_select.cc 2006-11-15 06:14:23 +03:00 +++ 1.471/sql/sql_select.cc 2006-11-15 06:14:23 +03:00 @@ -514,72 +514,63 @@ /* - test if it is known for optimisation IN subquery - + Remove the predicates pushed down into the subquery SYNOPSIS - JOIN::test_in_subselect() - where - pointer for variable in which conditions should be - stored if subquery is known + JOIN::remove_subq_pushed_predicates() + where IN Must be NULL + OUT The remaining WHERE condition, or NULL - RETURN - 1 - known - 0 - unknown + DESCRIPTION + Given that this join will be executed using (unique|index)_subquery, + without "checking NULL", remove the predicates that were pushed down + into the subquery. + + We can remove the equalities that will be guaranteed to be true by the + fact that subquery engine will be using index lookup. + If the subquery compares scalar values, we can remove the condition that + was wrapped into trig_cond. If the subquery compares row values, we need + to keep the wrapped equalities in the WHERE clause: when some parts of + the left tuple are NULLs and some aren't, we'll use full table scan and + will rely on the equalities for non-NULL tuple parts to be guaranteed to + be true. */ - -bool JOIN::test_in_subselect(Item **where) +void JOIN::remove_subq_pushed_predicates(Item **where) { if (conds->type() == Item::FUNC_ITEM && ((Item_func *)this->conds)->functype() == Item_func::EQ_FUNC && ((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM && ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM) { - join_tab->info= "Using index"; *where= 0; - return 1; + return; } if (conds->type() == Item::COND_ITEM && ((class Item_func *)this->conds)->functype() == Item_func::COND_AND_FUNC) { - if ((*where= remove_additional_cond(conds))) - join_tab->info= "Using index; Using where"; - else - join_tab->info= "Using index"; - return 1; + *where= remove_additional_cond(conds); } - return 0; } -/* - Check if the passed HAVING clause is a clause added by subquery optimizer - - SYNOPSIS - is_having_subq_predicates() - having Having clause - - RETURN - TRUE The passed HAVING clause was added by the subquery optimizer - FALSE Otherwise -*/ - -bool is_having_subq_predicates(Item *having) +static void save_index_subquery_explain_info(JOIN_TAB *join_tab, Item* where) { - if (having->type() == Item::FUNC_ITEM) + join_tab->packed_info= TAB_INFO_HAVE_VALUE; + if (join_tab->table->used_keys.is_set(join_tab->ref.key)) + join_tab->packed_info |= TAB_INFO_USING_INDEX; + if (where) + join_tab->packed_info |= TAB_INFO_USING_WHERE; + for (uint i = 0; i < join_tab->ref.key_parts; i++) { - if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC) - return TRUE; - if (((Item_func *) having)->functype() == Item_func::TRIG_COND_FUNC) + if (join_tab->ref.outer_ref_cols[i] != UINT_MAX) { - having= ((Item_func*)having)->arguments()[0]; - if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC) - return TRUE; + join_tab->packed_info |= TAB_INFO_FULL_SCAN_ON_NULL; + break; } - return TRUE; } - return FALSE; } + /* global select optimisation. return 0 - success @@ -1016,46 +1007,40 @@ if (join_tab[0].type == JT_EQ_REF && join_tab[0].ref.items[0]->name == in_left_expr_name) { - if (test_in_subselect(&where)) - { - join_tab[0].type= JT_UNIQUE_SUBQUERY; - error= 0; - DBUG_RETURN(unit->item-> - change_engine(new - subselect_uniquesubquery_engine(thd, - join_tab, - unit->item, - where))); - } + remove_subq_pushed_predicates(&where); + save_index_subquery_explain_info(join_tab, where); + join_tab[0].type= JT_UNIQUE_SUBQUERY; + error= 0; + DBUG_RETURN(unit->item-> + change_engine(new + subselect_uniquesubquery_engine(thd, + join_tab, + unit->item, + where))); } else if (join_tab[0].type == JT_REF && join_tab[0].ref.items[0]->name == in_left_expr_name) { - if (test_in_subselect(&where)) - { - join_tab[0].type= JT_INDEX_SUBQUERY; - error= 0; - DBUG_RETURN(unit->item-> - change_engine(new - subselect_indexsubquery_engine(thd, - join_tab, - unit->item, - where, - 0))); - } + remove_subq_pushed_predicates(&where); + save_index_subquery_explain_info(join_tab, where); + join_tab[0].type= JT_INDEX_SUBQUERY; + error= 0; + DBUG_RETURN(unit->item-> + change_engine(new + subselect_indexsubquery_engine(thd, + join_tab, + unit->item, + where, + 0))); } } else if (join_tab[0].type == JT_REF_OR_NULL && join_tab[0].ref.items[0]->name == in_left_expr_name && - is_having_subq_predicates(having)) + having->name == in_having_cond) { join_tab[0].type= JT_INDEX_SUBQUERY; error= 0; - - if ((conds= remove_additional_cond(conds))) - join_tab->info= "Using index; Using where"; - else - join_tab->info= "Using index"; - + conds= remove_additional_cond(conds); + save_index_subquery_explain_info(join_tab, conds); DBUG_RETURN(unit->item-> change_engine(new subselect_indexsubquery_engine(thd, join_tab, @@ -2540,9 +2525,7 @@ when val IS NULL. */ bool null_rejecting; - - /* TRUE<=> This ref access is an outer subquery reference access */ - bool outer_ref; + uint outer_ref_col; /* See KEYUSE::outer_ref_col */ } KEY_FIELD; /* Values in optimize */ @@ -2841,7 +2824,7 @@ cond->functype() == Item_func::MULT_EQUAL_FUNC) && ((*value)->type() == Item::FIELD_ITEM) && ((Item_field*)*value)->field->maybe_null()); - (*key_fields)->outer_ref= FALSE; + (*key_fields)->outer_ref_col= UINT_MAX; (*key_fields)++; } @@ -2945,6 +2928,7 @@ if (cond->type() == Item::FUNC_ITEM && ((Item_func*)cond)->functype() == Item_func::TRIG_COND_FUNC) { + int col= ((Item_func_trig_cond*)cond)->marker; cond= ((Item_func*)cond)->arguments()[0]; if (!join->group_list && !join->order && join->unit->item && @@ -2956,7 +2940,7 @@ sargables); // Indicate that this ref access candidate is for subquery lookup: for (; save != *key_fields; save++) - save->outer_ref= TRUE; + save->outer_ref_col= col; } return; } @@ -3136,7 +3120,7 @@ keyuse.used_tables=key_field->val->used_tables(); keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL; keyuse.null_rejecting= key_field->null_rejecting; - keyuse.outer_ref= key_field->outer_ref; + keyuse.outer_ref_col= key_field->outer_ref_col; VOID(insert_dynamic(keyuse_array,(gptr) &keyuse)); } } @@ -4977,7 +4961,8 @@ if (!(j->ref.key_buff= (byte*) thd->calloc(ALIGN_SIZE(length)*2)) || !(j->ref.key_copy= (store_key**) thd->alloc((sizeof(store_key*) * (keyparts+1)))) || - !(j->ref.items= (Item**) thd->alloc(sizeof(Item*)*keyparts))) + !(j->ref.items= (Item**) thd->alloc(sizeof(Item*)*keyparts)) || + !(j->ref.outer_ref_cols= (uint*) thd->alloc(sizeof(uint*)*keyparts))) { DBUG_RETURN(TRUE); } @@ -4992,6 +4977,8 @@ if (ftkey) { j->ref.items[0]=((Item_func*)(keyuse->val))->key_item(); + /* Predicates pushed down into subquery can't be used FT access */ + j->ref.outer_ref_cols[0]= UINT_MAX; if (keyuse->used_tables) DBUG_RETURN(TRUE); // not supported yet. SerG @@ -5008,6 +4995,7 @@ uint maybe_null= test(keyinfo->key_part[i].null_bit); j->ref.items[i]=keyuse->val; // Save for cond removal + j->ref.outer_ref_cols[i]= keyuse->outer_ref_col; if (keyuse->null_rejecting) j->ref.null_rejecting |= 1 << i; keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; @@ -14753,6 +14741,24 @@ if (tab->info) item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs)); + else if (tab->packed_info & TAB_INFO_HAVE_VALUE) + { + if (tab->packed_info & TAB_INFO_USING_INDEX) + extra.append(STRING_WITH_LEN("; Using index")); + if (tab->packed_info & TAB_INFO_USING_WHERE) + extra.append(STRING_WITH_LEN("; Using where")); + if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL) + extra.append(STRING_WITH_LEN("; Full scan on NULL key")); + /* Skip initial "; "*/ + const char *str= extra.ptr(); + uint32 len= extra.length(); + if (len) + { + str += 2; + len -= 2; + } + item_list.push_back(new Item_string(str, len, cs)); + } else { if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || @@ -14811,6 +14817,15 @@ } if (distinct & test_all_bits(used_tables,thd->used_tables)) extra.append(STRING_WITH_LEN("; Distinct")); + + for (uint part= 0; part < tab->ref.key_parts; part++) + { + if (tab->ref.outer_ref_cols[part] != UINT_MAX) + { + extra.append(STRING_WITH_LEN("; Full scan on NULL key")); + break; + } + } /* Skip initial "; "*/ const char *str= extra.ptr(); --- 1.112/sql/sql_select.h 2006-11-15 06:14:23 +03:00 +++ 1.113/sql/sql_select.h 2006-11-15 06:14:23 +03:00 @@ -36,8 +36,15 @@ satisfied if val has NULL 'value'. */ bool null_rejecting; - /* TRUE<=> This ref access is an outer subquery reference access */ - bool outer_ref; + /* + > 0 - This element was created from a triggered condition (which will + be turned of for "NULL IN (SELECT ...)" that was pushed down into + a subselect. The number is the number of column, e.g. for + (a, b) IN (SELECT x, keypart2 ...) + we'll get a KEYUSE created for "keypart2=b" with outer_ref_col == 1. + UINT_MAX - Otherwise + */ + uint outer_ref_col; } KEYUSE; class store_key; @@ -52,6 +59,12 @@ byte *key_buff2; // key_buff+key_length store_key **key_copy; // Item **items; // val()'s for each keypart + /* + Array of numbers of pushed-down subq predicates that were used to + construct equalities on keypart #i. If equality on keypart #i was not + constructed from pushed-down predicate, outer_ref_col[i]==UINT_MAX. + */ + uint *outer_ref_cols; /* (null_rejecting & (1<<i)) means the condition is '=' and no matching rows will be produced if items[i] IS NULL (see add_not_null_conds()) @@ -100,6 +113,13 @@ NESTED_LOOP_QUERY_LIMIT= 3, NESTED_LOOP_CURSOR_LIMIT= 4 }; + +/* Values for JOIN_TAB::packed_info */ +#define TAB_INFO_HAVE_VALUE 1 +#define TAB_INFO_USING_INDEX 2 +#define TAB_INFO_USING_WHERE 4 +#define TAB_INFO_FULL_SCAN_ON_NULL 8 + typedef enum_nested_loop_state (*Next_select_func)(JOIN *, struct st_join_table *, bool); typedef int (*Read_record_func)(struct st_join_table *tab); @@ -120,7 +140,15 @@ st_join_table *last_inner; /* last table table for embedding outer join */ st_join_table *first_upper; /* first inner table for embedding outer join */ st_join_table *first_unmatched; /* used for optimization purposes only */ + + /* Special content for EXPLAIN 'Extra' column or NULL if none */ const char *info; + /* + Bitmap of TAB_INFO_* bits that encodes special line for EXPLAIN 'Extra' + column, or 0 if there is no info. + */ + uint packed_info; + Read_record_func read_first_record; Next_select_func next_select; READ_RECORD read_record; @@ -387,7 +415,7 @@ Item_sum ***func); int rollup_send_data(uint idx); int rollup_write_data(uint idx, TABLE *table); - bool test_in_subselect(Item **where); + void remove_subq_pushed_predicates(Item **where); /* Release memory and, if possible, the open tables held by this execution plan (and nested plans). It's used to release some tables before --- 1.168/mysql-test/r/subselect.result 2006-11-15 06:14:23 +03:00 +++ 1.169/mysql-test/r/subselect.result 2006-11-15 06:14:23 +03:00 @@ -1305,7 +1305,7 @@ explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (`test`.`t1`.`b` <> 30)))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); @@ -1462,25 +1462,25 @@ explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index +2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index +2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index +2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Using where; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; @@ -2817,19 +2817,19 @@ 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having trigcond((<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); @@ -3010,7 +3010,7 @@ EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 --- 1.13/mysql-test/r/subselect2.result 2006-11-15 06:14:23 +03:00 +++ 1.14/mysql-test/r/subselect2.result 2006-11-15 06:14:23 +03:00 @@ -126,11 +126,11 @@ 1 PRIMARY t2 ALL DDOCTYPEID_IDX NULL NULL NULL 9 Using where 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 -2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where -3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where -4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where -5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where -6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 Using index; Using where +2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where +3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where +4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where +5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where +6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 Using where drop table t1, t2, t3, t4; CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; INSERT INTO t1 VALUES (1),(2); --- 1.141/sql/item_subselect.cc 2006-11-15 06:14:23 +03:00 +++ 1.142/sql/item_subselect.cc 2006-11-15 06:14:23 +03:00 @@ -192,16 +192,16 @@ return res; } -bool Item_subselect::exec(bool full_scan) +bool Item_subselect::exec(bool *enabled_conds) { int res; - res= engine->exec(full_scan); + res= engine->exec(enabled_conds); if (engine_changed) { engine_changed= 0; - return exec(full_scan); + return exec(enabled_conds); } return (res); } @@ -450,13 +450,13 @@ void Item_singlerow_subselect::bring_value() { - exec(FALSE); + exec(NULL); } double Item_singlerow_subselect::val_real() { DBUG_ASSERT(fixed == 1); - if (!exec(FALSE) && !value->null_value) + if (!exec(NULL) && !value->null_value) { null_value= 0; return value->val_real(); @@ -471,7 +471,7 @@ longlong Item_singlerow_subselect::val_int() { DBUG_ASSERT(fixed == 1); - if (!exec(FALSE) && !value->null_value) + if (!exec(NULL) && !value->null_value) { null_value= 0; return value->val_int(); @@ -485,7 +485,7 @@ String *Item_singlerow_subselect::val_str(String *str) { - if (!exec(FALSE) && !value->null_value) + if (!exec(NULL) && !value->null_value) { null_value= 0; return value->val_str(str); @@ -500,7 +500,7 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value) { - if (!exec(FALSE) && !value->null_value) + if (!exec(NULL) && !value->null_value) { null_value= 0; return value->val_decimal(decimal_value); @@ -515,7 +515,7 @@ bool Item_singlerow_subselect::val_bool() { - if (!exec(FALSE) && !value->null_value) + if (!exec(NULL) && !value->null_value) { null_value= 0; return value->val_bool(); @@ -567,7 +567,8 @@ Item_in_subselect::Item_in_subselect(Item * left_exp, st_select_lex *select_lex): Item_exists_subselect(), optimizer(0), transformed(0), - enable_pushed_conds(TRUE), upper_item(0) + enable_pushed_conds(TRUE), enable_pushed_conds_vec(&enable_pushed_conds), + upper_item(0) { DBUG_ENTER("Item_in_subselect::Item_in_subselect"); left_expr= left_exp; @@ -612,7 +613,7 @@ double Item_exists_subselect::val_real() { DBUG_ASSERT(fixed == 1); - if (exec(FALSE)) + if (exec(NULL)) { reset(); return 0; @@ -623,7 +624,7 @@ longlong Item_exists_subselect::val_int() { DBUG_ASSERT(fixed == 1); - if (exec(FALSE)) + if (exec(NULL)) { reset(); return 0; @@ -634,7 +635,7 @@ String *Item_exists_subselect::val_str(String *str) { DBUG_ASSERT(fixed == 1); - if (exec(FALSE)) + if (exec(NULL)) { reset(); return 0; @@ -647,7 +648,7 @@ my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); - if (exec(FALSE)) + if (exec(NULL)) { reset(); return 0; @@ -660,7 +661,7 @@ bool Item_exists_subselect::val_bool() { DBUG_ASSERT(fixed == 1); - if (exec(FALSE)) + if (exec(NULL)) { reset(); return 0; @@ -678,7 +679,7 @@ DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec(!enable_pushed_conds)) + if (exec(enable_pushed_conds_vec)) { reset(); null_value= 1; @@ -699,7 +700,7 @@ DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec(!enable_pushed_conds)) + if (exec(enable_pushed_conds_vec)) { reset(); null_value= 1; @@ -720,7 +721,7 @@ DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec(!enable_pushed_conds)) + if (exec(enable_pushed_conds_vec)) { reset(); null_value= 1; @@ -740,7 +741,7 @@ { DBUG_ASSERT(fixed == 1); null_value= 0; - if (exec(!enable_pushed_conds)) + if (exec(enable_pushed_conds_vec)) { reset(); null_value= 1; @@ -760,7 +761,7 @@ DBUG_ASSERT(0); null_value= 0; DBUG_ASSERT(fixed == 1); - if (exec(!enable_pushed_conds)) + if (exec(enable_pushed_conds_vec)) { reset(); null_value= 1; @@ -950,6 +951,8 @@ unit->uncacheable|= UNCACHEABLE_DEPENDENT; } + if (!abort_on_null && left_expr->maybe_null) + enable_pushed_conds_vec= &enable_pushed_conds; select_lex->uncacheable|= UNCACHEABLE_DEPENDENT; /* @@ -978,9 +981,10 @@ { /* We can encounter "NULL IN (SELECT ...)". Wrap the added condition - within a trigger. + within a trig_cond. */ item= new Item_func_trig_cond(item, &enable_pushed_conds); + ((Item_func_trig_cond*)item)->marker= 0; } /* @@ -989,6 +993,8 @@ argument (reference) to fix_fields() */ select_lex->having= join->having= and_items(join->having, item); + if (join->having == item) + item->name= (char*)in_having_cond; select_lex->having_fix_field= 1; /* we do not check join->having->fixed, because Item_and (from and_items) @@ -1021,12 +1027,14 @@ if (!(having= new Item_func_trig_cond(having, &enable_pushed_conds))) DBUG_RETURN(RES_ERROR); + ((Item_func_trig_cond*)having)->marker= 0; } /* Item_is_not_null_test can't be changed during fix_fields() we can assign select_lex->having here, and pass 0 as last argument (reference) to fix_fields() */ + having->name= (char*)in_having_cond; select_lex->having= join->having= having; select_lex->having_fix_field= 1; /* @@ -1043,15 +1051,21 @@ } /* If we may encounter NULL IN (SELECT ...) and care between NULL and - FALSE, wrap it in a trigger. + FALSE, wrap it in a trig_cond */ if (!abort_on_null && left_expr->maybe_null) { if (!(item= new Item_func_trig_cond(item, &enable_pushed_conds))) DBUG_RETURN(RES_ERROR); + ((Item_func_trig_cond*)item)->marker= 0; } - + /* + TODO: figure out why the following is done here in + single_value_transformer but there is no corresponding action in + row_value_transformer? + */ item->name= (char *)in_additional_cond; + /* AND can't be changed during fix_fields() we can assign select_lex->having here, and pass 0 as last @@ -1087,9 +1101,12 @@ if (!(new_having= new Item_func_trig_cond(new_having, &enable_pushed_conds))) DBUG_RETURN(RES_ERROR); + ((Item_func_trig_cond*)new_having)->marker= 0; } + new_having->name= (char*)in_having_cond; select_lex->having= join->having= new_having; select_lex->having_fix_field= 1; + /* we do not check join->having->fixed, because comparison function (from func->create) can't be fixed after creation @@ -1159,6 +1176,15 @@ thd->lex->current_select= current; unit->uncacheable|= UNCACHEABLE_DEPENDENT; + + if (!abort_on_null && left_expr->maybe_null) + { + if (!(enable_pushed_conds_vec= (bool*)join->thd->alloc(sizeof(bool) * + left_expr->cols()))) + DBUG_RETURN(RES_ERROR); + for (uint i= 0; i < cols_num; i++) + enable_pushed_conds_vec[i]= TRUE; + } } select_lex->uncacheable|= UNCACHEABLE_DEPENDENT; @@ -1175,6 +1201,7 @@ is_not_null_test(v3)) where is_not_null_test used to register nulls in case if we have not found matching to return correct NULL value + TODO: say here explicitly if the order of AND parts matters or not. */ Item *item_having_part2= 0; for (uint i= 0; i < cols_num; i++) @@ -1203,21 +1230,32 @@ (char *)"<no matter>", (char *)"<list ref>") ); - having_item= - and_items(having_item, - new Item_cond_or(item_eq, item_isnull)); - item_having_part2= - and_items(item_having_part2, - new - Item_is_not_null_test(this, - new - Item_ref(&select_lex->context, - select_lex-> - ref_pointer_array + i, - (char *)"<no matter>", - (char *)"<list ref>") - ) - ); + Item *col_item= new Item_cond_or(item_eq, item_isnull); + if (!abort_on_null && left_expr->el(i)->maybe_null) + { + if (!(col_item= new Item_func_trig_cond(col_item, + enable_pushed_conds_vec + i))) + DBUG_RETURN(RES_ERROR); + ((Item_func_trig_cond*)col_item)->marker= i; + } + having_item= and_items(having_item, col_item); + + Item *item_nnull_test= + new Item_is_not_null_test(this, + new Item_ref(&select_lex->context, + select_lex-> + ref_pointer_array + i, + (char *)"<no matter>", + (char *)"<list ref>")); + if (!abort_on_null && left_expr->el(i)->maybe_null) + { + if (!(item_nnull_test= + new Item_func_trig_cond(item_nnull_test, + enable_pushed_conds_vec + i))) + DBUG_RETURN(RES_ERROR); + ((Item_func_trig_cond*)item_nnull_test)->marker= i; + } + item_having_part2= and_items(item_having_part2, item_nnull_test); item_having_part2->top_level_item(); } having_item= and_items(having_item, item_having_part2); @@ -1266,18 +1304,15 @@ ); if (!abort_on_null) { - having_item= - and_items(having_item, - new - Item_is_not_null_test(this, - new - Item_ref(&select_lex->context, - select_lex-> - ref_pointer_array + i, - (char *)"<no matter>", - (char *)"<list ref>") - ) - ); + Item *having_col_item= + new Item_is_not_null_test(this, + new + Item_ref(&select_lex->context, + select_lex->ref_pointer_array + i, + (char *)"<no matter>", + (char *)"<list ref>")); + + item_isnull= new Item_func_isnull(new Item_direct_ref(&select_lex->context, @@ -1286,14 +1321,28 @@ (char *)"<no matter>", (char *)"<list ref>") ); - item= new Item_cond_or(item, item_isnull); - } + /* + TODO: why we create the above for cases where the right part + cant be NULL? + */ + if (left_expr->el(i)->maybe_null) + { + if (!(item= new Item_func_trig_cond(item, + enable_pushed_conds_vec + i))) + DBUG_RETURN(RES_ERROR); + ((Item_func_trig_cond*)item)->marker= i; + if (!(having_col_item= + new Item_func_trig_cond(having_col_item, + enable_pushed_conds_vec + i))) + DBUG_RETURN(RES_ERROR); + ((Item_func_trig_cond*)having_col_item)->marker= i; + } + having_item= and_items(having_item, having_col_item); + } where_item= and_items(where_item, item); } - if (where_item) - where_item= new Item_func_trig_cond(where_item, &enable_pushed_conds); /* AND can't be changed during fix_fields() we can assign select_lex->where here, and pass 0 as last @@ -1307,9 +1356,9 @@ if (having_item) { bool res; - having_item= new Item_func_trig_cond(having_item, &enable_pushed_conds); - select_lex->having= join->having= and_items(join->having, having_item); + if (having_item == select_lex->having) + having_item->name= (char*)in_having_cond; select_lex->having->top_level_item(); /* AND can't be changed during fix_fields() @@ -1695,7 +1744,7 @@ int join_read_always_key_or_null(JOIN_TAB *tab); int join_read_next_same_or_null(READ_RECORD *info); -int subselect_single_select_engine::exec(bool full_scan) +int subselect_single_select_engine::exec(bool *enabled_conds) { DBUG_ENTER("subselect_single_select_engine::exec"); char const *save_where= thd->where; @@ -1733,9 +1782,13 @@ if (!executed) { item->reset_value_registration(); - if (full_scan) + bool have_changed_access= FALSE; + JOIN_TAB *changed_tabs[MAX_TABLES]; + JOIN_TAB **last_changed_tab= changed_tabs; + if (enabled_conds) { /* + For at least one of the pushed predicates the following is true: We should not apply optimizations based on the condition that was pushed down into the subquery. Those optimizations are ref[_or_null] acceses. Change them to be full table scans. @@ -1743,32 +1796,34 @@ for (uint i=join->const_tables ; i < join->tables ; i++) { JOIN_TAB *tab=join->join_tab+i; - if (tab->keyuse && tab->keyuse->outer_ref) + if (tab && tab->keyuse) { - tab->read_first_record= init_read_record_seq; - tab->read_record.record= tab->table->record[0]; - tab->read_record.thd= join->thd; - tab->read_record.ref_length= tab->table->file->ref_length; + for (uint i= 0; i < tab->ref.key_parts; i++) + { + uint ref_col= tab->ref.outer_ref_cols[i]; + if (ref_col != UINT_MAX && !enabled_conds[ref_col]) + { + tab->read_first_record= init_read_record_seq; + tab->read_record.record= tab->table->record[0]; + tab->read_record.thd= join->thd; + tab->read_record.ref_length= tab->table->file->ref_length; + *(last_changed_tab++)= tab; + } + } } } } join->exec(); - if (full_scan) + /* Enable the optimizations back */ + for (JOIN_TAB **ptab= changed_tabs; ptab != last_changed_tab; ptab++) { - /* Enable the optimizations back */ - for (uint i=join->const_tables ; i < join->tables ; i++) - { - JOIN_TAB *tab=join->join_tab+i; - if (tab->keyuse && tab->keyuse->outer_ref) - { - tab->read_record.record= 0; - tab->read_record.ref_length= 0; - tab->read_first_record= join_read_always_key_or_null; - tab->read_record.read_record= join_read_next_same_or_null; - } - } + JOIN_TAB *tab= *ptab; + tab->read_record.record= 0; + tab->read_record.ref_length= 0; + tab->read_first_record= join_read_always_key_or_null; + tab->read_record.read_record= join_read_next_same_or_null; } executed= 1; thd->where= save_where; @@ -1780,12 +1835,12 @@ DBUG_RETURN(0); } -int subselect_union_engine::exec(bool full_scan) +int subselect_union_engine::exec(bool *enabled_conds) { char const *save_where= thd->where; /* - Ignore the full_scan parameter: the pushed down predicates are only used - for filtering, and the caller has disabled them if necessary. + Ignore the enabled_conds parameter: the pushed down predicates are only + used for filtering, and the caller has disabled them if necessary. */ int res= unit->exec(); thd->where= save_where; @@ -1920,7 +1975,7 @@ TRUE - an error occured while scanning */ -int subselect_uniquesubquery_engine::exec(bool full_scan) +int subselect_uniquesubquery_engine::exec(bool *enabled_conds) { DBUG_ENTER("subselect_uniquesubquery_engine::exec"); int error; @@ -2013,7 +2068,7 @@ 1 */ -int subselect_indexsubquery_engine::exec(bool full_scan) +int subselect_indexsubquery_engine::exec(bool *enabled_conds) { DBUG_ENTER("subselect_indexsubquery_engine::exec"); int error; --- 1.81/sql/item_subselect.h 2006-11-15 06:14:23 +03:00 +++ 1.82/sql/item_subselect.h 2006-11-15 06:14:23 +03:00 @@ -95,7 +95,7 @@ return null_value; } bool fix_fields(THD *thd, Item **ref); - virtual bool exec(bool full_scan); + virtual bool exec(bool *enabled_conds); virtual void fix_length_and_dec(); table_map used_tables() const; table_map not_null_tables() const { return 0; } @@ -251,12 +251,14 @@ public: /* Used to trigger on/off conditions that were pushed down to subselect */ bool enable_pushed_conds; + bool *enable_pushed_conds_vec; + Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery Item_in_subselect(Item * left_expr, st_select_lex *select_lex); Item_in_subselect() :Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0), - enable_pushed_conds(TRUE), upper_item(0) + enable_pushed_conds(TRUE), enable_pushed_conds_vec(NULL), upper_item(0) {} subs_type substype() { return IN_SUBS; } @@ -341,23 +343,25 @@ SYNOPSIS exec() - full_scan TRUE - Pushed-down predicates are disabled, the engine - must disable made based on those predicates. - FALSE - Pushed-down predicates are in effect. + enabled_conds Pointer to the array of bool flags which tell which + of the pushed down predicates are enabled. + NULL, if all predicates are enabled or there are no + pushed-down predicates. DESCRIPTION Execute the engine. The result of execution is subquery value that is either captured by previously set up select_result-based 'sink' or stored somewhere by the exec() method itself. - A required side effect: if full_scan==TRUE, subselect_engine->no_rows() - should return correct result. + A required side effect: If at least one pushed-down predicate is + disabled, call of subselect_engine->no_rows()must return correct + result after this call. RETURN 0 - OK - 1 - Either an execution error, or the engine was be "changed", and + 1 - Either an execution error, or the engine was "changed", and the caller should call exec() again for the new engine. */ - virtual int exec(bool full_scan)= 0; + virtual int exec(bool *enabled_conds)= 0; virtual uint cols()= 0; /* return number of columns in select */ virtual uint8 uncacheable()= 0; /* query is uncacheable */ enum Item_result type() { return res_type; } @@ -392,7 +396,7 @@ void cleanup(); int prepare(); void fix_length_and_dec(Item_cache** row); - int exec(bool full_scan); + int exec(bool *enabled_conds); uint cols(); uint8 uncacheable(); void exclude(); @@ -415,7 +419,7 @@ void cleanup(); int prepare(); void fix_length_and_dec(Item_cache** row); - int exec(bool full_scan); + int exec(bool *enabled_conds); uint cols(); uint8 uncacheable(); void exclude(); @@ -453,7 +457,7 @@ void cleanup(); int prepare(); void fix_length_and_dec(Item_cache** row); - int exec(bool full_scan); + int exec(bool *enabled_conds); uint cols() { return 1; } uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; } void exclude(); @@ -480,7 +484,7 @@ :subselect_uniquesubquery_engine(thd, tab_arg, subs, where), check_null(chk_null) {} - int exec(bool full_scan); + int exec(bool *enabled_conds); void print (String *str); }; --- 1.2/mysql-test/r/subselect3.result 2006-11-15 06:14:23 +03:00 +++ 1.3/mysql-test/r/subselect3.result 2006-11-15 06:14:23 +03:00 @@ -89,7 +89,7 @@ select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key Warnings: Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`)))) AS `Z` from `test`.`t2` @@ -153,7 +153,7 @@ from t3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where +2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where; Full scan on NULL key 2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where Warnings: Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 @@ -181,7 +181,7 @@ from t3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where +2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where; Full scan on NULL key 2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 Using where Warnings: Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 @@ -229,3 +229,40 @@ NULL 1 NULL NULL 0 0 drop table t1, t2; +create table t3 (a int); +insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int, b int, oref int); +insert into t2 values (NULL,1, 100), (NULL,2, 100); +create table t1 (a int, b int, c int, key(a,b)); +insert into t1 select 2*A, 2*A, 100 from t3; +explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))))))) AS `Z` from `test`.`t2` +select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; +a b oref Z +NULL 1 100 0 +NULL 2 100 NULL +create table t4 (x int); +insert into t4 select A.a + 10*B.a from t1 A, t1 B; +explain extended +select a,b, oref, +(a,b) in (select a,b from t1,t4 where c=t2.oref) Z +from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 Using where; Full scan on NULL key +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 Using where +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2` +select a,b, oref, +(a,b) in (select a,b from t1,t4 where c=t2.oref) Z +from t2; +a b oref Z +NULL 1 100 0 +NULL 2 100 NULL +drop table t1,t2,t3; --- 1.2/mysql-test/t/subselect3.test 2006-11-15 06:14:23 +03:00 +++ 1.3/mysql-test/t/subselect3.test 2006-11-15 06:14:23 +03:00 @@ -202,3 +202,31 @@ select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; drop table t1, t2; + +# +# BUG#24127: Incorrect results of row-based subqueries with NULLs on the left side. +# +create table t3 (a int); +insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int, b int, oref int); +insert into t2 values (NULL,1, 100), (NULL,2, 100); + +create table t1 (a int, b int, c int, key(a,b)); +insert into t1 select 2*A, 2*A, 100 from t3; + +# First test index subquery engine +explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; +select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; + +# Then check that we do turn off 'ref' scans in the subquery +create table t4 (x int); +insert into t4 select A.a + 10*B.a from t1 A, t1 B; +explain extended + select a,b, oref, + (a,b) in (select a,b from t1,t4 where c=t2.oref) Z + from t2; +select a,b, oref, + (a,b) in (select a,b from t1,t4 where c=t2.oref) Z +from t2; + +drop table t1,t2,t3;
| Thread | ||
|---|---|---|
| • bk commit into 5.0 tree (sergefp:1.2304) BUG#24127 | Sergey Petrunia | 15 Nov |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.881 sec. using MySQL 5.1.14-beta-log