- commits - lists.mariadb.org

[Commits] 7563d4c: MDEV-26135 Assertion failure when executing PS with a hanging recursive CTE
by IgorBabaev 17 Jul '21

17 Jul '21

revision-id: 7563d4c9270ebb789ca645266d34dc83fb6bd784 (mariadb-10.2.31-1059-g7563d4c) parent(s): c47e4aab62c65e1a1d431f9888ba1bc6b9841687 author: Igor Babaev committer: Igor Babaev timestamp: 2021-07-16 22:46:50 -0700 message: MDEV-26135 Assertion failure when executing PS with a hanging recursive CTE The bug affected execution of queries with With clauses containing so-called hanging recursive CTEs in PREPARE mode. A CTE is hanging if it's not used in the query. Preparation of a prepared statement from a query with a hanging CTE caused a leak in the server and execution of this prepared statement led to an assert failure of the server built in the debug mode. This happened because the units specifying recursive CTEs erroneously were not cleaned up if those CTEs were hanging. The patch enforces cleanup of hanging recursive CTEs in the same way as other hanging CTEs. Approved by dmitry.shulga(a)mariadb.com --- mysql-test/r/cte_recursive.result | 27 +++++++++++++++++++++++++++ mysql-test/t/cte_recursive.test | 21 +++++++++++++++++++++ sql/sql_union.cc | 6 ++++-- 3 files changed, 52 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 3e92652..a4d821e 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -4454,5 +4454,32 @@ deallocate prepare stmt; drop table folks; set big_tables=@save_big_tables; # +# MDEV-26135: execution of PS for query with hanging recursive CTE +# +create table t1 (a int); +insert into t1 values (5), (7); +create table t2 (b int); +insert into t2 values (3), (7), (1); +with recursive r as (select a from t1 union select a+1 from r where a < 10) +select * from t2; +b +3 +7 +1 +prepare stmt from "with recursive r as (select a from t1 union select a+1 from r where a < 10) +select * from t2"; +execute stmt; +b +3 +7 +1 +execute stmt; +b +3 +7 +1 +deallocate prepare stmt; +drop table t1,t2; +# # End of 10.2 tests # diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 849e76b..49f9c1f 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2820,5 +2820,26 @@ drop table folks; set big_tables=@save_big_tables; --echo # +--echo # MDEV-26135: execution of PS for query with hanging recursive CTE +--echo # + +create table t1 (a int); +insert into t1 values (5), (7); +create table t2 (b int); +insert into t2 values (3), (7), (1); + +let $q= +with recursive r as (select a from t1 union select a+1 from r where a < 10) +select * from t2; + +eval $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop table t1,t2; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7baedfb..e5648e6 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1382,7 +1382,8 @@ bool st_select_lex_unit::cleanup() { DBUG_RETURN(FALSE); } - if (with_element && with_element->is_recursive && union_result) + if (with_element && with_element->is_recursive && union_result && + with_element->rec_outer_references) { select_union_recursive *result= with_element->rec_result; if (++result->cleanup_count == with_element->rec_outer_references) @@ -1584,7 +1585,8 @@ bool st_select_lex::cleanup() for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) { - if (lex_unit->with_element && lex_unit->with_element->is_recursive) + if (lex_unit->with_element && lex_unit->with_element->is_recursive && + lex_unit->with_element->rec_outer_references) continue; error= (bool) ((uint) error | (uint) lex_unit->cleanup()); }

1 0

[Commits] 7929388: MDEV-26135 Assertion failure when executing PS with a hanging recursive CTE
by IgorBabaev 17 Jul '21

17 Jul '21

