tracker issue : CF-3143550

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

Dramatic change to CF9's implementation of CFQuery causes major backwards compatibility issues

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): David McGuigan / David McGuigan (David McGuigan)

Created: 03/21/2012

Components: Database

Versions: 10.0

Failure Type:

Found In Build/Fixed In Build: Public Beta / 282072

Priority/Frequency: Major / Some users will encounter

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

Vote Count: 1

The ability to programmatically manipulate cfquery contents and inject non-simple data into them as demonstrated below is an extremely powerful and valuable capability for certain dynamic programming techniques in ColdFusion.

The code below works in ColdFusion 9 and now errors out with whatever cfqueryparam type the column is ( note no cfqueryparams are even used )

Invalid data { anyNonSimpleContent = true } for CFSQLTYPE CF_SQL_VARCHAR

<cfcomponent>
<cfset this.name = ’arbitrary’ />
    
<cffunction name="onRequestStart">
    
    <cfquery name="q" datasource="arbitrary"> select * from arbitrary limit X </cfquery>
        <cfdump var="#q#" />
        
        <cfset q.arbitraryColumnName[ 1 ] = "bleh" />
        <cfdump var="#q#" />
        
        <cfset q.arbitraryColumnName[ 1 ] = { anyNonSimpleContent = true } />
        <cfdump var="#q#" />

         <cfset querySetCell( q, ’arbitrary’, { }, 1 ) /> <!--- would error too --->
        
</cffunction>    
</cfcomponent>	

As it stands now it would require rewriting a large portion of various codebases for us to move to CF 10, which up until the discovery of this bug we'd planned to do on release day, thank you.

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

Watson Bug ID:	3143550

External Customer Info:
External Company:  
External Customer Name: David Michael McGuigan
External Customer Email:

Attachments:

Comments:

+1 I believe this is a result of #CF-3000469, which I’d filed to allow array notation to behave like querySetCell(). Array notation used to not validate when writing to a query cell. The drawback was, for example, numbers would be written as strings. So sorting was wrong and stuff like "SELECT SUM(myQueryColumn) FROM myQuery" would fail, if 1+ of the numbers had been set via array notation instead of querySetCell. I _should've_ specified for array notation to only validate when writing "simple" data to a query cell. The fact that it also validates complex data now causes this backward-compat issue. Can this validation be tweaked to only validate simple data? Thanks!, -Aaron
Vote by External U.
20038 | March 21, 2012 11:41:35 PM GMT
This bug resulted while trying to bring consistency in behavior of querySetCell and setting cell value using array notation. Earlier setting thru array notation was not converting the value to desired type i.e. mentioned while defining querynew(). The earlier fix for bug CF-3000469 brought consistency but break code. Doing validation for simple datatype will not fix this. For instance – you can have a column defined as numeric and you are setting a string value and it would still fail. So we had two choice to fix this. 1) Try to convert the types and ignore the conversion exception if raised for both ways of setting cell value. But then what does datatype of a column mean? Should we be this relaxed? 2) Other approach is to validate and convert the value and ignore the exception while setting value thru array notation while keep on raising the conversion exception while using querysetcell. This again brings inconsistency between them. Ideally the current behavior of raising exception in both the approaches of setting cell data makes more sense but looking at the backward compatibility, I have taken approach 2 as mentioned above.
Comment by Sanjeev K.
20036 | March 22, 2012 04:59:04 AM GMT
Hi Sanjeev, Regarding: "Doing validation for simple datatype will not fix this. For instance – you can have a column defined as numeric and you are setting a string value and it would still fail." Exactly! That is the expected behavior. I've just filed CF-4199436 to respect column type for simple values. Please see the code examples in its description. Thanks!, -Aaron
Comment by Aaron N.
20037 | August 06, 2017 11:30:51 PM GMT