From 5af12e463549e4bbc2ce6ab720d78937d5e5db4e Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 12 Mar 2020 23:50:20 -0700 Subject: MDEV-21932 A fast plan with ROR index-merge is ignored when 'index_merge_sort_union=off' When index_merge_sort_union is set to 'off' and index_merge_union is set to 'on' then any evaluated index merge scan must consist only of ROR scans. The cheapest out of such index merges must be chosen. This index merge might not be the cheapest index merge. --- mysql-test/r/index_merge_myisam.result | 52 ++++++++++++++++++++++++++++++++ mysql-test/r/range_vs_index_merge.result | 2 +- mysql-test/t/index_merge_myisam.test | 48 +++++++++++++++++++++++++++++ sql/opt_range.cc | 7 +++++ 4 files changed, 108 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 091719095fb..0720dceac9a 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1704,3 +1704,55 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY,c1,i,c2 NULL NULL NULL 69 Using where DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; +# +# MDEV-21932: ROR union with index_merge_sort_union=off +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t0 select a+10 from t0; +insert into t0 select a+20 from t0; +insert into t0 select a+40 from t0; +insert into t0 select a+80 from t0; +insert into t0 select a+160 from t0; +delete from t0 where a > 300; +create table t1 ( +f1 int, f2 int, f3 int, f4 int, +primary key (f1), key (f3), key(f4) +) engine=myisam; +insert into t1 select a+100, a+100, a+100, a+100 from t0; +insert into t1 VALUES (9,0,2,6), (9930,0,0,NULL); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +set optimizer_switch='index_merge_sort_union=off'; +set optimizer_switch='index_merge_union=on'; +explain select * from t1 +where (( f3 = 1 or f1 = 7 ) and f1 < 10) or +(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 )); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 3 Using union(f3,PRIMARY,f3); Using where +select * from t1 +where (( f3 = 1 or f1 = 7 ) and f1 < 10) or +(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 )); +f1 f2 f3 f4 +9 0 2 6 +insert into t1 values (52,0,1,0),(53,0,1,0); +insert into t1 values (50,0,1,0),(51,0,1,0); +insert into t1 values (48,0,1,0),(49,0,1,0); +insert into t1 values (46,0,1,0),(47,0,1,0); +insert into t1 values (44,0,1,0),(45,0,1,0); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +explain select * from t1 +where (( f3 = 1 or f1 = 7 ) and f1 < 10) or +(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 )); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 13 Using union(f3,PRIMARY,f3); Using where +select * from t1 +where (( f3 = 1 or f1 = 7 ) and f1 < 10) or +(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 )); +f1 f2 f3 f4 +9 0 2 6 +drop table t0,t1; +set optimizer_switch= @optimizer_switch_save; diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index 0acaed37d22..752554ac53d 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -1653,7 +1653,7 @@ SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 2 Using where +1 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 3 Using union(f3,PRIMARY,f3); Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test index 82d0474e28e..79aa5447215 100644 --- a/mysql-test/t/index_merge_myisam.test +++ b/mysql-test/t/index_merge_myisam.test @@ -243,3 +243,51 @@ DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; +--echo # +--echo # MDEV-21932: ROR union with index_merge_sort_union=off +--echo # + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t0 select a+10 from t0; +insert into t0 select a+20 from t0; +insert into t0 select a+40 from t0; +insert into t0 select a+80 from t0; +insert into t0 select a+160 from t0; +delete from t0 where a > 300; + +create table t1 ( + f1 int, f2 int, f3 int, f4 int, + primary key (f1), key (f3), key(f4) +) engine=myisam; +insert into t1 select a+100, a+100, a+100, a+100 from t0; +insert into t1 VALUES (9,0,2,6), (9930,0,0,NULL); +analyze table t1; + +set optimizer_switch='index_merge_sort_union=off'; +set optimizer_switch='index_merge_union=on'; + +let $q1= +select * from t1 + where (( f3 = 1 or f1 = 7 ) and f1 < 10) or + (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 )); +eval explain $q1; +eval $q1; + +insert into t1 values (52,0,1,0),(53,0,1,0); +insert into t1 values (50,0,1,0),(51,0,1,0); +insert into t1 values (48,0,1,0),(49,0,1,0); +insert into t1 values (46,0,1,0),(47,0,1,0); +insert into t1 values (44,0,1,0),(45,0,1,0); +analyze table t1; + +let $q2= +select * from t1 + where (( f3 = 1 or f1 = 7 ) and f1 < 10) or + (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 )); +eval explain $q2; +eval $q2; + +drop table t0,t1; + +set optimizer_switch= @optimizer_switch_save; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index ca6f500fec6..70c1786dd83 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -6802,6 +6802,13 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, update_tbl_stats, &mrr_flags, &buf_size, &cost); + if (!param->is_ror_scan && + !optimizer_flag(param->thd, OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION)) + { + /* The scan is not a ROR-scan, just skip it */ + continue; + } + if (found_records != HA_POS_ERROR && tree->index_scans && (index_scan= (INDEX_SCAN_INFO *)alloc_root(param->mem_root, sizeof(INDEX_SCAN_INFO)))) -- cgit v1.2.1 From 407b0a6ae7c300f34535be59dcdb8ba8f84d1ce5 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 14 Mar 2020 19:58:57 -0700 Subject: MDEV-10466 Server crashed in SEL_ARG::store_min() with extended_keys=on This bug could manifest itself in a very rare cases when the optimizer chose an execution plan by which a joined table was accessed by a table scan and the optimizer was checking whether ranges checked for each record could improve this plan. In such cases the optimizer evaluates range conditions over a table that depend on other tables. For such conditions the constructed SEL_ARG trees are marked as MAYBE_KEY. If a SEL_ARG object constructed for a sargable condition marked as RANGE_KEY had the same first key part as a MAYBE_KEY SEL_ARG object and the key_and() function was called for this pair of SEL_ARG objects then an invalid SEL_ARG object could be constructed that ultimately could lead to a crash before the execution phase. --- mysql-test/r/range_innodb.result | 25 +++++++++++++++++++++++++ mysql-test/t/range_innodb.test | 25 +++++++++++++++++++++++++ sql/opt_range.cc | 2 +- 3 files changed, 51 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/range_innodb.result b/mysql-test/r/range_innodb.result index 794e6c7b3cc..50e967bef3e 100644 --- a/mysql-test/r/range_innodb.result +++ b/mysql-test/r/range_innodb.result @@ -37,3 +37,28 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 1 SIMPLE t2 range a,b b 5 NULL 201 Using where; Using join buffer (flat, BNL join) drop table t0,t1,t2; +# +# MDEV-10466: constructing an invalid SEL_ARG +# +create table t1 ( +pk int, a int, b int, +primary key (pk), index idx1(b), index idx2(b) +) engine=innodb; +insert into t1 values (1,6,0),(2,1,0),(3,5,2),(4,8,0); +create table t2 (c int) engine=innodb; +insert into t2 values (1),(2); +create table t3 (d int) engine=innodb; +insert into t3 values (3),(-1),(4); +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='extended_keys=on'; +explain +select pk, a, b from t1,t2,t3 where b >= d and pk < c and b = '0'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL PRIMARY,idx1,idx2 NULL NULL NULL 4 Using where; Using join buffer (incremental, BNL join) +select pk, a, b from t1,t2,t3 where b >= d and pk < c and b = '0'; +pk a b +1 6 0 +set optimizer_switch=@save_optimizer_switch; +drop table t1,t2,t3; diff --git a/mysql-test/t/range_innodb.test b/mysql-test/t/range_innodb.test index f76794814ef..3b97f4f1727 100644 --- a/mysql-test/t/range_innodb.test +++ b/mysql-test/t/range_innodb.test @@ -45,3 +45,28 @@ explain select * from t0 left join t2 on t2.a = d and pk < c and b = '0'; +select pk, a, b from t1,t2,t3 where b >= d and pk < c and b = '0'; + +set optimizer_switch=@save_optimizer_switch; + +drop table t1,t2,t3; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 70c1786dd83..37ca3a22c77 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -8837,7 +8837,7 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) if (key2->next_key_part) { key1->use_count--; // Incremented in and_all_keys - return and_all_keys(param, key1, key2, clone_flag); + return and_all_keys(param, key1, key2->next_key_part, clone_flag); } key2->use_count--; // Key2 doesn't have a tree } -- cgit v1.2.1 From 24cb76b8dd86bf54f264352be41506995b6c89c4 Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Tue, 24 Mar 2020 23:30:40 +0100 Subject: MDEV-22032 update HeidiSQL to version 11 --- win/packaging/heidisql.cmake | 2 +- win/packaging/heidisql.wxi.in | 28 +++++++++++++++++++--------- 2 files changed, 20 insertions(+), 10 deletions(-) diff --git a/win/packaging/heidisql.cmake b/win/packaging/heidisql.cmake index 1b9fb3d110c..1b8223072d2 100644 --- a/win/packaging/heidisql.cmake +++ b/win/packaging/heidisql.cmake @@ -1,4 +1,4 @@ -SET(HEIDISQL_BASE_NAME "HeidiSQL_10.2_32_Portable") +SET(HEIDISQL_BASE_NAME "HeidiSQL_11.0_32_Portable") SET(HEIDISQL_ZIP "${HEIDISQL_BASE_NAME}.zip") SET(HEIDISQL_URL "http://www.heidisql.com/downloads/releases/${HEIDISQL_ZIP}") SET(HEIDISQL_DOWNLOAD_DIR ${THIRD_PARTY_DOWNLOAD_LOCATION}/${HEIDISQL_BASE_NAME}) diff --git a/win/packaging/heidisql.wxi.in b/win/packaging/heidisql.wxi.in index e8e01700032..70849e67998 100644 --- a/win/packaging/heidisql.wxi.in +++ b/win/packaging/heidisql.wxi.in @@ -33,20 +33,25 @@ + + + + + - - + + - - + + - - + + @@ -54,6 +59,9 @@ + + + @@ -76,11 +84,13 @@ - - - + + + + + -- cgit v1.2.1