yangzhg opened a new issue #3187: JOIN HITS not work when table reordered
URL: https://github.com/apache/incubator-doris/issues/3187
**Describe the bug**
in hash join clasue， when tables needed to be reorderd, the join hint will not work
**To Reproduce**
Steps to reproduce the behavior:
1. two table in tpcds data set `catalog_sales` and `store_sales`, `catalog_sales` is larger than `store_sales`
2. run query
```
explain select count(*) from (select distinct store_sales.ss_addr_sk from catalog_sales inner join [shuffle] store_sales on catalog_sales.cs_bill_cdemo_sk = store_sales.ss_addr_sk) a;
```
**Expected behavior**
expected to be hash join is `INNER JOIN (PARTITIONED)` actrual is `INNER JOIN (BROADCAST)`
**Screenshots**
```
mysql> explain select count(*) from (select distinct store_sales.ss_addr_sk from catalog_sales inner join [shuffle] store_sales on catalog_sales.cs_bill_cdemo_sk = store_sales.ss_addr_sk) a;
+---------------------------------------------------------------------------------+
| Explain String |
+---------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:<slot 4> |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 9:AGGREGATE (merge finalize) |
| | output: count(<slot 4>) |
| | group by: |
| | tuple ids: 4 |
| | |
| 8:EXCHANGE |
| tuple ids: 4 |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: <slot 2> |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 08 |
| UNPARTITIONED |
| |
| 4:AGGREGATE (update serialize) |
| | output: count(*) |
| | group by: |
| | tuple ids: 4 |
| | |
| 7:AGGREGATE (merge finalize) |
| | group by: <slot 2> |
| | tuple ids: 2 |
| | |
| 6:EXCHANGE |
| tuple ids: 2 |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 06 |
| HASH_PARTITIONED: <slot 2> |
| |
| 3:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: `store_sales`.`ss_addr_sk` |
| | tuple ids: 2 |
| | |
| 2:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: table not in same group |
| `store_sales`.`ss_addr_sk` = `catalog_sales`.`cs_bill_cdemo_sk` |
| | tuple ids: 1 0 |
| | |
| |----5:EXCHANGE |
| | tuple ids: 0 |
| | |
| 0:OlapScanNode |
| TABLE: store_sales |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: store_sales |
| tabletRatio=100/100 |
| tabletList=11298,11300,11302,11304,11306,11308,11310,11312,11314,11316 ... |
| cardinality=28800990 |
| avgRowSize=69.8691 |
| numNodes=1 |
| tuple ids: 1 |
| |
| PLAN FRAGMENT 3 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 05 |
| UNPARTITIONED |
| |
| 1:OlapScanNode |
| TABLE: catalog_sales |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: catalog_sales |
| tabletRatio=100/100 |
| tabletList=10256,10258,10260,10262,10264,10266,10268,10270,10272,10274 ... |
| cardinality=14401260 |
| avgRowSize=103.92035 |
| numNodes=1 |
| tuple ids: 0 |
+---------------------------------------------------------------------------------+
88 rows in set (0.01 sec)
```
**Desktop (please complete the following information):**
- OS: [e.g. iOS]
- Browser [e.g. chrome, safari]
- Version [e.g. 22]
**Smartphone (please complete the following information):**
- Device: [e.g. iPhone6]
- OS: [e.g. iOS8.1]
- Browser [e.g. stock browser, safari]
- Version [e.g. 22]
**Additional context**
Add any other context about the problem here.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
imay closed issue #3187: joinHints not work when table reordered URL: https://github.com/apache/incubator-doris/issues/3187 ---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: users@infra.apache.org With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org For additional commands, e-mail: commits-help@doris.apache.org