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>
72#include <drizzled/key_part_info.h>
84static uint32_t cache_record_length(
Join *join, uint32_t index);
85static double prev_record_reads(
Join *join, uint32_t idx, table_map found_ref);
94 table_map remaining_tables,
99static bool greedy_search(
Join *join, table_map remaining_tables, uint32_t depth, uint32_t prune_level);
101 table_map remaining_tables,
106 uint32_t prune_level);
108static void make_simple_join(
Join*,
Table*);
111static void make_join_readinfo(
Join&);
115static void return_zero_rows(
Join *join,
120 uint64_t select_options,
126 Item **ref_pointer_array,
134 bool *hidden_group_fields);
142static void free_blobs(
Field **ptr);
146 uint64_t select_options_arg,
159 sort_and_group(false),
163 no_field_update(false),
176 hidden_group_fields(false),
178 found_const_table_map(0),
186 session(session_arg),
193 exec_tmp_table2(NULL),
199 select_options(select_options_arg),
201 lock(session_arg->open_tables.lock),
216 join_tab_reexec(NULL)
222 tmp_table_param.init();
223 tmp_table_param.end_write_records= HA_POS_ERROR;
224 rollup.setState(Rollup::STATE_NONE);
235 uint64_t select_options_arg,
249 sort_and_group=
false;
253 no_field_update=
false;
266 hidden_group_fields=
false;
268 found_const_table_map= 0;
276 session= session_arg;
283 exec_tmp_table2= NULL;
289 select_options= select_options_arg;
291 lock= session_arg->open_tables.lock;
306 join_tab_reexec= NULL;
311 tmp_table_param.init();
312 tmp_table_param.end_write_records= HA_POS_ERROR;
313 rollup.setState(Rollup::STATE_NONE);
316bool Join::is_top_level_join()
const
318 return unit == &session->lex().unit && (
unit->fake_select_lex == 0 ||
select_lex ==
unit->fake_select_lex);
341 Select_Lex *select_lex_arg,
342 Select_Lex_Unit *unit_arg)
358 session->lex().current_select->is_item_list_lookup= 1;
363 if (session->derived_tables_processing)
364 select_lex->exclude_from_table_unique_test=
true;
368 if (!(select_options & OPTION_SETUP_TABLES_DONE) &&
393 nesting_map save_allow_sum_func= session->lex().allow_sum_func;
394 session->setWhere(
"having clause");
395 session->lex().allow_sum_func|= 1 << select_lex_arg->nest_level;
397 bool having_fix_rc= (!having->fixed &&
398 (having->fix_fields(session, &having) ||
399 having->check_cols(1)));
401 if (having_fix_rc || session->is_error())
403 session->lex().allow_sum_func= save_allow_sum_func;
413 if ((subselect=
select_lex->master_unit()->item))
415 if (subselect->substype() == Item_subselect::IN_SUBS)
419 bool do_materialize=
true;
449 if (do_materialize &&
451 !
select_lex->master_unit()->first_select()->next_select() &&
452 select_lex->master_unit()->first_select()->leaf_tables &&
453 session->lex().sql_command == SQLCOM_SELECT)
455 if (in_subs->is_top_level_item() &&
456 !in_subs->is_correlated &&
457 in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED)
458 in_subs->exec_method= Item_in_subselect::MATERIALIZATION;
461 Item_subselect::trans_res trans_res;
462 if ((trans_res= subselect->select_transformer(
this)) !=
463 Item_subselect::RES_OK)
465 return((trans_res == Item_subselect::RES_ERROR));
474 for (ord= order; ord; ord= ord->next)
476 Item *item= *ord->item;
477 if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM)
482 if (having && having->with_sum_func)
491 item_sum= item_sum->next;
494 }
while (item_sum != end);
508 select_lex->full_group_by_flag.test(NON_AGG_FIELD_USED) &&
509 select_lex->full_group_by_flag.test(SUM_FUNC_USED))
511 my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
512 ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
518 for (
Order *group_tmp=
group_list ; group_tmp ; group_tmp= group_tmp->next)
531 if (result && result->prepare(
fields_list, unit_arg))
540#ifdef RESTRICTED_GROUP
541 if (sum_func_count && !
group_list && (func_count || field_count))
543 my_message(ER_WRONG_SUM_SELECT,ER(ER_WRONG_SUM_SELECT),MYF(0));
585void Join::remove_subq_pushed_predicates(
Item **where)
587 if (conds->type() == Item::FUNC_ITEM &&
588 ((
Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
589 ((
Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM &&
590 ((
Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM &&
617 session->set_proc_info(
"optimizing");
619 unit->select_limit_cnt);
621 select_limit=
unit->select_limit_cnt;
622 if (having || (select_options & OPTION_FOUND_ROWS))
623 select_limit= HA_POS_ERROR;
624 do_send_rows = (
unit->select_limit_cnt) ? 1 : 0;
626 if (session->lex().ignore)
627 session->lex().current_select->no_error= 1;
629#ifdef HAVE_REF_TO_FIELDS
646 conds->fix_fields(session, &conds);
648 conds->top_level_item();
658 conds= optimize_cond(
this, conds,
join_list, &cond_value);
659 if (session->is_error())
666 having= optimize_cond(
this, having,
join_list, &having_value);
667 if (session->is_error())
677 if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE ||
678 (!
unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS)))
681 "Impossible HAVING" :
"Impossible WHERE";
683 goto setup_subq_exit;
699 if (res == HA_ERR_KEY_NOT_FOUND)
703 goto setup_subq_exit;
714 goto setup_subq_exit;
729 if (conds && !(session->lex().describe & DESCRIBE_EXTENDED))
731 COND *table_independent_conds= make_cond_for_table(conds, PSEUDO_TABLE_BITS, 0, 0);
732 conds= table_independent_conds;
734 goto setup_subq_exit;
746 session->set_proc_info(
"statistics");
748 session->is_fatal_error)
755 session->set_proc_info(
"preparing");
756 if (result->initialize_tables(
this))
760 if (const_table_map != found_const_table_map &&
761 !(select_options & SELECT_DESCRIBE) &&
763 !(conds->used_tables() & RAND_TABLE_BIT) ||
764 select_lex->master_unit() == &session->lex().unit))
767 goto setup_subq_exit;
769 if (!(session->options & OPTION_BIG_SELECTS) &&
770 best_read > (
double) session->variables.max_join_size &&
771 !(select_options & SELECT_DESCRIBE))
773 my_message(ER_TOO_BIG_SELECT, ER(ER_TOO_BIG_SELECT), MYF(0));
777 if (const_tables && !(select_options & SELECT_NO_UNLOCK))
778 session->unlockSomeTables(table, const_tables);
779 if (!conds && outer_join)
784 select= optimizer::make_select(*table, const_table_map,
785 const_table_map, conds, 1, &error);
804 conds->update_used_tables();
811 for (
JoinTable *tab= join_tab + const_tables; tab < join_tab +
tables ; tab++)
813 if (*tab->on_expr_ref)
818 (*tab->on_expr_ref)->update_used_tables();
822 if (conds &&!outer_join && const_table_map != found_const_table_map &&
823 (select_options & SELECT_DESCRIBE) &&
824 select_lex->master_unit() == &session->lex().unit)
829 if (make_join_select(
this,
select, conds))
832 "Impossible WHERE noticed after reading const tables";
833 goto setup_subq_exit;
840 Order *org_order= order;
842 if (session->is_error())
852 if (!order && org_order)
868 ! tmp_table_param.sum_func_count &&
869 (! join_tab[const_tables].select ||
870 ! join_tab[const_tables].select->quick ||
871 join_tab[const_tables].select->quick->get_type() !=
872 optimizer::QuickSelectInterface::QS_TYPE_GROUP_MIN_MAX))
897 join_tab->table->keys_in_use_for_order_by=
898 join_tab->table->keys_in_use_for_group_by;
910 if (
group_list || tmp_table_param.sum_func_count)
912 if (! hidden_group_fields &&
rollup.getState() == Rollup::STATE_NONE)
933 bool all_order_fields_used;
936 &tab->table->keys_in_use_for_order_by);
939 &all_order_fields_used)))
943 &tab->table->keys_in_use_for_group_by) != 0);
945 if ((skip_group && all_order_fields_used) ||
946 select_limit == HA_POS_ERROR ||
952 if (all_order_fields_used)
960 tmp_table_param.quick_group=0;
969 else if (session->is_fatal_error)
974 Order *old_group_list;
976 rollup.getState() == Rollup::STATE_NONE,
978 if (session->is_error())
995 send_group_parts= tmp_table_param.group_parts;
998 (!
group_list && tmp_table_param.sum_func_count))
1019 need_tmp= (const_tables !=
tables &&
1022 test(select_options & OPTION_BUFFER_RESULT)));
1025 make_join_readinfo(*
this);
1038 unit->item &&
unit->item->substype() == Item_subselect::IN_SUBS &&
1047 remove_subq_pushed_predicates(&where);
1048 save_index_subquery_explain_info(join_tab, where);
1049 join_tab[0].type= AM_UNIQUE_SUBQUERY;
1053 else if (join_tab[0].
type == AM_REF &&
1056 remove_subq_pushed_predicates(&where);
1057 save_index_subquery_explain_info(join_tab, where);
1058 join_tab[0].type= AM_INDEX_SUBQUERY;
1063 else if (join_tab[0].
type == AM_REF_OR_NULL &&
1065 having->name == in_having_cond)
1067 join_tab[0].type= AM_INDEX_SUBQUERY;
1070 save_index_subquery_explain_info(join_tab, conds);
1083 for (uint32_t i = const_tables; i <
tables; i++)
1084 join_tab[i].table->prepare_for_position();
1087 if (const_tables !=
tables)
1096 (join_tab[const_tables].
type != AM_ALL) &&
1097 (join_tab[const_tables].
type != AM_REF_OR_NULL) &&
1098 ((order && simple_order) || (
group_list && simple_group)))
1105 if (!(select_options & SELECT_BIG_RESULT) &&
1109 unit->select_limit_cnt, 0,
1110 &join_tab[const_tables].table->
1111 keys_in_use_for_group_by))) ||
1113 tmp_table_param.quick_group)
1115 need_tmp=1; simple_order=simple_group=0;
1123 for (
Order *tmp_order= order; tmp_order ; tmp_order=tmp_order->next)
1125 Item *item= *tmp_order->item;
1126 if (item->is_expensive())
1129 need_tmp=1; simple_order=simple_group=0;
1137 if (select_options & SELECT_DESCRIBE)
1155 if (join_tab->is_using_loose_index_scan())
1156 tmp_table_param.precomputed_group_by=
true;
1161 session->set_proc_info(
"Creating tmp table");
1163 init_items_ref_array();
1166 Order *tmp_group= (((not simple_group) or not (getDebug().test(debug::NO_KEY_GROUP))) ?
group_list : NULL);
1176 !session->lex().current_select->with_sum_func) ?
1177 select_limit : HA_POS_ERROR;
1207 session->set_proc_info(
"Sorting for group");
1208 if (create_sort_index(session,
this,
group_list,
1209 HA_POS_ERROR, HA_POS_ERROR,
false) ||
1228 session->set_proc_info(
"Sorting for order");
1229 if (create_sort_index(session,
this, order, HA_POS_ERROR, HA_POS_ERROR,
true))
1245 table_map used_tables= session->used_tables;
1249 if (used_tables & last_join_tab->table->
map)
1251 last_join_tab->not_used_in_distinct=1;
1252 }
while (last_join_tab-- != join_tab);
1258 order,
unit->select_limit_cnt, 0,
1259 &join_tab[const_tables].table->
1260 keys_in_use_for_order_by))
1269 if (
select_lex->uncacheable.any() && not is_top_level_join())
1291 memcpy(
static_cast<void *
>(
tmp_join),
static_cast<const void *
>(
this),
1310 if (exec_tmp_table2)
1312 exec_tmp_table2->
cursor->extra(HA_EXTRA_RESET_STATE);
1314 exec_tmp_table2->free_io_cache();
1315 exec_tmp_table2->filesort_free_buffers();
1318 set_items_ref_array(
items0);
1321 memcpy(
static_cast<void *
>(join_tab),
1323 sizeof(JoinTable) *
tables);
1331 Item_sum *func, **func_ptr= sum_funcs;
1332 while ((func= *(func_ptr++)))
1357void Join::save_join_tab()
1381 session->set_proc_info(
"executing");
1387 if (select_options & SELECT_DESCRIBE)
1394 planner.printPlan();
1398 result->send_fields(*columns_list);
1404 if (cond_value != Item::COND_FALSE &&
1405 (!conds || conds->val_int()) &&
1406 (!having || having->val_int()))
1408 if (do_send_rows && result->send_data(
fields_list))
1412 error= (int) result->send_eof();
1413 send_records= ((select_options & OPTION_FOUND_ROWS) ? 1 : session->sent_row_count);
1418 error= (int) result->send_eof();
1423 session->limit_found_rows= send_records;
1424 session->examined_row_count= 0;
1433 session->limit_found_rows= 0;
1437 return_zero_rows(
this, result,
select_lex->leaf_tables, *columns_list, send_row_on_empty_set(), select_options,
zero_result_cause, having);
1441 if (select_options & SELECT_DESCRIBE)
1452 simple_order= simple_group;
1455 if (order && (order !=
group_list || !(select_options & SELECT_BIG_RESULT)))
1457 if (const_tables ==
tables
1459 &&
test_if_skip_sort_order(&join_tab[const_tables], order, select_limit, 0, &join_tab[const_tables].table->keys_in_use_for_query)))
1467 !
tables ?
"No tables used" : NULL);
1468 planner.printPlan();
1472 Join *curr_join=
this;
1475 Table *curr_tmp_table= 0;
1481 curr_join->examined_rows= 0;
1499 session->set_proc_info(
"Copying to tmp table");
1500 if (! curr_join->sort_and_group && curr_join->const_tables != curr_join->
tables)
1501 curr_join->join_tab[curr_join->const_tables].
sorted= 0;
1502 if ((tmp_error=
do_select(curr_join, NULL, curr_tmp_table)))
1507 curr_tmp_table->
cursor->info(HA_STATUS_VARIABLE);
1509 if (curr_join->having)
1517 if (sort_and_group || curr_tmp_table->group)
1533 curr_join->items1= items1;
1537 curr_join->set_items_ref_array(items1);
1539 if (sort_and_group || curr_tmp_table->group)
1541 curr_join->tmp_table_param.field_count+= curr_join->tmp_table_param.sum_func_count
1542 + curr_join->tmp_table_param.func_count;
1543 curr_join->tmp_table_param.sum_func_count= 0;
1544 curr_join->tmp_table_param.func_count= 0;
1548 curr_join->tmp_table_param.field_count+= curr_join->tmp_table_param.func_count;
1549 curr_join->tmp_table_param.func_count= 0;
1552 if (curr_tmp_table->group)
1568 || (curr_join->
select_distinct && curr_join->tmp_table_param.using_indirect_summary_function))
1572 make_simple_join(curr_join, curr_tmp_table);
1577 curr_join->tmp_table_param.hidden_field_count= curr_join->
tmp_all_fields1.size()
1580 if (exec_tmp_table2)
1582 curr_tmp_table= exec_tmp_table2;
1593 if (curr_join->join_tab->is_using_loose_index_scan())
1594 curr_join->tmp_table_param.precomputed_group_by=
true;
1596 if (!(curr_tmp_table=
1598 &curr_join->tmp_table_param,
1603 1, curr_join->select_options,
1610 curr_join->exec_tmp_table2= exec_tmp_table2;
1614 session->set_proc_info(
"Creating sort index");
1615 if (curr_join->join_tab == join_tab)
1617 if (create_sort_index(session, curr_join, curr_join->
group_list, HA_POS_ERROR, HA_POS_ERROR,
false) ||
1622 sortorder= curr_join->sortorder;
1625 session->set_proc_info(
"Copying to group table");
1627 if (curr_join !=
this)
1632 curr_join->sum_funcs_end= sum_funcs_end2;
1638 sum_funcs_end2= curr_join->sum_funcs_end;
1645 if (!curr_join->sort_and_group && (curr_join->const_tables != curr_join->
tables))
1646 curr_join->join_tab[curr_join->const_tables].
sorted= 0;
1649 || (tmp_error=
do_select(curr_join, NULL, curr_tmp_table)))
1654 curr_join->join_tab->read_record.end_read_record();
1655 curr_join->const_tables= curr_join->
tables;
1656 curr_join->join_tab[0].table= 0;
1663 tmp_fields_list2, tmp_all_fields2,
1666 curr_join->tmp_fields_list2= tmp_fields_list2;
1667 curr_join->tmp_all_fields2= tmp_all_fields2;
1669 curr_fields_list= &curr_join->tmp_fields_list2;
1670 curr_all_fields= &curr_join->tmp_all_fields2;
1671 curr_join->set_items_ref_array(items2);
1672 curr_join->tmp_table_param.field_count+= curr_join->tmp_table_param.sum_func_count;
1673 curr_join->tmp_table_param.sum_func_count= 0;
1675 if (curr_tmp_table->distinct)
1681 session->set_proc_info(
"Removing duplicates");
1685 if (remove_duplicates(curr_join, curr_tmp_table,
1692 curr_tmp_table->reginfo.lock_type= TL_UNLOCK;
1693 make_simple_join(curr_join, curr_tmp_table);
1699 if (curr_join->group || curr_join->tmp_table_param.sum_func_count)
1707 init_items_ref_array();
1710 items3, tmp_fields_list3, tmp_all_fields3,
1711 curr_fields_list->size(), *curr_all_fields);
1712 tmp_table_param.save_copy_funcs= curr_join->tmp_table_param.copy_funcs;
1713 tmp_table_param.save_copy_field= curr_join->tmp_table_param.copy_field;
1714 tmp_table_param.save_copy_field_end= curr_join->tmp_table_param.copy_field_end;
1715 curr_join->tmp_all_fields3= tmp_all_fields3;
1716 curr_join->tmp_fields_list3= tmp_fields_list3;
1720 curr_join->tmp_table_param.copy_funcs= tmp_table_param.save_copy_funcs;
1721 curr_join->tmp_table_param.copy_field= tmp_table_param.save_copy_field;
1722 curr_join->tmp_table_param.copy_field_end= tmp_table_param.save_copy_field_end;
1724 curr_fields_list= &tmp_fields_list3;
1725 curr_all_fields= &tmp_all_fields3;
1726 curr_join->set_items_ref_array(items3);
1731 session->is_fatal_error)
1734 if (curr_join->
group_list || curr_join->order)
1736 session->set_proc_info(
"Sorting result");
1742 JoinTable *curr_table= &curr_join->join_tab[curr_join->const_tables];
1743 table_map used_tables= (curr_join->const_table_map |
1744 curr_table->table->
map);
1746 Item* sort_table_cond= make_cond_for_table(curr_join->
tmp_having, used_tables, used_tables, 0);
1747 if (sort_table_cond)
1749 if (!curr_table->select)
1751 if (!curr_table->select->
cond)
1752 curr_table->select->
cond= sort_table_cond;
1762 curr_table->select_cond= curr_table->select->
cond;
1764 curr_join->
tmp_having= make_cond_for_table(curr_join->
tmp_having, ~ (table_map) 0, ~used_tables, 0);
1769 curr_join->select_limit= HA_POS_ERROR;
1776 JoinTable *curr_table= &curr_join->join_tab[curr_join->const_tables+1];
1778 for (; curr_table < end_table ; curr_table++)
1784 if (curr_table->select_cond ||
1788 curr_join->select_limit= HA_POS_ERROR;
1793 if (curr_join->join_tab == join_tab)
1804 if (create_sort_index(session, curr_join,
1807 curr_join->select_limit,
1808 (select_options & OPTION_FOUND_ROWS ?
1809 HA_POS_ERROR :
unit->select_limit_cnt),
1813 sortorder= curr_join->sortorder;
1814 if (curr_join->const_tables != curr_join->
tables &&
1815 !curr_join->join_tab[curr_join->const_tables].table->sort.io_cache)
1827 if (session->is_error())
1829 error= session->is_error();
1833 curr_join->fields= curr_fields_list;
1835 session->set_proc_info(
"Sending data");
1836 result->send_fields(*curr_fields_list);
1837 error=
do_select(curr_join, curr_fields_list, NULL);
1838 session->limit_found_rows= curr_join->send_records;
1841 session->examined_row_count+= curr_join->examined_rows;
1848 if (
items0 && (session->lex().describe & DESCRIBE_EXTENDED) &&
select_lex->linkage == DERIVED_TABLE_TYPE)
1849 set_items_ref_array(
items0);
1866 if (join_tab !=
tmp_join->join_tab)
1869 for (tab= join_tab, end= tab+
tables ; tab != end ; tab++)
1873 tmp_table_param.copy_field=0;
1880 exec_tmp_table2= NULL;
1910 for (Select_Lex_Unit *un=
select_lex->first_inner_unit(); un;
1911 un= un->next_unit())
1913 for (Select_Lex *sl= un->first_select(); sl; sl= sl->next_select())
1916 if (subquery_predicate &&
1917 subquery_predicate->substype() == Item_subselect::IN_SUBS)
1920 if (in_subs->exec_method == Item_in_subselect::MATERIALIZATION &&
1973 Select_Lex_Unit *tmp_unit;
1979 bool full= (
select_lex->uncacheable.none() && ! session->lex().describe);
1980 bool can_unlock= full;
1984 for (tmp_unit=
select_lex->first_inner_unit();
1986 tmp_unit= tmp_unit->next_unit())
1987 for (sl= tmp_unit->first_select(); sl; sl= sl->next_select())
1990 bool full_local= full && (!subselect ||
1991 (subselect->is_evaluated() &&
1992 !subselect->is_uncacheable()));
2002 sl->cleanup_all_joins(full_local);
2004 can_unlock= can_unlock && full_local;
2011 if (can_unlock && lock && session->open_tables.lock &&
2012 !(select_options & SELECT_NO_UNLOCK) &&
2014 (
select_lex == (session->lex().unit.fake_select_lex ?
2015 session->lex().unit.fake_select_lex : &session->lex().select_lex)))
2021 session->unlockReadTables(lock);
2048 if (
tables > const_tables)
2050 table[const_tables]->free_io_cache();
2051 table[const_tables]->filesort_free_buffers(full);
2061 for (tab= join_tab, end= tab+
tables; tab != end; tab++)
2067 for (tab= join_tab, end= tab+
tables; tab != end; tab++)
2070 tab->table->
cursor->ha_index_or_rnd_end();
2082 tmp_table_param.copy_field= 0;
2083 group_fields.delete_elements();
2088 tmp_table_param.copy_funcs.clear();
2097 tmp_join->tmp_table_param.copy_field ==
2098 tmp_table_param.copy_field)
2100 tmp_join->tmp_table_param.copy_field=
2101 tmp_join->tmp_table_param.save_copy_field= 0;
2103 tmp_table_param.cleanup();
2111static void clear_tables(
Join *join)
2117 for (uint32_t i= join->const_tables; i < join->tables; i++)
2119 join->table[i]->mark_as_null_row();
2134 uint32_t func_count, group_parts;
2136 func_count= tmp_table_param.sum_func_count;
2141 if (
rollup.getState() != Rollup::STATE_NONE)
2142 func_count*= (send_group_parts+1);
2144 group_parts= send_group_parts;
2159 for (ord= order; ord; ord= ord->next)
2165 sum_funcs= (
Item_sum**) session->mem.calloc(
sizeof(
Item_sum**) * (func_count+1) +
2166 sizeof(
Item_sum***) * (group_parts+1));
2167 sum_funcs_end= (
Item_sum***) (sum_funcs+func_count+1);
2168 return(sum_funcs == 0);
2186 bool before_group_by,
2189 List<Item>::iterator it(field_list.begin());
2193 if (*sum_funcs && !recompute)
2199 if (item->type() == Item::SUM_FUNC_ITEM && !item->const_item() &&
2200 (!((
Item_sum*) item)->depended_from() ||
2204 if (before_group_by &&
rollup.getState() == Rollup::STATE_INITED)
2206 rollup.setState(Rollup::STATE_READY);
2210 else if (
rollup.getState() == Rollup::STATE_NONE)
2212 for (uint32_t i=0 ; i <= send_group_parts ;i++)
2213 sum_funcs_end[i]= func;
2215 else if (
rollup.getState() == Rollup::STATE_READY)
2225 tmp_table_param.quick_group= 0;
2226 rollup.setState(Rollup::STATE_INITED);
2232 tmp_table_param.group_parts= send_group_parts;
2236 rollup.setRefPointerArrays((
Item***) (
rollup.getFields() + send_group_parts));
2237 Item** ref_array= (
Item**) (
rollup.getRefPointerArrays()+send_group_parts);
2243 for (uint32_t i= 0 ; i < send_group_parts ; i++)
2247 rollup_fields->clear();
2248 rollup.getRefPointerArrays()[i]= ref_array;
2252 for (uint32_t i= 0 ; i < send_group_parts; i++)
2254 for (uint32_t j= 0 ; j <
fields_list.size() ; j++)
2256 rollup.getFields()[i].push_back(
rollup.getNullItems()[i]);
2261 while (
Item* item= it++)
2264 bool found_in_group= 0;
2266 for (group_tmp=
group_list; group_tmp; group_tmp= group_tmp->next)
2268 if (*group_tmp->item == item)
2270 item->maybe_null= 1;
2272 if (item->const_item())
2288 new_item->fix_fields(session, NULL);
2289 *it.ref()= new_item;
2290 for (
Order *tmp= group_tmp; tmp; tmp= tmp->next)
2292 if (*tmp->item == item)
2293 *tmp->item= new_item;
2298 if (item->type() == Item::FUNC_ITEM && !found_in_group)
2300 bool changed=
false;
2309 item->with_sum_func= 1;
2332 List<Item>::iterator it(fields_arg.begin());
2333 Item *first_field= &sel_fields.front();
2357 for (level=0 ; level < send_group_parts ; level++)
2360 uint32_t pos= send_group_parts - level -1;
2361 bool real_fields= 0;
2363 List<Item>::iterator new_it(
rollup.getFields()[pos].begin());
2364 Item **ref_array_start=
rollup.getRefPointerArrays()[pos];
2368 Item **ref_array= ref_array_start + fields_arg.size()-1;
2371 sum_funcs_end[pos+1]= *func;
2374 for (i= 0, start_group=
group_list ;i++ < pos ;start_group= start_group->next)
2377 it= fields_arg.begin();
2378 while ((item= it++))
2380 if (item == first_field)
2383 ref_array= ref_array_start;
2386 if (item->type() == Item::SUM_FUNC_ITEM && !item->const_item() &&
2387 (!((
Item_sum*) item)->depended_from() ||
2399 item= item->copy_or_same(session);
2408 for (group_tmp= start_group, i= pos ;
2409 group_tmp ; group_tmp= group_tmp->next, i++)
2411 if (*group_tmp->item == item)
2419 null_item->result_field= item->get_tmp_table_field();
2429 new_it.replace(item);
2436 sum_funcs_end[0]= *func;
2460 for (uint32_t i= send_group_parts ; i-- > idx ; )
2465 if ((!having || having->val_int()))
2467 if (send_records < unit->select_limit_cnt && do_send_rows && result->send_data(
rollup.getFields()[i]))
2475 set_items_ref_array(current_ref_pointer_array);
2501 for (uint32_t i= send_group_parts ; i-- > idx ; )
2506 if ((!having || having->val_int()))
2508 List<Item>::iterator it(
rollup.getFields()[i].begin());
2509 while (
Item* item= it++)
2511 if (item->type() == Item::NULL_ITEM && item->is_result_field())
2512 item->save_in_result_field(1);
2515 if (table_arg->
cursor->insertRecord(table_arg->getInsertRecord()))
2517 my_error(ER_USE_SQL_BIG_RESULT, MYF(0));
2523 set_items_ref_array(current_ref_pointer_array);
2539 Item_sum *func, **func_ptr= sum_funcs;
2540 while ((func= *(func_ptr++)))
2571 bool cache_flag=
false;
2572 bool *analyzer_arg= &cache_flag;
2575 if (const_tables ==
tables)
2586 for (
JoinTable *tab= join_tab + const_tables; tab < join_tab +
tables ; tab++)
2588 if (*tab->on_expr_ref)
2592 (
unsigned char **)&analyzer_arg,
2594 (
unsigned char *)&cache_flag);
2612 bool not_used_in_distinct= join_tab->not_used_in_distinct;
2613 ha_rows found_records= join->found_records;
2614 COND *select_cond= join_tab->select_cond;
2616 if (error > 0 || (join->session->is_error()))
2617 return NESTED_LOOP_ERROR;
2619 return NESTED_LOOP_NO_MORE_ROWS;
2620 if (join->session->getKilled())
2622 join->session->send_kill_message();
2623 return NESTED_LOOP_KILLED;
2625 if (!select_cond || select_cond->
val_int())
2644 first_unmatched->
found= 1;
2645 for (
JoinTable *tab= first_unmatched; tab <= join_tab; tab++)
2647 if (tab->table->reginfo.not_exists_optimize)
2648 return NESTED_LOOP_NO_MORE_ROWS;
2655 if (tab->select_cond && !tab->select_cond->val_int())
2658 if (tab == join_tab)
2666 join->return_tab= tab;
2667 return NESTED_LOOP_OK;
2675 if ((first_unmatched= first_unmatched->
first_upper) &&
2681 JoinTable *return_tab= join->return_tab;
2689 join->examined_rows++;
2690 join->session->row_count++;
2696 rc= (*join_tab->next_select)(join, join_tab+1, 0);
2697 if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
2699 if (return_tab < join->return_tab)
2700 join->return_tab= return_tab;
2702 if (join->return_tab < join_tab)
2703 return NESTED_LOOP_OK;
2709 if (not_used_in_distinct && found_records != join->found_records)
2710 return NESTED_LOOP_NO_MORE_ROWS;
2713 join_tab->read_record.cursor->unlock_row();
2721 join->examined_rows++;
2722 join->session->row_count++;
2723 join_tab->read_record.cursor->unlock_row();
2725 return NESTED_LOOP_OK;
2743 for ( ; join_tab <= last_inner_tab ; join_tab++)
2749 join_tab->table->restoreRecordAsDefault();
2750 join_tab->table->mark_as_null_row();
2751 select_cond= join_tab->select_cond;
2753 if (select_cond && !select_cond->
val_int())
2754 return NESTED_LOOP_OK;
2769 if (! first_unmatched)
2771 first_unmatched->
found= 1;
2772 for (
JoinTable *tab= first_unmatched; tab <= join_tab; tab++)
2774 if (tab->select_cond && !tab->select_cond->val_int())
2776 join->return_tab= tab;
2777 return NESTED_LOOP_OK;
2787 return (*join_tab->next_select)(join, join_tab+1, 0);
2796 join_tab->table->null_row= 0;
2797 if (!join_tab->cache.records)
2799 return NESTED_LOOP_OK;
2804 (void) join_tab->cache.store_record_in_cache();
2808 if (join_tab->use_quick == 2)
2810 delete join_tab->select->quick;
2811 join_tab->select->quick= 0;
2814 if ((error=join_init_read_record(join_tab)))
2816 join_tab->cache.reset_cache_write();
2817 return error < 0 ? NESTED_LOOP_NO_MORE_ROWS: NESTED_LOOP_ERROR;
2820 for (
JoinTable *tmp=join->join_tab; tmp != join_tab ; tmp++)
2822 tmp->status=tmp->table->status;
2823 tmp->table->status=0;
2826 info= &join_tab->read_record;
2829 if (join->session->getKilled())
2831 join->session->send_kill_message();
2832 return NESTED_LOOP_KILLED;
2835 if (rc == NESTED_LOOP_OK &&
2836 (!join_tab->cache.select || !join_tab->cache.select->skip_record()))
2839 join_tab->cache.reset_cache_read();
2840 for (i=(join_tab->cache.records- (skip_last ? 1 : 0)) ; i-- > 0 ;)
2842 join_tab->readCachedRecord();
2843 if (!select || !select->skip_record())
2847 rc= (join_tab->next_select)(join,join_tab+1,0);
2848 if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
2850 join_tab->cache.reset_cache_write();
2855 return NESTED_LOOP_ERROR;
2859 }
while (!(error=info->read_record(info)));
2862 join_tab->readCachedRecord();
2863 join_tab->cache.reset_cache_write();
2865 return NESTED_LOOP_ERROR;
2866 for (
JoinTable *tmp2=join->join_tab; tmp2 != join_tab ; tmp2++)
2867 tmp2->table->status=tmp2->status;
2868 return NESTED_LOOP_OK;
2897 if (! end_of_records)
2900 if (join->having && join->having->val_int() == 0)
2901 return NESTED_LOOP_OK;
2903 if (join->do_send_rows)
2904 error=join->result->send_data(*join->fields);
2906 return NESTED_LOOP_ERROR;
2907 if (++join->send_records >= join->unit->select_limit_cnt && join->do_send_rows)
2909 if (join->select_options & OPTION_FOUND_ROWS)
2912 if ((join->tables == 1) && !join->tmp_table && !join->sort_and_group
2913 && !join->send_group_parts && !join->having && !jt->select_cond &&
2914 !(jt->select && jt->select->quick) &&
2915 (jt->table->cursor->getEngine()->check_flag(HTON_BIT_STATS_RECORDS_IS_EXACT)) &&
2919 Table *table= jt->table;
2921 join->select_options^= OPTION_FOUND_ROWS;
2922 if (table->sort.record_pointers ||
2923 (table->sort.io_cache && table->sort.io_cache->inited()))
2926 join->send_records= table->sort.found_records;
2930 table->cursor->info(HA_STATUS_VARIABLE);
2931 join->send_records= table->cursor->stats.records;
2936 join->do_send_rows= 0;
2937 if (join->unit->fake_select_lex)
2938 join->unit->fake_select_lex->select_limit= 0;
2939 return NESTED_LOOP_OK;
2942 return NESTED_LOOP_QUERY_LIMIT;
2944 else if (join->send_records >= join->fetch_limit)
2950 return NESTED_LOOP_CURSOR_LIMIT;
2954 return NESTED_LOOP_OK;
2959 Table *table= join->tmp_table;
2961 if (join->session->getKilled())
2963 join->session->send_kill_message();
2964 return NESTED_LOOP_KILLED;
2966 if (!end_of_records)
2969 if (
copy_funcs(join->tmp_table_param.items_to_copy, join->session))
2970 return NESTED_LOOP_ERROR;
2971 if (!join->having || join->having->val_int())
2974 join->found_records++;
2975 if ((error=table->cursor->insertRecord(table->getInsertRecord())))
2977 if (!table->cursor->is_fatal_error(error, HA_CHECK_DUP))
2979 return NESTED_LOOP_OK;
2982 my_error(ER_USE_SQL_BIG_RESULT, MYF(0));
2983 return NESTED_LOOP_ERROR;
2985 if (++join->send_records >= join->tmp_table_param.end_write_records && join->do_send_rows)
2987 if (!(join->select_options & OPTION_FOUND_ROWS))
2988 return NESTED_LOOP_QUERY_LIMIT;
2989 join->do_send_rows= 0;
2990 join->unit->select_limit_cnt= HA_POS_ERROR;
2991 return NESTED_LOOP_OK;
2996 return NESTED_LOOP_OK;
3002 Table *table= join->tmp_table;
3007 return NESTED_LOOP_OK;
3008 if (join->session->getKilled())
3010 join->session->send_kill_message();
3011 return NESTED_LOOP_KILLED;
3014 join->found_records++;
3017 for (group=table->group ; group ; group=group->next)
3019 Item *item= *group->item;
3020 item->save_org_in_field(group->field);
3022 if (item->maybe_null)
3023 group->buff[-1]= (char) group->field->is_null();
3025 if (!table->cursor->index_read_map(table->getUpdateRecord(),
3026 join->tmp_table_param.group_buff,
3030 table->restoreRecord();
3032 if ((error= table->cursor->updateRecord(table->getUpdateRecord(),
3033 table->getInsertRecord())))
3035 table->print_error(error,MYF(0));
3036 return NESTED_LOOP_ERROR;
3038 return NESTED_LOOP_OK;
3047 for (group=table->group,key_part=table->key_info[0].key_part;
3049 group=group->next,key_part++)
3051 if (key_part->null_bit)
3052 memcpy(table->getInsertRecord()+key_part->offset, group->buff, 1);
3054 init_tmptable_sum_functions(join->sum_funcs);
3055 if (
copy_funcs(join->tmp_table_param.items_to_copy, join->session))
3056 return NESTED_LOOP_ERROR;
3057 if ((error=table->cursor->insertRecord(table->getInsertRecord())))
3059 my_error(ER_USE_SQL_BIG_RESULT, MYF(0));
3060 return NESTED_LOOP_ERROR;
3062 join->send_records++;
3063 return NESTED_LOOP_OK;
3069 Table *table= join->tmp_table;
3073 return NESTED_LOOP_OK;
3074 if (join->session->getKilled())
3076 join->session->send_kill_message();
3077 return NESTED_LOOP_KILLED;
3080 init_tmptable_sum_functions(join->sum_funcs);
3082 if (
copy_funcs(join->tmp_table_param.items_to_copy, join->session))
3083 return NESTED_LOOP_ERROR;
3085 if (!(error= table->cursor->insertRecord(table->getInsertRecord())))
3086 join->send_records++;
3089 if ((
int) table->get_dup_key(error) < 0)
3091 table->print_error(error,MYF(0));
3092 return NESTED_LOOP_ERROR;
3094 if (table->cursor->rnd_pos(table->getUpdateRecord(),table->cursor->dup_ref))
3096 table->print_error(error,MYF(0));
3097 return NESTED_LOOP_ERROR;
3099 table->restoreRecord();
3101 if ((error= table->cursor->updateRecord(table->getUpdateRecord(),
3102 table->getInsertRecord())))
3104 table->print_error(error,MYF(0));
3105 return NESTED_LOOP_ERROR;
3108 return NESTED_LOOP_OK;
3125 if (main_join->group_fields_cache.size())
3127 curr_join->group_fields= main_join->group_fields_cache;
3128 curr_join->sort_and_group= 1;
3134 main_join->group_fields_cache= curr_join->group_fields;
3144 uint32_t key_length=0, parts=0, null_parts=0;
3148 for (; group ; group=group->next)
3150 Item *group_item= *group->item;
3151 Field *field= group_item->get_tmp_table_field();
3154 enum_field_types
type;
3155 if ((
type= field->type()) == DRIZZLE_TYPE_BLOB)
3156 key_length+=MAX_BLOB_WIDTH;
3157 else if (
type == DRIZZLE_TYPE_VARCHAR)
3158 key_length+= field->field_length + HA_KEY_BLOB_LENGTH;
3160 key_length+= field->pack_length();
3164 switch (group_item->result_type()) {
3166 key_length+=
sizeof(double);
3170 key_length+=
sizeof(int64_t);
3173 case DECIMAL_RESULT:
3174 key_length+= class_decimal_get_binary_size(group_item->max_length -
3175 (group_item->decimals ? 1 : 0),
3176 group_item->decimals);
3181 enum enum_field_types
type= group_item->field_type();
3187 if (field::isDateTime(
type))
3198 key_length+= group_item->max_length + HA_KEY_BLOB_LENGTH;
3207 my_error(ER_OUT_OF_RESOURCES, MYF(ME_FATALERROR));
3217 join->tmp_table_param.group_length=key_length+null_parts;
3218 join->tmp_table_param.group_parts=parts;
3219 join->tmp_table_param.group_null_parts=null_parts;
3231 for (; group ; group=group->next)
3236 join->group_fields.push_front(tmp);
3239 join->sort_and_group=1;
3243static uint32_t cache_record_length(Join *join,uint32_t idx)
3246 JoinTable **pos,**end;
3247 Session *session=join->session;
3249 for (pos=join->best_ref+join->const_tables,end=join->best_ref+idx ;
3253 JoinTable *join_tab= *pos;
3254 if (!join_tab->used_fieldlength)
3256 length+=join_tab->used_fieldlength;
3311static double prev_record_reads(
Join *join, uint32_t idx, table_map found_ref)
3319 if (pos->examinePosition(found_ref))
3321 found_ref|= pos->getRefDependMap();
3338 if (pos->getFanout() > DBL_EPSILON)
3339 found*= pos->getFanout();
3351 table_map used_tables;
3354 uint32_t table_count;
3355 Session *session=join->session;
3358 table_count=join->tables;
3359 join->join_tab=join_tab=
new (session->mem)
JoinTable[table_count];
3362 used_tables= OUTER_REF_TABLE_BIT;
3363 for (j=join_tab, tablenr=0 ; tablenr < table_count ; tablenr++,j++)
3366 cur_pos= join->getPosFromOptimalPlan(tablenr);
3367 *j= *cur_pos.getJoinTable();
3368 form=join->table[tablenr]=j->table;
3369 used_tables|= form->
map;
3370 form->reginfo.join_tab=j;
3372 form->reginfo.not_exists_optimize=0;
3373 if (j->
type == AM_CONST)
3379 if (j->
type == AM_SYSTEM)
3381 if (j->
keys.none() || ! (keyuse= cur_pos.getKeyUse()))
3384 if (tablenr != join->const_tables)
3387 else if (create_ref_for_key(join, j, keyuse, used_tables))
3391 for (i=0 ; i < table_count ; i++)
3392 join->map2table[join->join_tab[i].table->tablenr]=join->join_tab+i;
3408 join->setPosInPartialPlan(idx, tmp_pos);
3413 for (;next != table ; pos++)
3419 join->best_ref[idx]=table;
3442 uint32_t search_depth= join->session->variables.optimizer_search_depth;
3443 uint32_t prune_level= join->session->variables.optimizer_prune_level;
3444 bool straight_join= test(join->select_options & SELECT_STRAIGHT_JOIN);
3446 join->cur_embedding_map.reset();
3456 internal::my_qsort(join->best_ref + join->const_tables,
3457 join->tables - join->const_tables,
sizeof(
JoinTable*),
3465 if (search_depth == 0)
3468 if (
greedy_search(join, join_tables, search_depth, prune_level))
3478 if (join->session->lex().is_single_level_stmt())
3479 join->session->status_var.last_query_cost= join->best_read;
3510 table_map remaining_tables,
3512 double record_count,
3516 uint32_t best_max_key_part= 0;
3517 bool found_constraint= 0;
3518 double best= DBL_MAX;
3519 double best_time= DBL_MAX;
3520 double records= DBL_MAX;
3521 table_map best_ref_depends_map= 0;
3527 Table *table= s->table;
3530 double best_records= DBL_MAX;
3531 uint32_t max_key_part=0;
3535 for (keyuse= s->
keyuse; keyuse->getTable() == table; )
3537 key_part_map found_part= 0;
3538 table_map found_ref= 0;
3539 uint32_t key= keyuse->getKey();
3540 KeyInfo *keyinfo= table->key_info + key;
3542 key_part_map const_part= 0;
3544 key_part_map ref_or_null_part= 0;
3551 uint32_t keypart= keyuse->getKeypart();
3552 table_map best_part_found_ref= 0;
3553 double best_prev_record_reads= DBL_MAX;
3562 if (! (remaining_tables & keyuse->getUsedTables()) &&
3563 ! (ref_or_null_part && (keyuse->getOptimizeFlags() &
3564 KEY_OPTIMIZE_REF_OR_NULL)))
3566 found_part|= keyuse->getKeypartMap();
3567 if (! (keyuse->getUsedTables() & ~join->const_table_map))
3568 const_part|= keyuse->getKeypartMap();
3570 double tmp2= prev_record_reads(join, idx, (found_ref |
3571 keyuse->getUsedTables()));
3572 if (tmp2 < best_prev_record_reads)
3574 best_part_found_ref= keyuse->getUsedTables() & ~join->const_table_map;
3575 best_prev_record_reads= tmp2;
3577 if (rec > keyuse->getTableRows())
3578 rec= keyuse->getTableRows();
3583 if (keyuse->getOptimizeFlags() & KEY_OPTIMIZE_REF_OR_NULL)
3584 ref_or_null_part|= keyuse->getKeypartMap();
3588 }
while (keyuse->getTable() == table && keyuse->getKey() == key &&
3589 keyuse->getKeypart() == keypart);
3590 found_ref|= best_part_found_ref;
3591 }
while (keyuse->getTable() == table && keyuse->getKey() == key);
3603 found_constraint= 1;
3608 if (found_part == PREV_BITS(uint,keyinfo->key_parts) &&
3611 max_key_part= UINT32_MAX;
3612 if ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)
3614 tmp = prev_record_reads(join, idx, found_ref);
3638 if (table->quick_keys.test(key))
3639 records= (double) table->quick_rows[key];
3643 records= (double) s->
records/rec;
3648 if (compare_double(records= keyinfo->rec_per_key[keyinfo->key_parts-1], 0.0))
3651 ((double) s->
records / (double) rec *
3653 ((double) (table->getShare()->max_key_length-keyinfo->key_length) /
3654 (
double) table->getShare()->max_key_length)));
3668 if (table->quick_keys.test(key) &&
3669 const_part & (1 << table->quick_key_parts[key]) &&
3670 table->quick_n_ranges[key] == 1 &&
3671 records > (
double) table->quick_rows[key])
3673 records= (double) table->quick_rows[key];
3678 set_if_smaller(tmp, (
double) session->variables.max_seeks_for_key);
3679 if (table->covering_keys.test(key))
3682 tmp= record_count * table->cursor->index_only_read_time(key, tmp);
3685 tmp= record_count * min(tmp,s->worst_seeks);
3695 if ((found_part & 1) &&
3696 (!(table->index_flags(key) & HA_ONLY_WHOLE_INDEX) ||
3697 found_part == PREV_BITS(uint, keyinfo->key_parts)))
3739 if (table->quick_keys.test(key) && !found_ref &&
3740 table->quick_key_parts[key] == max_key_part &&
3741 table->quick_n_ranges[key] == 1+((ref_or_null_part)?1:0))
3743 tmp= records= (double) table->quick_rows[key];
3748 if (compare_ne_double((records= keyinfo->rec_per_key[max_key_part-1]), 0.0))
3766 if (!found_ref && table->quick_keys.test(key) &&
3767 table->quick_key_parts[key] > max_key_part &&
3768 records < (
double)table->quick_rows[key])
3769 records= (double)table->quick_rows[key];
3790 if (compare_double((rec_per_key=(
double) keyinfo->rec_per_key[keyinfo->key_parts-1]), 0.0))
3792 rec_per_key=(double) s->
records/rec+1;
3799 else if (rec_per_key/(
double) s->
records >= 0.01)
3806 if (keyinfo->key_parts > 1)
3807 tmp= (max_key_part * (rec_per_key - a) +
3808 a*keyinfo->key_parts - rec_per_key)/
3809 (keyinfo->key_parts-1);
3812 set_if_bigger(tmp,1.0);
3814 records = (uint32_t) tmp;
3817 if (ref_or_null_part)
3836 if (table->quick_keys.test(key) &&
3837 table->quick_key_parts[key] <= max_key_part &&
3838 const_part & (1 << table->quick_key_parts[key]) &&
3839 table->quick_n_ranges[key] == 1 + ((ref_or_null_part &
3840 const_part) ? 1 : 0) &&
3841 records > (
double) table->quick_rows[key])
3843 tmp= records= (double) table->quick_rows[key];
3848 set_if_smaller(tmp, (
double) session->variables.max_seeks_for_key);
3849 if (table->covering_keys.test(key))
3852 tmp= record_count * table->cursor->index_only_read_time(key, tmp);
3855 tmp= record_count * min(tmp,s->worst_seeks);
3866 best_records= records;
3867 best_key= start_key;
3868 best_max_key_part= max_key_part;
3869 best_ref_depends_map= found_ref;
3872 records= best_records;
3904 ! (s->quick && best_key && s->quick->
index == best_key->getKey() &&
3905 best_max_key_part >= s->table->quick_key_parts[best_key->getKey()]) &&
3906 ! ((s->table->
cursor->getEngine()->check_flag(HTON_BIT_TABLE_SCAN_ON_INDEX)) &&
3907 ! s->table->covering_keys.none() && best_key && !s->quick) &&
3908 ! (s->table->force_index && best_key && !s->quick))
3920 if (found_constraint)
3921 rnd_records-= rnd_records/4;
3953 tmp= s->table->
cursor->scan_time();
3954 if (s->table->
map & join->outer_join)
3968 tmp*= (1.0 + floor((
double) cache_record_length(join,idx) *
3970 (
double) session->variables.join_buff_size));
3987 if (compare_double(best, DBL_MAX) ||
3996 records= rnd_records;
3999 best_ref_depends_map= 0;
4008 best_ref_depends_map);
4009 join->setPosInPartialPlan(idx, tmp_pos);
4012 idx == join->const_tables &&
4013 s->table == join->sort_by_table &&
4014 join->unit->select_limit_cnt >= records)
4015 join->sort_by_table= (
Table*) 1;
4046 uint32_t idx= join->const_tables;
4047 double record_count= 1.0;
4048 double read_time= 0.0;
4050 for (
JoinTable **pos= join->best_ref + idx ; (s= *pos) ; pos++)
4054 record_count, read_time);
4056 partial_pos= join->getPosFromPartialPlan(idx);
4057 record_count*= partial_pos.getFanout();
4058 read_time+= partial_pos.getCost();
4059 join_tables&= ~(s->table->
map);
4064 partial_pos= join->getPosFromPartialPlan(join->const_tables);
4065 if (join->sort_by_table &&
4067 read_time+= record_count;
4068 join->copyPartialPlanIntoOptimalPlan(idx);
4069 join->best_read= read_time;
4153 table_map remaining_tables,
4154 uint32_t search_depth,
4155 uint32_t prune_level)
4157 double record_count= 1.0;
4158 double read_time= 0.0;
4159 uint32_t idx= join->const_tables;
4161 uint32_t size_remain;
4166 size_remain= internal::my_count_bits(remaining_tables);
4170 join->best_read= DBL_MAX;
4172 read_time, search_depth, prune_level))
4175 if (size_remain <= search_depth)
4185 best_pos= join->getPosFromOptimalPlan(idx);
4186 best_table= best_pos.getJoinTable();
4192 join->setPosInPartialPlan(idx, best_pos);
4205 JoinTable *pos= join->best_ref[best_idx];
4206 while (pos && best_table != pos)
4207 pos= join->best_ref[++best_idx];
4208 assert((pos != NULL));
4210 std::swap(join->best_ref[idx], join->best_ref[best_idx]);
4214 record_count*= partial_pos.getFanout();
4215 read_time+= partial_pos.getCost();
4217 remaining_tables&= ~(best_table->table->
map);
4341 table_map remaining_tables,
4343 double record_count,
4345 uint32_t search_depth,
4346 uint32_t prune_level)
4348 Session *session= join->session;
4349 if (session->getKilled())
4357 double best_record_count= DBL_MAX;
4358 double best_read_time= DBL_MAX;
4361 for (
JoinTable **pos= join->best_ref + idx ; (s= *pos) ; pos++)
4363 table_map real_table_bit= s->table->
map;
4364 if ((remaining_tables & real_table_bit) &&
4365 ! (remaining_tables & s->dependent) &&
4368 double current_record_count, current_read_time;
4379 record_count, read_time);
4381 partial_pos= join->getPosFromPartialPlan(idx);
4382 current_record_count= record_count * partial_pos.getFanout();
4383 current_read_time= read_time + partial_pos.getCost();
4386 if ((current_read_time +
4397 if (prune_level == 1)
4399 if (best_record_count > current_record_count ||
4400 best_read_time > current_read_time ||
4401 (idx == join->const_tables && s->table == join->sort_by_table))
4403 if (best_record_count >= current_record_count &&
4404 best_read_time >= current_read_time &&
4406 (! (s->key_dependent & remaining_tables) ||
4409 best_record_count= current_record_count;
4410 best_read_time= current_read_time;
4420 if ( (search_depth > 1) && (remaining_tables & ~real_table_bit) )
4422 std::swap(join->best_ref[idx], *pos);
4424 remaining_tables & ~real_table_bit,
4426 current_record_count,
4431 std::swap(join->best_ref[idx], *pos);
4438 partial_pos= join->getPosFromPartialPlan(join->const_tables);
4440 if (join->sort_by_table &&
4443 current_read_time+= current_record_count;
4444 if ((search_depth == 1) || (current_read_time < join->best_read))
4446 join->copyPartialPlanIntoOptimalPlan(idx + 1);
4447 join->best_read= current_read_time - 0.001;
4489 uint32_t table_count= join->tables - join->const_tables;
4490 uint32_t search_depth;
4492 uint32_t max_tables_for_exhaustive_opt= 7;
4494 if (table_count <= max_tables_for_exhaustive_opt)
4495 search_depth= table_count+1;
4501 search_depth= max_tables_for_exhaustive_opt;
4503 return search_depth;
4506static void make_simple_join(Join *join,Table *tmp_table)
4512 if (!join->table_reexec)
4514 join->table_reexec=
new (join->session->mem) Table*;
4516 join->tmp_join->table_reexec= join->table_reexec;
4518 if (!join->join_tab_reexec)
4520 join->join_tab_reexec=
new (join->session->mem)
JoinTable;
4522 join->tmp_join->join_tab_reexec= join->join_tab_reexec;
4524 Table** tableptr= join->table_reexec;
4525 JoinTable* join_tab= join->join_tab_reexec;
4527 join->join_tab=join_tab;
4528 join->table=tableptr; tableptr[0]=tmp_table;
4530 join->const_tables=0;
4531 join->const_table_map=0;
4532 join->tmp_table_param.field_count= join->tmp_table_param.sum_func_count=
4533 join->tmp_table_param.func_count=0;
4534 join->tmp_table_param.copy_field=join->tmp_table_param.copy_field_end=0;
4535 join->first_record=join->sort_and_group=0;
4536 join->send_records=(ha_rows) 0;
4538 join->row_limit=join->unit->select_limit_cnt;
4539 join->do_send_rows = (join->row_limit) ? 1 : 0;
4541 join_tab->table=tmp_table;
4543 join_tab->select_cond=0;
4545 join_tab->type= AM_ALL;
4546 join_tab->keys.set();
4548 join_tab->on_expr_ref=0;
4549 join_tab->last_inner= 0;
4550 join_tab->first_unmatched= 0;
4551 join_tab->ref.key = -1;
4552 join_tab->not_used_in_distinct=0;
4553 join_tab->read_first_record= join_init_read_record;
4554 join_tab->join=join;
4555 join_tab->ref.key_parts= 0;
4556 join_tab->read_record.init();
4557 tmp_table->status=0;
4558 tmp_table->null_row=0;
4604 for (uint32_t i=join->const_tables ; i < join->tables ; i++)
4607 Table *table=tab->table;
4608 TableList *tbl= table->pos_in_table_list;
4609 TableList *embedding= tbl->getEmbedding();
4622 tab->
first_upper= embedding->getNestedJoin()->first_nested;
4624 for ( ; embedding ; embedding= embedding->getEmbedding())
4629 NestedJoin *nested_join= embedding->getNestedJoin();
4630 if (!nested_join->counter_)
4636 nested_join->first_nested= tab;
4639 if (embedding->getEmbedding())
4640 tab->
first_upper= embedding->getEmbedding()->getNestedJoin()->first_nested;
4644 if (++nested_join->counter_ < nested_join->join_list.size())
4653static bool make_join_select(Join *join,
4654 optimizer::SqlSelect *select,
4657 Session *session= join->session;
4658 optimizer::Position cur_pos;
4661 add_not_null_conds(join);
4662 table_map used_tables;
4665 if (join->tables > 1)
4666 cond->update_used_tables();
4667 if (join->const_tables == join->tables &&
4668 session->lex().current_select->master_unit() ==
4669 &session->lex().unit)
4670 join->const_table_map|=RAND_TABLE_BIT;
4673 make_cond_for_table(cond,
4674 join->const_table_map,
4676 for (JoinTable *tab= join->join_tab+join->const_tables;
4677 tab < join->join_tab+join->tables ; tab++)
4679 if (*tab->on_expr_ref)
4681 JoinTable *cond_tab= tab->first_inner;
4682 COND *tmp= make_cond_for_table(*tab->on_expr_ref,
4683 join->const_table_map,
4687 tmp=
new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
4690 tmp->quick_fix_field();
4691 cond_tab->select_cond= !cond_tab->select_cond ? tmp :
4692 new Item_cond_and(cond_tab->select_cond,
4694 if (! cond_tab->select_cond)
4696 cond_tab->select_cond->quick_fix_field();
4699 if (const_cond && ! const_cond->val_int())
4705 used_tables=((select->const_tables=join->const_table_map) |
4706 OUTER_REF_TABLE_BIT | RAND_TABLE_BIT);
4707 for (uint32_t i=join->const_tables ; i < join->tables ; i++)
4715 table_map current_map= tab->table->
map;
4716 bool use_quick_range=0;
4723 if (i == join->tables-1)
4724 current_map|= OUTER_REF_TABLE_BIT | RAND_TABLE_BIT;
4725 used_tables|=current_map;
4727 if (tab->type == AM_REF && tab->quick &&
4728 (uint32_t) tab->ref.key == tab->quick->index &&
4729 tab->ref.key_length < tab->quick->max_used_key_length)
4736 tab->ref.key_parts= 0;
4737 cur_pos= join->getPosFromOptimalPlan(i);
4738 cur_pos.setFanout(tab->quick->records);
4743 if (i != join->const_tables && join->tables > join->const_tables + 1)
4747 if (join->full_join and not session->lex().current_select->is_cross and not cond)
4749 my_error(ER_CARTESIAN_JOIN_ATTEMPTED, MYF(0));
4755 tmp= make_cond_for_table(cond,used_tables,current_map, 0);
4756 if (cond && !tmp && tab->quick)
4758 if (tab->type != AM_ALL)
4779 if (tmp || !cond || tab->type == AM_REF || tab->type == AM_REF_OR_NULL ||
4780 tab->type == AM_EQ_REF)
4797 tab->select_cond=sel->cond=tmp;
4800 tab->select_cond= sel->
cond= NULL;
4802 sel->head=tab->table;
4807 if (tab->needed_reg.none() && tab->type != AM_EQ_REF
4808 && (tab->type != AM_REF || (uint32_t) tab->ref.key == tab->quick->index))
4810 sel->quick=tab->quick;
4811 sel->quick_keys.reset();
4812 sel->needed_reg.reset();
4820 uint32_t ref_key=
static_cast<uint32_t
>(sel->head->reginfo.join_tab->ref.key + 1);
4821 if (i == join->const_tables && ref_key)
4823 if (tab->const_keys.any() &&
4824 tab->table->reginfo.impossible_range)
4827 else if (tab->type == AM_ALL && ! use_quick_range)
4829 if (tab->const_keys.any() &&
4830 tab->table->reginfo.impossible_range)
4839 cur_pos= join->getPosFromOptimalPlan(i);
4840 if ((cond && (! ((tab->keys & tab->const_keys) == tab->keys) && i > 0)) ||
4841 (! tab->const_keys.none() && (i == join->const_tables) &&
4842 (join->unit->select_limit_cnt < cur_pos.getFanout()) && ((join->select_options & OPTION_FOUND_ROWS) ==
false)))
4845 COND *orig_cond= sel->cond;
4846 sel->cond= and_conds(sel->cond, *tab->on_expr_ref);
4855 if (sel->cond && ! sel->cond->fixed)
4856 sel->cond->quick_fix_field();
4858 if (sel->test_quick_select(session, tab->keys,
4859 used_tables & ~ current_map,
4860 (join->select_options &
4863 join->unit->select_limit_cnt), 0,
4870 sel->cond=orig_cond;
4871 if (! *tab->on_expr_ref ||
4872 sel->test_quick_select(session, tab->keys,
4873 used_tables & ~ current_map,
4874 (join->select_options &
4877 join->unit->select_limit_cnt),0,
4882 sel->cond=orig_cond;
4887 cur_pos= join->getPosFromOptimalPlan(i);
4888 cur_pos.setFanout(
static_cast<double>(sel->quick->records));
4893 sel->needed_reg= tab->needed_reg;
4894 sel->quick_keys.reset();
4896 if (!((tab->checked_keys & sel->quick_keys) == sel->quick_keys) ||
4897 !((tab->checked_keys & sel->needed_reg) == sel->needed_reg))
4899 tab->keys= sel->quick_keys;
4900 tab->keys|= sel->needed_reg;
4901 tab->use_quick= (!sel->needed_reg.none() &&
4902 (select->quick_keys.none() ||
4904 (select->quick->records >= 100L)))) ?
4906 sel->read_tables= used_tables & ~current_map;
4908 if (i != join->const_tables && tab->use_quick != 2)
4911 (tmp=make_cond_for_table(cond,
4912 join->const_table_map |
4917 tab->cache.select->cond= tmp;
4918 tab->cache.select->read_tables= join->const_table_map;
4934 for (
JoinTable *join_tab= join->join_tab+join->const_tables;
4935 join_tab < join->join_tab+join->tables ; join_tab++)
4937 if (*join_tab->on_expr_ref)
4940 tmp= make_cond_for_table(*join_tab->on_expr_ref,
4941 join->const_table_map,
4948 tmp->quick_fix_field();
4949 cond_tab->select_cond= !cond_tab->select_cond ? tmp :
4951 if (! cond_tab->select_cond)
4953 cond_tab->select_cond->quick_fix_field();
4959 while (first_inner_tab && first_inner_tab->last_inner == last_tab)
4965 COND *on_expr= *first_inner_tab->on_expr_ref;
4967 table_map used_tables2= (join->const_table_map |
4968 OUTER_REF_TABLE_BIT | RAND_TABLE_BIT);
4969 for (tab= join->join_tab+join->const_tables; tab <= last_tab ; tab++)
4971 current_map= tab->table->map;
4972 used_tables2|= current_map;
4973 COND *tmp_cond= make_cond_for_table(on_expr, used_tables2,
4977 JoinTable *cond_tab= tab < first_inner_tab ? first_inner_tab : tab;
4994 tmp_cond->quick_fix_field();
4996 cond_tab->select_cond= !cond_tab->select_cond ? tmp_cond :
4999 if (! cond_tab->select_cond)
5001 cond_tab->select_cond->quick_fix_field();
5004 first_inner_tab= first_inner_tab->first_upper;
5032static void make_join_readinfo(
Join& join)
5036 for (uint32_t i= join.const_tables; i < join.tables; i++)
5039 Table *table=tab->table;
5040 tab->read_record.table= table;
5041 tab->read_record.
cursor= table->cursor;
5047 tab->sorted= sorted;
5050 if (tab->insideout_match_tab)
5052 tab->insideout_buf= join.session->mem.alloc(tab->table->key_info[tab->index].key_length);
5055 optimizer::AccessMethodFactory::create(tab->type)->getStats(*table, *tab);
5058 join.join_tab[join.tables-1].next_select= NULL;
5064 JoinTable *join_tab=join->join_tab, *end=join_tab+join->tables;
5066 for (; join_tab != end ; join_tab++)
5069 table_map depend_map= 0;
5072 for (i=0 ; i < ref->
key_parts ; i++,item++)
5074 ref->
depend_map=depend_map & ~OUTER_REF_TABLE_BIT;
5075 depend_map&= ~OUTER_REF_TABLE_BIT;
5076 for (
JoinTable **tab=join->map2table; depend_map; tab++,depend_map>>=1 )
5087 for (; order ; order=order->next)
5089 table_map depend_map;
5090 order->item[0]->update_used_tables();
5091 order->depend_map=depend_map=order->item[0]->
used_tables();
5093 if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT))
5094 && !order->item[0]->with_sum_func)
5096 for (
JoinTable **tab=join->map2table; depend_map; tab++, depend_map>>=1)
5099 order->depend_map|=(*tab)->ref.depend_map;
5125 if (join->tables == join->const_tables)
5126 return change_list ? 0 : first_order;
5128 Order *order,**prev_ptr;
5129 table_map first_table= join->join_tab[join->const_tables].table->map;
5130 table_map not_const_tables= ~join->const_table_map;
5133 prev_ptr= &first_order;
5134 *simple_order= *join->join_tab[join->const_tables].on_expr_ref ? 0 : 1;
5139 for (order=first_order; order ; order=order->next)
5141 table_map order_tables=order->item[0]->
used_tables();
5142 if (order->item[0]->with_sum_func)
5144 else if (!(order_tables & not_const_tables))
5152 if (order_tables & (RAND_TABLE_BIT | OUTER_REF_TABLE_BIT))
5161 if ((ref=order_tables & (not_const_tables ^ first_table)))
5163 if (!(order_tables & first_table) &&
5164 only_eq_ref_tables(join,first_order, ref))
5174 prev_ptr= &order->next;
5178 if (prev_ptr == &first_order)
5180 return(first_order);
5183static 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)
5185 if (select_options & SELECT_DESCRIBE)
5187 optimizer::ExplainPlan planner(join,
false,
false,
false, info);
5188 planner.printPlan();
5197 table->table->mark_as_null_row();
5198 if (having && having->val_int() == 0)
5201 result->send_fields(fields);
5204 List<Item>::iterator it(fields.begin());
5205 while (
Item* item= it++)
5206 item->no_rows_in_result();
5207 result->send_data(fields);
5211 join->session->limit_found_rows= join->session->examined_row_count= 0;
5338 List<TableList>::iterator li(join_list->begin());
5346 table_map used_tables;
5347 table_map not_null_tables= (table_map) 0;
5349 if ((nested_join= table->getNestedJoin()))
5357 Item *expr= table->on_expr;
5366 expr=
simplify_joins(join, &nested_join->join_list, expr,
false);
5368 if (!table->prep_on_expr || expr != table->on_expr)
5372 table->on_expr= expr;
5373 table->prep_on_expr= expr->copy_andor_structure(join->session);
5376 nested_join->used_tables= (table_map) 0;
5377 nested_join->not_null_tables=(table_map) 0;
5378 conds=
simplify_joins(join, &nested_join->join_list, conds, top);
5379 used_tables= nested_join->used_tables;
5380 not_null_tables= nested_join->not_null_tables;
5384 if (!table->prep_on_expr)
5385 table->prep_on_expr= table->on_expr;
5386 used_tables= table->table->map;
5391 if (table->getEmbedding())
5393 table->getEmbedding()->getNestedJoin()->used_tables|= used_tables;
5394 table->getEmbedding()->getNestedJoin()->not_null_tables|= not_null_tables;
5397 if (!table->outer_join || (used_tables & not_null_tables))
5403 table->outer_join= 0;
5409 conds= and_conds(conds, table->on_expr);
5412 assert(!conds->
fixed);
5413 conds->fix_fields(join->session, &conds);
5416 conds= table->on_expr;
5417 table->prep_on_expr= table->on_expr= 0;
5430 table->setDepTables(table->getDepTables() | table->on_expr->used_tables());
5431 if (table->getEmbedding())
5433 table->setDepTables(table->getDepTables() & ~table->getEmbedding()->getNestedJoin()->used_tables);
5438 table->getEmbedding()->setOnExprDepTables(table->getEmbedding()->getOnExprDepTables() & table->on_expr->used_tables());
5441 table->setDepTables(table->getDepTables() & ~table->table->map);
5448 if ((test(join->select_options & SELECT_STRAIGHT_JOIN)) ||
5450 prev_table->setDepTables(prev_table->getDepTables() | used_tables);
5453 prev_table->setDepTables(prev_table->getDepTables() | table->getOnExprDepTables());
5454 table_map prev_used_tables= prev_table->getNestedJoin() ?
5455 prev_table->getNestedJoin()->used_tables :
5464 prev_table->setDepTables(prev_table->getDepTables() | used_tables);
5474 li= join_list->begin();
5477 nested_join= table->getNestedJoin();
5478 if (nested_join && !table->on_expr)
5480 List<TableList>::iterator it(nested_join->join_list.begin());
5483 tbl->setEmbedding(table->getEmbedding());
5484 tbl->setJoinList(table->getJoinList());
5486 li.replace(nested_join->join_list);
5492static int remove_duplicates(Join *join, Table *entry,List<Item> &fields, Item *having)
5494 entry->reginfo.lock_type=TL_WRITE;
5497 uint32_t field_count= 0;
5498 List<Item>::iterator it(fields.begin());
5499 while (Item* item=it++)
5501 if (item->get_tmp_table_field() && ! item->const_item())
5505 if (!field_count && !(join->select_options & OPTION_FOUND_ROWS) && !having)
5507 join->unit->select_limit_cnt= 1;
5510 Field **first_field=entry->getFields() + entry->getShare()->sizeFields() - field_count;
5511 uint32_t offset= field_count ? entry->getField(entry->getShare()->sizeFields() - field_count)->offset(entry->getInsertRecord()) : 0;
5512 uint32_t reclength= entry->getShare()->getRecordLength() - offset;
5514 entry->free_io_cache();
5515 entry->cursor->info(HA_STATUS_VARIABLE);
5517 if (entry->getShare()->db_type() == heap_engine ||
5518 (!entry->getShare()->blob_fields &&
5519 ((ALIGN_SIZE(reclength) + HASH_OVERHEAD) * entry->cursor->stats.records < join->session->variables.sortbuff_size)))
5525 error= remove_dup_with_compare(join->session, entry, first_field, offset, having);
5527 free_blobs(first_field);
5535 Item **ref_pointer_array,
5543 bool *hidden_group_fields)
5546 nesting_map save_allow_sum_func=session->lex().allow_sum_func;
5548 session->lex().allow_sum_func&= ~(1 << session->lex().current_select->nest_level);
5549 res= session->setup_conds(tables, conds);
5551 session->lex().allow_sum_func|= 1 << session->lex().current_select->nest_level;
5552 res= res ||
setup_order(session, ref_pointer_array, tables, fields, all_fields, order);
5553 session->lex().allow_sum_func&= ~(1 << session->lex().current_select->nest_level);
5554 res= res ||
setup_group(session, ref_pointer_array, tables, fields, all_fields, group, hidden_group_fields);
5555 session->lex().allow_sum_func= save_allow_sum_func;
5572 uint32_t table_count;
5573 uint32_t const_count;
5575 table_map found_const_table_map;
5576 table_map all_table_map;
5577 table_map found_ref;
5581 Table **table_vector= NULL;
5588 table_map outer_join= 0;
5589 vector<optimizer::SargableParam> sargables;
5593 table_count= join->tables;
5595 stat_ref=
new (join->session->mem)
JoinTable*[MAX_TABLES];
5596 table_vector=
new (join->session->mem)
Table*[2 * table_count];
5598 join->best_ref=stat_vector;
5600 stat_end=stat+table_count;
5601 found_const_table_map= all_table_map=0;
5606 s++, tables= tables->next_leaf, i++)
5608 TableList *embedding= tables->getEmbedding();
5613 s->needed_reg.reset();
5614 table_vector[i]=s->table=table=tables->table;
5615 table->pos_in_table_list= tables;
5616 assert(table->cursor);
5617 error= table->cursor->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
5620 table->print_error(error, MYF(0));
5623 table->quick_keys.reset();
5624 table->reginfo.join_tab=s;
5625 table->reginfo.not_exists_optimize=0;
5626 memset(table->const_key_parts, 0,
sizeof(key_part_map)*table->getShare()->sizeKeys());
5627 all_table_map|= table->map;
5631 s->dependent= tables->getDepTables();
5632 s->key_dependent= 0;
5633 table->quick_condition_rows= table->cursor->stats.records;
5639 if (!table->cursor->stats.records && !embedding)
5645 outer_join|= table->map;
5647 for (;embedding; embedding= embedding->getEmbedding())
5651 if (embedding && !(
false && ! embedding->getEmbedding()))
5657 NestedJoin *nested_join= embedding->getNestedJoin();
5659 s->dependent|= embedding->getDepTables();
5660 embedding= embedding->getEmbedding();
5661 outer_join|= nested_join->used_tables;
5666 if ((table->cursor->stats.records <= 1) && !s->dependent &&
5667 (table->cursor->getEngine()->check_flag(HTON_BIT_STATS_RECORDS_IS_EXACT)) &&
5668 !join->no_const_tables)
5674 join->outer_join=outer_join;
5676 if (join->outer_join)
5686 for (i= 0; i < table_count; i++)
5689 table= stat[i].table;
5691 if (!table->reginfo.join_tab->dependent)
5694 for (j= 0, s= stat; j < table_count; j++, s++)
5696 if (s->dependent & table->map)
5698 table_map was_dependent= s->dependent;
5699 s->dependent |= table->reginfo.join_tab->dependent;
5700 if (i > j && s->dependent != was_dependent)
5709 for (i= 0, s= stat ; i < table_count ; i++, s++)
5711 if (s->dependent & s->table->
map)
5714 my_message(ER_WRONG_OUTER_JOIN, ER(ER_WRONG_OUTER_JOIN), MYF(0));
5717 if (outer_join & s->table->
map)
5718 s->table->maybe_null= 1;
5720 s->key_dependent= s->dependent;
5724 if (conds || outer_join)
5725 update_ref_and_keys(join->session, keyuse_array, stat, join->tables, conds, join->cond_equal, ~outer_join, join->select_lex, sargables);
5728 join->const_table_map= 0;
5732 while (p_pos < p_end)
5735 s= p_pos->getJoinTable();
5737 join->const_table_map|=s->table->
map;
5738 if ((tmp= s->joinReadConstTable(p_pos)))
5744 found_const_table_map|= s->table->
map;
5752 more_const_tables_found:
5761 for (
JoinTable **pos= stat_vector+const_count; (s= *pos); pos++)
5782 while (keyuse->getTable() == table)
5784 if (! (keyuse->getVal()->
used_tables() & ~join->const_table_map) &&
5785 keyuse->getVal()->
is_null() && keyuse->isNullRejected())
5788 table->mark_as_null_row();
5789 found_const_table_map|= table->map;
5790 join->const_table_map|= table->map;
5792 goto more_const_tables_found;
5801 if (s->dependent & ~(found_const_table_map))
5803 if (table->cursor->stats.records <= 1L &&
5804 (table->cursor->getEngine()->check_flag(HTON_BIT_STATS_RECORDS_IS_EXACT)) &&
5805 !table->pos_in_table_list->getEmbedding())
5809 join->const_table_map|=table->map;
5811 partial_pos= join->getSpecificPosInPartialPlan(const_count - 1);
5812 if ((tmp= s->joinReadConstTable(partial_pos)))
5818 found_const_table_map|= table->map;
5826 while (keyuse->getTable() == table)
5828 start_keyuse= keyuse;
5829 key= keyuse->getKey();
5837 if (keyuse->getVal()->type() != Item::NULL_ITEM &&
5838 ! keyuse->getOptimizeFlags())
5840 if (! ((~found_const_table_map) & keyuse->getUsedTables()))
5841 const_ref.set(keyuse->getKeypart());
5843 refs|= keyuse->getUsedTables();
5844 eq_part.set(keyuse->getKeypart());
5847 }
while (keyuse->getTable() == table && keyuse->getKey() == key);
5849 if (is_keymap_prefix(eq_part, table->key_info[key].key_parts) &&
5850 ! table->pos_in_table_list->getEmbedding())
5852 if ((table->key_info[key].flags & (HA_NOSAME)) == HA_NOSAME)
5854 if (const_ref == eq_part)
5859 join->const_table_map|= table->map;
5861 if (create_ref_for_key(join, s, start_keyuse, found_const_table_map))
5863 partial_pos= join->getSpecificPosInPartialPlan(const_count - 1);
5864 if ((tmp=s->joinReadConstTable(partial_pos)))
5870 found_const_table_map|= table->map;
5876 else if (const_ref == eq_part)
5882 }
while (join->const_table_map & found_ref && ref_changed);
5888 if (const_count && ! sargables.empty())
5890 for (vector<optimizer::SargableParam>::reference iter : sargables)
5892 Field& field= *iter.getField();
5893 JoinTable *join_tab= field.getTable()->reginfo.join_tab;
5894 key_map possible_keys= field.key_start & field.getTable()->keys_in_use_for_query;
5895 bool is_const=
true;
5896 for (uint32_t j= 0; j < iter.getNumValues(); j++)
5897 is_const&= iter.isConstItem(j);
5905 for (s=stat ; s < stat_end ; s++)
5907 if (s->
type == AM_SYSTEM || s->
type == AM_CONST)
5924 if (s->worst_seeks < 2.0)
5934 !s->table->pos_in_table_list->getEmbedding())
5938 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);
5941 records= get_quick_record_count(join->session, select, s->table, &s->
const_keys, join->row_limit);
5942 s->quick=select->
quick;
5946 if (records == 0 && s->table->reginfo.impossible_range)
5954 join->const_table_map|= s->table->
map;
5960 s->info=
"Impossible ON condition";
5961 found_const_table_map|= s->table->
map;
5963 s->table->mark_as_null_row();
5966 if (records != HA_POS_ERROR)
5975 join->join_tab=stat;
5976 join->map2table=stat_ref;
5977 join->table= join->all_tables=table_vector;
5978 join->const_tables=const_count;
5979 join->found_const_table_map=found_const_table_map;
5982 if (join->const_tables != join->tables)
5987 DRIZZLE_QUERY_OPT_CHOOSE_PLAN_START(join->session->getQueryString()->c_str(), join->session->thread_id);
5988 bool res=
choose_plan(join, all_table_map & ~join->const_table_map);
5989 DRIZZLE_QUERY_OPT_CHOOSE_PLAN_DONE(res ? 1 : 0);
5995 join->copyPartialPlanIntoOptimalPlan(join->const_tables);
5996 join->best_read= 1.0;
6023 List<TableList>::iterator li(join_list->begin());
6026 if (
NestedJoin* nested_join= table->getNestedJoin())
6039 if (nested_join->join_list.size() != 1)
6043 nested_join->nj_map.set(first_unused++);
6049 return(first_unused);
6066 for (; a && b; a=a->next,b=b->next)
6068 if (!(*a->item)->eq(*b->item,1))
6072 if (!map || (map & (RAND_TABLE_BIT | OUTER_REF_TABLE_BIT)))
6075 for (; !(map & tables->table->map); tables= tables->next_leaf) {};
6076 if (map != tables->table->map)
6078 return tables->table;
6092 List<TableList>::iterator li(join_list->begin());
6096 if ((nested_join= table->getNestedJoin()))
6098 nested_join->counter_= 0;
6112 for (; a && b; a=a->next,b=b->next)
6114 if ((*a->item)->eq(*b->item,1))
6176 TableList *last_emb= last->table->pos_in_table_list->getEmbedding();
6177 Join *join= last->join;
6178 for (;last_emb != NULL; last_emb= last_emb->getEmbedding())
6182 bool was_fully_covered= nest->is_fully_covered();
6184 if (--nest->counter_ == 0)
6185 join->cur_embedding_map&= ~nest->nj_map;
6187 if (!was_fully_covered)
6190 join->cur_embedding_map|= nest->nj_map;
6204 Table *table=join_tab->table;
6206 for (uint32_t i=0 ; i < join_tab->ref.
key_parts ; i++)
6208 Field *field=table->getField(table->key_info[join_tab->ref.
key].key_part[i].fieldnr - 1);
6212 if (session->is_fatal_error)
6216 cond->fix_fields(session, (
Item**)&cond);
6218 if (join_tab->select)
6220 cond->add(join_tab->select->
cond);
6221 join_tab->select_cond=join_tab->select->
cond=cond;
6223 else if ((join_tab->select= optimizer::make_select(join_tab->table, 0, 0, cond, 0, &error)))
6224 join_tab->select_cond=cond;
6229static void free_blobs(Field **ptr)
6231 for (; *ptr ; ptr++)
6233 if ((*ptr)->flags & BLOB_FLAG)
6234 ((Field_blob *) (*ptr))->free();
virtual int64_t val_int()=0
virtual void top_level_item(void)
virtual table_map not_null_tables() const
virtual bool cache_const_expr_analyzer(unsigned char **arg)
virtual Item * cache_const_expr_transformer(unsigned char *arg)
virtual table_map used_tables() const
virtual String * val_str(String *str)=0
JoinTable * first_unmatched
std::bitset< 64 > embedding_map
optimizer::KeyUse * keyuse
const char * zero_result_cause
not 0 if exec must return zero result
uint32_t ref_pointer_array_size
size of above in bytes
Item ** ref_pointer_array
List< Item > tmp_fields_list1
List< Item > & fields_list
optimizer::SqlSelect * select
List< TableList > * join_list
List< Item > tmp_all_fields1
JoinTable * join_tab_save
bool group_optimized_away
uint32_t outer_join
Which join type.
bool straight
optimize with prev table
COND_EQUAL * cond_equal
Used with outer join.
Item * on_expr
Used with outer join.
Table * table
opened table
table_map map
ID bit of table (1,2,4,8,16...).
ha_rows quick_condition_rows
bool isConstTable() const
bool hasTableForSorting(Table *cmp_table) const
QuickSelectInterface * quick
#define MATCHING_ROWS_IN_OTHER_TABLE
enum_nested_loop_state evaluate_null_complemented_join_record(Join *join, JoinTable *join_tab)
static void best_access_path(Join *join, JoinTable *s, Session *session, table_map remaining_tables, uint32_t idx, double record_count, double read_time)
int join_tab_cmp_straight(const void *ptr1, const void *ptr2)
bool change_refs_to_tmp_fields(Session *session, Item **ref_pointer_array, List< Item > &res_selected_fields, List< Item > &res_all_fields, uint32_t elements, List< Item > &all_fields)
static void set_position(Join *join, uint32_t index, JoinTable *table, optimizer::KeyUse *key)
int rollup_send_data(uint32_t idx)
static void reset_nj_counters(List< TableList > *join_list)
Order * create_distinct_group(Session *session, Item **ref_pointer_array, Order *order_list, List< Item > &fields, List< Item > &, bool *all_order_by_fields_used)
bool copy_funcs(Item **func_ptr, const Session *session)
static bool choose_plan(Join *join, table_map join_tables)
void count_field_types(Select_Lex *select_lex, Tmp_Table_Param *param, List< Item > &fields, bool reset_with_sum_func)
bool list_contains_unique_index(Table *table, bool(*find_func)(Field *, void *), void *data)
void reset(Session *session_arg, List< Item > &fields_arg, uint64_t select_options_arg, select_result *result_arg)
uint32_t max_part_bit(key_part_map bits)
void update_ref_and_keys(Session *session, DYNAMIC_ARRAY *keyuse, JoinTable *join_tab, uint32_t tables, COND *cond, COND_EQUAL *, table_map normal_tables, Select_Lex *select_lex, vector< optimizer::SargableParam > &sargables)
bool setup_copy_fields(Session *session, Tmp_Table_Param *param, Item **ref_pointer_array, List< Item > &res_selected_fields, List< Item > &res_all_fields, uint32_t elements, List< Item > &all_fields)
static bool add_ref_to_table_cond(Session *session, JoinTable *join_tab)
static uint32_t build_bitmap_for_nested_joins(List< TableList > *join_list, uint32_t first_unused)
bool const_expression_in_where(COND *cond, Item *comp_item, Item **const_item)
static uint32_t determine_search_depth(Join *join)
COND * add_found_match_trig_cond(JoinTable *tab, COND *cond, JoinTable *root_tab)
static bool alloc_group_fields(Join *join, Order *group)
static bool greedy_search(Join *join, table_map remaining_tables, uint32_t depth, uint32_t prune_level)
static bool best_extension_by_limited_search(Join *join, table_map remaining_tables, uint32_t idx, double record_count, double read_time, uint32_t depth, uint32_t prune_level)
int do_select(Join *join, List< Item > *fields, Table *table)
void init_save_join_tab()
Save the original join layout.
static void optimize_straight_join(Join *join, table_map join_tables)
static bool make_group_fields(Join *main_join, Join *curr_join)
static Table * get_sort_by_table(Order *a, Order *b, TableList *tables)
static bool get_best_combination(Join *join)
bool change_to_use_tmp_fields(Session *session, Item **ref_pointer_array, List< Item > &res_selected_fields, List< Item > &res_all_fields, uint32_t elements, List< Item > &all_fields)
static COND * simplify_joins(Join *join, List< TableList > *join_list, COND *conds, bool top)
static void calc_group_buffer(Join *join, Order *group)
Join(Session *session_arg, List< Item > &fields_arg, uint64_t select_options_arg, select_result *result_arg)
void add_group_and_distinct_keys(Join *join, JoinTable *join_tab)
static void make_outerjoin_info(Join *join)
int join_tab_cmp(const void *ptr1, const void *ptr2)
bool change_result(select_result *result)
bool test_if_skip_sort_order(JoinTable *tab, Order *order, ha_rows select_limit, bool no_changes, const key_map *map)
static bool make_join_statistics(Join *join, TableList *leaves, COND *conds, DYNAMIC_ARRAY *keyuse)
enum_nested_loop_state evaluate_join_record(Join *join, JoinTable *join_tab, int error)
Process one record of the nested loop join.
static bool test_if_subpart(Order *a, Order *b)
void calc_used_field_length(Session *, JoinTable *join_tab)
bool find_field_in_item_list(Field *field, void *data)
static void restore_prev_nj_state(JoinTable *last)
static void update_depend_map(Join *join)
bool make_sum_func_list(List< Item > &all_fields, List< Item > &send_fields, bool before_group_by, bool recompute=false)
static int setup_without_group(Session *session, Item **ref_pointer_array, TableList *tables, TableList *, List< Item > &fields, List< Item > &all_fields, COND **conds, Order *order, Order *group, bool *hidden_group_fields)
Item * remove_additional_cond(Item *conds)
void optimize_keyuse(Join *join, DYNAMIC_ARRAY *keyuse_array)
bool change_group_ref(Session *session, Item_func *expr, Order *group_list, bool *changed)
bool find_field_in_order_list(Field *field, void *data)
int rollup_write_data(uint32_t idx, Table *table)
static Order * remove_constants(Join *join, Order *first_order, COND *cond, bool change_list, bool *simple_order)
bool rollup_make_fields(List< Item > &all_fields, List< Item > &fields, Item_sum ***func)
enum_nested_loop_state sub_select(Join *join, JoinTable *join_tab, bool end_of_records)
bool setup_sum_funcs(Session *session, Item_sum **func_ptr)
int remove_dup_with_hash_index(Session *session, Table *table, uint32_t field_count, Field **first_field, uint32_t key_length, Item *having)
enum_nested_loop_state end_unique_update(Join *join, JoinTable *, bool end_of_records)
void copy_fields(Tmp_Table_Param *param)
bool check_interleaving_with_nj(JoinTable *next_tab)
bool setup_subquery_materialization()
enum_nested_loop_state end_update(Join *join, JoinTable *, bool end_of_records)
COND * substitute_for_best_equal_field(COND *cond, COND_EQUAL *cond_equal, void *table_join_idx)
void copy_sum_funcs(Item_sum **func_ptr, Item_sum **end_ptr)
int prepare(Item ***rref_pointer_array, TableList *tables, uint32_t wind_num, COND *conds, uint32_t og_num, Order *order, Order *group, Item *having, Select_Lex *select, Select_Lex_Unit *unit)
int setup_order(Session *session, Item **ref_pointer_array, TableList *tables, List< Item > &fields, List< Item > &all_fields, Order *order)
void update_tmptable_sum_func(Item_sum **func_ptr, Table *)
int setup_group(Session *session, Item **ref_pointer_array, TableList *tables, List< Item > &fields, List< Item > &all_fields, Order *order, bool *hidden_group_fields)
Convert from decimal to binary representation.
TODO: Rename this file - func.h is stupid.
Table * create_tmp_table(Session *session, Tmp_Table_Param *param, List< Item > &fields, Order *group, bool distinct, bool save_sum_fields, uint64_t select_options, ha_rows rows_limit, const char *alias)
const char * in_left_expr_name
Cached_item * new_Cached_item(Session *session, Item *item)