ColdFusion Queries Do Not Throw Out-Of-Bounds Errors
The other day, in my post about ColdFusion query-of-queries and grouping, Terry Sasaki showed me something new and wonderful about the ColdFusion query object. Apparently, the ColdFusion query will never throw an out-of-bounds exception when you attempt to reference rows that don't exist. Much like a NULL column value, an out-of-bounds reference will simply return an empty string.
To see this in action, let's build a small query and then loop over it, including rows that are not part of the data set.
<!---
Define an empty query object - we will add the columns w/
default values independantly.
--->
<cfset data = queryNew( "" ) />
<!--- Add the value column. --->
<cfset queryAddColumn(
data,
"value",
"cf_sql_integer",
listToArray( "1,2,3,4,5" )
) />
<cfoutput>
<!---
Loop over the query using both pre/post out-of-bounds
index values.
--->
<cfloop
index="row"
from="-1"
to="#(data.recordCount + 2)#"
step="1">
<!---
Output the value in this row. Note that this row
may not actually be in-bounds with the record set.
--->
Row #row# :: #data.value[ row ]#<br />
</cfloop>
</cfoutput>
Notice that in our index loop, we are starting before row 1 and ending after row N. When we run the above code, we get the following page output:
Row -1 ::
Row 0 ::
Row 1 :: 1
Row 2 :: 2
Row 3 :: 3
Row 4 :: 4
Row 5 :: 5
Row 6 ::
Row 7 ::
As you can see, out-of-bounds row references simply return the empty string.
While this might not seem interesting at first - why would you ever want to reference rows that don't exist? It means that you can include comparison logic without having to include boundary logic. So, for example, where you may have once had logic like this:
<cfif (
(query.currentRow gt 1) &&
(query.value neq query.value[ query.currentRow - 1 ])
)>
... do something ...
</cfif>
... you can now simply the logic to be:
<cfif (query.value neq query.value[ query.currentRow - 1 ])>
... do something ...
</cfif>
Notice that we are completely able to remove the conditional check for row count. Since the ColdFusion query object won't throw a boundary exception, we don't have to check to see if our [N-1] row exists.
NOTE: This, of course, assumes that the value "" is not expected in a given column; otherwise, your comparison to a non-existing row might give you a false positive.
ColdFusion has always been focused on making things easy when they don't need to be complex. This query object behavior would appear to be very much in alignment with that value system. Will you often need to reference rows that don't exist? No, probably not. But, when you do, ColdFusion makes it painless because adding pain would not add value.
Want to use code from this post? Check out the license.
Reader Comments
Is this new behavior? I seem to recall that it USED to error when referring to a row that didn't exist. I could be wrong though.
@Andy,
Terry says he thinks it may have been added in MX6. I ran the above code in CF8 (I don't have access to anything prior to that).
Is this the difference between a direct db query object and query-of-query object? I think it used to error out for a direct query object.
@Smita,
I just ran this:
... and it worked fine. Looks like (at least from CF8), this works with standard queries as well.
@Ben,
That's good to know. Thanks for the info! I would have never known since I always do cfloop query...:)
@Smita,
Me too (re: cfloop for queries). I can't believe I made it to CF9 without ever seeing this work. I can definitely see a few cases where this is going to be good to know.
Maybe this is just a get-off-my-lawn comment (or the experience I have with strongly-typed languages, or both?), but I'm not sure I see this as something I would want to do. I feel that in general, it's bad practice to loop through any kind of ordered collection without respecting its boundaries, and I'm not really sure that you'd gain that much from starting before or ending after the query boundaries.
You could just as easily deal with the first row by setting your flag to match the value in row 1 and looping from row 2 to query.recordCount, and as the example shows, you have to do something specific simply to get past the final record in your query.
I suppose this is kind of like catching Exceptions and not doing anything with them. (In fact, that might be how CF's implemented it: a try-catch block that catches e, buries it, and returns an empty string.) It's not bad if you don't care about the exception anyway, but if you do write bad code that would ordinarily trigger an exception, you won't get one, and that can make debugging painful.
Looking at this as a curiosity is fine with me, but do be careful if you take advantage of this in a "real" application!
@Dave,
Most of the times we don't need to access a particular row's column value. But I have experienced this to be failing when I was coding in CF5 or so. For example I have a customer master query in my session and somewhere I need to display a particular customer's attribute, so instead of requerying, I can just do a listfind in the valuelist and pick the row and then use the row pointer to pick the column value. This I remember would always fail if listfind returns 0. So I remember checking the value before using to pick the query.column[row] value. It would have been so much easier just to display blank instead of erroring out.
I agree with @Ben that it is useful in certain instances.
@Dave,
The index-loop that started before/ended after the query bounds was not meant to be a practical example; that was merely meant to demonstrate that errors do not get thrown.
To me, the practical value of this is to not having to check bounds when doing row-to-row comparisons. If you know that an out-of-bounds record will return the empty string, then you can leverage that understanding of the core language behavior to simplify your logic.
@Smita, what we've done in similar instances is put the query in a struct and search the keys. If you're using CF's ORM or a framework (or both), there will typically be a "cleaner" way to look for a matching object.
@Ben, no worries, you've been pretty clear about this type of post being "Here's something that's possible" rather than "Ben Nadel sez do it this way" ... that's how I took it, at any rate, just as something that's possible. (And apparently has been possible for a while?) It's just that this sort of thing reminds me of other you-can-try-it-but-be-careful things ... C strings as arrays, for example, reading past the null character. (Strings were stored as sequences of characters ending with a null character (chr(0)); you could get the length of a string and loop from 0 to length-1, but you could also continue past the end, looking at whatever C happened to store in adjacent memory.) At least in this case, CF is consistent on both ends.
@Dave,
I think this type of thing probably falls under the category of "It makes sense if you know how it works, otherwise it's very confusing". This is like dealing with Truthy / Falsy values in some languages. If you know that "" is a falsy, it makes perfect sense; but if you don't, the code might seem completely illogical.
@Dave,
Yeah, I think I have looked up in structs as well. But if I remember correctly, there too you would have to say if StructKeyExists. I'll have to do a test to check this out.
@Smita, right, with structs a check is definitely required. (There are workarounds for that, too, but that goes back to the discussion about default values for methods ...) I think it's basically what Ben's saying ... this is something like using
when you know what resolves to True and False, except it's a shortcut for the CF equivalent of BOF and EOF.
I know referencing an out-of-bounds item *used* to throw an error. Since I was bitten by the issue in the past, I just assumed this continued to be the case--so I'm surprised to see the behavior has changed.
However, I wonder what the ramifications might be from doing this. I wonder if it's throwing an uncaught exception in the underlying code that might have some performance ramifications.
Anyway, this is a curious change in behavior and I wonder when it happened!
I seem to remember coming across this in the past, but I don't remember exactly when it was that I started encountering it (was it when I started using cf7, cf8, or was it back when I was just starting out and was using 6? I don't know...). But I DO remember explicitly coming across it while training my fellow employees to use coldFusion, because they weren't used to the language and specifically asked that question. I was a bit taken aback by the question at first and in fact had to research it to be sure of my answer, because I had grown so used to the behaviour that I actually kind of just took it for granted.
@Mr. DuPlantis - I think, if I understand correctly what you are saying, I believe it is just a difference of whether when you are coding you would prefer to have a syntax error (an exception error or something of the like) or a logical error (if even an error at all --you may very well intend on using this behaviour as a part of your application). But I, personally, have found this particular behaviour VERY useful in the past in programming using ColdFusion. I can't think of any specific examples, but I do remember times when it was useful. It can be a bit confusing to people who are coming from a background other than ColdFusion, though, as I discovered in my training classes, or people who use or prefer strongly typed languages. I don't think it causes programmers to be lazy and in the future not code correctly in a different language, I think it comes down to preference. I have had times when I had to go off the ColdFusion grid and program in a different language, and I have never had problems making the switch. It think it merely has to do with your preferences as far as this matter is concerned.
@Anna, I think that's fair to say. :)
@Dan,
The performance could be an issue; I'm not sure. I do like that message that Elliot Sprehn drilled into us at one of his CFUNITED presentations: Trust the language. Obviously, there will be exceptions to this; but, the point he was trying to make was that the language is probably going to be faster than you in most cases.
That said, I would hope that it's simply doing bounds-checking before it returns a value. I can't imagine why they would opt for error handling over value checking (which I assume is going to be loads faster).