- commits - lists.mariadb.org

[Commits] c55145ede08: MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker
by psergey 23 Apr '21

23 Apr '21

revision-id: c55145ede080f1bcedccda33dc66de7967b02dc8 (mariadb-10.3.26-137-gc55145ede08) parent(s): e3a25793be936d9682a711a00d6b4bf708b6fb8d author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-23 19:28:48 +0300 message: MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker The optimizer removes redundant GROUP BY operations. If GROUP BY element is a subselect, it is "eliminated". However one must not eliminate the item if it is used both in the select list and in the GROUP BY, like so: select (select ... ) as SUBQ from ... group by SUBQ Do not eliminate such items. --- mysql-test/main/subselect4.result | 50 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/subselect4.test | 32 +++++++++++++++++++++++++ sql/sql_select.cc | 11 ++++++++- 3 files changed, 92 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index 156e78e7778..456b9bcd829 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2786,4 +2786,54 @@ id select_type table type possible_keys key key_len ref rows Extra set names default; set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold; DROP TABLE t1,t2; +# +# MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker +# +CREATE TABLE t1 (id INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +SELECT +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY f +); +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY f +) +1 +SELECT +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY 1 +); +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY 1 +) +1 +DROP TABLE t1; # End of 10.3 tests diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index a5fcc507905..ab311d3e505 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2308,4 +2308,36 @@ set names default; set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold; DROP TABLE t1,t2; +--echo # +--echo # MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker +--echo # +CREATE TABLE t1 (id INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); + +SELECT + 1 IN ( + SELECT + (SELECT COUNT(id) + FROM t1 + WHERE t1_outer.id <> id + ) AS f + FROM + t1 AS t1_outer + GROUP BY f + ); + +SELECT + 1 IN ( + SELECT + (SELECT COUNT(id) + FROM t1 + WHERE t1_outer.id <> id + ) AS f + FROM + t1 AS t1_outer + GROUP BY 1 + ); + +DROP TABLE t1; + --echo # End of 10.3 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b4e6c505261..d53c592ff7b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -596,7 +596,16 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex) { for (ORDER *ord= subq_select_lex->group_list.first; ord; ord= ord->next) { - (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); + /* + Do not remove the item if it is used in select list and then referred + from GROUP BY clause by its name or number. Example: + + select (select ... ) as SUBQ ... group by SUBQ + + Here SUBQ cannot be removed. + */ + if (!ord->in_field_list) + (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); } subq_select_lex->join->group_list= NULL; subq_select_lex->group_list.empty();

1 0

[Commits] 55491d94a6c: MDEV-21603: Long WHERE IN mariadb 10.3.22 crash
by psergey 23 Apr '21

23 Apr '21

revision-id: 55491d94a6c0d61f9db9ef5bb7950e2417411633 (mariadb-10.3.26-135-g55491d94a6c) parent(s): 75c01f39b1b4a6d27d36d075f8baab9bdda7cc7e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-23 19:02:38 +0300 message: MDEV-21603: Long WHERE IN mariadb 10.3.22 crash I_S tables and SHOW COMMANDS use special logic for creating and populating temp.tables, e.g. table creation is delayed. The code attempted to apply the same logic for the IN-to-SELECT temptable, which resulted in an attempt to read from not-yet-created temptable and crash. Fix this by restricting use of I_S table logic only to I_S tables. --- mysql-test/main/information_schema2.result | 12 ++++++++++++ mysql-test/main/information_schema2.test | 13 +++++++++++++ sql/sql_base.cc | 1 - sql/sql_class.h | 12 ++++++++++-- sql/sql_derived.cc | 8 +++++--- sql/sql_prepare.cc | 2 +- 6 files changed, 41 insertions(+), 7 deletions(-) diff --git a/mysql-test/main/information_schema2.result b/mysql-test/main/information_schema2.result index e23e81b885c..0f24ca3f264 100644 --- a/mysql-test/main/information_schema2.result +++ b/mysql-test/main/information_schema2.result @@ -18,3 +18,15 @@ t2 t3 t4 drop table t1, t2, t3, t4; +# +# MDEV-21603: Long WHERE IN mariadb 10.3.22 crash +# +connect con1,localhost,root,,; +set @@in_predicate_conversion_threshold=10; +use mysql; +show tables where tables_in_mysql in ('1','2','3','4','5','6','7','8','9','boom'); +Tables_in_mysql +show fields from user where Field in ('1','2','3','4','5','6','7','8','9','boom'); +Field Type Null Key Default Extra +disconnect con1; +connection default; diff --git a/mysql-test/main/information_schema2.test b/mysql-test/main/information_schema2.test index d2fa3da2b5f..d0cfeefa31a 100644 --- a/mysql-test/main/information_schema2.test +++ b/mysql-test/main/information_schema2.test @@ -17,3 +17,16 @@ create table t4 AS select table_name from information_schema.TABLES where table_ delete from t4 where table_name not in (select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE'); select * from t4 order by table_name; drop table t1, t2, t3, t4; + +--echo # +--echo # MDEV-21603: Long WHERE IN mariadb 10.3.22 crash +--echo # +connect (con1,localhost,root,,); + +set @@in_predicate_conversion_threshold=10; +use mysql; +show tables where tables_in_mysql in ('1','2','3','4','5','6','7','8','9','boom'); +show fields from user where Field in ('1','2','3','4','5','6','7','8','9','boom'); + +disconnect con1; +connection default; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0b17032da8f..4e5b32ce1e5 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5187,7 +5187,6 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags, uint counter; MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint(); DBUG_ENTER("open_normal_and_derived_tables"); - DBUG_ASSERT(!thd->fill_derived_tables()); if (open_tables(thd, &tables, &counter, flags, &prelocking_strategy) || mysql_handle_derived(thd->lex, dt_phases)) goto end; diff --git a/sql/sql_class.h b/sql/sql_class.h index 5ab93de7957..de2dab3cc14 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -3636,9 +3636,17 @@ class THD :public Statement, { return server_status & SERVER_STATUS_IN_TRANS; } - inline bool fill_derived_tables() + inline bool fill_derived_table(TABLE_LIST *tl) { - return !stmt_arena->is_stmt_prepare() && !lex->only_view_structure(); + /* + Do not fill derived table when + 1. running a PREPARE command + 2. this is a SHOW command and the table is a temp.table representing + the I_S table. + */ + return !stmt_arena->is_stmt_prepare() && // (1) + !(lex->only_view_structure() && tl? tl->schema_table_reformed:false); // (2) + } inline bool fill_information_schema_tables() { diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 50b0178c6c9..33cb6a50710 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -85,14 +85,16 @@ mysql_handle_derived(LEX *lex, uint phases) break; if (!(phases & phase_flag)) continue; - if (phase_flag >= DT_CREATE && !thd->fill_derived_tables()) - break; for (SELECT_LEX *sl= lex->all_selects_list; sl && !res; sl= sl->next_select_in_list()) { TABLE_LIST *cursor= sl->get_table_list(); + + if (phase_flag >= DT_CREATE && !thd->fill_derived_table(cursor)) + break; + sl->changed_elements|= TOUCHED_SEL_DERIVED; /* DT_MERGE_FOR_INSERT is not needed for views/derived tables inside @@ -193,7 +195,7 @@ mysql_handle_single_derived(LEX *lex, TABLE_LIST *derived, uint phases) if (phase_flag != DT_PREPARE && !(allowed_phases & phase_flag)) continue; - if (phase_flag >= DT_CREATE && !thd->fill_derived_tables()) + if (phase_flag >= DT_CREATE && !thd->fill_derived_table(derived)) break; if ((res= (*processors[phase])(lex->thd, lex, derived))) diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 9775e99afdd..357c1334ee2 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -1452,7 +1452,7 @@ static int mysql_test_update(Prepared_statement *stmt, } /* - thd->fill_derived_tables() is false here for sure (because it is + thd->fill_derived_table(table_list) is false here for sure (because it is preparation of PS, so we even do not check it). */ if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT))

