Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.4(EOL)
-
None
Description
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
Attachments
Issue Links
- is caused by
-
MDEV-26301 Split optimization refills temporary table too many times
- Closed
- relates to
-
MDEV-22534 Trivial correlation detection/removal for IN subqueries
- In Review
-
MDEV-27691 make working view-protocol
- Open
-
MDEV-31407 Add aliases in opt_trace.test for long column name for removing "--disable-view-protocol"
- Closed