tracker issue : CF-4205912

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

isNull() Fails When Using Scoped Query Variable

| View in Tracker

Status/Resolution/Reason: To Track//NeedMoreInfo

Reporter/Name(from Bugbase): Michael D. / ()

Created: 11/14/2019

Components: Language, Null Support

Versions: 2018

Failure Type: Incorrect w/Workaround

Found In Build/Fixed In Build: 2018,0,05,315699 /

Priority/Frequency: Normal / Most users will encounter

Locale/System: English / Win 2012 Server x64

Vote Count: 0

Problem Description:

isNull() fails to return the correct value when using a scoped query field.  isNull() also fails when used within a cfif block when using a scoped query field.

Steps to Reproduce:

Enable null support in Application.cfc.

Perform a query (person) that returns a field (title) that contains null values in the records.

Issue 1:
Loop over the query results and display the result of isNull(person.title).
All results return "NO" even if the "title" field contains a non-null value.

<cfoutput query="person">
	<p>
		#isNull(person.title)#
	</p>
</cfoutput>


Issue 2:
Loop over the query results and use isNull() in a cfif block.
The following code throws an exception.

<cfoutput query="person">
	<p>
		<cfif not isNull(person.title)>#person.title#</cfif>
	</p>
</cfoutput>


Workaround That Solves Both Issues:

<cfoutput query="person">
	<cfset thisTitle = person.title>
	<p>
		#isNull(thisTitle)#<br>
		<cfif not isNull(thisTitle)>#thisTitle#</cfif>
	</p>
</cfoutput>

The workaround may require that a significant amount of code be modified to use the isNull function.

Attachments:

Comments:

Further testing reveals that the bug exists if any scope is specified in the isNull() function isNull(struct.key) <-- fails isNull(struct.struct.key) <-- fails
Comment by Michael D.
31842 | November 18, 2019 04:57:53 PM GMT
Michael, I am not able to reproduce the issue with HF5 or HF6. Can you share/try the following: - the error output - the complete working test code. - can you check if the issue is reproducible on HF6 Here's the test code that I tried. The output is as expected: ----------------------------------Application.cfc ---------------------------------- component{ this.name = "nullTest-T"; this.enableNullSupport = true; } ---------------------------------- test.cfm ---------------------------------- <cfscript> writeoutput(server.coldfusion.productversion) writeoutput(application.applicationname) s1 = {"k1" : "v1"} snull = {"k1": null} s2 = {"k1" : snull} person = queryNew("id,title","Integer,Varchar", [ {id=1,title="One"}, {id=2,title="Two"}, {id=3,title="Thirteen"} ]); writeDump(person) </cfscript> <cfoutput> <cfset val = s2.k1.k1> <cfif true> #isNull( s2.k1.k1)# </cfif> </cfoutput> <hr> <cfoutput query="person"> <p> #isNull(person.title)# </p> </cfoutput> <hr> <cfoutput query="person"> <p> <cfif not isNull(person.title)>#person.title#</cfif> </p> </cfoutput> <hr> <cfoutput query="person"> <cfset thisTitle = person.title> <p> #isNull(thisTitle)#<br> <cfif not isNull(thisTitle)>#thisTitle#</cfif> </p> </cfoutput>
Comment by Piyush K.
31884 | November 27, 2019 10:14:01 AM GMT
Piyush, thank you for looking into this issue. I took your code and modified it to show the issue happens on HF6. The code doesn't throw an exception, however, it returns unexpected results which can cause other errors to happen. Basically, the issue happens when using a query name scope within the isNull() function: isNull(queryName.queryColumn) <- This always returns NO. You can see this code in action at: https://retroshockband.com/scratch/4205912/test.cfm ---Application.cfc--- component{ this.name = "nullTest-T"; this.enableNullSupport = true; } ---test.cfm--- <cfscript> writeoutput(server.coldfusion.productversion & "<br>"); writeoutput(application.applicationname); person = queryNew("id,title","Integer,Varchar", [ {id=1,title="One"}, {id=2,title="Two"}, {id=3,title=null} ]); writeDump(person); </cfscript> <hr> <p>NOTE: I set the third record's title to be null in the queryNew() function.</p> <hr> <p>The following code returns INCORRECT results for the third record.<br> The third record should return "YES" for isNull().</p> <cfoutput query="person"> <p>#isNull(person.title)#</p> </cfoutput> <hr> <p>The following code returns INCORRECT results for the third record.<br> The brackets in the third iteration should not be displayed.</p> <cfoutput query="person"> <p> <cfif not isNull(person.title)>[#person.title#]</cfif> </p> </cfoutput> <hr> <p>The following code returns CORRECT results for the third record.<br> However, it requires an extra step of assigning a temp variable to the current record's title.<br> It should not be required to create a temp variable for every query column that could contain a null value.</p> <cfoutput query="person"> <cfset thisTitle = person.title> <p> #isNull(thisTitle)#<br> <cfif not isNull(thisTitle)>{#thisTitle#}</cfif> </p> </cfoutput>
Comment by Michael D.
31925 | December 04, 2019 11:36:30 PM GMT