- commits - lists.mariadb.org

[Commits] ba1c6577a36: MDEV-26402: A SEGV in Item_field::used_tables/update_depend_map_for_order...
by psergey 21 Apr '22

21 Apr '22

revision-id: ba1c6577a36b078a41289f6259880ce77fe28711 (mariadb-10.4.23-65-gba1c6577a36) parent(s): aec856073df12e95b68667587bfd8e469b60e7d4 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-04-21 22:45:31 +0300 message: MDEV-26402: A SEGV in Item_field::used_tables/update_depend_map_for_order... When doing condition pushdown from HAVING into WHERE, Item_equal::create_pushable_equalities() calls item->set_extraction_flag(IMMUTABLE_FL) for constant items. Then, Item::cleanup_excluding_immutables_processor() checks for this flag to see if it should call item->cleanup() or leave the item as-is. The failure happens when a constant item has a non-constant one inside it, like: (tbl.col=0 AND impossible_cond) item->walk(cleanup_excluding_immutables_processor) works in a bottom-up way so it 1. will call Item_func_eq(tbl.col=0)->cleanup() 2. will not call Item_cond_and->cleanup (as the AND is constant) This creates an item tree where a fixed Item has an un-fixed Item inside it which eventually causes an assertion failure. Fixed by introducing this rule: instead of just calling item->set_extraction_flag(IMMUTABLE_FL); we call Item::walk() to set the flag for all sub-items of the item. --- mysql-test/main/having_cond_pushdown.result | 15 +++++++++++++++ mysql-test/main/having_cond_pushdown.test | 10 ++++++++++ sql/item.h | 6 ++++++ sql/item_cmpfunc.cc | 12 +++++++++++- 4 files changed, 42 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index 9b124296e3d..9a9ed2b9213 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -4924,3 +4924,18 @@ SELECT a FROM t1 GROUP BY a HAVING a = ( SELECT MIN(c) FROM t2 ); a 2 DROP TABLE t1,t2; +# +# MDEV-26402: A SEGV in Item_field::used_tables/update_depend_map_for_order or Assertion `fixed == 1' +# +CREATE TABLE t1 (i int NOT NULL); +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL); +i +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL AND 'x' = 0); +i +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +SELECT * FROM t1 GROUP BY i HAVING i='1' IN ( i IS NULL AND 'x' = 0); +i +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +DROP TABLE t1; diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test index fc75122615c..270eac2a46f 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -1440,3 +1440,13 @@ eval EXPLAIN FORMAT=JSON $q; eval $q; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-26402: A SEGV in Item_field::used_tables/update_depend_map_for_order or Assertion `fixed == 1' +--echo # + +CREATE TABLE t1 (i int NOT NULL); +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL); +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL AND 'x' = 0); +SELECT * FROM t1 GROUP BY i HAVING i='1' IN ( i IS NULL AND 'x' = 0); +DROP TABLE t1; diff --git a/sql/item.h b/sql/item.h index 9182b578cdb..af6f350216d 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1938,6 +1938,12 @@ class Item: public Value_source, return 0; } + virtual bool set_extraction_flag_processor(void *arg) + { + set_extraction_flag(*(int*)arg); + return 0; + } + /** Check db/table_name if they defined in item and match arg values diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 15f1766b2e6..0ec20efdf27 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -7533,7 +7533,17 @@ bool Item_equal::create_pushable_equalities(THD *thd, if (!eq || equalities->push_back(eq, thd->mem_root)) return true; if (!clone_const) - right_item->set_extraction_flag(IMMUTABLE_FL); + { + /* + Also set IMMUTABLE_FL for any sub-items of the right_item. + This is needed to prevent Item::cleanup_excluding_immutables_processor + from peforming cleanup of the sub-items and so creating an item tree + where a fixed item has non-fixed items inside it. + */ + int new_flag= IMMUTABLE_FL; + right_item->walk(&Item::set_extraction_flag_processor, false, + (void*)&new_flag); + } } while ((item=it++))

1 0

[Commits] 1d82b9b: MDEV-27212 Crash in Item_equal::sort on second execution of stored procedure
by IgorBabaev 21 Apr '22

21 Apr '22

