hasura / hasura/graphql-engine
Result order of tracked postgres function changed in 2.1 upgrade
- Dominant language
- TypeScript
- Stars
- 32.1k
- Forks
- 3k
- PR merge metrics
- PR metrics pending
Description
### Version Information
Server Version: 2.1.1
### Environment
AWS Fargate / Aurora Postgres
### What is the expected behaviour?
When querying a tracked function without providing an `order_by`, results should be returned in the order they are provided by the postgres function.
### Keywords
postgres, order_by, sorting
### What is the current behaviour?
After upgrading to 2.1.1, my tracked function is returning results in an unexpected/inscrutable order when querying without `order_by`. Downgrading to 2.0.10 (which seemed to go without a hitch / manual catalog changes) returns the old behavior.
### How to reproduce the issue?
Here's my Postgres function:
```sql
SELECT *
FROM events
WHERE profile_id IN (
SELECT profile_id
FROM followed
WHERE user_id = hasura_session ->> 'x-hasura-user-id'
)
AND created_at > CURRENT_DATE - INTERVAL '6 months'
ORDER BY created_at DESC
LIMIT 200;
```
and this is the GQL query:
```gql
query TimelineScreenQuery($limit: Int = 12, $offset: Int = 0) {
feed_timeline(limit: $limit, offset: $offset) {
...TimelineFragment
}
}
```
### Any possible solutions?
Adding an `order_by` returns results in the proper order again. I have a mobile app deployed with a `order_by`-less query though, so that's not an option for me.
### Can you identify the location in the source code where the problem exists?
My guess would be that maybe "server: optimize SQL query generation with LIMITs" in the 2.10.0 changelog touched this behavior, but there is no linked PR to investigate further.
Contributor guide
Research direction
Start by reproducing the tracked PostgreSQL function query from the issue against versions 2.0.10 and 2.1.1, then inspect the changelog entry for “server: optimize SQL query generation with LIMITs.” Compare the generated query and result ordering; the issue is done when an order_by-less query preserves the function’s provided order, with regression coverage for that behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql
- Domain
- api, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 32/100