DaveWentzel.com            All Things Data

Unit Testing Is Not a Panacea

Perhaps you work at a company that REQUIRES all code to be unit tested.  Perhaps you work at a company where only some code is unit tested.  Perhaps only Java code is unit tested, not SQL code, etc.  I've worked in all of these environments and in each environment some code that was indeed unit tested did not work and some code that was never unit tested always worked like a champ since Day One.  Certainly unit tests help to get you to the Promised Land of bug-free code, but just because you have them doesn't mean your code works.  

I always advocate writing as many unit tests as you feel are necessary to exercise your code and make it more "self-documenting".  Notice I didn't say, "to exercise every line of code."  This is the first place I tend to disagree with most unit testing advocates.  Frankly if your code is written correctly there should be whole blocks of code that are never executed, or can't be executed, so how do you write test cases against them?  Think of WHEN OTHERS blocks in Oracle PL/SQL or TSQL CATCH blocks that can never generate an error short of shutting off the server when that line of code is executing.  The question becomes, are you unit testing that you understand how CASE or IF works, or are you unit testing YOUR code?  Said differently, are you unit testing that CASE or IF or error handling is implemented in the RDBMS as you think it is?  Or should you write unit tests that handle assumptions in your code that you would like to assert so the next developer understands better your assumptions?  Now, if a certain section of code throws a given error that we need to handle in a given way in WHEN OTHERS then of course we should test and assert that in a unit test.  In my opinion, every line of code does NOT need to be unit tested.  

What pisses me off is the advocates of total unit test coverage who

  • fail to test data access properly, ie, no integration tests.  
  • immediately state that "my tests succeeded, it's not my code" whenever a bug is opened.  

According to wikipedia unit testing is exercising the smallest functional component of a piece of software.  So basically, testing a function, method, subprocedure, or whatever.  We have junit for Java, nunit for C# (which are both really xunit ported to java or .net), PSUnit for PowerShell, but we never had a good unit testing tool for TSQL.  I wrote my own years ago which I used to simply assert that a given result set matched what I thought it should match.  The matching was done via Notepad++ automation.  Eventually I would assert the result sets were the same via EXCEPT and UNION of the outputs within TSQL alone, which cut down on the amount of data I needed to marshal to Notepad ++.  My new favorite tool is TSQLT which conceptually works just like xunit (it follows the Arrange/Act/Assert model) and rolls back your tests nicely upon completion.  Since TSQL is not a very robust language you can assume that TSQLT is not going to be a very robust unit test framework.  But what it does, it does well.  I love it.  You just need to understand its limitations.  

So, we have app guys that run junit tests, SQL guys that run TSQLT tests (if they test anything), but we haven't solved the impedance mismatch, namely are we sure the data is being populated by the app code correctly?  Unit testing paradigms stress separating the interface from the implementation, which means, for instance, your junit tests should not touch the database layer.  Ever.  

The Problem

Java developers write junit tests that exercise their class, but nothing outside of that class/process/network boundary.  If that class connects to a database then they'll mock out the database components by simulating the outputs that should be received by the database.  Likewise the SQL developers write their TSQLT unit tests for their stored procs but never concern themselves with looking at the actual inputs sent by the application.  That's the impedance mismatch.  If, and it's a big if, tests are written that cross boundaries then those are called "integration tests" and I don't hear many people touting the benefits of integration tests.  Another issue is that unit tests are supposed to run quickly...and settting up a db connection specifically for unit tests can make the test suite much slower.  

So what happens is the java guy makes assumptions about the stored procs, and the SQL developer makes assumptions about the inputs from java and suddenly everyone's unit tests are working but the system is totally non-functional. 

An Example

This happens daily for me, this isn't a contrived straw man.  Java Guy and SQL Guy are handed a requirement for a new screen in the application that allows them to filter Orders by a date parameter.  If the user does not enter a date parameter, then all rows entered FOR TODAY should be returned.  Both the Java Guy and SQL Guy agree that the proc will be called dbo.Foo with one parameter, @Date, returning one ID column.  Both run off in parallel to work on their piece of the story.  SQL Guy writes a proc similar to this:

And then writes a few unit tests that test that the necessary rows are returned

  1. when the @Date is valued it returns the correct data. 
  2. when the parameter is not valued it defaults to GETDATE()

Java Guy also codes his stuff and writes his junit tests (we won't cover those here).  SQL Guy tells Java Guy that his procedure is ready.  Java Guy tests that he can select a date and see data that looks reasonable.  He closes the story and passes the feature off to QA Guy for testing.  

QA Guy gets the code and enters some test data.  He passes a date parameter and sees his Orders.  Then he selects no date parameter and expects to see Orders FOR TODAY, but instead sees nothing.  QA Guy opens a Bug and assigns it to Java Guy.  Java Guy says his unit tests work and passes it on to SQL Guy who says the same thing.  Eventually they sit together and look at the issue together.  

So, what is the issue?  

Eventually SQL Guy looks at the call from the application using Profiler and notes the call looks like this:  


Do you see the problem?  

Although @Date parameter is defaulted to GETDATE(), Java is explicitly passing in NULL.  Usually at the point the simple fix is for SQL Guy to change his procedure to this:

This is a very simple example where unit tests are each layer of the application still has not produced good code.  Too many developers believe that the presence of working unit tests means their code is working.  Unit tests are great to help document code and assumptions but they are not a replacement for integration testing.