does version 5.1.1 support "count" and "group by"?

[phdbutbachelor]

In the first case, I configure create_time as sharding key, and use "count" function.
Print logs like this:

[http-nio-8080-exec-4] INFO ShardingSphere-SQL : Logic SQL: select count(accesslog0_.id) as col_0_0_ from ACCESS_LOG accesslog0_ where accesslog0_.create_time>=? and accesslog0_.create_time<?
[http-nio-8080-exec-4] INFO ShardingSphere-SQL : SQLStatement: SQL92SelectStatement(limit=Optional.empty)
[http-nio-8080-exec-4] INFO ShardingSphere-SQL : Actual SQL: main ::: select count(accesslog0_.id) as col_0_0_ from ACCESS_LOG_W202223 accesslog0_ where accesslog0_.create_time>=? and accesslog0_.create_time<? UNION ALL select count(accesslog0_.id) as col_0_0_ from ACCESS_LOG_W202224 accesslog0_ where accesslog0_.create_time>=? and accesslog0_.create_time<? UNION ALL select count(accesslog0_.id) as col_0_0_ from ACCESS_LOG_W202225 accesslog0_ where accesslog0_.create_time>=? and accesslog0_.create_time<? ::: [2022-05-01 00:00:00.0, 2022-07-01 00:00:00.0, 2022-05-01 00:00:00.0, 2022-07-01 00:00:00.0, 2022-05-01 00:00:00.0, 2022-07-01 00:00:00.0]

shardingsphere generated "UNION ALL" rather then add up the results, this leaded to an error.

In the second case, I configure startTime as sharding key, and use "group by" funtion.
Print logs like this:

[http-nio-8080-exec-10] INFO ShardingSphere-SQL : Logic SQL: select distinct infolog0_.actorId as col_0_0_ from set_info_logs infolog0_ where (infolog0_.actorId is not null) and infolog0_.actorId<>'' and ?<=infolog0_.startTime and ?>infolog0_.startTime limit ?
[http-nio-8080-exec-10] INFO ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@7166417b], lock=Optional.empty, window=Optional.empty)
[http-nio-8080-exec-10] INFO ShardingSphere-SQL : Actual SQL: main ::: select distinct infolog0_.actorId as col_0_0_ from set_info_logs infolog0_ where (infolog0_.actorId is not null) and infolog0_.actorId<>'' and ?<=infolog0_.startTime and ?>infolog0_.startTime limit ? ::: [2022-06-01 00:00:00.0, 2022-07-01 00:00:00.0, 10001]

shardingsphere generated sql without "goup by", which is unexpected.

I wonder how to use "count" and "grouo by" in version 5.1.1?

[linghengqian]

• For ShardingSphere 5.4.0, the answer to this answer is yes. If you think there is a problem, please open a new issue with an example.