- commits - lists.mariadb.org

[Commits] 2faefe5f7f4: MDEV-18383: Missing rows with pushdown condition defined with IF-function
by Galina 07 Mar '19

07 Mar '19

revision-id: 2faefe5f7f432f8d9b3435dd8f4e2a37163c5527 (mariadb-10.2.22-41-g2faefe5f7f4) parent(s): 57dd892ce8dc93bcd7eee953e01ebe8ed7e414c8 author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-03-07 12:35:18 +0300 message: MDEV-18383: Missing rows with pushdown condition defined with IF-function using Item_cond This bug is similar to the bug MDEV-16765. It appears because of the wrong pushdown into HAVING clause while this pushdown shouldn't be made at all. This happens because function that checks if Item_cond can be pushed always returns that it can be pushed. To fix it new method Item_cond::excl_dep_on_table() was added. --- mysql-test/r/derived_cond_pushdown.result | 21 +++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 22 ++++++++++++++++++++++ sql/item_cmpfunc.cc | 17 +++++++++++++++++ sql/item_cmpfunc.h | 1 + 4 files changed, 61 insertions(+) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 14c8e4d5e8f..00acdf241a6 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10480,4 +10480,25 @@ EXPLAIN } DROP VIEW v1,v2; DROP TABLE t1; +# +# MDEV-18383: pushdown condition with the IF structure +# defined with Item_cond item +# +CREATE TABLE t1(a INT, b INT); +CREATE TABLE t2(c INT, d INT); +INSERT INTO t1 VALUES (1,2),(3,4),(5,6); +INSERT INTO t2 VALUES (1,3),(3,7),(5,1); +SELECT * +FROM t1, +( +SELECT MAX(d) AS max_d,c +FROM t2 +GROUP BY c +) AS tab +WHERE t1.a=tab.c AND +IF(2,t1.a=1 OR t1.b>5,1=1); +a b max_d c +1 2 3 1 +5 6 1 5 +DROP TABLE t1,t2; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 25cb29e13db..313d77d2332 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2102,4 +2102,26 @@ eval EXPLAIN FORMAT=JSON $q2; DROP VIEW v1,v2; DROP TABLE t1; +--echo # +--echo # MDEV-18383: pushdown condition with the IF structure +--echo # defined with Item_cond item +--echo # + +CREATE TABLE t1(a INT, b INT); +CREATE TABLE t2(c INT, d INT); +INSERT INTO t1 VALUES (1,2),(3,4),(5,6); +INSERT INTO t2 VALUES (1,3),(3,7),(5,1); + +SELECT * +FROM t1, +( + SELECT MAX(d) AS max_d,c + FROM t2 + GROUP BY c +) AS tab +WHERE t1.a=tab.c AND + IF(2,t1.a=1 OR t1.b>5,1=1); + +DROP TABLE t1,t2; + --echo # End of 10.2 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 7357e57733a..badb0191471 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4994,6 +4994,23 @@ Item *Item_cond::build_clone(THD *thd, MEM_ROOT *mem_root) } +bool Item_cond::excl_dep_on_table(table_map tab_map) +{ + if (used_tables() & OUTER_REF_TABLE_BIT) + return false; + if (!(used_tables() & ~tab_map)) + return true; + List_iterator_fast<Item> li(list); + Item *item; + while ((item= li++)) + { + if (!item->excl_dep_on_table(tab_map)) + return false; + } + return true; +} + + bool Item_cond::excl_dep_on_grouping_fields(st_select_lex *sel) { List_iterator_fast<Item> li(list); diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 1b119c743d3..d083248f6cd 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2229,6 +2229,7 @@ class Item_cond :public Item_bool_func Item_transformer transformer, uchar *arg_t); bool eval_not_null_tables(void *opt_arg); Item *build_clone(THD *thd, MEM_ROOT *mem_root); + bool excl_dep_on_table(table_map tab_map); bool excl_dep_on_grouping_fields(st_select_lex *sel); };

1 0

[Commits] 65a1bb56c05: MDEV-17783: AddressSanitizer: stack-buffer-overflow in table_cond_selectivity with optimizer_use_condition_selectivity > 1, join_cache_level >2
by Varun 07 Mar '19

07 Mar '19

