Owen at 6 Months

Here are some new photos of Owen. He's six months old now!

SQL Subqueries with Null Values

Null values in data can often cause unexpected results. Recently I came across a case where the field in a NOT IN subquery contained null values, and I didn't get the behaviour I was expecting. After spending some time with this, I have it worked out, and the behaviour does make sense. It's worth having a look at.

We commonly use a NOT IN subquery to retrieve records that do not have a related record in another table. The common example of this type of query is to retrieve all customers that do not have any orders. Here's a simple example of a Customers table and an Orders table:

Customers                Orders
CustID    FullName       OrderID   CustID    OrderDate
     1    John Doe             1        2    1/1/2009
     2    Jane Doe             2        3    1/2/2009
     3    Jack Smith           3        3    1/3/2009
     4    Jane Smith

There are four customers, and two of these customers have placed orders. We can use IN and NOT IN subqueries like these:

select * from Customers where CustID in (select CustID from Orders)
2 Jane Doe
3 Jack Smith

select * from Customers where CustID not in (select CustID from Orders)
1 John Doe
4 Jane Smith

This is as we would expect. However, let's add a fourth record to the Orders table, with a null value for the CustID:

OrderID   CustID    OrderDate
      4     null    1/4/2009

Now, when we run the IN query, the results are unchanged; we still get customers 2 and 3. However, when we run the NOT IN query:

select * from Customers where CustID not in (select CustID from Orders)
No records returned

Why are there no records returned? Shouldn't we still be getting customers 1 and 4, since these CustIDs do not appear in the Orders table? Well, let's look at how this gets handled. The subquery generates a list of CustIDs, like this:

select * from Customers where CustID not in ( 2, 3, null )

Logically, the NOT IN is treated as a series of not equals expressions, like this:

select * from Customers where ( CustID <> 2 and CustID <> 3 and CustID <> null )

Now, we can consider how this evaluates for our customer records. For customer 1, the where clause becomes:

1 <> 2 and 1 <> 3 and 1 <> null
= true and true and null
= null

This is why record 1 doesn't appear in the result set; for records to appear, the where clause must evaluate to true, not to null.

Now that I've gone through the logic on this, it's not really correct to say that customers 1 and 4 don't have any orders. The null CustID value in the Orders table means that the customer for that order is unknown, so we can't guarantee that this order doesn't belong to customer 1 or 4.

Stonefield Article on SoftwareCEO

Stonefield Software is the subject of an extensive article on SoftwareCEO.

Stonefield Profile in the Financial Post

The Financial Post published an article profiling Stonefield Software yesterday.

Prairie firm finds its markets

Stonefield Profile

An article profiling Stonefield Software, the company I work for, was on the front page of the business section in the Leader Post. The article is available on the Leader Post website.

Stonefield Software the little Regina company that could

Exposition Problems

Recently, there was a blog post on my feed reader, referencing a recent paper on the topic of Open Exposition Problems in mathematics. To introduce this term, I'll use the same quote from the paper as was given in that blog post:

All mathematicians are familiar with the concept of an open research problem. I propose the less familiar concept of an open exposition problem. Solving an open exposition problem means explaining a mathematical subject in a way that renders it totally perspicuous. Every step should be motivated and clear; ideally, students should feel that they could have arrived at the results themselves.

This is an interesting idea, and I think it has applications in software development as well. The normal approach when explaining an algorithm is to just explain its steps. For any reasonably complex algorithm, it's also required to give some justification for why these steps achieve the desired result. Generally, the idea is that the student obtains enough of an understanding of the logic to produce a working version of the algorithm, and to extend it if need be.

That's fine, but the quoted text above goes further. It talks not only about the problem itself, but also about the motivation behind the steps of the solution, and about the student's feeling they could have constructed the solution themselves. This is something else entirely. We're now talking not just about explaining an algorithm, but explaining the process through which the algorithm was devised.

When writing code, we are always encouraged to add comments explaining how the code works. When the code needs to be maintained later, it's helpful to have these comments rather than having to work out what the code is doing. But, if someone's maintaining the code, it seems likely that they may be needing to write some similar code of their own. Maybe they need to extend this piece of code, or write a similar method in another language. In such a case, "exposition" comments might be useful as well, talking about how the code came about, other options that were rejected, and so on.

In any case, it's interesting to think about, both in terms of mathematics and software development. If nothing else, I learned the word perspicuous, and got a bit of a laugh that it was the word chosen to explain about making ideas completely clear.

Owen at 2 months

Here are a couple recent photos of Owen. Yesterday was his two-month birthday!