tracker issue | What iT iS dESign studios

Title:

isNull() Fails When Using Scoped Query Variable

| View in Tracker

Status/Resolution/Reason: To Fix//BugVerified

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

Can you please provide any update for this issue?  Thanks!

Comment by Michael D.

32038 | January 02, 2020 02:33:01 PM GMT

Updated to show that this issue still remains in HF7 (2018,0,07,316715)

Comment by Michael D.

32039 | January 28, 2020 02:58:19 AM GMT

I am able to reproduce this issue with the following code.

Working example of the following code:
https://retroshockband.com/scratch/4205912/test2.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},
		{id=4,title="Four"}
	]);

// writeDump(person);

writeOutput("<br><hr>Within cfscript block. This works as expected.<br><br>");

person.each(function(row, index) {
	writeOutput("Row: " & index & "<br>");
	writeOutput("isNull(row.title): ");
	writeOutput(isNull(row.title));
	writeOutput("<br>");

	writeOutput("row.title: ");
	if (!isNull(row.title)) {
		writeOutput(row.title);
	}
	writeOutput("<br><br>");
});

</cfscript>

<cfoutput>

<hr>
Within cfoutput block as inline expression. This works as expected.<br><br>

#person.each(function(row, index) {
	writeOutput("Row: " & index & "<br>");
	writeOutput("isNull(row.title): ");
	writeOutput(isNull(row.title));
	writeOutput("<br>");

	writeOutput("row.title: ");
	if (!isNull(row.title)) {
		writeOutput(row.title);
	}
	writeOutput("<br><br>");
})#

<hr>
Within cfloop query block. This returns incorrect results.
The third row should display "YES".<br><br>

<cfloop query="person">
	Row #person.currentRow#<br>
	isNull(person.title): #isNull(person.title)#<br>
	<cfif !isNull(person.title)>
		#person.title#
	</cfif>
	<br><br>
</cfloop>

</cfoutput>

Comment by Michael D.

32040 | January 28, 2020 10:16:43 PM GMT

Further testing reveals that isNull() does not work correctly when looping over query results with a for loop:

for (row in person) {
  isNull(person.title);
}

Comment by Michael D.

32041 | January 29, 2020 06:39:00 PM GMT

I want to note an error I made in my previous comment.  I used the wrong scope in the isNull() function.  It should have been:
isNull(row.title) rather than isNull(person.title)
This particular use-case works as expected when corrected.

Comment by Michael D.

33201 | February 26, 2020 03:01:39 PM GMT

tracker issue : CF-4205912

isNull() Fails When Using Scoped Query Variable