[Commits] d0b9a4c: MDEV-18956 Assertion `sel->quick' failed in JOIN::make_range_rowid_filters
by IgorBabaev 06 Apr '19
by IgorBabaev 06 Apr '19
06 Apr '19
revision-id: d0b9a4cfd8393d3b6f2746b89437b1b7207142ea (mariadb-10.4.3-165-gd0b9a4c)
parent(s): a2e477ffd0414248d1d3af2eeafe1e3cffddebc6
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-04-05 23:12:44 -0700
message:
MDEV-18956 Assertion `sel->quick' failed in JOIN::make_range_rowid_filters
If SUBS_IN_TO_EXISTS strategy has been chosen for a subquery then
additional conditions are injected into WHERE/ON/HAVING of this subquery
and it may happen that test_quick_select() invoked from
JOIN::make_range_rowid_filters() discovers impossible range. This
must be checked.
---
mysql-test/main/rowid_filter.result | 25 +++++++++++++++++++++++++
mysql-test/main/rowid_filter.test | 25 +++++++++++++++++++++++++
mysql-test/main/rowid_filter_innodb.result | 25 +++++++++++++++++++++++++
sql/sql_select.cc | 16 +++++++++++++---
4 files changed, 88 insertions(+), 3 deletions(-)
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index 2fdff7d..280ced7 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -1987,4 +1987,29 @@ pk i
1 10
2 20
DROP TABLE t1;
+#
+# MDEV-18956: Possible rowid filter for subquery for which
+# in_to_exists strategy has been chosen
+#
+CREATE TABLE t1 (pk int) engine=myisam ;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (
+pk int auto_increment PRIMARY KEY,
+i1 int, i2 int, c2 varchar(1),
+KEY (i1), KEY (i2)
+) engine=myisam;
+INSERT INTO t2 VALUES
+(1,8,6,'t'),(2,5,7,'i'),(3,4,4,'h'),(4,207,38,'d'),(5,183,206,'b'),
+(6,7,null,'o'),(7,1,2,'j'),(8,17,36,'s'),(9,4,5,'q'),(10,0,6,'l'),
+(11,1,9,'j'),(12,5,6,'y'),(13,null,0,'i'),(14,7,7,'x'),(15,5,2,'u');
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+pk
+EXPLAIN EXTENDED
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0
+DROP TABLE t1,t2;
set @@use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test
index d84839b..9c53367 100644
--- a/mysql-test/main/rowid_filter.test
+++ b/mysql-test/main/rowid_filter.test
@@ -275,4 +275,29 @@ SELECT * FROM t1 WHERE pk < 5;
DROP TABLE t1;
+--echo #
+--echo # MDEV-18956: Possible rowid filter for subquery for which
+--echo # in_to_exists strategy has been chosen
+--echo #
+
+CREATE TABLE t1 (pk int) engine=myisam ;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (
+ pk int auto_increment PRIMARY KEY,
+ i1 int, i2 int, c2 varchar(1),
+ KEY (i1), KEY (i2)
+) engine=myisam;
+
+INSERT INTO t2 VALUES
+ (1,8,6,'t'),(2,5,7,'i'),(3,4,4,'h'),(4,207,38,'d'),(5,183,206,'b'),
+ (6,7,null,'o'),(7,1,2,'j'),(8,17,36,'s'),(9,4,5,'q'),(10,0,6,'l'),
+ (11,1,9,'j'),(12,5,6,'y'),(13,null,0,'i'),(14,7,7,'x'),(15,5,2,'u');
+
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+EXPLAIN EXTENDED
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+
+DROP TABLE t1,t2;
+
set @@use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 6740111..d19aca1 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -1916,6 +1916,31 @@ pk i
1 10
2 20
DROP TABLE t1;
+#
+# MDEV-18956: Possible rowid filter for subquery for which
+# in_to_exists strategy has been chosen
+#
+CREATE TABLE t1 (pk int) engine=myisam ;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (
+pk int auto_increment PRIMARY KEY,
+i1 int, i2 int, c2 varchar(1),
+KEY (i1), KEY (i2)
+) engine=myisam;
+INSERT INTO t2 VALUES
+(1,8,6,'t'),(2,5,7,'i'),(3,4,4,'h'),(4,207,38,'d'),(5,183,206,'b'),
+(6,7,null,'o'),(7,1,2,'j'),(8,17,36,'s'),(9,4,5,'q'),(10,0,6,'l'),
+(11,1,9,'j'),(12,5,6,'y'),(13,null,0,'i'),(14,7,7,'x'),(15,5,2,'u');
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+pk
+EXPLAIN EXTENDED
+SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0
+DROP TABLE t1,t2;
set @@use_stat_tables=@save_use_stat_tables;
#
# MDEV-18755: possible RORI-plan and possible plan with range filter
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 676a73d..42a4278 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1621,12 +1621,22 @@ bool JOIN::make_range_rowid_filters()
filter_map.merge(tab->table->with_impossible_ranges);
bool force_index_save= tab->table->force_index;
tab->table->force_index= true;
- (void) sel->test_quick_select(thd, filter_map, (table_map) 0,
- (ha_rows) HA_POS_ERROR,
- true, false, true, true);
+ int rc= sel->test_quick_select(thd, filter_map, (table_map) 0,
+ (ha_rows) HA_POS_ERROR,
+ true, false, true, true);
tab->table->force_index= force_index_save;
if (thd->is_error())
goto no_filter;
+ /*
+ If SUBS_IN_TO_EXISTS is chosen for the subquery then additional
+ conditions are injected into WHERE/ON/HAVING and it may happen that
+ the call of test_quick_select() discovers impossible range.
+ */
+ if (rc == -1)
+ {
+ const_table_map|= tab->table->map;
+ goto no_filter;
+ }
DBUG_ASSERT(sel->quick);
filter_container=
tab->range_rowid_filter_info->create_container();
1
0
[Commits] a2e477ffd04: MDEV-19186: Assertion `field->table == table' failed in create_tmp_table
by Galina 05 Apr '19
by Galina 05 Apr '19
05 Apr '19
revision-id: a2e477ffd0414248d1d3af2eeafe1e3cffddebc6 (mariadb-10.4.3-164-ga2e477ffd04)
parent(s): 694d1a50bd7754c5357dde184f87d63b7032ee5e
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2019-04-06 00:04:52 +0300
message:
MDEV-19186: Assertion `field->table == table' failed in create_tmp_table
Temporary table is defined with the view field in HAVING.
Item_direct_view_ref for this field is dropped and that causes error.
To fix it Item_direct_view_ref::remove_item_direct_ref() is added.
---
mysql-test/main/having_cond_pushdown.result | 16 ++++++++++++++++
mysql-test/main/having_cond_pushdown.test | 22 ++++++++++++++++++++++
sql/item.h | 1 +
3 files changed, 39 insertions(+)
diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result
index e9fcd633895..85ca0342dee 100644
--- a/mysql-test/main/having_cond_pushdown.result
+++ b/mysql-test/main/having_cond_pushdown.result
@@ -4641,3 +4641,19 @@ pk
3
DROP TABLE t1;
DROP VIEW v1;
+#
+# MDEV-19186: temporary table defined with view field in HAVING
+#
+CREATE TABLE t1 (pk INT, x VARCHAR(10));
+INSERT INTO t1 VALUES (1,'y'),(2,'s'),(3,'aaa');
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE TABLE t2 (pk INT, x VARCHAR(10));
+INSERT INTO t2 VALUES (1,'aa'),(2,'t'),(3,'bb');
+CREATE TABLE tmp1
+SELECT v1.pk
+FROM t2,v1
+WHERE v1.x = t2.x
+GROUP BY v1.pk
+HAVING (v1.pk = 1);
+DROP TABLE t1,t2,tmp1;
+DROP VIEW v1;
diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test
index a50fa11484d..257e5cb4875 100644
--- a/mysql-test/main/having_cond_pushdown.test
+++ b/mysql-test/main/having_cond_pushdown.test
@@ -1318,3 +1318,25 @@ HAVING (1 NOT IN (SELECT COUNT(t1.c1) FROM (v1, t1)));
DROP TABLE t1;
DROP VIEW v1;
+
+
+--echo #
+--echo # MDEV-19186: temporary table defined with view field in HAVING
+--echo #
+
+CREATE TABLE t1 (pk INT, x VARCHAR(10));
+INSERT INTO t1 VALUES (1,'y'),(2,'s'),(3,'aaa');
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+CREATE TABLE t2 (pk INT, x VARCHAR(10));
+INSERT INTO t2 VALUES (1,'aa'),(2,'t'),(3,'bb');
+
+CREATE TABLE tmp1
+SELECT v1.pk
+FROM t2,v1
+WHERE v1.x = t2.x
+GROUP BY v1.pk
+HAVING (v1.pk = 1);
+
+DROP TABLE t1,t2,tmp1;
+DROP VIEW v1;
diff --git a/sql/item.h b/sql/item.h
index 716b411082f..97d31e6ba34 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -5733,6 +5733,7 @@ class Item_direct_view_ref :public Item_direct_ref
{ return get_item_copy<Item_direct_view_ref>(thd, this); }
Item *field_transformer_for_having_pushdown(THD *thd, uchar *arg)
{ return this; }
+ Item *remove_item_direct_ref() { return this; }
};
1
0
[Commits] 694d1a50bd7: MDEV-19185: Pushdown constant function defined with subquery
by Galina 05 Apr '19
by Galina 05 Apr '19
05 Apr '19
revision-id: 694d1a50bd7754c5357dde184f87d63b7032ee5e (mariadb-10.4.3-163-g694d1a50bd7)
parent(s): c84dde148f8d82232a110f9ff7c80df94d0d6c8c
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2019-04-05 22:55:20 +0300
message:
MDEV-19185: Pushdown constant function defined with subquery
The bug occurs because of the wrong pushdown of constant function
defined with subquery from HAVING into WHERE. Subqueries can't be
pushed into WHERE.
To fix it with_subquery() call is added to check if the function contains
subquery.
---
mysql-test/main/having_cond_pushdown.result | 16 ++++++++++++++++
mysql-test/main/having_cond_pushdown.test | 17 +++++++++++++++++
sql/item.cc | 5 ++---
sql/item_func.h | 2 +-
4 files changed, 36 insertions(+), 4 deletions(-)
diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result
index bec2a937e38..e9fcd633895 100644
--- a/mysql-test/main/having_cond_pushdown.result
+++ b/mysql-test/main/having_cond_pushdown.result
@@ -4625,3 +4625,19 @@ a MAX(t1.b) c
1 22 3
deallocate prepare stmt1;
DROP TABLE t1,t3;
+#
+# MDEV-19185: pushdown constant function with subquery
+#
+CREATE TABLE t1 (pk INT, c1 VARCHAR(64));
+INSERT INTO t1 VALUES (1,'bbb'),(2,'aaa'),(3,'ccc');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT pk
+FROM t1
+GROUP BY pk
+HAVING (1 NOT IN (SELECT COUNT(t1.c1) FROM (v1, t1)));
+pk
+1
+2
+3
+DROP TABLE t1;
+DROP VIEW v1;
diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test
index 1e5ad610e90..a50fa11484d 100644
--- a/mysql-test/main/having_cond_pushdown.test
+++ b/mysql-test/main/having_cond_pushdown.test
@@ -1301,3 +1301,20 @@ execute stmt1;
deallocate prepare stmt1;
DROP TABLE t1,t3;
+
+
+--echo #
+--echo # MDEV-19185: pushdown constant function with subquery
+--echo #
+
+CREATE TABLE t1 (pk INT, c1 VARCHAR(64));
+INSERT INTO t1 VALUES (1,'bbb'),(2,'aaa'),(3,'ccc');
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT pk
+FROM t1
+GROUP BY pk
+HAVING (1 NOT IN (SELECT COUNT(t1.c1) FROM (v1, t1)));
+
+DROP TABLE t1;
+DROP VIEW v1;
diff --git a/sql/item.cc b/sql/item.cc
index 543dc971228..e511921b30b 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -9060,9 +9060,8 @@ bool Item_args::excl_dep_on_grouping_fields(st_select_lex *sel)
{
for (uint i= 0; i < arg_count; i++)
{
- if (args[i]->type() == Item::SUBSELECT_ITEM ||
- (args[i]->type() == Item::FUNC_ITEM &&
- ((Item_func *)args[i])->functype() == Item_func::UDF_FUNC))
+ if (args[i]->type() == Item::FUNC_ITEM &&
+ ((Item_func *)args[i])->functype() == Item_func::UDF_FUNC)
return false;
if (args[i]->const_item())
continue;
diff --git a/sql/item_func.h b/sql/item_func.h
index a3bf4d78158..44e9691c9df 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -342,7 +342,7 @@ class Item_func :public Item_func_or_sum,
bool excl_dep_on_grouping_fields(st_select_lex *sel)
{
- if (has_rand_bit())
+ if (has_rand_bit() || with_subquery())
return false;
return Item_args::excl_dep_on_grouping_fields(sel);
}
1
0
05 Apr '19
revision-id: 3abdc95f082d80a3ce76bad7d6d319f8706429b2 (v5.8-1027-g3abdc95f0)
parent(s): d4bf4b4197d8887c119bcb5d87db6ba5feb9aabc
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-04-05 20:21:19 +0300
message:
Range Locking: fix Makefile-based debug build
- List correct files in the Makefile
- Lots of -Werror=unused-parameter errors
---
Makefile | 3 +++
include/rocksdb/utilities/transaction.h | 5 ++---
src.mk | 8 --------
.../transactions/range_locking/standalone_port.cc | 2 +-
utilities/transactions/transaction_lock_mgr.cc | 21 +++++++++++----------
utilities/transactions/transaction_lock_mgr.h | 8 ++++----
6 files changed, 21 insertions(+), 26 deletions(-)
diff --git a/Makefile b/Makefile
index 9ece4310c..3b9b5453b 100644
--- a/Makefile
+++ b/Makefile
@@ -1564,6 +1564,9 @@ blob_db_test: utilities/blob_db/blob_db_test.o $(LIBOBJECTS) $(TESTHARNESS)
repeatable_thread_test: util/repeatable_thread_test.o $(LIBOBJECTS) $(TESTHARNESS)
$(AM_LINK)
+range_locking_test: utilities/transactions/range_locking_test.o $(LIBOBJECTS) $(TESTHARNESS)
+ $(AM_LINK)
+
#-------------------------------------------------
# make install related stuff
INSTALL_PATH ?= /usr/local
diff --git a/include/rocksdb/utilities/transaction.h b/include/rocksdb/utilities/transaction.h
index 5a2932363..8a15a3bbd 100644
--- a/include/rocksdb/utilities/transaction.h
+++ b/include/rocksdb/utilities/transaction.h
@@ -255,9 +255,8 @@ class Transaction {
// Get a range lock on [start_endpoint; end_endpoint].
// Note: range endpoints generally a use a different data format than
// ranges.
- virtual Status GetRangeLock(ColumnFamilyHandle* column_family,
- const Slice& start_endp,
- const Slice& end_endp) {
+ virtual Status GetRangeLock(ColumnFamilyHandle*,
+ const Slice&, const Slice&) {
return Status::NotSupported();
}
diff --git a/src.mk b/src.mk
index de0644cac..2a19287e0 100644
--- a/src.mk
+++ b/src.mk
@@ -218,20 +218,12 @@ LIB_SOURCES = \
utilities/write_batch_with_index/write_batch_with_index.cc \
utilities/write_batch_with_index/write_batch_with_index_internal.cc \
utilities/transactions/range_locking/locktree/keyrange.cc \
- utilities/transactions/range_locking/locktree/keyrange.h \
- utilities/transactions/range_locking/locktree/txnid_set.h \
utilities/transactions/range_locking/locktree/range_buffer.cc \
- utilities/transactions/range_locking/locktree/locktree.h \
- utilities/transactions/range_locking/locktree/treenode.h \
utilities/transactions/range_locking/locktree/wfg.cc \
utilities/transactions/range_locking/locktree/lock_request.cc \
utilities/transactions/range_locking/locktree/locktree.cc \
- utilities/transactions/range_locking/locktree/range_buffer.h \
utilities/transactions/range_locking/locktree/manager.cc \
- utilities/transactions/range_locking/locktree/wfg.h \
utilities/transactions/range_locking/locktree/txnid_set.cc \
- utilities/transactions/range_locking/locktree/lock_request.h \
- utilities/transactions/range_locking/locktree/concurrent_tree.h \
utilities/transactions/range_locking/util/dbt.cc \
utilities/transactions/range_locking/util/memarena.cc \
utilities/transactions/range_locking/standalone_port.cc \
diff --git a/utilities/transactions/range_locking/standalone_port.cc b/utilities/transactions/range_locking/standalone_port.cc
index f87105e5b..dbdeb0b97 100644
--- a/utilities/transactions/range_locking/standalone_port.cc
+++ b/utilities/transactions/range_locking/standalone_port.cc
@@ -60,7 +60,7 @@ toku_instr_key *manager_escalator_mutex_key;
// portability/memory.cc
size_t
-toku_memory_footprint(void * p, size_t touched)
+toku_memory_footprint(void* , size_t touched)
{
return touched;
}
diff --git a/utilities/transactions/transaction_lock_mgr.cc b/utilities/transactions/transaction_lock_mgr.cc
index f15d9df21..e0802fb25 100644
--- a/utilities/transactions/transaction_lock_mgr.cc
+++ b/utilities/transactions/transaction_lock_mgr.cc
@@ -795,7 +795,7 @@ Status RangeLockMgr::TryRangeLock(PessimisticTransaction* txn,
uint32_t column_family_id,
const rocksdb::Slice &start_key,
const rocksdb::Slice &end_key,
- bool exclusive) {
+ bool /*exclusive*/) {
toku::lock_request request;
request.create(mutex_factory_);
DBT start_key_dbt, end_key_dbt;
@@ -836,8 +836,9 @@ Status RangeLockMgr::TryRangeLock(PessimisticTransaction* txn,
autovector<TransactionID> wait_ids;
bool done= false;
- static void lock_wait_callback(void *cdata, TXNID waiter, TXNID waitee) {
+ static void lock_wait_callback(void *cdata, TXNID /*waiter*/, TXNID waitee) {
auto self= (struct st_wait_info*)cdata;
+ // we know that the waiter is self->txn->GetID() (TODO: assert?)
if (!self->done)
{
self->wait_ids.push_back(waitee);
@@ -893,7 +894,7 @@ Status RangeLockMgr::TryRangeLock(PessimisticTransaction* txn,
// (currently it is the same as getting a range lock)
Status RangeLockMgr::TryLock(PessimisticTransaction* txn,
uint32_t column_family_id,
- const std::string& key, Env* env,
+ const std::string& key, Env*,
bool exclusive) {
std::string endpoint;
convert_key_to_endpoint(rocksdb::Slice(key.data(), key.size()), &endpoint);
@@ -904,7 +905,7 @@ Status RangeLockMgr::TryLock(PessimisticTransaction* txn,
static void
range_lock_mgr_release_lock_int(toku::locktree *lt,
const PessimisticTransaction* txn,
- uint32_t column_family_id,
+ uint32_t /*column_family_id*/,
const std::string& key) {
DBT key_dbt;
toku_fill_dbt(&key_dbt, key.data(), key.size());
@@ -917,13 +918,13 @@ range_lock_mgr_release_lock_int(toku::locktree *lt,
void RangeLockMgr::UnLock(PessimisticTransaction* txn,
uint32_t column_family_id,
- const std::string& key, Env* env) {
+ const std::string& key, Env*) {
range_lock_mgr_release_lock_int(lt, txn, column_family_id, key);
toku::lock_request::retry_all_lock_requests(lt, nullptr /* lock_wait_needed_callback */);
}
void RangeLockMgr::UnLock(const PessimisticTransaction* txn,
- const TransactionKeyMap* key_map, Env* env) {
+ const TransactionKeyMap* key_map, Env*) {
//TODO: if we collect all locks in a range buffer and then
// make one call to lock_tree::release_locks(), will that be faster?
for (auto& key_map_iter : *key_map) {
@@ -939,7 +940,7 @@ void RangeLockMgr::UnLock(const PessimisticTransaction* txn,
toku::lock_request::retry_all_lock_requests(lt, nullptr /* lock_wait_needed_callback */);
}
-void RangeLockMgr::UnLockAll(const PessimisticTransaction* txn, Env* env) {
+void RangeLockMgr::UnLockAll(const PessimisticTransaction* txn, Env*) {
// owned_locks may hold nullptr if the transaction has never acquired any
// locks.
@@ -1023,11 +1024,11 @@ RangeLockMgr::RangeLockMgr(TransactionDB* txn_db,
@param lt Lock Tree where escalation is happening (currently there is only one)
@param buffer Escalation result: list of locks that this transaction now
owns in this lock tree.
- @param extra Callback context
+ @param void* Callback context
*/
-void RangeLockMgr::on_escalate(TXNID txnid, const locktree *lt,
- const range_buffer &buffer, void *extra) {
+void RangeLockMgr::on_escalate(TXNID txnid, const locktree*,
+ const range_buffer &buffer, void *) {
auto txn= (PessimisticTransaction*)txnid;
RangeLockList* trx_locks= (RangeLockList*)txn->owned_locks.get();
diff --git a/utilities/transactions/transaction_lock_mgr.h b/utilities/transactions/transaction_lock_mgr.h
index e3de698f2..d6c5479f2 100644
--- a/utilities/transactions/transaction_lock_mgr.h
+++ b/utilities/transactions/transaction_lock_mgr.h
@@ -195,8 +195,8 @@ class RangeLockMgr :
public BaseLockMgr,
public RangeLockMgrControl {
public:
- void AddColumnFamily(uint32_t column_family_id) override { /* do nothing */ }
- void RemoveColumnFamily(uint32_t column_family_id) override { /* do nothing */ }
+ void AddColumnFamily(uint32_t) override { /* do nothing */ }
+ void RemoveColumnFamily(uint32_t) override { /* do nothing */ }
Status TryLock(PessimisticTransaction* txn, uint32_t column_family_id,
const std::string& key, Env* env, bool exclusive) override ;
@@ -263,8 +263,8 @@ class RangeLockMgr :
static int compare_dbt_endpoints(__toku_db*, void *arg, const DBT *a_key, const DBT *b_key);
// Callbacks
- static int on_create(locktree *lt, void *extra) { return 0; /* no error */ }
- static void on_destroy(locktree *lt) {}
+ static int on_create(locktree*, void*) { return 0; /* no error */ }
+ static void on_destroy(locktree*) {}
static void on_escalate(TXNID txnid, const locktree *lt,
const range_buffer &buffer, void *extra);
1
0
revision-id: d4bf4b4197d8887c119bcb5d87db6ba5feb9aabc (v5.8-1026-gd4bf4b419)
parent(s): 46d49ea0b7e0267b5c360d45d90a64776477b8ed
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-04-05 20:19:18 +0300
message:
Fix range_locking_test.cc
Now use_range_locking is a part of TransactionDBOptions, not a member
of TransactionDB.
---
utilities/transactions/range_locking_test.cc | 1 -
1 file changed, 1 deletion(-)
diff --git a/utilities/transactions/range_locking_test.cc b/utilities/transactions/range_locking_test.cc
index 447e4c44e..26ee9b84c 100644
--- a/utilities/transactions/range_locking_test.cc
+++ b/utilities/transactions/range_locking_test.cc
@@ -72,7 +72,6 @@ class RangeLockingTest : public ::testing::Test {
s = TransactionDB::Open(options, txn_db_options, dbname, &db);
assert(s.ok());
- db->use_range_locking= true;
rocksdb::RangeLockMgrControl *mgr= db->get_range_lock_manager();
assert(mgr);
// can also: mgr->set_max_lock_memory(rocksdb_max_lock_memory);
1
0
revision-id: 8191226affa9597bbe202fd5e2005d13af5c9377 (mariadb-10.4.3-84-g8191226)
parent(s): 83a19306d2581a7e33f4f6697cefb7f43476773d
committer: Alexey Botchkov
timestamp: 2019-04-05 16:20:04 +0400
message:
MDEV-7974 XA transactions.
tokudb_maria.xa test fixed.
---
storage/tokudb/mysql-test/tokudb_mariadb/r/xa.result | 1 +
storage/tokudb/mysql-test/tokudb_mariadb/t/xa.test | 1 +
2 files changed, 2 insertions(+)
diff --git a/storage/tokudb/mysql-test/tokudb_mariadb/r/xa.result b/storage/tokudb/mysql-test/tokudb_mariadb/r/xa.result
index 4724a0a..34233b6 100644
--- a/storage/tokudb/mysql-test/tokudb_mariadb/r/xa.result
+++ b/storage/tokudb/mysql-test/tokudb_mariadb/r/xa.result
@@ -65,4 +65,5 @@ a
20
disconnect con1;
connection default;
+xa rollback 'testb',0x2030405060,11;
drop table t1;
diff --git a/storage/tokudb/mysql-test/tokudb_mariadb/t/xa.test b/storage/tokudb/mysql-test/tokudb_mariadb/t/xa.test
index dc5520a..625abbb 100644
--- a/storage/tokudb/mysql-test/tokudb_mariadb/t/xa.test
+++ b/storage/tokudb/mysql-test/tokudb_mariadb/t/xa.test
@@ -69,5 +69,6 @@ select * from t1;
disconnect con1;
connection default;
+xa rollback 'testb',0x2030405060,11;
drop table t1;
1
0
revision-id: 83a19306d2581a7e33f4f6697cefb7f43476773d (mariadb-10.4.3-83-g83a1930)
parent(s): dfa24e558ea9fb1ace29e97383577fe4e18d0939
committer: Alexey Botchkov
timestamp: 2019-04-05 15:34:14 +0400
message:
MDEV-7974 XA transactions.
FLUSH TABLES WITH READ LOCK now blocks the XA COMMIT/ROLLBACK/PREPARE
statements as they write to the binlog/Innodb redo.
---
mysql-test/main/flush_read_lock.result | 159 ++++++++++++++++++++++++++++++++-
mysql-test/main/flush_read_lock.test | 112 ++++++++++++++++++++++-
mysql-test/main/xa.result | 60 +++++++++++++
mysql-test/main/xa.test | 46 +++++++++-
sql/transaction.cc | 101 ++++++++++++++++++---
5 files changed, 459 insertions(+), 19 deletions(-)
diff --git a/mysql-test/main/flush_read_lock.result b/mysql-test/main/flush_read_lock.result
index 33dc109..9a2f650 100644
--- a/mysql-test/main/flush_read_lock.result
+++ b/mysql-test/main/flush_read_lock.result
@@ -162,7 +162,19 @@ Success: FTWRL is blocked when 'alter event e1 comment 'test'' is active in anot
# --read-only for a discussion why.
#
Success: Was able to run 'analyze table t1_base' under FTWRL.
-Success: Was able to run 'analyze table t1_base' with FTWRL active in another connection.
+Timeout in wait_condition.inc for select count(*) = 0 from information_schema.processlist
+where info = "analyze table t1_base"
+Id User Host db Command Time State Info Progress
+3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+1 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+2 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000
+5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000
+9 root localhost test Query 31 Waiting for backup lock analyze table t1_base 0.000
+10 root localhost test Query 0 Init show full processlist 0.000
+11 root localhost test Sleep 31 NULL 0.000
+12 root localhost test Sleep 31 NULL 0.000
+Error: Wasn't able to run 'analyze table t1_base' with FTWRL active in another connection!
Success: Was able to run FTWRL while 'analyze table t1_base' was active in another connection.
#
# 3) BEGIN, ROLLBACK and COMMIT statements.
@@ -1309,6 +1321,8 @@ unlock tables;
# Check that XA non-COMMIT statements are not and COMMIT is
# blocked by active FTWRL in another connection
#
+# XA COMMIT, XA ROLLBACK and XA PREPARE does take COMMIT lock to ensure
+# that nothing is written to bin log and redo log under FTWRL mode.
connection con1;
flush tables with read lock;
connection default;
@@ -1321,11 +1335,51 @@ connection con1;
flush tables with read lock;
connection default;
xa end 'test1';
-xa prepare 'test1';
-xa rollback 'test1';
+xa prepare 'test1';;
+connection con1;
+Timeout in wait_condition.inc for select count(*) = 1 from information_schema.processlist
+where state = "Waiting for commit lock" and
+info = "xa prepare 'test1'"
+Id User Host db Command Time State Info Progress
+3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+1 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+2 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000
+5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000
+9 root localhost test Query 30 Waiting for backup lock xa prepare 'test1' 0.000
+10 root localhost test Query 0 Init show full processlist 0.000
+11 root localhost test Sleep 30 NULL 0.000
+12 root localhost test Sleep 63 NULL 0.000
+unlock tables;
+# Switching to connection 'default'.
+connection default;
+# Reap XA PREPARE.
+# Switching to connection 'con1'.
connection con1;
+flush tables with read lock;
+# Switching to connection 'default'.
+connection default;
+# Send XA ROLLBACK 'test1'
+xa rollback 'test1';
+# Switching to connection 'con1'.
+connection con1;
+# Wait until XA ROLLBACK is blocked.
+Timeout in wait_condition.inc for select count(*) = 1 from information_schema.processlist
+where state = "Waiting for commit lock" and
+info = "xa rollback 'test1'"
+Id User Host db Command Time State Info Progress
+3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+1 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+2 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000
+5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000
+9 root localhost test Query 30 Waiting for backup lock xa rollback 'test1' 0.000
+10 root localhost test Query 0 Init show full processlist 0.000
+11 root localhost test Sleep 61 NULL 0.000
+12 root localhost test Sleep 94 NULL 0.000
unlock tables;
connection default;
+# Reap XA ROLLBACK
xa start 'test1';
insert into t3_trans values (1);
connection con1;
@@ -1333,7 +1387,33 @@ flush tables with read lock;
connection default;
connection default;
xa end 'test1';
+# Send XA PREPARE 'test1'
xa prepare 'test1';
+# Switching to connection 'con1'.
+connection con1;
+# Wait until XA PREPARE is blocked.
+Timeout in wait_condition.inc for select count(*) = 1 from information_schema.processlist
+where state = "Waiting for commit lock" and
+info = "xa prepare 'test1'"
+Id User Host db Command Time State Info Progress
+3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+1 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+2 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000
+5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000
+9 root localhost test Query 31 Waiting for backup lock xa prepare 'test1' 0.000
+10 root localhost test Query 0 Init show full processlist 0.000
+11 root localhost test Sleep 92 NULL 0.000
+12 root localhost test Sleep 125 NULL 0.000
+unlock tables;
+# Switching to connection 'default'.
+connection default;
+# Reap XA PREPARE.
+# Switching to connection 'con1'.
+connection con1;
+flush tables with read lock;
+# Switching to connection 'default'.
+connection default;
# Send:
xa commit 'test1';;
connection con1;
@@ -1343,6 +1423,77 @@ connection default;
# Reap XA COMMIT.
delete from t3_trans;
#
+# Check that XA COMMIT / ROLLBACK for prepared transaction from a
+# disconnected session is blocked by active FTWRL in another connection.
+#
+# Create temporary connection for XA transaction.
+connect con_tmp,localhost,root,,;
+xa start 'test1';
+insert into t3_trans values (1);
+xa end 'test1';
+xa prepare 'test1';
+# Disconnect temporary connection
+disconnect con_tmp;
+# Create temporary connection for XA transaction.
+connect con_tmp,localhost,root,,;
+xa start 'test2';
+insert into t3_trans values (2);
+xa end 'test2';
+xa prepare 'test2';
+# Disconnect temporary connection
+disconnect con_tmp;
+# Switching to connection 'con1'.
+connection con1;
+flush tables with read lock;
+# Switching to connection 'default'.
+connection default;
+# Send XA ROLLBACK 'test1'
+xa rollback 'test1';
+# Switching to connection 'con1'.
+connection con1;
+# Wait until XA ROLLBACK is blocked.
+Timeout in wait_condition.inc for select count(*) = 1 from information_schema.processlist
+where state = "Waiting for commit lock" and
+info = "xa rollback 'test1'"
+Id User Host db Command Time State Info Progress
+3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+1 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+2 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000
+5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000
+9 root localhost test Query 30 Waiting for backup lock xa rollback 'test1' 0.000
+10 root localhost test Query 0 Init show full processlist 0.000
+11 root localhost test Sleep 123 NULL 0.000
+12 root localhost test Sleep 157 NULL 0.000
+unlock tables;
+flush tables with read lock;
+# Switching to connection 'default'.
+connection default;
+# Reap XA ROLLBACK
+# Send XA COMMIT
+xa commit 'test2';;
+# Switching to connection 'con1'.
+connection con1;
+# Wait until XA COMMIT is blocked.
+Timeout in wait_condition.inc for select count(*) = 1 from information_schema.processlist
+where state = "Waiting for commit lock" and
+info = "xa commit 'test2'"
+Id User Host db Command Time State Info Progress
+3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+1 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
+2 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000
+5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000
+9 root localhost test Query 31 Waiting for backup lock xa commit 'test2' 0.000
+10 root localhost test Query 0 Init show full processlist 0.000
+11 root localhost test Sleep 154 NULL 0.000
+12 root localhost test Sleep 188 NULL 0.000
+unlock tables;
+# Switching to connection 'default'.
+connection default;
+# Reap XA COMMIT.
+delete from t3_trans;
+#
# Check that XA COMMIT blocks FTWRL in another connection.
xa start 'test1';
insert into t3_trans values (1);
@@ -1412,6 +1563,7 @@ flush tables with read lock;
# Implicit commits are allowed under FTWRL.
analyze table t3_trans;
Table Op Msg_type Msg_text
+test.t3_trans analyze status Engine-independent statistics collected
test.t3_trans analyze status OK
unlock tables;
#
@@ -1425,6 +1577,7 @@ unlock tables;
connection default;
# Reap ANALYZE TABLE
Table Op Msg_type Msg_text
+test.t3_trans analyze status Engine-independent statistics collected
test.t3_trans analyze status OK
#
# 39.1.b) CHECK TABLE for transactional table is compatible with FTWRL.
diff --git a/mysql-test/main/flush_read_lock.test b/mysql-test/main/flush_read_lock.test
index f39dbec..74b7117 100644
--- a/mysql-test/main/flush_read_lock.test
+++ b/mysql-test/main/flush_read_lock.test
@@ -1590,6 +1590,8 @@ unlock tables;
--echo # Check that XA non-COMMIT statements are not and COMMIT is
--echo # blocked by active FTWRL in another connection
--echo #
+--echo # XA COMMIT, XA ROLLBACK and XA PREPARE does take COMMIT lock to ensure
+--echo # that nothing is written to bin log and redo log under FTWRL mode.
connection $con_aux1;
flush tables with read lock;
connection default;
@@ -1602,11 +1604,37 @@ connection $con_aux1;
flush tables with read lock;
connection default;
xa end 'test1';
-xa prepare 'test1';
-xa rollback 'test1';
+--send xa prepare 'test1';
connection $con_aux1;
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Waiting for commit lock" and
+ info = "xa prepare 'test1'";
+--source include/wait_condition.inc
+unlock tables;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap XA PREPARE.
+--reap
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+flush tables with read lock;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Send XA ROLLBACK 'test1'
+--send xa rollback 'test1'
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+--echo # Wait until XA ROLLBACK is blocked.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Waiting for commit lock" and
+ info = "xa rollback 'test1'";
+--source include/wait_condition.inc
unlock tables;
connection default;
+--echo # Reap XA ROLLBACK
+--reap
xa start 'test1';
insert into t3_trans values (1);
connection $con_aux1;
@@ -1614,7 +1642,27 @@ flush tables with read lock;
connection default;
connection default;
xa end 'test1';
-xa prepare 'test1';
+--echo # Send XA PREPARE 'test1'
+--send xa prepare 'test1'
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+--echo # Wait until XA PREPARE is blocked.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Waiting for commit lock" and
+ info = "xa prepare 'test1'";
+--source include/wait_condition.inc
+unlock tables;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap XA PREPARE.
+--reap
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+flush tables with read lock;
+--echo # Switching to connection 'default'.
+connection default;
+
--echo # Send:
--send xa commit 'test1';
connection $con_aux1;
@@ -1630,6 +1678,64 @@ connection default;
--reap
delete from t3_trans;
--echo #
+--echo # Check that XA COMMIT / ROLLBACK for prepared transaction from a
+--echo # disconnected session is blocked by active FTWRL in another connection.
+--echo #
+--echo # Create temporary connection for XA transaction.
+connect (con_tmp,localhost,root,,);
+xa start 'test1';
+insert into t3_trans values (1);
+xa end 'test1';
+xa prepare 'test1';
+--echo # Disconnect temporary connection
+disconnect con_tmp;
+--echo # Create temporary connection for XA transaction.
+connect (con_tmp,localhost,root,,);
+xa start 'test2';
+insert into t3_trans values (2);
+xa end 'test2';
+xa prepare 'test2';
+--echo # Disconnect temporary connection
+disconnect con_tmp;
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+flush tables with read lock;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Send XA ROLLBACK 'test1'
+--send xa rollback 'test1'
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+--echo # Wait until XA ROLLBACK is blocked.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Waiting for commit lock" and
+ info = "xa rollback 'test1'";
+--source include/wait_condition.inc
+unlock tables;
+flush tables with read lock;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap XA ROLLBACK
+--reap
+--echo # Send XA COMMIT
+--send xa commit 'test2';
+--echo # Switching to connection '$con_aux1'.
+connection $con_aux1;
+--echo # Wait until XA COMMIT is blocked.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Waiting for commit lock" and
+ info = "xa commit 'test2'";
+--source include/wait_condition.inc
+unlock tables;
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reap XA COMMIT.
+--reap
+delete from t3_trans;
+
+--echo #
--echo # Check that XA COMMIT blocks FTWRL in another connection.
xa start 'test1';
insert into t3_trans values (1);
diff --git a/mysql-test/main/xa.result b/mysql-test/main/xa.result
index a609b68..62e9c9b 100644
--- a/mysql-test/main/xa.result
+++ b/mysql-test/main/xa.result
@@ -355,3 +355,63 @@ DROP TABLE t1, t2, t3;
xa rollback 'testb',0x2030405060,11;
XA RECOVER;
formatID gtrid_length bqual_length data
+# Check XA state when lock_wait_timeout happens
+# More tests added to flush_read_lock.test
+connect con_tmp,localhost,root,,;
+set session lock_wait_timeout=1;
+create table asd (a int);
+xa start 'test1';
+insert into asd values(1);
+xa end 'test1';
+connection default;
+flush table with read lock;
+connection con_tmp;
+# PREPARE error will do auto rollback.
+xa prepare 'test1';
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+show errors;
+Level Code Message
+Error 1205 Lock wait timeout exceeded; try restarting transaction
+Error 1402 XA_RBROLLBACK: Transaction branch was rolled back
+connection default;
+unlock tables;
+connection con_tmp;
+xa start 'test1';
+insert into asd values(1);
+xa end 'test1';
+xa prepare 'test1';
+connection default;
+flush tables with read lock;
+connection con_tmp;
+# LOCK error during ROLLBACK will not alter transaction state.
+xa rollback 'test1';
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+show errors;
+Level Code Message
+Error 1205 Lock wait timeout exceeded; try restarting transaction
+Error 1401 XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
+xa recover;
+formatID gtrid_length bqual_length data
+1 5 0 test1
+# LOCK error during COMMIT will not alter transaction state.
+xa commit 'test1';
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+show errors;
+Level Code Message
+Error 1205 Lock wait timeout exceeded; try restarting transaction
+Error 1401 XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
+xa recover;
+formatID gtrid_length bqual_length data
+1 5 0 test1
+connection default;
+unlock tables;
+connection con_tmp;
+xa rollback 'test1';
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+xa recover;
+formatID gtrid_length bqual_length data
+drop table asd;
+disconnect con_tmp;
+connection default;
diff --git a/mysql-test/main/xa.test b/mysql-test/main/xa.test
index 7a5cc38..6f56779 100644
--- a/mysql-test/main/xa.test
+++ b/mysql-test/main/xa.test
@@ -468,7 +468,6 @@ REPLACE INTO t2 SELECT * FROM t2;
INSERT INTO t3 VALUES (1);
XA BEGIN 'xid3';
-
#Cleanup
--disconnect con2
--connection default
@@ -481,3 +480,48 @@ XA RECOVER;
--source include/wait_until_count_sessions.inc
+--echo # Check XA state when lock_wait_timeout happens
+--echo # More tests added to flush_read_lock.test
+connect (con_tmp,localhost,root,,);
+set session lock_wait_timeout=1;
+create table asd (a int);
+xa start 'test1';
+insert into asd values(1);
+xa end 'test1';
+connection default;
+flush table with read lock;
+connection con_tmp;
+--echo # PREPARE error will do auto rollback.
+--ERROR ER_LOCK_WAIT_TIMEOUT
+xa prepare 'test1';
+show errors;
+connection default;
+unlock tables;
+
+connection con_tmp;
+xa start 'test1';
+insert into asd values(1);
+xa end 'test1';
+xa prepare 'test1';
+connection default;
+flush tables with read lock;
+connection con_tmp;
+--echo # LOCK error during ROLLBACK will not alter transaction state.
+--ERROR ER_LOCK_WAIT_TIMEOUT
+xa rollback 'test1';
+show errors;
+xa recover;
+--echo # LOCK error during COMMIT will not alter transaction state.
+--ERROR ER_LOCK_WAIT_TIMEOUT
+xa commit 'test1';
+show errors;
+xa recover;
+connection default;
+unlock tables;
+connection con_tmp;
+xa rollback 'test1';
+xa recover;
+drop table asd;
+disconnect con_tmp;
+--source include/wait_until_disconnected.inc
+connection default;
diff --git a/sql/transaction.cc b/sql/transaction.cc
index c7b4b80..bef8caa 100644
--- a/sql/transaction.cc
+++ b/sql/transaction.cc
@@ -1047,18 +1047,39 @@ bool trans_xa_prepare(THD *thd)
xa_state_names[thd->transaction.xid_state.xa_state]);
else if (!thd->transaction.xid_state.xid.eq(thd->lex->xid))
my_error(ER_XAER_NOTA, MYF(0));
- else if (ha_prepare(thd))
- {
- xid_cache_delete(thd, &thd->transaction.xid_state);
- thd->transaction.xid_state.xa_state= XA_NOTR;
- my_error(ER_XA_RBROLLBACK, MYF(0));
- }
else
{
- res= 0;
- thd->transaction.xid_state.xa_state= XA_PREPARED;
- if (thd->variables.pseudo_slave_mode)
- res= applier_reset_xa_trans(thd);
+ /*
+ Acquire metadata lock which will ensure that COMMIT is blocked
+ by active FLUSH TABLES WITH READ LOCK (and vice versa COMMIT in
+ progress blocks FTWRL).
+
+ We allow FLUSHer to COMMIT; we assume FLUSHer knows what it does.
+ */
+ MDL_request mdl_request;
+ mdl_request.init(MDL_key::BACKUP, "", "", MDL_BACKUP_COMMIT,
+ MDL_STATEMENT);
+ if (thd->mdl_context.acquire_lock(&mdl_request,
+ thd->variables.lock_wait_timeout) ||
+ ha_prepare(thd))
+ {
+ if (!mdl_request.ticket)
+ ha_rollback_trans(thd, TRUE);
+ thd->variables.option_bits&= ~(OPTION_BEGIN | OPTION_KEEP_LOG);
+ thd->transaction.all.reset();
+ thd->server_status&=
+ ~(SERVER_STATUS_IN_TRANS | SERVER_STATUS_IN_TRANS_READONLY);
+ xid_cache_delete(thd, &thd->transaction.xid_state);
+ thd->transaction.xid_state.xa_state= XA_NOTR;
+ my_error(ER_XA_RBROLLBACK, MYF(0));
+ }
+ else
+ {
+ res= 0;
+ thd->transaction.xid_state.xa_state= XA_PREPARED;
+ if (thd->variables.pseudo_slave_mode)
+ res= applier_reset_xa_trans(thd);
+ }
}
DBUG_RETURN(res);
@@ -1101,6 +1122,27 @@ bool trans_xa_commit(THD *thd)
else
{
res= xa_trans_rolled_back(xs);
+ /*
+ Acquire metadata lock which will ensure that COMMIT is blocked
+ by active FLUSH TABLES WITH READ LOCK (and vice versa COMMIT in
+ progress blocks FTWRL).
+
+ We allow FLUSHer to COMMIT; we assume FLUSHer knows what it does.
+ */
+ MDL_request mdl_request;
+ mdl_request.init(MDL_key::BACKUP, "", "", MDL_BACKUP_COMMIT,
+ MDL_STATEMENT);
+ if (thd->mdl_context.acquire_lock(&mdl_request,
+ thd->variables.lock_wait_timeout))
+ {
+ /*
+ We can't rollback an XA transaction on lock failure due to
+ Innodb redo log and bin log update is involved in rollback.
+ Return error to user for a retry.
+ */
+ my_error(ER_XAER_RMERR, MYF(0));
+ DBUG_RETURN(true);
+ }
ha_commit_or_rollback_by_xid(thd->lex->xid, !res);
if((WSREP_EMULATE_BINLOG(thd) || mysql_bin_log.is_open()) &&
xs->is_binlogged)
@@ -1139,13 +1181,18 @@ bool trans_xa_commit(THD *thd)
We allow FLUSHer to COMMIT; we assume FLUSHer knows what it does.
*/
mdl_request.init(MDL_key::BACKUP, "", "", MDL_BACKUP_COMMIT,
- MDL_TRANSACTION);
+ MDL_STATEMENT);
if (thd->mdl_context.acquire_lock(&mdl_request,
thd->variables.lock_wait_timeout))
{
- ha_rollback_trans(thd, TRUE);
+ /*
+ We can't rollback an XA transaction on lock failure due to
+ Innodb redo log and bin log update is involved in rollback.
+ Return error to user for a retry.
+ */
my_error(ER_XAER_RMERR, MYF(0));
+ DBUG_RETURN(true);
}
else
{
@@ -1213,6 +1260,21 @@ bool trans_xa_rollback(THD *thd)
my_error(ER_XAER_NOTA, MYF(0));
else
{
+ MDL_request mdl_request;
+ mdl_request.init(MDL_key::BACKUP, "", "", MDL_BACKUP_COMMIT,
+ MDL_STATEMENT);
+ if (thd->mdl_context.acquire_lock(&mdl_request,
+ thd->variables.lock_wait_timeout))
+ {
+ /*
+ We can't rollback an XA transaction on lock failure due to
+ Innodb redo log and bin log update is involved in rollback.
+ Return error to user for a retry.
+ */
+ my_error(ER_XAER_RMERR, MYF(0));
+ DBUG_RETURN(true);
+ }
+
xa_trans_rolled_back(xs);
if (ha_commit_or_rollback_by_xid(thd->lex->xid, 0) == 0 &&
xs->is_binlogged &&
@@ -1232,6 +1294,21 @@ bool trans_xa_rollback(THD *thd)
DBUG_RETURN(TRUE);
}
+ MDL_request mdl_request;
+ mdl_request.init(MDL_key::BACKUP, "", "", MDL_BACKUP_COMMIT,
+ MDL_STATEMENT);
+ if (thd->mdl_context.acquire_lock(&mdl_request,
+ thd->variables.lock_wait_timeout))
+ {
+ /*
+ We can't rollback an XA transaction on lock failure due to
+ Innodb redo log and bin log update is involved in rollback.
+ Return error to user for a retry.
+ */
+ my_error(ER_XAER_RMERR, MYF(0));
+ DBUG_RETURN(true);
+ }
+
if(xa_state == XA_PREPARED && thd->transaction.xid_state.is_binlogged &&
(WSREP_EMULATE_BINLOG(thd) || mysql_bin_log.is_open()))
{
1
0
[Commits] 1c6c0e651fc: MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly
by Varun 05 Apr '19
by Varun 05 Apr '19
05 Apr '19
revision-id: 1c6c0e651fc3d6c576f2c49c8642b8b54f94f415 (mariadb-10.4.3-162-g1c6c0e651fc)
parent(s): 02d9b048a2ab549a3227a81e15ff2f8c45562a65
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-04-05 14:39:01 +0530
message:
MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly
Changed the function append_range_all_keyparts to use sel_arg_range_seq_init / sel_arg_range_seq_next to produce ranges.
Also adjusted to print format for the ranges, now the ranges are printed as:
(keypart1_min, keypart2_min,..) OP (keypart1_name,keypart2_name, ..) OP (keypart1_max,keypart2_max, ..)
Also added more tests for range and index merge access for optimizer trace
---
mysql-test/main/opt_trace.result | 276 ++++++++++-
mysql-test/main/opt_trace.test | 57 +++
mysql-test/main/opt_trace_index_merge.result | 509 ++++++++++++++++++++-
mysql-test/main/opt_trace_index_merge.test | 112 +++++
.../main/opt_trace_index_merge_innodb.result | 6 +-
sql/opt_range.cc | 283 ++++++------
sql/opt_range.h | 7 +-
7 files changed, 1080 insertions(+), 170 deletions(-)
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 12d4c713886..ed5881e08fd 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1248,7 +1248,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a {
{
"index": "a",
"covering": true,
- "ranges": ["2 <= b <= 2 AND 3 <= c <= 3"],
+ "ranges": ["(2,3) <= (b,c) <= (2,3)"],
"rows": 8,
"cost": 2.2
}
@@ -1264,7 +1264,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a {
"rows": 8,
"cost": 2.2,
"key_parts_used_for_access": ["a", "b", "c"],
- "ranges": ["2 <= b <= 2 AND 3 <= c <= 3"],
+ "ranges": ["(2,3) <= (b,c) <= (2,3)"],
"chosen": false,
"cause": "cost"
},
@@ -1446,7 +1446,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
{
"index": "id",
"covering": true,
- "ranges": ["0x24a20f <= a"],
+ "ranges": ["(0x24a20f) <= (a)"],
"rows": 9,
"cost": 2.35
}
@@ -1462,7 +1462,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
"rows": 9,
"cost": 2.35,
"key_parts_used_for_access": ["id"],
- "ranges": ["0x24a20f <= a"],
+ "ranges": ["(0x24a20f) <= (a)"],
"chosen": false,
"cause": "cost"
},
@@ -1624,7 +1624,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
{
"index": "id",
"covering": true,
- "ranges": ["0x24a20f <= a <= 0x24a20f"],
+ "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
"rows": 9,
"cost": 2.35
}
@@ -1640,7 +1640,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
"rows": 9,
"cost": 2.35,
"key_parts_used_for_access": ["id", "a"],
- "ranges": ["0x24a20f <= a <= 0x24a20f"],
+ "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
"chosen": false,
"cause": "cost"
},
@@ -1856,7 +1856,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"range_scan_alternatives": [
{
"index": "a_c",
- "ranges": ["1 <= a <= 1"],
+ "ranges": ["(1) <= (a) <= (1)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
@@ -1866,7 +1866,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
},
{
"index": "a_b",
- "ranges": ["1 <= a <= 1 AND 2 <= b <= 2"],
+ "ranges": ["(1,2) <= (a,b) <= (1,2)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
@@ -1885,7 +1885,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"type": "range_scan",
"index": "a_b",
"rows": 21,
- "ranges": ["1 <= a <= 1 AND 2 <= b <= 2"]
+ "ranges": ["(1,2) <= (a,b) <= (1,2)"]
},
"rows_for_plan": 21,
"cost_for_plan": 27.445,
@@ -2025,7 +2025,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"range_scan_alternatives": [
{
"index": "a_c",
- "ranges": ["1 <= a <= 1"],
+ "ranges": ["(1) <= (a) <= (1)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
@@ -2044,7 +2044,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"type": "range_scan",
"index": "a_c",
"rows": 180,
- "ranges": ["1 <= a <= 1"]
+ "ranges": ["(1) <= (a) <= (1)"]
},
"rows_for_plan": 180,
"cost_for_plan": 231.72,
@@ -2895,7 +2895,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"range_scan_alternatives": [
{
"index": "pk",
- "ranges": ["2 <= pk <= 2"],
+ "ranges": ["(2) <= (pk) <= (2)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
@@ -2906,7 +2906,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
},
{
"index": "pk_a",
- "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5"],
+ "ranges": ["(2,5) <= (pk,a) <= (2,5)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
@@ -2917,7 +2917,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
},
{
"index": "pk_a_b",
- "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5 AND 1 <= b <= 1"],
+ "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
@@ -2964,7 +2964,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"type": "range_scan",
"index": "pk_a_b",
"rows": 1,
- "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5 AND 1 <= b <= 1"]
+ "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"]
},
"rows_for_plan": 1,
"cost_for_plan": 1.1793,
@@ -3338,7 +3338,7 @@ explain delete from t0 where t0.a<3 {
"range_scan_alternatives": [
{
"index": "a",
- "ranges": ["NULL < a < 3"],
+ "ranges": ["(NULL) < (a) < (3)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
@@ -3354,7 +3354,7 @@ explain delete from t0 where t0.a<3 {
"type": "range_scan",
"index": "a",
"rows": 3,
- "ranges": ["NULL < a < 3"]
+ "ranges": ["(NULL) < (a) < (3)"]
},
"rows_for_plan": 3,
"cost_for_plan": 5.007,
@@ -3481,7 +3481,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"range_scan_alternatives": [
{
"index": "a",
- "ranges": ["NULL < a < 3"],
+ "ranges": ["(NULL) < (a) < (3)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
@@ -3500,7 +3500,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"type": "range_scan",
"index": "a",
"rows": 3,
- "ranges": ["NULL < a < 3"]
+ "ranges": ["(NULL) < (a) < (3)"]
},
"rows_for_plan": 3,
"cost_for_plan": 1.407,
@@ -3546,7 +3546,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"range_scan_alternatives": [
{
"index": "a",
- "ranges": ["NULL < a < 3"],
+ "ranges": ["(NULL) < (a) < (3)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
@@ -3565,7 +3565,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"type": "range_scan",
"index": "a",
"rows": 3,
- "ranges": ["NULL < a < 3"]
+ "ranges": ["(NULL) < (a) < (3)"]
},
"rows_for_plan": 3,
"cost_for_plan": 1.407,
@@ -6034,4 +6034,238 @@ COUNT(*)
1
DROP VIEW v1;
DROP TABLE t1;
+#
+# MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly.
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 ( a int, b int, key a_b(a,b));
+insert into t1 select a,a from one_k;
+set optimizer_trace='enabled=on';
+explain select * from t1 force index (a_b) where a=2 and b=4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_b a_b 10 const,const 1 Using index
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "a_b",
+ "ranges":
+ [
+ "(2,4) <= (a,b) <= (2,4)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 1,
+ "cost": 1.1783,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+explain select * from t1 where a >= 900 and b between 10 and 20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a_b a_b 10 NULL 107 Using where; Using index
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "a_b",
+ "ranges":
+ [
+ "(900,10) <= (a,b)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 107,
+ "cost": 10.955,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t0,t1;
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+explain select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range start_date start_date 8 NULL 1000 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "start_date",
+ "ranges":
+ [
+ "(0x4ac60f,NULL) < (start_date,end_date)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1000,
+ "cost": 1282.2,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1,one_k;
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+a int not null,
+b int not null,
+c int not null,
+d int not null,
+key a_b_c(a,b,c)
+);
+insert into t1 select a,a, a,a from ten;
+explain select * from t1 force index(a_b_c) where a between 1 and 4 and b < 50;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a_b_c a_b_c 8 NULL 4 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "a_b_c",
+ "ranges":
+ [
+ "(1) <= (a) < (4,50)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 4,
+ "cost": 6.2648,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table ten,t1;
+# Ported test from MYSQL for ranges involving Binary column
+CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C');
+INSERT INTO t1 VALUES (2, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 17 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(0xd95b94336a9946a39cf5b58cfe772d8c) <= (b) <= (0xd95b94336a9946a39cf5b58cfe772d8c)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3797,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+EXPLAIN SELECT * FROM t1 WHERE b IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 17 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(NULL) <= (b) <= (NULL)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3797,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 4ec7c338acd..981a53ac1ad 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -387,4 +387,61 @@ SELECT COUNT(*) FROM v1 WHERE MATCH (f) AGAINST ('fooba');
DROP VIEW v1;
DROP TABLE t1;
+--echo #
+--echo # MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly.
+--echo #
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 ( a int, b int, key a_b(a,b));
+insert into t1 select a,a from one_k;
+set optimizer_trace='enabled=on';
+
+explain select * from t1 force index (a_b) where a=2 and b=4;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+explain select * from t1 where a >= 900 and b between 10 and 20;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t0,t1;
+
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+--disable_warnings
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+--enable_warnings
+explain select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1,one_k;
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+ a int not null,
+ b int not null,
+ c int not null,
+ d int not null,
+ key a_b_c(a,b,c)
+);
+
+insert into t1 select a,a, a,a from ten;
+explain select * from t1 force index(a_b_c) where a between 1 and 4 and b < 50;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table ten,t1;
+
+--echo # Ported test from MYSQL for ranges involving Binary column
+
+CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C');
+INSERT INTO t1 VALUES (2, NULL);
+
+EXPLAIN SELECT * FROM t1 WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C);
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE b IS NULL;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t1;
+
set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result
index 50daef815d6..b5e68d04615 100644
--- a/mysql-test/main/opt_trace_index_merge.result
+++ b/mysql-test/main/opt_trace_index_merge.result
@@ -110,7 +110,7 @@ explain select * from t1 where a=1 or b=1 {
"range_scan_alternatives": [
{
"index": "a",
- "ranges": ["1 <= a <= 1"],
+ "ranges": ["(1) <= (a) <= (1)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
@@ -126,7 +126,7 @@ explain select * from t1 where a=1 or b=1 {
"range_scan_alternatives": [
{
"index": "b",
- "ranges": ["1 <= b <= 1"],
+ "ranges": ["(1) <= (b) <= (1)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
@@ -147,7 +147,7 @@ explain select * from t1 where a=1 or b=1 {
"type": "range_scan",
"index": "a",
"rows": 1,
- "ranges": ["1 <= a <= 1"],
+ "ranges": ["(1) <= (a) <= (1)"],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
}
@@ -156,7 +156,7 @@ explain select * from t1 where a=1 or b=1 {
"type": "range_scan",
"index": "b",
"rows": 1,
- "ranges": ["1 <= b <= 1"],
+ "ranges": ["(1) <= (b) <= (1)"],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
}
@@ -176,13 +176,13 @@ explain select * from t1 where a=1 or b=1 {
"type": "range_scan",
"index": "a",
"rows": 1,
- "ranges": ["1 <= a <= 1"]
+ "ranges": ["(1) <= (a) <= (1)"]
},
{
"type": "range_scan",
"index": "b",
"rows": 1,
- "ranges": ["1 <= b <= 1"]
+ "ranges": ["(1) <= (b) <= (1)"]
}
]
},
@@ -243,3 +243,500 @@ explain select * from t1 where a=1 or b=1 {
drop table t0,t1;
set optimizer_trace="enabled=off";
set @@optimizer_switch= @tmp_opt_switch;
+# More tests added index_merge access
+create table t1
+(
+/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
+st_a int not null default 0,
+swt1a int not null default 0,
+swt2a int not null default 0,
+st_b int not null default 0,
+swt1b int not null default 0,
+swt2b int not null default 0,
+/* fields/keys for row retrieval tests */
+key1 int,
+key2 int,
+key3 int,
+key4 int,
+/* make rows much bigger then keys */
+filler1 char (200),
+filler2 char (200),
+filler3 char (200),
+filler4 char (200),
+filler5 char (200),
+filler6 char (200),
+/* order of keys is important */
+key sta_swt12a(st_a,swt1a,swt2a),
+key sta_swt1a(st_a,swt1a),
+key sta_swt2a(st_a,swt2a),
+key sta_swt21a(st_a,swt2a,swt1a),
+key st_a(st_a),
+key stb_swt1a_2b(st_b,swt1b,swt2a),
+key stb_swt1b(st_b,swt1b),
+key st_b(st_b),
+key(key1),
+key(key2),
+key(key3),
+key(key4)
+) ;
+create table t0 as select * from t1;
+# Printing of many insert into t0 values (....) disabled.
+alter table t1 disable keys;
+# Printing of many insert into t1 select .... from t0 disabled.
+# Printing of many insert into t1 (...) values (....) disabled.
+alter table t1 enable keys;
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
+set optimizer_trace='enabled=on';
+# 3-way ROR-intersection
+explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 2 Using intersect(key1,key2,key3); Using where; Using index
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "key1",
+ "ranges":
+ [
+ "(100) <= (key1) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2243,
+ "cost": 2862.1,
+ "chosen": true
+ },
+
+ {
+ "index": "key2",
+ "ranges":
+ [
+ "(100) <= (key2) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2243,
+ "cost": 2862.1,
+ "chosen": false,
+ "cause": "cost"
+ },
+
+ {
+ "index": "key3",
+ "ranges":
+ [
+ "(100) <= (key3) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2243,
+ "cost": 2862.1,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "intersecting_indexes":
+ [
+
+ {
+ "index": "key1",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 58.252,
+ "disk_sweep_cost": 1923.1,
+ "cumulative_total_cost": 1981.4,
+ "usable": true,
+ "matching_rows_now": 2243,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ },
+
+ {
+ "index": "key2",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 116.5,
+ "disk_sweep_cost": 84.518,
+ "cumulative_total_cost": 201.02,
+ "usable": true,
+ "matching_rows_now": 77.636,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ },
+
+ {
+ "index": "key3",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 174.76,
+ "disk_sweep_cost": 0,
+ "cumulative_total_cost": 174.76,
+ "usable": true,
+ "matching_rows_now": 2.6872,
+ "intersect_covering_with_this_index": true,
+ "chosen": true
+ }
+ ],
+ "clustered_pk":
+ {
+ "clustered_pk_added_to_intersect": false,
+ "cause": "no clustered pk index"
+ },
+ "rows": 2,
+ "cost": 174.76,
+ "covering": true,
+ "chosen": true
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary'))
+[
+
+ {
+ "range_access_plan":
+ {
+ "type": "index_roworder_intersect",
+ "rows": 2,
+ "cost": 174.76,
+ "covering": true,
+ "clustered_pk_scan": false,
+ "intersect_of":
+ [
+
+ {
+ "type": "range_scan",
+ "index": "key1",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key1) <= (100)"
+ ]
+ },
+
+ {
+ "type": "range_scan",
+ "index": "key2",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key2) <= (100)"
+ ]
+ },
+
+ {
+ "type": "range_scan",
+ "index": "key3",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key3) <= (100)"
+ ]
+ }
+ ]
+ },
+ "rows_for_plan": 2,
+ "cost_for_plan": 174.76,
+ "chosen": true
+ }
+]
+# ROR-union(ROR-intersection, ROR-range)
+explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+
+ {
+ "indexes_to_merge":
+ [
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "key1",
+ "ranges":
+ [
+ "(100) <= (key1) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 2243,
+ "cost": 170.53,
+ "chosen": true
+ },
+
+ {
+ "index": "key2",
+ "ranges":
+ [
+ "(100) <= (key2) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 2243,
+ "cost": 170.53,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ],
+ "index_to_merge": "key1",
+ "cumulated_cost": 170.53
+ },
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "key3",
+ "ranges":
+ [
+ "(100) <= (key3) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 2243,
+ "cost": 170.53,
+ "chosen": true
+ },
+
+ {
+ "index": "key4",
+ "ranges":
+ [
+ "(100) <= (key4) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 2243,
+ "cost": 170.53,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ],
+ "index_to_merge": "key3",
+ "cumulated_cost": 341.05
+ }
+ ],
+ "cost_of_reading_ranges": 341.05,
+ "use_roworder_union": true,
+ "cause": "always cheaper than non roworder retrieval",
+ "analyzing_roworder_scans":
+ [
+
+ {
+ "type": "range_scan",
+ "index": "key1",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key1) <= (100)"
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "intersecting_indexes":
+ [
+
+ {
+ "index": "key1",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 58.252,
+ "disk_sweep_cost": 1923.1,
+ "cumulative_total_cost": 1981.4,
+ "usable": true,
+ "matching_rows_now": 2243,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ },
+
+ {
+ "index": "key2",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 116.5,
+ "disk_sweep_cost": 84.518,
+ "cumulative_total_cost": 201.02,
+ "usable": true,
+ "matching_rows_now": 77.636,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ }
+ ],
+ "clustered_pk":
+ {
+ "clustered_pk_added_to_intersect": false,
+ "cause": "no clustered pk index"
+ },
+ "rows": 77,
+ "cost": 201.02,
+ "covering": false,
+ "chosen": true
+ }
+ },
+
+ {
+ "type": "range_scan",
+ "index": "key3",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key3) <= (100)"
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "intersecting_indexes":
+ [
+
+ {
+ "index": "key3",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 58.252,
+ "disk_sweep_cost": 1923.1,
+ "cumulative_total_cost": 1981.4,
+ "usable": true,
+ "matching_rows_now": 2243,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ },
+
+ {
+ "index": "key4",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 116.5,
+ "disk_sweep_cost": 84.518,
+ "cumulative_total_cost": 201.02,
+ "usable": true,
+ "matching_rows_now": 77.636,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ }
+ ],
+ "clustered_pk":
+ {
+ "clustered_pk_added_to_intersect": false,
+ "cause": "no clustered pk index"
+ },
+ "rows": 77,
+ "cost": 201.02,
+ "covering": false,
+ "chosen": true
+ }
+ }
+ ],
+ "index_roworder_union_cost": 386.73,
+ "members": 2,
+ "chosen": true
+ }
+ ]
+ }
+]
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary'))
+[
+
+ {
+ "range_access_plan":
+ {
+ "type": "index_roworder_union",
+ "union_of":
+ [
+
+ {
+ "type": "index_roworder_intersect",
+ "rows": 77,
+ "cost": 201.02,
+ "covering": false,
+ "clustered_pk_scan": false,
+ "intersect_of":
+ [
+
+ {
+ "type": "range_scan",
+ "index": "key1",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key1) <= (100)"
+ ]
+ },
+
+ {
+ "type": "range_scan",
+ "index": "key2",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key2) <= (100)"
+ ]
+ }
+ ]
+ },
+
+ {
+ "type": "index_roworder_intersect",
+ "rows": 77,
+ "cost": 201.02,
+ "covering": false,
+ "clustered_pk_scan": false,
+ "intersect_of":
+ [
+
+ {
+ "type": "range_scan",
+ "index": "key3",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key3) <= (100)"
+ ]
+ },
+
+ {
+ "type": "range_scan",
+ "index": "key4",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key4) <= (100)"
+ ]
+ }
+ ]
+ }
+ ]
+ },
+ "rows_for_plan": 154,
+ "cost_for_plan": 386.73,
+ "chosen": true
+ }
+]
+drop table t0,t1;
+set optimizer_trace="enabled=off";
diff --git a/mysql-test/main/opt_trace_index_merge.test b/mysql-test/main/opt_trace_index_merge.test
index d5efaf81db5..73240b6a9e2 100644
--- a/mysql-test/main/opt_trace_index_merge.test
+++ b/mysql-test/main/opt_trace_index_merge.test
@@ -19,3 +19,115 @@ select * from information_schema.OPTIMIZER_TRACE;
drop table t0,t1;
set optimizer_trace="enabled=off";
set @@optimizer_switch= @tmp_opt_switch;
+
+--echo # More tests added index_merge access
+
+--enable_warnings
+create table t1
+(
+ /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
+ st_a int not null default 0,
+ swt1a int not null default 0,
+ swt2a int not null default 0,
+
+ st_b int not null default 0,
+ swt1b int not null default 0,
+ swt2b int not null default 0,
+
+ /* fields/keys for row retrieval tests */
+ key1 int,
+ key2 int,
+ key3 int,
+ key4 int,
+
+ /* make rows much bigger then keys */
+ filler1 char (200),
+ filler2 char (200),
+ filler3 char (200),
+ filler4 char (200),
+ filler5 char (200),
+ filler6 char (200),
+
+ /* order of keys is important */
+ key sta_swt12a(st_a,swt1a,swt2a),
+ key sta_swt1a(st_a,swt1a),
+ key sta_swt2a(st_a,swt2a),
+ key sta_swt21a(st_a,swt2a,swt1a),
+ key st_a(st_a),
+ key stb_swt1a_2b(st_b,swt1b,swt2a),
+ key stb_swt1b(st_b,swt1b),
+ key st_b(st_b),
+
+ key(key1),
+ key(key2),
+ key(key3),
+ key(key4)
+) ;
+# Fill table
+create table t0 as select * from t1;
+--disable_query_log
+--echo # Printing of many insert into t0 values (....) disabled.
+let $cnt=1000;
+while ($cnt)
+{
+ eval insert into t0 values (1, 2, 3, 1, 2, 3, 0, 0, 0, 0, 'data1', 'data2', 'data3', 'data4', 'data5', 'data6');
+ dec $cnt;
+}
+--enable_query_log
+
+alter table t1 disable keys;
+--disable_query_log
+--echo # Printing of many insert into t1 select .... from t0 disabled.
+let $1=4;
+while ($1)
+{
+ let $2=4;
+ while ($2)
+ {
+ let $3=4;
+ while ($3)
+ {
+ eval insert into t1 select $1, $2, $3, $1 ,$2, $3, key1, key2, key3, key4, filler1, filler2, filler3, filler4, filler5, filler6 from t0;
+ dec $3;
+ }
+ dec $2;
+ }
+ dec $1;
+}
+
+--echo # Printing of many insert into t1 (...) values (....) disabled.
+# Row retrieval tests
+# -1 is used for values 'out of any range we are using'
+# insert enough rows for index intersection to be used for (key1,key2)
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 100, 100,'key1-key2-key3-key4');
+let $cnt=400;
+while ($cnt)
+{
+ eval insert into t1 (key1, key2, key3, key4, filler1) values (100, -1, 100, -1,'key1-key3');
+ dec $cnt;
+}
+let $cnt=400;
+while ($cnt)
+{
+ eval insert into t1 (key1, key2, key3, key4, filler1) values (-1, 100, -1, 100,'key2-key4');
+ dec $cnt;
+}
+--enable_query_log
+alter table t1 enable keys;
+
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
+set optimizer_trace='enabled=on';
+
+--echo # 3-way ROR-intersection
+explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo # ROR-union(ROR-intersection, ROR-range)
+explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t0,t1;
+set optimizer_trace="enabled=off";
diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result
index 94e9d4f58cc..6a245cc83da 100644
--- a/mysql-test/main/opt_trace_index_merge_innodb.result
+++ b/mysql-test/main/opt_trace_index_merge_innodb.result
@@ -116,7 +116,7 @@ explain select * from t1 where pk1 != 0 and key1 = 1 {
"range_scan_alternatives": [
{
"index": "PRIMARY",
- "ranges": ["pk1 < 0", "0 < pk1"],
+ "ranges": ["(pk1) < (0)", "(0) < (pk1)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
@@ -127,7 +127,7 @@ explain select * from t1 where pk1 != 0 and key1 = 1 {
},
{
"index": "key1",
- "ranges": ["1 <= key1 <= 1"],
+ "ranges": ["(1) <= (key1) <= (1)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
@@ -164,7 +164,7 @@ explain select * from t1 where pk1 != 0 and key1 = 1 {
"type": "range_scan",
"index": "key1",
"rows": 1,
- "ranges": ["1 <= key1 <= 1"]
+ "ranges": ["(1) <= (key1) <= (1)"]
},
"rows_for_plan": 1,
"cost_for_plan": 2.3751,
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 5ab3d70214d..fa4e30a1e94 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -431,16 +431,18 @@ static int and_range_trees(RANGE_OPT_PARAM *param,
static bool remove_nonrange_trees(RANGE_OPT_PARAM *param, SEL_TREE *tree);
static void print_key_value(String *out, const KEY_PART_INFO *key_part,
- const uchar *key);
+ const uchar* key, uint length);
+static void print_keyparts_name(String *out, const KEY_PART_INFO *key_part,
+ uint n_keypart, key_part_map keypart_map);
static void append_range_all_keyparts(Json_writer_array *range_trace,
- String *range_string,
- String *range_so_far, const SEL_ARG *keypart,
+ PARAM param, uint idx,
+ SEL_ARG *keypart,
const KEY_PART_INFO *key_parts);
static
-void append_range(String *out, const KEY_PART_INFO *key_parts,
- const uchar *min_key, const uchar *max_key, const uint flag);
+void append_range(String *out, const KEY_PART_INFO *key_part,
+ KEY_MULTI_RANGE *range, uint n_key_parts);
/*
@@ -2273,10 +2275,7 @@ void TRP_RANGE::trace_basic_info(const PARAM *param,
// TRP_RANGE should not be created if there are no range intervals
DBUG_ASSERT(key);
- String range_info;
- range_info.length(0);
- range_info.set_charset(system_charset_info);
- append_range_all_keyparts(&trace_range, NULL, &range_info, key, key_part);
+ append_range_all_keyparts(&trace_range, *param, key_idx, key, key_part);
}
@@ -2489,10 +2488,8 @@ void TRP_GROUP_MIN_MAX::trace_basic_info(const PARAM *param,
// can have group quick without ranges
if (index_tree)
{
- String range_info;
- range_info.set_charset(system_charset_info);
- append_range_all_keyparts(&trace_range, NULL, &range_info, index_tree,
- key_part);
+ append_range_all_keyparts(&trace_range, *param, param_idx,
+ index_tree, key_part);
}
}
@@ -6398,20 +6395,9 @@ void TRP_ROR_INTERSECT::trace_basic_info(const PARAM *param,
trace_isect_idx.add("rows", (*cur_scan)->records);
Json_writer_array trace_range(thd, "ranges");
- for (const SEL_ARG *current= (*cur_scan)->sel_arg->first(); current;
- current= current->next)
- {
- String range_info;
- range_info.set_charset(system_charset_info);
- for (const SEL_ARG *part= current; part;
- part= part->next_key_part ? part->next_key_part : nullptr)
- {
- const KEY_PART_INFO *cur_key_part= key_part + part->part;
- append_range(&range_info, cur_key_part, part->min_value,
- part->max_value, part->min_flag | part->max_flag);
- }
- trace_range.add(range_info.ptr(), range_info.length());
- }
+
+ append_range_all_keyparts(&trace_range, *param, (*cur_scan)->idx,
+ (*cur_scan)->sel_arg->first(), key_part);
}
}
@@ -7389,9 +7375,6 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
const KEY &cur_key= param->table->key_info[keynr];
const KEY_PART_INFO *key_part= cur_key.key_part;
- String range_info;
- range_info.set_charset(system_charset_info);
-
index_scan->idx= idx;
index_scan->keynr= keynr;
index_scan->key_info= ¶m->table->key_info[keynr];
@@ -7402,8 +7385,8 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
*tree->index_scans_end++= index_scan;
if (unlikely(thd->trace_started()))
- append_range_all_keyparts(&trace_range, NULL, &range_info, key,
- key_part);
+ append_range_all_keyparts(&trace_range, (*param), idx,
+ key, key_part);
trace_range.end();
trace_idx.add("rowid_ordered", param->is_ror_scan)
@@ -13554,11 +13537,8 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time)
Json_writer_array trace_range(thd, "ranges");
const KEY_PART_INFO *key_part= cur_index_info->key_part;
-
- String range_info;
- range_info.set_charset(system_charset_info);
- append_range_all_keyparts(&trace_range, NULL, &range_info,
- cur_index_tree, key_part);
+ append_range_all_keyparts(&trace_range, *param, cur_param_idx,
+ cur_index_tree, key_part);
}
}
cost_group_min_max(table, cur_index_info, cur_used_key_parts,
@@ -15730,12 +15710,17 @@ void QUICK_GROUP_MIN_MAX_SELECT::dbug_dump(int indent, bool verbose)
}
#endif /* !DBUG_OFF */
+
static
void append_range(String *out, const KEY_PART_INFO *key_part,
- const uchar *min_key, const uchar *max_key, const uint flag)
+ KEY_MULTI_RANGE *range, uint n_key_parts)
{
- if (out->length() > 0)
- out->append(STRING_WITH_LEN(" AND "));
+ uint flag= range->range_flag;
+ String key_name;
+ key_name.set_charset(system_charset_info);
+ key_part_map keypart_map= range->start_key.length ?
+ range->start_key.keypart_map :
+ range->end_key.keypart_map;
if (flag & GEOM_FLAG)
{
@@ -15744,22 +15729,24 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
range types, so printing "col < some_geom" doesn't make sense.
Just print the column name, not operator.
*/
- out->append(key_part->field->field_name);
+ print_keyparts_name(out, key_part, n_key_parts, keypart_map);
out->append(STRING_WITH_LEN(" "));
- print_key_value(out, key_part, min_key);
+ print_key_value(out, key_part, range->start_key.key,
+ range->start_key.length);
return;
}
if (!(flag & NO_MIN_RANGE))
{
- print_key_value(out, key_part, min_key);
+ print_key_value(out, key_part, range->start_key.key,
+ range->start_key.length);
if (flag & NEAR_MIN)
out->append(STRING_WITH_LEN(" < "));
else
out->append(STRING_WITH_LEN(" <= "));
}
- out->append(key_part->field->field_name);
+ print_keyparts_name(out, key_part, n_key_parts, keypart_map);
if (!(flag & NO_MAX_RANGE))
{
@@ -15767,7 +15754,8 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
out->append(STRING_WITH_LEN(" < "));
else
out->append(STRING_WITH_LEN(" <= "));
- print_key_value(out, key_part, max_key);
+ print_key_value(out, key_part, range->end_key.key,
+ range->end_key.length);
}
}
@@ -15775,60 +15763,39 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
Add ranges to the trace
For ex:
- query: select * from t1 where a=2 ;
- and we have an index on a , so we create a range
- 2 <= a <= 2
+ lets say we have an index a_b(a,b)
+ query: select * from t1 where a=2 and b=4 ;
+ so we create a range:
+ (2,4) <= (a,b) <= (2,4)
this is added to the trace
*/
static void append_range_all_keyparts(Json_writer_array *range_trace,
- String *range_string,
- String *range_so_far, const SEL_ARG *keypart,
+ PARAM param, uint idx,
+ SEL_ARG *keypart,
const KEY_PART_INFO *key_parts)
{
-
- DBUG_ASSERT(keypart);
- DBUG_ASSERT(keypart && keypart != &null_element);
-
- // Navigate to first interval in red-black tree
+ SEL_ARG_RANGE_SEQ seq;
+ KEY_MULTI_RANGE range;
+ range_seq_t seq_it;
+ uint flags= 0;
+ RANGE_SEQ_IF seq_if = {NULL, sel_arg_range_seq_init,
+ sel_arg_range_seq_next, 0, 0};
+ KEY *keyinfo= param.table->key_info + param.real_keynr[idx];
+ uint n_key_parts= param.table->actual_n_key_parts(keyinfo);
+ seq.keyno= idx;
+ seq.real_keyno= param.real_keynr[idx];
+ seq.param= ¶m;
+ seq.start= keypart;
const KEY_PART_INFO *cur_key_part= key_parts + keypart->part;
- const SEL_ARG *keypart_range= keypart->first();
- const size_t save_range_so_far_length= range_so_far->length();
-
+ seq_it= seq_if.init((void *) &seq, 0, flags);
- while (keypart_range)
+ while (!seq_if.next(seq_it, &range))
{
- // Append the current range predicate to the range String
- switch (keypart->type)
- {
- case SEL_ARG::Type::KEY_RANGE:
- append_range(range_so_far, cur_key_part, keypart_range->min_value,
- keypart_range->max_value,
- keypart_range->min_flag | keypart_range->max_flag);
- break;
- case SEL_ARG::Type::MAYBE_KEY:
- range_so_far->append("MAYBE_KEY");
- break;
- case SEL_ARG::Type::IMPOSSIBLE:
- range_so_far->append("IMPOSSIBLE");
- break;
- default:
- DBUG_ASSERT(false);
- break;
- }
-
- if (keypart_range->next_key_part &&
- keypart_range->next_key_part->part ==
- keypart_range->part + 1 &&
- keypart_range->is_singlepoint())
- {
- append_range_all_keyparts(range_trace, range_string, range_so_far,
- keypart_range->next_key_part, key_parts);
- }
- else
- range_trace->add(range_so_far->c_ptr_safe(), range_so_far->length());
- keypart_range= keypart_range->next;
- range_so_far->length(save_range_so_far_length);
+ String range_info;
+ range_info.set_charset(system_charset_info);
+ append_range(&range_info, cur_key_part, &range, n_key_parts);
+ range_trace->add(range_info.c_ptr_safe(), range_info.length());
}
}
@@ -15838,70 +15805,110 @@ static void append_range_all_keyparts(Json_writer_array *range_trace,
@param[out] out String the key is appended to
@param[in] key_part Index components description
@param[in] key Key tuple
+ @param[in] used_length length of the key tuple
*/
+
static void print_key_value(String *out, const KEY_PART_INFO *key_part,
- const uchar *key)
+ const uchar* key, uint used_length)
{
+ out->append(STRING_WITH_LEN("("));
Field *field= key_part->field;
+ StringBuffer<128> tmp(system_charset_info);
+ TABLE *table= field->table;
+ uint store_length;
+ my_bitmap_map *old_sets[2];
+ dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
+ const uchar *key_end= key+used_length;
- if (field->flags & BLOB_FLAG)
+ for (; key < key_end; key+=store_length, key_part++)
{
- // Byte 0 of a nullable key is the null-byte. If set, key is NULL.
- if (field->real_maybe_null() && *key)
- out->append(STRING_WITH_LEN("NULL"));
- else
- (field->type() == MYSQL_TYPE_GEOMETRY)
- ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
- : out->append(STRING_WITH_LEN("unprintable_blob_value"));
- return;
- }
+ field= key_part->field;
+ store_length= key_part->store_length;
+ if (field->flags & BLOB_FLAG)
+ {
+ // Byte 0 of a nullable key is the null-byte. If set, key is NULL.
+ if (field->real_maybe_null() && *key)
+ out->append(STRING_WITH_LEN("NULL"));
+ else
+ (field->type() == MYSQL_TYPE_GEOMETRY)
+ ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
+ : out->append(STRING_WITH_LEN("unprintable_blob_value"));
+ goto next;
+ }
- uint store_length= key_part->store_length;
+ if (field->real_maybe_null())
+ {
+ /*
+ Byte 0 of key is the null-byte. If set, key is NULL.
+ Otherwise, print the key value starting immediately after the
+ null-byte
+ */
+ if (*key)
+ {
+ out->append(STRING_WITH_LEN("NULL"));
+ goto next;
+ }
+ key++; // Skip null byte
+ store_length--;
+ }
- if (field->real_maybe_null())
- {
/*
- Byte 0 of key is the null-byte. If set, key is NULL.
- Otherwise, print the key value starting immediately after the
- null-byte
+ Binary data cannot be converted to UTF8 which is what the
+ optimizer trace expects. If the column is binary, the hex
+ representation is printed to the trace instead.
*/
- if (*key)
+ if (field->flags & BINARY_FLAG)
{
- out->append(STRING_WITH_LEN("NULL"));
- return;
+ out->append("0x");
+ for (uint i = 0; i < store_length; i++)
+ {
+ out->append(_dig_vec_lower[*(key + i) >> 4]);
+ out->append(_dig_vec_lower[*(key + i) & 0x0F]);
+ }
+ goto next;
}
- key++; // Skip null byte
- store_length--;
+
+ field->set_key_image(key, key_part->length);
+ if (field->type() == MYSQL_TYPE_BIT)
+ (void)field->val_int_as_str(&tmp, 1); // may change tmp's charset
+ else
+ field->val_str(&tmp); // may change tmp's charset
+ out->append(tmp.ptr(), tmp.length(), tmp.charset());
+
+ next:
+ if (key + store_length < key_end)
+ out->append(STRING_WITH_LEN(","));
}
+ dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
+ out->append(STRING_WITH_LEN(")"));
+}
- /*
- Binary data cannot be converted to UTF8 which is what the
- optimizer trace expects. If the column is binary, the hex
- representation is printed to the trace instead.
- */
- if (field->flags & BINARY_FLAG)
+/**
+ Print key parts involed in a range
+ @param[out] out String the key is appended to
+ @param[in] key_part Index components description
+ @param[in] n_keypart Number of keyparts in index
+ @param[in] keypart_map map for keyparts involved in the range
+*/
+
+void print_keyparts_name(String *out, const KEY_PART_INFO *key_part,
+ uint n_keypart, key_part_map keypart_map)
+{
+ uint i;
+ out->append(STRING_WITH_LEN("("));
+ bool first_keypart= TRUE;
+ for (i=0; i < n_keypart; key_part++, i++)
{
- out->append("0x");
- for (uint i = 0; i < store_length; i++)
+ if (keypart_map & (1 << i))
{
- out->append(_dig_vec_lower[*(key + i) >> 4]);
- out->append(_dig_vec_lower[*(key + i) & 0x0F]);
+ if (first_keypart)
+ first_keypart= FALSE;
+ else
+ out->append(STRING_WITH_LEN(","));
+ out->append(key_part->field->field_name);
}
- return;
+ else
+ break;
}
-
- StringBuffer<128> tmp(system_charset_info);
- TABLE *table= field->table;
- my_bitmap_map *old_sets[2];
-
- dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
-
- field->set_key_image(key, key_part->length);
- if (field->type() == MYSQL_TYPE_BIT)
- (void)field->val_int_as_str(&tmp, 1); // may change tmp's charset
- else
- field->val_str(&tmp); // may change tmp's charset
- out->append(tmp.ptr(), tmp.length(), tmp.charset());
-
- dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
+ out->append(STRING_WITH_LEN(")"));
}
diff --git a/sql/opt_range.h b/sql/opt_range.h
index 2dab90b9f69..5c2a2e39137 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -458,7 +458,9 @@ class SEL_ARG :public Sql_alloc
SEL_ARG *key_tree= first();
uint res= key_tree->store_min(key[key_tree->part].store_length,
range_key, *range_key_flag);
- *range_key_flag|= key_tree->min_flag;
+ // add flags only if a key_part is written to the buffer
+ if (res)
+ *range_key_flag|= key_tree->min_flag;
if (key_tree->next_key_part &&
key_tree->next_key_part->type == SEL_ARG::KEY_RANGE &&
key_tree->part != last_part &&
@@ -480,7 +482,8 @@ class SEL_ARG :public Sql_alloc
SEL_ARG *key_tree= last();
uint res=key_tree->store_max(key[key_tree->part].store_length,
range_key, *range_key_flag);
- (*range_key_flag)|= key_tree->max_flag;
+ if (res)
+ (*range_key_flag)|= key_tree->max_flag;
if (key_tree->next_key_part &&
key_tree->next_key_part->type == SEL_ARG::KEY_RANGE &&
key_tree->part != last_part &&
1
0
[Commits] d2013e7: MDEV-18982 Partition pruning with column list causes syntax error in 10.4
by IgorBabaev 04 Apr '19
by IgorBabaev 04 Apr '19
04 Apr '19
revision-id: d2013e73288c953a6cbcdddf9688584c0353535d (mariadb-10.4.3-159-gd2013e7)
parent(s): ae15f91f227015b3e1ad3f566db9396232cf0a3f
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-04-04 16:36:26 -0700
message:
MDEV-18982 Partition pruning with column list causes syntax error in 10.4
A syntax error was reported for any INSERT statement with explicit
partition selection it if i used a column list.
Fixed by saving the parsing place before parsing the clause for explicit
partition selection and restoring it when the clause has been parsed.
---
mysql-test/main/partition_explicit_prune.result | 9 +++++++++
mysql-test/main/partition_explicit_prune.test | 9 +++++++++
sql/sql_lex.cc | 1 +
sql/sql_lex.h | 1 +
sql/sql_yacc.yy | 8 +++++++-
sql/sql_yacc_ora.yy | 8 +++++++-
6 files changed, 34 insertions(+), 2 deletions(-)
diff --git a/mysql-test/main/partition_explicit_prune.result b/mysql-test/main/partition_explicit_prune.result
index 650c8d2..1199bc2 100644
--- a/mysql-test/main/partition_explicit_prune.result
+++ b/mysql-test/main/partition_explicit_prune.result
@@ -1897,3 +1897,12 @@ SELECT * FROM t1 PARTITION (p0);
i
UNLOCK TABLES;
DROP TABLE t1, t2;
+#
+# MDEV-18982: INSERT using explicit patition pruning with column list
+#
+create table t1 (a int) partition by hash(a);
+insert into t1 partition (p0) (a) values (1);
+select * from t1;
+a
+1
+drop table t1;
diff --git a/mysql-test/main/partition_explicit_prune.test b/mysql-test/main/partition_explicit_prune.test
index b8b6e48..a516527 100644
--- a/mysql-test/main/partition_explicit_prune.test
+++ b/mysql-test/main/partition_explicit_prune.test
@@ -877,3 +877,12 @@ UNLOCK TABLES;
# Cleanup
DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-18982: INSERT using explicit patition pruning with column list
+--echo #
+
+create table t1 (a int) partition by hash(a);
+insert into t1 partition (p0) (a) values (1);
+select * from t1;
+drop table t1;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 4dc6b94..3f28ab9 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2381,6 +2381,7 @@ void st_select_lex::init_query()
first_natural_join_processing= 1;
first_cond_optimization= 1;
parsing_place= NO_MATTER;
+ save_parsing_place= NO_MATTER;
exclude_from_table_unique_test= no_wrap_view_item= FALSE;
nest_level= 0;
link_next= 0;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 8d5c248..3d31453 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1165,6 +1165,7 @@ class st_select_lex: public st_select_lex_node
*/
uint hidden_bit_fields;
enum_parsing_place parsing_place; /* where we are parsing expression */
+ enum_parsing_place save_parsing_place;
enum_parsing_place context_analysis_place; /* where we are in prepare */
bool with_sum_func; /* sum function indicator */
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 5869f51..11634f4 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -12052,6 +12052,8 @@ use_partition:
PARTITION_SYM '(' using_list ')' have_partitioning
{
$$= $3;
+ Select->parsing_place= Select->save_parsing_place;
+ Select->save_parsing_place= NO_MATTER;
}
;
@@ -13347,13 +13349,17 @@ insert2:
;
insert_table:
+ {
+ Select->save_parsing_place= Select->parsing_place;
+ }
table_name_with_opt_use_partition
{
LEX *lex=Lex;
//lex->field_list.empty();
lex->many_values.empty();
lex->insert_list=0;
- };
+ }
+ ;
insert_field_spec:
insert_values {}
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 39095bc..f5a4e55 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -12174,6 +12174,8 @@ use_partition:
PARTITION_SYM '(' using_list ')' have_partitioning
{
$$= $3;
+ Select->parsing_place= Select->save_parsing_place;
+ Select->save_parsing_place= NO_MATTER;
}
;
@@ -13485,13 +13487,17 @@ insert2:
;
insert_table:
+ {
+ Select->save_parsing_place= Select->parsing_place;
+ }
table_name_with_opt_use_partition
{
LEX *lex=Lex;
//lex->field_list.empty();
lex->many_values.empty();
lex->insert_list=0;
- };
+ }
+ ;
insert_field_spec:
insert_values {}
1
0
[Commits] a1929d001c7: MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly
by Varun 04 Apr '19
by Varun 04 Apr '19
04 Apr '19
revision-id: a1929d001c7b25e26d178834a196020bade819b8 (mariadb-10.3.6-318-ga1929d001c7)
parent(s): 0dc442ac61ac7ff1a1d6bd7d6090c0f2251fb558
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-04-03 13:10:07 +0530
message:
MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly
Changed the function append_range_all_keyparts to use sel_arg_range_seq_init / sel_arg_range_seq_next to produce ranges.
Also adjusted to print format for the ranges, now the ranges are printed as:
(keypart1_min, keypart2_min,..) OP (keypart1_name,keypart2_name, ..) OP (keypart1_max,keypart2_max, ..)
Also added more tests for range and index merge access for optimizer trace
---
mysql-test/main/opt_trace.result | 231 +++++++++-
mysql-test/main/opt_trace.test | 42 ++
mysql-test/main/opt_trace_index_merge.result | 509 ++++++++++++++++++++-
mysql-test/main/opt_trace_index_merge.test | 112 +++++
.../main/opt_trace_index_merge_innodb.result | 6 +-
sql/opt_range.cc | 285 ++++++------
6 files changed, 1016 insertions(+), 169 deletions(-)
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 12d4c713886..576282a4364 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1248,7 +1248,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a {
{
"index": "a",
"covering": true,
- "ranges": ["2 <= b <= 2 AND 3 <= c <= 3"],
+ "ranges": ["(2,3) <= (b,c) <= (2,3)"],
"rows": 8,
"cost": 2.2
}
@@ -1264,7 +1264,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a {
"rows": 8,
"cost": 2.2,
"key_parts_used_for_access": ["a", "b", "c"],
- "ranges": ["2 <= b <= 2 AND 3 <= c <= 3"],
+ "ranges": ["(2,3) <= (b,c) <= (2,3)"],
"chosen": false,
"cause": "cost"
},
@@ -1446,7 +1446,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
{
"index": "id",
"covering": true,
- "ranges": ["0x24a20f <= a"],
+ "ranges": ["(0x24a20f) <= (a)"],
"rows": 9,
"cost": 2.35
}
@@ -1462,7 +1462,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
"rows": 9,
"cost": 2.35,
"key_parts_used_for_access": ["id"],
- "ranges": ["0x24a20f <= a"],
+ "ranges": ["(0x24a20f) <= (a)"],
"chosen": false,
"cause": "cost"
},
@@ -1624,7 +1624,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
{
"index": "id",
"covering": true,
- "ranges": ["0x24a20f <= a <= 0x24a20f"],
+ "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
"rows": 9,
"cost": 2.35
}
@@ -1640,7 +1640,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
"rows": 9,
"cost": 2.35,
"key_parts_used_for_access": ["id", "a"],
- "ranges": ["0x24a20f <= a <= 0x24a20f"],
+ "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
"chosen": false,
"cause": "cost"
},
@@ -1856,7 +1856,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"range_scan_alternatives": [
{
"index": "a_c",
- "ranges": ["1 <= a <= 1"],
+ "ranges": ["(1) <= (a) <= (1)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
@@ -1866,7 +1866,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
},
{
"index": "a_b",
- "ranges": ["1 <= a <= 1 AND 2 <= b <= 2"],
+ "ranges": ["(1,2) <= (a,b) <= (1,2)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
@@ -1885,7 +1885,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"type": "range_scan",
"index": "a_b",
"rows": 21,
- "ranges": ["1 <= a <= 1 AND 2 <= b <= 2"]
+ "ranges": ["(1,2) <= (a,b) <= (1,2)"]
},
"rows_for_plan": 21,
"cost_for_plan": 27.445,
@@ -2025,7 +2025,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"range_scan_alternatives": [
{
"index": "a_c",
- "ranges": ["1 <= a <= 1"],
+ "ranges": ["(1) <= (a) <= (1)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
@@ -2044,7 +2044,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"type": "range_scan",
"index": "a_c",
"rows": 180,
- "ranges": ["1 <= a <= 1"]
+ "ranges": ["(1) <= (a) <= (1)"]
},
"rows_for_plan": 180,
"cost_for_plan": 231.72,
@@ -2895,7 +2895,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"range_scan_alternatives": [
{
"index": "pk",
- "ranges": ["2 <= pk <= 2"],
+ "ranges": ["(2) <= (pk) <= (2)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
@@ -2906,7 +2906,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
},
{
"index": "pk_a",
- "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5"],
+ "ranges": ["(2,5) <= (pk,a) <= (2,5)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
@@ -2917,7 +2917,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
},
{
"index": "pk_a_b",
- "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5 AND 1 <= b <= 1"],
+ "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
@@ -2964,7 +2964,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"type": "range_scan",
"index": "pk_a_b",
"rows": 1,
- "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5 AND 1 <= b <= 1"]
+ "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"]
},
"rows_for_plan": 1,
"cost_for_plan": 1.1793,
@@ -3338,7 +3338,7 @@ explain delete from t0 where t0.a<3 {
"range_scan_alternatives": [
{
"index": "a",
- "ranges": ["NULL < a < 3"],
+ "ranges": ["(NULL) < (a) < (3)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
@@ -3354,7 +3354,7 @@ explain delete from t0 where t0.a<3 {
"type": "range_scan",
"index": "a",
"rows": 3,
- "ranges": ["NULL < a < 3"]
+ "ranges": ["(NULL) < (a) < (3)"]
},
"rows_for_plan": 3,
"cost_for_plan": 5.007,
@@ -3481,7 +3481,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"range_scan_alternatives": [
{
"index": "a",
- "ranges": ["NULL < a < 3"],
+ "ranges": ["(NULL) < (a) < (3)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
@@ -3500,7 +3500,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"type": "range_scan",
"index": "a",
"rows": 3,
- "ranges": ["NULL < a < 3"]
+ "ranges": ["(NULL) < (a) < (3)"]
},
"rows_for_plan": 3,
"cost_for_plan": 1.407,
@@ -3546,7 +3546,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"range_scan_alternatives": [
{
"index": "a",
- "ranges": ["NULL < a < 3"],
+ "ranges": ["(NULL) < (a) < (3)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
@@ -3565,7 +3565,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"type": "range_scan",
"index": "a",
"rows": 3,
- "ranges": ["NULL < a < 3"]
+ "ranges": ["(NULL) < (a) < (3)"]
},
"rows_for_plan": 3,
"cost_for_plan": 1.407,
@@ -6034,4 +6034,193 @@ COUNT(*)
1
DROP VIEW v1;
DROP TABLE t1;
+#
+# MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly.
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 ( a int, b int, key a_b(a,b));
+insert into t1 select a,a from one_k;
+set optimizer_trace='enabled=on';
+explain select * from t1 force index (a_b) where a=2 and b=4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_b a_b 10 const,const 1 Using index
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "a_b",
+ "ranges":
+ [
+ "(2,4) <= (a,b) <= (2,4)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 1,
+ "cost": 1.1783,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+explain select * from t1 where a >= 900 and b between 10 and 20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a_b a_b 10 NULL 107 Using where; Using index
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "a_b",
+ "ranges":
+ [
+ "(900,10) <= (a,b)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 107,
+ "cost": 10.955,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t0,t1;
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+explain select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range start_date start_date 8 NULL 1000 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "start_date",
+ "ranges":
+ [
+ "(0x4ac60f,NULL) < (start_date,end_date)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1000,
+ "cost": 1282.2,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1,one_k;
+# Ported test from MYSQL for ranges involving Binary column
+CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C');
+INSERT INTO t1 VALUES (2, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 17 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(0xd95b94336a9946a39cf5b58cfe772d8c) <= (b) <= (0xd95b94336a9946a39cf5b58cfe772d8c)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3797,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+EXPLAIN SELECT * FROM t1 WHERE b IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 17 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(NULL) <= (b) <= (NULL)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3797,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 4ec7c338acd..484d3e53e56 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -387,4 +387,46 @@ SELECT COUNT(*) FROM v1 WHERE MATCH (f) AGAINST ('fooba');
DROP VIEW v1;
DROP TABLE t1;
+--echo #
+--echo # MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly.
+--echo #
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 ( a int, b int, key a_b(a,b));
+insert into t1 select a,a from one_k;
+set optimizer_trace='enabled=on';
+
+explain select * from t1 force index (a_b) where a=2 and b=4;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+explain select * from t1 where a >= 900 and b between 10 and 20;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t0,t1;
+
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+--disable_warnings
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+--enable_warnings
+explain select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1,one_k;
+
+--echo # Ported test from MYSQL for ranges involving Binary column
+
+CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C');
+INSERT INTO t1 VALUES (2, NULL);
+
+EXPLAIN SELECT * FROM t1 WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C);
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE b IS NULL;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t1;
+
set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result
index 50daef815d6..b5e68d04615 100644
--- a/mysql-test/main/opt_trace_index_merge.result
+++ b/mysql-test/main/opt_trace_index_merge.result
@@ -110,7 +110,7 @@ explain select * from t1 where a=1 or b=1 {
"range_scan_alternatives": [
{
"index": "a",
- "ranges": ["1 <= a <= 1"],
+ "ranges": ["(1) <= (a) <= (1)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
@@ -126,7 +126,7 @@ explain select * from t1 where a=1 or b=1 {
"range_scan_alternatives": [
{
"index": "b",
- "ranges": ["1 <= b <= 1"],
+ "ranges": ["(1) <= (b) <= (1)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
@@ -147,7 +147,7 @@ explain select * from t1 where a=1 or b=1 {
"type": "range_scan",
"index": "a",
"rows": 1,
- "ranges": ["1 <= a <= 1"],
+ "ranges": ["(1) <= (a) <= (1)"],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
}
@@ -156,7 +156,7 @@ explain select * from t1 where a=1 or b=1 {
"type": "range_scan",
"index": "b",
"rows": 1,
- "ranges": ["1 <= b <= 1"],
+ "ranges": ["(1) <= (b) <= (1)"],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
}
@@ -176,13 +176,13 @@ explain select * from t1 where a=1 or b=1 {
"type": "range_scan",
"index": "a",
"rows": 1,
- "ranges": ["1 <= a <= 1"]
+ "ranges": ["(1) <= (a) <= (1)"]
},
{
"type": "range_scan",
"index": "b",
"rows": 1,
- "ranges": ["1 <= b <= 1"]
+ "ranges": ["(1) <= (b) <= (1)"]
}
]
},
@@ -243,3 +243,500 @@ explain select * from t1 where a=1 or b=1 {
drop table t0,t1;
set optimizer_trace="enabled=off";
set @@optimizer_switch= @tmp_opt_switch;
+# More tests added index_merge access
+create table t1
+(
+/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
+st_a int not null default 0,
+swt1a int not null default 0,
+swt2a int not null default 0,
+st_b int not null default 0,
+swt1b int not null default 0,
+swt2b int not null default 0,
+/* fields/keys for row retrieval tests */
+key1 int,
+key2 int,
+key3 int,
+key4 int,
+/* make rows much bigger then keys */
+filler1 char (200),
+filler2 char (200),
+filler3 char (200),
+filler4 char (200),
+filler5 char (200),
+filler6 char (200),
+/* order of keys is important */
+key sta_swt12a(st_a,swt1a,swt2a),
+key sta_swt1a(st_a,swt1a),
+key sta_swt2a(st_a,swt2a),
+key sta_swt21a(st_a,swt2a,swt1a),
+key st_a(st_a),
+key stb_swt1a_2b(st_b,swt1b,swt2a),
+key stb_swt1b(st_b,swt1b),
+key st_b(st_b),
+key(key1),
+key(key2),
+key(key3),
+key(key4)
+) ;
+create table t0 as select * from t1;
+# Printing of many insert into t0 values (....) disabled.
+alter table t1 disable keys;
+# Printing of many insert into t1 select .... from t0 disabled.
+# Printing of many insert into t1 (...) values (....) disabled.
+alter table t1 enable keys;
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
+set optimizer_trace='enabled=on';
+# 3-way ROR-intersection
+explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 2 Using intersect(key1,key2,key3); Using where; Using index
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "key1",
+ "ranges":
+ [
+ "(100) <= (key1) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2243,
+ "cost": 2862.1,
+ "chosen": true
+ },
+
+ {
+ "index": "key2",
+ "ranges":
+ [
+ "(100) <= (key2) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2243,
+ "cost": 2862.1,
+ "chosen": false,
+ "cause": "cost"
+ },
+
+ {
+ "index": "key3",
+ "ranges":
+ [
+ "(100) <= (key3) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2243,
+ "cost": 2862.1,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "intersecting_indexes":
+ [
+
+ {
+ "index": "key1",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 58.252,
+ "disk_sweep_cost": 1923.1,
+ "cumulative_total_cost": 1981.4,
+ "usable": true,
+ "matching_rows_now": 2243,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ },
+
+ {
+ "index": "key2",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 116.5,
+ "disk_sweep_cost": 84.518,
+ "cumulative_total_cost": 201.02,
+ "usable": true,
+ "matching_rows_now": 77.636,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ },
+
+ {
+ "index": "key3",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 174.76,
+ "disk_sweep_cost": 0,
+ "cumulative_total_cost": 174.76,
+ "usable": true,
+ "matching_rows_now": 2.6872,
+ "intersect_covering_with_this_index": true,
+ "chosen": true
+ }
+ ],
+ "clustered_pk":
+ {
+ "clustered_pk_added_to_intersect": false,
+ "cause": "no clustered pk index"
+ },
+ "rows": 2,
+ "cost": 174.76,
+ "covering": true,
+ "chosen": true
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary'))
+[
+
+ {
+ "range_access_plan":
+ {
+ "type": "index_roworder_intersect",
+ "rows": 2,
+ "cost": 174.76,
+ "covering": true,
+ "clustered_pk_scan": false,
+ "intersect_of":
+ [
+
+ {
+ "type": "range_scan",
+ "index": "key1",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key1) <= (100)"
+ ]
+ },
+
+ {
+ "type": "range_scan",
+ "index": "key2",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key2) <= (100)"
+ ]
+ },
+
+ {
+ "type": "range_scan",
+ "index": "key3",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key3) <= (100)"
+ ]
+ }
+ ]
+ },
+ "rows_for_plan": 2,
+ "cost_for_plan": 174.76,
+ "chosen": true
+ }
+]
+# ROR-union(ROR-intersection, ROR-range)
+explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+
+ {
+ "indexes_to_merge":
+ [
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "key1",
+ "ranges":
+ [
+ "(100) <= (key1) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 2243,
+ "cost": 170.53,
+ "chosen": true
+ },
+
+ {
+ "index": "key2",
+ "ranges":
+ [
+ "(100) <= (key2) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 2243,
+ "cost": 170.53,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ],
+ "index_to_merge": "key1",
+ "cumulated_cost": 170.53
+ },
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "key3",
+ "ranges":
+ [
+ "(100) <= (key3) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 2243,
+ "cost": 170.53,
+ "chosen": true
+ },
+
+ {
+ "index": "key4",
+ "ranges":
+ [
+ "(100) <= (key4) <= (100)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": true,
+ "rows": 2243,
+ "cost": 170.53,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ],
+ "index_to_merge": "key3",
+ "cumulated_cost": 341.05
+ }
+ ],
+ "cost_of_reading_ranges": 341.05,
+ "use_roworder_union": true,
+ "cause": "always cheaper than non roworder retrieval",
+ "analyzing_roworder_scans":
+ [
+
+ {
+ "type": "range_scan",
+ "index": "key1",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key1) <= (100)"
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "intersecting_indexes":
+ [
+
+ {
+ "index": "key1",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 58.252,
+ "disk_sweep_cost": 1923.1,
+ "cumulative_total_cost": 1981.4,
+ "usable": true,
+ "matching_rows_now": 2243,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ },
+
+ {
+ "index": "key2",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 116.5,
+ "disk_sweep_cost": 84.518,
+ "cumulative_total_cost": 201.02,
+ "usable": true,
+ "matching_rows_now": 77.636,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ }
+ ],
+ "clustered_pk":
+ {
+ "clustered_pk_added_to_intersect": false,
+ "cause": "no clustered pk index"
+ },
+ "rows": 77,
+ "cost": 201.02,
+ "covering": false,
+ "chosen": true
+ }
+ },
+
+ {
+ "type": "range_scan",
+ "index": "key3",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key3) <= (100)"
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "intersecting_indexes":
+ [
+
+ {
+ "index": "key3",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 58.252,
+ "disk_sweep_cost": 1923.1,
+ "cumulative_total_cost": 1981.4,
+ "usable": true,
+ "matching_rows_now": 2243,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ },
+
+ {
+ "index": "key4",
+ "index_scan_cost": 58.252,
+ "cumulated_index_scan_cost": 116.5,
+ "disk_sweep_cost": 84.518,
+ "cumulative_total_cost": 201.02,
+ "usable": true,
+ "matching_rows_now": 77.636,
+ "intersect_covering_with_this_index": false,
+ "chosen": true
+ }
+ ],
+ "clustered_pk":
+ {
+ "clustered_pk_added_to_intersect": false,
+ "cause": "no clustered pk index"
+ },
+ "rows": 77,
+ "cost": 201.02,
+ "covering": false,
+ "chosen": true
+ }
+ }
+ ],
+ "index_roworder_union_cost": 386.73,
+ "members": 2,
+ "chosen": true
+ }
+ ]
+ }
+]
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary'))
+[
+
+ {
+ "range_access_plan":
+ {
+ "type": "index_roworder_union",
+ "union_of":
+ [
+
+ {
+ "type": "index_roworder_intersect",
+ "rows": 77,
+ "cost": 201.02,
+ "covering": false,
+ "clustered_pk_scan": false,
+ "intersect_of":
+ [
+
+ {
+ "type": "range_scan",
+ "index": "key1",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key1) <= (100)"
+ ]
+ },
+
+ {
+ "type": "range_scan",
+ "index": "key2",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key2) <= (100)"
+ ]
+ }
+ ]
+ },
+
+ {
+ "type": "index_roworder_intersect",
+ "rows": 77,
+ "cost": 201.02,
+ "covering": false,
+ "clustered_pk_scan": false,
+ "intersect_of":
+ [
+
+ {
+ "type": "range_scan",
+ "index": "key3",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key3) <= (100)"
+ ]
+ },
+
+ {
+ "type": "range_scan",
+ "index": "key4",
+ "rows": 2243,
+ "ranges":
+ [
+ "(100) <= (key4) <= (100)"
+ ]
+ }
+ ]
+ }
+ ]
+ },
+ "rows_for_plan": 154,
+ "cost_for_plan": 386.73,
+ "chosen": true
+ }
+]
+drop table t0,t1;
+set optimizer_trace="enabled=off";
diff --git a/mysql-test/main/opt_trace_index_merge.test b/mysql-test/main/opt_trace_index_merge.test
index d5efaf81db5..73240b6a9e2 100644
--- a/mysql-test/main/opt_trace_index_merge.test
+++ b/mysql-test/main/opt_trace_index_merge.test
@@ -19,3 +19,115 @@ select * from information_schema.OPTIMIZER_TRACE;
drop table t0,t1;
set optimizer_trace="enabled=off";
set @@optimizer_switch= @tmp_opt_switch;
+
+--echo # More tests added index_merge access
+
+--enable_warnings
+create table t1
+(
+ /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
+ st_a int not null default 0,
+ swt1a int not null default 0,
+ swt2a int not null default 0,
+
+ st_b int not null default 0,
+ swt1b int not null default 0,
+ swt2b int not null default 0,
+
+ /* fields/keys for row retrieval tests */
+ key1 int,
+ key2 int,
+ key3 int,
+ key4 int,
+
+ /* make rows much bigger then keys */
+ filler1 char (200),
+ filler2 char (200),
+ filler3 char (200),
+ filler4 char (200),
+ filler5 char (200),
+ filler6 char (200),
+
+ /* order of keys is important */
+ key sta_swt12a(st_a,swt1a,swt2a),
+ key sta_swt1a(st_a,swt1a),
+ key sta_swt2a(st_a,swt2a),
+ key sta_swt21a(st_a,swt2a,swt1a),
+ key st_a(st_a),
+ key stb_swt1a_2b(st_b,swt1b,swt2a),
+ key stb_swt1b(st_b,swt1b),
+ key st_b(st_b),
+
+ key(key1),
+ key(key2),
+ key(key3),
+ key(key4)
+) ;
+# Fill table
+create table t0 as select * from t1;
+--disable_query_log
+--echo # Printing of many insert into t0 values (....) disabled.
+let $cnt=1000;
+while ($cnt)
+{
+ eval insert into t0 values (1, 2, 3, 1, 2, 3, 0, 0, 0, 0, 'data1', 'data2', 'data3', 'data4', 'data5', 'data6');
+ dec $cnt;
+}
+--enable_query_log
+
+alter table t1 disable keys;
+--disable_query_log
+--echo # Printing of many insert into t1 select .... from t0 disabled.
+let $1=4;
+while ($1)
+{
+ let $2=4;
+ while ($2)
+ {
+ let $3=4;
+ while ($3)
+ {
+ eval insert into t1 select $1, $2, $3, $1 ,$2, $3, key1, key2, key3, key4, filler1, filler2, filler3, filler4, filler5, filler6 from t0;
+ dec $3;
+ }
+ dec $2;
+ }
+ dec $1;
+}
+
+--echo # Printing of many insert into t1 (...) values (....) disabled.
+# Row retrieval tests
+# -1 is used for values 'out of any range we are using'
+# insert enough rows for index intersection to be used for (key1,key2)
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 100, 100,'key1-key2-key3-key4');
+let $cnt=400;
+while ($cnt)
+{
+ eval insert into t1 (key1, key2, key3, key4, filler1) values (100, -1, 100, -1,'key1-key3');
+ dec $cnt;
+}
+let $cnt=400;
+while ($cnt)
+{
+ eval insert into t1 (key1, key2, key3, key4, filler1) values (-1, 100, -1, 100,'key2-key4');
+ dec $cnt;
+}
+--enable_query_log
+alter table t1 enable keys;
+
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
+set optimizer_trace='enabled=on';
+
+--echo # 3-way ROR-intersection
+explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo # ROR-union(ROR-intersection, ROR-range)
+explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t0,t1;
+set optimizer_trace="enabled=off";
diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result
index 94e9d4f58cc..6a245cc83da 100644
--- a/mysql-test/main/opt_trace_index_merge_innodb.result
+++ b/mysql-test/main/opt_trace_index_merge_innodb.result
@@ -116,7 +116,7 @@ explain select * from t1 where pk1 != 0 and key1 = 1 {
"range_scan_alternatives": [
{
"index": "PRIMARY",
- "ranges": ["pk1 < 0", "0 < pk1"],
+ "ranges": ["(pk1) < (0)", "(0) < (pk1)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
@@ -127,7 +127,7 @@ explain select * from t1 where pk1 != 0 and key1 = 1 {
},
{
"index": "key1",
- "ranges": ["1 <= key1 <= 1"],
+ "ranges": ["(1) <= (key1) <= (1)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
@@ -164,7 +164,7 @@ explain select * from t1 where pk1 != 0 and key1 = 1 {
"type": "range_scan",
"index": "key1",
"rows": 1,
- "ranges": ["1 <= key1 <= 1"]
+ "ranges": ["(1) <= (key1) <= (1)"]
},
"rows_for_plan": 1,
"cost_for_plan": 2.3751,
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 5ab3d70214d..f53efc55158 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -431,16 +431,18 @@ static int and_range_trees(RANGE_OPT_PARAM *param,
static bool remove_nonrange_trees(RANGE_OPT_PARAM *param, SEL_TREE *tree);
static void print_key_value(String *out, const KEY_PART_INFO *key_part,
- const uchar *key);
+ const uchar* key, uint length);
+static void print_keyparts_name(String *out, const KEY_PART_INFO *key_part,
+ uint n_keypart, key_part_map keypart_map);
static void append_range_all_keyparts(Json_writer_array *range_trace,
- String *range_string,
- String *range_so_far, const SEL_ARG *keypart,
+ PARAM param, uint idx,
+ SEL_ARG *keypart,
const KEY_PART_INFO *key_parts);
static
-void append_range(String *out, const KEY_PART_INFO *key_parts,
- const uchar *min_key, const uchar *max_key, const uint flag);
+void append_range(String *out, const KEY_PART_INFO *key_part,
+ KEY_MULTI_RANGE *range, uint n_key_parts);
/*
@@ -2273,10 +2275,7 @@ void TRP_RANGE::trace_basic_info(const PARAM *param,
// TRP_RANGE should not be created if there are no range intervals
DBUG_ASSERT(key);
- String range_info;
- range_info.length(0);
- range_info.set_charset(system_charset_info);
- append_range_all_keyparts(&trace_range, NULL, &range_info, key, key_part);
+ append_range_all_keyparts(&trace_range, *param, key_idx, key, key_part);
}
@@ -2489,10 +2488,8 @@ void TRP_GROUP_MIN_MAX::trace_basic_info(const PARAM *param,
// can have group quick without ranges
if (index_tree)
{
- String range_info;
- range_info.set_charset(system_charset_info);
- append_range_all_keyparts(&trace_range, NULL, &range_info, index_tree,
- key_part);
+ append_range_all_keyparts(&trace_range, *param, param_idx,
+ index_tree, key_part);
}
}
@@ -6398,20 +6395,9 @@ void TRP_ROR_INTERSECT::trace_basic_info(const PARAM *param,
trace_isect_idx.add("rows", (*cur_scan)->records);
Json_writer_array trace_range(thd, "ranges");
- for (const SEL_ARG *current= (*cur_scan)->sel_arg->first(); current;
- current= current->next)
- {
- String range_info;
- range_info.set_charset(system_charset_info);
- for (const SEL_ARG *part= current; part;
- part= part->next_key_part ? part->next_key_part : nullptr)
- {
- const KEY_PART_INFO *cur_key_part= key_part + part->part;
- append_range(&range_info, cur_key_part, part->min_value,
- part->max_value, part->min_flag | part->max_flag);
- }
- trace_range.add(range_info.ptr(), range_info.length());
- }
+
+ append_range_all_keyparts(&trace_range, *param, (*cur_scan)->idx,
+ (*cur_scan)->sel_arg->first(), key_part);
}
}
@@ -7389,9 +7375,6 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
const KEY &cur_key= param->table->key_info[keynr];
const KEY_PART_INFO *key_part= cur_key.key_part;
- String range_info;
- range_info.set_charset(system_charset_info);
-
index_scan->idx= idx;
index_scan->keynr= keynr;
index_scan->key_info= ¶m->table->key_info[keynr];
@@ -7402,8 +7385,8 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
*tree->index_scans_end++= index_scan;
if (unlikely(thd->trace_started()))
- append_range_all_keyparts(&trace_range, NULL, &range_info, key,
- key_part);
+ append_range_all_keyparts(&trace_range, (*param), idx,
+ key, key_part);
trace_range.end();
trace_idx.add("rowid_ordered", param->is_ror_scan)
@@ -13554,11 +13537,8 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time)
Json_writer_array trace_range(thd, "ranges");
const KEY_PART_INFO *key_part= cur_index_info->key_part;
-
- String range_info;
- range_info.set_charset(system_charset_info);
- append_range_all_keyparts(&trace_range, NULL, &range_info,
- cur_index_tree, key_part);
+ append_range_all_keyparts(&trace_range, *param, cur_param_idx,
+ cur_index_tree, key_part);
}
}
cost_group_min_max(table, cur_index_info, cur_used_key_parts,
@@ -15730,12 +15710,17 @@ void QUICK_GROUP_MIN_MAX_SELECT::dbug_dump(int indent, bool verbose)
}
#endif /* !DBUG_OFF */
+
static
void append_range(String *out, const KEY_PART_INFO *key_part,
- const uchar *min_key, const uchar *max_key, const uint flag)
+ KEY_MULTI_RANGE *range, uint n_key_parts)
{
- if (out->length() > 0)
- out->append(STRING_WITH_LEN(" AND "));
+ uint flag= range->range_flag;
+ String key_name;
+ key_name.set_charset(system_charset_info);
+ key_part_map keypart_map= range->start_key.length ?
+ range->start_key.keypart_map :
+ range->end_key.keypart_map;
if (flag & GEOM_FLAG)
{
@@ -15744,22 +15729,24 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
range types, so printing "col < some_geom" doesn't make sense.
Just print the column name, not operator.
*/
- out->append(key_part->field->field_name);
+ print_keyparts_name(out, key_part, n_key_parts, keypart_map);
out->append(STRING_WITH_LEN(" "));
- print_key_value(out, key_part, min_key);
+ print_key_value(out, key_part, range->start_key.key,
+ range->start_key.length);
return;
}
if (!(flag & NO_MIN_RANGE))
{
- print_key_value(out, key_part, min_key);
+ print_key_value(out, key_part, range->start_key.key,
+ range->start_key.length);
if (flag & NEAR_MIN)
out->append(STRING_WITH_LEN(" < "));
else
out->append(STRING_WITH_LEN(" <= "));
}
- out->append(key_part->field->field_name);
+ print_keyparts_name(out, key_part, n_key_parts, keypart_map);
if (!(flag & NO_MAX_RANGE))
{
@@ -15767,7 +15754,8 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
out->append(STRING_WITH_LEN(" < "));
else
out->append(STRING_WITH_LEN(" <= "));
- print_key_value(out, key_part, max_key);
+ print_key_value(out, key_part, range->end_key.key,
+ range->end_key.length);
}
}
@@ -15775,60 +15763,39 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
Add ranges to the trace
For ex:
- query: select * from t1 where a=2 ;
- and we have an index on a , so we create a range
- 2 <= a <= 2
+ lets say we have an index a_b(a,b)
+ query: select * from t1 where a=2 and b=4 ;
+ so we create a range:
+ (2,4) <= (a,b) <= (2,4)
this is added to the trace
*/
static void append_range_all_keyparts(Json_writer_array *range_trace,
- String *range_string,
- String *range_so_far, const SEL_ARG *keypart,
+ PARAM param, uint idx,
+ SEL_ARG *keypart,
const KEY_PART_INFO *key_parts)
{
-
- DBUG_ASSERT(keypart);
- DBUG_ASSERT(keypart && keypart != &null_element);
-
- // Navigate to first interval in red-black tree
+ SEL_ARG_RANGE_SEQ seq;
+ KEY_MULTI_RANGE range;
+ range_seq_t seq_it;
+ uint flags= 0;
+ RANGE_SEQ_IF seq_if = {NULL, sel_arg_range_seq_init,
+ sel_arg_range_seq_next, 0, 0};
+ KEY *keyinfo= param.table->key_info + param.real_keynr[idx];
+ uint n_key_parts= param.table->actual_n_key_parts(keyinfo);
+ seq.keyno= idx;
+ seq.real_keyno= param.real_keynr[idx];
+ seq.param= ¶m;
+ seq.start= keypart;
const KEY_PART_INFO *cur_key_part= key_parts + keypart->part;
- const SEL_ARG *keypart_range= keypart->first();
- const size_t save_range_so_far_length= range_so_far->length();
-
+ seq_it= seq_if.init((void *) &seq, 0, flags);
- while (keypart_range)
+ while (!seq_if.next(seq_it, &range))
{
- // Append the current range predicate to the range String
- switch (keypart->type)
- {
- case SEL_ARG::Type::KEY_RANGE:
- append_range(range_so_far, cur_key_part, keypart_range->min_value,
- keypart_range->max_value,
- keypart_range->min_flag | keypart_range->max_flag);
- break;
- case SEL_ARG::Type::MAYBE_KEY:
- range_so_far->append("MAYBE_KEY");
- break;
- case SEL_ARG::Type::IMPOSSIBLE:
- range_so_far->append("IMPOSSIBLE");
- break;
- default:
- DBUG_ASSERT(false);
- break;
- }
-
- if (keypart_range->next_key_part &&
- keypart_range->next_key_part->part ==
- keypart_range->part + 1 &&
- keypart_range->is_singlepoint())
- {
- append_range_all_keyparts(range_trace, range_string, range_so_far,
- keypart_range->next_key_part, key_parts);
- }
- else
- range_trace->add(range_so_far->c_ptr_safe(), range_so_far->length());
- keypart_range= keypart_range->next;
- range_so_far->length(save_range_so_far_length);
+ String range_info;
+ range_info.set_charset(system_charset_info);
+ append_range(&range_info, cur_key_part, &range, n_key_parts);
+ range_trace->add(range_info.c_ptr_safe(), range_info.length());
}
}
@@ -15838,70 +15805,110 @@ static void append_range_all_keyparts(Json_writer_array *range_trace,
@param[out] out String the key is appended to
@param[in] key_part Index components description
@param[in] key Key tuple
+ @param[in] used_length length of the key tuple
*/
+
static void print_key_value(String *out, const KEY_PART_INFO *key_part,
- const uchar *key)
+ const uchar* key, uint used_length)
{
+ out->append(STRING_WITH_LEN("("));
Field *field= key_part->field;
+ StringBuffer<128> tmp(system_charset_info);
+ TABLE *table= field->table;
+ uint store_length;
+ my_bitmap_map *old_sets[2];
+ dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
+ const uchar *key_end= key+used_length;
- if (field->flags & BLOB_FLAG)
+ for (; key < key_end; key+=store_length, key_part++)
{
- // Byte 0 of a nullable key is the null-byte. If set, key is NULL.
- if (field->real_maybe_null() && *key)
- out->append(STRING_WITH_LEN("NULL"));
- else
- (field->type() == MYSQL_TYPE_GEOMETRY)
- ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
- : out->append(STRING_WITH_LEN("unprintable_blob_value"));
- return;
- }
+ field= key_part->field;
+ store_length= key_part->store_length;
+ if (field->flags & BLOB_FLAG)
+ {
+ // Byte 0 of a nullable key is the null-byte. If set, key is NULL.
+ if (field->real_maybe_null() && *key)
+ out->append(STRING_WITH_LEN("NULL"));
+ else
+ (field->type() == MYSQL_TYPE_GEOMETRY)
+ ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
+ : out->append(STRING_WITH_LEN("unprintable_blob_value"));
+ goto next;
+ }
- uint store_length= key_part->store_length;
+ if (field->real_maybe_null())
+ {
+ /*
+ Byte 0 of key is the null-byte. If set, key is NULL.
+ Otherwise, print the key value starting immediately after the
+ null-byte
+ */
+ if (*key)
+ {
+ out->append(STRING_WITH_LEN("NULL"));
+ goto next;
+ }
+ key++; // Skip null byte
+ store_length--;
+ }
- if (field->real_maybe_null())
- {
/*
- Byte 0 of key is the null-byte. If set, key is NULL.
- Otherwise, print the key value starting immediately after the
- null-byte
+ Binary data cannot be converted to UTF8 which is what the
+ optimizer trace expects. If the column is binary, the hex
+ representation is printed to the trace instead.
*/
- if (*key)
+ if (field->flags & BINARY_FLAG)
{
- out->append(STRING_WITH_LEN("NULL"));
- return;
+ out->append("0x");
+ for (uint i = 0; i < store_length; i++)
+ {
+ out->append(_dig_vec_lower[*(key + i) >> 4]);
+ out->append(_dig_vec_lower[*(key + i) & 0x0F]);
+ }
+ goto next;
}
- key++; // Skip null byte
- store_length--;
+
+ field->set_key_image(key, key_part->length);
+ if (field->type() == MYSQL_TYPE_BIT)
+ (void)field->val_int_as_str(&tmp, 1); // may change tmp's charset
+ else
+ field->val_str(&tmp); // may change tmp's charset
+ out->append(tmp.ptr(), tmp.length(), tmp.charset());
+
+ next:
+ if (key + store_length < key_end)
+ out->append(STRING_WITH_LEN(","));
}
+ dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
+ out->append(STRING_WITH_LEN(")"));
+}
- /*
- Binary data cannot be converted to UTF8 which is what the
- optimizer trace expects. If the column is binary, the hex
- representation is printed to the trace instead.
- */
- if (field->flags & BINARY_FLAG)
+/**
+ Print key parts involed in a range
+ @param[out] out String the key is appended to
+ @param[in] key_part Index components description
+ @param[in] n_keypart Number of keyparts in index
+ @param[in] keypart_map map for keyparts involved in the range
+*/
+
+void print_keyparts_name(String *out, const KEY_PART_INFO *key_part,
+ uint n_keypart, key_part_map keypart_map)
+{
+ uint i;
+ out->append(STRING_WITH_LEN("("));
+ bool first_keypart= TRUE;
+ for (i=0; i < n_keypart; key_part++, i++)
{
- out->append("0x");
- for (uint i = 0; i < store_length; i++)
+ if (keypart_map & (1 << i))
{
- out->append(_dig_vec_lower[*(key + i) >> 4]);
- out->append(_dig_vec_lower[*(key + i) & 0x0F]);
+ if (first_keypart)
+ first_keypart= FALSE;
+ else
+ out->append(STRING_WITH_LEN(","));
+ out->append(key_part->field->field_name);
}
- return;
+ else
+ break;
}
-
- StringBuffer<128> tmp(system_charset_info);
- TABLE *table= field->table;
- my_bitmap_map *old_sets[2];
-
- dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
-
- field->set_key_image(key, key_part->length);
- if (field->type() == MYSQL_TYPE_BIT)
- (void)field->val_int_as_str(&tmp, 1); // may change tmp's charset
- else
- field->val_str(&tmp); // may change tmp's charset
- out->append(tmp.ptr(), tmp.length(), tmp.charset());
-
- dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
-}
+ out->append(STRING_WITH_LEN(")"));
+}
\ No newline at end of file
2
1