xuup opened a new issue, #18261:
URL: https://github.com/apache/shardingsphere/issues/18261
## Bug Report
### Which version of ShardingSphere did you use?
shardingsphere master
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Steps to reproduce the behavior,
```sql
select max(order_price) from t_order;
```
execute log
```
Logic SQL: select max(order_price) from t_order
SQLStatement: MySQLSelectStatement(table=Optional.empty,
limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
Actual SQL: ds_0 ::: select max(order_price) from t_order_0 UNION ALL select
max(order_price) from t_order_1
Actual SQL: ds_1 ::: select max(order_price) from t_order_0 UNION ALL select
max(order_price) from t_order_1
```
execute result:
```
max(order_price)
32.0
```
but when I add the function before max(order_price) as NULLIF(),
rpad(),CONCAT() etc , shardingsphere-proxy return unmerged result;
like this,
#### NULLIF()
```
select NULLIF(max(order_price),0.0) from t_order;
```
log
```
Logic SQL: select NULLIF(max(order_price),0.0) from t_order
SQLStatement: MySQLSelectStatement(table=Optional.empty,
limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
Actual SQL: ds_0 ::: select NULLIF(max(order_price),0.0) from t_order_0
UNION ALL select NULLIF(max(order_price),0.0) from t_order_1
Actual SQL: ds_1 ::: select NULLIF(max(order_price),0.0) from t_order_0
UNION ALL select NULLIF(max(order_price),0.0) from t_order_1
```
execute result
```
NULLIF(max(order_price),0.0)
32.0
32.0
32.0
```
#### rpad()
```
select rpad(max(order_price),10,"$") from t_order;
```
execute log
```
Logic SQL: select rpad(max(order_price),10,"$") from t_order
Actual SQL: ds_0 ::: select rpad(max(order_price),10,"$") from t_order_0
UNION ALL select rpad(max(order_price),10,"$") from t_order_1
Actual SQL: ds_1 ::: select rpad(max(order_price),10,"$") from t_order_0
UNION ALL select rpad(max(order_price),10,"$") from t_order_1
```
execute result
```
rpad(max(order_price),10,"$")
32.00$$$$$
32.00$$$$$
32.00$$$$$
```
#### CONCAT()
```
select CONCAT(max(order_price),"$") from t_order;
```
execute log
```
Logic SQL: select CONCAT(max(order_price),"$") from t_order
SQLStatement: MySQLSelectStatement(table=Optional.empty,
limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
Actual SQL: ds_0 ::: select CONCAT(max(order_price),"$") from t_order_0
UNION ALL select CONCAT(max(order_price),"$") from t_order_1
Actual SQL: ds_1 ::: select CONCAT(max(order_price),"$") from t_order_0
UNION ALL select CONCAT(max(order_price),"$") from t_order_1
```
execute result
```
CONCAT(max(order_price),"$")
32.00$
32.00$
32.00$
```
### Actual behavior
the expected number of results is 1, like
```
select rpad(max(order_price),10,"$") from t_order;
---
rpad(max(order_price),10,"$")
32.00$$$$$
```
```
select rpad(max(order_price),10,"$") from t_order;
---
CONCAT(max(order_price),"$")
32.00$
```
### Reason analyze
My analysis is as follows，
shardingsphere-proxy execute sql like this
```
select CONCAT(max(order_price),"$") from t_order;
```
the size of result from execute engine is more than 1, then ss will call
the merge engine to execute result merge.
But **isNeedProcessGroupBy()** method line 85 in
ShardingDQLResultMerger.java not return true, and result not merged.
```java
@Override
public MergedResult merge() throws SQLException {
if (1 == queryResults.size() &&
!isNeedAggregateRewrite(sqlStatementContext)) {
return new IteratorStreamMergedResult(queryResults);
}
……
MergedResult mergedResult = build(queryResults,
selectStatementContext, columnLabelIndexMap, database);
return decorate(queryResults, selectStatementContext, mergedResult);
}
private MergedResult build(final List<QueryResult> queryResults, final
SelectStatementContext selectStatementContext,
final Map<String, Integer>
columnLabelIndexMap, final ShardingSphereDatabase database) throws SQLException
{
……
if (isNeedProcessGroupBy(selectStatementContext)) { // the
condition should be true, but return false
return getGroupByMergedResult(queryResults,
selectStatementContext, columnLabelIndexMap, schema);
}
……
return new IteratorStreamMergedResult(queryResults);
}
```
--
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]
