- commits - lists.mariadb.org

[Commits] d6fd177: MDEV-22786 Crashes with nested table value constructors
by IgorBabaev 03 Mar '21

03 Mar '21

revision-id: d6fd177a5f0d0984da4aec46de04bbc84bce00a2 (mariadb-10.3.26-101-gd6fd177) parent(s): 11c4e9be19916d3dc4f77647aa99781ddacc88d7 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-03 09:16:15 -0800 message: MDEV-22786 Crashes with nested table value constructors The bug caused crashes of the server when processing queries with nested table value constructors (TVC) . It happened because the grammar rules to parse TVC used the same global lists for both nested TVC and nesting TVC. As a result invalid select trees were constructed for queries with nested TVC and this led to crashes at the prepare stage. This patch provides its own lists structures for each TVC nest level. Besides the patch fixes a bug in the function wrap_tvc() that missed inheritance of the SELECT_LEX::exclude_from_table_unique_test for selects that wrapped TVCs. This inheritance is critical for specifications of derived tables that employ nested TVCs. --- mysql-test/main/table_value_constr.result | 8 ++++++++ mysql-test/main/table_value_constr.test | 3 +++ sql/sql_lex.cc | 2 -- 3 files changed, 11 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 0d18df1..ff6d19a 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -3053,5 +3053,13 @@ select * from (values ((values ((select a from t1 where a=7))))) dt; select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt; (values ((select (values(2)) from t1 where a=8))) NULL +insert into t1(a) values ((values (2))), ((values (3))); +select * from t1; +a +3 +7 +1 +2 +3 drop table t1; End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index ec147e6..3e976f8 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1623,6 +1623,9 @@ select * from (values ((values ((select a from t1 where a=7))))) dt; select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt; +insert into t1(a) values ((values (2))), ((values (3))); +select * from t1; + drop table t1; --echo End of 10.3 tests diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 495b27c..c2bc838 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -8324,7 +8324,6 @@ void LEX::tvc_start() mysql_init_select(this); else save_values_list_state(); - field_list.empty(); many_values.empty(); insert_list= 0; } @@ -8336,7 +8335,6 @@ bool LEX::tvc_start_derived() unlikely(mysql_new_select(this, 1, NULL))) return true; save_values_list_state(); - field_list.empty(); many_values.empty(); insert_list= 0; return false;

1 0

[Commits] 11c4e9b: MDEV-22786 Crashes with nested table value constructors
by IgorBabaev 02 Mar '21

02 Mar '21

