revision-id: 8b312ab6f5b18f812114a7c23eee12b0ef7d559d (mariadb-10.3.20-49-g8b312ab6f5b) parent(s): e5e5877740f248de848219ee3a1d2881cd5c5b82 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-12-08 00:07:31 +0300 message: MDEV-21243: Join buffer: condition is checked in wrong place for range access In this scenario: - There is a possible range access for table T - And there is a ref access on the same index which uses fewer key parts - The join optimizer picks the ref access (because it is cheaper) - make_join_select applies this heuristic to switch to range: /* Range uses longer key; Use this instead of ref on key */ Join buffer will be used without having called JOIN_TAB::make_scan_filter(). This means, conditions that should be checked when reading table T will be checked after T is joined with the contents of the join buffer, instead. Fixed this by adding a make_scan_filter() check. (updated patch after backport to 10.3) --- mysql-test/main/join_cache.result | 72 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/join_cache.test | 40 ++++++++++++++++++++++ sql/sql_select.cc | 10 ++++++ 3 files changed, 122 insertions(+) diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index bab32395413..3074d3b3cf4 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -6056,4 +6056,76 @@ select f2 from t2,t1 where f2 = 0; f2 drop table t1, t2; set join_buffer_size=@save_join_buffer_size; +# +# MDEV-21243: Join buffer: condition is checked in wrong place for range access +# +create table t1(a int primary key); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int); +insert into t2 select A.a + 10*B.a from t1 A, t1 B; +create table t3 ( +kp1 int, +kp2 int, +col1 int, +col2 int, +key (kp1, kp2) +); +insert into t3 +select +A.a, +B.a, +A.a + 100*B.a, +A.a + 100*B.a +from +t2 A, t2 B; +analyze table t3; +Table Op Msg_type Msg_text +test.t3 analyze status Table is already up to date +# The following must have "B.col1 + 1 < 33333" attached to table B +# and not to the block-nl-join node: +explain format=json +select * +from t1 A, t3 B +where +B.kp1=A.a and +B.kp1 <= 10 and +B.kp2 <= 10 and +B.col1 +1 < 33333; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "A", + "access_type": "index", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["a"], + "rows": 10, + "filtered": 100, + "attached_condition": "A.a <= 10", + "using_index": true + }, + "block-nl-join": { + "table": { + "table_name": "B", + "access_type": "range", + "possible_keys": ["kp1"], + "key": "kp1", + "key_length": "10", + "used_key_parts": ["kp1", "kp2"], + "rows": 836, + "filtered": 11.962, + "index_condition": "B.kp2 <= 10", + "attached_condition": "B.kp2 <= 10 and B.col1 + 1 < 33333" + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "B.kp1 = A.a" + } + } +} +drop table t1,t2,t3; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 9576d598125..248b8dbddf5 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -4014,5 +4014,45 @@ select f2 from t2,t1 where f2 = 0; drop table t1, t2; set join_buffer_size=@save_join_buffer_size; + +--echo # +--echo # MDEV-21243: Join buffer: condition is checked in wrong place for range access +--echo # +create table t1(a int primary key); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int); +insert into t2 select A.a + 10*B.a from t1 A, t1 B; + +create table t3 ( + kp1 int, + kp2 int, + col1 int, + col2 int, + key (kp1, kp2) +); + +insert into t3 +select + A.a, + B.a, + A.a + 100*B.a, + A.a + 100*B.a +from + t2 A, t2 B; +analyze table t3; + +--echo # The following must have "B.col1 + 1 < 33333" attached to table B +--echo # and not to the block-nl-join node: +explain format=json +select * +from t1 A, t3 B +where + B.kp1=A.a and + B.kp1 <= 10 and + B.kp2 <= 10 and + B.col1 +1 < 33333; + +drop table t1,t2,t3; + # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 275c46141cd..fbce8708c79 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10960,6 +10960,13 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) if (i != join->const_tables && tab->use_quick != 2 && !tab->first_inner) { /* Read with cache */ + /* + TODO: the execution also gets here when we will not be using + join buffer. Review these cases and perhaps, remove this call. + (The final decision whether to use join buffer is made in + check_join_cache_usage, so we should only call make_scan_filter() + there, too). + */ if (tab->make_scan_filter()) DBUG_RETURN(1); } @@ -11922,6 +11929,9 @@ uint check_join_cache_usage(JOIN_TAB *tab, if ((tab->cache= new (root) JOIN_CACHE_BNL(join, tab, prev_cache))) { tab->icp_other_tables_ok= FALSE; + /* If make_join_select() hasn't called make_scan_filter(), do it now */ + if (!tab->cache_select && tab->make_scan_filter()) + goto no_join_cache; return (2 - MY_TEST(!prev_cache)); } goto no_join_cache;