revision-id: 7929388ed155a65f0693a98590b943bed0421aec (mariadb-10.2.31-1059-g7929388) parent(s): c47e4aab62c65e1a1d431f9888ba1bc6b9841687 author: Igor Babaev committer: Igor Babaev timestamp: 2021-07-16 22:44:35 -0700 message: MDEV-26135 Assertion failure when executing PS with a hanging recursive CTE The bug affected execution of queries with With clauses containing so-called hanging recursive CTEs in PREPARE mode. A CTE is hanging if it's not used in the query. Preparation of a prepared statement from a query with a hanging CTE caused a leak in the server and execution of this prepared statement led to an assert failure of the server built in the debug mode. This happened because the units specifying recursive CTEs erroneously were not cleaned up if those CTEs were hanging. The patch enforces cleanup of hanging recursive CTEs in the same way as other hanging CTEs. Approved by dmitry.shulga(a)mariadb.com Approved by dmitry.shulga(a)mariadb.com pre_setup_aliases Approved by dmitry.shulga(a)mariadb.com Approved by dmitry.shulga(a)mariadb.com Approved by dmitry.shulga(a)mariadb.com Approved by dmitry.shulga(a)mariadb.com Approved by dmitry.shulga(a)mariadb.com Approved by dmitry.shulga(a)mariadb.com --- mysql-test/r/cte_recursive.result | 27 +++++++++++++++++++++++++++ mysql-test/t/cte_recursive.test | 21 +++++++++++++++++++++ sql/sql_union.cc | 6 ++++-- 3 files changed, 52 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 3e92652..a4d821e 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -4454,5 +4454,32 @@ deallocate prepare stmt; drop table folks; set big_tables=@save_big_tables; # +# MDEV-26135: execution of PS for query with hanging recursive CTE +# +create table t1 (a int); +insert into t1 values (5), (7); +create table t2 (b int); +insert into t2 values (3), (7), (1); +with recursive r as (select a from t1 union select a+1 from r where a < 10) +select * from t2; +b +3 +7 +1 +prepare stmt from "with recursive r as (select a from t1 union select a+1 from r where a < 10) +select * from t2"; +execute stmt; +b +3 +7 +1 +execute stmt; +b +3 +7 +1 +deallocate prepare stmt; +drop table t1,t2; +# # End of 10.2 tests # diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 849e76b..49f9c1f 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2820,5 +2820,26 @@ drop table folks; set big_tables=@save_big_tables; --echo # +--echo # MDEV-26135: execution of PS for query with hanging recursive CTE +--echo # + +create table t1 (a int); +insert into t1 values (5), (7); +create table t2 (b int); +insert into t2 values (3), (7), (1); + +let $q= +with recursive r as (select a from t1 union select a+1 from r where a < 10) +select * from t2; + +eval $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop table t1,t2; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7baedfb..e5648e6 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1382,7 +1382,8 @@ bool st_select_lex_unit::cleanup() { DBUG_RETURN(FALSE); } - if (with_element && with_element->is_recursive && union_result) + if (with_element && with_element->is_recursive && union_result && + with_element->rec_outer_references) { select_union_recursive *result= with_element->rec_result; if (++result->cleanup_count == with_element->rec_outer_references) @@ -1584,7 +1585,8 @@ bool st_select_lex::cleanup() for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) { - if (lex_unit->with_element && lex_unit->with_element->is_recursive) + if (lex_unit->with_element && lex_unit->with_element->is_recursive && + lex_unit->with_element->rec_outer_references) continue; error= (bool) ((uint) error | (uint) lex_unit->cleanup()); }

1 0

[Commits] b326f3d672b: Update Xpand to: Change Xpand Version to 6.0.1, part 2
by psergey 13 Jul '21

13 Jul '21

revision-id: b326f3d672bc5a7775af83e97ababc6117640af2 (mariadb-10.5.2-1205-gb326f3d672b) parent(s): e9a2dca4d3073d77ca13f77dd3efa779a077152d author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-13 21:09:41 +0300 message: Update Xpand to: Change Xpand Version to 6.0.1, part 2 --- storage/xpand | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/storage/xpand b/storage/xpand index ea285dff95c..e578dbf7ed7 160000 --- a/storage/xpand +++ b/storage/xpand @@ -1 +1 @@ -Subproject commit ea285dff95c0c192223bea3a089d1e0a54228c1d +Subproject commit e578dbf7ed7d18c62ca18c64ea4c6bedbf6b0a11

1 0

[Commits] efb075c: MDEV-25565 Preliminary commit
by IgorBabaev 09 Jul '21

09 Jul '21

