[GitHub] [shardingsphere] strongduanmu edited a comment on issue #10564: sum+distinct not support

[GitBox <gi...@apache.org>]

strongduanmu edited a comment on issue #10564:
URL: https://github.com/apache/shardingsphere/issues/10564#issuecomment-851396537


   This exception is caused by SS rewriting. SS will rewrite `sum(distinct user_id)` into `distinct user_id` to execute, the routing result is as follows, if `distinct user_id` and `sum(order_id)` are executed at the same time, an error will occur.
   
   actual sql:
   
   ```sql
   [INFO ] 2021-05-31 18:22:54.567 [Connection-3-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select sum(distinct user_id),sum(order_id) from t_order;
   [INFO ] 2021-05-31 18:22:54.567 [Connection-3-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty)
   [INFO ] 2021-05-31 18:22:54.567 [Connection-3-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select DISTINCT user_id AS AGGREGATION_DISTINCT_DERIVED_0,sum(order_id) from t_order_0;
   [INFO ] 2021-05-31 18:22:54.567 [Connection-3-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select DISTINCT user_id AS AGGREGATION_DISTINCT_DERIVED_0,sum(order_id) from t_order_1;
   [INFO ] 2021-05-31 18:22:54.567 [Connection-3-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: select DISTINCT user_id AS AGGREGATION_DISTINCT_DERIVED_0,sum(order_id) from t_order_0;
   [INFO ] 2021-05-31 18:22:54.567 [Connection-3-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: select DISTINCT user_id AS AGGREGATION_DISTINCT_DERIVED_0,sum(order_id) from t_order_1;
   ```
   
   error message:
   ```
   org.postgresql.util.PSQLException: ERROR: column "t_order_0.user_id" must appear in the GROUP BY clause or be used in an aggregate function
     位置：17
   ```


-- 
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