Recently I learned of an issue with an application where presentation of a resultset in the UI led end users to assume the order of the resultset was a feature of the application. This is a pretty common scenario that I have seen with many applications both on the web and off. Usually it is the result of not using an ORDER BY clause in the SQL query or not having any logical column to sort by in the resultset. The problem is amplified in the case where the end user is the one who created the data in the first place and believes that the order they created the data (via the GUI normally) is perserved by the application. In time, the resultset from the scenario described above may be returned in a different order. Consult your RDMS guru for all the scenarios where this might happen. I have seen it occur during import/exports of data, database replication, and during updates to tables where all rows are affected.
Regardless of the cause, the net result is guaranteed to be user complaints about an issue with functionality (ordering of the resultset in the UI) which was never intended to be a function of the application. This is what I term accidental perceived functionality (no doubt, somebody has a better name). Please post other examples of this (so we can all learn) in the comments.
Tips to avoid this situation:
- Hire a good business analyst to capture all requirements (including eliminating possible points of confusion). This won’t necessary avoid the problem, but will increase the odds.
- Make it a habit to always include an ORDER BY clause in all SELECT statements.
- During table definitions always provide a column that allows ordering of results in accordance with the desired functionality (capture by requirements)
- Thoroughly unit test the results sets (including order) of your DAOs.
