hasura / hasura/graphql-engine

Result order of tracked postgres function changed in 2.1 upgrade

Open
#8,026 3 comments 0 reactions 0 assignees View on GitHub
k/bug
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

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.