revision-id: 1d82b9b7829659e0b94a7e2417235cb35fe7e19d (mariadb-10.2.31-1420-g1d82b9b) parent(s): 33ff18627ea009709bb0ba55b68f873e6c6c784c author: Igor Babaev committer: Igor Babaev timestamp: 2022-04-21 12:17:05 -0700 message: MDEV-27212 Crash in Item_equal::sort on second execution of stored procedure This bug could cause a crash of the server at the second call of a stored procedure when it executed a query containing a mergeable derived table / view whose specification used another mergeable derived_table or view and a subquery with outer reference in the select list of the specification. Such queries could cause the same problem when they were executed for the second time in a prepared mode. The problem appeared due to a typo mistake in the legacy code of the function create_view_field() that prevented building Item_direct_view_ref wrapper for the mentioned outer reference at the second execution of the query and setting the depended_from field for the outer reference. --- mysql-test/r/derived_view.result | 60 ++++++++++++++++++++++++++++++++++++++++ mysql-test/t/derived_view.test | 52 ++++++++++++++++++++++++++++++++++ sql/table.cc | 2 +- 3 files changed, 113 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 0c045e3..31a9220 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -3586,4 +3586,64 @@ f2 f3 DROP PROCEDURE p1; DROP VIEW v1,v2,v3; DROP TABLE t1; +# +# MDEV-27212: 2-nd execution of PS for select with embedded derived tables +# and correlated subquery in select list of outer derived +# +create table t1 ( id int, id2 int ) engine=myisam; +create table t2 ( x3 int , x1 int , x2 int, a1 int) engine=myisam; +insert into t1 values (3, 2), (4, 2), (3, 4); +insert into t2 values (1, 2, 2, 1), (1, 3, 3, 2), (2, 3, 3, 1); +prepare stmt from "select id from t1 +join +( select dt2.x1, +( select sum(a1) from t2 where t2.x1 = dt2.x1 ) m +from ( select x1 from t2 u where x3 = 1 ) dt2 +) dt +on t1.id = dt.x1 +where t1.id2 < dt.m"; +execute stmt; +id +3 +execute stmt; +id +3 +deallocate prepare stmt; +create procedure sp1() select id from t1 +join +( select dt2.x1, +( select sum(a1) from t2 where t2.x1 = dt2.x1 ) m +from ( select x1 from t2 u where x3 = 1 ) dt2 +) dt +on t1.id = dt.x1 +where t1.id2 < dt.m; +call sp1(); +id +3 +call sp1(); +id +3 +create view v2 as select x1 from t2 u where x3 = 1; +create view v as +select v2.x1, +( select sum(a1) from t2 where t2.x1 = v2.x1 ) m from v2; +prepare stmt from "select id from t1 join v on t1.id = v.x1 where t1.id2 < v.m"; +execute stmt; +id +3 +execute stmt; +id +3 +deallocate prepare stmt; +create procedure sp2() select id from t1 join v on t1.id = v.x1 where t1.id2 < v.m; +call sp2(); +id +3 +call sp2(); +id +3 +drop procedure sp1; +drop procedure sp2; +drop view v, v2; +drop table t1,t2; # End of 10.2 tests diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 0f3d9b2..f364012 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -2376,4 +2376,56 @@ DROP PROCEDURE p1; DROP VIEW v1,v2,v3; DROP TABLE t1; +--echo # +--echo # MDEV-27212: 2-nd execution of PS for select with embedded derived tables +--echo # and correlated subquery in select list of outer derived +--echo # +create table t1 ( id int, id2 int ) engine=myisam; +create table t2 ( x3 int , x1 int , x2 int, a1 int) engine=myisam; +insert into t1 values (3, 2), (4, 2), (3, 4); +insert into t2 values (1, 2, 2, 1), (1, 3, 3, 2), (2, 3, 3, 1); + +let $q= +select id from t1 + join + ( select dt2.x1, + ( select sum(a1) from t2 where t2.x1 = dt2.x1 ) m + from ( select x1 from t2 u where x3 = 1 ) dt2 + ) dt + on t1.id = dt.x1 +where t1.id2 < dt.m; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval create procedure sp1() $q; +call sp1(); +call sp1(); + +create view v2 as select x1 from t2 u where x3 = 1; +create view v as +select v2.x1, + ( select sum(a1) from t2 where t2.x1 = v2.x1 ) m from v2; + +let $q= +select id from t1 join v on t1.id = v.x1 where t1.id2 < v.m; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval create procedure sp2() $q; +call sp2(); +call sp2(); + +drop procedure sp1; +drop procedure sp2; + +drop view v, v2; + +drop table t1,t2; + --echo # End of 10.2 tests diff --git a/sql/table.cc b/sql/table.cc index 1f7b645..1060ee2 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5994,7 +5994,7 @@ Item *Field_iterator_view::create_item(THD *thd) Item *create_view_field(THD *thd, TABLE_LIST *view, Item **field_ref, const char *name) { - bool save_wrapper= thd->lex->select_lex.no_wrap_view_item; + bool save_wrapper= thd->lex->current_select->no_wrap_view_item; Item *field= *field_ref; DBUG_ENTER("create_view_field");

