CODEWITHSUNDEEP
postgresql
user904990
user904990

Reputation:

Rows sorting changes after updating a column not related to sorting at all

Have a ORM generated SQL like this:

SELECT * FROM "games" WHERE "competition_id" IN
(SELECT "id" FROM "competitions" WHERE "id"  IN (1,2,3))
ORDER BY "date" LIMIT 10

It is displaying rows 1 to 10.

However after:

UPDATE "games" SET "season_id"=2

same SELECT returns rows like 1,2,3 ... 11, 12 instead of 1,2,3 ... 9, 10

Yes, returned rows are still 10, but last ones are always replaced with ones after 10...

Upvotes: 0

Views: 116

Answers (1)

PinnyM
PinnyM

Reputation: 35531

LIMIT doesn't guarantee which rows will be returned if multiple matching results exist within the ORDER BY scope. So if you have more than 10 rows with the same date, or if most have different dates but it so happens that the 9th, 10th, 11th and 12th rows have the same date, you aren't guaranteed to get consistent results for which will be returned for the 9th and 10th slots. PostgreSQL will choose these at its own discretion - which will often change when records are updated.

Try adding id to the ORDER BY list to fix this.

Upvotes: 1

Related Questions