1 0

[Commits] 18db45ff355: MDEV-21603: Long WHERE IN mariadb 10.3.22 crash
by psergey 23 Apr '21

23 Apr '21

revision-id: 18db45ff3553b6830ec1f72941301472abb3e706 (mariadb-10.3.26-135-g18db45ff355) parent(s): 75c01f39b1b4a6d27d36d075f8baab9bdda7cc7e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-23 16:00:29 +0300 message: MDEV-21603: Long WHERE IN mariadb 10.3.22 crash I_S tables and SHOW COMMANDS use special logic for creating and populating temp.tables, e.g. table creation is delayed. The code attempted to apply the same logic for the IN-to-SELECT temptable, which resulted in an attempt to read from not-yet-created temptable and crash. Fix this by restricting use of I_S table logic only to I_S tables. --- mysql-test/main/information_schema2.result | 12 ++++++++++++ mysql-test/main/information_schema2.test | 13 +++++++++++++ sql/sql_base.cc | 1 - sql/sql_class.h | 12 ++++++++++-- sql/sql_derived.cc | 8 +++++--- sql/sql_prepare.cc | 2 +- 6 files changed, 41 insertions(+), 7 deletions(-) diff --git a/mysql-test/main/information_schema2.result b/mysql-test/main/information_schema2.result index e23e81b885c..0f24ca3f264 100644 --- a/mysql-test/main/information_schema2.result +++ b/mysql-test/main/information_schema2.result @@ -18,3 +18,15 @@ t2 t3 t4 drop table t1, t2, t3, t4; +# +# MDEV-21603: Long WHERE IN mariadb 10.3.22 crash +# +connect con1,localhost,root,,; +set @@in_predicate_conversion_threshold=10; +use mysql; +show tables where tables_in_mysql in ('1','2','3','4','5','6','7','8','9','boom'); +Tables_in_mysql +show fields from user where Field in ('1','2','3','4','5','6','7','8','9','boom'); +Field Type Null Key Default Extra +disconnect con1; +connection default; diff --git a/mysql-test/main/information_schema2.test b/mysql-test/main/information_schema2.test index d2fa3da2b5f..d0cfeefa31a 100644 --- a/mysql-test/main/information_schema2.test +++ b/mysql-test/main/information_schema2.test @@ -17,3 +17,16 @@ create table t4 AS select table_name from information_schema.TABLES where table_ delete from t4 where table_name not in (select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE'); select * from t4 order by table_name; drop table t1, t2, t3, t4; + +--echo # +--echo # MDEV-21603: Long WHERE IN mariadb 10.3.22 crash +--echo # +connect (con1,localhost,root,,); + +set @@in_predicate_conversion_threshold=10; +use mysql; +show tables where tables_in_mysql in ('1','2','3','4','5','6','7','8','9','boom'); +show fields from user where Field in ('1','2','3','4','5','6','7','8','9','boom'); + +disconnect con1; +connection default; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0b17032da8f..4e5b32ce1e5 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5187,7 +5187,6 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags, uint counter; MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint(); DBUG_ENTER("open_normal_and_derived_tables"); - DBUG_ASSERT(!thd->fill_derived_tables()); if (open_tables(thd, &tables, &counter, flags, &prelocking_strategy) || mysql_handle_derived(thd->lex, dt_phases)) goto end; diff --git a/sql/sql_class.h b/sql/sql_class.h index 5ab93de7957..9d19e25f44f 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -3636,9 +3636,17 @@ class THD :public Statement, { return server_status & SERVER_STATUS_IN_TRANS; } - inline bool fill_derived_tables() + inline bool fill_derived_table(TABLE_LIST *tl) { - return !stmt_arena->is_stmt_prepare() && !lex->only_view_structure(); + /* + Do not fill derived table when + 1. running a PREPARE command + 2. this is a SHOW command and the table is a temp.table representing + the I_S table. + */ + return !stmt_arena->is_stmt_prepare() && // (1) + !(lex->only_view_structure() && tl->schema_table_reformed); // (2) + } inline bool fill_information_schema_tables() { diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 50b0178c6c9..33cb6a50710 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -85,14 +85,16 @@ mysql_handle_derived(LEX *lex, uint phases) break; if (!(phases & phase_flag)) continue; - if (phase_flag >= DT_CREATE && !thd->fill_derived_tables()) - break; for (SELECT_LEX *sl= lex->all_selects_list; sl && !res; sl= sl->next_select_in_list()) { TABLE_LIST *cursor= sl->get_table_list(); + + if (phase_flag >= DT_CREATE && !thd->fill_derived_table(cursor)) + break; + sl->changed_elements|= TOUCHED_SEL_DERIVED; /* DT_MERGE_FOR_INSERT is not needed for views/derived tables inside @@ -193,7 +195,7 @@ mysql_handle_single_derived(LEX *lex, TABLE_LIST *derived, uint phases) if (phase_flag != DT_PREPARE && !(allowed_phases & phase_flag)) continue; - if (phase_flag >= DT_CREATE && !thd->fill_derived_tables()) + if (phase_flag >= DT_CREATE && !thd->fill_derived_table(derived)) break; if ((res= (*processors[phase])(lex->thd, lex, derived))) diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 9775e99afdd..357c1334ee2 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -1452,7 +1452,7 @@ static int mysql_test_update(Prepared_statement *stmt, } /* - thd->fill_derived_tables() is false here for sure (because it is + thd->fill_derived_table(table_list) is false here for sure (because it is preparation of PS, so we even do not check it). */ if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT))

1 0

[Commits] e3a2579: MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP
by IgorBabaev 23 Apr '21

23 Apr '21

