apache / apache/shardingsphere

Wrong output when query contains limit, group by and distinct combination

Open
#29,841 7 comments 0 reactions 0 assignees View on GitHub
feature: SQL federation type: enhancement
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.