Is it ok to sort a List in the body of the test case to check for boundary data?

Question

I'm testing the results of a query. The table where the results are stored has a structure like this:

Id  SomeValue  Date        Hour
-----------------------------------
1   foo1       2015-01-01  700
2   foo2       2015-01-01  800
3   foo3       2015-01-01  900
...
18  foo18      2015-01-01  2400
19  bar1       2015-01-02  100
20  bar2       2015-01-02  200
...
41  bar23      2015-01-02  2300
42  bar24      2015-01-02  2400
43  baz1       2015-01-03  100
44  baz2       2015-01-03  200
(and on...)

And the query receives parameters in order to do the search based on Date and Hour columns like this:

SELECT *
FROM table
WHERE
    (date, hour) >= (:dateFrom, :hourFrom)
    AND (date, hour) <= (:dateTo, :hourTo)
-- there's no ORDER BY clause in the query

For example, if I use the following values:

• dateFrom : '2015-01-01'
• hourFrom : 700
• dateTo : '2015-01-03'
• hourTo : 600

The query will return all rows where the value of Date is between 2015-01-01 and 2015-01-03, the values of hour are higher or equal than 700 only for Date = 2015-01-01 and the values of hour are less or equal than 600 for Date = 2015-01-03. In this example, all the rows with Date = 2015-01-02 will be retrieved from the data source.

I retrieve the results of the execution of the query in a list. In order to evaluate the results, I'm using the values of the parameters I've used to check if the data in the list complies with them. I'm using a method to check if the date of the element is between dateFrom and dateTo, but I'm wondering how can I test the values of hourFrom and hourTo. I have the following ideas:

• Start checking for the minumum value of hour on the elements where the value of Date is equals to my dateFrom parameter and check if this value is equals to hourFrom. Do similar for hourTo but with the maximum value of those rows where value of Date is equals to dateTo parameter value.
• Sort the list in my test method by Date and Hour, then check the first and last elements in the list. The sort method to use will be obtained from the programming language I'm using.

Which option is correct to do? If none, what will be the best strategy to do it?. I'm using Java to write the tests, but this question is more focused on how to write the test method rather than the technology/framework to use. Also, I cannot modify the query to add an ORDER BY clause (that would ease lot of my work but is not feasible).

I'm concerned about best practice. I was thinking on sorting the data so I will make assertions on two elements but then I'm worrying if I also have to test the Comparator used for sorting because it may sort the list wrongly and my test will fail, while checking each element manually means using if-else statements for the assertions, and I'm not sure if that is a good practice.

Answer 1

I can see how your primary concern may be to write a unit test with the simplest logic possible. Doing this would raise your confidence level that when the unit test reports a success or failure, that it really does mean that the query is returning good or bad results, and not that you coded a bug in the logic of your unit test. It may not even matter to you to have the absolute best performance.

If this is your case, I propose using your very straight-forward option #1, where you simply check each date/time sequentially, and fail the unit test as soon as you encounter a date/time that is not within your min/max date/time. But I would adjust the method of comparing 2 sets of date/times in the following way to keep the comparison logic very simple:

Concatenate and format every date/time into the following string format: YYYY-MM-DD hhmm (e.g.: 2015-01-01 0700). In other words, your string is formatted with the required zero-padding such that it will always have a length of 15. Following this format precisely has the very convenient property that if you compare 2 such strings using the built-in String.compareTo() method, it will compare your dates accurately.

This then allows you to keep your logic very simple and readable. Here is an example of what this could look like (you didn't specify, so I'll assume that your date is a string, and the time is a number. But you can adjust to your actual types):

// starting boundary values
String dateFrom = "2015-01-01";
int hourFrom = 700;
String dateTo = "2015-01-03";
int hourTo = 600;

String formatString = "%s %04d"; // adjust as necessary.

String fromDateTime = String.format(formatString, dateFrom, hourFrom);
String toDateTime = String.format(formatString, dateTo, hourTo);

// execute query here

while (rs.next()) {
    String dateTime = String.format(formatString, rs.getString("date"), rs.getInt("hour"));

    if (fromDateTime.compareTo(dateTime) > 0 || toDateTime.compareTo(dateTime) < 0) {
        throw new Exception("fail unit test");
    }
}

Answer 2

There's nothing wrong with sorting the results after retrieving them. That is the approach I would take, but checking each row would work as well.

Answer 3

Both of the options you proposed are correct. The option where you sort the data will easier to implement.