- commits - lists.mariadb.org

[Commits] d9c44fd063f: Update RocksDB: TEST PATCH: Range Locking uses memcmp() comparator.
by psergey 14 Jun '21

14 Jun '21

revision-id: d9c44fd063f7f537f67d9830b6d84891e3a8dc1e (percona-202102-59-gd9c44fd063f) parent(s): 4ae6cdd9954ad7ee4438785c076c93aa3a67c6ef author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-14 22:26:10 +0300 message: Update RocksDB: TEST PATCH: Range Locking uses memcmp() comparator. --- rocksdb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/rocksdb b/rocksdb index c0a4e59baa6..56846855b35 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit c0a4e59baa6e46f6e80d6ba049058b367c3a5c94 +Subproject commit 56846855b351a199d707e1b44c2d5f70e77d5535

1 0

[Commits] 56846855b: TEST PATCH: Range Locking uses memcmp() comparator.
by psergey 14 Jun '21

14 Jun '21

revision-id: 56846855b351a199d707e1b44c2d5f70e77d5535 (v5.8-3395-g56846855b) parent(s): c0a4e59baa6e46f6e80d6ba049058b367c3a5c94 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-14 22:25:34 +0300 message: TEST PATCH: Range Locking uses memcmp() comparator. --- .../lock/range/range_tree/lib/ft/comparator.h | 26 ++++++++++++++++++++++ 1 file changed, 26 insertions(+) diff --git a/utilities/transactions/lock/range/range_tree/lib/ft/comparator.h b/utilities/transactions/lock/range/range_tree/lib/ft/comparator.h index 718efc623..d337eb566 100644 --- a/utilities/transactions/lock/range/range_tree/lib/ft/comparator.h +++ b/utilities/transactions/lock/range/range_tree/lib/ft/comparator.h @@ -68,6 +68,27 @@ int toku_builtin_compare_fun(const DBT *, const DBT *) namespace toku { +inline int toku_keycompare2(const void *key1, size_t key1len, const void *key2, + size_t key2len) { + size_t comparelen = key1len < key2len ? key1len : key2len; + int c = memcmp(key1, key2, comparelen); + if (__builtin_expect(c != 0, 1)) { + return c; + } else { + if (key1len < key2len) { + return -1; + } else if (key1len > key2len) { + return 1; + } else { + return 0; + } + } +} + +inline int toku_builtin_compare_fun2(const DBT *a, const DBT *b) { + return toku_keycompare(a->data, a->size, b->data, b->size); +} + // a comparator object encapsulates the data necessary for // comparing two keys in a fractal tree. it further understands // that points may be positive or negative infinity. @@ -115,6 +136,7 @@ class comparator { if (__builtin_expect(toku_dbt_is_infinite(a) || toku_dbt_is_infinite(b), 0)) { return toku_dbt_infinite_compare(a, b); +#if 0 } else if (_memcmp_magic != MEMCMP_MAGIC_NONE // If `a' has the memcmp magic.. && dbt_has_memcmp_magic(a) @@ -126,6 +148,10 @@ class comparator { // yikes, const sadness here return _cmp(_cmp_arg, a, b); } +#endif + } else { + return toku_builtin_compare_fun2(a, b); + } } private:

1 0

[Commits] 4ae6cdd9954: Add rocksdb_range_lock_* counters
by psergey 14 Jun '21

14 Jun '21

revision-id: 4ae6cdd9954ad7ee4438785c076c93aa3a67c6ef (percona-202102-58-g4ae6cdd9954) parent(s): e5fb7e43eb90c1bd19570e93b23e56365af42189 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-14 22:21:36 +0300 message: Add rocksdb_range_lock_* counters --- rocksdb | 2 +- storage/rocksdb/rdb_perf_context.cc | 6 ++++++ storage/rocksdb/rdb_perf_context.h | 3 +++ 3 files changed, 10 insertions(+), 1 deletion(-) diff --git a/rocksdb b/rocksdb index 0f8c041ea7b..c0a4e59baa6 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit 0f8c041ea7bb458caa5ec0dbeef9fa42d0b97482 +Subproject commit c0a4e59baa6e46f6e80d6ba049058b367c3a5c94 diff --git a/storage/rocksdb/rdb_perf_context.cc b/storage/rocksdb/rdb_perf_context.cc index 12621e5f84f..57d740432f3 100644 --- a/storage/rocksdb/rdb_perf_context.cc +++ b/storage/rocksdb/rdb_perf_context.cc @@ -89,6 +89,9 @@ std::string rdb_pc_stat_types[] = { "BLOOM_SST_MISS_COUNT", "KEY_LOCK_WAIT_TIME", "KEY_LOCK_WAIT_COUNT", + "RANGE_LOCK_LOCKS", + "RANGE_LOCK_UNLOCKS", + "RANGE_LOCK_MUTEX_LOCKS", "IO_THREAD_POOL_ID", "IO_BYTES_WRITTEN", "IO_BYTES_READ", @@ -166,6 +169,9 @@ static void harvest_diffs(Rdb_atomic_perf_counters *const counters) { IO_PERF_RECORD(key_lock_wait_time); IO_PERF_RECORD(key_lock_wait_count); + IO_PERF_RECORD(range_lock_locks); + IO_PERF_RECORD(range_lock_unlocks); + IO_PERF_RECORD(range_lock_mutex_locks); if (rocksdb::get_iostats_context() != nullptr) { IO_STAT_RECORD(thread_pool_id); IO_STAT_RECORD(bytes_written); diff --git a/storage/rocksdb/rdb_perf_context.h b/storage/rocksdb/rdb_perf_context.h index c8718e77a00..917a97f9641 100644 --- a/storage/rocksdb/rdb_perf_context.h +++ b/storage/rocksdb/rdb_perf_context.h @@ -78,6 +78,9 @@ enum { PC_BLOOM_SST_MISS_COUNT, PC_KEY_LOCK_WAIT_TIME, PC_KEY_LOCK_WAIT_COUNT, + PC_RANGE_LOCK_LOCKS, + PC_RANGE_LOCK_UNLOCKS, + PC_RANGE_LOCK_MUTEX_LOCKS, PC_IO_THREAD_POOL_ID, PC_IO_BYTES_WRITTEN, PC_IO_BYTES_READ,

1 0

[Commits] e5fb7e43eb9: Re-point rocksdb to spetrunia/rocksdb
by psergey 14 Jun '21

14 Jun '21

revision-id: e5fb7e43eb90c1bd19570e93b23e56365af42189 (percona-202102-57-ge5fb7e43eb9) parent(s): c89e6b3453349616bbe5b810a0ba2cb060625889 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-14 22:21:17 +0300 message: Re-point rocksdb to spetrunia/rocksdb --- .gitmodules | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/.gitmodules b/.gitmodules index 79d0578b5e8..8844b19fbdc 100644 --- a/.gitmodules +++ b/.gitmodules @@ -4,7 +4,7 @@ ignore = dirty [submodule "rocksdb"] path = rocksdb - url = https://github.com/facebook/rocksdb.git + url = https://github.com/spetrunia/rocksdb.git [submodule "rqg"] path = rqg url = https://github.com/yoshinorim/rqg.git

1 0

[Commits] c0a4e59ba: Add Range Locking counters to PerfContext
by psergey 14 Jun '21

14 Jun '21

revision-id: c0a4e59baa6e46f6e80d6ba049058b367c3a5c94 (v5.8-3394-gc0a4e59ba) parent(s): 1ec5d0e1234f8deda4d135e2d48d0ed7406ea513 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-14 22:18:19 +0300 message: Add Range Locking counters to PerfContext Add range_lock_{locks,unlocks,mutex_locks} counters. --- include/rocksdb/perf_context.h | 5 +++++ monitoring/perf_context.cc | 16 ++++++++++++++++ .../lock/range/range_tree/lib/locktree/treenode.cc | 12 +++++++++++- .../lock/range/range_tree/range_tree_lock_manager.cc | 5 +++++ .../lock/range/range_tree/range_tree_lock_tracker.cc | 1 + 5 files changed, 38 insertions(+), 1 deletion(-) diff --git a/include/rocksdb/perf_context.h b/include/rocksdb/perf_context.h index 3d61000cc..ddc551944 100644 --- a/include/rocksdb/perf_context.h +++ b/include/rocksdb/perf_context.h @@ -193,6 +193,11 @@ struct PerfContext { // number of times acquiring a lock was blocked by another transaction. uint64_t key_lock_wait_count; + uint64_t range_lock_locks; + uint64_t range_lock_unlocks; + uint64_t range_lock_mutex_locks; + + // Total time spent in Env filesystem operations. These are only populated // when TimedEnv is used. uint64_t env_new_sequential_file_nanos; diff --git a/monitoring/perf_context.cc b/monitoring/perf_context.cc index 53f502405..84ef0dd43 100644 --- a/monitoring/perf_context.cc +++ b/monitoring/perf_context.cc @@ -97,6 +97,10 @@ PerfContext::PerfContext(const PerfContext& other) { key_lock_wait_time = other.key_lock_wait_time; key_lock_wait_count = other.key_lock_wait_count; + range_lock_locks= other.range_lock_locks; + range_lock_unlocks= other.range_lock_unlocks; + range_lock_mutex_locks= other.range_lock_mutex_locks; + env_new_sequential_file_nanos = other.env_new_sequential_file_nanos; env_new_random_access_file_nanos = other.env_new_random_access_file_nanos; env_new_writable_file_nanos = other.env_new_writable_file_nanos; @@ -194,6 +198,10 @@ PerfContext::PerfContext(PerfContext&& other) noexcept { key_lock_wait_time = other.key_lock_wait_time; key_lock_wait_count = other.key_lock_wait_count; + range_lock_locks= other.range_lock_locks; + range_lock_unlocks= other.range_lock_unlocks; + range_lock_mutex_locks= other.range_lock_mutex_locks; + env_new_sequential_file_nanos = other.env_new_sequential_file_nanos; env_new_random_access_file_nanos = other.env_new_random_access_file_nanos; env_new_writable_file_nanos = other.env_new_writable_file_nanos; @@ -293,6 +301,10 @@ PerfContext& PerfContext::operator=(const PerfContext& other) { key_lock_wait_time = other.key_lock_wait_time; key_lock_wait_count = other.key_lock_wait_count; + range_lock_locks= other.range_lock_locks; + range_lock_unlocks= other.range_lock_unlocks; + range_lock_mutex_locks= other.range_lock_mutex_locks; + env_new_sequential_file_nanos = other.env_new_sequential_file_nanos; env_new_random_access_file_nanos = other.env_new_random_access_file_nanos; env_new_writable_file_nanos = other.env_new_writable_file_nanos; @@ -389,6 +401,10 @@ void PerfContext::Reset() { key_lock_wait_time = 0; key_lock_wait_count = 0; + range_lock_locks= 0; + range_lock_unlocks= 0; + range_lock_mutex_locks= 0; + env_new_sequential_file_nanos = 0; env_new_random_access_file_nanos = 0; env_new_writable_file_nanos = 0; diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc index 8997f634b..bcdaa672f 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc @@ -57,11 +57,21 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. #include "../portability/toku_race_tools.h" +namespace rocksdb { + +void increment_mutex_lock_counter(); + +} + namespace toku { // TODO: source location info might have to be pulled up one caller // to be useful -void treenode::mutex_lock(void) { toku_mutex_lock(&m_mutex); } +void treenode::mutex_lock(void) { + + rocksdb::increment_mutex_lock_counter(); + toku_mutex_lock(&m_mutex); +} void treenode::mutex_unlock(void) { toku_mutex_unlock(&m_mutex); } 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 55a66bc59..1c07f0992 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 @@ -47,6 +47,10 @@ void serialize_endpoint(const Endpoint& endp, std::string* buf) { buf->append(endp.slice.data(), endp.slice.size()); } +void increment_mutex_lock_counter() { + PERF_COUNTER_ADD(range_lock_mutex_locks, 1); +} + // Decode the endpoint from the format it is stored in the locktree (DBT) to // one used outside (EndpointWithString) void deserialize_endpoint(const DBT* dbt, EndpointWithString* endp) { @@ -79,6 +83,7 @@ Status RangeTreeLockManager::TryLock(PessimisticTransaction* txn, auto lt = GetLockTreeForCF(column_family_id); + PERF_COUNTER_ADD(range_lock_locks, 1); // Put the key waited on into request's m_extra. See // wait_callback_for_locktree for details. std::string wait_key(start_endp.slice.data(), start_endp.slice.size()); diff --git a/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.cc b/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.cc index be1e1478b..d138ed91f 100644 --- a/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.cc +++ b/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.cc @@ -112,6 +112,7 @@ void RangeLockList::ReleaseLocks(RangeTreeLockManager *mgr, if (it.second->get_num_ranges()) { auto lt_ptr = mgr->GetLockTreeForCF(it.first); toku::locktree *lt = lt_ptr.get(); + PERF_COUNTER_ADD(range_lock_unlocks, it.second->get_num_ranges()); lt->release_locks((TXNID)txn, it.second.get(), all_trx_locks);

1 0

[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