ColdFusion Query of Queries And Undefined Cell Values
Posted July 17, 2006 at 1:52 PM by Ben Nadel
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.
Reader Comments
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.
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.
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
@TJ,
Glad to help.
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?
@Brian,
I have not tried the specifically on CF8. I don't think they updated much query stuff, though.
Hi,
I have this same problem with CF8. Thanks for the solutions.
Chris.
Ben,
You stated that you use the following to prevent problems with nulls in the QoQs:
ISNULL( COLUMN_NAME, '' )
Is this a function that you can use within the QoQ itself? Or, in the database, when creating the original query object?
@Jason,
I use IsNull() generally at the main SQL level. I am not sure off-hand if it is supported by Query of Queries.
I just ran into this old problem again.
I have a massive query with about half of the rows having null data in one of the fields.
Using the like clause %#filter#% on the QoQ was returning all rows with undefined data above the first row that met the like criteria. The remaining rows behaved correctly. i.e. null or not, they were not returned unless the filter matched.
Strange brew.
@Justin,
That's definitely and odd behavior! ColdFusion query of queries certainly rock, but they can fall short and be very limiting if you don't have the "right" kind of data to work with.
Tim, Ben,
Thanks for the help--I was going nuts over this one. Should have Googled sooner!
Ben Nadel to the rescue! (again)
@Jon,
No worries - I just like that you come visit some time :)
Thanks for this hint, QoQ drive me nuts..
Michael
This has been a thorn in my side for a long time and I remember trying these fixes a couple years ago.
Neither of them worked for me.
My QoQ is a simple:
select * from orig_qry where col_x = 6
The pre-QoQ query results are compound - in that we have used the queryaddcolumn to add on multiple columns to a rather large original query.
My problem seems to typically be tied to date data fields (90% of the added cols are date columns)
I do put in empty strings in place of null values, but I still get the same errors.
Argh!
@SP,
What kind of work-arounds do you use for this problem?
I was unable to find a solid work-around in CF.
I am now re-writing the query using more oracle (procedures and such) so that I don't need to have the compound query results.
I have not had a problem with direct results from oracle. Only with the added columns.
I was getting a null pointer running a QofQ of another Q. It did not always occur depending on the data. Even if I removed the Where statement in the final QofQ it still threw the error.
I was able to fix it by explicitly declaring the column dataTypes in the first QofQ. Hope that helps someone.