revision-id: 11c4e9be19916d3dc4f77647aa99781ddacc88d7 (mariadb-10.3.26-100-g11c4e9b) parent(s): 0f81ca6a0bb21fbba4bca93a7555f7c8e6357b47 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-01 20:23:16 -0800 message: MDEV-22786 Crashes with nested table value constructors The bug caused crashes of the server when processing queries with nested table value constructors (TVC) . It happened because the grammar rules to parse TVC used the same global lists for both nested TVC and nesting TVC. As a result invalid select trees were constructed for queries with nested TVC anf this led to crashes at the prepare stage. This patch provides its own lists structures for each TVC nest level. Besides the patch fixes a bug in the function wrap_tvc() that missed inheritance of the SELECT_LEX::exclude_from_table_unique_test for selects that wrapped TVCs. This inheritance is critical for specifications of derived tables that employ nested TVCs. --- mysql-test/main/table_value_constr.result | 167 ++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 103 ++++++++++++++++++ sql/sql_lex.cc | 47 ++++++++- sql/sql_lex.h | 12 +-- sql/sql_tvc.cc | 2 + sql/sql_yacc.yy | 3 +- sql/sql_yacc_ora.yy | 3 +- 7 files changed, 324 insertions(+), 13 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index d2965ab..0d18df1 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2887,4 +2887,171 @@ drop table t1,t2,t3; select sum((values(1))); sum((values(1))) 1 +# +# MDEV-22786: Nested table values constructors +# +values ((values (2))); +(values (2)) +2 +values ((values (2)), (5), (select 4)); +(values (2)) 5 (select 4) +2 5 4 +values ((7), (values (2)), (5), (select 4)); +7 (values (2)) 5 (select 4) +7 2 5 4 +values ((values (2))) union values ((values (3))); +(values (2)) +2 +3 +values ((values (2))), ((values (3))); +(values (2)) +2 +3 +values ((values (2))), ((select 4)), ((values (3))); +(values (2)) +2 +4 +3 +values ((values (4)), (values (5))), ((values (1)), (values (7))); +(values (4)) (values (5)) +4 5 +1 7 +values ((values (4)), (select 5)), ((select 1), (values (7))); +(values (4)) (select 5) +4 5 +1 7 +values ((select 2)) union values ((values (3))); +(select 2) +2 +3 +values ((values (2))) union values((select 3)); +(values (2)) +2 +3 +values ((values (2))) union all values ((values (2))); +(values (2)) +2 +2 +values ((values (4)), (values (5))), ((values (1)), (values (7))) +union +values ((values (4)), (select 5)), ((select 2), (values (8))); +(values (4)) (values (5)) +4 5 +1 7 +2 8 +values ((values (4)), (values (5))), ((values (1)), (values (7))) +union all +values ((values (4)), (select 5)), ((select 2), (values (8))); +(values (4)) (values (5)) +4 5 +1 7 +4 5 +2 8 +values ((values (1) union values (1))); +(values (1) union values (1)) +1 +values ((values (1) union values (1) union values (1))); +(values (1) union values (1) union values (1)) +1 +values ((values ((values (4))))); +(values ((values (4)))) +4 +values ((values ((select 5)))); +(values ((select 5))) +5 +values ((select (values (4))), (values ((values(5))))); +(select (values (4))) (values ((values(5)))) +4 5 +values ((select (values (4))), (values ((select 5)))); +(select (values (4))) (values ((select 5))) +4 5 +values ((select (values (4))), (values ((values(5))))) +union +values ((select (values (4))), (values ((select 7)))); +(select (values (4))) (values ((values(5)))) +4 5 +4 7 +values ((values (2))), ((values ((values (4))))); +(values (2)) +2 +4 +values ((values (2))), ((values ((select 4)))); +(values (2)) +2 +4 +values ((values (2))), ((values ((values (4))))) +union +values ((values (8))), ((values ((select 4)))); +(values (2)) +2 +4 +8 +values ((values (2))), ((values ((values (4))))) +union all +values ((values (8))), ((values ((select 4)))); +(values (2)) +2 +4 +8 +4 +select * from (values ((values (2)))) dt; +(values (2)) +2 +select * from (values ((values (2)), (5), (select 4))) dt; +(values (2)) 5 (select 4) +2 5 4 +select * from (values ((values (2))) union values ((values (3)))) dt; +(values (2)) +2 +3 +select * from (values ((values (2))), ((values (3)))) dt; +(values (2)) +2 +3 +select * from (values ((values (2))), ((values (3)))) dt; +(values (2)) +2 +3 +select * from (values ((values (2))), ((select 4)), ((values (3)))) dt; +(values (2)) +2 +4 +3 +create table t1 (a int); +insert into t1 values (3), (7), (1); +values ((values ((select a from t1 where a=7)))); +(values ((select a from t1 where a=7))) +7 +values ((values ((select (values(2)) from t1 where a=8)))); +(values ((select (values(2)) from t1 where a=8))) +NULL +values ((values ((select a from t1 where a=7)))) +union +values ((values ((select (values(2)) from t1 where a=8)))); +(values ((select a from t1 where a=7))) +7 +NULL +values ((values ((select a from t1 where a in ((values (7))))))); +(values ((select a from t1 where a in ((values (7)))))) +7 +values ((values ((select a from t1 where a in ((values (7), (8))))))); +(values ((select a from t1 where a in ((values (7), (8)))))) +7 +values ((values +((select a from t1 where a in (values (7) union values (8)))))); +(values +((select a from t1 where a in (values (7) union values (8))))) +7 +values ((values ((select (values(2)) from t1 where a=8)))); +(values ((select (values(2)) from t1 where a=8))) +NULL +values ((select (values(2)) from t1 where a<7)); +ERROR 21000: Subquery returns more than 1 row +select * from (values ((values ((select a from t1 where a=7))))) dt; +(values ((select a from t1 where a=7))) +7 +select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt; +(values ((select (values(2)) from t1 where a=8))) +NULL +drop table t1; End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 88d0ac2..ec147e6 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1522,4 +1522,107 @@ drop table t1,t2,t3; select sum((values(1))); +--echo # +--echo # MDEV-22786: Nested table values constructors +--echo # + +values ((values (2))); + +values ((values (2)), (5), (select 4)); + +values ((7), (values (2)), (5), (select 4)); + +values ((values (2))) union values ((values (3))); + +values ((values (2))), ((values (3))); + +values ((values (2))), ((select 4)), ((values (3))); + +values ((values (4)), (values (5))), ((values (1)), (values (7))); + +values ((values (4)), (select 5)), ((select 1), (values (7))); + +values ((select 2)) union values ((values (3))); + +values ((values (2))) union values((select 3)); + +values ((values (2))) union all values ((values (2))); + +values ((values (4)), (values (5))), ((values (1)), (values (7))) +union +values ((values (4)), (select 5)), ((select 2), (values (8))); + +values ((values (4)), (values (5))), ((values (1)), (values (7))) +union all +values ((values (4)), (select 5)), ((select 2), (values (8))); + +values ((values (1) union values (1))); + +values ((values (1) union values (1) union values (1))); + +values ((values ((values (4))))); + +values ((values ((select 5)))); + +values ((select (values (4))), (values ((values(5))))); + +values ((select (values (4))), (values ((select 5)))); + +values ((select (values (4))), (values ((values(5))))) +union +values ((select (values (4))), (values ((select 7)))); + +values ((values (2))), ((values ((values (4))))); + +values ((values (2))), ((values ((select 4)))); + +values ((values (2))), ((values ((values (4))))) +union +values ((values (8))), ((values ((select 4)))); + +values ((values (2))), ((values ((values (4))))) +union all +values ((values (8))), ((values ((select 4)))); + +select * from (values ((values (2)))) dt; + +select * from (values ((values (2)), (5), (select 4))) dt; + +select * from (values ((values (2))) union values ((values (3)))) dt; + +select * from (values ((values (2))), ((values (3)))) dt; + +select * from (values ((values (2))), ((values (3)))) dt; + +select * from (values ((values (2))), ((select 4)), ((values (3)))) dt; + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +values ((values ((select a from t1 where a=7)))); + +values ((values ((select (values(2)) from t1 where a=8)))); + +values ((values ((select a from t1 where a=7)))) +union +values ((values ((select (values(2)) from t1 where a=8)))); + +values ((values ((select a from t1 where a in ((values (7))))))); + +values ((values ((select a from t1 where a in ((values (7), (8))))))); + +values ((values + ((select a from t1 where a in (values (7) union values (8)))))); + +values ((values ((select (values(2)) from t1 where a=8)))); + +--error ER_SUBQUERY_NO_1_ROW +values ((select (values(2)) from t1 where a<7)); + +select * from (values ((values ((select a from t1 where a=7))))) dt; + +select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt; + +drop table t1; + --echo End of 10.3 tests diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 70d795c..495b27c 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2421,6 +2421,8 @@ void st_select_lex::init_select() with_dep= 0; join= 0; lock_type= TL_READ_DEFAULT; + save_many_values.empty(); + save_insert_list= 0; tvc= 0; in_funcs.empty(); curr_tvc_name= 0; @@ -8302,16 +8304,54 @@ bool LEX::last_field_generated_always_as_row_end() } +void LEX::save_values_list_state() +{ + current_select->save_many_values= many_values; + current_select->save_insert_list= insert_list; +} + + +void LEX::restore_values_list_state() +{ + many_values= current_select->save_many_values; + insert_list= current_select->save_insert_list; +} + + +void LEX::tvc_start() +{ + if (current_select == &select_lex) + mysql_init_select(this); + else + save_values_list_state(); + field_list.empty(); + many_values.empty(); + insert_list= 0; +} + + +bool LEX::tvc_start_derived() +{ + if (current_select->linkage == GLOBAL_OPTIONS_TYPE || + unlikely(mysql_new_select(this, 1, NULL))) + return true; + save_values_list_state(); + field_list.empty(); + many_values.empty(); + insert_list= 0; + return false; +} + + bool LEX::tvc_finalize() { - mysql_init_select(this); if (unlikely(!(current_select->tvc= new (thd->mem_root) table_value_constr(many_values, current_select, current_select->options)))) return true; - many_values.empty(); + restore_values_list_state(); if (!current_select->master_unit()->fake_select_lex) current_select->master_unit()->add_fake_select_lex(thd); return false; @@ -8326,9 +8366,6 @@ bool LEX::tvc_finalize_derived() thd->parse_error(); return true; } - if (current_select->linkage == GLOBAL_OPTIONS_TYPE || - unlikely(mysql_new_select(this, 1, NULL))) - return true; current_select->linkage= DERIVED_TABLE_TYPE; return tvc_finalize(); } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 979e212..474f317 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1176,6 +1176,8 @@ class st_select_lex: public st_select_lex_node /* it is for correct printing SELECT options */ thr_lock_type lock_type; + List<List_item> save_many_values; + List<Item> *save_insert_list; table_value_constr *tvc; bool in_tvc; @@ -4046,12 +4048,10 @@ struct LEX: public Query_tables_list return false; } - void tvc_start() - { - field_list.empty(); - many_values.empty(); - insert_list= 0; - } + void save_values_list_state(); + void restore_values_list_state(); + void tvc_start(); + bool tvc_start_derived(); bool tvc_finalize(); bool tvc_finalize_derived(); diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index cb056b0..96c5223 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -673,6 +673,8 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, wrapper_sl->nest_level= tvc_sl->nest_level; wrapper_sl->parsing_place= tvc_sl->parsing_place; wrapper_sl->linkage= tvc_sl->linkage; + wrapper_sl->exclude_from_table_unique_test= + tvc_sl->exclude_from_table_unique_test; lex->current_select= wrapper_sl; item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 88f12e9..b26e2dd 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -12334,7 +12334,8 @@ derived_query_specification: derived_table_value_constructor: VALUES { - Lex->tvc_start(); + if (Lex->tvc_start_derived()) + MYSQL_YYABORT; } values_list { diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index eaeaf2e..4af034d 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -12272,7 +12272,8 @@ derived_query_specification: derived_table_value_constructor: VALUES { - Lex->tvc_start(); + if (Lex->tvc_start_derived()) + MYSQL_YYABORT; } values_list {

1 0

[Commits] 273cc52: MDEV-24919 Crash with subselect formed by table value constructor and
by IgorBabaev 01 Mar '21

01 Mar '21

revision-id: 273cc529a8063206e2c432a7b5633001fe08a10f (mariadb-10.3.26-93-g273cc52) parent(s): 25ecf8ed4b4cbca69a9fa09c27bbd4e5c83fafe3 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-01 09:40:33 -0800 message: MDEV-24919 Crash with subselect formed by table value constructor and used in set function If a subselect is formed by a table value constructor (TVC) then the following transformation is applied at the prepare stage: VALUES (v1), ... (vn) => SELECT * FROM (VALUES (v1), ... (vn)) tvc_x. The transformation is performed by the function wrap_tvc() that resets THD::LEX::current select to the top level select of the result of the transformation. After the call of wrap_tvc() in the function Item_subselect::wrap_tvc_into_select() the field THD::LEX::current must be reset to the same select as before the call. It was not done. As a result if the subselect formed by a TVC was an argument of a set function then an assertion was hit in the function Item_sum::check_sum_func(). Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/table_value_constr.result | 6 ++++++ mysql-test/main/table_value_constr.test | 6 ++++++ sql/sql_tvc.cc | 15 +++++---------- 3 files changed, 17 insertions(+), 10 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 603f21a..d2965ab 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2881,4 +2881,10 @@ NULL deallocate prepare stmt; drop view v1; drop table t1,t2,t3; +# +# MDEV-24919: subselect formed by TVC and used in set function +# +select sum((values(1))); +sum((values(1))) +1 End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 2246a19..88d0ac2 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1516,4 +1516,10 @@ deallocate prepare stmt; drop view v1; drop table t1,t2,t3; +--echo # +--echo # MDEV-24919: subselect formed by TVC and used in set function +--echo # + +select sum((values(1))); + --echo End of 10.3 tests diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 0a771b5..cb056b0 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -648,7 +648,7 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, st_select_lex *parent_select) { LEX *lex= thd->lex; - select_result *save_result= thd->lex->result; + select_result *save_result= lex->result; uint8 save_derived_tables= lex->derived_tables; thd->lex->result= NULL; @@ -729,13 +729,13 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, if (arena) thd->restore_active_arena(arena, &backup); - thd->lex->result= save_result; + lex->result= save_result; return wrapper_sl; err: if (arena) thd->restore_active_arena(arena, &backup); - thd->lex->result= save_result; + lex->result= save_result; lex->derived_tables= save_derived_tables; return 0; } @@ -819,14 +819,9 @@ Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl) { if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE) ((subselect_single_select_engine *) engine)->change_select(wrapper_sl); - lex->current_select= wrapper_sl; - return wrapper_sl; - } - else - { - lex->current_select= parent_select; - return 0; } + lex->current_select= parent_select; + return wrapper_sl; }

