Strange SQL Server Behavior

A co-worker discovered the following odd behavior in Microsoft SQL Server 2000.

declare @foo table (
A int, B varchar(50))

insert @foo values(null,'abc')

select IsNull(A,'-'), B from @foo
select IsNull(convert(varchar(5),A),'-'), B from @foo

The result of the first select statement is the value 0 for the first column. This statement in reality should have caused an error message and indeed does cause an error when the type of the A column is changed to a float. For some reason an integer has an implicit null conversion to the value 0. This may have something to do with the setting of ANSI NULL, I have not investigated it that much.

As a final note, this functionality is the same in SQL Server 2000 and SQL Server 2005.

The following select statement in MySQL returns the value '-' for the column A when in reality column A should be of type Integer!

SELECT IFNULL( A, '-' ) , B

and the following query works without error as well:

SELECT 1,''
UNION
SELECT IFNULL(A,'-'),B
FROM FOO

when the previous query should return an error because an integer type is not compatible with a char/varchar type!

So in conclusion it pays to be aware of this issue as my co-worker discovered. Apparently a significant amount of work was done based upon incorrect data resulting from this odd behavior.

New Apple Products

Yesterday Apple released a new iPod touch. Essentially this is a wi-fi enabled tablet computer along the lines of the Nokia 770. It seemes priced right at $299 for the 8GB model and $399 for the 16GB model. I think it will fit a niche market of people who want some of the features of the iPhone but not the AT&T service contract. When a few more carriers agree to work with Apple to make the iPhone work on their networks (read Verizon) I think the iPhone may really take off more. For now there is a halfway product that has a lot of the glamour of the iPhone, just without the big iPhone price tag and service commitment.

Advantages of Interfaces in Unit Testing

As a software developer I strive to integrate Unit Testing into my development process. At my organization there is a hard and fast rule, no testing code can reside in the production assembly. This was a problem with a class I was attempting to test because the class contains many protected methods that I wanted to be able to test in addition to the public interface to the class. Fortunately in .NET, if protected methods implement an interface on a class, you can access those methods if you create an object instance which is typed to the interface instead of the concrete class.

In C# you might do the following:


// This would be the class you are attempting to test in another assembly
public class Testing:ITestable {
protected Boolean TestThis() {return false;}

// You must make this interface public in order to execute the tests.
// However, if you only want to make this available in certain build
// configurations then you can easily add pre-compiler directives to
// exclude this interface from the release build configuration.

#if DEBUG
public Interface ITestable {
#else
private Interface ITestable {
#endif
Boolean TestThis();
}
}

//This class would reside in another class library assembly and get executing using an NUnit test runner.
[TestFixture()]
public class TestingTests {

[Test()]
public void TestThisTest() {
Testing.ITestable testObject = new Testing();

Assert.IsFalse(testObject.TestThis());
}
}


Essentially what is happening is that you conditionally include an interface which the unit testing class uses to reach inside of the object under test to enable testing of internal functions. One caveat, it is generally a much better design principle to just test the public interface, allowing for redesign and application of test cases to children who may inherit from the base class. However, for situations where you can not change the entire architecture, this method allows you to improve the code coverage of unit tests for classes with protected or private members that need testing.