tracker issue : CF-4162304

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

<cfqueryparam cfsqltype="cf_sql_integer" list="true" value=""> is coercing to 0

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/AsDesigned

Reporter/Name(from Bugbase): Alexandre Potvin Latreille / Alexandre Potvin Latreille (Alexandre Potvin Latreille)

Created: 06/08/2016

Components: Database

Versions: 11.0

Failure Type: Incorrect w/Workaround

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Win 2008 Server

Vote Count: 0

Problem Description: 

<cfqueryparam type="cf_sql_integer" list="true" value=""> is getting coerced to the value 0. 

Steps to Reproduce:

Assuming your SQL server engine supports this construct (here we use SQL Server 2008):

<cfquery name="q" datasource="someDsn">
    SELECT CASE 
        WHEN 0 IN (<cfqueryparam cfsqltype="cf_sql_integer" list="true" value="">) 
        THEN 1 
        ELSE 0 
    END AS result
</cfquery>

<cfoutput>#q.result#</cfoutput>


Actual Result: The output will be 1. 

Expected Result:

The list should be left blank which will result in the SQL predicate 'IN ()'. The SQL engine may throw an exception if it doesn't support empty sets like most implementations.

It would be up to the developer to correctly handle cases where a list may be empty, but CF shouldn't silently coerce an empty list to 0 as this may lead to unpredictable behaviors.


Any Workarounds: Handle empty lists explicitly in a way that is suitable for the use case.

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

Watson Bug ID:	4162304

Reason:	PRHaveInfo

External Customer Info:
External Company:  
External Customer Name: Alexandre Potvin Latreille
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

Comments:

@Alexandre, it looks like 0 is returned from the database in this case. We could alternatively throw an exception but that would invalidate a fix we had done way back in CF9 (bug #CF-3040214). Let us know.
Comment by Vamseekrishna N.
2486 | October 03, 2016 12:19:58 AM GMT
In the bug #CF-3040214 we can read "that should work and yield WHERE x in ( ) which is valid SQL and matches no rows". This statement is incorrect as it is not the case for every relationnal DB. For instance, both SQL Server and MySQL will throw a syntax error when encoutering IN () clauses. http://sqlfiddle.com/#!9/c0414b/1 In my opinion, the right behavior of cfqueryparam should only be to ignore the validation of the "value" attribute when null="true". Therefore, developers would have to explicitely specify null="true" when they know they have an empty value, which will result in WHERE x IN (NULL) and effectively match no records because NULL is not even equal to itself. If you want cfqueryparam to do a reasonable default, then it should automatically set null="true" when value="" and list="true" for non-string-like cfsqltypes. Therefore, <cfqueryparam cfsqltype="cf_sql_integer" list="true" value=""> would actually give <cfqueryparam cfsqltype="cf_sql_integer" list="true" null="true"> and result into WHERE x IN (NULL), but <cfqueryparam cfsqltype="cf_sql_varchar" list="true" value=""> would result in WHERE x IN ('') like it should because the first element can be considered an empty string.
Comment by External U.
2487 | October 10, 2016 04:33:55 PM GMT
*BUMP*
Comment by Alexandre P.
2488 | May 18, 2017 06:04:27 PM GMT
i think we can close it by design, i checked the sql query generated for this cfc and it looks like the following as we are using bindvariables. SELECT CASE WHEN 0 IN (?) THEN 1 ELSE 0 END AS result I verified the result on MSSQL and MYSQL and both return the value of this query as 1 which is expected. To answer your question about the statement should form a query like {code:java}SELECT CASE WHEN 0 IN () THEN 1 ELSE 0 END AS result{code} is a wrong assumption we are making,this can only happen if you create it as a raw query. Given that this query is being written using queryParam where-in you are providing  "" value and not a null value the statement will always be written as {code:java}SELECT CASE WHEN 0 IN (?) THEN 1 ELSE 0 END AS result{code} and the params being passed as "".
Comment by Ashudeep S.
2489 | July 26, 2017 06:53:55 AM GMT