- commits - lists.mariadb.org

[Commits] 36791ad69aa: Fix a typo
by Sergei Petrunia 27 Jan '21

27 Jan '21

revision-id: 36791ad69aa1d970c3621a113affa7f50fade0d0 (mariadb-10.4.11-423-g36791ad69aa) parent(s): 5beb962ec229d13e2bab2c18427a915ae92c7209 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-27 15:09:13 +0300 message: Fix a typo --- sql/opt_range.cc | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/sql/opt_range.cc b/sql/opt_range.cc index f5e757c5c81..e23da39b796 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -10968,8 +10968,8 @@ SEL_ARG::tree_delete(SEL_ARG *key) uint new_weight= root->weight - (1 + (key->next_key_part? key->next_key_part->weight : 0)); - DBUG_ASSERT(root->weight > (1 + (key->next_key_part ? - key->next_key_part->weight : 0))); + DBUG_ASSERT(root->weight >= (1 + (key->next_key_part ? + key->next_key_part->weight : 0))); /* Unlink from list */ if (key->prev)

1 0

[Commits] 5beb962ec22: Address review input, part #1 (obvious fixes)
by Sergei Petrunia 27 Jan '21

27 Jan '21

revision-id: 5beb962ec229d13e2bab2c18427a915ae92c7209 (mariadb-10.4.11-422-g5beb962ec22) parent(s): 5cac06a04a468b421e39fff3daab7f93c28063f5 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-27 14:40:27 +0300 message: Address review input, part #1 (obvious fixes) --- sql/opt_range.cc | 40 ++++++++++++++++++++++++++++------------ 1 file changed, 28 insertions(+), 12 deletions(-) diff --git a/sql/opt_range.cc b/sql/opt_range.cc index df9d6ba68e0..f5e757c5c81 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -9769,8 +9769,8 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, if (!key1) return &null_element; // Impossible ranges key1->use_count++; - key1->max_part_no= MY_MAX(key2->max_part_no, key2->part+1); key1->weight= new_weight; + key1->max_part_no= MY_MAX(key2->max_part_no, key2->part+1); return key1; } @@ -9840,7 +9840,7 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) key1->next_key_part=key_and(param, key1->next_key_part, key2->next_key_part, clone_flag); - key1->weight = 1 + (key1->next_key_part? key1->next_key_part->weight : 0); + key1->weight= 1 + (key1->next_key_part? key1->next_key_part->weight : 0); if (key1->next_key_part && key1->next_key_part->type == SEL_ARG::IMPOSSIBLE) @@ -9893,6 +9893,11 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) if (!new_arg) return &null_element; // End of memory new_arg->next_key_part=next; + /* + We are going to add the 'new_arg' range to the new_tree. + the new_arg on its own has weight=1. + if it has non-null new_arg->next_key_part (=next), add its weight, too. + */ new_weight += 1 + (next? next->weight: 0); if (!new_tree) { @@ -9948,7 +9953,7 @@ uint SEL_ARG::verify_weight() { for (SEL_ARG *arg= first_arg; arg; arg= arg->next) { - computed_weight += 1; + computed_weight++; if (arg->next_key_part) computed_weight+= arg->next_key_part->verify_weight(); } @@ -9966,6 +9971,7 @@ uint SEL_ARG::verify_weight() { sql_print_error("SEL_ARG weight mismatch: computed %u have %u\n", computed_weight, weight); + DBUG_ASSERT(computed_weight == weight); // Fail an assertion } return computed_weight; } @@ -10715,7 +10721,7 @@ uint SEL_ARG::get_max_key_part() const if (cur->next_key_part) { uint mp= cur->next_key_part->get_max_key_part(); - max_part = MY_MAX(part, mp); + max_part= MY_MAX(part, mp); } } return max_part; @@ -10746,10 +10752,9 @@ void prune_sel_arg_graph(SEL_ARG *sel_arg, uint max_part) } else { - uint old_weight = cur->next_key_part->weight; + uint old_weight= cur->next_key_part->weight; prune_sel_arg_graph(cur->next_key_part, max_part); - old_weight -= cur->next_key_part->weight; - sel_arg->weight -= old_weight; + sel_arg->weight -= (old_weight - cur->next_key_part->weight); } } } @@ -10791,12 +10796,17 @@ SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, while (1) { - if (sel_arg->weight <= param->thd->variables.optimizer_max_sel_arg_weight) + if (likely(sel_arg->weight <= param->thd->variables. + optimizer_max_sel_arg_weight)) break; uint max_part= sel_arg->get_max_key_part(); if (max_part == sel_arg->part) { + /* + We don't return NULL right away as we want to have the information + about the changed tree in the optimizer trace. + */ sel_arg= NULL; break; } @@ -10950,11 +10960,17 @@ SEL_ARG::tree_delete(SEL_ARG *key) root=this; this->parent= 0; - // Compute the weight the tree will have after the element is removed - // We remove the element itself (weight=1) - // and the sub-graph connected to its next_key_part. + /* + Compute the weight the tree will have after the element is removed. + We remove the element itself (weight=1) + and the sub-graph connected to its next_key_part. + */ uint new_weight= root->weight - (1 + (key->next_key_part? key->next_key_part->weight : 0)); + + DBUG_ASSERT(root->weight > (1 + (key->next_key_part ? + key->next_key_part->weight : 0))); + /* Unlink from list */ if (key->prev) key->prev->next=key->next; @@ -11006,7 +11022,7 @@ SEL_ARG::tree_delete(SEL_ARG *key) test_rb_tree(root,root->parent); root->use_count=this->use_count; // Fix root counters - root->weight = new_weight; + root->weight= new_weight; root->elements=this->elements-1; root->maybe_flag=this->maybe_flag; DBUG_RETURN(root);

1 0

[Commits] 81787adb79a: Address review input, part #1 (obvious fixes)
by Sergei Petrunia 27 Jan '21

27 Jan '21

