- commits - lists.mariadb.org

[Commits] 4b077aec0da: Remove empty garbage file: mysql-test/main/show_explain.cc
by psergey 06 Apr '22

06 Apr '22

revision-id: 4b077aec0dabd53932edac4a516a04c597ef2f93 (mariadb-10.6.1-366-g4b077aec0da) parent(s): 7aff8b3049b547190b48de7ed3657ae3bee8df07 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-04-06 12:00:29 +0300 message: Remove empty garbage file: mysql-test/main/show_explain.cc --- mysql-test/main/show_explain.cc | 0 1 file changed, 0 insertions(+), 0 deletions(-) diff --git a/mysql-test/main/show_explain.cc b/mysql-test/main/show_explain.cc deleted file mode 100644 index e69de29bb2d..00000000000

1 0

[Commits] 0e62bfe9264: MDEV-28201: Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
by psergey 04 Apr '22

04 Apr '22

revision-id: 0e62bfe92647ab4b5a2a8582979e4d161c43b105 (mariadb-10.6.1-358-g0e62bfe9264) parent(s): afa835568a1623991799830175039816b9ac2681 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-04-04 12:32:22 +0300 message: MDEV-28201: Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON Fix it.(proper commit comment fill follow) --- mysql-test/suite/perfschema/r/nesting.result | 74 +++++++------- sql/item.cc | 20 +++- sql/mysqld.h | 4 + sql/sql_base.cc | 3 + sql/sql_class.cc | 3 - sql/sql_explain.cc | 145 +++++++++++++++++---------- sql/sql_explain.h | 104 +++++++++++-------- sql/sql_lex.cc | 3 +- sql/sql_parse.cc | 3 +- sql/sql_show.cc | 14 ++- 10 files changed, 232 insertions(+), 141 deletions(-) diff --git a/mysql-test/suite/perfschema/r/nesting.result b/mysql-test/suite/perfschema/r/nesting.result index 5fe515e2662..9e18e5ac272 100644 --- a/mysql-test/suite/perfschema/r/nesting.result +++ b/mysql-test/suite/perfschema/r/nesting.result @@ -128,11 +128,11 @@ relative_event_id relative_end_event_id event_name comment nesting_event_type re 11 11 stage/sql/Executing (stage) STATEMENT 0 12 12 stage/sql/End of update loop (stage) STATEMENT 0 13 13 stage/sql/Query end (stage) STATEMENT 0 -14 14 stage/sql/Commit (stage) STATEMENT 0 -15 15 stage/sql/closing tables (stage) STATEMENT 0 -16 16 stage/sql/Starting cleanup (stage) STATEMENT 0 -17 18 stage/sql/Freeing items (stage) STATEMENT 0 -18 18 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 17 +14 15 stage/sql/Commit (stage) STATEMENT 0 +15 15 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 14 +16 16 stage/sql/closing tables (stage) STATEMENT 0 +17 17 stage/sql/Starting cleanup (stage) STATEMENT 0 +18 18 stage/sql/Freeing items (stage) STATEMENT 0 19 19 wait/io/socket/sql/client_connection send STATEMENT 0 20 21 stage/sql/Reset for next command (stage) STATEMENT 0 21 21 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 20 @@ -152,11 +152,11 @@ relative_event_id relative_end_event_id event_name comment nesting_event_type re 35 35 stage/sql/Executing (stage) STATEMENT 24 36 36 stage/sql/End of update loop (stage) STATEMENT 24 37 37 stage/sql/Query end (stage) STATEMENT 24 -38 38 stage/sql/Commit (stage) STATEMENT 24 -39 39 stage/sql/closing tables (stage) STATEMENT 24 -40 40 stage/sql/Starting cleanup (stage) STATEMENT 24 -41 42 stage/sql/Freeing items (stage) STATEMENT 24 -42 42 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 41 +38 39 stage/sql/Commit (stage) STATEMENT 24 +39 39 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 38 +40 40 stage/sql/closing tables (stage) STATEMENT 24 +41 41 stage/sql/Starting cleanup (stage) STATEMENT 24 +42 42 stage/sql/Freeing items (stage) STATEMENT 24 43 43 wait/io/socket/sql/client_connection send STATEMENT 24 44 45 stage/sql/Reset for next command (stage) STATEMENT 24 45 45 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 44 @@ -176,11 +176,11 @@ relative_event_id relative_end_event_id event_name comment nesting_event_type re 59 59 stage/sql/Executing (stage) STATEMENT 48 60 60 stage/sql/End of update loop (stage) STATEMENT 48 61 61 stage/sql/Query end (stage) STATEMENT 48 -62 62 stage/sql/Commit (stage) STATEMENT 48 -63 63 stage/sql/closing tables (stage) STATEMENT 48 -64 64 stage/sql/Starting cleanup (stage) STATEMENT 48 -65 66 stage/sql/Freeing items (stage) STATEMENT 48 -66 66 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 65 +62 63 stage/sql/Commit (stage) STATEMENT 48 +63 63 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 62 +64 64 stage/sql/closing tables (stage) STATEMENT 48 +65 65 stage/sql/Starting cleanup (stage) STATEMENT 48 +66 66 stage/sql/Freeing items (stage) STATEMENT 48 67 67 wait/io/socket/sql/client_connection send STATEMENT 48 68 69 stage/sql/Reset for next command (stage) STATEMENT 48 69 69 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 68 @@ -203,12 +203,12 @@ select "With a third part to make things complete" as payload NULL NULL 84 84 stage/sql/Executing (stage) STATEMENT 72 85 85 stage/sql/End of update loop (stage) STATEMENT 72 86 86 stage/sql/Query end (stage) STATEMENT 72 -87 87 stage/sql/Commit (stage) STATEMENT 72 -88 88 stage/sql/closing tables (stage) STATEMENT 72 -89 89 stage/sql/Starting cleanup (stage) STATEMENT 72 -90 92 stage/sql/Freeing items (stage) STATEMENT 72 -91 91 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 90 -92 92 wait/io/socket/sql/client_connection send STAGE 90 +87 88 stage/sql/Commit (stage) STATEMENT 72 +88 88 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 87 +89 89 stage/sql/closing tables (stage) STATEMENT 72 +90 90 stage/sql/Starting cleanup (stage) STATEMENT 72 +91 92 stage/sql/Freeing items (stage) STATEMENT 72 +92 92 wait/io/socket/sql/client_connection send STAGE 91 93 110 statement/sql/select select "And this is the second part of a multi query" as payload; select "With a third part to make things complete" as payload NULL NULL 94 96 stage/sql/starting (stage) STATEMENT 93 @@ -222,12 +222,12 @@ select "With a third part to make things complete" as payload NULL NULL 102 102 stage/sql/Executing (stage) STATEMENT 93 103 103 stage/sql/End of update loop (stage) STATEMENT 93 104 104 stage/sql/Query end (stage) STATEMENT 93 -105 105 stage/sql/Commit (stage) STATEMENT 93 -106 106 stage/sql/closing tables (stage) STATEMENT 93 -107 107 stage/sql/Starting cleanup (stage) STATEMENT 93 -108 110 stage/sql/Freeing items (stage) STATEMENT 93 -109 109 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 108 -110 110 wait/io/socket/sql/client_connection send STAGE 108 +105 106 stage/sql/Commit (stage) STATEMENT 93 +106 106 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 105 +107 107 stage/sql/closing tables (stage) STATEMENT 93 +108 108 stage/sql/Starting cleanup (stage) STATEMENT 93 +109 110 stage/sql/Freeing items (stage) STATEMENT 93 +110 110 wait/io/socket/sql/client_connection send STAGE 109 111 129 statement/sql/select select "With a third part to make things complete" as payload NULL NULL 112 113 stage/sql/starting (stage) STATEMENT 111 113 113 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 112 @@ -239,11 +239,11 @@ select "With a third part to make things complete" as payload NULL NULL 119 119 stage/sql/Executing (stage) STATEMENT 111 120 120 stage/sql/End of update loop (stage) STATEMENT 111 121 121 stage/sql/Query end (stage) STATEMENT 111 -122 122 stage/sql/Commit (stage) STATEMENT 111 -123 123 stage/sql/closing tables (stage) STATEMENT 111 -124 124 stage/sql/Starting cleanup (stage) STATEMENT 111 -125 126 stage/sql/Freeing items (stage) STATEMENT 111 -126 126 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 125 +122 123 stage/sql/Commit (stage) STATEMENT 111 +123 123 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 122 +124 124 stage/sql/closing tables (stage) STATEMENT 111 +125 125 stage/sql/Starting cleanup (stage) STATEMENT 111 +126 126 stage/sql/Freeing items (stage) STATEMENT 111 127 127 wait/io/socket/sql/client_connection send STATEMENT 111 128 129 stage/sql/Reset for next command (stage) STATEMENT 111 129 129 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 128 @@ -263,11 +263,11 @@ select "With a third part to make things complete" as payload NULL NULL 143 143 stage/sql/Executing (stage) STATEMENT 132 144 144 stage/sql/End of update loop (stage) STATEMENT 132 145 145 stage/sql/Query end (stage) STATEMENT 132 -146 146 stage/sql/Commit (stage) STATEMENT 132 -147 147 stage/sql/closing tables (stage) STATEMENT 132 -148 148 stage/sql/Starting cleanup (stage) STATEMENT 132 -149 150 stage/sql/Freeing items (stage) STATEMENT 132 -150 150 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 149 +146 147 stage/sql/Commit (stage) STATEMENT 132 +147 147 wait/synch/mutex/sql/THD::LOCK_thd_kill lock STAGE 146 +148 148 stage/sql/closing tables (stage) STATEMENT 132 +149 149 stage/sql/Starting cleanup (stage) STATEMENT 132 +150 150 stage/sql/Freeing items (stage) STATEMENT 132 151 151 wait/io/socket/sql/client_connection send STATEMENT 132 152 153 stage/sql/Reset for next command (stage) STATEMENT 132 153 153 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 152 diff --git a/sql/item.cc b/sql/item.cc index c4ba402db49..c4606391d00 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3125,10 +3125,11 @@ void Item_field::set_field(Field *field_par) base_flags|= item_base_t::FIXED; if (field->table->s->tmp_table == SYSTEM_TMP_TABLE) - { any_privileges= 0; - refers_to_temp_table= true; - } + + if (field->table->s->tmp_table == SYSTEM_TMP_TABLE || + field->table->s->tmp_table == INTERNAL_TMP_TABLE) + set_refers_to_temp_table(true); } @@ -7818,7 +7819,15 @@ Item_direct_view_ref::grouping_field_transformer_for_where(THD *thd, void Item_field::print(String *str, enum_query_type query_type) { - if (!refers_to_temp_table && field && field->table->const_table && + /* + If the field refers to a constant table, print the value. + (1): But don't attempt to do that if + * the field refers to a temporary (work) table, and + * temp. tables might already have been dropped. + */ + if (!(refers_to_temp_table && // (1) + (query_type & QT_DONT_ACCESS_TMP_TABLES)) && // (1) + field && field->table->const_table && !(query_type & (QT_NO_DATA_EXPANSION | QT_VIEW_INTERNAL))) { print_value(str); @@ -7826,7 +7835,8 @@ void Item_field::print(String *str, enum_query_type query_type) } /* Item_ident doesn't have references to the underlying Field/TABLE objects, - so it's safe to use the following even for a temporary table: + so it's safe to make the following call even when the table is not + available already: */ Item_ident::print(str, query_type); } diff --git a/sql/mysqld.h b/sql/mysqld.h index 3a8710f9a85..3b5bc62638d 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -903,6 +903,10 @@ enum enum_query_type // it evaluates to. Should be used for error messages, so that they // don't reveal values. QT_NO_DATA_EXPANSION= (1 << 9), + + // The temporary tables used by the query might be freed by the time + // this print() call is made. + QT_DONT_ACCESS_TMP_TABLES= (1 << 12) }; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index a130cc53ad2..de4599b66a0 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -783,6 +783,9 @@ int close_thread_tables(THD *thd) int error= 0; DBUG_ENTER("close_thread_tables"); + if (thd->lex && thd->lex->explain) + thd->lex->explain->notify_tables_are_closed(); + THD_STAGE_INFO(thd, stage_closing_tables); #ifdef EXTRA_DEBUG diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 214c06e06c5..60d5e4e151d 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2241,9 +2241,6 @@ void THD::cleanup_after_query() thd_progress_end(this); - if (lex && lex->explain) - lex->explain->notify_item_objects_about_to_be_freed(); - /* Reset rand_used so that detection of calls to rand() will save random seeds if needed by the slave. diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index 31ba663eda6..a326a9016e2 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -39,8 +39,8 @@ const char *unit_operation_text[4]= const char *pushed_derived_text= "PUSHED DERIVED"; const char *pushed_select_text= "PUSHED SELECT"; -static void write_item(Json_writer *writer, Item *item); -static void append_item_to_str(String *out, Item *item); +static void write_item(Json_writer *writer, Item *item, bool no_tmp_tbl); +static void append_item_to_str(String *out, Item *item, bool no_tmp_tbl); Explain_query::Explain_query(THD *thd_arg, MEM_ROOT *root) : mem_root(root), upd_del_plan(nullptr), insert_plan(nullptr), @@ -157,18 +157,30 @@ void Explain_query::query_plan_ready() if (!apc_enabled) thd->apc_target.enable(); apc_enabled= true; +#ifndef DBUG_OFF + can_print_json= true; +#endif } -void Explain_query::notify_item_objects_about_to_be_freed() +void Explain_query::notify_tables_are_closed() { + /* + Disable processing of SHOW EXPLAIN|ANALYZE (because the query is about to + do cleanups which will make it difficult). + See sql_explain.h:ExplainDataStructureLifetime for details. + */ if (apc_enabled) { thd->apc_target.disable(); apc_enabled= false; +#ifndef DBUG_OFF + can_print_json= false; +#endif } } + /* Send EXPLAIN output to the client. */ @@ -184,7 +196,7 @@ int Explain_query::send_explain(THD *thd) int res= 0; if (thd->lex->explain_json) - print_explain_json(result, thd->lex->analyze_stmt); + print_explain_json(result, thd->lex->analyze_stmt, false /*is_show_cmd*/); else res= print_explain(result, lex->describe, thd->lex->analyze_stmt); @@ -227,25 +239,39 @@ int Explain_query::print_explain(select_result_sink *output, int Explain_query::print_explain_json(select_result_sink *output, bool is_analyze, + bool is_show_cmd, ulonglong query_time_in_progress_ms) { Json_writer writer; + +#ifndef DBUG_OFF + DBUG_ASSERT(can_print_json); +#endif + writer.start_object(); if (is_analyze && query_time_in_progress_ms > 0) writer.add_member("r_query_time_in_progress_ms"). add_ull(query_time_in_progress_ms); + /* + If we are printing ANALYZE FORMAT=JSON output, take into account that + query's temporary tables have already been freed. See sql_explain.h, + sql_explain.h:ExplainDataStructureLifetime for details. + */ + if (is_analyze) + is_show_cmd= true; + if (upd_del_plan) - upd_del_plan->print_explain_json(this, &writer, is_analyze); + upd_del_plan->print_explain_json(this, &writer, is_analyze, is_show_cmd); else if (insert_plan) - insert_plan->print_explain_json(this, &writer, is_analyze); + insert_plan->print_explain_json(this, &writer, is_analyze, is_show_cmd); else { /* Start printing from node with id=1 */ Explain_node *node= get_node(1); if (!node) return 1; /* No query plan */ - node->print_explain_json(this, &writer, is_analyze); + node->print_explain_json(this, &writer, is_analyze, is_show_cmd); } writer.end_object(); @@ -607,7 +633,8 @@ int Explain_union::print_explain(Explain_query *query, void Explain_union::print_explain_json(Explain_query *query, - Json_writer *writer, bool is_analyze) + Json_writer *writer, bool is_analyze, + bool no_tmp_tbl) { Json_writer_nesting_guard guard(writer); char table_name_buffer[SAFE_NAME_LEN]; @@ -650,12 +677,12 @@ void Explain_union::print_explain_json(Explain_query *query, //writer->add_member("dependent").add_str("TODO"); //writer->add_member("cacheable").add_str("TODO"); Explain_select *sel= query->get_select(union_members.at(i)); - sel->print_explain_json(query, writer, is_analyze); + sel->print_explain_json(query, writer, is_analyze, no_tmp_tbl); writer->end_object(); } writer->end_array(); - print_explain_json_for_children(query, writer, is_analyze); + print_explain_json_for_children(query, writer, is_analyze, no_tmp_tbl); writer->end_object(); // union_result writer->end_object(); // query_block @@ -713,7 +740,8 @@ bool is_connection_printable_in_json(enum Explain_node::explain_connection_type void Explain_node::print_explain_json_for_children(Explain_query *query, Json_writer *writer, - bool is_analyze) + bool is_analyze, + bool no_tmp_tbl) { Json_writer_nesting_guard guard(writer); @@ -733,7 +761,7 @@ void Explain_node::print_explain_json_for_children(Explain_query *query, } writer->start_object(); - node->print_explain_json(query, writer, is_analyze); + node->print_explain_json(query, writer, is_analyze, no_tmp_tbl); writer->end_object(); } @@ -913,7 +941,8 @@ void Explain_select::add_linkage(Json_writer *writer) } void Explain_select::print_explain_json(Explain_query *query, - Json_writer *writer, bool is_analyze) + Json_writer *writer, bool is_analyze, + bool no_tmp_tbl) { Json_writer_nesting_guard guard(writer); @@ -935,7 +964,7 @@ void Explain_select::print_explain_json(Explain_query *query, message); writer->end_object(); - print_explain_json_for_children(query, writer, is_analyze); + print_explain_json_for_children(query, writer, is_analyze, no_tmp_tbl); writer->end_object(); } else @@ -957,17 +986,17 @@ void Explain_select::print_explain_json(Explain_query *query, if (exec_const_cond) { writer->add_member("const_condition"); - write_item(writer, exec_const_cond); + write_item(writer, exec_const_cond, no_tmp_tbl); } if (outer_ref_cond) { writer->add_member("outer_ref_condition"); - write_item(writer, outer_ref_cond); + write_item(writer, outer_ref_cond, no_tmp_tbl); } if (pseudo_bits_cond) { writer->add_member("pseudo_bits_condition"); - write_item(writer, pseudo_bits_cond); + write_item(writer, pseudo_bits_cond, no_tmp_tbl); } /* we do not print HAVING which always evaluates to TRUE */ @@ -975,7 +1004,7 @@ void Explain_select::print_explain_json(Explain_query *query, { writer->add_member("having_condition"); if (likely(having)) - write_item(writer, having); + write_item(writer, having, no_tmp_tbl); else { /* Normally we should not go this branch, left just for safety */ @@ -998,7 +1027,8 @@ void Explain_select::print_explain_json(Explain_query *query, case AGGR_OP_FILESORT: { writer->add_member("filesort").start_object(); - ((Explain_aggr_filesort*)node)->print_json_members(writer, is_analyze); + auto aggr_node= (Explain_aggr_filesort*)node; + aggr_node->print_json_members(writer, is_analyze, no_tmp_tbl); break; } case AGGR_OP_REMOVE_DUPLICATES: @@ -1008,7 +1038,8 @@ void Explain_select::print_explain_json(Explain_query *query, { //TODO: make print_json_members virtual? writer->add_member("window_functions_computation").start_object(); - ((Explain_aggr_window_funcs*)node)->print_json_members(writer, is_analyze); + auto aggr_node= (Explain_aggr_window_funcs*)node; + aggr_node->print_json_members(writer, is_analyze, no_tmp_tbl); break; } default: @@ -1017,7 +1048,8 @@ void Explain_select::print_explain_json(Explain_query *query, started_objects++; } - Explain_basic_join::print_explain_json_interns(query, writer, is_analyze); + Explain_basic_join::print_explain_json_interns(query, writer, is_analyze, + no_tmp_tbl); for (;started_objects; started_objects--) writer->end_object(); @@ -1046,7 +1078,8 @@ Explain_aggr_filesort::Explain_aggr_filesort(MEM_ROOT *mem_root, void Explain_aggr_filesort::print_json_members(Json_writer *writer, - bool is_analyze) + bool is_analyze, + bool no_tmp_tbl) { char item_buf[256]; String str(item_buf, sizeof(item_buf), &my_charset_bin); @@ -1066,7 +1099,7 @@ void Explain_aggr_filesort::print_json_members(Json_writer *writer, { str.append(STRING_WITH_LEN(", ")); } - append_item_to_str(&str, item); + append_item_to_str(&str, item, no_tmp_tbl); if (*direction == ORDER::ORDER_DESC) str.append(STRING_WITH_LEN(" desc")); } @@ -1079,7 +1112,8 @@ void Explain_aggr_filesort::print_json_members(Json_writer *writer, void Explain_aggr_window_funcs::print_json_members(Json_writer *writer, - bool is_analyze) + bool is_analyze, + bool no_tmp_tbl) { Explain_aggr_filesort *srt; List_iterator<Explain_aggr_filesort> it(sorts); @@ -1088,19 +1122,19 @@ void Explain_aggr_window_funcs::print_json_members(Json_writer *writer, { Json_writer_object sort(writer); Json_writer_object filesort(writer, "filesort"); - srt->print_json_members(writer, is_analyze); + srt->print_json_members(writer, is_analyze, no_tmp_tbl); } } void Explain_basic_join::print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze) + bool is_analyze, bool no_tmp_tbl) { writer->add_member("query_block").start_object(); writer->add_member("select_id").add_ll(select_id); - print_explain_json_interns(query, writer, is_analyze); + print_explain_json_interns(query, writer, is_analyze, no_tmp_tbl); writer->end_object(); } @@ -1109,7 +1143,7 @@ void Explain_basic_join::print_explain_json(Explain_query *query, void Explain_basic_join:: print_explain_json_interns(Explain_query *query, Json_writer *writer, - bool is_analyze) + bool is_analyze, bool no_tmp_tbl) { { Json_writer_array loop(writer, "nested_loop"); @@ -1122,7 +1156,7 @@ print_explain_json_interns(Explain_query *query, writer->start_array(); } - join_tabs[i]->print_explain_json(query, writer, is_analyze); + join_tabs[i]->print_explain_json(query, writer, is_analyze, no_tmp_tbl); if (join_tabs[i]->end_dups_weedout) { @@ -1131,7 +1165,7 @@ print_explain_json_interns(Explain_query *query, } } } // "nested_loop" - print_explain_json_for_children(query, writer, is_analyze); + print_explain_json_for_children(query, writer, is_analyze, no_tmp_tbl); } @@ -1531,7 +1565,7 @@ const char *String_list::append_str(MEM_ROOT *mem_root, const char *str) } -static void write_item(Json_writer *writer, Item *item) +static void write_item(Json_writer *writer, Item *item, bool no_tmp_tbl) { THD *thd= current_thd; char item_buf[256]; @@ -1541,23 +1575,27 @@ static void write_item(Json_writer *writer, Item *item) ulonglong save_option_bits= thd->variables.option_bits; thd->variables.option_bits &= ~OPTION_QUOTE_SHOW_CREATE; - item->print(&str, QT_EXPLAIN); + auto qtype= QT_EXPLAIN | (no_tmp_tbl? QT_DONT_ACCESS_TMP_TABLES : 0); + item->print(&str, (enum_query_type)qtype); thd->variables.option_bits= save_option_bits; writer->add_str(str.c_ptr_safe()); } -static void append_item_to_str(String *out, Item *item) +static void append_item_to_str(String *out, Item *item, bool no_tmp_tbl) { THD *thd= current_thd; ulonglong save_option_bits= thd->variables.option_bits; thd->variables.option_bits &= ~OPTION_QUOTE_SHOW_CREATE; - item->print(out, QT_EXPLAIN); + auto qtype= QT_EXPLAIN | (no_tmp_tbl? QT_DONT_ACCESS_TMP_TABLES : 0); + item->print(out, (enum_query_type)qtype); thd->variables.option_bits= save_option_bits; } -void Explain_table_access::tag_to_json(Json_writer *writer, enum explain_extra_tag tag) +void Explain_table_access::tag_to_json(Json_writer *writer, + enum explain_extra_tag tag, + bool no_tmp_tbl) { switch (tag) { @@ -1581,11 +1619,11 @@ void Explain_table_access::tag_to_json(Json_writer *writer, enum explain_extra_t break; case ET_USING_INDEX_CONDITION: writer->add_member("index_condition"); - write_item(writer, pushed_index_cond); + write_item(writer, pushed_index_cond, no_tmp_tbl); break; case ET_USING_INDEX_CONDITION_BKA: writer->add_member("index_condition_bka"); - write_item(writer, pushed_index_cond); + write_item(writer, pushed_index_cond, no_tmp_tbl); break; case ET_USING_WHERE: { @@ -1599,7 +1637,7 @@ void Explain_table_access::tag_to_json(Json_writer *writer, enum explain_extra_t if (item) { writer->add_member("attached_condition"); - write_item(writer, item); + write_item(writer, item, no_tmp_tbl); } } break; @@ -1712,7 +1750,7 @@ void Explain_rowid_filter::print_explain_json(Explain_query *query, void Explain_table_access::print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze) + bool is_analyze, bool no_tmp_tbl) { Json_writer_object jsobj(writer); @@ -1743,7 +1781,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, } } writer->add_member("filesort").start_object(); - pre_join_sort->print_json_members(writer, is_analyze); + pre_join_sort->print_json_members(writer, is_analyze, no_tmp_tbl); } if (bka_type.is_using_jbuf()) @@ -1880,7 +1918,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, for (int i=0; i < (int)extra_tags.elements(); i++) { - tag_to_json(writer, extra_tags.at(i)); + tag_to_json(writer, extra_tags.at(i), no_tmp_tbl); } if (full_scan_on_null_key) @@ -1901,7 +1939,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, if (where_cond) { writer->add_member("attached_condition"); - write_item(writer, where_cond); + write_item(writer, where_cond, no_tmp_tbl); } if (is_analyze) @@ -1925,7 +1963,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, { writer->add_member("lateral").add_ll(1); } - node->print_explain_json(query, writer, is_analyze); + node->print_explain_json(query, writer, is_analyze, no_tmp_tbl); writer->end_object(); } if (non_merged_sjm_number) @@ -1935,7 +1973,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, writer->add_member("unique").add_ll(1); Explain_node *node= query->get_node(non_merged_sjm_number); node->connection_type= Explain_node::EXPLAIN_NODE_NON_MERGED_SJ; - node->print_explain_json(query, writer, is_analyze); + node->print_explain_json(query, writer, is_analyze, no_tmp_tbl); writer->end_object(); } if (sjm_nest) @@ -1943,7 +1981,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, /* This is a non-merged semi-join table. Print its contents here */ writer->add_member("materialized").start_object(); writer->add_member("unique").add_ll(1); - sjm_nest->print_explain_json(query, writer, is_analyze); + sjm_nest->print_explain_json(query, writer, is_analyze, no_tmp_tbl); writer->end_object(); } @@ -2249,7 +2287,8 @@ int Explain_delete::print_explain(Explain_query *query, void Explain_delete::print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze) + bool is_analyze, + bool no_tmp_tbl) { Json_writer_nesting_guard guard(writer); @@ -2264,7 +2303,7 @@ void Explain_delete::print_explain_json(Explain_query *query, writer->end_object(); // query_block return; } - Explain_update::print_explain_json(query, writer, is_analyze); + Explain_update::print_explain_json(query, writer, is_analyze, no_tmp_tbl); } @@ -2367,7 +2406,8 @@ int Explain_update::print_explain(Explain_query *query, void Explain_update::print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze) + bool is_analyze, + bool no_tmp_tbl) { Json_writer_nesting_guard guard(writer); @@ -2532,7 +2572,7 @@ void Explain_update::print_explain_json(Explain_query *query, if (where_cond) { writer->add_member("attached_condition"); - write_item(writer, where_cond); + write_item(writer, where_cond, no_tmp_tbl); } /*** The part of plan that is before the buffering/sorting ends here ***/ @@ -2544,7 +2584,7 @@ void Explain_update::print_explain_json(Explain_query *query, writer->end_object(); // table - print_explain_json_for_children(query, writer, is_analyze); + print_explain_json_for_children(query, writer, is_analyze, no_tmp_tbl); writer->end_object(); // query_block } @@ -2574,7 +2614,8 @@ int Explain_insert::print_explain(Explain_query *query, } void Explain_insert::print_explain_json(Explain_query *query, - Json_writer *writer, bool is_analyze) + Json_writer *writer, bool is_analyze, + bool no_tmp_tbl) { Json_writer_nesting_guard guard(writer); @@ -2583,7 +2624,7 @@ void Explain_insert::print_explain_json(Explain_query *query, writer->add_member("table").start_object(); writer->add_member("table_name").add_str(table_name.c_ptr()); writer->end_object(); // table - print_explain_json_for_children(query, writer, is_analyze); + print_explain_json_for_children(query, writer, is_analyze, no_tmp_tbl); writer->end_object(); // query_block } diff --git a/sql/sql_explain.h b/sql/sql_explain.h index f00d6bcf029..983cc820e9e 100644 --- a/sql/sql_explain.h +++ b/sql/sql_explain.h @@ -27,9 +27,8 @@ Query optimization produces two data structures: produce output of SHOW EXPLAIN, EXPLAIN [FORMAT=JSON], or ANALYZE [FORMAT=JSON], without accessing the execution data structures. -(the only exception is that Explain data structures keep Item* pointers, -and we require that one might call item->print(QT_EXPLAIN) when printing -FORMAT=JSON output) +The exception is that Explain data structures have Item* pointers. See +ExplainDataStructureLifetime below for details. === ANALYZE data === EXPLAIN data structures have embedded ANALYZE data structures. These are @@ -135,12 +134,13 @@ class Explain_node : public Sql_alloc virtual int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze)=0; virtual void print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze)= 0; + bool is_analyze, bool no_tmp_tbl)= 0; int print_explain_for_children(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); void print_explain_json_for_children(Explain_query *query, - Json_writer *writer, bool is_analyze); + Json_writer *writer, bool is_analyze, + bool no_tmp_tbl); bool print_explain_json_cache(Json_writer *writer, bool is_analyze); virtual ~Explain_node(){} }; @@ -174,10 +174,10 @@ class Explain_basic_join : public Explain_node int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); void print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze); + bool is_analyze, bool no_tmp_tbl); void print_explain_json_interns(Explain_query *query, Json_writer *writer, - bool is_analyze); + bool is_analyze, bool no_tmp_tbl); /* A flat array of Explain structs for tables. */ Explain_table_access** join_tabs; @@ -261,7 +261,7 @@ class Explain_select : public Explain_basic_join int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); void print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze); + bool is_analyze, bool no_tmp_tbl); Table_access_tracker *get_using_temporary_read_tracker() { @@ -304,7 +304,8 @@ class Explain_aggr_filesort : public Explain_aggr_node Explain_aggr_filesort(MEM_ROOT *mem_root, bool is_analyze, Filesort *filesort); - void print_json_members(Json_writer *writer, bool is_analyze); + void print_json_members(Json_writer *writer, bool is_analyze, + bool no_tmp_tbl); }; class Explain_aggr_tmp_table : public Explain_aggr_node @@ -325,7 +326,8 @@ class Explain_aggr_window_funcs : public Explain_aggr_node public: enum_explain_aggr_node_type get_type() { return AGGR_OP_WINDOW_FUNCS; } - void print_json_members(Json_writer *writer, bool is_analyze); + void print_json_members(Json_writer *writer, bool is_analyze, + bool no_tmp_tbl); friend class Window_funcs_computation; }; @@ -378,7 +380,7 @@ class Explain_union : public Explain_node int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); void print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze); + bool is_analyze, bool no_tmp_tbl); const char *fake_select_type; bool using_filesort; @@ -417,36 +419,54 @@ class Explain_insert; /* Explain structure for a query (i.e. a statement). - This should be able to survive when the query plan was deleted. Currently, - we do not intend for it survive until after query's MEM_ROOT is freed. It - does surivive freeing of query's items. - - For reference, the process of post-query cleanup is as follows: + This should be able to survive when the query plan was deleted. Currently, + we do not intend for it survive until after query's MEM_ROOT is freed. + + == ExplainDataStructureLifetime == >dispatch_command | >mysql_parse - | | ... - | | lex_end() - | | ... - | | >THD::cleanup_after_query - | | | ... - | | | free_items() - | | | ... - | | <THD::cleanup_after_query + | | ... + | | + | | explain->query_plan_ready(); // (1) + | | + | | some_join->cleanup(); // (2) + | | + | | explain->notify_tables_are_closed(); // (3) + | | close_thread_tables(); // (4) + | | ... + | | free_items(); // (5) + | | ... | | | <mysql_parse | - | log_slow_statement() - | + | log_slow_statement() // (6) + | | free_root() - | + | >dispatch_command - - That is, the order of actions is: - - free query's Items - - write to slow query log - - free query's MEM_ROOT - + + (1) - Query plan construction is finished and it is available for reading. + + (2) - Temporary tables are freed. After this point, + we need to pass QT_DONT_ACCESS_TMP_TABLES to item->print(). Since + we don't track when #2 happens for each temp.table, we pass this + flag whenever we're printing the query plan for a SHOW command. + Also, we pass it when printing ANALYZE (?) + + (3) - Notification about (4). + (4) - Tables used by the query are closed. One known consequence of this is + that the values of the const tables' fields are not available anymore. + We could use the same approach as in QT_DONT_ACCESS_TMP_TABLES to work + around that, but instead we disallow producing FORMAT=JSON output at + step #3. We also processing of SHOW command. The rationale is that + query is close to finish anyway. + + (5) - Item objects are freed. After this, it's certainly not possible to + print them into FORMAT=JSON output. + + (6) - We may decide to log tabular EXPLAIN output to the slow query log. + */ class Explain_query : public Sql_alloc @@ -479,14 +499,14 @@ class Explain_query : public Sql_alloc bool print_explain_str(THD *thd, String *out_str, bool is_analyze); int print_explain_json(select_result_sink *output, bool is_analyze, + bool is_show_cmd, ulonglong query_time_in_progress_ms= 0); /* If true, at least part of EXPLAIN can be printed */ bool have_query_plan() { return insert_plan || upd_del_plan|| get_node(1) != NULL; } void query_plan_ready(); - - void notify_item_objects_about_to_be_freed(); + void notify_tables_are_closed(); MEM_ROOT *mem_root; @@ -510,6 +530,9 @@ class Explain_query : public Sql_alloc is unacceptable. */ longlong operations; +#ifndef DBUG_OFF + bool can_print_json= false; +#endif }; @@ -853,14 +876,15 @@ class Explain_table_access : public Sql_alloc uint select_id, const char *select_type, bool using_temporary, bool using_filesort); void print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze); + bool is_analyze, bool no_tmp_tbl); private: void append_tag_name(String *str, enum explain_extra_tag tag); void fill_key_str(String *key_str, bool is_json) const; void fill_key_len_str(String *key_len_str, bool is_json) const; double get_r_filtered(); - void tag_to_json(Json_writer *writer, enum explain_extra_tag tag); + void tag_to_json(Json_writer *writer, enum explain_extra_tag tag, + bool no_tmp_tbl); }; @@ -943,7 +967,7 @@ class Explain_update : public Explain_node virtual int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); virtual void print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze); + bool is_analyze, bool no_tmp_tbl); }; @@ -969,7 +993,7 @@ class Explain_insert : public Explain_node int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); void print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze); + bool is_analyze, bool no_tmp_tbl); }; @@ -996,7 +1020,7 @@ class Explain_delete: public Explain_update virtual int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); virtual void print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze); + bool is_analyze, bool no_tmp_tbl); }; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 58aae66cda1..77b5483b772 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -5834,7 +5834,7 @@ bool st_select_lex::is_merged_child_of(st_select_lex *ancestor) } /* - This is used by SHOW EXPLAIN. It assuses query plan has been already + This is used by SHOW EXPLAIN|ANALYZE. It assumes query plan has been already collected into QPF structures and we only need to print it out. */ @@ -5854,6 +5854,7 @@ int LEX::print_explain(select_result_sink *output, uint8 explain_flags, query_time_in_progress_ms= (now - start_time) / (HRTIME_RESOLUTION / 1000); res= explain->print_explain_json(output, is_analyze, + true /* is_show_cmd */, query_time_in_progress_ms); } else diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index d6a15f340d8..00f357de0f3 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -6207,7 +6207,8 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables) result->remove_offset_limit(); if (lex->explain_json) { - lex->explain->print_explain_json(result, lex->analyze_stmt); + lex->explain->print_explain_json(result, lex->analyze_stmt, + false /* is_show_cmd */); } else { diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 7be12ccd911..9f718203a6c 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2999,14 +2999,24 @@ void Show_explain_request::call_in_target_thread() target_thd->query_charset()); DBUG_ASSERT(current_thd == target_thd); - set_current_thd(request_thd); + + /* + When producing JSON output, one should not change current_thd. + (If one does that, they will hit an assert when printing constant item + fields. + */ + if (!is_json_format) + set_current_thd(request_thd); + if (target_thd->lex->print_explain(explain_buf, 0 /* explain flags*/, is_analyze, is_json_format, &printed_anything)) { failed_to_produce= TRUE; } - set_current_thd(target_thd); + + if (!is_json_format) + set_current_thd(target_thd); if (!printed_anything) failed_to_produce= TRUE;

1 0

[Commits] f50820f: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list
by IgorBabaev 25 Mar '22

25 Mar '22

revision-id: f50820f4b0b00b5a2abf55281c8bbfa9714777d7 (mariadb-10.3.26-366-gf50820f) parent(s): bbf02c85ba2e850da546199421cb75c224747475 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-25 11:04:56 -0700 message: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list This bug affected queries with IN predicates that contain parameter markers in the value list. Such queries are executed via prepared statements. The problem appeared only if the number of elements in the value list was greater than the set value of the system variable in_predicate_conversion_threshold. The patch unconditionally prohibits conversion of an IN predicate to the equivalent IN predicand if the value list of the IN predicate contains parameters markers. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/opt_tvc.result | 27 +++++++++++++++++++++++++++ mysql-test/main/opt_tvc.test | 26 ++++++++++++++++++++++++++ sql/item_cmpfunc.cc | 7 ++++--- sql/item_cmpfunc.h | 2 ++ sql/sql_tvc.cc | 27 ++++++++++++++++++++++++--- 5 files changed, 83 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index a68e70e..02d9096 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -732,3 +732,30 @@ a b 4 4 drop table t1; SET @@in_predicate_conversion_threshold= default; +# +# MDEV-27937: Prepared statement with ? in the list if IN predicate +# +set in_predicate_conversion_threshold=2; +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +drop table t1; +set in_predicate_conversion_threshold=default; +# End of 10.3 tests diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index e4e8c6d..f8469f2 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -428,3 +428,29 @@ eval $query; drop table t1; SET @@in_predicate_conversion_threshold= default; +--echo # +--echo # MDEV-27937: Prepared statement with ? in the list if IN predicate +--echo # + +set in_predicate_conversion_threshold=2; + +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); + +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +deallocate prepare stmt; + +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +deallocate prepare stmt; + +drop table t1; + +set in_predicate_conversion_threshold=default; + +--echo # End of 10.3 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 38f0a28..f41414f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4472,10 +4472,11 @@ void Item_func_in::mark_as_condition_AND_part(TABLE_LIST *embedding) Query_arena *arena, backup; arena= thd->activate_stmt_arena_if_needed(&backup); - if (to_be_transformed_into_in_subq(thd)) + if (!transform_into_subq_checked) { - transform_into_subq= true; - thd->lex->current_select->in_funcs.push_back(this, thd->mem_root); + if ((transform_into_subq= to_be_transformed_into_in_subq(thd))) + thd->lex->current_select->in_funcs.push_back(this, thd->mem_root); + transform_into_subq_checked= true; } if (arena) diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 4c88f5b..f3d3be4 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2299,6 +2299,7 @@ class Item_func_in :public Item_func_opt_neg, SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Field *field, Item *value); bool transform_into_subq; + bool transform_into_subq_checked; public: /// An array of values, created when the bisection lookup method is used in_vector *array; @@ -2321,6 +2322,7 @@ class Item_func_in :public Item_func_opt_neg, Item_func_opt_neg(thd, list), Predicant_to_list_comparator(thd, arg_count - 1), transform_into_subq(false), + transform_into_subq_checked(false), array(0), have_null(0), arg_types_compatible(FALSE), emb_on_expr_nest(0) { } diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 3866b7c..13efd97 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -900,8 +900,6 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, if (!transform_into_subq) return this; - transform_into_subq= false; - List<List_item> values; LEX *lex= thd->lex; @@ -1058,15 +1056,38 @@ uint32 Item_func_in::max_length_of_left_expr() bool Item_func_in::to_be_transformed_into_in_subq(THD *thd) { + bool is_row_list= args[1]->type() == Item::ROW_ITEM; uint values_count= arg_count-1; - if (args[1]->type() == Item::ROW_ITEM) + if (is_row_list) values_count*= ((Item_row *)(args[1]))->cols(); if (thd->variables.in_subquery_conversion_threshold == 0 || thd->variables.in_subquery_conversion_threshold > values_count) return false; + if (!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE)) + return true; + + /* Occurence of '?' in IN list is checked only for PREPARE <stmt> commands */ + for (uint i=1; i < arg_count; i++) + { + if (!is_row_list) + { + if (args[i]->type() == Item::PARAM_ITEM) + return false; + } + else + { + Item_row *row_list= (Item_row *)(args[i]); + for (uint j=0; j < row_list->cols(); j++) + { + if (row_list->element_index(j)->type() == Item::PARAM_ITEM) + return false; + } + } + } + return true; }

1 0

[Commits] 2504263: MDEV-27159 Re-design the upper level of handling DML commands
by IgorBabaev 25 Mar '22

25 Mar '22

revision-id: 2504263fb920964b8ccaa2b19d8f48b8bddd0188 (mariadb-10.6.1-284-g2504263) parent(s): fc0842269bf1119aaee1cb49da9fb4b3167a61e2 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-25 10:05:00 -0700 message: MDEV-27159 Re-design the upper level of handling DML commands This is the second commit for the task. This patch allows to execute only single-table and multi-table DELETE statements using the method Sql_cmd_dml::execute(). The code that handles INSERT statements has not been touched. This patch still does not have the final changes to handle UPDATE/DELETE statements. All tests from the main suite passed. With --ps-protocol one test from opt_trace_security returns not the same result. This will be fixed soon. --- extra/wolfssl/wolfssl | 2 +- mysql-test/main/opt_trace.result | 14 +- sql/opt_range.cc | 2 +- sql/sql_base.cc | 7 +- sql/sql_cmd.h | 9 +- sql/sql_delete.cc | 577 ++++++++++++++++++++------------------- sql/sql_delete.h | 9 + sql/sql_lex.cc | 30 +- sql/sql_parse.cc | 133 +-------- sql/sql_parse.h | 1 + sql/sql_prepare.cc | 102 +------ sql/sql_select.cc | 1 - sql/sql_update.cc | 6 +- sql/sql_yacc.yy | 63 ++--- sql/table.h | 1 + 15 files changed, 374 insertions(+), 583 deletions(-) diff --git a/extra/wolfssl/wolfssl b/extra/wolfssl/wolfssl index c3513bf..9c87f97 160000 --- a/extra/wolfssl/wolfssl +++ b/extra/wolfssl/wolfssl @@ -1 +1 @@ -Subproject commit c3513bf2573c30f6d2df815de216120e92142020 +Subproject commit 9c87f979a7f1d3a6d786b260653d566c1d31a1c4 diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 044db82..1444320 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -3742,6 +3742,16 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain delete from t0 where t0.a<3 { "steps": [ { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select from dual where t0.a < 3" + } + ] + } + }, + { "table": "t0", "range_analysis": { "table_scan": { @@ -3773,7 +3783,7 @@ explain delete from t0 where t0.a<3 { }, "group_index_range": { "chosen": false, - "cause": "no join" + "cause": "no group by or distinct" }, "chosen_range_access_summary": { "range_access_plan": { @@ -3816,7 +3826,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "select_id": 1, "steps": [ { - "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3" + "expanded_query": "select from t0 join t1 where t0.a = t1.a and t1.a < 3" } ] } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 7909f5b..13457d7 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -11589,7 +11589,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, Skip materialized derived table/view result table from MRR check as they aren't contain any data yet. */ - if (param->table->pos_in_table_list->is_non_derived()) + if (!param->table->pos_in_table_list->is_materialized_derived()) rows= file->multi_range_read_info_const(keynr, &seq_if, (void*)&seq, 0, bufsize, mrr_flags, cost); param->quick_rows[keynr]= rows; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index a170aa0..3feeed5 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1069,7 +1069,9 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, */ if (table->table && thd->lex->sql_command != SQLCOM_UPDATE && - thd->lex->sql_command != SQLCOM_UPDATE_MULTI) + thd->lex->sql_command != SQLCOM_UPDATE_MULTI && + thd->lex->sql_command != SQLCOM_DELETE && + thd->lex->sql_command != SQLCOM_DELETE_MULTI) { /* All MyISAMMRG children are plain MyISAM tables. */ DBUG_ASSERT(table->table->file->ht->db_type != DB_TYPE_MRG_MYISAM); @@ -7609,6 +7611,9 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, if (!select_lex->with_wild) DBUG_RETURN(0); + if (!fields.elements) + DBUG_RETURN(0); + /* Don't use arena if we are not in prepared statements or stored procedures For PS/SP we have to use arena to remember the changes diff --git a/sql/sql_cmd.h b/sql/sql_cmd.h index c62fe83..2c069cf 100644 --- a/sql/sql_cmd.h +++ b/sql/sql_cmd.h @@ -262,6 +262,7 @@ class LEX; class select_result; class Prelocking_strategy; class DML_prelocking_strategy; +class Protocol; class Sql_cmd_dml : public Sql_cmd { @@ -289,7 +290,9 @@ class Sql_cmd_dml : public Sql_cmd protected: Sql_cmd_dml() - : Sql_cmd(), lex(nullptr), result(nullptr), m_empty_query(false) {} + : Sql_cmd(), lex(nullptr), result(nullptr), + m_empty_query(false), save_protocol(NULL) + {} /// @return true if query is guaranteed to return no data /** @@ -347,12 +350,14 @@ class Sql_cmd_dml : public Sql_cmd virtual DML_prelocking_strategy *get_dml_prelocking_strategy() = 0; - uint table_count; + uint table_count; protected: LEX *lex; ///< Pointer to LEX for this statement select_result *result; ///< Pointer to object for handling of the result bool m_empty_query; ///< True if query will produce no rows + List<Item> empty_list; + Protocol *save_protocol; }; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 95adf17..7ac4797 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -103,7 +103,7 @@ bool Update_plan::save_explain_data_intern(MEM_ROOT *mem_root, bool is_analyze) { explain->select_type= "SIMPLE"; - explain->table_name.append(&table->pos_in_table_list->alias); + explain->table_name.append(table->alias); explain->impossible_where= false; explain->no_partitions= false; @@ -294,124 +294,79 @@ int TABLE::delete_row() } -/** - Implement DELETE SQL word. - - @note Like implementations of other DDL/DML in MySQL, this function - relies on the caller to close the thread tables. This is done in the - end of dispatch_command(). -*/ - -bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, - SQL_I_List<ORDER> *order_list, ha_rows limit, - ulonglong options, select_result *result) +bool Sql_cmd_delete::delete_from_single_table(THD *thd) { - bool will_batch= FALSE; - int error, loc_error; - TABLE *table; - SQL_SELECT *select=0; - SORT_INFO *file_sort= 0; - READ_RECORD info; - bool using_limit=limit != HA_POS_ERROR; - bool transactional_table, safe_update, const_cond; - bool const_cond_result; - bool return_error= 0; - ha_rows deleted= 0; - bool reverse= FALSE; - bool has_triggers= false; - ORDER *order= (ORDER *) ((order_list && order_list->elements) ? - order_list->first : NULL); - SELECT_LEX *select_lex= thd->lex->first_select_lex(); - SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + int error; + int loc_error; + bool transactional_table; + bool const_cond; + bool safe_update; + bool const_cond_result; + bool return_error= 0; + TABLE *table; + SQL_SELECT *select= 0; + SORT_INFO *file_sort= 0; + READ_RECORD info; + ha_rows deleted= 0; + bool reverse= FALSE; + bool binlog_is_row; killed_state killed_status= NOT_KILLED; THD::enum_binlog_query_type query_type= THD::ROW_QUERY_TYPE; - bool binlog_is_row; - Explain_delete *explain; + bool will_batch= FALSE; + + bool has_triggers= false; + SELECT_LEX_UNIT *unit = &lex->unit; + SELECT_LEX *select_lex= unit->first_select(); + SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + TABLE_LIST *const table_list = select_lex->get_table_list(); + ulonglong options= select_lex->options; + ORDER *order= select_lex->order_list.first; + COND *conds= select_lex->join->conds; + ha_rows limit= unit->lim.get_select_limit(); + bool using_limit= limit != HA_POS_ERROR; + Delete_plan query_plan(thd->mem_root); + Explain_delete *explain; Unique * deltempfile= NULL; bool delete_record= false; - bool delete_while_scanning; + bool delete_while_scanning= table_list->delete_while_scanning; bool portion_of_time_through_update; - DBUG_ENTER("mysql_delete"); + + DBUG_ENTER("Sql_cmd_delete::delete_single_table"); query_plan.index= MAX_KEY; query_plan.using_filesort= FALSE; - create_explain_query(thd->lex, thd->mem_root); - if (open_and_lock_tables(thd, table_list, TRUE, 0)) - DBUG_RETURN(TRUE); - THD_STAGE_INFO(thd, stage_init_update); + create_explain_query(thd->lex, thd->mem_root); const bool delete_history= table_list->vers_conditions.delete_history; DBUG_ASSERT(!(delete_history && table_list->period_conditions.is_set())); - if (thd->lex->handle_list_of_derived(table_list, DT_MERGE_FOR_INSERT)) - DBUG_RETURN(TRUE); - if (thd->lex->handle_list_of_derived(table_list, DT_PREPARE)) - DBUG_RETURN(TRUE); + if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(1); + if (table_list->handle_derived(thd->lex, DT_PREPARE)) + DBUG_RETURN(1); + + table= table_list->table; if (!table_list->single_table_updatable()) { my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); DBUG_RETURN(TRUE); } - if (!(table= table_list->table) || !table->is_created()) + + if (!table || !table->is_created()) { my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), table_list->view_db.str, table_list->view_name.str); DBUG_RETURN(TRUE); } - table->map=1; + query_plan.select_lex= thd->lex->first_select_lex(); query_plan.table= table; - thd->lex->promote_select_describe_flag_if_needed(); - if (mysql_prepare_delete(thd, table_list, &conds, &delete_while_scanning)) - DBUG_RETURN(TRUE); - - if (table_list->has_period()) - { - if (!table_list->period_conditions.start.item->const_item() - || !table_list->period_conditions.end.item->const_item()) - { - my_error(ER_NOT_CONSTANT_EXPRESSION, MYF(0), "FOR PORTION OF"); - DBUG_RETURN(true); - } - } - - if (delete_history) - table->vers_write= false; - - if (returning) - (void) result->prepare(returning->item_list, NULL); - - if (thd->lex->current_select->first_cond_optimization) - { - thd->lex->current_select->save_leaf_tables(thd); - thd->lex->current_select->first_cond_optimization= 0; - } - /* check ORDER BY even if it can be ignored */ - if (order) - { - TABLE_LIST tables; - List<Item> fields; - List<Item> all_fields; - - bzero((char*) &tables,sizeof(tables)); - tables.table = table; - tables.alias = table_list->alias; - - if (select_lex->setup_ref_array(thd, order_list->elements) || - setup_order(thd, select_lex->ref_pointer_array, &tables, - fields, all_fields, order)) - { - free_underlaid_joins(thd, thd->lex->first_select_lex()); - DBUG_RETURN(TRUE); - } - } - /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */ if (select_lex->optimize_unflattened_subqueries(false)) DBUG_RETURN(TRUE); @@ -519,7 +474,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, table->covering_keys.clear_all(); table->opt_range_keys.clear_all(); - select=make_select(table, 0, 0, conds, (SORT_INFO*) 0, 0, &error); + select= make_select(table, 0, 0, conds, (SORT_INFO*) 0, 0, &error); if (unlikely(error)) DBUG_RETURN(TRUE); if ((select && select->check_quick(thd, safe_update, limit)) || !limit) @@ -953,7 +908,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, } } DBUG_ASSERT(transactional_table || !deleted || thd->transaction->stmt.modified_non_trans_table); - + if (likely(error < 0) || (thd->lex->ignore && !thd->is_error() && !thd->is_fatal_error)) { @@ -1003,90 +958,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, } -/* - Prepare items in DELETE statement - - SYNOPSIS - mysql_prepare_delete() - thd - thread handler - table_list - global/local table list - conds - conditions - - RETURN VALUE - FALSE OK - TRUE error -*/ -int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds, - bool *delete_while_scanning) -{ - Item *fake_conds= 0; - SELECT_LEX *select_lex= thd->lex->first_select_lex(); - DBUG_ENTER("mysql_prepare_delete"); - List<Item> all_fields; - - *delete_while_scanning= true; - thd->lex->allow_sum_func.clear_all(); - if (setup_tables_and_check_access(thd, &select_lex->context, - &select_lex->top_join_list, table_list, - select_lex->leaf_tables, FALSE, - DELETE_ACL, SELECT_ACL, TRUE)) - DBUG_RETURN(TRUE); - - if (table_list->vers_conditions.is_set() && table_list->is_view_or_derived()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(true); - } - - if (table_list->has_period()) - { - if (table_list->is_view_or_derived()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(true); - } - - if (select_lex->period_setup_conds(thd, table_list)) - DBUG_RETURN(true); - } - - DBUG_ASSERT(table_list->table); - // conds could be cached from previous SP call - DBUG_ASSERT(!table_list->vers_conditions.need_setup() || - !*conds || thd->stmt_arena->is_stmt_execute()); - if (select_lex->vers_setup_conds(thd, table_list)) - DBUG_RETURN(TRUE); - - *conds= select_lex->where; - - if (setup_returning_fields(thd, table_list) || - setup_conds(thd, table_list, select_lex->leaf_tables, conds) || - setup_ftfuncs(select_lex)) - DBUG_RETURN(TRUE); - if (!table_list->single_table_updatable() || - check_key_in_view(thd, table_list)) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); - DBUG_RETURN(TRUE); - } - - /* - Application-time periods: if FOR PORTION OF ... syntax used, DELETE - statement could issue delete_row's mixed with write_row's. This causes - problems for myisam and corrupts table, if deleting while scanning. - */ - if (table_list->has_period() - || unique_table(thd, table_list, table_list->next_global, 0)) - *delete_while_scanning= false; - - if (select_lex->inner_refs_list.elements && - fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array)) - DBUG_RETURN(TRUE); - - select_lex->fix_prepare_information(thd, conds, &fake_conds); - DBUG_RETURN(FALSE); -} - /*************************************************************************** Delete multiple tables from join @@ -1099,106 +970,6 @@ extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b) return file->cmp_ref((const uchar*)a, (const uchar*)b); } -/* - make delete specific preparation and checks after opening tables - - SYNOPSIS - mysql_multi_delete_prepare() - thd thread handler - - RETURN - FALSE OK - TRUE Error -*/ - -int mysql_multi_delete_prepare(THD *thd) -{ - LEX *lex= thd->lex; - TABLE_LIST *aux_tables= lex->auxiliary_table_list.first; - TABLE_LIST *target_tbl; - DBUG_ENTER("mysql_multi_delete_prepare"); - - if (mysql_handle_derived(lex, DT_INIT)) - DBUG_RETURN(TRUE); - if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT)) - DBUG_RETURN(TRUE); - if (mysql_handle_derived(lex, DT_PREPARE)) - DBUG_RETURN(TRUE); - /* - setup_tables() need for VIEWs. JOIN::prepare() will not do it second - time. - - lex->query_tables also point on local list of DELETE SELECT_LEX - */ - if (setup_tables_and_check_access(thd, - &thd->lex->first_select_lex()->context, - &thd->lex->first_select_lex()-> - top_join_list, - lex->query_tables, - lex->first_select_lex()->leaf_tables, - FALSE, DELETE_ACL, SELECT_ACL, FALSE)) - DBUG_RETURN(TRUE); - - /* - Multi-delete can't be constructed over-union => we always have - single SELECT on top and have to check underlying SELECTs of it - */ - lex->first_select_lex()->set_unique_exclude(); - /* Fix tables-to-be-deleted-from list to point at opened tables */ - for (target_tbl= (TABLE_LIST*) aux_tables; - target_tbl; - target_tbl= target_tbl->next_local) - { - - target_tbl->table= target_tbl->correspondent_table->table; - if (target_tbl->correspondent_table->is_multitable()) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - target_tbl->correspondent_table->view_db.str, - target_tbl->correspondent_table->view_name.str); - DBUG_RETURN(TRUE); - } - - if (!target_tbl->correspondent_table->single_table_updatable() || - check_key_in_view(thd, target_tbl->correspondent_table)) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), - target_tbl->table_name.str, "DELETE"); - DBUG_RETURN(TRUE); - } - } - - for (target_tbl= (TABLE_LIST*) aux_tables; - target_tbl; - target_tbl= target_tbl->next_local) - { - /* - Check that table from which we delete is not used somewhere - inside subqueries/view. - */ - { - TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, target_tbl->correspondent_table, - lex->query_tables, 0))) - { - update_non_unique_table_error(target_tbl->correspondent_table, - "DELETE", duplicate); - DBUG_RETURN(TRUE); - } - } - } - /* - Reset the exclude flag to false so it doesn't interfare - with further calls to unique_table - */ - lex->first_select_lex()->exclude_from_table_unique_test= FALSE; - - if (lex->save_prep_leaf_tables()) - DBUG_RETURN(TRUE); - - DBUG_RETURN(FALSE); -} - multi_delete::multi_delete(THD *thd_arg, TABLE_LIST *dt, uint num_of_tables_arg): select_result_interceptor(thd_arg), delete_tables(dt), deleted(0), found(0), @@ -1647,3 +1418,261 @@ bool multi_delete::send_eof() } return 0; } + + +bool Sql_cmd_delete::precheck(THD *thd) +{ + if (!multitable) + { + if (delete_precheck(thd, lex->query_tables)) + return true; + } + else + { + if (multi_delete_precheck(thd, lex->query_tables)) + return true; + } + return false; +} + + +bool Sql_cmd_delete::prepare_inner(THD *thd) +{ + int err= 0; + TABLE_LIST *target_tbl; + JOIN *join; + SELECT_LEX *const select_lex = thd->lex->first_select_lex(); + TABLE_LIST *const table_list = select_lex->get_table_list(); + TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first; + ulonglong select_options= select_lex->options; + bool free_join= 1; + SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + + DBUG_ENTER("Sql_cmd_delete::prepare_inner"); + + (void) read_statistics_for_tables_if_needed(thd, table_list); + + { + if (mysql_handle_derived(lex, DT_INIT)) + DBUG_RETURN(TRUE); + if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(TRUE); + if (mysql_handle_derived(lex, DT_PREPARE)) + DBUG_RETURN(TRUE); + } + + if (!(result= new (thd->mem_root) multi_delete(thd, aux_tables, + lex->table_count))) + { + DBUG_RETURN(TRUE); + } + + table_list->delete_while_scanning= true; + + if (setup_tables_and_check_access(thd, &select_lex->context, + &select_lex->top_join_list, + table_list, select_lex->leaf_tables, + false, DELETE_ACL, SELECT_ACL, true)) + DBUG_RETURN(TRUE); + + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, false, false)) + DBUG_RETURN(TRUE); + + if (!multitable) + { + if (table_list->vers_conditions.is_set() && table_list->is_view_or_derived()) + { + my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); + DBUG_RETURN(true); + } + + if (table_list->has_period()) + { + if (table_list->is_view_or_derived()) + { + my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); + DBUG_RETURN(true); + } + + if (select_lex->period_setup_conds(thd, table_list)) + DBUG_RETURN(true); + } + + if (select_lex->vers_setup_conds(thd, table_list)) + DBUG_RETURN(TRUE); + /* + Application-time periods: if FOR PORTION OF ... syntax used, DELETE + statement could issue delete_row's mixed with write_row's. This causes + problems for myisam and corrupts table, if deleting while scanning. + */ + if (table_list->has_period() + || unique_table(thd, table_list, table_list->next_global, 0)) + table_list->delete_while_scanning= false; + } + + if (multitable) + { + /* + Multi-delete can't be constructed over-union => we always have + single SELECT on top and have to check underlying SELECTs of it + */ + lex->first_select_lex()->set_unique_exclude(); + /* Fix tables-to-be-deleted-from list to point at opened tables */ + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { + target_tbl->table= target_tbl->correspondent_table->table; + if (target_tbl->correspondent_table->is_multitable()) + { + my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), + target_tbl->correspondent_table->view_db.str, + target_tbl->correspondent_table->view_name.str); + DBUG_RETURN(TRUE); + } + + if (!target_tbl->correspondent_table->single_table_updatable() || + check_key_in_view(thd, target_tbl->correspondent_table)) + { + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), + target_tbl->table_name.str, "DELETE"); + DBUG_RETURN(TRUE); + } + } + + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { + /* + Check that table from which we delete is not used somewhere + inside subqueries/view. + */ + { + TABLE_LIST *duplicate; + if ((duplicate= unique_table(thd, target_tbl->correspondent_table, + lex->query_tables, 0))) + { + update_non_unique_table_error(target_tbl->correspondent_table, + "DELETE", duplicate); + DBUG_RETURN(TRUE); + } + } + } + /* + Reset the exclude flag to false so it doesn't interfare + with further calls to unique_table + */ + lex->first_select_lex()->exclude_from_table_unique_test= FALSE; + } + + { + if (thd->lex->describe) + select_options|= SELECT_DESCRIBE; + + /* + When in EXPLAIN, delay deleting the joins so that they are still + available when we're producing EXPLAIN EXTENDED warning text. + */ + if (select_options & SELECT_DESCRIBE) + free_join= 0; + select_options|= + SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK | OPTION_SETUP_TABLES_DONE; + + if (!(join= new (thd->mem_root) JOIN(thd, empty_list, + select_options, result))) + DBUG_RETURN(TRUE); + THD_STAGE_INFO(thd, stage_init); + select_lex->join= join; + thd->lex->used_tables=0; + if ((err= join->prepare(table_list, select_lex->where, + select_lex->order_list.elements, + select_lex->order_list.first, + false, NULL, NULL, NULL, + select_lex, &lex->unit))) + + { + goto err; + } + + } + + + if (setup_returning_fields(thd, table_list) || + setup_ftfuncs(select_lex)) + goto err; + + free_join= false; + + if (returning) + (void) result->prepare(returning->item_list, NULL); + +err: + + if (free_join) + { + THD_STAGE_INFO(thd, stage_end); + err|= (int)(select_lex->cleanup()); + DBUG_RETURN(err || thd->is_error()); + } + DBUG_RETURN(err); + +} + +bool Sql_cmd_delete::execute_inner(THD *thd) +{ + if (!multitable) + { + if (lex->has_returning()) + { + select_result *sel_result= NULL; + delete result; + /* This is DELETE ... RETURNING. It will return output to the client */ + if (thd->lex->analyze_stmt) + { + /* + Actually, it is ANALYZE .. DELETE .. RETURNING. We need to produce + output and then discard it. + */ + sel_result= new (thd->mem_root) select_send_analyze(thd); + save_protocol= thd->protocol; + thd->protocol= new Protocol_discard(thd); + } + else + { + if (!lex->result && !(sel_result= new (thd->mem_root) select_send(thd))) + return true; + } + result= lex->result ? lex->result : sel_result; + } + } + + bool res= multitable ? Sql_cmd_dml::execute_inner(thd) + : delete_from_single_table(thd); + + res|= thd->is_error(); + + if (save_protocol) + { + delete thd->protocol; + thd->protocol= save_protocol; + } + { + if (unlikely(res)) + result->abort_result_set(); + else + { + if (thd->lex->describe || thd->lex->analyze_stmt) + res= thd->lex->explain->send_explain(thd); + } + } + + if (result) + { + res= false; + delete result; + } + + return res; +} diff --git a/sql/sql_delete.h b/sql/sql_delete.h index dabcafb..4aee510 100644 --- a/sql/sql_delete.h +++ b/sql/sql_delete.h @@ -17,6 +17,9 @@ #define SQL_DELETE_INCLUDED #include "my_base.h" /* ha_rows */ +#include "sql_class.h" /* enum_duplicates */ +#include "sql_cmd.h" // Sql_cmd_dml +#include "sql_base.h" class THD; struct TABLE_LIST; @@ -43,6 +46,11 @@ class Sql_cmd_delete final : public Sql_cmd_dml return multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE; } + DML_prelocking_strategy *get_dml_prelocking_strategy() + { + return &dml_prelocking_strategy; + } + protected: bool precheck(THD *thd) override; @@ -55,5 +63,6 @@ class Sql_cmd_delete final : public Sql_cmd_dml bool multitable; + DML_prelocking_strategy dml_prelocking_strategy; }; #endif /* SQL_DELETE_INCLUDED */ diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 5efc4a1..accefa8 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3328,34 +3328,6 @@ void st_select_lex_unit::exclude_level() } -#if 0 -/* - Exclude subtree of current unit from tree of SELECTs - - SYNOPSYS - st_select_lex_unit::exclude_tree() -*/ -void st_select_lex_unit::exclude_tree() -{ - for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select()) - { - // unlink current level from global SELECTs list - if (sl->link_prev && (*sl->link_prev= sl->link_next)) - sl->link_next->link_prev= sl->link_prev; - - // unlink underlay levels - for (SELECT_LEX_UNIT *u= sl->first_inner_unit(); u; u= u->next_unit()) - { - u->exclude_level(); - } - } - // exclude currect unit from list of nodes - (*prev)= next; - if (next) - next->prev= prev; -} -#endif - /* st_select_lex_node::mark_as_dependent mark all st_select_lex struct from @@ -3577,7 +3549,7 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num) select_n_where_fields + order_group_num + hidden_bit_fields + - fields_in_window_functions) * (size_t) 5; + fields_in_window_functions + 1) * (size_t) 5; DBUG_ASSERT(n_elems % 5 == 0); if (!ref_pointer_array.is_null()) { diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index fd33455..028c6a8 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4379,6 +4379,8 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) } case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: { DBUG_ASSERT(first_table == all_tables && first_table != 0); DBUG_ASSERT(lex->m_sql_cmd != NULL); @@ -4646,129 +4648,6 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) break; } - case SQLCOM_DELETE: - { - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - select_result *sel_result= NULL; - DBUG_ASSERT(first_table == all_tables && first_table != 0); - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - - if ((res= delete_precheck(thd, all_tables))) - break; - DBUG_ASSERT(select_lex->limit_params.offset_limit == 0); - unit->set_limit(select_lex); - - MYSQL_DELETE_START(thd->query()); - Protocol *save_protocol= NULL; - - if (lex->has_returning()) - { - /* This is DELETE ... RETURNING. It will return output to the client */ - if (thd->lex->analyze_stmt) - { - /* - Actually, it is ANALYZE .. DELETE .. RETURNING. We need to produce - output and then discard it. - */ - sel_result= new (thd->mem_root) select_send_analyze(thd); - save_protocol= thd->protocol; - thd->protocol= new Protocol_discard(thd); - } - else - { - if (!lex->result && !(sel_result= new (thd->mem_root) select_send(thd))) - goto error; - } - } - - res = mysql_delete(thd, all_tables, - select_lex->where, &select_lex->order_list, - unit->lim.get_select_limit(), select_lex->options, - lex->result ? lex->result : sel_result); - - if (save_protocol) - { - delete thd->protocol; - thd->protocol= save_protocol; - } - - if (thd->lex->analyze_stmt || thd->lex->describe) - { - if (!res) - res= thd->lex->explain->send_explain(thd); - } - - delete sel_result; - MYSQL_DELETE_DONE(res, (ulong) thd->get_row_count_func()); - break; - } - case SQLCOM_DELETE_MULTI: - { - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - DBUG_ASSERT(first_table == all_tables && first_table != 0); - TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first; - multi_delete *result; - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - - if ((res= multi_delete_precheck(thd, all_tables))) - break; - - /* condition will be TRUE on SP re-excuting */ - if (select_lex->item_list.elements != 0) - select_lex->item_list.empty(); - if (add_item_to_list(thd, new (thd->mem_root) Item_null(thd))) - goto error; - - THD_STAGE_INFO(thd, stage_init); - if ((res= open_and_lock_tables(thd, all_tables, TRUE, 0))) - break; - - MYSQL_MULTI_DELETE_START(thd->query()); - if (unlikely(res= mysql_multi_delete_prepare(thd))) - { - MYSQL_MULTI_DELETE_DONE(1, 0); - goto error; - } - - if (likely(!thd->is_fatal_error)) - { - result= new (thd->mem_root) multi_delete(thd, aux_tables, - lex->table_count); - if (likely(result)) - { - if (unlikely(select_lex->vers_setup_conds(thd, aux_tables))) - goto multi_delete_error; - res= mysql_select(thd, - select_lex->get_table_list(), - select_lex->item_list, - select_lex->where, - 0, (ORDER *)NULL, (ORDER *)NULL, (Item *)NULL, - (ORDER *)NULL, - (select_lex->options | thd->variables.option_bits | - SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK | - OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT, - result, unit, select_lex); - res|= (int)(thd->is_error()); - - MYSQL_MULTI_DELETE_DONE(res, result->num_deleted()); - if (res) - result->abort_result_set(); /* for both DELETE and EXPLAIN DELETE */ - else - { - if (lex->describe || lex->analyze_stmt) - res= thd->lex->explain->send_explain(thd); - } - multi_delete_error: - delete result; - } - } - else - { - res= TRUE; // Error - MYSQL_MULTI_DELETE_DONE(1, 0); - } - break; - } case SQLCOM_DROP_SEQUENCE: case SQLCOM_DROP_TABLE: { @@ -7643,12 +7522,16 @@ void create_select_for_variable(THD *thd, LEX_CSTRING *var_name) } -void mysql_init_multi_delete(LEX *lex) +void mysql_init_delete(LEX *lex) { - lex->sql_command= SQLCOM_DELETE_MULTI; mysql_init_select(lex); lex->first_select_lex()->limit_params.clear(); lex->unit.lim.clear(); +} + +void mysql_init_multi_delete(LEX *lex) +{ + lex->sql_command= SQLCOM_DELETE_MULTI; lex->first_select_lex()->table_list. save_and_clear(&lex->auxiliary_table_list); lex->query_tables= 0; diff --git a/sql/sql_parse.h b/sql/sql_parse.h index ebe3fe9..45cd15c 100644 --- a/sql/sql_parse.h +++ b/sql/sql_parse.h @@ -95,6 +95,7 @@ void mysql_parse(THD *thd, char *rawbuf, uint length, bool mysql_new_select(LEX *lex, bool move_down, SELECT_LEX *sel); void create_select_for_variable(THD *thd, LEX_CSTRING *var_name); void create_table_set_open_action_and_adjust_tables(LEX *lex); +void mysql_init_delete(LEX *lex); void mysql_init_multi_delete(LEX *lex); bool multi_delete_set_locks_and_link_aux_tables(LEX *lex); void create_table_set_open_action_and_adjust_tables(LEX *lex); diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 1d26add..72550b1 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -95,7 +95,6 @@ When one supplies long data for a placeholder: #include "sql_base.h" // open_normal_and_derived_tables #include "sql_cache.h" // query_cache_* #include "sql_view.h" // create_view_precheck -#include "sql_delete.h" // mysql_prepare_delete #include "sql_select.h" // for JOIN #include "sql_insert.h" // upgrade_lock_type_for_insert, mysql_prepare_insert #include "sql_db.h" // mysql_opt_change_db, mysql_change_db @@ -1398,56 +1397,6 @@ static bool mysql_test_insert(Prepared_statement *stmt, } -/** - Validate DELETE statement. - - @param stmt prepared statement - @param tables list of tables used in this query - - @retval - FALSE success - @retval - TRUE error, error message is set in THD -*/ - -static bool mysql_test_delete(Prepared_statement *stmt, - TABLE_LIST *table_list) -{ - uint table_count= 0; - THD *thd= stmt->thd; - LEX *lex= stmt->lex; - bool delete_while_scanning; - DBUG_ENTER("mysql_test_delete"); - - if (delete_precheck(thd, table_list) || - open_tables(thd, &table_list, &table_count, MYSQL_OPEN_FORCE_SHARED_MDL)) - goto error; - - if (mysql_handle_derived(thd->lex, DT_INIT)) - goto error; - if (mysql_handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) - goto error; - if (mysql_handle_derived(thd->lex, DT_PREPARE)) - goto error; - - if (!table_list->single_table_updatable()) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); - goto error; - } - if (!table_list->table || !table_list->table->is_created()) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - table_list->view_db.str, table_list->view_name.str); - goto error; - } - - DBUG_RETURN(mysql_prepare_delete(thd, table_list, - &lex->first_select_lex()->where, - &delete_while_scanning)); -error: - DBUG_RETURN(TRUE); -} /** @@ -2031,48 +1980,6 @@ static bool mysql_test_create_view(Prepared_statement *stmt) /** - Validate and prepare for execution a multi delete statement. - - @param stmt prepared statement - @param tables list of tables used in this query - - @retval - FALSE success - @retval - TRUE error, error message in THD is set. -*/ - -static bool mysql_test_multidelete(Prepared_statement *stmt, - TABLE_LIST *tables) -{ - THD *thd= stmt->thd; - - thd->lex->current_select= thd->lex->first_select_lex(); - if (add_item_to_list(thd, new (thd->mem_root) - Item_null(thd))) - { - my_error(ER_OUTOFMEMORY, MYF(ME_FATAL), 0); - goto error; - } - - if (multi_delete_precheck(thd, tables) || - select_like_stmt_test_with_open(stmt, tables, - &mysql_multi_delete_prepare, - OPTION_SETUP_TABLES_DONE)) - goto error; - if (!tables->table) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - tables->view_db.str, tables->view_name.str); - goto error; - } - return FALSE; -error: - return TRUE; -} - - -/** Wrapper for mysql_insert_select_prepare, to make change of local tables after open_normal_and_derived_tables() call. @@ -2350,14 +2257,13 @@ static bool check_prepared_statement(Prepared_statement *stmt) case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: res = lex->m_sql_cmd->prepare(thd); if (!res) lex->m_sql_cmd->unprepare(thd); break; - case SQLCOM_DELETE: - res= mysql_test_delete(stmt, tables); - break; /* The following allow WHERE clause, so they must be tested like SELECT */ case SQLCOM_SHOW_DATABASES: case SQLCOM_SHOW_TABLES: @@ -2494,10 +2400,6 @@ static bool check_prepared_statement(Prepared_statement *stmt) res= mysql_test_set_fields(stmt, tables, &lex->var_list); break; - case SQLCOM_DELETE_MULTI: - res= mysql_test_multidelete(stmt, tables); - break; - case SQLCOM_INSERT_SELECT: case SQLCOM_REPLACE_SELECT: res= mysql_test_insert_select(stmt, tables); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e46028c..50d8ccd 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -30336,7 +30336,6 @@ bool Sql_cmd_dml::execute(THD *thd) THD_STAGE_INFO(thd, stage_init); - DBUG_ASSERT(!lex->is_query_tables_locked()); /* Locking of tables is done after preparation but before optimization. This allows to do better partition pruning and avoid locking unused diff --git a/sql/sql_update.cc b/sql/sql_update.cc index bc66612..2d2a1d4 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -3024,12 +3024,10 @@ bool Sql_cmd_update::prepare_inner(THD *thd) { JOIN *join; int err= 0; - // uint table_cnt= 0; SELECT_LEX *const select_lex = thd->lex->first_select_lex(); TABLE_LIST *const table_list = select_lex->get_table_list(); ulonglong select_options= select_lex->options; bool free_join= 1; - // bool orig_multitable= multitable; DBUG_ENTER("Sql_cmd_update::prepare_inner"); if (!multitable) @@ -3083,8 +3081,8 @@ bool Sql_cmd_update::prepare_inner(THD *thd) DBUG_RETURN(TRUE); } - if (((multi_update *)result)->init(thd)) - DBUG_RETURN(TRUE); + if (((multi_update *)result)->init(thd)) + DBUG_RETURN(TRUE); if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, table_list, select_lex->leaf_tables, false, false)) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 036cab9..c02ac8a 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -70,6 +70,7 @@ #include "sql_type_json.h" #include "json_table.h" #include "sql_update.h" +#include "sql_delete.h" /* this is to get the bison compilation windows warnings out */ #ifdef _MSC_VER @@ -1675,7 +1676,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); opt_mi_check_type opt_to mi_check_types table_to_table_list table_to_table opt_table_list opt_as handler_rkey_function handler_read_or_scan - single_multi table_wild_list table_wild_one opt_wild + single_multi opt_wild opt_and select_var_list select_var_list_init help opt_extended_describe shutdown @@ -13273,12 +13274,11 @@ delete: DELETE_SYM { LEX *lex= Lex; - lex->sql_command= SQLCOM_DELETE; YYPS->m_lock_type= TL_WRITE_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_WRITE; if (Lex->main_select_push()) MYSQL_YYABORT; - mysql_init_select(lex); + mysql_init_delete(lex); lex->ignore= 0; lex->first_select_lex()->order_list.empty(); } @@ -13339,12 +13339,22 @@ single_multi: delete_limit_clause opt_returning { + LEX *lex= Lex; if ($3) Select->order_list= *($3); - Lex->pop_select(); //main select + lex->pop_select(); //main select + lex->sql_command= SQLCOM_DELETE; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(false))) + MYSQL_YYABORT; } - | table_wild_list + | table_alias_ref_list { + LEX *lex= Lex; + lex->sql_command= SQLCOM_DELETE_MULTI; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(true))) + MYSQL_YYABORT; mysql_init_multi_delete(Lex); YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; @@ -13356,6 +13366,11 @@ single_multi: } stmt_end {} | FROM table_alias_ref_list { + LEX *lex= Lex; + lex->sql_command= SQLCOM_DELETE_MULTI; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(true))) + MYSQL_YYABORT; mysql_init_multi_delete(Lex); YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; @@ -13391,44 +13406,6 @@ opt_returning: } ; -table_wild_list: - table_wild_one - | table_wild_list ',' table_wild_one - ; - -table_wild_one: - ident opt_wild - { - Table_ident *ti= new (thd->mem_root) Table_ident(&$1); - if (unlikely(ti == NULL)) - MYSQL_YYABORT; - if (unlikely(!Select-> - add_table_to_list(thd, - ti, - NULL, - (TL_OPTION_UPDATING | - TL_OPTION_ALIAS), - YYPS->m_lock_type, - YYPS->m_mdl_type))) - MYSQL_YYABORT; - } - | ident '.' ident opt_wild - { - Table_ident *ti= new (thd->mem_root) Table_ident(thd, &$1, &$3, 0); - if (unlikely(ti == NULL)) - MYSQL_YYABORT; - if (unlikely(!Select-> - add_table_to_list(thd, - ti, - NULL, - (TL_OPTION_UPDATING | - TL_OPTION_ALIAS), - YYPS->m_lock_type, - YYPS->m_mdl_type))) - MYSQL_YYABORT; - } - ; - opt_wild: /* empty */ {} | '.' '*' {} diff --git a/sql/table.h b/sql/table.h index 88216c7..fc7cc14 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2314,6 +2314,7 @@ struct TABLE_LIST */ select_unit *derived_result; /* Stub used for materialized derived tables. */ + bool delete_while_scanning; table_map map; /* ID bit of table (1,2,4,8,16...) */ table_map get_map() {

1 0

[Commits] bd48128: MDEV-27159 Re-design the upper level of handling DML commands
by IgorBabaev 25 Mar '22

25 Mar '22

revision-id: bd48128deab009fa8351bee3b0e55ddb60a33b8a (mariadb-10.6.1-50-gbd48128) parent(s): 086a212d96b7693d1bacf67e3ad14627fb802269 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-25 08:45:10 -0700 message: MDEV-27159 Re-design the upper level of handling DML commands This is the second commit for the task. This patch allows to execute only single-table and multi-table DELETE statements using the method Sql_cmd_dml::execute(). The code that handles INSERT statements has not been touched. This patch still does not have the final changes to handle UPDATE/DELETE statements. All tests from the main suite passed. With --ps-protocol one test from opt_trace_security returns not the same result. This will be fixed soon. --- mysql-test/main/opt_trace.result | 14 +- sql/opt_range.cc | 2 +- sql/sql_base.cc | 7 +- sql/sql_cmd.h | 9 +- sql/sql_delete.cc | 577 ++++++++++++++++++++------------------- sql/sql_delete.h | 9 + sql/sql_lex.cc | 30 +- sql/sql_parse.cc | 133 +-------- sql/sql_parse.h | 1 + sql/sql_prepare.cc | 102 +------ sql/sql_select.cc | 1 - sql/sql_update.cc | 6 +- sql/sql_yacc.yy | 63 ++--- sql/table.h | 1 + 14 files changed, 373 insertions(+), 582 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index fef7b4d..11bb18c 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -3740,6 +3740,16 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain delete from t0 where t0.a<3 { "steps": [ { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select from dual where t0.a < 3" + } + ] + } + }, + { "table": "t0", "range_analysis": { "table_scan": { @@ -3771,7 +3781,7 @@ explain delete from t0 where t0.a<3 { }, "group_index_range": { "chosen": false, - "cause": "no join" + "cause": "no group by or distinct" }, "chosen_range_access_summary": { "range_access_plan": { @@ -3814,7 +3824,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "select_id": 1, "steps": [ { - "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3" + "expanded_query": "select from t0 join t1 where t0.a = t1.a and t1.a < 3" } ] } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 698c709..31bba1e 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -11554,7 +11554,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, Skip materialized derived table/view result table from MRR check as they aren't contain any data yet. */ - if (param->table->pos_in_table_list->is_non_derived()) + if (!param->table->pos_in_table_list->is_materialized_derived()) rows= file->multi_range_read_info_const(keynr, &seq_if, (void*)&seq, 0, bufsize, mrr_flags, cost); param->quick_rows[keynr]= rows; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 65b1b92..38d1d60 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1069,7 +1069,9 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, */ if (table->table && thd->lex->sql_command != SQLCOM_UPDATE && - thd->lex->sql_command != SQLCOM_UPDATE_MULTI) + thd->lex->sql_command != SQLCOM_UPDATE_MULTI && + thd->lex->sql_command != SQLCOM_DELETE && + thd->lex->sql_command != SQLCOM_DELETE_MULTI) { /* All MyISAMMRG children are plain MyISAM tables. */ DBUG_ASSERT(table->table->file->ht->db_type != DB_TYPE_MRG_MYISAM); @@ -7592,6 +7594,9 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, if (!select_lex->with_wild) DBUG_RETURN(0); + if (!fields.elements) + DBUG_RETURN(0); + /* Don't use arena if we are not in prepared statements or stored procedures For PS/SP we have to use arena to remember the changes diff --git a/sql/sql_cmd.h b/sql/sql_cmd.h index a5557c6..64299d1 100644 --- a/sql/sql_cmd.h +++ b/sql/sql_cmd.h @@ -263,6 +263,7 @@ class LEX; class select_result; class Prelocking_strategy; class DML_prelocking_strategy; +class Protocol; class Sql_cmd_dml : public Sql_cmd { @@ -290,7 +291,9 @@ class Sql_cmd_dml : public Sql_cmd protected: Sql_cmd_dml() - : Sql_cmd(), lex(nullptr), result(nullptr), m_empty_query(false) {} + : Sql_cmd(), lex(nullptr), result(nullptr), + m_empty_query(false), save_protocol(NULL) + {} /// @return true if query is guaranteed to return no data /** @@ -348,12 +351,14 @@ class Sql_cmd_dml : public Sql_cmd virtual DML_prelocking_strategy *get_dml_prelocking_strategy() = 0; - uint table_count; + uint table_count; protected: LEX *lex; ///< Pointer to LEX for this statement select_result *result; ///< Pointer to object for handling of the result bool m_empty_query; ///< True if query will produce no rows + List<Item> empty_list; + Protocol *save_protocol; }; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 0a2db2e..01ee19b 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -103,7 +103,7 @@ bool Update_plan::save_explain_data_intern(MEM_ROOT *mem_root, bool is_analyze) { explain->select_type= "SIMPLE"; - explain->table_name.append(&table->pos_in_table_list->alias); + explain->table_name.append(table->alias); explain->impossible_where= false; explain->no_partitions= false; @@ -294,124 +294,79 @@ int TABLE::delete_row() } -/** - Implement DELETE SQL word. - - @note Like implementations of other DDL/DML in MySQL, this function - relies on the caller to close the thread tables. This is done in the - end of dispatch_command(). -*/ - -bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, - SQL_I_List<ORDER> *order_list, ha_rows limit, - ulonglong options, select_result *result) +bool Sql_cmd_delete::delete_from_single_table(THD *thd) { - bool will_batch= FALSE; - int error, loc_error; - TABLE *table; - SQL_SELECT *select=0; - SORT_INFO *file_sort= 0; - READ_RECORD info; - bool using_limit=limit != HA_POS_ERROR; - bool transactional_table, safe_update, const_cond; - bool const_cond_result; - bool return_error= 0; - ha_rows deleted= 0; - bool reverse= FALSE; - bool has_triggers= false; - ORDER *order= (ORDER *) ((order_list && order_list->elements) ? - order_list->first : NULL); - SELECT_LEX *select_lex= thd->lex->first_select_lex(); - SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + int error; + int loc_error; + bool transactional_table; + bool const_cond; + bool safe_update; + bool const_cond_result; + bool return_error= 0; + TABLE *table; + SQL_SELECT *select= 0; + SORT_INFO *file_sort= 0; + READ_RECORD info; + ha_rows deleted= 0; + bool reverse= FALSE; + bool binlog_is_row; killed_state killed_status= NOT_KILLED; THD::enum_binlog_query_type query_type= THD::ROW_QUERY_TYPE; - bool binlog_is_row; - Explain_delete *explain; + bool will_batch= FALSE; + + bool has_triggers= false; + SELECT_LEX_UNIT *unit = &lex->unit; + SELECT_LEX *select_lex= unit->first_select(); + SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + TABLE_LIST *const table_list = select_lex->get_table_list(); + ulonglong options= select_lex->options; + ORDER *order= select_lex->order_list.first; + COND *conds= select_lex->join->conds; + ha_rows limit= unit->lim.get_select_limit(); + bool using_limit= limit != HA_POS_ERROR; + Delete_plan query_plan(thd->mem_root); + Explain_delete *explain; Unique * deltempfile= NULL; bool delete_record= false; - bool delete_while_scanning; + bool delete_while_scanning= table_list->delete_while_scanning; bool portion_of_time_through_update; - DBUG_ENTER("mysql_delete"); + + DBUG_ENTER("Sql_cmd_delete::delete_single_table"); query_plan.index= MAX_KEY; query_plan.using_filesort= FALSE; - create_explain_query(thd->lex, thd->mem_root); - if (open_and_lock_tables(thd, table_list, TRUE, 0)) - DBUG_RETURN(TRUE); - THD_STAGE_INFO(thd, stage_init_update); + create_explain_query(thd->lex, thd->mem_root); const bool delete_history= table_list->vers_conditions.delete_history; DBUG_ASSERT(!(delete_history && table_list->period_conditions.is_set())); - if (thd->lex->handle_list_of_derived(table_list, DT_MERGE_FOR_INSERT)) - DBUG_RETURN(TRUE); - if (thd->lex->handle_list_of_derived(table_list, DT_PREPARE)) - DBUG_RETURN(TRUE); + if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(1); + if (table_list->handle_derived(thd->lex, DT_PREPARE)) + DBUG_RETURN(1); + + table= table_list->table; if (!table_list->single_table_updatable()) { my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); DBUG_RETURN(TRUE); } - if (!(table= table_list->table) || !table->is_created()) + + if (!table || !table->is_created()) { my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), table_list->view_db.str, table_list->view_name.str); DBUG_RETURN(TRUE); } - table->map=1; + query_plan.select_lex= thd->lex->first_select_lex(); query_plan.table= table; - thd->lex->promote_select_describe_flag_if_needed(); - if (mysql_prepare_delete(thd, table_list, &conds, &delete_while_scanning)) - DBUG_RETURN(TRUE); - - if (table_list->has_period()) - { - if (!table_list->period_conditions.start.item->const_item() - || !table_list->period_conditions.end.item->const_item()) - { - my_error(ER_NOT_CONSTANT_EXPRESSION, MYF(0), "FOR PORTION OF"); - DBUG_RETURN(true); - } - } - - if (delete_history) - table->vers_write= false; - - if (returning) - (void) result->prepare(returning->item_list, NULL); - - if (thd->lex->current_select->first_cond_optimization) - { - thd->lex->current_select->save_leaf_tables(thd); - thd->lex->current_select->first_cond_optimization= 0; - } - /* check ORDER BY even if it can be ignored */ - if (order) - { - TABLE_LIST tables; - List<Item> fields; - List<Item> all_fields; - - bzero((char*) &tables,sizeof(tables)); - tables.table = table; - tables.alias = table_list->alias; - - if (select_lex->setup_ref_array(thd, order_list->elements) || - setup_order(thd, select_lex->ref_pointer_array, &tables, - fields, all_fields, order)) - { - free_underlaid_joins(thd, thd->lex->first_select_lex()); - DBUG_RETURN(TRUE); - } - } - /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */ if (select_lex->optimize_unflattened_subqueries(false)) DBUG_RETURN(TRUE); @@ -518,7 +473,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, table->covering_keys.clear_all(); table->opt_range_keys.clear_all(); - select=make_select(table, 0, 0, conds, (SORT_INFO*) 0, 0, &error); + select= make_select(table, 0, 0, conds, (SORT_INFO*) 0, 0, &error); if (unlikely(error)) DBUG_RETURN(TRUE); if ((select && select->check_quick(thd, safe_update, limit)) || !limit) @@ -943,7 +898,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, } } DBUG_ASSERT(transactional_table || !deleted || thd->transaction->stmt.modified_non_trans_table); - + if (likely(error < 0) || (thd->lex->ignore && !thd->is_error() && !thd->is_fatal_error)) { @@ -993,90 +948,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, } -/* - Prepare items in DELETE statement - - SYNOPSIS - mysql_prepare_delete() - thd - thread handler - table_list - global/local table list - conds - conditions - - RETURN VALUE - FALSE OK - TRUE error -*/ -int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds, - bool *delete_while_scanning) -{ - Item *fake_conds= 0; - SELECT_LEX *select_lex= thd->lex->first_select_lex(); - DBUG_ENTER("mysql_prepare_delete"); - List<Item> all_fields; - - *delete_while_scanning= true; - thd->lex->allow_sum_func.clear_all(); - if (setup_tables_and_check_access(thd, &select_lex->context, - &select_lex->top_join_list, table_list, - select_lex->leaf_tables, FALSE, - DELETE_ACL, SELECT_ACL, TRUE)) - DBUG_RETURN(TRUE); - - if (table_list->vers_conditions.is_set() && table_list->is_view_or_derived()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(true); - } - - if (table_list->has_period()) - { - if (table_list->is_view_or_derived()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(true); - } - - if (select_lex->period_setup_conds(thd, table_list)) - DBUG_RETURN(true); - } - - DBUG_ASSERT(table_list->table); - // conds could be cached from previous SP call - DBUG_ASSERT(!table_list->vers_conditions.need_setup() || - !*conds || thd->stmt_arena->is_stmt_execute()); - if (select_lex->vers_setup_conds(thd, table_list)) - DBUG_RETURN(TRUE); - - *conds= select_lex->where; - - if (setup_returning_fields(thd, table_list) || - setup_conds(thd, table_list, select_lex->leaf_tables, conds) || - setup_ftfuncs(select_lex)) - DBUG_RETURN(TRUE); - if (!table_list->single_table_updatable() || - check_key_in_view(thd, table_list)) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); - DBUG_RETURN(TRUE); - } - - /* - Application-time periods: if FOR PORTION OF ... syntax used, DELETE - statement could issue delete_row's mixed with write_row's. This causes - problems for myisam and corrupts table, if deleting while scanning. - */ - if (table_list->has_period() - || unique_table(thd, table_list, table_list->next_global, 0)) - *delete_while_scanning= false; - - if (select_lex->inner_refs_list.elements && - fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array)) - DBUG_RETURN(TRUE); - - select_lex->fix_prepare_information(thd, conds, &fake_conds); - DBUG_RETURN(FALSE); -} - /*************************************************************************** Delete multiple tables from join @@ -1089,106 +960,6 @@ extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b) return file->cmp_ref((const uchar*)a, (const uchar*)b); } -/* - make delete specific preparation and checks after opening tables - - SYNOPSIS - mysql_multi_delete_prepare() - thd thread handler - - RETURN - FALSE OK - TRUE Error -*/ - -int mysql_multi_delete_prepare(THD *thd) -{ - LEX *lex= thd->lex; - TABLE_LIST *aux_tables= lex->auxiliary_table_list.first; - TABLE_LIST *target_tbl; - DBUG_ENTER("mysql_multi_delete_prepare"); - - if (mysql_handle_derived(lex, DT_INIT)) - DBUG_RETURN(TRUE); - if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT)) - DBUG_RETURN(TRUE); - if (mysql_handle_derived(lex, DT_PREPARE)) - DBUG_RETURN(TRUE); - /* - setup_tables() need for VIEWs. JOIN::prepare() will not do it second - time. - - lex->query_tables also point on local list of DELETE SELECT_LEX - */ - if (setup_tables_and_check_access(thd, - &thd->lex->first_select_lex()->context, - &thd->lex->first_select_lex()-> - top_join_list, - lex->query_tables, - lex->first_select_lex()->leaf_tables, - FALSE, DELETE_ACL, SELECT_ACL, FALSE)) - DBUG_RETURN(TRUE); - - /* - Multi-delete can't be constructed over-union => we always have - single SELECT on top and have to check underlying SELECTs of it - */ - lex->first_select_lex()->set_unique_exclude(); - /* Fix tables-to-be-deleted-from list to point at opened tables */ - for (target_tbl= (TABLE_LIST*) aux_tables; - target_tbl; - target_tbl= target_tbl->next_local) - { - - target_tbl->table= target_tbl->correspondent_table->table; - if (target_tbl->correspondent_table->is_multitable()) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - target_tbl->correspondent_table->view_db.str, - target_tbl->correspondent_table->view_name.str); - DBUG_RETURN(TRUE); - } - - if (!target_tbl->correspondent_table->single_table_updatable() || - check_key_in_view(thd, target_tbl->correspondent_table)) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), - target_tbl->table_name.str, "DELETE"); - DBUG_RETURN(TRUE); - } - } - - for (target_tbl= (TABLE_LIST*) aux_tables; - target_tbl; - target_tbl= target_tbl->next_local) - { - /* - Check that table from which we delete is not used somewhere - inside subqueries/view. - */ - { - TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, target_tbl->correspondent_table, - lex->query_tables, 0))) - { - update_non_unique_table_error(target_tbl->correspondent_table, - "DELETE", duplicate); - DBUG_RETURN(TRUE); - } - } - } - /* - Reset the exclude flag to false so it doesn't interfare - with further calls to unique_table - */ - lex->first_select_lex()->exclude_from_table_unique_test= FALSE; - - if (lex->save_prep_leaf_tables()) - DBUG_RETURN(TRUE); - - DBUG_RETURN(FALSE); -} - multi_delete::multi_delete(THD *thd_arg, TABLE_LIST *dt, uint num_of_tables_arg): select_result_interceptor(thd_arg), delete_tables(dt), deleted(0), found(0), @@ -1637,3 +1408,261 @@ bool multi_delete::send_eof() } return 0; } + + +bool Sql_cmd_delete::precheck(THD *thd) +{ + if (!multitable) + { + if (delete_precheck(thd, lex->query_tables)) + return true; + } + else + { + if (multi_delete_precheck(thd, lex->query_tables)) + return true; + } + return false; +} + + +bool Sql_cmd_delete::prepare_inner(THD *thd) +{ + int err= 0; + TABLE_LIST *target_tbl; + JOIN *join; + SELECT_LEX *const select_lex = thd->lex->first_select_lex(); + TABLE_LIST *const table_list = select_lex->get_table_list(); + TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first; + ulonglong select_options= select_lex->options; + bool free_join= 1; + SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + + DBUG_ENTER("Sql_cmd_delete::prepare_inner"); + + (void) read_statistics_for_tables_if_needed(thd, table_list); + + { + if (mysql_handle_derived(lex, DT_INIT)) + DBUG_RETURN(TRUE); + if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(TRUE); + if (mysql_handle_derived(lex, DT_PREPARE)) + DBUG_RETURN(TRUE); + } + + if (!(result= new (thd->mem_root) multi_delete(thd, aux_tables, + lex->table_count))) + { + DBUG_RETURN(TRUE); + } + + table_list->delete_while_scanning= true; + + if (setup_tables_and_check_access(thd, &select_lex->context, + &select_lex->top_join_list, + table_list, select_lex->leaf_tables, + false, DELETE_ACL, SELECT_ACL, true)) + DBUG_RETURN(TRUE); + + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, false, false)) + DBUG_RETURN(TRUE); + + if (!multitable) + { + if (table_list->vers_conditions.is_set() && table_list->is_view_or_derived()) + { + my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); + DBUG_RETURN(true); + } + + if (table_list->has_period()) + { + if (table_list->is_view_or_derived()) + { + my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); + DBUG_RETURN(true); + } + + if (select_lex->period_setup_conds(thd, table_list)) + DBUG_RETURN(true); + } + + if (select_lex->vers_setup_conds(thd, table_list)) + DBUG_RETURN(TRUE); + /* + Application-time periods: if FOR PORTION OF ... syntax used, DELETE + statement could issue delete_row's mixed with write_row's. This causes + problems for myisam and corrupts table, if deleting while scanning. + */ + if (table_list->has_period() + || unique_table(thd, table_list, table_list->next_global, 0)) + table_list->delete_while_scanning= false; + } + + if (multitable) + { + /* + Multi-delete can't be constructed over-union => we always have + single SELECT on top and have to check underlying SELECTs of it + */ + lex->first_select_lex()->set_unique_exclude(); + /* Fix tables-to-be-deleted-from list to point at opened tables */ + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { + target_tbl->table= target_tbl->correspondent_table->table; + if (target_tbl->correspondent_table->is_multitable()) + { + my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), + target_tbl->correspondent_table->view_db.str, + target_tbl->correspondent_table->view_name.str); + DBUG_RETURN(TRUE); + } + + if (!target_tbl->correspondent_table->single_table_updatable() || + check_key_in_view(thd, target_tbl->correspondent_table)) + { + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), + target_tbl->table_name.str, "DELETE"); + DBUG_RETURN(TRUE); + } + } + + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { + /* + Check that table from which we delete is not used somewhere + inside subqueries/view. + */ + { + TABLE_LIST *duplicate; + if ((duplicate= unique_table(thd, target_tbl->correspondent_table, + lex->query_tables, 0))) + { + update_non_unique_table_error(target_tbl->correspondent_table, + "DELETE", duplicate); + DBUG_RETURN(TRUE); + } + } + } + /* + Reset the exclude flag to false so it doesn't interfare + with further calls to unique_table + */ + lex->first_select_lex()->exclude_from_table_unique_test= FALSE; + } + + { + if (thd->lex->describe) + select_options|= SELECT_DESCRIBE; + + /* + When in EXPLAIN, delay deleting the joins so that they are still + available when we're producing EXPLAIN EXTENDED warning text. + */ + if (select_options & SELECT_DESCRIBE) + free_join= 0; + select_options|= + SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK | OPTION_SETUP_TABLES_DONE; + + if (!(join= new (thd->mem_root) JOIN(thd, empty_list, + select_options, result))) + DBUG_RETURN(TRUE); + THD_STAGE_INFO(thd, stage_init); + select_lex->join= join; + thd->lex->used_tables=0; + if ((err= join->prepare(table_list, select_lex->where, + select_lex->order_list.elements, + select_lex->order_list.first, + false, NULL, NULL, NULL, + select_lex, &lex->unit))) + + { + goto err; + } + + } + + + if (setup_returning_fields(thd, table_list) || + setup_ftfuncs(select_lex)) + goto err; + + free_join= false; + + if (returning) + (void) result->prepare(returning->item_list, NULL); + +err: + + if (free_join) + { + THD_STAGE_INFO(thd, stage_end); + err|= (int)(select_lex->cleanup()); + DBUG_RETURN(err || thd->is_error()); + } + DBUG_RETURN(err); + +} + +bool Sql_cmd_delete::execute_inner(THD *thd) +{ + if (!multitable) + { + if (lex->has_returning()) + { + select_result *sel_result= NULL; + delete result; + /* This is DELETE ... RETURNING. It will return output to the client */ + if (thd->lex->analyze_stmt) + { + /* + Actually, it is ANALYZE .. DELETE .. RETURNING. We need to produce + output and then discard it. + */ + sel_result= new (thd->mem_root) select_send_analyze(thd); + save_protocol= thd->protocol; + thd->protocol= new Protocol_discard(thd); + } + else + { + if (!lex->result && !(sel_result= new (thd->mem_root) select_send(thd))) + return true; + } + result= lex->result ? lex->result : sel_result; + } + } + + bool res= multitable ? Sql_cmd_dml::execute_inner(thd) + : delete_from_single_table(thd); + + res|= thd->is_error(); + + if (save_protocol) + { + delete thd->protocol; + thd->protocol= save_protocol; + } + { + if (unlikely(res)) + result->abort_result_set(); + else + { + if (thd->lex->describe || thd->lex->analyze_stmt) + res= thd->lex->explain->send_explain(thd); + } + } + + if (result) + { + res= false; + delete result; + } + + return res; +} diff --git a/sql/sql_delete.h b/sql/sql_delete.h index dabcafb..4aee510 100644 --- a/sql/sql_delete.h +++ b/sql/sql_delete.h @@ -17,6 +17,9 @@ #define SQL_DELETE_INCLUDED #include "my_base.h" /* ha_rows */ +#include "sql_class.h" /* enum_duplicates */ +#include "sql_cmd.h" // Sql_cmd_dml +#include "sql_base.h" class THD; struct TABLE_LIST; @@ -43,6 +46,11 @@ class Sql_cmd_delete final : public Sql_cmd_dml return multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE; } + DML_prelocking_strategy *get_dml_prelocking_strategy() + { + return &dml_prelocking_strategy; + } + protected: bool precheck(THD *thd) override; @@ -55,5 +63,6 @@ class Sql_cmd_delete final : public Sql_cmd_dml bool multitable; + DML_prelocking_strategy dml_prelocking_strategy; }; #endif /* SQL_DELETE_INCLUDED */ diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 47d9479..032e094 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3324,34 +3324,6 @@ void st_select_lex_unit::exclude_level() } -#if 0 -/* - Exclude subtree of current unit from tree of SELECTs - - SYNOPSYS - st_select_lex_unit::exclude_tree() -*/ -void st_select_lex_unit::exclude_tree() -{ - for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select()) - { - // unlink current level from global SELECTs list - if (sl->link_prev && (*sl->link_prev= sl->link_next)) - sl->link_next->link_prev= sl->link_prev; - - // unlink underlay levels - for (SELECT_LEX_UNIT *u= sl->first_inner_unit(); u; u= u->next_unit()) - { - u->exclude_level(); - } - } - // exclude currect unit from list of nodes - (*prev)= next; - if (next) - next->prev= prev; -} -#endif - /* st_select_lex_node::mark_as_dependent mark all st_select_lex struct from @@ -3573,7 +3545,7 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num) select_n_where_fields + order_group_num + hidden_bit_fields + - fields_in_window_functions) * 5; + fields_in_window_functions + 1) * 5; if (!ref_pointer_array.is_null()) { /* diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 47d42f9..f23b554 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4383,6 +4383,8 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) } case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: { DBUG_ASSERT(first_table == all_tables && first_table != 0); DBUG_ASSERT(lex->m_sql_cmd != NULL); @@ -4650,129 +4652,6 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) break; } - case SQLCOM_DELETE: - { - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - select_result *sel_result= NULL; - DBUG_ASSERT(first_table == all_tables && first_table != 0); - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - - if ((res= delete_precheck(thd, all_tables))) - break; - DBUG_ASSERT(select_lex->limit_params.offset_limit == 0); - unit->set_limit(select_lex); - - MYSQL_DELETE_START(thd->query()); - Protocol *save_protocol= NULL; - - if (lex->has_returning()) - { - /* This is DELETE ... RETURNING. It will return output to the client */ - if (thd->lex->analyze_stmt) - { - /* - Actually, it is ANALYZE .. DELETE .. RETURNING. We need to produce - output and then discard it. - */ - sel_result= new (thd->mem_root) select_send_analyze(thd); - save_protocol= thd->protocol; - thd->protocol= new Protocol_discard(thd); - } - else - { - if (!lex->result && !(sel_result= new (thd->mem_root) select_send(thd))) - goto error; - } - } - - res = mysql_delete(thd, all_tables, - select_lex->where, &select_lex->order_list, - unit->lim.get_select_limit(), select_lex->options, - lex->result ? lex->result : sel_result); - - if (save_protocol) - { - delete thd->protocol; - thd->protocol= save_protocol; - } - - if (thd->lex->analyze_stmt || thd->lex->describe) - { - if (!res) - res= thd->lex->explain->send_explain(thd); - } - - delete sel_result; - MYSQL_DELETE_DONE(res, (ulong) thd->get_row_count_func()); - break; - } - case SQLCOM_DELETE_MULTI: - { - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - DBUG_ASSERT(first_table == all_tables && first_table != 0); - TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first; - multi_delete *result; - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - - if ((res= multi_delete_precheck(thd, all_tables))) - break; - - /* condition will be TRUE on SP re-excuting */ - if (select_lex->item_list.elements != 0) - select_lex->item_list.empty(); - if (add_item_to_list(thd, new (thd->mem_root) Item_null(thd))) - goto error; - - THD_STAGE_INFO(thd, stage_init); - if ((res= open_and_lock_tables(thd, all_tables, TRUE, 0))) - break; - - MYSQL_MULTI_DELETE_START(thd->query()); - if (unlikely(res= mysql_multi_delete_prepare(thd))) - { - MYSQL_MULTI_DELETE_DONE(1, 0); - goto error; - } - - if (likely(!thd->is_fatal_error)) - { - result= new (thd->mem_root) multi_delete(thd, aux_tables, - lex->table_count); - if (likely(result)) - { - if (unlikely(select_lex->vers_setup_conds(thd, aux_tables))) - goto multi_delete_error; - res= mysql_select(thd, - select_lex->get_table_list(), - select_lex->item_list, - select_lex->where, - 0, (ORDER *)NULL, (ORDER *)NULL, (Item *)NULL, - (ORDER *)NULL, - (select_lex->options | thd->variables.option_bits | - SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK | - OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT, - result, unit, select_lex); - res|= (int)(thd->is_error()); - - MYSQL_MULTI_DELETE_DONE(res, result->num_deleted()); - if (res) - result->abort_result_set(); /* for both DELETE and EXPLAIN DELETE */ - else - { - if (lex->describe || lex->analyze_stmt) - res= thd->lex->explain->send_explain(thd); - } - multi_delete_error: - delete result; - } - } - else - { - res= TRUE; // Error - MYSQL_MULTI_DELETE_DONE(1, 0); - } - break; - } case SQLCOM_DROP_SEQUENCE: case SQLCOM_DROP_TABLE: { @@ -7643,12 +7522,16 @@ void create_select_for_variable(THD *thd, LEX_CSTRING *var_name) } -void mysql_init_multi_delete(LEX *lex) +void mysql_init_delete(LEX *lex) { - lex->sql_command= SQLCOM_DELETE_MULTI; mysql_init_select(lex); lex->first_select_lex()->limit_params.clear(); lex->unit.lim.clear(); +} + +void mysql_init_multi_delete(LEX *lex) +{ + lex->sql_command= SQLCOM_DELETE_MULTI; lex->first_select_lex()->table_list. save_and_clear(&lex->auxiliary_table_list); lex->query_tables= 0; diff --git a/sql/sql_parse.h b/sql/sql_parse.h index ebe3fe9..45cd15c 100644 --- a/sql/sql_parse.h +++ b/sql/sql_parse.h @@ -95,6 +95,7 @@ void mysql_parse(THD *thd, char *rawbuf, uint length, bool mysql_new_select(LEX *lex, bool move_down, SELECT_LEX *sel); void create_select_for_variable(THD *thd, LEX_CSTRING *var_name); void create_table_set_open_action_and_adjust_tables(LEX *lex); +void mysql_init_delete(LEX *lex); void mysql_init_multi_delete(LEX *lex); bool multi_delete_set_locks_and_link_aux_tables(LEX *lex); void create_table_set_open_action_and_adjust_tables(LEX *lex); diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 87ee207..ad51fe2 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -95,7 +95,6 @@ When one supplies long data for a placeholder: #include "sql_base.h" // open_normal_and_derived_tables #include "sql_cache.h" // query_cache_* #include "sql_view.h" // create_view_precheck -#include "sql_delete.h" // mysql_prepare_delete #include "sql_select.h" // for JOIN #include "sql_insert.h" // upgrade_lock_type_for_insert, mysql_prepare_insert #include "sql_db.h" // mysql_opt_change_db, mysql_change_db @@ -1396,56 +1395,6 @@ static bool mysql_test_insert(Prepared_statement *stmt, } -/** - Validate DELETE statement. - - @param stmt prepared statement - @param tables list of tables used in this query - - @retval - FALSE success - @retval - TRUE error, error message is set in THD -*/ - -static bool mysql_test_delete(Prepared_statement *stmt, - TABLE_LIST *table_list) -{ - uint table_count= 0; - THD *thd= stmt->thd; - LEX *lex= stmt->lex; - bool delete_while_scanning; - DBUG_ENTER("mysql_test_delete"); - - if (delete_precheck(thd, table_list) || - open_tables(thd, &table_list, &table_count, MYSQL_OPEN_FORCE_SHARED_MDL)) - goto error; - - if (mysql_handle_derived(thd->lex, DT_INIT)) - goto error; - if (mysql_handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) - goto error; - if (mysql_handle_derived(thd->lex, DT_PREPARE)) - goto error; - - if (!table_list->single_table_updatable()) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); - goto error; - } - if (!table_list->table || !table_list->table->is_created()) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - table_list->view_db.str, table_list->view_name.str); - goto error; - } - - DBUG_RETURN(mysql_prepare_delete(thd, table_list, - &lex->first_select_lex()->where, - &delete_while_scanning)); -error: - DBUG_RETURN(TRUE); -} /** @@ -2024,48 +1973,6 @@ static bool mysql_test_create_view(Prepared_statement *stmt) /** - Validate and prepare for execution a multi delete statement. - - @param stmt prepared statement - @param tables list of tables used in this query - - @retval - FALSE success - @retval - TRUE error, error message in THD is set. -*/ - -static bool mysql_test_multidelete(Prepared_statement *stmt, - TABLE_LIST *tables) -{ - THD *thd= stmt->thd; - - thd->lex->current_select= thd->lex->first_select_lex(); - if (add_item_to_list(thd, new (thd->mem_root) - Item_null(thd))) - { - my_error(ER_OUTOFMEMORY, MYF(ME_FATAL), 0); - goto error; - } - - if (multi_delete_precheck(thd, tables) || - select_like_stmt_test_with_open(stmt, tables, - &mysql_multi_delete_prepare, - OPTION_SETUP_TABLES_DONE)) - goto error; - if (!tables->table) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - tables->view_db.str, tables->view_name.str); - goto error; - } - return FALSE; -error: - return TRUE; -} - - -/** Wrapper for mysql_insert_select_prepare, to make change of local tables after open_normal_and_derived_tables() call. @@ -2343,14 +2250,13 @@ static bool check_prepared_statement(Prepared_statement *stmt) case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: res = lex->m_sql_cmd->prepare(thd); if (!res) lex->m_sql_cmd->unprepare(thd); break; - case SQLCOM_DELETE: - res= mysql_test_delete(stmt, tables); - break; /* The following allow WHERE clause, so they must be tested like SELECT */ case SQLCOM_SHOW_DATABASES: case SQLCOM_SHOW_TABLES: @@ -2487,10 +2393,6 @@ static bool check_prepared_statement(Prepared_statement *stmt) res= mysql_test_set_fields(stmt, tables, &lex->var_list); break; - case SQLCOM_DELETE_MULTI: - res= mysql_test_multidelete(stmt, tables); - break; - case SQLCOM_INSERT_SELECT: case SQLCOM_REPLACE_SELECT: res= mysql_test_insert_select(stmt, tables); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 47ac7c8..3d7ec69 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -30219,7 +30219,6 @@ bool Sql_cmd_dml::execute(THD *thd) THD_STAGE_INFO(thd, stage_init); - DBUG_ASSERT(!lex->is_query_tables_locked()); /* Locking of tables is done after preparation but before optimization. This allows to do better partition pruning and avoid locking unused diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 1877194..b20b704 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -3017,12 +3017,10 @@ bool Sql_cmd_update::prepare_inner(THD *thd) { JOIN *join; int err= 0; - // uint table_cnt= 0; SELECT_LEX *const select_lex = thd->lex->first_select_lex(); TABLE_LIST *const table_list = select_lex->get_table_list(); ulonglong select_options= select_lex->options; bool free_join= 1; - // bool orig_multitable= multitable; DBUG_ENTER("Sql_cmd_update::prepare_inner"); if (!multitable) @@ -3076,8 +3074,8 @@ bool Sql_cmd_update::prepare_inner(THD *thd) DBUG_RETURN(TRUE); } - if (((multi_update *)result)->init(thd)) - DBUG_RETURN(TRUE); + if (((multi_update *)result)->init(thd)) + DBUG_RETURN(TRUE); if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, table_list, select_lex->leaf_tables, false, false)) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 9d112c9..61d4ed9 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -70,6 +70,7 @@ #include "sql_type_json.h" #include "json_table.h" #include "sql_update.h" +#include "sql_delete.h" /* this is to get the bison compilation windows warnings out */ #ifdef _MSC_VER @@ -1680,7 +1681,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); opt_mi_check_type opt_to mi_check_types table_to_table_list table_to_table opt_table_list opt_as handler_rkey_function handler_read_or_scan - single_multi table_wild_list table_wild_one opt_wild + single_multi opt_wild opt_and select_var_list select_var_list_init help opt_extended_describe shutdown @@ -13554,12 +13555,11 @@ delete: DELETE_SYM { LEX *lex= Lex; - lex->sql_command= SQLCOM_DELETE; YYPS->m_lock_type= TL_WRITE_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_WRITE; if (Lex->main_select_push()) MYSQL_YYABORT; - mysql_init_select(lex); + mysql_init_delete(lex); lex->ignore= 0; lex->first_select_lex()->order_list.empty(); } @@ -13617,12 +13617,22 @@ single_multi: delete_limit_clause opt_returning { + LEX *lex= Lex; if ($3) Select->order_list= *($3); - Lex->pop_select(); //main select + lex->pop_select(); //main select + lex->sql_command= SQLCOM_DELETE; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(false))) + MYSQL_YYABORT; } - | table_wild_list + | table_alias_ref_list { + LEX *lex= Lex; + lex->sql_command= SQLCOM_DELETE_MULTI; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(true))) + MYSQL_YYABORT; mysql_init_multi_delete(Lex); YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; @@ -13634,6 +13644,11 @@ single_multi: } stmt_end {} | FROM table_alias_ref_list { + LEX *lex= Lex; + lex->sql_command= SQLCOM_DELETE_MULTI; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(true))) + MYSQL_YYABORT; mysql_init_multi_delete(Lex); YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; @@ -13669,44 +13684,6 @@ opt_returning: } ; -table_wild_list: - table_wild_one - | table_wild_list ',' table_wild_one - ; - -table_wild_one: - ident opt_wild - { - Table_ident *ti= new (thd->mem_root) Table_ident(&$1); - if (unlikely(ti == NULL)) - MYSQL_YYABORT; - if (unlikely(!Select-> - add_table_to_list(thd, - ti, - NULL, - (TL_OPTION_UPDATING | - TL_OPTION_ALIAS), - YYPS->m_lock_type, - YYPS->m_mdl_type))) - MYSQL_YYABORT; - } - | ident '.' ident opt_wild - { - Table_ident *ti= new (thd->mem_root) Table_ident(thd, &$1, &$3, 0); - if (unlikely(ti == NULL)) - MYSQL_YYABORT; - if (unlikely(!Select-> - add_table_to_list(thd, - ti, - NULL, - (TL_OPTION_UPDATING | - TL_OPTION_ALIAS), - YYPS->m_lock_type, - YYPS->m_mdl_type))) - MYSQL_YYABORT; - } - ; - opt_wild: /* empty */ {} | '.' '*' {} diff --git a/sql/table.h b/sql/table.h index 2e074ab..1bf8ee3 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2311,6 +2311,7 @@ struct TABLE_LIST */ select_unit *derived_result; /* Stub used for materialized derived tables. */ + bool delete_while_scanning; table_map map; /* ID bit of table (1,2,4,8,16...) */ table_map get_map() {

1 0

[Commits] b9cd8aa: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list
by IgorBabaev 24 Mar '22

24 Mar '22

revision-id: b9cd8aa1de578b72e2875f7a6b730d43c1f3fc13 (mariadb-10.3.26-366-gb9cd8aa) parent(s): bbf02c85ba2e850da546199421cb75c224747475 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-24 12:36:22 -0700 message: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list This bug affected queries with IN predicates that contain parameter markers in the value list. Such queries are executed via prepared statements. The problem appeared only if the number of elements in the value list was greater than the set value of the system variable in_predicate_conversion_threshold. The patch unconditionally prohibits conversion of an IN predicate to the equivalent IN predicand if the value list of the IN predicate contains parameters markers. --- mysql-test/main/opt_tvc.result | 27 +++++++++++++++++++++++++++ mysql-test/main/opt_tvc.test | 26 ++++++++++++++++++++++++++ sql/item_cmpfunc.cc | 7 ++++--- sql/item_cmpfunc.h | 2 ++ sql/sql_tvc.cc | 27 ++++++++++++++++++++++++--- 5 files changed, 83 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index a68e70e..02d9096 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -732,3 +732,30 @@ a b 4 4 drop table t1; SET @@in_predicate_conversion_threshold= default; +# +# MDEV-27937: Prepared statement with ? in the list if IN predicate +# +set in_predicate_conversion_threshold=2; +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +drop table t1; +set in_predicate_conversion_threshold=default; +# End of 10.3 tests diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index e4e8c6d..f8469f2 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -428,3 +428,29 @@ eval $query; drop table t1; SET @@in_predicate_conversion_threshold= default; +--echo # +--echo # MDEV-27937: Prepared statement with ? in the list if IN predicate +--echo # + +set in_predicate_conversion_threshold=2; + +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); + +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +deallocate prepare stmt; + +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +deallocate prepare stmt; + +drop table t1; + +set in_predicate_conversion_threshold=default; + +--echo # End of 10.3 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 38f0a28..f41414f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4472,10 +4472,11 @@ void Item_func_in::mark_as_condition_AND_part(TABLE_LIST *embedding) Query_arena *arena, backup; arena= thd->activate_stmt_arena_if_needed(&backup); - if (to_be_transformed_into_in_subq(thd)) + if (!transform_into_subq_checked) { - transform_into_subq= true; - thd->lex->current_select->in_funcs.push_back(this, thd->mem_root); + if ((transform_into_subq= to_be_transformed_into_in_subq(thd))) + thd->lex->current_select->in_funcs.push_back(this, thd->mem_root); + transform_into_subq_checked= true; } if (arena) diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 4c88f5b..f3d3be4 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2299,6 +2299,7 @@ class Item_func_in :public Item_func_opt_neg, SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Field *field, Item *value); bool transform_into_subq; + bool transform_into_subq_checked; public: /// An array of values, created when the bisection lookup method is used in_vector *array; @@ -2321,6 +2322,7 @@ class Item_func_in :public Item_func_opt_neg, Item_func_opt_neg(thd, list), Predicant_to_list_comparator(thd, arg_count - 1), transform_into_subq(false), + transform_into_subq_checked(false), array(0), have_null(0), arg_types_compatible(FALSE), emb_on_expr_nest(0) { } diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 3866b7c..13efd97 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -900,8 +900,6 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, if (!transform_into_subq) return this; - transform_into_subq= false; - List<List_item> values; LEX *lex= thd->lex; @@ -1058,15 +1056,38 @@ uint32 Item_func_in::max_length_of_left_expr() bool Item_func_in::to_be_transformed_into_in_subq(THD *thd) { + bool is_row_list= args[1]->type() == Item::ROW_ITEM; uint values_count= arg_count-1; - if (args[1]->type() == Item::ROW_ITEM) + if (is_row_list) values_count*= ((Item_row *)(args[1]))->cols(); if (thd->variables.in_subquery_conversion_threshold == 0 || thd->variables.in_subquery_conversion_threshold > values_count) return false; + if (!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE)) + return true; + + /* Occurence of '?' in IN list is checked only for PREPARE <stmt> commands */ + for (uint i=1; i < arg_count; i++) + { + if (!is_row_list) + { + if (args[i]->type() == Item::PARAM_ITEM) + return false; + } + else + { + Item_row *row_list= (Item_row *)(args[i]); + for (uint j=0; j < row_list->cols(); j++) + { + if (row_list->element_index(j)->type() == Item::PARAM_ITEM) + return false; + } + } + } + return true; }

1 0

[Commits] 1902fc4: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list
by IgorBabaev 24 Mar '22

24 Mar '22

revision-id: 1902fc43b74c9dabedbde0da6b567fa184eaf6d1 (mariadb-10.3.26-366-g1902fc4) parent(s): bbf02c85ba2e850da546199421cb75c224747475 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-24 11:43:17 -0700 message: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list This bug affected queries with IN predicates that contain parameter markers in the value list. Such queries are executed via prepared statements. The problem appeared only if the number of elements in the value list was greater than the set value of the system variable in_predicate_conversion_threshold. The patch unconditionally prohibits conversion of an IN predicate to the equivalent IN predicand if the value list of the IN predicate contains parameters markers. --- mysql-test/main/opt_tvc.result | 27 +++++++++++++++++++++++++++ mysql-test/main/opt_tvc.test | 26 ++++++++++++++++++++++++++ sql/item_cmpfunc.cc | 7 ++++--- sql/item_cmpfunc.h | 2 ++ sql/sql_tvc.cc | 27 ++++++++++++++++++++++++--- 5 files changed, 83 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index a68e70e..02d9096 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -732,3 +732,30 @@ a b 4 4 drop table t1; SET @@in_predicate_conversion_threshold= default; +# +# MDEV-27937: Prepared statement with ? in the list if IN predicate +# +set in_predicate_conversion_threshold=2; +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +drop table t1; +set in_predicate_conversion_threshold=default; +# End of 10.3 tests diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index e4e8c6d..f8469f2 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -428,3 +428,29 @@ eval $query; drop table t1; SET @@in_predicate_conversion_threshold= default; +--echo # +--echo # MDEV-27937: Prepared statement with ? in the list if IN predicate +--echo # + +set in_predicate_conversion_threshold=2; + +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); + +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +deallocate prepare stmt; + +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +deallocate prepare stmt; + +drop table t1; + +set in_predicate_conversion_threshold=default; + +--echo # End of 10.3 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 38f0a28..f41414f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4472,10 +4472,11 @@ void Item_func_in::mark_as_condition_AND_part(TABLE_LIST *embedding) Query_arena *arena, backup; arena= thd->activate_stmt_arena_if_needed(&backup); - if (to_be_transformed_into_in_subq(thd)) + if (!transform_into_subq_checked) { - transform_into_subq= true; - thd->lex->current_select->in_funcs.push_back(this, thd->mem_root); + if ((transform_into_subq= to_be_transformed_into_in_subq(thd))) + thd->lex->current_select->in_funcs.push_back(this, thd->mem_root); + transform_into_subq_checked= true; } if (arena) diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 4c88f5b..f3d3be4 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2299,6 +2299,7 @@ class Item_func_in :public Item_func_opt_neg, SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Field *field, Item *value); bool transform_into_subq; + bool transform_into_subq_checked; public: /// An array of values, created when the bisection lookup method is used in_vector *array; @@ -2321,6 +2322,7 @@ class Item_func_in :public Item_func_opt_neg, Item_func_opt_neg(thd, list), Predicant_to_list_comparator(thd, arg_count - 1), transform_into_subq(false), + transform_into_subq_checked(false), array(0), have_null(0), arg_types_compatible(FALSE), emb_on_expr_nest(0) { } diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 3866b7c..13efd97 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -900,8 +900,6 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, if (!transform_into_subq) return this; - transform_into_subq= false; - List<List_item> values; LEX *lex= thd->lex; @@ -1058,15 +1056,38 @@ uint32 Item_func_in::max_length_of_left_expr() bool Item_func_in::to_be_transformed_into_in_subq(THD *thd) { + bool is_row_list= args[1]->type() == Item::ROW_ITEM; uint values_count= arg_count-1; - if (args[1]->type() == Item::ROW_ITEM) + if (is_row_list) values_count*= ((Item_row *)(args[1]))->cols(); if (thd->variables.in_subquery_conversion_threshold == 0 || thd->variables.in_subquery_conversion_threshold > values_count) return false; + if (!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE)) + return true; + + /* Occurence of '?' in IN list is checked only for PREPARE <stmt> commands */ + for (uint i=1; i < arg_count; i++) + { + if (!is_row_list) + { + if (args[i]->type() == Item::PARAM_ITEM) + return false; + } + else + { + Item_row *row_list= (Item_row *)(args[i]); + for (uint j=0; j < row_list->cols(); j++) + { + if (row_list->element_index(j)->type() == Item::PARAM_ITEM) + return false; + } + } + } + return true; }

1 0

[Commits] 8fff088: MDEV-24281 Reading from freed memory when running main.view with --ps-protocol
by IgorBabaev 23 Mar '22

23 Mar '22

revision-id: 8fff0886ddc95eb5f8dba60b8b859183f42bd494 (mariadb-10.3.26-356-g8fff088) parent(s): bfed2c7d57a7ca34936d6ef0688af7357592dc40 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-23 12:45:56 -0700 message: MDEV-24281 Reading from freed memory when running main.view with --ps-protocol This bug could affect prepared statements for the command CREATE VIEW with specification that contained unnamed basic constant in select list. If generation of a valid name for the corresponding view column required resolution of conflicts with names of other columns that were explicitly defined then execution of such prepared statement and following deallocation of this statement led to reading from freed memory. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/view.result | 28 ++++++++++++++++++++++++++++ mysql-test/main/view.test | 26 ++++++++++++++++++++++++++ sql/sql_view.cc | 3 ++- 3 files changed, 56 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index a410ab7..6483d76 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6839,5 +6839,33 @@ id bar Drop View v1; Drop table t1; # +# MDEV-24281: Execution of PREPARE from CREATE VIEW statement +# +create table t1 (s1 int); +insert into t1 values (3), (7), (1); +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +deallocate prepare stmt; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 's1' AS `My_exp_1_s1`,`t1`.`s1` AS `s1`,1 AS `My_exp_s1` from `t1` latin1 latin1_swedish_ci +select * from v1; +My_exp_1_s1 s1 My_exp_s1 +s1 3 1 +s1 7 1 +s1 1 1 +drop view v1; +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +execute stmt; +ERROR 42S01: Table 'v1' already exists +deallocate prepare stmt; +drop view v1; +drop table t1; +# # End of 10.3 tests # diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 431dfdb..46232b1 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6577,5 +6577,31 @@ Drop View v1; Drop table t1; --echo # +--echo # MDEV-24281: Execution of PREPARE from CREATE VIEW statement +--echo # + +create table t1 (s1 int); +insert into t1 values (3), (7), (1); + +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +--error ER_TABLE_EXISTS_ERROR +execute stmt; +deallocate prepare stmt; +drop view v1; +drop table t1; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 024bd36..b6787a1 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -96,7 +96,8 @@ static void make_unique_view_field_name(THD *thd, Item *target, itc.rewind(); } - target->orig_name= target->name.str; + if (!target->orig_name) + target->orig_name= target->name.str; target->set_name(thd, buff, name_len, system_charset_info); }

1 0

[Commits] 260d13f0fb4: Update test results (8)
by psergey 22 Mar '22

22 Mar '22

revision-id: 260d13f0fb40822d259fab76c6f9127d0c3e0167 (mariadb-10.6.1-373-g260d13f0fb4) parent(s): 304a2a6ca6a906afaf60c2650b21c2de7a06df60 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-22 10:07:39 +0300 message: Update test results (8) --- mysql-test/suite/maria/mrr.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/mysql-test/suite/maria/mrr.result b/mysql-test/suite/maria/mrr.result index 61450c948bf..2047febb92a 100644 --- a/mysql-test/suite/maria/mrr.result +++ b/mysql-test/suite/maria/mrr.result @@ -187,7 +187,7 @@ explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 2.86 Using index condition; Rowid-ordered scan +1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 FLTRD Using index condition; Rowid-ordered scan select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); a b c filler

1 0

[Commits] 304a2a6ca6a: Update test results (7)
by psergey 22 Mar '22

22 Mar '22

revision-id: 304a2a6ca6a906afaf60c2650b21c2de7a06df60 (mariadb-10.6.1-372-g304a2a6ca6a) parent(s): 7ba842d1e8d81ca99887e500187ec6dc6964c858 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-22 07:56:44 +0300 message: Update test results (7) --- mysql-test/include/mrr_tests.inc | 1 + mysql-test/main/myisam_mrr.result | 2 +- mysql-test/main/xtradb_mrr.result | 2 +- mysql-test/suite/innodb/r/innodb_mysql.result | 2 +- mysql-test/suite/innodb/t/innodb_mysql.test | 1 + 5 files changed, 5 insertions(+), 3 deletions(-) diff --git a/mysql-test/include/mrr_tests.inc b/mysql-test/include/mrr_tests.inc index ad7dff61477..a50cc8c16f0 100644 --- a/mysql-test/include/mrr_tests.inc +++ b/mysql-test/include/mrr_tests.inc @@ -89,6 +89,7 @@ insert into t4 (a,b,c,filler) insert into t4 (a,b,c,filler) select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; +--replace_column 11 FLTRD explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); diff --git a/mysql-test/main/myisam_mrr.result b/mysql-test/main/myisam_mrr.result index 57e6a443940..37ad7565a1b 100644 --- a/mysql-test/main/myisam_mrr.result +++ b/mysql-test/main/myisam_mrr.result @@ -189,7 +189,7 @@ explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t4 NULL range idx1 idx1 29 NULL 10 1.79 Using index condition; Rowid-ordered scan +1 SIMPLE t4 NULL range idx1 idx1 29 NULL 10 FLTRD Using index condition; Rowid-ordered scan select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); a b c filler diff --git a/mysql-test/main/xtradb_mrr.result b/mysql-test/main/xtradb_mrr.result index ceb98f2c7d5..015d1e61753 100644 --- a/mysql-test/main/xtradb_mrr.result +++ b/mysql-test/main/xtradb_mrr.result @@ -186,7 +186,7 @@ explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 2.86 Using index condition; Rowid-ordered scan +1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 FLTRD Using index condition; Rowid-ordered scan select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); a b c filler diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 7a2dfa7eec8..0bdf7965b0e 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -3373,7 +3373,7 @@ SELECT v2 FROM t1 WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL ref i,v i 5 const 2 100.00 Using where +1 SIMPLE t1 NULL ref i,v i 5 const 2 FLTRD Using where DROP TABLE t1; # # Bug#54606 innodb fast alter table + pack_keys=0 diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test index d495186db25..ae681fc8631 100644 --- a/mysql-test/suite/innodb/t/innodb_mysql.test +++ b/mysql-test/suite/innodb/t/innodb_mysql.test @@ -1015,6 +1015,7 @@ WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; --echo --echo # Should not use index_merge +--replace_column 11 FLTRD EXPLAIN SELECT v2 FROM t1

1 0