1 0

[Commits] 6e85b78: MDEV-24919 Crash with subselect formed by table value constructor and
by IgorBabaev 27 Feb '21

27 Feb '21

revision-id: 6e85b78fe65bd7fe15689d4a532399676f62902d (mariadb-10.3.26-93-g6e85b78) parent(s): 25ecf8ed4b4cbca69a9fa09c27bbd4e5c83fafe3 author: Igor Babaev committer: Igor Babaev timestamp: 2021-02-26 16:55:45 -0800 message: MDEV-24919 Crash with subselect formed by table value constructor and used in set function If a subselect is formed by a table value constructor (TVC) then the following transformation is applied at the prepare stage: VALUES (v1), ... (vn) => SELECT * FROM (VALUES (v1), ... (vn)) tvc_x. The transformation is performed by the function wrap_tvc() that resets THD::LEX::current select to the top level select of the result of the transformation. After the call of wrap_tvc() in the function Item_subselect::wrap_tvc_into_select() the field THD::LEX::current must be reset to the same select as before the call. It was not done. As a result if the subselect formed by a TVC was an argument of a set function then an assertion was hit in the function Item_sum::check_sum_func(). --- mysql-test/main/table_value_constr.result | 6 ++++++ mysql-test/main/table_value_constr.test | 6 ++++++ sql/sql_tvc.cc | 15 +++++---------- 3 files changed, 17 insertions(+), 10 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 603f21a..d2965ab 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2881,4 +2881,10 @@ NULL deallocate prepare stmt; drop view v1; drop table t1,t2,t3; +# +# MDEV-24919: subselect formed by TVC and used in set function +# +select sum((values(1))); +sum((values(1))) +1 End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 2246a19..88d0ac2 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1516,4 +1516,10 @@ deallocate prepare stmt; drop view v1; drop table t1,t2,t3; +--echo # +--echo # MDEV-24919: subselect formed by TVC and used in set function +--echo # + +select sum((values(1))); + --echo End of 10.3 tests diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 0a771b5..cb056b0 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -648,7 +648,7 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, st_select_lex *parent_select) { LEX *lex= thd->lex; - select_result *save_result= thd->lex->result; + select_result *save_result= lex->result; uint8 save_derived_tables= lex->derived_tables; thd->lex->result= NULL; @@ -729,13 +729,13 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, if (arena) thd->restore_active_arena(arena, &backup); - thd->lex->result= save_result; + lex->result= save_result; return wrapper_sl; err: if (arena) thd->restore_active_arena(arena, &backup); - thd->lex->result= save_result; + lex->result= save_result; lex->derived_tables= save_derived_tables; return 0; } @@ -819,14 +819,9 @@ Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl) { if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE) ((subselect_single_select_engine *) engine)->change_select(wrapper_sl); - lex->current_select= wrapper_sl; - return wrapper_sl; - } - else - { - lex->current_select= parent_select; - return 0; } + lex->current_select= parent_select; + return wrapper_sl; }

1 0

[Commits] a6c2892: MDEV-22786 Crashes with nested table value constructors
by IgorBabaev 26 Feb '21

26 Feb '21

