- commits - lists.mariadb.org

[Commits] 1ec5d0e12: Basic return-early optimization in RangeTreeLockManager::TryLock
by psergey 14 Jun '21

14 Jun '21

revision-id: 1ec5d0e1234f8deda4d135e2d48d0ed7406ea513 (v5.8-3393-g1ec5d0e12) parent(s): 0f8c041ea7bb458caa5ec0dbeef9fa42d0b97482 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-14 22:14:24 +0300 message: Basic return-early optimization in RangeTreeLockManager::TryLock Do not call lock_request::wait when lock acquisition has succeeded. --- .../lock/range/range_tree/range_tree_lock_manager.cc | 10 ++++++---- 1 file changed, 6 insertions(+), 4 deletions(-) diff --git a/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc b/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc index 6dfb78d3f..55a66bc59 100644 --- a/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc +++ b/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc @@ -115,11 +115,13 @@ Status RangeTreeLockManager::TryLock(PessimisticTransaction* txn, std::move(end)}); }; - request.start(); + int r = request.start(); - const int r = request.wait(wait_time_msec, killed_time_msec, - nullptr, // killed_callback - wait_callback_for_locktree, nullptr); + if (r) { + r = request.wait(wait_time_msec, killed_time_msec, + nullptr, // killed_callback + wait_callback_for_locktree, nullptr); + } // Inform the txn that we are no longer waiting: txn->ClearWaitingTxn();

1 0

[Commits] dbc0683f13d: MDEV-25858: Query results are incorrect when indexes are added
by psergey 11 Jun '21

11 Jun '21

revision-id: dbc0683f13d07461c50ddd61b93f3c102bc1a9b3 (mariadb-10.2.31-1001-gdbc0683f13d) parent(s): 152c83d49ca821c54aa49f6b43e33cba63e4d19f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-11 12:37:32 +0300 message: MDEV-25858: Query results are incorrect when indexes are added If test_if_skip_sort_order() decides to use an index to produce required ordering, it should disable "Range Checked for each record" optimization. This is because Range-Checked-for-each-record may decide to use an index (or an index_merge) which will not produce the required ordering. --- mysql-test/r/order_by_innodb.result | 52 +++++++++++++++++++++++++++++++++++++ mysql-test/t/order_by_innodb.test | 51 ++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 12 +++++++++ 3 files changed, 115 insertions(+) diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 9cdf9800cee..14b9b861a14 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -147,4 +147,56 @@ i n 656 eight set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2,t3; +# +# MDEV-25858: Query results are incorrect when indexes are added +# +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb; +insert into t1 values (1),(2),(3); +CREATE TABLE t2 ( +id int NOT NULL PRIMARY KEY, +id2 int NOT NULL, +d1 datetime, +d2 timestamp NOT NULL, +KEY id2 (id2) +) engine=innodb; +insert into t2 values +(1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'), +(2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'), +(3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00'); +select +t1.id,t2.id +from +t1 left join +t2 on t2.id2 = t1.id and +t2.id = (select dd.id +from t2 dd +where +dd.id2 = t1.id and +d1 > '2019-02-06 00:00:00' + order by +dd.d1 desc, dd.d2 desc, dd.id desc limit 1 +); +id id +1 NULL +2 1 +3 3 +create index for_latest_sort on t2 (d1 desc, d2 desc, id desc); +select +t1.id,t2.id +from +t1 left join +t2 on t2.id2 = t1.id and +t2.id = (select dd.id +from t2 dd +where +dd.id2 = t1.id and +d1 > '2019-02-06 00:00:00' + order by +dd.d1 desc, dd.d2 desc, dd.id desc limit 1 +); +id id +1 NULL +2 1 +3 3 +drop table t1,t2; # End of 10.2 tests diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index f4c738263ae..97c043b8dbc 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -135,4 +135,55 @@ set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-25858: Query results are incorrect when indexes are added +--echo # + +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb; +insert into t1 values (1),(2),(3); + +CREATE TABLE t2 ( + id int NOT NULL PRIMARY KEY, + id2 int NOT NULL, + d1 datetime, + d2 timestamp NOT NULL, + KEY id2 (id2) +) engine=innodb; + +insert into t2 values + (1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'), + (2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'), + (3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00'); + +select + t1.id,t2.id +from + t1 left join + t2 on t2.id2 = t1.id and + t2.id = (select dd.id + from t2 dd + where + dd.id2 = t1.id and + d1 > '2019-02-06 00:00:00' + order by + dd.d1 desc, dd.d2 desc, dd.id desc limit 1 + ); + +create index for_latest_sort on t2 (d1 desc, d2 desc, id desc); + +select + t1.id,t2.id +from + t1 left join + t2 on t2.id2 = t1.id and + t2.id = (select dd.id + from t2 dd + where + dd.id2 = t1.id and + d1 > '2019-02-06 00:00:00' + order by + dd.d1 desc, dd.d2 desc, dd.id desc limit 1 + ); +drop table t1,t2; + --echo # End of 10.2 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ce706209017..b6f5385d653 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -21917,6 +21917,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, if (select->quick == save_quick) save_quick= 0; // make_reverse() consumed it select->set_quick(tmp); + /* Cancel "Range checked for each record" */ + if (tab->use_quick == 2) + { + tab->use_quick= 1; + tab->read_first_record= join_init_read_record; + } } else if (tab->type != JT_NEXT && tab->type != JT_REF_OR_NULL && tab->ref.key >= 0 && tab->ref.key_parts <= used_key_parts) @@ -21929,6 +21935,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, */ tab->read_first_record= join_read_last_key; tab->read_record.read_record= join_read_prev_same; + /* Cancel "Range checked for each record" */ + if (tab->use_quick == 2) + { + tab->use_quick= 1; + tab->read_first_record= join_init_read_record; + } /* Cancel Pushed Index Condition, as it doesn't work for reverse scans. */

1 0

[Commits] 3e7f8502d33: MDEV-25858: Query results are incorrect when indexes are added
by psergey 10 Jun '21

10 Jun '21

revision-id: 3e7f8502d33816678eac58e216842e1ee97b29ca (mariadb-10.2.31-1001-g3e7f8502d33) parent(s): 152c83d49ca821c54aa49f6b43e33cba63e4d19f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-10 23:54:14 +0300 message: MDEV-25858: Query results are incorrect when indexes are added If test_if_skip_sort_order() decides to use an index to produce required ordering, it should disable "Range Checked for each record" (RCfER) optimization. This is because RCfER may decide to use an index (or index_merge) which will not produce the required ordering. --- mysql-test/r/order_by_innodb.result | 52 +++++++++++++++++++++++++++++++++++++ mysql-test/t/order_by_innodb.test | 51 ++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 6 +++++ 3 files changed, 109 insertions(+) diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 9cdf9800cee..14b9b861a14 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -147,4 +147,56 @@ i n 656 eight set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2,t3; +# +# MDEV-25858: Query results are incorrect when indexes are added +# +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb; +insert into t1 values (1),(2),(3); +CREATE TABLE t2 ( +id int NOT NULL PRIMARY KEY, +id2 int NOT NULL, +d1 datetime, +d2 timestamp NOT NULL, +KEY id2 (id2) +) engine=innodb; +insert into t2 values +(1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'), +(2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'), +(3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00'); +select +t1.id,t2.id +from +t1 left join +t2 on t2.id2 = t1.id and +t2.id = (select dd.id +from t2 dd +where +dd.id2 = t1.id and +d1 > '2019-02-06 00:00:00' + order by +dd.d1 desc, dd.d2 desc, dd.id desc limit 1 +); +id id +1 NULL +2 1 +3 3 +create index for_latest_sort on t2 (d1 desc, d2 desc, id desc); +select +t1.id,t2.id +from +t1 left join +t2 on t2.id2 = t1.id and +t2.id = (select dd.id +from t2 dd +where +dd.id2 = t1.id and +d1 > '2019-02-06 00:00:00' + order by +dd.d1 desc, dd.d2 desc, dd.id desc limit 1 +); +id id +1 NULL +2 1 +3 3 +drop table t1,t2; # End of 10.2 tests diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index f4c738263ae..97c043b8dbc 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -135,4 +135,55 @@ set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-25858: Query results are incorrect when indexes are added +--echo # + +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb; +insert into t1 values (1),(2),(3); + +CREATE TABLE t2 ( + id int NOT NULL PRIMARY KEY, + id2 int NOT NULL, + d1 datetime, + d2 timestamp NOT NULL, + KEY id2 (id2) +) engine=innodb; + +insert into t2 values + (1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'), + (2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'), + (3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00'); + +select + t1.id,t2.id +from + t1 left join + t2 on t2.id2 = t1.id and + t2.id = (select dd.id + from t2 dd + where + dd.id2 = t1.id and + d1 > '2019-02-06 00:00:00' + order by + dd.d1 desc, dd.d2 desc, dd.id desc limit 1 + ); + +create index for_latest_sort on t2 (d1 desc, d2 desc, id desc); + +select + t1.id,t2.id +from + t1 left join + t2 on t2.id2 = t1.id and + t2.id = (select dd.id + from t2 dd + where + dd.id2 = t1.id and + d1 > '2019-02-06 00:00:00' + order by + dd.d1 desc, dd.d2 desc, dd.id desc limit 1 + ); +drop table t1,t2; + --echo # End of 10.2 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ce706209017..d91be9f4705 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -21917,6 +21917,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, if (select->quick == save_quick) save_quick= 0; // make_reverse() consumed it select->set_quick(tmp); + /* Cancel "Range checked for each record" */ + tab->use_quick= 1; + tab->read_first_record= join_init_read_record; } else if (tab->type != JT_NEXT && tab->type != JT_REF_OR_NULL && tab->ref.key >= 0 && tab->ref.key_parts <= used_key_parts) @@ -21929,6 +21932,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, */ tab->read_first_record= join_read_last_key; tab->read_record.read_record= join_read_prev_same; + /* Cancel "Range checked for each record" */ + tab->use_quick= 1; + tab->read_first_record= join_init_read_record; /* Cancel Pushed Index Condition, as it doesn't work for reverse scans. */

1 0

[Commits] c872125a664: MDEV-25630: Crash with window function in left expr of IN subquery
by psergey 09 Jun '21

09 Jun '21

revision-id: c872125a664842ecfb66c60f69b3a87390aec23d (mariadb-10.2.31-997-gc872125a664) parent(s): dfa2d0bc13362b949b1b1699955583f74e7db90a author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-09 15:52:13 +0300 message: MDEV-25630: Crash with window function in left expr of IN subquery * Make Item_in_optimizer::fix_fields inherit the with_window_func attribute of the subquery's left expression (the subquery itself cannot have window functions that are aggregated in this select) * Make Item_cache_wrapper::Item_cache_wrapper() inherit with_window_func attribute of the item it is caching. --- mysql-test/r/win.result | 19 +++++++++++++++++++ .../suite/encryption/r/tempfiles_encrypted.result | 19 +++++++++++++++++++ mysql-test/t/win.test | 14 ++++++++++++++ sql/item.cc | 1 + sql/item_cmpfunc.cc | 3 +++ 5 files changed, 56 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index dd74c5c77fd..8a31dcc0634 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3892,5 +3892,24 @@ id rn 1 1 drop table t1; # +# MDEV-25630: Crash with window function in left expr of IN subquery +# +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) FROM t1; +lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) +NULL +1 +0 +DROP TABLE t1; +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1; +sum(i) over () IN ( SELECT 1 FROM t1 a) +0 +0 +0 +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/suite/encryption/r/tempfiles_encrypted.result b/mysql-test/suite/encryption/r/tempfiles_encrypted.result index 27eedc45028..3c81d7b6046 100644 --- a/mysql-test/suite/encryption/r/tempfiles_encrypted.result +++ b/mysql-test/suite/encryption/r/tempfiles_encrypted.result @@ -3898,6 +3898,25 @@ id rn 1 1 drop table t1; # +# MDEV-25630: Crash with window function in left expr of IN subquery +# +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) FROM t1; +lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) +NULL +1 +0 +DROP TABLE t1; +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1; +sum(i) over () IN ( SELECT 1 FROM t1 a) +0 +0 +0 +DROP TABLE t1; +# # End of 10.2 tests # # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 57214ab0165..c07a81f17da 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2542,6 +2542,20 @@ order by rn desc; drop table t1; +--echo # +--echo # MDEV-25630: Crash with window function in left expr of IN subquery +--echo # + +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1; +DROP TABLE t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item.cc b/sql/item.cc index be64edca9a1..d7a3659a2ce 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -8203,6 +8203,7 @@ Item_cache_wrapper::Item_cache_wrapper(THD *thd, Item *item_arg): name= item_arg->name; name_length= item_arg->name_length; with_subselect= orig_item->with_subselect; + with_window_func= orig_item->with_window_func; if ((expr_value= Item_cache::get_cache(thd, orig_item))) expr_value->setup(thd, orig_item); diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 7b7604053e3..8a2c532f621 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1416,6 +1416,9 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref) maybe_null=1; with_subselect= 1; with_sum_func= with_sum_func || args[1]->with_sum_func; + with_window_func= args[0]->with_window_func; + // The subquery cannot have window functions aggregated in this select + DBUG_ASSERT(!args[1]->with_window_func); with_field= with_field || args[1]->with_field; with_param= args[0]->with_param || args[1]->with_param; used_tables_and_const_cache_join(args[1]);

1 0

[Commits] b3dede9: MDEV-25714 Join using derived with aggregation returns incorrect results
by IgorBabaev 08 Jun '21

08 Jun '21

revision-id: b3dede93e6226c4680e6326dd47b523f4a8b9715 (mariadb-10.4.4-2310-gb3dede9) parent(s): 72b33a046263dafa03c04d2ec59ed2ee2b430434 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-07 20:53:37 -0700 message: MDEV-25714 Join using derived with aggregation returns incorrect results If a join query uses a derived table (view / CTE) with GROUP BY clause then the execution plan for such join may employ split optimization. When this optimization is employed the derived table is not materialized. Rather only some partitions of the derived table are subject to grouping. Split optimization can be applied only if: - there are some indexes over the tables used in the join specifying the derived table whose prefixes partially cover the field items used in the GROUP BY list (such indexes are called splitting indexes) - the WHERE condition of the join query contains conjunctive equalities between columns of the derived table that comprise major parts of splitting indexes and columns of the other join tables. When the optimizer evaluates extending of a partial join by the rows of the derived table it always considers a possibility of using split optimization. Different splitting indexes can be used depending on the extended partial join. At some rare conditions, for example, when there is a non-splitting covering index for a table joined in the join specifying the derived table usage of a splitting index to produce rows needed for grouping may be still less beneficial than usage of such covering index without any splitting technique. The function JOIN_TAB::choose_best_splitting() must take this into account. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/derived_cond_pushdown.result | 2 +- mysql-test/main/derived_split_innodb.result | 65 ++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 41 ++++++++++++++++++ sql/opt_split.cc | 27 +++++++++--- 4 files changed, 129 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 33b625a..016ca22 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16679,7 +16679,7 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 LATERAL DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 55ace91..9edf9a1 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -176,3 +176,68 @@ id select_type table type possible_keys key key_len ref rows Extra 3 LATERAL DERIVED t1 ref a,a_2 a 5 test.t1.a 1 Using where; Using temporary; Using filesort 3 LATERAL DERIVED t2 ref c c 5 test.t1.b 1 Using index DROP TABLE t1, t2; +# +# Bug mdev-25714: usage non-splitting covering index is cheaper than +# usage of the best splitting index for one group +# +create table t1 ( +id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( +id int not null, itemid int not null, userid int not null, primary key (id), +index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +set use_stat_tables='never'; +set optimizer_use_condition_selectivity=1; +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +set optimizer_switch='split_materialized=on'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY <derived2> ref key1 key1 4 test.t2.id 2 +1 PRIMARY t1 ALL idx NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +2 DERIVED t3 ref idx1,idx2 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +set optimizer_switch='split_materialized=off'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY <derived2> ref key1 key1 4 test.t2.id 2 +1 PRIMARY t1 ALL idx NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +2 DERIVED t3 ref idx1 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; +set use_stat_tables=default; +set optimizer_use_condition_selectivity=default; +# End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 10fc3f9..bee9ef4 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -152,3 +152,44 @@ eval set statement optimizer_switch='split_materialized=on' for $query; DROP TABLE t1, t2; +--echo # +--echo # Bug mdev-25714: usage non-splitting covering index is cheaper than +--echo # usage of the best splitting index for one group +--echo # + +create table t1 ( + id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( + id int not null, itemid int not null, userid int not null, primary key (id), + index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +set use_stat_tables='never'; +set optimizer_use_condition_selectivity=1; +analyze table t1,t2,t3; + +let $q= +select t1.id, t1.itemid, dt.id, t2.id + from t1, + (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, + t2 + where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; + +set optimizer_switch='split_materialized=on'; +eval explain $q; +eval $q; + +set optimizer_switch='split_materialized=off'; +eval explain $q; +eval $q; + +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; +set use_stat_tables=default; +set optimizer_use_condition_selectivity=default; + +--echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 28a5483..316919c 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -958,11 +958,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, in the cache */ spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts); - if (!spl_plan && - (spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) && - (spl_plan->best_positions= - (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) && - !spl_opt_info->plan_cache.push_back(spl_plan)) + if (!spl_plan) { /* The plan for the chosen key has not been found in the cache. @@ -972,6 +968,27 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, best_key, remaining_tables, true); choose_plan(join, all_table_map & ~join->const_table_map); + + /* + Check that the chosen plan is really a splitting plan. + If not or if there is not enough memory to save the plan in the cache + then just return with no splitting plan. + */ + POSITION *first_non_const_pos= join->best_positions + join->const_tables; + TABLE *table= first_non_const_pos->table->table; + key_map spl_keys= table->keys_usable_for_splitting; + if (!(first_non_const_pos->key && + spl_keys.is_set(first_non_const_pos->key->key)) || + !(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) || + !(spl_plan->best_positions= + (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) || + spl_opt_info->plan_cache.push_back(spl_plan)) + { + reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, + best_key, remaining_tables, false); + return 0; + } + spl_plan->keyuse_ext_start= best_key_keyuse_ext_start; spl_plan->table= best_table; spl_plan->key= best_key;

1 0

[Commits] 6846a33: MDEV-25714 Join using derived with aggregation returns incorrect results
by IgorBabaev 08 Jun '21

08 Jun '21

revision-id: 6846a3326a3235c0a9f9d85b5b4bc2202ac7ca13 (mariadb-10.4.4-2310-g6846a33) parent(s): 72b33a046263dafa03c04d2ec59ed2ee2b430434 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-07 20:48:04 -0700 message: MDEV-25714 Join using derived with aggregation returns incorrect results If a join query uses a derived table (view / CTE) with GROUP BY clause then the execution plan for such join may employ split optimization. When this optimization is employed the derived table is not materialized. Rather only some partitions of the derived table are subject to grouping. Split optimization can be applied only if: - there are some indexes over the tables used in the join specifying the derived table whose prefixes partially cover the field items used in the GROUP BY list (such indexes are called splitting indexes) - the WHERE condition of the join query contains conjunctive equalities between columns of the derived table that comprise major parts of splitting indexes and columns of the other join tables. When the optimizer evaluates extending of a partial join by the rows of the derived table it always considers a possibility of using split optimization. Different splitting indexes can be used depending on the extended partial join. At some rare conditions, for example, when there is a non-splitting covering index for a table joined in the join specifying the derived table usage of a splitting index to produce rows needed for grouping may be still less beneficial than usage of such covering index without any splitting technique. The function JOIN_TAB::choose_best_splitting() must take this into account. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/derived_cond_pushdown.result | 2 +- mysql-test/main/derived_split_innodb.result | 65 ++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 41 ++++++++++++++++++ sql/opt_split.cc | 27 +++++++++--- 4 files changed, 129 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 33b625a..016ca22 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16679,7 +16679,7 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 LATERAL DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 55ace91..4a4f860 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -176,3 +176,68 @@ id select_type table type possible_keys key key_len ref rows Extra 3 LATERAL DERIVED t1 ref a,a_2 a 5 test.t1.a 1 Using where; Using temporary; Using filesort 3 LATERAL DERIVED t2 ref c c 5 test.t1.b 1 Using index DROP TABLE t1, t2; +# +# Bug mdev-25714: usage non-splitting covering index is cheaper than +# usage of the best splitting index for one group +# +create table t1 ( +id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( +id int not null, itemid int not null, userid int not null, primary key (id), +index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +set use_stat_tables='never'; +set optimizer_use_condition_selectivity=1; +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +set optimizer_switch='split_materialized=on'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1,idx2 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +set optimizer_switch='split_materialized=off'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; +set use_stat_tables=default; +set optimizer_use_condition_selectivity=default; +# End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 10fc3f9..bee9ef4 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -152,3 +152,44 @@ eval set statement optimizer_switch='split_materialized=on' for $query; DROP TABLE t1, t2; +--echo # +--echo # Bug mdev-25714: usage non-splitting covering index is cheaper than +--echo # usage of the best splitting index for one group +--echo # + +create table t1 ( + id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( + id int not null, itemid int not null, userid int not null, primary key (id), + index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +set use_stat_tables='never'; +set optimizer_use_condition_selectivity=1; +analyze table t1,t2,t3; + +let $q= +select t1.id, t1.itemid, dt.id, t2.id + from t1, + (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, + t2 + where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; + +set optimizer_switch='split_materialized=on'; +eval explain $q; +eval $q; + +set optimizer_switch='split_materialized=off'; +eval explain $q; +eval $q; + +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; +set use_stat_tables=default; +set optimizer_use_condition_selectivity=default; + +--echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 28a5483..316919c 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -958,11 +958,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, in the cache */ spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts); - if (!spl_plan && - (spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) && - (spl_plan->best_positions= - (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) && - !spl_opt_info->plan_cache.push_back(spl_plan)) + if (!spl_plan) { /* The plan for the chosen key has not been found in the cache. @@ -972,6 +968,27 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, best_key, remaining_tables, true); choose_plan(join, all_table_map & ~join->const_table_map); + + /* + Check that the chosen plan is really a splitting plan. + If not or if there is not enough memory to save the plan in the cache + then just return with no splitting plan. + */ + POSITION *first_non_const_pos= join->best_positions + join->const_tables; + TABLE *table= first_non_const_pos->table->table; + key_map spl_keys= table->keys_usable_for_splitting; + if (!(first_non_const_pos->key && + spl_keys.is_set(first_non_const_pos->key->key)) || + !(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) || + !(spl_plan->best_positions= + (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) || + spl_opt_info->plan_cache.push_back(spl_plan)) + { + reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, + best_key, remaining_tables, false); + return 0; + } + spl_plan->keyuse_ext_start= best_key_keyuse_ext_start; spl_plan->table= best_table; spl_plan->key= best_key;

1 0

[Commits] bb28bff: Ported the test case for MDEV-25682 from 10.2
by IgorBabaev 08 Jun '21

08 Jun '21

revision-id: bb28bffc3ed179a9912aced2b873e43999111887 (mariadb-10.4.11-642-gbb28bff) parent(s): ddddfc33c7825609a25ce9531183a0b0fb97f206 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-07 19:51:57 -0700 message: Ported the test case for MDEV-25682 from 10.2 No fix for this bug is needed starting from version 10.4. --- mysql-test/main/order_by.result | 25 +++++++++++++++++++++++++ mysql-test/main/order_by.test | 16 ++++++++++++++++ 2 files changed, 41 insertions(+) diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 826daf0..129bd89 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -3536,6 +3536,31 @@ SET max_length_for_sort_data=@save_max_length_for_sort_data; SET max_sort_length= @save_max_sort_length; SET sql_select_limit= @save_sql_select_limit; DROP TABLE t1; +# +# MDEV-25682: EXPLAIN for SELECT with ORDER BY after [ORDER BY] LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +explain (select a from t1 limit 2) order by a desc; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using filesort +2 DERIVED t1 ALL NULL NULL NULL NULL 3 +(select a from t1 limit 2) order by a desc; +a +7 +3 +create table t2 (a int, b int); +insert into t2 values (3,70), (7,10), (1,40), (4,30); +explain (select b,a from t2 order by a limit 3) order by b desc; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 4 Using filesort +(select b,a from t2 order by a limit 3) order by b desc; +b a +70 3 +40 1 +30 4 +drop table t1,t2; # End of 10.2 tests # # MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index 1bf353f..0bf0311 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2294,6 +2294,22 @@ SET max_sort_length= @save_max_sort_length; SET sql_select_limit= @save_sql_select_limit; DROP TABLE t1; +--echo # +--echo # MDEV-25682: EXPLAIN for SELECT with ORDER BY after [ORDER BY] LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); +explain (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; + +create table t2 (a int, b int); +insert into t2 values (3,70), (7,10), (1,40), (4,30); +explain (select b,a from t2 order by a limit 3) order by b desc; +(select b,a from t2 order by a limit 3) order by b desc; + +drop table t1,t2; + --echo # End of 10.2 tests --echo #

1 0

[Commits] 8149e4d: MDEV-25682 Explain shows an execution plan different from actually executed
by IgorBabaev 07 Jun '21

07 Jun '21

revision-id: 8149e4d0a139b901c8902b5b9fae371cef47275f (mariadb-10.3.26-174-g8149e4d) parent(s): b1b4d67bcda32472f5b9c46465bff9db86904a00 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-07 15:08:18 -0700 message: MDEV-25682 Explain shows an execution plan different from actually executed If a select query contained an ORDER BY clause that followed a LIMIT clause or an ORDER BY clause or ORDER BY with LIMIT the EXPLAIN output for the query showed an execution plan different from that was actually executed. Approved by Roman Nozdrin <roman.nozdrin(a)mariadb.com> --- mysql-test/main/order_by.result | 25 +++++++++++++++++++++++++ mysql-test/main/order_by.test | 16 ++++++++++++++++ sql/item_subselect.cc | 2 ++ sql/sql_select.cc | 2 +- 4 files changed, 44 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index f4e88d6..c8f63f8 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -3460,6 +3460,31 @@ SET max_length_for_sort_data=@save_max_length_for_sort_data; SET max_sort_length= @save_max_sort_length; SET sql_select_limit= @save_sql_select_limit; DROP TABLE t1; +# +# MDEV-25682: EXPLAIN for SELECT with ORDER BY after [ORDER BY] LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +explain (select a from t1 limit 2) order by a desc; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL Using filesort +(select a from t1 limit 2) order by a desc; +a +7 +3 +create table t2 (a int, b int); +insert into t2 values (3,70), (7,10), (1,40), (4,30); +explain (select b,a from t2 order by a limit 3) order by b desc; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using filesort +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL Using filesort +(select b,a from t2 order by a limit 3) order by b desc; +b a +70 3 +40 1 +30 4 +drop table t1,t2; # End of 10.2 tests # # MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index 7488414..08d5982 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2293,6 +2293,22 @@ SET max_sort_length= @save_max_sort_length; SET sql_select_limit= @save_sql_select_limit; DROP TABLE t1; +--echo # +--echo # MDEV-25682: EXPLAIN for SELECT with ORDER BY after [ORDER BY] LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); +explain (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; + +create table t2 (a int, b int); +insert into t2 values (3,70), (7,10), (1,40), (4,30); +explain (select b,a from t2 order by a limit 3) order by b desc; +(select b,a from t2 order by a limit 3) order by b desc; + +drop table t1,t2; + --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 9f43561..352d80da9 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -274,6 +274,8 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref) res= TRUE; goto end; } + if (sl == unit->first_select() && !sl->next_select()) + unit->fake_select_lex= 0; } } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6c090ea..7f4c6d2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26370,7 +26370,7 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result) sl->options|= SELECT_DESCRIBE; } - if (unit->is_unit_op()) + if (unit->is_unit_op() || unit->fake_select_lex) { if (unit->union_needs_tmp_table() && unit->fake_select_lex) {

1 0

[Commits] 9c51cfa: MDEV-25714 Join using derived with aggregation returns incorrect results
by IgorBabaev 07 Jun '21

07 Jun '21

revision-id: 9c51cfad0a70e0eab2653e5af69d40cbd8ff3e1f (mariadb-10.4.5-1158-g9c51cfa) parent(s): 07a7a92c2647a7bcfee52ac440588dafa36ba38f author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-07 13:25:53 -0700 message: MDEV-25714 Join using derived with aggregation returns incorrect results If a join query uses a derived table (view / CTE) with GROUP BY clause then the execution plan for such join may employ split optimization. When this optimization is employed the derived table is not materialized. Rather only some partitions of the derived table are subject to grouping. Split optimization can be applied only if: - there are some indexes over the tables used in the join specifying the derived table whose prefixes partially cover the field items used in the GROUP BY list (such indexes are called splitting indexes) - the WHERE condition of the join query contains conjunctive equalities between columns of the derived table that comprise major parts of splitting indexes and columns of the other join tables. When the optimizer evaluates extending of a partial join by the rows of the derived table it always considers a possibility of using split optimization. Different splitting indexes can be used depending on the extended partial join. At some rare conditions, for example, when there is a non-splitting covering index for a table joined in the join specifying the derived table usage of a splitting index to produce rows needed for grouping may be still less beneficial than usage of such covering index without any splitting technique. The function JOIN_TAB::choose_best_splitting() must take this into account. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/derived_cond_pushdown.result | 2 +- mysql-test/main/derived_split_innodb.result | 65 ++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 41 ++++++++++++++++++ sql/opt_split.cc | 27 +++++++++--- 4 files changed, 129 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 7644e65..52d8ccd 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16703,7 +16703,7 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 LATERAL DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index ff71b7d..63db8c9 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -175,3 +175,68 @@ id select_type table type possible_keys key key_len ref rows Extra 3 LATERAL DERIVED t1 ref a,a_2 a 5 test.t1.a 1 Using where; Using temporary; Using filesort 3 LATERAL DERIVED t2 ref c c 5 test.t1.b 1 Using index DROP TABLE t1, t2; +# +# Bug mdev-25714: usage non-splitting covering index is cheaper than +# usage of the best splitting index for one group +# +create table t1 ( +id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( +id int not null, itemid int not null, userid int not null, primary key (id), +index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +set use_stat_tables='never'; +set optimizer_use_condition_selectivity=1; +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +set optimizer_switch='split_materialized=on'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1,idx2 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +set optimizer_switch='split_materialized=off'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; +set use_stat_tables=default; +set optimizer_use_condition_selectivity=default; +# End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 19a6ecf..22793b4 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -150,3 +150,44 @@ eval set statement optimizer_switch='split_materialized=on' for $query; DROP TABLE t1, t2; +--echo # +--echo # Bug mdev-25714: usage non-splitting covering index is cheaper than +--echo # usage of the best splitting index for one group +--echo # + +create table t1 ( + id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( + id int not null, itemid int not null, userid int not null, primary key (id), + index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +set use_stat_tables='never'; +set optimizer_use_condition_selectivity=1; +analyze table t1,t2,t3; + +let $q= +select t1.id, t1.itemid, dt.id, t2.id + from t1, + (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, + t2 + where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; + +set optimizer_switch='split_materialized=on'; +eval explain $q; +eval $q; + +set optimizer_switch='split_materialized=off'; +eval explain $q; +eval $q; + +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; +set use_stat_tables=default; +set optimizer_use_condition_selectivity=default; + +--echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 395422d..d272638 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -960,11 +960,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, in the cache */ spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts); - if (!spl_plan && - (spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) && - (spl_plan->best_positions= - (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) && - !spl_opt_info->plan_cache.push_back(spl_plan)) + if (!spl_plan) { /* The plan for the chosen key has not been found in the cache. @@ -974,6 +970,27 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, best_key, remaining_tables, true); choose_plan(join, all_table_map & ~join->const_table_map); + + /* + Check that the chosen plan is really a splitting plan. + If not or if there is not enough memory to save the plan in the cache + then just return with no splitting plan. + */ + POSITION *first_non_const_pos= join->best_positions + join->const_tables; + TABLE *table= first_non_const_pos->table->table; + key_map spl_keys= table->keys_usable_for_splitting; + if (!(first_non_const_pos->key && + spl_keys.is_set(first_non_const_pos->key->key)) || + !(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) || + !(spl_plan->best_positions= + (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) || + spl_opt_info->plan_cache.push_back(spl_plan)) + { + reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, + best_key, remaining_tables, false); + return 0; + } + spl_plan->keyuse_ext_start= best_key_keyuse_ext_start; spl_plan->table= best_table; spl_plan->key= best_key;

1 0

[Commits] 3a467a2: MDEV-25714 Join using derived with aggregation returns incorrect results
by IgorBabaev 07 Jun '21

07 Jun '21

revision-id: 3a467a23acb1cb1b8329a830e42a517698e12627 (mariadb-10.4.5-1158-g3a467a2) parent(s): 07a7a92c2647a7bcfee52ac440588dafa36ba38f author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-07 12:52:06 -0700 message: MDEV-25714 Join using derived with aggregation returns incorrect results If a join query uses a derived table (view / CTE) with GROUP BY clause then the execution plan for such join may employ split optimization. When this optimization is employed the derived table is not materialized. Rather only some partitions of the derived table are subject to grouping. Split optimization can be applied only if: - there are some indexes over the tables used in the join specifying the derived table whose prefixes partially cover the field items used in the GROUP BY list (such indexes are called splitting indexes) - the WHERE condition of the join query contains conjunctive equalities between columns of the derived table that comprise major parts of splitting indexes and columns of the other join tables. When the optimizer evaluates extending of a partial join by the rows of the derived table it always considers a possibility of using split optimization. Different splitting indexes can be used depending on the extended partial join. At some rare conditions, for example, when there is a non-splitting covering index for a table joined in the join specifying the derived table usage of a splitting index to produce rows needed for grouping may be still less beneficial than usage of such covering index without any splitting technique. The function JOIN_TAB::choose_best_splitting() must take this into account. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/derived_cond_pushdown.result | 2 +- mysql-test/main/derived_split_innodb.result | 61 ++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 37 +++++++++++++++++ sql/opt_split.cc | 27 +++++++++--- 4 files changed, 121 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 7644e65..52d8ccd 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16703,7 +16703,7 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 LATERAL DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index ff71b7d..6119145 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -175,3 +175,64 @@ id select_type table type possible_keys key key_len ref rows Extra 3 LATERAL DERIVED t1 ref a,a_2 a 5 test.t1.a 1 Using where; Using temporary; Using filesort 3 LATERAL DERIVED t2 ref c c 5 test.t1.b 1 Using index DROP TABLE t1, t2; +# +# Bug mdev-25714: usage non-splitting covering index is cheaper than +# usage of the best splitting index for one group +# +create table t1 ( +id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( +id int not null, itemid int not null, userid int not null, primary key (id), +index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +set optimizer_switch='split_materialized=on'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1,idx2 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +set optimizer_switch='split_materialized=off'; +explain select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 ref idx idx 4 test.t2.id 1 +1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 +2 DERIVED t3 ref idx1 idx1 4 const 5 Using where; Using index +select t1.id, t1.itemid, dt.id, t2.id +from t1, +(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, +t2 +where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; +id itemid id id +4 2 4 2 +4 2 4 2 +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; +# End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 19a6ecf..6f33c71 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -150,3 +150,40 @@ eval set statement optimizer_switch='split_materialized=on' for $query; DROP TABLE t1, t2; +--echo # +--echo # Bug mdev-25714: usage non-splitting covering index is cheaper than +--echo # usage of the best splitting index for one group +--echo # + +create table t1 ( + id int not null, itemid int not null, index idx (itemid) +) engine=innodb; +insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3); +create table t2 (id int not null) engine=innodb; +insert into t2 values (2); +create table t3 ( + id int not null, itemid int not null, userid int not null, primary key (id), + index idx1 (userid, itemid), index idx2 (itemid) +) engine innodb; +insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1); +analyze table t1,t2,t3; + +let $q= +select t1.id, t1.itemid, dt.id, t2.id + from t1, + (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, + t2 + where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid; + +set optimizer_switch='split_materialized=on'; +eval explain $q; +eval $q; + +set optimizer_switch='split_materialized=off'; +eval explain $q; +eval $q; + +drop table t1,t2,t3; +set optimizer_switch='split_materialized=default'; + +--echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 395422d..d272638 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -960,11 +960,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, in the cache */ spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts); - if (!spl_plan && - (spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) && - (spl_plan->best_positions= - (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) && - !spl_opt_info->plan_cache.push_back(spl_plan)) + if (!spl_plan) { /* The plan for the chosen key has not been found in the cache. @@ -974,6 +970,27 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, best_key, remaining_tables, true); choose_plan(join, all_table_map & ~join->const_table_map); + + /* + Check that the chosen plan is really a splitting plan. + If not or if there is not enough memory to save the plan in the cache + then just return with no splitting plan. + */ + POSITION *first_non_const_pos= join->best_positions + join->const_tables; + TABLE *table= first_non_const_pos->table->table; + key_map spl_keys= table->keys_usable_for_splitting; + if (!(first_non_const_pos->key && + spl_keys.is_set(first_non_const_pos->key->key)) || + !(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) || + !(spl_plan->best_positions= + (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) || + spl_opt_info->plan_cache.push_back(spl_plan)) + { + reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, + best_key, remaining_tables, false); + return 0; + } + spl_plan->keyuse_ext_start= best_key_keyuse_ext_start; spl_plan->table= best_table; spl_plan->key= best_key;

1 0