Ben Nadel © 2008.
All content is the property of Ben Nadel and Kinky Solutions.
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):
Launch code in new window » Download code as text file »
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:
Launch code in new window » Download code as text file »
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:
Launch code in new window » Download code as text file »
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:
Launch code in new window » Download code as text file »
... It works absolutely fine. Not only that, you can even use the ORDER BY clause:
Launch code in new window » Download code as text file »
... 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:
Launch code in new window » Download code as text file »
Now, let's run the ColdFusion query of queries with a reference to the ID field in the WHERE clause:
Launch code in new window » Download code as text file »
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:
Launch code in new window » Download code as text file »
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:
Launch code in new window » Download code as text file »
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.
Download Code Snippet ZIP File
Comments (7) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
I suppose you've already found the solution to this, but just in case..
I ran into a similar problem doing some datagrid filtering with query of queries.. a quick summary:
WHERE
lower(somecolumn) like lower('somevalue%')
would yield the nullPointerException if any of the values of somecolumn were null in the original query. To fix it:
WHERE
lower('f'+somecolumn) like lower('f'+'somevalue%')
It works great, but it seems pretty lame that it can add or concatenate a nonnull value to a null value just fine, but it can't compare one? Gar.
Posted by Tim Schottler on Nov 20, 2006 at 12:39 PM
Tim,
That is a most excellent solution! Thanks for posting that. I agree that it is lame that it can concatenate but not compare. ColdFusion query of queries have a bunch of weird bugs, but I still love 'em.
The solution that I have settled on (and this is not always in my control) is just trying never to return a NULL value from the data base except for when absolutely necessary. So, i use things like:
ISNULL( COLUMN_NAME, '' )
So that if the column is NULL at least it returns an empty string instead of a null value. While these are not exactly the same thing, it works nicely. But in cases where this is not possible, i LOVE your solution. Thanks.
Posted by Ben Nadel on Nov 21, 2006 at 7:39 AM
This was EXTEMELY helpful....I had the NULL error when trying to use the Q of Q with LIKE...the fix to add on an extra letter to the column and value worked great....Glad to have found a fix after hours of racking my brain!
Thanks
Posted by TJ on Feb 21, 2008 at 1:37 AM
@TJ,
Glad to help.
Posted by Ben Nadel on Feb 21, 2008 at 7:16 AM
Ben,
Have you experienced this on CF8? I get this problem on CF7 with a reporting suite that I built but I haven't tried running the app on CF8 yet. Wondering if there have been any fixes in this area?
Posted by Brian on May 29, 2008 at 7:29 PM
@Brian,
I have not tried the specifically on CF8. I don't think they updated much query stuff, though.
Posted by Ben Nadel on May 29, 2008 at 7:34 PM
Hi,
I have this same problem with CF8. Thanks for the solutions.
Chris.
Posted by Chris on Aug 4, 2008 at 8:07 AM