revision-id: 65a1bb56c05b9fe80c88f26da5bf11674ba3cffd (mariadb-10.1.38-27-g65a1bb56c05) parent(s): 84645366c459b01771223a6d1a20bf7ac38adf48 author: Varun Gupta committer: Varun Gupta timestamp: 2019-03-07 13:38:18 +0530 message: MDEV-17783: AddressSanitizer: stack-buffer-overflow in table_cond_selectivity with optimizer_use_condition_selectivity > 1, join_cache_level >2 This case happens when we pick hash-join in best_access_path but the number of keyparts in the hash_key are > MAX_REF_PARTS. hash join should not use more than MAX_REF_PARTS for hash index. --- mysql-test/r/selectivity.result | 24 ++++++++++++++++++++++++ mysql-test/r/selectivity_innodb.result | 24 ++++++++++++++++++++++++ mysql-test/t/selectivity.test | 24 ++++++++++++++++++++++++ sql/sql_select.cc | 13 +++++++++---- 4 files changed, 81 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 2e69f674ea3..bf1cae3c787 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1635,3 +1635,27 @@ set @@use_stat_tables= @save_use_stat_tables; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; drop table t1; drop function f1; +# +# MDEV-17783: AddressSanitizer: stack-buffer-overflow in table_cond_selectivity +# with optimizer_use_condition_selectivity > 1, join_cache_level >2 +# +set @save_join_cache_level= @@join_cache_level; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set join_cache_level=3; +set optimizer_use_condition_selectivity=2; +CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int, c20 int, c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int, c29 int, c30 int, c31 int, c32 int, c33 int, c34 int); +insert into t1 values +(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33),(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33); +explain select * from t1, t1 t where t1.c1 = t.c1 and t1.c2 = t.c2 and t1.c3 = t.c3 and t1.c4 = t.c4 and t1.c5 = t.c5 and t1.c6 = t.c6 and t1.c7 = t.c7 and t1.c8 = t.c8 and t1.c9 = t.c9 and t1.c10 = t.c10 and t1.c11 = t.c11 and t1.c12 = t.c12 and t1.c13 = t.c13 and t1.c14 = t.c14 and t1.c15 = t.c15 and t1.c16 = t.c16 and t1.c17 = t.c17 and t1.c18 = t.c18 and t1.c19 = t.c19 and t1.c20 = t.c20 and t1.c21 = t.c21 and t1.c22 = t.c22 and t1.c23 = t.c23 and t1.c24 = t.c24 and t1.c25 = t.c25 and t1.c26 = t.c26 and t1.c27 = t.c27 and t1.c28 = t.c28 and t1.c29 = t.c29 and t1.c30 = t.c30 and t1.c31 = t.c31 and t1.c32 = t.c32 and t1.c33 = t.c33 and t1.c34 = t.c34; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t hash_ALL NULL #hash#$hj 160 test.t1.c1,test.t1.c2,test.t1.c3,test.t1.c4,test.t1.c5,test.t1.c6,test.t1.c7,test.t1.c8,test.t1.c9,test.t1.c10,test.t1.c11,test.t1.c12,test.t1.c13,test.t1.c14,test.t1.c15,test.t1.c16,test.t1.c17,test.t1.c18,test.t1.c19,test.t1.c20,test.t1.c21,test.t1.c22,test.t1.c23,test.t1.c24,test.t1.c25,test.t1.c26,test.t1.c27,test.t1.c28,test.t1.c29,test.t1.c30,test.t1.c31,test.t1.c32 2 Using where; Using join buffer (flat, BNLH join) +select * from t1, t1 t where t1.c1 = t.c1 and t1.c2 = t.c2 and t1.c3 = t.c3 and t1.c4 = t.c4 and t1.c5 = t.c5 and t1.c6 = t.c6 and t1.c7 = t.c7 and t1.c8 = t.c8 and t1.c9 = t.c9 and t1.c10 = t.c10 and t1.c11 = t.c11 and t1.c12 = t.c12 and t1.c13 = t.c13 and t1.c14 = t.c14 and t1.c15 = t.c15 and t1.c16 = t.c16 and t1.c17 = t.c17 and t1.c18 = t.c18 and t1.c19 = t.c19 and t1.c20 = t.c20 and t1.c21 = t.c21 and t1.c22 = t.c22 and t1.c23 = t.c23 and t1.c24 = t.c24 and t1.c25 = t.c25 and t1.c26 = t.c26 and t1.c27 = t.c27 and t1.c28 = t.c28 and t1.c29 = t.c29 and t1.c30 = t.c30 and t1.c31 = t.c31 and t1.c32 = t.c32 and t1.c33 = t.c33 and t1.c34 = t.c34; +c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32 c33 c34 c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32 c33 c34 +0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 +0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 +0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 +0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 +set join_cache_level= @save_join_cache_level; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index d3e71088f87..4027315826e 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1645,6 +1645,30 @@ set @@use_stat_tables= @save_use_stat_tables; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; drop table t1; drop function f1; +# +# MDEV-17783: AddressSanitizer: stack-buffer-overflow in table_cond_selectivity +# with optimizer_use_condition_selectivity > 1, join_cache_level >2 +# +set @save_join_cache_level= @@join_cache_level; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set join_cache_level=3; +set optimizer_use_condition_selectivity=2; +CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int, c20 int, c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int, c29 int, c30 int, c31 int, c32 int, c33 int, c34 int); +insert into t1 values +(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33),(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33); +explain select * from t1, t1 t where t1.c1 = t.c1 and t1.c2 = t.c2 and t1.c3 = t.c3 and t1.c4 = t.c4 and t1.c5 = t.c5 and t1.c6 = t.c6 and t1.c7 = t.c7 and t1.c8 = t.c8 and t1.c9 = t.c9 and t1.c10 = t.c10 and t1.c11 = t.c11 and t1.c12 = t.c12 and t1.c13 = t.c13 and t1.c14 = t.c14 and t1.c15 = t.c15 and t1.c16 = t.c16 and t1.c17 = t.c17 and t1.c18 = t.c18 and t1.c19 = t.c19 and t1.c20 = t.c20 and t1.c21 = t.c21 and t1.c22 = t.c22 and t1.c23 = t.c23 and t1.c24 = t.c24 and t1.c25 = t.c25 and t1.c26 = t.c26 and t1.c27 = t.c27 and t1.c28 = t.c28 and t1.c29 = t.c29 and t1.c30 = t.c30 and t1.c31 = t.c31 and t1.c32 = t.c32 and t1.c33 = t.c33 and t1.c34 = t.c34; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t hash_ALL NULL #hash#$hj 160 test.t1.c1,test.t1.c2,test.t1.c3,test.t1.c4,test.t1.c5,test.t1.c6,test.t1.c7,test.t1.c8,test.t1.c9,test.t1.c10,test.t1.c11,test.t1.c12,test.t1.c13,test.t1.c14,test.t1.c15,test.t1.c16,test.t1.c17,test.t1.c18,test.t1.c19,test.t1.c20,test.t1.c21,test.t1.c22,test.t1.c23,test.t1.c24,test.t1.c25,test.t1.c26,test.t1.c27,test.t1.c28,test.t1.c29,test.t1.c30,test.t1.c31,test.t1.c32 2 Using where; Using join buffer (flat, BNLH join) +select * from t1, t1 t where t1.c1 = t.c1 and t1.c2 = t.c2 and t1.c3 = t.c3 and t1.c4 = t.c4 and t1.c5 = t.c5 and t1.c6 = t.c6 and t1.c7 = t.c7 and t1.c8 = t.c8 and t1.c9 = t.c9 and t1.c10 = t.c10 and t1.c11 = t.c11 and t1.c12 = t.c12 and t1.c13 = t.c13 and t1.c14 = t.c14 and t1.c15 = t.c15 and t1.c16 = t.c16 and t1.c17 = t.c17 and t1.c18 = t.c18 and t1.c19 = t.c19 and t1.c20 = t.c20 and t1.c21 = t.c21 and t1.c22 = t.c22 and t1.c23 = t.c23 and t1.c24 = t.c24 and t1.c25 = t.c25 and t1.c26 = t.c26 and t1.c27 = t.c27 and t1.c28 = t.c28 and t1.c29 = t.c29 and t1.c30 = t.c30 and t1.c31 = t.c31 and t1.c32 = t.c32 and t1.c33 = t.c33 and t1.c34 = t.c34; +c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32 c33 c34 c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32 c33 c34 +0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 +0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 +0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 +0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 +set join_cache_level= @save_join_cache_level; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; set @tmp_oucs= @@optimizer_use_condition_selectivity; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 3df49456332..078f7e2e8f6 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1102,3 +1102,27 @@ set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectiv drop table t1; drop function f1; +--echo # +--echo # MDEV-17783: AddressSanitizer: stack-buffer-overflow in table_cond_selectivity +--echo # with optimizer_use_condition_selectivity > 1, join_cache_level >2 +--echo # + +set @save_join_cache_level= @@join_cache_level; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set join_cache_level=3; +set optimizer_use_condition_selectivity=2; + +CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int, c20 int, c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int, c29 int, c30 int, c31 int, c32 int, c33 int, c34 int); + +insert into t1 values +(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33),(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33); + +let $x= +select * from t1, t1 t where t1.c1 = t.c1 and t1.c2 = t.c2 and t1.c3 = t.c3 and t1.c4 = t.c4 and t1.c5 = t.c5 and t1.c6 = t.c6 and t1.c7 = t.c7 and t1.c8 = t.c8 and t1.c9 = t.c9 and t1.c10 = t.c10 and t1.c11 = t.c11 and t1.c12 = t.c12 and t1.c13 = t.c13 and t1.c14 = t.c14 and t1.c15 = t.c15 and t1.c16 = t.c16 and t1.c17 = t.c17 and t1.c18 = t.c18 and t1.c19 = t.c19 and t1.c20 = t.c20 and t1.c21 = t.c21 and t1.c22 = t.c22 and t1.c23 = t.c23 and t1.c24 = t.c24 and t1.c25 = t.c25 and t1.c26 = t.c26 and t1.c27 = t.c27 and t1.c28 = t.c28 and t1.c29 = t.c29 and t1.c30 = t.c30 and t1.c31 = t.c31 and t1.c32 = t.c32 and t1.c33 = t.c33 and t1.c34 = t.c34; + +eval explain $x; +eval $x; + +set join_cache_level= @save_join_cache_level; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 155e261ac34..65dff40f050 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7634,7 +7634,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, */ keyuse= pos->key; keyparts=0; - while (keyuse->table == table && keyuse->key == key) + while (keyuse->table == table && keyuse->key == key && keyparts < MAX_REF_PARTS) { if (!(keyuse->used_tables & (rem_tables | table->map))) { @@ -8935,7 +8935,8 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, } first_keyuse= FALSE; keyuse++; - } while (keyuse->table == table && keyuse->is_for_hash_join()); + } while (keyuse->table == table && keyuse->is_for_hash_join() && + key_parts < MAX_REF_PARTS); if (!key_parts) DBUG_RETURN(TRUE); /* This memory is allocated only once for the joined table join_tab */ @@ -8958,6 +8959,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, first_keyuse= TRUE; keyuse= org_keyuse; + key_parts= 0; do { if (!(~used_tables & keyuse->used_tables) && @@ -8988,11 +8990,13 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, table->create_key_part_by_field(key_part_info, field, fieldnr); keyinfo->key_length += key_part_info->store_length; key_part_info++; + key_parts++; } } first_keyuse= FALSE; keyuse++; - } while (keyuse->table == table && keyuse->is_for_hash_join()); + } while (keyuse->table == table && keyuse->is_for_hash_join() && + key_parts < MAX_REF_PARTS); keyinfo->ext_key_parts= keyinfo->user_defined_key_parts; keyinfo->ext_key_flags= keyinfo->flags; @@ -9090,7 +9094,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, } } keyuse++; - } while (keyuse->table == table && keyuse->key == key); + } while (keyuse->table == table && keyuse->key == key && + keyparts < MAX_REF_PARTS); if (!keyparts && allow_full_scan) {

1 0

[Commits] 059b0adf818: remove WSREP_SST_OPT_SUFFIX_VALUE, checking [mysqld] is covered in the parse_cnf --mysqld case also in wsrep_sst_xtrabackup-v2
by jan 07 Mar '19

07 Mar '19

