Ranges with NULLs 02: test cases

When I write SQL there is a bit of trial and error (laughter), so I need as complete a set of test cases as possible. Here’s what I came up with.

Current list of posts: 

[UPDATE 2018-11-28: added some test cases and recalculated their number.]

0: input with no rows

Would you believe that one of my solutions doesn’t work when the input has no rows? Well…let’s put it this way, I never said what “working” should mean with no input rows. Now that I’ve added that test case, I have to say what output I expect.

1: one range per test case

This is another situation I never bothered to think about – until now.

2: two ranges per test case

With two ranges to compare, it only takes 7 test cases to cover all the relations defined in Dr. Allen’s Time Interval Algebra.
Time Interval Relationships

The image mentions “day of month” but the logic would be identical with numbers instead of dates. The boundaries are starting and ending points: the starting point is included in the range, but the ending point is not. When A’s ending point is equal to B’s starting point, the ranges “meet” with no gap and no overlap.

3: three ranges per test case

In all the time I have looked at range-related problems on forums, every bug could be illustrated with at most three ranges. I decided to generate all the possible combinations of relations: range A to range B, A to C and B to C. It turns out there are 75 valid combinations, so 75 test cases at 3 rows per test case.

3: three combinations of NULL and NOT NULL

The whole point in this series is to deal with NULL starting and ending points, so I will test three variations:

  • No nulls
  • Start can be null, end not null
  • Start not null, end can be null
  • Start and end can both be null.

[UPDATE 2018-11-8: I now omit the variant “Start can be null, end not null” since I have never seen it in real life.]

7 / 79: comparing attributes

When ranges intersect in any way, there may be a requirement to compare additional attributes. For example, we may want to merge two rows together if they intersect and all the attributes are the same. Limiting myself to one attribute only, I’ve come up with six combinations:

  1. x=y: the attributes are identical
  2. x<y: the "earlier" range attribute is less than the "later" range attribute
  3. x>y: the earlier attribute is greater than the later one
  4. N-y: the earlier attribute is null
  5. x-N: the later attribute is null
  6. N-N: both attributes are null

For completeness, I’ll add case “0-n/a”: when there is a gap, the attributes are not compared.

When there are three ranges, there are up to 79 different combinations of comparison.

Summary: 5128 test cases

Changing the number of ranges from 0 through 3, and adding attributes when there are two or three relations without gaps, I get a total of 5128 test cases in 6 different tables:

Relations                   0       0       7       7             75      75
Relations with or w/o nulls 0       0       21      21            225     225
Combinations of attributes  0       0       0       7             0       79
Test cases                  1       3       21      111           225     4767
rows                        0       3       42      222           675     14301

Forgive me for not publishing the actual test data, and the convoluted code I used to generate it. I may show bits as needed in later posts.

Next up: finding gaps in ranges.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s