[Commits] d277c4682e4: MDEV-16751: Server crashes in st_join_table::cleanup or TABLE_LIST::is_with_table_recursive_reference
revision-id: d277c4682e4c6bb0e708296e6ae520c149dd78f4 (mariadb-5.5.60-47-gd277c4682e4) parent(s): 9cea4ccf12cb6e8746b9b440d9c62408a9ef04af author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-22 15:56:25 +0530 message: MDEV-16751: Server crashes in st_join_table::cleanup or TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 During muliple equality propagation for a query in which we have an IN subquery, the items in the select list of the subquery may not be part of the multiple equality because there might be another occurence of the same field in the where clause of the subquery. So we keyuse_is_valid_for_access_in_chosen_plan function which expects the items in the select list of the subquery to be same to the ones in the multiple equality (through these multiple equalities we create keyuse array). The solution would be that we expect the same field not the same Item because when we have SEMI JOIN MATERIALIZATION SCAN, we use copy back technique to copies back the materialised table fields to the original fields of the base tables. --- mysql-test/r/subselect_mat.result | 35 +++++++++++++++++++++++++++++++++++ mysql-test/r/subselect_sj_mat.result | 35 +++++++++++++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 25 +++++++++++++++++++++++++ sql/sql_select.cc | 4 +++- 4 files changed, 98 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 00448ac4f91..a56d076d528 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2360,6 +2360,41 @@ ec70316637232000158bbfc8bcbe5d60 ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-16751: Server crashes in st_join_table::cleanup or +# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 +# +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=4; +CREATE TABLE t1 ( id int NOT NULL); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ; +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +set @@join_cache_level= @save_join_cache_level; +alter table t1 add key(id); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +drop table t1,t2; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index cb5012a91c9..fa62259e3d6 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2400,4 +2400,39 @@ ec70316637232000158bbfc8bcbe5d60 ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-16751: Server crashes in st_join_table::cleanup or +# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 +# +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=4; +CREATE TABLE t1 ( id int NOT NULL); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ; +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +set @@join_cache_level= @save_join_cache_level; +alter table t1 add key(id); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +drop table t1,t2; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 90f63bea561..f1b64337702 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2151,4 +2151,29 @@ eval $q; DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +--echo # +--echo # MDEV-16751: Server crashes in st_join_table::cleanup or +--echo # TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 +--echo # + +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=4; +CREATE TABLE t1 ( id int NOT NULL); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); + +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ; +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); + +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); + +set @@join_cache_level= @save_join_cache_level; +alter table t1 add key(id); + +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); + +drop table t1,t2; --echo # End of 5.5 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6a64a0e9952..af6f63a55f1 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7438,7 +7438,9 @@ bool JOIN_TAB::keyuse_is_valid_for_access_in_chosen_plan(JOIN *join, st_select_lex *sjm_sel= emb_sj_nest->sj_subq_pred->unit->first_select(); for (uint i= 0; i < sjm_sel->item_list.elements; i++) { - if (sjm_sel->ref_pointer_array[i] == keyuse->val) + DBUG_ASSERT(keyuse->val->type() == Item::FIELD_ITEM); + Field *field = ((Item_field*)sjm_sel->ref_pointer_array[i])->field; + if (field->eq(((Item_field*)keyuse->val)->field)) return true; } return false;
On Sun, Jul 22, 2018 at 03:56:37PM +0530, Varun wrote:
revision-id: d277c4682e4c6bb0e708296e6ae520c149dd78f4 (mariadb-5.5.60-47-gd277c4682e4) parent(s): 9cea4ccf12cb6e8746b9b440d9c62408a9ef04af author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-22 15:56:25 +0530 message:
MDEV-16751: Server crashes in st_join_table::cleanup or TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2
During muliple equality propagation for a query in which we have an IN subquery, the items in the select list of the subquery may not be part of the multiple equality because there might be another occurence of the same field in the where clause of the subquery. So we keyuse_is_valid_for_access_in_chosen_plan function which expects the items in the select list of the subquery to be same to the ones in the multiple equality (through these multiple equalities we create keyuse array). The solution would be that we expect the same field not the same Item because when we have SEMI JOIN MATERIALIZATION SCAN, we use copy back technique to copies back the materialised table fields to the original fields of the base tables.
--- mysql-test/r/subselect_mat.result | 35 +++++++++++++++++++++++++++++++++++ mysql-test/r/subselect_sj_mat.result | 35 +++++++++++++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 25 +++++++++++++++++++++++++ sql/sql_select.cc | 4 +++- 4 files changed, 98 insertions(+), 1 deletion(-) ... diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6a64a0e9952..af6f63a55f1 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7438,7 +7438,9 @@ bool JOIN_TAB::keyuse_is_valid_for_access_in_chosen_plan(JOIN *join, st_select_lex *sjm_sel= emb_sj_nest->sj_subq_pred->unit->first_select(); for (uint i= 0; i < sjm_sel->item_list.elements; i++) { - if (sjm_sel->ref_pointer_array[i] == keyuse->val) + DBUG_ASSERT(keyuse->val->type() == Item::FIELD_ITEM); ^^(1) + Field *field = ((Item_field*)sjm_sel->ref_pointer_array[i])->field; ^^(2) + if (field->eq(((Item_field*)keyuse->val)->field)) return true; } return false;
As for typecast (2): As far as I understand, it is valid because of the following: - We get to this point in this function only when Semi-Join-Materialization-Scan is used. - SJM-Scan is only used when the subquery's select list has Item_field objects (This is checked in subquery_types_allow_materialization()). * Please add a comment in subquery_types_allow_materialization() about this function relying on that logic. * Please add here an assert that sjm_sel->ref_pointer_array[i] is an Item_field. The logic is complex and can be potentially broken. As for typecast (1): What if keyuse->val is not an Item_field? If we get to this point in this function, it means it's an expression that only depends on the SJ-Inner table. This cannot just happen due to limitations in subquery_types_allow_materialization(), and I wasn't able to construct an example where it would happen (tried playing with multiple-equalities, but substitute_for_best_equal_item is called after get_best_combination(), tried playing with constant tables but then SJ-Materialization is not used, etc. ) I think we will be safer by changing the assert to be this check: if (keyuse->val->type() == Item::FIELD_ITEM) { ... everything else } The idea is that a KEYUSE that satisfies the requirement will still be available, and we just want to filter out any extra KEYUSE that might pop up due to some optimization. what do you think? Ok to push after the above is addressed. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
participants (2)
-
Sergey Petrunia -
Varun