revision-id: 059b0adf81846721a11caa7a73a190d5e1115594 (mariadb-10.1.38-24-g059b0adf818) parent(s): 79d0e2ac64fecc77a13ff2715ba870ac7453daf1 author: Jan Lindström committer: Jan Lindström timestamp: 2019-03-07 08:06:50 +0200 message: remove WSREP_SST_OPT_SUFFIX_VALUE, checking [mysqld] is covered in the parse_cnf --mysqld case also in wsrep_sst_xtrabackup-v2 --- scripts/wsrep_sst_xtrabackup-v2.sh | 3 --- 1 file changed, 3 deletions(-) diff --git a/scripts/wsrep_sst_xtrabackup-v2.sh b/scripts/wsrep_sst_xtrabackup-v2.sh index 5fad91df18b..54a7c8852e5 100644 --- a/scripts/wsrep_sst_xtrabackup-v2.sh +++ b/scripts/wsrep_sst_xtrabackup-v2.sh @@ -905,9 +905,6 @@ if [ ! -z "$INNODB_DATA_HOME_DIR_ARG" ]; then INNODB_DATA_HOME_DIR=$INNODB_DATA_HOME_DIR_ARG fi # if INNODB_DATA_HOME_DIR env. variable is not set, try to get it from my.cnf -if [ -z "$INNODB_DATA_HOME_DIR" ]; then - INNODB_DATA_HOME_DIR=$(parse_cnf mysqld$WSREP_SST_OPT_SUFFIX_VALUE innodb-data-home-dir '') -fi if [ -z "$INNODB_DATA_HOME_DIR" ]; then INNODB_DATA_HOME_DIR=$(parse_cnf --mysqld innodb-data-home-dir "") fi

1 0

[Commits] cf0be6420cf: there are such tests
by Oleksandr Byelkin 06 Mar '19

06 Mar '19