revision-id: 81787adb79a71e47a506a3c780019fdbba3aafe2 (mariadb-10.4.11-422-g81787adb79a) parent(s): 5cac06a04a468b421e39fff3daab7f93c28063f5 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-27 14:32:09 +0300 message: Address review input, part #1 (obvious fixes) --- sql/opt_range.cc | 40 ++++++++++++++++++++++++++++------------ 1 file changed, 28 insertions(+), 12 deletions(-) diff --git a/sql/opt_range.cc b/sql/opt_range.cc index df9d6ba68e0..48ac16e1de9 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -9769,8 +9769,8 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, if (!key1) return &null_element; // Impossible ranges key1->use_count++; - key1->max_part_no= MY_MAX(key2->max_part_no, key2->part+1); key1->weight= new_weight; + key1->max_part_no= MY_MAX(key2->max_part_no, key2->part+1); return key1; } @@ -9840,7 +9840,7 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) key1->next_key_part=key_and(param, key1->next_key_part, key2->next_key_part, clone_flag); - key1->weight = 1 + (key1->next_key_part? key1->next_key_part->weight : 0); + key1->weight= 1 + (key1->next_key_part? key1->next_key_part->weight : 0); if (key1->next_key_part && key1->next_key_part->type == SEL_ARG::IMPOSSIBLE) @@ -9893,6 +9893,11 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) if (!new_arg) return &null_element; // End of memory new_arg->next_key_part=next; + /* + We are going to add the 'new_arg' range to the new_tree. + the new_arg on its own has weight=1. + if it has non-null new_arg->next_key_part (=next), add its weight, too. + */ new_weight += 1 + (next? next->weight: 0); if (!new_tree) { @@ -9948,7 +9953,7 @@ uint SEL_ARG::verify_weight() { for (SEL_ARG *arg= first_arg; arg; arg= arg->next) { - computed_weight += 1; + computed_weight++; if (arg->next_key_part) computed_weight+= arg->next_key_part->verify_weight(); } @@ -9966,6 +9971,7 @@ uint SEL_ARG::verify_weight() { sql_print_error("SEL_ARG weight mismatch: computed %u have %u\n", computed_weight, weight); + DBUG_ASSERT(computed_weight == weight); // Fail an assertion } return computed_weight; } @@ -10715,7 +10721,7 @@ uint SEL_ARG::get_max_key_part() const if (cur->next_key_part) { uint mp= cur->next_key_part->get_max_key_part(); - max_part = MY_MAX(part, mp); + max_part= MY_MAX(part, mp); } } return max_part; @@ -10746,10 +10752,9 @@ void prune_sel_arg_graph(SEL_ARG *sel_arg, uint max_part) } else { - uint old_weight = cur->next_key_part->weight; + uint old_weight= cur->next_key_part->weight; prune_sel_arg_graph(cur->next_key_part, max_part); - old_weight -= cur->next_key_part->weight; - sel_arg->weight -= old_weight; + sel_arg->weight -= (old_weight - cur->next_key_part->weight); } } } @@ -10791,12 +10796,17 @@ SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, while (1) { - if (sel_arg->weight <= param->thd->variables.optimizer_max_sel_arg_weight) + if (likely(sel_arg->weight <= param->thd->variables. + optimizer_max_sel_arg_weight)) break; uint max_part= sel_arg->get_max_key_part(); if (max_part == sel_arg->part) { + /* + We don't return NULL right away as we want to have the information + about the changed tree in the optimizer trace. + */ sel_arg= NULL; break; } @@ -10950,11 +10960,17 @@ SEL_ARG::tree_delete(SEL_ARG *key) root=this; this->parent= 0; - // Compute the weight the tree will have after the element is removed - // We remove the element itself (weight=1) - // and the sub-graph connected to its next_key_part. + /* + Compute the weight the tree will have after the element is removed. + We remove the element itself (weight=1) + and the sub-graph connected to its next_key_part. + */ uint new_weight= root->weight - (1 + (key->next_key_part? key->next_key_part->weight : 0)); + + DBUG_ASSERT(root->weight > (1 + (key->next_key_part ? + key->next_key_part->weight : 0)); + /* Unlink from list */ if (key->prev) key->prev->next=key->next; @@ -11006,7 +11022,7 @@ SEL_ARG::tree_delete(SEL_ARG *key) test_rb_tree(root,root->parent); root->use_count=this->use_count; // Fix root counters - root->weight = new_weight; + root->weight= new_weight; root->elements=this->elements-1; root->maybe_flag=this->maybe_flag; DBUG_RETURN(root);

1 0

[Commits] f3fc8db0065: MDEV-23291: SUM column from a derived table returns invalid values
by varun 27 Jan '21

27 Jan '21

revision-id: f3fc8db00653371972c777a2ee40b20d9ea6b62b (mariadb-10.2.31-712-gf3fc8db0065) parent(s): 7c9ce660cf715e06cf7b4b69e9c1011b01abdf74 author: Varun Gupta committer: Varun Gupta timestamp: 2021-01-27 14:32:52 +0530 message: MDEV-23291: SUM column from a derived table returns invalid values The issue here was the read_set bitmap was not set for a field which was used as a reference in an inner select. We need to make sure that if we are in an inner select and we have references from outer select then we update the table bitmaps for such references. Introduced a function in the class Item_subselect that would update bitmaps of table for the references within a subquery that are defined in outer selects. --- mysql-test/r/view.result | 15 +++++++++++++++ mysql-test/t/view.test | 16 ++++++++++++++++ sql/item_subselect.cc | 20 ++++++++++++++++++++ sql/item_subselect.h | 1 + 4 files changed, 52 insertions(+) diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index e48a99f6aff..ef4f0a48534 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -6818,5 +6818,20 @@ DROP PROCEDURE sp1; DROP VIEW v1; DROP TABLE t1, t2; # +# MDEV-23291: SUM column from a derived table returns invalid values +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +CREATE view v1 AS +SELECT a as x, (select x) as y, (select y) as z FROM t1; +SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q; +sum(z) +3 +SELECT sum(z) FROM v1; +sum(z) +3 +DROP TABLE t1; +DROP VIEW v1; +# # End of 10.2 tests # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index b5ce6a1cabf..8cb00f7a6f4 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -6543,6 +6543,22 @@ DROP PROCEDURE sp1; DROP VIEW v1; DROP TABLE t1, t2; +--echo # +--echo # MDEV-23291: SUM column from a derived table returns invalid values +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); + +CREATE view v1 AS +SELECT a as x, (select x) as y, (select y) as z FROM t1; + +SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q; +SELECT sum(z) FROM v1; + +DROP TABLE t1; +DROP VIEW v1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 802bfca64b7..6bd07c7d963 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -436,6 +436,26 @@ bool Item_subselect::mark_as_dependent(THD *thd, st_select_lex *select, } +/* + @brief + Update the table bitmaps for the outer references used within a subquery +*/ + +bool Item_subselect::update_table_bitmaps_processor(void *arg) +{ + List_iterator<Ref_to_outside> it(upper_refs); + Ref_to_outside *upper; + + while ((upper= it++)) + { + if (upper->item && + upper->item->walk(&Item::update_table_bitmaps_processor, FALSE, arg)) + return TRUE; + } + return FALSE; +} + + /* Adjust attributes after our parent select has been merged into grandparent diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 2292c22480f..9116238c640 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -249,6 +249,7 @@ class Item_subselect :public Item_result_field, @retval FALSE otherwise */ bool is_expensive_processor(void *arg) { return is_expensive(); } + bool update_table_bitmaps_processor(void *arg); /** Get the SELECT_LEX structure associated with this Item.

