- commits - lists.mariadb.org

[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

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

07 Jul '21

revision-id: e16f905fd7cb67e2ad82dd46c0a9895e70d86403 (10.5.8-5ES-6-ge16f905) parent(s): e5d45a713d7369ad1091948d7b8fe3c854f94b6f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-07 15:19:04 +0300 message: Update Xpand Plugin vesion to 6.0.1 --- ha_xpand.cc | 4 ++-- mysql-test/xpand/xpand_plugin.result | 4 ++-- 2 files changed, 4 insertions(+), 4 deletions(-) diff --git a/ha_xpand.cc b/ha_xpand.cc index e5f088a..354e694 100644 --- a/ha_xpand.cc +++ b/ha_xpand.cc @@ -2315,10 +2315,10 @@ 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 */ - "5.3.16", /* String Version */ + "6.0.1", /* String Version */ MariaDB_PLUGIN_MATURITY_STABLE /* Maturity Level */ } maria_declare_plugin_end; diff --git a/mysql-test/xpand/xpand_plugin.result b/mysql-test/xpand/xpand_plugin.result index 9862c4c..018d7ff 100644 --- a/mysql-test/xpand/xpand_plugin.result +++ b/mysql-test/xpand/xpand_plugin.result @@ -6,8 +6,8 @@ from information_schema.plugins WHERE PLUGIN_NAME='XPAND'; PLUGIN_NAME XPAND PLUGIN_MATURITY Stable -PLUGIN_VERSION 5.3 -PLUGIN_AUTH_VERSION 5.3.16 +PLUGIN_VERSION 6.0 +PLUGIN_AUTH_VERSION 6.0.1 # # Xpand supports a replication mode where both master and slave MariaDB # are connected to the same Clustrix instance. But it must NOT be

1 0

[Commits] 2f2bf3d: MDEV-26095 Infinite recursion when processing embedded recursive CTE
by IgorBabaev 06 Jul '21

06 Jul '21

revision-id: 2f2bf3df7c4318a1e94a331b304c3c18fcafd7bb (mariadb-10.2.31-1040-g2f2bf3d) parent(s): 99f700a820ef90b5b36ef765fb1532145ab3e907 author: Igor Babaev committer: Igor Babaev timestamp: 2021-07-06 14:38:32 -0700 message: MDEV-26095 Infinite recursion when processing embedded recursive CTE with missing RECURSIVE If a table reference r used inthe specification of a CTE whose definition is contained in the WITH clause where RECURSIVE is omitted then this table reference cannot be considered as a recursive table reference even if it is used in the query that specifies CTE whose name is r. It can be considered only as a reference to an embedding CTE or to a temporary table or to a base table/view. If there is no such object with name r then an error message must be reported. This patch fixes the code that actually in some cases resolved r as a reference to the CTE whose specification contained r if its name was r in spite of the fact that r was not considered as a recursive CTE. This happened in the cases when the definition of r was used in the specification of another CTE. Such wrong name resolution for r led to an infinite recursive invocations of the parser that ultimately crashed the server. This bug is a result of the fix for mdev-13780 that was not quite correct. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/r/cte_nonrecursive.result | 25 +++++++++++++++++++++++++ mysql-test/t/cte_nonrecursive.test | 23 +++++++++++++++++++++++ sql/sql_cte.cc | 4 +++- 3 files changed, 51 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index c1d7fd0..5cc5a25 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -2019,4 +2019,29 @@ drop procedure sp1; drop procedure sp2; drop procedure sp3; drop table t1; +# +# MDEV-26095: missing RECURSIVE for the recursive definition of CTE +# embedded into another CTE definition +# +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; +a +5 +7 +6 +8 +9 +10 +with cte_e as ( +with 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; +ERROR 42S02: Table 'test.cte_r' doesn't exist +drop table t1; # End of 10.2 tests diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index cbe4f8b..68dbc0c 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1492,4 +1492,27 @@ drop procedure sp3; drop table t1; +--echo # +--echo # MDEV-26095: missing RECURSIVE for the recursive definition of CTE +--echo # embedded into another CTE definition +--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; + +--ERROR ER_NO_SUCH_TABLE +with cte_e as ( + with 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; + +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 9dad33d..702db8f 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1256,6 +1256,7 @@ bool With_element::is_anchor(st_select_lex *sel) With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) { With_element *found= NULL; + With_clause *containing_with_clause= NULL; st_select_lex_unit *master_unit; st_select_lex *outer_sl; for (st_select_lex *sl= this; sl; sl= outer_sl) @@ -1268,6 +1269,7 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) */ With_clause *attached_with_clause= sl->get_with_clause(); if (attached_with_clause && + attached_with_clause != containing_with_clause && (found= attached_with_clause->find_table_def(table, NULL))) break; master_unit= sl->master_unit(); @@ -1275,7 +1277,7 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) With_element *with_elem= sl->get_with_element(); if (with_elem) { - With_clause *containing_with_clause= with_elem->get_owner(); + containing_with_clause= with_elem->get_owner(); With_element *barrier= containing_with_clause->with_recursive ? NULL : with_elem; if ((found= containing_with_clause->find_table_def(table, barrier)))

