tracker issue : CF-3556076

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/Withdrawn/Duplicate

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

Created: 05/07/2013

Components: Database, CFQuery

Versions: 10.0

Failure Type:

Found In Build/Fixed In Build: Final /

Priority/Frequency: Major / All users will encounter

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

Vote Count: 1

Duplicate ID:	CF-3556075

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:	3556076

Deployment Phase:	Release Candidate

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



Tried it on ColdFusion 9,0,1,274733 (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.
15465 | May 07, 2013 02:08:45 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.
15466 | May 07, 2013 02:36:54 PM GMT
+1 -- Adam ////////////
Vote by External U.
15468 | May 07, 2013 02:37:08 PM GMT
Verified in CF10 update 11 and Splendor: <cfset q = queryNew("nullCol")> <cfset queryAddColumn(q, "dataCol", ["A"])> <cfdump var="#q#"> <cfoutput query="q"> dataCol: "#dataCol#"; IsNULL(dataCol): #IsNULL(dataCol)#; </cfoutput> Actual: dataCol: "A"; IsNULL(dataCol): YES; Expected: dataCol: "A"; IsNULL(dataCol): NO; (Comment added from ex-user id:nawani)
Comment by Adobe D.
15467 | September 20, 2013 05:47:15 AM GMT