1 0

[Commits] b588a1e: MDEV-24675 Server crash when table value constructor uses a subselect
by IgorBabaev 25 Jan '21

25 Jan '21

revision-id: b588a1ee72770d3ad8116576bdc24041bccba0df (mariadb-10.3.26-73-gb588a1e) parent(s): 75538f94ca06915ddc22458b82b8e148e51dd0db author: Igor Babaev committer: Igor Babaev timestamp: 2021-01-25 15:21:52 -0800 message: MDEV-24675 Server crash when table value constructor uses a subselect This patch actually fixes the bug MDEV-24675 and the bug MDEV-24618: Assertion failure when TVC uses a row in the context expecting scalar value The cause of these bugs is the same wrong call of the function that fixes value expressions in the value list of a table value constructor. The assertion failure happened when an expression in the value list is of the row type. In this case an error message was expected, but it was not issued because the function fix_fields_if_needed() was called for to check fields of value expressions in a TVC instead of the function fix_fields_if_needed_for_scalar() that would also check that the value expressions are are of a scalar type. The first bug happened when a table value expression used an expression returned by single-row subselect. In this case the call of the fix_fields_if_needed_for_scalar virtual function must be provided with and address to which the single-row subselect has to be attached. Test cases were added for each of the bugs. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/table_value_constr.result | 54 +++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 49 ++++++++++++++++++++++++++++ sql/sql_tvc.cc | 4 +-- 3 files changed, 105 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index b95f928..69c75dd 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2621,3 +2621,57 @@ EXECUTE IMMEDIATE 'VALUES (?)' USING IGNORE; ERROR HY000: 'ignore' is not allowed in this context EXECUTE IMMEDIATE 'VALUES (?)' USING DEFAULT; ERROR HY000: 'default' is not allowed in this context +# +# MDEV-24675: TVC using subqueries +# +values((select 1)); +(select 1) +1 +values (2), ((select 1)); +2 +2 +1 +values ((select 1)), (2), ((select 3)); +(select 1) +1 +2 +3 +values ((select 1), 2), (3,4), (5, (select 6)); +(select 1) 2 +1 2 +3 4 +5 6 +create table t1 (a int, b int); +insert into t1 values (1,3), (2,3), (3,2), (1,2); +values((select max(a) from t1)); +(select max(a) from t1) +3 +values((select min(b) from t1)); +(select min(b) from t1) +2 +values ((select max(a) from t1), (select min(b) from t1)); +(select max(a) from t1) (select min(b) from t1) +3 2 +values((select * from (select max(b) from t1) as t)); +(select * from (select max(b) from t1) as t) +3 +drop table t1; +# +# MDEV-24618: TVC contains extra parenthesis for row expressions +# in value list +# +create table t1 (a int, b int); +insert into t1 values (1,3), (2,3); +insert into t1 values ((5,4)); +ERROR 21000: Operand should contain 1 column(s) +values ((1,2)); +ERROR 21000: Operand should contain 1 column(s) +select * from (values ((1,2))) dt; +ERROR 21000: Operand should contain 1 column(s) +values (1,2); +1 2 +1 2 +values ((select min(a), max(b) from t1)); +ERROR 21000: Operand should contain 1 column(s) +drop table t1; +End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 11d553f..e8697be 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1353,3 +1353,52 @@ VALUES (DEFAULT); EXECUTE IMMEDIATE 'VALUES (?)' USING IGNORE; --error ER_UNKNOWN_ERROR EXECUTE IMMEDIATE 'VALUES (?)' USING DEFAULT; + +--echo # +--echo # MDEV-24675: TVC using subqueries +--echo # + +values((select 1)); + +values (2), ((select 1)); + +values ((select 1)), (2), ((select 3)); + +values ((select 1), 2), (3,4), (5, (select 6)); + +create table t1 (a int, b int); +insert into t1 values (1,3), (2,3), (3,2), (1,2); + +values((select max(a) from t1)); + +values((select min(b) from t1)); + +values ((select max(a) from t1), (select min(b) from t1)); + +values((select * from (select max(b) from t1) as t)); + +drop table t1; + +--echo # +--echo # MDEV-24618: TVC contains extra parenthesis for row expressions +--echo # in value list +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,3), (2,3); +--error ER_OPERAND_COLUMNS +insert into t1 values ((5,4)); + +--error ER_OPERAND_COLUMNS +values ((1,2)); + +--error ER_OPERAND_COLUMNS +select * from (values ((1,2))) dt; + +values (1,2); +--error ER_OPERAND_COLUMNS +values ((select min(a), max(b) from t1)); + +drop table t1; + +--echo End of 10.3 tests diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 10a279b..1f91539 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -47,7 +47,7 @@ bool fix_fields_for_tvc(THD *thd, List_iterator_fast<List_item> &li) while ((lst= li++)) { - List_iterator_fast<Item> it(*lst); + List_iterator<Item> it(*lst); Item *item; while ((item= it++)) @@ -59,7 +59,7 @@ bool fix_fields_for_tvc(THD *thd, List_iterator_fast<List_item> &li) while replacing their values to NAME_CONST()s. So fix only those that have not been. */ - if (item->fix_fields_if_needed(thd, 0) || + if (item->fix_fields_if_needed_for_scalar(thd, it.ref()) || item->check_is_evaluable_expression_or_error()) DBUG_RETURN(true); }

1 0

[Commits] 3e30071e2b5: MDEV-24666, Step #2: fix calculation of derived table cardinality
by Sergei Petrunia 25 Jan '21

25 Jan '21

revision-id: 3e30071e2b5405623a4f24f2910e9efb341c3c0d (mariadb-10.5.4-436-g3e30071e2b5) parent(s): 7c400434aac9e968696e6d6ad1b990d0ef70045e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-25 21:41:48 +0300 message: MDEV-24666, Step #2: fix calculation of derived table cardinality Use JOIN::join_record_count. Do not try to compute a product of POSITION::records_read as that ignores the semi-join duplicate removal, condition selectivities, etc. --- mysql-test/main/opt_tvc.result | 4 ++-- sql/sql_select.cc | 12 +++--------- 2 files changed, 5 insertions(+), 11 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 9752aa71bfb..190f7d33c95 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -444,7 +444,7 @@ where b in (3,5) group by b ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 2 DERIVED <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 @@ -464,7 +464,7 @@ as tvc_0 group by b ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 2 DERIVED <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5422346884d..7cd399fc76b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5541,7 +5541,6 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, DBUG_RETURN(TRUE); /* purecov: inspected */ { - double records= 1; SELECT_LEX_UNIT *unit= join->select_lex->master_unit(); /* Find an optimal join order of the non-constant tables. */ @@ -5568,14 +5567,9 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (!(join->select_options & SELECT_DESCRIBE) && unit->derived && unit->derived->is_materialized_derived()) { - /* - Calculate estimated number of rows for materialized derived - table/view. - */ - for (i= 0; i < join->table_count ; i++) - if (double rr= join->best_positions[i].records_read) - records= COST_MULT(records, rr); - ha_rows rows= records > (double) HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records; + ha_rows rows= (join->join_record_count > (double) HA_ROWS_MAX)? + HA_ROWS_MAX : + (ha_rows) join->join_record_count; set_if_smaller(rows, unit->lim.get_select_limit()); join->select_lex->increase_derived_records(rows); }

