Bug with Query of Query: using LIKE incorrectly finds records with null values
Note: This blog post is from 2008. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.Someone reported on a mailing list that they'd noticed a mistake in the results of a Query or Query operation when using a LIKE. It finds a record whose value for the column is null, which is of course wrong (that doesn't match the LIKE pattern--indeed a null never equals or doesn't equal anything).
The problem can be worked around by adding "and column is not null", but it really surprised me (and him, obviously) to discover the bug. Maybe someone else has written of it before.
But I tested it in 8.01 and can confirm it's still an issue. While I've recommended that he file it as a bug, I wanted to share it here until it's resolved. Hope this may help someone. (If we're missing something, feel free to comment.)
Here's some code you can run which uses the example app database that comes with CF. See the comments for what's wrong:
select * from employees
</CFQUERY>
<!--- note that by default, all records in the example employees table have values in all columns --->
<cfdump var="#demoq#" label="before insert">
<!--- insert a record with only one column (causing the others to be null)
--->
<CFQUERY NAME="insert" DATASOURCE="cfdocexamples" >
insert into employees (firstname) values ('charlie')
</CFQUERY>
<CFQUERY NAME="test2" DATASOURCE="cfdocexamples">
select * from employees
</CFQUERY>
<cfdump var="#test2#" label="after insert">
<!--- do a Q of Q of that query, for a column which would have a null for that last added records, so you'd expect it not to find it --->
<cfquery dbtype="query" name="test3">
select * from test2
where department like 'Sales%'
</cfquery>
<!--- that record with the null value still shows up! --->
<cfdump var="#test3#" label="dump of Q of Q">
Again, the problem is that the record with the null value still shows up in the Q of Q result, and it definitely should not. A solution for now is that if you change the Where clause to "department is not null and department like 'Sales%'", then it properly does not show the null-valued record(s) anymore.
(If anyone plays with this on their own code, note as well that Q of Q is case-sensitive, so no records would be found at all if you mistakenly used 'sales%' rather than 'Sales%', above.)
