Today I discovered a seemingly little discussed bug in ColdFusion when it comes to performing a query of a query, and using the LIKE keyword on a text field. In my case, I was running a query against a larger query matching on the trimmed email address. Despite clearly seeing only one record in the database, and in the parent query, my query of a query kept returning 13 hits. When I changed the query to display the field I was matching on, I discovered that it was matching the one record I wanted it too, plus 12 more that had no email address at all!
Figure 1: Query of Query wrongly returning NULL values as a valid match for a string value
To compound it further, it ONLY does this if it has at least one valid match, and it doesn't do it consistently.
Figure 2: This similar query, with a different email address, works the way it should, returning only a single match
For now, the only known fix is to deliberately add a second conditional to your WHERE statement of field IS NOT NULL to ensure you get the expected results consistently.
Figure 3: Once the "NOT NULL" conditional is added, we get the results we expected
This really isn't an excusable thing for ColdFusion...particularly as it has been around since at least 2008, at least per the entry at Charlie Arehart's blog, and possibly longer. Please ignore the idiot trying to argue that this is "correct" behavior in the comments on Charlie's entry, because it is not. Run the same code directly against any DBMS, MySQL, PostgreSQL, Oracle, and MS SQL, and you will NOT get null results. Why? Because LIKE '%somevalue%' is NOT equal to NULL. Nothing is. Only NULL is NULL, and that ColdFusion wrongly returns these as "matches" is just plain bad. I'm rather appalled that it hasn't been fixed in any of the patch releases, as it is clearly a flaw. I'd be curious to know if it was fixed in ColdFusion 9.