Double Check Your Math Performing calculations in our applications is important. In fact, if we do any sort of math, we really want to ensure that our results are what we expect. That seems obvious, but I've run into applications where the code deployed didn't quite calculate things as expected. In fact, there are all sorts of cases where someone was using a type of rounding that didn't work well. You can read about a number of stories in this article, some of which were very expensive. While much of the work of calculations is done in application software, there are powerful capabilities in a database platform that are useful, especially when working with more than a row of data. It seems that even when developers want to treat an RDBMS as a storage location, they still will manipulate data with SQL functions to get results from a query that are easier to work with in their application. Some of us might use FLOOR(), CEILING() , ROUND(), or other mathematical functions in our work. We should be sure this is what the actual specification calls for, and that we are performing the calculations correctly. After all, mathematics in computers are often not as precise as we'd like them to be, or perhaps, not as we'd expect them to be. This is one reason I think you should have database testing. Clients have had the wrong calculations in production, sometimes for years. When we make switches based on values, or we perform a calculation designed to somehow round or trim a number, we may do so incorrectly. I've seen incorrect discount rates, tax calculations, and more in production systems. Even if you don't want to write tests for most of your database query code, you ought to at least ensure that any math calculations have some testing around them that documents the expected behavior with easy to use numbers. This might not prevent mistakes, but it does give you a way to explain what you expect to happen in the code, verify it works, and show the input and output to a client. Perhaps their set of eyes on your test and test data will prevent silly mistakes in your math algorithms. Steve Jones - SSC Editor Join the debate, and respond to today's editorial on the forums |
Ref: Variable Expansion in Strings - 
