You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/10/10 06:40:39 UTC

[GitHub] [shardingsphere] xuup opened a new issue, #18261: ShardingSphere-Proxy not support some mysql functions in result merge case

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: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] terrymanu commented on issue #18261: ShardingSphere-Proxy not support some mysql functions in result merge case

Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #18261:
URL: https://github.com/apache/shardingsphere/issues/18261#issuecomment-1164683429

   I just set it as invalid because of the author close the issue without any reason.


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] github-actions[bot] commented on issue #18261: ShardingSphere-Proxy not support some mysql functions in result merge case

Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #18261:
URL: https://github.com/apache/shardingsphere/issues/18261#issuecomment-1272349649

   Hello , this issue has not received a reply for several days.
   This issue is supposed to be closed.


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] xuup commented on issue #18261: ShardingSphere-Proxy not support some mysql functions in result merge case

Posted by GitBox <gi...@apache.org>.
xuup commented on issue #18261:
URL: https://github.com/apache/shardingsphere/issues/18261#issuecomment-1181299659

   I closed this issue earlier  for some reason,  I reopen this issue.


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] xuup closed issue #18261: ShardingSphere-Proxy not support some mysql functions in result merge case

Posted by GitBox <gi...@apache.org>.
xuup closed issue #18261: ShardingSphere-Proxy not support some mysql functions in result merge case  
URL: https://github.com/apache/shardingsphere/issues/18261


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] terrymanu commented on issue #18261: ShardingSphere-Proxy not support some mysql functions in result merge case

Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #18261:
URL: https://github.com/apache/shardingsphere/issues/18261#issuecomment-1193664467

   The master version always changes, can you provide the commit id?


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] github-actions[bot] closed issue #18261: ShardingSphere-Proxy not support some mysql functions in result merge case

Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #18261: ShardingSphere-Proxy not support some mysql functions in result merge case  
URL: https://github.com/apache/shardingsphere/issues/18261


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org