revision-id: a6c2892003775b903180b648e872381455dfae33 (mariadb-10.3.26-88-ga6c2892) parent(s): bf6484e7bb4af3a3bc60289d86e4bde813f4e0c0 author: Igor Babaev committer: Igor Babaev timestamp: 2021-02-25 23:11:03 -0800 message: MDEV-22786 Crashes with nested table value constructors The bug caused crashes of the server when processing queries with nested table value constructors (TVC) . It happened because the grammar rules to parse TVC used the same global lists for both nested TVC and nesting TVC. This patch provides its own lists structures for each TVC nest level. --- mysql-test/main/table_value_constr.result | 138 ++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 87 +++++++++++++++++++ sql/sql_lex.cc | 47 ++++++++-- sql/sql_lex.h | 12 +-- sql/sql_yacc.yy | 3 +- sql/sql_yacc_ora.yy | 3 +- 6 files changed, 277 insertions(+), 13 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 603f21a..0a165aa 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2881,4 +2881,142 @@ NULL deallocate prepare stmt; drop view v1; drop table t1,t2,t3; +# +# MDEV-22786: Nested table values constructors +# +values ((values (2))); +(values (2)) +2 +values ((values (2)), (5), (select 4)); +(values (2)) 5 (select 4) +2 5 4 +values ((7), (values (2)), (5), (select 4)); +7 (values (2)) 5 (select 4) +7 2 5 4 +values ((values (2))) union values ((values (3))); +(values (2)) +2 +3 +values ((values (2))), ((values (3))); +(values (2)) +2 +3 +values ((values (2))), ((select 4)), ((values (3))); +(values (2)) +2 +4 +3 +values ((values (4)), (values (5))), ((values (1)), (values (7))); +(values (4)) (values (5)) +4 5 +1 7 +values ((values (4)), (select 5)), ((select 1), (values (7))); +(values (4)) (select 5) +4 5 +1 7 +values ((select 2)) union values ((values (3))); +(select 2) +2 +3 +values ((values (2))) union values((select 3)); +(values (2)) +2 +3 +values ((values (2))) union all values ((values (2))); +(values (2)) +2 +2 +values ((values (4)), (values (5))), ((values (1)), (values (7))) +union +values ((values (4)), (select 5)), ((select 2), (values (8))); +(values (4)) (values (5)) +4 5 +1 7 +2 8 +values ((values (4)), (values (5))), ((values (1)), (values (7))) +union all +values ((values (4)), (select 5)), ((select 2), (values (8))); +(values (4)) (values (5)) +4 5 +1 7 +4 5 +2 8 +values ((values (1) union values (1))); +(values (1) union values (1)) +1 +values ((values (1) union values (1) union values (1))); +(values (1) union values (1) union values (1)) +1 +values ((values ((values (4))))); +(values ((values (4)))) +4 +values ((values ((select 5)))); +(values ((select 5))) +5 +values ((select (values (4))), (values ((values(5))))); +(select (values (4))) (values ((values(5)))) +4 5 +values ((select (values (4))), (values ((select 5)))); +(select (values (4))) (values ((select 5))) +4 5 +values ((select (values (4))), (values ((values(5))))) +union +values ((select (values (4))), (values ((select 7)))); +(select (values (4))) (values ((values(5)))) +4 5 +4 7 +values ((values (2))), ((values ((values (4))))); +(values (2)) +2 +4 +values ((values (2))), ((values ((select 4)))); +(values (2)) +2 +4 +values ((values (2))), ((values ((values (4))))) +union +values ((values (8))), ((values ((select 4)))); +(values (2)) +2 +4 +8 +values ((values (2))), ((values ((values (4))))) +union all +values ((values (8))), ((values ((select 4)))); +(values (2)) +2 +4 +8 +4 +create table t1 (a int); +insert into t1 values (3), (7), (1); +values ((values ((select a from t1 where a=7)))); +(values ((select a from t1 where a=7))) +7 +values ((values ((select (values(2)) from t1 where a=8)))); +(values ((select (values(2)) from t1 where a=8))) +NULL +values ((values ((select a from t1 where a=7)))) +union +values ((values ((select (values(2)) from t1 where a=8)))); +(values ((select a from t1 where a=7))) +7 +NULL +values ((values ((select a from t1 where a in ((values (7))))))); +(values ((select a from t1 where a in ((values (7)))))) +7 +values ((values ((select a from t1 where a in ((values (7), (8))))))); +(values ((select a from t1 where a in ((values (7), (8)))))) +7 +values ((values +((select a from t1 where a in (values (7) union values (8)))))); +(values +((select a from t1 where a in (values (7) union values (8))))) +7 +values ((values ((select (values(2)) from t1 where a=8)))); +(values ((select (values(2)) from t1 where a=8))) +NULL +values ((select (values(2)) from t1 where a<7)); +ERROR 21000: Subquery returns more than 1 row +drop table t1; End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 2246a19..3e87ac8 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1516,4 +1516,91 @@ deallocate prepare stmt; drop view v1; drop table t1,t2,t3; +--echo # +--echo # MDEV-22786: Nested table values constructors +--echo # + +values ((values (2))); + +values ((values (2)), (5), (select 4)); + +values ((7), (values (2)), (5), (select 4)); + +values ((values (2))) union values ((values (3))); + +values ((values (2))), ((values (3))); + +values ((values (2))), ((select 4)), ((values (3))); + +values ((values (4)), (values (5))), ((values (1)), (values (7))); + +values ((values (4)), (select 5)), ((select 1), (values (7))); + +values ((select 2)) union values ((values (3))); + +values ((values (2))) union values((select 3)); + +values ((values (2))) union all values ((values (2))); + +values ((values (4)), (values (5))), ((values (1)), (values (7))) +union +values ((values (4)), (select 5)), ((select 2), (values (8))); + +values ((values (4)), (values (5))), ((values (1)), (values (7))) +union all +values ((values (4)), (select 5)), ((select 2), (values (8))); + +values ((values (1) union values (1))); + +values ((values (1) union values (1) union values (1))); + +values ((values ((values (4))))); + +values ((values ((select 5)))); + +values ((select (values (4))), (values ((values(5))))); + +values ((select (values (4))), (values ((select 5)))); + +values ((select (values (4))), (values ((values(5))))) +union +values ((select (values (4))), (values ((select 7)))); + +values ((values (2))), ((values ((values (4))))); + +values ((values (2))), ((values ((select 4)))); + +values ((values (2))), ((values ((values (4))))) +union +values ((values (8))), ((values ((select 4)))); + +values ((values (2))), ((values ((values (4))))) +union all +values ((values (8))), ((values ((select 4)))); + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +values ((values ((select a from t1 where a=7)))); + +values ((values ((select (values(2)) from t1 where a=8)))); + +values ((values ((select a from t1 where a=7)))) +union +values ((values ((select (values(2)) from t1 where a=8)))); + +values ((values ((select a from t1 where a in ((values (7))))))); + +values ((values ((select a from t1 where a in ((values (7), (8))))))); + +values ((values + ((select a from t1 where a in (values (7) union values (8)))))); + +values ((values ((select (values(2)) from t1 where a=8)))); + +--error ER_SUBQUERY_NO_1_ROW +values ((select (values(2)) from t1 where a<7)); + +drop table t1; + --echo End of 10.3 tests diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 70d795c..495b27c 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2421,6 +2421,8 @@ void st_select_lex::init_select() with_dep= 0; join= 0; lock_type= TL_READ_DEFAULT; + save_many_values.empty(); + save_insert_list= 0; tvc= 0; in_funcs.empty(); curr_tvc_name= 0; @@ -8302,16 +8304,54 @@ bool LEX::last_field_generated_always_as_row_end() } +void LEX::save_values_list_state() +{ + current_select->save_many_values= many_values; + current_select->save_insert_list= insert_list; +} + + +void LEX::restore_values_list_state() +{ + many_values= current_select->save_many_values; + insert_list= current_select->save_insert_list; +} + + +void LEX::tvc_start() +{ + if (current_select == &select_lex) + mysql_init_select(this); + else + save_values_list_state(); + field_list.empty(); + many_values.empty(); + insert_list= 0; +} + + +bool LEX::tvc_start_derived() +{ + if (current_select->linkage == GLOBAL_OPTIONS_TYPE || + unlikely(mysql_new_select(this, 1, NULL))) + return true; + save_values_list_state(); + field_list.empty(); + many_values.empty(); + insert_list= 0; + return false; +} + + bool LEX::tvc_finalize() { - mysql_init_select(this); if (unlikely(!(current_select->tvc= new (thd->mem_root) table_value_constr(many_values, current_select, current_select->options)))) return true; - many_values.empty(); + restore_values_list_state(); if (!current_select->master_unit()->fake_select_lex) current_select->master_unit()->add_fake_select_lex(thd); return false; @@ -8326,9 +8366,6 @@ bool LEX::tvc_finalize_derived() thd->parse_error(); return true; } - if (current_select->linkage == GLOBAL_OPTIONS_TYPE || - unlikely(mysql_new_select(this, 1, NULL))) - return true; current_select->linkage= DERIVED_TABLE_TYPE; return tvc_finalize(); } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 979e212..474f317 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1176,6 +1176,8 @@ class st_select_lex: public st_select_lex_node /* it is for correct printing SELECT options */ thr_lock_type lock_type; + List<List_item> save_many_values; + List<Item> *save_insert_list; table_value_constr *tvc; bool in_tvc; @@ -4046,12 +4048,10 @@ struct LEX: public Query_tables_list return false; } - void tvc_start() - { - field_list.empty(); - many_values.empty(); - insert_list= 0; - } + void save_values_list_state(); + void restore_values_list_state(); + void tvc_start(); + bool tvc_start_derived(); bool tvc_finalize(); bool tvc_finalize_derived(); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 88f12e9..b26e2dd 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -12334,7 +12334,8 @@ derived_query_specification: derived_table_value_constructor: VALUES { - Lex->tvc_start(); + if (Lex->tvc_start_derived()) + MYSQL_YYABORT; } values_list { diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index eaeaf2e..4af034d 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -12272,7 +12272,8 @@ derived_query_specification: derived_table_value_constructor: VALUES { - Lex->tvc_start(); + if (Lex->tvc_start_derived()) + MYSQL_YYABORT; } values_list {

1 0

[Commits] bf6484e: MDEV-24910 Crash with SELECT that uses table value constructor as a subselect
by IgorBabaev 24 Feb '21

24 Feb '21

