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

["ly164608001 (via GitHub)" <gi...@apache.org>]

ly164608001 opened a new issue, #28914:
URL: https://github.com/apache/shardingsphere/issues/28914

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


-- 
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.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

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

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu commented on issue #28914:
URL: https://github.com/apache/shardingsphere/issues/28914#issuecomment-3621628763

     Root Cause: In the default sharding execution path, the inner GROUP BY distinct information is not preserved for merging. The outer COUNT (with no GROUP BY) sums per-shard counts directly, so duplicate name values across shards are double-counted.
   
     Problem Analysis:
   
     - Routing sees a subquery, so aggregate rewrite is not enabled; each shard executes a local count.
     - Merge phase uses accumulation for COUNT without global distinct tracking because only local counts are returned, not grouped rows.
     - Cross-shard duplicates therefore inflate the result to 3 instead of 2.
   
     Conclusion:
   
     - This is a known limitation of the default execution path.
     - Recommended usage: enable SQL Federation (e.g., set the sql-federation rule to FORCE/ALL) so GROUP BY de-duplication happens globally before counting; or change to select name ... group by name and count in the application after ShardingSphere performs global
       grouping.
     - If you need further confirmation, please share your rule configuration (whether SQL Federation is enabled), data distribution, and execution logs to verify whether the query took the sharding merge path instead of the federation path.


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

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

["github-actions[bot] (via GitHub)" <gi...@apache.org>]

github-actions[bot] commented on issue #28914:
URL: https://github.com/apache/shardingsphere/issues/28914#issuecomment-1837241964

   There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.


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

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

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu closed issue #28914: The query result is incorrect when querying the total count of cross-table pagination with a SQL statement that includes GROUP BY.
URL: https://github.com/apache/shardingsphere/issues/28914


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