Home » Blog » Identifying Test Cases #2

Identifying Test Cases #2

2010-06-15 - General

Last time, in Identifying Test Cases #1, we looked at some simple examples of identifying test cases for SQL statements. This time, we're going to look at a more complex example in the form of a database view.

Our Example
Suppose we want to know which of our employees are under the age of 18. In many areas, employment rules are different for those employees, and our application will need to find them. We might write a view like this:

CREATE VIEW HumanResources.MinorEmployee AS
    SELECT EmployeeId, DATEDIFF(year, BirthDate, GETDATE()) Age
    FROM HumanResources.Employee
    WHERE DATEDIFF(year, BirthDate, GETDATE()) < 18;

We're going to look at three things from the perspective of identifying test cases:
1. The calculation of age
2. The GETDATE function
3. The boundary, where age < 18

We'll find some test cases, and maybe a bug or two along the way...

Calculation of age
The first thing we'll notice is that the age calculation is done twice! A simple mistyping here would lead to a defect. Furthermore, a calculation embedded inside a view or other location can be rather difficult to test. Let's make life a little simpler and move the calculation to a function. Our function might look like this:

CREATE FUNCTION HumanResources.CalculateAge (
    @BirthDate DATETIME
) RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(year, @BirthDate, GETDATE());
END;

The GETDATE function
We're not out of the water yet on testing our age calculation. The way our function is written right now, our test cases would have to change every time the value of GETDATE() changes... and well that changes pretty often! One solution is to make it so that our function doesn't use GETDATE() at all:

CREATE FUNCTION HumanResources.CalculateAge (
    @BirthDate DATETIME,
    @ReferenceDate DATETIME
) RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(year, @BirthDate, @ReferenceDate);
END;

You might be saying, "Well, Big Deal! Now calling this function looks so much like calling DATEDIFF, what's the point?" Bear with me a little longer, and we'll see why this helps!

At this point, we can easily write test cases against this function. Here's an example in the tSQLt SQL Server unit testing framework:

CREATE PROCEDURE testHumanResources.[test CalculateAge returns correct age on a birthday]
AS
BEGIN
    DECLARE @actualResult INT;
    
    SELECT @actualResult = HumanResources.CalculateAge('6/13/2000', '6/13/2010');
    
    EXEC tSQLt.AssertEquals 10, @actualResult;
END;
GO

So here are some other test cases we might consider:
1. BirthDate or ReferenceDate are NULL - because we know how special NULLs are. These tests will force you to consider how you want to handle nulls in your application.
2. BirthDate and ReferenceDate are equal - this is a special case. While it is not likely in this particular example because an employee would not be hired the day they were born, an age calculation may happen in other circumstances (e.g. the age of inventory).
3. BirthDate < ReferenceDate - the most common case, but let's take a closer look at the following:
   a) when BirthDate is just a few days before ReferenceDate. Is the result 0 as we'd expect?
   b) when BirthDate is exactly on a year boundary, as in our test case example above
   c) when BirthDate is a day before a year boundary
   d) when BirthDate is a day after a year boundary
4. BirthDate > ReferenceDate - this really should never happen, right?
5. When BirthDate is on February 29th of a leap year - leap years are often an overlooked piece of date handling

Some skepticism
Shouldn't we expect DATEDIFF to just work?
Perhaps, but we also need to validate our usage of it. If you write these test cases against the CalculateAge function, you may be surprised to find that there is a bug. (That's for homework, by the way)

Why do we test things that should never happen?
In the test cases #1, #2 and #4 above, we wouldn't really expect these values to happen in real life. #1 for example, may be entirely preventable if BirthDate is a NOT NULL column for the Employee table. However, if the CalculateAge function is applied to another purpose, it is reasonable that NULL values could be present.

#2 and #4 came to mind when working on a different application involving newborns. Obviously in this case, the BirthDate could be today; and if some crafty user decided that BirthDate was a good spot for expected due date (or made a typo and our application wasn't smart enough to catch it) a future date could be present. Besides, if the reference date could be in the past, it makes it possible for the BirthDate to be in the future relative to the reference date.

What are all these strange cases for #3 about?
Since the function calculates the age, one of the boundaries is the birthday anniversary. On either side of the employee's birthday, the value of age should be different. It's important to test boundary conditions because these are common places for defects to exist.

Homework
We're not done with this example yet, but I think you have enough to chew on for this time 🙂
Homework for this time is to write some of the test cases and fix the bug in CalculateAge.

What's next?
Next time we'll take a break from looking at code examples and talk about helpful tools and techniques for identifying test cases. Then we'll revisit this example and discuss a bit more about that pesky GETDATE() function and how to test the MinorEmployee view independently of the CalculateAge() function.

Categories: General