revision-id: bf6484e7bb4af3a3bc60289d86e4bde813f4e0c0 (mariadb-10.3.26-87-gbf6484e) parent(s): 13f0e1e1392b1e275d55a7d37b3dac946d908bb0 author: Igor Babaev committer: Igor Babaev timestamp: 2021-02-24 13:51:47 -0800 message: MDEV-24910 Crash with SELECT that uses table value constructor as a subselect This bug caused crashes of the server when processing queries with table value constructors (TVC) that contained subqueries and were used itself as subselects. For such TVCs the following transformation is applied at the prepare stage: VALUES (v1), ... (vn) => SELECT * FROM (VALUES (v1), ... (vn)) tvc_x. This transformation allows to reduce the problem of evaluation of TVCs used as subselects to the problem of evaluation of regular subselects. The transformation is implemented in the wrap_tvc(). The code the function to mimic the behaviour of the parser when processing the result of the transformation. However this imitation was not free of some flaws. First the function called the method exclude() that completely destroyed the select tree structures below the transformed TVC. Second the function used the procedure mysql_new_select to create st_select_lex nodes for both wrapping select of the transformation and TVC. This also led to constructing of invalid select tree structures. The patch actually re-engineers the code of wrap_tvc(). Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/table_value_constr.result | 110 ++++++++++++++++++++- mysql-test/main/table_value_constr.test | 57 +++++++++++ .../compat/oracle/r/table_value_constr.result | 4 +- sql/sql_lex.cc | 23 +++++ sql/sql_lex.h | 1 + sql/sql_tvc.cc | 67 ++++++++----- 6 files changed, 231 insertions(+), 31 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index e112aca..603f21a 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -748,7 +748,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -983,7 +983,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -2775,4 +2775,110 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where drop table t1; +# +# MDEV-24910: TVC containing subquery used as a subselect +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); +create table t2 (b int) engine=myisam; +insert into t2 values (1), (2); +select (values ((select 2))) from t2; +(values ((select 2))) +2 +2 +explain select (values ((select 2))) from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1249 Select 3 was reduced during optimization +prepare stmt from "select (values ((select 2))) from t2"; +execute stmt; +(values ((select 2))) +2 +2 +execute stmt; +(values ((select 2))) +2 +2 +deallocate prepare stmt; +select (values ((select * from t1 where a > 10))) from t2; +(values ((select * from t1 where a > 10))) +NULL +NULL +explain select (values ((select * from t1 where a > 10))) from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +prepare stmt from "select (values ((select * from t1 where a > 10))) from t2"; +execute stmt; +(values ((select * from t1 where a > 10))) +NULL +NULL +execute stmt; +(values ((select * from t1 where a > 10))) +NULL +NULL +deallocate prepare stmt; +create table t3 (a int); +insert into t3 values +(3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8); +create view v1 as select count(a) as c from t3 group by a; +select +(values ((select * from t3 where a in (select * from v1)))); +(values ((select * from t3 where a in (select * from v1)))) +1 +explain select +(values ((select * from t3 where a in (select * from v1)))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +6 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t3 ALL NULL NULL NULL NULL 11 +3 SUBQUERY <subquery4> eq_ref distinct_key distinct_key 8 func 1 Using where +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 11 +5 DERIVED t3 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +prepare stmt from "select +(values ((select * from t3 where a in (select * from v1))))"; +execute stmt; +(values ((select * from t3 where a in (select * from v1)))) +1 +execute stmt; +(values ((select * from t3 where a in (select * from v1)))) +1 +deallocate prepare stmt; +select +(values ((select * from t3 +where a > 10 and a in (select * from v1)))); +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +explain select +(values ((select * from t3 +where a > 10 and a in (select * from v1)))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +6 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t3 ALL NULL NULL NULL NULL 11 Using where +3 SUBQUERY <subquery4> eq_ref distinct_key distinct_key 8 func 1 Using where +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 11 +5 DERIVED t3 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +prepare stmt from "select +(values ((select * from t3 +where a > 10 and a in (select * from v1))))"; +execute stmt; +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +execute stmt; +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +deallocate prepare stmt; +drop view v1; +drop table t1,t2,t3; End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 0a78fd9..2246a19 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1459,4 +1459,61 @@ eval explain $q3; drop table t1; +--echo # +--echo # MDEV-24910: TVC containing subquery used as a subselect +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); +create table t2 (b int) engine=myisam; +insert into t2 values (1), (2); + +let $q1= +select (values ((select 2))) from t2; +eval $q1; +eval explain $q1; +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q2= +select (values ((select * from t1 where a > 10))) from t2; +eval $q2; +eval explain $q2; +eval prepare stmt from "$q2"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +create table t3 (a int); +insert into t3 values + (3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8); + +create view v1 as select count(a) as c from t3 group by a; + +let $q3= +select +(values ((select * from t3 where a in (select * from v1)))); +eval $q3; +eval explain $q3; +eval prepare stmt from "$q3"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q4= +select +(values ((select * from t3 + where a > 10 and a in (select * from v1)))); +eval $q4; +eval explain $q4; +eval prepare stmt from "$q4"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop view v1; +drop table t1,t2,t3; + --echo End of 10.3 tests diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result index d4f8e28..5393295 100644 --- a/mysql-test/suite/compat/oracle/r/table_value_constr.result +++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result @@ -746,7 +746,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1 +Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -981,7 +981,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1 +Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 9766a28..70d795c 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2464,9 +2464,32 @@ void st_select_lex_node::add_slave(st_select_lex_node *slave_arg) { slave= slave_arg; slave_arg->master= this; + slave->prev= &master->slave; + slave->next= 0; } } +/* + @brief + Substitute this node in select tree for a newly creates node + + @param subst the node to substitute for + + @details + The function substitute this node in the select tree for a newly + created node subst. This node is just removed from the tree but all + its link fields and the attached sub-tree remain untouched. +*/ + +void st_select_lex_node::substitute_in_tree(st_select_lex_node *subst) +{ + if ((subst->next= next)) + next->prev= &subst->next; + subst->prev= prev; + (*prev)= subst; + subst->master= master; +} + /* include on level down (but do not link) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 77b4e15..979e212 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -711,6 +711,7 @@ class st_select_lex_node { void include_global(st_select_lex_node **plink); void exclude(); void exclude_from_tree(); + void substitute_in_tree(st_select_lex_node *subst); void set_slave(st_select_lex_node *slave_arg) { slave= slave_arg; } void move_node(st_select_lex_node *where_to_move) diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 0a5f668..0a771b5 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -654,44 +654,61 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, Query_arena backup; Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); + + Item *item; + SELECT_LEX *wrapper_sl; + SELECT_LEX_UNIT *derived_unit; + /* - Create SELECT_LEX of the select used in the result of transformation + Create SELECT_LEX wrapper_sl of the select used in the result + of the transformation */ - lex->current_select= tvc_sl; - if (mysql_new_select(lex, 0, NULL)) + if (!(wrapper_sl= new (thd->mem_root) SELECT_LEX())) goto err; - mysql_init_select(lex); - /* Create item list as '*' for the subquery SQ */ - Item *item; - SELECT_LEX *wrapper_sl; - wrapper_sl= lex->current_select; + wrapper_sl->select_number= ++thd->lex->stmt_lex->current_select_number; + wrapper_sl->parent_lex= lex; /* Used in init_query. */ + wrapper_sl->init_query(); + wrapper_sl->init_select(); + + wrapper_sl->nest_level= tvc_sl->nest_level; + wrapper_sl->parsing_place= tvc_sl->parsing_place; wrapper_sl->linkage= tvc_sl->linkage; - wrapper_sl->parsing_place= SELECT_LIST; + + lex->current_select= wrapper_sl; item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context, NULL, NULL, &star_clex_str); if (item == NULL || add_item_to_list(thd, item)) goto err; (wrapper_sl->with_wild)++; - - /* Exclude SELECT with TVC */ - tvc_sl->exclude(); + + /* Include the newly created select into the global list of selects */ + wrapper_sl->include_global((st_select_lex_node**)&lex->all_selects_list); + + /* Substitute select node used of TVC for the newly created select */ + tvc_sl->substitute_in_tree(wrapper_sl); + /* - Create derived table DT that will wrap TVC in the result of transformation + Create a unit for the substituted select used for TVC and attach it + to the the wrapper select wrapper_sl as the only unit. The created + unit is the unit for the derived table tvc_x of the transformation. */ - SELECT_LEX *tvc_select; // select for tvc - SELECT_LEX_UNIT *derived_unit; // unit for tvc_select - if (mysql_new_select(lex, 1, tvc_sl)) + if (!(derived_unit= new (thd->mem_root) SELECT_LEX_UNIT())) goto err; - tvc_select= lex->current_select; - derived_unit= tvc_select->master_unit(); - tvc_select->linkage= DERIVED_TABLE_TYPE; + derived_unit->init_query(); + derived_unit->thd= thd; + derived_unit->include_down(wrapper_sl); - lex->current_select= wrapper_sl; + /* + Attach the select used of TVC as the only slave to the unit for + the derived table tvc_x of the transformation + */ + derived_unit->add_slave(tvc_sl); + tvc_sl->linkage= DERIVED_TABLE_TYPE; /* - Create the name of the wrapping derived table and - add it to the FROM list of the wrapper - */ + Generate the name of the derived table created for TVC and + add it to the FROM list of the wrapping select + */ Table_ident *ti; LEX_CSTRING alias; TABLE_LIST *derived_tab; @@ -710,10 +727,6 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, wrapper_sl->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE; lex->derived_tables|= DERIVED_SUBQUERY; - wrapper_sl->where= 0; - wrapper_sl->set_braces(false); - derived_unit->set_with_clause(0); - if (arena) thd->restore_active_arena(arena, &backup); thd->lex->result= save_result;

1 0

[Commits] 47c85a4: MDEV-24910 Crash with SELECT that uses table value constructor as a subselect
by IgorBabaev 24 Feb '21

