tracker issue : CF-4203676

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

ColdFusion is inconsistent in the way it handles data of BIT type within a query

| View in Tracker

Status/Resolution/Reason: Open//

Reporter/Name(from Bugbase): A. Bakia / ()

Created: 12/07/2018

Components: Database, General

Versions: 2016

Failure Type: Others

Found In Build/Fixed In Build: 2016.0.07.311392 /

Priority/Frequency: Normal /

Locale/System: / Windows 10 64 bit

Vote Count: 1

Problem Description:
Take the two database brands SQL Server and MySQL, for example. In SQL Server a BIT has value 0, 1 or NULL. In MySQL, BIT(1) can take values 0, 1 or NULL. They are stored as such in the database.

However, when you specify the value of a BIT type as 1 or 0 in a query, ColdFusion converts the value interchangeably to Yes or No or to True or False. This can lead to inconsistent behaviour and to errors. 

Steps to Reproduce:
1. Create a database table called tbl in SQL Server. It has just one column, testBit, of type BIT. Insert 4 rows into the table, containing respectively the values 1, 1, 0, 0.
2. Run the following code:
		sSql_1="	select testBit
				  from tbl
				  where testBit = :nBit ;";
		stSqlParams.nBit={value:1, cfsqltype:"BIT"};
		q1=queryExecute(sSql_1, stSqlParams);

		isValidList=valueList(q1.testBit);

	        writedump(var=q1, label="q1");

		sSql_2="	select testBit
				  	  from q1
				     where testBit in (#valueList(q1.testBit)#) ";
               q2=queryExecute(sSql_2, stSqlParams, {dbtype="query"});

	      writedump(var=q2, label="q2");

Actual Result:
Inconsistency - The value of the BIT is 1. Whereas the dump of q1 shows that the SQL parameter bound to is "Yes" instead. Even though the binding is to "Yes", the value of valueList(q1.testBit) is "true,true" instead of "Yes,Yes".
Error - The query-of-query, q2, results in an error. That is because valueList(q1.testBit) results in the clause "where testBit in (true,true)", which is bad SQL.

Expected Result:
Consistency in the way ColdFusion handles the BIT values 1, Yes and True.
No errors.

Any Workarounds:
If the valueList comes into the second query as an SQL parameter, then there will be no error. Something like this for the second part:

            sSql_2="	select testBit
				  	  from q1
				     where testBit in (:bitList) ";
            stSqlParams.bitList={value:valueList(q1.testBit), cfsqltype:"VARCHAR", list=true};
	    q2=queryExecute(sSql_2, stSqlParams, {dbtype="query"});
	      writedump(var=q2, label="q2");

Attachments:

Comments:

I assume you have defined your SQL Server datasource in Application.cfc
Comment by A. B.
30025 | December 07, 2018 03:33:16 PM GMT
Hi all, I have mixed thoughts.. <cfscript> //Create table `myTable` with column `myBit` of type `bit` and populate it w/ value `1`, then run: q = queryExecute("SELECT myBit FROM myTable WHERE myBit = 1") writeOutput(q.myBit[1].getClass().getName() & ' ' & q.myBit[1] & ' ' & q.getRow(1).myBit & ' ' & valueList(q.myBit) & ' ' & q.valueArray("myBit").toList()) //Actual Result: java.lang.Integer 1 YES true YES //Suggestion 1: java.lang.Integer 1 1 1 1 //Suggestion 2: coldfusion.runtime.CFBoolean true true true true //Suggestion 3: java.lang.Integer 1 true true true </cfscript> I'd advise against Suggestion 1 b/c it'd break code that expects serializeJSON(myQuery) to preserve bit as `true`/`false`. I'd propose Suggestion 3. Since both java.lang.Integer and coldfusion.runtime.CFBoolean allow string and numeric member functions already, existing code that uses string or numeric member functions on the bit-as-integer values would not break. I realize Suggestion 3 would still cause `valueList(q1.testBit)` (from this ticket's description) to still return "true,true". And I agree "1,1" would be preferred over "true,true" in that specific context. But, IMO, parameterized queries (the workaround from this ticket's description) should be recommended.. Thoughts? Thanks!, -Aaron
Comment by Aaron N.
30028 | December 08, 2018 05:52:13 AM GMT
+1 - BIT consistency is needed.
Vote by Aaron N.
30030 | December 08, 2018 05:54:07 AM GMT
Oops! I wish I could edit my previous comment. Adobe, can you please find/replace these?: 1) "I'd propose Suggestion 3." => "I'd propose Suggestion 2." 2) "I realize Suggestion 3" => "I realize Suggestion 2" Thanks!, -Aaron
Comment by Aaron N.
30029 | December 08, 2018 05:58:23 AM GMT
Thanks, Aaron. I prefer Suggestion 1., as it is the most intuitive. After all, Bit is a subset of Integer and java.lang.Integer is the wrapper class for integers. In any case, there are further reasons for us to call for consistency. There seem to be subtle changes in the underlying Java classes as we go from ColdFusion 11 and 2016 to ColdFusion 2018. To borrow from your example, x=1; writeoutput(x.getClass().getName()); gives: java.lang.String in CF11 and CF2016; java.lang.Integer in CF2018.
Comment by A. B.
30031 | December 09, 2018 07:03:18 PM GMT