revision-id: f3dda8e2002dc2ad630c07bda0d8c00be7269907 (mariadb-10.2.14-72-gf3dda8e) parent(s): b4c5e4a717e3ce2c2d434106cc74417fe9a1d3dc author: Igor Babaev committer: Igor Babaev timestamp: 2018-04-30 21:26:38 -0700 message: MDEV-15357 Wrong result with join_cache_level=4, BNLH join This bug was introduced by the architectural changes of the patch for MDEV-11640. The patch moved initialization of join caches after the call of make_join_readinfo(). As any failure to initialize a join cache caused denial of its usage the execution code for the query had to be revised. This revision required rolling back many actions taken by make_join_readinfo(). It was not done in the patch. As a result if a denial of join cache happened some join conditions could be lost. This was exactly the cause of wrong results in the bug's reported test case. Thus the introduced architectural change is not valid and it would be better to roll it back. At the same time two new methods adjust_read_set_for_vcol_keyread() and get_covering_index_for_scan() were added to the class JOIN_TAB to resolve the problems of MDEV-11640. --- mysql-test/r/analyze_format_json.result | 1 - mysql-test/r/join_cache.result | 62 +++++++++++++ mysql-test/r/limit_rows_examined.result | 24 +++-- mysql-test/t/join_cache.test | 47 ++++++++++ mysql-test/t/limit_rows_examined.test | 1 + sql/sql_join_cache.cc | 12 ++- sql/sql_select.cc | 150 ++++++++++++++++++-------------- sql/sql_select.h | 4 +- 8 files changed, 218 insertions(+), 83 deletions(-) diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result index 307fb73..c8bb615 100644 --- a/mysql-test/r/analyze_format_json.result +++ b/mysql-test/r/analyze_format_json.result @@ -626,7 +626,6 @@ ANALYZE "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", - "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))", "r_filtered": null }, "subqueries": [ diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index eea3974..4f57266 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -6016,4 +6016,66 @@ select f2 from t2,t1 where f2 = 0; f2 drop table t1, t2; set join_buffer_size = default; +# +# MDEV-15357: join cache denied: optimize_join_buffer_size=off and +# join_buffer_size > join_buffer_space_limit +# +CREATE TABLE t1 ( +id varchar(30) NOT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('ZSBILL1000'),('ZSBILL1010'),('ZSBILL1020'),('ZSBILL1030'),('ZSBILL2000'), +('ZSBILL2010'),('ZSBILL2020'),('ZSBILL6040'),('ZSBILL6050'),('ZSBILL7000'); +CREATE TABLE t2 ( +id varchar(30) NOT NULL, +a char(1) NOT NULL DEFAULT '3', +PRIMARY KEY (id,a) +) ENGINE=MyISAM; +INSERT INTO t2(id) VALUES +('ZSBILL1000'),('ZSBILL1010'),('ZSBILL1020'),('ZSBILL1030'),('ZSBILL2000'), +('ZSBILL2010'),('ZSBILL2020'),('ZSBILL2030'),('ZSBILL3000'); +SET join_buffer_size=30000000; +SET join_cache_level=4; +SELECT @@join_buffer_space_limit; +@@join_buffer_space_limit +2097152 +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='optimize_join_buffer_size=on'; +EXPLAIN EXTENDED SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index PRIMARY PRIMARY 33 NULL 9 100.00 Using index +1 SIMPLE t1 hash_index PRIMARY #hash#PRIMARY:PRIMARY 32:32 test.t2.id 10 10.00 Using index; Using join buffer (flat, BNLH join) +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`id` = `test`.`t2`.`id` +SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id; +id id +ZSBILL1000 ZSBILL1000 +ZSBILL1010 ZSBILL1010 +ZSBILL1020 ZSBILL1020 +ZSBILL1030 ZSBILL1030 +ZSBILL2000 ZSBILL2000 +ZSBILL2010 ZSBILL2010 +ZSBILL2020 ZSBILL2020 +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='optimize_join_buffer_size=off'; +EXPLAIN EXTENDED SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index PRIMARY PRIMARY 33 NULL 9 100.00 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 32 test.t2.id 1 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`id` = `test`.`t2`.`id` +SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id; +id id +ZSBILL1000 ZSBILL1000 +ZSBILL1010 ZSBILL1010 +ZSBILL1020 ZSBILL1020 +ZSBILL1030 ZSBILL1030 +ZSBILL2000 ZSBILL2000 +ZSBILL2010 ZSBILL2010 +ZSBILL2020 ZSBILL2020 +SET join_buffer_size=default; +SET join_cache_level=default; +set optimizer_switch= @tmp_optimizer_switch; +DROP TABLE t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result index 2b5bca2..dd1c971 100644 --- a/mysql-test/r/limit_rows_examined.result +++ b/mysql-test/r/limit_rows_examined.result @@ -750,24 +750,22 @@ FROM t1, t2 AS alias2, t2 AS alias3 WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) GROUP BY field1, field2, field3, field4, field5 LIMIT ROWS EXAMINED 120; -field1 field2 field3 field4 field5 -Warnings: -Warning 1931 Query execution was interrupted. The query examined at least 121 rows, which exceeds LIMIT ROWS EXAMINED (120). The query result may be incomplete +ERROR HY000: Sort aborted: SHOW STATUS LIKE 'Handler_read%'; Variable_name Value Handler_read_first 1 -Handler_read_key 4 +Handler_read_key 5 Handler_read_last 0 -Handler_read_next 4 +Handler_read_next 7 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 -Handler_read_rnd_next 46 +Handler_read_rnd_next 38 SHOW STATUS LIKE 'Handler_tmp%'; Variable_name Value Handler_tmp_update 0 -Handler_tmp_write 66 +Handler_tmp_write 70 FLUSH STATUS; SELECT a AS field1, alias2.d AS field2, alias2.f AS field3, alias2.e AS field4, b AS field5 FROM t1, t2 AS alias2, t2 AS alias3 @@ -775,20 +773,20 @@ WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) GROUP BY field1, field2, field3, field4, field5 LIMIT ROWS EXAMINED 124; field1 field2 field3 field4 field5 +00:21:38 06:07:10 a 2007-06-08 04:35:26 2007-05-28 00:00:00 Warnings: Warning 1931 Query execution was interrupted. The query examined at least 125 rows, which exceeds LIMIT ROWS EXAMINED (124). The query result may be incomplete -Warning 1931 Query execution was interrupted. The query examined at least 127 rows, which exceeds LIMIT ROWS EXAMINED (124). The query result may be incomplete SHOW STATUS LIKE 'Handler_read%'; Variable_name Value Handler_read_first 1 -Handler_read_key 4 +Handler_read_key 5 Handler_read_last 0 -Handler_read_next 4 +Handler_read_next 7 Handler_read_prev 0 Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 48 +Handler_read_rnd 2 +Handler_read_rnd_deleted 1 +Handler_read_rnd_next 39 SHOW STATUS LIKE 'Handler_tmp%'; Variable_name Value Handler_tmp_update 0 diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index b775725..5356341 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3957,6 +3957,53 @@ select f2 from t2,t1 where f2 = 0; drop table t1, t2; set join_buffer_size = default; +--echo # +--echo # MDEV-15357: join cache denied: optimize_join_buffer_size=off and +--echo # join_buffer_size > join_buffer_space_limit +--echo # + +CREATE TABLE t1 ( + id varchar(30) NOT NULL, + PRIMARY KEY (id) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('ZSBILL1000'),('ZSBILL1010'),('ZSBILL1020'),('ZSBILL1030'),('ZSBILL2000'), +('ZSBILL2010'),('ZSBILL2020'),('ZSBILL6040'),('ZSBILL6050'),('ZSBILL7000'); + +CREATE TABLE t2 ( + id varchar(30) NOT NULL, + a char(1) NOT NULL DEFAULT '3', + PRIMARY KEY (id,a) +) ENGINE=MyISAM; +INSERT INTO t2(id) VALUES +('ZSBILL1000'),('ZSBILL1010'),('ZSBILL1020'),('ZSBILL1030'),('ZSBILL2000'), +('ZSBILL2010'),('ZSBILL2020'),('ZSBILL2030'),('ZSBILL3000'); + +SET join_buffer_size=30000000; +SET join_cache_level=4; + +SELECT @@join_buffer_space_limit; + +let $q= +SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id; + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='optimize_join_buffer_size=on'; +eval EXPLAIN EXTENDED $q; +eval $q; + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='optimize_join_buffer_size=off'; +eval EXPLAIN EXTENDED $q; +eval $q; + +SET join_buffer_size=default; +SET join_cache_level=default; + +set optimizer_switch= @tmp_optimizer_switch; + +DROP TABLE t1,t2; + # The following command must be the last one the file # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/limit_rows_examined.test b/mysql-test/t/limit_rows_examined.test index 815394a..29b3b41 100644 --- a/mysql-test/t/limit_rows_examined.test +++ b/mysql-test/t/limit_rows_examined.test @@ -489,6 +489,7 @@ GROUP BY field1, field2, field3, field4, field5 LIMIT ROWS EXAMINED 120; FLUSH STATUS; +--error 1028 SELECT a AS field1, alias2.d AS field2, alias2.f AS field3, alias2.e AS field4, b AS field5 FROM t1, t2 AS alias2, t2 AS alias3 WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index 3612cb6..242cb91 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -226,6 +226,16 @@ void JOIN_CACHE::calc_record_fields() flag_fields+= MY_TEST(tab->table->maybe_null); fields+= tab->used_fields; blobs+= tab->used_blobs; + if (tab->type == JT_ALL || tab->type == JT_HASH) + { + uint idx= tab->get_covering_index_for_scan(); + if (idx != MAX_KEY) + { + fields-= bitmap_bits_set(tab->table->read_set); + tab->adjust_read_set_for_vcol_keyread(idx); + fields+= bitmap_bits_set(tab->table->read_set); + } + } } if ((with_match_flag= join_tab->use_match_flag())) flag_fields++; @@ -596,7 +606,7 @@ void JOIN_CACHE::create_remaining_fields() bitmap_invert(&table->tmp_set); bitmap_intersect(&table->tmp_set, table->read_set); rem_field_set= &table->tmp_set; - } + } length+= add_table_data_fields_to_join_cache(tab, rem_field_set, &data_field_count, ©, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 30ef26f..e5b75ec 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1143,36 +1143,6 @@ int JOIN::optimize() } -int JOIN::init_join_caches() -{ - JOIN_TAB *tab; - - for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); - tab; - tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) - { - TABLE *table= tab->table; - if (table->file->keyread_enabled()) - { - if (!(table->file->index_flags(table->file->keyread, 0, 1) & HA_CLUSTERED_INDEX)) - table->mark_columns_used_by_index(table->file->keyread, table->read_set); - } - else if ((tab->read_first_record == join_read_first || - tab->read_first_record == join_read_last) && - !tab->filesort && table->covering_keys.is_set(tab->index) && - !table->no_keyread) - { - table->prepare_for_keyread(tab->index, table->read_set); - } - if (tab->cache && tab->cache->init(select_options & SELECT_DESCRIBE)) - revise_cache_usage(tab); - else - tab->remove_redundant_bnl_scan_conds(); - } - return 0; -} - - /** global select optimisation. @@ -2189,9 +2159,6 @@ JOIN::optimize_inner() if (make_aggr_tables_info()) DBUG_RETURN(1); - if (init_join_caches()) - DBUG_RETURN(1); - error= 0; if (select_options & SELECT_DESCRIBE) @@ -11142,7 +11109,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, case JT_ALL: if (cache_level == 1) prev_cache= 0; - if ((tab->cache= new (root) JOIN_CACHE_BNL(join, tab, prev_cache))) + if ((tab->cache= new (root) JOIN_CACHE_BNL(join, tab, prev_cache)) && + !tab->cache->init(options & SELECT_DESCRIBE)) { tab->icp_other_tables_ok= FALSE; return (2 - MY_TEST(!prev_cache)); @@ -11176,7 +11144,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, goto no_join_cache; if (cache_level == 3) prev_cache= 0; - if ((tab->cache= new (root) JOIN_CACHE_BNLH(join, tab, prev_cache))) + if ((tab->cache= new (root) JOIN_CACHE_BNLH(join, tab, prev_cache)) && + !tab->cache->init(options & SELECT_DESCRIBE)) { tab->icp_other_tables_ok= FALSE; return (4 - MY_TEST(!prev_cache)); @@ -11196,7 +11165,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, { if (cache_level == 5) prev_cache= 0; - if ((tab->cache= new (root) JOIN_CACHE_BKA(join, tab, flags, prev_cache))) + if ((tab->cache= new (root) JOIN_CACHE_BKA(join, tab, flags, prev_cache)) && + !tab->cache->init(options & SELECT_DESCRIBE)) return (6 - MY_TEST(!prev_cache)); goto no_join_cache; } @@ -11204,7 +11174,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, { if (cache_level == 7) prev_cache= 0; - if ((tab->cache= new (root) JOIN_CACHE_BKAH(join, tab, flags, prev_cache))) + if ((tab->cache= new (root) JOIN_CACHE_BKAH(join, tab, flags, prev_cache)) && + !tab->cache->init(options & SELECT_DESCRIBE)) { tab->idx_cond_fact_out= FALSE; return (8 - MY_TEST(!prev_cache)); @@ -11404,6 +11375,61 @@ void JOIN_TAB::remove_redundant_bnl_scan_conds() } +uint JOIN_TAB::get_covering_index_for_scan() +{ + int idx= MAX_KEY; + if (table->no_keyread) + return idx; + if (select && select->quick && + select->quick->index != MAX_KEY && //not index_merge + table->covering_keys.is_set(select->quick->index)) + idx= select->quick->index; + else if (!table->covering_keys.is_clear_all() && + !(select && select->quick)) + { // Only read index tree + if (loosescan_match_tab) + idx= loosescan_key; + else + { +#ifdef BAD_OPTIMIZATION + /* + It has turned out that the below change, while speeding things + up for disk-bound loads, slows them down for cases when the data + is in disk cache (see BUG#35850): + See bug #26447: "Using the clustered index for a table scan + is always faster than using a secondary index". + */ + if (table->s->primary_key != MAX_KEY && + table->file->primary_key_is_clustered()) + idx= table->s->primary_key; + else +#endif + idx= find_shortest_key(table, & table->covering_keys); + } + } + return idx; +} + + +void JOIN_TAB::adjust_read_set_for_vcol_keyread(uint keyread_idx) +{ + if (!table->vcol_set || bitmap_is_clear_all(table->vcol_set)) + return; + + MY_BITMAP *keyread_set= &table->cond_set; // is free for use at call + bitmap_clear_all(keyread_set); + table->mark_columns_used_by_index(keyread_idx, keyread_set); + bitmap_intersect(keyread_set, table->read_set); + bitmap_intersect(keyread_set, table->vcol_set); + if (!bitmap_is_clear_all(keyread_set)) + { + bitmap_clear_all(keyread_set); + table->mark_columns_used_by_index(keyread_idx, keyread_set); + bitmap_intersect(table->read_set, keyread_set); + } +} + + /* Plan refinement stage: do various setup things for the executor @@ -11526,7 +11552,10 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) tab->read_first_record= tab->type == JT_SYSTEM ? join_read_system : join_read_const; if (table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) + { + tab->adjust_read_set_for_vcol_keyread(tab->ref.key); table->file->ha_start_keyread(tab->ref.key); + } else if ((!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); break; @@ -11534,7 +11563,10 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) tab->read_record.unlock_row= join_read_key_unlock_row; /* fall through */ if (table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) + { + tab->adjust_read_set_for_vcol_keyread(tab->ref.key); table->file->ha_start_keyread(tab->ref.key); + } else if ((!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); break; @@ -11548,7 +11580,10 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) delete tab->quick; tab->quick=0; if (table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) + { + tab->adjust_read_set_for_vcol_keyread(tab->ref.key); table->file->ha_start_keyread(tab->ref.key); + } else if ((!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); break; @@ -11606,35 +11641,19 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) } } } - if (!table->no_keyread) + uint idx= tab->get_covering_index_for_scan(); + if (idx != MAX_KEY) { if (tab->select && tab->select->quick && tab->select->quick->index != MAX_KEY && //not index_merge table->covering_keys.is_set(tab->select->quick->index)) - table->file->ha_start_keyread(tab->select->quick->index); + table->file->ha_start_keyread(idx); else if (!table->covering_keys.is_clear_all() && !(tab->select && tab->select->quick)) - { // Only read index tree - if (tab->loosescan_match_tab) - tab->index= tab->loosescan_key; - else - { -#ifdef BAD_OPTIMIZATION - /* - It has turned out that the below change, while speeding things - up for disk-bound loads, slows them down for cases when the data - is in disk cache (see BUG#35850): - See bug #26447: "Using the clustered index for a table scan - is always faster than using a secondary index". - */ - if (table->s->primary_key != MAX_KEY && - table->file->primary_key_is_clustered()) - tab->index= table->s->primary_key; - else -#endif - tab->index=find_shortest_key(table, & table->covering_keys); - } + { // Only read index tree + tab->index= idx; tab->read_first_record= join_read_first; + table->file->ha_start_keyread(tab->index); /* Read with index_first / index_next */ tab->type= tab->type == JT_ALL ? JT_NEXT : JT_HASH_NEXT; } @@ -11657,6 +11676,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) /* purecov: end */ } + tab->remove_redundant_bnl_scan_conds(); DBUG_EXECUTE("where", char buff[256]; String str(buff,sizeof(buff),system_charset_info); @@ -19663,9 +19683,8 @@ join_read_first(JOIN_TAB *tab) TABLE *table=tab->table; DBUG_ENTER("join_read_first"); - DBUG_ASSERT(table->no_keyread || - !table->covering_keys.is_set(tab->index) || - table->file->keyread == tab->index); + if (table->covering_keys.is_set(tab->index) && !table->no_keyread) + table->file->ha_start_keyread(tab->index); tab->table->status=0; tab->read_record.read_record=join_read_next; tab->read_record.table=table; @@ -19701,11 +19720,10 @@ join_read_last(JOIN_TAB *tab) { TABLE *table=tab->table; int error= 0; - DBUG_ENTER("join_read_first"); + DBUG_ENTER("join_read_last"); - DBUG_ASSERT(table->no_keyread || - !table->covering_keys.is_set(tab->index) || - table->file->keyread == tab->index); + if (table->covering_keys.is_set(tab->index) && !table->no_keyread) + table->file->ha_start_keyread(tab->index); tab->table->status=0; tab->read_record.read_record=join_read_prev; tab->read_record.table=table; diff --git a/sql/sql_select.h b/sql/sql_select.h index 2003cc1..b87d97e 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -614,7 +614,8 @@ typedef struct st_join_table { bool use_order() const; ///< Use ordering provided by chosen index? bool sort_table(); bool remove_duplicates(); - + uint get_covering_index_for_scan(); + void adjust_read_set_for_vcol_keyread(uint keyread_idx); } JOIN_TAB; @@ -1522,7 +1523,6 @@ class JOIN :public Sql_alloc bool flatten_subqueries(); bool optimize_unflattened_subqueries(); bool optimize_constant_subqueries(); - int init_join_caches(); bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields, bool before_group_by, bool recompute= FALSE);