revision-id: 96ee9ea02e69fb45f369815ace2187dd73398ac4 (mariadb-5.5.64-17-g96ee9ea)
parent(s): 6db2ebbb2a63994ef2b43d42a11dbacb8b55c207
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-06-10 22:38:55 -0700
message:
MDEV-18479 Another complement
This patch complements the patch that fixes bug MDEV-18479.
This patch takes care of possible overflow in JOIN::get_examined_rows().
---
mysql-test/r/derived_view.result | 18 +++++++++---------
sql/sql_select.cc | 17 ++++++++++-------
2 files changed, 19 insertions(+), 16 deletions(-)
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index d74b532..f8cf919 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2942,15 +2942,15 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived9> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived10> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived11> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived12> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived13> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived15> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived16> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived7> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived8> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived9> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived10> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived11> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived12> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived13> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived15> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived16> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived7> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived8> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join)
17 DERIVED t2 system NULL NULL NULL NULL 1
17 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
17 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d7ff92a..a273aae 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -6894,17 +6894,22 @@ double JOIN::get_examined_rows()
{
ha_rows examined_rows;
double prev_fanout= 1;
+ double records;
JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS);
JOIN_TAB *prev_tab= tab;
- examined_rows= tab->get_examined_rows();
+ records= tab->get_examined_rows();
while ((tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab)))
{
- prev_fanout *= prev_tab->records_read;
- examined_rows+= (ha_rows) (tab->get_examined_rows() * prev_fanout);
+ prev_fanout= COST_MULT(prev_fanout, prev_tab->records_read);
+ records=
+ COST_ADD(records,
+ COST_MULT((double) (tab->get_examined_rows()), prev_fanout));
prev_tab= tab;
}
+ examined_rows=
+ records > (double) HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records;
return examined_rows;
}
@@ -10824,7 +10829,7 @@ ha_rows JOIN_TAB::get_examined_rows()
}
}
else
- examined_rows= (ha_rows) records_read;
+ examined_rows= records_read;
return examined_rows;
}
@@ -22987,9 +22992,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
else
{
ha_rows examined_rows= tab->get_examined_rows();
- ha_rows displ_rows= examined_rows;
- set_if_smaller(displ_rows, HA_ROWS_MAX/2);
- item_list.push_back(new Item_int((longlong) (ulonglong) displ_rows,
+ item_list.push_back(new Item_int((ulonglong) examined_rows,
MY_INT64_NUM_DECIMAL_DIGITS));
/* Add "filtered" field to item_list. */
1
0
revision-id: c8ac1b1fefdf1eae17c0c6942ba269bb9fae46a9 (mariadb-5.5.64-17-gc8ac1b1)
parent(s): 6db2ebbb2a63994ef2b43d42a11dbacb8b55c207
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-06-10 22:37:04 -0700
message:
MDEV-18479 Anothe complement
This patch complements the patch that fixes bug MDEV-18479.
This patch takes care of possible overflow in JOIN::get_examined_rows().
---
mysql-test/r/derived_view.result | 18 +++++++++---------
sql/sql_select.cc | 17 ++++++++++-------
2 files changed, 19 insertions(+), 16 deletions(-)
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index d74b532..f8cf919 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2942,15 +2942,15 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived9> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived10> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived11> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived12> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived13> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived15> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived16> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived7> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived8> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived9> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived10> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived11> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived12> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived13> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived15> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived16> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived7> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived8> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join)
17 DERIVED t2 system NULL NULL NULL NULL 1
17 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
17 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d7ff92a..a273aae 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -6894,17 +6894,22 @@ double JOIN::get_examined_rows()
{
ha_rows examined_rows;
double prev_fanout= 1;
+ double records;
JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS);
JOIN_TAB *prev_tab= tab;
- examined_rows= tab->get_examined_rows();
+ records= tab->get_examined_rows();
while ((tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab)))
{
- prev_fanout *= prev_tab->records_read;
- examined_rows+= (ha_rows) (tab->get_examined_rows() * prev_fanout);
+ prev_fanout= COST_MULT(prev_fanout, prev_tab->records_read);
+ records=
+ COST_ADD(records,
+ COST_MULT((double) (tab->get_examined_rows()), prev_fanout));
prev_tab= tab;
}
+ examined_rows=
+ records > (double) HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records;
return examined_rows;
}
@@ -10824,7 +10829,7 @@ ha_rows JOIN_TAB::get_examined_rows()
}
}
else
- examined_rows= (ha_rows) records_read;
+ examined_rows= records_read;
return examined_rows;
}
@@ -22987,9 +22992,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
else
{
ha_rows examined_rows= tab->get_examined_rows();
- ha_rows displ_rows= examined_rows;
- set_if_smaller(displ_rows, HA_ROWS_MAX/2);
- item_list.push_back(new Item_int((longlong) (ulonglong) displ_rows,
+ item_list.push_back(new Item_int((ulonglong) examined_rows,
MY_INT64_NUM_DECIMAL_DIGITS));
/* Add "filtered" field to item_list. */
1
0
revision-id: c91095e08fbdacb69831a93ab403a80487dee435 (v5.8-1042-gc91095e08)
parent(s): 1b423e3954d932c4624337308e3e1cd98481a495
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-06-10 23:11:27 +0300
message:
Range Locking: address review input
---
include/rocksdb/utilities/transaction_db.h | 26 ++++++++++++++++++----
utilities/transactions/pessimistic_transaction.cc | 4 +---
.../transactions/pessimistic_transaction_db.cc | 8 +++----
utilities/transactions/transaction_lock_mgr.h | 3 +++
4 files changed, 29 insertions(+), 12 deletions(-)
diff --git a/include/rocksdb/utilities/transaction_db.h b/include/rocksdb/utilities/transaction_db.h
index 7ebac3e06..84761c80e 100644
--- a/include/rocksdb/utilities/transaction_db.h
+++ b/include/rocksdb/utilities/transaction_db.h
@@ -33,9 +33,26 @@ enum TxnDBWritePolicy {
const uint32_t kInitialMaxDeadlocks = 5;
+class BaseLockMgr;
+
+// A lock manager handle
+// The workflow is as follows:
+// * Use a factory method (like NewRangeLockManager()) to create a lock
+// manager and get its handle.
+// * A Handle for a particular kind of lock manager will have extra
+// methods and parameters to control the lock manager
+// * Pass the handle to RocksDB in TransactionDBOptions::lock_mgr_handle. It
+// will be used to perform locking.
+class LockManagerHandle {
+ public:
+ // Get the underlying LockManager. To be used by RocksDB Internals
+ virtual BaseLockMgr* GetManager()=0;
+ virtual ~LockManagerHandle(){};
+};
+
// A handle to control RangeLockMgr (Range-based lock manager) from outside
// RocksDB
-class RangeLockMgrHandle {
+class RangeLockMgrHandle : public LockManagerHandle {
public:
virtual int set_max_lock_memory(size_t max_lock_memory) = 0;
virtual uint64_t get_escalation_count() = 0;
@@ -44,7 +61,7 @@ class RangeLockMgrHandle {
// A factory function to create a Range Lock Manager. The created object should
// be:
-// 1. Passed in TransactionDBOptions::range_lock_mgr to open the database in
+// 1. Passed in TransactionDBOptions::lock_mgr_handle to open the database in
// range-locking mode
// 2. Used to control the lock manager when the DB is already open.
RangeLockMgrHandle* NewRangeLockManager(
@@ -112,8 +129,9 @@ struct TransactionDBOptions {
// for the special way that myrocks uses this operands.
bool rollback_merge_operands = false;
- // If non-null, range locking should be used, and the lock manager is passed.
- std::shared_ptr<RangeLockMgrHandle> range_lock_mgr;
+ // nullptr means use default lock manager.
+ // Other value means the user provides a custom lock manager.
+ std::shared_ptr<LockManagerHandle> lock_mgr_handle;
};
struct TransactionOptions {
diff --git a/utilities/transactions/pessimistic_transaction.cc b/utilities/transactions/pessimistic_transaction.cc
index 082934e75..4c0578444 100644
--- a/utilities/transactions/pessimistic_transaction.cc
+++ b/utilities/transactions/pessimistic_transaction.cc
@@ -537,8 +537,6 @@ Status PessimisticTransaction::TryLock(ColumnFamilyHandle* column_family,
// an upgrade.
if (!previously_locked || lock_upgrade) {
s = txn_db_impl_->TryLock(this, cfh_id, key_str, exclusive);
- if (!s.ok())
- return s;
}
SetSnapshotIfNeeded();
@@ -569,7 +567,7 @@ Status PessimisticTransaction::TryLock(ColumnFamilyHandle* column_family,
// since the snapshot. This must be done after we locked the key.
// If we already have validated an earilier snapshot it must has been
// reflected in tracked_at_seq and ValidateSnapshot will return OK.
- if (s.ok()) { //psergey-todo: this check seems to be meaningless, s.ok()==true always
+ if (s.ok()) {
s = ValidateSnapshot(column_family, key, &tracked_at_seq);
if (!s.ok()) {
diff --git a/utilities/transactions/pessimistic_transaction_db.cc b/utilities/transactions/pessimistic_transaction_db.cc
index 14b82de00..bacf95479 100644
--- a/utilities/transactions/pessimistic_transaction_db.cc
+++ b/utilities/transactions/pessimistic_transaction_db.cc
@@ -42,12 +42,10 @@ PessimisticTransactionDB::PessimisticTransactionDB(
void PessimisticTransactionDB::init_lock_manager() {
- if (txn_db_options_.range_lock_mgr) {
+ if (txn_db_options_.lock_mgr_handle) {
// A custom lock manager was provided in options
- std::shared_ptr<RangeLockMgr> tmp;
- tmp = std::static_pointer_cast<RangeLockMgr>(txn_db_options_.range_lock_mgr);
- lock_mgr_= tmp;
- range_lock_mgr_ = static_cast<RangeLockMgr*>(lock_mgr_.get());
+ lock_mgr_.reset(txn_db_options_.lock_mgr_handle->GetManager());
+ range_lock_mgr_ = dynamic_cast<RangeLockMgr*>(lock_mgr_.get());
} else {
// Use point lock manager by default
std::shared_ptr<TransactionDBMutexFactory> mutex_factory =
diff --git a/utilities/transactions/transaction_lock_mgr.h b/utilities/transactions/transaction_lock_mgr.h
index 608d6f34f..54ae77a52 100644
--- a/utilities/transactions/transaction_lock_mgr.h
+++ b/utilities/transactions/transaction_lock_mgr.h
@@ -243,6 +243,9 @@ class RangeLockMgr :
LockStatusData GetLockStatusData() override;
+ BaseLockMgr* GetManager() override {
+ return this;
+ }
private:
toku::locktree_manager ltm_;
1
0
047c7b119f4: MDEV-17603 REPLACE and INSERT…ON DUPLICATE KEY UPDATE are deadlock-prone in statement-based replication
by sachin.setiya＠mariadb.com 10 Jun '19
by sachin.setiya＠mariadb.com 10 Jun '19
10 Jun '19
revision-id: 047c7b119f498856918069540a741fb9fee279de (mariadb-10.1.39-56-g047c7b119f4)
parent(s): e7695f95ae714f3168ce953fd022ddfb40f03e67
author: Sachin
committer: Sachin
timestamp: 2019-06-11 00:09:31 +0530
message:
MDEV-17603 REPLACE and INSERT…ON DUPLICATE KEY UPDATE are deadlock-prone in statement-based replication
Make INSERT...ON DUPLICATE KEY UPDATE unsafe while using stmt or mixed format
When there is more then one unique key.
Although there is two exception.
1. Auto Increment key is not counted because Innodb will get gap lock for
failed Insert and concurrent insert will get a next increment value.
2. Count only unique keys for which insertion is performed.
---
.../suite/rpl/r/rpl_known_bugs_detection.result | 29 +++++++++++------
.../suite/rpl/t/rpl_known_bugs_detection.test | 20 +++++++-----
sql/sql_class.h | 28 ++++++++---------
sql/sql_insert.cc | 36 ++++++++++++++++++++++
4 files changed, 81 insertions(+), 32 deletions(-)
diff --git a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result
index ea738b710fd..9aa681bdc85 100644
--- a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result
+++ b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result
@@ -2,18 +2,27 @@ call mtr.add_suppression("Unsafe statement written to the binary log using state
include/master-slave.inc
[connection master]
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
-CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
-UNIQUE(b));
-INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT,
+UNIQUE(b), c int) engine=innodb;
+INSERT INTO t1 VALUES (1, 1, 1);
+BEGIN;
+INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
+Warnings:
+Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe
+INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
+Warnings:
+Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe
+COMMIT;
SELECT * FROM t1;
-a b
-1 10
-2 2
-call mtr.add_suppression("Slave SQL.*suffer.*http:..bugs.mysql.com.bug.php.id=24432");
-include/wait_for_slave_sql_error.inc [errno=1105]
-Last_SQL_Error = 'Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'test'. Query: 'INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10''
+a b c
+1 1 2
+2 2 3
+include/wait_for_slave_sql_error.inc [errno=1062]
+Last_SQL_Error = 'Error 'Duplicate entry '1' for key 'b'' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c)''
SELECT * FROM t1;
-a b
+a b c
+1 1 1
+2 2 3
stop slave;
include/wait_for_slave_to_stop.inc
reset slave;
diff --git a/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test b/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test
index ab263ece407..75fb59e7a21 100644
--- a/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test
+++ b/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test
@@ -6,6 +6,7 @@
call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
source include/have_debug.inc;
+source include/have_innodb.inc;
# because of pretend_version_50034_in_binlog the test can't run with checksum
source include/have_binlog_checksum_off.inc;
@@ -25,19 +26,22 @@ call mtr.add_suppression("Unsafe statement written to the binary log using state
#
# testcase with INSERT VALUES
-CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
-UNIQUE(b));
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT,
+UNIQUE(b), c int) engine=innodb;
sync_slave_with_master;
connection master;
-INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
+INSERT INTO t1 VALUES (1, 1, 1);
+BEGIN;
+INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
+ --connection master1
+ INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
+--connection master
+COMMIT;
SELECT * FROM t1;
-connection slave;
-
+--connection slave
# show the error message
-#1105 = ER_UNKNOWN_ERROR
---let $slave_sql_errno= 1105
+--let $slave_sql_errno= 1062
--let $show_slave_sql_error= 1
-call mtr.add_suppression("Slave SQL.*suffer.*http:..bugs.mysql.com.bug.php.id=24432");
--source include/wait_for_slave_sql_error.inc
# show that it was not replicated
SELECT * FROM t1;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 1cb516c0656..24545e2e140 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -2208,6 +2208,20 @@ class THD :public Statement,
/* container for handler's private per-connection data */
Ha_data ha_data[MAX_HA];
+ /**
+ Bit field for the state of binlog warnings.
+
+ The first Lex::BINLOG_STMT_UNSAFE_COUNT bits list all types of
+ unsafeness that the current statement has.
+
+ This must be a member of THD and not of LEX, because warnings are
+ detected and issued in different places (@c
+ decide_logging_format() and @c binlog_query(), respectively).
+ Between these calls, the THD->lex object may change; e.g., if a
+ stored routine is invoked. Only THD persists between the calls.
+ */
+ uint32 binlog_unsafe_warning_flags;
+
#ifndef MYSQL_CLIENT
binlog_cache_mngr * binlog_setup_trx_data();
@@ -2317,20 +2331,6 @@ class THD :public Statement,
*/
enum_binlog_format current_stmt_binlog_format;
- /**
- Bit field for the state of binlog warnings.
-
- The first Lex::BINLOG_STMT_UNSAFE_COUNT bits list all types of
- unsafeness that the current statement has.
-
- This must be a member of THD and not of LEX, because warnings are
- detected and issued in different places (@c
- decide_logging_format() and @c binlog_query(), respectively).
- Between these calls, the THD->lex object may change; e.g., if a
- stored routine is invoked. Only THD persists between the calls.
- */
- uint32 binlog_unsafe_warning_flags;
-
/*
Number of outstanding table maps, i.e., table maps in the
transaction cache.
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 0be8abf4842..3d9c91dc060 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -1025,6 +1025,42 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
error= 1;
break;
}
+ /*
+ INSERT...ON DUPLICATE KEY UPDATE on a table with more than one unique keys
+ can be unsafe.
+ */
+ if(thd->wsrep_binlog_format() <= BINLOG_FORMAT_STMT &&
+ !thd->is_current_stmt_binlog_format_row() &&
+ !thd->lex->is_stmt_unsafe() &&
+ thd->lex->sql_command == SQLCOM_INSERT &&
+ thd->lex->duplicates == DUP_UPDATE)
+ {
+ uint unique_keys= 0;
+ uint keys= table->s->keys, i= 0;
+ Field *field;
+ for (KEY* keyinfo= table->s->key_info;
+ i < keys && unique_keys <= 1; i++, keyinfo++)
+ if (keyinfo->flags & HA_NOSAME &&
+ !(keyinfo->key_part->field->flags & AUTO_INCREMENT_FLAG))
+ {
+ for (uint j= 0; j < keyinfo->user_defined_key_parts; j++)
+ {
+ field= keyinfo->key_part[j].field;
+ if(!bitmap_is_set(table->write_set,field->field_index))
+ goto exit;
+ }
+ unique_keys++;
+exit:;
+ }
+
+ if (unique_keys > 1)
+ {
+ thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_INSERT_TWO_KEYS);
+ thd->binlog_unsafe_warning_flags|= thd->lex->get_stmt_unsafe_flags();
+ thd->set_current_stmt_binlog_format_row_if_mixed();
+ }
+
+ }
#ifndef EMBEDDED_LIBRARY
if (lock_type == TL_WRITE_DELAYED)
{
1
0
[Commits] d88e173890f: Range Locking: Limited support for shared point locks: Lock escalation
by Sergei Petrunia 10 Jun '19
by Sergei Petrunia 10 Jun '19
10 Jun '19
revision-id: d88e173890f85c5138067c0dec897cc60566972a (fb-prod201801-235-gd88e173890f)
parent(s): eb6f779acccf4e0f86de98eb894155fa28616cc9
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-06-10 17:40:00 +0300
message:
Range Locking: Limited support for shared point locks: Lock escalation
- Update RocksDB to a revision with it
MyRocks part of it:
- Testcase
- @@rocksdb_max_lock_memory is now a writable global variable
- When in range locking mode, there is 'rocksdb_current_lock_memory'
status variable that shows amount of memory currently used for locks
---
.../rocksdb/include/select_from_rocksdb_locks.inc | 25 ++++-
.../rocksdb/r/range_locking_shared_locks.result | 125 ++++++++++++++++++++-
.../rocksdb/t/range_locking_shared_locks.test | 85 +++++++++++++-
rocksdb | 2 +-
storage/rocksdb/ha_rocksdb.cc | 46 ++++++--
5 files changed, 265 insertions(+), 18 deletions(-)
diff --git a/mysql-test/suite/rocksdb/include/select_from_rocksdb_locks.inc b/mysql-test/suite/rocksdb/include/select_from_rocksdb_locks.inc
index 31280b0510c..79bd261d7b4 100644
--- a/mysql-test/suite/rocksdb/include/select_from_rocksdb_locks.inc
+++ b/mysql-test/suite/rocksdb/include/select_from_rocksdb_locks.inc
@@ -1,3 +1,16 @@
+#
+# An include to print contents of I_S.ROCKSB_LOCKS
+#
+# Implicit "parameters"
+# - Currently it prints locks on t1.PRIMARY
+#
+# Explicit "parameter" variables:
+# - $TRX1_ID - print this transaction as "TRX1"
+# - $TRX2_ID - print this transaction as "TRX2"
+#
+# - $select_from_is_rowlocks_current_trx_only
+# - $order_by_rowkey
+
--echo # select * from information_schema.rocksdb_locks; # With replacements by select_from_rocksdb_locks.inc
--disable_query_log
set @cf_id=(select column_family from information_schema.rocksdb_ddl
@@ -24,12 +37,20 @@ if ($TRX2_ID)
let $transaction_col = replace($transaction_col, '$TRX2_ID', "\$TRX2_ID");
}
---sorted_result
+if ($order_by_rowkey)
+{
+ let $extra_order_by = ORDER BY 3,2;
+}
+
+if (!$order_by_rowkey)
+{
+ --sorted_result
+}
eval select
replace(column_family_id, @cf_id, "\$cf_id") as COLUMN_FAMILY_ID,
$transaction_col as TRANSACTION_ID,
replace(`key`, @indexnr, '\${indexnr}') as `KEY`,
mode
-from information_schema.rocksdb_locks $extra_where;
+from information_schema.rocksdb_locks $extra_where $extra_order_by;
--enable_query_log
diff --git a/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result b/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result
index 014b107aea5..12b6ae9a6ea 100644
--- a/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result
+++ b/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result
@@ -112,8 +112,6 @@ pk a
3 3
4 4
5 5
-# TODO: the following prints an X lock on the range, because GetRangeLock API
-# currently only supports write locks:
# select * from information_schema.rocksdb_locks; # With replacements by select_from_rocksdb_locks.inc
COLUMN_FAMILY_ID TRANSACTION_ID KEY mode
$cf_id $TRX1_ID 0000${indexnr}80000002 S
@@ -126,3 +124,126 @@ $cf_id $TRX2_ID 0000${indexnr}80000384 X
rollback;
disconnect con1;
drop table t0,t1;
+#
+# Test shared point locks and lock escalation
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+pk int primary key,
+a int
+) engine=rocksdb;
+insert into t1
+select 1000 + 100*A.a + 10*B.a + C.a, 12345 from t0 A, t0 B, t0 C;
+show status like 'rocksdb_locktree_current_lock_memory';
+Variable_name Value
+rocksdb_locktree_current_lock_memory 0
+connect con1,localhost,root,,;
+connection con1;
+begin;
+# CON1: get some shared locks
+select * from t1 where pk=1001 lock in share mode;
+pk a
+1001 12345
+select * from t1 where pk=1100 lock in share mode;
+pk a
+1100 12345
+select * from t1 where pk=1200 lock in share mode;
+pk a
+1200 12345
+select * from t1 where pk=2500 lock in share mode;
+pk a
+connection default;
+begin;
+# DEFAULT: get the same locks so we have locks with multiple owners
+select * from t1 where pk=1001 lock in share mode;
+pk a
+1001 12345
+select * from t1 where pk=1100 lock in share mode;
+pk a
+1100 12345
+select * from t1 where pk=1200 lock in share mode;
+pk a
+1200 12345
+# DEFAULT: get shared locks with one owner:
+select * from t1 where pk=2510 lock in share mode;
+pk a
+# DEFAULT: exclusive locks on 0-10:
+insert into t1 select A.a, 0 from t0 A;
+connection con1;
+# CON1: exclusive locks on 2000-2010:
+insert into t1 select 2000+A.a, 0 from t0 A;
+# select * from information_schema.rocksdb_locks; # With replacements by select_from_rocksdb_locks.inc
+COLUMN_FAMILY_ID TRANSACTION_ID KEY mode
+$cf_id $TRX2_ID 0000${indexnr}80000000 X
+$cf_id $TRX2_ID 0000${indexnr}80000001 X
+$cf_id $TRX2_ID 0000${indexnr}80000002 X
+$cf_id $TRX2_ID 0000${indexnr}80000003 X
+$cf_id $TRX2_ID 0000${indexnr}80000004 X
+$cf_id $TRX2_ID 0000${indexnr}80000005 X
+$cf_id $TRX2_ID 0000${indexnr}80000006 X
+$cf_id $TRX2_ID 0000${indexnr}80000007 X
+$cf_id $TRX2_ID 0000${indexnr}80000008 X
+$cf_id $TRX2_ID 0000${indexnr}80000009 X
+$cf_id $TRX1_ID 0000${indexnr}800003e9 S
+$cf_id $TRX2_ID 0000${indexnr}800003e9 S
+$cf_id $TRX1_ID 0000${indexnr}8000044c S
+$cf_id $TRX2_ID 0000${indexnr}8000044c S
+$cf_id $TRX1_ID 0000${indexnr}800004b0 S
+$cf_id $TRX2_ID 0000${indexnr}800004b0 S
+$cf_id $TRX1_ID 0000${indexnr}800007d0 X
+$cf_id $TRX1_ID 0000${indexnr}800007d1 X
+$cf_id $TRX1_ID 0000${indexnr}800007d2 X
+$cf_id $TRX1_ID 0000${indexnr}800007d3 X
+$cf_id $TRX1_ID 0000${indexnr}800007d4 X
+$cf_id $TRX1_ID 0000${indexnr}800007d5 X
+$cf_id $TRX1_ID 0000${indexnr}800007d6 X
+$cf_id $TRX1_ID 0000${indexnr}800007d7 X
+$cf_id $TRX1_ID 0000${indexnr}800007d8 X
+$cf_id $TRX1_ID 0000${indexnr}800007d9 X
+$cf_id $TRX1_ID 0000${indexnr}800009c4 S
+$cf_id $TRX2_ID 0000${indexnr}800009ce S
+connection default;
+show status like 'rocksdb_locktree_current_lock_memory';
+Variable_name Value
+rocksdb_locktree_current_lock_memory 7896
+set @save_mlm= @@rocksdb_max_lock_memory;
+# Set the limit to cause lock escalation:
+set @cur_mem_usage= (select
+variable_value
+from
+information_schema.GLOBAL_STATUS
+where
+variable_name='rocksdb_locktree_current_lock_memory');
+set global rocksdb_max_lock_memory = cast(@cur_mem_usage+4 as SIGNED);
+connection con1;
+insert into t1 select 3000+A.a, 0 from t0 A;
+# select * from information_schema.rocksdb_locks; # With replacements by select_from_rocksdb_locks.inc
+COLUMN_FAMILY_ID TRANSACTION_ID KEY mode
+$cf_id $TRX2_ID 0000${indexnr}80000000 - 0000${indexnr}80000009 X
+$cf_id $TRX1_ID 0000${indexnr}800003e9 S
+$cf_id $TRX2_ID 0000${indexnr}800003e9 S
+$cf_id $TRX1_ID 0000${indexnr}8000044c S
+$cf_id $TRX2_ID 0000${indexnr}8000044c S
+$cf_id $TRX1_ID 0000${indexnr}800004b0 S
+$cf_id $TRX2_ID 0000${indexnr}800004b0 S
+$cf_id $TRX1_ID 0000${indexnr}800007d0 - 0000${indexnr}800007d9 X
+$cf_id $TRX1_ID 0000${indexnr}800009c4 S
+$cf_id $TRX2_ID 0000${indexnr}800009ce S
+$cf_id $TRX1_ID 0000${indexnr}80000bb8 X
+$cf_id $TRX1_ID 0000${indexnr}80000bb9 X
+$cf_id $TRX1_ID 0000${indexnr}80000bba X
+$cf_id $TRX1_ID 0000${indexnr}80000bbb X
+$cf_id $TRX1_ID 0000${indexnr}80000bbc X
+$cf_id $TRX1_ID 0000${indexnr}80000bbd X
+$cf_id $TRX1_ID 0000${indexnr}80000bbe X
+$cf_id $TRX1_ID 0000${indexnr}80000bbf X
+$cf_id $TRX1_ID 0000${indexnr}80000bc0 X
+$cf_id $TRX1_ID 0000${indexnr}80000bc1 X
+connection con1;
+rollback;
+connection default;
+rollback;
+disconnect con1;
+set global rocksdb_max_lock_memory= cast(@save_mlm as SIGNED);
+drop table t0, t1;
diff --git a/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test b/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test
index 52118aa343f..abc437742bb 100644
--- a/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test
+++ b/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test
@@ -110,9 +110,6 @@ select * from t1 where a between 2 and 5 lock in share mode;
select * from t1 where a between 2 and 5 lock in share mode;
let $TRX1_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`;
---echo # TODO: the following prints an X lock on the range, because GetRangeLock API
---echo # currently only supports write locks:
-
--source suite/rocksdb/include/select_from_rocksdb_locks.inc
rollback;
@@ -121,3 +118,85 @@ disconnect con1;
drop table t0,t1;
+--echo #
+--echo # Test shared point locks and lock escalation
+--echo #
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (
+ pk int primary key,
+ a int
+) engine=rocksdb;
+
+insert into t1
+select 1000 + 100*A.a + 10*B.a + C.a, 12345 from t0 A, t0 B, t0 C;
+
+show status like 'rocksdb_locktree_current_lock_memory';
+
+connect (con1,localhost,root,,);
+connection con1;
+
+begin;
+--echo # CON1: get some shared locks
+select * from t1 where pk=1001 lock in share mode;
+select * from t1 where pk=1100 lock in share mode;
+select * from t1 where pk=1200 lock in share mode;
+
+select * from t1 where pk=2500 lock in share mode;
+let $TRX1_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`;
+
+connection default;
+begin;
+--echo # DEFAULT: get the same locks so we have locks with multiple owners
+select * from t1 where pk=1001 lock in share mode;
+select * from t1 where pk=1100 lock in share mode;
+select * from t1 where pk=1200 lock in share mode;
+
+--echo # DEFAULT: get shared locks with one owner:
+select * from t1 where pk=2510 lock in share mode;
+let $TRX2_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`;
+
+
+--echo # DEFAULT: exclusive locks on 0-10:
+insert into t1 select A.a, 0 from t0 A;
+
+connection con1;
+--echo # CON1: exclusive locks on 2000-2010:
+insert into t1 select 2000+A.a, 0 from t0 A;
+
+let $order_by_rowkey=1;
+#select * from information_schema.rocksdb_locks;
+--source suite/rocksdb/include/select_from_rocksdb_locks.inc
+
+connection default;
+show status like 'rocksdb_locktree_current_lock_memory';
+set @save_mlm= @@rocksdb_max_lock_memory;
+
+--echo # Set the limit to cause lock escalation:
+set @cur_mem_usage= (select
+ variable_value
+ from
+ information_schema.GLOBAL_STATUS
+ where
+ variable_name='rocksdb_locktree_current_lock_memory');
+
+set global rocksdb_max_lock_memory = cast(@cur_mem_usage+4 as SIGNED);
+
+connection con1;
+insert into t1 select 3000+A.a, 0 from t0 A;
+
+#select * from information_schema.rocksdb_locks;
+--source suite/rocksdb/include/select_from_rocksdb_locks.inc
+
+connection con1;
+rollback;
+connection default;
+rollback;
+
+disconnect con1;
+set global rocksdb_max_lock_memory= cast(@save_mlm as SIGNED);
+
+drop table t0, t1;
+
+
diff --git a/rocksdb b/rocksdb
index 2f0ee897552..ae4869e880a 160000
--- a/rocksdb
+++ b/rocksdb
@@ -1 +1 @@
-Subproject commit 2f0ee897552bb4a8aa66b933c0d6f8529a82e2e8
+Subproject commit ae4869e880a4118bd912fafe7fbeb6f464c4d893
diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc
index af6bc90ede7..c7a05f581a3 100644
--- a/storage/rocksdb/ha_rocksdb.cc
+++ b/storage/rocksdb/ha_rocksdb.cc
@@ -487,6 +487,10 @@ static void rocksdb_set_max_latest_deadlocks(THD *thd,
struct st_mysql_sys_var *var,
void *var_ptr, const void *save);
+static void rocksdb_set_max_lock_memory(THD *thd,
+ struct st_mysql_sys_var *var,
+ void *var_ptr, const void *save);
+
static void rdb_set_collation_exception_list(const char *exception_list);
static void rocksdb_set_collation_exception_list(THD *thd,
struct st_mysql_sys_var *var,
@@ -595,7 +599,7 @@ static uint32_t rocksdb_max_manual_compactions = 0;
// (note that this is different from rocksdb_max_row_locks as
// that one is a hard per-thread count limit, and this one is a
// global memory limit)
-static ulong rocksdb_max_lock_memory;
+static ulonglong rocksdb_max_lock_memory;
static my_bool rocksdb_use_range_locking = 0;
static std::shared_ptr<rocksdb::RangeLockMgrHandle> range_lock_mgr;
@@ -980,6 +984,13 @@ static MYSQL_SYSVAR_UINT(max_latest_deadlocks, rocksdb_max_latest_deadlocks,
nullptr, rocksdb_set_max_latest_deadlocks,
rocksdb::kInitialMaxDeadlocks, 0, UINT32_MAX, 0);
+static MYSQL_SYSVAR_ULONGLONG(max_lock_memory, rocksdb_max_lock_memory,
+ PLUGIN_VAR_RQCMDARG,
+ "Range-locking mode: Maximum amount of memory "
+ "that locks from all transactions can use at a time",
+ nullptr, rocksdb_set_max_lock_memory,
+ /*initial*/1073741824, 0, UINT64_MAX, 0);
+
static MYSQL_SYSVAR_ENUM(
info_log_level, rocksdb_info_log_level, PLUGIN_VAR_RQCMDARG,
"Filter level for info logs to be written mysqld error log. "
@@ -1715,13 +1726,6 @@ static MYSQL_SYSVAR_BOOL(use_range_locking, rocksdb_use_range_locking,
"Use Range Locking (NEW, incomplete yet)",
nullptr, nullptr, FALSE);
-static MYSQL_SYSVAR_ULONG(
- max_lock_memory, rocksdb_max_lock_memory,
- PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
- "Max Lock Memory when using Range Locking (like in TokuDB)",
- nullptr, nullptr, 1024*1024*1024 /* default value */, 1024 /* min value */,
- ULONG_MAX /* max value */, 0);
-
static const int ROCKSDB_ASSUMED_KEY_VALUE_DISK_SIZE = 100;
static struct st_mysql_sys_var *rocksdb_system_variables[] = {
@@ -5006,7 +5010,7 @@ static int rocksdb_init_func(void *const p) {
{
range_lock_mgr->set_max_lock_memory(rocksdb_max_lock_memory);
sql_print_information("RocksDB: USING NEW RANGE LOCKING");
- sql_print_information("RocksDB: Max lock memory=%lu", rocksdb_max_lock_memory);
+ sql_print_information("RocksDB: Max lock memory=%llu", rocksdb_max_lock_memory);
}
else
sql_print_information("RocksDB: USING POINT LOCKING");
@@ -12932,10 +12936,13 @@ static void show_rocksdb_stall_vars(THD *thd, SHOW_VAR *var, char *buff) {
// psergey: lock tree escalation count status variable.
//
static longlong rocksdb_locktree_escalation_count=1234;
+static longlong rocksdb_locktree_current_lock_memory=0;
static SHOW_VAR rocksdb_locktree_status_variables[] = {
DEF_STATUS_VAR_FUNC("escalation_count",
&rocksdb_locktree_escalation_count, SHOW_LONGLONG),
+ DEF_STATUS_VAR_FUNC("current_lock_memory",
+ &rocksdb_locktree_current_lock_memory, SHOW_LONGLONG),
// end of the array marker
{NullS, NullS, SHOW_LONG}};
@@ -12946,7 +12953,9 @@ static void show_rocksdb_locktree_vars(THD *thd, SHOW_VAR *var, char *buff) {
var->type = SHOW_ARRAY;
if (range_lock_mgr)
{
- rocksdb_locktree_escalation_count= range_lock_mgr->get_escalation_count();
+ auto status = range_lock_mgr->GetStatus();
+ rocksdb_locktree_escalation_count = status.escalation_count;
+ rocksdb_locktree_current_lock_memory = status.current_lock_memory;
var->value = reinterpret_cast<char *>(&rocksdb_locktree_status_variables);
}
else
@@ -13614,6 +13623,23 @@ void rocksdb_set_delayed_write_rate(THD *thd, struct st_mysql_sys_var *var,
RDB_MUTEX_UNLOCK_CHECK(rdb_sysvars_mutex);
}
+void rocksdb_set_max_lock_memory(THD *thd, struct st_mysql_sys_var *var,
+ void *var_ptr, const void *save) {
+ const uint64_t new_val = *static_cast<const uint64_t *>(save);
+ if (rocksdb_max_lock_memory != new_val) {
+ if (range_lock_mgr->set_max_lock_memory(new_val)) {
+ /* NO_LINT_DEBUG */
+ sql_print_warning("MyRocks: failed to set max_lock_memory");
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+ ER_ERROR_WHEN_EXECUTING_COMMAND,
+ "Cannot set max_lock_memory to size below currently used");
+ } else {
+ // Succeeded
+ rocksdb_max_lock_memory = new_val;
+ }
+ }
+}
+
void rocksdb_set_max_latest_deadlocks(THD *thd, struct st_mysql_sys_var *var,
void *var_ptr, const void *save) {
RDB_MUTEX_LOCK_CHECK(rdb_sysvars_mutex);
1
0
[Commits] ae4869e88: Range Locking: shared point lock support: support lock escalations
by Sergei Petrunia 10 Jun '19
by Sergei Petrunia 10 Jun '19
10 Jun '19
revision-id: ae4869e880a4118bd912fafe7fbeb6f464c4d893 (v5.8-1043-gae4869e88)
parent(s): 2f0ee897552bb4a8aa66b933c0d6f8529a82e2e8
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-06-10 17:16:12 +0300
message:
Range Locking: shared point lock support: support lock escalations
- Make Lock Escalation keep shared locks. Shared locks are not collapsed
with other kinds of locks.
- Replace RangeLockMgrHandle::get_escalation_count() with GetStatus()
which also reports amount of memory used for Range Locking (and there
is more data we could report through this)
- Initialize LTM_STATUS_S::m_initialized.
---
include/rocksdb/utilities/transaction_db.h | 9 ++-
.../transactions/range_locking/ft/ft-status.h | 2 +-
.../range_locking/locktree/locktree.cc | 78 ++++++++++++++++------
.../range_locking/locktree/treenode.cc | 10 +++
.../transactions/range_locking/locktree/treenode.h | 11 ---
utilities/transactions/transaction_lock_mgr.cc | 18 ++---
utilities/transactions/transaction_lock_mgr.h | 6 +-
7 files changed, 89 insertions(+), 45 deletions(-)
diff --git a/include/rocksdb/utilities/transaction_db.h b/include/rocksdb/utilities/transaction_db.h
index 7ebac3e06..7444cb5f2 100644
--- a/include/rocksdb/utilities/transaction_db.h
+++ b/include/rocksdb/utilities/transaction_db.h
@@ -38,7 +38,14 @@ const uint32_t kInitialMaxDeadlocks = 5;
class RangeLockMgrHandle {
public:
virtual int set_max_lock_memory(size_t max_lock_memory) = 0;
- virtual uint64_t get_escalation_count() = 0;
+
+ class Counters {
+ public:
+ uint64_t escalation_count;
+ uint64_t current_lock_memory;
+ };
+
+ virtual Counters GetStatus() = 0;
virtual ~RangeLockMgrHandle() {};
};
diff --git a/utilities/transactions/range_locking/ft/ft-status.h b/utilities/transactions/range_locking/ft/ft-status.h
index 25051f1ed..242964f0b 100644
--- a/utilities/transactions/range_locking/ft/ft-status.h
+++ b/utilities/transactions/range_locking/ft/ft-status.h
@@ -80,7 +80,7 @@ public:
TOKU_ENGINE_STATUS_ROW_S status[LTM_STATUS_NUM_ROWS];
private:
- bool m_initialized;
+ bool m_initialized = false;
};
typedef LTM_STATUS_S* LTM_STATUS;
extern LTM_STATUS_S ltm_status;
diff --git a/utilities/transactions/range_locking/locktree/locktree.cc b/utilities/transactions/range_locking/locktree/locktree.cc
index 0e5f7c307..00ce5aace 100644
--- a/utilities/transactions/range_locking/locktree/locktree.cc
+++ b/utilities/transactions/range_locking/locktree/locktree.cc
@@ -742,11 +742,16 @@ static int extract_first_n_row_locks(concurrent_tree::locked_keyrange *lkr,
int num_to_extract;
row_lock *row_locks;
bool fn(const keyrange &range, TXNID txnid, bool is_shared, TxnidVector *owners) {
- // psergey-todo: multiple owners!
if (num_extracted < num_to_extract) {
row_lock lock;
lock.range.create_copy(range);
lock.txnid = txnid;
+ lock.is_shared= is_shared;
+ // deep-copy the set of owners:
+ if (owners)
+ lock.owners = new TxnidVector(*owners);
+ else
+ lock.owners = nullptr;
row_locks[num_extracted++] = lock;
return true;
} else {
@@ -834,38 +839,60 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca
// through them and merge adjacent locks with the same txnid into
// one dominating lock and save it to a set of escalated locks.
//
- // first, find the index of the next row lock with a different txnid
+ // first, find the index of the next row lock that
+ // - belongs to a different txnid, or
+ // - belongs to several txnids, or
+ // - is a shared lock (we could potentially merge those but
+ // currently we don't)
int next_txnid_index = current_index + 1;
+
while (next_txnid_index < num_extracted &&
- extracted_buf[current_index].txnid == extracted_buf[next_txnid_index].txnid) {
+ (extracted_buf[current_index].txnid ==
+ extracted_buf[next_txnid_index].txnid) &&
+ !extracted_buf[next_txnid_index].is_shared &&
+ !extracted_buf[next_txnid_index].owners) {
next_txnid_index++;
}
// Create an escalated range for the current txnid that dominates
// each range between the current indext and the next txnid's index.
- const TXNID current_txnid = extracted_buf[current_index].txnid;
+ //const TXNID current_txnid = extracted_buf[current_index].txnid;
const DBT *escalated_left_key = extracted_buf[current_index].range.get_left_key();
const DBT *escalated_right_key = extracted_buf[next_txnid_index - 1].range.get_right_key();
// Try to find a range buffer for the current txnid. Create one if it doesn't exist.
// Then, append the new escalated range to the buffer.
- uint32_t idx;
- struct txnid_range_buffer *existing_range_buffer;
- int r = range_buffers.find_zero<TXNID, txnid_range_buffer::find_by_txnid>(
- current_txnid,
- &existing_range_buffer,
- &idx
- );
- if (r == DB_NOTFOUND) {
- struct txnid_range_buffer *XMALLOC(new_range_buffer);
- new_range_buffer->txnid = current_txnid;
- new_range_buffer->buffer.create();
- new_range_buffer->buffer.append(escalated_left_key, escalated_right_key);
- range_buffers.insert_at(new_range_buffer, idx);
- } else {
- invariant_zero(r);
- invariant(existing_range_buffer->txnid == current_txnid);
- existing_range_buffer->buffer.append(escalated_left_key, escalated_right_key);
+ // (If a lock is shared by multiple txnids, append it each of txnid's lists)
+ TxnidVector *owners_ptr;
+ TxnidVector singleton_owner;
+ if (extracted_buf[current_index].owners)
+ owners_ptr = extracted_buf[current_index].owners;
+ else {
+ singleton_owner.insert(extracted_buf[current_index].txnid);
+ owners_ptr = &singleton_owner;
+ }
+
+ for (auto cur_txnid : *owners_ptr ) {
+ uint32_t idx;
+ struct txnid_range_buffer *existing_range_buffer;
+ int r = range_buffers.find_zero<TXNID, txnid_range_buffer::find_by_txnid>(
+ cur_txnid,
+ &existing_range_buffer,
+ &idx
+ );
+ if (r == DB_NOTFOUND) {
+ struct txnid_range_buffer *XMALLOC(new_range_buffer);
+ new_range_buffer->txnid = cur_txnid;
+ new_range_buffer->buffer.create();
+ new_range_buffer->buffer.append(escalated_left_key, escalated_right_key,
+ !extracted_buf[current_index].is_shared);
+ range_buffers.insert_at(new_range_buffer, idx);
+ } else {
+ invariant_zero(r);
+ invariant(existing_range_buffer->txnid == cur_txnid);
+ existing_range_buffer->buffer.append(escalated_left_key, escalated_right_key,
+ !extracted_buf[current_index].is_shared);
+ }
}
current_index = next_txnid_index;
@@ -873,6 +900,7 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca
// destroy the ranges copied during the extraction
for (int i = 0; i < num_extracted; i++) {
+ delete extracted_buf[i].owners;
extracted_buf[i].range.destroy();
}
}
@@ -880,6 +908,12 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca
// Rebuild the locktree from each range in each range buffer,
// then notify higher layers that the txnid's locks have changed.
+ //
+ // (shared locks: if a lock was initially shared between transactions TRX1,
+ // TRX2, etc, we will now try to acquire it acting on behalf on TRX1, on
+ // TRX2, etc. This will succeed and an identical shared lock will be
+ // constructed)
+
invariant(m_rangetree->is_empty());
const size_t num_range_buffers = range_buffers.size();
for (size_t i = 0; i < num_range_buffers; i++) {
@@ -894,7 +928,7 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca
keyrange range;
range.create(rec.get_left_key(), rec.get_right_key());
row_lock lock = { .range = range, .txnid = current_txnid,
- .is_shared= false, // psergey-todo: SharedLockEscalation
+ .is_shared= !rec.get_exclusive_flag(),
.owners= nullptr };
insert_row_lock_into_tree(&lkr, lock, m_mgr);
iter.next();
diff --git a/utilities/transactions/range_locking/locktree/treenode.cc b/utilities/transactions/range_locking/locktree/treenode.cc
index f44918a1b..5bf349749 100644
--- a/utilities/transactions/range_locking/locktree/treenode.cc
+++ b/utilities/transactions/range_locking/locktree/treenode.cc
@@ -134,6 +134,16 @@ void treenode::swap_in_place(treenode *node1, treenode *node2) {
node2->m_is_shared= tmp_is_shared;
}
+void treenode::add_shared_owner(TXNID txnid) {
+ assert(m_is_shared);
+ if (m_txnid != TXNID_SHARED) {
+ m_owners= new TxnidVector;
+ m_owners->insert(m_txnid);
+ m_txnid= TXNID_SHARED;
+ }
+ m_owners->insert(txnid);
+}
+
void treenode::free(treenode *node) {
// destroy the range, freeing any copied keys
node->m_range.destroy();
diff --git a/utilities/transactions/range_locking/locktree/treenode.h b/utilities/transactions/range_locking/locktree/treenode.h
index 6b082acc4..f23324f03 100644
--- a/utilities/transactions/range_locking/locktree/treenode.h
+++ b/utilities/transactions/range_locking/locktree/treenode.h
@@ -167,17 +167,6 @@ private:
// it has at the time of destruction.
keyrange m_range;
- void add_shared_owner(TXNID txnid)
- {
- assert(m_is_shared);
- if (m_txnid != TXNID_SHARED)
- {
- m_owners= new TxnidVector;
- m_owners->insert(m_txnid);
- m_txnid= TXNID_SHARED;
- }
- m_owners->insert(txnid);
- }
void remove_shared_owner(TXNID txnid);
bool has_multiple_owners() { return (m_txnid == TXNID_SHARED); }
diff --git a/utilities/transactions/transaction_lock_mgr.cc b/utilities/transactions/transaction_lock_mgr.cc
index 34144c2af..564cd8600 100644
--- a/utilities/transactions/transaction_lock_mgr.cc
+++ b/utilities/transactions/transaction_lock_mgr.cc
@@ -1154,25 +1154,25 @@ RangeLockMgr::~RangeLockMgr() {
ltm_.destroy();
}
-uint64_t RangeLockMgr::get_escalation_count() {
+RangeLockMgrHandle::Counters RangeLockMgr::GetStatus() {
LTM_STATUS_S ltm_status_test;
ltm_.get_status(<m_status_test);
+ Counters res;
// Searching status variable by its string name is how Toku's unit tests
// do it (why didn't they make LTM_ESCALATION_COUNT constant visible?)
- TOKU_ENGINE_STATUS_ROW key_status = NULL;
// lookup keyname in status
- for (int i = 0; ; i++) {
+ for (int i = 0; i < LTM_STATUS_S::LTM_STATUS_NUM_ROWS; i++) {
TOKU_ENGINE_STATUS_ROW status = <m_status_test.status[i];
- if (status->keyname == NULL)
- break;
if (strcmp(status->keyname, "LTM_ESCALATION_COUNT") == 0) {
- key_status = status;
- break;
+ res.escalation_count = status->value.num;
+ continue;
+ }
+ if (strcmp(status->keyname, "LTM_SIZE_CURRENT") == 0) {
+ res.current_lock_memory = status->value.num;
}
}
- assert(key_status);
- return key_status->value.num;
+ return res;
}
void RangeLockMgr::AddColumnFamily(const ColumnFamilyHandle *cfh) {
diff --git a/utilities/transactions/transaction_lock_mgr.h b/utilities/transactions/transaction_lock_mgr.h
index 608d6f34f..6c8e0638c 100644
--- a/utilities/transactions/transaction_lock_mgr.h
+++ b/utilities/transactions/transaction_lock_mgr.h
@@ -239,7 +239,11 @@ class RangeLockMgr :
return ltm_.set_max_lock_memory(max_lock_memory);
}
- uint64_t get_escalation_count() override;
+ size_t get_max_lock_memory() {
+ return ltm_.get_max_lock_memory();
+ }
+
+ Counters GetStatus() override;
LockStatusData GetLockStatusData() override;
1
0
[Commits] f04edf4: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
by IgorBabaev 09 Jun '19
by IgorBabaev 09 Jun '19
09 Jun '19
revision-id: f04edf4b68f6b9fd2722dca289a675266ec99a7a (mariadb-5.5.64-13-gf04edf4)
parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-06-09 10:39:52 -0700
message:
MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
Handling of top level conjuncts in WHERE whose used_tables() contained
RAND_TABLE_BIT in the function make_join_select() was incorrect.
As a result if such a conjunct referred to fields non of which belonged
to the last joined table it was pushed twice. (This could be seen
for a test case from subselect.test whose output was changed after this
patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for
the query from this test case we clearly see that one of the conjuncts
is pushed twice.) This fact by itself was not good. Besides, if such a
conjunct was pushed to a table that was the result of materialization
of a semi-join the query could return a wrong result set. In particular
we could watch it for queries with semi-join subqueries whose left parts
used stored functions without "deterministic' specifier.
---
mysql-test/r/subselect.result | 2 +-
mysql-test/r/subselect_mat.result | 89 +++++++++++++++++++++++++++++++
mysql-test/r/subselect_no_mat.result | 2 +-
mysql-test/r/subselect_no_opts.result | 2 +-
mysql-test/r/subselect_no_scache.result | 2 +-
mysql-test/r/subselect_no_semijoin.result | 2 +-
mysql-test/r/subselect_sj_mat.result | 89 +++++++++++++++++++++++++++++++
mysql-test/t/subselect_sj_mat.test | 75 ++++++++++++++++++++++++++
sql/sql_select.cc | 87 +++++++++++++++++++++++++-----
9 files changed, 331 insertions(+), 19 deletions(-)
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index cdedc02..2999f4c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6939,7 +6939,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index efc348a..0e10800 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2453,6 +2453,95 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
1
drop table t1,t2;
drop view v1;
+#
+# MDEV-19580: function invocation in the left part of IN subquery
+#
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+create function f1(who int, dt date) returns int
+deterministic
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+create function f2(who int, dt date) returns int
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+# Deterministic function in left part of IN subquery: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+# Non-deterministic function in left part of IN subq: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+select t1.*, t4.*,
+(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+from t1 left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a s
+3 paul 1 1 songwriter 1
+4 art 1 1 songwriter 1
+1 mrs 2 2 song character 2
+explain extended select t1.*, t4.*,
+(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+from t1 left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(select max(`test`.`t4`.`id`) from `test`.`t4` where ((`test`.`t4`.`id` = `test`.`t1`.`b`) and (sleep(0) = 0))) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
# End of 5.5 tests
set @subselect_mat_test_optimizer_switch_value=null;
set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index a729129..5c21e74 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6937,7 +6937,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index c41fa1b..8ab1f1b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 1c18135..f0afa7e 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6945,7 +6945,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 89c6712..0d5fbc3 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index fd9435e..cb4b630 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2493,4 +2493,93 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
1
drop table t1,t2;
drop view v1;
+#
+# MDEV-19580: function invocation in the left part of IN subquery
+#
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+create function f1(who int, dt date) returns int
+deterministic
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+create function f2(who int, dt date) returns int
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+# Deterministic function in left part of IN subquery: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+# Non-deterministic function in left part of IN subq: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+select t1.*, t4.*,
+(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+from t1 left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a s
+3 paul 1 1 songwriter 1
+4 art 1 1 songwriter 1
+1 mrs 2 2 song character 2
+explain extended select t1.*, t4.*,
+(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+from t1 left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(select max(`test`.`t4`.`id`) from `test`.`t4` where ((`test`.`t4`.`id` = `test`.`t1`.`b`) and (sleep(0) = 0))) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
# End of 5.5 tests
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index c82c1e7..9c3788a 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -2230,4 +2230,79 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
drop table t1,t2;
drop view v1;
+
+
+--echo #
+--echo # MDEV-19580: function invocation in the left part of IN subquery
+--echo #
+
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+
+delimiter $$;
+
+create function f1(who int, dt date) returns int
+deterministic
+begin
+ declare result int;
+ select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+ return result;
+end$$
+
+create function f2(who int, dt date) returns int
+begin
+ declare result int;
+ select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+ return result;
+end$$
+
+delimiter ;$$
+
+--echo # Deterministic function in left part of IN subquery: semi-join is OK
+
+let $q1=
+select * from t1
+ left join t4 on t1.b = t4.id
+ where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q1;
+eval explain extended $q1;
+
+--echo # Non-deterministic function in left part of IN subq: semi-join is OK
+
+let $q2=
+select * from t1
+ left join t4 on t1.b = t4.id
+ where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q2;
+eval explain extended $q2;
+
+let $q3=
+select t1.*, t4.*,
+ (select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+ from t1 left join t4 on t1.b = t4.id
+ where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q3;
+eval explain extended $q3;
+
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
+
--echo # End of 5.5 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 37f8292..d7ff92a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -204,7 +204,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond,
table_map used_table,
int join_tab_idx_arg,
bool exclude_expensive_cond,
- bool retain_ref_cond);
+ bool retain_ref_cond,
+ bool is_top_and_level);
static Item* part_of_refkey(TABLE *form,Field *field);
uint find_shortest_key(TABLE *table, const key_map *usable_keys);
@@ -8922,12 +8923,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
RAND_TABLE_BIT;
}
- /*
- Following force including random expression in last table condition.
- It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5
- */
- if (tab == join->join_tab + last_top_base_tab_idx)
- current_map|= RAND_TABLE_BIT;
used_tables|=current_map;
if (tab->type == JT_REF && tab->quick &&
@@ -8968,6 +8963,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
{
tmp= make_cond_for_table(thd, cond, used_tables, current_map, i,
FALSE, FALSE);
+ if (tab == join->join_tab + last_top_base_tab_idx)
+ {
+ /*
+ This pushes conjunctive conditions of WHERE condition such that:
+ - their used_tables() contain RAND_TABLE_BIT
+ - the conditions does not refer to any fields
+ (such like rand() > 0.5)
+ */
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+ COND *rand_cond= make_cond_for_table(thd, cond, used_tables,
+ rand_table_bit, -1,
+ FALSE, FALSE);
+ add_cond_and_fix(thd, &tmp, rand_cond);
+ }
}
/* Add conditions added by add_not_null_conds(). */
if (tab->select_cond)
@@ -9283,8 +9292,24 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
psergey: have put the -1 below. It's bad, will need to fix it.
*/
COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2,
- current_map, /*(tab - first_tab)*/ -1,
- FALSE, FALSE);
+ current_map,
+ /*(tab - first_tab)*/ -1,
+ FALSE, FALSE);
+ if (tab == last_tab)
+ {
+ /*
+ This pushes conjunctive conditions of ON expression of an outer
+ join such that:
+ - their used_tables() contain RAND_TABLE_BIT
+ - the conditions does not refer to any fields
+ (such like rand() > 0.5)
+ */
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+ COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2,
+ rand_table_bit, -1,
+ FALSE, FALSE);
+ add_cond_and_fix(thd, &tmp_cond, rand_cond);
+ }
bool is_sjm_lookup_tab= FALSE;
if (tab->bush_children)
{
@@ -18824,7 +18849,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables,
return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table,
join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, true);
}
@@ -18834,9 +18859,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
int join_tab_idx_arg,
bool exclude_expensive_cond __attribute__
((unused)),
- bool retain_ref_cond)
+ bool retain_ref_cond,
+ bool is_top_and_level)
{
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+
if (used_table && !(cond->used_tables() & used_table))
return (COND*) 0; // Already checked
@@ -18852,11 +18880,28 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
Item *item;
while ((item=li++))
{
+ /*
+ Special handling of top level conjuncts with RAND_TABLE_BIT:
+ if such a conjunct contains a reference to a field that is not
+ an outer field then it is pushed to the corresponding table by
+ the same rule as all other conjuncts. Otherwise, if the conjunct
+ is used in WHERE is is pushed to the last joined table, if is it
+ is used in ON condition of an outer join it is pushed into the
+ last inner table of the outer join. Such conjuncts are pushed in
+ a call of make_cond_for_table_from_pred() with the
+ parameter 'used_table' equal to PSEUDO_TABLE_BITS.
+ */
+ if (is_top_and_level && used_table == rand_table_bit &&
+ (item->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+ {
+ /* The conjunct with RAND_TABLE_BIT has been allready pushed */
+ continue;
+ }
Item *fix=make_cond_for_table_from_pred(thd, root_cond, item,
tables, used_table,
- join_tab_idx_arg,
+ join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, false);
if (fix)
new_cond->argument_list()->push_back(fix);
}
@@ -18880,6 +18925,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
}
else
{ // Or list
+ if (is_top_and_level && used_table == rand_table_bit &&
+ (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+ {
+ /* This top level formula with RAND_TABLE_BIT has been already pushed */
+ return (COND*) 0;
+ }
+
Item_cond_or *new_cond=new Item_cond_or;
if (!new_cond)
return (COND*) 0; // OOM /* purecov: inspected */
@@ -18891,7 +18943,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
tables, 0L,
join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, false);
if (!fix)
return (COND*) 0; // Always true
new_cond->argument_list()->push_back(fix);
@@ -18908,6 +18960,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
}
}
+ if (is_top_and_level && used_table == rand_table_bit &&
+ (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+ {
+ /* This top level formula with RAND_TABLE_BIT has been already pushed */
+ return (COND*) 0;
+ }
+
/*
Because the following test takes a while and it can be done
table_count times, we mark each item that we have examined with the result
1
0
[Commits] 5d06edfb261: MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
by Sergei Petrunia 07 Jun '19
by Sergei Petrunia 07 Jun '19
07 Jun '19
revision-id: 5d06edfb2616ab0b4b067580c281afbbef8fdc74 (mariadb-10.2.24-69-g5d06edfb261)
parent(s): 9b22354a594570e23cc675d90836743ce7a3ba1c
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-06-08 02:28:29 +0300
message:
MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
Make it visible
---
mysql-test/r/subselect_no_semijoin.result | 55 +++++++++++++++++++++++++++++++
mysql-test/t/subselect_no_semijoin.test | 23 +++++++++++++
sql/sql_explain.cc | 5 +++
sql/sql_explain.h | 1 +
sql/sql_select.cc | 1 +
5 files changed, 85 insertions(+)
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index a0625246e2b..55d408c54fc 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7291,5 +7291,60 @@ pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
# End of 10.2 tests
+#
+# MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1),(5);
+CREATE TABLE t2 ( b INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 ( c INT );
+INSERT INTO t3 VALUES (4),(5);
+SET @tmp19714=@@optimizer_switch;
+SET optimizer_switch='subquery_cache=off';
+explain format=json
+SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 2,
+ "filtered": 100
+ },
+ "subqueries": [
+ {
+ "query_block": {
+ "select_id": 2,
+ "pseudo_bits_condition": "1 = t1.a or <in_optimizer>(1,<exists>(subquery#3))",
+ "table": {
+ "table_name": "t2",
+ "access_type": "system",
+ "rows": 1,
+ "filtered": 100
+ },
+ "subqueries": [
+ {
+ "query_block": {
+ "select_id": 3,
+ "table": {
+ "table_name": "t3",
+ "access_type": "ALL",
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "1 = t3.c"
+ }
+ }
+ }
+ ]
+ }
+ }
+ ]
+ }
+}
+SET optimizer_switch=@tmp19714;
+drop table t1,t2,t3;
set @optimizer_switch_for_subselect_test=null;
set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/t/subselect_no_semijoin.test b/mysql-test/t/subselect_no_semijoin.test
index 46791667173..eda95b93503 100644
--- a/mysql-test/t/subselect_no_semijoin.test
+++ b/mysql-test/t/subselect_no_semijoin.test
@@ -8,5 +8,28 @@ set @join_cache_level_for_subselect_test=@@join_cache_level;
--source t/subselect.test
+--echo #
+--echo # MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
+--echo #
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1),(5);
+
+# t2 must be MyISAM or Aria and contain 1 row
+CREATE TABLE t2 ( b INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+
+CREATE TABLE t3 ( c INT );
+INSERT INTO t3 VALUES (4),(5);
+
+SET @tmp19714=@@optimizer_switch;
+SET optimizer_switch='subquery_cache=off';
+
+explain format=json
+SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1;
+
+SET optimizer_switch=@tmp19714;
+
+drop table t1,t2,t3;
+
set @optimizer_switch_for_subselect_test=null;
set @join_cache_level_for_subselect_test=NULL;
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 1ab10fb3c37..3ebd44dc0bb 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -857,6 +857,11 @@ void Explain_select::print_explain_json(Explain_query *query,
writer->add_member("outer_ref_condition");
write_item(writer, outer_ref_cond);
}
+ if (pseudo_bits_cond)
+ {
+ writer->add_member("pseudo_bits_condition");
+ write_item(writer, pseudo_bits_cond);
+ }
/* we do not print HAVING which always evaluates to TRUE */
if (having || (having_value == Item::COND_FALSE))
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index 3ca816477fc..08af84b3562 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -232,6 +232,7 @@ class Explain_select : public Explain_basic_join
/* Expensive constant condition */
Item *exec_const_cond;
Item *outer_ref_cond;
+ Item *pseudo_bits_cond;
/* HAVING condition */
Item *having;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 182e913c16c..0ee3735c464 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -25025,6 +25025,7 @@ int JOIN::save_explain_data_intern(Explain_query *output,
xpl_sel->exec_const_cond= exec_const_cond;
xpl_sel->outer_ref_cond= outer_ref_cond;
+ xpl_sel->pseudo_bits_cond= pseudo_bits_cond;
if (tmp_having)
xpl_sel->having= tmp_having;
else
1
0
[Commits] f7579518e2c: MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
by Sergei Petrunia 05 Jun '19
by Sergei Petrunia 05 Jun '19
05 Jun '19
revision-id: f7579518e2c32936442a1e20e391f60660c94b3c (mariadb-10.3.12-226-gf7579518e2c)
parent(s): 7060b0320d1479bb9476e0cbd4acc584e059e1ff
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-06-05 14:00:45 +0300
message:
MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
Modify best_access_path() to produce rows=1 estimate for null-rejecting
lookups on unique NULL keys.
---
mysql-test/main/invisible_field.result | 4 +-
mysql-test/main/join.result | 30 +++++++++
mysql-test/main/join.test | 30 +++++++++
mysql-test/main/order_by.result | 2 +-
mysql-test/main/subselect_sj.result | 74 ++++++++++++++++++++---
mysql-test/main/subselect_sj.test | 52 +++++++++++++++-
mysql-test/main/subselect_sj_jcl6.result | 58 +++++++++++++++++-
mysql-test/main/subselect_sj_nonmerged.result | 12 ++--
mysql-test/main/table_elim.result | 2 +-
mysql-test/suite/gcol/r/gcol_select_myisam.result | 2 +-
sql/sql_select.cc | 47 +++++++++-----
11 files changed, 276 insertions(+), 37 deletions(-)
diff --git a/mysql-test/main/invisible_field.result b/mysql-test/main/invisible_field.result
index 876a80814e5..43a8b9d726b 100644
--- a/mysql-test/main/invisible_field.result
+++ b/mysql-test/main/invisible_field.result
@@ -404,8 +404,8 @@ b int(11) YES NULL
c int(11) YES NULL
explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 10
-1 SIMPLE t1 ALL b,c NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t1 ref b,c b 5 test.t2.c 1 Using where
select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
a a b c
1 1 1 1
diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result
index cc8e174c8e6..8ca82002855 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -1599,3 +1599,33 @@ SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a );
a b c b c
DROP TABLE t1,t2;
+#
+# MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+pk int not null primary key auto_increment,
+a int,
+b int,
+unique key(a)
+);
+insert into t1 (a,b) select null, 12345 from t0 A, t0 B, t0 C;
+insert into t1 (a,b) select a,a from t0;
+# Simulate InnoDB's persistent statistics (It always uses nulls_equal)
+set @tmp1= @@myisam_stats_method;
+set myisam_stats_method=nulls_equal;
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+set myisam_stats_method=@tmp1;
+show keys from t1;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
+t1 0 PRIMARY 1 pk A 1010 NULL NULL BTREE
+t1 0 a 1 a A 10 NULL NULL YES BTREE
+# t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45):
+explain select * from t0,t1 where t0.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t1 ref a a 5 test.t0.a 1
+drop table t0,t1;
diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test
index 3d2a02e2346..b90a9cc39eb 100644
--- a/mysql-test/main/join.test
+++ b/mysql-test/main/join.test
@@ -1254,3 +1254,33 @@ SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a );
DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
+--echo #
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (
+ pk int not null primary key auto_increment,
+ a int,
+ b int,
+ unique key(a)
+);
+
+# 10K of null values
+insert into t1 (a,b) select null, 12345 from t0 A, t0 B, t0 C;
+insert into t1 (a,b) select a,a from t0;
+
+--echo # Simulate InnoDB's persistent statistics (It always uses nulls_equal)
+set @tmp1= @@myisam_stats_method;
+set myisam_stats_method=nulls_equal;
+analyze table t1;
+set myisam_stats_method=@tmp1;
+show keys from t1;
+
+--echo # t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45):
+explain select * from t0,t1 where t0.a=t1.a;
+
+drop table t0,t1;
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index 8d1e471f618..8692e727c60 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -1515,7 +1515,7 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range a,b,c c 5 NULL 420 Using where
-1 SIMPLE t1 ref a a 39 test.t2.a,const 10 Using where; Using index
+1 SIMPLE t1 ref a a 39 test.t2.a,const 1 Using where; Using index
SELECT d FROM t3 AS t1, t2 AS t2
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result
index 13d5224566b..ad43a09c7ff 100644
--- a/mysql-test/main/subselect_sj.result
+++ b/mysql-test/main/subselect_sj.result
@@ -2555,33 +2555,89 @@ CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
INSERT INTO t1 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),
(6,3),(7,1),(8,4),(9,3),(10,2);
-CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+CREATE TABLE t2 ( c INT, d INT, KEY(c) );
INSERT INTO t2 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+analyze table t1,t2;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+explain
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+1 PRIMARY t1 index b b 5 NULL 10 Using where; Using index; LooseScan
+1 PRIMARY t2 ref c c 5 test.t1.b 1 Using where; FirstMatch(t1)
+1 PRIMARY t1 ref b b 5 test.t1.b 2
SELECT a, b, d FROM t1, t2
WHERE ( b, d ) IN
( SELECT b, d FROM t1, t2 WHERE b = c );
a b d
2 1 2
7 1 2
-2 1 2
-7 1 2
-1 2 1
-4 2 1
-10 2 1
+8 4 2
1 2 1
4 2 1
10 2 1
3 3 3
6 3 3
9 3 3
+2 1 2
+7 1 2
+8 4 2
+5 5 5
3 3 3
6 3 3
9 3 3
-8 4 2
-8 4 2
-5 5 5
+1 2 1
+4 2 1
+10 2 1
DROP TABLE t1, t2;
+# Another testcase for the above that still uses LooseScan:
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t10(a int primary key);
+insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t1 (
+pk int primary key auto_increment,
+kp1 int,
+kp2 int,
+filler char(100),
+key (kp1, kp2)
+);
+insert into t1 (kp1, kp2, filler)
+select
+A.a, B.a, 'filler-data'
+from t0 A, t0 B;
+create table t2 (a int, filler char(100), key(a));
+create table t3 (a int);
+insert into t3 values (1),(2);
+insert into t2
+select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B;
+analyze table t1,t2,t3;
+Table Op Msg_type Msg_text
+test.t1 analyze status Table is already up to date
+test.t2 analyze status Table is already up to date
+test.t3 analyze status OK
+delete from t1 where kp2 in (1,3);
+# Ref + LooseScan on t1:
+explain select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 10 Using where; Using index; LooseScan
+1 PRIMARY t2 ref a a 5 test.t1.kp2 20 Using index
+select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+sum(t2.a)
+1640
+drop table t0,t10;
+drop table t1,t2,t3;
#
# BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
#
diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test
index 29211cadd9c..81cca95092d 100644
--- a/mysql-test/main/subselect_sj.test
+++ b/mysql-test/main/subselect_sj.test
@@ -2285,16 +2285,66 @@ INSERT INTO t1 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),
(6,3),(7,1),(8,4),(9,3),(10,2);
-CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+CREATE TABLE t2 ( c INT, d INT, KEY(c) );
INSERT INTO t2 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+analyze table t1,t2;
+explain
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+ ( SELECT b, d FROM t1, t2 WHERE b = c );
SELECT a, b, d FROM t1, t2
WHERE ( b, d ) IN
( SELECT b, d FROM t1, t2 WHERE b = c );
DROP TABLE t1, t2;
+--echo # Another testcase for the above that still uses LooseScan:
+
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t10(a int primary key);
+insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+
+create table t1 (
+ pk int primary key auto_increment,
+ kp1 int,
+ kp2 int,
+ filler char(100),
+ key (kp1, kp2)
+);
+
+# 10 groups, each has 10 elements.
+insert into t1 (kp1, kp2, filler)
+select
+ A.a, B.a, 'filler-data'
+from t0 A, t0 B;
+
+create table t2 (a int, filler char(100), key(a));
+
+create table t3 (a int);
+insert into t3 values (1),(2);
+
+insert into t2
+select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B;
+
+analyze table t1,t2,t3;
+delete from t1 where kp2 in (1,3);
+
+--echo # Ref + LooseScan on t1:
+explain select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+
+select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+
+drop table t0,t10;
+drop table t1,t2,t3;
+
--echo #
--echo # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
--echo #
diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result
index 014c44d1181..0026924b605 100644
--- a/mysql-test/main/subselect_sj_jcl6.result
+++ b/mysql-test/main/subselect_sj_jcl6.result
@@ -2569,9 +2569,22 @@ CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
INSERT INTO t1 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),
(6,3),(7,1),(8,4),(9,3),(10,2);
-CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+CREATE TABLE t2 ( c INT, d INT, KEY(c) );
INSERT INTO t2 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+analyze table t1,t2;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+explain
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+1 PRIMARY t1 index b b 5 NULL 10 Using where; Using index; LooseScan
+1 PRIMARY t2 ref c c 5 test.t1.b 1 Using where; FirstMatch(t1)
+1 PRIMARY t1 ref b b 5 test.t1.b 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT a, b, d FROM t1, t2
WHERE ( b, d ) IN
( SELECT b, d FROM t1, t2 WHERE b = c );
@@ -2596,6 +2609,49 @@ a b d
10 2 1
10 2 1
DROP TABLE t1, t2;
+# Another testcase for the above that still uses LooseScan:
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t10(a int primary key);
+insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t1 (
+pk int primary key auto_increment,
+kp1 int,
+kp2 int,
+filler char(100),
+key (kp1, kp2)
+);
+insert into t1 (kp1, kp2, filler)
+select
+A.a, B.a, 'filler-data'
+from t0 A, t0 B;
+create table t2 (a int, filler char(100), key(a));
+create table t3 (a int);
+insert into t3 values (1),(2);
+insert into t2
+select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B;
+analyze table t1,t2,t3;
+Table Op Msg_type Msg_text
+test.t1 analyze status Table is already up to date
+test.t2 analyze status Table is already up to date
+test.t3 analyze status OK
+delete from t1 where kp2 in (1,3);
+# Ref + LooseScan on t1:
+explain select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 10 Using where; Using index; LooseScan
+1 PRIMARY t2 ref a a 5 test.t1.kp2 20 Using index
+select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+sum(t2.a)
+1640
+drop table t0,t10;
+drop table t1,t2,t3;
#
# BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
#
diff --git a/mysql-test/main/subselect_sj_nonmerged.result b/mysql-test/main/subselect_sj_nonmerged.result
index 47970668ae5..4d9a70e6bba 100644
--- a/mysql-test/main/subselect_sj_nonmerged.result
+++ b/mysql-test/main/subselect_sj_nonmerged.result
@@ -67,9 +67,9 @@ insert into t4 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t0 A, t0 B;
explain select * from t0, t4 where
t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 10
-1 PRIMARY t4 ALL a NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t4.a 1
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),test.t0.a 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B;
@@ -77,9 +77,9 @@ explain select * from t4 where
t4.a in (select max(t2.a) from t1, t2 group by t2.b) and
t4.b in (select max(t2.a) from t1, t2 group by t2.b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5
-1 PRIMARY t4 ref a a 5 <subquery2>.max(t2.a) 12 Using index condition
-1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 test.t4.b 1
+1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join)
+1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 1
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary
3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary
diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result
index cf9a4a38779..7780e165451 100644
--- a/mysql-test/main/table_elim.result
+++ b/mysql-test/main/table_elim.result
@@ -279,7 +279,7 @@ insert into t2 values
explain select t1.* from t1 left join t2 on t2.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index
-1 SIMPLE t2 ref a a 3 test.t1.a 2 Using where
+1 SIMPLE t2 ref a a 3 test.t1.a 1 Using where
drop table t1, t2;
#
# check UPDATE/DELETE that look like they could be eliminated
diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result
index d0fe7fbd0d4..280618ed76e 100644
--- a/mysql-test/suite/gcol/r/gcol_select_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result
@@ -1102,7 +1102,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN SELECT * FROM t1 AS t2 STRAIGHT_JOIN t1 FORCE INDEX(b) WHERE t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where
-1 SIMPLE t1 ref b b 5 test.t2.b 2
+1 SIMPLE t1 ref b b 5 test.t2.b 1
EXPLAIN SELECT b FROM t1 FORCE INDEX(b);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL b 5 NULL 2 Using index
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0fbc4bac7d8..13a5cd88c93 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5507,18 +5507,16 @@ add_key_field(JOIN *join,
(*key_fields)->level= and_level;
(*key_fields)->optimize= optimize;
/*
- If the condition has form "tbl.keypart = othertbl.field" and
- othertbl.field can be NULL, there will be no matches if othertbl.field
- has NULL value.
- We use null_rejecting in add_not_null_conds() to add
- 'othertbl.field IS NOT NULL' to tab->select_cond.
+ If the condition we are analyzing is NULL-rejecting and at least
+ one side of the equalities is NULLable, mark the KEY_FIELD object as
+ null-rejecting. This property is used by:
+ - add_not_null_conds() to add "column IS NOT NULL" conditions
+ - best_access_path() to produce better estimates for NULL-able unique keys.
*/
{
- Item *real= (*value)->real_item();
- if (((cond->functype() == Item_func::EQ_FUNC) ||
- (cond->functype() == Item_func::MULT_EQUAL_FUNC)) &&
- (real->type() == Item::FIELD_ITEM) &&
- ((Item_field*)real)->field->maybe_null())
+ if ((cond->functype() == Item_func::EQ_FUNC ||
+ cond->functype() == Item_func::MULT_EQUAL_FUNC) &&
+ ((*value)->maybe_null || field->real_maybe_null()))
(*key_fields)->null_rejecting= true;
else
(*key_fields)->null_rejecting= false;
@@ -6834,6 +6832,7 @@ best_access_path(JOIN *join,
ulong key_flags;
uint key_parts;
key_part_map found_part= 0;
+ key_part_map notnull_part=0; // key parts which won't have NULL in lookup tuple.
table_map found_ref= 0;
uint key= keyuse->key;
bool ft_key= (keyuse->keypart == FT_KEYPART);
@@ -6892,6 +6891,9 @@ best_access_path(JOIN *join,
if (!(keyuse->used_tables & ~join->const_table_map))
const_part|= keyuse->keypart_map;
+ if (!keyuse->val->maybe_null || keyuse->null_rejecting)
+ notnull_part|=keyuse->keypart_map;
+
double tmp2= prev_record_reads(join->positions, idx,
(found_ref | keyuse->used_tables));
if (tmp2 < best_prev_record_reads)
@@ -6942,12 +6944,19 @@ best_access_path(JOIN *join,
loose_scan_opt.check_ref_access_part1(s, key, start_key, found_part);
/* Check if we found full key */
- if (found_part == PREV_BITS(uint, key_parts) &&
- !ref_or_null_part)
+ const key_part_map all_key_parts= PREV_BITS(uint, key_parts);
+ if (found_part == all_key_parts && !ref_or_null_part)
{ /* use eq key */
max_key_part= (uint) ~0;
- if ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME ||
- MY_TEST(key_flags & HA_EXT_NOSAME))
+ /*
+ If the index is a unique index (1), and
+ - all its columns are not null (2), or
+ - equalities we are using reject NULLs (3)
+ then the estimate is rows=1.
+ */
+ if ((key_flags & (HA_NOSAME | HA_EXT_NOSAME)) && // (1)
+ (!(key_flags & HA_NULL_PART_KEY) || // (2)
+ all_key_parts == notnull_part)) // (3)
{
tmp = prev_record_reads(join->positions, idx, found_ref);
records=1.0;
@@ -9991,8 +10000,16 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
uint maybe_null= MY_TEST(keyinfo->key_part[i].null_bit);
j->ref.items[i]=keyuse->val; // Save for cond removal
j->ref.cond_guards[i]= keyuse->cond_guard;
- if (keyuse->null_rejecting)
+
+ /*
+ Set ref.null_rejecting to true only if we are going to inject a
+ "keyuse->val IS NOT NULL" predicate.
+ */
+ Item *real= (keyuse->val)->real_item();
+ if (keyuse->null_rejecting && (real->type() == Item::FIELD_ITEM) &&
+ ((Item_field*)real)->field->maybe_null())
j->ref.null_rejecting|= (key_part_map)1 << i;
+
keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
/*
We don't want to compute heavy expressions in EXPLAIN, an example would
1
0
revision-id: 38564f99677726bf35013b5b94dcbf11a3809db0 (mariadb-10.1.39-43-g38564f9)
parent(s): aaf53ea0b68efde4a90cabbbcaf9ca41c1fbf62f
committer: Alexey Botchkov
timestamp: 2019-05-27 09:48:40 +0400
message:
Sc #27132 proposed fix.
---
sql/sql_audit.cc | 16 ++++++++++++++++
sql/sql_audit.h | 1 +
sql/sql_class.cc | 4 +++-
sql/sql_class.h | 1 +
sql/sql_connect.cc | 3 ++-
sql/sql_plugin.cc | 3 +++
sql/sql_plugin.h | 1 +
sql/threadpool_common.cc | 3 ++-
8 files changed, 29 insertions(+), 3 deletions(-)
diff --git a/sql/sql_audit.cc b/sql/sql_audit.cc
index dd98e3c..cee0ac2 100644
--- a/sql/sql_audit.cc
+++ b/sql/sql_audit.cc
@@ -212,6 +212,7 @@ void mysql_audit_acquire_plugins(THD *thd, ulong *event_class_mask)
{
plugin_foreach(thd, acquire_plugins, MYSQL_AUDIT_PLUGIN, event_class_mask);
add_audit_mask(thd->audit_class_mask, event_class_mask);
+ thd->audit_plugin_version= global_plugin_version;
}
DBUG_VOID_RETURN;
}
@@ -242,6 +243,20 @@ void mysql_audit_notify(THD *thd, uint event_class, uint event_subtype, ...)
/**
+ Check if there were changes in the state of plugins
+ so we need to do the mysql_audit_release asap.
+
+ @param[in] thd
+
+*/
+
+my_bool mysql_audit_release_required(THD *thd)
+{
+ return thd && (thd->audit_plugin_version != global_plugin_version);
+}
+
+
+/**
Release any resources associated with the current thd.
@param[in] thd
@@ -276,6 +291,7 @@ void mysql_audit_release(THD *thd)
/* Reset the state of thread values */
reset_dynamic(&thd->audit_class_plugins);
bzero(thd->audit_class_mask, sizeof(thd->audit_class_mask));
+ thd->audit_plugin_version= -1;
}
diff --git a/sql/sql_audit.h b/sql/sql_audit.h
index 550b2a5..9a74675 100644
--- a/sql/sql_audit.h
+++ b/sql/sql_audit.h
@@ -60,6 +60,7 @@ static inline void mysql_audit_notify(THD *thd, uint event_class,
#define mysql_audit_connection_enabled() 0
#define mysql_audit_table_enabled() 0
#endif
+extern my_bool mysql_audit_release_required(THD *thd);
extern void mysql_audit_release(THD *thd);
#define MAX_USER_HOST_SIZE 512
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 8fabcd5..6bcff6d 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -776,6 +776,9 @@ THD::THD(bool is_wsrep_applier)
waiting_on_group_commit(FALSE), has_waiter(FALSE),
spcont(NULL),
m_parser_state(NULL),
+#ifndef EMBEDDED_LIBRARY
+ audit_plugin_version(-1),
+#endif
#if defined(ENABLED_DEBUG_SYNC)
debug_sync_control(0),
#endif /* defined(ENABLED_DEBUG_SYNC) */
@@ -1562,7 +1565,6 @@ THD::~THD()
mdl_context.destroy();
ha_close_connection(this);
- mysql_audit_release(this);
plugin_thdvar_cleanup(this);
main_security_ctx.destroy();
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 1cb516c..6392394 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -2978,6 +2978,7 @@ class THD :public Statement,
added to the list of audit plugins which are currently in use.
*/
unsigned long audit_class_mask[MYSQL_AUDIT_CLASS_MASK_SIZE];
+ int audit_plugin_version;
#endif
#if defined(ENABLED_DEBUG_SYNC)
diff --git a/sql/sql_connect.cc b/sql/sql_connect.cc
index 4dbb53f..a6a01b1 100644
--- a/sql/sql_connect.cc
+++ b/sql/sql_connect.cc
@@ -1326,7 +1326,8 @@ void do_handle_one_connection(THD *thd_arg)
while (thd_is_connection_alive(thd))
{
- mysql_audit_release(thd);
+ if (mysql_audit_release_required(thd))
+ mysql_audit_release(thd);
if (do_command(thd))
break;
}
diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc
index 21093e3..48131b1 100644
--- a/sql/sql_plugin.cc
+++ b/sql/sql_plugin.cc
@@ -228,6 +228,7 @@ static DYNAMIC_ARRAY plugin_array;
static HASH plugin_hash[MYSQL_MAX_PLUGIN_TYPE_NUM];
static MEM_ROOT plugin_mem_root;
static bool reap_needed= false;
+volatile int global_plugin_version= 1;
static bool initialized= 0;
ulong dlopen_count;
@@ -2181,6 +2182,7 @@ bool mysql_install_plugin(THD *thd, const LEX_STRING *name,
reap_plugins();
}
err:
+ global_plugin_version++;
mysql_mutex_unlock(&LOCK_plugin);
if (argv)
free_defaults(argv);
@@ -2327,6 +2329,7 @@ bool mysql_uninstall_plugin(THD *thd, const LEX_STRING *name,
}
reap_plugins();
+ global_plugin_version++;
mysql_mutex_unlock(&LOCK_plugin);
DBUG_RETURN(error);
diff --git a/sql/sql_plugin.h b/sql/sql_plugin.h
index 7f74114..3bde06a 100644
--- a/sql/sql_plugin.h
+++ b/sql/sql_plugin.h
@@ -37,6 +37,7 @@ enum enum_plugin_load_option { PLUGIN_OFF, PLUGIN_ON, PLUGIN_FORCE,
PLUGIN_FORCE_PLUS_PERMANENT };
extern const char *global_plugin_typelib_names[];
+extern volatile int global_plugin_version;
extern ulong dlopen_count;
#include <my_sys.h>
diff --git a/sql/threadpool_common.cc b/sql/threadpool_common.cc
index b4066bd..b8be708 100644
--- a/sql/threadpool_common.cc
+++ b/sql/threadpool_common.cc
@@ -266,7 +266,8 @@ int threadpool_process_request(THD *thd)
{
Vio *vio;
thd->net.reading_or_writing= 0;
- mysql_audit_release(thd);
+ if (mysql_audit_release_required(thd))
+ mysql_audit_release(thd);
if ((retval= do_command(thd)) != 0)
goto end;
3
4