I just came across an interesting "Feature" of the ColdFusion query involving null values. As I already knew, you can use the ColdFusion function QueryAddRow() to add records to existing record sets. What I did not realize is that after adding a row, those cells in the new record exist in a sort quasi-null value state.

Take this example for instance, where we create a test query from scratch (the same thing happens if you query directly from a SQL database):

  • <!--- Create a test query of sexy body parts. --->
  • <cfset qParts = QueryNew( "id, body_part, weight" ) />
  •  
  • <!--- Add some records to the query. --->
  • <cfset QueryAddRow( qParts ) />
  • <cfset qParts[ "id" ][ qParts.RecordCount ] = 1 />
  • <cfset qParts[ "body_part" ][ qParts.RecordCount ] = "face" />
  • <cfset qParts[ "weight" ][ qParts.RecordCount ] = "60%" />
  •  
  • <cfset QueryAddRow( qParts ) />
  • <cfset qParts[ "id" ][ qParts.RecordCount ] = 2 />
  • <cfset qParts[ "body_part" ][ qParts.RecordCount ] = "butt" />
  • <cfset qParts[ "weight" ][ qParts.RecordCount ] = "20%" />
  •  
  • <cfset QueryAddRow( qParts ) />
  • <cfset qParts[ "id" ][ qParts.RecordCount ] = 3 />
  • <cfset qParts[ "body_part" ][ qParts.RecordCount ] = "legs" />
  • <cfset qParts[ "weight" ][ qParts.RecordCount ] = "5%" />
  •  
  • <cfset QueryAddRow( qParts ) />
  • <cfset qParts[ "id" ][ qParts.RecordCount ] = 4 />
  • <cfset qParts[ "body_part" ][ qParts.RecordCount ] = "boobies" />
  • <cfset qParts[ "weight" ][ qParts.RecordCount ] = "15%" />

For each row that was added, all cells in the record were set. The following query uses the beloved query of queries feature to get all records that have a body part defined:

  • <!--- Query for defined parts. --->
  • <cfquery name="qGoodParts" dbtype="query">
  • SELECT
  • id,
  • body_part,
  • weight
  • FROM
  • qParts
  • WHERE
  • <!--- Make sure we only get parts that are defined. --->
  • body_part LIKE '_%'
  • </cfquery>
  •  
  • <!--- Dump out the query. --->
  • <cfdump var="#qGoodParts#" />

This query runs with absolutely no problems. Now, let's add another record but instead of setting all the cells, we are going to leave the BODY_PART field undefined:

  • <!--- Add another record to the query. --->
  • <cfset QueryAddRow( qParts ) />
  • <cfset qParts[ "id" ][ qParts.RecordCount ] = 5 />
  • <cfset qParts[ "weight" ][ qParts.RecordCount ] = "5%" />

Notice that in the above query, only ID and WEIGHT are defined, BODY_PART is not. If we try to run that same query of queries that we ran above, we get a the error "Null Pointers are another name for undefined values." If you run it through the CFError handler, it describes the error to be: java.lang.NullPointerException. It's like it won't let me reference the cell since it wasn't explicitly set.

But, if you run the query with no WHERE clause:

  • <cfquery name="qGoodParts2" dbtype="query">
  • SELECT
  • id,
  • body_part,
  • weight
  • FROM
  • qParts
  • </cfquery>

... It works absolutely fine. Not only that, you can even use the ORDER BY clause:

  • <cfquery name="qGoodParts3" dbtype="query">
  • SELECT
  • id,
  • body_part,
  • weight
  • FROM
  • qParts
  • ORDER BY
  • body_part ASC
  • </cfquery>

... and that works fine. To me, that doesn't make any sense. A WHERE clause will break on an unset value, but an ORDER BY clause will execute quite correctly.

Ok, so let's take it one step further. We are testing on a text field. What happens if we do the same test on the numeric ID field? Let's add another record, but leave the ID field undefined:

  • <!--- Add another record to the query. --->
  • <cfset QueryAddRow( qParts ) />
  • <cfset qParts[ "body_part" ][ qParts.RecordCount ] = "hips" />
  • <cfset qParts[ "weight" ][ qParts.RecordCount ] = "5%" />

Now, let's run the ColdFusion query of queries with a reference to the ID field in the WHERE clause:

  • <cfquery name="qGoodParts4" dbtype="query">
  • SELECT
  • id,
  • body_part,
  • weight
  • FROM
  • qParts
  • WHERE
  • <!--- Make sure we only get parts have a very small ID. --->
  • id < 3
  • </cfquery>

Again, this runs perfectly fine. Well, sort of; it returns the records with IDs 1 and 2, but it also returns the record with the undefined ID field.

Well, maybe it's not the numeric vs. the text field type. I mean, SELECT doesn't seem to be throwing the error when we remove the WHERE clause. Maybe it's the LIKE statement itself. Let's try running the query of queries using a text comparison other than LIKE:

  • <!--- Query for defined parts. --->
  • <cfquery name="qGoodParts5" dbtype="query">
  • SELECT
  • id,
  • body_part,
  • weight
  • FROM
  • qParts
  • WHERE
  • body_part = 'face'
  • </cfquery>

This runs perfectly. No errors, no runtime exceptions. It doesn't even return any undefined row values. So what is it about the LIKE directive of the WHERE clause? There must be some calculations going on under the hood that cannot handle undefined values. I wouldn't say that it's a NULL issue as I have covered in previous posts that while NULL values are not great, they will not throw errors in queries.

As one final test, I tried to run the query if something that would have to do calculations with the numeric ID field. Remember that earlier we added a record with an unset ID field value:

  • <cfquery name="qGoodParts6" dbtype="query">
  • SELECT
  • id,
  • body_part,
  • weight
  • FROM
  • qParts
  • WHERE
  • <!--- Force ID to be used in math. --->
  • (id + 1) = 3
  • </cfquery>

Again, no errors. It returns the correct record (ID = 2). Damn you LIKE directive, DAMN YOU! After some more testing, I have found that UPPER() and LOWER() on body_part field cause NULL pointer exceptions on undefined query of query values.