1 0

[Commits] 7c400434aac: MDEV-24666, Step #1: JOIN_TAB::choose_best_splitting sets records=0
by Sergei Petrunia 25 Jan '21

25 Jan '21

revision-id: 7c400434aac9e968696e6d6ad1b990d0ef70045e (mariadb-10.5.4-435-g7c400434aac) parent(s): 49b87749513fd931eb874d4559b34ed6aeec8f05 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-25 15:53:51 +0300 message: MDEV-24666, Step #1: JOIN_TAB::choose_best_splitting sets records=0 If we get the estimated #records to be less than 1.0, don't set JOIN_TAB::records=0. Round it up to 1, instead. --- sql/opt_split.cc | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 6cb4a12e51f..12715ffa886 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -1015,6 +1015,12 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, } else startup_cost= spl_opt_info->unsplit_cost; + + // JOIN_TAB::records is integer. Make sure we did not set it to 0 + // when converting from unsplit_card. + if (!records) + records= 1; + return spl_plan; }

1 0

[Commits] 5cac06a04a4: Update test results
by psergey 22 Jan '21

22 Jan '21

revision-id: 5cac06a04a468b421e39fff3daab7f93c28063f5 (mariadb-10.4.11-421-g5cac06a04a4) parent(s): ef5fe786a2f6608a67a34b1bfe91603d9a9f04f7 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-22 18:31:23 +0300 message: Update test results --- .../sys_vars/r/sysvars_server_embedded,32bit.rdiff | 137 +++++++++++---------- 1 file changed, 75 insertions(+), 62 deletions(-) diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff index 9ec15d3079a..d383deb9116 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff @@ -734,7 +734,20 @@ VARIABLE_COMMENT If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 or autoset then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of file descriptors NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2244,7 +2244,7 @@ READ_ONLY YES +@@ -2244,10 +2244,10 @@ + COMMAND_LINE_ARGUMENT REQUIRED + VARIABLE_NAME OPTIMIZER_MAX_SEL_ARG_WEIGHT + VARIABLE_SCOPE SESSION +-VARIABLE_TYPE BIGINT UNSIGNED ++VARIABLE_TYPE INT UNSIGNED + VARIABLE_COMMENT The maximum weight of the SEL_ARG graph. Set to 0 for no limit + NUMERIC_MIN_VALUE 0 +-NUMERIC_MAX_VALUE 18446744073709551615 ++NUMERIC_MAX_VALUE 4294967295 + NUMERIC_BLOCK_SIZE 1 + ENUM_VALUE_LIST NULL + READ_ONLY NO +@@ -2254,7 +2254,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION @@ -743,7 +756,7 @@ VARIABLE_COMMENT Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from the optimizer search space. Meaning: 0 - do not apply any heuristic, thus perform exhaustive search; 1 - prune plans based on number of retrieved rows NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1 -@@ -2254,7 +2254,7 @@ READ_ONLY NO +@@ -2264,7 +2264,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SEARCH_DEPTH VARIABLE_SCOPE SESSION @@ -752,7 +765,7 @@ VARIABLE_COMMENT Maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to compile a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will automatically pick a reasonable value. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 62 -@@ -2264,7 +2264,7 @@ READ_ONLY NO +@@ -2274,7 +2274,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SELECTIVITY_SAMPLING_LIMIT VARIABLE_SCOPE SESSION @@ -761,7 +774,7 @@ VARIABLE_COMMENT Controls number of record samples to check condition selectivity NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 4294967295 -@@ -2294,17 +2294,17 @@ READ_ONLY NO +@@ -2304,17 +2304,17 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_TRACE_MAX_MEM_SIZE VARIABLE_SCOPE SESSION @@ -782,7 +795,7 @@ VARIABLE_COMMENT Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial join when it searches for the best execution plan Meaning: 1 - use selectivity of index backed range conditions to calculate the cardinality of a partial join if the last joined table is accessed by full table scan or an index scan, 2 - use selectivity of index backed range conditions to calculate the cardinality of a partial join in any case, 3 - additionally always use selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join, 4 - use histograms to calculate selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join.5 - additionally use selectivity of certain non-range predicates calculated on record samples NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 5 -@@ -2324,7 +2324,7 @@ READ_ONLY YES +@@ -2334,7 +2334,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME PERFORMANCE_SCHEMA_ACCOUNTS_SIZE VARIABLE_SCOPE GLOBAL @@ -791,7 +804,7 @@ VARIABLE_COMMENT Maximum number of instrumented user@host accounts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2334,7 +2334,7 @@ READ_ONLY YES +@@ -2344,7 +2344,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_DIGESTS_SIZE VARIABLE_SCOPE GLOBAL @@ -800,7 +813,7 @@ VARIABLE_COMMENT Size of the statement digest. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 200 -@@ -2344,7 +2344,7 @@ READ_ONLY YES +@@ -2354,7 +2354,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -809,7 +822,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_STAGES_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2354,7 +2354,7 @@ READ_ONLY YES +@@ -2364,7 +2364,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -818,7 +831,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STAGES_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2364,7 +2364,7 @@ READ_ONLY YES +@@ -2374,7 +2374,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -827,7 +840,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2374,7 +2374,7 @@ READ_ONLY YES +@@ -2384,7 +2384,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -836,7 +849,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STATEMENTS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2384,7 +2384,7 @@ READ_ONLY YES +@@ -2394,7 +2394,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -845,7 +858,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_WAITS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2394,7 +2394,7 @@ READ_ONLY YES +@@ -2404,7 +2404,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -854,7 +867,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_WAITS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2404,7 +2404,7 @@ READ_ONLY YES +@@ -2414,7 +2414,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_HOSTS_SIZE VARIABLE_SCOPE GLOBAL @@ -863,7 +876,7 @@ VARIABLE_COMMENT Maximum number of instrumented hosts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2414,7 +2414,7 @@ READ_ONLY YES +@@ -2424,7 +2424,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_COND_CLASSES VARIABLE_SCOPE GLOBAL @@ -872,7 +885,7 @@ VARIABLE_COMMENT Maximum number of condition instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2424,7 +2424,7 @@ READ_ONLY YES +@@ -2434,7 +2434,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_COND_INSTANCES VARIABLE_SCOPE GLOBAL @@ -881,7 +894,7 @@ VARIABLE_COMMENT Maximum number of instrumented condition objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2434,7 +2434,7 @@ READ_ONLY YES +@@ -2444,7 +2444,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH VARIABLE_SCOPE GLOBAL @@ -890,7 +903,7 @@ VARIABLE_COMMENT Maximum length considered for digest text, when stored in performance_schema tables. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2444,7 +2444,7 @@ READ_ONLY YES +@@ -2454,7 +2454,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_CLASSES VARIABLE_SCOPE GLOBAL @@ -899,7 +912,7 @@ VARIABLE_COMMENT Maximum number of file instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2454,7 +2454,7 @@ READ_ONLY YES +@@ -2464,7 +2464,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_HANDLES VARIABLE_SCOPE GLOBAL @@ -908,7 +921,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented files. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2464,7 +2464,7 @@ READ_ONLY YES +@@ -2474,7 +2474,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES VARIABLE_SCOPE GLOBAL @@ -917,7 +930,7 @@ VARIABLE_COMMENT Maximum number of instrumented files. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2474,7 +2474,7 @@ READ_ONLY YES +@@ -2484,7 +2484,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MUTEX_CLASSES VARIABLE_SCOPE GLOBAL @@ -926,7 +939,7 @@ VARIABLE_COMMENT Maximum number of mutex instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2484,7 +2484,7 @@ READ_ONLY YES +@@ -2494,7 +2494,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES VARIABLE_SCOPE GLOBAL @@ -935,7 +948,7 @@ VARIABLE_COMMENT Maximum number of instrumented MUTEX objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2494,7 +2494,7 @@ READ_ONLY YES +@@ -2504,7 +2504,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_RWLOCK_CLASSES VARIABLE_SCOPE GLOBAL @@ -944,7 +957,7 @@ VARIABLE_COMMENT Maximum number of rwlock instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2504,7 +2504,7 @@ READ_ONLY YES +@@ -2514,7 +2514,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES VARIABLE_SCOPE GLOBAL @@ -953,7 +966,7 @@ VARIABLE_COMMENT Maximum number of instrumented RWLOCK objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2514,7 +2514,7 @@ READ_ONLY YES +@@ -2524,7 +2524,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SOCKET_CLASSES VARIABLE_SCOPE GLOBAL @@ -962,7 +975,7 @@ VARIABLE_COMMENT Maximum number of socket instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2524,7 +2524,7 @@ READ_ONLY YES +@@ -2534,7 +2534,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SOCKET_INSTANCES VARIABLE_SCOPE GLOBAL @@ -971,7 +984,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented sockets. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2534,7 +2534,7 @@ READ_ONLY YES +@@ -2544,7 +2544,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STAGE_CLASSES VARIABLE_SCOPE GLOBAL @@ -980,7 +993,7 @@ VARIABLE_COMMENT Maximum number of stage instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2544,7 +2544,7 @@ READ_ONLY YES +@@ -2554,7 +2554,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES VARIABLE_SCOPE GLOBAL @@ -989,7 +1002,7 @@ VARIABLE_COMMENT Maximum number of statement instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2554,7 +2554,7 @@ READ_ONLY YES +@@ -2564,7 +2564,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES VARIABLE_SCOPE GLOBAL @@ -998,7 +1011,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2564,7 +2564,7 @@ READ_ONLY YES +@@ -2574,7 +2574,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1007,7 +1020,7 @@ VARIABLE_COMMENT Maximum number of instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2574,7 +2574,7 @@ READ_ONLY YES +@@ -2584,7 +2584,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_THREAD_CLASSES VARIABLE_SCOPE GLOBAL @@ -1016,7 +1029,7 @@ VARIABLE_COMMENT Maximum number of thread instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2584,7 +2584,7 @@ READ_ONLY YES +@@ -2594,7 +2594,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1034,7 +1047,7 @@ VARIABLE_COMMENT Size of session attribute string buffer per thread. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2604,7 +2604,7 @@ READ_ONLY YES +@@ -2614,7 +2614,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SETUP_ACTORS_SIZE VARIABLE_SCOPE GLOBAL @@ -1043,7 +1056,7 @@ VARIABLE_COMMENT Maximum number of rows in SETUP_ACTORS. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1024 -@@ -2614,7 +2614,7 @@ READ_ONLY YES +@@ -2624,7 +2624,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SETUP_OBJECTS_SIZE VARIABLE_SCOPE GLOBAL @@ -1052,7 +1065,7 @@ VARIABLE_COMMENT Maximum number of rows in SETUP_OBJECTS. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2624,7 +2624,7 @@ READ_ONLY YES +@@ -2634,7 +2634,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_USERS_SIZE VARIABLE_SCOPE GLOBAL @@ -1061,7 +1074,7 @@ VARIABLE_COMMENT Maximum number of instrumented users. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2674,7 +2674,7 @@ READ_ONLY YES +@@ -2684,7 +2684,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PRELOAD_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1070,7 +1083,7 @@ VARIABLE_COMMENT The size of the buffer that is allocated when preloading indexes NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 -@@ -2694,7 +2694,7 @@ READ_ONLY NO +@@ -2704,7 +2704,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME PROFILING_HISTORY_SIZE VARIABLE_SCOPE SESSION @@ -1079,7 +1092,7 @@ VARIABLE_COMMENT Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 100 -@@ -2704,7 +2704,7 @@ READ_ONLY NO +@@ -2714,7 +2714,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PROGRESS_REPORT_TIME VARIABLE_SCOPE SESSION @@ -1088,7 +1101,7 @@ VARIABLE_COMMENT Seconds between sending progress reports to the client for time-consuming statements. Set to 0 to disable progress reporting. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2764,7 +2764,7 @@ READ_ONLY NO +@@ -2774,7 +2774,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME QUERY_ALLOC_BLOCK_SIZE VARIABLE_SCOPE SESSION @@ -1097,7 +1110,7 @@ VARIABLE_COMMENT Allocation block size for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 -@@ -2774,7 +2774,7 @@ READ_ONLY NO +@@ -2784,7 +2784,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_LIMIT VARIABLE_SCOPE GLOBAL @@ -1106,7 +1119,7 @@ VARIABLE_COMMENT Don't cache results that are bigger than this NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2784,7 +2784,7 @@ READ_ONLY NO +@@ -2794,7 +2794,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_MIN_RES_UNIT VARIABLE_SCOPE GLOBAL @@ -1115,7 +1128,7 @@ VARIABLE_COMMENT The minimum size for blocks allocated by the query cache NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2797,7 +2797,7 @@ VARIABLE_SCOPE GLOBAL +@@ -2807,7 +2807,7 @@ VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The memory allocated to store results from old queries NUMERIC_MIN_VALUE 0 @@ -1124,7 +1137,7 @@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -2834,7 +2834,7 @@ READ_ONLY NO +@@ -2844,7 +2844,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME QUERY_PREALLOC_SIZE VARIABLE_SCOPE SESSION @@ -1133,7 +1146,7 @@ VARIABLE_COMMENT Persistent buffer for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 -@@ -2847,7 +2847,7 @@ VARIABLE_SCOPE SESSION ONLY +@@ -2857,7 +2857,7 @@ VARIABLE_SCOPE SESSION ONLY VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@ -1142,7 +1155,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -2857,14 +2857,14 @@ VARIABLE_SCOPE SESSION ONLY +@@ -2867,14 +2867,14 @@ VARIABLE_SCOPE SESSION ONLY VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@ -1159,7 +1172,7 @@ VARIABLE_COMMENT Allocation block size for storing ranges during optimization NUMERIC_MIN_VALUE 4096 NUMERIC_MAX_VALUE 4294967295 -@@ -2874,7 +2874,7 @@ READ_ONLY NO +@@ -2884,7 +2884,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME READ_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1168,7 +1181,7 @@ VARIABLE_COMMENT Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value NUMERIC_MIN_VALUE 8192 NUMERIC_MAX_VALUE 2147483647 -@@ -2894,7 +2894,7 @@ READ_ONLY NO +@@ -2904,7 +2904,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME READ_RND_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1177,7 +1190,7 @@ VARIABLE_COMMENT When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 2147483647 -@@ -2904,10 +2904,10 @@ READ_ONLY NO +@@ -2914,10 +2914,10 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ROWID_MERGE_BUFF_SIZE VARIABLE_SCOPE SESSION @@ -1190,7 +1203,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -2944,7 +2944,7 @@ READ_ONLY YES +@@ -2954,7 +2954,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SERVER_ID VARIABLE_SCOPE SESSION @@ -1199,7 +1212,7 @@ VARIABLE_COMMENT Uniquely identifies the server instance in the community of replication partners NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 4294967295 -@@ -3014,7 +3014,7 @@ READ_ONLY NO +@@ -3024,7 +3024,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME SLAVE_MAX_ALLOWED_PACKET VARIABLE_SCOPE GLOBAL @@ -1208,7 +1221,7 @@ VARIABLE_COMMENT The maximum packet length to sent successfully from the master to slave. NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 -@@ -3024,7 +3024,7 @@ READ_ONLY NO +@@ -3034,7 +3034,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLOW_LAUNCH_TIME VARIABLE_SCOPE GLOBAL @@ -1217,7 +1230,7 @@ VARIABLE_COMMENT If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 31536000 -@@ -3067,7 +3067,7 @@ VARIABLE_SCOPE SESSION +@@ -3077,7 +3077,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Each thread that needs to do a sort allocates a buffer of this size NUMERIC_MIN_VALUE 1024 @@ -1226,7 +1239,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3274,7 +3274,7 @@ READ_ONLY NO +@@ -3284,7 +3284,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME STORED_PROGRAM_CACHE VARIABLE_SCOPE GLOBAL @@ -1235,7 +1248,7 @@ VARIABLE_COMMENT The soft upper limit for number of cached stored routines for one connection. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 524288 -@@ -3354,7 +3354,7 @@ READ_ONLY NO +@@ -3364,7 +3364,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TABLE_DEFINITION_CACHE VARIABLE_SCOPE GLOBAL @@ -1244,7 +1257,7 @@ VARIABLE_COMMENT The number of cached table definitions NUMERIC_MIN_VALUE 400 NUMERIC_MAX_VALUE 2097152 -@@ -3364,7 +3364,7 @@ READ_ONLY NO +@@ -3374,7 +3374,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TABLE_OPEN_CACHE VARIABLE_SCOPE GLOBAL @@ -1253,7 +1266,7 @@ VARIABLE_COMMENT The number of cached open tables NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 1048576 -@@ -3424,7 +3424,7 @@ READ_ONLY NO +@@ -3434,7 +3434,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME THREAD_CACHE_SIZE VARIABLE_SCOPE GLOBAL @@ -1262,7 +1275,7 @@ VARIABLE_COMMENT How many threads we should keep in a cache for reuse. These are freed after 5 minutes of idle time NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16384 -@@ -3434,7 +3434,7 @@ READ_ONLY NO +@@ -3444,7 +3444,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME THREAD_CONCURRENCY VARIABLE_SCOPE GLOBAL @@ -1271,7 +1284,7 @@ VARIABLE_COMMENT Permits the application to give the threads system a hint for the desired number of threads that should be run at the same time.This variable has no effect, and is deprecated. It will be removed in a future release. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 512 -@@ -3527,7 +3527,7 @@ VARIABLE_SCOPE SESSION +@@ -3537,7 +3537,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Max size for data for an internal temporary on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 1024 @@ -1280,7 +1293,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3537,7 +3537,7 @@ VARIABLE_SCOPE SESSION +@@ -3547,7 +3547,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. Same as tmp_table_size. NUMERIC_MIN_VALUE 1024 @@ -1289,7 +1302,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3547,14 +3547,14 @@ VARIABLE_SCOPE SESSION +@@ -3557,14 +3557,14 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Alias for tmp_memory_table_size. If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 1024 @@ -1306,7 +1319,7 @@ VARIABLE_COMMENT Allocation block size for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 -@@ -3564,7 +3564,7 @@ READ_ONLY NO +@@ -3574,7 +3574,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TRANSACTION_PREALLOC_SIZE VARIABLE_SCOPE SESSION @@ -1315,7 +1328,7 @@ VARIABLE_COMMENT Persistent buffer for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 -@@ -3704,7 +3704,7 @@ READ_ONLY YES +@@ -3714,7 +3714,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME WAIT_TIMEOUT VARIABLE_SCOPE SESSION @@ -1324,7 +1337,7 @@ VARIABLE_COMMENT The number of seconds the server waits for activity on a connection before closing it NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 31536000 -@@ -3731,7 +3731,7 @@ order by variable_name; +@@ -3741,7 +3741,7 @@ order by variable_name; VARIABLE_NAME LOG_TC_SIZE GLOBAL_VALUE_ORIGIN AUTO VARIABLE_SCOPE GLOBAL