revision-id: efb075c752ad37402fb754a53523e07fd24e8188 (mariadb-10.2.31-1002-gefb075c) parent(s): 4352c77c5a3ac89acc5fd90a38f806d0ec500aa4 author: Igor Babaev committer: Igor Babaev timestamp: 2021-07-09 12:00:02 -0700 message: MDEV-25565 Preliminary commit Some new test cases are to be added after rebase. --- mysql-test/r/win.result | 111 ++++++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/win.test | 70 ++++++++++++++++++++++++++++++ sql/sql_union.cc | 26 ++++++++++++ sql/sql_window.cc | 12 ++++++ sql/sql_window.h | 5 ++- 5 files changed, 223 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 8a31dcc..432c12b 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3911,5 +3911,116 @@ sum(i) over () IN ( SELECT 1 FROM t1 a) 0 DROP TABLE t1; # +# MDEV-25565: 2-nd call of SP with SELECT joining a view / derived table +and returning the result of calculation of 2 window +functions that use the same window specification +# +create table t1 (a int); +insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5); +create table t2 (b int); +insert into t2 values (1), (4), (9), (8), (2), (9), (7), (1); +create view v2 as select a from t1 group by a; +create view v1 as select * from v2; +create procedure sp1() select v1.a, +sum(v1.a) over (partition by v1.a order by v1.a) as k, +avg(v1.a) over (partition by v1.a order by v1.a) as m +from v1, t2 where t2.b = v1.a; +call sp1(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +call sp1(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +prepare stmt from "select v1.a, +sum(v1.a) over (partition by v1.a order by v1.a) as k, +avg(v1.a) over (partition by v1.a order by v1.a) as m +from v1, t2 where t2.b = v1.a"; +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +deallocate prepare stmt; +create procedure sp2() select * from +( select dt1.a, +sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, +avg(dt1.a) over (partition by dt1.a order by dt1.a) as m +from (select * from v2) as dt1, t2 where t2.b=dt1.a ) as dt; +call sp2(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +call sp2(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +prepare stmt from "select * from +( select dt1.a, +sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, +avg(dt1.a) over (partition by dt1.a order by dt1.a) as m +from (select * from v2) as dt1, t2 where t2.b=dt1.a ) as dt"; +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +deallocate prepare stmt; +create procedure sp3() select * from +( select dt1.a, +sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, +avg(dt1.a) over (partition by dt1.a order by dt1.a) as m +from ( select * from (select * from t1 group by a) as dt2 ) as dt1, +t2 +where t2.b=dt1.a ) as dt; +call sp3(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +call sp3(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +prepare stmt from "select * from +( select dt1.a, +sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, +avg(dt1.a) over (partition by dt1.a order by dt1.a) as m +from ( select * from (select * from t1 group by a) as dt2 ) as dt1, +t2 +where t2.b=dt1.a ) as dt"; +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +deallocate prepare stmt; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop view v1,v2; +drop table t1,t2; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index c07a81f..e42c8c9 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2557,5 +2557,75 @@ SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1; DROP TABLE t1; --echo # +--echo # MDEV-25565: 2-nd call of SP with SELECT joining a view / derived table +--echo and returning the result of calculation of 2 window +--echo functions that use the same window specification +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5); +create table t2 (b int); +insert into t2 values (1), (4), (9), (8), (2), (9), (7), (1); + +create view v2 as select a from t1 group by a; +create view v1 as select * from v2; + +let $q1= +select v1.a, + sum(v1.a) over (partition by v1.a order by v1.a) as k, + avg(v1.a) over (partition by v1.a order by v1.a) as m +from v1, t2 where t2.b = v1.a; + +eval create procedure sp1() $q1; +call sp1(); +call sp1(); + +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + + +let $q2= +select * from + ( select dt1.a, + sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, + avg(dt1.a) over (partition by dt1.a order by dt1.a) as m + from (select * from v2) as dt1, t2 where t2.b=dt1.a ) as dt; + +eval create procedure sp2() $q2; +call sp2(); +call sp2(); + +eval prepare stmt from "$q2"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q3= +select * from + ( select dt1.a, + sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, + avg(dt1.a) over (partition by dt1.a order by dt1.a) as m + from ( select * from (select * from t1 group by a) as dt2 ) as dt1, + t2 + where t2.b=dt1.a ) as dt; + +eval create procedure sp3() $q3; +call sp3(); +call sp3(); + +eval prepare stmt from "$q3"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop view v1,v2; +drop table t1,t2; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7baedfb..f3c90b8 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -30,6 +30,7 @@ #include "filesort.h" // filesort_free_buffers #include "sql_view.h" #include "sql_cte.h" +#include "item_windowfunc.h" bool mysql_union(THD *thd, LEX *lex, select_result *result, SELECT_LEX_UNIT *unit, ulong setup_tables_done_option) @@ -1550,6 +1551,29 @@ static void cleanup_order(ORDER *order) } +static void cleanup_window_funcs(List<Item_window_func> &win_funcs) +{ + List_iterator_fast<Item_window_func> it(win_funcs); + Item_window_func *win_func; + while ((win_func= it++)) + { + Window_spec *win_spec= win_func->window_spec; + if (!win_spec) + continue; + if (win_spec->save_partition_list) + { + win_spec->partition_list= win_spec->save_partition_list; + win_spec->save_partition_list= NULL; + } + if (win_spec->save_order_list) + { + win_spec->order_list= win_spec->save_order_list; + win_spec->save_order_list= NULL; + } + } +} + + bool st_select_lex::cleanup() { bool error= FALSE; @@ -1558,6 +1582,8 @@ bool st_select_lex::cleanup() cleanup_order(order_list.first); cleanup_order(group_list.first); + cleanup_window_funcs(window_funcs); + if (join) { List_iterator<TABLE_LIST> ti(leaf_tables); diff --git a/sql/sql_window.cc b/sql/sql_window.cc index 612c6e6..3ef751b 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -479,9 +479,15 @@ int compare_window_funcs_by_window_specs(Item_window_func *win_func1, Let's use only one of the lists. */ if (!win_spec1->name() && win_spec2->name()) + { + win_spec1->save_partition_list= win_spec1->partition_list; win_spec1->partition_list= win_spec2->partition_list; + } else + { + win_spec2->save_partition_list= win_spec2->partition_list; win_spec2->partition_list= win_spec1->partition_list; + } cmp= compare_order_lists(win_spec1->order_list, win_spec2->order_list); @@ -494,9 +500,15 @@ int compare_window_funcs_by_window_specs(Item_window_func *win_func1, Let's use only one of the lists. */ if (!win_spec1->name() && win_spec2->name()) + { + win_spec1->save_order_list= win_spec2->order_list; win_spec1->order_list= win_spec2->order_list; + } else + { + win_spec1->save_order_list= win_spec2->order_list; win_spec2->order_list= win_spec1->order_list; + } cmp= compare_window_frames(win_spec1->window_frame, win_spec2->window_frame); diff --git a/sql/sql_window.h b/sql/sql_window.h index e0c1563..417d0bc 100644 --- a/sql/sql_window.h +++ b/sql/sql_window.h @@ -99,8 +99,10 @@ class Window_spec : public Sql_alloc LEX_STRING *window_ref; SQL_I_List<ORDER> *partition_list; + SQL_I_List<ORDER> *save_partition_list; SQL_I_List<ORDER> *order_list; + SQL_I_List<ORDER> *save_order_list; Window_frame *window_frame; @@ -111,7 +113,8 @@ class Window_spec : public Sql_alloc SQL_I_List<ORDER> *ord_list, Window_frame *win_frame) : window_names_are_checked(false), window_ref(win_ref), - partition_list(part_list), order_list(ord_list), + partition_list(part_list), save_partition_list(NULL), + order_list(ord_list), save_order_list(NULL), window_frame(win_frame), referenced_win_spec(NULL) {} virtual char *name() { return NULL; }

