[Commits] ae4869e88: Range Locking: shared point lock support: support lock escalations
by Sergei Petrunia 10 Jun '19
by Sergei Petrunia 10 Jun '19
10 Jun '19
revision-id: ae4869e880a4118bd912fafe7fbeb6f464c4d893 (v5.8-1043-gae4869e88)
parent(s): 2f0ee897552bb4a8aa66b933c0d6f8529a82e2e8
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-06-10 17:16:12 +0300
message:
Range Locking: shared point lock support: support lock escalations
- Make Lock Escalation keep shared locks. Shared locks are not collapsed
with other kinds of locks.
- Replace RangeLockMgrHandle::get_escalation_count() with GetStatus()
which also reports amount of memory used for Range Locking (and there
is more data we could report through this)
- Initialize LTM_STATUS_S::m_initialized.
---
include/rocksdb/utilities/transaction_db.h | 9 ++-
.../transactions/range_locking/ft/ft-status.h | 2 +-
.../range_locking/locktree/locktree.cc | 78 ++++++++++++++++------
.../range_locking/locktree/treenode.cc | 10 +++
.../transactions/range_locking/locktree/treenode.h | 11 ---
utilities/transactions/transaction_lock_mgr.cc | 18 ++---
utilities/transactions/transaction_lock_mgr.h | 6 +-
7 files changed, 89 insertions(+), 45 deletions(-)
diff --git a/include/rocksdb/utilities/transaction_db.h b/include/rocksdb/utilities/transaction_db.h
index 7ebac3e06..7444cb5f2 100644
--- a/include/rocksdb/utilities/transaction_db.h
+++ b/include/rocksdb/utilities/transaction_db.h
@@ -38,7 +38,14 @@ const uint32_t kInitialMaxDeadlocks = 5;
class RangeLockMgrHandle {
public:
virtual int set_max_lock_memory(size_t max_lock_memory) = 0;
- virtual uint64_t get_escalation_count() = 0;
+
+ class Counters {
+ public:
+ uint64_t escalation_count;
+ uint64_t current_lock_memory;
+ };
+
+ virtual Counters GetStatus() = 0;
virtual ~RangeLockMgrHandle() {};
};
diff --git a/utilities/transactions/range_locking/ft/ft-status.h b/utilities/transactions/range_locking/ft/ft-status.h
index 25051f1ed..242964f0b 100644
--- a/utilities/transactions/range_locking/ft/ft-status.h
+++ b/utilities/transactions/range_locking/ft/ft-status.h
@@ -80,7 +80,7 @@ public:
TOKU_ENGINE_STATUS_ROW_S status[LTM_STATUS_NUM_ROWS];
private:
- bool m_initialized;
+ bool m_initialized = false;
};
typedef LTM_STATUS_S* LTM_STATUS;
extern LTM_STATUS_S ltm_status;
diff --git a/utilities/transactions/range_locking/locktree/locktree.cc b/utilities/transactions/range_locking/locktree/locktree.cc
index 0e5f7c307..00ce5aace 100644
--- a/utilities/transactions/range_locking/locktree/locktree.cc
+++ b/utilities/transactions/range_locking/locktree/locktree.cc
@@ -742,11 +742,16 @@ static int extract_first_n_row_locks(concurrent_tree::locked_keyrange *lkr,
int num_to_extract;
row_lock *row_locks;
bool fn(const keyrange &range, TXNID txnid, bool is_shared, TxnidVector *owners) {
- // psergey-todo: multiple owners!
if (num_extracted < num_to_extract) {
row_lock lock;
lock.range.create_copy(range);
lock.txnid = txnid;
+ lock.is_shared= is_shared;
+ // deep-copy the set of owners:
+ if (owners)
+ lock.owners = new TxnidVector(*owners);
+ else
+ lock.owners = nullptr;
row_locks[num_extracted++] = lock;
return true;
} else {
@@ -834,38 +839,60 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca
// through them and merge adjacent locks with the same txnid into
// one dominating lock and save it to a set of escalated locks.
//
- // first, find the index of the next row lock with a different txnid
+ // first, find the index of the next row lock that
+ // - belongs to a different txnid, or
+ // - belongs to several txnids, or
+ // - is a shared lock (we could potentially merge those but
+ // currently we don't)
int next_txnid_index = current_index + 1;
+
while (next_txnid_index < num_extracted &&
- extracted_buf[current_index].txnid == extracted_buf[next_txnid_index].txnid) {
+ (extracted_buf[current_index].txnid ==
+ extracted_buf[next_txnid_index].txnid) &&
+ !extracted_buf[next_txnid_index].is_shared &&
+ !extracted_buf[next_txnid_index].owners) {
next_txnid_index++;
}
// Create an escalated range for the current txnid that dominates
// each range between the current indext and the next txnid's index.
- const TXNID current_txnid = extracted_buf[current_index].txnid;
+ //const TXNID current_txnid = extracted_buf[current_index].txnid;
const DBT *escalated_left_key = extracted_buf[current_index].range.get_left_key();
const DBT *escalated_right_key = extracted_buf[next_txnid_index - 1].range.get_right_key();
// Try to find a range buffer for the current txnid. Create one if it doesn't exist.
// Then, append the new escalated range to the buffer.
- uint32_t idx;
- struct txnid_range_buffer *existing_range_buffer;
- int r = range_buffers.find_zero<TXNID, txnid_range_buffer::find_by_txnid>(
- current_txnid,
- &existing_range_buffer,
- &idx
- );
- if (r == DB_NOTFOUND) {
- struct txnid_range_buffer *XMALLOC(new_range_buffer);
- new_range_buffer->txnid = current_txnid;
- new_range_buffer->buffer.create();
- new_range_buffer->buffer.append(escalated_left_key, escalated_right_key);
- range_buffers.insert_at(new_range_buffer, idx);
- } else {
- invariant_zero(r);
- invariant(existing_range_buffer->txnid == current_txnid);
- existing_range_buffer->buffer.append(escalated_left_key, escalated_right_key);
+ // (If a lock is shared by multiple txnids, append it each of txnid's lists)
+ TxnidVector *owners_ptr;
+ TxnidVector singleton_owner;
+ if (extracted_buf[current_index].owners)
+ owners_ptr = extracted_buf[current_index].owners;
+ else {
+ singleton_owner.insert(extracted_buf[current_index].txnid);
+ owners_ptr = &singleton_owner;
+ }
+
+ for (auto cur_txnid : *owners_ptr ) {
+ uint32_t idx;
+ struct txnid_range_buffer *existing_range_buffer;
+ int r = range_buffers.find_zero<TXNID, txnid_range_buffer::find_by_txnid>(
+ cur_txnid,
+ &existing_range_buffer,
+ &idx
+ );
+ if (r == DB_NOTFOUND) {
+ struct txnid_range_buffer *XMALLOC(new_range_buffer);
+ new_range_buffer->txnid = cur_txnid;
+ new_range_buffer->buffer.create();
+ new_range_buffer->buffer.append(escalated_left_key, escalated_right_key,
+ !extracted_buf[current_index].is_shared);
+ range_buffers.insert_at(new_range_buffer, idx);
+ } else {
+ invariant_zero(r);
+ invariant(existing_range_buffer->txnid == cur_txnid);
+ existing_range_buffer->buffer.append(escalated_left_key, escalated_right_key,
+ !extracted_buf[current_index].is_shared);
+ }
}
current_index = next_txnid_index;
@@ -873,6 +900,7 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca
// destroy the ranges copied during the extraction
for (int i = 0; i < num_extracted; i++) {
+ delete extracted_buf[i].owners;
extracted_buf[i].range.destroy();
}
}
@@ -880,6 +908,12 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca
// Rebuild the locktree from each range in each range buffer,
// then notify higher layers that the txnid's locks have changed.
+ //
+ // (shared locks: if a lock was initially shared between transactions TRX1,
+ // TRX2, etc, we will now try to acquire it acting on behalf on TRX1, on
+ // TRX2, etc. This will succeed and an identical shared lock will be
+ // constructed)
+
invariant(m_rangetree->is_empty());
const size_t num_range_buffers = range_buffers.size();
for (size_t i = 0; i < num_range_buffers; i++) {
@@ -894,7 +928,7 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca
keyrange range;
range.create(rec.get_left_key(), rec.get_right_key());
row_lock lock = { .range = range, .txnid = current_txnid,
- .is_shared= false, // psergey-todo: SharedLockEscalation
+ .is_shared= !rec.get_exclusive_flag(),
.owners= nullptr };
insert_row_lock_into_tree(&lkr, lock, m_mgr);
iter.next();
diff --git a/utilities/transactions/range_locking/locktree/treenode.cc b/utilities/transactions/range_locking/locktree/treenode.cc
index f44918a1b..5bf349749 100644
--- a/utilities/transactions/range_locking/locktree/treenode.cc
+++ b/utilities/transactions/range_locking/locktree/treenode.cc
@@ -134,6 +134,16 @@ void treenode::swap_in_place(treenode *node1, treenode *node2) {
node2->m_is_shared= tmp_is_shared;
}
+void treenode::add_shared_owner(TXNID txnid) {
+ assert(m_is_shared);
+ if (m_txnid != TXNID_SHARED) {
+ m_owners= new TxnidVector;
+ m_owners->insert(m_txnid);
+ m_txnid= TXNID_SHARED;
+ }
+ m_owners->insert(txnid);
+}
+
void treenode::free(treenode *node) {
// destroy the range, freeing any copied keys
node->m_range.destroy();
diff --git a/utilities/transactions/range_locking/locktree/treenode.h b/utilities/transactions/range_locking/locktree/treenode.h
index 6b082acc4..f23324f03 100644
--- a/utilities/transactions/range_locking/locktree/treenode.h
+++ b/utilities/transactions/range_locking/locktree/treenode.h
@@ -167,17 +167,6 @@ private:
// it has at the time of destruction.
keyrange m_range;
- void add_shared_owner(TXNID txnid)
- {
- assert(m_is_shared);
- if (m_txnid != TXNID_SHARED)
- {
- m_owners= new TxnidVector;
- m_owners->insert(m_txnid);
- m_txnid= TXNID_SHARED;
- }
- m_owners->insert(txnid);
- }
void remove_shared_owner(TXNID txnid);
bool has_multiple_owners() { return (m_txnid == TXNID_SHARED); }
diff --git a/utilities/transactions/transaction_lock_mgr.cc b/utilities/transactions/transaction_lock_mgr.cc
index 34144c2af..564cd8600 100644
--- a/utilities/transactions/transaction_lock_mgr.cc
+++ b/utilities/transactions/transaction_lock_mgr.cc
@@ -1154,25 +1154,25 @@ RangeLockMgr::~RangeLockMgr() {
ltm_.destroy();
}
-uint64_t RangeLockMgr::get_escalation_count() {
+RangeLockMgrHandle::Counters RangeLockMgr::GetStatus() {
LTM_STATUS_S ltm_status_test;
ltm_.get_status(<m_status_test);
+ Counters res;
// Searching status variable by its string name is how Toku's unit tests
// do it (why didn't they make LTM_ESCALATION_COUNT constant visible?)
- TOKU_ENGINE_STATUS_ROW key_status = NULL;
// lookup keyname in status
- for (int i = 0; ; i++) {
+ for (int i = 0; i < LTM_STATUS_S::LTM_STATUS_NUM_ROWS; i++) {
TOKU_ENGINE_STATUS_ROW status = <m_status_test.status[i];
- if (status->keyname == NULL)
- break;
if (strcmp(status->keyname, "LTM_ESCALATION_COUNT") == 0) {
- key_status = status;
- break;
+ res.escalation_count = status->value.num;
+ continue;
+ }
+ if (strcmp(status->keyname, "LTM_SIZE_CURRENT") == 0) {
+ res.current_lock_memory = status->value.num;
}
}
- assert(key_status);
- return key_status->value.num;
+ return res;
}
void RangeLockMgr::AddColumnFamily(const ColumnFamilyHandle *cfh) {
diff --git a/utilities/transactions/transaction_lock_mgr.h b/utilities/transactions/transaction_lock_mgr.h
index 608d6f34f..6c8e0638c 100644
--- a/utilities/transactions/transaction_lock_mgr.h
+++ b/utilities/transactions/transaction_lock_mgr.h
@@ -239,7 +239,11 @@ class RangeLockMgr :
return ltm_.set_max_lock_memory(max_lock_memory);
}
- uint64_t get_escalation_count() override;
+ size_t get_max_lock_memory() {
+ return ltm_.get_max_lock_memory();
+ }
+
+ Counters GetStatus() override;
LockStatusData GetLockStatusData() override;
1
0
[Commits] f04edf4: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
by IgorBabaev 09 Jun '19
by IgorBabaev 09 Jun '19
09 Jun '19
revision-id: f04edf4b68f6b9fd2722dca289a675266ec99a7a (mariadb-5.5.64-13-gf04edf4)
parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-06-09 10:39:52 -0700
message:
MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
Handling of top level conjuncts in WHERE whose used_tables() contained
RAND_TABLE_BIT in the function make_join_select() was incorrect.
As a result if such a conjunct referred to fields non of which belonged
to the last joined table it was pushed twice. (This could be seen
for a test case from subselect.test whose output was changed after this
patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for
the query from this test case we clearly see that one of the conjuncts
is pushed twice.) This fact by itself was not good. Besides, if such a
conjunct was pushed to a table that was the result of materialization
of a semi-join the query could return a wrong result set. In particular
we could watch it for queries with semi-join subqueries whose left parts
used stored functions without "deterministic' specifier.
---
mysql-test/r/subselect.result | 2 +-
mysql-test/r/subselect_mat.result | 89 +++++++++++++++++++++++++++++++
mysql-test/r/subselect_no_mat.result | 2 +-
mysql-test/r/subselect_no_opts.result | 2 +-
mysql-test/r/subselect_no_scache.result | 2 +-
mysql-test/r/subselect_no_semijoin.result | 2 +-
mysql-test/r/subselect_sj_mat.result | 89 +++++++++++++++++++++++++++++++
mysql-test/t/subselect_sj_mat.test | 75 ++++++++++++++++++++++++++
sql/sql_select.cc | 87 +++++++++++++++++++++++++-----
9 files changed, 331 insertions(+), 19 deletions(-)
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index cdedc02..2999f4c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6939,7 +6939,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index efc348a..0e10800 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2453,6 +2453,95 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
1
drop table t1,t2;
drop view v1;
+#
+# MDEV-19580: function invocation in the left part of IN subquery
+#
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+create function f1(who int, dt date) returns int
+deterministic
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+create function f2(who int, dt date) returns int
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+# Deterministic function in left part of IN subquery: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+# Non-deterministic function in left part of IN subq: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+select t1.*, t4.*,
+(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+from t1 left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a s
+3 paul 1 1 songwriter 1
+4 art 1 1 songwriter 1
+1 mrs 2 2 song character 2
+explain extended select t1.*, t4.*,
+(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+from t1 left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(select max(`test`.`t4`.`id`) from `test`.`t4` where ((`test`.`t4`.`id` = `test`.`t1`.`b`) and (sleep(0) = 0))) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
# End of 5.5 tests
set @subselect_mat_test_optimizer_switch_value=null;
set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index a729129..5c21e74 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6937,7 +6937,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index c41fa1b..8ab1f1b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 1c18135..f0afa7e 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6945,7 +6945,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 89c6712..0d5fbc3 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index fd9435e..cb4b630 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2493,4 +2493,93 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
1
drop table t1,t2;
drop view v1;
+#
+# MDEV-19580: function invocation in the left part of IN subquery
+#
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+create function f1(who int, dt date) returns int
+deterministic
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+create function f2(who int, dt date) returns int
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+# Deterministic function in left part of IN subquery: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+# Non-deterministic function in left part of IN subq: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+select t1.*, t4.*,
+(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+from t1 left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a s
+3 paul 1 1 songwriter 1
+4 art 1 1 songwriter 1
+1 mrs 2 2 song character 2
+explain extended select t1.*, t4.*,
+(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+from t1 left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(select max(`test`.`t4`.`id`) from `test`.`t4` where ((`test`.`t4`.`id` = `test`.`t1`.`b`) and (sleep(0) = 0))) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
# End of 5.5 tests
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index c82c1e7..9c3788a 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -2230,4 +2230,79 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
drop table t1,t2;
drop view v1;
+
+
+--echo #
+--echo # MDEV-19580: function invocation in the left part of IN subquery
+--echo #
+
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+
+delimiter $$;
+
+create function f1(who int, dt date) returns int
+deterministic
+begin
+ declare result int;
+ select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+ return result;
+end$$
+
+create function f2(who int, dt date) returns int
+begin
+ declare result int;
+ select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+ return result;
+end$$
+
+delimiter ;$$
+
+--echo # Deterministic function in left part of IN subquery: semi-join is OK
+
+let $q1=
+select * from t1
+ left join t4 on t1.b = t4.id
+ where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q1;
+eval explain extended $q1;
+
+--echo # Non-deterministic function in left part of IN subq: semi-join is OK
+
+let $q2=
+select * from t1
+ left join t4 on t1.b = t4.id
+ where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q2;
+eval explain extended $q2;
+
+let $q3=
+select t1.*, t4.*,
+ (select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+ from t1 left join t4 on t1.b = t4.id
+ where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q3;
+eval explain extended $q3;
+
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
+
--echo # End of 5.5 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 37f8292..d7ff92a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -204,7 +204,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond,
table_map used_table,
int join_tab_idx_arg,
bool exclude_expensive_cond,
- bool retain_ref_cond);
+ bool retain_ref_cond,
+ bool is_top_and_level);
static Item* part_of_refkey(TABLE *form,Field *field);
uint find_shortest_key(TABLE *table, const key_map *usable_keys);
@@ -8922,12 +8923,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
RAND_TABLE_BIT;
}
- /*
- Following force including random expression in last table condition.
- It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5
- */
- if (tab == join->join_tab + last_top_base_tab_idx)
- current_map|= RAND_TABLE_BIT;
used_tables|=current_map;
if (tab->type == JT_REF && tab->quick &&
@@ -8968,6 +8963,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
{
tmp= make_cond_for_table(thd, cond, used_tables, current_map, i,
FALSE, FALSE);
+ if (tab == join->join_tab + last_top_base_tab_idx)
+ {
+ /*
+ This pushes conjunctive conditions of WHERE condition such that:
+ - their used_tables() contain RAND_TABLE_BIT
+ - the conditions does not refer to any fields
+ (such like rand() > 0.5)
+ */
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+ COND *rand_cond= make_cond_for_table(thd, cond, used_tables,
+ rand_table_bit, -1,
+ FALSE, FALSE);
+ add_cond_and_fix(thd, &tmp, rand_cond);
+ }
}
/* Add conditions added by add_not_null_conds(). */
if (tab->select_cond)
@@ -9283,8 +9292,24 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
psergey: have put the -1 below. It's bad, will need to fix it.
*/
COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2,
- current_map, /*(tab - first_tab)*/ -1,
- FALSE, FALSE);
+ current_map,
+ /*(tab - first_tab)*/ -1,
+ FALSE, FALSE);
+ if (tab == last_tab)
+ {
+ /*
+ This pushes conjunctive conditions of ON expression of an outer
+ join such that:
+ - their used_tables() contain RAND_TABLE_BIT
+ - the conditions does not refer to any fields
+ (such like rand() > 0.5)
+ */
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+ COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2,
+ rand_table_bit, -1,
+ FALSE, FALSE);
+ add_cond_and_fix(thd, &tmp_cond, rand_cond);
+ }
bool is_sjm_lookup_tab= FALSE;
if (tab->bush_children)
{
@@ -18824,7 +18849,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables,
return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table,
join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, true);
}
@@ -18834,9 +18859,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
int join_tab_idx_arg,
bool exclude_expensive_cond __attribute__
((unused)),
- bool retain_ref_cond)
+ bool retain_ref_cond,
+ bool is_top_and_level)
{
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+
if (used_table && !(cond->used_tables() & used_table))
return (COND*) 0; // Already checked
@@ -18852,11 +18880,28 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
Item *item;
while ((item=li++))
{
+ /*
+ Special handling of top level conjuncts with RAND_TABLE_BIT:
+ if such a conjunct contains a reference to a field that is not
+ an outer field then it is pushed to the corresponding table by
+ the same rule as all other conjuncts. Otherwise, if the conjunct
+ is used in WHERE is is pushed to the last joined table, if is it
+ is used in ON condition of an outer join it is pushed into the
+ last inner table of the outer join. Such conjuncts are pushed in
+ a call of make_cond_for_table_from_pred() with the
+ parameter 'used_table' equal to PSEUDO_TABLE_BITS.
+ */
+ if (is_top_and_level && used_table == rand_table_bit &&
+ (item->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+ {
+ /* The conjunct with RAND_TABLE_BIT has been allready pushed */
+ continue;
+ }
Item *fix=make_cond_for_table_from_pred(thd, root_cond, item,
tables, used_table,
- join_tab_idx_arg,
+ join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, false);
if (fix)
new_cond->argument_list()->push_back(fix);
}
@@ -18880,6 +18925,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
}
else
{ // Or list
+ if (is_top_and_level && used_table == rand_table_bit &&
+ (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+ {
+ /* This top level formula with RAND_TABLE_BIT has been already pushed */
+ return (COND*) 0;
+ }
+
Item_cond_or *new_cond=new Item_cond_or;
if (!new_cond)
return (COND*) 0; // OOM /* purecov: inspected */
@@ -18891,7 +18943,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
tables, 0L,
join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, false);
if (!fix)
return (COND*) 0; // Always true
new_cond->argument_list()->push_back(fix);
@@ -18908,6 +18960,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
}
}
+ if (is_top_and_level && used_table == rand_table_bit &&
+ (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+ {
+ /* This top level formula with RAND_TABLE_BIT has been already pushed */
+ return (COND*) 0;
+ }
+
/*
Because the following test takes a while and it can be done
table_count times, we mark each item that we have examined with the result
1
0
[Commits] 5d06edfb261: MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
by Sergei Petrunia 07 Jun '19
by Sergei Petrunia 07 Jun '19
07 Jun '19
revision-id: 5d06edfb2616ab0b4b067580c281afbbef8fdc74 (mariadb-10.2.24-69-g5d06edfb261)
parent(s): 9b22354a594570e23cc675d90836743ce7a3ba1c
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-06-08 02:28:29 +0300
message:
MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
Make it visible
---
mysql-test/r/subselect_no_semijoin.result | 55 +++++++++++++++++++++++++++++++
mysql-test/t/subselect_no_semijoin.test | 23 +++++++++++++
sql/sql_explain.cc | 5 +++
sql/sql_explain.h | 1 +
sql/sql_select.cc | 1 +
5 files changed, 85 insertions(+)
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index a0625246e2b..55d408c54fc 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7291,5 +7291,60 @@ pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
# End of 10.2 tests
+#
+# MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1),(5);
+CREATE TABLE t2 ( b INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 ( c INT );
+INSERT INTO t3 VALUES (4),(5);
+SET @tmp19714=@@optimizer_switch;
+SET optimizer_switch='subquery_cache=off';
+explain format=json
+SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 2,
+ "filtered": 100
+ },
+ "subqueries": [
+ {
+ "query_block": {
+ "select_id": 2,
+ "pseudo_bits_condition": "1 = t1.a or <in_optimizer>(1,<exists>(subquery#3))",
+ "table": {
+ "table_name": "t2",
+ "access_type": "system",
+ "rows": 1,
+ "filtered": 100
+ },
+ "subqueries": [
+ {
+ "query_block": {
+ "select_id": 3,
+ "table": {
+ "table_name": "t3",
+ "access_type": "ALL",
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "1 = t3.c"
+ }
+ }
+ }
+ ]
+ }
+ }
+ ]
+ }
+}
+SET optimizer_switch=@tmp19714;
+drop table t1,t2,t3;
set @optimizer_switch_for_subselect_test=null;
set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/t/subselect_no_semijoin.test b/mysql-test/t/subselect_no_semijoin.test
index 46791667173..eda95b93503 100644
--- a/mysql-test/t/subselect_no_semijoin.test
+++ b/mysql-test/t/subselect_no_semijoin.test
@@ -8,5 +8,28 @@ set @join_cache_level_for_subselect_test=@@join_cache_level;
--source t/subselect.test
+--echo #
+--echo # MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
+--echo #
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1),(5);
+
+# t2 must be MyISAM or Aria and contain 1 row
+CREATE TABLE t2 ( b INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+
+CREATE TABLE t3 ( c INT );
+INSERT INTO t3 VALUES (4),(5);
+
+SET @tmp19714=@@optimizer_switch;
+SET optimizer_switch='subquery_cache=off';
+
+explain format=json
+SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1;
+
+SET optimizer_switch=@tmp19714;
+
+drop table t1,t2,t3;
+
set @optimizer_switch_for_subselect_test=null;
set @join_cache_level_for_subselect_test=NULL;
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 1ab10fb3c37..3ebd44dc0bb 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -857,6 +857,11 @@ void Explain_select::print_explain_json(Explain_query *query,
writer->add_member("outer_ref_condition");
write_item(writer, outer_ref_cond);
}
+ if (pseudo_bits_cond)
+ {
+ writer->add_member("pseudo_bits_condition");
+ write_item(writer, pseudo_bits_cond);
+ }
/* we do not print HAVING which always evaluates to TRUE */
if (having || (having_value == Item::COND_FALSE))
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index 3ca816477fc..08af84b3562 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -232,6 +232,7 @@ class Explain_select : public Explain_basic_join
/* Expensive constant condition */
Item *exec_const_cond;
Item *outer_ref_cond;
+ Item *pseudo_bits_cond;
/* HAVING condition */
Item *having;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 182e913c16c..0ee3735c464 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -25025,6 +25025,7 @@ int JOIN::save_explain_data_intern(Explain_query *output,
xpl_sel->exec_const_cond= exec_const_cond;
xpl_sel->outer_ref_cond= outer_ref_cond;
+ xpl_sel->pseudo_bits_cond= pseudo_bits_cond;
if (tmp_having)
xpl_sel->having= tmp_having;
else
1
0
[Commits] f7579518e2c: MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
by Sergei Petrunia 05 Jun '19
by Sergei Petrunia 05 Jun '19
05 Jun '19
revision-id: f7579518e2c32936442a1e20e391f60660c94b3c (mariadb-10.3.12-226-gf7579518e2c)
parent(s): 7060b0320d1479bb9476e0cbd4acc584e059e1ff
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-06-05 14:00:45 +0300
message:
MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
Modify best_access_path() to produce rows=1 estimate for null-rejecting
lookups on unique NULL keys.
---
mysql-test/main/invisible_field.result | 4 +-
mysql-test/main/join.result | 30 +++++++++
mysql-test/main/join.test | 30 +++++++++
mysql-test/main/order_by.result | 2 +-
mysql-test/main/subselect_sj.result | 74 ++++++++++++++++++++---
mysql-test/main/subselect_sj.test | 52 +++++++++++++++-
mysql-test/main/subselect_sj_jcl6.result | 58 +++++++++++++++++-
mysql-test/main/subselect_sj_nonmerged.result | 12 ++--
mysql-test/main/table_elim.result | 2 +-
mysql-test/suite/gcol/r/gcol_select_myisam.result | 2 +-
sql/sql_select.cc | 47 +++++++++-----
11 files changed, 276 insertions(+), 37 deletions(-)
diff --git a/mysql-test/main/invisible_field.result b/mysql-test/main/invisible_field.result
index 876a80814e5..43a8b9d726b 100644
--- a/mysql-test/main/invisible_field.result
+++ b/mysql-test/main/invisible_field.result
@@ -404,8 +404,8 @@ b int(11) YES NULL
c int(11) YES NULL
explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 10
-1 SIMPLE t1 ALL b,c NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t1 ref b,c b 5 test.t2.c 1 Using where
select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
a a b c
1 1 1 1
diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result
index cc8e174c8e6..8ca82002855 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -1599,3 +1599,33 @@ SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a );
a b c b c
DROP TABLE t1,t2;
+#
+# MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+pk int not null primary key auto_increment,
+a int,
+b int,
+unique key(a)
+);
+insert into t1 (a,b) select null, 12345 from t0 A, t0 B, t0 C;
+insert into t1 (a,b) select a,a from t0;
+# Simulate InnoDB's persistent statistics (It always uses nulls_equal)
+set @tmp1= @@myisam_stats_method;
+set myisam_stats_method=nulls_equal;
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+set myisam_stats_method=@tmp1;
+show keys from t1;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
+t1 0 PRIMARY 1 pk A 1010 NULL NULL BTREE
+t1 0 a 1 a A 10 NULL NULL YES BTREE
+# t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45):
+explain select * from t0,t1 where t0.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t1 ref a a 5 test.t0.a 1
+drop table t0,t1;
diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test
index 3d2a02e2346..b90a9cc39eb 100644
--- a/mysql-test/main/join.test
+++ b/mysql-test/main/join.test
@@ -1254,3 +1254,33 @@ SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a );
DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
+--echo #
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (
+ pk int not null primary key auto_increment,
+ a int,
+ b int,
+ unique key(a)
+);
+
+# 10K of null values
+insert into t1 (a,b) select null, 12345 from t0 A, t0 B, t0 C;
+insert into t1 (a,b) select a,a from t0;
+
+--echo # Simulate InnoDB's persistent statistics (It always uses nulls_equal)
+set @tmp1= @@myisam_stats_method;
+set myisam_stats_method=nulls_equal;
+analyze table t1;
+set myisam_stats_method=@tmp1;
+show keys from t1;
+
+--echo # t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45):
+explain select * from t0,t1 where t0.a=t1.a;
+
+drop table t0,t1;
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index 8d1e471f618..8692e727c60 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -1515,7 +1515,7 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range a,b,c c 5 NULL 420 Using where
-1 SIMPLE t1 ref a a 39 test.t2.a,const 10 Using where; Using index
+1 SIMPLE t1 ref a a 39 test.t2.a,const 1 Using where; Using index
SELECT d FROM t3 AS t1, t2 AS t2
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result
index 13d5224566b..ad43a09c7ff 100644
--- a/mysql-test/main/subselect_sj.result
+++ b/mysql-test/main/subselect_sj.result
@@ -2555,33 +2555,89 @@ CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
INSERT INTO t1 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),
(6,3),(7,1),(8,4),(9,3),(10,2);
-CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+CREATE TABLE t2 ( c INT, d INT, KEY(c) );
INSERT INTO t2 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+analyze table t1,t2;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+explain
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+1 PRIMARY t1 index b b 5 NULL 10 Using where; Using index; LooseScan
+1 PRIMARY t2 ref c c 5 test.t1.b 1 Using where; FirstMatch(t1)
+1 PRIMARY t1 ref b b 5 test.t1.b 2
SELECT a, b, d FROM t1, t2
WHERE ( b, d ) IN
( SELECT b, d FROM t1, t2 WHERE b = c );
a b d
2 1 2
7 1 2
-2 1 2
-7 1 2
-1 2 1
-4 2 1
-10 2 1
+8 4 2
1 2 1
4 2 1
10 2 1
3 3 3
6 3 3
9 3 3
+2 1 2
+7 1 2
+8 4 2
+5 5 5
3 3 3
6 3 3
9 3 3
-8 4 2
-8 4 2
-5 5 5
+1 2 1
+4 2 1
+10 2 1
DROP TABLE t1, t2;
+# Another testcase for the above that still uses LooseScan:
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t10(a int primary key);
+insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t1 (
+pk int primary key auto_increment,
+kp1 int,
+kp2 int,
+filler char(100),
+key (kp1, kp2)
+);
+insert into t1 (kp1, kp2, filler)
+select
+A.a, B.a, 'filler-data'
+from t0 A, t0 B;
+create table t2 (a int, filler char(100), key(a));
+create table t3 (a int);
+insert into t3 values (1),(2);
+insert into t2
+select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B;
+analyze table t1,t2,t3;
+Table Op Msg_type Msg_text
+test.t1 analyze status Table is already up to date
+test.t2 analyze status Table is already up to date
+test.t3 analyze status OK
+delete from t1 where kp2 in (1,3);
+# Ref + LooseScan on t1:
+explain select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 10 Using where; Using index; LooseScan
+1 PRIMARY t2 ref a a 5 test.t1.kp2 20 Using index
+select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+sum(t2.a)
+1640
+drop table t0,t10;
+drop table t1,t2,t3;
#
# BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
#
diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test
index 29211cadd9c..81cca95092d 100644
--- a/mysql-test/main/subselect_sj.test
+++ b/mysql-test/main/subselect_sj.test
@@ -2285,16 +2285,66 @@ INSERT INTO t1 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),
(6,3),(7,1),(8,4),(9,3),(10,2);
-CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+CREATE TABLE t2 ( c INT, d INT, KEY(c) );
INSERT INTO t2 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+analyze table t1,t2;
+explain
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+ ( SELECT b, d FROM t1, t2 WHERE b = c );
SELECT a, b, d FROM t1, t2
WHERE ( b, d ) IN
( SELECT b, d FROM t1, t2 WHERE b = c );
DROP TABLE t1, t2;
+--echo # Another testcase for the above that still uses LooseScan:
+
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t10(a int primary key);
+insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+
+create table t1 (
+ pk int primary key auto_increment,
+ kp1 int,
+ kp2 int,
+ filler char(100),
+ key (kp1, kp2)
+);
+
+# 10 groups, each has 10 elements.
+insert into t1 (kp1, kp2, filler)
+select
+ A.a, B.a, 'filler-data'
+from t0 A, t0 B;
+
+create table t2 (a int, filler char(100), key(a));
+
+create table t3 (a int);
+insert into t3 values (1),(2);
+
+insert into t2
+select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B;
+
+analyze table t1,t2,t3;
+delete from t1 where kp2 in (1,3);
+
+--echo # Ref + LooseScan on t1:
+explain select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+
+select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+
+drop table t0,t10;
+drop table t1,t2,t3;
+
--echo #
--echo # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
--echo #
diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result
index 014c44d1181..0026924b605 100644
--- a/mysql-test/main/subselect_sj_jcl6.result
+++ b/mysql-test/main/subselect_sj_jcl6.result
@@ -2569,9 +2569,22 @@ CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
INSERT INTO t1 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),
(6,3),(7,1),(8,4),(9,3),(10,2);
-CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+CREATE TABLE t2 ( c INT, d INT, KEY(c) );
INSERT INTO t2 VALUES
(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+analyze table t1,t2;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+explain
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+1 PRIMARY t1 index b b 5 NULL 10 Using where; Using index; LooseScan
+1 PRIMARY t2 ref c c 5 test.t1.b 1 Using where; FirstMatch(t1)
+1 PRIMARY t1 ref b b 5 test.t1.b 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT a, b, d FROM t1, t2
WHERE ( b, d ) IN
( SELECT b, d FROM t1, t2 WHERE b = c );
@@ -2596,6 +2609,49 @@ a b d
10 2 1
10 2 1
DROP TABLE t1, t2;
+# Another testcase for the above that still uses LooseScan:
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t10(a int primary key);
+insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t1 (
+pk int primary key auto_increment,
+kp1 int,
+kp2 int,
+filler char(100),
+key (kp1, kp2)
+);
+insert into t1 (kp1, kp2, filler)
+select
+A.a, B.a, 'filler-data'
+from t0 A, t0 B;
+create table t2 (a int, filler char(100), key(a));
+create table t3 (a int);
+insert into t3 values (1),(2);
+insert into t2
+select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B;
+analyze table t1,t2,t3;
+Table Op Msg_type Msg_text
+test.t1 analyze status Table is already up to date
+test.t2 analyze status Table is already up to date
+test.t3 analyze status OK
+delete from t1 where kp2 in (1,3);
+# Ref + LooseScan on t1:
+explain select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 10 Using where; Using index; LooseScan
+1 PRIMARY t2 ref a a 5 test.t1.kp2 20 Using index
+select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+sum(t2.a)
+1640
+drop table t0,t10;
+drop table t1,t2,t3;
#
# BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
#
diff --git a/mysql-test/main/subselect_sj_nonmerged.result b/mysql-test/main/subselect_sj_nonmerged.result
index 47970668ae5..4d9a70e6bba 100644
--- a/mysql-test/main/subselect_sj_nonmerged.result
+++ b/mysql-test/main/subselect_sj_nonmerged.result
@@ -67,9 +67,9 @@ insert into t4 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t0 A, t0 B;
explain select * from t0, t4 where
t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 10
-1 PRIMARY t4 ALL a NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t4.a 1
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),test.t0.a 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B;
@@ -77,9 +77,9 @@ explain select * from t4 where
t4.a in (select max(t2.a) from t1, t2 group by t2.b) and
t4.b in (select max(t2.a) from t1, t2 group by t2.b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5
-1 PRIMARY t4 ref a a 5 <subquery2>.max(t2.a) 12 Using index condition
-1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 test.t4.b 1
+1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join)
+1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 1
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary
3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary
diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result
index cf9a4a38779..7780e165451 100644
--- a/mysql-test/main/table_elim.result
+++ b/mysql-test/main/table_elim.result
@@ -279,7 +279,7 @@ insert into t2 values
explain select t1.* from t1 left join t2 on t2.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index
-1 SIMPLE t2 ref a a 3 test.t1.a 2 Using where
+1 SIMPLE t2 ref a a 3 test.t1.a 1 Using where
drop table t1, t2;
#
# check UPDATE/DELETE that look like they could be eliminated
diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result
index d0fe7fbd0d4..280618ed76e 100644
--- a/mysql-test/suite/gcol/r/gcol_select_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result
@@ -1102,7 +1102,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN SELECT * FROM t1 AS t2 STRAIGHT_JOIN t1 FORCE INDEX(b) WHERE t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where
-1 SIMPLE t1 ref b b 5 test.t2.b 2
+1 SIMPLE t1 ref b b 5 test.t2.b 1
EXPLAIN SELECT b FROM t1 FORCE INDEX(b);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL b 5 NULL 2 Using index
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0fbc4bac7d8..13a5cd88c93 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5507,18 +5507,16 @@ add_key_field(JOIN *join,
(*key_fields)->level= and_level;
(*key_fields)->optimize= optimize;
/*
- If the condition has form "tbl.keypart = othertbl.field" and
- othertbl.field can be NULL, there will be no matches if othertbl.field
- has NULL value.
- We use null_rejecting in add_not_null_conds() to add
- 'othertbl.field IS NOT NULL' to tab->select_cond.
+ If the condition we are analyzing is NULL-rejecting and at least
+ one side of the equalities is NULLable, mark the KEY_FIELD object as
+ null-rejecting. This property is used by:
+ - add_not_null_conds() to add "column IS NOT NULL" conditions
+ - best_access_path() to produce better estimates for NULL-able unique keys.
*/
{
- Item *real= (*value)->real_item();
- if (((cond->functype() == Item_func::EQ_FUNC) ||
- (cond->functype() == Item_func::MULT_EQUAL_FUNC)) &&
- (real->type() == Item::FIELD_ITEM) &&
- ((Item_field*)real)->field->maybe_null())
+ if ((cond->functype() == Item_func::EQ_FUNC ||
+ cond->functype() == Item_func::MULT_EQUAL_FUNC) &&
+ ((*value)->maybe_null || field->real_maybe_null()))
(*key_fields)->null_rejecting= true;
else
(*key_fields)->null_rejecting= false;
@@ -6834,6 +6832,7 @@ best_access_path(JOIN *join,
ulong key_flags;
uint key_parts;
key_part_map found_part= 0;
+ key_part_map notnull_part=0; // key parts which won't have NULL in lookup tuple.
table_map found_ref= 0;
uint key= keyuse->key;
bool ft_key= (keyuse->keypart == FT_KEYPART);
@@ -6892,6 +6891,9 @@ best_access_path(JOIN *join,
if (!(keyuse->used_tables & ~join->const_table_map))
const_part|= keyuse->keypart_map;
+ if (!keyuse->val->maybe_null || keyuse->null_rejecting)
+ notnull_part|=keyuse->keypart_map;
+
double tmp2= prev_record_reads(join->positions, idx,
(found_ref | keyuse->used_tables));
if (tmp2 < best_prev_record_reads)
@@ -6942,12 +6944,19 @@ best_access_path(JOIN *join,
loose_scan_opt.check_ref_access_part1(s, key, start_key, found_part);
/* Check if we found full key */
- if (found_part == PREV_BITS(uint, key_parts) &&
- !ref_or_null_part)
+ const key_part_map all_key_parts= PREV_BITS(uint, key_parts);
+ if (found_part == all_key_parts && !ref_or_null_part)
{ /* use eq key */
max_key_part= (uint) ~0;
- if ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME ||
- MY_TEST(key_flags & HA_EXT_NOSAME))
+ /*
+ If the index is a unique index (1), and
+ - all its columns are not null (2), or
+ - equalities we are using reject NULLs (3)
+ then the estimate is rows=1.
+ */
+ if ((key_flags & (HA_NOSAME | HA_EXT_NOSAME)) && // (1)
+ (!(key_flags & HA_NULL_PART_KEY) || // (2)
+ all_key_parts == notnull_part)) // (3)
{
tmp = prev_record_reads(join->positions, idx, found_ref);
records=1.0;
@@ -9991,8 +10000,16 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
uint maybe_null= MY_TEST(keyinfo->key_part[i].null_bit);
j->ref.items[i]=keyuse->val; // Save for cond removal
j->ref.cond_guards[i]= keyuse->cond_guard;
- if (keyuse->null_rejecting)
+
+ /*
+ Set ref.null_rejecting to true only if we are going to inject a
+ "keyuse->val IS NOT NULL" predicate.
+ */
+ Item *real= (keyuse->val)->real_item();
+ if (keyuse->null_rejecting && (real->type() == Item::FIELD_ITEM) &&
+ ((Item_field*)real)->field->maybe_null())
j->ref.null_rejecting|= (key_part_map)1 << i;
+
keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
/*
We don't want to compute heavy expressions in EXPLAIN, an example would
1
0
revision-id: 38564f99677726bf35013b5b94dcbf11a3809db0 (mariadb-10.1.39-43-g38564f9)
parent(s): aaf53ea0b68efde4a90cabbbcaf9ca41c1fbf62f
committer: Alexey Botchkov
timestamp: 2019-05-27 09:48:40 +0400
message:
Sc #27132 proposed fix.
---
sql/sql_audit.cc | 16 ++++++++++++++++
sql/sql_audit.h | 1 +
sql/sql_class.cc | 4 +++-
sql/sql_class.h | 1 +
sql/sql_connect.cc | 3 ++-
sql/sql_plugin.cc | 3 +++
sql/sql_plugin.h | 1 +
sql/threadpool_common.cc | 3 ++-
8 files changed, 29 insertions(+), 3 deletions(-)
diff --git a/sql/sql_audit.cc b/sql/sql_audit.cc
index dd98e3c..cee0ac2 100644
--- a/sql/sql_audit.cc
+++ b/sql/sql_audit.cc
@@ -212,6 +212,7 @@ void mysql_audit_acquire_plugins(THD *thd, ulong *event_class_mask)
{
plugin_foreach(thd, acquire_plugins, MYSQL_AUDIT_PLUGIN, event_class_mask);
add_audit_mask(thd->audit_class_mask, event_class_mask);
+ thd->audit_plugin_version= global_plugin_version;
}
DBUG_VOID_RETURN;
}
@@ -242,6 +243,20 @@ void mysql_audit_notify(THD *thd, uint event_class, uint event_subtype, ...)
/**
+ Check if there were changes in the state of plugins
+ so we need to do the mysql_audit_release asap.
+
+ @param[in] thd
+
+*/
+
+my_bool mysql_audit_release_required(THD *thd)
+{
+ return thd && (thd->audit_plugin_version != global_plugin_version);
+}
+
+
+/**
Release any resources associated with the current thd.
@param[in] thd
@@ -276,6 +291,7 @@ void mysql_audit_release(THD *thd)
/* Reset the state of thread values */
reset_dynamic(&thd->audit_class_plugins);
bzero(thd->audit_class_mask, sizeof(thd->audit_class_mask));
+ thd->audit_plugin_version= -1;
}
diff --git a/sql/sql_audit.h b/sql/sql_audit.h
index 550b2a5..9a74675 100644
--- a/sql/sql_audit.h
+++ b/sql/sql_audit.h
@@ -60,6 +60,7 @@ static inline void mysql_audit_notify(THD *thd, uint event_class,
#define mysql_audit_connection_enabled() 0
#define mysql_audit_table_enabled() 0
#endif
+extern my_bool mysql_audit_release_required(THD *thd);
extern void mysql_audit_release(THD *thd);
#define MAX_USER_HOST_SIZE 512
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 8fabcd5..6bcff6d 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -776,6 +776,9 @@ THD::THD(bool is_wsrep_applier)
waiting_on_group_commit(FALSE), has_waiter(FALSE),
spcont(NULL),
m_parser_state(NULL),
+#ifndef EMBEDDED_LIBRARY
+ audit_plugin_version(-1),
+#endif
#if defined(ENABLED_DEBUG_SYNC)
debug_sync_control(0),
#endif /* defined(ENABLED_DEBUG_SYNC) */
@@ -1562,7 +1565,6 @@ THD::~THD()
mdl_context.destroy();
ha_close_connection(this);
- mysql_audit_release(this);
plugin_thdvar_cleanup(this);
main_security_ctx.destroy();
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 1cb516c..6392394 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -2978,6 +2978,7 @@ class THD :public Statement,
added to the list of audit plugins which are currently in use.
*/
unsigned long audit_class_mask[MYSQL_AUDIT_CLASS_MASK_SIZE];
+ int audit_plugin_version;
#endif
#if defined(ENABLED_DEBUG_SYNC)
diff --git a/sql/sql_connect.cc b/sql/sql_connect.cc
index 4dbb53f..a6a01b1 100644
--- a/sql/sql_connect.cc
+++ b/sql/sql_connect.cc
@@ -1326,7 +1326,8 @@ void do_handle_one_connection(THD *thd_arg)
while (thd_is_connection_alive(thd))
{
- mysql_audit_release(thd);
+ if (mysql_audit_release_required(thd))
+ mysql_audit_release(thd);
if (do_command(thd))
break;
}
diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc
index 21093e3..48131b1 100644
--- a/sql/sql_plugin.cc
+++ b/sql/sql_plugin.cc
@@ -228,6 +228,7 @@ static DYNAMIC_ARRAY plugin_array;
static HASH plugin_hash[MYSQL_MAX_PLUGIN_TYPE_NUM];
static MEM_ROOT plugin_mem_root;
static bool reap_needed= false;
+volatile int global_plugin_version= 1;
static bool initialized= 0;
ulong dlopen_count;
@@ -2181,6 +2182,7 @@ bool mysql_install_plugin(THD *thd, const LEX_STRING *name,
reap_plugins();
}
err:
+ global_plugin_version++;
mysql_mutex_unlock(&LOCK_plugin);
if (argv)
free_defaults(argv);
@@ -2327,6 +2329,7 @@ bool mysql_uninstall_plugin(THD *thd, const LEX_STRING *name,
}
reap_plugins();
+ global_plugin_version++;
mysql_mutex_unlock(&LOCK_plugin);
DBUG_RETURN(error);
diff --git a/sql/sql_plugin.h b/sql/sql_plugin.h
index 7f74114..3bde06a 100644
--- a/sql/sql_plugin.h
+++ b/sql/sql_plugin.h
@@ -37,6 +37,7 @@ enum enum_plugin_load_option { PLUGIN_OFF, PLUGIN_ON, PLUGIN_FORCE,
PLUGIN_FORCE_PLUS_PERMANENT };
extern const char *global_plugin_typelib_names[];
+extern volatile int global_plugin_version;
extern ulong dlopen_count;
#include <my_sys.h>
diff --git a/sql/threadpool_common.cc b/sql/threadpool_common.cc
index b4066bd..b8be708 100644
--- a/sql/threadpool_common.cc
+++ b/sql/threadpool_common.cc
@@ -266,7 +266,8 @@ int threadpool_process_request(THD *thd)
{
Vio *vio;
thd->net.reading_or_writing= 0;
- mysql_audit_release(thd);
+ if (mysql_audit_release_required(thd))
+ mysql_audit_release(thd);
if ((retval= do_command(thd)) != 0)
goto end;
3
4
[Commits] 15f97e3fd92: MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, [Warning] InnoDB: Using a partial-field key prefix in search
by Varun 03 Jun '19
by Varun 03 Jun '19
03 Jun '19
revision-id: 15f97e3fd922933987e78ef3c2b6490f0edf8172 (mariadb-10.4.4-141-g15f97e3fd92)
parent(s): 92df31dfbfcf6068f4f4a7e7794a15333158c569
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-06-03 11:22:39 +0530
message:
MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, [Warning] InnoDB: Using a partial-field key prefix in search
For a key with keyparts (k1,k2,k3) , if we are building a range over the keyparts
we should make sure that if min_value/max_value for a keypart is not added to
key buffer then the keyparts following should also not be allowed.
---
mysql-test/main/range_innodb.result | 19 +++++++++++++++++++
mysql-test/main/range_innodb.test | 19 +++++++++++++++++++
sql/opt_range.h | 10 ++++++----
3 files changed, 44 insertions(+), 4 deletions(-)
diff --git a/mysql-test/main/range_innodb.result b/mysql-test/main/range_innodb.result
index 30161a2711d..7d8c6e5b475 100644
--- a/mysql-test/main/range_innodb.result
+++ b/mysql-test/main/range_innodb.result
@@ -80,3 +80,22 @@ ERROR HY000: Table definition has changed, please retry transaction
DROP TABLE t0,t1;
set @@global.debug_dbug="-d";
set @@optimizer_switch= @optimizer_switch_save;
+#
+# MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase,
+# [Warning] InnoDB: Using a partial-field key prefix in search
+#
+CREATE TABLE t1 (
+pk INT,
+a VARCHAR(1),
+b INT,
+PRIMARY KEY (pk),
+KEY (a,b)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'a',1),(2,'b',2);
+explain
+SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index PRIMARY,a a 9 NULL 2 Using where; Using index
+SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0;
+a
+drop table t1;
diff --git a/mysql-test/main/range_innodb.test b/mysql-test/main/range_innodb.test
index a17ef3f1146..2c225df27fe 100644
--- a/mysql-test/main/range_innodb.test
+++ b/mysql-test/main/range_innodb.test
@@ -87,3 +87,22 @@ select * from t1 where a=10 and b=10;
DROP TABLE t0,t1;
set @@global.debug_dbug="-d";
set @@optimizer_switch= @optimizer_switch_save;
+
+--echo #
+--echo # MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase,
+--echo # [Warning] InnoDB: Using a partial-field key prefix in search
+--echo #
+
+CREATE TABLE t1 (
+ pk INT,
+ a VARCHAR(1),
+ b INT,
+ PRIMARY KEY (pk),
+ KEY (a,b)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1,'a',1),(2,'b',2);
+
+explain SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0;
+SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0;
+drop table t1;
diff --git a/sql/opt_range.h b/sql/opt_range.h
index ae0e3822272..73def7bde92 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -459,8 +459,9 @@ class SEL_ARG :public Sql_alloc
uint res= key_tree->store_min(key[key_tree->part].store_length,
range_key, *range_key_flag);
// add flags only if a key_part is written to the buffer
- if (res)
- *range_key_flag|= key_tree->min_flag;
+ if (!res)
+ return 0;
+ *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 &&
@@ -482,8 +483,9 @@ 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);
- if (res)
- (*range_key_flag)|= key_tree->max_flag;
+ if (!res)
+ return 0;
+ *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] 32818f5e57d: MDEV-18791 Wrong error upon creating Aria table with long index on BLOB
by sachin.setiyaï¼ mariadb.com 02 Jun '19
by sachin.setiyaï¼ mariadb.com 02 Jun '19
02 Jun '19
revision-id: 32818f5e57ddb262dff8b2e72ed612748d29d3d0 (mariadb-10.4.5-25-g32818f5e57d)
parent(s): 8b545d3d41416772203cb98f06243873a4f0d8f7
author: Sachin
committer: Sachin
timestamp: 2019-06-02 13:05:54 +0530
message:
MDEV-18791 Wrong error upon creating Aria table with long index on BLOB
Create a Create_table_error_handler to mask ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN error.
---
mysql-test/main/long_unique_bugs.result | 2 ++
mysql-test/main/long_unique_bugs.test | 6 ++++++
sql/handler.cc | 9 +++++++++
sql/share/errmsg-utf8.txt | 2 ++
sql/sql_class.cc | 25 +++++++++++++++++++++++++
sql/sql_class.h | 24 ++++++++++++++++++++++++
6 files changed, 68 insertions(+)
diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result
index 33496c4e20d..4a65cc69ced 100644
--- a/mysql-test/main/long_unique_bugs.result
+++ b/mysql-test/main/long_unique_bugs.result
@@ -265,3 +265,5 @@ ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
disconnect con1;
connection default;
DROP TABLE t1, t2;
+CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria;
+ERROR HY000: Aria Storage engine does not support long unique keys
diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test
index dc78f6c7067..ce76211184d 100644
--- a/mysql-test/main/long_unique_bugs.test
+++ b/mysql-test/main/long_unique_bugs.test
@@ -317,3 +317,9 @@ INSERT IGNORE INTO t1 VALUES (4, 1)/*4*/;
--disconnect con1
--connection default
DROP TABLE t1, t2;
+
+#
+# MDEV-18791 Wrong error upon creating Aria table with long index on BLOB
+#
+--error ER_NO_LONG_UNIQUE_ENGINE_SUPPORT
+CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria;
diff --git a/sql/handler.cc b/sql/handler.cc
index 124f5c8e9ce..b5303fbf67e 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -5135,6 +5135,7 @@ int ha_create_table(THD *thd, const char *path,
TABLE_SHARE share;
bool temp_table __attribute__((unused)) =
create_info->options & (HA_LEX_CREATE_TMP_TABLE | HA_CREATE_TMP_ALTER);
+ Create_table_error_handler err_handler;
DBUG_ENTER("ha_create_table");
init_tmp_table_share(thd, &share, db, 0, table_name, path);
@@ -5170,12 +5171,20 @@ int ha_create_table(THD *thd, const char *path,
name= get_canonical_filename(table.file, share.path.str, name_buff);
+ thd->push_internal_handler(&err_handler);
error= table.file->ha_create(name, &table, create_info);
+ thd->pop_internal_handler();
if (unlikely(error))
{
if (!thd->is_error())
+ {
+ if (err_handler.safely_trapped_errors())
+ {
+ my_error(ER_NO_LONG_UNIQUE_ENGINE_SUPPORT, MYF(0), hton_name(table.file->ht)->str);
+ }
my_error(ER_CANT_CREATE_TABLE, MYF(0), db, table_name, error);
+ }
table.file->print_error(error, MYF(ME_WARNING));
PSI_CALL_drop_table_share(temp_table, share.db.str, (uint)share.db.length,
share.table_name.str, (uint)share.table_name.length);
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index c64f60f3562..3fb53ad55e6 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7931,3 +7931,5 @@ ER_PERIOD_CONSTRAINT_DROP
eng "Can't DROP CONSTRAINT `%s`. Use DROP PERIOD `%s` for this"
ER_TOO_LONG_KEYPART 42000 S1009
eng "Specified key part was too long; max key part length is %u bytes"
+ER_NO_LONG_UNIQUE_ENGINE_SUPPORT
+ eng "%s Storage engine does not support long unique keys"
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 94e2b518fa4..4cd3c49cd41 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -563,6 +563,31 @@ bool Drop_table_error_handler::handle_condition(THD *thd,
sql_errno == ER_TRG_NO_DEFINER);
}
+/**
+ Implementation of Create_table_error_handler::handle_condition().
+ The reason in having this implementation is to silence technical low-level
+ error during CREATE TABLE operation. Currently we don't want to expose
+ the following warnings during DROP TABLE:
+ - When using long unique on ARIA storage engine show long unique not
+ avaliable in ARIA instead of ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
+ @return TRUE if the condition is handled.
+*/
+bool Create_table_error_handler::handle_condition(THD *thd,
+ uint sql_errno,
+ const char* sqlstate,
+ Sql_condition::enum_warning_level *level,
+ const char* msg,
+ Sql_condition ** cond_hdl)
+{
+ *cond_hdl= NULL;
+ if (sql_errno == ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN)
+ {
+ m_handled_error++;
+ return true;
+ }
+ return false;
+}
+
/**
Handle an error from MDL_context::upgrade_lock() and mysql_lock_tables().
diff --git a/sql/sql_class.h b/sql/sql_class.h
index dd9cfbbd1c4..c5c3058cc06 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1808,6 +1808,30 @@ class Drop_table_error_handler : public Internal_error_handler
private:
};
+/**
+ This class is an internal error handler implementation for
+ CREATE TABLE statements. The thing is that there may be error during
+ execution of these statements, which should not be exposed to the user.
+ This class is intended to silence such error.
+*/
+
+class Create_table_error_handler : public Internal_error_handler
+{
+public:
+ Create_table_error_handler(): m_handled_error(0) {}
+
+public:
+ bool handle_condition(THD *thd,
+ uint sql_errno,
+ const char* sqlstate,
+ Sql_condition::enum_warning_level *level,
+ const char* msg,
+ Sql_condition ** cond_hdl);
+ bool safely_trapped_errors(){return m_handled_error > 0;}
+private:
+ int m_handled_error;
+};
+
/**
Internal error handler to process an error from MDL_context::upgrade_lock()
1
0
[Commits] a602998: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
by IgorBabaev 01 Jun '19
by IgorBabaev 01 Jun '19
01 Jun '19
revision-id: a6029989cc289f80eed5da707815838c6848d24b (mariadb-5.5.64-13-ga602998)
parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-06-01 13:07:09 -0700
message:
MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
Handling of top level conjuncts in WHERE whose used_tables() contained
RAND_TABLE_BIT in the function make_join_select() was incorrect.
As a result if such a conjunct referred to fields non of which belonged
to the last joined table it was pushed twice. (This could be seen
for a test case from subselect.test whose output was changed after this
patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for
the query from this test case we clearly see that one of the conjuncts
is pushed twice.) This fact by itself was not good. Besides, if such a
conjunct was pushed to a table that was the result of materialization
of a semi-join the query could return a wrong result set. In particular
we could watch it for queries with semi-join subqueries whose left parts
used stored functions without "deterministic' specifier.
---
mysql-test/r/subselect.result | 2 +-
mysql-test/r/subselect_mat.result | 68 ++++++++++++++++++++++++
mysql-test/r/subselect_no_mat.result | 2 +-
mysql-test/r/subselect_no_opts.result | 2 +-
mysql-test/r/subselect_no_scache.result | 2 +-
mysql-test/r/subselect_no_semijoin.result | 2 +-
mysql-test/r/subselect_sj_mat.result | 68 ++++++++++++++++++++++++
mysql-test/t/subselect_sj_mat.test | 66 ++++++++++++++++++++++++
sql/sql_select.cc | 86 ++++++++++++++++++++++++++-----
9 files changed, 279 insertions(+), 19 deletions(-)
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index cdedc02..2999f4c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6939,7 +6939,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index efc348a..ae24485 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2453,6 +2453,74 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
1
drop table t1,t2;
drop view v1;
+#
+# MDEV-19580: function invocation in the left part of IN subquery
+#
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+create function f1(who int, dt date) returns int
+deterministic
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+create function f2(who int, dt date) returns int
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+# Deterministic function in left part of IN subquery: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+# Non-deterministic function in left part of IN subq: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
# End of 5.5 tests
set @subselect_mat_test_optimizer_switch_value=null;
set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index a729129..5c21e74 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6937,7 +6937,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index c41fa1b..8ab1f1b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 1c18135..f0afa7e 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6945,7 +6945,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 89c6712..0d5fbc3 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index fd9435e..9dde115 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2493,4 +2493,72 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
1
drop table t1,t2;
drop view v1;
+#
+# MDEV-19580: function invocation in the left part of IN subquery
+#
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+create function f1(who int, dt date) returns int
+deterministic
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+create function f2(who int, dt date) returns int
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+# Deterministic function in left part of IN subquery: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+# Non-deterministic function in left part of IN subq: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
# End of 5.5 tests
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index c82c1e7..997e71a 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -2230,4 +2230,70 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
drop table t1,t2;
drop view v1;
+
+
+--echo #
+--echo # MDEV-19580: function invocation in the left part of IN subquery
+--echo #
+
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+
+delimiter $$;
+
+create function f1(who int, dt date) returns int
+deterministic
+begin
+ declare result int;
+ select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+ return result;
+end$$
+
+create function f2(who int, dt date) returns int
+begin
+ declare result int;
+ select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+ return result;
+end$$
+
+delimiter ;$$
+
+--echo # Deterministic function in left part of IN subquery: semi-join is OK
+
+let $q1=
+select * from t1
+ left join t4 on t1.b = t4.id
+ where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q1;
+eval explain extended $q1;
+
+--echo # Non-deterministic function in left part of IN subq: semi-join is OK
+
+let $q2=
+select * from t1
+ left join t4 on t1.b = t4.id
+ where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q2;
+eval explain extended $q2;
+
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
+
--echo # End of 5.5 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 37f8292..bc69817 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -204,7 +204,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond,
table_map used_table,
int join_tab_idx_arg,
bool exclude_expensive_cond,
- bool retain_ref_cond);
+ bool retain_ref_cond,
+ bool is_top_and_level);
static Item* part_of_refkey(TABLE *form,Field *field);
uint find_shortest_key(TABLE *table, const key_map *usable_keys);
@@ -8922,12 +8923,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
RAND_TABLE_BIT;
}
- /*
- Following force including random expression in last table condition.
- It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5
- */
- if (tab == join->join_tab + last_top_base_tab_idx)
- current_map|= RAND_TABLE_BIT;
used_tables|=current_map;
if (tab->type == JT_REF && tab->quick &&
@@ -8968,6 +8963,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
{
tmp= make_cond_for_table(thd, cond, used_tables, current_map, i,
FALSE, FALSE);
+ if (tab == join->join_tab + last_top_base_tab_idx)
+ {
+ /*
+ This pushes conjunctive conditions of WHERE condition such that:
+ - their used_tables() contain RAND_TABLE_BIT
+ - the conditions does not refer to any fields
+ (such like rand() > 0.5)
+ */
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+ COND *rand_cond= make_cond_for_table(thd, cond, used_tables,
+ rand_table_bit, -1,
+ FALSE, FALSE);
+ add_cond_and_fix(thd, &tmp, rand_cond);
+ }
}
/* Add conditions added by add_not_null_conds(). */
if (tab->select_cond)
@@ -9283,8 +9292,24 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
psergey: have put the -1 below. It's bad, will need to fix it.
*/
COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2,
- current_map, /*(tab - first_tab)*/ -1,
- FALSE, FALSE);
+ current_map,
+ /*(tab - first_tab)*/ -1,
+ FALSE, FALSE);
+ if (tab == last_tab)
+ {
+ /*
+ This pushes conjunctive conditions of ON expression of an outer
+ join such that:
+ - their used_tables() contain RAND_TABLE_BIT
+ - the conditions does not refer to any fields
+ (such like rand() > 0.5)
+ */
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+ COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2,
+ rand_table_bit, -1,
+ FALSE, FALSE);
+ add_cond_and_fix(thd, &tmp_cond, rand_cond);
+ }
bool is_sjm_lookup_tab= FALSE;
if (tab->bush_children)
{
@@ -18824,7 +18849,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables,
return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table,
join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, true);
}
@@ -18834,9 +18859,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
int join_tab_idx_arg,
bool exclude_expensive_cond __attribute__
((unused)),
- bool retain_ref_cond)
+ bool retain_ref_cond,
+ bool is_top_and_level)
{
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+
if (used_table && !(cond->used_tables() & used_table))
return (COND*) 0; // Already checked
@@ -18852,11 +18880,27 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
Item *item;
while ((item=li++))
{
+ /*
+ Special handling of top level conjuncts with RAND_TABLE_BIT:
+ if such a conjunct contains a reference to a field then it is pushed
+ to the corresponding table by the same rule as all other conjuncts.
+ Otherwise, if the conjunct is used in WHERE is is pushed to the last
+ joined table, if is it is used in ON condition of an outer join it
+ is pushed into the last inner table of the outer join. Such conjuncts
+ are pushed in a call of make_cond_for_table_from_pred() with the
+ parameter 'used_table' equal to RAND_TABLE_BIT.
+ */
+ if (is_top_and_level && used_table == rand_table_bit &&
+ item->used_tables() != rand_table_bit)
+ {
+ /* The conjunct with RAND_TABLE_BIT has been allready pushed */
+ continue;
+ }
Item *fix=make_cond_for_table_from_pred(thd, root_cond, item,
tables, used_table,
- join_tab_idx_arg,
+ join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, false);
if (fix)
new_cond->argument_list()->push_back(fix);
}
@@ -18880,6 +18924,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
}
else
{ // Or list
+ if (is_top_and_level && used_table == rand_table_bit &&
+ cond->used_tables() != rand_table_bit)
+ {
+ /* This top level formula with RAND_TABLE_BIT has been already pushed */
+ return (COND*) 0;
+ }
+
Item_cond_or *new_cond=new Item_cond_or;
if (!new_cond)
return (COND*) 0; // OOM /* purecov: inspected */
@@ -18891,7 +18942,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
tables, 0L,
join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, false);
if (!fix)
return (COND*) 0; // Always true
new_cond->argument_list()->push_back(fix);
@@ -18908,6 +18959,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
}
}
+ if (is_top_and_level && used_table == rand_table_bit &&
+ cond->used_tables() != rand_table_bit)
+ {
+ /* This top level formula with RAND_TABLE_BIT has been already pushed */
+ return (COND*) 0;
+ }
+
/*
Because the following test takes a while and it can be done
table_count times, we mark each item that we have examined with the result
1
0
[Commits] eb89a6c: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
by IgorBabaev 01 Jun '19
by IgorBabaev 01 Jun '19
01 Jun '19
revision-id: eb89a6cd23df80cad091ef6f1d50e0969488de7c (mariadb-5.5.64-13-geb89a6c)
parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-06-01 13:04:08 -0700
message:
MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
Handling of top level conjuncts in WHERE whose used_tables() contained
RAND_TABLE_BIT in the function make_join_select() was incorrect.
As a result if such a conjunct referred to fields non of which belonged
to the last joined table it was pushed twice. (This could be seen
for a test case from subselect.test whose output was changed after this
patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for
the query from this test case we clearly see that one of the conjuncts
is pushed twice.) This fact by itself was not good. Besides, if such a
conjunct was pushed to a table that was the result of materialization
of a semi-join the query could return a wrong result set. In particular
we could watch it for queries with semi-join subqueries whose left parts
used stored functions without "deterministic' specifier.
---
mysql-test/r/subselect.result | 2 +-
mysql-test/r/subselect_mat.result | 68 ++++++++++++++++++++++++
mysql-test/r/subselect_no_mat.result | 2 +-
mysql-test/r/subselect_no_opts.result | 2 +-
mysql-test/r/subselect_no_scache.result | 2 +-
mysql-test/r/subselect_no_semijoin.result | 2 +-
mysql-test/r/subselect_sj_mat.result | 68 ++++++++++++++++++++++++
mysql-test/t/subselect_sj_mat.test | 66 ++++++++++++++++++++++++
sql/sql_select.cc | 86 ++++++++++++++++++++++++++-----
9 files changed, 279 insertions(+), 19 deletions(-)
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index cdedc02..2999f4c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6939,7 +6939,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index efc348a..ae24485 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2453,6 +2453,74 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
1
drop table t1,t2;
drop view v1;
+#
+# MDEV-19580: function invocation in the left part of IN subquery
+#
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+create function f1(who int, dt date) returns int
+deterministic
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+create function f2(who int, dt date) returns int
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+# Deterministic function in left part of IN subquery: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+# Non-deterministic function in left part of IN subq: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
# End of 5.5 tests
set @subselect_mat_test_optimizer_switch_value=null;
set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index a729129..5c21e74 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6937,7 +6937,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index c41fa1b..8ab1f1b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 1c18135..f0afa7e 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6945,7 +6945,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 89c6712..0d5fbc3 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where
-1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index
+1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
set @tmp_mdev410=@@global.userstat;
set global userstat=on;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index fd9435e..9dde115 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2493,4 +2493,72 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
1
drop table t1,t2;
drop view v1;
+#
+# MDEV-19580: function invocation in the left part of IN subquery
+#
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+create function f1(who int, dt date) returns int
+deterministic
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+create function f2(who int, dt date) returns int
+begin
+declare result int;
+select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+return result;
+end$$
+# Deterministic function in left part of IN subquery: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+# Non-deterministic function in left part of IN subq: semi-join is OK
+select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id a b id a
+3 paul 1 1 songwriter
+4 art 1 1 songwriter
+1 mrs 2 2 song character
+explain extended select * from t1
+left join t4 on t1.b = t4.id
+where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`))
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
# End of 5.5 tests
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index c82c1e7..997e71a 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -2230,4 +2230,70 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
drop table t1,t2;
drop view v1;
+
+
+--echo #
+--echo # MDEV-19580: function invocation in the left part of IN subquery
+--echo #
+
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+
+delimiter $$;
+
+create function f1(who int, dt date) returns int
+deterministic
+begin
+ declare result int;
+ select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+ return result;
+end$$
+
+create function f2(who int, dt date) returns int
+begin
+ declare result int;
+ select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+ return result;
+end$$
+
+delimiter ;$$
+
+--echo # Deterministic function in left part of IN subquery: semi-join is OK
+
+let $q1=
+select * from t1
+ left join t4 on t1.b = t4.id
+ where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q1;
+eval explain extended $q1;
+
+--echo # Non-deterministic function in left part of IN subq: semi-join is OK
+
+let $q2=
+select * from t1
+ left join t4 on t1.b = t4.id
+ where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q2;
+eval explain extended $q2;
+
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
+
--echo # End of 5.5 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 37f8292..797b3a5 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -204,7 +204,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond,
table_map used_table,
int join_tab_idx_arg,
bool exclude_expensive_cond,
- bool retain_ref_cond);
+ bool retain_ref_cond,
+ bool is_top_and_level);
static Item* part_of_refkey(TABLE *form,Field *field);
uint find_shortest_key(TABLE *table, const key_map *usable_keys);
@@ -8922,12 +8923,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
RAND_TABLE_BIT;
}
- /*
- Following force including random expression in last table condition.
- It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5
- */
- if (tab == join->join_tab + last_top_base_tab_idx)
- current_map|= RAND_TABLE_BIT;
used_tables|=current_map;
if (tab->type == JT_REF && tab->quick &&
@@ -8968,6 +8963,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
{
tmp= make_cond_for_table(thd, cond, used_tables, current_map, i,
FALSE, FALSE);
+ if (tab == join->join_tab + last_top_base_tab_idx)
+ {
+ /*
+ This pushes conjunctive conditions of WHERE condition such that:
+ - their used_tables() contain RAND_TABLE_BIT
+ - the conditions does not refer to any fields
+ (such like rand() > 0.5)
+ */
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+ COND *rand_cond= make_cond_for_table(thd, cond, used_tables,
+ rand_table_bit, -1,
+ FALSE, FALSE);
+ add_cond_and_fix(thd, &tmp, rand_cond);
+ }
}
/* Add conditions added by add_not_null_conds(). */
if (tab->select_cond)
@@ -9283,8 +9292,24 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
psergey: have put the -1 below. It's bad, will need to fix it.
*/
COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2,
- current_map, /*(tab - first_tab)*/ -1,
- FALSE, FALSE);
+ current_map,
+ /*(tab - first_tab)*/ -1,
+ FALSE, FALSE);
+ if (tab == last_tab)
+ {
+ /*
+ This pushes conjunctive conditions of ON expression of an outer
+ join such that:
+ - their used_tables() contain RAND_TABLE_BIT
+ - the conditions does not refer to any fields
+ (such like rand() > 0.5)
+ */
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+ COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2,
+ rand_table_bit, -1,
+ FALSE, FALSE);
+ add_cond_and_fix(thd, &tmp_cond, rand_cond);
+ }
bool is_sjm_lookup_tab= FALSE;
if (tab->bush_children)
{
@@ -18824,7 +18849,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables,
return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table,
join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, true);
}
@@ -18834,9 +18859,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
int join_tab_idx_arg,
bool exclude_expensive_cond __attribute__
((unused)),
- bool retain_ref_cond)
+ bool retain_ref_cond,
+ bool is_top_and_level)
{
+ table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+
if (used_table && !(cond->used_tables() & used_table))
return (COND*) 0; // Already checked
@@ -18852,11 +18880,27 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
Item *item;
while ((item=li++))
{
+ /*
+ Special handling of top level conjuncts with RAND_TABLE_BIT:
+ if such a conjunct contains a reference to a field then it is pushed
+ to the corresponding table by the same rule as all other conjuncts.
+ Otherwise, if the conjunct is used in WHERE is is pushed to the last
+ joined table, if is it is used in ON condition of an outer join it
+ is pushed into the last inner table of the outer join. Such conjuncts
+ are pushed in a call of make_cond_for_table_from_pred() with the
+ parameter 'used_table' equal to RAND_TABLE_BIT.
+ */
+ if (is_top_and_level && used_table == rand_table_bit &&
+ item->used_tables() != rand_table_bit)
+ {
+ /* The conjunct with RAND_TABLE_BIT has been allready pushed */
+ continue;
+ }
Item *fix=make_cond_for_table_from_pred(thd, root_cond, item,
tables, used_table,
- join_tab_idx_arg,
+ join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, false);
if (fix)
new_cond->argument_list()->push_back(fix);
}
@@ -18880,6 +18924,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
}
else
{ // Or list
+ if (is_top_and_level && used_table == rand_table_bit &&
+ cond->used_tables() != rand_table_bit)
+ {
+ /* This top level formula with RAND_TABLE_BIT has been already pushed */
+ return (COND*) 0;
+ }
+
Item_cond_or *new_cond=new Item_cond_or;
if (!new_cond)
return (COND*) 0; // OOM /* purecov: inspected */
@@ -18891,7 +18942,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
tables, 0L,
join_tab_idx_arg,
exclude_expensive_cond,
- retain_ref_cond);
+ retain_ref_cond, false);
if (!fix)
return (COND*) 0; // Always true
new_cond->argument_list()->push_back(fix);
@@ -18908,6 +18959,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
}
}
+ if (is_top_and_level && used_table == rand_table_bit &&
+ cond->used_tables() != rand_table_bit)
+ {
+ /* This top level formula with RAND_TABLE_BIT has been already pushed */
+ return (COND*) 0;
+ }
+
/*
Because the following test takes a while and it can be done
table_count times, we mark each item that we have examined with the result
1
0
[Commits] 5469d88e7b4: MDEV-19049 Server crashes in check_duplicate_long_entry_key, ASAN stack-buffer-overflow in Field_blob::get_key_image
by sachin.setiyaï¼ mariadb.com 31 May '19
by sachin.setiyaï¼ mariadb.com 31 May '19
31 May '19
revision-id: 5469d88e7b4e723e545cd7d511801a16a3642dc4 (mariadb-10.4.3-104-g5469d88e7b4)
parent(s): 0bc42602266815b81fe86b08c2228912c1a95340
author: Sachin
committer: Sachin
timestamp: 2019-03-28 11:29:25 +0530
message:
MDEV-19049 Server crashes in check_duplicate_long_entry_key, ASAN stack-buffer-overflow in Field_blob::get_key_image
Long Unique keys should always be last unique key.
---
mysql-test/main/long_unique.result | 64 +++++++++++++++----------------
mysql-test/main/long_unique_bugs.result | 46 ++++++++++++++++++++++
mysql-test/main/long_unique_bugs.test | 22 ++++++++++-
mysql-test/main/long_unique_innodb.result | 4 +-
mysql-test/main/long_unique_update.result | 8 ++--
sql/sql_table.cc | 9 +++++
6 files changed, 114 insertions(+), 39 deletions(-)
diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result
index 3843ff4aff0..8ea6d36c321 100644
--- a/mysql-test/main/long_unique.result
+++ b/mysql-test/main/long_unique.result
@@ -184,8 +184,8 @@ t1 CREATE TABLE `t1` (
`a` blob DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`db_row_hash_1` int(11) DEFAULT NULL,
- UNIQUE KEY `a` (`a`) USING HASH,
- UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`)
+ UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+ UNIQUE KEY `a` (`a`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values(45,1,55),(46,1,55);
ERROR 23000: Duplicate entry '55' for key 'db_row_hash_1'
@@ -507,13 +507,13 @@ t1 CREATE TABLE `t1` (
`db_row_hash_1` int(11) DEFAULT NULL,
`db_row_hash_2` int(11) DEFAULT NULL,
`db_row_hash_3` int(11) DEFAULT NULL,
+ UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+ UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+ UNIQUE KEY `db_row_hash_3` (`db_row_hash_3`),
UNIQUE KEY `a` (`a`) USING HASH,
UNIQUE KEY `c` (`c`) USING HASH,
UNIQUE KEY `d` (`d`) USING HASH,
- UNIQUE KEY `e` (`e`) USING HASH,
- UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
- UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
- UNIQUE KEY `db_row_hash_3` (`db_row_hash_3`)
+ UNIQUE KEY `e` (`e`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
alter table t1 add column db_row_hash_7 int, add column db_row_hash_5 int , add column db_row_hash_4 int ;
alter table t1 drop column db_row_hash_7,drop column db_row_hash_3, drop column db_row_hash_4;
@@ -543,17 +543,17 @@ t1 CREATE TABLE `t1` (
`db_row_hash_1` int(11) DEFAULT NULL,
`db_row_hash_2` int(11) DEFAULT NULL,
`db_row_hash_5` int(11) DEFAULT NULL,
- UNIQUE KEY `d` (`d`) USING HASH,
- UNIQUE KEY `e` (`e`) USING HASH,
UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
- UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`)
+ UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+ UNIQUE KEY `d` (`d`) USING HASH,
+ UNIQUE KEY `e` (`e`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
-t1 0 d 1 d A NULL NULL NULL YES HASH
-t1 0 e 1 e A NULL NULL NULL YES HASH
t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE
t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE
+t1 0 d 1 d A NULL NULL NULL YES HASH
+t1 0 e 1 e A NULL NULL NULL YES HASH
#add column with unique index on blob;
alter table t1 add column a blob unique;
show create table t1;
@@ -567,18 +567,18 @@ t1 CREATE TABLE `t1` (
`db_row_hash_2` int(11) DEFAULT NULL,
`db_row_hash_5` int(11) DEFAULT NULL,
`a` blob DEFAULT NULL,
- UNIQUE KEY `d` (`d`) USING HASH,
- UNIQUE KEY `e` (`e`) USING HASH,
UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+ UNIQUE KEY `d` (`d`) USING HASH,
+ UNIQUE KEY `e` (`e`) USING HASH,
UNIQUE KEY `a` (`a`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
-t1 0 d 1 d A NULL NULL NULL YES HASH
-t1 0 e 1 e A NULL NULL NULL YES HASH
t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE
t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE
+t1 0 d 1 d A NULL NULL NULL YES HASH
+t1 0 e 1 e A NULL NULL NULL YES HASH
t1 0 a 1 a A NULL NULL NULL YES HASH
#try to change the blob unique column name;
#this will change index to b tree;
@@ -594,19 +594,19 @@ t1 CREATE TABLE `t1` (
`db_row_hash_2` int(11) DEFAULT NULL,
`db_row_hash_5` int(11) DEFAULT NULL,
`a` int(11) DEFAULT NULL,
- UNIQUE KEY `d` (`d`) USING HASH,
- UNIQUE KEY `e` (`e`),
UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
- UNIQUE KEY `a` (`a`)
+ UNIQUE KEY `e` (`e`),
+ UNIQUE KEY `a` (`a`),
+ UNIQUE KEY `d` (`d`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
-t1 0 d 1 d A NULL NULL NULL YES HASH
-t1 0 e 1 e A NULL NULL NULL YES BTREE
t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE
t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE
+t1 0 e 1 e A NULL NULL NULL YES BTREE
t1 0 a 1 a A NULL NULL NULL YES BTREE
+t1 0 d 1 d A NULL NULL NULL YES HASH
alter table t1 add column clm1 blob unique,add column clm2 blob unique;
#try changing the name;
alter table t1 change column clm1 clm_changed1 blob, change column clm2 clm_changed2 blob;
@@ -623,21 +623,21 @@ t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`clm_changed1` blob DEFAULT NULL,
`clm_changed2` blob DEFAULT NULL,
- UNIQUE KEY `d` (`d`) USING HASH,
- UNIQUE KEY `e` (`e`),
UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+ UNIQUE KEY `e` (`e`),
UNIQUE KEY `a` (`a`),
+ UNIQUE KEY `d` (`d`) USING HASH,
UNIQUE KEY `clm1` (`clm_changed1`) USING HASH,
UNIQUE KEY `clm2` (`clm_changed2`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
-t1 0 d 1 d A NULL NULL NULL YES HASH
-t1 0 e 1 e A NULL NULL NULL YES BTREE
t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE
t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE
+t1 0 e 1 e A NULL NULL NULL YES BTREE
t1 0 a 1 a A NULL NULL NULL YES BTREE
+t1 0 d 1 d A NULL NULL NULL YES HASH
t1 0 clm1 1 clm_changed1 A NULL NULL NULL YES HASH
t1 0 clm2 1 clm_changed2 A NULL NULL NULL YES HASH
#now drop the unique key;
@@ -655,19 +655,19 @@ t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`clm_changed1` blob DEFAULT NULL,
`clm_changed2` blob DEFAULT NULL,
- UNIQUE KEY `d` (`d`) USING HASH,
- UNIQUE KEY `e` (`e`),
UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
- UNIQUE KEY `a` (`a`)
+ UNIQUE KEY `e` (`e`),
+ UNIQUE KEY `a` (`a`),
+ UNIQUE KEY `d` (`d`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
-t1 0 d 1 d A NULL NULL NULL YES HASH
-t1 0 e 1 e A NULL NULL NULL YES BTREE
t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE
t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE
+t1 0 e 1 e A NULL NULL NULL YES BTREE
t1 0 a 1 a A NULL NULL NULL YES BTREE
+t1 0 d 1 d A NULL NULL NULL YES HASH
drop table t1;
#now the table with key on multiple columns; the ultimate test;
create table t1(a blob, b int , c varchar(2000) , d text , e varchar(3000) , f longblob , g int , h text ,
@@ -1130,17 +1130,17 @@ t1 CREATE TABLE `t1` (
`c` blob DEFAULT NULL,
`d` blob DEFAULT NULL,
`e` int(11) DEFAULT NULL,
+ UNIQUE KEY `e` (`e`),
UNIQUE KEY `a` (`a`,`c`) USING HASH,
- UNIQUE KEY `b` (`b`,`d`) USING HASH,
- UNIQUE KEY `e` (`e`)
+ UNIQUE KEY `b` (`b`,`d`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
+t1 0 e 1 e A NULL NULL NULL YES BTREE
t1 0 a 1 a A NULL NULL NULL YES HASH
t1 0 a 2 c A NULL NULL NULL YES HASH
t1 0 b 1 b A NULL NULL NULL YES HASH
t1 0 b 2 d A NULL NULL NULL YES HASH
-t1 0 e 1 e A 0 NULL NULL YES BTREE
drop table t1;
#visibility of db_row_hash
create table t1 (a blob unique , b blob unique);
diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result
index 87a57fb4614..48e74bdd564 100644
--- a/mysql-test/main/long_unique_bugs.result
+++ b/mysql-test/main/long_unique_bugs.result
@@ -239,3 +239,49 @@ CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS
INSERT INTO t1 VALUES (2);
REPLACE INTO t1 VALUES (2);
DROP TABLE t1;
+CREATE TABLE t1 (pk INT, a CHAR(4), b BLOB NOT NULL, PRIMARY KEY(pk));
+INSERT INTO t1 VALUES (1,'foo','bar');
+ALTER TABLE t1 ADD KEY (b(64));
+ALTER TABLE t1 ADD UNIQUE (b(165));
+ALTER TABLE t1 ADD KEY (b(1000));
+ALTER TABLE t1 ADD KEY (b(500));
+ALTER TABLE t1 ADD UNIQUE (a,b);
+ALTER TABLE t1 ADD UNIQUE (b(95));
+ALTER TABLE t1 ADD KEY (b(30));
+ALTER TABLE t1 ADD UNIQUE (b(20));
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `pk` int(11) NOT NULL,
+ `a` char(4) DEFAULT NULL,
+ `b` blob NOT NULL,
+ PRIMARY KEY (`pk`),
+ UNIQUE KEY `b_2` (`b`(165)),
+ UNIQUE KEY `b_5` (`b`(95)),
+ UNIQUE KEY `b_7` (`b`(20)),
+ UNIQUE KEY `a` (`a`,`b`) USING HASH,
+ KEY `b` (`b`(64)),
+ KEY `b_3` (`b`(1000)),
+ KEY `b_4` (`b`(500)),
+ KEY `b_6` (`b`(30))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+ALTER TABLE t1 ADD UNIQUE (b);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `pk` int(11) NOT NULL,
+ `a` char(4) DEFAULT NULL,
+ `b` blob NOT NULL,
+ PRIMARY KEY (`pk`),
+ UNIQUE KEY `b_2` (`b`(165)),
+ UNIQUE KEY `b_5` (`b`(95)),
+ UNIQUE KEY `b_7` (`b`(20)),
+ UNIQUE KEY `a` (`a`,`b`) USING HASH,
+ UNIQUE KEY `b_8` (`b`) USING HASH,
+ KEY `b` (`b`(64)),
+ KEY `b_3` (`b`(1000)),
+ KEY `b_4` (`b`(500)),
+ KEY `b_6` (`b`(30))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+ALTER TABLE t1 FORCE;
+DROP TABLE t1;
diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test
index ed0daee426f..11b1c4f09b6 100644
--- a/mysql-test/main/long_unique_bugs.test
+++ b/mysql-test/main/long_unique_bugs.test
@@ -1,4 +1,5 @@
--source include/have_innodb.inc
+--source include/have_partition.inc
#
# MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list
@@ -269,8 +270,27 @@ drop table t1;
#
# MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map
#
---source include/have_partition.inc
CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS 2;
INSERT INTO t1 VALUES (2);
REPLACE INTO t1 VALUES (2);
DROP TABLE t1;
+
+#
+# MDEV-19049 Server crashes in check_duplicate_long_entry_key, ASAN stack-buffer-overflow in Field_blob::get_key_image
+#
+CREATE TABLE t1 (pk INT, a CHAR(4), b BLOB NOT NULL, PRIMARY KEY(pk));
+INSERT INTO t1 VALUES (1,'foo','bar');
+
+ALTER TABLE t1 ADD KEY (b(64));
+ALTER TABLE t1 ADD UNIQUE (b(165));
+ALTER TABLE t1 ADD KEY (b(1000));
+ALTER TABLE t1 ADD KEY (b(500));
+ALTER TABLE t1 ADD UNIQUE (a,b);
+ALTER TABLE t1 ADD UNIQUE (b(95));
+ALTER TABLE t1 ADD KEY (b(30));
+ALTER TABLE t1 ADD UNIQUE (b(20));
+show create table t1;
+ALTER TABLE t1 ADD UNIQUE (b);
+show create table t1;
+ALTER TABLE t1 FORCE;
+DROP TABLE t1;
diff --git a/mysql-test/main/long_unique_innodb.result b/mysql-test/main/long_unique_innodb.result
index cb8c3ea4858..135bb0808cc 100644
--- a/mysql-test/main/long_unique_innodb.result
+++ b/mysql-test/main/long_unique_innodb.result
@@ -9,8 +9,8 @@ Table Create Table
t1 CREATE TABLE `t1` (
`a` blob DEFAULT NULL,
`c` int(11) DEFAULT NULL,
- UNIQUE KEY `a` (`a`) USING HASH,
- UNIQUE KEY `c` (`c`)
+ UNIQUE KEY `c` (`c`),
+ UNIQUE KEY `a` (`a`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
#test for concurrent insert of long unique in innodb
diff --git a/mysql-test/main/long_unique_update.result b/mysql-test/main/long_unique_update.result
index 60a4fb46558..b508583f47c 100644
--- a/mysql-test/main/long_unique_update.result
+++ b/mysql-test/main/long_unique_update.result
@@ -71,8 +71,8 @@ create table t1 (a int primary key, b blob unique , c int unique );
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 a A 0 NULL NULL BTREE
-t1 0 b 1 b A NULL NULL NULL YES HASH
t1 0 c 1 c A NULL NULL NULL YES BTREE
+t1 0 b 1 b A NULL NULL NULL YES HASH
insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7);
select * from t1 limit 3;
a b c
@@ -220,18 +220,18 @@ t1 CREATE TABLE `t1` (
`f` int(11) DEFAULT NULL,
`g` text DEFAULT NULL,
PRIMARY KEY (`a`),
- UNIQUE KEY `b` (`b`,`c`) USING HASH,
UNIQUE KEY `b_2` (`b`,`f`),
+ UNIQUE KEY `b` (`b`,`c`) USING HASH,
UNIQUE KEY `e` (`e`,`g`) USING HASH,
UNIQUE KEY `a` (`a`,`b`,`c`,`d`,`e`,`f`,`g`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 a A 0 NULL NULL BTREE
-t1 0 b 1 b A NULL NULL NULL YES HASH
-t1 0 b 2 c A NULL NULL NULL YES HASH
t1 0 b_2 1 b A NULL NULL NULL YES BTREE
t1 0 b_2 2 f A NULL NULL NULL YES BTREE
+t1 0 b 1 b A NULL NULL NULL YES HASH
+t1 0 b 2 c A NULL NULL NULL YES HASH
t1 0 e 1 e A NULL NULL NULL YES HASH
t1 0 e 2 g A NULL NULL NULL YES HASH
t1 0 a 1 a A NULL NULL NULL HASH
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index ad62ecc1103..c755a74e174 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -2773,6 +2773,7 @@ bool quick_rm_table(THD *thd, handlerton *base, const LEX_CSTRING *db,
- UNIQUE keys where all column are NOT NULL
- UNIQUE keys that don't contain partial segments
- Other UNIQUE keys
+ - LONG UNIQUE keys
- Normal keys
- Fulltext keys
@@ -2796,6 +2797,14 @@ static int sort_keys(KEY *a, KEY *b)
{
if (!(b_flags & HA_NOSAME))
return -1;
+ /*
+ Long Unique keys should always be last unique key.
+ Before this patch they used to change order wrt to partial keys (MDEV-19049)
+ */
+ if (a->algorithm == HA_KEY_ALG_LONG_HASH)
+ return 1;
+ if (b->algorithm == HA_KEY_ALG_LONG_HASH)
+ return -1;
if ((a_flags ^ b_flags) & HA_NULL_PART_KEY)
{
/* Sort NOT NULL keys before other keys */
1
0