revision-id: cf0be6420cfe107230aaf6025818638c969960cc (mariadb-10.4.3-37-gcf0be6420cf) parent(s): 3897734cb0b080585798dfbab031f8ef1eaa6ce9 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-03-06 17:49:57 +0100 message: there are such tests --- mysql-test/main/subselect_innodb.result | 35 ------------------------------ mysql-test/main/subselect_innodb.test | 38 --------------------------------- 2 files changed, 73 deletions(-) diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result index 64e67c1dfc1..0eb40c9be00 100644 --- a/mysql-test/main/subselect_innodb.result +++ b/mysql-test/main/subselect_innodb.result @@ -616,38 +616,3 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` join `test`.`t2` semi join (`test`.`t4`) join `test`.`t3` where `test`.`t4`.`f4` = 1 and `test`.`t1`.`f1` >= `test`.`t2`.`f2` DROP TABLE t1,t2,t3,t4; -# -# MDEV-18339: ASAN heap-buffer-overflow in -# Item_exists_subselect::is_top_level_item -# -CREATE TABLE t1 ( pk int PRIMARY KEY , iiiiiiiiiiiii int , col_int1111 int, col_date_nokey date , col_time_key time, col_time_nokey time , col_datetime_key time, col_datetime_nokey time , ccccccccccccccc varchar(1), vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; -CREATE TABLE t2 ( iiiiiiiiiiiii int , vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; -CREATE TABLE t3 ( pk int PRIMARY KEY) engine=innodb; -CREATE TABLE t4 ( iiiiiiiiiiiii int , vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; -select * from -(select distinct -(select count(t111111111.`ccccccccccccccc`) from t1 as t111111111 -where (exists(select distinct t22222222222.`iiiiiiiiiiiii` from t2 as t22222222222 where t22222222222.`vvvvvvvvvvvvvvvvv` < t111111111.`vvvvvvvvvvvvvvvvv`) -or t111111111.`ccccccccccccccc` != t111111111.`vvvvvvvvvvvvvvvvv`) -) as field1 -from -(select t1_______2.* -from (t1 as t1_______1 join t1 as t1_______2 -on (t1_______2.`vvvvvvvvvvvvvvvvv` = t1_______1.`ccccccccccccccc` - and t1_______1.`iiiiiiiiiiiii` != -(select sum(t44444444444.`iiiiiiiiiiiii`) -from (t4 as t44444444444 join t3 as t33333333333 -on (t33333333333.`pk` = t44444444444.`iiiiiiiiiiiii`)) -where t44444444444.`vvvvvvvvvvvvvvvvv` > 'x') -) -) -) as alias1 -straight_join -t2 as alias2 -on (alias2.`iiiiiiiiiiiii` = alias1.`iiiiiiiiiiiii`) -where ((select 9 from dual) is null) -and alias1.`pk` in (32, 129, 87, 51, 58, 152, 241, 37, 55, 237, 166) -group by field1 /* 111 -111111111 */ ) as derived_aaaaa /* comment11111111111111111111111111 */; -field1 -# End of 10.4 tests diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test index 90d3b07c1ad..544bcd994ed 100644 --- a/mysql-test/main/subselect_innodb.test +++ b/mysql-test/main/subselect_innodb.test @@ -611,41 +611,3 @@ FROM t1 DROP TABLE t1,t2,t3,t4; ---echo # ---echo # MDEV-18339: ASAN heap-buffer-overflow in ---echo # Item_exists_subselect::is_top_level_item ---echo # - -CREATE TABLE t1 ( pk int PRIMARY KEY , iiiiiiiiiiiii int , col_int1111 int, col_date_nokey date , col_time_key time, col_time_nokey time , col_datetime_key time, col_datetime_nokey time , ccccccccccccccc varchar(1), vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; - -CREATE TABLE t2 ( iiiiiiiiiiiii int , vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; -CREATE TABLE t3 ( pk int PRIMARY KEY) engine=innodb; -CREATE TABLE t4 ( iiiiiiiiiiiii int , vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; - -select * from -(select distinct - (select count(t111111111.`ccccccccccccccc`) from t1 as t111111111 - where (exists(select distinct t22222222222.`iiiiiiiiiiiii` from t2 as t22222222222 where t22222222222.`vvvvvvvvvvvvvvvvv` < t111111111.`vvvvvvvvvvvvvvvvv`) - or t111111111.`ccccccccccccccc` != t111111111.`vvvvvvvvvvvvvvvvv`) - ) as field1 -from - (select t1_______2.* - from (t1 as t1_______1 join t1 as t1_______2 - on (t1_______2.`vvvvvvvvvvvvvvvvv` = t1_______1.`ccccccccccccccc` - and t1_______1.`iiiiiiiiiiiii` != - (select sum(t44444444444.`iiiiiiiiiiiii`) - from (t4 as t44444444444 join t3 as t33333333333 - on (t33333333333.`pk` = t44444444444.`iiiiiiiiiiiii`)) - where t44444444444.`vvvvvvvvvvvvvvvvv` > 'x') - ) - ) - ) as alias1 -straight_join - t2 as alias2 -on (alias2.`iiiiiiiiiiiii` = alias1.`iiiiiiiiiiiii`) -where ((select 9 from dual) is null) -and alias1.`pk` in (32, 129, 87, 51, 58, 152, 241, 37, 55, 237, 166) -group by field1 /* 111 -111111111 */ ) as derived_aaaaa /* comment11111111111111111111111111 */; - ---echo # End of 10.4 tests

1 0

[Commits] 79d0e2ac64f: Galera 3 versions of the result files recorded.
by jan 06 Mar '19

06 Mar '19

revision-id: 79d0e2ac64fecc77a13ff2715ba870ac7453daf1 (mariadb-10.1.38-23-g79d0e2ac64f) parent(s): 70584750cb4f36a956f00a0131e82922795c3516 author: Jan Lindström committer: Jan Lindström timestamp: 2019-03-06 16:41:40 +0200 message: Galera 3 versions of the result files recorded. --- .../r/galera_ipv6_mariabackup_section.result | 18 ++++++++++++++++++ .../r/galera_ipv6_rsync_section.result | 5 ----- .../t/galera_ipv6_mariabackup_section.test | 21 +++++++++++---------- 3 files changed, 29 insertions(+), 15 deletions(-) diff --git a/mysql-test/suite/galera_3nodes/r/galera_ipv6_mariabackup_section.result b/mysql-test/suite/galera_3nodes/r/galera_ipv6_mariabackup_section.result new file mode 100644 index 00000000000..53e35939a79 --- /dev/null +++ b/mysql-test/suite/galera_3nodes/r/galera_ipv6_mariabackup_section.result @@ -0,0 +1,18 @@ +SELECT VARIABLE_VALUE LIKE '%[::1]%' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_incoming_addresses'; +VARIABLE_VALUE LIKE '%[::1]%' +1 +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +VARIABLE_VALUE = 3 +1 +SET GLOBAL wsrep_provider_options='gmcast.isolate=1'; +CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +SET GLOBAL wsrep_provider_options='gmcast.isolate=0'; +SELECT COUNT(*) = 1 FROM t1; +COUNT(*) = 1 +1 +DROP TABLE t1; +include/assert_grep.inc [Streaming the backup to joiner at \[::1\]] +include/assert_grep.inc [async IST sender starting to serve tcp://\[::1\]:] +include/assert_grep.inc [IST receiver addr using tcp://\[::1\]] +include/assert_grep.inc [Prepared IST receiver, listening at: tcp://\[::1\]] diff --git a/mysql-test/suite/galera_3nodes/r/galera_ipv6_rsync_section.result b/mysql-test/suite/galera_3nodes/r/galera_ipv6_rsync_section.result index 3f810d3eb97..a2bf5f4d98c 100644 --- a/mysql-test/suite/galera_3nodes/r/galera_ipv6_rsync_section.result +++ b/mysql-test/suite/galera_3nodes/r/galera_ipv6_rsync_section.result @@ -1,17 +1,12 @@ -connection node_2; -connection node_1; SELECT VARIABLE_VALUE LIKE '%[::1]%' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_incoming_addresses'; VARIABLE_VALUE LIKE '%[::1]%' 1 SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; VARIABLE_VALUE = 3 1 -connection node_2; SET GLOBAL wsrep_provider_options='gmcast.isolate=1'; -connection node_1; CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); -connection node_2; SET GLOBAL wsrep_provider_options='gmcast.isolate=0'; SELECT COUNT(*) = 1 FROM t1; COUNT(*) = 1 diff --git a/mysql-test/suite/galera_3nodes/t/galera_ipv6_mariabackup_section.test b/mysql-test/suite/galera_3nodes/t/galera_ipv6_mariabackup_section.test index 71d17c133a5..95cd1a5bea5 100644 --- a/mysql-test/suite/galera_3nodes/t/galera_ipv6_mariabackup_section.test +++ b/mysql-test/suite/galera_3nodes/t/galera_ipv6_mariabackup_section.test @@ -39,30 +39,31 @@ DROP TABLE t1; --let $assert_only_after = CURRENT_TEST # The SSTs happen when nodes are started first time ---let $assert_count = 2 +--let $assert_count= 2 --let $assert_text = Streaming the backup to joiner at \[::1\] --let $assert_select = Streaming the backup to joiner at \[::1\] --source include/assert_grep.inc -# There will be 3 ISTs donated from node_1 in Galera 4. +# There will be 1 ISTs donated from node_1 in Galera 3. # Two first happen at the initial startup to populate the certification # index. The third one is from the IST which happens during the actual test. ---let $assert_count = 3 +--let $assert_count= 1 --let $assert_text = async IST sender starting to serve tcp://\[::1\]: --let $assert_select = async IST sender starting to serve tcp://\[::1\]: --source include/assert_grep.inc +--connection node_2 --let $assert_file = $MYSQLTEST_VARDIR/log/mysqld.2.err +--let $assert_only_after = CURRENT_TEST -# There are two ISTs on joiner, the first at the initial startup, the second -# during the actual test. ---let $assert_count = 2 +# There is one ISTs on joiner at Galera 3. +--let $assert_count= 1 --let $assert_text = IST receiver addr using tcp://\[::1\] --let $assert_select = IST receiver addr using tcp://\[::1\] --source include/assert_grep.inc -# The receiver expects seqnos 3-6 only once. ---let $assert_count = 1 ---let $assert_text = Prepared IST receiver for 3-6, listening at: tcp://\[::1\] ---let $assert_select = Prepared IST receiver for 3-6, listening at: tcp://\[::1\] +# There will be only one Prepared IST and in Galera 3 segnos are not printed +--let $assert_count= 1 +--let $assert_text = Prepared IST receiver, listening at: tcp://\[::1\] +--let $assert_select = Prepared IST receiver, listening at: tcp://\[::1\] --source include/assert_grep.inc

1 0

[Commits] 3897734cb0b: MDEV-18339: ASAN heap-buffer-overflow in Item_exists_subselect::is_top_level_item
by Oleksandr Byelkin 06 Mar '19

06 Mar '19

revision-id: 3897734cb0b080585798dfbab031f8ef1eaa6ce9 (mariadb-10.4.3-36-g3897734cb0b) parent(s): 2a791c53ad93c8bc1441dd227000234bd49c4990 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-03-06 15:31:50 +0100 message: MDEV-18339: ASAN heap-buffer-overflow in Item_exists_subselect::is_top_level_item Right argument of Item_in_optimizer can not be cast to Item_in_subselect in invisible mode. --- mysql-test/main/subselect_innodb.result | 35 ++++++++++++++++++++++++++++++ mysql-test/main/subselect_innodb.test | 38 +++++++++++++++++++++++++++++++++ sql/item_cmpfunc.cc | 8 +++---- 3 files changed, 77 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result index 0eb40c9be00..64e67c1dfc1 100644 --- a/mysql-test/main/subselect_innodb.result +++ b/mysql-test/main/subselect_innodb.result @@ -616,3 +616,38 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` join `test`.`t2` semi join (`test`.`t4`) join `test`.`t3` where `test`.`t4`.`f4` = 1 and `test`.`t1`.`f1` >= `test`.`t2`.`f2` DROP TABLE t1,t2,t3,t4; +# +# MDEV-18339: ASAN heap-buffer-overflow in +# Item_exists_subselect::is_top_level_item +# +CREATE TABLE t1 ( pk int PRIMARY KEY , iiiiiiiiiiiii int , col_int1111 int, col_date_nokey date , col_time_key time, col_time_nokey time , col_datetime_key time, col_datetime_nokey time , ccccccccccccccc varchar(1), vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; +CREATE TABLE t2 ( iiiiiiiiiiiii int , vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; +CREATE TABLE t3 ( pk int PRIMARY KEY) engine=innodb; +CREATE TABLE t4 ( iiiiiiiiiiiii int , vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; +select * from +(select distinct +(select count(t111111111.`ccccccccccccccc`) from t1 as t111111111 +where (exists(select distinct t22222222222.`iiiiiiiiiiiii` from t2 as t22222222222 where t22222222222.`vvvvvvvvvvvvvvvvv` < t111111111.`vvvvvvvvvvvvvvvvv`) +or t111111111.`ccccccccccccccc` != t111111111.`vvvvvvvvvvvvvvvvv`) +) as field1 +from +(select t1_______2.* +from (t1 as t1_______1 join t1 as t1_______2 +on (t1_______2.`vvvvvvvvvvvvvvvvv` = t1_______1.`ccccccccccccccc` + and t1_______1.`iiiiiiiiiiiii` != +(select sum(t44444444444.`iiiiiiiiiiiii`) +from (t4 as t44444444444 join t3 as t33333333333 +on (t33333333333.`pk` = t44444444444.`iiiiiiiiiiiii`)) +where t44444444444.`vvvvvvvvvvvvvvvvv` > 'x') +) +) +) as alias1 +straight_join +t2 as alias2 +on (alias2.`iiiiiiiiiiiii` = alias1.`iiiiiiiiiiiii`) +where ((select 9 from dual) is null) +and alias1.`pk` in (32, 129, 87, 51, 58, 152, 241, 37, 55, 237, 166) +group by field1 /* 111 +111111111 */ ) as derived_aaaaa /* comment11111111111111111111111111 */; +field1 +# End of 10.4 tests diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test index 544bcd994ed..90d3b07c1ad 100644 --- a/mysql-test/main/subselect_innodb.test +++ b/mysql-test/main/subselect_innodb.test @@ -611,3 +611,41 @@ FROM t1 DROP TABLE t1,t2,t3,t4; +--echo # +--echo # MDEV-18339: ASAN heap-buffer-overflow in +--echo # Item_exists_subselect::is_top_level_item +--echo # + +CREATE TABLE t1 ( pk int PRIMARY KEY , iiiiiiiiiiiii int , col_int1111 int, col_date_nokey date , col_time_key time, col_time_nokey time , col_datetime_key time, col_datetime_nokey time , ccccccccccccccc varchar(1), vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; + +CREATE TABLE t2 ( iiiiiiiiiiiii int , vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; +CREATE TABLE t3 ( pk int PRIMARY KEY) engine=innodb; +CREATE TABLE t4 ( iiiiiiiiiiiii int , vvvvvvvvvvvvvvvvv varchar(1)) engine=innodb; + +select * from +(select distinct + (select count(t111111111.`ccccccccccccccc`) from t1 as t111111111 + where (exists(select distinct t22222222222.`iiiiiiiiiiiii` from t2 as t22222222222 where t22222222222.`vvvvvvvvvvvvvvvvv` < t111111111.`vvvvvvvvvvvvvvvvv`) + or t111111111.`ccccccccccccccc` != t111111111.`vvvvvvvvvvvvvvvvv`) + ) as field1 +from + (select t1_______2.* + from (t1 as t1_______1 join t1 as t1_______2 + on (t1_______2.`vvvvvvvvvvvvvvvvv` = t1_______1.`ccccccccccccccc` + and t1_______1.`iiiiiiiiiiiii` != + (select sum(t44444444444.`iiiiiiiiiiiii`) + from (t4 as t44444444444 join t3 as t33333333333 + on (t33333333333.`pk` = t44444444444.`iiiiiiiiiiiii`)) + where t44444444444.`vvvvvvvvvvvvvvvvv` > 'x') + ) + ) + ) as alias1 +straight_join + t2 as alias2 +on (alias2.`iiiiiiiiiiiii` = alias1.`iiiiiiiiiiiii`) +where ((select 9 from dual) is null) +and alias1.`pk` in (32, 129, 87, 51, 58, 152, 241, 37, 55, 237, 166) +group by field1 /* 111 +111111111 */ ) as derived_aaaaa /* comment11111111111111111111111111 */; + +--echo # End of 10.4 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 55a06254917..ffb7b60e4de 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1182,6 +1182,8 @@ longlong Item_func_truth::val_int() bool Item_in_optimizer::is_top_level_item() { + if (invisible_mode()) + return FALSE; return ((Item_in_subselect *)args[1])->is_top_level_item(); } @@ -1237,8 +1239,7 @@ void Item_in_optimizer::print(String *str, enum_query_type query_type) void Item_in_optimizer::restore_first_argument() { - if (args[1]->type() == Item::SUBSELECT_ITEM && - ((Item_subselect *)args[1])->is_in_predicate()) + if (!invisible_mode()) { args[0]= ((Item_in_subselect *)args[1])->left_expr; } @@ -1255,8 +1256,7 @@ bool Item_in_optimizer::fix_left(THD *thd) it is args[0]. */ Item **ref0= args; - if (args[1]->type() == Item::SUBSELECT_ITEM && - ((Item_subselect *)args[1])->is_in_predicate()) + if (!invisible_mode()) { /* left_expr->fix_fields() may cause left_expr to be substituted for