1 0

[Commits] eb75e8705d9: MDEV-8134: The relay-log is not flushed after the slave-relay-log.999999 showed
by sujatha 21 Jan '21

21 Jan '21

revision-id: eb75e8705d9a444e10057967eaebf947b1115ff8 (mariadb-10.2.31-695-geb75e8705d9) parent(s): 53acd1c1d88be82190c56af3e4cc11fb2770a169 author: Sujatha committer: Sujatha timestamp: 2021-01-21 13:00:02 +0530 message: MDEV-8134: The relay-log is not flushed after the slave-relay-log.999999 showed Problem: ======== Auto purge of relaylogs stops when relay-log-file is 'slave-relay-log.999999' and slave_parallel_threads is enabled. Analysis: ========= The problem is that in Relay_log_info::inc_group_relay_log_pos() function, when two log names are compared via strcmp() function, it gives correct result, when log name sequence numbers are of same digits(6 digits), But when the number goes to 7 digits, a 999999 compares greater than 1000000, which is wrong, hence the bug. Fix: ==== Extract the numeric extension part of the file name, convert it into unsigned long and compare. Thanks to David Zhao for the contribution. --- .../suite/rpl/r/rpl_relay_max_extension.result | 37 +++++++ .../suite/rpl/t/rpl_relay_max_extension.test | 109 +++++++++++++++++++++ sql/rpl_parallel.cc | 5 +- sql/rpl_rli.cc | 4 +- sql/sql_repl.cc | 17 ++++ sql/sql_repl.h | 1 + 6 files changed, 169 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_relay_max_extension.result b/mysql-test/suite/rpl/r/rpl_relay_max_extension.result new file mode 100644 index 00000000000..4444398203e --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_relay_max_extension.result @@ -0,0 +1,37 @@ +include/rpl_init.inc [topology=1->2] +connection server_2; +include/stop_slave.inc +RESET SLAVE; +include/start_slave.inc +include/stop_slave.inc +# +# Stop slave server +# +# +# Simulate file number get close to 999997 +# by renaming relay logs and modifying index/info files +# +# Restart slave server +# +SET @save_slave_parallel_threads= @@GLOBAL.slave_parallel_threads; +SET @save_max_relay_log_size= @@GLOBAL.max_relay_log_size; +SET GLOBAL slave_parallel_threads=1; +SET GLOBAL max_relay_log_size=100 * 1024; +include/start_slave.inc +connection server_1; +create table t1 (i int, c varchar(1024)); +# +# Insert some data to generate enough amount of binary logs +# +connection server_2; +# +# Assert that 'slave-relay-bin.999999' is purged. +# +NOT FOUND /slave-relay-bin.999999/ in slave-relay-bin.index +include/stop_slave.inc +SET GLOBAL slave_parallel_threads= @save_slave_parallel_threads; +SET GLOBAL max_relay_log_size= @save_max_relay_log_size; +include/start_slave.inc +connection server_1; +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_relay_max_extension.test b/mysql-test/suite/rpl/t/rpl_relay_max_extension.test new file mode 100644 index 00000000000..e1e087f2e0e --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_relay_max_extension.test @@ -0,0 +1,109 @@ +# ==== Purpose ==== +# +# Test verifies that auto purging mechanism of relay logs works fine when the +# file extension grows beyond 999999. +# +# ==== Implementation ==== +# +# Steps: +# 0 - In master-slave setup clear all the relay logs on the slave server. +# 1 - Start the slave so that new relay logs starting from +# 'slave-relay-bin.000001' are created. +# 2 - Get the active relay-log file name by using SHOW SLAVE STATUS. +# Shutdown the slave server. +# 3 - Rename active relay log to '999997' in both 'relay-log.info' and +# 'slave-relay-bin.index' files. +# 4 - Restart the slave server by configuring 'slave_parallel_threads=1' +# and 'max_relay_log_size=100K'. +# 5 - Generate load on master such that few relay logs are generated on +# slave. The relay log sequence number will change to 7 digits. +# 6 - Sync slave with master to ensure that relay logs are applied on +# slave. They should have been automatically purged. +# 7 - Assert that there is no 'slave-relay-bin.999999' file in +# 'relay-log.info'. +# +# ==== References ==== +# +# MDEV-8134: The relay-log is not flushed after the slave-relay-log.999999 +# showed +# + +--source include/have_innodb.inc +--source include/have_binlog_format_row.inc +--let $rpl_topology=1->2 +--source include/rpl_init.inc + +--connection server_2 +--source include/stop_slave.inc +RESET SLAVE; +--source include/start_slave.inc +--source include/stop_slave.inc +--let $relay_log=query_get_value(SHOW SLAVE STATUS, Relay_Log_File, 1) + +--echo # +--echo # Stop slave server +--echo # + +--let $datadir = `select @@datadir` +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.2.expect +--shutdown_server 10 +--source include/wait_until_disconnected.inc + +--exec sed -i "s/$relay_log/slave-relay-bin.999997/g" $datadir/relay-log.info +--exec sed -i "s/$relay_log/slave-relay-bin.999997/g" $datadir/slave-relay-bin.index + +--echo # +--echo # Simulate file number get close to 999997 +--echo # by renaming relay logs and modifying index/info files + +--move_file $datadir/$relay_log $datadir/slave-relay-bin.999997 + +--echo # +--echo # Restart slave server +--echo # + +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.2.expect +--enable_reconnect +--source include/wait_until_connected_again.inc +SET @save_slave_parallel_threads= @@GLOBAL.slave_parallel_threads; +SET @save_max_relay_log_size= @@GLOBAL.max_relay_log_size; + +SET GLOBAL slave_parallel_threads=1; +SET GLOBAL max_relay_log_size=100 * 1024; +--source include/start_slave.inc + +--connection server_1 +create table t1 (i int, c varchar(1024)); +--echo # +--echo # Insert some data to generate enough amount of binary logs +--echo # +--let $count = 1000 +--disable_query_log +while ($count) +{ + eval insert into t1 values (1001 - $count, repeat('a',1000)); + dec $count; +} +--enable_query_log +--save_master_pos + +--connection server_2 +--sync_with_master + +--let $relay_log=query_get_value(SHOW SLAVE STATUS, Relay_Log_File, 1) + +--echo # +--echo # Assert that 'slave-relay-bin.999999' is purged. +--echo # +let SEARCH_FILE=$datadir/slave-relay-bin.index; +let SEARCH_PATTERN=slave-relay-bin.999999; +source include/search_pattern_in_file.inc; + +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads= @save_slave_parallel_threads; +SET GLOBAL max_relay_log_size= @save_max_relay_log_size; +--source include/start_slave.inc + +--connection server_1 +DROP TABLE t1; +--source include/rpl_end.inc diff --git a/sql/rpl_parallel.cc b/sql/rpl_parallel.cc index 4cf87ba73b7..869640fd46f 100644 --- a/sql/rpl_parallel.cc +++ b/sql/rpl_parallel.cc @@ -4,6 +4,7 @@ #include "rpl_mi.h" #include "sql_parse.h" #include "debug_sync.h" +#include "sql_repl.h" /* Code for optional parallel execution of replicated events on the slave. @@ -82,7 +83,7 @@ handle_queued_pos_update(THD *thd, rpl_parallel_thread::queued_event *qev) return; mysql_mutex_lock(&rli->data_lock); - cmp= strcmp(rli->group_relay_log_name, qev->event_relay_log_name); + cmp= compare_log_name(rli->group_relay_log_name, qev->event_relay_log_name); if (cmp < 0) { rli->group_relay_log_pos= qev->future_event_relay_log_pos; @@ -91,7 +92,7 @@ handle_queued_pos_update(THD *thd, rpl_parallel_thread::queued_event *qev) rli->group_relay_log_pos < qev->future_event_relay_log_pos) rli->group_relay_log_pos= qev->future_event_relay_log_pos; - cmp= strcmp(rli->group_master_log_name, qev->future_event_master_log_name); + cmp= compare_log_name(rli->group_master_log_name, qev->future_event_master_log_name); if (cmp < 0) { strcpy(rli->group_master_log_name, qev->future_event_master_log_name); diff --git a/sql/rpl_rli.cc b/sql/rpl_rli.cc index 40ab375571a..5273b33c728 100644 --- a/sql/rpl_rli.cc +++ b/sql/rpl_rli.cc @@ -989,7 +989,7 @@ void Relay_log_info::inc_group_relay_log_pos(ulonglong log_pos, if (rgi->is_parallel_exec) { /* In case of parallel replication, do not update the position backwards. */ - int cmp= strcmp(group_relay_log_name, rgi->event_relay_log_name); + int cmp= compare_log_name(group_relay_log_name, rgi->event_relay_log_name); if (cmp < 0) { group_relay_log_pos= rgi->future_event_relay_log_pos; @@ -1001,7 +1001,7 @@ void Relay_log_info::inc_group_relay_log_pos(ulonglong log_pos, In the parallel case we need to update the master_log_name here, rather than in Rotate_log_event::do_update_pos(). */ - cmp= strcmp(group_master_log_name, rgi->future_event_master_log_name); + cmp= compare_log_name(group_master_log_name, rgi->future_event_master_log_name); if (cmp <= 0) { if (cmp < 0) diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index 4af8ebc2dd8..59a3f686e45 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -4541,5 +4541,22 @@ rpl_gtid_pos_update(THD *thd, char *str, size_t len) return false; } +int compare_log_name(const char *log_1, const char *log_2) { + int res= 1; + const char *ext1_str= strrchr(log_1, '.'); + const char *ext2_str= strrchr(log_2, '.'); + char file_name_1[255], file_name_2[255]; + strmake(file_name_1, log_1, (ext1_str - log_1)); + strmake(file_name_2, log_2, (ext2_str - log_2)); + char *endptr = NULL; + res= strcmp(file_name_1, file_name_2); + if (!res) + { + ulong ext1= strtoul(++ext1_str, &endptr, 10); + ulong ext2= strtoul(++ext2_str, &endptr, 10); + res= (ext1 > ext2 ? 1 : ((ext1 == ext2) ? 0 : -1)); + } + return res; +} #endif /* HAVE_REPLICATION */ diff --git a/sql/sql_repl.h b/sql/sql_repl.h index 8ddfa9239f6..9129aaeed5e 100644 --- a/sql/sql_repl.h +++ b/sql/sql_repl.h @@ -56,6 +56,7 @@ bool show_binlogs(THD* thd); extern int init_master_info(Master_info* mi); void kill_zombie_dump_threads(uint32 slave_server_id); int check_binlog_magic(IO_CACHE* log, const char** errmsg); +int compare_log_name(const char *log_1, const char *log_2); struct LOAD_FILE_IO_CACHE : public IO_CACHE {

