set @opt_context_schema='$opt_context_schema';
#
# In this test, each query is run more than once by
# using run_query_twice_and_compare_stats.inc file
#
set session use_stat_tables='PREFERABLY_FOR_QUERIES';
set optimizer_record_context=ON;
set optimizer_replay_context="";
create database db1;
use db1;
create function round_cost(explain_output text)
returns text
begin
declare len int default 0;
declare prec int default 7;
declare cost_elem text;
declare cost_value_json text;
declare cost_value double;
set cost_elem= '$.query_block[0].cost';
set cost_value_json= (select json_extract(explain_output, cost_elem));
set explain_output= (select json_replace(explain_output, cost_elem, round(cost_value_json, prec)));
set len= (select json_length(json_extract(explain_output, '$.query_block.nested_loop')));
for i in 0 .. len-1
do
set cost_elem= concat('$.query_block[0].nested_loop[', i, '].table.cost');
set cost_value_json= (select json_extract(explain_output, cost_elem));
set explain_output= (select json_replace(explain_output, cost_elem, round(cost_value_json, prec)));
set cost_elem= concat('$.query_block[0].nested_loop[', i, '].block-nl-join.table.cost');
set cost_value_json= (select json_extract(explain_output, cost_elem));
set explain_output= (select json_replace(explain_output, cost_elem, round(cost_value_json, prec)));
end for;
return explain_output;
end;
//
#
# Range query on a single table by using 1 unique index on a single column
#
create table t1 (
c1 int,
c2 int,
unique(c1),
unique(c2)
) ENGINE=InnoDB;
insert into t1 select seq, seq from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 a, t1 b where a.c1 < 3 and b.c1 < 33
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0384631,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "a",
                    "access_type": "range",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "5",
                    "used_key_parts": 
                    ["c1"],
                    "loops": 1,
                    "rows": 2,
                    "cost": 0.0052431,
                    "filtered": 100,
                    "index_condition": "a.c1 < 3"
                }
            },
            {
                "block-nl-join": 
                {
                    "table": 
                    {
                        "table_name": "b",
                        "access_type": "ALL",
                        "possible_keys": 
                        ["c1"],
                        "loops": 2,
                        "rows": 100,
                        "cost": 0.0332200,
                        "filtered": 32,
                        "attached_condition": "b.c1 < 33"
                    },
                    "buffer_type": "flat",
                    "buffer_size": "119",
                    "join_type": "BNL"
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
#
# Range query on a single table by using 2 unique indexes on 2 different column
#
insert into t1 select seq, seq from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 a, t1 b where a.c1 < 3 and b.c1 < 33 and a.c2 < 3 and b.c2 < 33
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0328705,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "a",
                    "access_type": "range",
                    "possible_keys": 
                    [
                        "c1",
                        "c2"
                    ],
                    "key": "c1",
                    "key_length": "5",
                    "used_key_parts": 
                    ["c1"],
                    "rowid_filter": 
                    {
                        "range": 
                        {
                            "key": "c2",
                            "used_key_parts": 
                            ["c2"]
                        },
                        "rows": 2,
                        "selectivity_pct": 2
                    },
                    "loops": 1,
                    "rows": 2,
                    "cost": 0.0047039,
                    "filtered": 2,
                    "index_condition": "a.c1 < 3",
                    "attached_condition": "a.c2 < 3"
                }
            },
            {
                "block-nl-join": 
                {
                    "table": 
                    {
                        "table_name": "b",
                        "access_type": "ALL",
                        "possible_keys": 
                        [
                            "c1",
                            "c2"
                        ],
                        "loops": 1,
                        "rows": 100,
                        "cost": 0.0281666,
                        "filtered": 10.23999977,
                        "attached_condition": "b.c1 < 33 and b.c2 < 33"
                    },
                    "buffer_type": "flat",
                    "buffer_size": "119",
                    "join_type": "BNL"
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
#
# Add more data to the table and execute the query
#
insert into t1 select seq, seq from seq_1_to_200;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
drop table t1;
#
# Equi-Join query on a single table having 1 non-unique index on a single column.
# Also, index column is used in the condition
#
set optimizer_replay_context="";
create table t1 (
c1 int,
c2 int,
index(c1)
) ENGINE=InnoDB;
insert into t1 select seq%5, seq%10 from seq_1_to_200;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 3.8137228,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "possible_keys": 
                    ["c1"],
                    "loops": 1,
                    "rows": 200,
                    "cost": 0.0434548,
                    "filtered": 100
                }
            },
            {
                "block-nl-join": 
                {
                    "table": 
                    {
                        "table_name": "tt2",
                        "access_type": "ALL",
                        "possible_keys": 
                        ["c1"],
                        "loops": 200,
                        "rows": 200,
                        "cost": 3.7702680,
                        "filtered": 20
                    },
                    "buffer_type": "flat",
                    "buffer_size": "2KiB",
                    "join_type": "BNL",
                    "attached_condition": "tt2.c1 = tt1.c1"
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
drop table t1;
#
# Equi-Join query on a single table having 1 primary key index on a single column.
# Also, index column is used in the condition
#
create table t1 (
c1 int,
c2 int,
primary key (c1)
) ENGINE=InnoDB;
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.1174180,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "possible_keys": 
                    ["PRIMARY"],
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 100
                }
            },
            {
                "table": 
                {
                    "table_name": "tt2",
                    "access_type": "eq_ref",
                    "possible_keys": 
                    ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "4",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["db1.tt1.c1"],
                    "loops": 100,
                    "rows": 1,
                    "cost": 0.0902632,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
drop table t1;
#
# Equi-Join query on a single table having 1 non-unique index on 2 columns.
# Both the index columns are used in the condition
#
create table t1 (
c1 int,
c2 int,
index(c1, c2)
) ENGINE=InnoDB;
insert into t1 select seq%3, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1 and tt1.c2 = tt2.c2
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.1928166,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "index",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "10",
                    "used_key_parts": 
                    [
                        "c1",
                        "c2"
                    ],
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0213144,
                    "filtered": 100,
                    "attached_condition": "tt1.c1 is not null and tt1.c2 is not null",
                    "using_index": true
                }
            },
            {
                "table": 
                {
                    "table_name": "tt2",
                    "access_type": "ref",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "10",
                    "used_key_parts": 
                    [
                        "c1",
                        "c2"
                    ],
                    "ref": 
                    [
                        "db1.tt1.c1",
                        "db1.tt1.c2"
                    ],
                    "loops": 100,
                    "rows": 6,
                    "cost": 0.1715022,
                    "filtered": 100,
                    "using_index": true
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
drop table t1;
#
# Equi-Join query on a single table having 1 primary key index on 2 columns.
# Both the index columns are used in the condition
#
create table t1 (
c1 int,
c2 int,
primary key (c1, c2)
) ENGINE=InnoDB;
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1 and tt1.c2 = tt2.c2
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.1174180,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "possible_keys": 
                    ["PRIMARY"],
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 100
                }
            },
            {
                "table": 
                {
                    "table_name": "tt2",
                    "access_type": "eq_ref",
                    "possible_keys": 
                    ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "8",
                    "used_key_parts": 
                    [
                        "c1",
                        "c2"
                    ],
                    "ref": 
                    [
                        "db1.tt1.c1",
                        "db1.tt1.c2"
                    ],
                    "loops": 100,
                    "rows": 1,
                    "cost": 0.0902632,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
drop table t1;
#
# Equi-Join query on a single table having 1 non-unique index on 2 columns.
# However, only 1 column from the index is used in the condition
#
create table t1 (
c1 int,
c2 int,
index(c1, c2)
) ENGINE=InnoDB;
insert into t1 select seq%5, seq%10 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.3981756,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "index",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "10",
                    "used_key_parts": 
                    [
                        "c1",
                        "c2"
                    ],
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0213144,
                    "filtered": 100,
                    "attached_condition": "tt1.c1 is not null",
                    "using_index": true
                }
            },
            {
                "table": 
                {
                    "table_name": "tt2",
                    "access_type": "ref",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "5",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["db1.tt1.c1"],
                    "loops": 100,
                    "rows": 20,
                    "cost": 0.3768612,
                    "filtered": 100,
                    "using_index": true
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
drop table t1;
#
# Equi-Join query on a single table having 1 primary key index on 2 columns.
# However, only 1 column from the index that has unique values is used in the condition
#
create table t1 (
c1 int,
c2 int,
primary key (c1, c2)
) ENGINE=InnoDB;
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.1244310,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "possible_keys": 
                    ["PRIMARY"],
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 100
                }
            },
            {
                "table": 
                {
                    "table_name": "tt2",
                    "access_type": "ref",
                    "possible_keys": 
                    ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "4",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["db1.tt1.c1"],
                    "loops": 100,
                    "rows": 1,
                    "cost": 0.0972762,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
#
# Equi-Join query on a single table having 1 primary key index on 2 columns.
# However, only 1 column from the index that has non-unique values is used in the condition
#
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c2 = tt2.c2
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.9890562,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 100
                }
            },
            {
                "block-nl-join": 
                {
                    "table": 
                    {
                        "table_name": "tt2",
                        "access_type": "ALL",
                        "loops": 100,
                        "rows": 100,
                        "cost": 0.9619014,
                        "filtered": 100
                    },
                    "buffer_type": "flat",
                    "buffer_size": "1008",
                    "join_type": "BNL",
                    "attached_condition": "tt2.c2 = tt1.c2"
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
#
# Query on a single table having 1 primary key index on 2 columns.
# However, a constant literal is used in the equality predicate with only 1 column from the index that has unique values.
#
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1 where tt1.c1 = 5
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0017838,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ref",
                    "possible_keys": 
                    ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "4",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["const"],
                    "loops": 1,
                    "rows": 1,
                    "cost": 0.0017838,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
#
# Query on a single table having 1 primary key index on 2 columns.
# However, a constant literal is used in the equality predicate with only 1 column from the index that has non-unique values.
#
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1 where tt1.c2 = 5
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0271548,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 100,
                    "attached_condition": "tt1.c2 = 5"
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
drop table t1;
#
# Query on a single table having 1 non-unique index on 2 columns.
# However, a constant literal is used in the equality predicate using only 1 column from the index.
#
create table t1 (
c1 int,
c2 int,
index(c1)
) ENGINE=InnoDB;
insert into t1 select seq%3, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1 where tt1.c1 = 3
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0034586,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ref",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "5",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["const"],
                    "loops": 1,
                    "rows": 1,
                    "cost": 0.0034586,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
#
# Query on a single table having 1 non-unique index on a single column.
# Also, a constant literal is used in the equality predicate on the column that is in the index.
#
insert into t1 select seq%3, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1 where tt1.c1 = 5
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0034586,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ref",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "5",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["const"],
                    "loops": 1,
                    "rows": 1,
                    "cost": 0.0034586,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
drop table t1;
#
# Query on a single table having 1 primary key index with only 1 column.
# However, a constant literal is used in the equality predicate on the column that is not in the index.
#
create table t1 (
c1 int,
c2 int,
primary key(c1)
) ENGINE=InnoDB;
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1 where tt1.c2 = 4
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0271548,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 100,
                    "attached_condition": "tt1.c2 = 4"
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
drop table t1;
#
# Index-Merge query on a single table having 2 non-unique index with a single column in each.
# Also, index column is used in the condition
#
set optimizer_replay_context="";
create table t1 (
c1 int,
c2 int,
index(c1),
index(c2)
) ENGINE=InnoDB;
insert into t1 select seq%5, seq%10 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 as tt1 where tt1.c1 = 5 OR tt1.c2 = 10
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0077168,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "index_merge",
                    "possible_keys": 
                    [
                        "c1",
                        "c2"
                    ],
                    "key_length": "5,5",
                    "index_merge": 
                    {
                        "union": 
                        [
                            {
                                "range": 
                                {
                                    "key": "c1",
                                    "used_key_parts": 
                                    ["c1"]
                                }
                            },
                            {
                                "range": 
                                {
                                    "key": "c2",
                                    "used_key_parts": 
                                    ["c2"]
                                }
                            }
                        ]
                    },
                    "loops": 1,
                    "rows": 2,
                    "cost": 0.0077168,
                    "filtered": 100,
                    "attached_condition": "tt1.c1 = 5 or tt1.c2 = 10"
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
drop table t1;
#
# Index-Merge query on a single table having 2 indexes with overlapping keys
#
set optimizer_replay_context="";
create table t1 (
a int,
b int,
c int,
index idx_ab(a, b),
index idx_ac(a, c)
) ENGINE=InnoDB;
insert into t1 select seq%2, seq%3, seq%5 from seq_1_to_20;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context="";
explain format=json select * from t1 where a=1 and b=1 and c=1
set @saved_opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_var_name='saved_opt_context';
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0038076,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "t1",
                    "access_type": "index_merge",
                    "possible_keys": 
                    [
                        "idx_ab",
                        "idx_ac"
                    ],
                    "key_length": "10,10",
                    "index_merge": 
                    {
                        "intersect": 
                        [
                            {
                                "range": 
                                {
                                    "key": "idx_ac",
                                    "used_key_parts": 
                                    [
                                        "a",
                                        "c"
                                    ]
                                }
                            },
                            {
                                "range": 
                                {
                                    "key": "idx_ab",
                                    "used_key_parts": 
                                    [
                                        "a",
                                        "b"
                                    ]
                                }
                            }
                        ]
                    },
                    "loops": 1,
                    "rows": 1,
                    "cost": 0.0038076,
                    "filtered": 100,
                    "attached_condition": "t1.a = 1 and t1.b = 1 and t1.c = 1",
                    "using_index": true
                }
            }
        ]
    }
}
set @saved_explain_output=@explain_output;
set optimizer_replay_context="";
truncate table t1;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status OK
set optimizer_replay_context=@saved_opt_context_var_name;
set @explain_output='$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context="";
drop table t1;
drop function round_cost;
drop database db1;