Bug #109170 Setting index_merge_sort_union=off also disables other index merge plans

• Submitted: 22 Nov 2022 19:45
• Modified: 25 Apr 2023 6:34
• Reporter: Manuel Ung
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 8.0.31
• OS: Any
• CPU Architecture: Any

[22 Nov 2022 19:45] Manuel Ung

Description:
Setting index_merge_sort_union=off seems to disable other index merge plans, even though the intention is to only disable sort_union index merge plans.

How to repeat:
create table t (i int, j int, k int, key(i), key(j, k), key(j));
insert into t (WITH RECURSIVE a (i) AS ( SELECT 0  union all SELECT i+1  from a where i < 9  ), b (i) AS  (SELECT x.i + y.i * 10 + z.i * 100 + w.i * 1000 +  v.i * 10000 FROM a x, a y, a z, a w, a v) SELECT b.i, b.i, b.i from b order by i);
explain select * from t where i = 0 or j = 2;
set optimizer_switch='index_merge_sort_union=off';
explain select * from t where i = 0 or j = 2;
explain select /*+ INDEX_MERGE(t i,j_2) */ * from t where i = 0 or j = 2;
drop table t;

The 3 explains give the following output:
explain select * from t where i = 0 or j = 2;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t       NULL    index_merge     i,j,j_2 i,j     5,5     NULL    2       100.00  Using sort_union(i,j); Using where

set optimizer_switch='index_merge_sort_union=off';
explain select * from t where i = 0 or j = 2;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t       NULL    ALL     i,j,j_2 NULL    NULL    NULL    100000  0.00    Using where

explain select /*+ INDEX_MERGE(t i,j_2) */ * from t where i = 0 or j = 2;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t       NULL    index_merge     i,j,j_2 i,j_2   5,5     NULL    2       100.00  Using union(i,j_2); Using where

You can see that after turning off index_merge_sort_union=off, we fall back to full table scan. However, I would expect it to fallback to a union plan instead, since index_merge_union is on.  If I change the query to use optimizer hints, then you can see that the union plan is possible (and chosen).

[23 Nov 2022 4:38] MySQL Verification Team

Hello Manuel Ung,

Thank you for the report and feedback.

regards,
Umesh

[25 Apr 2023 6:26] Nimita Joshi

This is a duplicate of Bug #109169

[25 Apr 2023 6:34] Manuel Ung

Can you explain how this is a duplicate? The other bug is about costing.

This bug is about optimizer_switch being broken.

[25 Apr 2023 8:54] Nimita Joshi

In this test case, after turning off index_merge_sort_union=off,
it fell back to full table scan because as explained in case of
bug 109169, existing range scan was used which consisted of "non-
ror scans" to find out if "ror-union" can be done. Since all best
range scans were not "ror-scans", "ror-union" scan could not be
done and if went for a full table scan. So this bug is a side
effect of Bug#109169.