1 0

[Commits] 962624a3f82: MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index
by varun 20 Jan '21

20 Jan '21

revision-id: 962624a3f82f36f13551afc049eab903f82de6e0 (mariadb-10.2.31-679-g962624a3f82) parent(s): a87320e335b6345f1ce04eea8b790a741c1ec23e author: Varun Gupta committer: Varun Gupta timestamp: 2021-01-20 17:20:53 +0530 message: MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index The issue happens when the secondary keys are extended with primary key parts. Inside the function TABLE_SHARE::init_from_binary_frm_image() adds the length bytes for the primary key key parts to the length of the secondary key. This is not needed because when the extended keys are used we recalculate the length for the used key parts. --- mysql-test/r/innodb_ext_key.result | 16 ++++++++++++++++ mysql-test/t/innodb_ext_key.test | 19 +++++++++++++++++++ sql/table.cc | 3 ++- 3 files changed, 37 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result index c7a5b1a80ca..880d7a8bceb 100644 --- a/mysql-test/r/innodb_ext_key.result +++ b/mysql-test/r/innodb_ext_key.result @@ -790,3 +790,19 @@ EXPLAIN } drop table t1; SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 ( +pk VARCHAR(50), +a VARCHAR(20), +KEY k1(a), +PRIMARY KEY(pk) +)ENGINE=INNODB; +INSERT INTO t2 SELECT a,a FROM t1; +EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL k1 23 NULL 10 Using index +DROP TABLE t1,t2; diff --git a/mysql-test/t/innodb_ext_key.test b/mysql-test/t/innodb_ext_key.test index f5edd736490..de9ca10ff1b 100644 --- a/mysql-test/t/innodb_ext_key.test +++ b/mysql-test/t/innodb_ext_key.test @@ -606,3 +606,22 @@ explain format= json select * from t1 force index(k1) where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and f1 <= '3'; drop table t1; SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index +--echo # + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +CREATE TABLE t2 ( + pk VARCHAR(50), + a VARCHAR(20), + KEY k1(a), + PRIMARY KEY(pk) +)ENGINE=INNODB; + +INSERT INTO t2 SELECT a,a FROM t1; +EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); + +DROP TABLE t1,t2; diff --git a/sql/table.cc b/sql/table.cc index e4492f21a30..04bede3f8a3 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -2285,7 +2285,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, else key_part->key_part_flag|= HA_VAR_LENGTH_PART; key_part->store_length+=HA_KEY_BLOB_LENGTH; - keyinfo->key_length+= HA_KEY_BLOB_LENGTH; + if (i < keyinfo->user_defined_key_parts) + keyinfo->key_length+= HA_KEY_BLOB_LENGTH; } if (field->type() == MYSQL_TYPE_BIT) key_part->key_part_flag|= HA_BIT_PART;

1 0