24 Feb '21

revision-id: 47c85a4d0120cc1ef9f28ba5824a07dd683fca80 (mariadb-10.3.26-85-g47c85a4) parent(s): 640f42311a72fa82bf7117c2791fc47ceb420361 author: Igor Babaev committer: Igor Babaev timestamp: 2021-02-23 22:33:06 -0800 message: MDEV-24910 Crash with SELECT that uses table value constructor as a subselect This bug caused crashes of the server when processing queries with table value constructors (TVC) that contained subqueries and were used itself as subselects. For such TVCs the following transformation is applied at the prepare stage: VALUES (v1), ... (vn) => SELECT * FROM (VALUES (v1), ... (vn)) tvc_x. This transformation allows to reduce the problem of evaluation of TVCs used as subselects to the problem of evaluation of regular subselects. The transformation is implemented in the wrap_tvc(). The code the function to mimic the behaviour of the parser when processing the result of the transformation. However this imitation was not free of some flaws. First the function called the method exclude() that completely destroyed the select tree structures below the transformed TVC. Second the function used the procedure mysql_new_select to create st_select_lex nodes for both wrapping select of the transformation and TVC. This also led to constructing of invalid select tree structures. The patch actually re-engineers the code of wrap_tvc(). --- mysql-test/main/table_value_constr.result | 110 +++++++++++++++++++++++++++++- mysql-test/main/table_value_constr.test | 57 ++++++++++++++++ sql/sql_lex.cc | 23 +++++++ sql/sql_lex.h | 1 + sql/sql_tvc.cc | 67 ++++++++++-------- 5 files changed, 229 insertions(+), 29 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index e112aca..603f21a 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -748,7 +748,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -983,7 +983,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -2775,4 +2775,110 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where drop table t1; +# +# MDEV-24910: TVC containing subquery used as a subselect +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); +create table t2 (b int) engine=myisam; +insert into t2 values (1), (2); +select (values ((select 2))) from t2; +(values ((select 2))) +2 +2 +explain select (values ((select 2))) from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1249 Select 3 was reduced during optimization +prepare stmt from "select (values ((select 2))) from t2"; +execute stmt; +(values ((select 2))) +2 +2 +execute stmt; +(values ((select 2))) +2 +2 +deallocate prepare stmt; +select (values ((select * from t1 where a > 10))) from t2; +(values ((select * from t1 where a > 10))) +NULL +NULL +explain select (values ((select * from t1 where a > 10))) from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +prepare stmt from "select (values ((select * from t1 where a > 10))) from t2"; +execute stmt; +(values ((select * from t1 where a > 10))) +NULL +NULL +execute stmt; +(values ((select * from t1 where a > 10))) +NULL +NULL +deallocate prepare stmt; +create table t3 (a int); +insert into t3 values +(3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8); +create view v1 as select count(a) as c from t3 group by a; +select +(values ((select * from t3 where a in (select * from v1)))); +(values ((select * from t3 where a in (select * from v1)))) +1 +explain select +(values ((select * from t3 where a in (select * from v1)))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +6 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t3 ALL NULL NULL NULL NULL 11 +3 SUBQUERY <subquery4> eq_ref distinct_key distinct_key 8 func 1 Using where +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 11 +5 DERIVED t3 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +prepare stmt from "select +(values ((select * from t3 where a in (select * from v1))))"; +execute stmt; +(values ((select * from t3 where a in (select * from v1)))) +1 +execute stmt; +(values ((select * from t3 where a in (select * from v1)))) +1 +deallocate prepare stmt; +select +(values ((select * from t3 +where a > 10 and a in (select * from v1)))); +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +explain select +(values ((select * from t3 +where a > 10 and a in (select * from v1)))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +6 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t3 ALL NULL NULL NULL NULL 11 Using where +3 SUBQUERY <subquery4> eq_ref distinct_key distinct_key 8 func 1 Using where +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 11 +5 DERIVED t3 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +prepare stmt from "select +(values ((select * from t3 +where a > 10 and a in (select * from v1))))"; +execute stmt; +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +execute stmt; +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +deallocate prepare stmt; +drop view v1; +drop table t1,t2,t3; End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 0a78fd9..2246a19 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1459,4 +1459,61 @@ eval explain $q3; drop table t1; +--echo # +--echo # MDEV-24910: TVC containing subquery used as a subselect +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); +create table t2 (b int) engine=myisam; +insert into t2 values (1), (2); + +let $q1= +select (values ((select 2))) from t2; +eval $q1; +eval explain $q1; +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q2= +select (values ((select * from t1 where a > 10))) from t2; +eval $q2; +eval explain $q2; +eval prepare stmt from "$q2"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +create table t3 (a int); +insert into t3 values + (3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8); + +create view v1 as select count(a) as c from t3 group by a; + +let $q3= +select +(values ((select * from t3 where a in (select * from v1)))); +eval $q3; +eval explain $q3; +eval prepare stmt from "$q3"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q4= +select +(values ((select * from t3 + where a > 10 and a in (select * from v1)))); +eval $q4; +eval explain $q4; +eval prepare stmt from "$q4"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop view v1; +drop table t1,t2,t3; + --echo End of 10.3 tests diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 9766a28..70d795c 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2464,9 +2464,32 @@ void st_select_lex_node::add_slave(st_select_lex_node *slave_arg) { slave= slave_arg; slave_arg->master= this; + slave->prev= &master->slave; + slave->next= 0; } } +/* + @brief + Substitute this node in select tree for a newly creates node + + @param subst the node to substitute for + + @details + The function substitute this node in the select tree for a newly + created node subst. This node is just removed from the tree but all + its link fields and the attached sub-tree remain untouched. +*/ + +void st_select_lex_node::substitute_in_tree(st_select_lex_node *subst) +{ + if ((subst->next= next)) + next->prev= &subst->next; + subst->prev= prev; + (*prev)= subst; + subst->master= master; +} + /* include on level down (but do not link) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 77b4e15..979e212 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -711,6 +711,7 @@ class st_select_lex_node { void include_global(st_select_lex_node **plink); void exclude(); void exclude_from_tree(); + void substitute_in_tree(st_select_lex_node *subst); void set_slave(st_select_lex_node *slave_arg) { slave= slave_arg; } void move_node(st_select_lex_node *where_to_move) diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 0a5f668..0a771b5 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -654,44 +654,61 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, Query_arena backup; Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); + + Item *item; + SELECT_LEX *wrapper_sl; + SELECT_LEX_UNIT *derived_unit; + /* - Create SELECT_LEX of the select used in the result of transformation + Create SELECT_LEX wrapper_sl of the select used in the result + of the transformation */ - lex->current_select= tvc_sl; - if (mysql_new_select(lex, 0, NULL)) + if (!(wrapper_sl= new (thd->mem_root) SELECT_LEX())) goto err; - mysql_init_select(lex); - /* Create item list as '*' for the subquery SQ */ - Item *item; - SELECT_LEX *wrapper_sl; - wrapper_sl= lex->current_select; + wrapper_sl->select_number= ++thd->lex->stmt_lex->current_select_number; + wrapper_sl->parent_lex= lex; /* Used in init_query. */ + wrapper_sl->init_query(); + wrapper_sl->init_select(); + + wrapper_sl->nest_level= tvc_sl->nest_level; + wrapper_sl->parsing_place= tvc_sl->parsing_place; wrapper_sl->linkage= tvc_sl->linkage; - wrapper_sl->parsing_place= SELECT_LIST; + + lex->current_select= wrapper_sl; item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context, NULL, NULL, &star_clex_str); if (item == NULL || add_item_to_list(thd, item)) goto err; (wrapper_sl->with_wild)++; - - /* Exclude SELECT with TVC */ - tvc_sl->exclude(); + + /* Include the newly created select into the global list of selects */ + wrapper_sl->include_global((st_select_lex_node**)&lex->all_selects_list); + + /* Substitute select node used of TVC for the newly created select */ + tvc_sl->substitute_in_tree(wrapper_sl); + /* - Create derived table DT that will wrap TVC in the result of transformation + Create a unit for the substituted select used for TVC and attach it + to the the wrapper select wrapper_sl as the only unit. The created + unit is the unit for the derived table tvc_x of the transformation. */ - SELECT_LEX *tvc_select; // select for tvc - SELECT_LEX_UNIT *derived_unit; // unit for tvc_select - if (mysql_new_select(lex, 1, tvc_sl)) + if (!(derived_unit= new (thd->mem_root) SELECT_LEX_UNIT())) goto err; - tvc_select= lex->current_select; - derived_unit= tvc_select->master_unit(); - tvc_select->linkage= DERIVED_TABLE_TYPE; + derived_unit->init_query(); + derived_unit->thd= thd; + derived_unit->include_down(wrapper_sl); - lex->current_select= wrapper_sl; + /* + Attach the select used of TVC as the only slave to the unit for + the derived table tvc_x of the transformation + */ + derived_unit->add_slave(tvc_sl); + tvc_sl->linkage= DERIVED_TABLE_TYPE; /* - Create the name of the wrapping derived table and - add it to the FROM list of the wrapper - */ + Generate the name of the derived table created for TVC and + add it to the FROM list of the wrapping select + */ Table_ident *ti; LEX_CSTRING alias; TABLE_LIST *derived_tab; @@ -710,10 +727,6 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, wrapper_sl->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE; lex->derived_tables|= DERIVED_SUBQUERY; - wrapper_sl->where= 0; - wrapper_sl->set_braces(false); - derived_unit->set_with_clause(0); - if (arena) thd->restore_active_arena(arena, &backup); thd->lex->result= save_result;

