summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/opt_subselect.cc63
-rw-r--r--sql/sql_class.h1
-rw-r--r--sql/sql_delete.cc11
-rw-r--r--sql/sql_lex.h2
-rw-r--r--sql/sql_select.cc3
-rw-r--r--sql/sql_update.cc11
-rw-r--r--sql/sql_yacc.yy13
7 files changed, 91 insertions, 13 deletions
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index fa338f0e60c..cd88e7856a2 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -533,6 +533,31 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs,
}
+/**
+ @brief Check whether an IN subquery must be excluded from conversion to SJ
+
+ @param thd global context the processed statement
+ @returns true if the IN subquery must be excluded from conversion to SJ
+
+ @note
+ Currently a top level IN subquery of an update/delete statement
+ is not converted to SJ if the statement contains ORDER BY ... LIMIT
+ or contains RETURNING.
+*/
+
+bool SELECT_LEX::is_sj_conversion_prohibited(THD *thd)
+{
+ DBUG_ASSERT(master_unit()->item->substype() == Item_subselect::IN_SUBS);
+ SELECT_LEX *outer_sl= outer_select();
+ return !outer_sl->outer_select() &&
+ (thd->lex->sql_command == SQLCOM_UPDATE ||
+ thd->lex->sql_command == SQLCOM_DELETE) &&
+ ((outer_sl->order_list.elements &&
+ outer_sl->get_limit() != HA_POS_ERROR) ||
+ (thd->lex->sql_command == SQLCOM_DELETE &&
+ thd->lex->has_returning()));
+}
+
/*
Check if we need JOIN::prepare()-phase subquery rewrites and if yes, do them
@@ -675,9 +700,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
3. Subquery does not have GROUP BY or ORDER BY
4. Subquery does not use aggregate functions or HAVING
5. Subquery predicate is at the AND-top-level of ON/WHERE clause
- 6. We are not in a subquery of a single table UPDATE/DELETE that
- doesn't have a JOIN (TODO: We should handle this at some
- point by switching to multi-table UPDATE/DELETE)
+ 6. We are not in a top level subquery of a single-table UPDATE or
+ DELETE with ORDER BY ... LIMIT and we are not in a top level
+ subquery of a single-table DELETE with returning
7. We're not in a table-less subquery like "SELECT 1"
8. No execution method was already chosen (by a prepared statement)
9. Parent select is not a table-less select
@@ -692,9 +717,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
!select_lex->group_list.elements && !join->order && // 3
!join->having && !select_lex->with_sum_func && // 4
in_subs->emb_on_expr_nest && // 5
- select_lex->outer_select()->join && // 6
- (!thd->lex->m_sql_cmd ||
- thd->lex->m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI) &&
+ !select_lex->is_sj_conversion_prohibited(thd) && // 6
parent_unit->first_select()->leaf_tables.elements && // 7
!in_subs->has_strategy() && // 8
select_lex->outer_select()->table_list.first && // 9
@@ -754,7 +777,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
*/
if (in_subs && !in_subs->has_strategy())
{
- if (is_materialization_applicable(thd, in_subs, select_lex))
+ if (!select_lex->is_sj_conversion_prohibited(thd) &&
+ is_materialization_applicable(thd, in_subs, select_lex))
{
in_subs->add_strategy(SUBS_MATERIALIZATION);
@@ -7260,3 +7284,28 @@ bool TABLE_LIST::is_sjm_scan_table()
{
return is_active_sjm() && sj_mat_info->is_sj_scan;
}
+
+
+/**
+ @brief Check whether this subselect can be lifted to the top level
+ @returns true if the test is positive, false otherwise
+*/
+
+bool SELECT_LEX::is_sj_subselect_lifted_to_top()
+{
+ st_select_lex *sl= this;
+ st_select_lex *outer_sl= outer_select();
+ for ( ; outer_sl; sl= outer_sl, outer_sl= outer_sl->outer_select())
+ {
+ List_iterator_fast<Item_in_subselect> it(outer_sl->sj_subselects);
+ Item_in_subselect *in_subs;
+ while ((in_subs= it++))
+ {
+ if (in_subs->unit->first_select() == sl)
+ break;
+ }
+ if (!in_subs)
+ return false;
+ }
+ return true;
+}
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 167df5b07d9..3a2704ffe5f 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -7005,6 +7005,7 @@ public:
// Methods used by ColumnStore
uint get_num_of_tables() const { return num_of_tables; }
TABLE_LIST* get_tables() const { return delete_tables; }
+ void set_delete_tables (TABLE_LIST *tbl) { delete_tables= tbl; }
public:
multi_delete(THD *thd_arg, TABLE_LIST *dt, uint num_of_tables);
~multi_delete();
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 2ff43316954..ed4b6beab34 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -1591,10 +1591,16 @@ bool Sql_cmd_delete::prepare_inner(THD *thd)
select_lex->order_list.first,
false, NULL, NULL, NULL,
select_lex, &lex->unit)))
-
{
goto err;
}
+
+ if (!multitable &&
+ select_lex->sj_subselects.elements)
+ multitable= true;
+
+ if (!multitable)
+ ((multi_delete *)result)->set_delete_tables(0);
}
if (multitable)
@@ -1638,7 +1644,8 @@ bool Sql_cmd_delete::prepare_inner(THD *thd)
{
TABLE_LIST *duplicate;
if ((duplicate= unique_table(thd, target_tbl->correspondent_table,
- lex->query_tables, 0)))
+ lex->query_tables, 0)) &&
+ !duplicate->select_lex->is_sj_subselect_lifted_to_top())
{
update_non_unique_table_error(target_tbl->correspondent_table,
"DELETE", duplicate);
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index e733b4fc53f..d1f0274136d 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1642,6 +1642,8 @@ public:
void lex_start(LEX *plex);
bool is_unit_nest() { return (nest_flags & UNIT_NEST_FL); }
void mark_as_unit_nest() { nest_flags= UNIT_NEST_FL; }
+ bool is_sj_conversion_prohibited(THD *thd);
+ bool is_sj_subselect_lifted_to_top();
};
typedef class st_select_lex SELECT_LEX;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index bf49d69023e..a7e5bc77be4 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5834,8 +5834,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
s->needed_reg=select->needed_reg;
select->quick=0;
impossible_range= records == 0 && s->table->reginfo.impossible_range;
- if (join->thd->lex->sql_command == SQLCOM_SELECT &&
- optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER))
+ if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER))
s->table->init_cost_info_for_usable_range_rowid_filters(join->thd);
}
if (!impossible_range)
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 6b14c4f3fcc..b056b13bca3 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -2471,6 +2471,8 @@ int multi_update::do_updates()
table = cur_table->table;
if (table == table_to_update)
continue; // Already updated
+ if (table->file->pushed_rowid_filter)
+ table->file->disable_pushed_rowid_filter();
org_updated= updated;
tmp_table= tmp_tables[cur_table->shared];
tmp_table->file->extra(HA_EXTRA_CACHE); // Change to read cache
@@ -2665,7 +2667,8 @@ int multi_update::do_updates()
check_opt_it.rewind();
while (TABLE *tbl= check_opt_it++)
tbl->file->ha_rnd_end();
-
+ if (table->file->save_pushed_rowid_filter)
+ table->file->enable_pushed_rowid_filter();
}
DBUG_RETURN(0);
@@ -2676,6 +2679,8 @@ err:
}
err2:
+ if (table->file->save_pushed_rowid_filter)
+ table->file->enable_pushed_rowid_filter();
if (table->file->inited)
(void) table->file->ha_rnd_end();
if (tmp_table->file->inited)
@@ -2955,7 +2960,9 @@ bool Sql_cmd_update::prepare_inner(THD *thd)
{
goto err;
}
-
+ if (!multitable &&
+ select_lex->sj_subselects.elements)
+ multitable= true;
}
if (table_list->has_period())
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index a587a378086..13dc602a2d6 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -13370,8 +13370,21 @@ delete_single_table:
YYPS->m_lock_type,
YYPS->m_mdl_type,
NULL,
+ 0)))
+ MYSQL_YYABORT;
+ Select->table_list.save_and_clear(&Lex->auxiliary_table_list);
+ Lex->table_count= 1;
+ Lex->query_tables= 0;
+ Lex->query_tables_last= &Lex->query_tables;
+ if (unlikely(!Select->
+ add_table_to_list(thd, $2, NULL, TL_OPTION_UPDATING,
+ YYPS->m_lock_type,
+ YYPS->m_mdl_type,
+ NULL,
$3)))
MYSQL_YYABORT;
+ Lex->auxiliary_table_list.first->correspondent_table=
+ Lex->query_tables;
YYPS->m_lock_type= TL_READ_DEFAULT;
YYPS->m_mdl_type= MDL_SHARED_READ;
}
View Lorry Controller Status