[Commits] f51bd6ecfb3: MDEV-16751: Server crashes in st_join_table::cleanup or TABLE_LIST::is_with_table_recursive_reference
by Varun 20 Jul '18
by Varun 20 Jul '18
20 Jul '18
revision-id: f51bd6ecfb33aa1628ffcbc442661ee936da7b0a (mariadb-5.5.60-47-gf51bd6ecfb3)
parent(s): 9cea4ccf12cb6e8746b9b440d9c62408a9ef04af
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-07-20 16:40:46 +0530
message:
MDEV-16751: Server crashes in st_join_table::cleanup or TABLE_LIST::is_with_table_recursive_reference
with join_cache_level>2
During muliple equality propagation for a query in which we have an IN subquery, the items in the select list of the
subquery may not be part of the multiple equality because there might be another occurence of the same field in the
where clause of the subquery.
So we keyuse_is_valid_for_access_in_chosen_plan function which expects the items in the select list of the subquery to
be same to the ones in the multiple equality (through these multiple equalities we create keyuse array).
The solution would be that we expect the same field not the same Item because when we have SEMI JOIN MATERIALIZATION SCAN,
we use copy back technique to copies back the materialised table fields to the original fields of the base tables.
---
mysql-test/r/subselect_mat.result | 35 +++++++++++++++++++++++++++++++++++
mysql-test/r/subselect_sj_mat.result | 35 +++++++++++++++++++++++++++++++++++
mysql-test/t/subselect_sj_mat.test | 25 +++++++++++++++++++++++++
sql/sql_select.cc | 3 ++-
4 files changed, 97 insertions(+), 1 deletion(-)
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 00448ac4f91..a56d076d528 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2360,6 +2360,41 @@ ec70316637232000158bbfc8bcbe5d60
ebb4620037332000158bbfc8bcbe5d89
DROP TABLE t1,t2,t3;
set optimizer_switch=@save_optimizer_switch;
+#
+# MDEV-16751: Server crashes in st_join_table::cleanup or
+# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2
+#
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=4;
+CREATE TABLE t1 ( id int NOT NULL);
+INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
+CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ;
+INSERT INTO t2 VALUES (11,11),(12,12),(13,13);
+explain
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3
+1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+1
+1
+1
+1
+set @@join_cache_level= @save_join_cache_level;
+alter table t1 add key(id);
+explain
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3
+1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+1
+1
+1
+1
+drop table t1,t2;
# 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_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index cb5012a91c9..fa62259e3d6 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2400,4 +2400,39 @@ ec70316637232000158bbfc8bcbe5d60
ebb4620037332000158bbfc8bcbe5d89
DROP TABLE t1,t2,t3;
set optimizer_switch=@save_optimizer_switch;
+#
+# MDEV-16751: Server crashes in st_join_table::cleanup or
+# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2
+#
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=4;
+CREATE TABLE t1 ( id int NOT NULL);
+INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
+CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ;
+INSERT INTO t2 VALUES (11,11),(12,12),(13,13);
+explain
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3
+1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+1
+1
+1
+1
+set @@join_cache_level= @save_join_cache_level;
+alter table t1 add key(id);
+explain
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3
+1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+1
+1
+1
+1
+drop table t1,t2;
# End of 5.5 tests
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 90f63bea561..f1b64337702 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -2151,4 +2151,29 @@ eval $q;
DROP TABLE t1,t2,t3;
set optimizer_switch=@save_optimizer_switch;
+--echo #
+--echo # MDEV-16751: Server crashes in st_join_table::cleanup or
+--echo # TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2
+--echo #
+
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=4;
+CREATE TABLE t1 ( id int NOT NULL);
+INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
+
+CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ;
+INSERT INTO t2 VALUES (11,11),(12,12),(13,13);
+
+explain
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+
+set @@join_cache_level= @save_join_cache_level;
+alter table t1 add key(id);
+
+explain
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
+
+drop table t1,t2;
--echo # End of 5.5 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6a64a0e9952..1aa54b82d5e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7438,7 +7438,8 @@ bool JOIN_TAB::keyuse_is_valid_for_access_in_chosen_plan(JOIN *join,
st_select_lex *sjm_sel= emb_sj_nest->sj_subq_pred->unit->first_select();
for (uint i= 0; i < sjm_sel->item_list.elements; i++)
{
- if (sjm_sel->ref_pointer_array[i] == keyuse->val)
+ DBUG_ASSERT(keyuse->val->type() == Item::FIELD_ITEM);
+ if (((Item_field*)sjm_sel->ref_pointer_array[i])->field == ((Item_field*)keyuse->val)->field)
return true;
}
return false;
1
0
[Commits] 4c3837b: MDEV-16726 Assertion `tab->type == JT_REF || tab->type == JT_EQ_REF' failed
by IgorBabaev 19 Jul '18
by IgorBabaev 19 Jul '18
19 Jul '18
revision-id: 4c3837b2db99befddc0e6bfdd991558367e43e23 (mariadb-5.5.60-45-g4c3837b)
parent(s): 2fbf2277ffec86d69f793534da7043b6dd540780
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-07-19 15:31:30 -0700
message:
MDEV-16726 Assertion `tab->type == JT_REF || tab->type == JT_EQ_REF' failed
Due to a legacy bug in the code of make_join_statistics() detecting
so-called constant tables could miss some of them in rare queries
that used RIGHT JOIN. As a result these queries had execution plans
different from the execution plans of the equivalent queries with
LEFT JOIN.
Besides starting from 10.2 this could trigger an assertion failure.
---
mysql-test/r/join_outer.result | 50 +++++++++++++++++++++++++++++
mysql-test/r/join_outer_jcl6.result | 50 +++++++++++++++++++++++++++++
mysql-test/r/subselect_mat_cost_bugs.result | 2 +-
mysql-test/t/join_outer.test | 48 +++++++++++++++++++++++++++
sql/sql_select.cc | 4 +--
5 files changed, 151 insertions(+), 3 deletions(-)
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 67b22ca..d55f11c 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2368,5 +2368,55 @@ id sid id
1 NULL NULL
2 NULL NULL
drop table t1, t2;
+#
+# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN
+# converted to INNER JOIN with first constant inner table
+#
+CREATE TABLE t1 (
+pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1)
+) engine=MyISAM;
+INSERT INTO t1 VALUES
+(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'),
+(14,226,'m','m'),(15,133,'p','p');
+CREATE TABLE t2 (
+pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1)
+) engine=MyISAM;
+INSERT INTO t2 VALUES (10,6,'p','p');
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+RIGHT JOIN
+(t2,t1)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+Warnings:
+Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+(t2,t1)
+LEFT JOIN
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+Warnings:
+Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL
+SELECT STRAIGHT_JOIN DISTINCT t2.v2
+FROM
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+RIGHT JOIN
+(t2,t1)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+v2
+DROP TABLE t1,t2;
# end of 5.5 tests
SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index c019da6..8a9b395 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -2379,6 +2379,56 @@ id sid id
1 NULL NULL
2 NULL NULL
drop table t1, t2;
+#
+# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN
+# converted to INNER JOIN with first constant inner table
+#
+CREATE TABLE t1 (
+pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1)
+) engine=MyISAM;
+INSERT INTO t1 VALUES
+(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'),
+(14,226,'m','m'),(15,133,'p','p');
+CREATE TABLE t2 (
+pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1)
+) engine=MyISAM;
+INSERT INTO t2 VALUES (10,6,'p','p');
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+RIGHT JOIN
+(t2,t1)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+Warnings:
+Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+(t2,t1)
+LEFT JOIN
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+Warnings:
+Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL
+SELECT STRAIGHT_JOIN DISTINCT t2.v2
+FROM
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+RIGHT JOIN
+(t2,t1)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+v2
+DROP TABLE t1,t2;
# end of 5.5 tests
SET optimizer_switch=@save_optimizer_switch;
set join_cache_level=default;
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index d33f148..03f4b3e 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -442,7 +442,7 @@ SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1
-2 DEPENDENT SUBQUERY t2 index NULL i2 11 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index i2 i2 11 NULL 2 Using where; Using index
DROP TABLE t1,t2,t3;
#
# MDEV-7599: in-to-exists chosen after min/max optimization
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 2769aea..305421c 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1911,6 +1911,54 @@ select * from t1 t
on t.id=r.id ;
drop table t1, t2;
+--echo #
+--echo # MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN
+--echo # converted to INNER JOIN with first constant inner table
+--echo #
+
+CREATE TABLE t1 (
+ pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1)
+) engine=MyISAM;
+INSERT INTO t1 VALUES
+ (8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'),
+ (14,226,'m','m'),(15,133,'p','p');
+
+CREATE TABLE t2 (
+ pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1)
+) engine=MyISAM;
+INSERT INTO t2 VALUES (10,6,'p','p');
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+ (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+ RIGHT JOIN
+ (t2,t1)
+ ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+ (t2,t1)
+ LEFT JOIN
+ (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+ ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+
+SELECT STRAIGHT_JOIN DISTINCT t2.v2
+FROM
+ (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+ RIGHT JOIN
+ (t2,t1)
+ ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+
+DROP TABLE t1,t2;
+
--echo # end of 5.5 tests
SET optimizer_switch=@save_optimizer_switch;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 700b7b3..6a64a0e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3456,8 +3456,8 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
int ref_changed;
do
{
- more_const_tables_found:
ref_changed = 0;
+ more_const_tables_found:
found_ref=0;
/*
@@ -3622,7 +3622,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
}
}
}
- } while (join->const_table_map & found_ref && ref_changed);
+ } while (ref_changed);
join->sort_by_table= get_sort_by_table(join->order, join->group_list,
join->select_lex->leaf_tables,
1
0
revision-id: f99fe68b4fb7ca1715d059d283e50f050f692294 (mariadb-galera-10.0.35-12-gf99fe68b4fb)
parent(s): c09d54924a46df13daf68f61caf906b6fecefca1
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-07-19 21:05:36 +0300
message:
Fix compile error.
---
sql/sql_trigger.cc | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 34875824e2e..784464110cf 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -508,7 +508,9 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
if (err_status)
goto end;
}
- WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL);
+
+ if (thd->wsrep_exec_mode == LOCAL_STATE)
+ WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL);
/* We should have only one table in table list. */
DBUG_ASSERT(tables->next_global == 0);
1
0
[Commits] 86b89b08368: MDEV-10564: Galera `wsrep_debug` patch logs MySQL user credentials
by jan 19 Jul '18
by jan 19 Jul '18
19 Jul '18
revision-id: 86b89b0836817ef18cd46489f3165c11d1b750d8 (mariadb-10.1.34-26-g86b89b08368)
parent(s): 4d06b7e1bd3b825da32c9200e6f5ca609add0e13
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-07-19 15:13:31 +0300
message:
MDEV-10564: Galera `wsrep_debug` patch logs MySQL user credentials
Restricted output for CREATE USER, GRANT, REVOKE and SET PASSWORD
so that it shows only above keywords but not rest of query i.e.
not user or password.
---
sql/wsrep_mysqld.cc | 26 +++++++++++++++++++++++---
1 file changed, 23 insertions(+), 3 deletions(-)
diff --git a/sql/wsrep_mysqld.cc b/sql/wsrep_mysqld.cc
index b127d3a8f00..a88713652ed 100644
--- a/sql/wsrep_mysqld.cc
+++ b/sql/wsrep_mysqld.cc
@@ -1450,6 +1450,25 @@ static bool wsrep_can_run_in_toi(THD *thd, const char *db, const char *table,
}
}
+static const char* wsrep_get_query_or_msg(const THD* thd)
+{
+ switch(thd->lex->sql_command)
+ {
+ case SQLCOM_CREATE_USER:
+ return "CREATE USER";
+ case SQLCOM_GRANT:
+ return "GRANT";
+ case SQLCOM_REVOKE:
+ return "REVOKE";
+ case SQLCOM_SET_OPTION:
+ if (thd->lex->definer)
+ return "SET PASSWORD";
+ /* fallthrough */
+ default:
+ return thd->query();
+ }
+}
+
/*
returns:
0: statement was replicated as TOI
@@ -1472,7 +1491,8 @@ static int wsrep_TOI_begin(THD *thd, char *db_, char *table_,
}
WSREP_DEBUG("TO BEGIN: %lld, %d : %s", (long long)wsrep_thd_trx_seqno(thd),
- thd->wsrep_exec_mode, thd->query() );
+ thd->wsrep_exec_mode, wsrep_get_query_or_msg(thd));
+
switch (thd->lex->sql_command)
{
case SQLCOM_CREATE_VIEW:
@@ -1546,8 +1566,8 @@ static void wsrep_TOI_end(THD *thd) {
wsrep_status_t ret;
wsrep_to_isolation--;
- WSREP_DEBUG("TO END: %lld, %d : %s", (long long)wsrep_thd_trx_seqno(thd),
- thd->wsrep_exec_mode, (thd->query()) ? thd->query() : "void");
+ WSREP_DEBUG("TO END: %lld, %d: %s", (long long)wsrep_thd_trx_seqno(thd),
+ thd->wsrep_exec_mode, wsrep_get_query_or_msg(thd));
wsrep_set_SE_checkpoint(thd->wsrep_trx_meta.gtid.uuid,
thd->wsrep_trx_meta.gtid.seqno);
1
0
19 Jul '18
revision-id: 0896d7ebc3698f898bd66544bbc0ba6e4a4fbe0e (mariadb-10.1.34-25-g0896d7ebc36)
parent(s): 312de43f40e221096b5565f6f4999eaadae09ef4 ada54101a7185782657813c553907f61f2a35faf
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-07-19 12:55:54 +0200
message:
Merge branch '10.0' into bb-10.1-merge
cmake/os/Windows.cmake | 2 +-
mysql-test/r/derived.result | 33 ++++++++++++
mysql-test/r/join.result | 40 +++++++++++++++
mysql-test/r/join_cache.result | 33 ++++++++++++
mysql-test/r/stat_tables.result | 60 ++++++++++++++++++++++
mysql-test/r/stat_tables_innodb.result | 60 ++++++++++++++++++++++
mysql-test/r/subselect_sj2_mat.result | 51 ++++++++++++++++++
.../suite/binlog/include/check_binlog_size.inc | 31 +++++++++++
.../suite/binlog/r/binlog_tmp_table_row.result | 7 +++
.../suite/binlog/t/binlog_tmp_table_row.test | 30 +++++++++++
mysql-test/suite/vcol/t/vcol_misc.test | 2 +
mysql-test/t/derived.test | 21 ++++++++
mysql-test/t/join.test | 53 +++++++++++++++++++
mysql-test/t/join_cache.test | 32 +++++++++++-
mysql-test/t/stat_tables.test | 50 ++++++++++++++++++
mysql-test/t/subselect_sj2_mat.test | 49 ++++++++++++++++++
mysql-test/valgrind.supp | 13 +++++
scripts/mysql_install_db.sh | 5 ++
sql/log.cc | 11 ++--
sql/mysqld.cc | 7 ++-
sql/opt_subselect.cc | 3 +-
sql/sql_base.cc | 12 +++--
sql/sql_parse.cc | 3 ++
sql/sql_select.cc | 4 +-
sql/table.cc | 19 +++++--
storage/xtradb/os/os0file.cc | 41 ++++++++++++++-
26 files changed, 649 insertions(+), 23 deletions(-)
diff --cc mysql-test/r/derived.result
index 687497ceb7e,889518343c0..6f786e34a9a
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@@ -1077,49 -1079,36 +1078,81 @@@
drop procedure pr;
drop view v1;
drop table t1;
+ set @@join_cache_level= @save_join_cache_level;
+ #
+ # MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views
+ #
+ CREATE TABLE t1 (c1 text, c2 int);
+ INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3);
+ CREATE TABLE t2 (c1 text, c2 int);
+ INSERT INTO t2 VALUES ('b',2), ('c',3);
+ CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+ explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ id select_type table type possible_keys key key_len ref rows Extra
+ 1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+ 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
+ 2 DERIVED t1 ALL NULL NULL NULL NULL 5
+ SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ c1 c2 c1 c2
+ c 3 c 3
+ c 3 c 3
+ set @save_join_cache_level= @@join_cache_level;
+ set @@join_cache_level=4;
+ explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ id select_type table type possible_keys key key_len ref rows Extra
+ 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+ 1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 3 test.t2.c1 5 Using where; Using join buffer (flat, BNLH join)
+ 2 DERIVED t1 ALL NULL NULL NULL NULL 5
+ SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ c1 c2 c1 c2
+ c 3 c 3
+ c 3 c 3
+ drop table t1,t2;
+ drop view v1;
+ set @@join_cache_level= @save_join_cache_level;
# end of 5.5
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin
+#
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('a'),('A');
+SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+a
+a
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+a
+a
+DROP TABLE t1;
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+a
+5
+DROP TABLE t1;
+#
+# MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2
+#
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+a
+5
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --cc mysql-test/r/stat_tables.result
index 279c09ff44f,c1457d5e91a..ceadb61feea
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@@ -516,73 -516,64 +516,133 @@@ use test
drop database db1;
drop database db2;
drop table t1;
+ #
+ # MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
+ #
+ SET use_stat_tables = PREFERABLY;
+ SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
+ CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' )
+ NULL
+ set use_stat_tables=@save_use_stat_tables;
+ #
+ # MDEV-16757: manual addition of min/max statistics for BLOB
+ #
+ SET use_stat_tables= PREFERABLY;
+ CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ Table Op Msg_type Msg_text
+ test.t1 analyze status Engine-independent statistics collected
++test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+ test.t1 analyze status OK
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t NULL NULL 0.0000 3.0000 NULL NULL NULL NULL
+ DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+ INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+ test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+ SELECT pk FROM t1;
+ pk
+ 1
+ 2
+ DROP TABLE t1;
+ set use_stat_tables=@save_use_stat_tables;
+ #
+ # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+ #
+ SET use_stat_tables= PREFERABLY;
+ CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ Table Op Msg_type Msg_text
+ test.t1 analyze status Engine-independent statistics collected
+ test.t1 analyze status OK
+ SELECT * FROM t1;
+ pk c
+ 1 foo
+ 2 bar
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+ test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+ CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+ SELECT * FROM t1;
+ pk a
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+test.t1 analyze status OK
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1;
+pk c
+1 foo
+2 bar
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk a
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
diff --cc mysql-test/r/stat_tables_innodb.result
index 1604c413743,2ac868e9341..c5e7309861c
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@@ -543,75 -543,66 +543,135 @@@ use test
drop database db1;
drop database db2;
drop table t1;
+ #
+ # MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
+ #
+ SET use_stat_tables = PREFERABLY;
+ SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
+ CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' )
+ NULL
+ set use_stat_tables=@save_use_stat_tables;
+ #
+ # MDEV-16757: manual addition of min/max statistics for BLOB
+ #
+ SET use_stat_tables= PREFERABLY;
+ CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ Table Op Msg_type Msg_text
+ test.t1 analyze status Engine-independent statistics collected
++test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+ test.t1 analyze status OK
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t NULL NULL 0.0000 3.0000 NULL NULL NULL NULL
+ DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+ INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+ test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+ SELECT pk FROM t1;
+ pk
+ 1
+ 2
+ DROP TABLE t1;
+ set use_stat_tables=@save_use_stat_tables;
+ #
+ # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+ #
+ SET use_stat_tables= PREFERABLY;
+ CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ Table Op Msg_type Msg_text
+ test.t1 analyze status Engine-independent statistics collected
+ test.t1 analyze status OK
+ SELECT * FROM t1;
+ pk c
+ 1 foo
+ 2 bar
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+ test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+ CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+ SELECT * FROM t1;
+ pk a
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+test.t1 analyze status OK
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1;
+pk c
+1 foo
+2 bar
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk a
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --cc mysql-test/t/derived.test
index b0415cf3b04,eeaf94adcee..778d141b80f
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@@ -935,39 -936,25 +936,59 @@@ call pr(2)
drop procedure pr;
drop view v1;
drop table t1;
+ set @@join_cache_level= @save_join_cache_level;
+ --echo #
+ --echo # MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views
+ --echo #
+
+ CREATE TABLE t1 (c1 text, c2 int);
+ INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3);
+ CREATE TABLE t2 (c1 text, c2 int);
+ INSERT INTO t2 VALUES ('b',2), ('c',3);
+ CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+
+ explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ set @save_join_cache_level= @@join_cache_level;
+ set @@join_cache_level=4;
+ explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ drop table t1,t2;
+ drop view v1;
+ set @@join_cache_level= @save_join_cache_level;
--echo # end of 5.5
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin
+--echo #
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('a'),('A');
+SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2
+--echo #
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --cc mysql-test/t/stat_tables.test
index 51c9995b6fa,d69b00618ea..2c9c1eca7d3
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@@ -305,66 -305,54 +305,116 @@@ drop database db1
drop database db2;
drop table t1;
+ --echo #
+ --echo # MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
+ --echo #
+
+ SET use_stat_tables = PREFERABLY;
+ SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
+ set use_stat_tables=@save_use_stat_tables;
+
+ --echo #
+ --echo # MDEV-16757: manual addition of min/max statistics for BLOB
+ --echo #
+
+ SET use_stat_tables= PREFERABLY;
+
+ CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ --sorted_result
+ SELECT * FROM mysql.column_stats;
+ DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+ INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+ --sorted_result
+ SELECT * FROM mysql.column_stats;
+
+ SELECT pk FROM t1;
+
+ DROP TABLE t1;
+
+ set use_stat_tables=@save_use_stat_tables;
+
+ --echo #
+ --echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+ --echo #
+
+ SET use_stat_tables= PREFERABLY;
+
+ CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ SELECT * FROM t1;
+ SELECT * FROM mysql.column_stats;
+
+ CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+ SELECT * FROM t1;
+ SELECT * FROM mysql.column_stats;
+
+ DROP TABLE t1;
+
set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # MDEV-16757: manual addition of min/max statistics for BLOB
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+--sorted_result
+SELECT * FROM mysql.column_stats;
+
+SELECT pk FROM t1;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+SELECT * FROM t1;
+SELECT * FROM mysql.column_stats;
+
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+SELECT * FROM mysql.column_stats;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
+
+
+--echo #
+--echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+
+SELECT MAX(pk) FROM t1;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
diff --cc sql/mysqld.cc
index 5f954f7576d,75d6cca7fda..0f5147c8cf1
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@@ -5889,14 -5497,10 +5894,14 @@@ int mysqld_main(int argc, char **argv
(char*) "" : mysqld_unix_port),
mysqld_port,
MYSQL_COMPILATION_COMMENT);
+
#ifndef _WIN32
- /* Only close stdin if it was open initinally. */
- if (please_close_stdin)
+ // try to keep fd=0 busy
- if (!freopen("/dev/null", "r", stdin))
++ if (please_close_stdin && !freopen("/dev/null", "r", stdin))
+ {
+ // fall back on failure
fclose(stdin);
+ }
#endif
#if defined(_WIN32) && !defined(EMBEDDED_LIBRARY)
diff --cc sql/sql_base.cc
index c2bf37a1064,1ed57a051d8..2a9b409dff5
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@@ -7451,7 -7405,7 +7452,7 @@@ store_natural_using_join_columns(THD *t
nj_col_1= it_1.get_natural_column_ref();
if (nj_col_1->is_common)
{
- natural_using_join->join_columns->push_back(nj_col_1, thd->mem_root);
- join_columns->push_back(nj_col_1);
++ join_columns->push_back(nj_col_1, thd->mem_root);
/* Reset the common columns for the next call to mark_common_columns. */
nj_col_1->is_common= FALSE;
}
@@@ -7505,7 -7459,8 +7506,8 @@@
}
if (non_join_columns->elements > 0)
- natural_using_join->join_columns->append(non_join_columns);
- join_columns->concat(non_join_columns);
++ join_columns->append(non_join_columns);
+ natural_using_join->join_columns= join_columns;
natural_using_join->is_join_columns_complete= TRUE;
result= FALSE;
1
0
[Commits] 98dd5324b6d: MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
by Varun 19 Jul '18
by Varun 19 Jul '18
19 Jul '18
revision-id: 98dd5324b6d6205549a4dd9b2f8358737f86fd23 (mariadb-5.5.60-44-g98dd5324b6d)
parent(s): 6bc722b85308709b1df647d97ee83247061c3aa2
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-07-19 15:47:32 +0530
message:
MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
So to push index condition for each join tab we have calculate the index condition that can be pushed and then
remove this index condition from the original condition. This is done through the function make_cond_remainder.
The problem is the function make_cond_remainder does not remove index condition when there is an OR operator.
Fixed this by making the function make_cond_remainder to keep in mind of the OR operator.
Also updated results for multipe test files which were incorrectly updated by the commit e0c1b3f24246d22e6785315f9a8448bd9a590422
code which was supposed to remove the conditon present in the index
condition was not getting executed when the condition had OR operator, with AND the pushed index conditon was getting removed from where.
---
mysql-test/r/innodb_icp.result | 2 +-
mysql-test/r/join_cache.result | 2 +-
mysql-test/r/mrr_icp_extra.result | 6 +++---
mysql-test/r/myisam_icp.result | 23 +++++++++++++++++++++--
mysql-test/r/myisam_mrr.result | 4 ++--
mysql-test/r/order_by.result | 4 ++--
mysql-test/r/range.result | 8 ++++----
mysql-test/r/range_mrr_icp.result | 8 ++++----
mysql-test/r/range_vs_index_merge.result | 10 +++++-----
mysql-test/r/range_vs_index_merge_innodb.result | 8 ++++----
mysql-test/r/select.result | 2 +-
mysql-test/r/select_jcl6.result | 2 +-
mysql-test/r/select_pkeycache.result | 2 +-
mysql-test/r/subselect_mat_cost.result | 4 ++--
mysql-test/r/xtradb_mrr.result | 4 ++--
mysql-test/t/myisam_icp.test | 15 +++++++++++++++
sql/opt_index_cond_pushdown.cc | 13 +++++--------
17 files changed, 74 insertions(+), 43 deletions(-)
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 9ca02595939..0b282b21ae0 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -649,7 +649,7 @@ SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort
SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index f1e6fb577c8..2cd9d6311fb 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -4985,7 +4985,7 @@ EXPLAIN
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
diff --git a/mysql-test/r/mrr_icp_extra.result b/mysql-test/r/mrr_icp_extra.result
index f7adcfb19fd..b5d712c1def 100644
--- a/mysql-test/r/mrr_icp_extra.result
+++ b/mysql-test/r/mrr_icp_extra.result
@@ -72,7 +72,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (2, 11), (1, 11), (4, 14), (3, 14), (6, 12), (5, 12);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -81,7 +81,7 @@ a b
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
@@ -123,7 +123,7 @@ Table Op Msg_type Msg_text
test.t1 optimize status OK
explain select * from t1 force index (a) where a=0 or a=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Rowid-ordered scan
select * from t1 force index (a) where a=0 or a=2;
a b c
0 NULL 0
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 9b31bca7932..1ecd936c971 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -511,7 +511,7 @@ SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
@@ -653,7 +653,7 @@ SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort
SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
@@ -976,4 +976,23 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 10 NULL 10 Using where
drop table t0, t1;
+#
+# MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
+#
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+create table t1 (key1 int not null, filler char(100));
+insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
+alter table t1 add key(key1);
+explain select * from t1 where key1 < 3 or key1 > 99999;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range key1 key1 4 NULL 20 Using index condition; Rowid-ordered scan
+select * from t1 where key1 < 3 or key1 > 99999;
+key1 filler
+0 filler-data
+1 filler-data
+2 filler-data
+drop table ten,one_k,t1;
set optimizer_switch=@myisam_icp_tmp;
diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result
index bd50df7c40e..12954718973 100644
--- a/mysql-test/r/myisam_mrr.result
+++ b/mysql-test/r/myisam_mrr.result
@@ -188,7 +188,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
@@ -210,7 +210,7 @@ NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Rowid-ordered scan
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index d3f5cd89eee..8b0241d3f62 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -654,7 +654,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -663,7 +663,7 @@ a b
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 630a692cef6..84a074d614b 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -930,7 +930,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 10 Using index condition
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where
+1 SIMPLE t1 range status status 23 NULL 10 Using index condition
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1034,13 +1034,13 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 13 NULL # Using index condition
explain select * from t1 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where
+1 SIMPLE t1 range a a 13 NULL # Using index condition
explain select * from t2 where a between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 13 const # Using index condition
explain select * from t2 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 13 const # Using index condition; Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
update t1 set a='b' where a<>'a';
explain select * from t1 where a not between 'b' and 'b';
id select_type table type possible_keys key key_len ref rows Extra
@@ -1972,7 +1972,7 @@ INSERT INTO t100(I,J) VALUES(8,26);
EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where
+1 SIMPLE t100 range I I 10 NULL 4 Using index condition
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index 3f5de5b0189..8b17afd2c51 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -932,7 +932,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Rowid-ordered scan
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Rowid-ordered scan
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1036,13 +1036,13 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan
explain select * from t1 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan
explain select * from t2 where a between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 13 const # Using index condition
explain select * from t2 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 13 const # Using index condition; Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
update t1 set a='b' where a<>'a';
explain select * from t1 where a not between 'b' and 'b';
id select_type table type possible_keys key key_len ref rows Extra
@@ -1974,7 +1974,7 @@ INSERT INTO t100(I,J) VALUES(8,26);
EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Rowid-ordered scan
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index 0acaed37d22..649346b3ee3 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -327,11 +327,11 @@ ID Name Country Population
EXPLAIN
SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 Using index condition
EXPLAIN
SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 Using index condition
EXPLAIN
SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500);
id select_type table type possible_keys key key_len ref rows Extra
@@ -343,11 +343,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 222 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 222 Using index condition
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 72 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 72 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -1163,7 +1163,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 29 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 29 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index ff4940281ce..f0af0fded0c 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -344,11 +344,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 394 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 394 Using index condition
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 133 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 133 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -1077,7 +1077,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
@@ -1164,7 +1164,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index cff5caf7b7a..781c26a4f17 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3422,7 +3422,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 359e7c8e2e7..f40354bbf14 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -3433,7 +3433,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index cff5caf7b7a..781c26a4f17 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -3422,7 +3422,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result
index 081196a227b..b7b25a001d9 100644
--- a/mysql-test/r/subselect_mat_cost.result
+++ b/mysql-test/r/subselect_mat_cost.result
@@ -285,7 +285,7 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English
AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
AND Code = Country;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 45 Using index condition; Using where; Rowid-ordered scan
+1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 45 Using index condition; Rowid-ordered scan
1 PRIMARY Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
2 MATERIALIZED CountryLanguage ref PRIMARY,Language Language 30 const 47 Using index condition
SELECT Country.Name
@@ -348,7 +348,7 @@ FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000))
AND Language IN ('English','Spanish');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY CountryLanguage range Language Language 30 NULL 72 Using index condition; Using where; Rowid-ordered scan
-2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition; Using where
+2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition
2 DEPENDENT SUBQUERY Country eq_ref PRIMARY PRIMARY 3 world.City.Country 1 Using where; Using index
select count(*)
from CountryLanguage
diff --git a/mysql-test/r/xtradb_mrr.result b/mysql-test/r/xtradb_mrr.result
index c238d0530af..e679606c2ca 100644
--- a/mysql-test/r/xtradb_mrr.result
+++ b/mysql-test/r/xtradb_mrr.result
@@ -186,7 +186,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
@@ -208,7 +208,7 @@ NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Rowid-ordered scan
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test
index 508c282b1dc..b6d35968b1c 100644
--- a/mysql-test/t/myisam_icp.test
+++ b/mysql-test/t/myisam_icp.test
@@ -282,5 +282,20 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc;
drop table t0, t1;
+--echo #
+--echo # MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
+--echo #
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+create table t1 (key1 int not null, filler char(100));
+insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
+alter table t1 add key(key1);
+explain select * from t1 where key1 < 3 or key1 > 99999;
+select * from t1 where key1 < 3 or key1 > 99999;
+drop table ten,one_k,t1;
+
set optimizer_switch=@myisam_icp_tmp;
diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc
index df9dae8e442..35093dee235 100644
--- a/sql/opt_index_cond_pushdown.cc
+++ b/sql/opt_index_cond_pushdown.cc
@@ -263,6 +263,10 @@ Item *make_cond_for_index(Item *cond, TABLE *table, uint keyno,
Item *make_cond_remainder(Item *cond, TABLE *table, uint keyno,
bool other_tbls_ok, bool exclude_index)
{
+ if (exclude_index &&
+ uses_index_fields_only(cond, table, keyno, other_tbls_ok))
+ return NULL;
+
if (cond->type() == Item::COND_ITEM)
{
table_map tbl_map= 0;
@@ -317,14 +321,7 @@ Item *make_cond_remainder(Item *cond, TABLE *table, uint keyno,
return new_cond;
}
}
- else
- {
- if (exclude_index &&
- uses_index_fields_only(cond, table, keyno, other_tbls_ok))
- return 0;
- else
- return cond;
- }
+ return cond;
}
1
0
[Commits] 09f1476: MDEV-16777: galera.galera_gra_log fails with File ...GRA_*.log not found error
by psergey＠askmonty.org 19 Jul '18
by psergey＠askmonty.org 19 Jul '18
19 Jul '18
revision-id: 09f147659f36a30d7075885d88392c9a41244505
parent(s): 4d06b7e1bd3b825da32c9200e6f5ca609add0e13
committer: Sergei Petrunia
branch nick: 10.1-r2
timestamp: 2018-07-19 12:07:07 +0300
message:
MDEV-16777: galera.galera_gra_log fails with File ...GRA_*.log not found error
snprintf returns the number of bytes it wrote (or would have written) NOT
counting the \0 terminal character.
The buffer size it accepts as argument DOES COUNT the \0 character.
Pass the right parameter value.
---
sql/wsrep_binlog.cc | 14 +++++++++++---
1 file changed, 11 insertions(+), 3 deletions(-)
diff --git a/sql/wsrep_binlog.cc b/sql/wsrep_binlog.cc
index 902190d..c7674cd 100644
--- a/sql/wsrep_binlog.cc
+++ b/sql/wsrep_binlog.cc
@@ -326,11 +326,16 @@ void wsrep_dump_rbr_buf(THD *thd, const void* rbr_buf, size_t buf_len)
WSREP_ERROR("snprintf error: %d, skipping dump.", len);
return;
}
+ /*
+ len doesn't count the \0 end-of-string. Use len+1 below
+ to alloc and pass as an argument to snprintf.
+ */
char *filename= (char *)malloc(len+1);
- int len1= snprintf(filename, len, "%s/GRA_%ld_%lld.log",
+ int len1= snprintf(filename, len+1, "%s/GRA_%ld_%lld.log",
wsrep_data_home_dir, thd->thread_id,
(long long)wsrep_thd_trx_seqno(thd));
+
if (len > len1)
{
WSREP_ERROR("RBR dump path truncated: %d, skipping dump.", len);
@@ -469,7 +474,10 @@ void wsrep_dump_rbr_buf_with_header(THD *thd, const void *rbr_buf,
int len= snprintf(NULL, 0, "%s/GRA_%ld_%lld_v2.log",
wsrep_data_home_dir, thd->thread_id,
thd_trx_seqno);
-
+ /*
+ len doesn't count the \0 end-of-string. Use len+1 below
+ to alloc and pass as an argument to snprintf.
+ */
char *filename;
if (len < 0 || !(filename= (char*)malloc(len+1)))
{
@@ -477,7 +485,7 @@ void wsrep_dump_rbr_buf_with_header(THD *thd, const void *rbr_buf,
DBUG_VOID_RETURN;
}
- int len1= snprintf(filename, len, "%s/GRA_%ld_%lld_v2.log",
+ int len1= snprintf(filename, len+1, "%s/GRA_%ld_%lld_v2.log",
wsrep_data_home_dir, thd->thread_id,
thd_trx_seqno);
1
0
[Commits] c8499c0c580: MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
by Varun 19 Jul '18
by Varun 19 Jul '18
19 Jul '18
revision-id: c8499c0c580cbf3722770f9766af0bc47ac045a3 (mariadb-5.5.60-44-gc8499c0c580)
parent(s): 6bc722b85308709b1df647d97ee83247061c3aa2
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-07-19 14:22:30 +0530
message:
MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
So to push index condition for each join tab we have calculate the index condition that can be pushed and then
remove this index condition from the original condition. This is done through the function make_cond_remainder.
The problem is the function make_cond_remainder does not remove index condition when there is an OR operator.
Fixed this by making the function make_cond_remainder to keep in mind of the OR operator.
Also updated results for multipe test files which were incorrectly updated by the commit e0c1b3f24246d22e6785315f9a8448bd9a590422
code which was supposed to remove the conditon present in the index
condition was not getting executed when the condition had OR operator, with AND the pushed index conditon was getting removed from where.
---
mysql-test/r/innodb_icp.result | 2 +-
mysql-test/r/join_cache.result | 2 +-
mysql-test/r/mrr_icp_extra.result | 6 +++---
mysql-test/r/myisam_icp.result | 26 +++++++++++++++++++++++--
mysql-test/r/myisam_mrr.result | 4 ++--
mysql-test/r/order_by.result | 4 ++--
mysql-test/r/range.result | 8 ++++----
mysql-test/r/range_mrr_icp.result | 8 ++++----
mysql-test/r/range_vs_index_merge.result | 10 +++++-----
mysql-test/r/range_vs_index_merge_innodb.result | 8 ++++----
mysql-test/r/select.result | 2 +-
mysql-test/r/select_jcl6.result | 2 +-
mysql-test/r/select_pkeycache.result | 2 +-
mysql-test/r/subselect_mat_cost.result | 4 ++--
mysql-test/r/xtradb_mrr.result | 4 ++--
mysql-test/t/myisam_icp.test | 15 ++++++++++++++
sql/opt_index_cond_pushdown.cc | 13 +++++--------
17 files changed, 77 insertions(+), 43 deletions(-)
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 9ca02595939..0b282b21ae0 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -649,7 +649,7 @@ SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort
SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index f1e6fb577c8..2cd9d6311fb 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -4985,7 +4985,7 @@ EXPLAIN
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
diff --git a/mysql-test/r/mrr_icp_extra.result b/mysql-test/r/mrr_icp_extra.result
index f7adcfb19fd..b5d712c1def 100644
--- a/mysql-test/r/mrr_icp_extra.result
+++ b/mysql-test/r/mrr_icp_extra.result
@@ -72,7 +72,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (2, 11), (1, 11), (4, 14), (3, 14), (6, 12), (5, 12);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -81,7 +81,7 @@ a b
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
@@ -123,7 +123,7 @@ Table Op Msg_type Msg_text
test.t1 optimize status OK
explain select * from t1 force index (a) where a=0 or a=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Rowid-ordered scan
select * from t1 force index (a) where a=0 or a=2;
a b c
0 NULL 0
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 9b31bca7932..2a3e9c9e0ba 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -511,7 +511,7 @@ SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
@@ -653,7 +653,7 @@ SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort
SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
@@ -976,4 +976,26 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 10 NULL 10 Using where
drop table t0, t1;
+#
+# MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
+#
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+create table t1 (key1 int not null, filler char(100)) engine=rocksdb;
+Warnings:
+Warning 1286 Unknown storage engine 'rocksdb'
+Warning 1266 Using storage engine MyISAM for table 't1'
+insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
+alter table t1 add key(key1);
+explain select * from t1 where key1 < 3 or key1 > 99999;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range key1 key1 4 NULL 20 Using index condition; Rowid-ordered scan
+select * from t1 where key1 < 3 or key1 > 99999;
+key1 filler
+0 filler-data
+1 filler-data
+2 filler-data
+drop table ten,one_k,t1;
set optimizer_switch=@myisam_icp_tmp;
diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result
index bd50df7c40e..12954718973 100644
--- a/mysql-test/r/myisam_mrr.result
+++ b/mysql-test/r/myisam_mrr.result
@@ -188,7 +188,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
@@ -210,7 +210,7 @@ NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Rowid-ordered scan
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index d3f5cd89eee..8b0241d3f62 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -654,7 +654,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -663,7 +663,7 @@ a b
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 630a692cef6..84a074d614b 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -930,7 +930,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 10 Using index condition
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where
+1 SIMPLE t1 range status status 23 NULL 10 Using index condition
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1034,13 +1034,13 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 13 NULL # Using index condition
explain select * from t1 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where
+1 SIMPLE t1 range a a 13 NULL # Using index condition
explain select * from t2 where a between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 13 const # Using index condition
explain select * from t2 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 13 const # Using index condition; Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
update t1 set a='b' where a<>'a';
explain select * from t1 where a not between 'b' and 'b';
id select_type table type possible_keys key key_len ref rows Extra
@@ -1972,7 +1972,7 @@ INSERT INTO t100(I,J) VALUES(8,26);
EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where
+1 SIMPLE t100 range I I 10 NULL 4 Using index condition
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index 3f5de5b0189..8b17afd2c51 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -932,7 +932,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Rowid-ordered scan
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Rowid-ordered scan
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1036,13 +1036,13 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan
explain select * from t1 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan
explain select * from t2 where a between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 13 const # Using index condition
explain select * from t2 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 13 const # Using index condition; Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
update t1 set a='b' where a<>'a';
explain select * from t1 where a not between 'b' and 'b';
id select_type table type possible_keys key key_len ref rows Extra
@@ -1974,7 +1974,7 @@ INSERT INTO t100(I,J) VALUES(8,26);
EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Rowid-ordered scan
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index 0acaed37d22..649346b3ee3 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -327,11 +327,11 @@ ID Name Country Population
EXPLAIN
SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 Using index condition
EXPLAIN
SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 Using index condition
EXPLAIN
SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500);
id select_type table type possible_keys key key_len ref rows Extra
@@ -343,11 +343,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 222 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 222 Using index condition
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 72 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 72 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -1163,7 +1163,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 29 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 29 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index ff4940281ce..f0af0fded0c 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -344,11 +344,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 394 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 394 Using index condition
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 133 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 133 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -1077,7 +1077,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
@@ -1164,7 +1164,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index cff5caf7b7a..781c26a4f17 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3422,7 +3422,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 359e7c8e2e7..f40354bbf14 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -3433,7 +3433,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index cff5caf7b7a..781c26a4f17 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -3422,7 +3422,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result
index 081196a227b..b7b25a001d9 100644
--- a/mysql-test/r/subselect_mat_cost.result
+++ b/mysql-test/r/subselect_mat_cost.result
@@ -285,7 +285,7 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English
AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
AND Code = Country;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 45 Using index condition; Using where; Rowid-ordered scan
+1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 45 Using index condition; Rowid-ordered scan
1 PRIMARY Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
2 MATERIALIZED CountryLanguage ref PRIMARY,Language Language 30 const 47 Using index condition
SELECT Country.Name
@@ -348,7 +348,7 @@ FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000))
AND Language IN ('English','Spanish');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY CountryLanguage range Language Language 30 NULL 72 Using index condition; Using where; Rowid-ordered scan
-2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition; Using where
+2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition
2 DEPENDENT SUBQUERY Country eq_ref PRIMARY PRIMARY 3 world.City.Country 1 Using where; Using index
select count(*)
from CountryLanguage
diff --git a/mysql-test/r/xtradb_mrr.result b/mysql-test/r/xtradb_mrr.result
index c238d0530af..e679606c2ca 100644
--- a/mysql-test/r/xtradb_mrr.result
+++ b/mysql-test/r/xtradb_mrr.result
@@ -186,7 +186,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
@@ -208,7 +208,7 @@ NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Rowid-ordered scan
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test
index 508c282b1dc..c7dc90394be 100644
--- a/mysql-test/t/myisam_icp.test
+++ b/mysql-test/t/myisam_icp.test
@@ -282,5 +282,20 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc;
drop table t0, t1;
+--echo #
+--echo # MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
+--echo #
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+create table t1 (key1 int not null, filler char(100)) engine=rocksdb;
+insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
+alter table t1 add key(key1);
+explain select * from t1 where key1 < 3 or key1 > 99999;
+select * from t1 where key1 < 3 or key1 > 99999;
+drop table ten,one_k,t1;
+
set optimizer_switch=@myisam_icp_tmp;
diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc
index df9dae8e442..35093dee235 100644
--- a/sql/opt_index_cond_pushdown.cc
+++ b/sql/opt_index_cond_pushdown.cc
@@ -263,6 +263,10 @@ Item *make_cond_for_index(Item *cond, TABLE *table, uint keyno,
Item *make_cond_remainder(Item *cond, TABLE *table, uint keyno,
bool other_tbls_ok, bool exclude_index)
{
+ if (exclude_index &&
+ uses_index_fields_only(cond, table, keyno, other_tbls_ok))
+ return NULL;
+
if (cond->type() == Item::COND_ITEM)
{
table_map tbl_map= 0;
@@ -317,14 +321,7 @@ Item *make_cond_remainder(Item *cond, TABLE *table, uint keyno,
return new_cond;
}
}
- else
- {
- if (exclude_index &&
- uses_index_fields_only(cond, table, keyno, other_tbls_ok))
- return 0;
- else
- return cond;
- }
+ return cond;
}
1
0
[Commits] b8e75a2: Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction
by psergey＠askmonty.org 18 Jul '18
by psergey＠askmonty.org 18 Jul '18
18 Jul '18
revision-id: b8e75a29dd371913772a7eaaddb56430c9e8ac8e
parent(s): a47797f16671a44a92632935fc93e302e0c969ff
committer: Sergei Petrunia
branch nick: mysql-5.6-rocksdb-spetrunia
timestamp: 2018-07-18 12:26:31 +0300
message:
Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction
rollback_to_stmt_savepoint() calls do_rollback_to_savepoint(). This
removes the changes made by this statement, and also removes the last
set savepoint.
Before we start processing the next statement, we need to set the new
savepoint, so we will have something to rollback to if the next statement
fails.
Since rollback_to_stmt_savepoint always sets a new savepoint now,
m_n_savepoints is now redundant and is removed.
Squash with D7509380
---
mysql-test/suite/rocksdb/r/transaction.result | 17 +++++++++++++++++
mysql-test/suite/rocksdb/t/transaction.test | 23 +++++++++++++++++++++++
storage/rocksdb/ha_rocksdb.cc | 16 ++++++++--------
3 files changed, 48 insertions(+), 8 deletions(-)
diff --git a/mysql-test/suite/rocksdb/r/transaction.result b/mysql-test/suite/rocksdb/r/transaction.result
index 006baaf..8a5825b 100644
--- a/mysql-test/suite/rocksdb/r/transaction.result
+++ b/mysql-test/suite/rocksdb/r/transaction.result
@@ -958,3 +958,20 @@ a
rollback;
drop function func;
drop table t1,t2,t3;
+#
+# MDEV-16710: Slave SQL: Could not execute Update_rows_v1 event with RocksDB and triggers
+# Issue#857: MyRocks: Incorrect behavior when multiple statements fail inside a transaction
+#
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=RocksDB;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=RocksDB;
+CREATE TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW INSERT INTO non_existing_table VALUES (NULL);
+BEGIN;
+DELETE FROM t1;
+INSERT INTO t2 VALUES (1);
+INSERT INTO t2 VALUES (2);
+# Must return empty result:
+SELECT * FROM t1;
+a
+COMMIT;
+drop table t1,t2;
diff --git a/mysql-test/suite/rocksdb/t/transaction.test b/mysql-test/suite/rocksdb/t/transaction.test
index 3350db9..129484b 100644
--- a/mysql-test/suite/rocksdb/t/transaction.test
+++ b/mysql-test/suite/rocksdb/t/transaction.test
@@ -133,3 +133,26 @@ rollback;
drop function func;
drop table t1,t2,t3;
+--echo #
+--echo # MDEV-16710: Slave SQL: Could not execute Update_rows_v1 event with RocksDB and triggers
+--echo # Issue#857: MyRocks: Incorrect behavior when multiple statements fail inside a transaction
+--echo #
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=RocksDB;
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=RocksDB;
+
+CREATE TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW INSERT INTO non_existing_table VALUES (NULL);
+
+BEGIN;
+DELETE FROM t1;
+--error 0,ER_NO_SUCH_TABLE
+INSERT INTO t2 VALUES (1);
+--error 0,ER_NO_SUCH_TABLE
+INSERT INTO t2 VALUES (2);
+--echo # Must return empty result:
+SELECT * FROM t1;
+COMMIT;
+
+drop table t1,t2;
+
diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc
index 57eaa83..a71a5b3 100644
--- a/storage/rocksdb/ha_rocksdb.cc
+++ b/storage/rocksdb/ha_rocksdb.cc
@@ -1875,8 +1875,6 @@ protected:
bool m_is_two_phase = false;
private:
- /* Number of RockDB savepoints taken */
- int m_n_savepoints;
/*
Number of write operations this transaction had when we took the last
savepoint (the idea is not to take another savepoint if we haven't made
@@ -2436,7 +2434,6 @@ public:
entire transaction.
*/
do_set_savepoint();
- m_n_savepoints= 1;
m_writes_at_last_savepoint= m_write_count;
}
@@ -2453,7 +2450,6 @@ public:
{
do_set_savepoint();
m_writes_at_last_savepoint= m_write_count;
- m_n_savepoints++;
}
}
@@ -2464,10 +2460,14 @@ public:
void rollback_to_stmt_savepoint() {
if (m_writes_at_last_savepoint != m_write_count) {
do_rollback_to_savepoint();
- if (!--m_n_savepoints) {
- do_set_savepoint();
- m_n_savepoints= 1;
- }
+ /*
+ RollbackToSavePoint "removes the most recent SetSavePoint()", so
+ we need to set it again so that next statement can roll back to this
+ stage.
+ It's ok to do it here at statement end (instead of doing it at next
+ statement start) because setting a savepoint is cheap.
+ */
+ do_set_savepoint();
m_writes_at_last_savepoint= m_write_count;
}
}
1
0
[Commits] 4b026efd7ba: MDEV-16769: Notes "WSREP: Waiting for SST to complete" flood the error log
by jan 18 Jul '18
by jan 18 Jul '18
18 Jul '18
revision-id: 4b026efd7ba029fe27709088f65a17f7c585a9fa (mariadb-10.1.34-24-g4b026efd7ba)
parent(s): e08ddccc35f1c199f503861bb63c6f7bcef2a9f6
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-07-18 11:22:17 +0300
message:
MDEV-16769: Notes "WSREP: Waiting for SST to complete" flood the error log
Used wrong initialization for condition timeout, should have
used set_timespec.
---
sql/wsrep_sst.cc | 26 ++++++++++++++++----------
1 file changed, 16 insertions(+), 10 deletions(-)
diff --git a/sql/wsrep_sst.cc b/sql/wsrep_sst.cc
index 60683bf740c..155051bfe84 100644
--- a/sql/wsrep_sst.cc
+++ b/sql/wsrep_sst.cc
@@ -204,8 +204,7 @@ void wsrep_sst_grab ()
// Wait for end of SST
bool wsrep_sst_wait ()
{
- struct timespec wtime = {WSREP_TIMEDWAIT_SECONDS, 0};
- uint32 total_wtime = 0;
+ double total_wtime = 0;
if (mysql_mutex_lock (&LOCK_wsrep_sst))
abort();
@@ -214,14 +213,18 @@ bool wsrep_sst_wait ()
while (!sst_complete)
{
+ struct timespec wtime;
+ set_timespec(wtime, WSREP_TIMEDWAIT_SECONDS);
+ time_t start_time = time(NULL);
mysql_cond_timedwait (&COND_wsrep_sst, &LOCK_wsrep_sst, &wtime);
+ time_t end_time = time(NULL);
if (!sst_complete)
{
- total_wtime += wtime.tv_sec;
- WSREP_DEBUG("Waiting for SST to complete. waited %u secs.", total_wtime);
+ total_wtime += difftime(end_time, start_time);
+ WSREP_DEBUG("Waiting for SST to complete. current seqno: %ld waited %f secs.", local_seqno, total_wtime);
service_manager_extend_timeout(WSREP_EXTEND_TIMEOUT_INTERVAL,
- "WSREP state transfer ongoing, current seqno: %ld", local_seqno);
+ "WSREP state transfer ongoing, current seqno: %ld waited %f secs", local_seqno, total_wtime);
}
}
@@ -1319,19 +1322,22 @@ void wsrep_SE_init_grab()
void wsrep_SE_init_wait()
{
- struct timespec wtime = {WSREP_TIMEDWAIT_SECONDS, 0};
- uint32 total_wtime=0;
+ double total_wtime=0;
while (SE_initialized == false)
{
+ struct timespec wtime;
+ set_timespec(wtime, WSREP_TIMEDWAIT_SECONDS);
+ time_t start_time = time(NULL);
mysql_cond_timedwait (&COND_wsrep_sst_init, &LOCK_wsrep_sst_init, &wtime);
+ time_t end_time = time(NULL);
if (!SE_initialized)
{
- total_wtime += wtime.tv_sec;
- WSREP_DEBUG("Waiting for SST to complete. waited %u secs.", total_wtime);
+ total_wtime += difftime(end_time, start_time);
+ WSREP_DEBUG("Waiting for SST to complete. current seqno: %ld waited %f secs.", local_seqno, total_wtime);
service_manager_extend_timeout(WSREP_EXTEND_TIMEOUT_INTERVAL,
- "WSREP SE initialization ongoing.");
+ "WSREP state transfer ongoing, current seqno: %ld waited %f secs", local_seqno, total_wtime);
}
}
1
0