terrymanu commented on issue #29137:
URL:
https://github.com/apache/shardingsphere/issues/29137#issuecomment-3633121305
### Understanding
- In ShardingSphere-Proxy 5.4.1 with PostgreSQL, a query using
row_number() over(partition by ...) routed across shards returns 1,1 instead of
the expected 1,2. Issue #29137 remains open.
### Root Cause
- PostgreSQL and the parser support row_number(), but when the query
routes to multiple shards, the window function is computed independently on
each shard. The current merge layer does not globally recompute window
functions, so row numbers are not consolidated.
### Analysis
- The merge engine already handles global sorting/grouping/aggregation,
but it lacks global processing for window functions. Without a global merge and
rewrite of window outputs across shards (partition/order aware), duplicate row
numbers appear. This is a functional
gap in the merge layer for cross-shard window functions.
### Conclusion (Fix Proposal & PR Invitation)
- This is a functional gap for cross-shard window function merging.
Proposed direction:
1. Expose/preserve WindowSegment in SelectStatementContext to detect
window functions.
2. In the PostgreSQL merge-engine selector, if a window function
exists and routing is multi-shard, choose a new window-merge branch.
3. Add a WindowMergedResult-like wrapper (on top of
OrderByStreamMergedResult):
- Streamed k-way merge by partition key + order key (priority
queue), honoring ASC/DESC and NULLS FIRST/LAST.
- Track current partition and counter; rewrite row_number() per
merged row.
- Work with LIMIT/OFFSET; prefer streaming to avoid full buffering.
4. Scope: initially support common row_number() with partition/order;
extend to more complex frames later.
5. Tests:
- Unit tests in infra/merge for cross-shard row_number()
(partition changes, ASC/DESC, NULLS FIRST/LAST, LIMIT/OFFSET).
- E2E in PostgreSQL proxy module with two shards and a
non-sharding filter, expecting 1,2.
6. Docs: add a note on the current limitation and the new support once
implemented.
We warmly welcome community contributors to submit a PR implementing the
merge logic and tests; we’ll gladly help review and verify.
--
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]
