apache / apache/shardingsphere
Wrong output when query contains limit, group by and distinct combination
- Dominant language
- Java
- Stars
- 20.8k
- Forks
- 6.9k
- Avg merge
- 9h 55m
- Merged PRs (30d)
- 311
Description
## Bug Report
### Which version of ShardingSphere did you use?
5.4.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
Query returns accurate results.
### Actual behavior
Actual Results, not number of rows returned depends on the limit specified after group by keyword.
### Reason analyze (If you can)
If the user entered query is :
`SELECT COUNT(distinct Id2),Id1 C1 FROM ACCOUNT GROUP BY Id1 LIMIT 10`
before querying in individual shards, the query gets converted into
`SELECT distinct Id2,,Id1 C1 FROM ACCOUNT LIMIT 10`
hence the combined output depends on the limit value, limit value no longer has just the control over the number of rows returned but it is influencing the actual data.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
More than one Shards to reproduce the issue.
Contributor guide
Research direction
The report names no source files or tests. Start by reproducing the query on more than one shard and trace the rewrite from the grouped COUNT(DISTINCT ...) query to the per-shard DISTINCT query. Done means LIMIT affects only the number of returned rows and the combined results remain accurate.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases, distributed-systems
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100