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:
- Ranges with NULLs 01: starting over
- Ranges with NULLs 02: test cases
- Ranges with NULLs 03: Gaps
- Ranges with NULLs 04: Pack, Merge
- Ranges with NULLs 05: Segments
- Ranges with NULLs 06: Overlaps with Conflicting Data
- Ranges with NULLs 07: Swiss Army Knife
[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.
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:
- x=y: the attributes are identical
- x<y: the "earlier" range attribute is less than the "later" range attribute
- x>y: the earlier attribute is greater than the later one
- N-y: the earlier attribute is null
- x-N: the later attribute is null
- 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:
Table name RANGES0 RANGES1 RANGES2 RANGES2_ATTRS RANGES3 RANGES3_ATTR 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.