wangbo opened a new issue #3464:
URL: https://github.com/apache/incubator-doris/issues/3464
**Describe the bug**
In the join case ```fact_table_a left join dim_table_b```, when
```dim_table_b```'s size exceeds 2GB and the ```fact_table_a``` has no data,the
query will failed and prints error stack as below:
```
Memory exceed limit. Hash join, while constructing the hash table. Backend:
123.456.789, fragment: 4fbdd81809244f3d-909e26102118fe92 Used: 2147529904,
Limit: 2147483648. You can change the limit by session variable exec_mem_limit.
```
**Why the bug happened**
1. broadcast cost calculation error.
Current broadcast cost calculation logic is
```broadcastCost = rhsDataSize * leftChildFragment.getNumNodes();```
But when ```the leftChildFragment``` has no data which means 0 *
rhsDataSize.
So broadcastCost is 0, then broadcast join happens.
when the right table(dim_table_b) has big size, the query must fail.
The correct result here should be ```broadcastCost = rhsDataSize * 1```
2. ```choose host for exchange fragment``` has better implementation
In current implementation,```exchange fragment``` will be assigned to
the most left child's node and keeps the same parallelism.
But when the most left child has no data, there will be only one
backend for it.
So send all right table's data to one exchange node to build hash table
will cause memory exceeds when the right tables' size is big.
**Solution**
1. When the ```leftChildFragment``` 's has no data and returned num nodes 0,
the returned num nodes should be changed to 1. Because for a scan node has no
data,there will be as least a random selected node for it.
2. Find backends for ```exchange fragment``` by ```cost``` instead of the
```left most child fragment```
```exchange fragment``` should be kept in the same node with child
fragment which has more data to reduce data transfer across network or has more
instance num to improve the parallelism.
In the current case, ```dim_table_b``` has more data and more instance
num, so the``` exchange fragment``` should be kept in the same node with
```dim_table_b```'s fragment.
**temporary solution**
Change SQL from ```fact_table_a left join dim_table_b``` to ```dim_table_b
right join fact_table_a```
----------------------------------------------------------------
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:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
