The query result is incorrect when querying the total count of cross-table pagination with a SQL statement that includes GROUP BY.

[ly164608001]

Bug Report

The query result is incorrect when querying the total count of cross-table pagination with a SQL statement that includes GROUP BY

Which version of ShardingSphere did you use?

5.2.0

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

Able to return the correct result.

Actual behavior

Simply calculated the sum of their respective results

Reason analyze (If you can)

The results should be compared after grouping by, removing duplicates, and then calculating the statistics

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1、CREATE table sharingtest_202309(id BIGINT,name VARCHAR, create_date date);
CREATE table sharingtest_202310(id BIGINT,name VARCHAR, create_date date);

2、shardingRule
sharding_test:
actualDataNodes: db0.sharding_test_$->{2021..2030}$->{['01','02','03','04','05','06','07','08','09','10','11','12']}
tableStrategy:
standard:
shardingColumn: create_date
shardingAlgorithmName: table-month
3、executeSQL: select count(0) from (select name
from
sharingtest
where
create_date >= '2023-09-01'
and create_date <= '2023-10-31'
group by
name
) tmp_count
4、The expected result is 2, but the actual result is 3