MySQL数据库

开发平台：

Visual C++

sql_select.cpp：源码内容

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
/* mysql_select and join optimization */
#ifdef USE_PRAGMA_IMPLEMENTATION
#pragma implementation // gcc: Class implementation
#endif
#include "mysql_priv.h"
#include "sql_select.h"
#include <m_ctype.h>
#include <hash.h>
#include <ft_global.h>
const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref",
"MAYBE_REF","ALL","range","index","fulltext",
"ref_or_null","unique_subquery","index_subquery"
};
static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array);
static bool make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
DYNAMIC_ARRAY *keyuse);
static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,
JOIN_TAB *join_tab,
uint tables, COND *conds,
table_map table_map, SELECT_LEX *select_lex);
static int sort_keyuse(KEYUSE *a,KEYUSE *b);
static void set_position(JOIN *join,uint index,JOIN_TAB *table,KEYUSE *key);
static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
table_map used_tables);
static void find_best_combination(JOIN *join,table_map rest_tables);
static void find_best(JOIN *join,table_map rest_tables,uint index,
double record_count,double read_time);
static uint cache_record_length(JOIN *join,uint index);
static double prev_record_reads(JOIN *join,table_map found_ref);
static bool get_best_combination(JOIN *join);
static store_key *get_store_key(THD *thd,
KEYUSE *keyuse, table_map used_tables,
KEY_PART_INFO *key_part, char *key_buff,
uint maybe_null);
static bool make_simple_join(JOIN *join,TABLE *tmp_table);
static bool make_join_select(JOIN *join,SQL_SELECT *select,COND *item);
static void make_join_readinfo(JOIN *join,uint options);
static bool only_eq_ref_tables(JOIN *join, ORDER *order, table_map tables);
static void update_depend_map(JOIN *join);
static void update_depend_map(JOIN *join, ORDER *order);
static ORDER *remove_const(JOIN *join,ORDER *first_order,COND *cond,
bool change_list, bool *simple_order);
static int return_zero_rows(JOIN *join, select_result *res,TABLE_LIST *tables,
List<Item> &fields, bool send_row,
uint select_options, const char *info,
Item *having, Procedure *proc,
SELECT_LEX_UNIT *unit);
static COND *optimize_cond(THD *thd, COND *conds,
Item::cond_result *cond_value);
static bool const_expression_in_where(COND *conds,Item *item, Item **comp_item);
static bool open_tmp_table(TABLE *table);
static bool create_myisam_tmp_table(TABLE *table,TMP_TABLE_PARAM *param,
ulong options);
static int do_select(JOIN *join,List<Item> *fields,TABLE *tmp_table,
Procedure *proc);
static int sub_select_cache(JOIN *join,JOIN_TAB *join_tab,bool end_of_records);
static int sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records);
static int flush_cached_records(JOIN *join,JOIN_TAB *join_tab,bool skip_last);
static int end_send(JOIN *join, JOIN_TAB *join_tab, bool end_of_records);
static int end_send_group(JOIN *join, JOIN_TAB *join_tab,bool end_of_records);
static int end_write(JOIN *join, JOIN_TAB *join_tab, bool end_of_records);
static int end_update(JOIN *join, JOIN_TAB *join_tab, bool end_of_records);
static int end_unique_update(JOIN *join,JOIN_TAB *join_tab,
bool end_of_records);
static int end_write_group(JOIN *join, JOIN_TAB *join_tab,
bool end_of_records);
static int test_if_group_changed(List<Item_buff> &list);
static int join_read_const_table(JOIN_TAB *tab, POSITION *pos);
static int join_read_system(JOIN_TAB *tab);
static int join_read_const(JOIN_TAB *tab);
static int join_read_key(JOIN_TAB *tab);
static int join_read_always_key(JOIN_TAB *tab);
static int join_read_last_key(JOIN_TAB *tab);
static int join_no_more_records(READ_RECORD *info);
static int join_read_next(READ_RECORD *info);
static int join_init_quick_read_record(JOIN_TAB *tab);
static int test_if_quick_select(JOIN_TAB *tab);
static int join_init_read_record(JOIN_TAB *tab);
static int join_read_first(JOIN_TAB *tab);
static int join_read_next(READ_RECORD *info);
static int join_read_next_same(READ_RECORD *info);
static int join_read_last(JOIN_TAB *tab);
static int join_read_prev_same(READ_RECORD *info);
static int join_read_prev(READ_RECORD *info);
static int join_ft_read_first(JOIN_TAB *tab);
static int join_ft_read_next(READ_RECORD *info);
static int join_read_always_key_or_null(JOIN_TAB *tab);
static int join_read_next_same_or_null(READ_RECORD *info);
static COND *make_cond_for_table(COND *cond,table_map table,
table_map used_table);
static Item* part_of_refkey(TABLE *form,Field *field);
static uint find_shortest_key(TABLE *table, const key_map *usable_keys);
static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,
ha_rows select_limit, bool no_changes);
static int create_sort_index(THD *thd, JOIN *join, ORDER *order,
ha_rows filesort_limit, ha_rows select_limit);
static int remove_duplicates(JOIN *join,TABLE *entry,List<Item> &fields,
Item *having);
static int remove_dup_with_compare(THD *thd, TABLE *entry, Field **field,
ulong offset,Item *having);
static int remove_dup_with_hash_index(THD *thd,TABLE *table,
uint field_count, Field **first_field,
ulong key_length,Item *having);
static int join_init_cache(THD *thd,JOIN_TAB *tables,uint table_count);
static ulong used_blob_length(CACHE_FIELD **ptr);
static bool store_record_in_cache(JOIN_CACHE *cache);
static void reset_cache_read(JOIN_CACHE *cache);
static void reset_cache_write(JOIN_CACHE *cache);
static void read_cached_record(JOIN_TAB *tab);
static bool cmp_buffer_with_ref(JOIN_TAB *tab);
static bool setup_new_fields(THD *thd,TABLE_LIST *tables,List<Item> &fields,
List<Item> &all_fields,ORDER *new_order);
static ORDER *create_distinct_group(THD *thd, Item **ref_pointer_array,
ORDER *order, List<Item> &fields,
bool *all_order_by_fields_used);
static bool test_if_subpart(ORDER *a,ORDER *b);
static TABLE *get_sort_by_table(ORDER *a,ORDER *b,TABLE_LIST *tables);
static void calc_group_buffer(JOIN *join,ORDER *group);
static bool make_group_fields(JOIN *main_join, JOIN *curr_join);
static bool alloc_group_fields(JOIN *join,ORDER *group);
// Create list for using with tempory table
static bool change_to_use_tmp_fields(THD *thd, Item **ref_pointer_array,
List<Item> &new_list1,
List<Item> &new_list2,
uint elements, List<Item> &items);
// Create list for using with tempory table
static bool change_refs_to_tmp_fields(THD *thd, Item **ref_pointer_array,
List<Item> &new_list1,
List<Item> &new_list2,
uint elements, List<Item> &items);
static void init_tmptable_sum_functions(Item_sum **func);
static void update_tmptable_sum_func(Item_sum **func,TABLE *tmp_table);
static void copy_sum_funcs(Item_sum **func_ptr, Item_sum **end);
static bool add_ref_to_table_cond(THD *thd, JOIN_TAB *join_tab);
static bool init_sum_functions(Item_sum **func, Item_sum **end);
static bool update_sum_func(Item_sum **func);
static void select_describe(JOIN *join, bool need_tmp_table,bool need_order,
bool distinct, const char *message=NullS);
static Item *remove_additional_cond(Item* conds);
/*
This handles SELECT with and without UNION
*/
int handle_select(THD *thd, LEX *lex, select_result *result)
{
int res;
register SELECT_LEX *select_lex = &lex->select_lex;
DBUG_ENTER("handle_select");
if (select_lex->next_select())
res=mysql_union(thd, lex, result, &lex->unit);
else
res= mysql_select(thd, &select_lex->ref_pointer_array,
(TABLE_LIST*) select_lex->table_list.first,
select_lex->with_wild, select_lex->item_list,
select_lex->where,
select_lex->order_list.elements +
select_lex->group_list.elements,
(ORDER*) select_lex->order_list.first,
(ORDER*) select_lex->group_list.first,
select_lex->having,
(ORDER*) lex->proc_list.first,
select_lex->options | thd->options,
result, &(lex->unit), &(lex->select_lex));
/* Don't set res if it's -1 as we may want this later */
DBUG_PRINT("info",("res: %d report_error: %d", res,
thd->net.report_error));
if (thd->net.report_error || res<0)
{
result->send_error(0, NullS);
result->abort();
res= 1; // Error sent to client
}
DBUG_RETURN(res);
}
/*
Function to setup clauses without sum functions
*/
inline int setup_without_group(THD *thd, Item **ref_pointer_array,
TABLE_LIST *tables,
List<Item> &fields,
List<Item> &all_fields,
COND **conds,
ORDER *order,
ORDER *group, bool *hidden_group_fields)
{
bool save_allow_sum_func;
int res;
DBUG_ENTER("setup_without_group");
save_allow_sum_func= thd->allow_sum_func;
thd->allow_sum_func= 0;
res= (setup_conds(thd, tables, conds) ||
setup_order(thd, ref_pointer_array, tables, fields, all_fields,
order) ||
setup_group(thd, ref_pointer_array, tables, fields, all_fields,
group, hidden_group_fields));
thd->allow_sum_func= save_allow_sum_func;
DBUG_RETURN(res);
}
/*****************************************************************************
Check fields, find best join, do the select and output fields.
mysql_select assumes that all tables are already opened
*****************************************************************************/
/*
Prepare of whole select (including sub queries in future).
return -1 on error
0 on success
*/
int
JOIN::prepare(Item ***rref_pointer_array,
TABLE_LIST *tables_init,
uint wild_num, COND *conds_init, uint og_num,
ORDER *order_init, ORDER *group_init,
Item *having_init,
ORDER *proc_param_init, SELECT_LEX *select_lex_arg,
SELECT_LEX_UNIT *unit_arg)
{
DBUG_ENTER("JOIN::prepare");
// to prevent double initialization on EXPLAIN
if (optimized)
DBUG_RETURN(0);
conds= conds_init;
order= order_init;
group_list= group_init;
having= having_init;
proc_param= proc_param_init;
tables_list= tables_init;
select_lex= select_lex_arg;
select_lex->join= this;
union_part= (unit_arg->first_select()->next_select() != 0);
/* Check that all tables, fields, conds and order are ok */
if (setup_tables(tables_list) ||
setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) ||
select_lex->setup_ref_array(thd, og_num) ||
setup_fields(thd, (*rref_pointer_array), tables_list, fields_list, 1,
&all_fields, 1) ||
setup_without_group(thd, (*rref_pointer_array), tables_list, fields_list,
all_fields, &conds, order, group_list,
&hidden_group_fields))
DBUG_RETURN(-1); /* purecov: inspected */
ref_pointer_array= *rref_pointer_array;
if (having)
{
thd->where="having clause";
thd->allow_sum_func=1;
select_lex->having_fix_field= 1;
bool having_fix_rc= (!having->fixed &&
(having->fix_fields(thd, tables_list, &having) ||
having->check_cols(1)));
select_lex->having_fix_field= 0;
if (having_fix_rc || thd->net.report_error)
DBUG_RETURN(-1); /* purecov: inspected */
if (having->with_sum_func)
having->split_sum_func(thd, ref_pointer_array, all_fields);
}
// Is it subselect
{
Item_subselect *subselect;
if ((subselect= select_lex->master_unit()->item))
{
Item_subselect::trans_res res;
if ((res= subselect->select_transformer(this)) !=
Item_subselect::RES_OK)
DBUG_RETURN((res == Item_subselect::RES_ERROR));
}
}
if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */
DBUG_RETURN(-1);
/*
Check if one one uses a not constant column with group functions
and no GROUP BY.
TODO: Add check of calculation of GROUP functions and fields:
SELECT COUNT(*)+table.col1 from table1;
*/
{
if (!group_list)
{
uint flag=0;
List_iterator_fast<Item> it(fields_list);
Item *item;
while ((item= it++))
{
if (item->with_sum_func)
flag|=1;
else if (!(flag & 2) && !item->const_during_execution())
flag|=2;
}
if (flag == 3)
{
my_error(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,MYF(0));
DBUG_RETURN(-1);
}
}
TABLE_LIST *table_ptr;
for (table_ptr= tables_list ; table_ptr ; table_ptr= table_ptr->next)
tables++;
}
{
/* Caclulate the number of groups */
send_group_parts= 0;
for (ORDER *group_tmp= group_list ; group_tmp ; group_tmp= group_tmp->next)
send_group_parts++;
}
procedure= setup_procedure(thd, proc_param, result, fields_list, &error);
if (error)
goto err; /* purecov: inspected */
if (procedure)
{
if (setup_new_fields(thd, tables_list, fields_list, all_fields,
procedure->param_fields))
goto err; /* purecov: inspected */
if (procedure->group)
{
if (!test_if_subpart(procedure->group,group_list))
{ /* purecov: inspected */
my_message(0,"Can't handle procedures with differents groups yet",
MYF(0)); /* purecov: inspected */
goto err; /* purecov: inspected */
}
}
#ifdef NOT_NEEDED
else if (!group_list && procedure->flags & PROC_GROUP)
{
my_message(0,"Select must have a group with this procedure",MYF(0));
goto err;
}
#endif
if (order && (procedure->flags & PROC_NO_SORT))
{ /* purecov: inspected */
my_message(0,"Can't use order with this procedure",MYF(0)); /* purecov: inspected */
goto err; /* purecov: inspected */
}
}
/* Init join struct */
count_field_types(&tmp_table_param, all_fields, 0);
ref_pointer_array_size= all_fields.elements*sizeof(Item*);
this->group= group_list != 0;
row_limit= ((select_distinct || order || group_list) ? HA_POS_ERROR :
unit_arg->select_limit_cnt);
/* select_limit is used to decide if we are likely to scan the whole table */
select_limit= unit_arg->select_limit_cnt;
if (having || (select_options & OPTION_FOUND_ROWS))
select_limit= HA_POS_ERROR;
do_send_rows = (unit_arg->select_limit_cnt) ? 1 : 0;
unit= unit_arg;
#ifdef RESTRICTED_GROUP
if (sum_func_count && !group_list && (func_count || field_count))
{
my_message(ER_WRONG_SUM_SELECT,ER(ER_WRONG_SUM_SELECT),MYF(0));
goto err;
}
#endif
/*
We must not yet prepare the result table if it is the same as one of the
source tables (INSERT SELECT). This is checked in mysql_execute_command()
and OPTION_BUFFER_RESULT is added to the select_options. A temporary
table is then used to hold the result. The preparation may disable
indexes on the result table, which may be used during the select, if it
is the same table (Bug #6034). Do the preparation after the select phase.
*/
if (! procedure && ! test(select_options & OPTION_BUFFER_RESULT) &&
result && result->prepare(fields_list, unit_arg))
goto err; /* purecov: inspected */
if (select_lex->olap == ROLLUP_TYPE && rollup_init())
goto err;
if (alloc_func_list())
goto err;
DBUG_RETURN(0); // All OK
err:
delete procedure; /* purecov: inspected */
procedure= 0;
DBUG_RETURN(-1); /* purecov: inspected */
}
/*
test if it is known for optimisation IN subquery
SYNOPSYS
JOIN::test_in_subselect
where - pointer for variable in which conditions should be
stored if subquery is known
RETURN
1 - known
0 - unknown
*/
bool JOIN::test_in_subselect(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;
}
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;
}
return 0;
}
/*
global select optimisation.
return 0 - success
1 - error
error code saved in field 'error'
*/
int
JOIN::optimize()
{
DBUG_ENTER("JOIN::optimize");
// to prevent double initialization on EXPLAIN
if (optimized)
DBUG_RETURN(0);
optimized= 1;
// Ignore errors of execution if option IGNORE present
if (thd->lex->ignore)
thd->lex->current_select->no_error= 1;
#ifdef HAVE_REF_TO_FIELDS // Not done yet
/* Add HAVING to WHERE if possible */
if (having && !group_list && !sum_func_count)
{
if (!conds)
{
conds= having;
having= 0;
}
else if ((conds=new Item_cond_and(conds,having)))
{
conds->fix_fields(thd, tables_list, &conds);
conds->change_ref_to_fields(thd, tables_list);
conds->top_level_item();
having= 0;
}
}
#endif
conds= optimize_cond(thd, conds, &cond_value);
if (thd->net.report_error)
{
error= 1;
DBUG_PRINT("error",("Error from optimize_cond"));
DBUG_RETURN(1);
}
if (cond_value == Item::COND_FALSE ||
(!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS)))
{ /* Impossible cond */
zero_result_cause= "Impossible WHERE";
error= 0;
DBUG_RETURN(0);
}
/* Optimize count(*), min() and max() */
if (tables_list && tmp_table_param.sum_func_count && ! group_list)
{
int res;
/*
opt_sum_query() returns -1 if no rows match to the WHERE conditions,
or 1 if all items were resolved, or 0, or an error number HA_ERR_...
*/
if ((res=opt_sum_query(tables_list, all_fields, conds)))
{
if (res > 1)
{
DBUG_RETURN(1);
}
if (res < 0)
{
zero_result_cause= "No matching min/max row";
error=0;
DBUG_RETURN(0);
}
zero_result_cause= "Select tables optimized away";
tables_list= 0; // All tables resolved
}
}
if (!tables_list)
{
error= 0;
DBUG_RETURN(0);
}
error= -1; // Error is sent to client
sort_by_table= get_sort_by_table(order, group_list, tables_list);
/* Calculate how to do the join */
thd->proc_info= "statistics";
if (make_join_statistics(this, tables_list, conds, &keyuse) ||
thd->is_fatal_error)
{
DBUG_PRINT("error",("Error: make_join_statistics() failed"));
DBUG_RETURN(1);
}
/* Remove distinct if only const tables */
select_distinct= select_distinct && (const_tables != tables);
thd->proc_info= "preparing";
if (result->initialize_tables(this))
{
DBUG_PRINT("error",("Error: initialize_tables() failed"));
DBUG_RETURN(1); // error == -1
}
if (const_table_map != found_const_table_map &&
!(select_options & SELECT_DESCRIBE) &&
(!conds ||
!(conds->used_tables() & RAND_TABLE_BIT) ||
select_lex->master_unit() == &thd->lex->unit)) // upper level SELECT
{
zero_result_cause= "no matching row in const table";
DBUG_PRINT("error",("Error: %s", zero_result_cause));
error= 0;
DBUG_RETURN(0);
}
if (!(thd->options & OPTION_BIG_SELECTS) &&
best_read > (double) thd->variables.max_join_size &&
!(select_options & SELECT_DESCRIBE))
{ /* purecov: inspected */
my_message(ER_TOO_BIG_SELECT, ER(ER_TOO_BIG_SELECT), MYF(0));
error= -1;
DBUG_RETURN(1);
}
if (const_tables && !thd->locked_tables &&
!(select_options & SELECT_NO_UNLOCK))
mysql_unlock_some_tables(thd, table, const_tables);
if (!conds && outer_join)
{
/* Handle the case where we have an OUTER JOIN without a WHERE */
conds=new Item_int((longlong) 1,1); // Always true
}
select=make_select(*table, const_table_map,
const_table_map, conds, &error);
if (error)
{ /* purecov: inspected */
error= -1; /* purecov: inspected */
DBUG_PRINT("error",("Error: make_select() failed"));
DBUG_RETURN(1);
}
if (make_join_select(this, select, conds))
{
zero_result_cause=
"Impossible WHERE noticed after reading const tables";
DBUG_RETURN(0); // error == 0
}
error= -1; /* if goto err */
/* Optimize distinct away if possible */
{
ORDER *org_order= order;
order=remove_const(this, order,conds,1, &simple_order);
/*
If we are using ORDER BY NULL or ORDER BY const_expression,
return result in any order (even if we are using a GROUP BY)
*/
if (!order && org_order)
skip_sort_order= 1;
}
if (group_list || tmp_table_param.sum_func_count)
{
if (! hidden_group_fields && rollup.state == ROLLUP::STATE_NONE)
select_distinct=0;
}
else if (select_distinct && tables - const_tables == 1)
{
/*
We are only using one table. In this case we change DISTINCT to a
GROUP BY query if:
- The GROUP BY can be done through indexes (no sort) and the ORDER
BY only uses selected fields.
(In this case we can later optimize away GROUP BY and ORDER BY)
- We are scanning the whole table without LIMIT
This can happen if:
- We are using CALC_FOUND_ROWS
- We are using an ORDER BY that can't be optimized away.
We don't want to use this optimization when we are using LIMIT
because in this case we can just create a temporary table that
holds LIMIT rows and stop when this table is full.
*/
JOIN_TAB *tab= &join_tab[const_tables];
bool all_order_fields_used;
if (order)
skip_sort_order= test_if_skip_sort_order(tab, order, select_limit, 1);
if ((group_list=create_distinct_group(thd, select_lex->ref_pointer_array,
order, fields_list,
&all_order_fields_used)))
{
bool skip_group= (skip_sort_order &&
test_if_skip_sort_order(tab, group_list, select_limit,
1) != 0);
if ((skip_group && all_order_fields_used) ||
select_limit == HA_POS_ERROR ||
(order && !skip_sort_order))
{
/* Change DISTINCT to GROUP BY */
select_distinct= 0;
no_order= !order;
if (all_order_fields_used)
{
if (order && skip_sort_order)
{
/*
Force MySQL to read the table in sorted order to get result in
ORDER BY order.
*/
tmp_table_param.quick_group=0;
}
order=0;
}
group=1; // For end_write_group
}
else
group_list= 0;
}
else if (thd->is_fatal_error) // End of memory
DBUG_RETURN(1);
}
simple_group= 0;
{
ORDER *old_group_list;
group_list= remove_const(this, (old_group_list= group_list), conds,
rollup.state == ROLLUP::STATE_NONE,
&simple_group);
if (old_group_list && !group_list)
select_distinct= 0;
}
if (!group_list && group)
{
order=0; // The output has only one row
simple_order=1;
select_distinct= 0; // No need in distinct for 1 row
}
calc_group_buffer(this, group_list);
send_group_parts= tmp_table_param.group_parts; /* Save org parts */
if (procedure && procedure->group)
{
group_list= procedure->group= remove_const(this, procedure->group, conds,
1, &simple_group);
calc_group_buffer(this, group_list);
}
if (test_if_subpart(group_list, order) ||
(!group_list && tmp_table_param.sum_func_count))
order=0;
// Can't use sort on head table if using row cache
if (full_join)
{
if (group_list)
simple_group=0;
if (order)
simple_order=0;
}
/*
Check if we need to create a temporary table.
This has to be done if all tables are not already read (const tables)
and one of the following conditions holds:
- We are using DISTINCT (simple distinct's are already optimized away)
- We are using an ORDER BY or GROUP BY on fields not in the first table
- We are using different ORDER BY and GROUP BY orders
- The user wants us to buffer the result.
*/
need_tmp= (const_tables != tables &&
((select_distinct || !simple_order || !simple_group) ||
(group_list && order) ||
test(select_options & OPTION_BUFFER_RESULT)));
// No cache for MATCH
make_join_readinfo(this,
(select_options & (SELECT_DESCRIBE |
SELECT_NO_JOIN_CACHE)) |
(select_lex->ftfunc_list->elements ?
SELECT_NO_JOIN_CACHE : 0));
/* Perform FULLTEXT search before all regular searches */
if (!(select_options & SELECT_DESCRIBE))
init_ftfuncs(thd, select_lex, test(order));
/*
is this simple IN subquery?
*/
if (!group_list && !order &&
unit->item && unit->item->substype() == Item_subselect::IN_SUBS &&
tables == 1 && conds &&
!unit->first_select()->next_select())
{
if (!having)
{
Item *where= 0;
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)));
}
}
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)));
}
}
} else if (join_tab[0].type == JT_REF_OR_NULL &&
join_tab[0].ref.items[0]->name == in_left_expr_name &&
having->type() == Item::FUNC_ITEM &&
((Item_func *) having)->functype() ==
Item_func::ISNOTNULLTEST_FUNC)
{
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";
DBUG_RETURN(unit->item->
change_engine(new subselect_indexsubquery_engine(thd,
join_tab,
unit->item,
conds,
1)));
}
}
/*
Need to tell Innobase that to play it safe, it should fetch all
columns of the tables: this is because MySQL may build row
pointers for the rows, and for all columns of the primary key the
field->query_id has not necessarily been set to thd->query_id by
MySQL.
*/
#ifdef HAVE_INNOBASE_DB
if (need_tmp || select_distinct || group_list || order)
{
for (uint i_h = const_tables; i_h < tables; i_h++)
{
TABLE* table_h = join_tab[i_h].table;
table_h->file->extra(HA_EXTRA_RETRIEVE_PRIMARY_KEY);
}
}
#endif
DBUG_EXECUTE("info",TEST_join(this););
/*
Because filesort always does a full table scan or a quick range scan
we must add the removed reference to the select for the table.
We only need to do this when we have a simple_order or simple_group
as in other cases the join is done before the sort.
*/
if (const_tables != tables &&
(order || group_list) &&
join_tab[const_tables].type != JT_ALL &&
join_tab[const_tables].type != JT_FT &&
join_tab[const_tables].type != JT_REF_OR_NULL &&
(order && simple_order || group_list && simple_group))
{
if (add_ref_to_table_cond(thd,&join_tab[const_tables]))
DBUG_RETURN(1);
}
if (!(select_options & SELECT_BIG_RESULT) &&
((group_list && const_tables != tables &&
(!simple_group ||
!test_if_skip_sort_order(&join_tab[const_tables], group_list,
unit->select_limit_cnt, 0))) ||
select_distinct) &&
tmp_table_param.quick_group && !procedure)
{
need_tmp=1; simple_order=simple_group=0; // Force tmp table without sort
}
tmp_having= having;
if (select_options & SELECT_DESCRIBE)
{
error= 0;
DBUG_RETURN(0);
}
having= 0;
/* Create a tmp table if distinct or if the sort is too complicated */
if (need_tmp)
{
DBUG_PRINT("info",("Creating tmp table"));
thd->proc_info="Creating tmp table";
init_items_ref_array();
tmp_table_param.hidden_field_count= (all_fields.elements -
fields_list.elements);
if (!(exec_tmp_table1 =
create_tmp_table(thd, &tmp_table_param, all_fields,
((!simple_group && !procedure &&
!(test_flags & TEST_NO_KEY_GROUP)) ?
group_list : (ORDER*) 0),
group_list ? 0 : select_distinct,
group_list && simple_group,
select_options,
(order == 0 || skip_sort_order) ? select_limit :
HA_POS_ERROR,
(char *) "")))
DBUG_RETURN(1);
/*
We don't have to store rows in temp table that doesn't match HAVING if:
- we are sorting the table and writing complete group rows to the
temp table.
- We are using DISTINCT without resolving the distinct as a GROUP BY
on all columns.
If having is not handled here, it will be checked before the row
is sent to the client.
*/
if (tmp_having &&
(sort_and_group || (exec_tmp_table1->distinct && !group_list)))
having= tmp_having;
/* if group or order on first table, sort first */
if (group_list && simple_group)
{
DBUG_PRINT("info",("Sorting for group"));
thd->proc_info="Sorting for group";
if (create_sort_index(thd, this, group_list,
HA_POS_ERROR, HA_POS_ERROR) ||
alloc_group_fields(this, group_list) ||
make_sum_func_list(all_fields, fields_list, 1))
DBUG_RETURN(1);
group_list=0;
}
else
{
if (make_sum_func_list(all_fields, fields_list, 0))
DBUG_RETURN(1);
if (!group_list && ! exec_tmp_table1->distinct && order && simple_order)
{
DBUG_PRINT("info",("Sorting for order"));
thd->proc_info="Sorting for order";
if (create_sort_index(thd, this, order,
HA_POS_ERROR, HA_POS_ERROR))
DBUG_RETURN(1);
order=0;
}
}
/*
Optimize distinct when used on some of the tables
SELECT DISTINCT t1.a FROM t1,t2 WHERE t1.b=t2.b
In this case we can stop scanning t2 when we have found one t1.a
*/
if (exec_tmp_table1->distinct)
{
table_map used_tables= thd->used_tables;
JOIN_TAB *last_join_tab= join_tab+tables-1;
do
{
if (used_tables & last_join_tab->table->map)
break;
last_join_tab->not_used_in_distinct=1;
} while (last_join_tab-- != join_tab);
/* Optimize "select distinct b from t1 order by key_part_1 limit #" */
if (order && skip_sort_order)
{
/* Should always succeed */
if (test_if_skip_sort_order(&join_tab[const_tables],
order, unit->select_limit_cnt, 0))
order=0;
}
}
if (thd->lex->subqueries)
{
if (!(tmp_join= (JOIN*)thd->alloc(sizeof(JOIN))))
DBUG_RETURN(-1);
error= 0; // Ensure that tmp_join.error= 0
restore_tmp();
}
}
error= 0;
DBUG_RETURN(0);
}
/*
Restore values in temporary join
*/
void JOIN::restore_tmp()
{
memcpy(tmp_join, this, (size_t) sizeof(JOIN));
}
int
JOIN::reinit()
{
DBUG_ENTER("JOIN::reinit");
/* TODO move to unit reinit */
unit->offset_limit_cnt =select_lex->offset_limit;
unit->select_limit_cnt =select_lex->select_limit+select_lex->offset_limit;
if (unit->select_limit_cnt < select_lex->select_limit)
unit->select_limit_cnt= HA_POS_ERROR; // no limit
if (unit->select_limit_cnt == HA_POS_ERROR)
select_lex->options&= ~OPTION_FOUND_ROWS;
if (!optimized && setup_tables(tables_list))
DBUG_RETURN(1);
/* Reset of sum functions */
first_record= 0;
if (exec_tmp_table1)
{
exec_tmp_table1->file->extra(HA_EXTRA_RESET_STATE);
exec_tmp_table1->file->delete_all_rows();
free_io_cache(exec_tmp_table1);
filesort_free_buffers(exec_tmp_table1);
}
if (exec_tmp_table2)
{
exec_tmp_table2->file->extra(HA_EXTRA_RESET_STATE);
exec_tmp_table2->file->delete_all_rows();
free_io_cache(exec_tmp_table2);
filesort_free_buffers(exec_tmp_table2);
}
if (items0)
set_items_ref_array(items0);
if (join_tab_save)
memcpy(join_tab, join_tab_save, sizeof(JOIN_TAB) * tables);
if (tmp_join)
restore_tmp();
if (sum_funcs)
{
Item_sum *func, **func_ptr= sum_funcs;
while ((func= *(func_ptr++)))
func->clear();
}
DBUG_RETURN(0);
}
bool
JOIN::save_join_tab()
{
if (!join_tab_save && select_lex->master_unit()->uncacheable)
{
if (!(join_tab_save= (JOIN_TAB*)thd->memdup((gptr) join_tab,
sizeof(JOIN_TAB) * tables)))
return 1;
}
return 0;
}
/*
Exec select
*/
void
JOIN::exec()
{
List<Item> *columns_list= &fields_list;
int tmp_error;
DBUG_ENTER("JOIN::exec");
error= 0;
if (procedure)
{
procedure_fields_list= fields_list;
if (procedure->change_columns(procedure_fields_list) ||
result->prepare(procedure_fields_list, unit))
{
thd->limit_found_rows= thd->examined_row_count= 0;
DBUG_VOID_RETURN;
}
columns_list= &procedure_fields_list;
}
else if (test(select_options & OPTION_BUFFER_RESULT) &&
result && result->prepare(fields_list, unit))
{
error= 1;
thd->limit_found_rows= thd->examined_row_count= 0;
DBUG_VOID_RETURN;
}
if (!tables_list)
{ // Only test of functions
if (select_options & SELECT_DESCRIBE)
select_describe(this, FALSE, FALSE, FALSE,
(zero_result_cause?zero_result_cause:"No tables used"));
else
{
result->send_fields(*columns_list, 1);
/*
We have to test for 'conds' here as the WHERE may not be constant
even if we don't have any tables for prepared statements or if
conds uses something like 'rand()'.
*/
if (cond_value != Item::COND_FALSE &&
(!conds || conds->val_int()) &&
(!having || having->val_int()))
{
if (do_send_rows &&
(procedure ? (procedure->send_row(procedure_fields_list) ||
procedure->end_of_records()) : result->send_data(fields_list)))
error= 1;
else
{
error= (int) result->send_eof();
send_records= ((select_options & OPTION_FOUND_ROWS) ? 1 :
thd->sent_row_count);
}
}
else
{
error=(int) result->send_eof();
send_records= 0;
}
}
/* Single select (without union) always returns 0 or 1 row */
thd->limit_found_rows= send_records;
thd->examined_row_count= 0;
DBUG_VOID_RETURN;
}
thd->limit_found_rows= thd->examined_row_count= 0;
if (zero_result_cause)
{
(void) return_zero_rows(this, result, tables_list, *columns_list,
send_row_on_empty_set(),
select_options,
zero_result_cause,
having, procedure,
unit);
DBUG_VOID_RETURN;
}
if (select_options & SELECT_DESCRIBE)
{
/*
Check if we managed to optimize ORDER BY away and don't use temporary
table to resolve ORDER BY: in that case, we only may need to do
filesort for GROUP BY.
*/
if (!order && !no_order && (!skip_sort_order || !need_tmp))
{
/*
Reset 'order' to 'group_list' and reinit variables describing
'order'
*/
order= group_list;
simple_order= simple_group;
skip_sort_order= 0;
}
if (order &&
(const_tables == tables ||
((simple_order || skip_sort_order) &&
test_if_skip_sort_order(&join_tab[const_tables], order,
select_limit, 0))))
order=0;
having= tmp_having;
select_describe(this, need_tmp,
order != 0 && !skip_sort_order,
select_distinct);
DBUG_VOID_RETURN;
}
JOIN *curr_join= this;
List<Item> *curr_all_fields= &all_fields;
List<Item> *curr_fields_list= &fields_list;
TABLE *curr_tmp_table= 0;
/* Create a tmp table if distinct or if the sort is too complicated */
if (need_tmp)
{
if (tmp_join)
curr_join= tmp_join;
curr_tmp_table= exec_tmp_table1;
/* Copy data to the temporary table */
thd->proc_info= "Copying to tmp table";
if ((tmp_error= do_select(curr_join, (List<Item> *) 0, curr_tmp_table, 0)))
{
error= tmp_error;
DBUG_VOID_RETURN;
}
curr_tmp_table->file->info(HA_STATUS_VARIABLE);
if (curr_join->having)
curr_join->having= curr_join->tmp_having= 0; // Allready done
/* Change sum_fields reference to calculated fields in tmp_table */
curr_join->all_fields= *curr_all_fields;
if (!items1)
{
items1= items0 + all_fields.elements;
if (sort_and_group || curr_tmp_table->group)
{
if (change_to_use_tmp_fields(thd, items1,
tmp_fields_list1, tmp_all_fields1,
fields_list.elements, all_fields))
DBUG_VOID_RETURN;
}
else
{
if (change_refs_to_tmp_fields(thd, items1,
tmp_fields_list1, tmp_all_fields1,
fields_list.elements, all_fields))
DBUG_VOID_RETURN;
}
curr_join->tmp_all_fields1= tmp_all_fields1;
curr_join->tmp_fields_list1= tmp_fields_list1;
curr_join->items1= items1;
}
curr_all_fields= &tmp_all_fields1;
curr_fields_list= &tmp_fields_list1;
curr_join->set_items_ref_array(items1);
if (sort_and_group || curr_tmp_table->group)
{
curr_join->tmp_table_param.field_count+=
curr_join->tmp_table_param.sum_func_count+
curr_join->tmp_table_param.func_count;
curr_join->tmp_table_param.sum_func_count=
curr_join->tmp_table_param.func_count= 0;
}
else
{
curr_join->tmp_table_param.field_count+=
curr_join->tmp_table_param.func_count;
curr_join->tmp_table_param.func_count= 0;
}
// procedure can't be used inside subselect => we do nothing special for it
if (procedure)
procedure->update_refs();
if (curr_tmp_table->group)
{ // Already grouped
if (!curr_join->order && !curr_join->no_order && !skip_sort_order)
curr_join->order= curr_join->group_list; /* order by group */
curr_join->group_list= 0;
}
/*
If we have different sort & group then we must sort the data by group
and copy it to another tmp table
This code is also used if we are using distinct something
we haven't been able to store in the temporary table yet
like SEC_TO_TIME(SUM(...)).
*/
if (curr_join->group_list && (!test_if_subpart(curr_join->group_list,
curr_join->order) ||
curr_join->select_distinct) ||
(curr_join->select_distinct &&
curr_join->tmp_table_param.using_indirect_summary_function))
{ /* Must copy to another table */
DBUG_PRINT("info",("Creating group table"));
/* Free first data from old join */
curr_join->join_free(0);
if (make_simple_join(curr_join, curr_tmp_table))
DBUG_VOID_RETURN;
calc_group_buffer(curr_join, group_list);
count_field_types(&curr_join->tmp_table_param,
curr_join->tmp_all_fields1,
curr_join->select_distinct && !curr_join->group_list);
curr_join->tmp_table_param.hidden_field_count=
(curr_join->tmp_all_fields1.elements-
curr_join->tmp_fields_list1.elements);
if (exec_tmp_table2)
curr_tmp_table= exec_tmp_table2;
else
{
/* group data to new table */
if (!(curr_tmp_table=
exec_tmp_table2= create_tmp_table(thd,
&curr_join->tmp_table_param,
*curr_all_fields,
(ORDER*) 0,
curr_join->select_distinct &&
!curr_join->group_list,
1, curr_join->select_options,
HA_POS_ERROR,
(char *) "")))
DBUG_VOID_RETURN;
curr_join->exec_tmp_table2= exec_tmp_table2;
}
if (curr_join->group_list)
{
thd->proc_info= "Creating sort index";
if (curr_join->join_tab == join_tab && save_join_tab())
{
DBUG_VOID_RETURN;
}
if (create_sort_index(thd, curr_join, curr_join->group_list,
HA_POS_ERROR, HA_POS_ERROR) ||
make_group_fields(this, curr_join))
{
DBUG_VOID_RETURN;
}
}
thd->proc_info="Copying to group table";
tmp_error= -1;
if (curr_join != this)
{
if (sum_funcs2)
{
curr_join->sum_funcs= sum_funcs2;
curr_join->sum_funcs_end= sum_funcs_end2;
}
else
{
curr_join->alloc_func_list();
sum_funcs2= curr_join->sum_funcs;
sum_funcs_end2= curr_join->sum_funcs_end;
}
}
if (curr_join->make_sum_func_list(*curr_all_fields, *curr_fields_list,
1))
DBUG_VOID_RETURN;
curr_join->group_list= 0;
if ((tmp_error= do_select(curr_join, (List<Item> *) 0, curr_tmp_table,
0)))
{
error= tmp_error;
DBUG_VOID_RETURN;
}
end_read_record(&curr_join->join_tab->read_record);
curr_join->const_tables= curr_join->tables; // Mark free for join_free()
curr_join->join_tab[0].table= 0; // Table is freed
// No sum funcs anymore
if (!items2)
{
items2= items1 + all_fields.elements;
if (change_to_use_tmp_fields(thd, items2,
tmp_fields_list2, tmp_all_fields2,
fields_list.elements, tmp_all_fields1))
DBUG_VOID_RETURN;
curr_join->tmp_fields_list2= tmp_fields_list2;
curr_join->tmp_all_fields2= tmp_all_fields2;
}
curr_fields_list= &curr_join->tmp_fields_list2;
curr_all_fields= &curr_join->tmp_all_fields2;
curr_join->set_items_ref_array(items2);
curr_join->tmp_table_param.field_count+=
curr_join->tmp_table_param.sum_func_count;
curr_join->tmp_table_param.sum_func_count= 0;
}
if (curr_tmp_table->distinct)
curr_join->select_distinct=0; /* Each row is unique */
curr_join->join_free(0); /* Free quick selects */
if (curr_join->select_distinct && ! curr_join->group_list)
{
thd->proc_info="Removing duplicates";
if (curr_join->tmp_having)
curr_join->tmp_having->update_used_tables();
if (remove_duplicates(curr_join, curr_tmp_table,
*curr_fields_list, curr_join->tmp_having))
DBUG_VOID_RETURN;
curr_join->tmp_having=0;
curr_join->select_distinct=0;
}
curr_tmp_table->reginfo.lock_type= TL_UNLOCK;
if (make_simple_join(curr_join, curr_tmp_table))
DBUG_VOID_RETURN;
calc_group_buffer(curr_join, curr_join->group_list);
count_field_types(&curr_join->tmp_table_param, *curr_all_fields, 0);
}
if (procedure)
count_field_types(&curr_join->tmp_table_param, *curr_all_fields, 0);
if (curr_join->group || curr_join->tmp_table_param.sum_func_count ||
(procedure && (procedure->flags & PROC_GROUP)))
{
if (make_group_fields(this, curr_join))
{
DBUG_VOID_RETURN;
}
if (!items3)
{
if (!items0)
init_items_ref_array();
items3= ref_pointer_array + (all_fields.elements*4);
setup_copy_fields(thd, &curr_join->tmp_table_param,
items3, tmp_fields_list3, tmp_all_fields3,
curr_fields_list->elements, *curr_all_fields);
tmp_table_param.save_copy_funcs= curr_join->tmp_table_param.copy_funcs;
tmp_table_param.save_copy_field= curr_join->tmp_table_param.copy_field;
tmp_table_param.save_copy_field_end=
curr_join->tmp_table_param.copy_field_end;
curr_join->tmp_all_fields3= tmp_all_fields3;
curr_join->tmp_fields_list3= tmp_fields_list3;
}
else
{
curr_join->tmp_table_param.copy_funcs= tmp_table_param.save_copy_funcs;
curr_join->tmp_table_param.copy_field= tmp_table_param.save_copy_field;
curr_join->tmp_table_param.copy_field_end=
tmp_table_param.save_copy_field_end;
}
curr_fields_list= &tmp_fields_list3;
curr_all_fields= &tmp_all_fields3;
curr_join->set_items_ref_array(items3);
if (curr_join->make_sum_func_list(*curr_all_fields, *curr_fields_list,
1) || thd->is_fatal_error)
DBUG_VOID_RETURN;
}
if (curr_join->group_list || curr_join->order)
{
DBUG_PRINT("info",("Sorting for send_fields"));
thd->proc_info="Sorting result";
/* If we have already done the group, add HAVING to sorted table */
if (curr_join->tmp_having && ! curr_join->group_list &&
! curr_join->sort_and_group)
{
// Some tables may have been const
curr_join->tmp_having->update_used_tables();
JOIN_TAB *curr_table= &curr_join->join_tab[curr_join->const_tables];
table_map used_tables= (curr_join->const_table_map |
curr_table->table->map);
Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having,
used_tables,
used_tables);
if (sort_table_cond)
{
if (!curr_table->select)
if (!(curr_table->select= new SQL_SELECT))
DBUG_VOID_RETURN;
if (!curr_table->select->cond)
curr_table->select->cond= sort_table_cond;
else // This should never happen
{
if (!(curr_table->select->cond=
new Item_cond_and(curr_table->select->cond,
sort_table_cond)))
DBUG_VOID_RETURN;
/*
Item_cond_and do not need fix_fields for execution, its parameters
are fixed or do not need fix_fields, too
*/
curr_table->select->cond->quick_fix_field();
}
curr_table->select_cond= curr_table->select->cond;
curr_table->select_cond->top_level_item();
DBUG_EXECUTE("where",print_where(curr_table->select->cond,
"select and having"););
curr_join->tmp_having= make_cond_for_table(curr_join->tmp_having,
~ (table_map) 0,
~used_tables);
DBUG_EXECUTE("where",print_where(curr_join->tmp_having,
"having after sort"););
}
}
{
if (group)
curr_join->select_limit= HA_POS_ERROR;
else
{
/*
We can abort sorting after thd->select_limit rows if we there is no
WHERE clause for any tables after the sorted one.
*/
JOIN_TAB *curr_table= &curr_join->join_tab[curr_join->const_tables+1];
JOIN_TAB *end_table= &curr_join->join_tab[curr_join->tables];
for (; curr_table < end_table ; curr_table++)
{
/*
table->keyuse is set in the case there was an original WHERE clause
on the table that was optimized away.
table->on_expr tells us that it was a LEFT JOIN and there will be
at least one row generated from the table.
*/
if (curr_table->select_cond ||
(curr_table->keyuse && !curr_table->on_expr))
{
/* We have to sort all rows */
curr_join->select_limit= HA_POS_ERROR;
break;
}
}
}
if (curr_join->join_tab == join_tab && save_join_tab())
{
DBUG_VOID_RETURN;
}
/*
Here we sort rows for ORDER BY/GROUP BY clause, if the optimiser
chose FILESORT to be faster than INDEX SCAN or there is no
suitable index present.
Note, that create_sort_index calls test_if_skip_sort_order and may
finally replace sorting with index scan if there is a LIMIT clause in
the query. XXX: it's never shown in EXPLAIN!
OPTION_FOUND_ROWS supersedes LIMIT and is taken into account.
*/
if (create_sort_index(thd, curr_join,
curr_join->group_list ?
curr_join->group_list : curr_join->order,
curr_join->select_limit,
(select_options & OPTION_FOUND_ROWS ?
HA_POS_ERROR : unit->select_limit_cnt)))
DBUG_VOID_RETURN;
}
}
curr_join->having= curr_join->tmp_having;
thd->proc_info="Sending data";
error= thd->net.report_error ? -1 :
do_select(curr_join, curr_fields_list, NULL, procedure);
thd->limit_found_rows= curr_join->send_records;
thd->examined_row_count= curr_join->examined_rows;
DBUG_VOID_RETURN;
}
/*
Clean up join. Return error that hold JOIN.
*/
int
JOIN::cleanup()
{
DBUG_ENTER("JOIN::cleanup");
select_lex->join= 0;
if (tmp_join)
{
if (join_tab != tmp_join->join_tab)
{
JOIN_TAB *tab, *end;
for (tab= join_tab, end= tab+tables ; tab != end ; tab++)
{
tab->cleanup();
}
}
tmp_join->tmp_join= 0;
tmp_table_param.copy_field=0;
DBUG_RETURN(tmp_join->cleanup());
}
lock=0; // It's faster to unlock later
join_free(1);
if (exec_tmp_table1)
free_tmp_table(thd, exec_tmp_table1);
if (exec_tmp_table2)
free_tmp_table(thd, exec_tmp_table2);
delete select;
delete_dynamic(&keyuse);
delete procedure;
for (SELECT_LEX_UNIT *lex_unit= select_lex->first_inner_unit();
lex_unit != 0;
lex_unit= lex_unit->next_unit())
{
error|= lex_unit->cleanup();
}
DBUG_RETURN(error);
}
int
mysql_select(THD *thd, Item ***rref_pointer_array,
TABLE_LIST *tables, uint wild_num, List<Item> &fields,
COND *conds, uint og_num, ORDER *order, ORDER *group,
Item *having, ORDER *proc_param, ulong select_options,
select_result *result, SELECT_LEX_UNIT *unit,
SELECT_LEX *select_lex)
{
int err;
bool free_join= 1;
DBUG_ENTER("mysql_select");
JOIN *join;
if (select_lex->join != 0)
{
join= select_lex->join;
// is it single SELECT in derived table, called in derived table creation
if (select_lex->linkage != DERIVED_TABLE_TYPE ||
(select_options & SELECT_DESCRIBE))
{
if (select_lex->linkage != GLOBAL_OPTIONS_TYPE)
{
//here is EXPLAIN of subselect or derived table
if (join->change_result(result))
{
DBUG_RETURN(-1);
}
}
else
{
if (join->prepare(rref_pointer_array, tables, wild_num,
conds, og_num, order, group, having, proc_param,
select_lex, unit))
{
goto err;
}
}
}
free_join= 0;
join->select_options= select_options;
}
else
{
if (!(join= new JOIN(thd, fields, select_options, result)))
DBUG_RETURN(-1);
thd->proc_info="init";
thd->used_tables=0; // Updated by setup_fields
if (join->prepare(rref_pointer_array, tables, wild_num,
conds, og_num, order, group, having, proc_param,
select_lex, unit))
{
goto err;
}
}
if ((err= join->optimize()))
{
goto err; // 1
}
if (thd->lex->describe & DESCRIBE_EXTENDED)
{
join->conds_history= join->conds;
join->having_history= (join->having?join->having:join->tmp_having);
}
if (thd->net.report_error)
goto err;
join->exec();
if (thd->lex->describe & DESCRIBE_EXTENDED)
{
select_lex->where= join->conds_history;
select_lex->having= join->having_history;
}
err:
if (free_join)
{
thd->proc_info="end";
err= join->cleanup();
if (thd->net.report_error)
err= -1;
delete join;
DBUG_RETURN(err);
}
DBUG_RETURN(join->error);
}
/*****************************************************************************
Create JOIN_TABS, make a guess about the table types,
Approximate how many records will be used in each table
*****************************************************************************/
static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select,
TABLE *table,
const key_map *keys,ha_rows limit)
{
int error;
DBUG_ENTER("get_quick_record_count");
if (select)
{
select->head=table;
table->reginfo.impossible_range=0;
if ((error= select->test_quick_select(thd, *(key_map *)keys,(table_map) 0,
limit, 0)) == 1)
DBUG_RETURN(select->quick->records);
if (error == -1)
{
table->reginfo.impossible_range=1;
DBUG_RETURN(0);
}
DBUG_PRINT("warning",("Couldn't use record count on const keypart"));
}
DBUG_RETURN(HA_POS_ERROR); /* This shouldn't happend */
}
/*
Calculate the best possible join and initialize the join structure
RETURN VALUES
0 ok
1 Fatal error
*/
static bool
make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
DYNAMIC_ARRAY *keyuse_array)
{
int error;
uint i,table_count,const_count,key;
table_map found_const_table_map, all_table_map, found_ref, refs;
key_map const_ref, eq_part;
TABLE **table_vector;
JOIN_TAB *stat,*stat_end,*s,**stat_ref;
KEYUSE *keyuse,*start_keyuse;
table_map outer_join=0;
JOIN_TAB *stat_vector[MAX_TABLES+1];
DBUG_ENTER("make_join_statistics");
table_count=join->tables;
stat=(JOIN_TAB*) join->thd->calloc(sizeof(JOIN_TAB)*table_count);
stat_ref=(JOIN_TAB**) join->thd->alloc(sizeof(JOIN_TAB*)*MAX_TABLES);
table_vector=(TABLE**) join->thd->alloc(sizeof(TABLE*)*(table_count*2));
if (!stat || !stat_ref || !table_vector)
DBUG_RETURN(1); // Eom /* purecov: inspected */
join->best_ref=stat_vector;
stat_end=stat+table_count;
found_const_table_map= all_table_map=0;
const_count=0;
for (s=stat,i=0 ; tables ; s++,tables=tables->next,i++)
{
TABLE *table;
stat_vector[i]=s;
s->keys.init();
s->const_keys.init();
s->checked_keys.init();
s->needed_reg.init();
table_vector[i]=s->table=table=tables->table;
table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);// record count
table->quick_keys.clear_all();
table->reginfo.join_tab=s;
table->reginfo.not_exists_optimize=0;
bzero((char*) table->const_key_parts, sizeof(key_part_map)*table->keys);
all_table_map|= table->map;
s->join=join;
s->info=0; // For describe
if ((s->on_expr=tables->on_expr))
{
/* Left join */
if (!table->file->records)
{ // Empty table
s->key_dependent=s->dependent=0; // Ignore LEFT JOIN depend.
set_position(join,const_count++,s,(KEYUSE*) 0);
continue;
}
s->key_dependent=s->dependent=
s->on_expr->used_tables() & ~(table->map);
if (table->outer_join & JOIN_TYPE_LEFT)
s->dependent|=stat_vector[i-1]->dependent | table_vector[i-1]->map;
if (tables->outer_join & JOIN_TYPE_RIGHT)
s->dependent|=tables->next->table->map;
outer_join|=table->map;
continue;
}
if (tables->straight) // We don't have to move this
s->dependent= table_vector[i-1]->map | stat_vector[i-1]->dependent;
else
s->dependent=(table_map) 0;
s->key_dependent=(table_map) 0;
if ((table->system || table->file->records <= 1) && ! s->dependent &&
!(table->file->table_flags() & HA_NOT_EXACT_COUNT) &&
!table->fulltext_searched)
{
set_position(join,const_count++,s,(KEYUSE*) 0);
}
}
stat_vector[i]=0;
join->outer_join=outer_join;
/*
If outer join: Re-arrange tables in stat_vector so that outer join
tables are after all tables it is dependent of.
For example: SELECT * from A LEFT JOIN B ON B.c=C.c, C WHERE A.C=C.C
Will shift table B after table C.
*/
if (outer_join)
{
table_map used_tables=0L;
for (i=0 ; i < join->tables-1 ; i++)
{
if (stat_vector[i]->dependent & ~used_tables)
{
JOIN_TAB *save= stat_vector[i];
uint j;
for (j=i+1;
j < join->tables && stat_vector[j]->dependent & ~used_tables;
j++)
{
JOIN_TAB *tmp=stat_vector[j]; // Move element up
stat_vector[j]=save;
save=tmp;
}
if (j == join->tables)
{
join->tables=0; // Don't use join->table
my_error(ER_WRONG_OUTER_JOIN,MYF(0));
DBUG_RETURN(1);
}
stat_vector[i]=stat_vector[j];
stat_vector[j]=save;
}
used_tables|= stat_vector[i]->table->map;
}
}
if (conds || outer_join)
if (update_ref_and_keys(join->thd, keyuse_array, stat, join->tables,
conds, ~outer_join, join->select_lex))
DBUG_RETURN(1);
/* Read tables with 0 or 1 rows (system tables) */
join->const_table_map= 0;
for (POSITION *p_pos=join->positions, *p_end=p_pos+const_count;
p_pos < p_end ;
p_pos++)
{
int tmp;
s= p_pos->table;
s->type=JT_SYSTEM;
join->const_table_map|=s->table->map;
if ((tmp=join_read_const_table(s, p_pos)))
{
if (tmp > 0)
DBUG_RETURN(1); // Fatal error
}
else
found_const_table_map|= s->table->map;
}
/* loop until no more const tables are found */
int ref_changed;
do
{
ref_changed = 0;
found_ref=0;
/*
We only have to loop from stat_vector + const_count as
set_position() will move all const_tables first in stat_vector
*/
for (JOIN_TAB **pos=stat_vector+const_count ; (s= *pos) ; pos++)
{
TABLE *table=s->table;
if (s->dependent) // If dependent on some table
{
// All dep. must be constants
if (s->dependent & ~(found_const_table_map))
continue;
if (table->file->records <= 1L &&
!(table->file->table_flags() & HA_NOT_EXACT_COUNT))
{ // system table
int tmp= 0;
s->type=JT_SYSTEM;
join->const_table_map|=table->map;
set_position(join,const_count++,s,(KEYUSE*) 0);
if ((tmp= join_read_const_table(s,join->positions+const_count-1)))
{
if (tmp > 0)
DBUG_RETURN(1); // Fatal error
}
else
found_const_table_map|= table->map;
continue;
}
}
/* check if table can be read by key or table only uses const refs */
if ((keyuse=s->keyuse))
{
s->type= JT_REF;
while (keyuse->table == table)
{
start_keyuse=keyuse;
key=keyuse->key;
s->keys.set_bit(key); // QQ: remove this ?
refs=0;
const_ref.clear_all();
eq_part.clear_all();
do
{
if (keyuse->val->type() != Item::NULL_ITEM && !keyuse->optimize)
{
if (!((~found_const_table_map) & keyuse->used_tables))
const_ref.set_bit(keyuse->keypart);
else
refs|=keyuse->used_tables;
eq_part.set_bit(keyuse->keypart);
}
keyuse++;
} while (keyuse->table == table && keyuse->key == key);
if (eq_part.is_prefix(table->key_info[key].key_parts) &&
((table->key_info[key].flags & (HA_NOSAME | HA_END_SPACE_KEY)) ==
HA_NOSAME) &&
!table->fulltext_searched)
{
if (const_ref == eq_part)
{ // Found everything for ref.
int tmp;
ref_changed = 1;
s->type= JT_CONST;
join->const_table_map|=table->map;
set_position(join,const_count++,s,start_keyuse);
if (create_ref_for_key(join, s, start_keyuse,
found_const_table_map))
DBUG_RETURN(1);
if ((tmp=join_read_const_table(s,
join->positions+const_count-1)))
{
if (tmp > 0)
DBUG_RETURN(1); // Fatal error
}
else
found_const_table_map|= table->map;
break;
}
else
found_ref|= refs; // Table is const if all refs are const
}
}
}
}
} while (join->const_table_map & found_ref && ref_changed);
/* Calc how many (possible) matched records in each table */
for (s=stat ; s < stat_end ; s++)
{
if (s->type == JT_SYSTEM || s->type == JT_CONST)
{
/* Only one matching row */
s->found_records=s->records=s->read_time=1; s->worst_seeks=1.0;
continue;
}
/* Approximate found rows and time to read them */
s->found_records=s->records=s->table->file->records;
s->read_time=(ha_rows) s->table->file->scan_time();
/*
Set a max range of how many seeks we can expect when using keys
This is can't be to high as otherwise we are likely to use
table scan.
*/
s->worst_seeks= min((double) s->found_records / 10,
(double) s->read_time*3);
if (s->worst_seeks < 2.0) // Fix for small tables
s->worst_seeks=2.0;
if (! s->const_keys.is_clear_all())
{
ha_rows records;
SQL_SELECT *select;
select= make_select(s->table, found_const_table_map,
found_const_table_map,
s->on_expr ? s->on_expr : conds,
&error);
records= get_quick_record_count(join->thd, select, s->table,
&s->const_keys, join->row_limit);
s->quick=select->quick;
s->needed_reg=select->needed_reg;
select->quick=0;
if (records == 0 && s->table->reginfo.impossible_range)
{
/*
Impossible WHERE or ON expression
In case of ON, we mark that the we match one empty NULL row.
In case of WHERE, don't set found_const_table_map to get the
caller to abort with a zero row result.
*/
join->const_table_map|= s->table->map;
set_position(join,const_count++,s,(KEYUSE*) 0);
s->type= JT_CONST;
if (s->on_expr)
{
/* Generate empty row */
s->info= "Impossible ON condition";
found_const_table_map|= s->table->map;
s->type= JT_CONST;
mark_as_null_row(s->table); // All fields are NULL
}
}
if (records != HA_POS_ERROR)
{
s->found_records=records;
s->read_time= (ha_rows) (s->quick ? s->quick->read_time : 0.0);
}
delete select;
}
}
/* Find best combination and return it */
join->join_tab=stat;
join->map2table=stat_ref;
join->table= join->all_tables=table_vector;
join->const_tables=const_count;
join->found_const_table_map=found_const_table_map;
if (join->const_tables != join->tables)
{
optimize_keyuse(join, keyuse_array);
find_best_combination(join,all_table_map & ~join->const_table_map);
}
else
{
memcpy((gptr) join->best_positions,(gptr) join->positions,
sizeof(POSITION)*join->const_tables);
join->best_read=1.0;
}
DBUG_RETURN(join->thd->killed || get_best_combination(join));
}
/*****************************************************************************
Check with keys are used and with tables references with tables
Updates in stat:
keys Bitmap of all used keys
const_keys Bitmap of all keys with may be used with quick_select
keyuse Pointer to possible keys
*****************************************************************************/
typedef struct key_field_t { // Used when finding key fields
Field *field;
Item *val; // May be empty if diff constant
uint level;
uint optimize;
bool eq_func;
/*
If true, the condition this struct represents will not be satisfied
when val IS NULL.
*/
bool null_rejecting;
} KEY_FIELD;
/* Values in optimize */
#define KEY_OPTIMIZE_EXISTS 1
#define KEY_OPTIMIZE_REF_OR_NULL 2
/*
Merge new key definitions to old ones, remove those not used in both
This is called for OR between different levels
To be able to do 'ref_or_null' we merge a comparison of a column
and 'column IS NULL' to one test. This is useful for sub select queries
that are internally transformed to something like:
SELECT * FROM t1 WHERE t1.key=outer_ref_field or t1.key IS NULL
KEY_FIELD::null_rejecting is processed as follows:
result has null_rejecting=true if it is set for both ORed references.
for example:
(t2.key = t1.field OR t2.key = t1.field) -> null_rejecting=true
(t2.key = t1.field OR t2.key <=> t1.field) -> null_rejecting=false
*/
static KEY_FIELD *
merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
uint and_level)
{
if (start == new_fields)
return start; // Impossible or
if (new_fields == end)
return start; // No new fields, skip all
KEY_FIELD *first_free=new_fields;
/* Mark all found fields in old array */
for (; new_fields != end ; new_fields++)
{
for (KEY_FIELD *old=start ; old != first_free ; old++)
{
if (old->field == new_fields->field)
{
if (new_fields->val->used_tables())
{
/*
If the value matches, we can use the key reference.
If not, we keep it until we have examined all new values
*/
if (old->val->eq(new_fields->val, old->field->binary()))
{
old->level= and_level;
old->optimize= ((old->optimize & new_fields->optimize &
KEY_OPTIMIZE_EXISTS) |
((old->optimize | new_fields->optimize) &
KEY_OPTIMIZE_REF_OR_NULL));
old->null_rejecting= (old->null_rejecting &&
new_fields->null_rejecting);
}
}
else if (old->eq_func && new_fields->eq_func &&
old->val->eq(new_fields->val, old->field->binary()))
{
old->level= and_level;
old->optimize= ((old->optimize & new_fields->optimize &
KEY_OPTIMIZE_EXISTS) |
((old->optimize | new_fields->optimize) &
KEY_OPTIMIZE_REF_OR_NULL));
old->null_rejecting= (old->null_rejecting &&
new_fields->null_rejecting);
}
else if (old->eq_func && new_fields->eq_func &&
(old->val->is_null() || new_fields->val->is_null()))
{
/* field = expression OR field IS NULL */
old->level= and_level;
old->optimize= KEY_OPTIMIZE_REF_OR_NULL;
/* Remember the NOT NULL value */
if (old->val->is_null())
old->val= new_fields->val;
/* The referred expression can be NULL: */
old->null_rejecting= 0;
}
else
{
/*
We are comparing two different const. In this case we can't
use a key-lookup on this so it's better to remove the value
and let the range optimzier handle it
*/
if (old == --first_free) // If last item
break;
*old= *first_free; // Remove old value
old--; // Retry this value
}
}
}
}
/* Remove all not used items */
for (KEY_FIELD *old=start ; old != first_free ;)
{
if (old->level != and_level)
{ // Not used in all levels
if (old == --first_free)
break;
*old= *first_free; // Remove old value
continue;
}
old++;
}
return first_free;
}
/*
Add a possible key to array of possible keys if it's usable as a key
SYNPOSIS
add_key_field()
key_fields Pointer to add key, if usable
and_level And level, to be stored in KEY_FIELD
field Field used in comparision
eq_func True if we used =, <=> or IS NULL
value Value used for comparison with field
usable_tables Tables which can be used for key optimization
NOTES
If we are doing a NOT NULL comparison on a NOT NULL field in a outer join
table, we store this to be able to do not exists optimization later.
RETURN
*key_fields is incremented if we stored a key in the array
*/
static void
add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
Field *field,bool eq_func,Item **value, uint num_values,
table_map usable_tables)
{
uint exists_optimize= 0;
if (!(field->flags & PART_KEY_FLAG))
{
// Don't remove column IS NULL on a LEFT JOIN table
if (!eq_func || (*value)->type() != Item::NULL_ITEM ||
!field->table->maybe_null || field->null_ptr)
return; // Not a key. Skip it
exists_optimize= KEY_OPTIMIZE_EXISTS;
}
else
{
table_map used_tables=0;
bool optimizable=0;
for (uint i=0; i<num_values; i++)
{
used_tables|=(value[i])->used_tables();
if (!((value[i])->used_tables() & (field->table->map | RAND_TABLE_BIT)))
optimizable=1;
}
if (!optimizable)
return;
if (!(usable_tables & field->table->map))
{
if (!eq_func || (*value)->type() != Item::NULL_ITEM ||
!field->table->maybe_null || field->null_ptr)
return; // Can't use left join optimize
exists_optimize= KEY_OPTIMIZE_EXISTS;
}
else
{
JOIN_TAB *stat=field->table->reginfo.join_tab;
key_map possible_keys=field->key_start;
possible_keys.intersect(field->table->keys_in_use_for_query);
stat[0].keys.merge(possible_keys); // Add possible keys
/*
Save the following cases:
Field op constant
Field LIKE constant where constant doesn't start with a wildcard
Field = field2 where field2 is in a different table
Field op formula
Field IS NULL
Field IS NOT NULL
Field BETWEEN ...
Field IN ...
*/
stat[0].key_dependent|=used_tables;
bool is_const=1;
for (uint i=0; i<num_values; i++)
is_const&= value[i]->const_item();
if (is_const)
stat[0].const_keys.merge(possible_keys);
/*
We can't always use indexes when comparing a string index to a
number. cmp_type() is checked to allow compare of dates to numbers.
eq_func is NEVER true when num_values > 1
*/
if (!eq_func)
return;
if (field->result_type() == STRING_RESULT)
{
if ((*value)->result_type() != STRING_RESULT)
{
if (field->cmp_type() != (*value)->result_type())
return;
}
else
{
/*
We can't use indexes if the effective collation
of the operation differ from the field collation.
We can also not used index on a text column, as the column may
contain 'x' 'xt' 'x ' and 'read_next_same' will stop after
'x' when searching for WHERE col='x '
*/
if (field->cmp_type() == STRING_RESULT &&
(((Field_str*)field)->charset() != cond->compare_collation() ||
((*value)->type() != Item::NULL_ITEM &&
(field->flags & BLOB_FLAG) && !field->binary())))
return;
}
}
}
}
DBUG_ASSERT(num_values == 1);
/*
For the moment eq_func is always true. This slot is reserved for future
extensions where we want to remembers other things than just eq comparisons
*/
DBUG_ASSERT(eq_func);
/* Store possible eq field */
(*key_fields)->field= field;
(*key_fields)->eq_func= eq_func;
(*key_fields)->val= *value;
(*key_fields)->level= and_level;
(*key_fields)->optimize= exists_optimize;
/*
If the condition has form "tbl.keypart = othertbl.field" and
othertbl.field can be NULL, there will be no matches if othertbl.field
has NULL value.
We use null_rejecting in add_not_null_conds() to add
'othertbl.field IS NOT NULL' to tab->select_cond.
*/
(*key_fields)->null_rejecting= ((cond->functype() == Item_func::EQ_FUNC) &&
((*value)->type() == Item::FIELD_ITEM) &&
((Item_field*)*value)->field->maybe_null());
(*key_fields)++;
}
/*
SYNOPSIS
add_key_fields()
key_fields Add KEY_FIELD entries to this array (and move the
pointer)
and_level AND-level (a value that is different for every n-way
AND operation)
cond Condition to analyze
usable_tables Value to pass to add_key_field
*/
static void
add_key_fields(KEY_FIELD **key_fields,uint *and_level,
COND *cond, table_map usable_tables)
{
if (cond->type() == Item_func::COND_ITEM)
{
List_iterator_fast<Item> li(*((Item_cond*) cond)->argument_list());
KEY_FIELD *org_key_fields= *key_fields;
if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
{
Item *item;
while ((item=li++))
add_key_fields(key_fields,and_level,item,usable_tables);
for (; org_key_fields != *key_fields ; org_key_fields++)
org_key_fields->level= *and_level;
}
else
{
(*and_level)++;
add_key_fields(key_fields,and_level,li++,usable_tables);
Item *item;
while ((item=li++))
{
KEY_FIELD *start_key_fields= *key_fields;
(*and_level)++;
add_key_fields(key_fields,and_level,item,usable_tables);
*key_fields=merge_key_fields(org_key_fields,start_key_fields,
*key_fields,++(*and_level));
}
}
return;
}
/* If item is of type 'field op field/constant' add it to key_fields */
if (cond->type() != Item::FUNC_ITEM)
return;
Item_func *cond_func= (Item_func*) cond;
switch (cond_func->select_optimize()) {
case Item_func::OPTIMIZE_NONE:
break;
case Item_func::OPTIMIZE_KEY:
// BETWEEN, IN, NOT
if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM &&
!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
add_key_field(key_fields,*and_level,cond_func,
((Item_field*)(cond_func->key_item()->real_item()))->field,
cond_func->argument_count() == 2 &&
cond_func->functype() == Item_func::IN_FUNC &&
!((Item_func_in*)cond_func)->negated,
cond_func->arguments()+1, cond_func->argument_count()-1,
usable_tables);
break;
case Item_func::OPTIMIZE_OP:
{
bool equal_func=(cond_func->functype() == Item_func::EQ_FUNC ||
cond_func->functype() == Item_func::EQUAL_FUNC);
if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
!(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
{
add_key_field(key_fields,*and_level,cond_func,
((Item_field*) (cond_func->arguments()[0])->real_item())
->field,
equal_func,
cond_func->arguments()+1, 1, usable_tables);
}
if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
cond_func->functype() != Item_func::LIKE_FUNC &&
!(cond_func->arguments()[1]->used_tables() & OUTER_REF_TABLE_BIT))
{
add_key_field(key_fields,*and_level,cond_func,
((Item_field*) (cond_func->arguments()[1])->real_item())
->field,
equal_func,
cond_func->arguments(),1,usable_tables);
}
break;
}
case Item_func::OPTIMIZE_NULL:
/* column_name IS [NOT] NULL */
if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
{
Item *tmp=new Item_null;
if (unlikely(!tmp)) // Should never be true
return;
add_key_field(key_fields,*and_level,cond_func,
((Item_field*) (cond_func->arguments()[0])->real_item())
->field,
cond_func->functype() == Item_func::ISNULL_FUNC,
&tmp, 1, usable_tables);
}
break;
}
return;
}
/*
Add all keys with uses 'field' for some keypart
If field->and_level != and_level then only mark key_part as const_part
*/
static uint
max_part_bit(key_part_map bits)
{
uint found;
for (found=0; bits & 1 ; found++,bits>>=1) ;
return found;
}
static void
add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field)
{
Field *field=key_field->field;
TABLE *form= field->table;
KEYUSE keyuse;
if (key_field->eq_func && !(key_field->optimize & KEY_OPTIMIZE_EXISTS))
{
for (uint key=0 ; key < form->keys ; key++)
{
if (!(form->keys_in_use_for_query.is_set(key)))
continue;
if (form->key_info[key].flags & HA_FULLTEXT)
continue; // ToDo: ft-keys in non-ft queries. SerG
uint key_parts= (uint) form->key_info[key].key_parts;
for (uint part=0 ; part < key_parts ; part++)
{
if (field->eq(form->key_info[key].key_part[part].field))
{
keyuse.table= field->table;
keyuse.val = key_field->val;
keyuse.key = key;
keyuse.keypart=part;
keyuse.keypart_map= (key_part_map) 1 << part;
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;
VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
}
}
}
}
}
#define FT_KEYPART (MAX_REF_PARTS+10)
static void
add_ft_keys(DYNAMIC_ARRAY *keyuse_array,
JOIN_TAB *stat,COND *cond,table_map usable_tables)
{
Item_func_match *cond_func=NULL;
if (!cond)
return;
if (cond->type() == Item::FUNC_ITEM)
{
Item_func *func=(Item_func *)cond;
Item_func::Functype functype= func->functype();
if (functype == Item_func::FT_FUNC)
cond_func=(Item_func_match *)cond;
else if (func->arg_count == 2)
{
Item_func *arg0=(Item_func *)(func->arguments()[0]),
*arg1=(Item_func *)(func->arguments()[1]);
if (arg1->const_item() &&
((functype == Item_func::GE_FUNC && arg1->val()> 0) ||
(functype == Item_func::GT_FUNC && arg1->val()>=0)) &&
arg0->type() == Item::FUNC_ITEM &&
arg0->functype() == Item_func::FT_FUNC)
cond_func=(Item_func_match *) arg0;
else if (arg0->const_item() &&
((functype == Item_func::LE_FUNC && arg0->val()> 0) ||
(functype == Item_func::LT_FUNC && arg0->val()>=0)) &&
arg1->type() == Item::FUNC_ITEM &&
arg1->functype() == Item_func::FT_FUNC)
cond_func=(Item_func_match *) arg1;
}
}
else if (cond->type() == Item::COND_ITEM)
{
List_iterator_fast<Item> li(*((Item_cond*) cond)->argument_list());
if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
{
Item *item;
while ((item=li++))
add_ft_keys(keyuse_array,stat,item,usable_tables);
}
}
if (!cond_func || cond_func->key == NO_SUCH_KEY ||
!(usable_tables & cond_func->table->map))
return;
KEYUSE keyuse;
keyuse.table= cond_func->table;
keyuse.val = cond_func;
keyuse.key = cond_func->key;
keyuse.keypart= FT_KEYPART;
keyuse.used_tables=cond_func->key_item()->used_tables();
keyuse.optimize= 0;
keyuse.keypart_map= 0;
VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
}
static int
sort_keyuse(KEYUSE *a,KEYUSE *b)
{
int res;
if (a->table->tablenr != b->table->tablenr)
return (int) (a->table->tablenr - b->table->tablenr);
if (a->key != b->key)
return (int) (a->key - b->key);
if (a->keypart != b->keypart)
return (int) (a->keypart - b->keypart);
// Place const values before other ones
if ((res= test((a->used_tables & ~OUTER_REF_TABLE_BIT)) -
test((b->used_tables & ~OUTER_REF_TABLE_BIT))))
return res;
/* Place rows that are not 'OPTIMIZE_REF_OR_NULL' first */
return (int) ((a->optimize & KEY_OPTIMIZE_REF_OR_NULL) -
(b->optimize & KEY_OPTIMIZE_REF_OR_NULL));
}
/*
Update keyuse array with all possible keys we can use to fetch rows
SYNOPSIS
update_ref_and_keys()
thd
keyuse OUT Put here ordered array of KEYUSE structures
join_tab Array in tablenr_order
tables Number of tables in join
cond WHERE condition (note that the function analyzes
join_tab[i]->on_expr too)
normal_tables tables not inner w.r.t some outer join (ones for which
we can make ref access based the WHERE clause)
select_lex current SELECT
RETURN
0 - OK
1 - Out of memory.
*/
static bool
update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
uint tables, COND *cond, table_map normal_tables,
SELECT_LEX *select_lex)
{
uint and_level,i,found_eq_constant;
KEY_FIELD *key_fields, *end, *field;
if (!(key_fields=(KEY_FIELD*)
thd->alloc(sizeof(key_fields[0])*
(thd->lex->current_select->cond_count+1)*2)))
return TRUE; /* purecov: inspected */
and_level= 0;
field= end= key_fields;
if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64))
return TRUE;
if (cond)
{
add_key_fields(&end,&and_level,cond,normal_tables);
for (; field != end ; field++)
{
add_key_part(keyuse,field);
/* Mark that we can optimize LEFT JOIN */
if (field->val->type() == Item::NULL_ITEM &&
!field->field->real_maybe_null())
field->field->table->reginfo.not_exists_optimize=1;
}
}
for (i=0 ; i < tables ; i++)
{
if (join_tab[i].on_expr)
{
add_key_fields(&end,&and_level,join_tab[i].on_expr,
join_tab[i].table->map);
}
}
/* fill keyuse with found key parts */
for ( ; field != end ; field++)
add_key_part(keyuse,field);
if (select_lex->ftfunc_list->elements)
{
add_ft_keys(keyuse,join_tab,cond,normal_tables);
}
/*
Special treatment for ft-keys.
Remove the following things from KEYUSE:
- ref if there is a keypart which is a ref and a const.
- keyparts without previous keyparts.
*/
if (keyuse->elements)
{
KEYUSE end,*prev,*save_pos,*use;
qsort(keyuse->buffer,keyuse->elements,sizeof(KEYUSE),
(qsort_cmp) sort_keyuse);
bzero((char*) &end,sizeof(end)); /* Add for easy testing */
VOID(insert_dynamic(keyuse,(gptr) &end));
use=save_pos=dynamic_element(keyuse,0,KEYUSE*);
prev=&end;
found_eq_constant=0;
for (i=0 ; i < keyuse->elements-1 ; i++,use++)
{
if (!use->used_tables)
use->table->const_key_parts[use->key]|= use->keypart_map;
if (use->keypart != FT_KEYPART)
{
if (use->key == prev->key && use->table == prev->table)
{
if (prev->keypart+1 < use->keypart ||
prev->keypart == use->keypart && found_eq_constant)
continue; /* remove */
}
else if (use->keypart != 0) // First found must be 0
continue;
}
*save_pos= *use;
prev=use;
found_eq_constant= !use->used_tables;
/* Save ptr to first use */
if (!use->table->reginfo.join_tab->keyuse)
use->table->reginfo.join_tab->keyuse=save_pos;
use->table->reginfo.join_tab->checked_keys.set_bit(use->key);
save_pos++;
}
i=(uint) (save_pos-(KEYUSE*) keyuse->buffer);
VOID(set_dynamic(keyuse,(gptr) &end,i));
keyuse->elements=i;
}
return FALSE;
}
/*
Update some values in keyuse for faster find_best_combination() loop
*/
static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array)
{
KEYUSE *end,*keyuse= dynamic_element(keyuse_array, 0, KEYUSE*);
for (end= keyuse+ keyuse_array->elements ; keyuse < end ; keyuse++)
{
table_map map;
/*
If we find a ref, assume this table matches a proportional
part of this table.
For example 100 records matching a table with 5000 records
gives 5000/100 = 50 records per key
Constant tables are ignored.
To avoid bad matches, we don't make ref_table_rows less than 100.
*/
keyuse->ref_table_rows= ~(ha_rows) 0; // If no ref
if (keyuse->used_tables &
(map= (keyuse->used_tables & ~join->const_table_map &
~OUTER_REF_TABLE_BIT)))
{
uint tablenr;
for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ;
if (map == 1) // Only one table
{
TABLE *tmp_table=join->all_tables[tablenr];
keyuse->ref_table_rows= max(tmp_table->file->records, 100);
}
}
/*
Outer reference (external field) is constant for single executing
of subquery
*/
if (keyuse->used_tables == OUTER_REF_TABLE_BIT)
keyuse->ref_table_rows= 1;
}
}
/*****************************************************************************
Go through all combinations of not marked tables and find the one
which uses least records
*****************************************************************************/
/* Save const tables first as used tables */
static void
set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key)
{
join->positions[idx].table= table;
join->positions[idx].key=key;
join->positions[idx].records_read=1.0; /* This is a const table */
/* Move the const table as down as possible in best_ref */
JOIN_TAB **pos=join->best_ref+idx+1;
JOIN_TAB *next=join->best_ref[idx];
for (;next != table ; pos++)
{
JOIN_TAB *tmp=pos[0];
pos[0]=next;
next=tmp;
}
join->best_ref[idx]=table;
}
static void
find_best_combination(JOIN *join, table_map rest_tables)
{
DBUG_ENTER("find_best_combination");
join->best_read=DBL_MAX;
find_best(join,rest_tables, join->const_tables,1.0,0.0);
DBUG_VOID_RETURN;
}
static void
find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
double read_time)
{
ha_rows rec;
double tmp;
THD *thd= join->thd;
if (!rest_tables)
{
DBUG_PRINT("best",("read_time: %g record_count: %g",read_time,
record_count));
read_time+=record_count/(double) TIME_FOR_COMPARE;
if (join->sort_by_table &&
join->sort_by_table !=
join->positions[join->const_tables].table->table)
read_time+=record_count; // We have to make a temp table
if (read_time < join->best_read)
{
memcpy((gptr) join->best_positions,(gptr) join->positions,
sizeof(POSITION)*idx);
join->best_read=read_time;
}
return;
}
if (read_time+record_count/(double) TIME_FOR_COMPARE >= join->best_read)
return; /* Found better before */
JOIN_TAB *s;
double best_record_count=DBL_MAX,best_read_time=DBL_MAX;
for (JOIN_TAB **pos=join->best_ref+idx ; (s=*pos) ; pos++)
{
table_map real_table_bit=s->table->map;
if ((rest_tables & real_table_bit) && !(rest_tables & s->dependent))
{
double best,best_time,records;
best=best_time=records=DBL_MAX;
KEYUSE *best_key=0;
uint best_max_key_part=0;
my_bool found_constraint= 0;
if (s->keyuse)
{ /* Use key if possible */
TABLE *table=s->table;
KEYUSE *keyuse,*start_key=0;
double best_records=DBL_MAX;
uint max_key_part=0;
/* Test how we can use keys */
rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE; // Assumed records/key
for (keyuse=s->keyuse ; keyuse->table == table ;)
{
key_part_map found_part=0;
table_map found_ref=0;
uint key=keyuse->key;
KEY *keyinfo=table->key_info+key;
bool ft_key=(keyuse->keypart == FT_KEYPART);
uint found_ref_or_null= 0;
/* Calculate how many key segments of the current key we can use */
start_key=keyuse;
do
{
uint keypart=keyuse->keypart;
table_map best_part_found_ref= 0;
double best_prev_record_reads= DBL_MAX;
do
{
if (!(rest_tables & keyuse->used_tables) &&
!(found_ref_or_null & keyuse->optimize))
{
found_part|=keyuse->keypart_map;
double tmp= prev_record_reads(join,
(found_ref |
keyuse->used_tables));
if (tmp < best_prev_record_reads)
{
best_part_found_ref= keyuse->used_tables;
best_prev_record_reads= tmp;
}
if (rec > keyuse->ref_table_rows)
rec= keyuse->ref_table_rows;
/*
If there is one 'key_column IS NULL' expression, we can
use this ref_or_null optimisation of this field
*/
found_ref_or_null|= (keyuse->optimize &
KEY_OPTIMIZE_REF_OR_NULL);
}
keyuse++;
} while (keyuse->table == table && keyuse->key == key &&
keyuse->keypart == keypart);
found_ref|= best_part_found_ref;
} while (keyuse->table == table && keyuse->key == key);
/*
Assume that that each key matches a proportional part of table.
*/
if (!found_part && !ft_key)
continue; // Nothing usable found
if (rec < MATCHING_ROWS_IN_OTHER_TABLE)
rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables
/*
ft-keys require special treatment
*/
if (ft_key)
{
/*
Really, there should be records=0.0 (yes!)
but 1.0 would be probably safer
*/
tmp=prev_record_reads(join,found_ref);
records=1.0;
}
else
{
found_constraint= 1;
/*
Check if we found full key
*/
if (found_part == PREV_BITS(uint,keyinfo->key_parts) &&
!found_ref_or_null)
{ /* use eq key */
max_key_part= (uint) ~0;
if ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY |
HA_END_SPACE_KEY)) == HA_NOSAME)
{
tmp=prev_record_reads(join,found_ref);
records=1.0;
}
else
{
if (!found_ref)
{ // We found a const key
if (table->quick_keys.is_set(key))
records= (double) table->quick_rows[key];
else
{
/* quick_range couldn't use key! */
records= (double) s->records/rec;
}
}
else
{
if (!(records=keyinfo->rec_per_key[keyinfo->key_parts-1]))
{ // Prefere longer keys
records=
((double) s->records / (double) rec *
(1.0 +
((double) (table->max_key_length-keyinfo->key_length) /
(double) table->max_key_length)));
if (records < 2.0)
records=2.0; // Can't be as good as a unique
}
}
/* Limit the number of matched rows */
tmp= records;
set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key);
if (table->used_keys.is_set(key))
{
/* we can use only index tree */
uint keys_per_block= table->file->block_size/2/
(keyinfo->key_length+table->file->ref_length)+1;
tmp=record_count*(tmp+keys_per_block-1)/keys_per_block;
}
else
tmp=record_count*min(tmp,s->worst_seeks);
}
}
else
{
/*
Use as much key-parts as possible and a uniq key is better
than a not unique key
Set tmp to (previous record count) * (records / combination)
*/
if ((found_part & 1) &&
(!(table->file->index_flags(key,0,0) & HA_ONLY_WHOLE_INDEX) ||
found_part == PREV_BITS(uint,keyinfo->key_parts)))
{
max_key_part=max_part_bit(found_part);
/*
Check if quick_range could determinate how many rows we
will match
*/
if (table->quick_keys.is_set(key) &&
table->quick_key_parts[key] == max_key_part)
tmp=records= (double) table->quick_rows[key];
else
{
/* Check if we have statistic about the distribution */
if ((records=keyinfo->rec_per_key[max_key_part-1]))
tmp=records;
else
{
/*
Assume that the first key part matches 1% of the file
and that the hole key matches 10 (duplicates) or 1
(unique) records.
Assume also that more key matches proportionally more
records
This gives the formula:
records= (x * (b-a) + a*c-b)/(c-1)
b = records matched by whole key
a = records matched by first key part (10% of all records?)
c = number of key parts in key
x = used key parts (1 <= x <= c)
*/
double rec_per_key;
rec_per_key= keyinfo->rec_per_key[keyinfo->key_parts-1] ?
(double) keyinfo->rec_per_key[keyinfo->key_parts-1] :
(double) s->records/rec+1;
if (!s->records)
tmp=0;
else if (rec_per_key/(double) s->records >= 0.01)
tmp=rec_per_key;
else
{
double a=s->records*0.01;
tmp=(max_key_part * (rec_per_key - a) +
a*keyinfo->key_parts - rec_per_key)/
(keyinfo->key_parts-1);
set_if_bigger(tmp,1.0);
}
records=(ulong) tmp;
}
/*
If quick_select was used on a part of this key, we know
the maximum number of rows that the key can match.
*/
if (table->quick_keys.is_set(key) &&
table->quick_key_parts[key] <= max_key_part &&
records > (double) table->quick_rows[key])
tmp= records= (double) table->quick_rows[key];
else if (found_ref_or_null)
{
/* We need to do two key searches to find key */
tmp*= 2.0;
records*= 2.0;
}
}
/* Limit the number of matched rows */
set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key);
if (table->used_keys.is_set(key))
{
/* we can use only index tree */
uint keys_per_block= table->file->block_size/2/
(keyinfo->key_length+table->file->ref_length)+1;
tmp=record_count*(tmp+keys_per_block-1)/keys_per_block;
}
else
tmp=record_count*min(tmp,s->worst_seeks);
}
else
tmp=best_time; // Do nothing
}
} /* not ft_key */
if (tmp < best_time - records/(double) TIME_FOR_COMPARE)
{
best_time=tmp + records/(double) TIME_FOR_COMPARE;
best=tmp;
best_records=records;
best_key=start_key;
best_max_key_part=max_key_part;
}
}
records=best_records;
}
/*
Don't test table scan if it can't be better.
Prefer key lookup if we would use the same key for scanning.
Don't do a table scan on InnoDB tables, if we can read the used
parts of the row from any of the used index.
This is because table scans uses index and we would not win
anything by using a table scan.
*/
if ((records >= s->found_records || best > s->read_time) &&
!(s->quick && best_key && s->quick->index == best_key->key &&
best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&
!((s->table->file->table_flags() & HA_TABLE_SCAN_ON_INDEX) &&
! s->table->used_keys.is_clear_all() && best_key) &&
!(s->table->force_index && best_key))
{ // Check full join
ha_rows rnd_records= s->found_records;
/*
If there is a restriction on the table, assume that 25% of the
rows can be skipped on next part.
This is to force tables that this table depends on before this
table
*/
if (found_constraint)
rnd_records-= rnd_records/4;
/*
Range optimizer never proposes a RANGE if it isn't better
than FULL: so if RANGE is present, it's always preferred to FULL.
Here we estimate its cost.
*/
if (s->quick)
{
/*
For each record we:
- read record range through 'quick'
- skip rows which does not satisfy WHERE constraints
*/
tmp= record_count *
(s->quick->read_time +
(s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);
}
else
{
/* Estimate cost of reading table. */
tmp= s->table->file->scan_time();
if (s->on_expr) // Can't use join cache
{
/*
For each record we have to:
- read the whole table record
- skip rows which does not satisfy join condition
*/
tmp= record_count *
(tmp +
(s->records - rnd_records)/(double) TIME_FOR_COMPARE);
}
else
{
/* We read the table as many times as join buffer becomes full. */
tmp*= (1.0 + floor((double) cache_record_length(join,idx) *
record_count /
(double) thd->variables.join_buff_size));
/*
We don't make full cartesian product between rows in the scanned
table and existing records because we skip all rows from the
scanned table, which does not satisfy join condition when
we read the table (see flush_cached_records for details). Here we
take into account cost to read and skip these records.
*/
tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
}
}
/*
We estimate the cost of evaluating WHERE clause for found records
as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus
tmp give us total cost of using TABLE SCAN
*/
if (best == DBL_MAX ||
(tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records <
best + record_count/(double) TIME_FOR_COMPARE*records))
{
/*
If the table has a range (s->quick is set) make_join_select()
will ensure that this will be used
*/
best=tmp;
records= rows2double(rnd_records);
best_key=0;
}
}
join->positions[idx].records_read= records;
join->positions[idx].key=best_key;
join->positions[idx].table= s;
if (!best_key && idx == join->const_tables &&
s->table == join->sort_by_table &&
join->unit->select_limit_cnt >= records)
join->sort_by_table= (TABLE*) 1; // Must use temporary table
/*
Go to the next level only if there hasn't been a better key on
this level! This will cut down the search for a lot simple cases!
*/
double current_record_count=record_count*records;
double current_read_time=read_time+best;
if (best_record_count > current_record_count ||
best_read_time > current_read_time ||
idx == join->const_tables && s->table == join->sort_by_table)
{
if (best_record_count >= current_record_count &&
best_read_time >= current_read_time &&
(!(s->key_dependent & rest_tables) || records < 2.0))
{
best_record_count=current_record_count;
best_read_time=current_read_time;
}
swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
find_best(join,rest_tables & ~real_table_bit,idx+1,
current_record_count,current_read_time);
if (thd->killed)
return;
swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
}
if (join->select_options & SELECT_STRAIGHT_JOIN)
break; // Don't test all combinations
}
}
}
/*
Find how much space the prevous read not const tables takes in cache
*/
static void calc_used_field_length(THD *thd, JOIN_TAB *join_tab)
{
uint null_fields,blobs,fields,rec_length;
null_fields=blobs=fields=rec_length=0;
Field **f_ptr,*field;
for (f_ptr=join_tab->table->field ; (field= *f_ptr) ; f_ptr++)
{
if (field->query_id == thd->query_id)
{
uint flags=field->flags;
fields++;
rec_length+=field->pack_length();
if (flags & BLOB_FLAG)
blobs++;
if (!(flags & NOT_NULL_FLAG))
null_fields++;
}
}
if (null_fields)
rec_length+=(join_tab->table->null_fields+7)/8;
if (join_tab->table->maybe_null)
rec_length+=sizeof(my_bool);
if (blobs)
{
uint blob_length=(uint) (join_tab->table->file->mean_rec_length-
(join_tab->table->reclength- rec_length));
rec_length+=(uint) max(4,blob_length);
}
join_tab->used_fields=fields;
join_tab->used_fieldlength=rec_length;
join_tab->used_blobs=blobs;
}
static uint
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;
}
return length;
}
static double
prev_record_reads(JOIN *join,table_map found_ref)
{
double found=1.0;
found_ref&= ~OUTER_REF_TABLE_BIT;
for (POSITION *pos=join->positions ; found_ref ; pos++)
{
if (pos->table->table->map & found_ref)
{
found_ref&= ~pos->table->table->map;
found*=pos->records_read;
}
}
return found;
}
/*****************************************************************************
Set up join struct according to best position.
*****************************************************************************/
static bool
get_best_combination(JOIN *join)
{
uint i,tablenr;
table_map used_tables;
JOIN_TAB *join_tab,*j;
KEYUSE *keyuse;
uint table_count;
THD *thd=join->thd;
table_count=join->tables;
if (!(join->join_tab=join_tab=
(JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB)*table_count)))
return TRUE;
join->full_join=0;
used_tables= OUTER_REF_TABLE_BIT; // Outer row is already read
for (j=join_tab, tablenr=0 ; tablenr < table_count ; tablenr++,j++)
{
TABLE *form;
*j= *join->best_positions[tablenr].table;
form=join->table[tablenr]=j->table;
used_tables|= form->map;
form->reginfo.join_tab=j;
if (!j->on_expr)
form->reginfo.not_exists_optimize=0; // Only with LEFT JOIN
if (j->type == JT_CONST)
continue; // Handled in make_join_stat..
j->ref.key = -1;
j->ref.key_parts=0;
if (j->type == JT_SYSTEM)
continue;
if (j->keys.is_clear_all() || !(keyuse= join->best_positions[tablenr].key))
{
j->type=JT_ALL;
if (tablenr != join->const_tables)
join->full_join=1;
}
else if (create_ref_for_key(join, j, keyuse, used_tables))
return TRUE; // Something went wrong
}
for (i=0 ; i < table_count ; i++)
join->map2table[join->join_tab[i].table->tablenr]=join->join_tab+i;
update_depend_map(join);
return 0;
}
static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
table_map used_tables)
{
KEYUSE *keyuse=org_keyuse;
bool ftkey=(keyuse->keypart == FT_KEYPART);
THD *thd= join->thd;
uint keyparts,length,key;
TABLE *table;
KEY *keyinfo;
/* Use best key from find_best */
table=j->table;
key=keyuse->key;
keyinfo=table->key_info+key;
if (ftkey)
{
Item_func_match *ifm=(Item_func_match *)keyuse->val;
length=0;
keyparts=1;
ifm->join_key=1;
}
else
{
keyparts=length=0;
uint found_part_ref_or_null= 0;
/*
Calculate length for the used key
Stop if there is a missing key part or when we find second key_part
with KEY_OPTIMIZE_REF_OR_NULL
*/
do
{
if (!(~used_tables & keyuse->used_tables))
{
if (keyparts == keyuse->keypart &&
!(found_part_ref_or_null & keyuse->optimize))
{
keyparts++;
length+= keyinfo->key_part[keyuse->keypart].store_length;
found_part_ref_or_null|= keyuse->optimize;
}
}
keyuse++;
} while (keyuse->table == table && keyuse->key == key);
} /* not ftkey */
/* set up fieldref */
keyinfo=table->key_info+key;
j->ref.key_parts=keyparts;
j->ref.key_length=length;
j->ref.key=(int) key;
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)))
{
return TRUE;
}
j->ref.key_buff2=j->ref.key_buff+ALIGN_SIZE(length);
j->ref.key_err=1;
j->ref.null_rejecting= 0;
keyuse=org_keyuse;
store_key **ref_key= j->ref.key_copy;
byte *key_buff=j->ref.key_buff, *null_ref_key= 0;
bool keyuse_uses_no_tables= TRUE;
if (ftkey)
{
j->ref.items[0]=((Item_func*)(keyuse->val))->key_item();
if (keyuse->used_tables)
return TRUE; // not supported yet. SerG
j->type=JT_FT;
}
else
{
uint i;
for (i=0 ; i < keyparts ; keyuse++,i++)
{
while (keyuse->keypart != i ||
((~used_tables) & keyuse->used_tables))
keyuse++; /* Skip other parts */
uint maybe_null= test(keyinfo->key_part[i].null_bit);
j->ref.items[i]=keyuse->val; // Save for cond removal
if (keyuse->null_rejecting)
j->ref.null_rejecting |= 1 << i;
keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
if (!keyuse->used_tables &&
!(join->select_options & SELECT_DESCRIBE))
{ // Compare against constant
store_key_item tmp(thd, keyinfo->key_part[i].field,
(char*)key_buff + maybe_null,
maybe_null ? (char*) key_buff : 0,
keyinfo->key_part[i].length, keyuse->val);
if (thd->is_fatal_error)
return TRUE;
tmp.copy();
}
else
*ref_key++= get_store_key(thd,
keyuse,join->const_table_map,
&keyinfo->key_part[i],
(char*) key_buff,maybe_null);
/*
Remeber if we are going to use REF_OR_NULL
But only if field _really_ can be null i.e. we force JT_REF
instead of JT_REF_OR_NULL in case if field can't be null
*/
if ((keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL) && maybe_null)
null_ref_key= key_buff;
key_buff+=keyinfo->key_part[i].store_length;
}
} /* not ftkey */
*ref_key=0; // end_marker
if (j->type == JT_FT)
return 0;
if (j->type == JT_CONST)
j->table->const_table= 1;
else if (((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY |
HA_END_SPACE_KEY)) != HA_NOSAME) ||
keyparts != keyinfo->key_parts || null_ref_key)
{
/* Must read with repeat */
j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
j->ref.null_ref_key= null_ref_key;
}
else if (keyuse_uses_no_tables)
{
/*
This happen if we are using a constant expression in the ON part
of an LEFT JOIN.
SELECT * FROM a LEFT JOIN b ON b.key=30
Here we should not mark the table as a 'const' as a field may
have a 'normal' value or a NULL value.
*/
j->type=JT_CONST;
}
else
j->type=JT_EQ_REF;
return 0;
}
static store_key *
get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables,
KEY_PART_INFO *key_part, char *key_buff, uint maybe_null)
{
if (!((~used_tables) & keyuse->used_tables)) // if const item