- commits - lists.mariadb.org

[Commits] c01ee954bf3: MDEV-26047: MariaDB server crash at Item_subselect::init_expr_cache_tracker
by psergey 22 Apr '22

22 Apr '22

revision-id: c01ee954bf3b10fef85af7b8c77d319ff7bd6b61 (mariadb-10.2.43-72-gc01ee954bf3) parent(s): 3c209bfc040ddfc41ece8357d772547432353fd2 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-04-22 20:26:14 +0300 message: MDEV-26047: MariaDB server crash at Item_subselect::init_expr_cache_tracker The cause of crash: remove_redundant_subquery_clauses() removes redundant item expressions. The primary goal of this is to remove the subquery items. The removal process unlinks the subquery from SELECT_LEX tree, but does not remove it from SELECT_LEX:::ref_pointer_array or from JOIN::all_fields. Then, setup_subquery_caches() tries to wrap the subquery item in an expression cache, which fails, the first reason for failure being that the item doesn't have a query plan. Solution: do not wrap eliminated items with expression cache. (also added an assert to check that we do not attempt to execute them). This may look like an incomplete fix: why don't we remove any mention of eliminated item everywhere? The difficulties here are: * items can be "un-removed" (see set_fake_select_as_master_processor) * it's difficult to remove an element from ref_pointer_array: Item_ref objects refer to elements of that array, so one can't shift elements in it. Replacing eliminated subselect with a dummy Item doesn't look like a good idea, either. --- mysql-test/r/subselect_innodb.result | 32 ++++++++++++++++++++++++++++++++ mysql-test/t/subselect_innodb.test | 28 ++++++++++++++++++++++++++++ sql/item_subselect.cc | 11 +++++++++++ 3 files changed, 71 insertions(+) diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 4eaea099451..4796245f8e3 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -629,3 +629,35 @@ a b 2019-03-10 02:55:05 2019-03-10 02:55:05 DROP TABLE t1,t2; set character_set_connection=@save_character_set_connection; +# +# MDEV-26047: MariaDB server crash at Item_subselect::init_expr_cache_tracker +# +CREATE TABLE t1 (a int) engine=innodb; +SELECT 1 IN ( +SELECT NULL +FROM t1 +WHERE +a IS NOT NULL +GROUP BY +(SELECT NULL from dual WHERE a = 1) +); +1 IN ( +SELECT NULL +FROM t1 +WHERE +a IS NOT NULL +GROUP BY +(SELECT NULL from dual WHERE a = 1) +) +0 +drop table t1; +# Testcase from MDEV-26164 +create table t1(a int); +# Disable the warning as it includes current time and changes for every test run. +select 1 from t1 where not exists +( +select 1 from t1 where binary current_time() +group by (select a),(select 1) +); +1 +drop table t1; diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 2c117fe00d6..f42ac514d53 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -627,3 +627,31 @@ SELECT * FROM t1 WHERE (SELECT 1,CONCAT(a) FROM t1) = (SELECT 1,CONCAT(a) FROM t DROP TABLE t1,t2; set character_set_connection=@save_character_set_connection; + +--echo # +--echo # MDEV-26047: MariaDB server crash at Item_subselect::init_expr_cache_tracker +--echo # +CREATE TABLE t1 (a int) engine=innodb; + +SELECT 1 IN ( + SELECT NULL + FROM t1 + WHERE + a IS NOT NULL + GROUP BY + (SELECT NULL from dual WHERE a = 1) +); +drop table t1; + +--echo # Testcase from MDEV-26164 +create table t1(a int); +--echo # Disable the warning as it includes current time and changes for every test run. +--disable_warnings +select 1 from t1 where not exists +( + select 1 from t1 where binary current_time() + group by (select a),(select 1) +); +--enable_warnings +drop table t1; + diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 7033d38c07b..de9b5dc8438 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -752,6 +752,7 @@ bool Item_subselect::exec() DBUG_ENTER("Item_subselect::exec"); DBUG_ASSERT(fixed); + DBUG_ASSERT(!eliminated); /* Do not execute subselect in case of a fatal error @@ -1313,6 +1314,16 @@ Item* Item_singlerow_subselect::expr_cache_insert_transformer(THD *tmp_thd, DBUG_ASSERT(thd == tmp_thd); + /* + Do not create subquery cache if the subquery was eliminated. + The optimizer may eliminate subquery items (see + eliminate_subselect_processor). However it does not update + all query's data structures, so the eliminated item may be + still reachable. + */ + if (eliminated) + DBUG_RETURN(this); + if (expr_cache) DBUG_RETURN(expr_cache);

1 0

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

22 Apr '22

revision-id: 173e232a67a8f9f6bd4321a8069319a78698ed58 (mariadb-10.2.31-1420-g173e232) parent(s): 33ff18627ea009709bb0ba55b68f873e6c6c784c author: Igor Babaev committer: Igor Babaev timestamp: 2022-04-22 07:53:16 -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. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- 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] 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