1 0

[Commits] baec17bc033: MDEV-25994: Crash with union of my_decimal type in ORDER BY clause
by psergey 21 Apr '22

21 Apr '22

revision-id: baec17bc033cb9be02428e2089046df9f530754a (mariadb-10.2.43-66-gbaec17bc033) parent(s): e4e25d2bacc067417c35750f5f6c44cad10c81de author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-04-21 15:03:23 +0300 message: MDEV-25994: Crash with union of my_decimal type in ORDER BY clause When single-row subquery fails with "Subquery reutrns more than 1 row" error, it will raise an error and return NULL. On the other hand, Item_singlerow_subselect sets item->maybe_null=0 for table-less subqueries like "(SELECT not_null_value)" (*) This discrepancy (item with maybe_null=0 returning NULL) causes the code in Type_handler_decimal_result::make_sort_key_part() to crash. Fixed this by allowing inference (*) only when the subquery is NOT a UNION. --- mysql-test/r/order_by.result | 20 ++++++++++++++++++++ mysql-test/r/subselect.result | 2 +- mysql-test/r/subselect_no_exists_to_in.result | 2 +- mysql-test/r/subselect_no_mat.result | 2 +- mysql-test/r/subselect_no_opts.result | 2 +- mysql-test/r/subselect_no_scache.result | 2 +- mysql-test/r/subselect_no_semijoin.result | 2 +- mysql-test/t/order_by.test | 25 +++++++++++++++++++++++++ sql/item_subselect.cc | 15 +++++++++++---- 9 files changed, 62 insertions(+), 10 deletions(-) diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index d1f2d067643..b21e350ac4a 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -3508,4 +3508,24 @@ DELETE FROM t1 ORDER BY c; DROP TABLE t1; SET @@SESSION.max_sort_length=DEFAULT; SET sql_mode=DEFAULT; +# +# MDEV-25994 Crash with union of my_decimal type in ORDER BY clause +# +CREATE TABLE t1 (v1 INTEGER) ; +INSERT INTO t1 (v1) VALUES (8); +UPDATE t1 SET v1 = 1 ORDER BY (SELECT 1.1 UNION SELECT -1); +ERROR 21000: Subquery returns more than 1 row +# This one must be successful +UPDATE t1 SET v1 = 2 ORDER BY (SELECT 1 UNION SELECT 1); +UPDATE t1 SET v1 = 3 ORDER BY (SELECT 'a' UNION SELECT 'b'); +ERROR 21000: Subquery returns more than 1 row +# Insert some more data +INSERT INTO t1 (v1) VALUES (8),(9),(100),(-234),(46584),(0); +UPDATE t1 SET v1 = v1+1 ORDER BY (SELECT 100.122 UNION SELECT -189.2); +ERROR 21000: Subquery returns more than 1 row +# This one must be successful +UPDATE t1 SET v1 = v1-200 ORDER BY (SELECT 1 UNION SELECT 1); +UPDATE t1 SET v1 = v1 ORDER BY (SELECT 'abc' UNION SELECT 'bbb'); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1; # End of 10.2 tests diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index a03a2cff207..ed3b49ff40d 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1261,7 +1261,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index f7da3fdb2e2..499cad5538a 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -1265,7 +1265,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 6ab304c190b..a6e97636eed 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -1268,7 +1268,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 338ddd5808b..571d0553212 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -1264,7 +1264,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 741b070a38b..b2b25c3efa8 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -1267,7 +1267,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index ebabafb9c77..af8df6a6331 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -1264,7 +1264,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 7cb24b7d03b..2187786deb7 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -2331,5 +2331,30 @@ DROP TABLE t1; SET @@SESSION.max_sort_length=DEFAULT; SET sql_mode=DEFAULT; +--echo # +--echo # MDEV-25994 Crash with union of my_decimal type in ORDER BY clause +--echo # + +CREATE TABLE t1 (v1 INTEGER) ; +INSERT INTO t1 (v1) VALUES (8); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = 1 ORDER BY (SELECT 1.1 UNION SELECT -1); +--echo # This one must be successful +UPDATE t1 SET v1 = 2 ORDER BY (SELECT 1 UNION SELECT 1); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = 3 ORDER BY (SELECT 'a' UNION SELECT 'b'); + +-- echo # Insert some more data +INSERT INTO t1 (v1) VALUES (8),(9),(100),(-234),(46584),(0); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = v1+1 ORDER BY (SELECT 100.122 UNION SELECT -189.2); +--echo # This one must be successful +UPDATE t1 SET v1 = v1-200 ORDER BY (SELECT 1 UNION SELECT 1); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = v1 ORDER BY (SELECT 'abc' UNION SELECT 'bbb'); + + +DROP TABLE t1; + --echo # End of 10.2 tests diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index e01c92b7a4f..7033d38c07b 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1266,11 +1266,18 @@ bool Item_singlerow_subselect::fix_length_and_dec() } unsigned_flag= value->unsigned_flag; /* - If there are not tables in subquery then ability to have NULL value - depends on SELECT list (if single row subquery have tables then it - always can be NULL if there are not records fetched). + If the subquery has no tables (1) and is not a UNION (2), like: + + (SELECT subq_value) + + then its NULLability is the same as subq_value's NULLability. + + (1): A subquery that uses a table will return NULL when the table is empty. + (2): A UNION subquery will return NULL if it produces a "Subquery returns + more than one row" error. */ - if (engine->no_tables()) + if (engine->no_tables() && + engine->engine_type() != subselect_engine::UNION_ENGINE) maybe_null= engine->may_be_null(); else {

1 0

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

07 Apr '22

revision-id: b904307368298cc2fa2fcb2ecdc0bac85f9c61d0 (mariadb-10.6.1-358-gb9043073682) parent(s): afa835568a1623991799830175039816b9ac2681 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-04-07 18:52:35 +0300 message: MDEV-28201: Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON - Describe the lifetime of EXPLAIN data structures in sql_explain.h:ExplainDataStructureLifetime. - Make Item_field::set_field() call set_refers_to_temp_table() when it refers to a temp. table. - Introduce QT_DONT_ACCESS_TMP_TABLES flag for Item::print. It directs Item_field::print to not try access its the temp table. - Introduce Explain_query::notify_tables_are_closed() and call it right before the query closes its tables. - Make Explain data stuctures' print_explain_json() methods accept "no_tmp_tbl" parameter which means pass QT_DONT_ACCESS_TMP_TABLES when printing items. - Make Show_explain_request::call_in_target_thread() not call set_current_thd(). This wasn't needed as the code inside lex->print_explain() uses output->thd anyway. output->thd refers to the SHOW command's THD object. --- mysql-test/main/show_analyze.result | 44 +++++++- mysql-test/main/show_analyze.test | 16 ++- mysql-test/main/show_analyze_json.result | 2 +- mysql-test/main/show_analyze_json.test | 2 +- mysql-test/main/show_explain.result | 2 +- mysql-test/main/show_explain.test | 2 +- mysql-test/main/show_explain_json.result | 2 +- mysql-test/main/show_explain_json.test | 2 +- mysql-test/suite/perfschema/r/nesting.result | 74 ++++++------ sql/item.cc | 20 +++- sql/my_apc.cc | 1 + sql/mysqld.h | 4 + sql/sql_base.cc | 12 ++ sql/sql_base.h | 1 + sql/sql_class.cc | 3 - sql/sql_explain.cc | 163 +++++++++++++++++---------- sql/sql_explain.h | 106 ++++++++++------- sql/sql_lex.cc | 3 +- sql/sql_parse.cc | 10 +- sql/sql_prepare.cc | 2 +- sql/sql_select.cc | 1 + sql/sql_show.cc | 10 +- 22 files changed, 319 insertions(+), 163 deletions(-) diff --git a/mysql-test/main/show_analyze.result b/mysql-test/main/show_analyze.result index 8f50bb4d970..56dac487117 100644 --- a/mysql-test/main/show_analyze.result +++ b/mysql-test/main/show_analyze.result @@ -379,7 +379,7 @@ drop table t0,t1; # connection con1; set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency'; -set debug_dbug='+d,log_slow_statement_end'; +set debug_dbug='+d,explain_notify_tables_are_closed'; SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency; connection default; SHOW ANALYZE FOR $thr2; @@ -391,3 +391,45 @@ count(*) - count(*) connection default; disconnect con1; set debug_sync='RESET'; +# +# MDEV-28201: Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON +# +CREATE TABLE t1 ( a varchar(1)); +INSERT INTO t1 VALUES ('a'),('b'); +ANALYZE format=json +SELECT 1 FROM t1 GROUP BY convert_tz('1969-12-31 22:00:00',a,'+10:00'); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "filesort": { + "sort_key": "convert_tz('1969-12-31 22:00:00',t1.a,'+10:00')", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 1, + "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + ] + } + } + } +} +DROP TABLE t1; diff --git a/mysql-test/main/show_analyze.test b/mysql-test/main/show_analyze.test index e4e16bb6159..bd0d3388f74 100644 --- a/mysql-test/main/show_analyze.test +++ b/mysql-test/main/show_analyze.test @@ -335,7 +335,7 @@ drop table t0,t1; let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2; connection con1; set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency'; -set debug_dbug='+d,log_slow_statement_end'; +set debug_dbug='+d,explain_notify_tables_are_closed'; # Statement guarantees to produce 0 on every run send SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency; @@ -350,4 +350,16 @@ reap; --echo # End connection default; disconnect con1; -set debug_sync='RESET'; \ No newline at end of file +set debug_sync='RESET'; + + +--echo # +--echo # MDEV-28201: Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON +--echo # +CREATE TABLE t1 ( a varchar(1)); +INSERT INTO t1 VALUES ('a'),('b'); +--source include/analyze-format.inc +ANALYZE format=json +SELECT 1 FROM t1 GROUP BY convert_tz('1969-12-31 22:00:00',a,'+10:00'); +DROP TABLE t1; + diff --git a/mysql-test/main/show_analyze_json.result b/mysql-test/main/show_analyze_json.result index 5b34d8f0952..dc8ae2aa66b 100644 --- a/mysql-test/main/show_analyze_json.result +++ b/mysql-test/main/show_analyze_json.result @@ -1228,7 +1228,7 @@ drop table t0,t1; # connection con1; set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency'; -set debug_dbug='+d,log_slow_statement_end'; +set debug_dbug='+d,explain_notify_tables_are_closed'; SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency; connection default; SHOW ANALYZE FORMAT=JSON FOR $thr2; diff --git a/mysql-test/main/show_analyze_json.test b/mysql-test/main/show_analyze_json.test index 18eea8c7a9a..1644c32f261 100644 --- a/mysql-test/main/show_analyze_json.test +++ b/mysql-test/main/show_analyze_json.test @@ -370,7 +370,7 @@ drop table t0,t1; let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2; connection con1; set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency'; -set debug_dbug='+d,log_slow_statement_end'; +set debug_dbug='+d,explain_notify_tables_are_closed'; # Statement guarantees to produce 0 on every run send SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency; diff --git a/mysql-test/main/show_explain.result b/mysql-test/main/show_explain.result index 35a528aff4e..e747126f8b7 100644 --- a/mysql-test/main/show_explain.result +++ b/mysql-test/main/show_explain.result @@ -1427,7 +1427,7 @@ drop table t0,t1,t2; # connection con1; set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency'; -set debug_dbug='+d,log_slow_statement_end'; +set debug_dbug='+d,explain_notify_tables_are_closed'; SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency; connection default; SHOW EXPLAIN FOR $thr2; diff --git a/mysql-test/main/show_explain.test b/mysql-test/main/show_explain.test index 47073628ca7..b71dcc0bcfd 100644 --- a/mysql-test/main/show_explain.test +++ b/mysql-test/main/show_explain.test @@ -1312,7 +1312,7 @@ drop table t0,t1,t2; let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2; connection con1; set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency'; -set debug_dbug='+d,log_slow_statement_end'; +set debug_dbug='+d,explain_notify_tables_are_closed'; # Statement guarantees to produce 0 on every run send SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency; diff --git a/mysql-test/main/show_explain_json.result b/mysql-test/main/show_explain_json.result index 4713d8194f0..a5c441af5b8 100644 --- a/mysql-test/main/show_explain_json.result +++ b/mysql-test/main/show_explain_json.result @@ -1294,7 +1294,7 @@ Note 1051 Unknown table 'test.t2' # connection con1; set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency'; -set debug_dbug='+d,log_slow_statement_end'; +set debug_dbug='+d,explain_notify_tables_are_closed'; SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency; connection default; SHOW EXPLAIN FORMAT=JSON FOR $thr2; diff --git a/mysql-test/main/show_explain_json.test b/mysql-test/main/show_explain_json.test index 3799ff7ec04..6dbcacd96f6 100644 --- a/mysql-test/main/show_explain_json.test +++ b/mysql-test/main/show_explain_json.test @@ -478,7 +478,7 @@ drop table if exists t0,t1,t2; let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2; connection con1; set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency'; -set debug_dbug='+d,log_slow_statement_end'; +set debug_dbug='+d,explain_notify_tables_are_closed'; # Statement guarantees to produce 0 on every run send SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency; 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/my_apc.cc b/sql/my_apc.cc index cef00c0a738..9777deb399a 100644 --- a/sql/my_apc.cc +++ b/sql/my_apc.cc @@ -187,6 +187,7 @@ bool Apc_target::make_apc_call(THD *caller_thd, Apc_call *call, else { #ifndef DBUG_OFF + /* We didn't make the call, because the target is disabled */ n_calls_processed++; #endif mysql_mutex_unlock(LOCK_thd_kill_ptr); 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..c82db898f3b 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -759,6 +759,18 @@ close_all_tables_for_name(THD *thd, TABLE_SHARE *share, } +int close_thread_tables_for_query(THD *thd) +{ + if (thd->lex && thd->lex->explain) + thd->lex->explain->notify_tables_are_closed(); + + DBUG_EXECUTE_IF("explain_notify_tables_are_closed", + if (dbug_user_var_equals_str(thd, "show_explain_probe_query", + thd->query())) + dbug_serve_apcs(thd, 1); + ); + return close_thread_tables(thd); +} /* Close all tables used by the current substatement, or all tables used by this thread if we are on the upper level. diff --git a/sql/sql_base.h b/sql/sql_base.h index 5b449fdddac..c593d85b5e7 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -161,6 +161,7 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table, const LEX_CSTRING *db_name, const LEX_CSTRING *table_name); int close_thread_tables(THD *thd); +int close_thread_tables_for_query(THD *thd); void switch_to_nullable_trigger_fields(List<Item> &items, TABLE *); void switch_defaults_to_nullable_trigger_fields(TABLE *table); bool fill_record_n_invoke_before_triggers(THD *thd, TABLE *table, 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..d992762c611 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -39,12 +39,12 @@ 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), - unions(root), selects(root), thd(thd_arg), apc_enabled(false), + unions(root), selects(root), stmt_thd(thd_arg), apc_enabled(false), operations(0) { } @@ -65,7 +65,7 @@ static void print_json_array(Json_writer *writer, Explain_query::~Explain_query() { if (apc_enabled) - thd->apc_target.disable(); + stmt_thd->apc_target.disable(); delete upd_del_plan; delete insert_plan; @@ -155,20 +155,32 @@ void Explain_query::add_upd_del_plan(Explain_update *upd_del_plan_arg) void Explain_query::query_plan_ready() { if (!apc_enabled) - thd->apc_target.enable(); + stmt_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. The query is about to close + the tables it is using, which will make it impossible to print Item* + values. See sql_explain.h:ExplainDataStructureLifetime for details. + */ if (apc_enabled) { - thd->apc_target.disable(); + stmt_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); @@ -225,27 +237,48 @@ int Explain_query::print_explain(select_result_sink *output, } +/* + @param is_show_cmd TRUE<=> This is a SHOW EXPLAIN|ANALYZE command. + (These commands may be called at late stage in + the query processing, we need to pass no_tmp_tbl=true + to other print functions) +*/ + 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(); @@ -253,6 +286,7 @@ int Explain_query::print_explain_json(select_result_sink *output, CHARSET_INFO *cs= system_charset_info; List<Item> item_list; const String *buf= writer.output.get_string(); + THD *thd= output->thd; item_list.push_back(new (thd->mem_root) Item_string(thd, buf->ptr(), buf->length(), cs), thd->mem_root); @@ -607,7 +641,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 +685,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 +748,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 +769,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 +949,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 +972,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 +994,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 +1012,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 +1035,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 +1046,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 +1056,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 +1086,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 +1107,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 +1120,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 +1130,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 +1151,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 +1164,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 +1173,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); } @@ -1290,7 +1332,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai uint select_id, const char *select_type, bool using_temporary, bool using_filesort) { - THD *thd= output->thd; + THD *thd= output->thd; // note: for SHOW EXPLAIN, this is target thd. MEM_ROOT *mem_root= thd->mem_root; List<Item> item_list; @@ -1531,7 +1573,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 +1583,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 +1627,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 +1645,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 +1758,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 +1789,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 +1926,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 +1947,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 +1971,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 +1981,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 +1989,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 +2295,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 +2311,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 +2414,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 +2580,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 +2592,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 +2622,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 +2632,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..2847fe4a5e1 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; @@ -501,7 +521,7 @@ class Explain_query : public Sql_alloc Dynamic_array<Explain_union*> unions; Dynamic_array<Explain_select*> selects; - THD *thd; // for APC start/stop + THD *stmt_thd; // for APC start/stop bool apc_enabled; /* Debugging aid: count how many times add_node() was called. Ideally, it @@ -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..8dc5bd65dd9 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2559,11 +2559,6 @@ void log_slow_statement(THD *thd) end: delete_explain_query(thd->lex); - DBUG_EXECUTE_IF("log_slow_statement_end", - if (dbug_user_var_equals_str(thd, "show_explain_probe_query", - thd->query())) - dbug_serve_apcs(thd, 1); - ); DBUG_VOID_RETURN; } @@ -6058,7 +6053,7 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) } /* Free tables. Set stage 'closing tables' */ - close_thread_tables(thd); + close_thread_tables_for_query(thd); #ifndef DBUG_OFF @@ -6207,7 +6202,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_prepare.cc b/sql/sql_prepare.cc index e025147c71e..ba97bd1c389 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -4440,7 +4440,7 @@ bool Prepared_statement::prepare(const char *packet, uint packet_len) /* No need to commit statement transaction, it's not started. */ DBUG_ASSERT(thd->transaction->stmt.is_empty()); - close_thread_tables(thd); + close_thread_tables_for_query(thd); thd->mdl_context.rollback_to_savepoint(mdl_savepoint); /* diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 58f8f000f11..531179f4089 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26917,6 +26917,7 @@ int print_explain_message_line(select_result_sink *result, ha_rows *rows, const char *message) { + /* Note: for SHOW EXPLAIN, this is caller thread's THD */ THD *thd= result->thd; MEM_ROOT *mem_root= thd->mem_root; Item *item_null= new (mem_root) Item_null(thd); diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 7be12ccd911..5fcc6182677 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2999,14 +2999,18 @@ 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 (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 (!printed_anything) failed_to_produce= TRUE; @@ -3025,6 +3029,8 @@ int select_result_explain_buffer::send_data(List<Item> &items) Switch to the receiveing thread, so that we correctly count memory used by it. This is needed as it's the receiving thread that will free the memory. + (TODO: Now that we don't change current_thd in + Show_explain_request::call_in_target_thread, is this necessary anymore?) */ set_current_thd(thd); fill_record(thd, dst_table, dst_table->field, items, TRUE, FALSE);

1 0

[Commits] 5a8766a9805: Better comments in Item_in_subselect::inject_in_to_exists_cond()
by psergey 07 Apr '22

07 Apr '22

revision-id: 5a8766a98059b93798aa34d6824c6e130727d552 (mariadb-10.2.43-54-g5a8766a9805) parent(s): 53b580a91c12e9272623fc45496631be65313dd8 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-04-07 15:40:34 +0300 message: Better comments in Item_in_subselect::inject_in_to_exists_cond() --- sql/item_subselect.cc | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 92ba085af5b..e01c92b7a4f 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2757,6 +2757,8 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg) } where_item= and_items(thd, join_arg->conds, where_item); + + /* This is the fix_fields() call mentioned in the comment above */ if (!where_item->fixed && where_item->fix_fields(thd, 0)) DBUG_RETURN(true); // TIMOUR TODO: call optimize_cond() for the new where clause @@ -2767,7 +2769,10 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg) /* Attach back the list of multiple equalities to the new top-level AND. */ if (and_args && join_arg->cond_equal) { - /* The argument list of the top-level AND may change after fix fields. */ + /* + The fix_fields() call above may have changed the argument list, so + fetch it again: + */ and_args= ((Item_cond*) join_arg->conds)->argument_list(); ((Item_cond_and *) (join_arg->conds))->m_cond_equal= *join_arg->cond_equal;

1 0

[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