tracker issue : CF-3556075

select a category, or use search below
(searches all categories and all time range)
Title:

Using isNull() on a non-NULL query column returns TRUE

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): James Moberg / James Moberg (James Moberg)

Created: 05/07/2013

Components: Language

Versions: 9.0.1

Failure Type: Non Functioning

Found In Build/Fixed In Build: 9.0.1 /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Win 2008 Server R2 64 bit

Vote Count: 1

Problem Description: When using isNull() on data returned from a query, isNull() will return TRUE for all values.

Steps to Reproduce: Perform a query with NULL and non-NULL values. CFDump will indicate "[empty string]" only for NULL values, but isNull() performed on values will indicate that they are empty.

Actual Result: TRUE for all values

Expected Result: TRUE only for NULL values.

Any Workarounds:  Use Java since ColdFusion is broken and doesn't work as advertised.
<CFSET Request.testNull = GetResult.GetString("ResultColumn")>
<CFIF NOT StructKeyExists(Reques, "testNull")>NULL<CFELSE>Not NULL</CFIF>

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3556075

External Customer Info:
External Company:  
External Customer Name: Jamo
External Customer Email:  
External Test Config: My Hardware and Environment details: ColdFusion 9,0,1,274733 (64bit)



Tried it on ColdFusion 10,0,9,284568 (64bit) and it didn't work either.

Attachments:

Comments:

Here's some sample code that I wrote to test. <CFQUERY NAME="GetResults" DATASOURCE="CFMailblast">SELECT TOP 5 Email, Reason FROM AddressBad WHERE Reason IS NULL</CFQUERY> <CFDUMP VAR="#GetResults#"> <CFOUTPUT QUERY="GetResults"><CFSET Request.EmailNull = GetResults.GetString("Email")> <CFSET Request.ReasonNull = GetResults.GetString("Reason")> #CurrentRow#. "#Email#" #IsNULL(Email)# <b><CFIF NOT StructKeyExists(Request,"EmailNull")>NULL<CFELSE>Not NULL</CFIF></b><br> #CurrentRow#. "#Reason#" #IsNULL(Reason)# <b><CFIF NOT StructKeyExists(Request,"ReasonNull")>NULL<CFELSE>Not NULL</CFIF></b><br> <hr> </CFOUTPUT>
Comment by External U.
15470 | May 07, 2013 02:07:03 PM GMT
Here is a self contained repro case: <cfset q = queryNew("nullCol")> <cfset queryAddColumn(q, "dataCol", ["A", "B", "C"])> <cfdump var="#q#"> <cfoutput query="q"> <cfset dataColValue = q.getString("dataCol")> <cfset nullColValue = q.getString("nullCol")> #CurrentRow#<br> dataCol: "#dataCol#"; IsNULL(dataCol): #IsNULL(dataCol)#; IsNULL(q.dataCol): #IsNULL(q.dataCol)#; IsNULL(q.dataCol[currentRow]): #IsNULL(q.dataCol[currentRow])#; Actual Null: #!StructKeyExists(variables,"dataColValue")#; <br> nullCol: "#nullCol#"; IsNULL(nullCol): #IsNULL(nullCol)#; IsNULL(q.nullCol): #IsNULL(q.nullCol)#; IsNULL(q.nullCol[currentRow]): #IsNULL(q.nullCol[currentRow])#; Actual Null: #!StructKeyExists(variables, "nullColValue")#; <br> <hr> </cfoutput> Output: query DATACOL NULLCOL 1 A [empty string] 2 B [empty string] 3 C [empty string] 1 dataCol: "A"; IsNULL(dataCol): YES; IsNULL(q.dataCol): NO; IsNULL(q.dataCol[currentRow]): NO; Actual Null: NO; nullCol: ""; IsNULL(nullCol): YES; IsNULL(q.nullCol): NO; IsNULL(q.nullCol[currentRow]): NO; Actual Null: YES; 2 dataCol: "B"; IsNULL(dataCol): YES; IsNULL(q.dataCol): NO; IsNULL(q.dataCol[currentRow]): NO; Actual Null: NO; nullCol: ""; IsNULL(nullCol): YES; IsNULL(q.nullCol): NO; IsNULL(q.nullCol[currentRow]): NO; Actual Null: YES; 3 dataCol: "C"; IsNULL(dataCol): YES; IsNULL(q.dataCol): NO; IsNULL(q.dataCol[currentRow]): NO; Actual Null: NO; nullCol: ""; IsNULL(nullCol): YES; IsNULL(q.nullCol): NO; IsNULL(q.nullCol[currentRow]): NO; Actual Null: YES; Clearly a bug
Comment by External U.
15471 | May 07, 2013 02:36:34 PM GMT
+1 -- Adam ////////////
Vote by External U.
15477 | May 07, 2013 02:36:48 PM GMT
Now isNull() returns NO for both datacol and nullcol columns.
Comment by HariKrishna K.
15472 | January 21, 2015 07:18:43 AM GMT
Great! I've been waiting patiently for about two years for this ColdFusion 9 bug to be fixed. Where can I download the updated version of ColdFusion 9? Seriously if it's not fixed in v9, which version has it been fixed in? 10, 11 or 12?
Comment by External U.
15473 | January 21, 2015 10:51:24 AM GMT
Hi James, I've confirmed this is not fixed in CF11 Update 5. I'll let Hari specify which version it is fixed in. Hi Hari, In the version this is 'fixed' in, it is fixed only for data columns but not for NULL columns. Here is the result from running Adam's code in that version: 1 dataCol: "A"; IsNULL(dataCol): NO; IsNULL(q.dataCol): NO; IsNULL(q.dataCol[currentRow]): NO; Actual Null: NO; nullCol: ""; IsNULL(nullCol): NO; IsNULL(q.nullCol): NO; IsNULL(q.nullCol[currentRow]): NO; Actual Null: YES; 2 dataCol: "B"; IsNULL(dataCol): NO; IsNULL(q.dataCol): NO; IsNULL(q.dataCol[currentRow]): NO; Actual Null: NO; nullCol: ""; IsNULL(nullCol): NO; IsNULL(q.nullCol): NO; IsNULL(q.nullCol[currentRow]): NO; Actual Null: YES; 3 dataCol: "C"; IsNULL(dataCol): NO; IsNULL(q.dataCol): NO; IsNULL(q.dataCol[currentRow]): NO; Actual Null: NO; nullCol: ""; IsNULL(nullCol): NO; IsNULL(q.nullCol): NO; IsNULL(q.nullCol[currentRow]): NO; Actual Null: YES; Thanks!, -Aaron
Comment by External U.
15474 | July 30, 2015 03:31:45 PM GMT
This is partially fixed. So IsNull will now return false if values are defined. However, since CF does not have proper Null support yet, it cannot differentiate between nulls and empty strings. So there will be some incorrect results where IsNull returns false when it should have returned true. The only case where IsNull returns true when dealing with query variables is when there is no column defined with the given column name. This bug isNull() on a non-Null query is fixed, hence I will close this bug and logged a separate bug #CF-4178249 for isNull() on a Null column returns NO.
Comment by HariKrishna K.
15475 | August 03, 2016 12:23:56 AM GMT
"The only case where IsNull returns true when dealing with query variables is when there is no column defined with the given column name." That doesn't seem right. If a column doesn't exists in a query, and you try to reference that column in any function (including IsNull), shouldn't an error be thrown? Returning TRUE here is confusing.
Comment by External U.
15476 | August 03, 2016 01:02:42 PM GMT