tracker issue : CF-4207863

Issue: When column type is defined as OBJECT, empty string is stored as NULL (good). When column type is undefined (meaning, column type should default to OBJECT as per docs and CF10 discussions), empty string is stored as empty string (bad - b/c it should store as per how OBJECT type stores it). When column type is defined as VARCHAR, empty string is stored as empty string (bad - b/c the OBJECT type [which is the default type], and all other types, store it as NULL). This VARCHAR-specific inconsistency makes IS [NOT] NULL checks, in QoQ, unreliable.

Repro:

Application.cfc:
-----------
component {THIS.name = "MyApp"; THIS.enableNullSupport = false;}
-----------

index.cfm
-----------
<cfscript>
  //Create source query w/ empty cell
  mySourceQuery = queryNew("emptyCell", "varchar");
  mySourceQuery.addRow();
  //Create destination query w/ two rows, and populate the first row
  columnNameAndTypeList = ["object","integer","bigint","double","decimal","varchar","binary","bit","time","date","timestamp"];
  headerRowValues = [{one=1},1,1,1,1,1,charsetDecode(1,"utf-8"),1,createTime(3,4,5),createDate(2020,1,2),createDateTime(2020,1,2,3,4,5)];
  myQuery = queryNew(columnNameAndTypeList.toList(), columnNameAndTypeList.toList());//Columns w/ specified types are added here
  myQuery.addColumn("default", []);//Column w/ unspecified (i.e. 'default') type is added here
  myQuery.addRow();
  myQuery.setCell("default", queryNew("default"));//Populate the unspecifically-typed column
  for(i=1; i<=columnNameAndTypeList.len(); i++) {//Populate the specifically-typed columns
	  myQuery.setCell(columnNameAndTypeList[i], headerRowValues[i]);
  }
  myQuery.addRow();
  //Copy empty cell from source query into destination query, then use IS NULL to find destination query's empty cell
  for(columnMetadata in getMetadata(myQuery)) {
	  try {
		  myQuery.setCell(columnMetadata.name, mySourceQuery.emptyCell[1]);//Copy empty cell from source query into destination query
		  findNull = queryExecute("SELECT [#columnMetadata.name#] FROM myQuery WHERE [#columnMetadata.name#] IS NULL", [], {dbtype="query"});//Use IS NULL to find destination query's empty cell
		  typeName = columnMetadata.keyExists("typeName") ? columnMetadata.typeName : 'NULL';
		  writeOutput(columnMetadata.name & ' [' & typeName & ']: <span style="color:#findNull.RecordCount?'green':'red'#;">' & yesNoFormat(findNull.RecordCount) & '!</span><br>');
	  }
	  catch(any e) {
		  writeOutput(columnMetadata.name & ': <span style="color:red;">' & e.message & ' - ' & reReplace(e.detail, "<[^>]*>", "", "all") & '</span><br>');
	  }
  }
</cfscript>
-----------

Actual Result:

object [JAVA_OBJECT]: Yes!
integer [INTEGER]: Yes!
bigint [BIGINT]: Yes!
double [DOUBLE]: Yes!
decimal [DECIMAL]: Yes!
varchar [VARCHAR]: No! <= "No!" should be "Yes!"
binary [BINARY]: Yes!
bit [BIT]: Yes!
time [TIME]: Yes!
date [DATE]: Yes!
timestamp [TIMESTAMP]: Yes!
default [NULL]: Yes! <= "NULL" should be "JAVA_OBJECT" (CF-4207855)

Expected Result:

object [JAVA_OBJECT]: Yes!
integer [INTEGER]: Yes!
bigint [BIGINT]: Yes!
double [DOUBLE]: Yes!
decimal [DECIMAL]: Yes!
varchar [VARCHAR]: Yes!
binary [BINARY]: Yes!
bit [BIT]: Yes!
time [TIME]: Yes!
date [DATE]: Yes!
timestamp [TIMESTAMP]: Yes!
default [JAVA_OBJECT]: Yes!

Verified in CF2016 Update 1 (build 2016.0.01.298513) and CF2018 Update 8 (build 2018.0.08.318307) and later.

Note: This issue should've been fixed in CF10 when OBJECT was formalized as the default query column type. If users switch from unspecifically-typed columns to specifically-typed columns, their IS [NOT] NULL checks would be causing silent data corruption when touching now-varchar-typed columns containing 'empty' cells. THIS.enableNullSupport=true is not a workaround, due to issues/bugs.

Attachments:

Comments: