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 2021/05/31 02:54:04 UTC
[GitHub] [shardingsphere] dbzzzzz opened a new issue #10563: count+order by not support
dbzzzzz opened a new issue #10563:
URL: https://github.com/apache/shardingsphere/issues/10563
## Bug Report
### Which version of ShardingSphere did you use?
shardingsphere 5
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
select count(1) from t_order order by order_id desc;
### Actual behavior
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'testdb_0.t_order_0.order_id'; this is incompatible with sql_mode=only_full_group_by
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### Example codes for reproduce this issue (such as a github link).
--
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
[GitHub] [shardingsphere] strongduanmu closed issue #10563: count+order by not support
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #10563:
URL: https://github.com/apache/shardingsphere/issues/10563
--
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] strongduanmu commented on issue #10563: count+order by not support
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #10563:
URL: https://github.com/apache/shardingsphere/issues/10563#issuecomment-851374562
@dbzzzzz It can be seen from the error information that this error is thrown when PG executes SQL, and `SS` is responsible for routing, rewriting, executing and merging results.
actual sql:
```
[INFO ] 2021-05-31 17:49:45.008 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select count(1) from t_order order by order_id desc;
[INFO ] 2021-05-31 17:49:45.011 [Connection-2-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty)
[INFO ] 2021-05-31 17:49:45.011 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select count(1) , order_id AS ORDER_BY_DERIVED_0 from t_order_0 order by order_id desc;
[INFO ] 2021-05-31 17:49:45.011 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select count(1) , order_id AS ORDER_BY_DERIVED_0 from t_order_1 order by order_id desc;
[INFO ] 2021-05-31 17:49:45.011 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: select count(1) , order_id AS ORDER_BY_DERIVED_0 from t_order_0 order by order_id desc;
[INFO ] 2021-05-31 17:49:45.011 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: select count(1) , order_id AS ORDER_BY_DERIVED_0 from t_order_1 order by order_id desc;
```
error message:
```
org.postgresql.util.PSQLException: ERROR: column "t_order_0.order_id" must appear in the GROUP BY clause or be used in an aggregate function
位置：19
```
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #10563: count+order by not support
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #10563:
URL: https://github.com/apache/shardingsphere/issues/10563#issuecomment-927413680
This exception is caused by the fact that the aggregate function must use `group by`, which is not supported by the native database.
```
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'testdb_0.t_order_0.order_id'; this is incompatible with sql_mode=only_full_group_by
```
If sql is rewritten as `select count(1) from t_order group by order_id order by order_id desc;` it can be executed normally. `Can't find index` exception has been fixed by #11971.
--
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] strongduanmu commented on issue #10563: count+order by not support
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #10563:
URL: https://github.com/apache/shardingsphere/issues/10563#issuecomment-851383186
After we change the sql to `select count(1) from t_order group by order_id order by order_id desc;`, another error will occur:
```
ERROR: Can't find index: AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
```
This exception is because the `columnLabel` that in the `columnLabelIndexMap` returned by PG is `count`, and the `columnLabel` processed by SS is `count(1)`, the inconsistency of the `columnLabel` results in an exception in the verification. Considering the different processing way of MySQL, we need to distinguish between different database dialects to obtain the `columnLabel` in SS.
```java
private void setIndexForAggregationProjection(final Map<String, Integer> columnLabelIndexMap) {
for (AggregationProjection each : projectionsContext.getAggregationProjections()) {
Preconditions.checkState(columnLabelIndexMap.containsKey(each.getColumnLabel()), "Can't find index: %s, please add alias for aggregate selections", each);
each.setIndex(columnLabelIndexMap.get(each.getColumnLabel()));
for (AggregationProjection derived : each.getDerivedAggregationProjections()) {
Preconditions.checkState(columnLabelIndexMap.containsKey(derived.getColumnLabel()), "Can't find index: %s", derived);
derived.setIndex(columnLabelIndexMap.get(derived.getColumnLabel()));
}
}
}
```
--
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