diff options
Diffstat (limited to 'sql/sql_select.cc')
-rw-r--r-- | sql/sql_select.cc | 451 |
1 files changed, 319 insertions, 132 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 76e7f1914f9..69c4ae12470 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -100,6 +100,7 @@ static void make_outerjoin_info(JOIN *join); static Item* make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, table_map sjm_tables); static bool make_join_select(JOIN *join,SQL_SELECT *select,COND *item); +static void revise_cache_usage(JOIN_TAB *join_tab); static bool make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after); static bool only_eq_ref_tables(JOIN *join, ORDER *order, table_map tables); static void update_depend_map(JOIN *join); @@ -1631,6 +1632,62 @@ void JOIN::restore_tmp() } +/* + Shrink join buffers used for preceding tables to reduce the occupied space + + SYNOPSIS + shrink_join_buffers() + jt table up to which the buffers are to be shrunk + curr_space the size of the space used by the buffers for tables 1..jt + needed_space the size of the space that has to be used by these buffers + + DESCRIPTION + The function makes an attempt to shrink all join buffers used for the + tables starting from the first up to jt to reduce the total size of the + space occupied by the buffers used for tables 1,...,jt from curr_space + to needed_space. + The function assumes that the buffer for the table jt has not been + allocated yet. + + RETURN + FALSE if all buffer have been successfully shrunk + TRUE otherwise +*/ + +bool JOIN::shrink_join_buffers(JOIN_TAB *jt, + ulonglong curr_space, + ulonglong needed_space) +{ + JOIN_CACHE *cache; + for (JOIN_TAB *tab= join_tab+const_tables; tab < jt; tab++) + { + cache= tab->cache; + if (cache) + { + ulong buff_size; + if (needed_space < cache->get_min_join_buffer_size()) + return TRUE; + if (cache->shrink_join_buffer_in_ratio(curr_space, needed_space)) + { + revise_cache_usage(tab); + return TRUE; + } + buff_size= cache->get_join_buffer_size(); + curr_space-= buff_size; + needed_space-= buff_size; + } + } + + cache= jt->cache; + DBUG_ASSERT(cache); + if (needed_space < cache->get_min_join_buffer_size()) + return TRUE; + cache->set_join_buffer_size(needed_space); + + return FALSE; +} + + int JOIN::reinit() { @@ -4601,7 +4658,8 @@ best_access_path(JOIN *join, if (table->covering_keys.is_set(key)) { /* we can use only index tree */ - tmp= record_count * table->file->keyread_read_time(key, 1, tmp); + tmp= record_count * + table->file->keyread_read_time(key, 1, (ha_rows) tmp); } else tmp= record_count*min(tmp,s->worst_seeks); @@ -4766,7 +4824,8 @@ best_access_path(JOIN *join, if (table->covering_keys.is_set(key)) { /* we can use only index tree */ - tmp= record_count * table->file->keyread_read_time(key, 1, tmp); + tmp= record_count * + table->file->keyread_read_time(key, 1, (ha_rows) tmp); } else tmp= record_count * min(tmp,s->worst_seeks); @@ -5832,15 +5891,15 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, Find how much space the prevous read not const tables takes in cache. */ -void calc_used_field_length(THD *thd, JOIN_TAB *join_tab) +void JOIN_TAB::calc_used_field_length(bool max_fl) { uint null_fields,blobs,fields,rec_length; Field **f_ptr,*field; uint uneven_bit_fields; - MY_BITMAP *read_set= join_tab->table->read_set; + MY_BITMAP *read_set= table->read_set; uneven_bit_fields= null_fields= blobs= fields= rec_length=0; - for (f_ptr=join_tab->table->field ; (field= *f_ptr) ; f_ptr++) + for (f_ptr=table->field ; (field= *f_ptr) ; f_ptr++) { if (bitmap_is_set(read_set, field->field_index)) { @@ -5857,24 +5916,31 @@ void calc_used_field_length(THD *thd, JOIN_TAB *join_tab) } } if (null_fields || uneven_bit_fields) - rec_length+=(join_tab->table->s->null_fields+7)/8; - if (join_tab->table->maybe_null) + rec_length+=(table->s->null_fields+7)/8; + if (table->maybe_null) rec_length+=sizeof(my_bool); - if (blobs) + if (max_fl) { - uint blob_length=(uint) (join_tab->table->file->stats.mean_rec_length- - (join_tab->table->s->reclength-rec_length)); - rec_length+=(uint) max(4,blob_length); - } + // TODO: to improve this estimate for max expected length if the record + if (blobs) + { + uint blob_length=(uint) (table->file->stats.mean_rec_length- + (table->s->reclength-rec_length)); + rec_length+=(uint) max(4,blob_length); + } + } + else + rec_length= table->file->stats.mean_rec_length; + /* psergey-todo: why we don't count here rowid that we might need to store when using DuplicateElimination? */ - join_tab->used_fields=fields; - join_tab->used_fieldlength=rec_length; - join_tab->used_blobs=blobs; - join_tab->used_null_fields= null_fields; - join_tab->used_uneven_bit_fields= uneven_bit_fields; + used_fields=fields; + used_fieldlength=rec_length; + used_blobs=blobs; + used_null_fields= null_fields; + used_uneven_bit_fields= uneven_bit_fields; } @@ -5883,16 +5949,13 @@ cache_record_length(JOIN *join,uint idx) { uint length=0; JOIN_TAB **pos,**end; - THD *thd=join->thd; for (pos=join->best_ref+join->const_tables,end=join->best_ref+idx ; pos != end ; pos++) { JOIN_TAB *join_tab= *pos; - if (!join_tab->used_fieldlength) /* Not calced yet */ - calc_used_field_length(thd, join_tab); - length+=join_tab->used_fieldlength; + length+= join_tab->get_used_fieldlength(); } return length; } @@ -6362,6 +6425,8 @@ inline void add_cond_and_fix(Item **e1, Item *e2) { if (*e1) { + if (!e2) + return; Item *res; if ((res= new Item_cond_and(*e1, e2))) { @@ -6432,9 +6497,8 @@ static void add_not_null_conds(JOIN *join) for (uint i=join->const_tables ; i < join->tables ; i++) { JOIN_TAB *tab=join->join_tab+i; - if ((tab->type == JT_REF || tab->type == JT_EQ_REF || - tab->type == JT_REF_OR_NULL) && - !tab->table->maybe_null) + if (tab->type == JT_REF || tab->type == JT_EQ_REF || + tab->type == JT_REF_OR_NULL) { for (uint keypart= 0; keypart < tab->ref.key_parts; keypart++) { @@ -6466,9 +6530,14 @@ static void add_not_null_conds(JOIN *join) DBUG_EXECUTE("where",print_where(notnull, referred_tab->table->alias, QT_ORDINARY);); - COND *new_cond= referred_tab->select_cond; - add_cond_and_fix(&new_cond, notnull); - referred_tab->set_select_cond(new_cond, __LINE__); + if (!tab->first_inner) + { + COND *new_cond= referred_tab->select_cond; + add_cond_and_fix(&new_cond, notnull); + referred_tab->set_select_cond(new_cond, __LINE__); + } + else + add_cond_and_fix(tab->first_inner->on_expr_ref, notnull); } } } @@ -6648,6 +6717,10 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) make_cond_for_table(cond, join->const_table_map, (table_map) 0, TRUE); + /* Add conditions added by add_not_null_conds(). */ + for (uint i= 0 ; i < join->const_tables ; i++) + add_cond_and_fix(&const_cond, join->join_tab[i].select_cond); + DBUG_EXECUTE("where",print_where(const_cond,"constants", QT_ORDINARY);); for (JOIN_TAB *tab= join->join_tab+join->const_tables; tab < join->join_tab+join->tables ; tab++) @@ -6746,6 +6819,9 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) tmp= NULL; if (cond) tmp= make_cond_for_table(cond, used_tables, current_map, FALSE); + /* Add conditions added by add_not_null_conds(). */ + if (tab->select_cond) + add_cond_and_fix(&tmp, tab->select_cond); if (cond && !tmp && tab->quick) { // Outer join if (tab->type != JT_ALL) @@ -6996,9 +7072,13 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) used_tables2|= current_map; COND *tmp_cond= make_cond_for_table(on_expr, used_tables2, current_map, FALSE); + add_cond_and_fix(&tmp_cond, tab->on_precond); if (tmp_cond) { JOIN_TAB *cond_tab= tab < first_inner_tab ? first_inner_tab : tab; + Item **sel_cond_ref= tab < first_inner_tab ? + &first_inner_tab->on_precond : + &tab->select_cond; /* First add the guards for match variables of all embedding outer join operations. @@ -7021,15 +7101,15 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) tmp_cond->quick_fix_field(); /* Add the predicate to other pushed down predicates */ DBUG_PRINT("info", ("Item_cond_and")); - cond_tab->select_cond= !cond_tab->select_cond ? tmp_cond : - new Item_cond_and(cond_tab->select_cond, - tmp_cond); + *sel_cond_ref= !(*sel_cond_ref) ? + tmp_cond : + new Item_cond_and(*sel_cond_ref, tmp_cond); DBUG_PRINT("info", ("Item_cond_and 0x%lx", - (ulong)cond_tab->select_cond)); - if (!cond_tab->select_cond) - DBUG_RETURN(1); - cond_tab->select_cond->quick_fix_field(); - cond_tab->select_cond->update_used_tables(); + (ulong)(*sel_cond_ref))); + if (!(*sel_cond_ref)) + DBUG_RETURN(1); + (*sel_cond_ref)->quick_fix_field(); + (*sel_cond_ref)->update_used_tables(); if (cond_tab->select) cond_tab->select->cond= cond_tab->select_cond; } @@ -7121,6 +7201,19 @@ void set_join_cache_denial(JOIN_TAB *join_tab) { if (join_tab->cache) { + /* + If there is a previous cache linked to this cache through the + next_cache pointer: remove the link. + */ + if (join_tab->cache->prev_cache) + join_tab->cache->prev_cache->next_cache= 0; + /* + No need to do the same for next_cache since cache denial is done + backwards starting from the latest cache in the linked list (see + revise_cache_usage()). + */ + DBUG_ASSERT(!join_tab->cache->next_cache); + join_tab->cache->free(); join_tab->cache= 0; } @@ -7315,6 +7408,8 @@ end_sj_materialize(JOIN *join, JOIN_TAB *join_tab, bool end_of_records) no_jbuf_after don't use join buffering after table with this number icp_other_tables_ok OUT TRUE if condition pushdown supports other tables presence + idx_cond_fact_out OUT TRUE if condition pushed to the index is factored + out of the condition pushed to the table DESCRIPTION The function finds out whether the table 'tab' can be joined using a join @@ -7326,24 +7421,66 @@ end_sj_materialize(JOIN *join, JOIN_TAB *join_tab, bool end_of_records) depend on: - the access method to access rows of the joined table - whether the join table is an inner table of an outer join or semi-join + - whether the optimizer switches + outer_join_with_cache, semijoin_with_cache, join_cache_incremental, + join_cache_hashed, join_cache_bka, + are set on or off - the join cache level set for the query - the join 'options'. + In any case join buffer is not used if the number of the joined table is greater than 'no_jbuf_after'. It's also never used if the value of join_cache_level is equal to 0. - The other valid settings of join_cache_level lay in the interval 1..8. - If join_cache_level==1|2 then join buffer is used only for inner joins - with 'JT_ALL' access method. - If join_cache_level==3|4 then join buffer is used for any join operation - (inner join, outer join, semi-join) with 'JT_ALL' access method. - If 'JT_ALL' access method is used to read rows of the joined table then - always a JOIN_CACHE_BNL object is employed. + If the optimizer switch outer_join_with_cache is off no join buffer is + used for outer join operations. + If the optimizer switch semijoin_with_cache is off no join buffer is used + for semi-join operations. + If the optimizer switch join_cache_incremental is off no incremental join + buffers are used. + If the optimizer switch join_cache_hashed is off then the optimizer does + not use neither BNLH algorithm, nor BKAH algorithm to perform join + operations. + + If the optimizer switch join_cache_bka is off then the optimizer does not + use neither BKA algprithm, nor BKAH algorithm to perform join operation. + The valid settings for join_cache_level lay in the interval 0..8. + If it set to 0 no join buffers are used to perform join operations. + Currently we differentiate between join caches of 8 levels: + 1 : non-incremental join cache used for BNL join algorithm + 2 : incremental join cache used for BNL join algorithm + 3 : non-incremental join cache used for BNLH join algorithm + 4 : incremental join cache used for BNLH join algorithm + 5 : non-incremental join cache used for BKA join algorithm + 6 : incremental join cache used for BKA join algorithm + 7 : non-incremental join cache used for BKAH join algorithm + 8 : incremental join cache used for BKAH join algorithm + If the value of join_cache_level is set to n then no join caches of + levels higher than n can be employed. + + If the optimizer switches outer_join_with_cache, semijoin_with_cache, + join_cache_incremental, join_cache_hashed, join_cache_bka are all on + the following rules are applied. + If join_cache_level==1|2 then join buffer is used for inner joins, outer + joins and semi-joins with 'JT_ALL' access method. In this case a + JOIN_CACHE_BNL object is employed. + If join_cache_level==3|4 and then join buffer is used for a join operation + (inner join, outer join, semi-join) with 'JT_REF'/'JT_EQREF' access method + then a JOIN_CACHE_BNLH object is employed. If an index is used to access rows of the joined table and the value of join_cache_level==5|6 then a JOIN_CACHE_BKA object is employed. If an index is used to access rows of the joined table and the value of - join_cache_level==7|8 then a JOIN_CACHE_BKA_UNIQUE object is employed. + join_cache_level==7|8 then a JOIN_CACHE_BKAH object is employed. If the value of join_cache_level is odd then creation of a non-linked join cache is forced. + + Currently for any join operation a join cache of the level of the + highest allowed and applicable level is used. + For example, if join_cache_level is set to 6 and the optimizer switch + join_cache_bka is off, while the optimizer switch join_cache_hashed is + on then for any inner join operation with JT_REF/JT_EQREF access method + to the joined table the BNLH join algorithm will be used, while for + the table accessed by the JT_ALL methods the BNL algorithm will be used. + If the function decides that a join buffer can be used to join the table 'tab' then it sets the value of tab->use_join_buffer to TRUE and assigns the selected join cache object to the field 'cache' of the previous @@ -7360,10 +7497,13 @@ end_sj_materialize(JOIN *join, JOIN_TAB *join_tab, bool end_of_records) For a nested outer join/semi-join, currently, we either use join buffers for all inner tables or for none of them. Some engines (e.g. Falcon) currently allow to use only a join cache - of the type JOIN_CACHE_BKA_UNIQUE when the joined table is accessed through + of the type JOIN_CACHE_BKAH when the joined table is accessed through an index. For these engines setting the value of join_cache_level to 5 or 6 results in that no join buffer is used to join the table. + RETURN VALUE + cache level if cache is used, otherwise returns 0 + TODO Support BKA inside SJ-Materialization nests. When doing this, we'll need to only store sj-inner tables in the join buffer. @@ -7387,17 +7527,14 @@ end_sj_materialize(JOIN *join, JOIN_TAB *join_tab, bool end_of_records) first_tab= join->join_tab + first_sjm_table; } #endif - - RETURN - - cache level if cache is used, otherwise returns 0 */ static uint check_join_cache_usage(JOIN_TAB *tab, JOIN *join, ulonglong options, uint no_jbuf_after, - bool *icp_other_tables_ok) + bool *icp_other_tables_ok, + bool *idx_cond_fact_out) { uint flags; COST_VECT cost; @@ -7405,10 +7542,16 @@ uint check_join_cache_usage(JOIN_TAB *tab, uint bufsz= 4096; JOIN_CACHE *prev_cache=0; uint cache_level= join->thd->variables.join_cache_level; - bool force_unlinked_cache= test(cache_level & 1); + bool force_unlinked_cache= + !optimizer_flag(join->thd, OPTIMIZER_SWITCH_JOIN_CACHE_INCREMENTAL); + bool no_hashed_cache= + !optimizer_flag(join->thd, OPTIMIZER_SWITCH_JOIN_CACHE_HASHED); + bool no_bka_cache= + !optimizer_flag(join->thd, OPTIMIZER_SWITCH_JOIN_CACHE_BKA); uint i= tab - join->join_tab; *icp_other_tables_ok= TRUE; + *idx_cond_fact_out= TRUE; if (cache_level == 0 || i == join->const_tables) return 0; @@ -7420,16 +7563,23 @@ uint check_join_cache_usage(JOIN_TAB *tab, */ if (tab->use_quick == 2) goto no_join_cache; + + if (tab->is_inner_table_of_semi_join_with_first_match() && + !optimizer_flag(join->thd, OPTIMIZER_SWITCH_SEMIJOIN_WITH_CACHE)) + goto no_join_cache; + if (tab->is_inner_table_of_outer_join() && + !optimizer_flag(join->thd, OPTIMIZER_SWITCH_OUTER_JOIN_WITH_CACHE)) + goto no_join_cache; + /* Non-linked join buffers can't guarantee one match */ - if (force_unlinked_cache && - (!tab->type == JT_ALL || cache_level <= 4) && - ((tab->is_inner_table_of_semi_join_with_first_match() && - !tab->is_single_inner_of_semi_join_with_first_match()) || - (tab->is_inner_table_of_outer_join() && - !tab->is_single_inner_of_outer_join()))) - goto no_join_cache; + if (force_unlinked_cache && + ((tab->is_inner_table_of_semi_join_with_first_match() && + !tab->is_single_inner_of_semi_join_with_first_match()) || + (tab->is_inner_table_of_outer_join() && + !tab->is_single_inner_of_outer_join()))) + goto no_join_cache; /* Don't use join buffering if we're dictated not to by no_jbuf_after (this @@ -7474,43 +7624,78 @@ uint check_join_cache_usage(JOIN_TAB *tab, switch (tab->type) { case JT_ALL: - if (cache_level <= 2 && (tab->first_inner || tab->first_sj_inner_tab)) - goto no_join_cache; - if ((options & SELECT_DESCRIBE) || - (((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache))) && - !tab->cache->init())) + if (cache_level == 1) + prev_cache= 0; + if ((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache)) && + ((options & SELECT_DESCRIBE) || !tab->cache->init())) { *icp_other_tables_ok= FALSE; - return cache_level; + return (2-test(!prev_cache)); } goto no_join_cache; case JT_SYSTEM: case JT_CONST: case JT_REF: case JT_EQ_REF: - if (cache_level <= 4) - return 0; + if (cache_level <=2 || (no_hashed_cache && no_bka_cache)) + goto no_join_cache; + flags= HA_MRR_NO_NULL_ENDPOINTS; if (tab->table->covering_keys.is_set(tab->ref.key)) flags|= HA_MRR_INDEX_ONLY; rows= tab->table->file->multi_range_read_info(tab->ref.key, 10, 20, &bufsz, &flags, &cost); - if ((rows != HA_POS_ERROR) && !(flags & HA_MRR_USE_DEFAULT_IMPL) && - (!(flags & HA_MRR_NO_ASSOCIATION) || cache_level > 6) && - ((options & SELECT_DESCRIBE) || - (((cache_level <= 6 && - (tab->cache= new JOIN_CACHE_BKA(join, tab, flags, prev_cache))) || - (cache_level > 6 && - (tab->cache= new JOIN_CACHE_BKA_UNIQUE(join, tab, flags, prev_cache))) - ) && !tab->cache->init()))) - return cache_level; + + if ((cache_level <=4 && !no_hashed_cache) || no_bka_cache || + (flags & HA_MRR_NO_ASSOCIATION) && cache_level <=6) + { + if (cache_level == 3) + prev_cache= 0; + if ((tab->cache= new JOIN_CACHE_BNLH(join, tab, prev_cache)) && + ((options & SELECT_DESCRIBE) || !tab->cache->init())) + { + *icp_other_tables_ok= FALSE; + return (4-test(!prev_cache)); + } + goto no_join_cache; + } + if (cache_level > 4 && no_bka_cache) + goto no_join_cache; + + if ((flags & HA_MRR_NO_ASSOCIATION) && + (cache_level <= 6 || no_hashed_cache)) + goto no_join_cache; + + if ((rows != HA_POS_ERROR) && !(flags & HA_MRR_USE_DEFAULT_IMPL)) + { + if (cache_level <= 6 || no_hashed_cache) + { + if (cache_level == 5) + prev_cache= 0; + if ((tab->cache= new JOIN_CACHE_BKA(join, tab, flags, prev_cache)) && + ((options & SELECT_DESCRIBE) || !tab->cache->init())) + return (6-test(!prev_cache)); + goto no_join_cache; + } + else + { + if (cache_level == 7) + prev_cache= 0; + if ((tab->cache= new JOIN_CACHE_BKAH(join, tab, flags, prev_cache)) && + ((options & SELECT_DESCRIBE) || !tab->cache->init())) + { + *idx_cond_fact_out= FALSE; + return (8-test(!prev_cache)); + } + goto no_join_cache; + } + } goto no_join_cache; default : ; } no_join_cache: - if (cache_level>2) - revise_cache_usage(tab); + revise_cache_usage(tab); return 0; } @@ -7541,6 +7726,7 @@ static bool make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) { uint i; + uint jcl; bool statistics= test(!(join->select_options & SELECT_DESCRIBE)); bool sorted= 1; uint first_sjm_table= MAX_TABLES; @@ -7552,17 +7738,31 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) setup_semijoin_dups_elimination(join, options, no_jbuf_after)) DBUG_RETURN(TRUE); /* purecov: inspected */ + for (i= 0; i < join->const_tables; i++) + join->join_tab[i].partial_join_cardinality= 1; + for (i=join->const_tables ; i < join->tables ; i++) { JOIN_TAB *tab=join->join_tab+i; TABLE *table=tab->table; bool icp_other_tables_ok; + bool idx_cond_fact_out; tab->read_record.table= table; tab->read_record.file=table->file; tab->read_record.unlock_row= rr_unlock_row; tab->next_select=sub_select; /* normal select */ tab->sorted= sorted; sorted= 0; // only first must be sorted + + /* + The approximation below for partial join cardinality is not good because + - it does not take into account some pushdown predicates + - it does not differentiate between inner joins, outer joins and semi-joins. + Later it should be improved. + */ + tab->partial_join_cardinality= join->best_positions[i].records_read * + (i ? (tab-1)->partial_join_cardinality : 1); + if (tab->loosescan_match_tab) { if (!(tab->loosescan_buf= (uchar*)join->thd->alloc(tab-> @@ -7592,27 +7792,29 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) /* Only happens with outer joins */ tab->read_first_record= tab->type == JT_SYSTEM ? join_read_system :join_read_const; - if (check_join_cache_usage(tab, join, options, no_jbuf_after, - &icp_other_tables_ok)) + if ((jcl= check_join_cache_usage(tab, join, options, + no_jbuf_after, &icp_other_tables_ok, + &idx_cond_fact_out))) { tab->use_join_cache= TRUE; tab[-1].next_select=sub_select_cache; } - else if (table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) { table->key_read=1; table->file->extra(HA_EXTRA_KEYREAD); } - else - push_index_cond(tab, tab->ref.key, icp_other_tables_ok); + else if (!jcl || jcl > 4) + push_index_cond(tab, tab->ref.key, + icp_other_tables_ok, idx_cond_fact_out); break; case JT_EQ_REF: tab->read_record.unlock_row= join_read_key_unlock_row; /* fall through */ - if (check_join_cache_usage(tab, join, options, no_jbuf_after, - &icp_other_tables_ok)) + if ((jcl= check_join_cache_usage(tab, join, options, + no_jbuf_after, &icp_other_tables_ok, + &idx_cond_fact_out))) { tab->use_join_cache= TRUE; tab[-1].next_select=sub_select_cache; @@ -7623,8 +7825,9 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) table->key_read=1; table->file->extra(HA_EXTRA_KEYREAD); } - else - push_index_cond(tab, tab->ref.key, icp_other_tables_ok ); + else if (!jcl || jcl > 4) + push_index_cond(tab, tab->ref.key, + icp_other_tables_ok, idx_cond_fact_out); break; case JT_REF_OR_NULL: case JT_REF: @@ -7635,8 +7838,9 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) } delete tab->quick; tab->quick=0; - if (check_join_cache_usage(tab, join, options, no_jbuf_after, - &icp_other_tables_ok)) + if ((jcl= check_join_cache_usage(tab, join, options, + no_jbuf_after, &icp_other_tables_ok, + &idx_cond_fact_out))) { tab->use_join_cache= TRUE; tab[-1].next_select=sub_select_cache; @@ -7644,8 +7848,9 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) if (table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) table->enable_keyread(); - else - push_index_cond(tab, tab->ref.key, icp_other_tables_ok); + else if (!jcl || jcl > 4) + push_index_cond(tab, tab->ref.key, + icp_other_tables_ok, idx_cond_fact_out); break; case JT_ALL: /* @@ -7655,7 +7860,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) materialization nest. */ if (check_join_cache_usage(tab, join, options, no_jbuf_after, - &icp_other_tables_ok)) + &icp_other_tables_ok, &idx_cond_fact_out)) { tab->use_join_cache= TRUE; tab[-1].next_select=sub_select_cache; @@ -7732,7 +7937,8 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) } if (tab->select && tab->select->quick && tab->select->quick->index != MAX_KEY && ! tab->table->key_read) - push_index_cond(tab, tab->select->quick->index, icp_other_tables_ok); + push_index_cond(tab, tab->select->quick->index, + icp_other_tables_ok, idx_cond_fact_out); } break; case JT_FT: @@ -7756,8 +7962,11 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) JOIN_TAB *tab=join->join_tab+i; if (tab->use_join_cache) { - JOIN_TAB *sort_by_tab= join->get_sort_by_join_tab(); - if (sort_by_tab && !join->need_tmp) + JOIN_TAB *sort_by_tab= join->group && join->simple_group && + join->group_list ? + join->join_tab+join->const_tables : + join->get_sort_by_join_tab(); + if (sort_by_tab) { join->need_tmp= 1; join->simple_order= join->simple_group= 0; @@ -13070,7 +13279,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) DBUG_RETURN(nls); } int error; - enum_nested_loop_state rc; + enum_nested_loop_state rc= NESTED_LOOP_OK; READ_RECORD *info= &join_tab->read_record; if (join_tab->flush_weedout_table) @@ -13103,18 +13312,21 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) /* Set first_unmatched for the last inner table of this group */ join_tab->last_inner->first_unmatched= join_tab; - } + if (join_tab->on_precond && !join_tab->on_precond->val_int()) + rc= NESTED_LOOP_NO_MORE_ROWS; + } join->thd->row_count= 0; if (join_tab->loosescan_match_tab) join_tab->loosescan_match_tab->found_match= FALSE; - error= (*join_tab->read_first_record)(join_tab); - - if (join_tab->keep_current_rowid) - join_tab->table->file->position(join_tab->table->record[0]); - - rc= evaluate_join_record(join, join_tab, error); + if (rc != NESTED_LOOP_NO_MORE_ROWS) + { + error= (*join_tab->read_first_record)(join_tab); + if (join_tab->keep_current_rowid) + join_tab->table->file->position(join_tab->table->record[0]); + rc= evaluate_join_record(join, join_tab, error); + } } /* @@ -13406,27 +13618,6 @@ evaluate_null_complemented_join_record(JOIN *join, JOIN_TAB *join_tab) return (*join_tab->next_select)(join, join_tab+1, 0); } -#ifdef MERGE_JUNK -//psergey3-merge: remove: - SQL_SELECT *select; - select= join_tab->select; - - int err= 0; - (err= join_tab->cache.select->skip_record(join->thd)) != 0 )) - { - reset_cache_write(&join_tab->cache); - return NESTED_LOOP_ERROR; - } - - if (!select || (err= select->skip_record(join->thd)) != 0) - if (err < 0) - { - reset_cache_write(&join_tab->cache); - return NESTED_LOOP_ERROR; - } - - rc= NESTED_LOOP_OK; -#endif /***************************************************************************** The different ways to read a record Returns -1 if row was not found, 0 if row was found and 1 on errors @@ -13765,13 +13956,6 @@ join_read_always_key(JOIN_TAB *tab) } } - /* Perform "Late NULLs Filtering" (see internals manual for explanations) */ - for (uint i= 0 ; i < tab->ref.key_parts ; i++) - { - if ((tab->ref.null_rejecting & 1 << i) && tab->ref.items[i]->is_null()) - return -1; - } - if (cp_buffer_from_ref(tab->join->thd, table, &tab->ref)) return -1; if ((error= table->file->ha_index_read_map(table->record[0], @@ -18367,8 +18551,8 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, item_list.push_back(new Item_string("func", strlen("func"), cs)); } /* rows */ - ha_rows rows= (sj_strategy == SJ_OPT_MATERIALIZE_SCAN)? - tab->emb_sj_nest->sj_mat_info->rows : 1; + ha_rows rows= (ha_rows) ((sj_strategy == SJ_OPT_MATERIALIZE_SCAN)? + tab->emb_sj_nest->sj_mat_info->rows : 1); item_list.push_back(new Item_int((longlong)rows, MY_INT64_NUM_DECIMAL_DIGITS)); /* filtered */ @@ -18776,8 +18960,11 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, } } - if (i > 0 && tab[-1].next_select == sub_select_cache) + if (tab->cache) + { extra.append(STRING_WITH_LEN("; Using join buffer")); + tab->cache->print_explain_comment(&extra); + } /* Skip initial "; "*/ const char *str= extra.ptr(); |