phdbutbachelor opened a new issue, #18207: URL: https://github.com/apache/shardingsphere/issues/18207
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 infolog0_.method as col_0_0_, count(distinct infolog0_.actorId) as col_1_0_, count(infolog0_.id) as col_2_0_ from set_info_logs infolog0_ where infolog0_.startTime>=? and infolog0_.startTime<=? group by infolog0_.method order by count(infolog0_.id) DESC 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@770083b1], lock=Optional.empty, window=Optional.empty) [http-nio-8080-exec-10] INFO ShardingSphere-SQL : Actual SQL: main ::: select DISTINCT infolog0_.method as col_0_0_, infolog0_.actorId as col_1_0_, count(infolog0_.id) as col_2_0_ from set_info_logs_202206 infolog0_ where infolog0_.startTime>=? and infolog0_.startTime<=? order by count(infolog0_.id) DESC limit ? ::: [2022-06-01 00:00:00.0, 2022-07-07 00:00:00.0, 2147483647] ----------------------------------------------------------------------------------------------------------------------------------- shardingsphere generated sql without "goup by", which is unexpected. I wonder how to use "count" and "grouo by" in version 5.1.1? -- 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]