1 0

[Commits] 7af7b669eb1: c
by Oleksandr Byelkin 06 Mar '19

06 Mar '19

revision-id: 7af7b669eb14d86c19409a058607d1ba9e2ceff4 (mariadb-10.2.22-36-g7af7b669eb1) parent(s): b68d5c38a41908feb78857d4437626c1adf268e5 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-03-06 13:36:44 +0100 message: c --- sql/handler.cc | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) diff --git a/sql/handler.cc b/sql/handler.cc index dc454fa1c06..d307433cf5d 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3308,8 +3308,7 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, MY_BITMAP *old_read_set; bool rnd_inited= (inited == RND); - if (rnd_inited && - ha_rnd_end()) + if (rnd_inited && ha_rnd_end()) return; old_read_set= table->prepare_for_keyread(table->s->next_number_index); @@ -3320,7 +3319,7 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, DBUG_ASSERT(0); (void) extra(HA_EXTRA_NO_KEYREAD); *first_value= ULONGLONG_MAX; - if (rnd_inited && + if (rnd_inited) ha_rnd_init(0); return; }

1 0

[Commits] 3c6a45ddd94: MDEV-18830: Port SST fixes from 10.4 to 10.1
by jan 06 Mar '19

06 Mar '19

revision-id: 3c6a45ddd941e88ec88b0c03654e21dd1e785228 (mariadb-10.1.38-20-g3c6a45ddd94) parent(s): 91e4f00389483d22fa81517bbcdcd21499fd283a author: Jan Lindström committer: Jan Lindström timestamp: 2019-03-06 13:33:37 +0200 message: MDEV-18830: Port SST fixes from 10.4 to 10.1 modified: mysql-test/suite/galera/disabled.def modified: mysql-test/suite/galera/r/galera_many_rows.result modified: mysql-test/suite/galera/t/galera_kill_nochanges.test new file: mysql-test/suite/galera/t/galera_many_rows.cnf modified: mysql-test/suite/galera/t/galera_many_rows.test modified: mysql-test/suite/galera/t/galera_var_dirty_reads.test modified: scripts/wsrep_sst_mariabackup.sh --- mysql-test/suite/galera/disabled.def | 18 ++++- mysql-test/suite/galera/r/galera_many_rows.result | 26 +++--- .../suite/galera/t/galera_kill_nochanges.test | 9 ++- mysql-test/suite/galera/t/galera_many_rows.cnf | 5 ++ mysql-test/suite/galera/t/galera_many_rows.test | 19 +++-- .../suite/galera/t/galera_var_dirty_reads.test | 5 -- scripts/wsrep_sst_mariabackup.sh | 93 ++++++++++++++-------- 7 files changed, 112 insertions(+), 63 deletions(-) diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index b81c9a737ec..1fc67475b93 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -19,12 +19,24 @@ galera_as_master_gtid_change_master : Requires MySQL GTID galera_as_slave_preordered : wsrep-preordered feature not merged to MariaDB GAL-419 : MDEV-13549 Galera test failures galera_var_notify_cmd : MDEV-13549 Galera test failures -galera_as_slave_replication_bundle : MDEV-13549 Galera test failures galera_ssl_upgrade : MDEV-13549 Galera test failures -galera.MW-329 : wsrep_local_replays not stable +MW-329 : wsrep_local_replays not stable MW-416 : MDEV-13549 Galera test failures +MW-44 : MDEV-15809 Test failure on galera.MW-44 +galera_pc_ignore_sb : MDEV-15811 Test failure on galera_pc_ignore_sb +galera_kill_applier : race condition at the start of the test +galera_ist_progress: MDEV-15236 galera_ist_progress fails when trying to read transfer status +pxc-421: Lock timeout exceeded galera_sst_mysqldump_with_key : MDEV-16890 Galera test failure +galera_kill_ddl : MDEV-17108 Test failure on galera.galera_kill_ddl +galera_var_node_address : MDEV-17151 Galera test failure on galera.galera_var_node_address galera_gc_fc_limit : MDEV-17061 Test failure on galera.galera_gc_fc_limit -galera_as_slave_replication_budle : MDEV-15785 Test case galera_as_slave_replication_bundle caused debug assertion +galera_as_slave_replication_bundle : MDEV-15785 Test case galera_as_slave_replication_bundle caused debug assertion galera_wan : MDEV-17259: Test failure on galera.galera_wan galera_pc_ignore_sb : MDEV-17357 Test failure on galera.galera_pc_ignore_sb +MW-328A : MDEV-17847 Galera test failure on MW-328[A|B|C] +MW-328B : MDEV-17847 Galera test failure on MW-328[A|B|C] +MW-328C : MDEV-17847 Galera test failure on MW-328[A|B|C] +query_cache : MDEV-18137: Galera test failure on query_cache +galera_ist_mariabackup : MDEV-18829 Galera test galera_ist_mariabackup leaves port open causing following SST tests to fail +galera.galera_gcache_recover_manytrx : MDEV-18834 Galera test failure on galera_gcache_recover_manytrx diff --git a/mysql-test/suite/galera/r/galera_many_rows.result b/mysql-test/suite/galera/r/galera_many_rows.result index e650dd6f5de..6f441d9b401 100644 --- a/mysql-test/suite/galera/r/galera_many_rows.result +++ b/mysql-test/suite/galera/r/galera_many_rows.result @@ -1,27 +1,27 @@ SET SESSION innodb_lock_wait_timeout=600; SET SESSION lock_wait_timeout=600; -CREATE TABLE ten (f1 INTEGER); +CREATE TABLE ten (f1 INTEGER) engine=InnoDB; INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT PRIMARY KEY, f2 INTEGER) Engine=InnoDB; INSERT INTO t1 (f2) SELECT a1.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5; SET SESSION wsrep_sync_wait = 0; SET SESSION wsrep_sync_wait = 15; SET GLOBAL wsrep_provider_options = 'repl.causal_read_timeout=PT1H'; -SELECT COUNT(*) = 100000 FROM t1; -COUNT(*) = 100000 -1 +SELECT COUNT(*) FROM t1; +COUNT(*) +100000 INSERT INTO t1 (f2) SELECT a1.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5; -SELECT COUNT(*) = 200000 FROM t1; -COUNT(*) = 200000 -1 +SELECT COUNT(*) FROM t1; +COUNT(*) +200000 UPDATE t1 SET f2 = 1; -SELECT COUNT(*) = 200000 FROM t1 WHERE f2 = 1; -COUNT(*) = 200000 -1 +SELECT COUNT(*) FROM t1 WHERE f2 = 1; +COUNT(*) +200000 START TRANSACTION; -SELECT COUNT(*) = 200000 FROM t1; -COUNT(*) = 200000 -1 +SELECT COUNT(*) FROM t1; +COUNT(*) +200000 UPDATE t1 SET f2 = 3; START TRANSACTION; UPDATE t1 SET f2 = 4; diff --git a/mysql-test/suite/galera/t/galera_kill_nochanges.test b/mysql-test/suite/galera/t/galera_kill_nochanges.test index 4106378885f..9360ad542f6 100644 --- a/mysql-test/suite/galera/t/galera_kill_nochanges.test +++ b/mysql-test/suite/galera/t/galera_kill_nochanges.test @@ -3,7 +3,11 @@ # --source include/galera_cluster.inc ---source include/have_innodb.inc + +# Save original auto_increment_offset values. +--let $node_1=node_1 +--let $node_2=node_2 +--source include/auto_increment_offset_save.inc --connection node_1 CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; @@ -29,4 +33,7 @@ SET SESSION wsrep_sync_wait = DEFAULT; SELECT COUNT(*) = 1 FROM t1; SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +--let $node_2=node_2a +--source include/auto_increment_offset_restore.inc + DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_many_rows.cnf b/mysql-test/suite/galera/t/galera_many_rows.cnf new file mode 100644 index 00000000000..4e1022cf67f --- /dev/null +++ b/mysql-test/suite/galera/t/galera_many_rows.cnf @@ -0,0 +1,5 @@ +!include ../galera_2nodes.cnf + +[mysqld] +innodb-status-output=ON +innodb-status-output-locks=ON diff --git a/mysql-test/suite/galera/t/galera_many_rows.test b/mysql-test/suite/galera/t/galera_many_rows.test index 58ba85e1b9e..bc9e99db8da 100644 --- a/mysql-test/suite/galera/t/galera_many_rows.test +++ b/mysql-test/suite/galera/t/galera_many_rows.test @@ -1,13 +1,16 @@ - --source include/big_test.inc --source include/galera_cluster.inc ---source include/have_innodb.inc + +# Save original auto_increment_offset values. +--let $node_1=node_1 +--let $node_2=node_2 +--source include/auto_increment_offset_save.inc --connection node_1 SET SESSION innodb_lock_wait_timeout=600; SET SESSION lock_wait_timeout=600; -CREATE TABLE ten (f1 INTEGER); +CREATE TABLE ten (f1 INTEGER) engine=InnoDB; INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT PRIMARY KEY, f2 INTEGER) Engine=InnoDB; @@ -20,19 +23,19 @@ SET SESSION wsrep_sync_wait = 15; SET GLOBAL wsrep_provider_options = 'repl.causal_read_timeout=PT1H'; -SELECT COUNT(*) = 100000 FROM t1; +SELECT COUNT(*) FROM t1; INSERT INTO t1 (f2) SELECT a1.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5; --connection node_1 -SELECT COUNT(*) = 200000 FROM t1; +SELECT COUNT(*) FROM t1; UPDATE t1 SET f2 = 1; --connection node_2 -SELECT COUNT(*) = 200000 FROM t1 WHERE f2 = 1; +SELECT COUNT(*) FROM t1 WHERE f2 = 1; --connection node_1 START TRANSACTION; -SELECT COUNT(*) = 200000 FROM t1; +SELECT COUNT(*) FROM t1; UPDATE t1 SET f2 = 3; --connection node_2 @@ -50,5 +53,7 @@ COMMIT; --eval SET GLOBAL wsrep_provider_options = '$wsrep_provider_options_node2'; --enable_query_log +--source include/auto_increment_offset_restore.inc + DROP TABLE t1; DROP TABLE ten; diff --git a/mysql-test/suite/galera/t/galera_var_dirty_reads.test b/mysql-test/suite/galera/t/galera_var_dirty_reads.test index 1f01c4aac07..3e2108868af 100644 --- a/mysql-test/suite/galera/t/galera_var_dirty_reads.test +++ b/mysql-test/suite/galera/t/galera_var_dirty_reads.test @@ -11,11 +11,6 @@ --let $node_2=node_2 --source include/auto_increment_offset_save.inc -# Save original auto_increment_offset values. ---let $node_1=node_1 ---let $node_2=node_2 ---source include/auto_increment_offset_save.inc - --connection node_2 --let $wsrep_cluster_address_saved = `SELECT @@global.wsrep_cluster_address` diff --git a/scripts/wsrep_sst_mariabackup.sh b/scripts/wsrep_sst_mariabackup.sh index 3fd93a90c1e..b05e99ada36 100644 --- a/scripts/wsrep_sst_mariabackup.sh +++ b/scripts/wsrep_sst_mariabackup.sh @@ -1,6 +1,6 @@ #!/bin/bash -ue # Copyright (C) 2013 Percona Inc -# Copyright (C) 2017 MariaDB +# Copyright (C) 2017-2019 MariaDB # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -22,6 +22,7 @@ . $(dirname $0)/wsrep_sst_common +OS=$(uname) ealgo="" ekey="" ekeyfile="" @@ -76,13 +77,13 @@ sdecomp="" # 5.6.21 PXC and later can't donate to an older joiner sst_ver=1 -if which pv &>/dev/null && pv --help | grep -q FORMAT;then +if pv --help 2>/dev/null | grep -q FORMAT;then pvopts+=$pvformat fi pcmd="pv $pvopts" declare -a RC -INNOBACKUPEX_BIN=mariabackup +INNOBACKUPEX_BIN=$(which mariabackup) XBSTREAM_BIN=mbstream XBCRYPT_BIN=xbcrypt # Not available in MariaBackup @@ -174,10 +175,8 @@ get_transfer() fi if [[ $tfmt == 'nc' ]];then - if [[ ! -x `which nc` ]];then - wsrep_log_error "nc(netcat) not found in path: $PATH" - exit 2 - fi + wsrep_check_programs nc + wsrep_log_info "Using netcat as streamer" if [[ "$WSREP_SST_OPT_ROLE" == "joiner" ]];then if nc -h 2>&1 | grep -q ncat;then @@ -204,11 +203,8 @@ get_transfer() fi else tfmt='socat' + wsrep_check_programs socat wsrep_log_info "Using socat as streamer" - if [[ ! -x `which socat` ]];then - wsrep_log_error "socat not found in path: $PATH" - exit 2 - fi if [[ $encrypt -eq 2 || $encrypt -eq 3 ]] && ! socat -V | grep -q "WITH_OPENSSL 1";then wsrep_log_error "Encryption requested, but socat is not OpenSSL enabled (encrypt=$encrypt)" @@ -297,7 +293,7 @@ get_footprint() adjust_progress() { - if [[ ! -x `which pv` ]];then + if ! command -v pv >/dev/null;then wsrep_log_error "pv not found in path: $PATH" wsrep_log_error "Disabling all progress/rate-limiting" pcmd="" @@ -336,6 +332,7 @@ read_cnf() rebuild=$(parse_cnf sst rebuild 0) ttime=$(parse_cnf sst time 0) cpat=$(parse_cnf sst cpat '.*galera\.cache$\|.*sst_in_progress$\|.*\.sst$\|.*gvwstate\.dat$\|.*grastate\.dat$\|.*\.err$\|.*\.log$\|.*RPM_UPGRADE_MARKER$\|.*RPM_UPGRADE_HISTORY$') + [[ $OS == "FreeBSD" ]] && cpat=$(parse_cnf sst cpat '.*galera\.cache$|.*sst_in_progress$|.*\.sst$|.*gvwstate\.dat$|.*grastate\.dat$|.*\.err$|.*\.log$|.*RPM_UPGRADE_MARKER$|.*RPM_UPGRADE_HISTORY$') ealgo=$(parse_cnf xtrabackup encrypt "") ekey=$(parse_cnf xtrabackup encrypt-key "") ekeyfile=$(parse_cnf xtrabackup encrypt-key-file "") @@ -550,8 +547,12 @@ wait_for_listen() local MODULE=$3 for i in {1..50} do - ss -p state listening "( sport = :$PORT )" | grep -qE 'socat|nc' && break - sleep 0.2 + if [ "$OS" = "FreeBSD" ];then + sockstat -46lp $PORT | grep -qE "^[^ ]* *(socat|nc) *[^ ]* *[^ ]* *[^ ]* *[^ ]*:$PORT" && break + else + ss -p state listening "( sport = :$PORT )" | grep -qE 'socat|nc' && break + fi + sleep 0.2 done echo "ready ${ADDR}/${MODULE}//$sst_ver" } @@ -597,7 +598,7 @@ recv_joiner() pushd ${dir} 1>/dev/null set +e - if [[ $tmt -gt 0 && -x `which timeout` ]];then + if [[ $tmt -gt 0 ]] && command -v timeout >/dev/null;then if timeout --help | grep -q -- '-k';then ltcmd="timeout -k $(( tmt+10 )) $tmt $tcmd" else @@ -662,13 +663,13 @@ monitor_process() while true ; do - if ! ps --pid "${WSREP_SST_OPT_PARENT}" &>/dev/null; then + if ! ps -p "${WSREP_SST_OPT_PARENT}" &>/dev/null; then wsrep_log_error "Parent mysqld process (PID:${WSREP_SST_OPT_PARENT}) terminated unexpectedly." kill -- -"${WSREP_SST_OPT_PARENT}" exit 32 fi - if ! ps --pid "${sst_stream_pid}" &>/dev/null; then + if ! ps -p "${sst_stream_pid}" &>/dev/null; then break fi @@ -677,10 +678,7 @@ monitor_process() done } -if [[ ! -x `which $INNOBACKUPEX_BIN` ]];then - wsrep_log_error "${INNOBACKUPEX_BIN} not in path: $PATH" - exit 2 -fi +wsrep_check_programs "$INNOBACKUPEX_BIN" rm -f "${MAGIC_FILE}" @@ -704,9 +702,33 @@ fi INNOEXTRA="" +INNODB_DATA_HOME_DIR=${INNODB_DATA_HOME_DIR:-""} +# Try to set INNODB_DATA_HOME_DIR from the command line: +if [ ! -z "$INNODB_DATA_HOME_DIR_ARG" ]; then + INNODB_DATA_HOME_DIR=$INNODB_DATA_HOME_DIR_ARG +fi +# if INNODB_DATA_HOME_DIR env. variable is not set, try to get it from my.cnf +if [ -z "$INNODB_DATA_HOME_DIR" ]; then + INNODB_DATA_HOME_DIR=$(parse_cnf mysqld$WSREP_SST_OPT_SUFFIX_VALUE innodb-data-home-dir '') +fi +if [ -z "$INNODB_DATA_HOME_DIR" ]; then + INNODB_DATA_HOME_DIR=$(parse_cnf --mysqld innodb-data-home-dir "") +fi +if [ ! -z "$INNODB_DATA_HOME_DIR" ]; then + INNOEXTRA+=" --innodb-data-home-dir=$INNODB_DATA_HOME_DIR" +fi + +if [ -n "$INNODB_DATA_HOME_DIR" ]; then + # handle both relative and absolute paths + INNODB_DATA_HOME_DIR=$(cd $DATA; mkdir -p "$INNODB_DATA_HOME_DIR"; cd $INNODB_DATA_HOME_DIR; pwd -P) +else + # default to datadir + INNODB_DATA_HOME_DIR=$(cd $DATA; pwd -P) +fi + if [[ $ssyslog -eq 1 ]];then - if [[ ! -x `which logger` ]];then + if ! command -v logger >/dev/null;then wsrep_log_error "logger not in path: $PATH. Ignoring" else @@ -724,7 +746,7 @@ if [[ $ssyslog -eq 1 ]];then logger -p daemon.info -t ${ssystag}wsrep-sst-$WSREP_SST_OPT_ROLE "$@" } - INNOAPPLY="${INNOBACKUPEX_BIN} --innobackupex $disver $iapts --apply-log \$rebuildcmd \${DATA} 2>&1 | logger -p daemon.err -t ${ssystag}innobackupex-apply " + INNOAPPLY="${INNOBACKUPEX_BIN} --innobackupex $disver $iapts \$INNOEXTRA --apply-log \$rebuildcmd \${DATA} 2>&1 | logger -p daemon.err -t ${ssystag}innobackupex-apply " INNOMOVE="${INNOBACKUPEX_BIN} --innobackupex ${WSREP_SST_OPT_CONF} $disver $impts --move-back --force-non-empty-directories \${DATA} 2>&1 | logger -p daemon.err -t ${ssystag}innobackupex-move " INNOBACKUP="${INNOBACKUPEX_BIN} --innobackupex ${WSREP_SST_OPT_CONF} $disver $iopts \$tmpopts \$INNOEXTRA --galera-info --stream=\$sfmt \$itmpdir 2> >(logger -p daemon.err -t ${ssystag}innobackupex-backup)" fi @@ -733,8 +755,8 @@ else if [[ "$sstlogarchive" -eq 1 ]] then - ARCHIVETIMESTAMP=$(date "+%Y.%m.%d-%H.%M.%S") - newfile="" + ARCHIVETIMESTAMP=$(date "+%Y.%m.%d-%H.%M.%S.%N") + newfile="" if [[ ! -z "$sstlogarchivedir" ]] then @@ -788,7 +810,7 @@ then fi - INNOAPPLY="${INNOBACKUPEX_BIN} --innobackupex $disver $iapts --apply-log \$rebuildcmd \${DATA} &> ${INNOAPPLYLOG}" + INNOAPPLY="${INNOBACKUPEX_BIN} --innobackupex $disver $iapts \$INNOEXTRA --apply-log \$rebuildcmd \${DATA} &> ${INNOAPPLYLOG}" INNOMOVE="${INNOBACKUPEX_BIN} --innobackupex ${WSREP_SST_OPT_CONF} $disver $impts --move-back --force-non-empty-directories \${DATA} &> ${INNOMOVELOG}" INNOBACKUP="${INNOBACKUPEX_BIN} --innobackupex ${WSREP_SST_OPT_CONF} $disver $iopts \$tmpopts \$INNOEXTRA --galera-info --stream=\$sfmt \$itmpdir 2> ${INNOBACKUPLOG}" fi @@ -809,7 +831,7 @@ then exit 93 fi - if [[ -z $(parse_cnf mysqld tmpdir "") && -z $(parse_cnf xtrabackup tmpdir "") ]];then + if [[ -z $(parse_cnf --mysqld tmpdir "") && -z $(parse_cnf xtrabackup tmpdir "") ]];then xtmpdir=$(mktemp -d) tmpopts=" --tmpdir=$xtmpdir " wsrep_log_info "Using $xtmpdir as xtrabackup temporary directory" @@ -931,9 +953,9 @@ then [[ -e $SST_PROGRESS_FILE ]] && wsrep_log_info "Stale sst_in_progress file: $SST_PROGRESS_FILE" [[ -n $SST_PROGRESS_FILE ]] && touch $SST_PROGRESS_FILE - ib_home_dir=$(parse_cnf mysqld innodb-data-home-dir "") - ib_log_dir=$(parse_cnf mysqld innodb-log-group-home-dir "") - ib_undo_dir=$(parse_cnf mysqld innodb-undo-directory "") + ib_home_dir=$INNODB_DATA_HOME_DIR + ib_log_dir=$(parse_cnf --mysqld innodb-log-group-home-dir "") + ib_undo_dir=$(parse_cnf --mysqld innodb-undo-directory "") stagemsg="Joiner-Recv" @@ -1003,11 +1025,14 @@ then jpid=$! wsrep_log_info "Proceeding with SST" - wsrep_log_info "Cleaning the existing datadir and innodb-data/log directories" - find $ib_home_dir $ib_log_dir $ib_undo_dir $DATA -mindepth 1 -regex $cpat -prune -o -exec rm -rfv {} 1>&2 \+ + if [ "${OS}" = "FreeBSD" ]; then + find -E $ib_home_dir $ib_log_dir $ib_undo_dir $DATA -mindepth 1 -prune -regex $cpat -o -exec rm -rfv {} 1>&2 \+ + else + find $ib_home_dir $ib_log_dir $ib_undo_dir $DATA -mindepth 1 -prune -regex $cpat -o -exec rm -rfv {} 1>&2 \+ + fi - tempdir=$(parse_cnf mysqld log-bin "") + tempdir=$(parse_cnf --mysqld log-bin "") if [[ -n ${tempdir:-} ]];then binlog_dir=$(dirname $tempdir) binlog_file=$(basename $tempdir) @@ -1052,7 +1077,7 @@ then wsrep_log_info "Compressed qpress files found" - if [[ ! -x `which qpress` ]];then + if ! command -v qpress >/dev/null;then wsrep_log_error "qpress not found in path: $PATH" exit 22 fi

