Always Have an Order By

Yesterday there was a report of a strange bug by a user on a system I’m currently working with.  I was told a specific row was not showing up in the data set.  The system was using PostgreSQL for the database and used ExtJS grid component to render the data set so my first instinct was the user was not paging to the next set of data.  To my surprise even when paging I was unable to find the data as well.  I was really confused on this one so I decided to throw the query into my trusty SQL editor and see what is going on.  The first thing I did was remove the limit and offset portions of the query as I had no interest in paging.  To my surprise the entry did in fact show up when I ran it without the limit and offset.  When I ran the query with a limit and a offset and paged through each page I could not find this row.

I was really confused now and to add to my confusion ExtJS was set to do sorting on the ID field of the dataset once the data arrived.  Yet the data itself did not do any sorting so it came back sort of out of place but not entirely.  So I then decided to add an ORDER BY to the statement just so the result would turn up on the first page and sure enough it did.

The reason for this is if you do not set an ORDER BY the database determines how to sort it.  Now usually this is fine if you have one table it’ll end up sorting by the order the data came into the database which usually means it’ll be ordered by the primary key.  This data set however joined several tables and did some sub queries.  So with the changing of the offset on the query resulted in the query planner changing how it was sorting the data set or at least that’s my theory.  It seems strange for it to do it but if you play around with a query analyzer for a bit some times you can see very different ways of putting a query together depending on just a few small changes in the query that you’d think would not result in any changes in how the data is put together.

So my tip to everyone righting queries make a habit of putting an order by statement in no matter how simple the query may be.  You may get logical results on simple queries but as they become more complicated the sorting may start to become unpredictable especially when you’re using offset or limit clauses.