|
In case below it is expected that result from both SELECT from VIEW will be the same:
--source include/have_sequence.inc
|
|
create table t1(a int, b int);
|
insert into t1
|
select seq,seq from seq_1_to_5;
|
|
create table t2(a int, b int, key(a));
|
insert into t2
|
select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
|
|
create table t3(a int, b int, key(a));
|
insert into t3
|
select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
|
|
create table t10 (grp_id int, col1 int, key(grp_id));
|
insert into t10
|
select A.seq, B.seq from seq_1_to_100 A, seq_1_to_100 B;
|
|
create table t11 (col1 int, col2 int);
|
insert into t11
|
select A.seq, A.seq from seq_1_to_10 A;
|
|
analyze table t1,t2,t3,t10,t11 persistent for all;
|
|
set optimizer_trace=1;
|
explain
|
select * from
|
(
|
(t1 left join t2 on t2.a=t1.b)
|
left join t3 on t3.a=t1.b
|
) left join (select grp_id, count(*)
|
from t10 left join t11 on t11.col1=t10.col1
|
group by grp_id) T on T.grp_id=t1.b;
|
|
|
create view v1 as select
|
json_detailed(
|
json_remove(
|
json_extract(trace, '$**.choose_best_splitting')
|
, '$[0].split_plan_search[0]'
|
)
|
) as JS
|
from information_schema.optimizer_trace;
|
|
select * from v1;
|
select * from v1;
|
|
drop view v1;
|
drop table t1,t2,t3,t10,t11;
|
set optimizer_trace=DEFAULT;
|
Actual result:
JS
|
[
|
{
|
"considered_keys":
|
[
|
{
|
"table_name": "t10",
|
"index": "grp_id",
|
"rec_per_key": 100,
|
"param_tables": 1
|
}
|
],
|
"refills": 5,
|
"spl_pd_boundary": 2,
|
"split_plan_search":
|
[],
|
"lead_table": "t10",
|
"index": "grp_id",
|
"parts": 1,
|
"split_sel": 0.001,
|
"cost": 2536,
|
"records": 100,
|
"refills": 5,
|
"chosen": true
|
}
|
]
|
select * from v1;
|
JS
|
NULL
|
Expected result:
JS
|
[
|
{
|
"considered_keys":
|
[
|
{
|
"table_name": "t10",
|
"index": "grp_id",
|
"rec_per_key": 100,
|
"param_tables": 1
|
}
|
],
|
"refills": 5,
|
"spl_pd_boundary": 2,
|
"split_plan_search":
|
[],
|
"lead_table": "t10",
|
"index": "grp_id",
|
"parts": 1,
|
"split_sel": 0.001,
|
"cost": 2536,
|
"records": 100,
|
"refills": 5,
|
"chosen": true
|
}
|
]
|
select * from v1;
|
JS
|
[
|
{
|
"considered_keys":
|
[
|
{
|
"table_name": "t10",
|
"index": "grp_id",
|
"rec_per_key": 100,
|
"param_tables": 1
|
}
|
],
|
"refills": 5,
|
"spl_pd_boundary": 2,
|
"split_plan_search":
|
[],
|
"lead_table": "t10",
|
"index": "grp_id",
|
"parts": 1,
|
"split_sel": 0.001,
|
"cost": 2536,
|
"records": 100,
|
"refills": 5,
|
"chosen": true
|
}
|
]
|
After fix enable "view-protocol" for cases marked with MDEV-31408
|