1 0

[Commits] c7443a0911a: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
by psergey 01 Jul '21

01 Jul '21

revision-id: c7443a0911a98dccfc9c5bda4c2f4d9052516d8f (mariadb-10.4.20-22-gc7443a0911a) parent(s): eebe2090c848b5cedc5235473d80dbd2c25d2943 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-01 01:08:28 +0300 message: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP Post-merge fix in 10.4: add a testcase for pushdown into IN subquery --- mysql-test/main/derived_cond_pushdown.result | 57 +++++++++++++++++++++++++++- mysql-test/main/derived_cond_pushdown.test | 19 +++++++++- 2 files changed, 74 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 76918d975cc..01a863ccb8b 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -10814,9 +10814,64 @@ EXPLAIN } } } +# Extra test for 10.4+: Check that this works for pushdown into IN +# subqueries: +create table t4 (a int, b int, c decimal); +insert into t4 select a,a,a from t1; +# The subquery must be materialized and must have +# "attached_condition": "t1.a + 1 > 10", +# "having_condition": "`f1(a)` > 1 and `sum(b)` > 123", +explain format=json +select * +from t4 +where +(a,b,c) in (select a, f1(a), sum(b) from t1 group by a, f1(a)) +and +(a+1) > 10 AND b > 1 and c>123; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t4", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t4.a + 1 > 10 and t4.b > 1 and t4.c > 123 and t4.a is not null and t4.b is not null and t4.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "23", + "used_key_parts": ["a", "f1(a)", "sum(b)"], + "ref": ["test.t4.a", "test.t4.b", "test.t4.c"], + "rows": 1, + "filtered": 100, + "attached_condition": "t4.c = `<subquery2>`.`sum(b)`", + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`f1(a)` > 1 and `sum(b)` > 123", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t1.a + 1 > 10" + } + } + } + } + } + } +} drop view v2; drop function f1; -drop table t1; +drop table t1, t4; # End of 10.2 tests # # MDEV-14579: pushdown conditions into materialized views/derived tables diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index bc5034621b4..9544ad34572 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2305,9 +2305,26 @@ select a, f1(a), sum(b) from t1 group by a, f1(a); explain format=json select * from v2 where (s+1) > 10 AND a > 1 and a2>123; +--echo # Extra test for 10.4+: Check that this works for pushdown into IN +--echo # subqueries: + +create table t4 (a int, b int, c decimal); +insert into t4 select a,a,a from t1; + +--echo # The subquery must be materialized and must have +--echo # "attached_condition": "t1.a + 1 > 10", +--echo # "having_condition": "`f1(a)` > 1 and `sum(b)` > 123", +explain format=json +select * +from t4 +where + (a,b,c) in (select a, f1(a), sum(b) from t1 group by a, f1(a)) + and + (a+1) > 10 AND b > 1 and c>123; + drop view v2; drop function f1; -drop table t1; +drop table t1, t4; --echo # End of 10.2 tests --echo #

1 0