summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authorMarko Mäkelä <marko.makela@mariadb.com>2019-05-28 15:56:24 +0300
committerMarko Mäkelä <marko.makela@mariadb.com>2019-05-28 15:56:24 +0300
commitd59e15bdb96bd24dadaed39bba25fcb0c0bbf8f4 (patch)
treef371cddd0134ed01964b7b3e051d890b2cec20f1 /sql
parent88157247fc5920ef77cfa96aebd33460d86b3905 (diff)
parent8358c6f03edb941be488f009c2bd0eb9df47e8c5 (diff)
downloadmariadb-git-d59e15bdb96bd24dadaed39bba25fcb0c0bbf8f4.tar.gz
Merge 10.1 into 10.2
Diffstat (limited to 'sql')
-rw-r--r--sql/mysqld.h8
-rw-r--r--sql/opt_subselect.cc73
-rw-r--r--sql/sql_const.h8
-rw-r--r--sql/sql_parse.cc7
-rw-r--r--sql/sql_select.cc136
5 files changed, 141 insertions, 91 deletions
diff --git a/sql/mysqld.h b/sql/mysqld.h
index 8faac6f6f5d..26229c8cc5d 100644
--- a/sql/mysqld.h
+++ b/sql/mysqld.h
@@ -1,5 +1,5 @@
/* Copyright (c) 2006, 2016, Oracle and/or its affiliates.
- Copyright (c) 2010, 2017, MariaDB Corporation.
+ Copyright (c) 2010, 2019, MariaDB Corporation.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -566,11 +566,11 @@ extern mysql_mutex_t
LOCK_item_func_sleep, LOCK_status, LOCK_show_status,
LOCK_error_log, LOCK_delayed_insert, LOCK_short_uuid_generator,
LOCK_delayed_status, LOCK_delayed_create, LOCK_crypt, LOCK_timezone,
- LOCK_slave_list, LOCK_active_mi, LOCK_manager,
- LOCK_global_system_variables, LOCK_user_conn,
+ LOCK_slave_list, LOCK_active_mi, LOCK_manager, LOCK_user_conn,
LOCK_prepared_stmt_count, LOCK_error_messages, LOCK_connection_count,
LOCK_slave_background;
-extern MYSQL_PLUGIN_IMPORT mysql_mutex_t LOCK_thread_count;
+extern MYSQL_PLUGIN_IMPORT mysql_mutex_t LOCK_thread_count,
+ LOCK_global_system_variables;
extern mysql_mutex_t LOCK_start_thread;
#ifdef HAVE_OPENSSL
extern char* des_key_file;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 38c4d622db1..99fa1c7344f 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1,5 +1,5 @@
/*
- Copyright (c) 2010, 2015, MariaDB
+ Copyright (c) 2010, 2019, MariaDB
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -28,6 +28,7 @@
#include <my_global.h>
#include "sql_base.h"
+#include "sql_const.h"
#include "sql_select.h"
#include "filesort.h"
#include "opt_subselect.h"
@@ -523,7 +524,7 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs,
if (optimizer_flag(thd, OPTIMIZER_SWITCH_MATERIALIZATION) && // 0
!child_select->is_part_of_union() && // 1
parent_unit->first_select()->leaf_tables.elements && // 2
- child_select->outer_select()->leaf_tables.elements && // 2A
+ child_select->outer_select()->table_list.first && // 2A
subquery_types_allow_materialization(in_subs) &&
(in_subs->is_top_level_item() || //3
optimizer_flag(thd,
@@ -1383,8 +1384,8 @@ void get_delayed_table_estimates(TABLE *table,
*startup_cost= item->jtbm_read_time;
/* Calculate cost of scanning the temptable */
- double data_size= item->jtbm_record_count *
- hash_sj_engine->tmp_table->s->reclength;
+ double data_size= COST_MULT(item->jtbm_record_count,
+ hash_sj_engine->tmp_table->s->reclength);
/* Do like in handler::read_time */
*scan_time= data_size/IO_SIZE + 2;
}
@@ -2463,7 +2464,8 @@ bool optimize_semijoin_nests(JOIN *join, table_map all_table_map)
int tableno;
double rows= 1.0;
while ((tableno = tm_it.next_bit()) != Table_map_iterator::BITMAP_END)
- rows *= join->map2table[tableno]->table->quick_condition_rows;
+ rows= COST_MULT(rows,
+ join->map2table[tableno]->table->quick_condition_rows);
sjm->rows= MY_MIN(sjm->rows, rows);
}
memcpy((uchar*) sjm->positions,
@@ -2576,7 +2578,7 @@ static uint get_tmp_table_rec_length(Ref_ptr_array p_items, uint elements)
static double
get_tmp_table_lookup_cost(THD *thd, double row_count, uint row_size)
{
- if (row_count * row_size > thd->variables.max_heap_table_size)
+ if (row_count > thd->variables.max_heap_table_size / (double) row_size)
return (double) DISK_TEMPTABLE_LOOKUP_COST;
else
return (double) HEAP_TEMPTABLE_LOOKUP_COST;
@@ -2982,8 +2984,11 @@ bool Sj_materialization_picker::check_qep(JOIN *join,
}
double mat_read_time= prefix_cost.total_cost();
- mat_read_time += mat_info->materialization_cost.total_cost() +
- prefix_rec_count * mat_info->lookup_cost.total_cost();
+ mat_read_time=
+ COST_ADD(mat_read_time,
+ COST_ADD(mat_info->materialization_cost.total_cost(),
+ COST_MULT(prefix_rec_count,
+ mat_info->lookup_cost.total_cost())));
/*
NOTE: When we pick to use SJM[-Scan] we don't memcpy its POSITION
@@ -3023,9 +3028,12 @@ bool Sj_materialization_picker::check_qep(JOIN *join,
}
/* Add materialization cost */
- prefix_cost += mat_info->materialization_cost.total_cost() +
- prefix_rec_count * mat_info->scan_cost.total_cost();
- prefix_rec_count *= mat_info->rows;
+ prefix_cost=
+ COST_ADD(prefix_cost,
+ COST_ADD(mat_info->materialization_cost.total_cost(),
+ COST_MULT(prefix_rec_count,
+ mat_info->scan_cost.total_cost())));
+ prefix_rec_count= COST_MULT(prefix_rec_count, mat_info->rows);
uint i;
table_map rem_tables= remaining_tables;
@@ -3039,8 +3047,8 @@ bool Sj_materialization_picker::check_qep(JOIN *join,
{
best_access_path(join, join->positions[i].table, rem_tables, i,
disable_jbuf, prefix_rec_count, &curpos, &dummy);
- prefix_rec_count *= curpos.records_read;
- prefix_cost += curpos.read_time;
+ prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read);
+ prefix_cost= COST_ADD(prefix_cost, curpos.read_time);
}
*strategy= SJ_OPT_MATERIALIZE_SCAN;
@@ -3347,16 +3355,18 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join,
for (uint j= first_dupsweedout_table; j <= idx; j++)
{
POSITION *p= join->positions + j;
- current_fanout *= p->records_read;
- dups_cost += p->read_time + current_fanout / TIME_FOR_COMPARE;
+ current_fanout= COST_MULT(current_fanout, p->records_read);
+ dups_cost= COST_ADD(dups_cost,
+ COST_ADD(p->read_time,
+ current_fanout / TIME_FOR_COMPARE));
if (p->table->emb_sj_nest)
{
- sj_inner_fanout *= p->records_read;
+ sj_inner_fanout= COST_MULT(sj_inner_fanout, p->records_read);
dups_removed_fanout |= p->table->table->map;
}
else
{
- sj_outer_fanout *= p->records_read;
+ sj_outer_fanout= COST_MULT(sj_outer_fanout, p->records_read);
temptable_rec_size += p->table->table->file->ref_length;
}
}
@@ -3375,12 +3385,13 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join,
sj_outer_fanout,
temptable_rec_size);
- double write_cost= join->positions[first_tab].prefix_record_count*
- sj_outer_fanout * one_write_cost;
- double full_lookup_cost= join->positions[first_tab].prefix_record_count*
- sj_outer_fanout* sj_inner_fanout *
- one_lookup_cost;
- dups_cost += write_cost + full_lookup_cost;
+ double write_cost= COST_MULT(join->positions[first_tab].prefix_record_count,
+ sj_outer_fanout * one_write_cost);
+ double full_lookup_cost=
+ COST_MULT(join->positions[first_tab].prefix_record_count,
+ COST_MULT(sj_outer_fanout,
+ sj_inner_fanout * one_lookup_cost));
+ dups_cost= COST_ADD(dups_cost, COST_ADD(write_cost, full_lookup_cost));
*read_time= dups_cost;
*record_count= prefix_rec_count * sj_outer_fanout;
@@ -3527,8 +3538,8 @@ static void recalculate_prefix_record_count(JOIN *join, uint start, uint end)
if (j == join->const_tables)
prefix_count= 1.0;
else
- prefix_count= join->best_positions[j-1].prefix_record_count *
- join->best_positions[j-1].records_read;
+ prefix_count= COST_MULT(join->best_positions[j-1].prefix_record_count,
+ join->best_positions[j-1].records_read);
join->best_positions[j].prefix_record_count= prefix_count;
}
@@ -5874,14 +5885,16 @@ bool JOIN::choose_subquery_plan(table_map join_tables)
The cost of executing the subquery and storing its result in an indexed
temporary table.
*/
- double materialization_cost= inner_read_time_1 +
- write_cost * inner_record_count_1;
+ double materialization_cost= COST_ADD(inner_read_time_1,
+ COST_MULT(write_cost,
+ inner_record_count_1));
- materialize_strategy_cost= materialization_cost +
- outer_lookup_keys * lookup_cost;
+ materialize_strategy_cost= COST_ADD(materialization_cost,
+ COST_MULT(outer_lookup_keys,
+ lookup_cost));
/* C.2 Compute the cost of the IN=>EXISTS strategy. */
- in_exists_strategy_cost= outer_lookup_keys * inner_read_time_2;
+ in_exists_strategy_cost= COST_MULT(outer_lookup_keys, inner_read_time_2);
/* C.3 Compare the costs and choose the cheaper strategy. */
if (materialize_strategy_cost >= in_exists_strategy_cost)
diff --git a/sql/sql_const.h b/sql/sql_const.h
index b4fdbaafda5..65fe94135ec 100644
--- a/sql/sql_const.h
+++ b/sql/sql_const.h
@@ -236,6 +236,14 @@
#define HEAP_TEMPTABLE_LOOKUP_COST 0.05
#define DISK_TEMPTABLE_LOOKUP_COST 1.0
+
+#define COST_MAX (DBL_MAX * (1.0 - DBL_EPSILON))
+
+#define COST_ADD(c,d) (COST_MAX - (d) > (c) ? (c) + (d) : COST_MAX)
+
+#define COST_MULT(c,f) (COST_MAX / (f) > (c) ? (c) * (f) : COST_MAX)
+
+
#define MY_CHARSET_BIN_MB_MAXLEN 1
/** Don't pack string keys shorter than this (if PACK_KEYS=1 isn't used). */
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index d26a19ab4e3..6cec9902491 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4530,6 +4530,13 @@ end_with_restore_list:
*/
/* Skip first table, which is the table we are inserting in */
TABLE_LIST *second_table= first_table->next_local;
+ /*
+ This is a hack: this leaves select_lex->table_list in an inconsistent
+ state as 'elements' does not contain number of elements in the list.
+ Moreover, if second_table == NULL then 'next' becomes invalid.
+ TODO: fix it by removing the front element (restoring of it should
+ be done properly as well)
+ */
select_lex->table_list.first= second_table;
select_lex->context.table_list=
select_lex->context.first_name_resolution_table= second_table;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 614af3ee73c..182e913c16c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1,5 +1,5 @@
-/* Copyright (c) 2000, 2016 Oracle and/or its affiliates.
- Copyright (c) 2009, 2019 MariaDB Corporation
+/* Copyright (c) 2000, 2016, Oracle and/or its affiliates.
+ Copyright (c) 2009, 2019, MariaDB Corporation.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -6514,7 +6514,7 @@ best_access_path(JOIN *join,
else
tmp= table->file->read_time(key, 1,
(ha_rows) MY_MIN(tmp,s->worst_seeks));
- tmp*= record_count;
+ tmp= COST_MULT(tmp, record_count);
}
}
else
@@ -6679,18 +6679,18 @@ best_access_path(JOIN *join,
else
tmp= table->file->read_time(key, 1,
(ha_rows) MY_MIN(tmp,s->worst_seeks));
- tmp*= record_count;
+ tmp= COST_MULT(tmp, record_count);
}
else
- tmp= best_time; // Do nothing
+ tmp= best_time; // Do nothing
}
- tmp += s->startup_cost;
+ tmp= COST_ADD(tmp, s->startup_cost);
loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
} /* not ft_key */
if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE)
{
- best_time= tmp + records/(double) TIME_FOR_COMPARE;
+ best_time= COST_ADD(tmp, records/(double) TIME_FOR_COMPARE);
best= tmp;
best_records= records;
best_key= start_key;
@@ -6724,14 +6724,18 @@ best_access_path(JOIN *join,
use_cond_selectivity);
tmp= s->quick ? s->quick->read_time : s->scan_time();
- tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ tmp= COST_ADD(tmp, cmp_time);
/* We read the table as many times as join buffer becomes full. */
- tmp*= (1.0 + floor((double) cache_record_length(join,idx) *
- record_count /
- (double) thd->variables.join_buff_size));
- best_time= tmp +
- (record_count*join_sel) / TIME_FOR_COMPARE * rnd_records;
+
+ double refills= (1.0 + floor((double) cache_record_length(join,idx) *
+ record_count /
+ (double) thd->variables.join_buff_size));
+ tmp= COST_MULT(tmp, refills);
+ best_time= COST_ADD(tmp,
+ COST_MULT((record_count*join_sel) / TIME_FOR_COMPARE,
+ rnd_records));
best= tmp;
records= rnd_records;
best_key= hj_start_key;
@@ -6757,7 +6761,8 @@ best_access_path(JOIN *join,
'range' access using index IDX, and the best way to perform 'ref'
access is to use the same index IDX, with the same or more key parts.
(note: it is not clear how this rule is/should be extended to
- index_merge quick selects)
+ index_merge quick selects). Also if we have a hash join we prefer that
+ over a table scan
(3) See above note about InnoDB.
(4) NOT ("FORCE INDEX(...)" is used for table and there is 'ref' access
path, but there is no quick select)
@@ -6773,6 +6778,7 @@ best_access_path(JOIN *join,
be used for cases with small datasets, which is annoying.
*/
if ((records >= s->found_records || best > s->read_time) && // (1)
+ !(best_key && best_key->key == MAX_KEY) && // (2)
!(s->quick && best_key && s->quick->index == best_key->key && // (2)
best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2)
!((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3)
@@ -6800,9 +6806,9 @@ best_access_path(JOIN *join,
access (see first else-branch below), but we don't take it into
account here for range/index_merge access. Find out why this is so.
*/
- tmp= record_count *
- (s->quick->read_time +
- (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);
+ double cmp_time= (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE;
+ tmp= COST_MULT(record_count,
+ COST_ADD(s->quick->read_time, cmp_time));
loose_scan_opt.check_range_access(join, idx, s->quick);
}
@@ -6821,16 +6827,15 @@ best_access_path(JOIN *join,
- read the whole table record
- skip rows which does not satisfy join condition
*/
- tmp= record_count *
- (tmp +
- (s->records - rnd_records)/(double) TIME_FOR_COMPARE);
+ double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ tmp= COST_MULT(record_count, COST_ADD(tmp,cmp_time));
}
else
{
- /* We read the table as many times as join buffer becomes full. */
- tmp*= (1.0 + floor((double) cache_record_length(join,idx) *
- record_count /
- (double) thd->variables.join_buff_size));
+ double refills= (1.0 + floor((double) cache_record_length(join,idx) *
+ (record_count /
+ (double) thd->variables.join_buff_size)));
+ tmp= COST_MULT(tmp, refills);
/*
We don't make full cartesian product between rows in the scanned
table and existing records because we skip all rows from the
@@ -6838,7 +6843,8 @@ best_access_path(JOIN *join,
we read the table (see flush_cached_records for details). Here we
take into account cost to read and skip these records.
*/
- tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ tmp= COST_ADD(tmp, cmp_time);
}
}
@@ -6849,9 +6855,9 @@ best_access_path(JOIN *join,
tmp give us total cost of using TABLE SCAN
*/
if (best == DBL_MAX ||
- (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records <
+ COST_ADD(tmp, record_count/(double) TIME_FOR_COMPARE*rnd_records) <
(best_key->is_for_hash_join() ? best_time :
- best + record_count/(double) TIME_FOR_COMPARE*records)))
+ COST_ADD(best, record_count/(double) TIME_FOR_COMPARE*records)))
{
/*
If the table has a range (s->quick is set) make_join_select()
@@ -7362,9 +7368,10 @@ optimize_straight_join(JOIN *join, table_map join_tables)
join->positions + idx, &loose_scan_pos);
/* compute the cost of the new plan extended with 's' */
- record_count*= join->positions[idx].records_read;
- read_time+= join->positions[idx].read_time +
- record_count / (double) TIME_FOR_COMPARE;
+ record_count= COST_MULT(record_count, join->positions[idx].records_read);
+ read_time= COST_ADD(read_time,
+ COST_ADD(join->positions[idx].read_time,
+ record_count / (double) TIME_FOR_COMPARE));
advance_sj_state(join, join_tables, idx, &record_count, &read_time,
&loose_scan_pos);
@@ -7554,9 +7561,10 @@ greedy_search(JOIN *join,
swap_variables(JOIN_TAB*, join->best_ref[idx], join->best_ref[best_idx]);
/* compute the cost of the new plan extended with 'best_table' */
- record_count*= join->positions[idx].records_read;
- read_time+= join->positions[idx].read_time +
- record_count / (double) TIME_FOR_COMPARE;
+ record_count= COST_MULT(record_count, join->positions[idx].records_read);
+ read_time= COST_ADD(read_time,
+ COST_ADD(join->positions[idx].read_time,
+ record_count / (double) TIME_FOR_COMPARE));
remaining_tables&= ~(best_table->table->map);
--size_remain;
@@ -7663,11 +7671,13 @@ void JOIN::get_partial_cost_and_fanout(int end_tab_idx,
}
if (tab->records_read && (cur_table_map & filter_map))
{
- record_count *= tab->records_read;
- read_time += tab->read_time + record_count / (double) TIME_FOR_COMPARE;
+ record_count= COST_MULT(record_count, tab->records_read);
+ read_time= COST_ADD(read_time,
+ COST_ADD(tab->read_time,
+ record_count / (double) TIME_FOR_COMPARE));
if (tab->emb_sj_nest)
- sj_inner_fanout *= tab->records_read;
- }
+ sj_inner_fanout= COST_MULT(sj_inner_fanout, tab->records_read);
+ }
if (i == last_sj_table)
{
@@ -7705,8 +7715,8 @@ void JOIN::get_prefix_cost_and_fanout(uint n_tables,
{
if (best_positions[i].records_read)
{
- record_count *= best_positions[i].records_read;
- read_time += best_positions[i].read_time;
+ record_count= COST_MULT(record_count, best_positions[i].records_read);
+ read_time= COST_ADD(read_time, best_positions[i].read_time);
}
}
*read_time_arg= read_time;// + record_count / TIME_FOR_COMPARE;
@@ -8267,13 +8277,12 @@ best_extension_by_limited_search(JOIN *join,
best_access_path(join, s, remaining_tables, idx, disable_jbuf,
record_count, join->positions + idx, &loose_scan_pos);
- /* Compute the cost of extending the plan with 's', avoid overflow */
- if (position->records_read < DBL_MAX / record_count)
- current_record_count= record_count * position->records_read;
- else
- current_record_count= DBL_MAX;
- current_read_time=read_time + position->read_time +
- current_record_count / (double) TIME_FOR_COMPARE;
+ /* Compute the cost of extending the plan with 's' */
+ current_record_count= COST_MULT(record_count, position->records_read);
+ current_read_time=COST_ADD(read_time,
+ COST_ADD(position->read_time,
+ current_record_count /
+ (double) TIME_FOR_COMPARE));
advance_sj_state(join, remaining_tables, idx, &current_record_count,
&current_read_time, &loose_scan_pos);
@@ -8355,12 +8364,12 @@ best_extension_by_limited_search(JOIN *join,
if (join->sort_by_table &&
join->sort_by_table !=
join->positions[join->const_tables].table->table)
- /*
- We may have to make a temp table, note that this is only a
- heuristic since we cannot know for sure at this point.
+ /*
+ We may have to make a temp table, note that this is only a
+ heuristic since we cannot know for sure at this point.
Hence it may be wrong.
*/
- current_read_time+= current_record_count;
+ current_read_time= COST_ADD(current_read_time, current_record_count);
if (current_read_time < join->best_read)
{
memcpy((uchar*) join->best_positions, (uchar*) join->positions,
@@ -8678,8 +8687,8 @@ prev_record_reads(POSITION *positions, uint idx, table_map found_ref)
#max_nested_outer_joins=64-1) will not make it any more precise.
*/
if (pos->records_read)
- found*= pos->records_read;
- }
+ found= COST_MULT(found, pos->records_read);
+ }
}
return found;
}
@@ -14824,8 +14833,20 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top,
table->table->maybe_null= FALSE;
table->outer_join= 0;
if (!(straight_join || table->straight))
- table->dep_tables= table->embedding && !table->embedding->sj_subq_pred ?
- table->embedding->dep_tables : 0;
+ {
+ table->dep_tables= 0;
+ TABLE_LIST *embedding= table->embedding;
+ while (embedding)
+ {
+ if (embedding->nested_join->join_list.head()->outer_join)
+ {
+ if (!embedding->sj_subq_pred)
+ table->dep_tables= embedding->dep_tables;
+ break;
+ }
+ embedding= embedding->embedding;
+ }
+ }
if (table->on_expr)
{
/* Add ON expression to the WHERE or upper-level ON condition. */
@@ -15348,11 +15369,12 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab,
pos= loose_scan_pos;
reopt_remaining_tables &= ~rs->table->map;
- rec_count *= pos.records_read;
- cost += pos.read_time;
+ rec_count= COST_MULT(rec_count, pos.records_read);
+ cost= COST_ADD(cost, pos.read_time);
+
if (!rs->emb_sj_nest)
- *outer_rec_count *= pos.records_read;
+ *outer_rec_count= COST_MULT(*outer_rec_count, pos.records_read);
}
join->cur_sj_inner_tables= save_cur_sj_inner_tables;