Clint Hill

This week I learned a valuable lesson. Well actually I learned a valuable lesson, and then I also learned an interesting detail about Oracle databases. Calling this detail valuable is debatable but it’s definitely something I never considered important. And that’s the key here (pun intended – you’ll see).

So first the valuable lesson. Here it is:

Never design an application that depends on a particular order for it’s data lists.

Easy right? Right. Here is an example. Imagine you have a list of colors. In that list however you want to make sure 2 colors always remain the default colors. The list might look like this:

• (1) Red
• (2) Green
• (3) Blue
• (4) Yellow
• (5) Orange

In this list you want Red and Green to always be default and always remain in the list and first and second position respectively. The numbers represent the ID the color has in the database and in this case isn’t relevant to the order necessarily. The order is specific to the default colors first, then all the others in any order. With the other 3 you can remove them and add new colors. In order to accomplish this you might put little “Delete” buttons next to the last 3 colors like this:

• (1) Red
• (2) Green
• (3) Blue [delete]
• (4) Yellow [delete]
• (5) Orange [delete]
• [add new]

This way the Red and Green stay default and the others you can add/remove. The simple design (and wrong) would be that the delete method simply deletes the color that is in the same position the delete button is in. Which is to say that the delete button next to Yellow would delete the 4th color in the list and the delete button next to Orange would delete the 5th color and so on.

Following our simple design we click on the delete button next to Yellow and the application deletes the 4th color in the data. If the database returns the colors in the same order we show them on screen we are fine. But what if the database returns the colors in another order? What if Yellow is showing up first and Red is 4th?

Well that’s easy, we apply a primary key index to the colors in the database and tell it to sort in ascending order by their ID number. Now the database should return the colors in the order that we specified on screen and our code will work.

Except it won’t always. And you may not notice it until it’s too late.

The problem is that there is a disconnect in the way the user screen is set and how the data is ordered coming from the database. The screen may always know how to list the colors, but it may not know what position those colors are in from the database. And even if you tell the database to place the primary key index in ascending order, it still might not work.

Let’s interject that interesting detail about Oracle here to help tell the story.

Setting a primary key index on an ID column in a table will default the sort order to ascending. Which implies that the results coming from the table will be in ascending order. If you place a primary key index on a numeric ID column for example, one might assume that the results of a SELECT would be that column returning in 1,2,3 order.

However: Do not ever count on the order of rows from a query unless you have an ORDER BY statement on your query! Oracle does not guarantee the sort order of any SELECT statement to be in the order of any index unless specified. FULL STOP

Back to the story. Now we’re deleting the colors based on position in the list and so far everything has worked. Except one time you delete Yellow and the screen shows Red missing and Yellow is still there. WTF!

Let’s go over how this might have happened. Given the order the colors are showing on the screen above, the database might have returned them in this order:

ID    COLOR
--    ------------
4     Yellow
2     Green
3     Blue
1     Red
5     Orange

And the problem should be clear now. We deleted the 4th color in the list.

“But wait …” you say.

Well remember that detail about Oracle? Even if you placed a primary key index on the ID column, Oracle will not guarantee that column will ever return in ascending order. Unless you specify.

The solution is to add “ORDER BY ID ASC” in the select statement that returns our colors from the database.

But the real solution and the point of this post is you should never design an application that is dependent on items being in a particular order from the database. In this example you could have the application separate the default colors from the list before deleting colors. Or some incantation like that. The point is that the application should do more to identify which color it wants to delete than to simply specify position in a list.

P.S. I’m no DBA, I just play one on T.V. but something really irked me about this nuance in Oracle. It makes theoretical sense (I read the manual), but really hurts in practical sense. This situation really happened to me and while I accept responsibility for the design mistake, I just wish Oracle might have acted like other databases in this case. Just to help a brother out. Am I wrong?