revision-id: 44af33cfe8abf11d37e7277ee33ad968a6e7f9d1 (mariadb-10.2.31-693-g44af33c) parent(s): b22285e4821b49546de9b88990bbc9c453dc14b2 author: Igor Babaev committer: Igor Babaev timestamp: 2021-01-29 18:17:37 -0800 message: MDEV-21104 Wrong result (extra rows and wrong values) with incremental BNLH This bug could affect multi-way join queries with embedded outer joins that contained a conjunctive IS NULL predicate over a non-nullable column from inner table of an outer join. The predicate could occur in WHERE condition or in ON condition. Due to this bug a wrong result set could be returned by the query. The bug manifested itself only when join buffers were employed for join operations. The problem appeared because - a bug in the function JOIN_CACHE::get_match_flag_by_pos that not always returned proper match flags for embedding outer joins stored together with table rows put a join buffer. - bug in the function JOIN_CACHE::join_matching_records that not always correctly determined that a row from the buffer could be skipped due to applied 'not_exists' optimization. --- mysql-test/r/join_cache.result | 53 ++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/join_cache.test | 36 ++++++++++++++++++++++++++++ sql/sql_join_cache.cc | 8 +++++-- 3 files changed, 95 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index e41c79a..87c4079 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -6054,4 +6054,57 @@ select f2 from t2,t1 where f2 = 0; f2 drop table t1, t2; set join_buffer_size=@save_join_buffer_size; +# +# MDEV-21104: BNLH used for multi-join query with embedded outer join +# and possible 'not exists' optimization +# +set join_cache_level=4; +CREATE TABLE t1 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int, c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,2),(2,4); +CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t4 (e int primary key) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1),(2); +ANALYZE TABLE t1,t2,t3,t4; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +test.t4 analyze status OK +SELECT * FROM t2 LEFT JOIN t3 ON c = d; +b c d +1 2 2 +2 4 NULL +SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4; +b c d e +1 2 2 1 +2 4 NULL 1 +1 2 2 2 +2 4 NULL 2 +EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 hash_index d #hash#d:d 5:5 test.t2.c 2 Using where; Using index; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.b 2 Using index; Using join buffer (incremental, BNLH join) +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e; +a b c d e +1 1 2 2 1 +2 1 2 2 1 +1 2 4 NULL 2 +2 2 4 NULL 2 +EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e +WHERE e IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 hash_index d #hash#d:d 5:5 test.t2.c 2 Using where; Using index; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.b 2 Using where; Using index; Not exists; Using join buffer (incremental, BNLH join) +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e +WHERE e IS NULL; +a b c d e +DROP TABLE t1,t2,t3,t4; +set join_cache_level=@save_join_cache_level; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 9576d59..15cd1e9 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -4014,5 +4014,41 @@ select f2 from t2,t1 where f2 = 0; drop table t1, t2; set join_buffer_size=@save_join_buffer_size; + +--echo # +--echo # MDEV-21104: BNLH used for multi-join query with embedded outer join +--echo # and possible 'not exists' optimization +--echo # + +set join_cache_level=4; + +CREATE TABLE t1 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int, c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,2),(2,4); +CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t4 (e int primary key) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1),(2); +ANALYZE TABLE t1,t2,t3,t4; + +SELECT * FROM t2 LEFT JOIN t3 ON c = d; +SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4; + +let $q1= +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e; +eval EXPLAIN $q1; +eval $q1; + +let $q2= +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e + WHERE e IS NULL; +eval EXPLAIN $q2; +eval $q2; + +DROP TABLE t1,t2,t3,t4; + +set join_cache_level=@save_join_cache_level; + # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index 1dfc938..7a5f172 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -1669,7 +1669,8 @@ void JOIN_CACHE::get_record_by_pos(uchar *rec_ptr) enum JOIN_CACHE::Match_flag JOIN_CACHE::get_match_flag_by_pos(uchar *rec_ptr) { Match_flag match_fl= MATCH_NOT_FOUND; - if (with_match_flag) + if (with_match_flag && + (!join_tab->first_unmatched || join_tab == join_tab->first_unmatched)) { match_fl= (enum Match_flag) rec_ptr[0]; return match_fl; @@ -2221,7 +2222,10 @@ enum_nested_loop_state JOIN_CACHE::join_matching_records(bool skip_last) int error; enum_nested_loop_state rc= NESTED_LOOP_OK; join_tab->table->null_row= 0; - bool check_only_first_match= join_tab->check_only_first_match(); + bool check_only_first_match= + join_tab->check_only_first_match() && + (!join_tab->first_inner || // semi-join case + join_tab->first_inner == join_tab->first_unmatched); // outer join case bool outer_join_first_inner= join_tab->is_first_inner_for_outer_join(); DBUG_ENTER("JOIN_CACHE::join_matching_records");