MySQL数据库

开发平台：

Visual C++

sql_select.cc：源码内容

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 __GNUC__
#pragma implementation // gcc: Class implementation
#endif
#include "mysql_priv.h"
#include "sql_select.h"
#include "opt_ft.h"
#include <m_ctype.h>
#include <hash.h>
#include <ft_global.h>
#include <assert.h>
const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref",
"MAYBE_REF","ALL","range","index","fulltext" };
static bool make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
DYNAMIC_ARRAY *keyuse,List<Item_func_match> &ftfuncs);
static bool update_ref_and_keys(DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
uint tables,COND *conds,table_map table_map,
List<Item_func_match> &ftfuncs);
static int sort_keyuse(KEYUSE *a,KEYUSE *b);
static void set_position(JOIN *join,uint index,JOIN_TAB *table,KEYUSE *key);
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(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 void join_free(JOIN *join);
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 *simple_order);
static int return_zero_rows(select_result *res,TABLE_LIST *tables,
List<Item> &fields, bool send_row,
uint select_options, const char *info,
Item *having, Procedure *proc);
static COND *optimize_cond(COND *conds,Item::cond_result *cond_value);
static COND *remove_eq_conds(COND *cond,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,
uint 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 skipp_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_tables(JOIN *join);
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_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_init_read_first_with_key(JOIN_TAB *tab);
static int join_init_read_next_with_key(READ_RECORD *info);
static int join_init_read_last_with_key(JOIN_TAB *tab);
static int join_init_read_prev_with_key(READ_RECORD *info);
static int join_ft_read_first(JOIN_TAB *tab);
static int join_ft_read_next(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, key_map usable_keys);
static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,
ha_rows select_limit);
static int create_sort_index(JOIN_TAB *tab,ORDER *order,ha_rows select_limit);
static int remove_duplicates(JOIN *join,TABLE *entry,List<Item> &fields);
static int remove_dup_with_compare(THD *thd, TABLE *entry, Field **field,
ulong offset);
static int remove_dup_with_hash_index(THD *thd, TABLE *table,
uint field_count, Field **first_field,
ulong key_length);
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(JOIN_CACHE *cache);
static void read_cached_record(JOIN_TAB *tab);
static bool cmp_buffer_with_ref(JOIN_TAB *tab);
static int setup_group(THD *thd,TABLE_LIST *tables,List<Item> &fields,
List<Item> &all_fields, ORDER *order, bool *hidden);
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(ORDER *order, List<Item> &fields);
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 alloc_group_fields(JOIN *join,ORDER *group);
static bool make_sum_func_list(JOIN *join,List<Item> &fields);
static bool change_to_use_tmp_fields(List<Item> &func);
static bool change_refs_to_tmp_fields(THD *thd, List<Item> &func);
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);
static bool add_ref_to_table_cond(THD *thd, JOIN_TAB *join_tab);
static void init_sum_functions(Item_sum **func);
static bool update_sum_func(Item_sum **func);
static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
bool distinct);
static void describe_info(THD *thd, const char *info);
/*****************************************************************************
** check fields, find best join, do the select and output fields.
** mysql_select assumes that all tables are allready opened
*****************************************************************************/
int
mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds,
List<Item_func_match> &ftfuncs,
ORDER *order, ORDER *group,Item *having,ORDER *proc_param,
uint select_options,select_result *result)
{
TABLE *tmp_table;
int error,tmp;
bool need_tmp,hidden_group_fields;
bool simple_order,simple_group,no_order;
Item::cond_result cond_value;
SQL_SELECT *select;
DYNAMIC_ARRAY keyuse;
JOIN join;
Procedure *procedure;
List<Item> all_fields(fields);
bool select_distinct;
DBUG_ENTER("mysql_select");
/* Check that all tables, fields, conds and order are ok */
select_distinct=test(select_options & SELECT_DISTINCT);
tmp_table=0;
select=0;
no_order=0;
bzero((char*) &keyuse,sizeof(keyuse));
thd->proc_info="init";
thd->used_tables=0; // Updated by setup_fields
if (setup_fields(thd,tables,fields,1,&all_fields) ||
setup_conds(thd,tables,&conds) ||
setup_order(thd,tables,fields,all_fields,order) ||
setup_group(thd,tables,fields,all_fields,group,&hidden_group_fields) ||
setup_ftfuncs(thd,tables,ftfuncs))
DBUG_RETURN(-1); /* purecov: inspected */
if (having)
{
thd->where="having clause";
thd->allow_sum_func=1;
if (having->fix_fields(thd,tables) || thd->fatal_error)
DBUG_RETURN(-1); /* purecov: inspected */
if (having->with_sum_func)
having->split_sum_func(all_fields);
}
/*
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;
*/
join.table=0;
join.tables=0;
{
if (!group)
{
uint flag=0;
List_iterator<Item> it(fields);
Item *item;
while ((item= it++))
{
if (item->with_sum_func)
flag|=1;
else if (!item->const_item())
flag|=2;
}
if (flag == 3)
{
my_error(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,MYF(0));
DBUG_RETURN(-1);
}
}
TABLE_LIST *table;
for (table=tables ; table ; table=table->next)
join.tables++;
}
procedure=setup_procedure(thd,proc_param,result,fields,&error);
if (error)
DBUG_RETURN(-1); /* purecov: inspected */
if (procedure)
{
if (setup_new_fields(thd,tables,fields,all_fields,procedure->param_fields))
{ /* purecov: inspected */
delete procedure; /* purecov: inspected */
DBUG_RETURN(-1); /* purecov: inspected */
}
if (procedure->group)
{
if (!test_if_subpart(procedure->group,group))
{ /* purecov: inspected */
my_message(0,"Can't handle procedures with differents groups yet",
MYF(0)); /* purecov: inspected */
delete procedure; /* purecov: inspected */
DBUG_RETURN(-1); /* purecov: inspected */
}
}
#ifdef NOT_NEEDED
else if (!group && procedure->flags & PROC_GROUP)
{
my_message(0,"Select must have a group with this procedure",MYF(0));
delete procedure;
DBUG_RETURN(-1);
}
#endif
if (order && (procedure->flags & PROC_NO_SORT))
{ /* purecov: inspected */
my_message(0,"Can't use order with this procedure",MYF(0)); /* purecov: inspected */
delete procedure; /* purecov: inspected */
DBUG_RETURN(-1); /* purecov: inspected */
}
}
/* Init join struct */
join.thd=thd;
join.lock=thd->lock;
join.join_tab=0;
join.tmp_table_param.copy_field=0;
join.sum_funcs=0;
join.send_records=join.found_records=0;
join.tmp_table_param.end_write_records= HA_POS_ERROR;
join.first_record=join.sort_and_group=0;
join.select_options=select_options;
join.result=result;
count_field_types(&join.tmp_table_param,all_fields,0);
join.const_tables=0;
join.having=0;
join.group= group != 0;
#ifdef RESTRICTED_GROUP
if (join.sum_func_count && !group && (join.func_count || join.field_count))
{
my_message(ER_WRONG_SUM_SELECT,ER(ER_WRONG_SUM_SELECT));
delete procedure;
DBUG_RETURN(-1);
}
#endif
if (!procedure && result->prepare(fields))
{ /* purecov: inspected */
DBUG_RETURN(-1); /* purecov: inspected */
}
#ifdef HAVE_REF_TO_FIELDS // Not done yet
/* Add HAVING to WHERE if possible */
if (having && !group && ! join.sum_func_count)
{
if (!conds)
{
conds=having;
having=0;
}
else if ((conds=new Item_cond_and(conds,having)))
{
conds->fix_fields(thd,tables);
conds->change_ref_to_fields(thd,tables);
having=0;
}
}
#endif
conds=optimize_cond(conds,&cond_value);
if (thd->fatal_error) // Out of memory
{
delete procedure;
DBUG_RETURN(0);
}
if (cond_value == Item::COND_FALSE || !thd->select_limit)
{ /* Impossible cond */
error=return_zero_rows(result, tables, fields,
join.tmp_table_param.sum_func_count != 0 && !group,
select_options,"Impossible WHERE",having,
procedure);
delete procedure;
DBUG_RETURN(error);
}
/* Optimize count(*), min() and max() */
if (tables && join.tmp_table_param.sum_func_count && ! group)
{
int res;
if ((res=opt_sum_query(tables, all_fields, conds)))
{
if (res < 0)
{
error=return_zero_rows(result, tables, fields, !group,
select_options,"No matching min/max row",
having,procedure);
delete procedure;
DBUG_RETURN(error);
}
if (select_options & SELECT_DESCRIBE)
{
describe_info(thd,"Select tables optimized away");
delete procedure;
DBUG_RETURN(0);
}
tables=0; // All tables resolved
}
}
if (!tables)
{ // Only test of functions
error=0;
if (select_options & SELECT_DESCRIBE)
describe_info(thd,"No tables used");
else
{
result->send_fields(fields,1);
if (!having || having->val_int())
{
if (result->send_data(fields))
{
result->send_error(0,NullS); /* purecov: inspected */
error=1;
}
else
error=(int) result->send_eof();
}
else
error=(int) result->send_eof();
}
delete procedure;
DBUG_RETURN(0);
}
error = -1;
join.sort_by_table=get_sort_by_table(order,group,tables);
/* Calculate how to do the join */
thd->proc_info="statistics";
if (make_join_statistics(&join,tables,conds,&keyuse,ftfuncs) ||
thd->fatal_error)
goto err;
thd->proc_info="preparing";
if ((tmp=join_read_const_tables(&join)) > 0)
goto err;
if (tmp && !(select_options & SELECT_DESCRIBE))
{
error=return_zero_rows(result,tables,fields,
join.tmp_table_param.sum_func_count != 0 &&
!group,0,"",having,procedure);
goto err;
}
if (!(thd->options & OPTION_BIG_SELECTS) &&
join.best_read > (double) thd->max_join_size &&
!(select_options & SELECT_DESCRIBE))
{ /* purecov: inspected */
result->send_error(ER_TOO_BIG_SELECT,ER(ER_TOO_BIG_SELECT)); /* purecov: inspected */
error= 1; /* purecov: inspected */
goto err; /* purecov: inspected */
}
if (join.const_tables && !thd->locked_tables)
mysql_unlock_some_tables(thd, join.table,join.const_tables);
if (!conds && join.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(*join.table, join.const_table_map,
join.const_table_map,conds,&error);
if (error)
{ /* purecov: inspected */
error= -1; /* purecov: inspected */
goto err; /* purecov: inspected */
}
if (make_join_select(&join,select,conds))
{
error=return_zero_rows(result,tables,fields,
join.tmp_table_param.sum_func_count != 0 && !group,
select_options,
"Impossible WHERE noticed after reading const tables",
having,procedure);
goto err;
}
error= -1; /* if goto err */
/* Optimize distinct away if possible */
order=remove_const(&join,order,conds,&simple_order);
if (group || join.tmp_table_param.sum_func_count)
{
if (! hidden_group_fields)
select_distinct=0;
}
else if (select_distinct && join.tables - join.const_tables == 1 &&
(order || thd->select_limit == HA_POS_ERROR))
{
if ((group=create_distinct_group(order,fields)))
{
select_distinct=0;
no_order= !order;
join.group=1; // For end_write_group
}
else if (thd->fatal_error) // End of memory
goto err;
}
group=remove_const(&join,group,conds,&simple_group);
if (!group && join.group)
{
order=0; // The output has only one row
simple_order=1;
}
calc_group_buffer(&join,group);
join.send_group_parts=join.tmp_table_param.group_parts; /* Save org parts */
if (procedure && procedure->group)
{
group=procedure->group=remove_const(&join,procedure->group,conds,
&simple_group);
calc_group_buffer(&join,group);
}
if (test_if_subpart(group,order) ||
(!group && join.tmp_table_param.sum_func_count))
order=0;
// Can't use sort on head table if using cache
if (join.full_join)
{
if (group)
simple_group=0;
if (order)
simple_order=0;
}
need_tmp= (join.const_tables != join.tables &&
((select_distinct || !simple_order || !simple_group) ||
(group && order) ||
test(select_options & OPTION_BUFFER_RESULT)));
make_join_readinfo(&join, (select_options & SELECT_DESCRIBE) |
(ftfuncs.elements ? 0 : SELECT_USE_CACHE)); // No cache for MATCH
/* 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 || order)
{
for (uint i_h = join.const_tables; i_h < join.tables; i_h++)
{
TABLE* table_h = join.join_tab[i_h].table;
if (table_h->db_type == DB_TYPE_INNOBASE)
table_h->file->extra(HA_EXTRA_DONT_USE_CURSOR_TO_UPDATE);
}
}
#endif
DBUG_EXECUTE("info",TEST_join(&join););
/*
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 ((order || group) && join.join_tab[join.const_tables].type != JT_ALL &&
join.join_tab[join.const_tables].type != JT_FT &&
(order && simple_order || group && simple_group))
{
if (add_ref_to_table_cond(thd,&join.join_tab[join.const_tables]))
goto err;
}
if (!(select_options & SELECT_BIG_RESULT) &&
((group && join.const_tables != join.tables &&
!test_if_skip_sort_order(&join.join_tab[join.const_tables], group,
HA_POS_ERROR)) ||
select_distinct) &&
join.tmp_table_param.quick_group && !procedure)
{
need_tmp=1; simple_order=simple_group=0; // Force tmp table without sort
}
if (select_options & SELECT_DESCRIBE)
{
if (!order && !no_order)
order=group;
if (order &&
(join.const_tables == join.tables ||
test_if_skip_sort_order(&join.join_tab[join.const_tables], order,
(having || group ||
join.const_tables != join.tables - 1) ?
HA_POS_ERROR : thd->select_limit)))
order=0;
select_describe(&join,need_tmp,
(order != 0 &&
(!need_tmp || order != group || simple_group)),
select_distinct);
error=0;
goto err;
}
/* Perform FULLTEXT search before all regular searches */
if (ftfuncs.elements)
{
List_iterator<Item_func_match> li(ftfuncs);
Item_func_match *ifm;
DBUG_PRINT("info",("Performing FULLTEXT search"));
thd->proc_info="FULLTEXT searching";
while ((ifm=li++))
{
ifm->init_search(test(order));
}
}
/* 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";
if (!(tmp_table =
create_tmp_table(thd,&join.tmp_table_param,all_fields,
((!simple_group && !procedure &&
!(test_flags & TEST_NO_KEY_GROUP)) ?
group : (ORDER*) 0),
group ? 0 : select_distinct,
group && simple_group,
order == 0,
join.select_options)))
goto err; /* purecov: inspected */
if (having && (join.sort_and_group || (tmp_table->distinct && !group)))
join.having=having;
/* if group or order on first table, sort first */
if (group && simple_group)
{
DBUG_PRINT("info",("Sorting for group"));
thd->proc_info="Sorting for group";
if (create_sort_index(&join.join_tab[join.const_tables],group,
HA_POS_ERROR) ||
make_sum_func_list(&join,all_fields) ||
alloc_group_fields(&join,group))
goto err;
group=0;
}
else
{
if (make_sum_func_list(&join,all_fields))
goto err;
if (!group && ! tmp_table->distinct && order && simple_order)
{
DBUG_PRINT("info",("Sorting for order"));
thd->proc_info="Sorting for order";
if (create_sort_index(&join.join_tab[join.const_tables],order,
HA_POS_ERROR))
goto err; /* purecov: inspected */
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 (tmp_table->distinct)
{
table_map used_tables= thd->used_tables;
JOIN_TAB *join_tab=join.join_tab+join.tables-1;
do
{
if (used_tables & join_tab->table->map)
break;
join_tab->not_used_in_distinct=1;
} while (join_tab-- != join.join_tab);
}
/* Copy data to the temporary table */
thd->proc_info="Copying to tmp table";
if (do_select(&join,(List<Item> *) 0,tmp_table,0))
goto err; /* purecov: inspected */
if (join.having)
join.having=having=0; // Allready done
/* Change sum_fields reference to calculated fields in tmp_table */
if (join.sort_and_group || tmp_table->group)
{
if (change_to_use_tmp_fields(all_fields))
goto err;
join.tmp_table_param.field_count+=join.tmp_table_param.sum_func_count+
join.tmp_table_param.func_count;
join.tmp_table_param.sum_func_count=join.tmp_table_param.func_count=0;
}
else
{
if (change_refs_to_tmp_fields(thd,all_fields))
goto err;
join.tmp_table_param.field_count+=join.tmp_table_param.func_count;
join.tmp_table_param.func_count=0;
}
if (procedure)
procedure->update_refs();
if (tmp_table->group)
{ // Already grouped
if (!order && !no_order)
order=group; /* order by group */
group=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 (group && (!test_if_subpart(group,order) || select_distinct) ||
(select_distinct &&
join.tmp_table_param.using_indirect_summary_function))
{ /* Must copy to another table */
TABLE *tmp_table2;
DBUG_PRINT("info",("Creating group table"));
/* Free first data from old join */
join_free(&join);
if (make_simple_join(&join,tmp_table))
goto err;
calc_group_buffer(&join,group);
count_field_types(&join.tmp_table_param,all_fields,
select_distinct && !group);
join.tmp_table_param.hidden_field_count=(all_fields.elements-
fields.elements);
/* group data to new table */
if (!(tmp_table2 = create_tmp_table(thd,&join.tmp_table_param,all_fields,
(ORDER*) 0,
select_distinct && !group,
1, 0,
join.select_options)))
goto err; /* purecov: inspected */
if (group)
{
thd->proc_info="Creating sort index";
if (create_sort_index(join.join_tab,group,HA_POS_ERROR) ||
alloc_group_fields(&join,group))
{
free_tmp_table(thd,tmp_table2); /* purecov: inspected */
goto err; /* purecov: inspected */
}
group=0;
}
thd->proc_info="Copying to group table";
if (make_sum_func_list(&join,all_fields) ||
do_select(&join,(List<Item> *) 0,tmp_table2,0))
{
free_tmp_table(thd,tmp_table2);
goto err; /* purecov: inspected */
}
end_read_record(&join.join_tab->read_record);
free_tmp_table(thd,tmp_table);
join.const_tables=join.tables; // Mark free for join_free()
tmp_table=tmp_table2;
join.join_tab[0].table=0; // Table is freed
if (change_to_use_tmp_fields(all_fields)) // No sum funcs anymore
goto err;
join.tmp_table_param.field_count+=join.tmp_table_param.sum_func_count;
join.tmp_table_param.sum_func_count=0;
}
if (tmp_table->distinct)
select_distinct=0; /* Each row is uniq */
join_free(&join); /* Free quick selects */
if (select_distinct && ! group)
{
thd->proc_info="Removing duplicates";
if (remove_duplicates(&join,tmp_table,fields))
goto err; /* purecov: inspected */
select_distinct=0;
}
tmp_table->reginfo.lock_type=TL_UNLOCK;
if (make_simple_join(&join,tmp_table))
goto err;
calc_group_buffer(&join,group);
count_field_types(&join.tmp_table_param,all_fields,0);
}
if (procedure)
{
if (procedure->change_columns(fields) ||
result->prepare(fields))
goto err;
count_field_types(&join.tmp_table_param,all_fields,0);
}
if (join.group || join.tmp_table_param.sum_func_count ||
(procedure && (procedure->flags & PROC_GROUP)))
{
alloc_group_fields(&join,group);
setup_copy_fields(&join.tmp_table_param,all_fields);
if (make_sum_func_list(&join,all_fields) || thd->fatal_error)
goto err; /* purecov: inspected */
}
if (group || 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 (having && ! group && ! join.sort_and_group)
{
having->update_used_tables(); // Some tables may have been const
JOIN_TAB *table=&join.join_tab[join.const_tables];
table_map used_tables= join.const_table_map | table->table->map;
Item* sort_table_cond=make_cond_for_table(having,used_tables,used_tables);
if (sort_table_cond)
{
if (!table->select)
if (!(table->select=new SQL_SELECT))
goto err;
if (!table->select->cond)
table->select->cond=sort_table_cond;
else // This should never happen
if (!(table->select->cond=new Item_cond_and(table->select->cond,
sort_table_cond)))
goto err;
table->select_cond=table->select->cond;
DBUG_EXECUTE("where",print_where(table->select->cond,
"select and having"););
having=make_cond_for_table(having,~ (table_map) 0,~used_tables);
DBUG_EXECUTE("where",print_where(conds,"having after sort"););
}
}
if (create_sort_index(&join.join_tab[join.const_tables],
group ? group : order,
(having || group ||
join.const_tables != join.tables - 1) ?
HA_POS_ERROR : thd->select_limit))
goto err; /* purecov: inspected */
}
join.having=having; // Actually a parameter
thd->proc_info="Sending data";
error=do_select(&join,&fields,NULL,procedure);
err:
thd->proc_info="end";
join.lock=0; // It's faster to unlock later
join_free(&join);
thd->proc_info="end2"; // QQ
if (tmp_table)
free_tmp_table(thd,tmp_table);
thd->proc_info="end3"; // QQ
delete select;
delete_dynamic(&keyuse);
delete procedure;
thd->proc_info="end4"; // QQ
DBUG_RETURN(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(SQL_SELECT *select,TABLE *table,
key_map keys)
{
int error;
DBUG_ENTER("get_quick_record_count");
if (select)
{
select->head=table;
table->reginfo.impossible_range=0;
if ((error=select->test_quick_select(keys,(table_map) 0,HA_POS_ERROR))
== 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 */
}
static bool
make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
DYNAMIC_ARRAY *keyuse_array,
List<Item_func_match> &ftfuncs)
{
int error;
uint i,table_count,const_count,found_ref,refs,key,const_ref,eq_part;
table_map const_table_map,all_table_map;
TABLE **table_vector;
JOIN_TAB *stat,*stat_end,*s,**stat_ref;
SQL_SELECT *select;
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 */
select=0;
join->best_ref=stat_vector;
stat_end=stat+table_count;
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;
table_vector[i]=s->table=table=tables->table;
table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);// record count
table->quick_keys=0;
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;
if ((s->on_expr=tables->on_expr))
{
// table->maybe_null=table->outer_join=1; // Mark for send fields
if (!table->file->records)
{ // Empty table
s->key_dependent=s->dependent=0;
s->type=JT_SYSTEM;
const_table_map|=table->map;
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->option_flag() & HA_NOT_EXACT_COUNT))
{
s->type=JT_SYSTEM;
const_table_map|=table->map;
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(keyuse_array,stat,join->tables,
conds,~outer_join,ftfuncs))
DBUG_RETURN(1);
/* loop until no more const tables are found */
int ref_changed;
do
{
ref_changed = 0;
found_ref=0;
for (JOIN_TAB **pos=stat_vector+const_count; (s= *pos) ; pos++)
{
if (s->dependent) // If dependent on some table
{
if (s->dependent & ~(const_table_map)) // All dep. must be constants
continue;
if (s->table->file->records <= 1L &&
!(s->table->file->option_flag() & HA_NOT_EXACT_COUNT))
{ // system table
s->type=JT_SYSTEM;
const_table_map|=s->table->map;
set_position(join,const_count++,s,(KEYUSE*) 0);
continue;
}
}
/* check if table can be read by key or table only uses const refs */
if ((keyuse=s->keyuse))
{
TABLE *table=s->table;
s->type= JT_REF;
while (keyuse->table == table)
{
start_keyuse=keyuse;
key=keyuse->key;
s->keys|= (key_map) 1 << key; // QQ: remove this ?
refs=const_ref=eq_part=0;
do
{
if (keyuse->val->type() != Item::NULL_ITEM)
{
if (!((~const_table_map) & keyuse->used_tables))
const_ref|= (key_map) 1 << keyuse->keypart;
else
refs|=keyuse->used_tables;
eq_part|= (uint) 1 << keyuse->keypart;
}
keyuse++;
} while (keyuse->table == table && keyuse->key == key);
if (eq_part == PREV_BITS(uint,table->key_info[key].key_parts) &&
(table->key_info[key].flags & HA_NOSAME))
{
if (const_ref == eq_part)
{ // Found everything for ref.
s->type=JT_CONST;
const_table_map|=table->map;
set_position(join,const_count++,s,start_keyuse);
ref_changed = 1;
break;
}
else
found_ref|= refs; // Table is const if all refs are const
}
}
}
}
} while (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 */
s->worst_seeks= (double) (s->read_time*2);
if (s->worst_seeks < 2.0) // Fix for small tables
s->worst_seeks=2.0;
/* if (s->type == JT_EQ_REF)
continue; */
if (s->const_keys)
{
ha_rows records;
if (!select)
select=make_select(s->table,const_table_map,
0,
and_conds(conds,s->on_expr),&error);
records=get_quick_record_count(select,s->table, s->const_keys);
s->quick=select->quick;
s->needed_reg=select->needed_reg;
select->quick=0;
select->read_tables=const_table_map;
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->const_table_map=const_table_map;
if (join->const_tables != join->tables)
find_best_combination(join,all_table_map & ~const_table_map);
else
{
memcpy((gptr) join->best_positions,(gptr) join->positions,
sizeof(POSITION)*join->const_tables);
join->best_read=1.0;
}
DBUG_RETURN(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,const_level; // QQ: Remove const_level
bool eq_func;
bool exists_optimize;
} KEY_FIELD;
/* merge new key definitions to old ones, remove those not used in both */
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, skipp 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 (old->val->eq(new_fields->val))
{
old->level=old->const_level=and_level;
old->exists_optimize&=new_fields->exists_optimize;
}
}
else if (old->val->eq(new_fields->val) && old->eq_func &&
new_fields->eq_func)
{
old->level=old->const_level=and_level;
old->exists_optimize&=new_fields->exists_optimize;
}
else // Impossible; remove 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 && old->const_level != and_level)
{ // Not used in all levels
if (old == --first_free)
break;
*old= *first_free; // Remove old value
continue;
}
old++;
}
return first_free;
}
static void
add_key_field(KEY_FIELD **key_fields,uint and_level,
Field *field,bool eq_func,Item *value,
table_map usable_tables)
{
bool exists_optimize=0;
if (!(field->flags & PART_KEY_FLAG))
{
// Don't remove column IS NULL on a LEFT JOIN table
if (!eq_func || !value || value->type() != Item::NULL_ITEM ||
!field->table->maybe_null || field->null_ptr)
return; // Not a key. Skipp it
exists_optimize=1;
}
else
{
table_map used_tables=0;
if (value && (used_tables=value->used_tables()) &
(field->table->map | RAND_TABLE_BIT))
return;
if (!(usable_tables & field->table->map))
{
if (!eq_func || !value || value->type() != Item::NULL_ITEM ||
!field->table->maybe_null || field->null_ptr)
return; // Can't use left join optimize
exists_optimize=1;
}
else
{
JOIN_TAB *stat=field->table->reginfo.join_tab;
stat[0].keys|=field->key_start; // Add possible keys
if (!value)
{ // Probably BETWEEN or IN
stat[0].const_keys |= field->key_start;
return; // Can't be used as eq key
}
/* 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
*/
stat[0].key_dependent|=used_tables;
if (value->const_item())
stat[0].const_keys |= field->key_start;
/* 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 */
if (!eq_func ||
field->result_type() == STRING_RESULT &&
value->result_type() != STRING_RESULT &&
field->cmp_type() != value->result_type())
return;
}
}
/* Store possible eq field */
(*key_fields)->field=field;
(*key_fields)->eq_func=eq_func;
(*key_fields)->val=value;
(*key_fields)->level=(*key_fields)->const_level=and_level;
(*key_fields)->exists_optimize=exists_optimize;
(*key_fields)++;
}
static void
add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
COND *cond, table_map usable_tables)
{
if (cond->type() == Item_func::COND_ITEM)
{
List_iterator<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(stat,key_fields,and_level,item,usable_tables);
for (; org_key_fields != *key_fields ; org_key_fields++)
{
if (org_key_fields->const_level == org_key_fields->level)
org_key_fields->const_level=org_key_fields->level= *and_level;
else
org_key_fields->const_level= *and_level;
}
}
else
{
(*and_level)++;
add_key_fields(stat,key_fields,and_level,li++,usable_tables);
Item *item;
while ((item=li++))
{
KEY_FIELD *start_key_fields= *key_fields;
(*and_level)++;
add_key_fields(stat,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:
if (cond_func->key_item()->type() == Item::FIELD_ITEM)
add_key_field(key_fields,*and_level,
((Item_field*) (cond_func->key_item()))->field,
0,(Item*) 0,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]->type() == Item::FIELD_ITEM)
{
add_key_field(key_fields,*and_level,
((Item_field*) (cond_func->arguments()[0]))->field,
equal_func,
(cond_func->arguments()[1]),usable_tables);
}
if (cond_func->arguments()[1]->type() == Item::FIELD_ITEM &&
cond_func->functype() != Item_func::LIKE_FUNC)
{
add_key_field(key_fields,*and_level,
((Item_field*) (cond_func->arguments()[1]))->field,
equal_func,
(cond_func->arguments()[0]),usable_tables);
}
break;
}
case Item_func::OPTIMIZE_NULL:
/* column_name IS [NOT] NULL */
if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM)
{
add_key_field(key_fields,*and_level,
((Item_field*) (cond_func->arguments()[0]))->field,
cond_func->functype() == Item_func::ISNULL_FUNC,
new Item_null, 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_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->exists_optimize)
{
for (uint key=0 ; key < form->keys ; key++)
{
if (!(form->keys_in_use_for_query & (((key_map) 1) << 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.used_tables=key_field->val->used_tables();
VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
}
}
}
}
/* Mark that we can optimize LEFT JOIN */
if (key_field->val->type() == Item::NULL_ITEM &&
!key_field->field->real_maybe_null())
key_field->field->table->reginfo.not_exists_optimize=1;
}
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,
*arg0=(Item_func *)(func->arguments()[0]),
*arg1=(Item_func *)(func->arguments()[1]);
if (func->functype() == Item_func::FT_FUNC)
cond_func=(Item_func_match *)cond;
else if ((func->functype() == Item_func::GE_FUNC ||
func->functype() == Item_func::GT_FUNC) &&
arg0->type() == Item::FUNC_ITEM &&
arg0->functype() == Item_func::FT_FUNC &&
arg1->const_item() && arg1->val()>=0)
cond_func=(Item_func_match *)arg0;
else if ((func->functype() == Item_func::LE_FUNC ||
func->functype() == Item_func::LT_FUNC) &&
arg1->type() == Item::FUNC_ITEM &&
arg1->functype() == Item_func::FT_FUNC &&
arg0->const_item() && arg0->val()>=0)
cond_func=(Item_func_match *)arg1;
}
else if (cond->type() == Item::COND_ITEM)
{
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
{
Item *item;
/* I'm too lazy to implement proper recursive descent here,
and anyway, nobody will use such a stupid queries
that will require it :-)
May be later...
*/
while ((item=li++))
if (item->type() == Item::FUNC_ITEM &&
((Item_func *)item)->functype() == Item_func::FT_FUNC)
{
cond_func=(Item_func_match *)item;
break;
}
}
}
if(!cond_func)
return;
KEYUSE keyuse;
keyuse.table= cond_func->table;
keyuse.val = cond_func;
keyuse.key = cond_func->key;
#define FT_KEYPART (MAX_REF_PARTS+10)
keyuse.keypart=FT_KEYPART;
keyuse.used_tables=cond_func->key_item()->used_tables();
VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
}
static int
sort_keyuse(KEYUSE *a,KEYUSE *b)
{
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);
return test(a->used_tables) - test(b->used_tables); // Place const first
}
/*
** Update keyuse array with all possible keys we can use to fetch rows
** join_tab is a array in tablenr_order
** stat is a reference array in 'prefered' order.
*/
static bool
update_ref_and_keys(DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,uint tables,
COND *cond, table_map normal_tables,List<Item_func_match> &ftfuncs)
{
uint and_level,i,found_eq_constant;
{
KEY_FIELD *key_fields,*end;
if (!(key_fields=(KEY_FIELD*)
my_malloc(sizeof(key_fields[0])*
(current_thd->cond_count+1)*2,MYF(0))))
return TRUE; /* purecov: inspected */
and_level=0; end=key_fields;
if (cond)
add_key_fields(join_tab,&end,&and_level,cond,normal_tables);
for (i=0 ; i < tables ; i++)
{
if (join_tab[i].on_expr)
{
add_key_fields(join_tab,&end,&and_level,join_tab[i].on_expr,
join_tab[i].table->map);
}
}
if (init_dynamic_array(keyuse,sizeof(KEYUSE),20,64))
{
my_free((gptr) key_fields,MYF(0));
return TRUE;
}
/* fill keyuse with found key parts */
for (KEY_FIELD *field=key_fields ; field != end ; field++)
add_key_part(keyuse,field);
my_free((gptr) key_fields,MYF(0));
}
if (ftfuncs.elements)
{
add_ft_keys(keyuse,join_tab,cond,normal_tables);
}
/*
** remove ref if there is a keypart which is a ref and a const.
** remove keyparts without previous keyparts.
** Special treatment for ft-keys. SerG.
*/
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] |=
(key_part_map) 1 << use->keypart;
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|= (key_map) 1 << use->key;
save_pos++;
}
i=(uint) (save_pos-(KEYUSE*) keyuse->buffer);
VOID(set_dynamic(keyuse,(gptr) &end,i));
keyuse->elements=i;
}
return FALSE;
}
/*****************************************************************************
** 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)
{
ulong rec;
double tmp;
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;
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_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);
start_key=keyuse;
do
{
uint keypart=keyuse->keypart;
do
{
if (!ft_key)
{
table_map map;
if (!(rest_tables & keyuse->used_tables))
{
found_part|= (key_part_map) 1 << keypart;
found_ref|= keyuse->used_tables;
}
/*
** 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 and to avoid bad matches,
** we don't make rec less than 100.
*/
if (keyuse->used_tables &
(map=(keyuse->used_tables & ~join->const_table_map)))
{
uint tablenr;
for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ;
if (map == 1) // Only one table
{
TABLE *tmp_table=join->all_tables[tablenr];
if (rec > tmp_table->file->records && rec > 100)
rec=max(tmp_table->file->records,100);
}
}
}
keyuse++;
} while (keyuse->table == table && keyuse->key == key &&
keyuse->keypart == keypart);
} 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 == 0)
rec=1L; // 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
{
/*
** Check if we found full key
*/
if (found_part == PREV_BITS(uint,keyinfo->key_parts))
{ /* use eq key */
max_key_part= (uint) ~0;
if ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_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 & ((key_map) 1 << key))
records= (double) table->quick_rows[key];
else
records= (double) s->records/rec; // quick_range couldn't use key!
}
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
}
}
if (table->used_keys & ((key_map) 1 << key))
{
/* we can use only index tree */
uint keys_per_block= table->file->block_size/2/
keyinfo->key_length+1;
tmp=(record_count*(records+keys_per_block-1)/
keys_per_block);
}
else
tmp=record_count*min(records,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->option_flag() & HA_ONLY_WHOLE_INDEX))
{
max_key_part=max_part_bit(found_part);
/* Check if quick_range could determinate how many rows we
will match */
if (table->quick_keys & ((key_map) 1 << 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 (dupplicates) 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;
if (!(rec_per_key=(double)
keyinfo->rec_per_key[keyinfo->key_parts-1]))
rec_per_key=(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 (table->used_keys & ((key_map) 1 << key))
{
/* we can use only index tree */
uint keys_per_block= table->file->block_size/2/
keyinfo->key_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.
*/
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]))
{ // Check full join
if (s->on_expr)
{
tmp=s->found_records; // Can't use read cache
}
else
{
tmp=(double) s->read_time;
/* Calculate time to read through cache */
tmp*=(1.0+floor((double) cache_record_length(join,idx)*
record_count/(double) join_buff_size));
}
if (best == DBL_MAX ||
(tmp + record_count/(double) TIME_FOR_COMPARE*s->found_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=s->found_records;
best_key=0;
}
}
join->positions[idx].records_read=(double) 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->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(JOIN_TAB*,join->best_ref[idx],*pos);
find_best(join,rest_tables & ~real_table_bit,idx+1,
current_record_count,current_read_time);
swap(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 uint
cache_record_length(JOIN *join,uint idx)
{
uint length;
JOIN_TAB **pos,**end;
THD *thd=current_thd;
length=0;
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 */
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;
}
length+=join_tab->used_fieldlength;
}
return length;
}
static double
prev_record_reads(JOIN *join,table_map found_ref)
{
double found=1.0;
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,key,tablenr;
table_map used_tables;
TABLE *table;
JOIN_TAB *join_tab,*j;
KEYUSE *keyuse;
KEY *keyinfo;
uint table_count;
table_count=join->tables;
if (!(join->join_tab=join_tab=
(JOIN_TAB*) join->thd->alloc(sizeof(JOIN_TAB)*table_count)))
return TRUE;
join->const_tables=0; /* for checking */
join->const_table_map=0;
join->full_join=0;
used_tables=0;
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;
j->ref.key = -1;
j->ref.key_parts=0;
j->info=0; // For describe
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_SYSTEM)
{
j->table->const_table=1;
if (join->const_tables == tablenr)
{
join->const_tables++;
join->const_table_map|=form->map;
}
continue;
}
if (!j->keys || !(keyuse= join->best_positions[tablenr].key))
{
j->type=JT_ALL;
if (tablenr != join->const_tables)
join->full_join=1;
}
else
{
uint keyparts,length;
bool ftkey=(keyuse->keypart == FT_KEYPART);
/*
** 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;
do
{
if (!((~used_tables) & keyuse->used_tables))
{
if (keyparts == keyuse->keypart)
{
keyparts++;
length+=keyinfo->key_part[keyuse->keypart].store_length;
}
}
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*) sql_calloc(ALIGN_SIZE(length)*2)) ||
!(j->ref.key_copy= (store_key**) sql_alloc((sizeof(store_key*) *
(keyparts+1)))) ||
!(j->ref.items= (Item**) sql_alloc(sizeof(Item*)*keyparts)))
{
return TRUE;
}
j->ref.key_buff2=j->ref.key_buff+ALIGN_SIZE(length);
j->ref.key_err=1;
keyuse=join->best_positions[tablenr].key;
store_key **ref_key=j->ref.key_copy;
byte *key_buff=j->ref.key_buff;
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
{
THD *thd=current_thd;
for (i=0 ; i < keyparts ; keyuse++,i++)
{
while (keyuse->keypart != i ||
((~used_tables) & keyuse->used_tables))
keyuse++; /* Skipp other parts */
uint maybe_null= test(keyinfo->key_part[i].null_bit);
j->ref.items[i]=keyuse->val; // Save for cond removal
if (!keyuse->used_tables &&
!(join->select_options & SELECT_DESCRIBE))
{ // Compare against constant
store_key_item *tmp=new store_key_item(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->fatal_error)
{
return TRUE;
}
tmp->copy();
}
else
*ref_key++= get_store_key(keyuse,join->const_table_map,
&keyinfo->key_part[i],
(char*) key_buff,maybe_null);
key_buff+=keyinfo->key_part[i].store_length;
}
} /* not ftkey */
*ref_key=0; // end_marker
if (j->type == JT_FT) /* no-op */;
else if (j->type == JT_CONST)
{
j->table->const_table=1;
if (join->const_tables == tablenr)
{
join->const_tables++;
join->const_table_map|=form->map;
}
}
else if (((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) != HA_NOSAME) ||
keyparts != keyinfo->key_parts)
j->type=JT_REF; /* Must read with repeat */
else if (ref_key == j->ref.key_copy)
{ /* Should never be reached */
j->type=JT_CONST; /* purecov: deadcode */
if (join->const_tables == tablenr)
{
join->const_tables++; /* purecov: deadcode */
join->const_table_map|=form->map;
}
}
else
j->type=JT_EQ_REF;
}
}
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 store_key *
get_store_key(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
{
return new store_key_const_item(key_part->field,
key_buff + maybe_null,
maybe_null ? key_buff : 0,
key_part->length,
keyuse->val);
}
else if (keyuse->val->type() == Item::FIELD_ITEM)
return new store_key_field(key_part->field,
key_buff + maybe_null,
maybe_null ? key_buff : 0,
key_part->length,
((Item_field*) keyuse->val)->field,
keyuse->val->full_name());
return new store_key_item(key_part->field,
key_buff + maybe_null,
maybe_null ? key_buff : 0,
key_part->length,
keyuse->val);
}
/*
** This function is only called for const items on fields which are keys
** returns 1 if there was some conversion made when the field was stored.
*/
bool
store_val_in_field(Field *field,Item *item)
{
THD *thd=current_thd;
ulong cuted_fields=thd->cuted_fields;
thd->count_cuted_fields=1;
item->save_in_field(field);
thd->count_cuted_fields=0;
return cuted_fields != thd->cuted_fields;
}
static bool
make_simple_join(JOIN *join,TABLE *tmp_table)
{
TABLE **tableptr;
JOIN_TAB *join_tab;
if (!(tableptr=(TABLE**) join->thd->alloc(sizeof(TABLE*))) ||
!(join_tab=(JOIN_TAB*) join->thd->alloc(sizeof(JOIN_TAB))))
return TRUE;
join->join_tab=join_tab;
join->table=tableptr; tableptr[0]=tmp_table;
join->tables=1;
join->const_tables=0;
join->const_table_map=0;
join->tmp_table_param.copy_field_count=join->tmp_table_param.field_count=
join->tmp_table_param.sum_func_count= join->tmp_table_param.func_count=0;
join->tmp_table_param.copy_field=0;
join->first_record=join->sort_and_group=0;
join->sum_funcs=0;
join->send_records=(ha_rows) 0;
join->group=0;
join_tab->cache.buff=0; /* No cacheing */
join_tab->table=tmp_table;
join_tab->select=0;
join_tab->select_cond=0;
join_tab->quick=0;
join_tab->type= JT_ALL; /* Map through all records */
join_tab->keys= (uint) ~0; /* test everything in quick */
join_tab->info=0;
join_tab->on_expr=0;
join_tab->ref.key = -1;
join_tab->not_used_in_distinct=0;
join_tab->read_first_record= join_init_read_record;
bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record));
tmp_table->status=0;
tmp_table->null_row=0;
return FALSE;
}
static bool
make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
{
DBUG_ENTER("make_join_select");
if (select)
{
table_map used_tables;
if (join->tables > 1)
cond->update_used_tables(); // Tablenr may have changed
{ // Check const tables
COND *const_cond=
make_cond_for_table(cond,join->const_table_map,(table_map) 0);
DBUG_EXECUTE("where",print_where(const_cond,"constants"););
if (const_cond && !const_cond->val_int())
{
DBUG_PRINT("info",("Found impossible WHERE condition"));
DBUG_RETURN(1); // Impossible const condition
}
}
used_tables=(select->const_tables=join->const_table_map) | RAND_TABLE_BIT;
for (uint i=join->const_tables ; i < join->tables ; i++)
{
JOIN_TAB *tab=join->join_tab+i;
table_map current_map= tab->table->map;
used_tables|=current_map;
COND *tmp=make_cond_for_table(cond,used_tables,current_map);
if (!tmp && tab->quick)
{ // Outer join
/*
Hack to handle the case where we only refer to a table
in the ON part of an OUTER JOIN.
*/
tmp=new Item_int((longlong) 1,1); // Always true
}
if (tmp)
{
DBUG_EXECUTE("where",print_where(tmp,tab->table->table_name););
SQL_SELECT *sel=tab->select=(SQL_SELECT*)
sql_memdup((gptr) select, sizeof(SQL_SELECT));
if (!sel)
DBUG_RETURN(1); // End of memory
tab->select_cond=sel->cond=tmp;
sel->head=tab->table;
if (tab->quick)
{
/* Use quick key read if it's a constant and it's not used
with key reading */
if (tab->needed_reg == 0 && tab->type != JT_EQ_REF
&& tab->type != JT_FT && (tab->type != JT_REF ||
(uint) tab->ref.key == tab->quick->index))
{
sel->quick=tab->quick; // Use value from get_quick_...
sel->quick_keys=0;
sel->needed_reg=0;
}
else
{
delete tab->quick;
}
tab->quick=0;
}
uint ref_key=(uint) sel->head->reginfo.join_tab->ref.key+1;
if (i == join->const_tables && ref_key)
{
if (tab->const_keys && tab->table->reginfo.impossible_range)
DBUG_RETURN(1);
}
else if (tab->type == JT_ALL)
{
if (tab->const_keys &&
tab->table->reginfo.impossible_range)
DBUG_RETURN(1); // Impossible range
/*
We plan to scan all rows.
Check again if we should use an index.
We could have used an column from a previous table in
the index if we are using limit and this is the first table
*/
if ((tab->keys & ~ tab->const_keys && i > 0) ||
tab->const_keys && i == join->const_tables &&
join->thd->select_limit < join->best_positions[i].records_read)
{
/* Join with outer join condition */
COND *orig_cond=sel->cond;
sel->cond=and_conds(sel->cond,tab->on_expr);
if (sel->test_quick_select(tab->keys,
used_tables & ~ current_map,
join->thd->select_limit) < 0)
DBUG_RETURN(1); // Impossible range
sel->cond=orig_cond;
}
else
{
sel->needed_reg=tab->needed_reg;
sel->quick_keys=0;
}
if ((sel->quick_keys | sel->needed_reg) & ~tab->checked_keys)
{
tab->keys=sel->quick_keys | sel->needed_reg;
tab->use_quick= (sel->needed_reg &&
(!select->quick_keys ||
(select->quick &&
(select->quick->records >= 100L)))) ?
2 : 1;
sel->read_tables= used_tables;
}
if (i != join->const_tables && tab->use_quick != 2)
{ /* Read with cache */
if ((tmp=make_cond_for_table(cond,
join->const_table_map |
current_map,
current_map)))
{
DBUG_EXECUTE("where",print_where(tmp,"cache"););
tab->cache.select=(SQL_SELECT*) sql_memdup((gptr) sel,
sizeof(SQL_SELECT));
tab->cache.select->cond=tmp;
tab->cache.select->read_tables=join->const_table_map;
}
}
}
if (tab->type == JT_REF && sel->quick &&
tab->ref.key_length < sel->quick->max_used_key_length)
{
/* Range uses longer key; Use this instead of ref on key */
tab->type=JT_ALL;
tab->use_quick=1;
tab->ref.key_parts=0; // Don't use ref key.
join->best_positions[i].records_read=sel->quick->records;
}
}
}
}
DBUG_RETURN(0);
}
static void
make_join_readinfo(JOIN *join,uint options)
{
uint i;
DBUG_ENTER("make_join_readinfo");
for (i=join->const_tables ; i < join->tables ; i++)
{
JOIN_TAB *tab=join->join_tab+i;
TABLE *table=tab->table;
tab->read_record.table= table;
tab->read_record.file=table->file;
tab->next_select=sub_select; /* normal select */
switch (tab->type) {
case JT_SYSTEM: // Only happens with left join
table->status=STATUS_NO_RECORD;
tab->read_first_record= join_read_system;
tab->read_record.read_record= join_no_more_records;
break;
case JT_CONST: // Only happens with left join
table->status=STATUS_NO_RECORD;
tab->read_first_record= join_read_const;
tab->read_record.read_record= join_no_more_records;
break;
case JT_EQ_REF:
table->status=STATUS_NO_RECORD;
if (tab->select)
{
delete tab->select->quick;
tab->select->quick=0;
}
delete tab->quick;
tab->quick=0;
table->file->index_init(tab->ref.key);
tab->read_first_record= join_read_key;
tab->read_record.read_record= join_no_more_records;
if (table->used_keys & ((key_map) 1 << tab->ref.key))
{
table->key_read=1;
table->file->extra(HA_EXTRA_KEYREAD);
}
break;
case JT_REF:
table->status=STATUS_NO_RECORD;
if (tab->select)
{
delete tab->select->quick;
tab->select->quick=0;
}
delete tab->quick;
tab->quick=0;
table->file->index_init(tab->ref.key);
tab->read_first_record= join_read_always_key;
tab->read_record.read_record= join_read_next;
if (table->used_keys & ((key_map) 1 << tab->ref.key))
{
table->key_read=1;
table->file->extra(HA_EXTRA_KEYREAD);
}
break;
case JT_FT:
table->status=STATUS_NO_RECORD;
table->file->index_init(tab->ref.key);
tab->read_first_record= join_ft_read_first;
tab->read_record.read_record= join_ft_read_next;
break;
case JT_ALL:
/*
** if previous table use cache
*/
table->status=STATUS_NO_RECORD;
if (i != join->const_tables && (options & SELECT_USE_CACHE) &&
tab->use_quick != 2 && !tab->on_expr)
{
if ((options & SELECT_DESCRIBE) ||
!join_init_cache(join->thd,join->join_tab+join->const_tables,
i-join->const_tables))
{
tab[-1].next_select=sub_select_cache; /* Patch previous */
}
}
/* These init changes read_record */
if (tab->use_quick == 2)
{
join->thd->lex.options|=QUERY_NO_GOOD_INDEX_USED;
tab->read_first_record= join_init_quick_read_record;
statistic_increment(select_range_check_count, &LOCK_status);
}
else
{
tab->read_first_record= join_init_read_record;
if (i == join->const_tables)
{
if (tab->select && tab->select->quick)
{
statistic_increment(select_range_count, &LOCK_status);
}
else
{
join->thd->lex.options|=QUERY_NO_INDEX_USED;
statistic_increment(select_scan_count, &LOCK_status);
}
}
else
{
if (tab->select && tab->select->quick)
{
statistic_increment(select_full_range_join_count, &LOCK_status);
}
else
{
join->thd->lex.options|=QUERY_NO_INDEX_USED;
statistic_increment(select_full_join_count, &LOCK_status);
}
}
if (tab->select && tab->select->quick &&
table->used_keys & ((key_map) 1 << tab->select->quick->index))
{
table->key_read=1;
table->file->extra(HA_EXTRA_KEYREAD);
}
else if (table->used_keys && ! (tab->select && tab->select->quick))
{ // Only read index tree
tab->index=find_shortest_key(table, table->used_keys);
tab->table->file->index_init(tab->index);
tab->read_first_record= join_init_read_first_with_key;
tab->type=JT_NEXT; // Read with index_first / index_next
}
}
break;
default:
DBUG_PRINT("error",("Table type %d found",tab->type)); /* purecov: deadcode */
break; /* purecov: deadcode */
case JT_UNKNOWN:
case JT_MAYBE_REF:
abort(); /* purecov: deadcode */
}
}
join->join_tab[join->tables-1].next_select=0; /* Set by do_select */
DBUG_VOID_RETURN;
}
static void
join_free(JOIN *join)
{
JOIN_TAB *tab,*end;
if (join->table)
{
/*
Only a sorted table may be cached. This sorted table is always the
first non const table in join->table
*/
if (join->tables > join->const_tables) // Test for not-const tables
free_io_cache(join->table[join->const_tables]);
for (tab=join->join_tab,end=tab+join->tables ; tab != end ; tab++)
{
delete tab->select;
delete tab->quick;
x_free(tab->cache.buff);
end_read_record(&tab->read_record);
if (tab->table)
{
if (tab->table->key_read)
{
tab->table->key_read=0;
tab->table->file->extra(HA_EXTRA_NO_KEYREAD);
}
tab->table->file->index_end();
}
}
join->table=0;
}
// We are not using tables anymore
// Unlock all tables. We may be in an INSERT .... SELECT statement.
if (join->lock && join->thd->lock)
{
mysql_unlock_read_tables(join->thd, join->lock);// Don't free join->lock
join->lock=0;
}
join->group_fields.delete_elements();
join->tmp_table_param.copy_funcs.delete_elements();
delete [] join->tmp_table_param.copy_field;
join->tmp_table_param.copy_field=0;
}
/*****************************************************************************
** Remove the following expressions from ORDER BY and GROUP BY:
** Constant expressions
** Expression that only uses tables that are of type EQ_REF and the reference
** is in the ORDER list or if all refereed tables are of the above type.
**
** In the following, the X field can be removed:
** SELECT * FROM t1,t2 WHERE t1.a=t2.a ORDER BY t1.a,t2.X
** SELECT * FROM t1,t2,t3 WHERE t1.a=t2.a AND t2.b=t3.b ORDER BY t1.a,t3.X
**
** These can't be optimized:
** SELECT * FROM t1,t2 WHERE t1.a=t2.a ORDER BY t2.X,t1.a
** SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b ORDER BY t1.a,t2.c
** SELECT * FROM t1,t2 WHERE t1.a=t2.a ORDER BY t2.b,t1.a
*****************************************************************************/
static bool
eq_ref_table(JOIN *join, ORDER *start_order, JOIN_TAB *tab)
{
if (tab->cached_eq_ref_table) // If cached
return tab->eq_ref_table;
tab->cached_eq_ref_table=1;
if (tab->type == JT_CONST) // We can skip const tables
return (tab->eq_ref_table=1); /* purecov: inspected */
if (tab->type != JT_EQ_REF)
return (tab->eq_ref_table=0); // We must use this
Item **ref_item=tab->ref.items;
Item **end=ref_item+tab->ref.key_parts;
uint found=0;
table_map map=tab->table->map;
for (; ref_item != end ; ref_item++)
{
if (! (*ref_item)->const_item())
{ // Not a const ref
ORDER *order;
for (order=start_order ; order ; order=order->next)
{
if ((*ref_item)->eq(order->item[0]))
break;
}
if (order)
{
found++;
dbug_assert(!(order->used & map));
order->used|=map;
continue; // Used in ORDER BY
}
if (!only_eq_ref_tables(join,start_order, (*ref_item)->used_tables()))
return (tab->eq_ref_table=0);
}
}
/* Check that there was no reference to table before sort order */
for ( ; found && start_order ; start_order=start_order->next)
{
if (start_order->used & map)
{
found--;
continue;
}
if (start_order->depend_map & map)
return (tab->eq_ref_table=0);
}
return tab->eq_ref_table=1;
}
static bool
only_eq_ref_tables(JOIN *join,ORDER *order,table_map tables)
{
if (specialflag & SPECIAL_SAFE_MODE)
return 0; // skip this optimize /* purecov: inspected */
for (JOIN_TAB **tab=join->map2table ; tables ; tab++, tables>>=1)
{
if (tables & 1 && !eq_ref_table(join, order, *tab))
return 0;
}
return 1;
}
/* Update the dependency map for the tables */
static void update_depend_map(JOIN *join)
{
JOIN_TAB *join_tab=join->join_tab, *end=join_tab+join->tables;
for ( ; join_tab != end ; join_tab++)
{
TABLE_REF *ref= &join_tab->ref;
table_map depend_map=0;
Item **item=ref->items;
uint i;
for (i=0 ; i < ref->key_parts ; i++,item++)
depend_map|=(*item)->used_tables();
ref->depend_map=depend_map;
for (JOIN_TAB *join_tab2=join->join_tab;
depend_map ;
join_tab2++,depend_map>>=1 )
{
if (depend_map & 1)
ref->depend_map|=join_tab2->ref.depend_map;
}
}
}
/* Update the dependency map for the sort order */
static void update_depend_map(JOIN *join, ORDER *order)
{
for ( ; order ; order=order->next)
{
table_map depend_map;
order->item[0]->update_used_tables();
order->depend_map=depend_map=order->item[0]->used_tables();
if (!(order->depend_map & RAND_TABLE_BIT)) // Not item_sum() or RAND()
{
for (JOIN_TAB *join_tab=join->join_tab;
depend_map ;
join_tab++, depend_map>>=1)
{
if (depend_map & 1)
order->depend_map|=join_tab->ref.depend_map;
}
}
}
}
/*
** simple_order is set to 1 if sort_order only uses fields from head table
** and the head table is not a LEFT JOIN table
*/
static ORDER *
remove_const(JOIN *join,ORDER *first_order, COND *cond, bool *simple_order)
{
if (join->tables == join->const_tables)
return 0; // No need to sort
DBUG_ENTER("remove_const");
ORDER *order,**prev_ptr;
table_map first_table= join->join_tab[join->const_tables].table->map;
table_map not_const_tables= ~join->const_table_map;
table_map ref;
prev_ptr= &first_order;
*simple_order= join->join_tab[join->const_tables].on_expr ? 0 : 1;
/* NOTE: A variable of not_const_tables ^ first_table; breaks gcc 2.7 */
update_depend_map(join, first_order);
for (order=first_order; order ; order=order->next)
{
table_map order_tables=order->item[0]->used_tables();
if (order->item[0]->with_sum_func)
*simple_order=0; // Must do a temp table to sort
else if (!(order_tables & not_const_tables))
{
DBUG_PRINT("info",("removing: %s", order->item[0]->full_name()));
continue; // skipp const item
}
else
{
if (order_tables & RAND_TABLE_BIT)
*simple_order=0;
else
{
Item *comp_item=0;
if (cond && const_expression_in_where(cond,order->item[0], &comp_item))
{
DBUG_PRINT("info",("removing: %s", order->item[0]->full_name()));
continue;
}
if ((ref=order_tables & (not_const_tables ^ first_table)))
{
if (only_eq_ref_tables(join,first_order,ref))
{
DBUG_PRINT("info",("removing: %s", order->item[0]->full_name()));
continue;
}
*simple_order=0; // Must do a temp table to sort
}
}
}
*prev_ptr= order; // use this entry
prev_ptr= &order->next;
}
*prev_ptr=0;
if (!first_order) // Nothing to sort/group
*simple_order=1;
DBUG_PRINT("exit",("simple_order: %d",(int) *simple_order));
DBUG_RETURN(first_order);
}
static int
return_zero_rows(select_result *result,TABLE_LIST *tables,List<Item> &fields,
bool send_row, uint select_options,const char *info,
Item *having, Procedure *procedure)
{
DBUG_ENTER("return_zero_rows");
if (select_options & SELECT_DESCRIBE)
{
describe_info(current_thd, info);
DBUG_RETURN(0);
}
if (procedure)
{
if (result->prepare(fields)) // This hasn't been done yet
DBUG_RETURN(-1);
}
if (send_row)
{
for (TABLE_LIST *table=tables; table ; table=table->next)
mark_as_null_row(table->table); // All fields are NULL
if (having && having->val_int() == 0)
send_row=0;
}
if (!tables || !(result->send_fields(fields,1)))
{
if (send_row)
result->send_data(fields);
if (tables) // Not from do_select()
result->send_eof(); // Should be safe
}
DBUG_RETURN(0);
}
static void clear_tables(JOIN *join)
{
for (uint i=0 ; i < join->tables ; i++)
mark_as_null_row(join->table[i]); // All fields are NULL
}
/*****************************************************************************
** Make som simple condition optimization:
** If there is a test 'field = const' change all refs to 'field' to 'const'
** Remove all dummy tests 'item = item', 'const op const'.
** Remove all 'item is NULL', when item can never be null!
** item->marker should be 0 for all items on entry
** Return in cond_value FALSE if condition is impossible (1 = 2)
*****************************************************************************/
class COND_CMP :public ilink {
public:
static void *operator new(size_t size) {return (void*) sql_alloc((uint) size); }
static void operator delete(void *ptr __attribute__((unused)),
size_t size __attribute__((unused))) {} /*lint -e715 */
Item *and_level;
Item_func *cmp_func;
COND_CMP(Item *a,Item_func *b) :and_level(a),cmp_func(b) {}
};
#ifdef __GNUC__
template class I_List<COND_CMP>;
template class I_List_iterator<COND_CMP>;
template class List<Item_func_match>;
template class List_iterator<Item_func_match>;
#endif
/*
** change field = field to field = const for each found field = const in the
** and_level
*/
static void
change_cond_ref_to_const(I_List<COND_CMP> *save_list,Item *and_father,
Item *cond, Item *field, Item *value)
{
if (cond->type() == Item::COND_ITEM)
{
bool and_level= ((Item_cond*) cond)->functype() ==
Item_func::COND_AND_FUNC;
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
Item *item;
while ((item=li++))
change_cond_ref_to_const(save_list,and_level ? cond : item, item,
field, value);
return;
}
if (cond->eq_cmp_result() == Item::COND_OK)
return; // Not a boolean function
Item_bool_func2 *func= (Item_bool_func2*) cond;
Item *left_item= func->arguments()[0];
Item *right_item= func->arguments()[1];
Item_func::Functype functype= func->functype();
if (right_item->eq(field) && left_item != value)
{
Item *tmp=value->new_item();
if (tmp)
{
func->arguments()[1] = tmp;
func->update_used_tables();
if ((functype == Item_func::EQ_FUNC || functype == Item_func::EQUAL_FUNC)
&& and_father != cond && !left_item->const_item())
{
cond->marker=1;
COND_CMP *tmp2;
if ((tmp2=new COND_CMP(and_father,func)))
save_list->push_back(tmp2);
}
func->set_cmp_func(item_cmp_type(func->arguments()[0]->result_type(),
func->arguments()[1]->result_type()));
}
}
else if (left_item->eq(field) && right_item != value)
{
Item *tmp=value->new_item();
if (tmp)
{
func->arguments()[0] = value = tmp;
func->update_used_tables();
if ((functype == Item_func::EQ_FUNC || functype == Item_func::EQUAL_FUNC)
&& and_father != cond && !right_item->const_item())
{
func->arguments()[0] = func->arguments()[1]; // For easy check
func->arguments()[1] = value;
cond->marker=1;
COND_CMP *tmp2;
if ((tmp2=new COND_CMP(and_father,func)))
save_list->push_back(tmp2);
}
func->set_cmp_func(item_cmp_type(func->arguments()[0]->result_type(),
func->arguments()[1]->result_type()));
}
}
}
static void
propagate_cond_constants(I_List<COND_CMP> *save_list,COND *and_level,
COND *cond)
{
if (cond->type() == Item::COND_ITEM)
{
bool and_level= ((Item_cond*) cond)->functype() ==
Item_func::COND_AND_FUNC;
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
Item *item;
I_List<COND_CMP> save;
while ((item=li++))
{
propagate_cond_constants(&save,and_level ? cond : item, item);
}
if (and_level)
{ // Handle other found items
I_List_iterator<COND_CMP> cond_itr(save);
COND_CMP *cond_cmp;
while ((cond_cmp=cond_itr++))
if (!cond_cmp->cmp_func->arguments()[0]->const_item())
change_cond_ref_to_const(&save,cond_cmp->and_level,
cond_cmp->and_level,
cond_cmp->cmp_func->arguments()[0],
cond_cmp->cmp_func->arguments()[1]);
}
}
else if (and_level != cond && !cond->marker) // In a AND group
{
if (cond->type() == Item::FUNC_ITEM &&
(((Item_func*) cond)->functype() == Item_func::EQ_FUNC ||
((Item_func*) cond)->functype() == Item_func::EQUAL_FUNC))
{
Item_func_eq *func=(Item_func_eq*) cond;
bool left_const= func->arguments()[0]->const_item();
bool right_const=func->arguments()[1]->const_item();
if (!(left_const && right_const))
{
if (right_const)
{
func->arguments()[1]=resolve_const_item(func->arguments()[1],
func->arguments()[0]);
func->update_used_tables();
change_cond_ref_to_const(save_list,and_level,and_level,
func->arguments()[0],
func->arguments()[1]);
}
else if (left_const)
{
func->arguments()[0]=resolve_const_item(func->arguments()[0],
func->arguments()[1]);
func->update_used_tables();
change_cond_ref_to_const(save_list,and_level,and_level,
func->arguments()[1],
func->arguments()[0]);
}
}
}
}
}
static COND *
optimize_cond(COND *conds,Item::cond_result *cond_value)
{
if (!conds)
{
*cond_value= Item::COND_TRUE;
return conds;
}
/* change field = field to field = const for each found field = const */
DBUG_EXECUTE("where",print_where(conds,"original"););
propagate_cond_constants((I_List<COND_CMP> *) 0,conds,conds);
/*
** Remove all instances of item == item
** Remove all and-levels where CONST item != CONST item
*/
DBUG_EXECUTE("where",print_where(conds,"after const change"););
conds=remove_eq_conds(conds,cond_value) ;
DBUG_EXECUTE("info",print_where(conds,"after remove"););
return conds;
}
/*
** remove const and eq items. Return new item, or NULL if no condition
** cond_value is set to according:
** COND_OK query is possible (field = constant)
** COND_TRUE always true ( 1 = 1 )
** COND_FALSE always false ( 1 = 2 )
*/
static COND *
remove_eq_conds(COND *cond,Item::cond_result *cond_value)
{
if (cond->type() == Item::COND_ITEM)
{
bool and_level= ((Item_cond*) cond)->functype()
== Item_func::COND_AND_FUNC;
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
Item::cond_result tmp_cond_value;
*cond_value=Item::COND_UNDEF;
Item *item;
while ((item=li++))
{
Item *new_item=remove_eq_conds(item,&tmp_cond_value);
if (!new_item)
{
#ifdef DELETE_ITEMS
delete item; // This may be shared
#endif
li.remove();
}
else if (item != new_item)
{
#ifdef DELETE_ITEMS
delete item; // This may be shared
#endif
VOID(li.replace(new_item));
}
if (*cond_value == Item::COND_UNDEF)
*cond_value=tmp_cond_value;
switch (tmp_cond_value) {
case Item::COND_OK: // Not TRUE or FALSE
if (and_level || *cond_value == Item::COND_FALSE)
*cond_value=tmp_cond_value;
break;
case Item::COND_FALSE:
if (and_level)
{
*cond_value=tmp_cond_value;
return (COND*) 0; // Always false
}
break;
case Item::COND_TRUE:
if (!and_level)
{
*cond_value= tmp_cond_value;
return (COND*) 0; // Always true
}
break;
case Item::COND_UNDEF: // Impossible
break; /* purecov: deadcode */
}
}
if (!((Item_cond*) cond)->argument_list()->elements ||
*cond_value != Item::COND_OK)
return (COND*) 0;
if (((Item_cond*) cond)->argument_list()->elements == 1)
{ // Remove list
item= ((Item_cond*) cond)->argument_list()->head();
((Item_cond*) cond)->argument_list()->empty();
return item;
}
}
else if (cond->type() == Item::FUNC_ITEM &&
((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC)
{
/*
** Handles this special case for some ODBC applications:
** The are requesting the row that was just updated with a auto_increment
** value with this construct:
**
** SELECT * from table_name where auto_increment_column IS NULL
** This will be changed to:
** SELECT * from table_name where auto_increment_column = LAST_INSERT_ID
*/
Item_func_isnull *func=(Item_func_isnull*) cond;
Item **args= func->arguments();
THD *thd=current_thd;
if (args[0]->type() == Item::FIELD_ITEM)
{
Field *field=((Item_field*) args[0])->field;
if (field->flags & AUTO_INCREMENT_FLAG && !field->table->maybe_null &&
(thd->options & OPTION_AUTO_IS_NULL) &&
thd->insert_id())
{
COND *new_cond;
if ((new_cond= new Item_func_eq(args[0],
new Item_int("last_insert_id()",
thd->insert_id(),
21))))
{
cond=new_cond;
cond->fix_fields(thd,0);
}
thd->insert_id(0); // Clear for next request
}
/* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */
else if (((field->type() == FIELD_TYPE_DATE) ||
(field->type() == FIELD_TYPE_DATETIME)) &&
(field->flags & NOT_NULL_FLAG))
{
COND *new_cond;
if ((new_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2))))
{
cond=new_cond;
cond->fix_fields(thd,0);
}
}
}
}
else if (cond->const_item())
{
*cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
return (COND*) 0;
}
else if ((*cond_value= cond->eq_cmp_result()) != Item::COND_OK)
{ // boolan compare function
Item *left_item= ((Item_func*) cond)->arguments()[0];
Item *right_item= ((Item_func*) cond)->arguments()[1];
if (left_item->eq(right_item))
{
if (!left_item->maybe_null ||
((Item_func*) cond)->functype() == Item_func::EQUAL_FUNC)
return (COND*) 0; // Compare of identical items
}
}
*cond_value=Item::COND_OK;
return cond; /* Point at next and level */
}
/*
** Return 1 if the item is a const value in all the WHERE clause
*/
static bool
const_expression_in_where(COND *cond, Item *comp_item, Item **const_item)
{
if (cond->type() == Item::COND_ITEM)
{
bool and_level= (((Item_cond*) cond)->functype()
== Item_func::COND_AND_FUNC);
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
Item *item;
while ((item=li++))
{
bool res=const_expression_in_where(item, comp_item, const_item);
if (res) // Is a const value
{
if (and_level)
return 1;
}
else if (!and_level)
return 0;
}
return and_level ? 0 : 1;
}
else if (cond->eq_cmp_result() != Item::COND_OK)
{ // boolan compare function
Item_func* func= (Item_func*) cond;
if (func->functype() != Item_func::EQUAL_FUNC &&
func->functype() != Item_func::EQ_FUNC)
return 0;
Item *left_item= ((Item_func*) cond)->arguments()[0];
Item *right_item= ((Item_func*) cond)->arguments()[1];
if (left_item->eq(comp_item))
{
if (right_item->const_item())
{
if (*const_item)
return right_item->eq(*const_item);
*const_item=right_item;
return 1;
}
}
else if (right_item->eq(comp_item))
{
if (left_item->const_item())
{
if (*const_item)
return left_item->eq(*const_item);
*const_item=left_item;
return 1;
}
}
}
return 0;
}
/****************************************************************************
** Create a temp table according to a field list.
** Set distinct if duplicates could be removed
** Given fields field pointers are changed to point at tmp_table
** for send_fields
****************************************************************************/
Field *create_tmp_field(TABLE *table,Item *item, Item::Type type,
Item_result_field ***copy_func, Field **from_field,
bool group, bool modify_item)
{
switch (type) {
case Item::SUM_FUNC_ITEM:
{
Item_sum *item_sum=(Item_sum*) item;
bool maybe_null=item_sum->maybe_null;
switch (item_sum->sum_func()) {
case Item_sum::AVG_FUNC: /* Place for sum & count */
if (group)
return new Field_string(sizeof(double)+sizeof(longlong),
maybe_null, item->name,table,1);
else
return new Field_double(item_sum->max_length,maybe_null,
item->name, table, item_sum->decimals);
case Item_sum::STD_FUNC: /* Place for sum & count */
if (group)
return new Field_string(sizeof(double)*2+sizeof(longlong),
maybe_null, item->name,table,1);
else
return new Field_double(item_sum->max_length, maybe_null,
item->name,table,item_sum->decimals);
case Item_sum::UNIQUE_USERS_FUNC:
return new Field_long(9,maybe_null,item->name,table,1);
default:
switch (item_sum->result_type()) {
case REAL_RESULT:
return new Field_double(item_sum->max_length,maybe_null,
item->name,table,item_sum->decimals);
case INT_RESULT:
return new Field_longlong(item_sum->max_length,maybe_null,
item->name,table);
case STRING_RESULT:
if (item_sum->max_length > 255)
return new Field_blob(item_sum->max_length,maybe_null,
item->name,table,item->binary);
return new Field_string(item_sum->max_length,maybe_null,
item->name,table,item->binary);
}
}
current_thd->fatal_error=1;
return 0; // Error
}
case Item::FIELD_ITEM:
{
Field *org_field=((Item_field*) item)->field,*new_field;
*from_field=org_field;
if ((new_field= org_field->new_field(table))) // Should always be true
{
if (modify_item)
((Item_field*) item)->result_field= new_field;
else
new_field->field_name=item->name;
if (org_field->maybe_null())
new_field->flags&= ~NOT_NULL_FLAG; // Because of outer join
}
return new_field;
}
case Item::PROC_ITEM:
case Item::FUNC_ITEM:
case Item::COND_ITEM:
case Item::FIELD_AVG_ITEM:
case Item::FIELD_STD_ITEM:
/* The following can only happen with 'CREATE TABLE ... SELECT' */
case Item::INT_ITEM:
case Item::REAL_ITEM:
case Item::STRING_ITEM:
case Item::REF_ITEM:
case Item::NULL_ITEM:
{
bool maybe_null=item->maybe_null;
Field *new_field;
LINT_INIT(new_field);
switch (item->result_type()) {
case REAL_RESULT:
new_field=new Field_double(item->max_length,maybe_null,
item->name,table,item->decimals);
break;
case INT_RESULT:
new_field=new Field_longlong(item->max_length,maybe_null,
item->name,table);
break;
case STRING_RESULT:
if (item->max_length > 255)
new_field= new Field_blob(item->max_length,maybe_null,
item->name,table,item->binary);
else
new_field= new Field_string(item->max_length,maybe_null,
item->name,table,item->binary);
break;
}
if (copy_func)
*((*copy_func)++) = (Item_result_field*) item; // Save for copy_funcs
if (modify_item)
((Item_result_field*) item)->result_field=new_field;
return new_field;
}
default: // Dosen't have to be stored
return 0;
}
}
TABLE *
create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
ORDER *group, bool distinct, bool save_sum_fields,
bool allow_distinct_limit, uint select_options)
{
TABLE *table;
uint i,field_count,reclength,null_count,null_pack_length,
hidden_null_count, hidden_null_pack_length, hidden_field_count,
blob_count,group_null_items;
bool using_unique_constraint=0;
char *tmpname,path[FN_REFLEN];
byte *pos,*group_buff;
uchar *null_flags;
Field **reg_field,**from_field;
Copy_field *copy=0;
KEY *keyinfo;
KEY_PART_INFO *key_part_info;
Item_result_field **copy_func;
MI_COLUMNDEF *recinfo;
uint temp_pool_slot=MY_BIT_NONE;
DBUG_ENTER("create_tmp_table");
DBUG_PRINT("enter",("distinct: %d save_sum_fields: %d allow_distinct_limit: %d group: %d",
(int) distinct, (int) save_sum_fields,
(int) allow_distinct_limit,test(group)));
statistic_increment(created_tmp_tables, &LOCK_status);
if (use_temp_pool)
temp_pool_slot = bitmap_set_next(&temp_pool);
if (temp_pool_slot != MY_BIT_NONE) // we got a slot
sprintf(path, "%s%s_%lx_%i", mysql_tmpdir, tmp_file_prefix,
current_pid, temp_pool_slot);
else // if we run out of slots or we are not using tempool
sprintf(path,"%s%s%lx_%lx_%x",mysql_tmpdir,tmp_file_prefix,current_pid,
thd->thread_id, thd->tmp_table++);
if (group)
{
if (!param->quick_group)
group=0; // Can't use group key
else for (ORDER *tmp=group ; tmp ; tmp=tmp->next)
(*tmp->item)->marker=4; // Store null in key
if (param->group_length >= MAX_BLOB_WIDTH)
using_unique_constraint=1;
if (group)
distinct=0; // Can't use distinct
}
field_count=param->field_count+param->func_count+param->sum_func_count;
hidden_field_count=param->hidden_field_count;
if (!my_multi_malloc(MYF(MY_WME),
&table,sizeof(*table),
&reg_field,sizeof(Field*)*(field_count+1),
&from_field,sizeof(Field*)*field_count,
&copy_func,sizeof(*copy_func)*(param->func_count+1),
&param->keyinfo,sizeof(*param->keyinfo),
&key_part_info,
sizeof(*key_part_info)*(param->group_parts+1),