tracker issue : CF-4203676

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: