revision-id: 793b74ba759845db885ceb1b9cbc5f8e99fd2cc3 (mariadb-10.4.4-30-g793b74b) parent(s): 878ca5ca4f8c5598f2e289a753327c87b5a8818c author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-19 16:17:51 -0700 message: MDEV-19255 Server crash in st_join_table::save_explain_data or assertion `sel->quick' failure in JOIN::make_range_rowid_filters upon query with rowid_filter=ON Index ranges can be defined using conditions with inexpensive subqueries. Such a subquery is evaluated when some representation of a possible range sequence is built. After the evaluation the JOIN structure of the subsquery is distroyed. Any attempt to build the above representation may fail because the function that checks whether a subquery is inexpensive in some cases uses the join structure of the subquery. When a range rowid filter is built by a range sequence constructed out of a range condition that uses an inexpensive subquery the representation of the the sequence is built twice. Building the second representation fails due to the described problem with the execution of Item_subselect::is_expensive(). The function was corrected to return the result of the last its invocation if the Item_subselect object has been already evaluated. --- mysql-test/main/rowid_filter.result | 92 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter.test | 39 +++++++++++++ mysql-test/main/rowid_filter_innodb.result | 92 ++++++++++++++++++++++++++++++ sql/item_subselect.cc | 20 ++++--- sql/item_subselect.h | 2 + 5 files changed, 237 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index 280ced7..efe914f 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -2012,4 +2012,96 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 DROP TABLE t1,t2; +# +# MDEV-19255: rowid range filter built for range condition +# that uses in expensive subquery +# +CREATE TABLE t1 ( +pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +pk1 a1 b1 pk2 a2 b2 +65 2 a 109 65 NULL +EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where +1 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` +EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 101, + "filtered": 100, + "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "b1"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk1"], + "ref": ["test.t2.a2"], + "rowid_filter": { + "range": { + "key": "b1", + "used_key_parts": ["b1"] + }, + "rows": 87, + "selectivity_pct": 87 + }, + "rows": 1, + "filtered": 87, + "attached_condition": "t1.b1 <= (subquery#2)" + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "range", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk2"], + "rows": 1, + "filtered": 100, + "index_condition": "t2.pk2 <= 1" + } + } + } + ] + } +} +DROP TABLE t1,t2; set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index 9c53367..6f26e81 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -300,4 +300,43 @@ SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3); DROP TABLE t1,t2; +--echo # +--echo # MDEV-19255: rowid range filter built for range condition +--echo # that uses in expensive subquery +--echo # + +CREATE TABLE t1 ( + pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); + +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; + +let $q= +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) + WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); + +eval $q; +eval EXPLAIN EXTENDED $q; +eval EXPLAIN FORMAT=JSON $q; + +DROP TABLE t1,t2; + set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index c877e5a..54c7e03 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1941,6 +1941,98 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 DROP TABLE t1,t2; +# +# MDEV-19255: rowid range filter built for range condition +# that uses in expensive subquery +# +CREATE TABLE t1 ( +pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +pk1 a1 b1 pk2 a2 b2 +65 2 a 109 65 NULL +EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where +1 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` +EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 101, + "filtered": 100, + "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "b1"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk1"], + "ref": ["test.t2.a2"], + "rowid_filter": { + "range": { + "key": "b1", + "used_key_parts": ["b1"] + }, + "rows": 87, + "selectivity_pct": 87 + }, + "rows": 1, + "filtered": 87, + "attached_condition": "t1.b1 <= (subquery#2)" + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "range", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk2"], + "rows": 1, + "filtered": 100, + "index_condition": "t2.pk2 <= 1" + } + } + } + ] + } +} +DROP TABLE t1,t2; set @@use_stat_tables=@save_use_stat_tables; # # MDEV-18755: possible RORI-plan and possible plan with range filter diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 4fb9abc..2b1c4c1 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -54,7 +54,8 @@ Item_subselect::Item_subselect(THD *thd_arg): value_assigned(0), own_engine(0), thd(0), old_engine(0), have_to_be_excluded(0), inside_first_fix_fields(0), done_first_fix_fields(FALSE), - expr_cache(0), forced_const(FALSE), substitution(0), engine(0), eliminated(FALSE), + expr_cache(0), forced_const(FALSE), expensive_fl(FALSE), + substitution(0), engine(0), eliminated(FALSE), changed(0), is_correlated(FALSE), with_recursive_reference(0) { DBUG_ENTER("Item_subselect::Item_subselect"); @@ -585,6 +586,9 @@ bool Item_subselect::is_expensive() double examined_rows= 0; bool all_are_simple= true; + if (!expensive_fl && is_evaluated()) + return false; + /* check extremely simple select */ if (!unit->first_select()->next_select()) // no union { @@ -595,7 +599,7 @@ bool Item_subselect::is_expensive() SELECT_LEX *sl= unit->first_select(); JOIN *join = sl->join; if (join && !join->tables_list && !sl->first_inner_unit()) - return false; + return (expensive_fl= false); } @@ -605,14 +609,14 @@ bool Item_subselect::is_expensive() /* not optimized subquery */ if (!cur_join) - return true; + return (expensive_fl= true); /* If the subquery is not optimised or in the process of optimization it supposed to be expensive */ if (cur_join->optimization_state != JOIN::OPTIMIZATION_DONE) - return true; + return (expensive_fl= true); if (!cur_join->tables_list && !sl->first_inner_unit()) continue; @@ -634,7 +638,7 @@ bool Item_subselect::is_expensive() considered optimized if it has a join plan. */ if (!cur_join->join_tab) - return true; + return (expensive_fl= true); if (sl->first_inner_unit()) { @@ -642,15 +646,15 @@ bool Item_subselect::is_expensive() Subqueries that contain subqueries are considered expensive. @todo: accumulate the cost of subqueries. */ - return true; + return (expensive_fl= true); } examined_rows+= cur_join->get_examined_rows(); } // here we are sure that subquery is optimized so thd is set - return !all_are_simple && - (examined_rows > thd->variables.expensive_subquery_limit); + return (expensive_fl= !all_are_simple && + (examined_rows > thd->variables.expensive_subquery_limit)); } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 0e771ba..bbc24d3 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -73,6 +73,8 @@ class Item_subselect :public Item_result_field, to substitute 'this' with a constant item. */ bool forced_const; + /* Set to the result of the last call of is_expensive() */ + bool expensive_fl; #ifndef DBUG_OFF /* Count the number of times this subquery predicate has been executed. */ uint exec_counter;