35 #include <drizzled/item/cache.h>
36 #include <drizzled/item/cmpfunc.h>
37 #include <drizzled/item/copy_string.h>
38 #include <drizzled/item/uint.h>
39 #include <drizzled/cached_item.h>
40 #include <drizzled/sql_base.h>
41 #include <drizzled/sql_select.h>
42 #include <drizzled/lock.h>
43 #include <drizzled/nested_join.h>
45 #include <drizzled/join_cache.h>
47 #include <drizzled/field/blob.h>
48 #include <drizzled/open_tables_state.h>
49 #include <drizzled/optimizer/position.h>
50 #include <drizzled/optimizer/sargable_param.h>
51 #include <drizzled/optimizer/key_use.h>
52 #include <drizzled/optimizer/range.h>
53 #include <drizzled/optimizer/sum.h>
54 #include <drizzled/optimizer/explain_plan.h>
55 #include <drizzled/optimizer/access_method_factory.h>
56 #include <drizzled/optimizer/access_method.h>
57 #include <drizzled/records.h>
58 #include <drizzled/probes.h>
59 #include <drizzled/internal/my_bit.h>
60 #include <drizzled/internal/my_sys.h>
61 #include <drizzled/internal/iocache.h>
62 #include <drizzled/plugin/storage_engine.h>
63 #include <drizzled/session.h>
64 #include <drizzled/select_result.h>
65 #include <drizzled/debug.h>
66 #include <drizzled/item/subselect.h>
67 #include <drizzled/my_hash.h>
68 #include <drizzled/sql_lex.h>
69 #include <drizzled/statistics_variables.h>
70 #include <drizzled/system_variables.h>
77 extern plugin::StorageEngine *heap_engine;
83 static uint32_t cache_record_length(Join *join, uint32_t index);
84 static double prev_record_reads(Join *join, uint32_t idx, table_map found_ref);
89 optimizer::KeyUse *key);
90 static bool choose_plan(Join *join,table_map join_tables);
93 table_map remaining_tables,
98 static bool greedy_search(Join *join, table_map remaining_tables, uint32_t depth, uint32_t prune_level);
100 table_map remaining_tables,
105 uint32_t prune_level);
107 static void make_simple_join(Join*, Table*);
109 static bool make_join_select(Join *join, optimizer::SqlSelect *select,COND *item);
110 static void make_join_readinfo(Join&);
113 static Order *
remove_constants(Join *join,Order *first_order,COND *cond,
bool change_list,
bool *simple_order);
114 static void return_zero_rows(Join *join,
119 uint64_t select_options,
122 static COND *
simplify_joins(Join *join, List<TableList> *join_list, COND *conds,
bool top);
123 static int remove_duplicates(Join *join,Table *entry,List<Item> &fields, Item *having);
125 Item **ref_pointer_array,
129 List<Item> &all_fields,
133 bool *hidden_group_fields);
134 static bool make_join_statistics(Join *join, TableList *leaves, COND *conds, DYNAMIC_ARRAY *keyuse);
141 static void free_blobs(Field **ptr);
145 uint64_t select_options_arg,
158 sort_and_group(false),
162 no_field_update(false),
164 resume_nested_loop(false),
165 no_const_tables(false),
166 select_distinct(false),
167 group_optimized_away(false),
171 skip_sort_order(false),
175 hidden_group_fields(false),
177 found_const_table_map(0),
184 fetch_limit(HA_POS_ERROR),
185 session(session_arg),
186 fields_list(fields_arg),
191 exec_tmp_table1(NULL),
192 exec_tmp_table2(NULL),
197 having_history(NULL),
198 select_options(select_options_arg),
200 lock(session_arg->open_tables.lock),
202 all_fields(fields_arg),
206 ref_pointer_array(NULL),
211 ref_pointer_array_size(0),
212 zero_result_cause(NULL),
215 join_tab_reexec(NULL)
220 memset(&keyuse, 0,
sizeof(keyuse));
221 tmp_table_param.init();
222 tmp_table_param.end_write_records= HA_POS_ERROR;
223 rollup.setState(Rollup::STATE_NONE);
234 uint64_t select_options_arg,
248 sort_and_group=
false;
252 no_field_update=
false;
265 hidden_group_fields=
false;
267 found_const_table_map= 0;
275 session= session_arg;
282 exec_tmp_table2= NULL;
288 select_options= select_options_arg;
290 lock= session_arg->open_tables.lock;
305 join_tab_reexec= NULL;
309 memset(&keyuse, 0,
sizeof(keyuse));
310 tmp_table_param.init();
311 tmp_table_param.end_write_records= HA_POS_ERROR;
312 rollup.setState(Rollup::STATE_NONE);
315 bool Join::is_top_level_join()
const
317 return unit == &session->lex().unit && (
unit->fake_select_lex == 0 ||
select_lex ==
unit->fake_select_lex);
340 Select_Lex *select_lex_arg,
341 Select_Lex_Unit *unit_arg)
357 session->lex().current_select->is_item_list_lookup= 1;
363 select_lex->exclude_from_table_unique_test=
true;
367 if (!(select_options & OPTION_SETUP_TABLES_DONE) &&
392 nesting_map save_allow_sum_func= session->lex().allow_sum_func;
393 session->setWhere(
"having clause");
394 session->lex().allow_sum_func|= 1 << select_lex_arg->nest_level;
396 bool having_fix_rc= (!having->
fixed &&
397 (having->fix_fields(session, &having) ||
398 having->check_cols(1)));
400 if (having_fix_rc || session->
is_error())
402 session->lex().allow_sum_func= save_allow_sum_func;
412 if ((subselect=
select_lex->master_unit()->item))
414 if (subselect->substype() == Item_subselect::IN_SUBS)
418 bool do_materialize=
true;
448 if (do_materialize &&
450 !
select_lex->master_unit()->first_select()->next_select() &&
451 select_lex->master_unit()->first_select()->leaf_tables &&
452 session->lex().sql_command == SQLCOM_SELECT)
454 if (in_subs->is_top_level_item() &&
455 !in_subs->is_correlated &&
456 in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED)
457 in_subs->exec_method= Item_in_subselect::MATERIALIZATION;
460 Item_subselect::trans_res trans_res;
461 if ((trans_res= subselect->select_transformer(
this)) !=
462 Item_subselect::RES_OK)
464 return((trans_res == Item_subselect::RES_ERROR));
473 for (ord= order; ord; ord= ord->next)
475 Item *item= *ord->item;
476 if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM)
481 if (having && having->with_sum_func)
490 item_sum= item_sum->next;
493 }
while (item_sum != end);
507 select_lex->full_group_by_flag.test(NON_AGG_FIELD_USED) &&
508 select_lex->full_group_by_flag.test(SUM_FUNC_USED))
510 my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
511 ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
517 for (
Order *group_tmp=
group_list ; group_tmp ; group_tmp= group_tmp->next)
530 if (result && result->prepare(
fields_list, unit_arg))
539 #ifdef RESTRICTED_GROUP
540 if (sum_func_count && !
group_list && (func_count || field_count))
542 my_message(ER_WRONG_SUM_SELECT,ER(ER_WRONG_SUM_SELECT),MYF(0));
584 void Join::remove_subq_pushed_predicates(
Item **where)
586 if (conds->type() == Item::FUNC_ITEM &&
587 ((
Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
588 ((
Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM &&
589 ((
Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM &&
618 unit->select_limit_cnt);
620 select_limit=
unit->select_limit_cnt;
621 if (having || (select_options & OPTION_FOUND_ROWS))
622 select_limit= HA_POS_ERROR;
623 do_send_rows = (
unit->select_limit_cnt) ? 1 : 0;
625 if (session->lex().ignore)
626 session->lex().current_select->no_error= 1;
628 #ifdef HAVE_REF_TO_FIELDS // Not done yet
645 conds->fix_fields(session, &conds);
657 conds= optimize_cond(
this, conds,
join_list, &cond_value);
665 having= optimize_cond(
this, having,
join_list, &having_value);
676 if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE ||
677 (!
unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS)))
680 "Impossible HAVING" :
"Impossible WHERE";
682 goto setup_subq_exit;
698 if (res == HA_ERR_KEY_NOT_FOUND)
702 goto setup_subq_exit;
713 goto setup_subq_exit;
728 if (conds && !(session->lex().describe & DESCRIBE_EXTENDED))
730 COND *table_independent_conds= make_cond_for_table(conds, PSEUDO_TABLE_BITS, 0, 0);
731 conds= table_independent_conds;
733 goto setup_subq_exit;
755 if (result->initialize_tables(
this))
759 if (const_table_map != found_const_table_map &&
760 !(select_options & SELECT_DESCRIBE) &&
763 select_lex->master_unit() == &session->lex().unit))
766 goto setup_subq_exit;
768 if (!(session->
options & OPTION_BIG_SELECTS) &&
770 !(select_options & SELECT_DESCRIBE))
772 my_message(ER_TOO_BIG_SELECT, ER(ER_TOO_BIG_SELECT), MYF(0));
776 if (const_tables && !(select_options & SELECT_NO_UNLOCK))
778 if (!conds && outer_join)
783 select= optimizer::make_select(*table, const_table_map,
784 const_table_map, conds, 1, &error);
803 conds->update_used_tables();
810 for (
JoinTable *tab= join_tab + const_tables; tab < join_tab +
tables ; tab++)
812 if (*tab->on_expr_ref)
817 (*tab->on_expr_ref)->update_used_tables();
821 if (conds &&!outer_join && const_table_map != found_const_table_map &&
822 (select_options & SELECT_DESCRIBE) &&
823 select_lex->master_unit() == &session->lex().unit)
828 if (make_join_select(
this,
select, conds))
831 "Impossible WHERE noticed after reading const tables";
832 goto setup_subq_exit;
839 Order *org_order= order;
851 if (!order && org_order)
867 ! tmp_table_param.sum_func_count &&
868 (! join_tab[const_tables].select ||
869 ! join_tab[const_tables].select->
quick ||
871 optimizer::QuickSelectInterface::QS_TYPE_GROUP_MIN_MAX))
896 join_tab->table->keys_in_use_for_order_by=
897 join_tab->table->keys_in_use_for_group_by;
909 if (
group_list || tmp_table_param.sum_func_count)
911 if (! hidden_group_fields &&
rollup.getState() == Rollup::STATE_NONE)
932 bool all_order_fields_used;
935 &tab->table->keys_in_use_for_order_by);
938 &all_order_fields_used)))
942 &tab->table->keys_in_use_for_group_by) != 0);
944 if ((skip_group && all_order_fields_used) ||
945 select_limit == HA_POS_ERROR ||
951 if (all_order_fields_used)
959 tmp_table_param.quick_group=0;
973 Order *old_group_list;
975 rollup.getState() == Rollup::STATE_NONE,
994 send_group_parts= tmp_table_param.group_parts;
997 (!
group_list && tmp_table_param.sum_func_count))
1018 need_tmp= (const_tables != tables &&
1021 test(select_options & OPTION_BUFFER_RESULT)));
1024 make_join_readinfo(*
this);
1037 unit->item &&
unit->item->substype() == Item_subselect::IN_SUBS &&
1038 tables == 1 && conds &&
1044 if (join_tab[0].type == AM_EQ_REF && join_tab[0].ref.items[0]->name ==
in_left_expr_name)
1046 remove_subq_pushed_predicates(&where);
1047 save_index_subquery_explain_info(join_tab, where);
1048 join_tab[0].
type= AM_UNIQUE_SUBQUERY;
1052 else if (join_tab[0].type == AM_REF &&
1055 remove_subq_pushed_predicates(&where);
1056 save_index_subquery_explain_info(join_tab, where);
1057 join_tab[0].
type= AM_INDEX_SUBQUERY;
1062 else if (join_tab[0].type == AM_REF_OR_NULL &&
1064 having->
name == in_having_cond)
1066 join_tab[0].
type= AM_INDEX_SUBQUERY;
1069 save_index_subquery_explain_info(join_tab, conds);
1082 for (uint32_t i = const_tables; i <
tables; i++)
1083 join_tab[i].table->prepare_for_position();
1086 if (const_tables != tables)
1095 (join_tab[const_tables].type != AM_ALL) &&
1096 (join_tab[const_tables].type != AM_REF_OR_NULL) &&
1097 ((order && simple_order) || (
group_list && simple_group)))
1104 if (!(select_options & SELECT_BIG_RESULT) &&
1108 unit->select_limit_cnt, 0,
1109 &join_tab[const_tables].table->
1110 keys_in_use_for_group_by))) ||
1112 tmp_table_param.quick_group)
1114 need_tmp=1; simple_order=simple_group=0;
1122 for (
Order *tmp_order= order; tmp_order ; tmp_order=tmp_order->next)
1124 Item *item= *tmp_order->item;
1128 need_tmp=1; simple_order=simple_group=0;
1136 if (select_options & SELECT_DESCRIBE)
1154 if (join_tab->is_using_loose_index_scan())
1155 tmp_table_param.precomputed_group_by=
true;
1162 init_items_ref_array();
1165 Order *tmp_group= (((not simple_group) or not (getDebug().test(debug::NO_KEY_GROUP))) ?
group_list : NULL);
1175 !session->lex().current_select->with_sum_func) ?
1176 select_limit : HA_POS_ERROR;
1207 if (create_sort_index(session,
this,
group_list,
1208 HA_POS_ERROR, HA_POS_ERROR,
false) ||
1228 if (create_sort_index(session,
this, order, HA_POS_ERROR, HA_POS_ERROR,
true))
1245 JoinTable *last_join_tab= join_tab+tables-1;
1248 if (used_tables & last_join_tab->table->
map)
1250 last_join_tab->not_used_in_distinct=1;
1251 }
while (last_join_tab-- != join_tab);
1257 order,
unit->select_limit_cnt, 0,
1258 &join_tab[const_tables].table->
1259 keys_in_use_for_order_by))
1268 if (
select_lex->uncacheable.any() && not is_top_level_join())
1308 if (exec_tmp_table2)
1310 exec_tmp_table2->
cursor->extra(HA_EXTRA_RESET_STATE);
1312 exec_tmp_table2->free_io_cache();
1313 exec_tmp_table2->filesort_free_buffers();
1316 set_items_ref_array(
items0);
1327 Item_sum *func, **func_ptr= sum_funcs;
1328 while ((func= *(func_ptr++)))
1353 void Join::save_join_tab()
1383 if (select_options & SELECT_DESCRIBE)
1390 planner.printPlan();
1394 result->send_fields(*columns_list);
1400 if (cond_value != Item::COND_FALSE &&
1401 (!conds || conds->
val_int()) &&
1402 (!having || having->
val_int()))
1404 if (do_send_rows && result->send_data(
fields_list))
1408 error= (int) result->send_eof();
1409 send_records= ((select_options & OPTION_FOUND_ROWS) ? 1 : session->
sent_row_count);
1414 error= (int) result->send_eof();
1433 return_zero_rows(
this, result,
select_lex->leaf_tables, *columns_list, send_row_on_empty_set(), select_options,
zero_result_cause, having);
1437 if (select_options & SELECT_DESCRIBE)
1448 simple_order= simple_group;
1451 if (order && (order !=
group_list || !(select_options & SELECT_BIG_RESULT)))
1453 if (const_tables ==
tables
1455 &&
test_if_skip_sort_order(&join_tab[const_tables], order, select_limit, 0, &join_tab[const_tables].table->keys_in_use_for_query)))
1463 !
tables ?
"No tables used" : NULL);
1464 planner.printPlan();
1468 Join *curr_join=
this;
1471 Table *curr_tmp_table= 0;
1477 curr_join->examined_rows= 0;
1496 if (! curr_join->sort_and_group && curr_join->const_tables != curr_join->
tables)
1497 curr_join->join_tab[curr_join->const_tables].
sorted= 0;
1498 if ((tmp_error=
do_select(curr_join, NULL, curr_tmp_table)))
1503 curr_tmp_table->
cursor->info(HA_STATUS_VARIABLE);
1505 if (curr_join->having)
1513 if (sort_and_group || curr_tmp_table->group)
1529 curr_join->items1= items1;
1533 curr_join->set_items_ref_array(items1);
1535 if (sort_and_group || curr_tmp_table->group)
1537 curr_join->tmp_table_param.field_count+= curr_join->tmp_table_param.sum_func_count
1538 + curr_join->tmp_table_param.func_count;
1539 curr_join->tmp_table_param.sum_func_count= 0;
1540 curr_join->tmp_table_param.func_count= 0;
1544 curr_join->tmp_table_param.field_count+= curr_join->tmp_table_param.func_count;
1545 curr_join->tmp_table_param.func_count= 0;
1548 if (curr_tmp_table->group)
1564 || (curr_join->
select_distinct && curr_join->tmp_table_param.using_indirect_summary_function))
1568 make_simple_join(curr_join, curr_tmp_table);
1573 curr_join->tmp_table_param.hidden_field_count= curr_join->
tmp_all_fields1.size()
1576 if (exec_tmp_table2)
1578 curr_tmp_table= exec_tmp_table2;
1589 if (curr_join->join_tab->is_using_loose_index_scan())
1590 curr_join->tmp_table_param.precomputed_group_by=
true;
1592 if (!(curr_tmp_table=
1594 &curr_join->tmp_table_param,
1599 1, curr_join->select_options,
1606 curr_join->exec_tmp_table2= exec_tmp_table2;
1611 if (curr_join->join_tab == join_tab)
1613 if (create_sort_index(session, curr_join, curr_join->
group_list, HA_POS_ERROR, HA_POS_ERROR,
false) ||
1618 sortorder= curr_join->sortorder;
1623 if (curr_join !=
this)
1628 curr_join->sum_funcs_end= sum_funcs_end2;
1634 sum_funcs_end2= curr_join->sum_funcs_end;
1641 if (!curr_join->sort_and_group && (curr_join->const_tables != curr_join->
tables))
1642 curr_join->join_tab[curr_join->const_tables].
sorted= 0;
1645 || (tmp_error=
do_select(curr_join, NULL, curr_tmp_table)))
1650 curr_join->join_tab->read_record.end_read_record();
1651 curr_join->const_tables= curr_join->
tables;
1652 curr_join->join_tab[0].table= 0;
1659 tmp_fields_list2, tmp_all_fields2,
1662 curr_join->tmp_fields_list2= tmp_fields_list2;
1663 curr_join->tmp_all_fields2= tmp_all_fields2;
1665 curr_fields_list= &curr_join->tmp_fields_list2;
1666 curr_all_fields= &curr_join->tmp_all_fields2;
1667 curr_join->set_items_ref_array(items2);
1668 curr_join->tmp_table_param.field_count+= curr_join->tmp_table_param.sum_func_count;
1669 curr_join->tmp_table_param.sum_func_count= 0;
1671 if (curr_tmp_table->distinct)
1681 if (remove_duplicates(curr_join, curr_tmp_table,
1688 curr_tmp_table->reginfo.lock_type= TL_UNLOCK;
1689 make_simple_join(curr_join, curr_tmp_table);
1695 if (curr_join->group || curr_join->tmp_table_param.sum_func_count)
1703 init_items_ref_array();
1706 items3, tmp_fields_list3, tmp_all_fields3,
1707 curr_fields_list->size(), *curr_all_fields);
1708 tmp_table_param.save_copy_funcs= curr_join->tmp_table_param.copy_funcs;
1709 tmp_table_param.save_copy_field= curr_join->tmp_table_param.copy_field;
1710 tmp_table_param.save_copy_field_end= curr_join->tmp_table_param.copy_field_end;
1711 curr_join->tmp_all_fields3= tmp_all_fields3;
1712 curr_join->tmp_fields_list3= tmp_fields_list3;
1716 curr_join->tmp_table_param.copy_funcs= tmp_table_param.save_copy_funcs;
1717 curr_join->tmp_table_param.copy_field= tmp_table_param.save_copy_field;
1718 curr_join->tmp_table_param.copy_field_end= tmp_table_param.save_copy_field_end;
1720 curr_fields_list= &tmp_fields_list3;
1721 curr_all_fields= &tmp_all_fields3;
1722 curr_join->set_items_ref_array(items3);
1730 if (curr_join->
group_list || curr_join->order)
1738 JoinTable *curr_table= &curr_join->join_tab[curr_join->const_tables];
1739 table_map used_tables= (curr_join->const_table_map |
1740 curr_table->table->
map);
1742 Item* sort_table_cond= make_cond_for_table(curr_join->
tmp_having, used_tables, used_tables, 0);
1743 if (sort_table_cond)
1745 if (!curr_table->select)
1747 if (!curr_table->select->
cond)
1748 curr_table->select->
cond= sort_table_cond;
1758 curr_table->select_cond= curr_table->select->
cond;
1760 curr_join->
tmp_having= make_cond_for_table(curr_join->
tmp_having, ~ (table_map) 0, ~used_tables, 0);
1765 curr_join->select_limit= HA_POS_ERROR;
1772 JoinTable *curr_table= &curr_join->join_tab[curr_join->const_tables+1];
1774 for (; curr_table < end_table ; curr_table++)
1780 if (curr_table->select_cond ||
1784 curr_join->select_limit= HA_POS_ERROR;
1789 if (curr_join->join_tab == join_tab)
1800 if (create_sort_index(session, curr_join,
1803 curr_join->select_limit,
1804 (select_options & OPTION_FOUND_ROWS ?
1805 HA_POS_ERROR :
unit->select_limit_cnt),
1809 sortorder= curr_join->sortorder;
1810 if (curr_join->const_tables != curr_join->
tables &&
1811 !curr_join->join_tab[curr_join->const_tables].table->sort.io_cache)
1829 curr_join->fields= curr_fields_list;
1832 result->send_fields(*curr_fields_list);
1833 error=
do_select(curr_join, curr_fields_list, NULL);
1844 if (
items0 && (session->lex().describe & DESCRIBE_EXTENDED) &&
select_lex->linkage == DERIVED_TABLE_TYPE)
1845 set_items_ref_array(
items0);
1862 if (join_tab !=
tmp_join->join_tab)
1865 for (tab= join_tab, end= tab+
tables ; tab != end ; tab++)
1869 tmp_table_param.copy_field=0;
1876 exec_tmp_table2= NULL;
1906 for (Select_Lex_Unit *un=
select_lex->first_inner_unit(); un;
1907 un= un->next_unit())
1909 for (Select_Lex *sl= un->first_select(); sl; sl= sl->next_select())
1912 if (subquery_predicate &&
1913 subquery_predicate->substype() == Item_subselect::IN_SUBS)
1916 if (in_subs->exec_method == Item_in_subselect::MATERIALIZATION &&
1969 Select_Lex_Unit *tmp_unit;
1975 bool full= (
select_lex->uncacheable.none() && ! session->lex().describe);
1976 bool can_unlock= full;
1980 for (tmp_unit=
select_lex->first_inner_unit();
1982 tmp_unit= tmp_unit->next_unit())
1983 for (sl= tmp_unit->first_select(); sl; sl= sl->next_select())
1986 bool full_local= full && (!subselect ||
1987 (subselect->is_evaluated() &&
1988 !subselect->is_uncacheable()));
1998 sl->cleanup_all_joins(full_local);
2000 can_unlock= can_unlock && full_local;
2007 if (can_unlock && lock && session->open_tables.lock &&
2008 !(select_options & SELECT_NO_UNLOCK) &&
2010 (
select_lex == (session->lex().unit.fake_select_lex ?
2011 session->lex().unit.fake_select_lex : &session->lex().select_lex)))
2044 if (
tables > const_tables)
2046 table[const_tables]->free_io_cache();
2047 table[const_tables]->filesort_free_buffers(full);
2057 for (tab= join_tab, end= tab+
tables; tab != end; tab++)
2063 for (tab= join_tab, end= tab+
tables; tab != end; tab++)
2066 tab->table->
cursor->ha_index_or_rnd_end();
2078 tmp_table_param.copy_field= 0;
2079 group_fields.delete_elements();
2084 tmp_table_param.copy_funcs.clear();
2093 tmp_join->tmp_table_param.copy_field ==
2094 tmp_table_param.copy_field)
2096 tmp_join->tmp_table_param.copy_field=
2097 tmp_join->tmp_table_param.save_copy_field= 0;
2099 tmp_table_param.cleanup();
2107 static void clear_tables(
Join *join)
2113 for (uint32_t i= join->const_tables; i < join->tables; i++)
2115 join->table[i]->mark_as_null_row();
2130 uint32_t func_count, group_parts;
2132 func_count= tmp_table_param.sum_func_count;
2137 if (
rollup.getState() != Rollup::STATE_NONE)
2138 func_count*= (send_group_parts+1);
2140 group_parts= send_group_parts;
2155 for (ord= order; ord; ord= ord->next)
2161 sum_funcs= (
Item_sum**) session->mem.calloc(
sizeof(
Item_sum**) * (func_count+1) +
2162 sizeof(
Item_sum***) * (group_parts+1));
2163 sum_funcs_end= (
Item_sum***) (sum_funcs+func_count+1);
2164 return(sum_funcs == 0);
2182 bool before_group_by,
2189 if (*sum_funcs && !recompute)
2195 if (item->type() == Item::SUM_FUNC_ITEM && !item->
const_item() &&
2196 (!((
Item_sum*) item)->depended_from() ||
2200 if (before_group_by &&
rollup.getState() == Rollup::STATE_INITED)
2202 rollup.setState(Rollup::STATE_READY);
2206 else if (
rollup.getState() == Rollup::STATE_NONE)
2208 for (uint32_t i=0 ; i <= send_group_parts ;i++)
2209 sum_funcs_end[i]= func;
2211 else if (
rollup.getState() == Rollup::STATE_READY)
2221 tmp_table_param.quick_group= 0;
2222 rollup.setState(Rollup::STATE_INITED);
2228 tmp_table_param.group_parts= send_group_parts;
2232 rollup.setRefPointerArrays((
Item***) (
rollup.getFields() + send_group_parts));
2233 Item** ref_array= (
Item**) (
rollup.getRefPointerArrays()+send_group_parts);
2239 for (uint32_t i= 0 ; i < send_group_parts ; i++)
2243 rollup_fields->clear();
2244 rollup.getRefPointerArrays()[i]= ref_array;
2248 for (uint32_t i= 0 ; i < send_group_parts; i++)
2250 for (uint32_t j= 0 ; j <
fields_list.size() ; j++)
2252 rollup.getFields()[i].push_back(
rollup.getNullItems()[i]);
2257 while (
Item* item= it++)
2260 bool found_in_group= 0;
2262 for (group_tmp=
group_list; group_tmp; group_tmp= group_tmp->next)
2264 if (*group_tmp->item == item)
2266 item->maybe_null= 1;
2268 if (item->const_item())
2284 new_item->fix_fields(session, NULL);
2285 *it.ref()= new_item;
2286 for (
Order *tmp= group_tmp; tmp; tmp= tmp->next)
2288 if (*tmp->item == item)
2289 *tmp->item= new_item;
2294 if (item->type() == Item::FUNC_ITEM && !found_in_group)
2296 bool changed=
false;
2305 item->with_sum_func= 1;
2329 Item *first_field= &sel_fields.front();
2353 for (level=0 ; level < send_group_parts ; level++)
2356 uint32_t pos= send_group_parts - level -1;
2357 bool real_fields= 0;
2360 Item **ref_array_start=
rollup.getRefPointerArrays()[pos];
2364 Item **ref_array= ref_array_start + fields_arg.size()-1;
2367 sum_funcs_end[pos+1]= *func;
2370 for (i= 0, start_group=
group_list ;i++ < pos ;start_group= start_group->next)
2373 it= fields_arg.begin();
2374 while ((item= it++))
2376 if (item == first_field)
2379 ref_array= ref_array_start;
2382 if (item->type() == Item::SUM_FUNC_ITEM && !item->
const_item() &&
2383 (!((
Item_sum*) item)->depended_from() ||
2395 item= item->copy_or_same(session);
2404 for (group_tmp= start_group, i= pos ;
2405 group_tmp ; group_tmp= group_tmp->next, i++)
2407 if (*group_tmp->item == item)
2415 null_item->result_field= item->get_tmp_table_field();
2425 new_it.replace(item);
2432 sum_funcs_end[0]= *func;
2456 for (uint32_t i= send_group_parts ; i-- > idx ; )
2461 if ((!having || having->
val_int()))
2463 if (send_records < unit->select_limit_cnt && do_send_rows && result->send_data(
rollup.getFields()[i]))
2471 set_items_ref_array(current_ref_pointer_array);
2497 for (uint32_t i= send_group_parts ; i-- > idx ; )
2502 if ((!having || having->
val_int()))
2505 while (
Item* item= it++)
2507 if (item->type() == Item::NULL_ITEM && item->is_result_field())
2508 item->save_in_result_field(1);
2511 if (table_arg->
cursor->insertRecord(table_arg->getInsertRecord()))
2513 my_error(ER_USE_SQL_BIG_RESULT, MYF(0));
2519 set_items_ref_array(current_ref_pointer_array);
2535 Item_sum *func, **func_ptr= sum_funcs;
2536 while ((func= *(func_ptr++)))
2567 bool cache_flag=
false;
2568 bool *analyzer_arg= &cache_flag;
2571 if (const_tables ==
tables)
2582 for (
JoinTable *tab= join_tab + const_tables; tab < join_tab +
tables ; tab++)
2584 if (*tab->on_expr_ref)
2588 (
unsigned char **)&analyzer_arg,
2590 (
unsigned char *)&cache_flag);
2608 bool not_used_in_distinct= join_tab->not_used_in_distinct;
2609 ha_rows found_records= join->found_records;
2610 COND *select_cond= join_tab->select_cond;
2612 if (error > 0 || (join->session->
is_error()))
2613 return NESTED_LOOP_ERROR;
2615 return NESTED_LOOP_NO_MORE_ROWS;
2616 if (join->session->getKilled())
2618 join->session->send_kill_message();
2619 return NESTED_LOOP_KILLED;
2621 if (!select_cond || select_cond->
val_int())
2640 first_unmatched->
found= 1;
2641 for (
JoinTable *tab= first_unmatched; tab <= join_tab; tab++)
2643 if (tab->table->reginfo.not_exists_optimize)
2644 return NESTED_LOOP_NO_MORE_ROWS;
2651 if (tab->select_cond && !tab->select_cond->val_int())
2654 if (tab == join_tab)
2663 return NESTED_LOOP_OK;
2671 if ((first_unmatched= first_unmatched->
first_upper) &&
2685 join->examined_rows++;
2692 rc= (*join_tab->next_select)(join, join_tab+1, 0);
2693 if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
2695 if (return_tab < join->return_tab)
2699 return NESTED_LOOP_OK;
2705 if (not_used_in_distinct && found_records != join->found_records)
2706 return NESTED_LOOP_NO_MORE_ROWS;
2709 join_tab->read_record.cursor->unlock_row();
2717 join->examined_rows++;
2719 join_tab->read_record.cursor->unlock_row();
2721 return NESTED_LOOP_OK;
2739 for ( ; join_tab <= last_inner_tab ; join_tab++)
2745 join_tab->table->restoreRecordAsDefault();
2746 join_tab->table->mark_as_null_row();
2747 select_cond= join_tab->select_cond;
2749 if (select_cond && !select_cond->
val_int())
2750 return NESTED_LOOP_OK;
2765 if (! first_unmatched)
2767 first_unmatched->
found= 1;
2768 for (
JoinTable *tab= first_unmatched; tab <= join_tab; tab++)
2770 if (tab->select_cond && !tab->select_cond->val_int())
2773 return NESTED_LOOP_OK;
2783 return (*join_tab->next_select)(join, join_tab+1, 0);
2792 join_tab->table->null_row= 0;
2793 if (!join_tab->cache.records)
2795 return NESTED_LOOP_OK;
2800 (void) join_tab->cache.store_record_in_cache();
2804 if (join_tab->use_quick == 2)
2806 delete join_tab->select->quick;
2807 join_tab->select->quick= 0;
2810 if ((error=join_init_read_record(join_tab)))
2812 join_tab->cache.reset_cache_write();
2813 return error < 0 ? NESTED_LOOP_NO_MORE_ROWS: NESTED_LOOP_ERROR;
2816 for (JoinTable *tmp=join->join_tab; tmp != join_tab ; tmp++)
2818 tmp->status=tmp->table->status;
2819 tmp->table->status=0;
2822 info= &join_tab->read_record;
2825 if (join->session->getKilled())
2827 join->session->send_kill_message();
2828 return NESTED_LOOP_KILLED;
2830 optimizer::SqlSelect *select= join_tab->select;
2831 if (rc == NESTED_LOOP_OK &&
2832 (!join_tab->cache.select || !join_tab->cache.select->skip_record()))
2835 join_tab->cache.reset_cache_read();
2836 for (i=(join_tab->cache.records- (skip_last ? 1 : 0)) ; i-- > 0 ;)
2838 join_tab->readCachedRecord();
2839 if (!select || !select->skip_record())
2843 rc= (join_tab->next_select)(join,join_tab+1,0);
2844 if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
2846 join_tab->cache.reset_cache_write();
2851 return NESTED_LOOP_ERROR;
2855 }
while (!(error=info->read_record(info)));
2858 join_tab->readCachedRecord();
2859 join_tab->cache.reset_cache_write();
2861 return NESTED_LOOP_ERROR;
2862 for (JoinTable *tmp2=join->join_tab; tmp2 != join_tab ; tmp2++)
2863 tmp2->table->status=tmp2->status;
2864 return NESTED_LOOP_OK;
2893 if (! end_of_records)
2896 if (join->having && join->having->val_int() == 0)
2897 return NESTED_LOOP_OK;
2899 if (join->do_send_rows)
2900 error=join->result->send_data(*join->fields);
2902 return NESTED_LOOP_ERROR;
2903 if (++join->send_records >= join->unit->select_limit_cnt && join->do_send_rows)
2905 if (join->select_options & OPTION_FOUND_ROWS)
2907 JoinTable *jt=join->join_tab;
2908 if ((join->tables == 1) && !join->tmp_table && !join->sort_and_group
2909 && !join->send_group_parts && !join->having && !jt->select_cond &&
2910 !(jt->select && jt->select->quick) &&
2911 (jt->table->cursor->getEngine()->check_flag(HTON_BIT_STATS_RECORDS_IS_EXACT)) &&
2915 Table *table= jt->table;
2917 join->select_options^= OPTION_FOUND_ROWS;
2918 if (table->sort.record_pointers ||
2919 (table->sort.io_cache && table->sort.io_cache->inited()))
2922 join->send_records= table->sort.found_records;
2926 table->cursor->info(HA_STATUS_VARIABLE);
2927 join->send_records= table->cursor->stats.records;
2932 join->do_send_rows= 0;
2933 if (join->unit->fake_select_lex)
2934 join->unit->fake_select_lex->select_limit= 0;
2935 return NESTED_LOOP_OK;
2938 return NESTED_LOOP_QUERY_LIMIT;
2940 else if (join->send_records >= join->fetch_limit)
2946 return NESTED_LOOP_CURSOR_LIMIT;
2950 return NESTED_LOOP_OK;
2955 Table *table= join->tmp_table;
2957 if (join->session->getKilled())
2959 join->session->send_kill_message();
2960 return NESTED_LOOP_KILLED;
2962 if (!end_of_records)
2965 if (
copy_funcs(join->tmp_table_param.items_to_copy, join->session))
2966 return NESTED_LOOP_ERROR;
2967 if (!join->having || join->having->val_int())
2970 join->found_records++;
2971 if ((error=table->cursor->insertRecord(table->getInsertRecord())))
2973 if (!table->cursor->is_fatal_error(error, HA_CHECK_DUP))
2975 return NESTED_LOOP_OK;
2978 my_error(ER_USE_SQL_BIG_RESULT, MYF(0));
2979 return NESTED_LOOP_ERROR;
2981 if (++join->send_records >= join->tmp_table_param.end_write_records && join->do_send_rows)
2983 if (!(join->select_options & OPTION_FOUND_ROWS))
2984 return NESTED_LOOP_QUERY_LIMIT;
2985 join->do_send_rows= 0;
2986 join->unit->select_limit_cnt= HA_POS_ERROR;
2987 return NESTED_LOOP_OK;
2992 return NESTED_LOOP_OK;
2998 Table *table= join->tmp_table;
3003 return NESTED_LOOP_OK;
3004 if (join->session->getKilled())
3006 join->session->send_kill_message();
3007 return NESTED_LOOP_KILLED;
3010 join->found_records++;
3013 for (group=table->group ; group ; group=group->next)
3015 Item *item= *group->item;
3016 item->save_org_in_field(group->field);
3019 group->buff[-1]= (char) group->field->is_null();
3022 join->tmp_table_param.group_buff,
3026 table->restoreRecord();
3028 if ((error= table->
cursor->updateRecord(table->getUpdateRecord(),
3029 table->getInsertRecord())))
3031 table->print_error(error,MYF(0));
3032 return NESTED_LOOP_ERROR;
3034 return NESTED_LOOP_OK;
3043 for (group=table->group,key_part=table->
key_info[0].key_part;
3045 group=group->next,key_part++)
3047 if (key_part->null_bit)
3048 memcpy(table->getInsertRecord()+key_part->offset, group->buff, 1);
3050 init_tmptable_sum_functions(join->sum_funcs);
3051 if (
copy_funcs(join->tmp_table_param.items_to_copy, join->session))
3052 return NESTED_LOOP_ERROR;
3053 if ((error=table->
cursor->insertRecord(table->getInsertRecord())))
3055 my_error(ER_USE_SQL_BIG_RESULT, MYF(0));
3056 return NESTED_LOOP_ERROR;
3058 join->send_records++;
3059 return NESTED_LOOP_OK;
3065 Table *table= join->tmp_table;
3069 return NESTED_LOOP_OK;
3070 if (join->session->getKilled())
3072 join->session->send_kill_message();
3073 return NESTED_LOOP_KILLED;
3076 init_tmptable_sum_functions(join->sum_funcs);
3078 if (
copy_funcs(join->tmp_table_param.items_to_copy, join->session))
3079 return NESTED_LOOP_ERROR;
3081 if (!(error= table->
cursor->insertRecord(table->getInsertRecord())))
3082 join->send_records++;
3087 table->print_error(error,MYF(0));
3088 return NESTED_LOOP_ERROR;
3090 if (table->
cursor->rnd_pos(table->getUpdateRecord(),table->
cursor->dup_ref))
3092 table->print_error(error,MYF(0));
3093 return NESTED_LOOP_ERROR;
3095 table->restoreRecord();
3097 if ((error= table->
cursor->updateRecord(table->getUpdateRecord(),
3098 table->getInsertRecord())))
3100 table->print_error(error,MYF(0));
3101 return NESTED_LOOP_ERROR;
3104 return NESTED_LOOP_OK;
3121 if (main_join->group_fields_cache.size())
3123 curr_join->group_fields= main_join->group_fields_cache;
3124 curr_join->sort_and_group= 1;
3130 main_join->group_fields_cache= curr_join->group_fields;
3140 uint32_t key_length=0, parts=0, null_parts=0;
3144 for (; group ; group=group->next)
3146 Item *group_item= *group->item;
3147 Field *field= group_item->get_tmp_table_field();
3150 enum_field_types type;
3151 if ((type= field->type()) == DRIZZLE_TYPE_BLOB)
3152 key_length+=MAX_BLOB_WIDTH;
3153 else if (type == DRIZZLE_TYPE_VARCHAR)
3160 switch (group_item->result_type()) {
3162 key_length+=
sizeof(double);
3166 key_length+=
sizeof(int64_t);
3169 case DECIMAL_RESULT:
3170 key_length+= class_decimal_get_binary_size(group_item->max_length -
3171 (group_item->decimals ? 1 : 0),
3172 group_item->decimals);
3177 enum enum_field_types type= group_item->field_type();
3183 if (field::isDateTime(type))
3194 key_length+= group_item->max_length + HA_KEY_BLOB_LENGTH;
3203 my_error(ER_OUT_OF_RESOURCES, MYF(ME_FATALERROR));
3213 join->tmp_table_param.group_length=key_length+null_parts;
3214 join->tmp_table_param.group_parts=parts;
3215 join->tmp_table_param.group_null_parts=null_parts;
3227 for (; group ; group=group->next)
3232 join->group_fields.push_front(tmp);
3235 join->sort_and_group=1;
3239 static uint32_t cache_record_length(Join *join,uint32_t idx)
3242 JoinTable **pos,**end;
3243 Session *session=join->session;
3245 for (pos=join->best_ref+join->const_tables,end=join->best_ref+idx ;
3249 JoinTable *join_tab= *pos;
3250 if (!join_tab->used_fieldlength)
3252 length+=join_tab->used_fieldlength;
3307 static double prev_record_reads(Join *join, uint32_t idx, table_map found_ref)
3310 optimizer::Position *pos_end= join->getSpecificPosInPartialPlan(-1);
3311 for (optimizer::Position *pos= join->getSpecificPosInPartialPlan(idx - 1);
3315 if (pos->examinePosition(found_ref))
3317 found_ref|= pos->getRefDependMap();
3334 if (pos->getFanout() > DBL_EPSILON)
3335 found*= pos->getFanout();
3347 table_map used_tables;
3350 uint32_t table_count;
3351 Session *session=join->session;
3354 table_count=join->
tables;
3355 join->join_tab=join_tab=
new (session->mem)
JoinTable[table_count];
3358 used_tables= OUTER_REF_TABLE_BIT;
3359 for (j=join_tab, tablenr=0 ; tablenr < table_count ; tablenr++,j++)
3363 *j= *cur_pos.getJoinTable();
3364 form=join->table[tablenr]=j->table;
3365 used_tables|= form->
map;
3366 form->reginfo.join_tab=j;
3368 form->reginfo.not_exists_optimize=0;
3369 if (j->
type == AM_CONST)
3375 if (j->
type == AM_SYSTEM)
3377 if (j->
keys.none() || ! (keyuse= cur_pos.getKeyUse()))
3380 if (tablenr != join->const_tables)
3383 else if (create_ref_for_key(join, j, keyuse, used_tables))
3387 for (i=0 ; i < table_count ; i++)
3388 join->
map2table[join->join_tab[i].table->tablenr]=join->join_tab+i;
3409 for (;next != table ; pos++)
3415 join->best_ref[idx]=table;
3438 uint32_t search_depth= join->session->
variables.optimizer_search_depth;
3439 uint32_t prune_level= join->session->
variables.optimizer_prune_level;
3440 bool straight_join= test(join->select_options & SELECT_STRAIGHT_JOIN);
3452 internal::my_qsort(join->best_ref + join->const_tables,
3461 if (search_depth == 0)
3464 if (
greedy_search(join, join_tables, search_depth, prune_level))
3474 if (join->session->lex().is_single_level_stmt())
3475 join->session->status_var.last_query_cost= join->
best_read;
3506 table_map remaining_tables,
3508 double record_count,
3512 uint32_t best_max_key_part= 0;
3513 bool found_constraint= 0;
3514 double best= DBL_MAX;
3515 double best_time= DBL_MAX;
3516 double records= DBL_MAX;
3517 table_map best_ref_depends_map= 0;
3523 Table *table= s->table;
3526 double best_records= DBL_MAX;
3527 uint32_t max_key_part=0;
3531 for (keyuse= s->
keyuse; keyuse->getTable() == table; )
3533 key_part_map found_part= 0;
3534 table_map found_ref= 0;
3535 uint32_t key= keyuse->getKey();
3538 key_part_map const_part= 0;
3540 key_part_map ref_or_null_part= 0;
3547 uint32_t keypart= keyuse->getKeypart();
3548 table_map best_part_found_ref= 0;
3549 double best_prev_record_reads= DBL_MAX;
3558 if (! (remaining_tables & keyuse->getUsedTables()) &&
3559 ! (ref_or_null_part && (keyuse->getOptimizeFlags() &
3560 KEY_OPTIMIZE_REF_OR_NULL)))
3562 found_part|= keyuse->getKeypartMap();
3563 if (! (keyuse->getUsedTables() & ~join->const_table_map))
3564 const_part|= keyuse->getKeypartMap();
3566 double tmp2= prev_record_reads(join, idx, (found_ref |
3567 keyuse->getUsedTables()));
3568 if (tmp2 < best_prev_record_reads)
3570 best_part_found_ref= keyuse->getUsedTables() & ~join->const_table_map;
3571 best_prev_record_reads= tmp2;
3573 if (rec > keyuse->getTableRows())
3574 rec= keyuse->getTableRows();
3579 if (keyuse->getOptimizeFlags() & KEY_OPTIMIZE_REF_OR_NULL)
3580 ref_or_null_part|= keyuse->getKeypartMap();
3584 }
while (keyuse->getTable() == table && keyuse->getKey() == key &&
3585 keyuse->getKeypart() == keypart);
3586 found_ref|= best_part_found_ref;
3587 }
while (keyuse->getTable() == table && keyuse->getKey() == key);
3599 found_constraint= 1;
3604 if (found_part == PREV_BITS(uint,keyinfo->key_parts) &&
3607 max_key_part= UINT32_MAX;
3608 if ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)
3610 tmp = prev_record_reads(join, idx, found_ref);
3634 if (table->quick_keys.test(key))
3635 records= (
double) table->quick_rows[key];
3639 records= (double) s->
records/rec;
3644 if (!(records=keyinfo->rec_per_key[keyinfo->key_parts-1]))
3647 ((double) s->
records / (
double) rec *
3649 ((double) (table->getShare()->max_key_length-keyinfo->key_length) /
3650 (
double) table->getShare()->max_key_length)));
3664 if (table->quick_keys.test(key) &&
3665 const_part & (1 << table->quick_key_parts[key]) &&
3666 table->quick_n_ranges[key] == 1 &&
3667 records > (
double) table->quick_rows[key])
3669 records= (double) table->quick_rows[key];
3674 set_if_smaller(tmp, (
double) session->
variables.max_seeks_for_key);
3675 if (table->covering_keys.test(key))
3681 tmp= record_count * min(tmp,s->worst_seeks);
3691 if ((found_part & 1) &&
3692 (!(table->index_flags(key) & HA_ONLY_WHOLE_INDEX) ||
3693 found_part == PREV_BITS(uint, keyinfo->key_parts)))
3735 if (table->quick_keys.test(key) && !found_ref &&
3736 table->quick_key_parts[key] == max_key_part &&
3737 table->quick_n_ranges[key] == 1+((ref_or_null_part)?1:0))
3739 tmp= records= (double) table->quick_rows[key];
3744 if ((records= keyinfo->rec_per_key[max_key_part-1]))
3762 if (!found_ref && table->quick_keys.test(key) &&
3763 table->quick_key_parts[key] > max_key_part &&
3764 records < (double)table->quick_rows[key])
3765 records= (double)table->quick_rows[key];
3786 if (!(rec_per_key=(
double)
3787 keyinfo->rec_per_key[keyinfo->key_parts-1]))
3788 rec_per_key=(double) s->
records/rec+1;
3792 else if (rec_per_key/(
double) s->
records >= 0.01)
3797 if (keyinfo->key_parts > 1)
3798 tmp= (max_key_part * (rec_per_key - a) +
3799 a*keyinfo->key_parts - rec_per_key)/
3800 (keyinfo->key_parts-1);
3803 set_if_bigger(tmp,1.0);
3805 records = (uint32_t) tmp;
3808 if (ref_or_null_part)
3827 if (table->quick_keys.test(key) &&
3828 table->quick_key_parts[key] <= max_key_part &&
3829 const_part & (1 << table->quick_key_parts[key]) &&
3830 table->quick_n_ranges[key] == 1 + ((ref_or_null_part &
3831 const_part) ? 1 : 0) &&
3832 records > (
double) table->quick_rows[key])
3834 tmp= records= (double) table->quick_rows[key];
3839 set_if_smaller(tmp, (
double) session->
variables.max_seeks_for_key);
3840 if (table->covering_keys.test(key))
3846 tmp= record_count * min(tmp,s->worst_seeks);
3855 best_time= tmp + records/(double) TIME_FOR_COMPARE;
3857 best_records= records;
3858 best_key= start_key;
3859 best_max_key_part= max_key_part;
3860 best_ref_depends_map= found_ref;
3863 records= best_records;
3895 ! (s->quick && best_key && s->quick->
index == best_key->getKey() &&
3896 best_max_key_part >= s->table->quick_key_parts[best_key->getKey()]) &&
3897 ! ((s->table->
cursor->getEngine()->check_flag(HTON_BIT_TABLE_SCAN_ON_INDEX)) &&
3898 ! s->table->covering_keys.none() && best_key && !s->quick) &&
3899 ! (s->table->force_index && best_key && !s->quick))
3911 if (found_constraint)
3912 rnd_records-= rnd_records/4;
3944 tmp= s->table->
cursor->scan_time();
3945 if (s->table->
map & join->outer_join)
3959 tmp*= (1.0 + floor((
double) cache_record_length(join,idx) *
3961 (
double) session->
variables.join_buff_size));
3978 if (best == DBL_MAX ||
3987 records= rnd_records;
3990 best_ref_depends_map= 0;
3999 best_ref_depends_map);
4003 idx == join->const_tables &&
4005 join->
unit->select_limit_cnt >= records)
4037 uint32_t idx= join->const_tables;
4038 double record_count= 1.0;
4039 double read_time= 0.0;
4041 for (
JoinTable **pos= join->best_ref + idx ; (s= *pos) ; pos++)
4045 record_count, read_time);
4048 record_count*= partial_pos.getFanout();
4049 read_time+= partial_pos.getCost();
4050 join_tables&= ~(s->table->
map);
4058 read_time+= record_count;
4144 table_map remaining_tables,
4145 uint32_t search_depth,
4146 uint32_t prune_level)
4148 double record_count= 1.0;
4149 double read_time= 0.0;
4150 uint32_t idx= join->const_tables;
4152 uint32_t size_remain;
4157 size_remain= internal::my_count_bits(remaining_tables);
4163 read_time, search_depth, prune_level))
4166 if (size_remain <= search_depth)
4177 best_table= best_pos.getJoinTable();
4196 JoinTable *pos= join->best_ref[best_idx];
4197 while (pos && best_table != pos)
4198 pos= join->best_ref[++best_idx];
4199 assert((pos != NULL));
4201 std::swap(join->best_ref[idx], join->best_ref[best_idx]);
4205 record_count*= partial_pos.getFanout();
4206 read_time+= partial_pos.getCost();
4208 remaining_tables&= ~(best_table->table->
map);
4332 table_map remaining_tables,
4334 double record_count,
4336 uint32_t search_depth,
4337 uint32_t prune_level)
4339 Session *session= join->session;
4340 if (session->getKilled())
4348 double best_record_count= DBL_MAX;
4349 double best_read_time= DBL_MAX;
4352 for (
JoinTable **pos= join->best_ref + idx ; (s= *pos) ; pos++)
4354 table_map real_table_bit= s->table->
map;
4355 if ((remaining_tables & real_table_bit) &&
4356 ! (remaining_tables & s->dependent) &&
4359 double current_record_count, current_read_time;
4370 record_count, read_time);
4373 current_record_count= record_count * partial_pos.getFanout();
4374 current_read_time= read_time + partial_pos.getCost();
4377 if ((current_read_time +
4388 if (prune_level == 1)
4390 if (best_record_count > current_record_count ||
4391 best_read_time > current_read_time ||
4392 (idx == join->const_tables && s->table == join->
sort_by_table))
4394 if (best_record_count >= current_record_count &&
4395 best_read_time >= current_read_time &&
4397 (! (s->key_dependent & remaining_tables) ||
4400 best_record_count= current_record_count;
4401 best_read_time= current_read_time;
4411 if ( (search_depth > 1) && (remaining_tables & ~real_table_bit) )
4413 std::swap(join->best_ref[idx], *pos);
4415 remaining_tables & ~real_table_bit,
4417 current_record_count,
4422 std::swap(join->best_ref[idx], *pos);
4430 current_read_time+= current_record_count / (double) TIME_FOR_COMPARE;
4434 current_read_time+= current_record_count;
4435 if ((search_depth == 1) || (current_read_time < join->
best_read))
4438 join->
best_read= current_read_time - 0.001;
4480 uint32_t table_count= join->
tables - join->const_tables;
4481 uint32_t search_depth;
4483 uint32_t max_tables_for_exhaustive_opt= 7;
4485 if (table_count <= max_tables_for_exhaustive_opt)
4486 search_depth= table_count+1;
4492 search_depth= max_tables_for_exhaustive_opt;
4494 return search_depth;
4497 static void make_simple_join(Join *join,Table *tmp_table)
4503 if (!join->table_reexec)
4505 join->table_reexec=
new (join->session->mem) Table*;
4507 join->tmp_join->table_reexec= join->table_reexec;
4509 if (!join->join_tab_reexec)
4511 join->join_tab_reexec=
new (join->session->mem) JoinTable;
4513 join->tmp_join->join_tab_reexec= join->join_tab_reexec;
4515 Table** tableptr= join->table_reexec;
4516 JoinTable* join_tab= join->join_tab_reexec;
4518 join->join_tab=join_tab;
4519 join->table=tableptr; tableptr[0]=tmp_table;
4521 join->const_tables=0;
4522 join->const_table_map=0;
4523 join->tmp_table_param.field_count= join->tmp_table_param.sum_func_count=
4524 join->tmp_table_param.func_count=0;
4525 join->tmp_table_param.copy_field=join->tmp_table_param.copy_field_end=0;
4526 join->first_record=join->sort_and_group=0;
4527 join->send_records=(ha_rows) 0;
4529 join->row_limit=join->unit->select_limit_cnt;
4530 join->do_send_rows = (join->row_limit) ? 1 : 0;
4532 join_tab->table=tmp_table;
4534 join_tab->select_cond=0;
4536 join_tab->type= AM_ALL;
4537 join_tab->keys.set();
4539 join_tab->on_expr_ref=0;
4540 join_tab->last_inner= 0;
4541 join_tab->first_unmatched= 0;
4542 join_tab->ref.key = -1;
4543 join_tab->not_used_in_distinct=0;
4544 join_tab->read_first_record= join_init_read_record;
4545 join_tab->join=join;
4546 join_tab->ref.key_parts= 0;
4547 join_tab->read_record.init();
4548 tmp_table->status=0;
4549 tmp_table->null_row=0;
4595 for (uint32_t i=join->const_tables ; i < join->tables ; i++)
4598 Table *table=tab->table;
4599 TableList *tbl= table->pos_in_table_list;
4600 TableList *embedding= tbl->getEmbedding();
4613 tab->
first_upper= embedding->getNestedJoin()->first_nested;
4615 for ( ; embedding ; embedding= embedding->getEmbedding())
4620 NestedJoin *nested_join= embedding->getNestedJoin();
4621 if (!nested_join->counter_)
4627 nested_join->first_nested= tab;
4630 if (embedding->getEmbedding())
4631 tab->
first_upper= embedding->getEmbedding()->getNestedJoin()->first_nested;
4635 if (++nested_join->counter_ < nested_join->join_list.size())
4644 static bool make_join_select(Join *join,
4645 optimizer::SqlSelect *select,
4648 Session *session= join->session;
4649 optimizer::Position cur_pos;
4652 add_not_null_conds(join);
4653 table_map used_tables;
4656 if (join->tables > 1)
4657 cond->update_used_tables();
4658 if (join->const_tables == join->tables &&
4659 session->lex().current_select->master_unit() ==
4660 &session->lex().unit)
4661 join->const_table_map|=RAND_TABLE_BIT;
4664 make_cond_for_table(cond,
4665 join->const_table_map,
4667 for (JoinTable *tab= join->join_tab+join->const_tables;
4668 tab < join->join_tab+join->tables ; tab++)
4670 if (*tab->on_expr_ref)
4672 JoinTable *cond_tab= tab->first_inner;
4673 COND *tmp= make_cond_for_table(*tab->on_expr_ref,
4674 join->const_table_map,
4678 tmp=
new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
4681 tmp->quick_fix_field();
4682 cond_tab->select_cond= !cond_tab->select_cond ? tmp :
4683 new Item_cond_and(cond_tab->select_cond,
4685 if (! cond_tab->select_cond)
4687 cond_tab->select_cond->quick_fix_field();
4690 if (const_cond && ! const_cond->val_int())
4696 used_tables=((select->const_tables=join->const_table_map) |
4697 OUTER_REF_TABLE_BIT | RAND_TABLE_BIT);
4698 for (uint32_t i=join->const_tables ; i < join->tables ; i++)
4700 JoinTable *tab=join->join_tab+i;
4705 JoinTable *first_inner_tab= tab->first_inner;
4706 table_map current_map= tab->table->map;
4707 bool use_quick_range=0;
4714 if (i == join->tables-1)
4715 current_map|= OUTER_REF_TABLE_BIT | RAND_TABLE_BIT;
4716 used_tables|=current_map;
4718 if (tab->type == AM_REF && tab->quick &&
4719 (uint32_t) tab->ref.key == tab->quick->index &&
4720 tab->ref.key_length < tab->quick->max_used_key_length)
4727 tab->ref.key_parts= 0;
4728 cur_pos= join->getPosFromOptimalPlan(i);
4729 cur_pos.setFanout(tab->quick->records);
4734 if (i != join->const_tables && join->tables > join->const_tables + 1)
4738 if (join->full_join and not session->lex().current_select->is_cross and not cond)
4740 my_error(ER_CARTESIAN_JOIN_ATTEMPTED, MYF(0));
4746 tmp= make_cond_for_table(cond,used_tables,current_map, 0);
4747 if (cond && !tmp && tab->quick)
4749 if (tab->type != AM_ALL)
4766 tmp=
new Item_int((int64_t) 1,1);
4770 if (tmp || !cond || tab->type == AM_REF || tab->type == AM_REF_OR_NULL ||
4771 tab->type == AM_EQ_REF)
4773 optimizer::SqlSelect *sel= tab->select= ((optimizer::SqlSelect*)session->mem.memdup(select,
sizeof(*select)));
4786 if (! (tmp= add_found_match_trig_cond(first_inner_tab, tmp, 0)))
4788 tab->select_cond=sel->cond=tmp;
4791 tab->select_cond= sel->cond= NULL;
4793 sel->head=tab->table;
4798 if (tab->needed_reg.none() && tab->type != AM_EQ_REF
4799 && (tab->type != AM_REF || (uint32_t) tab->ref.key == tab->quick->index))
4801 sel->quick=tab->quick;
4802 sel->quick_keys.reset();
4803 sel->needed_reg.reset();
4811 uint32_t ref_key=
static_cast<uint32_t
>(sel->head->reginfo.join_tab->ref.key + 1);
4812 if (i == join->const_tables && ref_key)
4814 if (tab->const_keys.any() &&
4815 tab->table->reginfo.impossible_range)
4818 else if (tab->type == AM_ALL && ! use_quick_range)
4820 if (tab->const_keys.any() &&
4821 tab->table->reginfo.impossible_range)
4830 cur_pos= join->getPosFromOptimalPlan(i);
4831 if ((cond && (! ((tab->keys & tab->const_keys) == tab->keys) && i > 0)) ||
4832 (! tab->const_keys.none() && (i == join->const_tables) &&
4833 (join->unit->select_limit_cnt < cur_pos.getFanout()) && ((join->select_options & OPTION_FOUND_ROWS) ==
false)))
4836 COND *orig_cond= sel->cond;
4837 sel->cond= and_conds(sel->cond, *tab->on_expr_ref);
4846 if (sel->cond && ! sel->cond->fixed)
4847 sel->cond->quick_fix_field();
4849 if (sel->test_quick_select(session, tab->keys,
4850 used_tables & ~ current_map,
4851 (join->select_options &
4854 join->unit->select_limit_cnt), 0,
4861 sel->cond=orig_cond;
4862 if (! *tab->on_expr_ref ||
4863 sel->test_quick_select(session, tab->keys,
4864 used_tables & ~ current_map,
4865 (join->select_options &
4868 join->unit->select_limit_cnt),0,
4873 sel->cond=orig_cond;
4878 cur_pos= join->getPosFromOptimalPlan(i);
4879 cur_pos.setFanout(static_cast<double>(sel->quick->records));
4884 sel->needed_reg= tab->needed_reg;
4885 sel->quick_keys.reset();
4887 if (!((tab->checked_keys & sel->quick_keys) == sel->quick_keys) ||
4888 !((tab->checked_keys & sel->needed_reg) == sel->needed_reg))
4890 tab->keys= sel->quick_keys;
4891 tab->keys|= sel->needed_reg;
4892 tab->use_quick= (!sel->needed_reg.none() &&
4893 (select->quick_keys.none() ||
4895 (select->quick->records >= 100L)))) ?
4897 sel->read_tables= used_tables & ~current_map;
4899 if (i != join->const_tables && tab->use_quick != 2)
4902 (tmp=make_cond_for_table(cond,
4903 join->const_table_map |
4907 tab->cache.select= (optimizer::SqlSelect*)session->mem.memdup(sel,
sizeof(optimizer::SqlSelect));
4908 tab->cache.select->cond= tmp;
4909 tab->cache.select->read_tables= join->const_table_map;
4925 for (JoinTable *join_tab= join->join_tab+join->const_tables;
4926 join_tab < join->join_tab+join->tables ; join_tab++)
4928 if (*join_tab->on_expr_ref)
4930 JoinTable *cond_tab= join_tab->first_inner;
4931 tmp= make_cond_for_table(*join_tab->on_expr_ref,
4932 join->const_table_map,
4936 tmp=
new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
4939 tmp->quick_fix_field();
4940 cond_tab->select_cond= !cond_tab->select_cond ? tmp :
4941 new Item_cond_and(cond_tab->select_cond,tmp);
4942 if (! cond_tab->select_cond)
4944 cond_tab->select_cond->quick_fix_field();
4949 JoinTable *last_tab= tab;
4950 while (first_inner_tab && first_inner_tab->last_inner == last_tab)
4956 COND *on_expr= *first_inner_tab->on_expr_ref;
4958 table_map used_tables2= (join->const_table_map |
4959 OUTER_REF_TABLE_BIT | RAND_TABLE_BIT);
4960 for (tab= join->join_tab+join->const_tables; tab <= last_tab ; tab++)
4962 current_map= tab->table->map;
4963 used_tables2|= current_map;
4964 COND *tmp_cond= make_cond_for_table(on_expr, used_tables2,
4968 JoinTable *cond_tab= tab < first_inner_tab ? first_inner_tab : tab;
4973 if (!(tmp_cond= add_found_match_trig_cond(cond_tab->first_inner,
4981 tmp_cond=
new Item_func_trig_cond(tmp_cond,
4985 tmp_cond->quick_fix_field();
4987 cond_tab->select_cond= !cond_tab->select_cond ? tmp_cond :
4988 new Item_cond_and(cond_tab->select_cond,
4990 if (! cond_tab->select_cond)
4992 cond_tab->select_cond->quick_fix_field();
4995 first_inner_tab= first_inner_tab->first_upper;
5023 static void make_join_readinfo(Join& join)
5027 for (uint32_t i= join.const_tables; i < join.tables; i++)
5029 JoinTable *tab=join.join_tab+i;
5030 Table *table=tab->table;
5031 tab->read_record.table= table;
5032 tab->read_record.cursor= table->cursor;
5038 tab->sorted= sorted;
5041 if (tab->insideout_match_tab)
5043 tab->insideout_buf= join.session->mem.alloc(tab->table->key_info[tab->index].key_length);
5046 optimizer::AccessMethodFactory::create(tab->type)->getStats(*table, *tab);
5049 join.join_tab[join.tables-1].next_select= NULL;
5057 for (; join_tab != end ; join_tab++)
5060 table_map depend_map= 0;
5063 for (i=0 ; i < ref->
key_parts ; i++,item++)
5065 ref->
depend_map=depend_map & ~OUTER_REF_TABLE_BIT;
5066 depend_map&= ~OUTER_REF_TABLE_BIT;
5078 for (; order ; order=order->next)
5080 table_map depend_map;
5081 order->item[0]->update_used_tables();
5082 order->depend_map=depend_map=order->item[0]->
used_tables();
5084 if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT))
5085 && !order->item[0]->with_sum_func)
5090 order->depend_map|=(*tab)->ref.depend_map;
5116 if (join->
tables == join->const_tables)
5117 return change_list ? 0 : first_order;
5119 Order *order,**prev_ptr;
5120 table_map first_table= join->join_tab[join->const_tables].table->
map;
5121 table_map not_const_tables= ~join->const_table_map;
5124 prev_ptr= &first_order;
5125 *simple_order= *join->join_tab[join->const_tables].
on_expr_ref ? 0 : 1;
5130 for (order=first_order; order ; order=order->next)
5132 table_map order_tables=order->item[0]->
used_tables();
5133 if (order->item[0]->with_sum_func)
5135 else if (!(order_tables & not_const_tables))
5143 if (order_tables & (RAND_TABLE_BIT | OUTER_REF_TABLE_BIT))
5152 if ((ref=order_tables & (not_const_tables ^ first_table)))
5154 if (!(order_tables & first_table) &&
5155 only_eq_ref_tables(join,first_order, ref))
5165 prev_ptr= &order->next;
5169 if (prev_ptr == &first_order)
5171 return(first_order);
5174 static void return_zero_rows(Join *join, select_result *result, TableList *tables, List<Item> &fields,
bool send_row, uint64_t select_options,
const char *info, Item *having)
5176 if (select_options & SELECT_DESCRIBE)
5178 optimizer::ExplainPlan planner(join,
false,
false,
false, info);
5179 planner.printPlan();
5187 for (TableList *table= tables; table; table= table->next_leaf)
5188 table->table->mark_as_null_row();
5189 if (having && having->val_int() == 0)
5192 result->send_fields(fields);
5195 List<Item>::iterator it(fields.begin());
5196 while (Item* item= it++)
5197 item->no_rows_in_result();
5198 result->send_data(fields);
5202 join->session->limit_found_rows= join->session->examined_row_count= 0;
5337 table_map used_tables;
5338 table_map not_null_tables= (table_map) 0;
5340 if ((nested_join= table->getNestedJoin()))
5348 Item *expr= table->on_expr;
5357 expr=
simplify_joins(join, &nested_join->join_list, expr,
false);
5359 if (!table->prep_on_expr || expr != table->on_expr)
5363 table->on_expr= expr;
5364 table->prep_on_expr= expr->copy_andor_structure(join->session);
5367 nested_join->used_tables= (table_map) 0;
5368 nested_join->not_null_tables=(table_map) 0;
5369 conds=
simplify_joins(join, &nested_join->join_list, conds, top);
5370 used_tables= nested_join->used_tables;
5371 not_null_tables= nested_join->not_null_tables;
5375 if (!table->prep_on_expr)
5376 table->prep_on_expr= table->on_expr;
5377 used_tables= table->table->map;
5382 if (table->getEmbedding())
5384 table->getEmbedding()->getNestedJoin()->used_tables|= used_tables;
5385 table->getEmbedding()->getNestedJoin()->not_null_tables|= not_null_tables;
5388 if (!table->outer_join || (used_tables & not_null_tables))
5394 table->outer_join= 0;
5400 conds= and_conds(conds, table->on_expr);
5403 assert(!conds->
fixed);
5404 conds->fix_fields(join->session, &conds);
5407 conds= table->on_expr;
5408 table->prep_on_expr= table->on_expr= 0;
5421 table->setDepTables(table->getDepTables() | table->on_expr->used_tables());
5422 if (table->getEmbedding())
5424 table->setDepTables(table->getDepTables() & ~table->getEmbedding()->getNestedJoin()->used_tables);
5429 table->getEmbedding()->setOnExprDepTables(table->getEmbedding()->getOnExprDepTables() & table->on_expr->used_tables());
5432 table->setDepTables(table->getDepTables() & ~table->table->map);
5439 if ((test(join->select_options & SELECT_STRAIGHT_JOIN)) ||
5441 prev_table->setDepTables(prev_table->getDepTables() | used_tables);
5444 prev_table->setDepTables(prev_table->getDepTables() | table->getOnExprDepTables());
5445 table_map prev_used_tables= prev_table->getNestedJoin() ?
5446 prev_table->getNestedJoin()->used_tables :
5455 prev_table->setDepTables(prev_table->getDepTables() | used_tables);
5465 li= join_list->begin();
5468 nested_join= table->getNestedJoin();
5469 if (nested_join && !table->on_expr)
5474 tbl->setEmbedding(table->getEmbedding());
5475 tbl->setJoinList(table->getJoinList());
5477 li.replace(nested_join->join_list);
5483 static int remove_duplicates(Join *join, Table *entry,List<Item> &fields, Item *having)
5485 entry->reginfo.lock_type=TL_WRITE;
5488 uint32_t field_count= 0;
5489 List<Item>::iterator it(fields.begin());
5490 while (Item* item=it++)
5492 if (item->get_tmp_table_field() && ! item->const_item())
5496 if (!field_count && !(join->select_options & OPTION_FOUND_ROWS) && !having)
5498 join->unit->select_limit_cnt= 1;
5501 Field **first_field=entry->getFields() + entry->getShare()->sizeFields() - field_count;
5502 uint32_t offset= field_count ? entry->getField(entry->getShare()->sizeFields() - field_count)->offset(entry->getInsertRecord()) : 0;
5503 uint32_t reclength= entry->getShare()->getRecordLength() - offset;
5505 entry->free_io_cache();
5506 entry->cursor->info(HA_STATUS_VARIABLE);
5508 if (entry->getShare()->db_type() == heap_engine ||
5509 (!entry->getShare()->blob_fields &&
5510 ((ALIGN_SIZE(reclength) + HASH_OVERHEAD) * entry->cursor->stats.records < join->session->variables.sortbuff_size)))
5516 error= remove_dup_with_compare(join->session, entry, first_field, offset, having);
5518 free_blobs(first_field);
5526 Item **ref_pointer_array,
5534 bool *hidden_group_fields)
5537 nesting_map save_allow_sum_func=session->lex().allow_sum_func;
5539 session->lex().allow_sum_func&= ~(1 << session->lex().current_select->nest_level);
5540 res= session->setup_conds(tables, conds);
5542 session->lex().allow_sum_func|= 1 << session->lex().current_select->nest_level;
5543 res= res ||
setup_order(session, ref_pointer_array, tables, fields, all_fields, order);
5544 session->lex().allow_sum_func&= ~(1 << session->lex().current_select->nest_level);
5545 res= res ||
setup_group(session, ref_pointer_array, tables, fields, all_fields, group, hidden_group_fields);
5546 session->lex().allow_sum_func= save_allow_sum_func;
5563 uint32_t table_count;
5564 uint32_t const_count;
5566 table_map found_const_table_map;
5567 table_map all_table_map;
5568 table_map found_ref;
5572 Table **table_vector= NULL;
5579 table_map outer_join= 0;
5580 vector<optimizer::SargableParam> sargables;
5584 table_count= join->
tables;
5586 stat_ref=
new (join->session->mem)
JoinTable*[MAX_TABLES];
5587 table_vector=
new (join->session->mem)
Table*[2 * table_count];
5589 join->best_ref=stat_vector;
5591 stat_end=stat+table_count;
5592 found_const_table_map= all_table_map=0;
5599 TableList *embedding= tables->getEmbedding();
5604 s->needed_reg.reset();
5605 table_vector[i]=s->table=table=tables->table;
5606 table->pos_in_table_list= tables;
5608 error= table->
cursor->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
5611 table->print_error(error, MYF(0));
5614 table->quick_keys.reset();
5615 table->reginfo.join_tab=s;
5616 table->reginfo.not_exists_optimize=0;
5617 memset(table->const_key_parts, 0,
sizeof(key_part_map)*table->getShare()->sizeKeys());
5618 all_table_map|= table->
map;
5622 s->dependent= tables->getDepTables();
5623 s->key_dependent= 0;
5630 if (!table->
cursor->stats.records && !embedding)
5636 outer_join|= table->
map;
5638 for (;embedding; embedding= embedding->getEmbedding())
5642 if (embedding && !(
false && ! embedding->getEmbedding()))
5648 NestedJoin *nested_join= embedding->getNestedJoin();
5650 s->dependent|= embedding->getDepTables();
5651 embedding= embedding->getEmbedding();
5652 outer_join|= nested_join->used_tables;
5657 if ((table->
cursor->stats.records <= 1) && !s->dependent &&
5658 (table->
cursor->getEngine()->check_flag(HTON_BIT_STATS_RECORDS_IS_EXACT)) &&
5665 join->outer_join=outer_join;
5667 if (join->outer_join)
5677 for (i= 0; i < table_count; i++)
5680 table= stat[i].table;
5682 if (!table->reginfo.join_tab->dependent)
5685 for (j= 0, s= stat; j < table_count; j++, s++)
5687 if (s->dependent & table->
map)
5689 table_map was_dependent= s->dependent;
5690 s->dependent |= table->reginfo.join_tab->dependent;
5691 if (i > j && s->dependent != was_dependent)
5700 for (i= 0, s= stat ; i < table_count ; i++, s++)
5702 if (s->dependent & s->table->
map)
5705 my_message(ER_WRONG_OUTER_JOIN, ER(ER_WRONG_OUTER_JOIN), MYF(0));
5708 if (outer_join & s->table->
map)
5709 s->table->maybe_null= 1;
5711 s->key_dependent= s->dependent;
5715 if (conds || outer_join)
5719 join->const_table_map= 0;
5723 while (p_pos < p_end)
5726 s= p_pos->getJoinTable();
5728 join->const_table_map|=s->table->
map;
5729 if ((tmp= s->joinReadConstTable(p_pos)))
5735 found_const_table_map|= s->table->
map;
5743 more_const_tables_found:
5752 for (
JoinTable **pos= stat_vector+const_count; (s= *pos); pos++)
5773 while (keyuse->getTable() == table)
5775 if (! (keyuse->getVal()->
used_tables() & ~join->const_table_map) &&
5776 keyuse->getVal()->
is_null() && keyuse->isNullRejected())
5779 table->mark_as_null_row();
5780 found_const_table_map|= table->
map;
5781 join->const_table_map|= table->
map;
5783 goto more_const_tables_found;
5792 if (s->dependent & ~(found_const_table_map))
5794 if (table->
cursor->stats.records <= 1L &&
5795 (table->
cursor->getEngine()->check_flag(HTON_BIT_STATS_RECORDS_IS_EXACT)) &&
5796 !table->pos_in_table_list->getEmbedding())
5800 join->const_table_map|=table->
map;
5803 if ((tmp= s->joinReadConstTable(partial_pos)))
5809 found_const_table_map|= table->
map;
5817 while (keyuse->getTable() == table)
5819 start_keyuse= keyuse;
5820 key= keyuse->getKey();
5828 if (keyuse->getVal()->type() != Item::NULL_ITEM &&
5829 ! keyuse->getOptimizeFlags())
5831 if (! ((~found_const_table_map) & keyuse->getUsedTables()))
5832 const_ref.set(keyuse->getKeypart());
5834 refs|= keyuse->getUsedTables();
5835 eq_part.set(keyuse->getKeypart());
5838 }
while (keyuse->getTable() == table && keyuse->getKey() == key);
5840 if (is_keymap_prefix(eq_part, table->
key_info[key].key_parts) &&
5841 ! table->pos_in_table_list->getEmbedding())
5843 if ((table->
key_info[key].flags & (HA_NOSAME)) == HA_NOSAME)
5845 if (const_ref == eq_part)
5850 join->const_table_map|= table->
map;
5852 if (create_ref_for_key(join, s, start_keyuse, found_const_table_map))
5855 if ((tmp=s->joinReadConstTable(partial_pos)))
5861 found_const_table_map|= table->
map;
5867 else if (const_ref == eq_part)
5873 }
while (join->const_table_map & found_ref && ref_changed);
5879 if (const_count && ! sargables.empty())
5881 BOOST_FOREACH(vector<optimizer::SargableParam>::reference iter, sargables)
5883 Field& field= *iter.getField();
5884 JoinTable *join_tab= field.getTable()->reginfo.join_tab;
5885 key_map possible_keys= field.
key_start & field.getTable()->keys_in_use_for_query;
5886 bool is_const=
true;
5887 for (uint32_t j= 0; j < iter.getNumValues(); j++)
5888 is_const&= iter.isConstItem(j);
5896 for (s=stat ; s < stat_end ; s++)
5898 if (s->
type == AM_SYSTEM || s->
type == AM_CONST)
5915 if (s->worst_seeks < 2.0)
5925 !s->table->pos_in_table_list->getEmbedding())
5929 select= optimizer::make_select(s->table, found_const_table_map, found_const_table_map, *s->
on_expr_ref ? *s->
on_expr_ref : conds, 1, &error);
5932 records= get_quick_record_count(join->session, select, s->table, &s->
const_keys, join->row_limit);
5933 s->quick=select->
quick;
5937 if (records == 0 && s->table->reginfo.impossible_range)
5945 join->const_table_map|= s->table->
map;
5951 s->info=
"Impossible ON condition";
5952 found_const_table_map|= s->table->
map;
5954 s->table->mark_as_null_row();
5957 if (records != HA_POS_ERROR)
5966 join->join_tab=stat;
5968 join->table= join->all_tables=table_vector;
5969 join->const_tables=const_count;
5970 join->found_const_table_map=found_const_table_map;
5973 if (join->const_tables != join->
tables)
5978 DRIZZLE_QUERY_OPT_CHOOSE_PLAN_START(join->session->getQueryString()->c_str(), join->session->thread_id);
5979 bool res=
choose_plan(join, all_table_map & ~join->const_table_map);
5980 DRIZZLE_QUERY_OPT_CHOOSE_PLAN_DONE(res ? 1 : 0);
6017 if (
NestedJoin* nested_join= table->getNestedJoin())
6030 if (nested_join->join_list.size() != 1)
6034 nested_join->nj_map.set(first_unused++);
6040 return(first_unused);
6057 for (; a && b; a=a->next,b=b->next)
6059 if (!(*a->item)->eq(*b->item,1))
6063 if (!map || (map & (RAND_TABLE_BIT | OUTER_REF_TABLE_BIT)))
6069 return tables->
table;
6087 if ((nested_join= table->getNestedJoin()))
6089 nested_join->counter_= 0;
6103 for (; a && b; a=a->next,b=b->next)
6105 if ((*a->item)->eq(*b->item,1))
6167 TableList *last_emb= last->table->pos_in_table_list->getEmbedding();
6168 Join *join= last->join;
6169 for (;last_emb != NULL; last_emb= last_emb->getEmbedding())
6173 bool was_fully_covered= nest->is_fully_covered();
6175 if (--nest->counter_ == 0)
6178 if (!was_fully_covered)
6195 Table *table=join_tab->table;
6197 for (uint32_t i=0 ; i < join_tab->ref.
key_parts ; i++)
6199 Field *field=table->getField(table->
key_info[join_tab->ref.
key].key_part[i].fieldnr - 1);
6207 cond->fix_fields(session, (
Item**)&cond);
6209 if (join_tab->select)
6211 cond->add(join_tab->select->
cond);
6212 join_tab->select_cond=join_tab->select->
cond=cond;
6214 else if ((join_tab->select= optimizer::make_select(join_tab->table, 0, 0, cond, 0, &error)))
6215 join_tab->select_cond=cond;
6220 static void free_blobs(Field **ptr)
6222 for (; *ptr ; ptr++)
6224 if ((*ptr)->flags & BLOB_FLAG)
6225 ((Field_blob *) (*ptr))->free();