1 0

[Commits] 78735dc: MDEV-26108 Crash with query referencing twice CTE that uses embedded recursive CTE
by IgorBabaev 09 Jul '21

09 Jul '21

revision-id: 78735dcaf757cd71c8f0ff3d21071b0f89018150 (mariadb-10.4.20-31-g78735dc) parent(s): e56fe393104960eb62043c3777ce7d21de9362f4 author: Igor Babaev committer: Igor Babaev timestamp: 2021-07-08 17:47:17 -0700 message: MDEV-26108 Crash with query referencing twice CTE that uses embedded recursive CTE This bug could affect queries that had at least two references to a CTE that used an embedded recursive CTE. Starting from version 10.4 some code in With_element::clone_parsed_spec() that assumed a certain order of selects after parsing the specification of a CTE became not valid anymore. It could lead to global select lists where some selects were missing. If a missing CTE happened to belong to the recursive part of a recursive CTE some recursive table references were not set as references to materialized derived tables and this caused a crash of the server. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/cte_nonrecursive.result | 2 +- mysql-test/main/cte_recursive.result | 19 +++++++++++++++++++ mysql-test/main/cte_recursive.test | 17 +++++++++++++++++ sql/sql_cte.cc | 10 +++++++--- 4 files changed, 44 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 040afdf..4cd466a 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1126,7 +1126,7 @@ NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT <union11,12> ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT <union1,6> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with cte_e as (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union select `cte_e2`.`a` AS `a` from `cte_e` `cte_e2` +Note 1003 with cte_e as (with cte_o as (with cte_i as (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)/* select#3 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)/* select#4 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union /* select#5 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)/* select#1 */ select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union /* select#6 */ select `cte_e2`.`a` AS `a` from `cte_e` `cte_e2` drop table t1; # # MDEV-13753: embedded CTE in a VIEW created in prepared statement diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 6f30de3..74b450f 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -4791,3 +4791,22 @@ a NULL DROP TABLE t1; # End of 10.3 tests +# +# MDEV-26108: Recursive CTE embedded into another CTE which is used twice +# +create table t1 (a int); +insert into t1 values (5), (7); +with cte_e as ( +with recursive cte_r as ( +select a from t1 union select a+1 as a from cte_r r where a < 10 +) select * from cte_r +) select * from cte_e s1, cte_e s2 where s1.a=s2.a; +a a +5 5 +7 7 +6 6 +8 8 +9 9 +10 10 +drop table t1; +# End of 10.4 tests diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index c3537e5..3b140b3 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -3087,3 +3087,20 @@ SELECT * FROM cte; DROP TABLE t1; --echo # End of 10.3 tests + +--echo # +--echo # MDEV-26108: Recursive CTE embedded into another CTE which is used twice +--echo # + +create table t1 (a int); +insert into t1 values (5), (7); + +with cte_e as ( + with recursive cte_r as ( + select a from t1 union select a+1 as a from cte_r r where a < 10 + ) select * from cte_r +) select * from cte_e s1, cte_e s2 where s1.a=s2.a; + +drop table t1; + +--echo # End of 10.4 tests diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index dfcb4e1..c5dcc15 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1038,6 +1038,7 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, bool parse_status= false; st_select_lex *with_select; + st_select_lex *last_clone_select; char save_end= unparsed_spec.str[unparsed_spec.length]; ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= '\0'; @@ -1124,11 +1125,14 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, lex->unit.include_down(with_table->select_lex); lex->unit.set_slave(with_select); lex->unit.cloned_from= spec; + last_clone_select= lex->all_selects_list; + while (last_clone_select->next_select_in_list()) + last_clone_select= last_clone_select->next_select_in_list(); old_lex->all_selects_list= (st_select_lex*) (lex->all_selects_list-> - insert_chain_before( - (st_select_lex_node **) &(old_lex->all_selects_list), - with_select)); + insert_chain_before( + (st_select_lex_node **) &(old_lex->all_selects_list), + last_clone_select)); /* Now all references to the CTE defined outside of the cloned specification

1 0

[Commits] 35294053b28: MDEV-26106: [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record
by psergey 07 Jul '21

07 Jul '21

revision-id: 35294053b281294bb49a6217da07b93eb6c2595b (mariadb-10.5.11-33-g35294053b28) parent(s): c262ccac027e71f22fc1329cf295a6bf687e4684 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-07 18:54:00 +0300 message: MDEV-26106: [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record Port the following patch from MySQL: commit 1b2e8ea269c80cb93cc79d8be934c40b1c58e947 Author: Kailasnath Nagarkar <kailasnath.nagarkar(a)oracle.com> Date: Fri Nov 30 16:43:13 2018 +0530 Bug #20939184: INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE RECORD Issue: ------ Consdier tables t1 and t2 such that t1 has multiple rows and join condition for t1 left join t2 results in only single row from t2. In this case, access to table t2 is const since there is a single row that qualifies the join condition. However, while executing the query, attempt is made to unlock t2's row multiple times. The current algorithm to fetch rows approximates to: 1) Retrieve the row for t1. 2) Retrieve the row for t2. 3) Apply the join conditions. a) If condition evaluates to true: Project the row to the result. b) If condition evaluates to false: i) If t2's qep_tab->not_null_complement is true, unlock t2's row. ii) Null-complement the row by calling "evaluate_null_complemented_join_record()". In this function qep_tab->not_null_complement is set to false. The t2's only one row, that qualifies join condition, is unlocked in Step i) when t1's row is evaluated to false. When t1's next row is also evaluated to false, another attempt is made to unlock t2's already unlocked row. This results in following error being logged in error.log: "[ERROR] InnoDB: Unlock row could not find a 3 mode lock on the record. Current statement: select * from t1 left join t2 ......" Solution: --------- When a table's access method is "const", set record unlock method for this table to do no operation. --- mysql-test/main/join_outer_innodb.result | 19 +++++++++++++++++++ mysql-test/main/join_outer_innodb.test | 17 +++++++++++++++++ sql/sql_select.cc | 31 +++++++++++++++++++++++++++---- 3 files changed, 63 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index 0b34a399d77..09a37a29702 100644 --- a/mysql-test/main/join_outer_innodb.result +++ b/mysql-test/main/join_outer_innodb.result @@ -496,3 +496,22 @@ natural right outer join t3; drop table t1,t2,t3; set optimizer_prune_level=@mdev4270_opl; set optimizer_search_depth=@mdev4270_osd; +# +# Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE +# RECORD +# +CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1,c2) ) engine=innodb; +CREATE TABLE t2 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1), KEY (c2)) engine=innodb; +INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5); +INSERT INTO t2 SELECT * FROM t1; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +START TRANSACTION; +SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE; +c1 c2 c3 c1 c2 c3 +1 2 3 1 2 3 +2 3 4 NULL NULL NULL +3 4 5 NULL NULL NULL +UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10; +COMMIT; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +DROP TABLE t1,t2; diff --git a/mysql-test/main/join_outer_innodb.test b/mysql-test/main/join_outer_innodb.test index c26cd62fbc7..6b332f3d155 100644 --- a/mysql-test/main/join_outer_innodb.test +++ b/mysql-test/main/join_outer_innodb.test @@ -374,3 +374,20 @@ drop table t1,t2,t3; set optimizer_prune_level=@mdev4270_opl; set optimizer_search_depth=@mdev4270_osd; +--echo # +--echo # Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE +--echo # RECORD +--echo # +CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1,c2) ) engine=innodb; +CREATE TABLE t2 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1), KEY (c2)) engine=innodb; +INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5); +INSERT INTO t2 SELECT * FROM t1; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +START TRANSACTION; +#unlocks rows in table t2 where c1 = 1 +SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE; +UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10; +COMMIT; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +DROP TABLE t1,t2; + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 8800fbf5b6d..ab54bd3b94d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -189,6 +189,7 @@ static int join_read_system(JOIN_TAB *tab); static int join_read_const(JOIN_TAB *tab); static int join_read_key(JOIN_TAB *tab); static void join_read_key_unlock_row(st_join_table *tab); +static void join_const_unlock_row(JOIN_TAB *tab); static int join_read_always_key(JOIN_TAB *tab); static int join_read_last_key(JOIN_TAB *tab); static int join_no_more_records(READ_RECORD *info); @@ -10968,8 +10969,12 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, else j->type=JT_EQ_REF; - j->read_record.unlock_row= (j->type == JT_EQ_REF)? - join_read_key_unlock_row : rr_unlock_row; + if (j->type == JT_EQ_REF) + j->read_record.unlock_row= join_read_key_unlock_row; + else if (j->type == JT_CONST) + j->read_record.unlock_row= join_const_unlock_row; + else + j->read_record.unlock_row= rr_unlock_row; DBUG_RETURN(0); } @@ -13232,6 +13237,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) /* Only happens with outer joins */ tab->read_first_record= tab->type == JT_SYSTEM ? join_read_system : join_read_const; + tab->read_record.unlock_row= join_const_unlock_row; if (!(table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) && (!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); @@ -21532,6 +21538,19 @@ join_read_key_unlock_row(st_join_table *tab) tab->ref.use_count--; } +/** + Rows from const tables are read once but potentially used + multiple times during execution of a query. + Ensure such rows are never unlocked during query execution. +*/ + +void +join_const_unlock_row(JOIN_TAB *tab) +{ + DBUG_ASSERT(tab->type == JT_CONST); +} + + /* ref access method implementation: "read_first" function @@ -23938,8 +23957,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, else if (select && select->quick) select->quick->need_sorted_output(); - tab->read_record.unlock_row= (tab->type == JT_EQ_REF) ? - join_read_key_unlock_row : rr_unlock_row; + if (tab->type == JT_EQ_REF) + tab->read_record.unlock_row= join_read_key_unlock_row; + else if (tab->type == JT_CONST) + tab->read_record.unlock_row= join_const_unlock_row; + else + tab->read_record.unlock_row= rr_unlock_row; } // QEP has been modified

1 0

[Commits] 8de6c5b2190: as MDEV-26106: [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record
by psergey 07 Jul '21

07 Jul '21

revision-id: 8de6c5b21906a731c50a880bd5a87d5bedf9938a (mariadb-10.5.11-33-g8de6c5b2190) parent(s): c262ccac027e71f22fc1329cf295a6bf687e4684 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-07 18:51:13 +0300 message: as MDEV-26106: [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record Port the following patch from MySQL: commit 1b2e8ea269c80cb93cc79d8be934c40b1c58e947 Author: Kailasnath Nagarkar <kailasnath.nagarkar(a)oracle.com> Date: Fri Nov 30 16:43:13 2018 +0530 Bug #20939184: INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE RECORD Issue: ------ Consdier tables t1 and t2 such that t1 has multiple rows and join condition for t1 left join t2 results in only single row from t2. In this case, access to table t2 is const since there is a single row that qualifies the join condition. However, while executing the query, attempt is made to unlock t2's row multiple times. The current algorithm to fetch rows approximates to: 1) Retrieve the row for t1. 2) Retrieve the row for t2. 3) Apply the join conditions. a) If condition evaluates to true: Project the row to the result. b) If condition evaluates to false: i) If t2's qep_tab->not_null_complement is true, unlock t2's row. ii) Null-complement the row by calling "evaluate_null_complemented_join_record()". In this function qep_tab->not_null_complement is set to false. The t2's only one row, that qualifies join condition, is unlocked in Step i) when t1's row is evaluated to false. When t1's next row is also evaluated to false, another attempt is made to unlock t2's already unlocked row. This results in following error being logged in error.log: "[ERROR] InnoDB: Unlock row could not find a 3 mode lock on the record. Current statement: select * from t1 left join t2 ......" Solution: --------- When a table's access method is "const", set record unlock method for this table to do no operation. --- mysql-test/main/join_outer_innodb.result | 19 +++++++++++++++++++ mysql-test/main/join_outer_innodb.test | 17 +++++++++++++++++ sql/sql_select.cc | 31 +++++++++++++++++++++++++++---- 3 files changed, 63 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index 0b34a399d77..09a37a29702 100644 --- a/mysql-test/main/join_outer_innodb.result +++ b/mysql-test/main/join_outer_innodb.result @@ -496,3 +496,22 @@ natural right outer join t3; drop table t1,t2,t3; set optimizer_prune_level=@mdev4270_opl; set optimizer_search_depth=@mdev4270_osd; +# +# Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE +# RECORD +# +CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1,c2) ) engine=innodb; +CREATE TABLE t2 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1), KEY (c2)) engine=innodb; +INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5); +INSERT INTO t2 SELECT * FROM t1; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +START TRANSACTION; +SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE; +c1 c2 c3 c1 c2 c3 +1 2 3 1 2 3 +2 3 4 NULL NULL NULL +3 4 5 NULL NULL NULL +UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10; +COMMIT; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +DROP TABLE t1,t2; diff --git a/mysql-test/main/join_outer_innodb.test b/mysql-test/main/join_outer_innodb.test index c26cd62fbc7..6b332f3d155 100644 --- a/mysql-test/main/join_outer_innodb.test +++ b/mysql-test/main/join_outer_innodb.test @@ -374,3 +374,20 @@ drop table t1,t2,t3; set optimizer_prune_level=@mdev4270_opl; set optimizer_search_depth=@mdev4270_osd; +--echo # +--echo # Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE +--echo # RECORD +--echo # +CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1,c2) ) engine=innodb; +CREATE TABLE t2 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1), KEY (c2)) engine=innodb; +INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5); +INSERT INTO t2 SELECT * FROM t1; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +START TRANSACTION; +#unlocks rows in table t2 where c1 = 1 +SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE; +UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10; +COMMIT; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +DROP TABLE t1,t2; + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 8800fbf5b6d..ab54bd3b94d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -189,6 +189,7 @@ static int join_read_system(JOIN_TAB *tab); static int join_read_const(JOIN_TAB *tab); static int join_read_key(JOIN_TAB *tab); static void join_read_key_unlock_row(st_join_table *tab); +static void join_const_unlock_row(JOIN_TAB *tab); static int join_read_always_key(JOIN_TAB *tab); static int join_read_last_key(JOIN_TAB *tab); static int join_no_more_records(READ_RECORD *info); @@ -10968,8 +10969,12 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, else j->type=JT_EQ_REF; - j->read_record.unlock_row= (j->type == JT_EQ_REF)? - join_read_key_unlock_row : rr_unlock_row; + if (j->type == JT_EQ_REF) + j->read_record.unlock_row= join_read_key_unlock_row; + else if (j->type == JT_CONST) + j->read_record.unlock_row= join_const_unlock_row; + else + j->read_record.unlock_row= rr_unlock_row; DBUG_RETURN(0); } @@ -13232,6 +13237,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) /* Only happens with outer joins */ tab->read_first_record= tab->type == JT_SYSTEM ? join_read_system : join_read_const; + tab->read_record.unlock_row= join_const_unlock_row; if (!(table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) && (!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); @@ -21532,6 +21538,19 @@ join_read_key_unlock_row(st_join_table *tab) tab->ref.use_count--; } +/** + Rows from const tables are read once but potentially used + multiple times during execution of a query. + Ensure such rows are never unlocked during query execution. +*/ + +void +join_const_unlock_row(JOIN_TAB *tab) +{ + DBUG_ASSERT(tab->type == JT_CONST); +} + + /* ref access method implementation: "read_first" function @@ -23938,8 +23957,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, else if (select && select->quick) select->quick->need_sorted_output(); - tab->read_record.unlock_row= (tab->type == JT_EQ_REF) ? - join_read_key_unlock_row : rr_unlock_row; + if (tab->type == JT_EQ_REF) + tab->read_record.unlock_row= join_read_key_unlock_row; + else if (tab->type == JT_CONST) + tab->read_record.unlock_row= join_const_unlock_row; + else + tab->read_record.unlock_row= rr_unlock_row; } // QEP has been modified

1 0

[Commits] a2afe9cb1ed: Update Xpand to: Change Xpand Version to 6.0.1, part 2
by psergey 07 Jul '21

07 Jul '21

revision-id: a2afe9cb1ed23ca00556858583f476220297fa33 (mariadb-10.5.2-1047-ga2afe9cb1ed) parent(s): be9ab9dcff48462d053769927e21d60eb3e32b76 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-07 18:30:20 +0300 message: Update Xpand to: Change Xpand Version to 6.0.1, part 2 --- storage/xpand | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/storage/xpand b/storage/xpand index ea285dff95c..e578dbf7ed7 160000 --- a/storage/xpand +++ b/storage/xpand @@ -1 +1 @@ -Subproject commit ea285dff95c0c192223bea3a089d1e0a54228c1d +Subproject commit e578dbf7ed7d18c62ca18c64ea4c6bedbf6b0a11

1 0

[Commits] e578dbf: Change Xpand Version to 6.0.1, part 2
by psergey 07 Jul '21

07 Jul '21

revision-id: e578dbf7ed7d18c62ca18c64ea4c6bedbf6b0a11 (10.5.8-5ES-8-ge578dbf) parent(s): ea285dff95c0c192223bea3a089d1e0a54228c1d author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-07 18:28:32 +0300 message: Change Xpand Version to 6.0.1, part 2 --- ha_xpand.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/ha_xpand.cc b/ha_xpand.cc index 505d74f..0f91022 100644 --- a/ha_xpand.cc +++ b/ha_xpand.cc @@ -2316,7 +2316,7 @@ maria_declare_plugin(xpand) { PLUGIN_LICENSE_GPL, /* Plugin Licence */ xpand_init, /* Plugin Entry Point */ xpand_deinit, /* Plugin Deinitializer */ - 0x0503, /* Hex Version Number (9.3) */ + 0x0600, /* Hex Version Number (9.3) */ xpand_status_vars, /* Status Variables */ xpand_system_variables, /* System Variables */ "6.0.1", /* String Version */

1 0

[Commits] e405ba6e2ef: Update Xpand Plugin to 6.0.1
by psergey 07 Jul '21

07 Jul '21

revision-id: e405ba6e2ef4b2de8063705fd9caa133e03aab1a (mariadb-10.5.4-862-ge405ba6e2ef) parent(s): 0b743db642eaccdb56b607f3a68c6a4926ce6228 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-07 15:22:55 +0300 message: Update Xpand Plugin to 6.0.1 --- storage/xpand | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/storage/xpand b/storage/xpand index e5d45a713d7..e16f905fd7c 160000 --- a/storage/xpand +++ b/storage/xpand @@ -1 +1 @@ -Subproject commit e5d45a713d7369ad1091948d7b8fe3c854f94b6f +Subproject commit e16f905fd7cb67e2ad82dd46c0a9895e70d86403

1 0