revision-id: e3a25793be936d9682a711a00d6b4bf708b6fb8d (mariadb-10.3.26-136-ge3a2579) parent(s): 6f271302b649ee11e7987b46fe24824c2ca2be7c author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-22 20:02:08 -0700 message: MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP Before this patch mergeable derived tables / view used in a multi-table update / delete were merged before the preparation stage. When the merge of a derived table / view is performed the on expression attached to it is fixed and ANDed with the where condition of the select S containing this derived table / view. It happens after the specification of the derived table / view has been merged into S. If the ON expression refers to a non existing field an error is reported and some other mergeable derived tables / views remain unmerged. It's not a problem if the multi-table update / delete statement is standalone. Yet if it is used in a stored procedure the select with incompletely merged derived tables / views may cause a problem for the second call of the procedure. This does not happen for select queries using derived tables / views, because in this case their specifications are merged after the preparation stage at which all ON expressions are fixed. This patch makes sure that merging of the derived tables / views used in a multi-table update / delete statement is performed after the preparation stage. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/multi_update.result | 53 +++++++++++++++++++++++++++++++++++++ mysql-test/main/multi_update.test | 53 +++++++++++++++++++++++++++++++++++++ sql/sql_base.cc | 3 ++- sql/sql_class.h | 4 ++- sql/sql_delete.cc | 15 +++++------ sql/sql_derived.cc | 5 +--- sql/sql_update.cc | 39 ++++++++++++++++++++------- 7 files changed, 147 insertions(+), 25 deletions(-) diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index b3edeb7..520199d 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -982,6 +982,59 @@ drop function f1; # # end of 5.5 tests # +# +# MDEV-24823: Invalid multi-table update of view within SP +# +create table t1 (id int) engine=myisam; +insert into t1 values (1),(2),(1); +create table t2 (pk int, c0 int) engine=myisam; +insert into t2 values (1,1), (2,3); +create view v2 as select * from t2; +create view v3 as select * from t2 where c0 < 3; +create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1; +call sp0(); +ERROR 42S22: Unknown column 't1.c1' in 'where clause' +call sp0(); +ERROR 42S22: Unknown column 't1.c1' in 'where clause' +create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1; +call sp1(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp1(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1; +call sp2(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp2(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +create procedure sp3() +update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1; +select * from t2; +pk c0 +1 1 +2 3 +call sp3(); +select * from t2; +pk c0 +1 2 +2 3 +call sp3(); +select * from t2; +pk c0 +1 3 +2 3 +create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1; +call sp4(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp4(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +drop procedure sp0; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop procedure sp4; +drop view v2,v3; +drop table t1,t2; +# End of 10.2 tests create table t1 (c1 int, c3 int); insert t1(c3) values (1), (2), (3), (4), (5), (6), (7), (8); create table t2 select * from t1; diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test index 8a32f62..84f06a7 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -958,6 +958,59 @@ drop function f1; --echo # end of 5.5 tests --echo # +--echo # +--echo # MDEV-24823: Invalid multi-table update of view within SP +--echo # + +create table t1 (id int) engine=myisam; +insert into t1 values (1),(2),(1); +create table t2 (pk int, c0 int) engine=myisam; +insert into t2 values (1,1), (2,3); +create view v2 as select * from t2; +create view v3 as select * from t2 where c0 < 3; + +create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1; +--error ER_BAD_FIELD_ERROR +call sp0(); +--error ER_BAD_FIELD_ERROR +call sp0(); + +create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1; +--error ER_BAD_FIELD_ERROR +call sp1(); +--error ER_BAD_FIELD_ERROR +call sp1(); + +create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1; +--error ER_BAD_FIELD_ERROR +call sp2(); +--error ER_BAD_FIELD_ERROR +call sp2(); + +create procedure sp3() +update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1; +select * from t2; +call sp3(); +select * from t2; +call sp3(); +select * from t2; + +create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1; +--error ER_BAD_FIELD_ERROR +call sp4(); +--error ER_BAD_FIELD_ERROR +call sp4(); + +drop procedure sp0; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop procedure sp4; +drop view v2,v3; +drop table t1,t2; + +--echo # End of 10.2 tests + # # MDEV-13911 Support ORDER BY and LIMIT in multi-table update # diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0b17032..74ac1a3 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7780,7 +7780,8 @@ bool setup_tables(THD *thd, Name_resolution_context *context, if (table_list->jtbm_subselect) { Item *item= table_list->jtbm_subselect->optimizer; - if (table_list->jtbm_subselect->optimizer->fix_fields(thd, &item)) + if (!table_list->jtbm_subselect->optimizer->fixed && + table_list->jtbm_subselect->optimizer->fix_fields(thd, &item)) { my_error(ER_TOO_MANY_TABLES,MYF(0), static_cast<int>(MAX_TABLES)); /* psergey-todo: WHY ER_TOO_MANY_TABLES ???*/ DBUG_RETURN(1); diff --git a/sql/sql_class.h b/sql/sql_class.h index 5ab93de..4f0df74 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -6117,7 +6117,8 @@ class multi_delete :public select_result_interceptor class multi_update :public select_result_interceptor { TABLE_LIST *all_tables; /* query/update command tables */ - List<TABLE_LIST> *leaves; /* list of leves of join table tree */ + List<TABLE_LIST> *leaves; /* list of leaves of join table tree */ + List<TABLE_LIST> updated_leaves; /* list of of updated leaves */ TABLE_LIST *update_tables; TABLE **tmp_tables, *main_table, *table_to_update; TMP_TABLE_PARAM *tmp_table_param; @@ -6155,6 +6156,7 @@ class multi_update :public select_result_interceptor List<Item> *fields, List<Item> *values, enum_duplicates handle_duplicates, bool ignore); ~multi_update(); + bool init(THD *thd); int prepare(List<Item> &list, SELECT_LEX_UNIT *u); int send_data(List<Item> &items); bool initialize_tables (JOIN *join); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 0857c4e..c78e8ed 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -1009,9 +1009,6 @@ int mysql_multi_delete_prepare(THD *thd) DELETE_ACL, SELECT_ACL, FALSE)) DBUG_RETURN(TRUE); - if (lex->select_lex.handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(TRUE); - /* Multi-delete can't be constructed over-union => we always have single SELECT on top and have to check underlying SELECTs of it @@ -1039,6 +1036,12 @@ int mysql_multi_delete_prepare(THD *thd) target_tbl->table_name.str, "DELETE"); DBUG_RETURN(TRUE); } + } + + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { /* Check that table from which we delete is not used somewhere inside subqueries/view. @@ -1083,12 +1086,6 @@ multi_delete::prepare(List<Item> &values, SELECT_LEX_UNIT *u) unit= u; do_delete= 1; THD_STAGE_INFO(thd, stage_deleting_from_main_table); - SELECT_LEX *select_lex= u->first_select(); - if (select_lex->first_cond_optimization) - { - if (select_lex->handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(TRUE); - } DBUG_RETURN(0); } diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 50b0178..eef2ae7 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -355,10 +355,6 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_RETURN(FALSE); } - if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI || - thd->lex->sql_command == SQLCOM_DELETE_MULTI) - thd->save_prep_leaf_list= TRUE; - arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test if (!derived->merged_for_insert || @@ -436,6 +432,7 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) derived->on_expr= expr; derived->prep_on_expr= expr->copy_andor_structure(thd); } + thd->where= "on clause"; if (derived->on_expr && derived->on_expr->fix_fields_if_needed_for_bool(thd, &derived->on_expr)) { diff --git a/sql/sql_update.cc b/sql/sql_update.cc index c66a474..c58eb4e 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1571,15 +1571,8 @@ bool Multiupdate_prelocking_strategy::handle_end(THD *thd) call in setup_tables()). */ - if (setup_tables_and_check_access(thd, &select_lex->context, - &select_lex->top_join_list, table_list, select_lex->leaf_tables, - FALSE, UPDATE_ACL, SELECT_ACL, FALSE)) - DBUG_RETURN(1); - - if (select_lex->handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(1); - - if (thd->lex->save_prep_leaf_tables()) + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, FALSE, TRUE)) DBUG_RETURN(1); List<Item> *fields= &lex->select_lex.item_list; @@ -1755,6 +1748,7 @@ int mysql_multi_update_prepare(THD *thd) skip all tables of UPDATE SELECT itself */ lex->select_lex.exclude_from_table_unique_test= TRUE; + /* We only need SELECT privilege for columns in the values list */ List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables); while ((tl= ti++)) @@ -1805,9 +1799,16 @@ bool mysql_multi_update(THD *thd, TABLE_LIST *table_list, List<Item> *fields, DBUG_RETURN(TRUE); } + if ((*result)->init(thd)) + DBUG_RETURN(1); + thd->abort_on_warning= !ignore && thd->is_strict_mode(); List<Item> total_list; + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, FALSE, FALSE)) + DBUG_RETURN(1); + if (select_lex->vers_setup_conds(thd, table_list)) DBUG_RETURN(1); @@ -1849,6 +1850,24 @@ multi_update::multi_update(THD *thd_arg, TABLE_LIST *table_list, } +bool multi_update::init(THD *thd) +{ + table_map tables_to_update= get_table_map(fields); + List_iterator_fast<TABLE_LIST> li(*leaves); + TABLE_LIST *tbl; + while ((tbl =li++)) + { + if (tbl->is_jtbm()) + continue; + if (!(tbl->table->map & tables_to_update)) + continue; + if (updated_leaves.push_back(tbl, thd->mem_root)) + return true; + } + return false; +} + + /* Connect fields with tables and create list of tables that are updated */ @@ -1865,7 +1884,7 @@ int multi_update::prepare(List<Item> &not_used_values, List_iterator_fast<Item> value_it(*values); uint i, max_fields; uint leaf_table_count= 0; - List_iterator<TABLE_LIST> ti(*leaves); + List_iterator<TABLE_LIST> ti(updated_leaves); DBUG_ENTER("multi_update::prepare"); if (prepared)

1 0

[Commits] 4ab7e1e: MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP
by IgorBabaev 23 Apr '21

23 Apr '21

revision-id: 4ab7e1e3e0d8d0114b83991d56591c7f5fc6cae9 (mariadb-10.3.26-136-g4ab7e1e) parent(s): 6f271302b649ee11e7987b46fe24824c2ca2be7c author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-22 17:42:12 -0700 message: MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP Before this patch mergeable derived tables / view used in a multi-table update / delete were merged before the preparation stage. When the merge of a derived table / view is performed the on expression attached to it is fixed and ANDed with the where condition of the select S containing this derived table / view. It happens after the specification of the derived table / view has been merged into S. If the ON expression refers to a non existing field an error is reported and some other mergeable derived tables / views remain unmerged. It's not a problem if the multi-table update / delete statement is standalone. Yet if it is used in a stored procedure the select with incompletely merged derived tables / views may cause a problem for the second call of the procedure. This does not happen for select queries using derived tables / views, because in this case their specifications are merged after the preparation stage at which all ON expressions are fixed. This patch makes sure that merging of the derived tables / views used in a multi-table update / delete statement is performed after the preparation stage. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/multi_update.result | 53 +++++++++++++++++++++++++++++++++++++ mysql-test/main/multi_update.test | 53 +++++++++++++++++++++++++++++++++++++ sql/sql_base.cc | 3 ++- sql/sql_class.h | 4 ++- sql/sql_delete.cc | 15 +++++------ sql/sql_derived.cc | 5 +--- sql/sql_lex.cc | 21 +++++++++++++++ sql/sql_lex.h | 2 ++ sql/sql_update.cc | 43 +++++++++++++++++++++--------- 9 files changed, 172 insertions(+), 27 deletions(-) diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index b3edeb7..520199d 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -982,6 +982,59 @@ drop function f1; # # end of 5.5 tests # +# +# MDEV-24823: Invalid multi-table update of view within SP +# +create table t1 (id int) engine=myisam; +insert into t1 values (1),(2),(1); +create table t2 (pk int, c0 int) engine=myisam; +insert into t2 values (1,1), (2,3); +create view v2 as select * from t2; +create view v3 as select * from t2 where c0 < 3; +create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1; +call sp0(); +ERROR 42S22: Unknown column 't1.c1' in 'where clause' +call sp0(); +ERROR 42S22: Unknown column 't1.c1' in 'where clause' +create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1; +call sp1(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp1(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1; +call sp2(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp2(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +create procedure sp3() +update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1; +select * from t2; +pk c0 +1 1 +2 3 +call sp3(); +select * from t2; +pk c0 +1 2 +2 3 +call sp3(); +select * from t2; +pk c0 +1 3 +2 3 +create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1; +call sp4(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp4(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +drop procedure sp0; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop procedure sp4; +drop view v2,v3; +drop table t1,t2; +# End of 10.2 tests create table t1 (c1 int, c3 int); insert t1(c3) values (1), (2), (3), (4), (5), (6), (7), (8); create table t2 select * from t1; diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test index 8a32f62..84f06a7 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -958,6 +958,59 @@ drop function f1; --echo # end of 5.5 tests --echo # +--echo # +--echo # MDEV-24823: Invalid multi-table update of view within SP +--echo # + +create table t1 (id int) engine=myisam; +insert into t1 values (1),(2),(1); +create table t2 (pk int, c0 int) engine=myisam; +insert into t2 values (1,1), (2,3); +create view v2 as select * from t2; +create view v3 as select * from t2 where c0 < 3; + +create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1; +--error ER_BAD_FIELD_ERROR +call sp0(); +--error ER_BAD_FIELD_ERROR +call sp0(); + +create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1; +--error ER_BAD_FIELD_ERROR +call sp1(); +--error ER_BAD_FIELD_ERROR +call sp1(); + +create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1; +--error ER_BAD_FIELD_ERROR +call sp2(); +--error ER_BAD_FIELD_ERROR +call sp2(); + +create procedure sp3() +update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1; +select * from t2; +call sp3(); +select * from t2; +call sp3(); +select * from t2; + +create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1; +--error ER_BAD_FIELD_ERROR +call sp4(); +--error ER_BAD_FIELD_ERROR +call sp4(); + +drop procedure sp0; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop procedure sp4; +drop view v2,v3; +drop table t1,t2; + +--echo # End of 10.2 tests + # # MDEV-13911 Support ORDER BY and LIMIT in multi-table update # diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0b17032..74ac1a3 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7780,7 +7780,8 @@ bool setup_tables(THD *thd, Name_resolution_context *context, if (table_list->jtbm_subselect) { Item *item= table_list->jtbm_subselect->optimizer; - if (table_list->jtbm_subselect->optimizer->fix_fields(thd, &item)) + if (!table_list->jtbm_subselect->optimizer->fixed && + table_list->jtbm_subselect->optimizer->fix_fields(thd, &item)) { my_error(ER_TOO_MANY_TABLES,MYF(0), static_cast<int>(MAX_TABLES)); /* psergey-todo: WHY ER_TOO_MANY_TABLES ???*/ DBUG_RETURN(1); diff --git a/sql/sql_class.h b/sql/sql_class.h index 5ab93de..4f0df74 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -6117,7 +6117,8 @@ class multi_delete :public select_result_interceptor class multi_update :public select_result_interceptor { TABLE_LIST *all_tables; /* query/update command tables */ - List<TABLE_LIST> *leaves; /* list of leves of join table tree */ + List<TABLE_LIST> *leaves; /* list of leaves of join table tree */ + List<TABLE_LIST> updated_leaves; /* list of of updated leaves */ TABLE_LIST *update_tables; TABLE **tmp_tables, *main_table, *table_to_update; TMP_TABLE_PARAM *tmp_table_param; @@ -6155,6 +6156,7 @@ class multi_update :public select_result_interceptor List<Item> *fields, List<Item> *values, enum_duplicates handle_duplicates, bool ignore); ~multi_update(); + bool init(THD *thd); int prepare(List<Item> &list, SELECT_LEX_UNIT *u); int send_data(List<Item> &items); bool initialize_tables (JOIN *join); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 0857c4e..c78e8ed 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -1009,9 +1009,6 @@ int mysql_multi_delete_prepare(THD *thd) DELETE_ACL, SELECT_ACL, FALSE)) DBUG_RETURN(TRUE); - if (lex->select_lex.handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(TRUE); - /* Multi-delete can't be constructed over-union => we always have single SELECT on top and have to check underlying SELECTs of it @@ -1039,6 +1036,12 @@ int mysql_multi_delete_prepare(THD *thd) target_tbl->table_name.str, "DELETE"); DBUG_RETURN(TRUE); } + } + + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { /* Check that table from which we delete is not used somewhere inside subqueries/view. @@ -1083,12 +1086,6 @@ multi_delete::prepare(List<Item> &values, SELECT_LEX_UNIT *u) unit= u; do_delete= 1; THD_STAGE_INFO(thd, stage_deleting_from_main_table); - SELECT_LEX *select_lex= u->first_select(); - if (select_lex->first_cond_optimization) - { - if (select_lex->handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(TRUE); - } DBUG_RETURN(0); } diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 50b0178..eef2ae7 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -355,10 +355,6 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_RETURN(FALSE); } - if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI || - thd->lex->sql_command == SQLCOM_DELETE_MULTI) - thd->save_prep_leaf_list= TRUE; - arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test if (!derived->merged_for_insert || @@ -436,6 +432,7 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) derived->on_expr= expr; derived->prep_on_expr= expr->copy_andor_structure(thd); } + thd->where= "on clause"; if (derived->on_expr && derived->on_expr->fix_fields_if_needed_for_bool(thd, &derived->on_expr)) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index fe4d086..5604658 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4927,6 +4927,27 @@ bool st_select_lex::save_prep_leaf_tables(THD *thd) } +/** + Set exclude_from_table_unique_test for selects of this select and all selects + belonging to the underlying units of derived tables or views +*/ + +void st_select_lex::set_unique_exclude() +{ + exclude_from_table_unique_test= TRUE; + for (SELECT_LEX_UNIT *unit= first_inner_unit(); + unit; + unit= unit->next_unit()) + { + if (unit->derived && unit->derived->is_view_or_derived()) + { + for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select()) + sl->set_unique_exclude(); + } + } +} + + /* Return true if this select_lex has been converted into a semi-join nest within 'ancestor'. diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b5b39fe..c3e48a7 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1354,6 +1354,8 @@ class st_select_lex: public st_select_lex_node bool save_leaf_tables(THD *thd); bool save_prep_leaf_tables(THD *thd); + void set_unique_exclude(); + bool is_merged_child_of(st_select_lex *ancestor); /* diff --git a/sql/sql_update.cc b/sql/sql_update.cc index c66a474..01bf6e7 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1571,15 +1571,8 @@ bool Multiupdate_prelocking_strategy::handle_end(THD *thd) call in setup_tables()). */ - if (setup_tables_and_check_access(thd, &select_lex->context, - &select_lex->top_join_list, table_list, select_lex->leaf_tables, - FALSE, UPDATE_ACL, SELECT_ACL, FALSE)) - DBUG_RETURN(1); - - if (select_lex->handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(1); - - if (thd->lex->save_prep_leaf_tables()) + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, FALSE, TRUE)) DBUG_RETURN(1); List<Item> *fields= &lex->select_lex.item_list; @@ -1754,7 +1747,8 @@ int mysql_multi_update_prepare(THD *thd) Check that we are not using table that we are updating, but we should skip all tables of UPDATE SELECT itself */ - lex->select_lex.exclude_from_table_unique_test= TRUE; + lex->select_lex.set_unique_exclude(); + /* We only need SELECT privilege for columns in the values list */ List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables); while ((tl= ti++)) @@ -1805,12 +1799,19 @@ bool mysql_multi_update(THD *thd, TABLE_LIST *table_list, List<Item> *fields, DBUG_RETURN(TRUE); } + if ((*result)->init(thd)) + DBUG_RETURN(1); + thd->abort_on_warning= !ignore && thd->is_strict_mode(); List<Item> total_list; - if (select_lex->vers_setup_conds(thd, table_list)) + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, FALSE, FALSE)) DBUG_RETURN(1); + if (select_lex->vers_setup_conds(thd, table_list)) + DBUG_RETURN(1); + res= mysql_select(thd, table_list, select_lex->with_wild, total_list, conds, select_lex->order_list.elements, @@ -1849,6 +1850,24 @@ multi_update::multi_update(THD *thd_arg, TABLE_LIST *table_list, } +bool multi_update::init(THD *thd) +{ + table_map tables_to_update= get_table_map(fields); + List_iterator_fast<TABLE_LIST> li(*leaves); + TABLE_LIST *tbl; + while ((tbl =li++)) + { + if (tbl->is_jtbm()) + continue; + if (!(tbl->table->map & tables_to_update)) + continue; + if (updated_leaves.push_back(tbl, thd->mem_root)) + return true; + } + return false; +} + + /* Connect fields with tables and create list of tables that are updated */ @@ -1865,7 +1884,7 @@ int multi_update::prepare(List<Item> &not_used_values, List_iterator_fast<Item> value_it(*values); uint i, max_fields; uint leaf_table_count= 0; - List_iterator<TABLE_LIST> ti(*leaves); + List_iterator<TABLE_LIST> ti(updated_leaves); DBUG_ENTER("multi_update::prepare"); if (prepared)

1 0

[Commits] dd6ad380685: Code cleanup: merge walk_items_for_table_list with walk_table_functions_for_list
by psergey 21 Apr '21

21 Apr '21

revision-id: dd6ad3806856221f1af302e61ebd985905a00060 (mariadb-10.5.2-640-gdd6ad380685) parent(s): 4930f9c94bb610f4d76b73a8b6b351d9fa9c5d68 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-21 12:32:58 +0300 message: Code cleanup: merge walk_items_for_table_list with walk_table_functions_for_list --- sql/item_subselect.cc | 18 ++++++++++++++---- 1 file changed, 14 insertions(+), 4 deletions(-) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index c9afc9c1bcd..3f60203387a 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -685,6 +685,12 @@ bool Item_subselect::is_expensive() } +/* + @brief + Apply item processor for all scalar (i.e. Item*) expressions that + occur in the nested join. +*/ + static int walk_items_for_table_list(Item_processor processor, bool walk_subquery, void *argument, @@ -699,6 +705,14 @@ int walk_items_for_table_list(Item_processor processor, if ((res= table->on_expr->walk(processor, walk_subquery, argument))) return res; } + if (Table_function_json_table *tf= table->table_function) + { + if ((res= tf->walk_items(processor, walk_subquery, argument))) + { + return res; + } + } + if (table->nested_join) { if ((res= walk_items_for_table_list(processor, walk_subquery, argument, @@ -807,10 +821,6 @@ bool Item_subselect::walk(Item_processor processor, bool walk_subquery, *lex->join_list)) return 1; - if (walk_table_functions_for_list(processor, walk_subquery, argument, - *lex->join_list)) - return 1; - while (Item *item= li++) { if (item->walk(processor, walk_subquery, argument))

1 0

[Commits] 03a162bc51a: MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe...
by psergey 20 Apr '21

20 Apr '21

revision-id: 03a162bc51aadb8fd71a60bb24806ffea3b828cf (mariadb-10.5.2-589-g03a162bc51a) parent(s): 36c3be336616cb95443c5164a0e082862c6270d0 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-20 14:29:36 +0300 message: MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe... Actually the functions are safe for the current version. Still, we will mark the function as SBR-unsafe to be more future-proof. --- mysql-test/suite/json/r/json_table_binlog.result | 26 ++++++++++++++++++++++++ mysql-test/suite/json/t/json_table_binlog.test | 25 +++++++++++++++++++++++ sql/json_table.cc | 16 +++++++++++++++ sql/json_table.h | 7 +------ sql/sql_yacc.yy | 2 +- 5 files changed, 69 insertions(+), 7 deletions(-) diff --git a/mysql-test/suite/json/r/json_table_binlog.result b/mysql-test/suite/json/r/json_table_binlog.result new file mode 100644 index 00000000000..472f7395648 --- /dev/null +++ b/mysql-test/suite/json/r/json_table_binlog.result @@ -0,0 +1,26 @@ +# +# MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe for statement binlog and should be marked as such +# +create table t1 (a int); +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +set binlog_format='statement'; +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave +set binlog_format='mixed'; +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; +# This must show Annotate_rows, Write_rows_v1 events. Not the statement event +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +drop table t1; diff --git a/mysql-test/suite/json/t/json_table_binlog.test b/mysql-test/suite/json/t/json_table_binlog.test new file mode 100644 index 00000000000..dcc05fb855d --- /dev/null +++ b/mysql-test/suite/json/t/json_table_binlog.test @@ -0,0 +1,25 @@ +--source include/have_binlog_format_mixed.inc + +--echo # +--echo # MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe for statement binlog and should be marked as such +--echo # + +create table t1 (a int); + +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +set binlog_format='statement'; +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; + +set binlog_format='mixed'; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +let $binlog_file= LAST; + +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; + +--echo # This must show Annotate_rows, Write_rows_v1 events. Not the statement event +--source include/show_binlog_events.inc +drop table t1; diff --git a/sql/json_table.cc b/sql/json_table.cc index a16adc25f08..c07c96e1b7a 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -978,6 +978,22 @@ int Json_table_column::On_response::print(const char *name, String *str) const str->append(name)); } +Table_function_json_table::Table_function_json_table(THD *thd, Item *json) : + m_json(json) +{ + cur_parent= &m_nested_path; + last_sibling_hook= &m_nested_path.m_nested; + /* + Currently, the evaluation of JSON_TABLE is deterministic (passing the same + input string will produce the same set of rows in the same order). + In order to be future-proof and to be able to make changes like + - sort array arguments by name (like MySQL does) + - change the way duplicate object members are handled + we mark the function as SBR-unsafe. (If we re-consider, making the function + SBR-safe is easier than making it unsafe) + */ + thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_SYSTEM_FUNCTION); +} void Table_function_json_table::start_nested_path(Json_table_nested_path *np) { diff --git a/sql/json_table.h b/sql/json_table.h index 09e4295d80c..f1ae5d5c577 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -225,12 +225,7 @@ class Table_function_json_table : public Sql_alloc String *str, enum_query_type query_type); /*** Construction interface to be used from the parser ***/ - Table_function_json_table(Item *json): - m_json(json) - { - cur_parent= &m_nested_path; - last_sibling_hook= &m_nested_path.m_nested; - } + Table_function_json_table(THD *thd, Item *json); void start_nested_path(Json_table_nested_path *np); void end_nested_path(); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index b92a83665ea..fab17ff7a25 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11685,7 +11685,7 @@ table_function: expr ',' { Table_function_json_table *jt= - new (thd->mem_root) Table_function_json_table($4); + new (thd->mem_root) Table_function_json_table(thd, $4); if (unlikely(!jt)) MYSQL_YYABORT; Lex->json_table= jt;

1 0

[Commits] 75cc808: MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP
by IgorBabaev 20 Apr '21

20 Apr '21

revision-id: 75cc8083605e8ce92d45574a98121b7043e29e5a (mariadb-10.2.31-812-g75cc808) parent(s): 8f7a6cde580298116b50b144984f996dc6af60ae author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-19 19:52:06 -0700 message: MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP Before this patch mergeable derived tables / view used in a multi-table update / delete were merged before the preparation stage. When the merge of a derived table / view is performed the on expression attached to it is fixed and ANDed with the where condition of the select S containing this derived table / view. It happens after the specification of the derived table / view has been merged into S. If the ON expression refers to a non existing field an error is reported and some other mergeable derived tables / views remain unmerged. It's not a problem if the multi-table update / delete statement is standalone. Yet if it is used in a stored procedure the select with incompletely merged derived tables / views may cause a problem for the second call of the procedure. This does not happen for select queries using derived tables / views, because in this case their specifications are merged after the preparation stage at which all ON expressions are fixed. This patch makes sure that merging of the derived tables / views used in a multi-table update / delete statement is performed after the preparation stage. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/r/multi_update.result | 53 ++++++++++++++++++++++++++++++++++++++++ mysql-test/t/multi_update.test | 53 ++++++++++++++++++++++++++++++++++++++++ sql/sql_base.cc | 3 ++- sql/sql_class.h | 4 ++- sql/sql_delete.cc | 15 +++++------- sql/sql_derived.cc | 5 +--- sql/sql_lex.cc | 21 ++++++++++++++++ sql/sql_lex.h | 2 ++ sql/sql_update.cc | 41 ++++++++++++++++++++++--------- 9 files changed, 171 insertions(+), 26 deletions(-) diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index a5c60d6..1532ebb 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -1043,3 +1043,56 @@ drop function f1; # # end of 5.5 tests # +# +# MDEV-24823: Invalid multi-table update of view within SP +# +create table t1 (id int) engine=myisam; +insert into t1 values (1),(2),(1); +create table t2 (pk int, c0 int) engine=myisam; +insert into t2 values (1,1), (2,3); +create view v2 as select * from t2; +create view v3 as select * from t2 where c0 < 3; +create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1; +call sp0(); +ERROR 42S22: Unknown column 't1.c1' in 'where clause' +call sp0(); +ERROR 42S22: Unknown column 't1.c1' in 'where clause' +create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1; +call sp1(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp1(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1; +call sp2(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp2(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +create procedure sp3() +update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1; +select * from t2; +pk c0 +1 1 +2 3 +call sp3(); +select * from t2; +pk c0 +1 2 +2 3 +call sp3(); +select * from t2; +pk c0 +1 3 +2 3 +create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1; +call sp4(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp4(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +drop procedure sp0; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop procedure sp4; +drop view v2,v3; +drop table t1,t2; +# End of 10.2 tests diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index b5328f7..dff3938 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -1041,3 +1041,56 @@ drop function f1; --echo # --echo # end of 5.5 tests --echo # + +--echo # +--echo # MDEV-24823: Invalid multi-table update of view within SP +--echo # + +create table t1 (id int) engine=myisam; +insert into t1 values (1),(2),(1); +create table t2 (pk int, c0 int) engine=myisam; +insert into t2 values (1,1), (2,3); +create view v2 as select * from t2; +create view v3 as select * from t2 where c0 < 3; + +create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1; +--error ER_BAD_FIELD_ERROR +call sp0(); +--error ER_BAD_FIELD_ERROR +call sp0(); + +create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1; +--error ER_BAD_FIELD_ERROR +call sp1(); +--error ER_BAD_FIELD_ERROR +call sp1(); + +create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1; +--error ER_BAD_FIELD_ERROR +call sp2(); +--error ER_BAD_FIELD_ERROR +call sp2(); + +create procedure sp3() +update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1; +select * from t2; +call sp3(); +select * from t2; +call sp3(); +select * from t2; + +create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1; +--error ER_BAD_FIELD_ERROR +call sp4(); +--error ER_BAD_FIELD_ERROR +call sp4(); + +drop procedure sp0; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop procedure sp4; +drop view v2,v3; +drop table t1,t2; + +--echo # End of 10.2 tests diff --git a/sql/sql_base.cc b/sql/sql_base.cc index b8d18ab..2086bea 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7501,7 +7501,8 @@ bool setup_tables(THD *thd, Name_resolution_context *context, if (table_list->jtbm_subselect) { Item *item= table_list->jtbm_subselect->optimizer; - if (table_list->jtbm_subselect->optimizer->fix_fields(thd, &item)) + if (!table_list->jtbm_subselect->optimizer->fixed && + table_list->jtbm_subselect->optimizer->fix_fields(thd, &item)) { my_error(ER_TOO_MANY_TABLES,MYF(0), static_cast<int>(MAX_TABLES)); /* psergey-todo: WHY ER_TOO_MANY_TABLES ???*/ DBUG_RETURN(1); diff --git a/sql/sql_class.h b/sql/sql_class.h index c47ea9c..ce4bf67 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -5600,7 +5600,8 @@ class multi_delete :public select_result_interceptor class multi_update :public select_result_interceptor { TABLE_LIST *all_tables; /* query/update command tables */ - List<TABLE_LIST> *leaves; /* list of leves of join table tree */ + List<TABLE_LIST> *leaves; /* list of leaves of join table tree */ + List<TABLE_LIST> updated_leaves; /* list of of updated leaves */ TABLE_LIST *update_tables, *table_being_updated; TABLE **tmp_tables, *main_table, *table_to_update; TMP_TABLE_PARAM *tmp_table_param; @@ -5632,6 +5633,7 @@ class multi_update :public select_result_interceptor List<Item> *fields, List<Item> *values, enum_duplicates handle_duplicates, bool ignore); ~multi_update(); + bool init(THD *thd); int prepare(List<Item> &list, SELECT_LEX_UNIT *u); int send_data(List<Item> &items); bool initialize_tables (JOIN *join); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index e2d4cd4..61a3b4e 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -841,9 +841,6 @@ int mysql_multi_delete_prepare(THD *thd) DELETE_ACL, SELECT_ACL, FALSE)) DBUG_RETURN(TRUE); - if (lex->select_lex.handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(TRUE); - /* Multi-delete can't be constructed over-union => we always have single SELECT on top and have to check underlying SELECTs of it @@ -871,6 +868,12 @@ int mysql_multi_delete_prepare(THD *thd) target_tbl->table_name, "DELETE"); DBUG_RETURN(TRUE); } + } + + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { /* Check that table from which we delete is not used somewhere inside subqueries/view. @@ -915,12 +918,6 @@ multi_delete::prepare(List<Item> &values, SELECT_LEX_UNIT *u) unit= u; do_delete= 1; THD_STAGE_INFO(thd, stage_deleting_from_main_table); - SELECT_LEX *select_lex= u->first_select(); - if (select_lex->first_cond_optimization) - { - if (select_lex->handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(TRUE); - } DBUG_RETURN(0); } diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index be5905d..404fb03 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -354,10 +354,6 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_RETURN(FALSE); } - if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI || - thd->lex->sql_command == SQLCOM_DELETE_MULTI) - thd->save_prep_leaf_list= TRUE; - arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test if (!derived->merged_for_insert || @@ -435,6 +431,7 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) derived->on_expr= expr; derived->prep_on_expr= expr->copy_andor_structure(thd); } + thd->where= "on clause"; if (derived->on_expr && ((!derived->on_expr->fixed && derived->on_expr->fix_fields(thd, &derived->on_expr)) || diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 5059e4f..97c4185 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4619,6 +4619,27 @@ bool st_select_lex::save_prep_leaf_tables(THD *thd) } +/** + Set exclude_from_table_unique_test for selects of this select and all selects + belonging to the underlying units of derived tables or views +*/ + +void st_select_lex::set_unique_exclude() +{ + exclude_from_table_unique_test= TRUE; + for (SELECT_LEX_UNIT *unit= first_inner_unit(); + unit; + unit= unit->next_unit()) + { + if (unit->derived && unit->derived->is_view_or_derived()) + { + for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select()) + sl->set_unique_exclude(); + } + } +} + + /* Return true if this select_lex has been converted into a semi-join nest within 'ancestor'. diff --git a/sql/sql_lex.h b/sql/sql_lex.h index f733f78..531c5c9 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1146,6 +1146,8 @@ class st_select_lex: public st_select_lex_node bool save_leaf_tables(THD *thd); bool save_prep_leaf_tables(THD *thd); + void set_unique_exclude(); + bool is_merged_child_of(st_select_lex *ancestor); /* diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 01743a6..e8b973c 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1390,15 +1390,8 @@ bool Multiupdate_prelocking_strategy::handle_end(THD *thd) call in setup_tables()). */ - if (setup_tables_and_check_access(thd, &select_lex->context, - &select_lex->top_join_list, table_list, select_lex->leaf_tables, - FALSE, UPDATE_ACL, SELECT_ACL, FALSE)) - DBUG_RETURN(1); - - if (select_lex->handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(1); - - if (thd->lex->save_prep_leaf_tables()) + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, FALSE, TRUE)) DBUG_RETURN(1); List<Item> *fields= &lex->select_lex.item_list; @@ -1574,7 +1567,8 @@ int mysql_multi_update_prepare(THD *thd) Check that we are not using table that we are updating, but we should skip all tables of UPDATE SELECT itself */ - lex->select_lex.exclude_from_table_unique_test= TRUE; + lex->select_lex.set_unique_exclude(); + /* We only need SELECT privilege for columns in the values list */ List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables); while ((tl= ti++)) @@ -1635,9 +1629,16 @@ bool mysql_multi_update(THD *thd, TABLE_LIST *table_list, List<Item> *fields, DBUG_RETURN(TRUE); } + if ((*result)->init(thd)) + DBUG_RETURN(1); + thd->abort_on_warning= !ignore && thd->is_strict_mode(); List<Item> total_list; + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, FALSE, FALSE)) + DBUG_RETURN(1); + res= mysql_select(thd, table_list, select_lex->with_wild, total_list, conds, 0, NULL, NULL, NULL, NULL, @@ -1673,6 +1674,24 @@ multi_update::multi_update(THD *thd_arg, TABLE_LIST *table_list, {} +bool multi_update::init(THD *thd) +{ + table_map tables_to_update= get_table_map(fields); + List_iterator_fast<TABLE_LIST> li(*leaves); + TABLE_LIST *tbl; + while ((tbl =li++)) + { + if (tbl->is_jtbm()) + continue; + if (!(tbl->table->map & tables_to_update)) + continue; + if (updated_leaves.push_back(tbl, thd->mem_root)) + return true; + } + return false; +} + + /* Connect fields with tables and create list of tables that are updated */ @@ -1689,7 +1708,7 @@ int multi_update::prepare(List<Item> &not_used_values, List_iterator_fast<Item> value_it(*values); uint i, max_fields; uint leaf_table_count= 0; - List_iterator<TABLE_LIST> ti(*leaves); + List_iterator<TABLE_LIST> ti(updated_leaves); DBUG_ENTER("multi_update::prepare"); if (prepared)

1 0

[Commits] a7928cc: MDEV-25362 Incorrect name resolution for subqueries in ON expressions
by IgorBabaev 19 Apr '21

19 Apr '21

revision-id: a7928cc7e7848e0a282d60c09cfe9dd82c0382fe (mariadb-10.4.11-599-ga7928cc) parent(s): c3b016efde4b1e0c2b85ca26c814ad43f5611ab2 author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-18 23:29:33 -0700 message: MDEV-25362 Incorrect name resolution for subqueries in ON expressions This patch sets the proper name resolution context for outer references used in a subquery from an ON clause. Usually this context is more narrow than the name resolution context of the parent select that were used before this fix. This fix revealed another problem that concerned ON expressions used in from clauses of specifications of derived tables / views / CTEs. The name resolution outer context for such ON expression must be set to NULL to prevent name resolution beyond the derived table where it is used. The solution to resolve this problem applied in sql_derived.cc was provided by Sergei Petrunia <sergey(a)mariadb.com>. The change in sql_parse.cc is not good for 10.4+. A corresponding diff for 10.4+ will be provided in JIRA entry for this bug. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/join_outer.result | 40 +++++++++++++++++++++++++++ mysql-test/main/join_outer.test | 50 ++++++++++++++++++++++++++++++++++ mysql-test/main/join_outer_jcl6.result | 40 +++++++++++++++++++++++++++ sql/sql_derived.cc | 31 +++++++++++++++++++++ sql/sql_lex.cc | 2 +- 5 files changed, 162 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index c92b8e6..362335a 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2687,6 +2687,46 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV DROP TABLE t1,t2,t3,t4; # end of 10.1 tests # +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests +# # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins # create table t1(a int); diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index f835d8a..bf75a52 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -2192,6 +2192,56 @@ DROP TABLE t1,t2,t3,t4; --echo # end of 10.1 tests --echo # +--echo # MDEV-25362: name resolution for subqueries in ON expressions +--echo # + +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=t1.a) + ) on (t2.c=t1.a ); + +# This must produce an error: +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=(select max(g) from t4 where t4.h=t1.a)) + ) on (t2.c=t1.a ); + +drop table t1,t2,t3,t4; + +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); + +--error ER_BAD_FIELD_ERROR +select * from ( select * from t1 left join t2 + on b in (select x from t3 as sq1) + ) as sq2; + +drop table t1,t2,t3; + +--echo # end of 10.2 tests + +--echo # --echo # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins --echo # create table t1(a int); diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index 2c7dc7b..2810ffa 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2694,6 +2694,46 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV DROP TABLE t1,t2,t3,t4; # end of 10.1 tests # +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests +# # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins # create table t1(a int); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 500cb43..2ad31ae 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -596,6 +596,32 @@ bool mysql_derived_init(THD *thd, LEX *lex, TABLE_LIST *derived) } + /* + @brief + Prevent name resolution out of context of ON expressions in derived tables + + @param + join_list list of tables used in from list of a derived + + @details + The function sets the Name_resolution_context::outer_context to NULL + for all ON expressions contexts in the given join list. It does this + recursively for all nested joins the list contains. +*/ + +static void nullify_outer_context_for_on_clauses(List<TABLE_LIST>& join_list) +{ + List_iterator<TABLE_LIST> li(join_list); + while (TABLE_LIST *table= li++) + { + if (table->on_context) + table->on_context->outer_context= NULL; + if (table->nested_join) + nullify_outer_context_for_on_clauses(table->nested_join->join_list); + } +} + + /* Create temporary table structure (but do not fill it) @@ -760,7 +786,12 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) /* prevent name resolving out of derived table */ for (SELECT_LEX *sl= first_select; sl; sl= sl->next_select()) { + // Prevent it for the WHERE clause sl->context.outer_context= 0; + + // And for ON clauses, if there are any + nullify_outer_context_for_on_clauses(*sl->join_list); + if (!derived->is_with_table_recursive_reference() || (!derived->with->with_anchor && !derived->with->is_with_prepared_anchor())) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index ecde337..5338fad 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -9391,7 +9391,7 @@ SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit) (curr_sel == NULL && current_select == &builtin_select)); if (curr_sel) { - curr_sel->register_unit(unit, &curr_sel->context); + curr_sel->register_unit(unit, context_stack.head()); curr_sel->add_statistics(unit); }

1 0

[Commits] 48645d7f73d: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set
by psergey 17 Apr '21

17 Apr '21

revision-id: 48645d7f73ddab1752468eae10f22df43001d231 (mariadb-10.5.2-592-g48645d7f73d) parent(s): d3004d1b88072c170a0764a329bfdcec5406328e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-17 18:23:15 +0300 message: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set Followup part#2: allocate the List object on the right mem-root, too. --- mysql-test/suite/json/r/json_table.result | 38 +++++++++++++++++++++++++++++- mysql-test/suite/json/t/json_table.test | 39 ++++++++++++++++++++++++++++++- sql/json_table.cc | 2 +- 3 files changed, 76 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index 1ebb90918f9..3ef87b3e24b 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -722,7 +722,43 @@ insert into t1 values ('Jeans', '{"color": ["black"], "price": 60}'); insert into t1 values ('Jeans', '{"color": ["gray"], "price": 60}'); insert into t1 values ('Laptop', '{"color": ["black"], "price": 1000}'); insert into t1 values ('Shirt', '{"color": ["black"], "price": 20}'); -select t.item_name, jt.* from (select t1.item_name, concat(concat(concat("{\"color\": ",concat(concat("[\"",group_concat(jt.color separator "\", \"")),"\"]")),','),concat(concat("\"price\": ",jt.price),'}')) as item_props from t1, json_table(t1.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt group by t1.item_name, jt.price) as t, json_table(t.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt order by t.item_name, jt.price, jt.color; +select +t.item_name, +jt.* +from +(select +t1.item_name, +concat( +concat( +concat( +"{\"color\": ", +concat( +concat("[\"", + group_concat( jt.color separator "\", \"") + ), + "\"]") +),',' + ), +concat(concat("\"price\": ",jt.price),'}') +) as item_props +from +t1, +json_table( +t1.item_props, +'$' columns ( +nested path '$.color[*]' columns (color varchar(32) path '$'), +price int path '$.price') +) as jt +group by +t1.item_name, jt.price +) as t, +json_table(t.item_props, +'$' columns ( +nested path '$.color[*]' columns (color varchar(32) path '$'), +price int path '$.price') +) as jt +order by +t.item_name, jt.price, jt.color; item_name color price Jeans brown 50 Jeans green 50 diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 39c97f73496..b8b16a750d1 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -623,7 +623,44 @@ insert into t1 values ('Jeans', '{"color": ["gray"], "price": 60}'); insert into t1 values ('Laptop', '{"color": ["black"], "price": 1000}'); insert into t1 values ('Shirt', '{"color": ["black"], "price": 20}'); -select t.item_name, jt.* from (select t1.item_name, concat(concat(concat("{\"color\": ",concat(concat("[\"",group_concat(jt.color separator "\", \"")),"\"]")),','),concat(concat("\"price\": ",jt.price),'}')) as item_props from t1, json_table(t1.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt group by t1.item_name, jt.price) as t, json_table(t.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt order by t.item_name, jt.price, jt.color; +select + t.item_name, + jt.* +from + (select + t1.item_name, + concat( + concat( + concat( + "{\"color\": ", + concat( + concat("[\"", + group_concat( jt.color separator "\", \"") + ), + "\"]") + ),',' + ), + concat(concat("\"price\": ",jt.price),'}') + ) as item_props + from + t1, + json_table( + t1.item_props, + '$' columns ( + nested path '$.color[*]' columns (color varchar(32) path '$'), + price int path '$.price') + ) as jt + group by + t1.item_name, jt.price + ) as t, + + json_table(t.item_props, + '$' columns ( + nested path '$.color[*]' columns (color varchar(32) path '$'), + price int path '$.price') + ) as jt +order by + t.item_name, jt.price, jt.color; drop table t1; diff --git a/sql/json_table.cc b/sql/json_table.cc index 8ad246f39f1..39eb415aaed 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -148,7 +148,7 @@ List<TABLE_LIST>* get_disallowed_table_deps(MEM_ROOT *mem_root, { List<TABLE_LIST> *disallowed_tables; - if (!(disallowed_tables = new List<TABLE_LIST>)) + if (!(disallowed_tables = new (mem_root) List<TABLE_LIST>)) return NULL; int res= get_disallowed_table_deps_for_list(mem_root, table_func,

1 0