You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "agoodcoolman (via GitHub)" <gi...@apache.org> on 2023/11/23 09:21:04 UTC
[I] post [shardingsphere]
agoodcoolman opened a new issue, #29137:
URL: https://github.com/apache/shardingsphere/issues/29137
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
5.4.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy apache-shardingsphere-5.4.1-shardingsphere-proxy-bin
### Expected behavior
create table sql
`CREATE TABLE memberinfo ( id INT PRIMARY KEY, name VARCHAR(50), idcard VARCHAR(50), house_id VARCHAR(50),relation INT；`
insert into sample data.
`insert into memberinfo (id, name, idcard, house_id, relation) values (1, '张三', '421106199901011234', "123456houseid", 1) ,(2, '李四', '42110619900101789', "123456houseid", 1)`
query sql
`select house_id, relation, row_number() over (partition by house_id ORDER BY relation asc ) AS relation_index from memberinfo where house_id = '123456houseid'`
sharding-config
###expected result:
### Actual behavior
relation_xuhao coloum 1,1 is error.
expected result relation_index is 1,2.
### Reason analyze (If you can)
in postgrmaybe not support.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### Example codes for reproduce this issue (such as a github link).
in database: Postgresql
query sql 'select house_id, relation, row_number() over (partition by house_id ORDER BY relation asc ) AS relation_index from memberinfo where house_id = '123456houseid''
> in Postgresql, function row_number() is not work
--
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] Postgresql : not support function row_number() [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Postgresql : not support function row_number() [shardingsphere]
Posted by "zt9788 (via GitHub)" <gi...@apache.org>.
zt9788 commented on issue #29137:
URL: https://github.com/apache/shardingsphere/issues/29137#issuecomment-1882498023
mysql not support `ROW_NUMBER() OVER (ORDER BY id)` too ,in shardingshpere5.3.2 @RaigorJiang
dose it will support in next version? or plan to support it
--
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] Postgresql : not support function row_number() [shardingsphere]
Posted by "shubham-j-sde (via GitHub)" <gi...@apache.org>.
shubham-j-sde commented on issue #29137:
URL: https://github.com/apache/shardingsphere/issues/29137#issuecomment-2067320348
Hey @agoodcoolman
I've been trying to reproduce the issue, using ShardingSphereProxy 5.4.1 and Postgres 16.2.
Have used the sharding-config you have posted (naming it as sharding_db).
But row_number() seems to be working fine:
<img width="1162" alt="Screenshot 2024-04-20 at 2 55 45 AM" src="https://github.com/apache/shardingsphere/assets/70763025/057bc83c-08d6-49d9-aaf4-d76177ecc8c1">
can you share postgres version and a sample dataset to look into this further.
also, @RaigorJiang , i'll like to try resolving this issue.
Thanks
--
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] Postgresql : not support function row_number() [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29137:
URL: https://github.com/apache/shardingsphere/issues/29137#issuecomment-1936532634
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] Postgresql : not support function row_number() [shardingsphere]
Posted by "agoodcoolman (via GitHub)" <gi...@apache.org>.
agoodcoolman commented on issue #29137:
URL: https://github.com/apache/shardingsphere/issues/29137#issuecomment-1833334579
@RaigorJiang sorry, I don't have time to modify this issue.
--
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] Postgresql : not support function row_number() [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29137:
URL: https://github.com/apache/shardingsphere/issues/29137#issuecomment-1873029669
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] Postgresql : not support function row_number() [shardingsphere]
Posted by "agoodcoolman (via GitHub)" <gi...@apache.org>.
agoodcoolman commented on issue #29137:
URL: https://github.com/apache/shardingsphere/issues/29137#issuecomment-1825036801
I'll try it.
--
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] Postgresql : not support function row_number() [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29137:
URL: https://github.com/apache/shardingsphere/issues/29137#issuecomment-2123356882
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] Postgresql : not support function row_number() [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #29137:
URL: https://github.com/apache/shardingsphere/issues/29137#issuecomment-1824650758
Hi @agoodcoolman ,
Thanks for your feedback, are you intrested in improving it?
--
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] Postgresql : not support function row_number() [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #29137:
URL: https://github.com/apache/shardingsphere/issues/29137#issuecomment-1835383304
@agoodcoolman
OK, I'll mark this issue as volunteer wanted, thank you~
--
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