apache / apache/shardingsphere
Postgresql : not support function row_number()
- Dominant language
- Java
- Stars
- 20.8k
- Forks
- 6.9k
- Avg merge
- 11h 38m
- Merged PRs (30d)
- 326
Description
## 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
Contributor guide
Research direction
Start with the ShardingSphere-Proxy 5.4.1 setup and reproduce the PostgreSQL query using row_number() over partition by house_id. Compare the proxy result with the expected relation_index values 1 and 2; done means the query produces the expected row numbers for the supplied memberinfo data.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100