While I was working on a test the other day, it kept failing. Not a big surprise, but I couldn’t figure out why. When I looked at tsqlt.testresults, I saw extra rows. Double rows in fact, and that threw me.
This was my Assemble code.
-- Assemble
CREATE TABLE #Expected (
yearnum int
, monthnum TINYINT
, salestotal money
)
INSERT INTO #Expected
( yearnum
, monthnum
, salestotal
)
VALUES
( 2012, 11, 2500.23 )
, ( 2012, 12, 2200.15 )
, ( 2013, 1, 2656.75 )
SELECT *
INTO #actual
FROM #Expected AS e
EXEC tsqlt.FakeTable @TableName = N'MonthlySales', @SchemaName='dbo';
INSERT dbo.MonthlySales
VALUES
( 11, 1000.00)
, ( 11, 1500.23)
, ( 12, 2200.15)
, ( 13, 1000.00)
, ( 13, 1656.00)
, ( 13, 0000.75);
Here was the output (ignoring the failure messages):
[tArticles].[test sum of sales by month for multiple months] failed: (Failure) The calculations are incorrect
|_m_|yearnum|monthnum|salestotal|
+—+——-+——–+———-+
|= |2012 |11 |2500.2300 |
|= |2012 |12 |2200.1500 |
|= |2013 |1 |2656.7500 |
|> |2013 |1 |2656.7500 |
|> |2012 |12 |2200.1500 |
|> |2012 |11 |2500.2300 |
Hmmm. What’s going on? Why don’t the rows match? If I run the query, I see the results I expect. Is it the query or test?
In this case, you read the results as showing that I have 3 rows that are the same in my expected and actual tables (@expected and @actual variables in the assert). However I also have 3 extra rows in the actual table, which appear to be duplicates.
If I go back to the Assemble, I see a pattern that’s a problem. Some people might think these hassles are a way to give up on testing. Some might build a better pattern. I’ll do the latter.
In this case I create the expected table and then I insert the expected results. Then I create my actual table from the expected one to keep the schema the same and avoid repeating code. However in this case I have a bug.
The bug is I’m moving the expected results to actual. If I asserted at this point, I’d pass. However then I run the query and insert the results, which happen to be the same as the expected results (my query works). If the query didn’t work, I might really spend a lot of time debugging it, but here I can tell my test code is buggy.
I have two choices to fix this.
- Add a WHERE clause of WHERE 1 = 0 (no rows inserted)
- Move the creation of the actual table.
My first thought was to adjust the pattern to this:
CREATE TABLE #Expected (
yearnum int
, monthnum TINYINT
, salestotal money
)
SELECT *
INTO #actual
FROM #Expected AS e
INSERT INTO #Expected
( yearnum
, monthnum
, salestotal
)
VALUES
( 2012, 11, 2500.23 )
, ( 2012, 12, 2200.15 )
, ( 2013, 1, 2656.75 )
I move the #Actual and #Expected tables together, so that once I get the results set, I immediately create the #Actual copy. I could leave things and do this:
CREATE TABLE #Expected (
yearnum int
, monthnum TINYINT
, salestotal money
)
INSERT INTO #Expected
( yearnum
, monthnum
, salestotal
)
VALUES
( 2012, 11, 2500.23 )
, ( 2012, 12, 2200.15 )
, ( 2013, 1, 2656.75 )
SELECT *
INTO #actual
FROM #Expected AS e
WHERE 1 = 0
EXEC tsqlt.FakeTable @TableName = N'MonthlySales', @SchemaName='dbo';
Maybe the best thing is to be careful and do this:
-- Assemble
CREATE TABLE #Expected (
yearnum int
, monthnum TINYINT
, salestotal money
)
SELECT *
INTO #actual
FROM #Expected AS e
WHERE 1 = 0
INSERT INTO #Expected
( yearnum
, monthnum
, salestotal
)
VALUES
( 2012, 11, 2500.23 )
, ( 2012, 12, 2200.15 )
, ( 2013, 1, 2656.75 )
Combine the ideas and keep this insulated from refactoring moving or adding code in there.
Remember, tests are code. This is why they should fail first, so that you have some confidence in your code working and causing a test to pass.