1 0

[Commits] 2e3c5d8: MDEV-18816 Assertion `sel->quick' failed in JOIN::make_range_rowid_filters
by IgorBabaev 06 Mar '19

06 Mar '19

revision-id: 2e3c5d8ab252cd0d64cab8000ff7f384766ae08f (mariadb-10.4.3-34-g2e3c5d8) parent(s): 1bcb66c597b79ed61a945661916ccac71ded0cfa author: Igor Babaev committer: Igor Babaev timestamp: 2019-03-05 23:42:04 -0800 message: MDEV-18816 Assertion `sel->quick' failed in JOIN::make_range_rowid_filters Do not build range filters with detected impossible WHERE. Anyway conditions cannot be used anymore to extract ranges for filters. --- mysql-test/main/rowid_filter.result | 33 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter.test | 31 ++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.result | 33 ++++++++++++++++++++++++++++++ sql/sql_select.cc | 8 ++++++++ 4 files changed, 105 insertions(+) diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index ea7de8c..f025305 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -1941,4 +1941,37 @@ ALTER TABLE orders DROP COLUMN o_totaldiscount; DROP VIEW v1; DROP DATABASE dbt3_s001; use test; +# +# MDEV-18816: potential range filter for one join table with +# impossible WHERE for another +# +create table t1 ( +pk int not null primary key, c2 varchar(10) , i1 int,key (c2) +) engine=myisam; +insert into t1 values (1,'a',-5),(2,'a',null); +create table t2 ( +pk int, i1 int, c1 varchar(30) , key c1 (c1(30)), key i1 (i1) +) engine=myisam; +insert into t2 values +(1,-5,'a'),(2,null,'a'),(3,null,'a'),(4,null,'a'),(5,5,'a'),(6,null,'a'), +(7,4,'a'),(8,55,'a'),(9,null,'a'),(10,null,'a'),(11,null,'a'),(12,-5,'a'), +(13,-5,'a'),(14,null,'a'),(15,null,'a'),(16,-5,'a'),(17,-5,'a'); +select 1 +from t1 +left join +t2 join t1 as t1_a on t2.i1 = t1_a.pk +on t1.c2 = t2.c1 +where t1_a.pk is null and t1_a.i1 != 3; +1 +explain extended select 1 +from t1 +left join +t2 join t1 as t1_a on t2.i1 = t1_a.pk +on t1.c2 = t2.c1 +where t1_a.pk is null and t1_a.i1 != 3; +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 1 AS `1` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t1_a` where 0 +drop table t1,t2; set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index a674021..87e8b2c 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -230,4 +230,35 @@ DROP DATABASE dbt3_s001; use test; +--echo # +--echo # MDEV-18816: potential range filter for one join table with +--echo # impossible WHERE for another +--echo # + +create table t1 ( + pk int not null primary key, c2 varchar(10) , i1 int,key (c2) +) engine=myisam; +insert into t1 values (1,'a',-5),(2,'a',null); + +create table t2 ( + pk int, i1 int, c1 varchar(30) , key c1 (c1(30)), key i1 (i1) +) engine=myisam; +insert into t2 values + (1,-5,'a'),(2,null,'a'),(3,null,'a'),(4,null,'a'),(5,5,'a'),(6,null,'a'), + (7,4,'a'),(8,55,'a'),(9,null,'a'),(10,null,'a'),(11,null,'a'),(12,-5,'a'), + (13,-5,'a'),(14,null,'a'),(15,null,'a'),(16,-5,'a'),(17,-5,'a'); + +let $q= +select 1 + from t1 + left join + t2 join t1 as t1_a on t2.i1 = t1_a.pk + on t1.c2 = t2.c1 +where t1_a.pk is null and t1_a.i1 != 3; + +eval $q; +eval explain extended $q; + +drop table t1,t2; + set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index cd09f1d..ac7ca11 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1870,6 +1870,39 @@ ALTER TABLE orders DROP COLUMN o_totaldiscount; DROP VIEW v1; DROP DATABASE dbt3_s001; use test; +# +# MDEV-18816: potential range filter for one join table with +# impossible WHERE for another +# +create table t1 ( +pk int not null primary key, c2 varchar(10) , i1 int,key (c2) +) engine=myisam; +insert into t1 values (1,'a',-5),(2,'a',null); +create table t2 ( +pk int, i1 int, c1 varchar(30) , key c1 (c1(30)), key i1 (i1) +) engine=myisam; +insert into t2 values +(1,-5,'a'),(2,null,'a'),(3,null,'a'),(4,null,'a'),(5,5,'a'),(6,null,'a'), +(7,4,'a'),(8,55,'a'),(9,null,'a'),(10,null,'a'),(11,null,'a'),(12,-5,'a'), +(13,-5,'a'),(14,null,'a'),(15,null,'a'),(16,-5,'a'),(17,-5,'a'); +select 1 +from t1 +left join +t2 join t1 as t1_a on t2.i1 = t1_a.pk +on t1.c2 = t2.c1 +where t1_a.pk is null and t1_a.i1 != 3; +1 +explain extended select 1 +from t1 +left join +t2 join t1 as t1_a on t2.i1 = t1_a.pk +on t1.c2 = t2.c1 +where t1_a.pk is null and t1_a.i1 != 3; +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 1 AS `1` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t1_a` where 0 +drop table t1,t2; set @@use_stat_tables=@save_use_stat_tables; # # MDEV-18755: possible RORI-plan and possible plan with range filter diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 211898f..8c75f4d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1581,6 +1581,13 @@ bool JOIN::make_range_rowid_filters() { DBUG_ENTER("make_range_rowid_filters"); + /* + Do not build range filters with detected impossible WHERE. + Anyway conditions cannot be used anymore to extract ranges for filters. + */ + if (const_table_map != found_const_table_map) + DBUG_RETURN(0); + JOIN_TAB *tab; for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); @@ -7051,6 +7058,7 @@ void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key) // join->positions[idx].loosescan_key= MAX_KEY; /* Not a LooseScan */ join->positions[idx].sj_strategy= SJ_OPT_NONE; join->positions[idx].use_join_buffer= FALSE; + join->positions[idx].range_rowid_filter_info= 0; /* Move the const table as down as possible in best_ref */ JOIN_TAB **pos=join->best_ref+idx+1;

1 0

[Commits] 0d6bf4d98fe: Use rocksdb+range_locking that was submitted as PR
by Sergei Petrunia 05 Mar '19

05 Mar '19

revision-id: 0d6bf4d98febd26b290430ad796e63ce3bce9a3c (fb-prod201801-217-g0d6bf4d98fe) parent(s): c8a8a09f0a88e110ec0382af13825ffd3e73ce48 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-03-05 19:05:51 +0300 message: Use rocksdb+range_locking that was submitted as PR --- rocksdb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/rocksdb b/rocksdb index cf25bb5ccb4..c0a1babb157 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit cf25bb5ccb48e30756b2d1236636f2154ce5ec3b +Subproject commit c0a1babb157b83b1eb41820fd7652e23b81f8c48

1 0