HappenLee opened a new issue #6171: URL: https://github.com/apache/incubator-doris/issues/6171
**Describe the bug**
create table and load data
```
create table T_DORIS_A
(
ID bigint not null,
APPLY_CRCL bigint(19)
)ENGINE=OLAP
UNIQUE KEY(`ID`)
COMMENT "T_DORIS_A"
DISTRIBUTED BY HASH(`ID`) BUCKETS 32
PROPERTIES(
"replication_num"="1",
"in_memory"="false",
"storage_format"="V2"
);
create table T_DORIS_B
(
ID bigint not null,
FACTOR_FIN_VALUE decimal(19,2),
PRJT_ID bigint(19)
)ENGINE=OLAP
UNIQUE KEY(`ID`)
COMMENT "T_DORIS_B"
DISTRIBUTED BY HASH(`ID`) BUCKETS 32
PROPERTIES(
"replication_num"="1",
"in_memory"="false",
"storage_format"="V2"
);
create table T_DORIS_C
(
ID bigint not null
)ENGINE=OLAP
UNIQUE KEY(`ID`)
COMMENT "T_DORIS_C"
DISTRIBUTED BY HASH(`ID`) BUCKETS 32
PROPERTIES(
"replication_num"="1",
"in_memory"="false",
"storage_format"="V2"
);
create table T_DORIS_D
(
ID bigint not null,
LIMIT_ID bigint(19),
CORE_ID bigint(19)
)ENGINE=OLAP
UNIQUE KEY(`ID`)
COMMENT "T_DORIS_D"
DISTRIBUTED BY HASH(`ID`) BUCKETS 32
PROPERTIES(
"replication_num"="1",
"in_memory"="false",
"storage_format"="V2"
);
create table T_DORIS_E
(
ID bigint not null,
SHARE_ID bigint,
SPONSOR_ID bigint
)ENGINE=OLAP
UNIQUE KEY(`ID`)
COMMENT "T_DORIS_E"
DISTRIBUTED BY HASH(`ID`) BUCKETS 32
PROPERTIES(
"replication_num"="1",
"in_memory"="false",
"storage_format"="V2"
);
```
**To Reproduce**
check query paln
```
desc SELECT B.FACTOR_FIN_VALUE, D.limit_id FROM T_DORIS_A A LEFT JOIN
T_DORIS_B B ON B.PRJT_ID = A.ID LEFT JOIN T_DORIS_C C ON A.apply_crcl =
C.id JOIN T_DORIS_D D ON C.ID = D.CORE_ID order by B.FACTOR_FIN_VALUE,
D.limit_id desc;
```
T_DORIS_C and T_DORIS_D should not be bucket shuffle join, but we got it.
```
| 6:HASH JOIN
|
| | join op: INNER JOIN (BUCKET_SHUFFLE)
|
| | hash predicates:
|
| | colocate: false, reason: The src data has been redistributed
|
| | equal join conjunct: `C`.`ID` = `D`.`CORE_ID`
|
| |
|
| |----10:EXCHANGE
|
| |
|
| 4:HASH JOIN
|
| | join op: LEFT OUTER JOIN (BROADCAST)
|
| | hash predicates:
|
| | colocate: false, reason: Tables are not in the same group
|
| | equal join conjunct: `A`.`apply_crcl` = `C`.`id`
|
| |
|
| |----9:EXCHANGE
|
| |
|
| 2:HASH JOIN
|
| | join op: LEFT OUTER JOIN (BUCKET_SHUFFLE)
|
| | hash predicates:
|
| | colocate: false, reason: Tables are not in the same group
|
| | equal join conjunct: `A`.`ID` = `B`.`PRJT_ID`
|
| |
|
| |----8:EXCHANGE
|
| |
|
| 0:OlapScanNode
|
| TABLE: T_DORIS_A
|
| PREAGGREGATION: OFF. Reason: No AggregateInfo
|
| partitions=1/1
|
| rollup: T_DORIS_A
|
| tabletRatio=32/32
|
|
tabletList=71030,71032,71034,71036,71038,71040,71042,71044,71046,71048 ... |
| cardinality=9884
|
| avgRowSize=5.156718
|
| numNodes=1
```
**Expected behavior**
The right query paln should be:
```
| 6:HASH JOIN
|
| | join op: INNER JOIN (BROADCAST)
|
| | hash predicates:
|
| | colocate: false, reason: The src data has been redistributed
|
| | equal join conjunct: `C`.`ID` = `D`.`CORE_ID`
|
| |
|
| |----10:EXCHANGE
|
| |
|
| 4:HASH JOIN
|
| | join op: LEFT OUTER JOIN (BROADCAST)
|
| | hash predicates:
|
| | colocate: false, reason: Tables are not in the same group
|
| | equal join conjunct: `A`.`apply_crcl` = `C`.`id`
|
| |
|
| |----9:EXCHANGE
|
| |
|
| 2:HASH JOIN
|
| | join op: LEFT OUTER JOIN (BUCKET_SHUFFLE)
|
| | hash predicates:
|
| | colocate: false, reason: Tables are not in the same group
|
| | equal join conjunct: `A`.`ID` = `B`.`PRJT_ID`
|
| |
|
| |----8:EXCHANGE
|
| |
|
| 0:OlapScanNode
|
| TABLE: T_DORIS_A
|
| PREAGGREGATION: OFF. Reason: No AggregateInfo
|
| partitions=1/1
|
| rollup: T_DORIS_A
|
| tabletRatio=32/32
|
|
tabletList=71030,71032,71034,71036,71038,71040,71042,71044,71046,71048 ... |
| cardinality=9884
|
| avgRowSize=5.156718
|
| numNodes=1
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
