[Looking for Charlie's main web site?]

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:

<CFQUERY NAME="demoq" DATASOURCE="cfdocexamples">
   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.)

Anyone know where I can find a recorded presentation about ...?

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.
I saw a question on a CF list today, where someone was asking whether anyone knew of a recorded presentation about a particular topic. That made me think it might be time to remind the community of my UGTV site:

http://www.carehart.org/ugtv/

There you can find over 200 recorded presentations from nearly 150 presenters, totaling 220+ hours of video, all for free, and searchable, with RSS feeds, including RSS feeds based on your search criteria.

New recordings are being added all the time. Indeed, I've been thinking also of starting to publish a weekly summary of submissions here on my blog, for those who don't think to get the RSS feed.

Copyright ©2024 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting