Strange postgres SELECT ordering issue

Question

I have this postgres sql query:

select * from stats where athlete_id = 5

That will return, as you'd imagine, all the stats for each athlete of id 5

So far so normal.

However I just noticed that for athlete_id above 110, it returns the stats rows in the opposite order.

So for athlete_id = 110 the 'id' column comes out like this which is what i'm used to:

etc. Each stats table row in order by id.

Then if you select 111, it comes out like this:

How is that even possible? This is all doing the queries inside the pgadmin interface. There's no additional where clauses, only the one I've stated, i.e. WHERE athlete_id = 111

What? I've been making all sorts of code changes but what on earth would cause this within pgaadmin /pgsql ?

What happened between 110 & 111? Lots and lots of refactoring and code changes inside rails, but no actual direct SQL manipulation. Yes I realise the answer might be in there, but I don't know how to look, as this is not in the rails app - this is pure SQL via pgadmin, so something must have been done in postgres - I need to understand what that might be or I'm at a loss to debug.

Any ideas? In rails, things like:

Athlete.stats.last.score return the 'first' row instead of the 'last' row, completely screwing up the application, but only for athletes with an id above 110!

Totally confused!

rows are not kept in a specific order in a database, if you want to have rows ordered by a column use order by column, — cristian
– cristian, Commented Dec 14, 2011 at 22:42

Mark Byers · Accepted Answer · 2011-12-14 22:48:08Z

6

If you don't specify an ORDER BY clause, there is no guarantee as to what order the rows will be returned in. They could be ascending order, descending order, an order that has no apparent logic to it, and potentially even a different order each time you query (though that is unlikely to happen in practice).

The specific order you get back is due to implementation details of the steps in the query plan. When you change the parameters it can cause PostgreSQL to prefer one query plan over another. For example, it may decide to use a table scan for one query, but an index for the other. It's hard to know exactly the reason in your specific case, but looking at the results of EXPLAIN might give some hints.

To fix your query to return the rows in the order you expect, add an ORDER BY clause:

SELECT *
FROM stats
WHERE athlete_id = 5
ORDER BY id

edited Dec 14, 2011 at 22:48

answered Dec 14, 2011 at 22:41

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

SlavaNov Over a year ago

You can write ORDER BY id ASC or ORDER BY id DESC to specify the order (A-Z or Z-A)

Dave Over a year ago

I understand all that. But something changed. And also, if ruby/rails doesn't implement order by in order to use the '.find' method ... what the hell is it doing? I'm trying to figure that out right now. It seems bizarre that rails would rely on whatever query plan happens to be working as described in the answer above.

mu is too short Over a year ago

@Dave: If you're saying Stat.where('athlete_id = ?', 5) (or similar) then Rails won't include an ORDER BY clause because you didn't ask for one, if you want an ORDER BY then you need to add a .order('whatever ordering you want') call. Why would Rails go to the trouble of sorting when you didn't ask for it?

Dave Over a year ago

Hi mu is too short --> spot on. I was doing:

Dave Over a year ago

Sorry, pressed enter and submitted comment. I was doing: lastStat = self.videostats.last ...but looking at the log it wasnt adding an order by clause. I've changed that to lastStat = self.videostats.find_last and that does add the order_by and things work correctly. it does not explain whats happened to change the ordering [i understand that ordering can be arbitrary but it did change] but a big piece of the puzzle is that 110 was the last entry downloaded from a heroku db dump, and 111 was a new one. So its still a mystery to me but there's clues all around!

Joe Stefanelli · Accepted Answer · 2011-12-14 22:42:43Z

2

Unless you specify an ORDER BY clause on your query, you cannot assume/expect that SQL will return the data to you in any certain order.

answered Dec 14, 2011 at 22:42

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Collectives™ on Stack Overflow

Strange postgres SELECT ordering issue

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related