1 0

[Commits] c2213b92184: MDEV-24953: 10.5.9 crashes with large IN() list
by psergey 23 Feb '21

23 Feb '21

revision-id: c2213b92184a6a2e6cad231cdd30bdf1ed34c10c (mariadb-10.5.4-498-gc2213b92184) parent(s): 85bec9d691bb69ed20beb565b03d5585b94624fe author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-02-23 23:38:57 +0300 message: MDEV-24953: 10.5.9 crashes with large IN() list The problem was in and_all_keys(), the code of MDEV-9759 which calculates the new tree weight: First, it didn't take into account the case when (next->next_key_part=tmp) == NULL and dereferenced a NULL pointer when getting tmp->weight. Second, "if (param->alloced_sel_args > SEL_ARG::MAX_SEL_ARGS) break" could leave the loop with incorrect value of weight. Fixed by introducing SEL_ARG::update_weight_locally() and calling it at the end of the function. This allows to avoid caring about all the above cases. --- mysql-test/main/range_notembedded.result | 16 ++++++++++- mysql-test/main/range_notembedded.test | 32 +++++++++++++++++++++- sql/opt_range.cc | 46 ++++++++++++++++++++------------ sql/opt_range.h | 2 ++ 4 files changed, 77 insertions(+), 19 deletions(-) diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result index 87fa85d3ac6..0ecf47c892e 100644 --- a/mysql-test/main/range_notembedded.result +++ b/mysql-test/main/range_notembedded.result @@ -159,7 +159,6 @@ left(@json, 2500) ] ] ## Repeat the above with a bit higher max_weight: -set @tmp9750_weight=@@optimizer_max_sel_arg_weight; set optimizer_max_sel_arg_weight=120; explain select * from t1 where kp1 in (1,2,3,4,5,6,7,8,9,10) and @@ -225,3 +224,18 @@ SELECT * FROM mysql.help_relation ignore index (help_topic_id) WHERE (help_topic_id = 8 OR help_keyword_id = 0) AND help_keyword_id != 2 AND help_topic_id >= 1900; help_topic_id help_keyword_id +# +# MDEV-24953: 10.5.9 crashes with large IN() list +# +CREATE TABLE t1 ( +notification_type_id smallint(4) unsigned NOT NULL DEFAULT 0, +item_id int(10) unsigned NOT NULL DEFAULT 0, +item_parent_id int(10) unsigned NOT NULL DEFAULT 0, +user_id int(10) unsigned NOT NULL DEFAULT 0, +PRIMARY KEY (notification_type_id,item_id,item_parent_id,user_id) +); +insert into t1 values (1,1,1,1), (2,2,2,2), (3,3,3,3); +# Run crashing query +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 2 NULL 3 Using where +drop table t1; diff --git a/mysql-test/main/range_notembedded.test b/mysql-test/main/range_notembedded.test index a70749ced6b..5f6a05e8d91 100644 --- a/mysql-test/main/range_notembedded.test +++ b/mysql-test/main/range_notembedded.test @@ -82,7 +82,6 @@ set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); select left(@json, 2500); --echo ## Repeat the above with a bit higher max_weight: -set @tmp9750_weight=@@optimizer_max_sel_arg_weight; set optimizer_max_sel_arg_weight=120; explain select * from t1 where kp1 in (1,2,3,4,5,6,7,8,9,10) and @@ -110,3 +109,34 @@ SELECT * FROM mysql.help_relation ignore index (help_topic_id) WHERE (help_topic_id = 8 OR help_keyword_id = 0) AND help_keyword_id != 2 AND help_topic_id >= 1900; +--echo # +--echo # MDEV-24953: 10.5.9 crashes with large IN() list +--echo # +--source include/have_sequence.inc + +CREATE TABLE t1 ( + notification_type_id smallint(4) unsigned NOT NULL DEFAULT 0, + item_id int(10) unsigned NOT NULL DEFAULT 0, + item_parent_id int(10) unsigned NOT NULL DEFAULT 0, + user_id int(10) unsigned NOT NULL DEFAULT 0, + PRIMARY KEY (notification_type_id,item_id,item_parent_id,user_id) +); +insert into t1 values (1,1,1,1), (2,2,2,2), (3,3,3,3); + +let $consts=`select group_concat(concat("'",seq,"'")) from seq_1_to_4642`; + +--echo # Run crashing query +--disable_query_log +eval +explain +DELETE FROM t1 +WHERE + notification_type_id IN (3, 4, 5, 6, 23) + AND + user_id = '5044' + AND + item_parent_id IN ($consts) +; +--enable_query_log + +drop table t1; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 3684db40242..a02b6171a20 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -9800,7 +9800,6 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, key1->right= key1->left= &null_element; key1->next= key1->prev= 0; } - uint new_weight= 0; for (next=key1->first(); next ; next=next->next) { @@ -9813,22 +9812,21 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, continue; } next->next_key_part=tmp; - new_weight += 1 + tmp->weight; if (use_count) next->increment_use_count(use_count); if (param->alloced_sel_args > SEL_ARG::MAX_SEL_ARGS) break; } else - { - new_weight += 1 + key2->weight; next->next_key_part=key2; - } } if (!key1) return &null_element; // Impossible ranges key1->use_count++; - key1->weight= new_weight; + + /* Re-compute the result tree's weight. */ + key1->update_weight_locally(); + key1->max_part_no= MY_MAX(key2->max_part_no, key2->part+1); return key1; } @@ -9992,6 +9990,30 @@ get_range(SEL_ARG **e1,SEL_ARG **e2,SEL_ARG *root1) return 0; } +/* + @brief + Update the tree weight. + + @detail + Utility function to be called on a SEL_ARG tree root after doing local + modifications concerning changes at this key part. + Assumes that the weight of the graphs connected via next_key_part is + up to dayte. +*/ +void SEL_ARG::update_weight_locally() +{ + uint new_weight= 0; + const SEL_ARG *sl; + for (sl= first(); sl ; sl= sl->next) + { + new_weight++; + if (sl->next_key_part) + new_weight += sl->next_key_part->weight; + } + weight= new_weight; +} + + #ifndef DBUG_OFF /* Verify SEL_TREE's weight. @@ -10728,17 +10750,7 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2) key1->use_count++; /* Re-compute the result tree's weight. */ - { - uint new_weight= 0; - const SEL_ARG *sl; - for (sl= key1->first(); sl ; sl= sl->next) - { - new_weight++; - if (sl->next_key_part) - new_weight += sl->next_key_part->weight; - } - key1->weight= new_weight; - } + key1->update_weight_locally(); key1->max_part_no= max_part_no; return key1; diff --git a/sql/opt_range.h b/sql/opt_range.h index 50cd43c0e85..1014176ecc5 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -316,6 +316,8 @@ class SEL_ARG :public Sql_alloc */ uint weight; enum { MAX_WEIGHT = 32000 }; + + void update_weight_locally(); #ifndef DBUG_OFF uint verify_weight(); #endif

1 0

[Commits] 65d950d: MDEV-24910 Crash with SELECT that uses table value constructor as a subselect
by IgorBabaev 22 Feb '21

22 Feb '21

revision-id: 65d950d48fc37989bbd5213f083a1f3cb3bd53fb (mariadb-10.3.26-79-g65d950d) parent(s): da88e1ec12b0ba39552bf54367c1bb3b89eac4a8 author: Igor Babaev committer: Igor Babaev timestamp: 2021-02-22 14:21:42 -0800 message: MDEV-24910 Crash with SELECT that uses table value constructor as a subselect A preliminary commit (not to be pushed). --- mysql-test/main/table_value_constr.result | 4 +-- sql/sql_lex.cc | 12 +++++++ sql/sql_lex.h | 1 + sql/sql_tvc.cc | 53 ++++++++++++++++++++----------- 4 files changed, 49 insertions(+), 21 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index d7b3286..cd839ae 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -748,7 +748,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -983,7 +983,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index b8f6610..d287466 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2464,10 +2464,22 @@ void st_select_lex_node::add_slave(st_select_lex_node *slave_arg) { slave= slave_arg; slave_arg->master= this; + slave->prev= &master->slave; + slave->next= 0; } } +void st_select_lex_node::substitute_in_tree(st_select_lex_node *subst) +{ + if ((subst->next= next)) + next->prev= &subst->next; + subst->prev= prev; + (*prev)= subst; + subst->master= master; +} + + /* include on level down (but do not link) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 55929ed..bed2b39 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -711,6 +711,7 @@ class st_select_lex_node { void include_global(st_select_lex_node **plink); void exclude(); void exclude_from_tree(); + void substitute_in_tree(st_select_lex_node *subst); void set_slave(st_select_lex_node *slave_arg) { slave= slave_arg; } void move_node(st_select_lex_node *where_to_move) diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 78607b6..8c54741 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -643,38 +643,53 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, Query_arena backup; Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); /* - Create SELECT_LEX of the select used in the result of transformation + Create SELECT_LEX wrapper_sl of the select used in the result + of the transformation */ - lex->current_select= tvc_sl; - if (mysql_new_select(lex, 0, NULL)) - goto err; - mysql_init_select(lex); - /* Create item list as '*' for the subquery SQ */ Item *item; SELECT_LEX *wrapper_sl; - wrapper_sl= lex->current_select; + SELECT_LEX_UNIT *derived_unit; + if (!(wrapper_sl= new (thd->mem_root) SELECT_LEX())) + goto err; + wrapper_sl->select_number= ++thd->lex->stmt_lex->current_select_number; + wrapper_sl->parent_lex= lex; /* Used in init_query. */ + wrapper_sl->init_query(); + wrapper_sl->init_select(); + + wrapper_sl->nest_level= tvc_sl->nest_level; + wrapper_sl->parsing_place= tvc_sl->parsing_place; wrapper_sl->linkage= tvc_sl->linkage; - wrapper_sl->parsing_place= SELECT_LIST; + + lex->current_select= wrapper_sl; item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context, NULL, NULL, &star_clex_str); if (item == NULL || add_item_to_list(thd, item)) goto err; (wrapper_sl->with_wild)++; - - /* Exclude SELECT with TVC */ - tvc_sl->exclude(); + + /* Include the newly created select into the global list of selects */ + wrapper_sl->include_global((st_select_lex_node**)&lex->all_selects_list); + + /* Substitute select node used for TVC for the newly created select */ + tvc_sl->substitute_in_tree(wrapper_sl); + /* - Create derived table DT that will wrap TVC in the result of transformation + Create a unit for the substituted select used for TVC and attach it + as the only unit to the the wrapper select_wrapper sl. The created + unit is the unit for the derived table tvc_x of the transformation. */ - SELECT_LEX *tvc_select; // select for tvc - SELECT_LEX_UNIT *derived_unit; // unit for tvc_select - if (mysql_new_select(lex, 1, tvc_sl)) + if (!(derived_unit= new (thd->mem_root) SELECT_LEX_UNIT())) goto err; - tvc_select= lex->current_select; - derived_unit= tvc_select->master_unit(); - tvc_select->linkage= DERIVED_TABLE_TYPE; + derived_unit->init_query(); + derived_unit->thd= thd; + derived_unit->include_down(wrapper_sl); - lex->current_select= wrapper_sl; + /* + Attach the select used for TVC as the only slave to the unit for + the derived table tvc_x of the transformation + */ + derived_unit->add_slave(tvc_sl); + tvc_sl->linkage= DERIVED_TABLE_TYPE; /* Create the name of the wrapping derived table and

1 0

[Commits] f3eafa5: MDEV-24936 EXPLAIN for query based on table value constructor lacks info
by IgorBabaev 22 Feb '21

22 Feb '21

revision-id: f3eafa5e05c924bfcf152a3200009d3f3c5819fc (mariadb-10.3.26-79-gf3eafa5) parent(s): da88e1ec12b0ba39552bf54367c1bb3b89eac4a8 author: Igor Babaev committer: Igor Babaev timestamp: 2021-02-21 22:01:24 -0800 message: MDEV-24936 EXPLAIN for query based on table value constructor lacks info on used subqueries If a query was based on a table value constructor that contained subqueries then EXPLAIN for such query did not contain any lines explaining the execution plans of the subqueries. This happened because - no optimize() method was called for any subquery used by the table value constructor when EXPLAIN command for the query was processed; - EXPLAIN node created for the table value constructor itself did not assume that some child nodes could be attached to it. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/table_value_constr.result | 33 +++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 25 +++++++++++++++++++++++ sql/sql_tvc.cc | 16 +++++++++++++-- 3 files changed, 72 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index d7b3286..e112aca 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2742,4 +2742,37 @@ NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`))) drop table t1; +# +# MDEV-24934:EXPLAIN for queries based on TVC using subqueries +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +values (8), ((select * from t1 where a between 2 and 4)); +8 +8 +3 +explain values (8), ((select * from t1 where a between 2 and 4)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +values ((select * from t1 where a between 2 and 4)), +((select * from t1 where a > 10)); +(select * from t1 where a between 2 and 4) +3 +NULL +explain values ((select * from t1 where a between 2 and 4)), +((select * from t1 where a > 10)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +values (10,11), ((select * from t1 where a = 7) + 1, 21); +10 11 +10 11 +8 21 +explain values (10,11), ((select * from t1 where a = 7) + 1, 21); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +drop table t1; End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index bac85ff..0a78fd9 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1434,4 +1434,29 @@ eval explain extended $q; drop table t1; +--echo # +--echo # MDEV-24934:EXPLAIN for queries based on TVC using subqueries +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +let $q1= +values (8), ((select * from t1 where a between 2 and 4)); +eval $q1; +eval explain $q1; + +let $q2= +values ((select * from t1 where a between 2 and 4)), + ((select * from t1 where a > 10)); +eval $q2; +eval explain $q2; + +let $q3= +values (10,11), ((select * from t1 where a = 7) + 1, 21); +eval $q3; +eval explain $q3; + +drop table t1; + --echo End of 10.3 tests diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 78607b6..0a5f668 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -342,6 +342,13 @@ int table_value_constr::save_explain_data_intern(THD *thd, if (select_lex->master_unit()->derived) explain->connection_type= Explain_node::EXPLAIN_NODE_DERIVED; + for (SELECT_LEX_UNIT *unit= select_lex->first_inner_unit(); + unit; + unit= unit->next_unit()) + { + explain->add_child(unit->first_select()->select_number); + } + output->add_node(explain); if (select_lex->is_top_level_node()) @@ -366,9 +373,14 @@ bool table_value_constr::optimize(THD *thd) thd->lex->explain && // for "SET" command in SPs. (!thd->lex->explain->get_select(select_lex->select_number))) { - return save_explain_data_intern(thd, thd->lex->explain); + if (save_explain_data_intern(thd, thd->lex->explain)) + return true; } - return 0; + + if (select_lex->optimize_unflattened_subqueries(true)) + return true; + + return false; }

1 0